MySQL基础
目录
SQL相关
-
SQL以分号结尾
-
MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
-
注释用法：
单行注释：-- 注释内容 或 # 注释内容 多行注释：/* 注释内容 */
DDL
库操作
查询所有数据库操作
show databases;查询当前数据库
select database();创建数据库
create database [if not exist] 库名 [default charset 默认字符集] [collate 排序规则]；删除数据库
drop database [if exist] 库名;切换数据库
use 库名;表操作
查询所有表操作
show tables;查看指定表结构
desc 表名;查询指定表的建表语句
show create table 表名;创建表操作
CREATE TABLE 表名(
字段1 字段1类型 [COMMENT '字段1注释']，
字段2 字段2类型 [COMMENT '字段2注释']，
。。。
字段n 字段n类型 [COMMENT '字段n注释']
)[COMMENT 表注释];添加字段
CREATE TABLE 表名(
字段1 字段1类型 [COMMENT '字段1注释']，
字段2 字段2类型 [COMMENT '字段2注释']，
。。。
字段n 字段n类型 [COMMENT '字段n注释']
)[COMMENT 表注释];修改字段类型
ALTER TABLE 表名 字段名 新字段类型;修改字段名和字段类型(single)
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 [COMMENT '注释'] [约束];删除字段
ALTER TABLE 表名 DROP 字段名;修改表名
ALTER TABLE 表名 RENAME TO 新表名;删除表
DROP TABLE [if exist] 表名;删除指定表，并以表名重新建造该表
TRUNCATE TABLE 表名;DML
向指定字段插入数据
（那么未指定的字段将会被赋予默认值。如果该字段被设置为NOT NULL，并且没有指定默认值，那么插入操作将会失败，因为MySQL不允许将NOT NULL字段插入空值。）
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 k1=v1, k2=v2, ... [WHERE 条件];删除数据
DELETE FROM 表名 [WHERE 条件];DQL
查询语句语法结构
SELECT
字段名
FROM
表名
WHERE
条件
GROUP BY
分组字段列表
HAVING
分组后的条件列表
ORDER BY
排序字段列表
LIMIT
分页参数基础查询
字段设置别名
SELECT 字段1 [AS 别名1]， 字段2 [AS 别名2] .. FROM 表名 [WHERE 条件];
SELECT 字段1 [别名1]， 字段2 [别名2] .. FROM 表名 [WHERE 条件];去除重复记录(重复的只显示1条)
SELECT DISTINCT 字段列表 FROM 表名;条件查询
常见运算符
<>或！= 不等于
BETWEEN A AND B 在[A,B]范围内
IN(...) 在IN列表中的值，查看是否匹配
NOT IN 不在指定的集合范围之内
ANY/SOME 子查询返回列表中，有任意一个满足即可
ALL 子查询返回列表的所有值都必须满足
LIKE占位符 模糊匹配（_匹配单个字符， %匹配任意个字符）
IS NULL 为NULL常见逻辑运算符
AND 或 && 和
OR 或 || 或
NOT 或 ！ 否聚合函数
语法
SELECT 聚合函数(字段) FROM 表名;常见聚合函数
count 统计数量
max 最大值
min 最小值
avg 平均值
sum 求和分组查询
语法
SELECT 字段 FROM 表名 [WHERE 条件] GROUP BY 分组字段列表 [HAVING 分组后过滤条件];WHERE和HAVING的区别
1.执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组 之后对结果进行过滤。
2.判断条件不同：where不能对聚合函数进行判断，而having可以。例：根据地址分类，获取平均工资大于5000的平均工资信息;
SELECT address, salary，avg(*) aver FROM table GROUP BY address HAVING aver > 5000;tips:
执行顺序: where > 聚合函数 > having 。
支持多字段分组, 具体语法为 : group by columnA,columnB
例1： 根据性别分组，查询男员工人数和女员工人数
SELECT gender，count(*) FROM tablename GROUP BY GENDER;例2：查询大于20岁的员工，根据性别分组，获取男员工人数和女员工人数
SELECT gender，count(*) FROM tablename WHERE age > 20 GROUP BY GENDER;例3：查询大于20岁的员工中，根据性别分组，获取薪水大于6000的男女员工人数
SELECT gender，count(*) FROM tablename WHERE age > 20 GROUP BY GENDER HAVING salary > 6000;例4：统计各个地址的男性员工人数和女性员工人数
SELECT address, gender, count(*) FROM tablename GROUP BY gender, workaddress;排序查询
排序方式
ASC: 升序 DESC:降序
语法
SELECT 字段 FROM 表名 [WHERE 条件] ORDER BY 字段1 排序方式， 字段2 排序方式， ..;分页查询
语法
SELECT 字段 FROM 表名 [WHERE 条件] LIMIT 起始索引，查询记录数;起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。
如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。
执行顺序
FROM 表名列表 -》 WHERE 条件 -》 GROUP BY 分组列表 -》 HAVING 分组后条件列表 -》 SELECT 字段列表 -》 ORDER BY 排序字段列表 -》 LIMIT 分页参数
DCL
MySQL中需要通过用户名@主机名的方式，来唯一标识一个用户。
主机名可以使用 % 通配。
管理用户
查询用户
SELECT * FROM mysql.user创建用户
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 '用户名'@'主机名';tips：
多个权限之间，使用逗号分隔 授权时， 数据库名和表名可以使用 * 进行通配，代表所有。
函数
字符串函数
CONCAT(S1,S2,...Sn) 字符串拼接，将S1，S2，... Sn拼接成一个字符串
LOWER(str) 将字符串str全部转为小写
UPPER(str) 将字符串str全部转为大写
LPAD(str,n,pad) 左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
RPAD(str,n,pad) 右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
TRIM(str) 去掉字符串头部和尾部的空格
SUBSTRING(str,start,len) 返回从字符串str从start位置起的len个长度的字符串LPAD例： 假设id为1，22，333 ，使用LPAD(id,5,'0')，那么得到的id将为00001，00022，00333
数值函数
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 exprtype) 返回一个日期/时间值加上一个时间间隔expr后的时间值
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默认值约束
分类
非空约束(NOT NULL), 唯一约束(UNIQUE), 主键约束(PRIMARY KEY), 默认约束(DEFAULT), 检查约束(CHECK), 外键约束(FOREIGN KEY), 自增(AUTO_INCREMENT)
建表语句例;
id自增且为主键
id int AUTO_INCREMENT PRIMARY KEY COMMENT 'ID唯一标识
name不为null且唯一
name varchar(10) NOT NULL UNIQUE COMMENT '姓名'
age在0-100之间
age int check (age > 0 && age <= 100) COMMENT '年龄'
status未指定默认为1
status char(1) default '1' COMMENT '状态'外键约束
建表时添加外键
CREATE TABLE 表名(
字段名 数据类型,
...
[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名)
);向已有表添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) ;删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;删除/更新行为
添加了外键之后，再删除父表数据时产生的约束行为，我们就称为删除/更新行为。
NOACTION：当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。 (与 RESTRICT 一致) 默认行为。
RESTRICT：当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。 (与 NO ACTION 一致) 默认行为。
CASCADE: 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录。
SET NULL: 当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表
中该外键值为null（这就要求该外键允许取null）。
SET DEFAULT: 父表有变更时，子表将外键列设置成一个默认的值 (Innodb不支持)语法
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;多表查询
多表查询中，需要消除无效的笛卡尔积的，只保留两张表关联部分的数据。
SELECT * FROM T1, T2 WHERE T1.id = T2.id;连接查询分类
-
内连接： 查询A, B 交集部分数据
-
外连接
-
左外连接：查询左表所有数据，以及两张表交集部分数据
-
右外连接：查询右表所有数据，以及两张表交集部分数据
-
自连接：当前表与自身的连接查询，也就是把一张表连接查询多次，自连接必须使用表别名
-
内连接
隐式内连接
SELECT 字段 FROM 表1， 表2 WHERE 条件;显式内连接 (注意条件限制改为ON)
SELECT 字段 FROM 表1，[INNER] JOIN 表2 ON 条件;外连接
左外连接（左表数据，及交集数据）
SELECT 字段 FROM 表1 LEFT JOIN 表2 ON 条件;右外连接（右表数据，及交集数据）
SELECT 字段 FROM 表1 RIGHT JOIN 表2 ON 条件;例：查询dept表的所有数据, 和对应的员工信息
select d.*, e.* from emp e right outer join dept d on e.dept_id = d.id;
select d.*, e.* from dept d left outer join emp e on e.dept_id = d.id;自连接
SELECT 字段 FROM 表1 别名1 JOIN 表1 别名2.. ON 条件;例：查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来
select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid =
b.id;联合查询
把多次查询的结果合并起来，形成一个新的查询结果集
SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;注意：
对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。 union all 会将全部的数据直接合并在一起，union 会多一次过滤，会对合并之后的数据去重。
子查询(嵌套查询)
SELECT/INSERT/UPDATE/DELETE * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 )事务
查看事务提交方式
SELECT @@autocommit ;设置事务提交方式
SET @@autocommit = 0 ;提交事务
COMMIT回滚事务
ROLLBACK开启事务
START TRANSACTION 或 BEGIN ;查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;设置事务隔离级别
SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }
981
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
