目录
。sql/Mysql 新建部门与员工表.sql · 汪少棠/my-document - Gitee.com。
1、Mysql基础.pdf。
SQL 语句分类：
| DDL : 数据定义语句 (create alter drop) DML : 数据操作语句 (insert update delete) DQL : 数据查询语句(select) DCL : 数据控制语句(grant revoke commit rollback) |
Myqsl 常用设置
Mysql 最大连接数设置
1、Mysql 8 安装后，默认的最大并发连接数为 151(最大可达16384)，程序开发或者生产使用中如果连接数超过了这个上限，就会报错：Data source rejected establishment of connection, message from server: "Too many connections"， 表示：数据源拒绝建立连接，来自服务器的消息：“连接太多”
2、查看数据库支持的最大连接数以及数据库当前被使用的连接数：
show variables like '%max_connections%';
show global status like 'Max_used_connections';
3、设置数据支持的最大连接数，即时生效，无需要重启 mysql 服务：
-- 方式1：未持久化到文件中，所以重启服务后失效
set GLOBAL max_connections=1024;
-- 方式2：修改的同时持久化到文件中，重启后也生效
set persist max_connections=1024;
4、设置数据支持的最大连接数为默认值。
-- 方式1：不持久化到配置文件中
set global max_connections=default;
-- 方式2：持久化到配置文件中
set persist max_connections=default;
show 服务器内部状态信息
-- 显示所有的系统变量的值，可以获取到所有统计信息
-- 默认是session级别的，可以指定查看全局的global
-- show 命令可以用like来进行过滤，查看指定项
show status;
show global status;
show session status;
show status like 'Aborted_clients'SHOW VARIABLES LIKE "%version%"; -- 显示数据库实例信息
SHOW PROCESSLIST; -- 显示数据库连接信息
SHOW INDEX FROM 表名;-- 显示数据库索引统计信息
-- 显示数据库存储过程和函数
SHOW PROCEDURE STATUS;
SHOW FUNCTION STATUS;
SHOW STATUS LIKE 'Qcache_hits';-- 显示数据库查询缓存命中率
SHOW OPEN TABLES WHERE In_use > 0;-- 显示数据库锁信息用户管理与授权
连接登陆与修改密码
1、如果本级安装了Mysql，则可以在命令行中使用以下命令。
| 命令 | 描述 |
|---|---|
| mysql -h主机地址 -p端口 -u用户名 －p用户密码 | 连接登陆到 MySQL 数据库。 比如：mysql -h localhost -p3306 -uroot -proot |
| exit | 退出 |
| mysqladmin -u用户名 -p旧密码 password 新密码 | 修改登陆密码(无需登陆)。 比如：mysqladmin -uroot -proot password root2 |
| select user(); | -- 查看当前用户，如 root@localhost |
DDL 数据库相关
| 连接使用数据库 | USE 数据库名称; 比如：mysql> use test |
| 查看当前选择的数据库 | select database(); |
| 查看显示 MySQL 的版本 | select version(); |
| 查看数据库创建信息 | SHOW CREATE DATABASE wangmx -- （wangmx是数据库名称） |
| 显示 mysql 中所有数据库 | SHOW DATABASES ; |
| 显示数据库表空间使用情况 | SELECT table_schema AS "Database", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM information_schema.tables GROUP BY table_schema; |
| 显示数据库 db_name 的创建语句 | SHOW CREATE DATABASE db_name ; |
| 显示数据库慢查询 | SELECT * FROM mysql.slow_log; |
| 创建数据库 | 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 FROM test;（test是数据库名称，只能查看表名） |
| -- 使用 information_schema.tables 查询指定数据库下全部表，包含了表的行数、占用内存等各种详细信息 select * from information_schema.tables where table_schema = 'test'; | |
| 查询当前数据库下所有的表名 | show TABLES; |
| 显示 person 创建表语句 | show CREATE TABLE person; |
| 获取表结构 | DESC 表名; -- 查看字段名、类型、是否允许为null，是否自动递增、主外键、默认值。 |
| -- 查看表结构更加详细的信息，包括字段注释信息、字符集、列顺序序号等 SELECT * from information_schema.COLUMNS where table_schema = 'test' and table_name = 'EMP'; | |
| show columns from emp; -- 查看字段的详细信息 show full columns from emp;-- 带 full，可以看到更加详细的信息，包含字符集、注释等等 | |
| 删除表 | 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;-- 复制表结构和数据建表，不包括主外键，索引等 | |
-- 创建 book 表，结尾的字符集、排序规则、存储引擎通常不写与数据库保存默认即可
CREATE TABLE if NOT EXISTS book2 (
id int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
title varchar(128) COLLATE utf8mb4_bin NOT NULL COMMENT '书名',
info varchar(128) DEFAULT NULL COMMENT '描述',
code varchar(64) DEFAULT NULL COMMENT '条形码',
PRIMARY KEY (id)
-- constraint 外键约束名 foreign key (本表中的外键列名) references 被关联的表名 (被关联的列名) on delete cascade on UPDATE cascade
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='书籍表';表结构修改
1、使用 ALTER TABLE 语句追加, 修改, 或删除列的语法。
| 增加字段 | ALTER TABLE 表名 ADD 字段 类型 约束 默认值 描述 [first|after xx]; ALTER TABLE tablename ADD (column datatype [DEFAULT expr] [, column datatype]...); -- 追加列 |
| 更改表名 | ALTER TABLE 旧表名 RENAME TO 新表名; rename table 旧表名 to 新表名; |
| 修改字段 | ALTER TABLE table_name MODIFY column_name datatype 约束 默认值 描述 [first|after xx]; ALTER TABLE tablename MODIFY column datatype [DEFAULT expr]; -- 修改列 |
| 修改列的类型和约束 | #alter table 表名 modify column 所要修改的列名 所要改成的类型 character set 所要改成的编码格式; alter table user modify column u_name char(20) character set utf8mb4 collate utf8mb4_unicode_ci; |
| 删除列 | ALTER TABLE tablename DROP (column); #alter table 表名 drop column 所要删除的列名; |
| alter table student character set utf8; -- 修改表的字符集 | |
-- 新增 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
-- 建表后，追加主键列
ALTER TABLE tv ADD tv_id int(11) FIRST;-- 先加列，位置为第一列
alter table tv add primary key(tv_id );-- 设置为主键列
ALTER TABLE tv MODIFY tv_id int(11) AUTO_INCREMENT NOT NULL COMMENT '主键' ; -- 调整为主键自增not null、unique, primary key, foreign key 和 check/默认值 五种约束可以参考：约束。
数据库 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 中按顺序传入花样 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 的数据，则先删除旧数据，然后插入新数据
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 表所有数据
数据库 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 emp t where (t.ENAME,t.JOB) in(('KING','PRESIDENT'),('BLAKE','MANAGER'),('JAMES','CLERK'));
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;
-- 左外连接查询价格高于50的所有书籍及其分类，没有指定分类的书籍的记录的 type_id、name 会为 NULL
SELECT t1.*,t2.name FROM book t1 LEFT JOIN book_type t2 ON t1.type_id = t2.id where t1.price >= 50;
-- 右外连接查询所有书籍分类，以及分类下的书籍
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 筛选条件连表查询时，条件放在on后边还是放在where后边的？
1、对于 left join(左外连接)，不管 on 后面跟什么条件，左表的数据全部会查出来，因此要想过滤结果必须把条件放到where后面，右外连接也是同理。
2、对于 inner join(内连接)，满足on后面的条件表的数据才能查出，可以起到过滤作用，也可以把条件放到where后面。
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);
扫一扫
1770
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
