MySQL 学习笔记基础篇

数据库基础学习

一、MySQL 数据库的数据模型

用户登录访问 数据库，数据库下存放若干表，表中存储信息。

二、SQL 语法——通用

1. 单行或多行书写，分号结尾；
2. 可空格、缩进；
3. 的 语句不区分大小写，关键字建议大写；
4. 单行注释用 -- something 或 # something；
5. 多行注释用 /* something */。

三、SQL 语法——DDL

全称 ，数据定义语言，用来定义数据库对象（数据库、表、字段）。

• 数据库-查询

  • 查询所有数据库

    SHOW DATABASES;

  • 查询当前数据库

    SELECT DATABASE();

• 数据库-创建

  CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];

• 数据库-删除

  DROP DATABASE [IF EXISTS] 数据库名;

• 数据库-使用

  USE 数据库名;

• 表-查询

  • 查询当前数据库所有表

    SHOW TABLES;

  • 查询表结构

    DESC 表名;

  • 查询指定表的建表语句

    SHOW CREATE TABLE 表名;

• 表-创建

  CREATE TABLE 表名(
  	字段1 字段1类型 [COMMENT 字段1注释],
      字段2 字段2类型 [COMMENT 字段2注释],
      字段3 字段3类型 [COMMENT 字段3注释],
      ......
      字段n 字段n类型 [COMMENT 字段n注释]
  ) [comment 表注释];

• 表-修改

  • 添加字段

    ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];

  • 修改数据类型

    ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);

  • 修改字段名和字段类型

    ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];

  • 删除字段

    ALTER TABLE 表名 DROP 字段名;

  • 修改表名

    ALTER TABLE 表名 RENAME TO 新表名;

• 表-删除

  • 删除表

    DROP TABLE [IF EXISTS] 表名;

  • 删除指定表，并重新创建该表

    TRUNCATE TABLE 表名;

补充： 的数据类型

1. 数值类型

分类	类型	大小	有符号(SIGNED)范围	无符号(UNSIGNED)范围
数值类型	TINYINT	1 byte	[-128,127]	[0,255]
	SMALLINT	2 byte	[-32768,32767]	[0,65535]
	MEDIUMINT	3 byte	[-8388608,8388607]	[0,16777215]
	INT/INTEGER	4 byte	[-2147483648,2147483647]	[0,4294967295]
	BIGINT	8 byte	[-263,263-1]	[0,2^64-1]
	FLOAT	4 byte	/	/
	DOUBLE	8 byte	/	/
	DECIMAL		/	/

关于 类型定义时传入两个参数 ， 表示精度，有效位数， 表示标度，小数位数。

2. 字符串类型

分类	类型	大小	描述
字符串类型	CHAR	0-255 bytes	定长字符串
	VARCHAR	0-65535 bytes	变长字符串
	TINYBLOB	0-255 bytes	不超过255个字符的二进制数据
	TINYTEXT	0-255 bytes	短文本字符串
	BLOB	0-65535 bytes	二进制形式的长文本数据
	TEXT	0-65535 bytes	长文本数据
	MEDIUMBLOB	0-16777215 bytes	二进制形式的中等长度文本数据
	MEDIUMTEXT	0-16777215 bytes	中等长度文本数据
	LONGBLOB	0-4294967295 bytes	二进制形式的极大文本数据
	LONGTEXT	0-4294967295 bytes	极大文本数据

后面传参是字符串长度

3. 日期类型

分类	类型	大小	范围	格式	描述
日期类型	DATE	3	1000-01-01 至 9999-12-31	YYYY-MM-DD	日期值
	TIME	3	-838:59:59 至 838:59:59	HH:MM:SS	时间值或持续时间
	YEAR	1	1901 至 2155	YYYY	年份值
	DATETIME	8	1000-01-01 00:00:00 至 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
	TIMESTAMP	4	1970-01-01 00:00:01 至 2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS	混合日期和时间值，时间戳

四、SQL 语法——DML

全称 ，数据操作语言，用来对数据库表中的数据进行增删改。

• 添加数据

  • 给指定字段添加数据

    INSERT INTO 表名(字段名1, 字段名2, ...) VALUES(值1, 值2, ...);

  • 给全部字段添加数据

    INSERT INTO 表名 VALUE(值1, 值2, ...);

  • 批量添加数据

    INSERT INTO 表名(字段名1, 字段名2, ...) VALUE(值1, 值2, ...), (值1 值2, ...), (值1, 值2, ...);

    NSERT INTO 表名 VALUE(值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);

• 修改数据

  UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2, ... [WHERE 条件];

• 删除数据

  DELETE FROM 表名 [WHERE 条件];

五、SQL 语法——DQL

全称 ，数据查询语言，用来查询数据库中表的记录。

SELECT [DISTINCT]
	字段1 [AS 别名1], 字段2 [AS 别名2], 字段3 [AS 别名3], ...
FROM
	表名列表
WHERE
	条件列表
GROUP BY
	分组字段列表
HAVING
	分组后条件列表
ORDER BY
	字段1 ASC/DESC, 字段2 ASC/DESC;
LIMIT
	起始索引, 查询记录数;

补充：聚合函数

统计数量 count

最大值 max

最小值 min

平均值 avg

求和 sum

六、SQL 语法——DCL

全称 ，数据控制语言，用来创建数据库用户、控制数据库的访问权限。

• 管理用户

  • 查询用户

    USE mysql;

    SELECT * FROM user;

  • 创建用户

    CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

  • 修改用户密码

    ALTER USER '用户名'@'主机名' IDENTIFIED WITH caching_sha2_password BY '新密码';

  • 删除用户

    DROP USER '用户名'@'主机名';

• 权限控制

  • 查询权限

    SHOW GRANTS FOR '用户名'@'主机名';

  • 授予权限

    GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

  • 撤销权限

    REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

七、函数

1. 字符串函数

   字符串拼接：CONCAT(S1,S2,...,Sn)

   字符串转为小写：LOWER(str)

   字符串转为大写：UPPER(str)

   左填充：LPAD(str,n,pad)

   右填充：RPAD(str,n,pad)

   去字符串头尾空格：TRIM(str)

   取子字符串：SUBSTRING(str,start,len)

2. 数值函数

   向上取整：CEIL(x)

   向下取整：FLOOR(x)

   取模：MOD(x)

   0~1随机数：RAND()

   四舍五入，保留小数：ROUND(x,y)

3. 日期函数

   当前日期：CURDATE()

   当前时间：CURTIME()

   当前日期和时间：NOW()

   获取年份：YEAR(date)

   获取月份：MONTH(date)

   获取日期：DAY(date)

   一个日期加上一个时间间隔：DATE_ADD(date,INTERVAL expr type)

   两个时间之间的天数：DATEDIFF(date1,date2)

4. 流程函数

   IF(value,t,f) 为 ，返回 ，否则返回

   IFNULL(value1,value2) 不为空输出 ，否则

   CASE WHEN [val1] THEN [res1] ... ELSE [dafault] END 如果 为 ，返回 ，……，否则返回 默认值

   CASE [expr] WHEN [val1] THEN [res1] ... ELSE [default] END 如果 的值等于 ，返回 ，……，否则返回 默认值

八、约束

约束是作用于表中字段上的规则，用于限制存储在表中的数据。

• 非空约束：限制该字段的数据不能为 ，关键字 NOT NULL
• 唯一约束：保证该字段的所有数据都是唯一、不重复的，关键字 UNIQUE
• 主键约束：主键是一行数据的唯一标识，要求非空且唯一，关键字 PRIMARY KEY，自增关键字 AUTO_INCREMENT
• 默认约束：保存数据时，如果未指定该字段的值，则采用默认值，关键字 DEFAULT
• 检查约束：保证字段值满足某一个条件，关键字 CHECK
• 外键约束：用来让两张表的数据之间建立连接，保证数据的一致性和完整性，关键字 FOREIGN KEY

外键约束语法：

CREATE TABLE 表名(
	字段名 数据类型,
   	......
    [CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主表字段名)
);

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表字段名);

外键删除/更新行为：

• NO ACTION：当在主表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新
• RESTRICT：同上
• CASCADE：当在主表中删除/更新对应记录时，首先检查该纪录是否有对应外键，如果有，则也删除/更新外键在子表中的记录
• SET NULL：当在主表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为 （要求子表字段可以设置为 ）
• SET DEFAULT：主表有变更时，子表将外键列设置成一个默认的值（Innodb不支持）

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表字段名) ON UPDATE 关键字 ON DELETE 关键字;

九、多表查询

使用 SELECT * from A,B 查询出来的是 表和 表的笛卡尔积，需要 关键字提取需要的项，去除不需要的项

1. 内连接

内连接查询的是两张表交集的部分

• 隐式内连接

  SELECT 字段列表 FROM 表1,表2 WHERE 条件…;

• 显式内连接

  SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件…;

2. 外连接

左外连接包括左表所有数据，右外连接包括右表所有数据

• 左外连接

  SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件…;

• 右外连接

  SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件…;

3. 自连接

可外可内

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件…;

4. 联合查询

把查询结果合并起来

SELECT 字段列表 FROM 表A …
UNION [ALL]
SELECT 字段列表 FROM 表B …;

UNION 去重

UNION ALL 不去重

5. 子查询

• 标量子查询
• 列子查询
• 行子查询
• 表子查询

十、事务

• 查看事务提交方式

  SELECT @@autocommit;

• 设置事务提交方式（设置成不自动提交）

  SET @@autocommit = 0;

• 提交事务

  COMMIT;

• 回滚事务

  ROLLBACK;

• 事务四大特性（ACID）

  • 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
  • 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
  • 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
  • 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

• 并发事务问题

  • 脏读：一个事务读到另外一个事务还没有提交的数据。
  • 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同。
  • 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在。

• 事务隔离级别

  • Read uncommitted：三个并发事务问题都会出现

  • Read committed：解决了脏读，没有解决不可重复读和幻读

  • Repeatable Read(默认)：解决了脏读和不可重复读，没有解决幻读

  • Serializable：三个问题都解决了

  • 查看事务隔离级别

    SELECT @@TRANSACTION_ISOLATION;

  • 设置事务隔离级别（SESSION 只管当前会话，GLOBAL 管全局）

    SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE};