MySQL：基础操作(增删查改)

目录一、库的操作创建数据库查看数据库显示创建语句修改数据库删除数据库备份和恢复二、表的操作创建表查看表结构修改表删除表三、表的增删查改新增数据插入否则更新插入查询的结果查找数据为查询结果指定别名结果去重where 条件结果排序筛选分页结果聚合函数查询分组查询HAVING 条件修改数据替换数据修改数据删除数据截断表SQL语句中不区分大小写下面都以小写为主下面的SQL语句第一行是最简便的第二行是具体的包含可选的内容[ ] 中代表的是可选项一、库的操作创建数据库创建一个新的数据库create database 库名; create database [if not exists] 库名 [charset字符集] [collate校验规则];charset用于指定数据库所采用的编码格式字符集collate用于指定数据库所采用的校验规则如果创建数据库时未指明数据库的编码格式或校验规则则默认使用MySQL配置文件中对应的编码格式和校验规则默认的编码格式是utf8默认的校验规则是utf8_general_ci不指明数据库的编码格式和校验规则通过charset和collate分别指明数据库的编码格式和校验规则查看数据库可以查看系统中所有的数据库show databases;显示创建语句可以查看对应数据库的创建语句show create database 数据库名注意①数据库的名字加上反引号是为了防止使用的数据库名与关键字冲突②**/*!40100 DEFAULT CHARACTER SET utf8 ***/不是注释它表示当前MySQL版本如果大于4.10则执行后面的SQL语句修改数据库可以修改数据库的字符集或校验规则alter database 库名 [charset字符集] [collate校验规则];需要注意的是MySQL数据库不支持修改库名db1的字符集、校验规则原本是默认的现在将数据库的字符集改为gbk将数据库的校验规则改为gbk_bin删除数据库删除一个现有的数据库drop database 库名; drop database [if exists] 库名;删除数据库后该数据库对应的文件夹就被删除了并且删除数据库后该数据库下的所有表也都会被级联删除因此不要随意删除数据库备份和恢复数据库的备份mysqldump -P 端口号 -u 用户名 -p 密码 -B 数据库名1 数据库名2 ... 数据库备份存储的文件路径先创建一个数据库接着在该数据库中创建两个表student、teacher并插入一些数据这时在命令行中执行如下命令即可将该数据库进行备份并指定将备份后产生的文件存放在当前目录下back.sql文件即可看到文件中的内容实际就是我们在该数据库中执行的各种SQL命令包括创建数据库、创建表、插入数据等SQL语句数据库恢复使用如下命令即可对指定数据库进行恢复source 数据库备份的文件路径删除数据库后执行的代码此时就会恢复刚刚删除的数据库且内容也与删除前完全一样表的备份mysqldump -P 端口号 -u 用户名 -p 密码 数据库名 表名1 表名2 ... 表备份存储的文件路径表的恢复source 表备份的文件路径表的备份与恢复类比上面的数据库的备份与恢复使用方式是相同的二、表的操作DDL(数据定义语言)比如建表、删表、该表、新增列、删除列等DML(数据操作语言)比如插入记录、删除记录、修改记录等下面都是表的DDL操作创建表create table 表名( 列名1 类型1 [comment 注释信息], 列名2 类型2 [comment 注释信息], 列名3 类型3 [comment 注释信息] );[charset字符集] [collate校验规则] [engine存储引擎];comment表示对指定列添加注释信息建表时没有指定使用哪种存储引擎那么就会默认使用InnoDB存储引擎查看表结构desc 表名可以查看表的结构①Field表示该字段的名字②Type表示该字段的类型③Null表示该字段是否允许为空④Key表示索引类型比如主键索引为PRI⑤Default表示该字段的默认值⑥Extra表示该字段的额外信息说明show create table [表名]G可以查看创建表时的相关细节修改表新增列 alter table 表名 add 新增列名 新增列的属性; 修改列的属性 alter table 表名 modify 列名 修改后的列属性; 删除列 alter table 表名 drop 列名; 修改表名 alter table 表名 rename 新表名; 修改列名和属性 alter table 表名 change 列名 新列名 新列属性;新增列如果想新增到哪一列之后可以在后面加上 after 列名alter table student add namevarchar(30) comment ‘姓名’ after sex;上述语句表示新增一列 name 列并将 name 列放在 sex 列的后面新增列后可能还需要对原来插入的记录进行修改因为假设原有2条数据此时新增一列那么原有的2条数据中新增的那一列数据就默认为空想将插入的列插入到第一列就在SQL语句的最后加上first即可删除表drop删除表结构会直接删除整个表内存中就没有这个表了delete删除只删除数据不删除结构delete完表还在只是为空了删除表结构 drop table [if exists] 表名; 删除表数据 delete from 表名; 删除部分数据 delete from 表名 where ...;三、表的增删查改表的增删查改简称CRUDCreate新增Retrieve查找Update修改Delete删除CRUD的操作对象是对表当中的数据是典型的DMLData Manipulation Language数据操作语言新增数据insert into 表名 [列名] values [数据];新增数据时如果在values前不加列名就表示按照表中默认的列顺序进行全列插入假设表的结构如下单行数据 全列插入不加列名全列插入多行数据 指定列插入用insert语句也可以一次向表中插入多条记录插入的多条记录之间使用逗号隔开并且插入记录时可以只指定某些列进行插入插入否则更新向表中插入记录时如果待插入记录中的主键或唯一键已经存在那么就会因为主键冲突或唯一键冲突导致插入失败如果表中没有冲突数据则直接插入数据如果表中有冲突数据则将表中的数据进行更新insert into 表名 [列名] values [数据] on duplicate key update [column1value1, ...];column1value1表示有冲突时需要更新的列值下图的含义就是如果出现主键或唯一键冲突则将表中冲突记录的学号和姓名进行更新执行插入否则更新的SQL后可以通过受影响的数据行数来判断本次数据的插入情况0 rows affected表中有冲突数据但冲突数据的值和指定更新的值相同1 row affected表中没有冲突数据数据直接被插入2 rows affected表中有冲突数据并且数据已经被更新插入查询的结果表示需要插入 select 出来的结果insert [into] 表名 [列名] select ... [where...] [order by...] [limit...];删除表中重复的记录重复的数据只能有一份假设当前表数据如下要求删除测试表中重复的数据思路如下①创建一张临时表表的结构和数据与原表相同②去重的方式查询并插入③将原表改名再将该表重命名为原表的名称从而完成去重操作步骤如下①创建临时表 no_duplicate 的时候可以借助like进行创建②通过插入查询语句将去重查询后的结果插入到临时表中临时表成功去重③将原表改名再将临时表改名为原表名称完成去重操作查找数据select {*/列名/表达式} from 表名 [where] [order by] [limit];其中select后面是*就表示全列查询如果是列名就对指定的列进行查询多个列用逗号隔开如果是表达式表达式中可以包含多个表中已有的字段例如列有数学、英语成绩表达式就可以数学英语成绩从而计算出更多有意义的数据后面的where、order by、limit都是可选项{ }中的 / 代表可以选择其中某一条语句全列查询在查询数据时直接用*表示进行全列查询这时将会显示被筛选出来的记录的所有列信息指定列查询查询数据时也可以只对指定的列进行查询这时将需要查询的列在列表列出即可表示查找指定的 name 和 math 列查询字段为表达式我们也可以将表达式罗列到列表中此时就会多一个列列名为11每行数据都是11的值这种方式可以帮助我们解决很多事情例如如果有一个成绩表我们想知道语文、数学和英语的总分表此时就可以将 select 后面的表达式写成 chinese math english为查询结果指定别名select 列名 [as] 别名 [, ...] from 表名;as可加可不加也可以指定多个列的别名在后面加逗号继续起别名即可所以上面查询字段为表达式是就可以将语数英的成绩综合改名为总分此时表中的该列就名为总分结果去重如果想要对查询结果进行去重操作可以在SQL中的select后面带上distinctselect distinct 列名 from 表名;这种操作就用于查询的数据有重复值我们想要将这些数据去重时的操作where 条件如果在查询数据时没有指定where子句那么会直接将表中所有的记录作为数据源来依次执行select语句如果在查询数据时指定了where子句那么在查询数据时会先根据where子句筛选出符合条件的记录然后将符合条件的记录作为数据源来依次执行select语句where子句中可以指明一个或多个筛选条件各个筛选条件之间用逻辑运算符and或or进行关联下面是where子句中常用的逻辑运算符和比较运算符逻辑运算符比较运算符下面举几个使用 where 字句的样例①查询语文成绩在80到100分的同学及其语文成绩下面这两种方式都可以select name, chinese from exam_result where chinese between 80 and 100; select name, chinese from exam_result where chinese 80 and chinese 100;②查询英语成绩是59或99分的同学及其英语成绩select name, english from exam_result where english59 or english99; select name, english from exam_result where english in (59,99);③查询姓王的同学/查询王某同学查询姓王的同学 select name from exam_result where name like 王%; 查询王某同学 select name from exam_result where name like 王_;④NULL的查询查询QQ号已知的同学/查询QQ号未知的同学查询QQ号已知的同学 select name, qq from students where qq is not null; 查询QQ号未知的同学 select name, qq from students where qqnull;在与 null 值作比较的时候应该使用 运算符使用 运算符无法得到正确的查询结果⑤查询语文和数学总分大于180分的同学select name, chinesemath 总分 from exam_result where chinesemath180;需要注意的是在where子句中不能使用select中指定的别名查询数据时是先根据where子句筛选出符合条件的记录然后再将符合条件的记录作为数据源来依次执行select语句所以在上面查询总分时where后面不能写成 总分180结果排序select {*/列名} from 表名 where order by 列名 [asc/desc];asc和desc分别代表的是排升序和排降序不特殊指明就默认为 asc 排升序注意null值视为比任何值都小因此排升序时出现在最上面order by子句中可以指明按照多个字段进行排序每个字段都可以指明按照升序或降序进行排序各个字段之间使用逗号隔开排序优先级与书写顺序相同①查询同学的各门成绩依次按数学降序、英语升序显示select name, math, english from exam_result order by math desc, english asc;上述SQL中当两条记录的数学成绩相同时就会按照英语成绩进行排序如果这两条记录的英语成绩也相同就会继续按照语文成绩进行排序以此类推②查询同学及其总分按总分降序显示select name, chinesemath 总分 from exam_result order by chinesemath desc; select name, chinesemath 总分 from exam_result order by 总分 desc;查询数据时是先根据where子句筛选出符合条件的记录然后再将符合条件的记录作为数据源来依次执行select语句最后再通过order by子句对select语句的执行结果进行排序order by子句的执行是在select语句之后的所以在order by子句中可以使用别名所以上述SQL可以使用 总分 desc 这样的方式③查询姓孙的同学或姓曹的同学及其数学成绩按数学成绩降序显示像这种长的题目先完成查询再进行排序查询 select name, math from exam_result where name like 孙% or name like 曹% 排序 order by math desc; 查询和排序合并起来 select name, math from exam_result where name like 孙% or name like 曹% order by math desc;筛选分页结果从第0条记录开始向后筛选出n条记录select {*/列名} from 表名 [where...] [order by] limit n;从第s条记录开始向后筛选出n条记录select {*/列名} from 表名 [where...] [order by] limit n offset s;SQL中各语句的执行顺序为where、select、order by、limitlimit子句在筛选记录时记录的下标从0开始如果从表中筛选出的记录不足n个则筛选出几个就显示几个按id进行分页每页3条记录分别显示第1、2、3页第一页筛选3条记录(从0开始) select * from exam_result limit 3 offset 0; 第二页筛选3条记录(从3开始) select * from exam_result limit 3 offset 3; 第三页筛选3条记录(从6开始不够就输出现有的数量) select * from exam_result limit 3 offset 6;从表中筛选出的记录不足n个则筛选出几个就显示几个聚合函数查询聚合函数可以在select语句中使用此时select每处理一条记录时都会将对应的参数传递给这些聚合函数使用*做统计可以获取表中的记录个数select count(*) from students;使用表达式做统计在select语句中使用count函数并将表达式作为参数传递给count函数这时也可以统计出表中的记录条数select count(1) from students;这种写法相当于下图自行新增了一列列名为特定表达式的列我们就是在用count函数统计该列中有多少个数据等价于统计表中有多少条记录统计班级收集的QQ号有多少个select count(qq) from students;如果count函数的参数是一个确定的列名那么count函数将会忽略该列中的NULL值统计本次考试数学成绩的分数个数去重select count(distinct math) from students;在count中加上 distinct 修饰能够完成去重的操作统计平均总分select avg(chinesemath) 平均总分 from exam_results;返回70分以上的英语最低分结合where和聚合函数查询select min(english) from exam_result where english70;分组查询select 列名 from 表名 [where...] group by 列名 [order by...] [limit...];查询SQL中各语句的执行顺序为where、group by、select、order by、limitgroup by后面的列名表示按照指定列进行分组查询显示每个部门的平均工资和最高工资题目要求的是显示每个部门所以就是按照部门分组查询select deptno, avg(sal) 平均工资, max(sal) 最高工资 from emp group by deptno显示每个部门的每种岗位的平均工资和最低工资题目要求的是显示每个部门所以就是按照部门和岗位分组查询select deptno, avg(sal) 平均工资, min(sal) 最低工资 from emp group by deptno job;HAVING 条件select ... from 表名 [where...] [group by...] [having...] [order by...] [limit...];SQL中各语句的执行顺序为where、group by、select、having、order by、limithaving子句中可以指明一个或多个筛选条件having子句和where子句的区别①where子句放在表名后面而having子句必须搭配group by子句使用放在group by子句的后面②where子句是对整表的数据进行筛选having子句是对分组后的数据进行筛选③where子句中不能使用聚合函数和别名而having子句中可以使用聚合函数和别名统计每个部门的平均工资select deptno, avg(sal) 平均工资 from emp group by deptno;显示平均工资低于2000的部门和它的平均工资select deptno, avg(sal) 平均工资 from emp group by deptno having 平均工资2000;修改数据替换数据如果表中没有冲突数据则直接插入数据如果表中有冲突数据则先将表中的冲突数据删除然后再插入数据replace into 表名 [列名] values [数据];例如下图所示的语句表达的含义就是如果出现冲突直接将冲突数据删除再替换如果没冲突直接插入执行替换数据的SQL后也可以通过受影响的数据行数来判断本次数据的插入情况1 row affected表中没有冲突数据数据直接被插入2 rows affected表中有冲突数据冲突数据被删除后重新插入修改数据update 表名 set 列名数值 [...] [where...] [order by...] [limit...];在修改数据之前需要先找到待修改的记录update语句中的where、order by和limit就是用来定位数据的下面举例说明①将孙悟空同学的数学成绩修改为80分update exam_result set math80 where name孙悟空;②将总成绩倒数前三的3位同学的数学成绩加上30分update exam_result set mathmath30 order by chinesemath asc limit 3;③将所有同学的语文成绩修改为原来的2倍update exam_result set chinesechinese*2;删除数据delete from 表名 [where...] [order by...] [limit...];在删除数据之前需要先找到待删除的记录delete语句中的where、order by和limit就是用来定位数据的①删除孙悟空同学的考试成绩delete from exam_result where name孙悟空;②删除整张表数据delete from exam_result;假设表中有自增长的主键id原本有三条数据id分别是1,2,3删除整张表数据后再插入数据不指明 id 是多少时默认从4开始这是有一个**AUTO_INCREMENTn?**的字段表示下一次插入数据时自增长字段的值应该是 n通过 show create table 表名 可以看到这个字段截断表truncate [table] 表名;①truncate只能对整表操作不能像delete一样针对部分数据操作②truncate实际上不对数据操作所以比delete更快③truncate在删除数据时不经过真正的事务所以无法回滚④truncate会重置AUTO_INCREMENTn字段使用截断表的操作时删除完数据后重新插入数据对应的自增长id值是重新从1开始增长数据库的增删改查(CURD)操作到此结束