为什么要学MySQL
SQL（Structured Query Language） 结构化查询语言，是关系数据库的标准语言
而MySQL是面向广大人群最热门的SQL语言之一，即容易上手又功能强大，集成了数据定义、数据操作、数据查询和数据控制四大功能
废话不多说，让我们直接开始学习MySQL的基本操作吧
常用数据类型
数值型
| 关键字 | 含义 |
|---|---|
| TINYINT | 小整数值 |
| INT | 大整数值 |
| BIGINT | 极大整数值 |
| FLOAT | 单精度浮点数值 |
| DOUBLE | 双精度浮点数值 |
日期和时间类型
| 关键字 | 格式 | 含义 |
|---|---|---|
| DATE | YYYY-MM-DD | 日期值 |
| TIME | HH:MM:SS | 时间值 |
| YEAR | YYYY | 年份值 |
| DATETIME | YYYY-MM-DD hh:mm:ss | 日期加时间值 |
| TIMESTAMP | YYYY-MM-DD hh:mm:ss | 日期加时间值（时间戳） |
字符串类型
| 关键字 | 含义 |
|---|---|
| CHAR(n) | 长度为n的定长字符串 |
| VARCHAR(n) | 最长为n的可变长字符串 |
以上这些常用数据类型已可以满足大多需求
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 表名 RENAME TO 新表名 ;
删除字段
ALTER TABLE 表名 DROP 字段名 ;
删除表
直接删除表
DROP TABLE [ IF EXISTS ] 表名 ;
删除指定表，并重新创建该表
TRUNCATE TABLE 表名 ;
DML----数据操作语言
插入
给指定字段添加数据
INSERT INTO 表名(字段名1，字段名2，...) VALUES(值1，值2，...) ;
给全部字段添加数据
INSERT INTO 表名 VALUES(值1，值2，...) ;
批量添加数据
INSERT INTO 表名(字段名1，字段名2，...) VALUES(值1，值2，...) , (值1，值2，...) , (值1，值2，...) ;
INSERT INTO 表名 VALUES(值1，值2，...) , (值1，值2，...) , (值1，值2，...) ;
修改
UPDATE 表名 SET 字段名1=值1，字段名2=值2，...[WHERE 条件];
如果没有条件将会修改整张表的记录
删除
DELETE FROM 表名 [WHERE 条件];
DQL----数据查询语言
这是SQL语句中的重点，此处先讲基础语句
查询总的语法
SELECT
字段列表
FROM
表名列表
WHERE
条件列表
count、max、min、avg、sum
聚合函数
GROUP BY
分组字段列表
HAVING
分组后条件列表
ORDER BY
排序字段列表
LIMIT
分页参数
基本查询
查询多个字段
SELECT 字段1，字段2，字段3... FROM 表名;
SELECT * FROM 表名;
“*”表示查询全部，不过尽量不要写"*"会影响效率，而且不直观
设置别名
SELECT 字段1 [AS 别名1] ，字段2 [别名2] ... FROM 表名;
AS 可以省略
去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;
条件查询
语法
SELECT 字段列表 FROM 表名 WHERE 条件列表;
条件
| BETWEEN…AND… | 在某个范围之内（包含最小值和最大值） |
|---|---|
| IN(…) | 在in之后的列表中的值，多选一，满足其中之一即可** |
| LIKE “占位符” | 模糊匹配( _ 匹配单个字符，% 匹配任意个字符) |
| IS NULL | 是NULL |
| AND 或 && | 并且(多个条件同时成立) |
| OR 或 || | 或者(多个条件任意一个成立) |
| NOT 或 ! | 非，不是 |
聚合函数
都是作用于表中的某一列，进行纵向计算
常见聚合函数
ps：所有NULL值不参与聚合函数的计算
| count | 统计数量 |
|---|---|
| max | 最大值 |
| min | 最小值 |
| avg | 平均值 |
| sum | 求和 |
语法
SELECT 聚合函数（字段列表）FROM 表名;
分组查询
语法
SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];
WHERE 和 HAVING 的区别
执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤
判断条件不同：where不能对聚合函数进行判断，而having可以
注意
执行顺序：where > 聚合函数 > having
分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义
排序查询
语法
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1，字段2 排序方式2;
如果是多字段排序，当第一个字段相同时，才会根据第二个字段进行排序
排序方式
ASC：升序（默认值）
DESC：降序
分页查询
语法
SELECT 字段列表 FROM 表名 LIMIT 起始索引，查询记录数；
注意
-
起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。
-
分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT
-
如果查询的是第一页数据，起始索引可以省略，直接简写为LIMIT 每页展示记录数
编写顺序和执行顺序
| 编写顺序 | 执行顺序 | ||
|---|---|---|---|
| SELECT | 字段列表 | FROM | 表名列表 |
| FROM | 表名列表 | WHERE | 条件列表 |
| WHERE | 条件列表 | GROUP BY | 分组字段列表 |
| GROUP BY | 分组字段列表 | HAVING | 分组后条件列表 |
| HAVING | 分组后条件列表 | SELECT | 字段列表 |
| ORDER BY | 排序字段列表 | ORDER BY | 排序字段列表 |
| LIMIT | 分页参数 | LIMIT | 分页参数 |
执行的时候是先确定在那张表上进行查找，之后确定满足条件的行，然后选择列，再进行排序和分页
DCL----数据控制语言
这一部分不重要，很少用
查询用户
USE mysql;
SELECT * FROM user;
创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
删除用户
DROP USER '用户名'@'主机名';
主机名可以使用%通配
权限
| 关键字 | 对应的权限 |
|---|---|
| ALL，ALL PRIVILEGES | 所有权限 |
| SELECT | 查询数据 |
| INSERT | 插入数据 |
| UPDATE | 修改数据 |
| DELETE | 删除数据 |
| ALTER | 修改表 |
| DROP | 删除数据库/表/视图 |
| CREATE | 创建数据库/表 |
权限控制
查询权限
SHOW GRANTS FOR '用户名'@'主机名';
授予权限
GRANT;权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
数据库名和表名都可以使用"*"进行通配，代表所有
我们了解完基础的操作之后，但是单靠这些基础还是远远不够的，接下来让我们来学习一下提升的操作
函数
字符串函数
| 关键字 | 作用 |
|---|---|
| CONCAT(S1,S2,…,Sn) | 字符串拼接 |
| LOWER(str) | 转化为小写 |
| UPPER(str) | 转化为大写 |
| LPAD(str,n,pad) | 左填充，用pad对str左边进行填充，直到长度为n |
| RPAD(str,n,pad) | 右填充，用pad对str右边进行填充，直到长度为n |
| TRIM(str) | 去掉字符串头部和尾部的空格 |
| SUBSTRING(str,start,len) | 返回从字符串str从start位置起的len个长度的字符串，它的索引从1开始 |
数值函数
| 关键字 | 作用 |
|---|---|
| CEIL(x) | 向上取整 |
| FLOOR(x) | 向下取整 |
| MOD(x,y) | 返回x/y的模 |
| RAND() | 返回0~1内随机数 |
| ROUND(x,y) | 求参数x的四舍五入的值，保留y位小数 |
日期函数
| 关键词 | 作用 |
|---|---|
| CURDATE() | 返回当前日期 |
| CURTIME() | 返回当前时间 |
| NOW() | 返回当前日期和时间 |
| YEAR(date) | 获取指定date的年份 |
| MONTH(date) | 获取指定date的月份 |
| DAY(date) | 获取指定date的日期 |
| DATE_ADD(date,INTERVAL expr type) | 返回date 加上 expr 个 type 后的时间 |
| expr为数量 type为单位，如DAY,MONTH,YEAR | |
| DATEDIFF(date1,date2) | 返回起始时间date1和结束时间date2之间的天数 |
流程函数
| 函数 | 作用 |
|---|---|
| IF(value,t,f) | 如果value为true，则返回t，否则返回f |
| IFNULL(value1,value2) | 如果value1不为空，返回value1，否则返回value2 |
| CASE WHEN [val1] THEN [res1] … ELSE [default] END | 如果val1为true，则返回res1，… ，否则返回default默认值 |
| CASE [expr] WHEN [val1] THEN [res1] … ELSE [default] END | 如果expr的值等于val1，返回res1，…，否则返回default默认值 |
约束
非空约束：限制该字段的数据不能为null
NOT NULL
唯一约束：保证该字段的所有数据都是唯一、不重复的
UNIQUE
主键约束：主键是一行数据的唯一标识，要求非空且唯一
PRIMARY KEY
默认约束：保存数据时，如果未指定该字段的值，则采用默认值
DEFAULT
检查约束：保证字段值满足某一个条件
CHECK
自动增长：从1开始自增
AUTO_INCREMENT
外键约束：用来让两张表的数据之间建立连接，保证数据的一致性和完整性
FOREIGN KEY
约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束
外键
添加外键
CREATE TABLE 表名(
字段名 数据类型，
...
[CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
);
# 也可以在建表函数外添加
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名);
删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
删除/更新行为
NO ACTION
当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新
RESTRICT
与NO ACTION 一致
CASCADE
当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录
SET NULL
当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null
ps：这就要求该外键允许取null
SET DEFAULT
父表有变更时，子表将外键列设置成一个默认的值（innodb不支持）
语法
ALTER TABLE 表名 ADD CONSTRAINT 外键名称
FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名)
ON UPDATE [行为] ON DELETE [行为];
多表查询 (重点)
多表关系
一对多（多对一）
在多的一方建立外键，指向一的一方的主键
多对多
建立第三张中间表，至少包含两个外键，分别关联两方主键
一对一
多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（UNIQUE）
内连接
内连接查询的是两张表交集的部分
隐式内连接
SELECT 字段列表 FROM 表1，表2 WHERE 条件 ...;
显式内连接
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 条件 ...;
外连接
通常用左外连接，因为把右外连接换个位置，就是左外连接
左外连接
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件...;
相当于查询 表1 的所有数据，也包含 表1 和 表2 交集部分的数据
右外连接
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件...;
相当于查询 表2 的所有数据，也包含 表1 和 表2 交集部分的数据
自连接
自连接查询，可以是内连接查询，也可以是外连接查询
自连接的内连接
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件;
自连接的外连接
SELECT 字段列表 FROM 表A 别名A LEFT JOIN 表B 别名B ON 条件;
联合查询
UNION , UNION ALL
把多次查询的结果合并起来，形成一个新的查询结果集
注意
- 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致
- UNION ALL 会将全部的数据直接合并在一起，UNION 会对合并之后的数据去重
语法
SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ...;
子查询
SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询
语法
SELECT * FROM t1 WHERE column1 = （SELECT column1 FROM t2）;
子查询外部的语句可以是 INSERT / UPDATE / DELETE /SELECT 的任何一个
根据子查询结果不同，分为：
| 类别 | 子查询结果 |
|---|---|
| 标量子查询 | 单个值 |
| 列子查询 | 一列 |
| 行子查询 | 一行 |
| 表子查询 | 多行多列 |
根据子查询位置，分为：WHERE 之后、FROM 之后、SELECT 之后
标量子查询
子查询返回的结果是单个值（数字、字符串、日期等）
常用的操作符：
| 操作符 | 含义 |
|---|---|
| = | 等于 |
| > | 大于 |
| >= | 大于等于 |
| < | 小于 |
| <= | 小于等于 |
| <>或!= | 不等于 |
例子：
select * from emp e where e.enterdate > (select a.enterdate from emp a where a.name='XXX');
查询emp表中比 “XXX” 入职时间晚的职员信息
列子查询
子查询返回的结果是一列（可以是多行）
常用的操作符：
| 操作符 | 含义 |
|---|---|
| IN | 在指定的集合范围之内，多选一 |
| NOT IN | 不在指定的集合范围之内 |
| ANY | 子查询返回列表中，有任意一个满足即可 |
| SOME | 与ANY等同，使用SOME的地方都可以使用ANY |
| ALL | 子查询返回列表的所有值都必须满足 |
例子：
select * from emp e where e.dept_id IN
(select d.id from dept d where d.name in('总经办','销售部'));
在emp表中找出 总经办 和 销售部 的职员信息
select * from emp where salary > all
(select salary from emp where dept_id=(select id from dept where name='研发部'));
查询emp表中工资 比任何一个研发部职员工资高 的职员信息
行子查询
子查询返回的结果是一行（可以是多列）
常用的操作符：
| 操作符 | 含义 |
|---|---|
| = | 等于 |
| <>或!= | 不等于 |
| IN | 在指定的集合范围之内，多选一 |
| NOT IN | 不在指定的集合范围之内 |
例子：
select * from emp where (salary,managerid) = (select salary,managerid from emp where name='XXX');
查询emp表中与 “XXX” 的工资以及领导都一致的职工信息
表子查询
子查询返回的结果是多行多列
常用的操作符：
| 操作符 | 含义 |
|---|---|
| IN | 在指定的集合范围之内，多选一 |
例子：
select * from emp where (salary,job) in (select salary,job from emp where name='XXX' or name='AAA');
查询emp表中与 “XXX” 或者 “AAA” 工资以及职位都一致的职工信息
事务
如果我们要去银行转账给张三，刚把我们卡里的钱扣了，张三还没收到钱，数据库就出现了故障，那么扣掉的这笔钱该何去何从呢？
有没有一种方式能保证 在正常情况下，正常运行；在出错的时候，能够回退呢？
所以我们引入了事务
- 事务是一组操作的集合，是不可分割的工作单位
- 事务会把所有的操作作为一个整体，一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败
方式一
查询事务提交方式
SELECT @@autocommit;
设置为手动提交
SET @@autocommit = 0;
提交事务
COMMIT ；
回滚事务
ROLLBACK ；
方式二
开启事务 开启了事务之后，就代表需要手动提交事务
START TRANSACTION; 或 BEGIN;
提交事务
COMMIT;
回滚事务
ROLLBACK;
事务的四大特性（ACID）
| 性质 | 含义 |
|---|---|
| 原子性（Atomicity） | 事务是不可分割的最小操作单元，要么全部成功，要么全部失败 |
| 一致性（Consistency） | 事务完成时，必须使所有的数据都保存一致状态 |
| 隔离性（Isolation） | 数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行 |
| 持久性（Durability） | 事务一旦提交或回滚，它对数据库中的数据的改变就是永久的 |
并发事务问题
| 问题 | 含义 |
|---|---|
| 脏读 | 一个事务读到另外一个事务还没有提交的数据 |
| 不可重复读 | 一个事务先后读取同一个记录，但两次读取的数据不同，称之为不可重复读 |
| 幻读 | 一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在,好像出现了“幻影”。 |
事务隔离级别
| 问题 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| Read uncommitted(读未提交) | √ | √ | √ |
| Read committed(读已提交) | × | √ | √ |
| Repeatable Read(默认)(可重复读) | × | × | √ |
| Serializable(串行化) | × | × | × |
从上往下，级别逐渐升高
事务隔离级别越高，数据越安全，但是性能越低
查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;
设置事务隔离级别
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED}
{READ COMMITTED}
{REPEATABLE READ}
{SERIALIZABLE};
以上就是这篇文章的全部内容，感谢大家观看，之后还会更新计算机学习的笔记，希望能帮到大家！！！
3398
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
