1.Create 新增1.1语法创建一个用于演示的表create table users ( id bigint, name varchar(20) comment ⽤⼾名 );1.2单行数据全列插入不用写列名value_list中值的数量必须和定义表的列的数量及顺序一致insert into users values (1, 张三);1.3单行数据指定列插入value_list中值的数量必须和指定列的数量及顺序一致insert into users(id, name) values (2,李四);1.4多行数据指定列插入在⼀条INSERT语句中也可以指定多个value_list用逗号隔开实现一次插入多行数据。insert into users(id, name) values (3,王五), (4,赵四);2.Retrieve 检索2.1语法数据构造数据-- 创建表结构 CREATE TABLE exam ( id BIGINT, name VARCHAR(20) COMMENT 同学姓名, chinese float COMMENT 语⽂成绩, math float COMMENT 数学成绩, english float COMMENT 英语成绩 ); -- 插⼊测试数据 INSERT INTO exam (id, name, chinese, math, english) VALUES (1, 唐三藏, 67, 98, 56), (2, 孙悟空, 87, 78, 77), (3, 猪悟能, 88, 98, 90), (4, 曹孟德, 82, 84, 67), (5, 刘⽞德, 55, 85, 45), (6, 孙权, 70, 73, 78), (7, 宋公明, 75, 65, 30);系统支持通过命令行 / 图形化客户端工具导入SQL语句及脚本。点击my.ini修改文件第63行改完后保存并重启MySQL刚才已经通过Navicat导入过sql所以此时仅有一个错误才正确但仍有字符编码错误可能有其他错误暂时不管了。2.2Select 选择在 SQL 查询中所有的 SELECT 操作结果都会通过临时表的形式返回给客户端。1全列查询查询所有记录select * from exam;2指定列查询在select后面的查询列表中指定希望查询的列可以是⼀个也可以是多个中间用逗号隔开指定列的顺序与表结构中的列的顺序无关。例查询所有人的编号、姓名和语文成绩select id, name, chinese from exam;3查询字段为表达式•查询列表中的表达式可以是表中不存在的值或列。•若表达式为字符串常量必须使用单引号将其括起。•表达式的执行过程首先从物理表中读取对应列的值然后计算表达式的值合并所有结果形成结果集最终通过临时表将结果返回给客户端。常量表达式select id, name, 10, 详情 from exam;把所有学生的语文成绩加10分select id, name, chinese10 from exam;计算所有学生语文、数学和英语成绩的总分可以对列和列进行计算。select id, name, chinesemathEnglish from exam;4为查询结果指定别名只是为结果起个别名where中不能用别名进行比较。AS可以省略别名如果包含空格必须用单引号包裹。select id, name, chinesemathEnglish as 总分 from exam;distinct--不同的5结果去重查询-distinct只有查询列表中所有列的值都相同才会判定为重复查询当前所的数学成绩在结果集中去除重复记录。select distinct math from exam;2.3Where 条件查询SQL 查询执行时会逐行读取表中的记录并对每一行应用WHERE 条件进行判断。符合条件的记录会被放入临时表中最终返回给客户端可以保证原数据不受影响。语法1比较运算符2逻辑运算符示例1:基本查询查询英语不及格的同学及英语成绩60条件--english60查询列--name, englishselect name, english from exam where english60;查询语文成绩高于英语成绩的同学条件--chineseenglish查询列--name, chinese, englishselect name, chinese, english from exam where chineseenglish;总分在200分以下的同学条件--chinesemathenglish200查询列--name, chinesemathenglish 总分select name, chinesemathenglish 总分 from exam where chinesemathenglish200;注where中不能用别名进行比较。示例2:AND和OR查询语文成绩大于80分且英语成绩大于80分的同学条件--chinese80 and english80查询列--name, chinese, englishselect name, chinese, english from exam where chinese80 and english 80;查询语文成绩大于80分或英语成绩大于80分的同学条件--chinese80 or english80查询列--name, chinese, englishselect name, chinese, english from exam where chinese80 or english 80;注意AND的优先级高于OR在同时使用时建议使用小括号()包裹优先执行的部分。示例3:范围查询语文成绩在[80,90]分的同学及语文成绩条件--chinese between 80 and 90查询列--name, chineseselect name, chinese from exam where chinese between 80 and 90;数学成绩是78或者79或者98或者99分的同学及数学成绩条件--math in(78, 79, 98, 99)查询列--name, mathselect name, math from exam where math in(78, 79, 98, 99);示例4:模糊查询示例5:NULL的查询写入⼀条数据英语成绩为NULLinsert into exam values (8, 张⻜, 27, 0, NULL);查询英语成绩为NULL的记录条件--math is null查询列--*select * from exam where english is null;查询英语成绩不为NULL的记录可以过滤掉不符合条件的值。条件--math is not null查询列--*select * from exam where english is not null;注意NULL与任何值进行运算结果为NULL所以可以过滤掉不符合条件的值。2.4Order by 排序1语法注意•对SELECT查询出来的有效结果集进行排序可以使用SELECT 列表中定义的列别名来进行排序。•当对多个列进行排序时各列之间需用逗号隔开。排序的执行顺序是依次进行的即后续列的排序规则会基于前一列排序的结果之上应用。•指定排序列后返回的结果集会按照当前排序规则重新组织。排序操作通常在额外的内存空间如临时表中进行以保证原数据不受影响。2示例按数学成绩从低到高排序(升序)asc查询列--*排序--math ascselect * from exam order by math asc;按语文成绩从高到低排序(降序)desc查询列--*排序--chinese descselect * from exam order by chinese desc;按英语成绩从高到低排序desc查询列--*排序--english descselect * from exam order by english desc;注NULL被看做比任何值都小查询同学各门成绩依次按数学降序英语升序语文升序的方式显示。查询列--name, math, english, chinese排序--math desc, english asc, chinese ascselect name, math, english, chinese from exam order by math desc, english asc, chinese asc;查询同学及总分由高到低排序查询列--name, chinesemathenglish 总分排序--总分 desc对SELECT查询出来的有效结果集进行排序可以使用SELECT 列表中定义的列别名来进行排序。select name, chinesemathenglish 总分 from exam order by 总分 desc;所有英语成绩不为NULL的同学按语文成绩从高到低排序条件--english is not null查询列--*排序--chinese desc2.5Limit 分页查询1语法注意•先执行order by后执行limit。•当start的值超出了表中记录数的范围返回null。•没有达到 limit 的条数限制也不会有任何影响有多少条就显示多少条2示例从第0条开始读3条记录类似每页3条记录此时查询的就是第一页。select * from exam limit 0, 3;从第3条开始读3条记录类似每页3条记录此时查询的就是第二页。select * from exam limit 3, 3;从第6条开始读3条记录类似每页3条记录此时查询的就是第三页。select * from exam limit 6, 3;3.Update 修改3.1语法注意•更新务必加WHERE条件避免误更新全表数据。•可以设置多个列的值列与列之间用逗号隔开。•对符合条件的结果进行列值更新。3.2示例将孙悟空同学的数学成绩变更为80分条件--name 孙悟空修改列--math修改值--80update exam set math 80 where name 孙悟空;先查一下孙悟空的数学成绩将曹孟德同学的数学成绩变更为60分语文成绩变更为70分条件--name 曹孟德修改列--math chinese修改值--60, 70update exam set math 60, chinese 70 where name 曹孟德;先查一下曹孟德的数学和语文成绩将总成绩倒数前三的3位同学的数学成绩加上30分先查一下总成绩倒数前三的3位同学的数学成绩条件--chinses math english is not null查询列--name, math, chinese math english 总分排序--总分 asc分页--3select name, math, chinese math english 总分 from exam where chinese math english is not null order by 总分 asc limit 3;再将筛选逻辑直接写入update注意 update 中不能使用 select 定义的别名必须重复写完整表达式。条件--下文修改列--math修改值--math 30update exam set math math 30 where chinese math english is not null order by chinese math english asc limit 3;查看结果将所有同学的语文成绩更新为原来的2倍条件--无修改列--chinese修改值--chinese * 2update exam set chinese chinese * 2;先查一下所有同学的语文成绩4.Delete 删除语法务必加WHERE条件避免误删除全表数据。示例1删除孙悟空同学的考试成绩delete from exam where name 孙悟空;先查看原始数据示例2不加条件的删除整张表数据危险操作delete from t_delete;准备测试表、插入测试数据、查看测试表、删除整张表中的数据、查看结果。5.Truncate 截断表语法示例准备测试表、插入测试数据、查看测试表、查看建表结构AUTO_INCREMENT4截断表注意受影响的行数是0查看表中的数据查看表结构AUTO_INCREMENT已被重置为0继续写入数据、自增主键从1开如计数、再次查看表结构AUTO_INCREMENT26.插入查询结果语法示例删除表中的重复记录重复的数据只能有一份# 创建测试表并构造数据 mysql CREATE TABLE t_recored (id int, name varchar(20)); # 插⼊测试数据 INSERT INTO t_recored VALUES (100, aaa), (100, aaa), (200, bbb), (200, bbb), (200, bbb), (300, ccc); # 查看结果 mysql select * from t_recored; ------------ | id | name | ------------ | 100 | aaa | | 100 | aaa | | 200 | bbb | | 200 | bbb | | 200 | bbb | | 300 | ccc | ------------ 6 rows in set (0.00 sec)创建一张新表表结构与t_recored相同新表中没有记录原表中的记录去重后写入到新表查询新表中的记录实现去重。另一种写法若想将旧表与新表进行互换也可以rename table 对表重命名 原表名 to 新表名新表与原表重命名查询重命名后表中的记录实现需求且原来中的记录不受影响。7.聚合函数常用函数7.1COUNT1语法NULL 的数据不会计入结果2示例统计exam表中有多少记录使用* / 常量做统计select count(*) from exam; select count(1) from exam;统计有多少学生参加英语考试select count(english) from exam;统计语文成绩小于50分的学生个数select count(chinese) from exam where chinese 50;7.2SUM语法NULL 的数据不会计入结果不能统计非数值的列示例统计所有学生数学、英语成绩总分select sum(math) 数学总分, sum(english) 英语总分 from exam;不能统计非数值的列select sum(name) from exam;7.3AVG语法示例统计英语成绩的平均分select avg(english) 英语平均分 from exam;统计平均总分select avg(chinese math english) 平均总分 from exam;7.4MAX查询英语最高分select max(english) 英语最高分 from exam;7.5MIN查询70分以上的数学最低分select min(math) 数学70分以上最低分 from exam where math 70;查询数学成绩的最高分与英语成绩的最低分可以使用多个聚合函数select max(math) 数学最高分, min(english) 英语最低分 from exam;8.Group by分组查询-having子句GROUP BY 子句的作用是通过一定的规则将一个数据集划分成若干个小的分组然后针对若干个 分组进行数据处理比如使用聚合函数对分组进行统计。8.1语法8.2示例• 准备测试表及数据职员表emp列分别为id(编号)name(姓名)role(角色)salary(薪水)drop table if exists emp; create table emp ( id bigint primary key auto_increment, name varchar(20) not null, role varchar(20) not null, salary decimal(10, 2) not null ); insert into emp values (1, ⻢云, ⽼板, 1500000.00); insert into emp values (2, ⻢化腾, ⽼板, 1800000.00); insert into emp values (3, 鑫哥, 讲师, 10000.00); insert into emp values (4, 博哥, 讲师, 12000.00); insert into emp values (5, 平姐, 学管, 9000.00); insert into emp values (6, 莹姐, 学管, 8000.00); insert into emp values (7, 孙悟空, 游戏⻆⾊, 956.8); insert into emp values (8, 猪悟能, 游戏⻆⾊, 700.5); insert into emp values (9, 沙和尚, 游戏⻆⾊, 333.3); select * from emp;•统计每个角色的人数select role, count(*) from emp group by role;•统计每个角色的平均工资最高工资最低工资select role, avg(salary), max(salary), min(salary) from emp group by role;8.3having子句使用GROUP BY 对结果进行分组处理之后对分组的结果进行过滤时不能使用WHERE子句而要使用HAVING 子句。示例显示平均工资低于1500的角色和它的平均工资过滤条件平均工资低于1500基于分组结果角色的平均工资之上进行过滤select role, avg(salary) from emp group by role;select role, avg(salary) from emp group by role having avg(salary) 1500;9.内置函数日期函数字符串处理函数数学函数其他常用函数