Mysql数据库学习及总结

2023年6月21日发(作者：)

Mysql数据库学习及总结前⾔ 本⼈是⼀名测试⼯程师，不管是对于数据分析师、开发⼯程师、测试⼯程师，数据库操作都是最基础也是最重要的⼀门技术，这篇⽂章就是本⼈在学习及⼯作过程对Mysql数据库的总结。⼀、基础概念1、数据库：是存储在计算机内的、有组织的、可共享的统⼀管理相关数据的集合，由表、关系、操作对象组成。2、事务的特征：原⼦性，⼀致性，隔离性，持久性。3、数据库管理系统：是⼀种操纵和管理数据库的⼤型软件，⽤来创建、使⽤、维护数据库。DDL:数据定义语⾔，DML:数据操纵语⾔，DCL:数据控制语⾔。4、Mysql数据库特点：性能⾼效、跨平台、⽀持多⽤户、免费开源、使⽤简单。5、主键：是唯⼀能标识某个表的记录的⽆具体业务含义的字段，像⾝份照、卡号、⼿机号、邮箱等都可以作为主键使⽤。特点：a.主键的字段具备唯⼀性；b.主键的字段不能为空；c.主键⾃带索引；d.可以多个字段组成主键；e.⼀个表仅⼀个主键。6、外键：⽤于在两个表之间建⽴关系，需要指定引⽤主表的哪⼀列。如果表A的主键是表B中的字段,则该字段称为表B的外键,表A(主表),表B(从表)。外键是⽤来实现参照完整性的，主表更新时从表也更新,主表删除时如果从表有匹配的项,删除失败。7、索引：是⼀种单独的、物理的对数据库表中⼀列或多列的值进⾏排序的⼀种存储结构。优点：使⽤索引能加快数据的查询速度，缺点：索引会占⽤内存、磁盘空间，会增加数据库管理系统的维护成本。8、视图：⼀个虚表，⾥⾯不保存任何数据，视图的数据来源于创建视图时的基表。作⽤：简化操作、能把复杂的sql简化，B.避免数据冗余，C.提⾼数据安全性，D.提⾼数据逻辑独⽴性。⼆、Mysql数据类型Mysql数据类型：⽇期类型：date /time /datetime/timestamp，字符串类型：char/varchar/text，浮点型：decimal/float/double，整型：tinyint/smallint/mediumint/int/bigint。Mysql中除了字符串类型需要设置长度,其他类型都有默认长度。 三、数据控制语⾔DCL1、系统操作根据进程名查端⼝：st|findstr mysql t -ano|findstr pid根据端⼝查进程：t -ano|findstr port st |findstr pid启动mysql服务：net start mysql停⽌mysql服务：net stop mysql访问mysql：mysql -h localhost -P 3306 -u root -p 密码查参数：show variables;设置参数：set 参数名称=’值’;查当前存储引擎：show engines ;修改当前存储引擎：set default_storage_engine=innodb;查状态：SHOW STATUS;查询版本：SELECT VERSION();2、⽤户权限创建⽤户：create user `test`@`localhost` identified by ‘123456’;修改密码：alter user `test`@`localhost` identified by ‘123456’;

删除⽤户：drop user `test`@`localhost` ;刷新⽤户：flush privileges;授予权限：grant all on *.* to `test`@`localhost` ;撤销权限：revoke all on *.* from `test`@`localhost` ;查询当前⽤户：select USER();查看⽤户的权限：show grants for Fox@localhost;查询数据库所有的⽤户：SELECT * FROM ;四、数据定义语⾔DDL1、建库查所有的库：show databases;查当前库：select database();修改数据库：ALTER DATABASE mybase CHARACTER SET UTF8;创建数据库：create database 数据库名 character set utf8;查建库语句：show create database 数据库名;删库：drop database 数据库名;切换数据库：USE mybase;2、建表查所有的表：show tables;创建表：create table exam( id INT(11) PRIMARY KEY AUTO_INCREMENT, -- 主键⾃动递增 name VARCHAR(20) DEFAULT NULL, -- 默认为空 English INT, sal DOUBLE(7,2), --整数位7，⼩数位2 Math int NOT NULL --⾮空);添加主键：alter table stu2 add PRIMARY key (id,`name`); --指定id name⼀起作为主键添加默认值：alter table stu2 alter COLUMN `gender` set DEFAULT 'm';添加唯⼀约束和⾮空约束：ALTER TABLE exam MODIFY NAME VARCHAR(21) UNIQUE NOT NULL;查表结构：desc 表名查建表语句：show create table 表名;删表：drop table 表名;修改表名称：RENAME TABLE exam TO score;修改表的字符集：ALTER TABLE score CHARACTER SET GBK;create table stu4 like stu3; -- 完全复制表结构，除了数据SHOW GLOBAL VARIABLES LIKE 'innodb_force_primary_key'; -- 查看是否开启强制主键SET GLOBAL innodb_force_primary_key=off; -- 关闭强制主键mysql8以后默认开启强制主键约束，得关闭强制主键才能使⽤下列语句。ps:⼀般公司都不允许关闭强制主键。create table stu5 as select * from stu3;-- 复制表中所有数据到新表，除了主键⽆法复制create table stu6 as select id,name from stu3;--复制原表部分字段到新表，除了主键create table stu7 as select * from stu3 where 1=0;--复制原表的表结构到新表，除了主键3、建索引建表时创建：create table book(isbn varchar(50) not null,bookname varchar(100) not null,

price decimal(8,2),author varchar(50),publishhouse varchar(50),summary text,PRIMARY key `pk_isbn`(`isbn`), -- 主键索引UNIQUE index `idx_bookname`(`bookname`), -- 唯⼀索引index `idx_author`(`author`), -- 普通索引FULLTEXT index `ft_summary`(`summary`) -- 全⽂索引);create语句：create index `idx_author` on `book`(`author`); -- 普通索引create UNIQUE index `idx_bookname` on `book`(`bookname`); -- 唯⼀索引create FULLTEXT index `ft_summary` on `book`(`summary`); -- 全⽂索引alter table 语句：alter table book add index `idx_author`(`author`); -- 普通索引alter table book add UNIQUE index `idx_bookname`(`bookname`); -- 唯⼀索引alter table book add FULLTEXT index `ft_summary`(`summary`); -- 全⽂索引删除索引：drop index 索引名 on 表名;查看索引：SHOW INDEX FROM 表名;4、建视图create view 视图名(字段列表) as select 字段列表 from ......CREATE VIEW v_studentCourseScore ( studentno, NAME, coursename, score) AS SELECT , a.`name`, , FROM student a, course b, score cWHERE = AND = _id;删除视图：drop view 视图名;5、修改表字段添加字段：alter table stu2 add major VARCHAR(20);修改数据类型：alter table stu2 MODIFY major char(20);修改字段名称：alter table stu2 change major professional char(20);删除字段：alter table stu2 drop professional;五、数据操纵语⾔DML1、插⼊数据Insert into：直接插⼊数据插⼊⼀条全部字段值：insert into stu2 values(1,'zhangsan','m',20);插⼊⼀条部分字段值：insert into stu2(id,name) values(3,'wangwu');⼀次性插⼊多条数据：insert into stu2(id,name) values(5,'zhao7'),(6,'zhao8'),(7,'zhao9');查询某个表的结果插⼊：insert into stu3(id,name,gender,age) select id,name,gender,age from stu2;Replace into：先检查是否存在，存在则更新，不存在则插⼊。Replace into 表名(字段列表) values(值列表)Replace into 表名(字段列表) select 字段列表 from 表 [where 约束条件]Replace into 表名 set 字段=值,字段=值2、更新数据update 表名 set 字段=值,字段=值 where 约束条件; --更新符合条件的⾏update 表名 set 字段=值,字段=值; --更新所有⾏3、删除数据delete from 表名 where 约束条件; --删除满⾜条件的记录，数据可以回滚delete from 表名; --删除全部记录，数据可以回滚truncate table 表名; --清空表，数据⽆法回滚。六、数据查询语⾔DQL1、select语法格式select 字段列表 from 表1,表2 where 约束条件 group by 分组字段 having 第⼆次筛选条件 order by 排序条件 limit 数量2、基本SQL语句查询全部数据：select * from stu;取别名as：select name as 姓名 from stu t; -- 查询列name并取别名为姓名，表名stu为t;排序order by：asc(升序)/desc(降序)，默认是升序。select * from stu2 order by age desc;-- 指定降序select * from stu2 order by age asc,weight desc;-- 多个字段排序limit关键字：select * from stu2 limit 2; -- 返回前2⾏select * from stu2 limit 1,2; -- 返回第⼆条 第三条，1是索引，索引从0开始distinct剔除重复的记录：select DISTINCT name from stu2; --返回名字不同的记录select DISTINCT name,age from stu2; -- 返回名字且年龄不同的记录where条件：关系运算符：= >= > <= < != <> <=>select * from stu2 where weight = null; -- 此处⽆法对null值进⾏约束，查询不到任何结果select * from stu2 where weight <=> null; -- 可以找出weight为空的所有记录select * from stu2 where weight >=100;--找出体重⼤于等于110的学⽣范围查询：between and 、not between and和 in、not inselect * from stu2 where weight BETWEEN 110 and 125; -- 适合连续值的范围查询select * from stu2 where weight in(110,120,125); -- 适合离散值的范围查询模糊匹配：like、 not likeselect * from stu2 where name like 'zhang%'; -- 以zhang开头的所有名字select * from stu2 where name like 'zhang_'; -- 以zhang开头的后⾯仅1个字符的名select * from stu2 where name like '%zhang%';-- 包含zhang的名字select * from stu2 where name like '_zhang_';-- 中间是zhang，前后各1个字符的名字判断是否为空：is null、is notselect * from stu2 where weight is null; -- 为空select * from stu2 where weight is not null; -- 不为空多条件查询：and、orselect * from stu2 where age > '18' and sex='男'; -- 查询年龄⼤于18岁且性别为男的记录select * from stu2 where age > '18' or sex='男'; -- 查询年龄⼤于18岁或性别为男的记录3、常⽤函数数学函数：select abs(-1.45); -- 取绝对值，输出1.45select FLOOR(1.5); -- 向下取整，输出1select CEILING(1.5); -- 向上取整，输出2select mod(2,3); -- 取模select TRUNCATE(156.567,2); --截取到⼩数点2位，输出156.56select TRUNCATE(156.567,0); --截取到整数，输出156select TRUNCATE(156.567,-2); --截取到整数2位，输出100select ROUND(156.567,2); --四舍五⼊到⼩数点2位，输出156.57select ROUND(156.567,0); --四舍五⼊到整数，输出157select ROUND(156.567,-2); --四舍五⼊到整数2位，输出200字符串函数：select CONCAT('i','have','a','dream'); -- 字符串连接select CONCAT_WS(' ','I','have','a','dream'); -- 字符串连接且每个单词空格隔开select left('湖南长沙',3); -- 取左边3个字符select right('湖南长沙',3); -- 取右边3个字符select length('abcdefg'); -- 显⽰字符串的长度select ltrim(' 湖南长沙五⼀⼴场'); -- 去掉左边空格select rtrim('湖南长沙五⼀⼴场 '); -- 去掉右边空格select trim(' 湖南 长沙 五⼀ ⼴场 '); -- 去掉前后空格select UPPER('abcd'); -- ⼩写变⼤写select LOWER('ABCD'); -- ⼤写变⼩写SELECT SUBSTR('123abcdABCD湖南长沙',4,3); -- 从第4位开始，截取3位，输出abcSELECT ename,IFNULL(COMMIT,0) FROM emp; -- 如果COMMIT有null值，则显⽰为0SELECT ename,CAST(sal AS INT) FROM emp; -- 把sal字段转为int类型输出SELECT ename,COALESCE(USER,COMMIT,0) FROM emp; -- 优先取user的值，user为null，则取commit的值，commit也为null，则输出0时间函数：select CURDATE(); -- 查当前⽇期2020-11-18select CURDATE() +0; -- 查当前⽇期20201118select CURTIME(); -- 查当前时间17:55:05select DATE_ADD('2018-02-10 10:10:10',INTERVAL 1 day); -- 加1天select DATE_ADD('2018-02-10 10:10:10',INTERVAL -1 MONTH); -- 减1⽉select DATE_ADD('2018-02-10 10:10:10',INTERVAL -1 YEAR); -- 减1年select DAYOFWEEK('2020-11-29 10:10:10'); -- 查询星期⼏，输出1，1代表星期⽇select DAYNAME('2020-11-29 10:10:10'); -- 查询星期的英⽂名称，输出Sundayselect MONTHNAME('2020-01-29 10:10:10'); -- 查询⽉份的英⽂名称，输出Januaryselect NOW(); -- 查询当前时间select UNIX_TIMESTAMP(); -- 查询当前时间戳聚合函数:select

max(score), -- 最⼤值min(score), -- 最⼩值avg(score), -- 平均值sum(score), -- 求和count(studentNO), -- 总次数from score;流程函数：SELECT IF(500<1000, 5, 10); -- 如果500<1000条件为真输出5，否则输出10;SELECT (CASEWHEN state='3' THEN '失败'WHEN state='5' THEN '成功'

ELSE '其他' END) '状态'FROM cm_job; -- 如果state='3'输出失败，state='5'输出成功分组查询：语法：Select [分组字段列表,] 聚合统计函数(...)From 表1,表2 where 约束条件Group by 分组字段 having 约束order by 排序字段 排序条件举例：查询各课程及格⼈数超过3个的，课程编号、⼈数select major,count(*) num from t

where score >=60 GROUP BY major having num>3;4、连接查询笛卡尔积select * from student a,class b;等值连接select ,ame from student a,class b where o=o;内连接：满⾜连接条件的结果集。select ,ame from student a,class b on o=o;select ,ame from student a,class b on o<>o;左连接：以左表为基础，结果会显⽰左表所有记录和右表匹配的记录、右表不匹配的⽤NULL代替。select ame,, from class a LEFT JOIN student b

on O=O left join score c on tNO=tNO

where ='m';右连接：以右表为基础，结果会显⽰右表所有记录和左表匹配的记录、左表不匹配的⽤NULL代替。select a.`name`,amefrom student a right join class b on O=O;⾃连接：表⾃⼰和⾃⼰进⾏连接select 领导编号, 领导姓名, 领导职务, 员⼯编号, 员⼯姓名, 员⼯职务from emp a, emp bwhere = ;5、⼦查询标量⼦查询：返回结果是1列1⾏--查询体重最重的学⽣信息select * from stu2 where weight=(select max(weight) from stu2);列⼦查询：⼦查询返回单列多⾏结果--找出语⽂分数最⾼的学⽣信息select * from student where studentno in(select tnofrom score s2,course c2 where no=no and =(select rom score s,course cwhere no=no and name='yuwen'order by desc limit 1) and name='yuwen')⾏⼦查询：返回单⾏多列的结果根据学号得到性别、班级编号、名字这些，再得到学⽣的详细信息select * from stu2 where (gender,class,name)=(select gender,class,name from stu2where id=1);表⼦查询：返回多⾏多列的结果找各课程中成绩相同的学⽣信息select * from student where studentno in(select tno from score s2, course c2 where no=no and(no,) in(select no, from score s, course c where no=no GROUP BY name, >=2));6、Exists谓词存在则后⾯的sql执⾏，不存在则后⾯的sql不执⾏。假如说⼀个学⽣他的语⽂成绩⼩于40，则把成绩更新成60。update score set score=60 where score<=40 and courseno =(select courseno from course where coursename='yuwen');7、派⽣表把sql语句返回的结果当做表来和其他表做关联，再次进⾏查询，这个就是派⽣表。各课程分数最⾼的学⽣信息。select * from student where studentno in(select tno

from score a,(select courseno,max(score) ma from score group by courseno) bwhere no=no and =);8、集合运算UNION：两个查询语句把结果并到⼀起，剔除重复的⾏UNION ALL：两个查询语句把结果并到⼀起，不剔除任何⾏SELECT FROM dept a UNION SELECT deptno b FROM emp;-- 查询的列必须相同

2023年6月21日发(作者：)

Mysql数据库学习及总结前⾔ 本⼈是⼀名测试⼯程师，不管是对于数据分析师、开发⼯程师、测试⼯程师，数据库操作都是最基础也是最重要的⼀门技术，这篇⽂章就是本⼈在学习及⼯作过程对Mysql数据库的总结。⼀、基础概念1、数据库：是存储在计算机内的、有组织的、可共享的统⼀管理相关数据的集合，由表、关系、操作对象组成。2、事务的特征：原⼦性，⼀致性，隔离性，持久性。3、数据库管理系统：是⼀种操纵和管理数据库的⼤型软件，⽤来创建、使⽤、维护数据库。DDL:数据定义语⾔，DML:数据操纵语⾔，DCL:数据控制语⾔。4、Mysql数据库特点：性能⾼效、跨平台、⽀持多⽤户、免费开源、使⽤简单。5、主键：是唯⼀能标识某个表的记录的⽆具体业务含义的字段，像⾝份照、卡号、⼿机号、邮箱等都可以作为主键使⽤。特点：a.主键的字段具备唯⼀性；b.主键的字段不能为空；c.主键⾃带索引；d.可以多个字段组成主键；e.⼀个表仅⼀个主键。6、外键：⽤于在两个表之间建⽴关系，需要指定引⽤主表的哪⼀列。如果表A的主键是表B中的字段,则该字段称为表B的外键,表A(主表),表B(从表)。外键是⽤来实现参照完整性的，主表更新时从表也更新,主表删除时如果从表有匹配的项,删除失败。7、索引：是⼀种单独的、物理的对数据库表中⼀列或多列的值进⾏排序的⼀种存储结构。优点：使⽤索引能加快数据的查询速度，缺点：索引会占⽤内存、磁盘空间，会增加数据库管理系统的维护成本。8、视图：⼀个虚表，⾥⾯不保存任何数据，视图的数据来源于创建视图时的基表。作⽤：简化操作、能把复杂的sql简化，B.避免数据冗余，C.提⾼数据安全性，D.提⾼数据逻辑独⽴性。⼆、Mysql数据类型Mysql数据类型：⽇期类型：date /time /datetime/timestamp，字符串类型：char/varchar/text，浮点型：decimal/float/double，整型：tinyint/smallint/mediumint/int/bigint。Mysql中除了字符串类型需要设置长度,其他类型都有默认长度。 三、数据控制语⾔DCL1、系统操作根据进程名查端⼝：st|findstr mysql t -ano|findstr pid根据端⼝查进程：t -ano|findstr port st |findstr pid启动mysql服务：net start mysql停⽌mysql服务：net stop mysql访问mysql：mysql -h localhost -P 3306 -u root -p 密码查参数：show variables;设置参数：set 参数名称=’值’;查当前存储引擎：show engines ;修改当前存储引擎：set default_storage_engine=innodb;查状态：SHOW STATUS;查询版本：SELECT VERSION();2、⽤户权限创建⽤户：create user `test`@`localhost` identified by ‘123456’;修改密码：alter user `test`@`localhost` identified by ‘123456’;

删除⽤户：drop user `test`@`localhost` ;刷新⽤户：flush privileges;授予权限：grant all on *.* to `test`@`localhost` ;撤销权限：revoke all on *.* from `test`@`localhost` ;查询当前⽤户：select USER();查看⽤户的权限：show grants for Fox@localhost;查询数据库所有的⽤户：SELECT * FROM ;四、数据定义语⾔DDL1、建库查所有的库：show databases;查当前库：select database();修改数据库：ALTER DATABASE mybase CHARACTER SET UTF8;创建数据库：create database 数据库名 character set utf8;查建库语句：show create database 数据库名;删库：drop database 数据库名;切换数据库：USE mybase;2、建表查所有的表：show tables;创建表：create table exam( id INT(11) PRIMARY KEY AUTO_INCREMENT, -- 主键⾃动递增 name VARCHAR(20) DEFAULT NULL, -- 默认为空 English INT, sal DOUBLE(7,2), --整数位7，⼩数位2 Math int NOT NULL --⾮空);添加主键：alter table stu2 add PRIMARY key (id,`name`); --指定id name⼀起作为主键添加默认值：alter table stu2 alter COLUMN `gender` set DEFAULT 'm';添加唯⼀约束和⾮空约束：ALTER TABLE exam MODIFY NAME VARCHAR(21) UNIQUE NOT NULL;查表结构：desc 表名查建表语句：show create table 表名;删表：drop table 表名;修改表名称：RENAME TABLE exam TO score;修改表的字符集：ALTER TABLE score CHARACTER SET GBK;create table stu4 like stu3; -- 完全复制表结构，除了数据SHOW GLOBAL VARIABLES LIKE 'innodb_force_primary_key'; -- 查看是否开启强制主键SET GLOBAL innodb_force_primary_key=off; -- 关闭强制主键mysql8以后默认开启强制主键约束，得关闭强制主键才能使⽤下列语句。ps:⼀般公司都不允许关闭强制主键。create table stu5 as select * from stu3;-- 复制表中所有数据到新表，除了主键⽆法复制create table stu6 as select id,name from stu3;--复制原表部分字段到新表，除了主键create table stu7 as select * from stu3 where 1=0;--复制原表的表结构到新表，除了主键3、建索引建表时创建：create table book(isbn varchar(50) not null,bookname varchar(100) not null,

price decimal(8,2),author varchar(50),publishhouse varchar(50),summary text,PRIMARY key `pk_isbn`(`isbn`), -- 主键索引UNIQUE index `idx_bookname`(`bookname`), -- 唯⼀索引index `idx_author`(`author`), -- 普通索引FULLTEXT index `ft_summary`(`summary`) -- 全⽂索引);create语句：create index `idx_author` on `book`(`author`); -- 普通索引create UNIQUE index `idx_bookname` on `book`(`bookname`); -- 唯⼀索引create FULLTEXT index `ft_summary` on `book`(`summary`); -- 全⽂索引alter table 语句：alter table book add index `idx_author`(`author`); -- 普通索引alter table book add UNIQUE index `idx_bookname`(`bookname`); -- 唯⼀索引alter table book add FULLTEXT index `ft_summary`(`summary`); -- 全⽂索引删除索引：drop index 索引名 on 表名;查看索引：SHOW INDEX FROM 表名;4、建视图create view 视图名(字段列表) as select 字段列表 from ......CREATE VIEW v_studentCourseScore ( studentno, NAME, coursename, score) AS SELECT , a.`name`, , FROM student a, course b, score cWHERE = AND = _id;删除视图：drop view 视图名;5、修改表字段添加字段：alter table stu2 add major VARCHAR(20);修改数据类型：alter table stu2 MODIFY major char(20);修改字段名称：alter table stu2 change major professional char(20);删除字段：alter table stu2 drop professional;五、数据操纵语⾔DML1、插⼊数据Insert into：直接插⼊数据插⼊⼀条全部字段值：insert into stu2 values(1,'zhangsan','m',20);插⼊⼀条部分字段值：insert into stu2(id,name) values(3,'wangwu');⼀次性插⼊多条数据：insert into stu2(id,name) values(5,'zhao7'),(6,'zhao8'),(7,'zhao9');查询某个表的结果插⼊：insert into stu3(id,name,gender,age) select id,name,gender,age from stu2;Replace into：先检查是否存在，存在则更新，不存在则插⼊。Replace into 表名(字段列表) values(值列表)Replace into 表名(字段列表) select 字段列表 from 表 [where 约束条件]Replace into 表名 set 字段=值,字段=值2、更新数据update 表名 set 字段=值,字段=值 where 约束条件; --更新符合条件的⾏update 表名 set 字段=值,字段=值; --更新所有⾏3、删除数据delete from 表名 where 约束条件; --删除满⾜条件的记录，数据可以回滚delete from 表名; --删除全部记录，数据可以回滚truncate table 表名; --清空表，数据⽆法回滚。六、数据查询语⾔DQL1、select语法格式select 字段列表 from 表1,表2 where 约束条件 group by 分组字段 having 第⼆次筛选条件 order by 排序条件 limit 数量2、基本SQL语句查询全部数据：select * from stu;取别名as：select name as 姓名 from stu t; -- 查询列name并取别名为姓名，表名stu为t;排序order by：asc(升序)/desc(降序)，默认是升序。select * from stu2 order by age desc;-- 指定降序select * from stu2 order by age asc,weight desc;-- 多个字段排序limit关键字：select * from stu2 limit 2; -- 返回前2⾏select * from stu2 limit 1,2; -- 返回第⼆条 第三条，1是索引，索引从0开始distinct剔除重复的记录：select DISTINCT name from stu2; --返回名字不同的记录select DISTINCT name,age from stu2; -- 返回名字且年龄不同的记录where条件：关系运算符：= >= > <= < != <> <=>select * from stu2 where weight = null; -- 此处⽆法对null值进⾏约束，查询不到任何结果select * from stu2 where weight <=> null; -- 可以找出weight为空的所有记录select * from stu2 where weight >=100;--找出体重⼤于等于110的学⽣范围查询：between and 、not between and和 in、not inselect * from stu2 where weight BETWEEN 110 and 125; -- 适合连续值的范围查询select * from stu2 where weight in(110,120,125); -- 适合离散值的范围查询模糊匹配：like、 not likeselect * from stu2 where name like 'zhang%'; -- 以zhang开头的所有名字select * from stu2 where name like 'zhang_'; -- 以zhang开头的后⾯仅1个字符的名select * from stu2 where name like '%zhang%';-- 包含zhang的名字select * from stu2 where name like '_zhang_';-- 中间是zhang，前后各1个字符的名字判断是否为空：is null、is notselect * from stu2 where weight is null; -- 为空select * from stu2 where weight is not null; -- 不为空多条件查询：and、orselect * from stu2 where age > '18' and sex='男'; -- 查询年龄⼤于18岁且性别为男的记录select * from stu2 where age > '18' or sex='男'; -- 查询年龄⼤于18岁或性别为男的记录3、常⽤函数数学函数：select abs(-1.45); -- 取绝对值，输出1.45select FLOOR(1.5); -- 向下取整，输出1select CEILING(1.5); -- 向上取整，输出2select mod(2,3); -- 取模select TRUNCATE(156.567,2); --截取到⼩数点2位，输出156.56select TRUNCATE(156.567,0); --截取到整数，输出156select TRUNCATE(156.567,-2); --截取到整数2位，输出100select ROUND(156.567,2); --四舍五⼊到⼩数点2位，输出156.57select ROUND(156.567,0); --四舍五⼊到整数，输出157select ROUND(156.567,-2); --四舍五⼊到整数2位，输出200字符串函数：select CONCAT('i','have','a','dream'); -- 字符串连接select CONCAT_WS(' ','I','have','a','dream'); -- 字符串连接且每个单词空格隔开select left('湖南长沙',3); -- 取左边3个字符select right('湖南长沙',3); -- 取右边3个字符select length('abcdefg'); -- 显⽰字符串的长度select ltrim(' 湖南长沙五⼀⼴场'); -- 去掉左边空格select rtrim('湖南长沙五⼀⼴场 '); -- 去掉右边空格select trim(' 湖南 长沙 五⼀ ⼴场 '); -- 去掉前后空格select UPPER('abcd'); -- ⼩写变⼤写select LOWER('ABCD'); -- ⼤写变⼩写SELECT SUBSTR('123abcdABCD湖南长沙',4,3); -- 从第4位开始，截取3位，输出abcSELECT ename,IFNULL(COMMIT,0) FROM emp; -- 如果COMMIT有null值，则显⽰为0SELECT ename,CAST(sal AS INT) FROM emp; -- 把sal字段转为int类型输出SELECT ename,COALESCE(USER,COMMIT,0) FROM emp; -- 优先取user的值，user为null，则取commit的值，commit也为null，则输出0时间函数：select CURDATE(); -- 查当前⽇期2020-11-18select CURDATE() +0; -- 查当前⽇期20201118select CURTIME(); -- 查当前时间17:55:05select DATE_ADD('2018-02-10 10:10:10',INTERVAL 1 day); -- 加1天select DATE_ADD('2018-02-10 10:10:10',INTERVAL -1 MONTH); -- 减1⽉select DATE_ADD('2018-02-10 10:10:10',INTERVAL -1 YEAR); -- 减1年select DAYOFWEEK('2020-11-29 10:10:10'); -- 查询星期⼏，输出1，1代表星期⽇select DAYNAME('2020-11-29 10:10:10'); -- 查询星期的英⽂名称，输出Sundayselect MONTHNAME('2020-01-29 10:10:10'); -- 查询⽉份的英⽂名称，输出Januaryselect NOW(); -- 查询当前时间select UNIX_TIMESTAMP(); -- 查询当前时间戳聚合函数:select

max(score), -- 最⼤值min(score), -- 最⼩值avg(score), -- 平均值sum(score), -- 求和count(studentNO), -- 总次数from score;流程函数：SELECT IF(500<1000, 5, 10); -- 如果500<1000条件为真输出5，否则输出10;SELECT (CASEWHEN state='3' THEN '失败'WHEN state='5' THEN '成功'

ELSE '其他' END) '状态'FROM cm_job; -- 如果state='3'输出失败，state='5'输出成功分组查询：语法：Select [分组字段列表,] 聚合统计函数(...)From 表1,表2 where 约束条件Group by 分组字段 having 约束order by 排序字段 排序条件举例：查询各课程及格⼈数超过3个的，课程编号、⼈数select major,count(*) num from t

where score >=60 GROUP BY major having num>3;4、连接查询笛卡尔积select * from student a,class b;等值连接select ,ame from student a,class b where o=o;内连接：满⾜连接条件的结果集。select ,ame from student a,class b on o=o;select ,ame from student a,class b on o<>o;左连接：以左表为基础，结果会显⽰左表所有记录和右表匹配的记录、右表不匹配的⽤NULL代替。select ame,, from class a LEFT JOIN student b

on O=O left join score c on tNO=tNO

where ='m';右连接：以右表为基础，结果会显⽰右表所有记录和左表匹配的记录、左表不匹配的⽤NULL代替。select a.`name`,amefrom student a right join class b on O=O;⾃连接：表⾃⼰和⾃⼰进⾏连接select 领导编号, 领导姓名, 领导职务, 员⼯编号, 员⼯姓名, 员⼯职务from emp a, emp bwhere = ;5、⼦查询标量⼦查询：返回结果是1列1⾏--查询体重最重的学⽣信息select * from stu2 where weight=(select max(weight) from stu2);列⼦查询：⼦查询返回单列多⾏结果--找出语⽂分数最⾼的学⽣信息select * from student where studentno in(select tnofrom score s2,course c2 where no=no and =(select rom score s,course cwhere no=no and name='yuwen'order by desc limit 1) and name='yuwen')⾏⼦查询：返回单⾏多列的结果根据学号得到性别、班级编号、名字这些，再得到学⽣的详细信息select * from stu2 where (gender,class,name)=(select gender,class,name from stu2where id=1);表⼦查询：返回多⾏多列的结果找各课程中成绩相同的学⽣信息select * from student where studentno in(select tno from score s2, course c2 where no=no and(no,) in(select no, from score s, course c where no=no GROUP BY name, >=2));6、Exists谓词存在则后⾯的sql执⾏，不存在则后⾯的sql不执⾏。假如说⼀个学⽣他的语⽂成绩⼩于40，则把成绩更新成60。update score set score=60 where score<=40 and courseno =(select courseno from course where coursename='yuwen');7、派⽣表把sql语句返回的结果当做表来和其他表做关联，再次进⾏查询，这个就是派⽣表。各课程分数最⾼的学⽣信息。select * from student where studentno in(select tno

from score a,(select courseno,max(score) ma from score group by courseno) bwhere no=no and =);8、集合运算UNION：两个查询语句把结果并到⼀起，剔除重复的⾏UNION ALL：两个查询语句把结果并到⼀起，不剔除任何⾏SELECT FROM dept a UNION SELECT deptno b FROM emp;-- 查询的列必须相同