目录
sql/Mysql 新建部门与员工表.sql · 汪少棠/my-document - Gitee.com
SQL 语句分类：
DDL : 数据定义语句 (create alter drop) DML : 数据操作语句 (insert update delete) DQL : 数据查询语句(select) DCL : 数据控制语句(grant revoke commit rollback) |
DDL 数据库相关
| 显示 mysql 中所有数据库 | SHOW DATABASES ; |
| 显示数据库 db_name 的创建语句 | SHOW CREATE DATABASE db_name ; |
| 创建数据库 | CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [, create_specification] ...] create_specification 可选值有: [DEFAULT] CHARACTER SET charset_name ：指定数据库采用的字符集，如 utf8、utf8mb4 等 [DEFAULT] COLLATE collation_name ：指定数据库字符集的排序规则，如 utf8_general_ci、utf8_bin 等 |
| 创建数据库 db_wmx，数据库使用 utf-8 编码,utf8_bin 排序规则 | CREATE DATABASE if NOT EXISTS db_wmx CHARACTER SET utf8 COLLATE utf8_bin; |
| 删除数据库 db_name | DROP DATABASE [IF EXISTS] db_name ; |
DDL 数据表相关
| 查询当前数据库下所有的表名 | show TABLES; |
| 显示 person 创建表语句 | show CREATE TABLE person; |
| 删除表 | DROP TABLE table_name; DROP TABLE IF EXISTS person2; |
| 创建表 | CREATE TABLE table_name( 列名1 数据类型, 列名2 数据类型, .. 列名n 数据类型 ) character set 字符集 collate 校对规则 engine 存储引擎方式 |
| 1）字符集不指定时，默认与数据库保持一致，如 utf8、utf8mb4 等 2）排序规则不指定时，默认与数据库保持一致，如 utf8_general_ci、utf8_bin 等 3）存储引擎方式不指定时，默认与数据库保存一致，如 InnoDB 或者 MyISAM 等。 |
|
| CREATE TABLE person2 LIKE person ; -- 复制表结构建表,不含数据，包括主外键，索引等 CREATE TABLE person3 AS SELECT * FROM person;-- 复制表结构和数据建表，不包括主外键，索引等 |
|
| 修改表 | 使用 ALTER TABLE 语句追加, 修改, 或删除列的语法. |
ALTER TABLE tablename ADD (column datatype [DEFAULT expr] [, column datatype]...); -- 追加列 ALTER TABLE tablename MODIFY column datatype [DEFAULT expr]; -- 修改列 ALTER TABLE tablename DROP (column); -- 删除列 rename table 表名 to 新表名; -- 修改表的名称 alter table student character set utf8; -- 修改表的字符集 |
|
-- 创建 book 表，结尾的字符集、排序规则、存储引擎通常不写与数据库保存默认即可
CREATE TABLE if NOT EXISTS book (
id int(11) NOT NULL AUTO_INCREMENT,
title varchar(128) NOT NULL,
info varchar(128) DEFAULT NULL,
code varchar(64) DEFAULT NULL,
PRIMARY KEY (id)
) CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB ;
-- 新增 code 列：不允许为 null，默认值为 -，指定注释，如果表中已经有数据，则都会赋值为 -.
-- FIRST：表示新增的列位于第一列，否则默认新增的列位于最后一列
ALTER TABLE book ADD code VARCHAR(256) NOT NULL DEFAULT '-' COMMENT '编码' FIRST;
-- AFTER xxx：表示新增的列位于 xxx 列的后面, 否则默认新增的列位于最后一列
ALTER TABLE book ADD code2 VARCHAR(256) NOT NULL DEFAULT '-' COMMENT '二编码' AFTER title;
-- 修改 code2 列类型：同样需要指定数据类型，约束，注释等信息，可以使用 AFTER 关键字移动列的顺序.
ALTER TABLE book MODIFY code2 VARCHAR(512) NOT NULL DEFAULT '-' COMMENT '二编码' AFTER type_id;
-- 修改列名称: code 改为 codes，同样需要指定列类型、约束、描述等信息.
ALTER TABLE book CHANGE COLUMN code codes VARCHAR(256) NOT NULL;
ALTER TABLE book DROP codes; -- 删除 book 表的 codes 列
RENAME TABLE books to book; -- 修改 book 表名为 books
数据库 DML 语句
1、使用 INSERT 语句向表中插入数据：INSERT INTO tablename [(column [, column...])] VALUES (value [, value...]);
1）values 后插入的数据必须与字段的数据类型相同，位置必须与被加入的列的排列位置相对对应。 2）数据的大小应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。 3）字符和日期型数据应包含在单引号中。 4）想要插入空值时，表名后可以不指定列，或指定列并设置列的值为 null. 5）表名后面不指定列时，默认为所有列插入数据. |
INSERT INTO book(id,title,summary,code) VALUES(NULL,'西游记','经典',88.00); -- 插入数据
-- 单条 sql 插入多组数据
INSERT INTO dept(dname, loc) VALUES ('Java研发中心', '长沙麓谷'),('大数据开发中心','深圳科技园');
INSERT INTO book VALUES(NULL,'水浒传','经典名著',98.00); -- 插入全部数据时，列名可以省略，values 中按顺序传入2、使用 update 语句修改表中数据：UPDATE tbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition]
UPDATE 语法可以用新值更新原有表行中的各列。 SET 子句指示要修改哪些列和要给予哪些值。 WHERE 子句指定应更新哪些行，如没有 WHERE 子句，则更新所有的行。 |
UPDATE book set summary = '四大名著' WHERE title = '西游记'; -- 修改 西游记的描述为 四大名著
3、使用 delete 语句删除表中数据：delete from tbl_name [WHERE where_definition]
如果不使用 where 子句，将删除表中所有数据。 使用 delete 语句仅删除记录，不删除表本身。如要删除表，使用 drop table tbl_name 语句。 同 insert 和 update 一样，从一个表中删除记录将引起其它表的参照完整性问题，在修改数据库数据时，头脑中应该始终不要忘记这个潜在的问题。 删除表中数据也可使用 TRUNCATE TABLE tbl_name 语句，delete 删除的数据可以恢复，truncate 虽然效率更高，但是删除的数据不可恢复，慎用。 |
DELETE FROM book WHERE title='西游记' ; -- 删除西游记
DELETE FROM book ; -- 删除 book 表所有数据
TRUNCATE book; -- 删除 book 表所有数据
花样 insert into
1、为了避免往数据库中插入重复数据，最常见的方式就是为字段设置唯一索引/约束，当插入重复数据时，抛出错误信息。
2、mysql 也能实现类似持久化框架一样功能，比如：如果数据不存在，则新增，已经存在时，则更新，或者先删除旧数据，然后插入新数据等等类似的功能。
3、mysql 判断数据是否重复的依据是：主键或者唯一约束，当插入的新数据的主键已经存在，或者新数据违反唯一约束时，都判定为重复数据，所以前提条件是插入的数据字段设置了主键或唯一索引。
3、演示表结构如下，pId 主键自增，pName 姓名，id_card 身份证(带唯一索引约束)，salary 薪水，summary 描述。
数据准备：sql/mysql/新建 person 用户表.sql · 汪少棠/material - Gitee.com
| insert ignore into | 插入数据时，如果数据存在，则忽略此次插入，当插入新数据时，MySQL 数据库会首先检索已有数据，如果存在，则忽略本次插入，如果不存在，则正常插入数据。 |
| on duplicate key update | 插入数据时，如果数据存在，则执行更新操作，如果不存在，则直接插入，注意更新的数据是 on duplicate key update 关键字后面的字段。 |
| replace into | 插入数据时，如果数据存在，则删除后再插入，如果不存在，则直接插入。 |
INSERT ignore INTO person(pId,pName,id_card,salary,summary) VALUES (13,'张无忌', '110101199003077467', '9999.99', '武学奇才');
-- 如果存在 pId=13 的数据，则本次不会插入，如果存在 id_card=110101199003077467 的数据，本次也不会插入
INSERT ignore INTO person(pName,id_card,salary,summary) VALUES ('张无忌', '110101199003077467', '9999.99', '武学奇才');
-- 如果存在 id_card=110101199003077467 的数据，本次不会插入
INSERT INTO person(pName,id_card,salary,summary) VALUES ('张无忌', '110101199003077417', '888.99', '武学大才') on duplicate key update salary=3333,summary='武学巅峰';
-- 如果存在 id_card=110101199003077467 的数据，且 on duplicate key update 后面的字段值与旧数据不相同时，则更新 on duplicate key update 后面的字段值
-- 如果不存在 id_card=110101199003077467 的数据，则直接新增，此时 on duplicate key update 后面的值不会处理
REPLACE INTO person(pName,id_card,salary,summary) VALUES ('张无忌', '110101199003077417', '12323.99', '武学大才');
-- 如果存在 id_card=110101199003077467 的数据，则先删除旧数据，然后插入新数据
数据库 DQL 基本查询
数据准备：sql/mysql/新建书籍与分类表.sql · 汪少棠/material - Gitee.com
1、基本 select 语句格式：
SELECT
[ DISTINCT ] *|{ column1 | expression1, column2 | expression2.column3 | expression3..}
FROM 表名
WHERE 表达式
GROUP BY 列1,列2...
HAVING 表达式
ORDER BY 列1 ASC | DESC, 列2 ASC | DESC
LIMTT offset, number;
-- GROUP BY xxx HAVING 分组通常与聚合函数一起使用：max(最大值)、min(最小值)、sum(求和)、avg(平均值)、count(总行数)
-- limit 用于限制结果条数(分页)，offset 表示偏移量/起始行位置，从0开始，不写时默认为0，number 表示取的条数.
-- where 条件针对的是表中的列进行查询，having 条件是针对查询结果进行再次过滤
-- Order by 指定排序的列，排序的列即可是表中的列名，也可以是 as 设置的别名，Asc 升序（默认）、Desc 降序。2、可使用 as 关键字设置列或者表的别名：SELECT columnname as 别名 from 表名 as 别名;
3、表达式中常用的运算符有：
| 运算符 | 描述 |
|---|---|
| <, <= , >, >=, =, != | 比较运算符 |
| BETWEEN ...AND... | 显示在某一区间的值。 限制查询数据范围时包括了边界值，not between 不包括边界值 |
| IN(set)，not in(set) | 显示在in列表中的值，例：in(100,200)，括号中的个数不建议超过 1000 |
| LIKE '张pattern' | 模糊查询.Like语句中，% 代表零个或多个任意字符，_ 代表一个字符 like '%xxx%' 表示包含 xxx，like 'xxx%' 表示以 xxx 开头的，like '%xxx' 表示以 xxx 结尾的。 |
| IS NULL，is not null | 判断是否为null，或者不为 null. |
| and、or、not | 逻辑运算符，与 或 非，例：where not(salary>100); |
SELECT * from book; -- 查询整个表的数据
SELECT title,price,publish from book; -- 查询指定列
SELECT DISTINCT title,price,publish from book; -- 查询指定列，同时去掉重复的结果
-- as 关键字可以省略; 别名有空格时，必须使用引号包裹;
SELECT t.title as 标题,price 价格,publish '发布 时间' from book as t;
SELECT * from book WHERE price > 50; -- 价格大于 50 的书籍
SELECT * from book WHERE price BETWEEN 40 AND 80; -- 价格在 [40,80] 之间的书籍
SELECT * from book WHERE title in('西游记','三国演义');
SELECT * from book WHERE title not in('西游记','三国演义');
SELECT * from book WHERE info like '%金庸%' and price < 100;
SELECT * from book WHERE type_id is NULL; -- 书籍类型为 null 的数据
SELECT * from book WHERE info is NOT NULL; -- 描述不为 null 的数据
SELECT * from book ORDER BY price DESC;-- 按价格倒序
SELECT * from book ORDER BY publish desc, price ASC LIMIT 5;-- 按发布时间先倒序，再按价格升序，取 5 条数据
-- LIMIT 用于分页，对前面的结果只取其中一部分
SELECT * from book WHERE price > 30 ORDER BY publish desc LIMIT 2,5;
-- 查询每个分类下书籍的个数，以及最贵的价格
SELECT type_id,max(price),count(1) from book t GROUP BY t.type_id;
-- 查询每个分类下面平均售价超过 50 的分类 ID 以及评价售价信息
SELECT type_id, avg(price) avg_price FROM book WHERE type_id IS NOT NULL GROUP BY type_id HAVING avg_price > 50;数据库 DQL 复合查询
数据准备：sql/mysql/新建书籍与分类表.sql · 汪少棠/material - Gitee.com
1、有时在实际应用中，为了合并多个 select 语句的结果，可以使用集合操作符号 union , union all. 2、被合并的两个结果集的列个数与列类型必须一 一对应，列名称可以不对应，此时以将第一个结果集的列名为准. |
|
| union | 该操作符用于取得两个结果集的并集，并对结果自动去重. |
| union all | 该操作赋与 union 相似，但是不会对结果自动去重，而且不会排序. |
| 内连接 | 实际上就是利用 where 子句对两张表形成的笛卡尔积进行筛选，这也是在开发过程中用的最多的连接查询。 内连接的语法：select 字段 表1 inner join 表2 on 连接条件 where .... |
| 左外连接 | 如果左侧表中的数据需要完整显示，就是左外连接；没有匹配的记录字段的值为 null. |
| 右外连接 | 如果右侧表中的数据需要完整显示，就是右外连接；没有匹配的记录字段的值为 null. |
-- 求并集，被合并的两个结果集的列个数与列类型必须一 一对应，列名称可以不对应，此时以将第一个结果集的列名为准.
SELECT id,title,price,publish,info,type_id FROM book WHERE price <= 40
UNION
SELECT id as id2,title,price as price2,publish,info,type_id FROM book WHERE price >= 80;
SELECT * FROM book WHERE price > 60 UNION ALL SELECT * FROM book WHERE type_id = 1;
-- 内连接：INNER JOIN 关键字可以省略
SELECT t1.*,t2.name FROM book t1 INNER JOIN book_type t2 ON t1.type_id = t2.id WHERE t1.price > 50;
-- 等价于
SELECT t1.*,t2.name FROM book t1, book_type t2 WHERE t1.type_id = t2.id AND t1.price > 50;
-- 左外连接查询所有书籍及其分类，没有指定分类的书籍的记录的 type_id、name 会为 NULL
SELECT t1.*,t2.name FROM book t1 LEFT JOIN book_type t2 ON t1.type_id = t2.id;
-- 右外连接查询所有书籍分类，以及分类下的书籍
SELECT t2.name,t1.* FROM book t1 RIGHT JOIN book_type t2 ON t1.type_id = t2.id;
-- 多张表的左外连接格式：select 字段 表1 left join 表2 on 连接条件1 left join 表3 on 连接条件2 where 筛选条件MySQL 常用命令
| 命令 | 描述 |
SELECT DATABASE(); |
-- 查看当前数据库名称 |
| SHOW CREATE DATABASE wangmx | -- 查看数据库创建信息（wangmx是数据库名称） |
| SELECT now(), user(), version(); | -- 显示当前时间、用户名、数据库版本 |
| SHOW TABLES FROM wangmx; | -- 查看数据库下的所有表（wangmx是数据库名称） |
| desc person; | -- 查看表结构，如字段名称与类型等（person 是表名） |
where、from、exists 子查询
| where 型子查询 | 把子查询的结果作为外层查询的比较条件。 |
| from 型子查询 | 把子查询的结果当成临时表，供外层 sq1再 次查询 |
| exists 型子查询 | 把外层的查询结果代入到子查询看否成立。子查询有值时返回 true，否则返回 false。 |
-- where 子查询：查询每个分类下价格最贵的书籍
SELECT * from book t where (t.type_id,t.price) in (SELECT type_id,MAX(price) from book GROUP BY type_id);
-- from 子查询：查询每个分类下价格最贵的书籍
SELECT t1.* from book t1,(SELECT type_id,MAX(price) as price from book GROUP BY type_id) t2 where t1.type_id=t2.type_id and t1.price = t2.price;
-- EXISTS 子查询：查询每个分类下价格最贵的书籍
SELECT t1.* from book t1 where
EXISTS (SELECT t2.type_id, MAX(price) price from book t2 GROUP BY t2.type_id HAVING t1.type_id=t2.type_id and t1.price = price);