表的增删查改

目录

插入数据

基本查询

更新数据

清空数据

聚合函数

group by子句

内置函数

基本查询练习

多表查询

子查询

合并查询

表的内外连接

---

插入数据

单行—全列插入

如下图，全列插入可以省略要在哪些列插入！

多行—指定列插入

如下图，可以在指定列中插入数据，也可以多行插入！

 

插入否则更新

对于主键和唯一键冲突，我们可以不让它报错，而是修改与之重复的哪一行数据

如下图，有2行被影响了，找到冲突的行是一行影响，更新数据又是一行影响

如下图，没有冲突，所以只有插入数据一行影响

如下图，有冲突，但是要修改的值与冲突的哪一行一模一样，所以是0行影响

替换

主键或唯一键没有冲突，则直接插入；冲突了，则删除那一行再插入

如下图，没有冲突，所以只有插入一行影响

如下图，冲突了，有删除和插入两行影响

基本查询

建立的表如下图

全列查询

如下图，* 表示全列查询，通常不建议使用 * 进行全列查询，原因如下：

查询的列越多，意味着需要传输的数据量越大；

可能会影响到索引的使用；

指定列查询

如下图，指定列的顺序不需要按定义表的顺序来！

查询字段为表达式

如下图，原始表中不包含字段，也能在查询结果添加字段

如下图，给每个人的数学成绩+10分

为查询结果指定别名

如下图，计算三门课的成绩总分

如下图，as为其指定别名，也可省略as

结果去重

如下图，用 distinct 给查询结果去重

如下图，当有多列时，就得整行都重复，才能去重！

 

 WHERE条件

如下图，查询英语成绩及格的同学

 

如下图，查询数学成绩为98分的同学

如下图，查询孙权同学的成绩

添加一行数据

如下图，查询英语成绩为NULL的同学成绩，不能用=，可以用<=>或 is

如下图，查询英语成绩不为空的同学成绩，用is not

如下图，查询数学成绩不等于98分的同学成绩

如下图，查询数学成绩在60分与70分之间的同学成绩

也能这样写，如下图

如下图，查询数学成绩为85分和英语成绩为67分的同学成绩

如下图，数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩

也能这样写，如下图

% 表示任意多个（包括 0 个）任意字符；_ 表示任意一个字符

如下图，查询姓孙的同学及孙某同学

如下图，查询语文成绩好于英语成绩的同学

查询总分在200分以下的同学，如下图，用别名在条件中去比较时，不认识'总分'这个别名，这是

因为得先按照条件筛选数据，然后再按照要求计算数据！所以别名不能用在where表达式中，同

时，也只能在select那里起别名

对于排序，可以用别名来排序，这是因为在进行排序前数据就已经筛选完成了，同时排序本质上也

属于计算！

如下图，查询语文成绩大于80并且不姓孙的同学成绩

如下图，孙某同学，否则要求总成绩 > 200 并且 语文成绩 < 数学成绩 并且 英语成绩 > 80  

对NULL的理解，NULL表示不存在，而0，1，""都是存在的！

结果排序

ASC为升序，DESC为降序，默认为升序！

NULL 视为比任何值都小，升序出现在最上面，降序出现在最下面！

注意：排序是整条记录排序，而不是单列的数据排序！！！

如下图，同学及数学成绩，按数学成绩升序显示

如下图，查询同学各门成绩，依次按数学降序，英语升序，语文升序的方式显示，即先按数学排

序，数学成绩相同时，就按英语排序，英语成绩也相同时，再按语文成绩排序！

如下图，查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩由高到低显示

筛选分页结果

如下图，查询数学成绩为前三名的同学成绩

如下图，查询数学成绩为第六名的同学成绩

也能用下图中的方式查询

如下图，如果期望要的记录条数不够，也只会显示剩下的，不会报错！

更新数据

如下图，将孙悟空同学的数学成绩变更为 80 分

如下图，将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分

如下图，将总成绩倒数前三的 3 位同学的数学成绩加上 30 分，MySQL没有+=，*=

如下图，将所有同学的语文成绩更新为原来的 2 倍

注意：更新全表的语句要慎用！！！

清空数据

如下图，清空整张表

如下图，表中数据虽然被清空，但是auto_increment项没有被重置，所以还是在其基础上自增长

删除id为6的同学的信息

截断表

也能用下图的方式清空表

同时，还会重置auto_increment项

truncate与delete的区别

只能对整表操作，不能像 DELETE 一样针对部分数据操作

实际上 MySQL 不对数据操作，所以比 DELETE 更快，但是TRUNCATE在删除数据的时候，并不

经过真正的事物，所以无法回滚

会重置 AUTO_INCREMENT 项

区别还有：

日志：承担很大的功能要求

bin log：几乎所有的sql操作，mysqld服务器都会给我们记录下来！该log用来进行多主机同步，

增量备份

redo log：mysql数据持久化和crash-safe功能

undo log：在事物中承担回滚的日志，数据操作回复功能

truncate会清空日志，而delete不会清空日志！！！

插入查询结果

如下图，是一张表，对它进行去重操作

如下图，建立和上面一张结构一模一样的表

如下图，再将去重的结果插入到新表中

如下图，然后再修改表名即可！

聚合函数

COUNT：查询到的数据的数量

如下图，统计班级共有多少同学，用 * 做统计，不受NULL影响

如下图，统计本次考试的英语成绩分数个数

SUM：查询到的数据的总和，不是数字没有意义

如下图，统计数学成绩总分

AVG：返回查询到的数据的平均值，不是数字没有意义

如下图，统计平均总分

MAX：返回查询到的数据的最大值，不是数字没有意义

如下图，返回英语最高分

MIN：返回查询到的数据的最小值，不是数字没有意义

如下图，返回 > 70 分以上的数学最低分

group by子句 

待要查询的3张表如下： 

如下图，显示每个部门的平均工资和最高工资

如下图，显示每个部门的每种岗位的平均工资和最低工资，先按部门分组，再按岗位分组

如下图，显示平均工资低于2000的部门和它的平均工资，这里不能用where，因为where比group

by 先执行，所以需要用having

where与having的区别

两者的执行位置，次序是不同的！where是过滤表中数据的；having是过滤分组数据的，两者不会

发生冲突！

内置函数

日期函数

current_date()：当前日期

current_time()：当前时间

current_timestamp()：当前时间戳

date(datetime)：返回datetime参数的日期部分

date_add(date，interval  数字 单位)日期或时间增加，interval可看成一个关键字

date_sub(date，interval  数字 单位)日期或时间减少

datediff(date1,date2)两个日期相差多少天

如下图，统计1分钟内发送的信息！now()：当前日期时间

字符串函数

charset(str)：返回字符串字符集

concat(str,...)：连接字符串

instr(string,substring)：返回substring在string中的位置，没有返回0

ucase(str)：转化成大写

lcase(str)：转化成小写

left(str,len)：从str字符串中的左边取len个字符

length(str)：str的长度，单位字节， 如果是多字节字符则计算多个字节数；如果是单字 节字符则

算作一个字节。比如：字母，数组算作一个字节，中文表示多个字节数

replace(str,search_str,replace_str)：在str中用replace_str替换search_str

strcmp(str1,str2)：逐字符比较字符串大小

substring(str,pos,[len])：从str中pos开始，取len个字符

ltrim(str)，rtrim(str)，trim(str)：去除前空格或后空格

显示student表中的信息，显示格式：“唐三藏的语文成绩：XXX分，数学成绩：XXX分，英语成

绩：XXX分”  

将EMP表中所有名字中有S的，把S替换成'上海'

以首字母小写的方式显示所有员工的姓名

数学函数

abs(num)：绝对值函数

bin(decimal_number)：十进制转换二进制

hex(decimalNumber)：转换成十六进制

conv(num,from_base,to_base)：进制转换

ceiling(num)：向上取整

floor(num)：向下取整

format(num,decimal_places)：格式化，保留小数位数，四舍五入

rand()：返回随机浮点数，范围[0.0,1.0]

mod(num,denominator)：取模，求余

其它函数

user() 查询当前用户

md5(str)对一个字符串进行md5摘要，摘要后得到一个32位字符串

database()显示当前正在使用的数据库

password()函数，MySQL数据库使用该函数对用户加密

 

ifnull（val1， val2） 如果val1为null，返回val2，否则返回val1的值

基本查询练习

查询工资高于2000或岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J  

按照部门号升序而雇员的工资降序排序

使用年薪进行降序排序

显示工资最高的员工的名字和工作岗位

显示工资高于平均工资的员工信息 

显示每种岗位的雇员总数，平均工资  

多表查询 

如下图，当我们要查找的数据涉及多张表时，可以采用多张表拼接的方式，也就是求其笛卡尔积！

这时，就可以把多张表当作一张表来处理！

显示部门号为10的部门名，员工名和工资

显示各个员工的姓名，工资，及工资级别

自连接 

同一张表自己连接自己，如下图 

显示员工FORD的上级领导的编号和姓名

子查询(嵌套查询)

单行子查询

返回一行记录的子查询

显示 SMITH 同一部门的员工

多行子查询 

返回多行记录的子查询

查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10自己的

显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号  

显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门的员工），

any表示只要满足其中一个就行！

多列子查询

返回多个列数据的子查询语句

查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人

在 from 子句中使用子查询

我们可以把查询到的所有的'记录'，来当作一张表！

显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资

查找每个部门工资最高的人的姓名、工资、部门、最高工资

显示每个部门的信息（部门名，编号，地址）和人员数量

合并查询 

union

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行

将工资大于25000或职位是MANAGER的人找出来

union all

该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行

表的内外连接

内连接

内连接实际上就是利用where子句对两种表形成的笛卡儿积进行筛选，上述的查询用到的连接都是

内连接！

语法：select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件；

显示SMITH的名字和部门名称

外连接

外连接分为左外连接和右外连接

如下图，是创建的两张表

 

左外连接

如果联合查询，左侧的表完全显示我们就说是左外连接，右侧的表数据不存在显示为NULL

语法：select 字段名 from 表名1 left join 表名2 on 连接条件

 

查询没有参加考试的同学信息

右外连接

如果联合查询，右侧的表完全显示我们就说是右外连接，左侧的表数据不存在显示为NULL

查询非法学生