MySQL 学习笔记基础篇

数据库基础学习

一、MySQL 数据库的数据模型

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

二、SQL 语法——通用

单行或多行书写，分号结尾；
可空格、缩进；
的语句不区分大小写，关键字建议大写；
单行注释用 -- something 或 # something；
多行注释用 /* 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 表名;

补充：的数据类型

数值类型

分类	类型	大小	有符号(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	[-2^63,263-1]	[0,2^64-1]
	FLOAT	4 byte	/	/
	DOUBLE	8 byte	/	/
	DECIMAL		/	/

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

字符串类型

分类	类型	大小	描述
字符串类型	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	极大文本数据

后面传参是字符串长度

日期类型

分类	类型	大小	范围	格式	描述
日期类型	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 '用户名'@'主机名';

七、函数

字符串函数

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

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

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

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

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

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

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

向上取整：CEIL(x)

向下取整：FLOOR(x)

取模：MOD(x)

0~1随机数：RAND()

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

当前日期：CURDATE()

当前时间：CURTIME()

当前日期和时间：NOW()

获取年份：YEAR(date)

获取月份：MONTH(date)

获取日期：DAY(date)

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

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

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 主表(主表字段名)
);

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

外键删除/更新行为：

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

1	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. 联合查询

把查询结果合并起来

1
2
3

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};

本文采用CC-BY-SA-3.0协议，转载请注明出处
作者: wsy_jim

M-071的甲板