概述
数据库database(DB)
数据库管理系统database management system(DBMS)
SQL structured query language
SQL-DDL
数据库操作 | |
查询所有数据库 | SHOW DATABASE; |
查询当前数据库(当前处于哪个数据库) | SELECT DATABASE(); |
创建数据库 | CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则]; |
删除数据库 | DROP DATABASE [IF EXISTS] 数据库名; |
使用数据库(转换到某个数据库进行操作) | USE 数据库名; |
表操作 | |
查询当前数据库所有表 | SHOW TABLES; |
查询指定表的结构 | DECS 表名; |
查询指定表的建表语句 | SHOW CREATE TABLE 表名; |
创建表 字段:列名 字段类型:数据类型int/float/double…… | CREATE TABLE 表名( 字段1 字段1类型[COMMENT 字段1注释], 字段2 字段2类型[COMMENT 字段2注释], …… 字段n 字段n类型[COMMENT 字段n注释] )[COMMENT 表注释]; |
添加字段 | ALTER TABLE 表名 ADD 字段名 类型 [COMMENT 注释] [约束]; |
删除字段 | ALTER TABLE 表名 DROP 字段名; |
修改数据类型 | ALTER TABLE 表名 MODIFY 字段名 新数据类型; |
修改字段名和字段类型 | ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 [COMMENT 注释] [约束]; |
修改表名 | ALTER TABLE 表名 RENAME TO 新表名; |
删除表(表中所有数据也被删除) | DROP TABLE [IF EXISTS] 表名; |
删除指定表并重新创建该表 | TRUNCATE TABLE 表名; |
SQL-DML
给指定字段添加数据 | INSERT INTO 表名(字段名1, 字段名2…) VALUES (值1, 值2…) |
给全部字段添加数据 | INSERT INTO表名 VALUES (值1, 值2…) |
批量添加数据 | INSERT INTO 表名(字段名1, 字段名2…) VALUES (值1, 值2…),(值1, 值2…),…… INSERT INTO 表名 VALUES (值1, 值2…),(值1, 值2…),…… |
修改数据 | UPDATE 表名 SET 字段名1=值1,字段名2=值2……[WHERE]; |
删除数据(删除整行) | DELETE FREOM 表名 [WHERE]; |
SQL-DQL
基本查询 | 查询多个字段 | SELECT 字段1,字段2…… FROM 表名; SELECT * FROM 表名; |
设置别名 | SELECT 字段1[AS 别名1],字段2[AS 别名2]…… FROM 表名; | |
去除重复记录 | SELECT DISTINCT 字段 FROM 表名; | |
条件查询 | 比较运算符 条件运算符 | SELECT字段 FROM 表名 WHERE 条件列表; LIKE 模糊匹配 _匹配单个字符 %匹配多个字符 |
分组查询 | 聚合函数: count min max avg sum | SELECT字段 FROM 表名 [WHERE 条件列表] GROUP BY 分组字段名 [HAVING 分组后过滤条件]; e.g. 查询年龄小于45岁的员工根据工作地点分组,获得人数大于等于3的工作地点 select workadd.count(*) from where age<45 group by workadd having count(*)>=3 |
排序查询 | ASC升序 DESC降序 | SELECT字段 FROM 表名 ORDER BY字段1 排序方式1,字段2 排序方式2…… 多字段排序时,在第一个字段值相同时才会根据第二个字段排序 |
分页查询 | 起始索引从0开始 | SELECT字段 FROM 表名 LIMIT 起始索引,查询记录数; |
编写顺序:SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT 执行顺序:FROM, WHERE, GROUP BY/HAVING, SELECT, ORDER BY, LIMIT |
聚合函数:将一列数据作为一个整体,进行纵向计算
SQL-DCL
用户管理 | 查询用户 | USE mysql; SELECT * FROM user; |
创建用户 | CREATE USER ‘用户名’@’主机名’ INDENTIFIED BY ‘密码’; | |
修改用户密码 | ALTER USER ‘用户名’@’主机名’ INDENTIFIED WITH mysql_native_password BY ‘新密码’; | |
删除用户 | DROP USER ‘用户名’@’主机名’;(主机名为%时为任意主机访问) | |
权限控制 | 查询权限 | SHOW GRANTS FOR ‘用户名’@’主机名’; |
授予权限 | GRANT 权限列表 ON 数据库名.表名 TO ‘用户名’@’主机名’; | |
撤销权限 | REVOKE权限列表 ON 数据库名.表名 FROM ‘用户名’@’主机名’; | |
ALL所有权限SELECT查询数据INSERT插入数据UPDATE修改数据DELETE删除数据ALTER修改表DROP删除数据库或表CREATE创建数据库或表 |
函数
字符串函数 | 数值函数 | ||
CONCAT(S1,S2,…) | 字符串拼接 | CEIL(X)/FLOOR(X) | 向上/下取整 |
LOWER(str)/ UPPER(str) | 转成小写/大写 | MOD(X,Y) | 返回X/Y的模 |
LPAD(str,n,pad)/ RPAD(str,n,pad) | 左/右填充,用字符串pad填充到n个字符 | RAND() | 返回0~1的随机数 |
TRIM(str) | 去掉头部和尾部空格 | ROUND(X,Y) | 返回参数X四舍五入保留Y位 |
SUBSTRING(str,start,len) | 返回从start位置起的len个长度的字符串 | ||
日期函数 | 流程函数 | ||
CURDATE()/CURTIME()/NOW() | 返回当前日期/时间/日期和时间 | IF(value,t,f) | |
YEAR(date)/MONTH(date)/DAY(date) | 获取指定date的年份/月份/日期 | IFNULL(value1,value2) | 非空value1 |
DATE_ADD(date,INTERVAL expr type) | 返回一个日期加上时间间隔expr后的时间 | CASE WHEN [val1] THEN [res1]… ELSE [default] END | |
DATEDIFF(date1,date2) | 返回date1-date2之间的天数 | CASE [expr] WHEN [val1] THEN [res1]… ELSE [default] END |
约束
非空约束 | 限制不能为null | NOT NULL | 默认约束 | 未指定该字段的值则采用默认值 | DEFAULT |
唯一约束 | 保证该字段数据唯一不重复 | UNIQUE | 检查约束 | 保证字段值满足某一个条件 | CHECK |
主键约束 | 主键是一行数据的唯一标识,非空唯一 | PRIMARY KEY | 外键约束 | 让两张表的数据之间建立连接 | FOREIGN KEY |
外键约束
添加外键 | CREATE TABLE 表名( 字段 字段类型[COMMENT 字段1注释], …… [CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) )[COMMENT 表注释]; |
添加外键 | ALTER TABLE 从表 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) |
删除外键 | ALTER TABLE 从表 DROP FOREIGN KEY 外键名称 |
设置修改主表时从表自动修改 | ALTER TABLE 从表 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) ON UPDATE CASCADE/SET NULL ON DELETE CASCADE/SET NULL |
多表查询
内连接 查询交集 | 隐式内连接 | SELECT 字段列表 FROM 表1,表2 WHERE 条件; |
显示内连接 | SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件; | |
外连接 | 左外连接 | SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件; |
右外连接 | SELECT 字段列表 FROM 表1 RIGHT[OUTER] JOIN 表2 ON 条件; | |
自连接(可内可外) | SELECT 字段列表 FROM 表1 别名1 JOIN表1 别名2 ON 条件; | |
联合查询 | UNION ALL | SELECT 字段列表 FROM 表1 UNION [ALL] SELECT 字段列表 FROM 表2 |
UNION 去重 | ||
子查询 | 标量子查询 | SELECT 字段列表 FROM 表名 |
列子查询 | ||
行子查询 | ||
表子查询 | ||