mysql DML

查

普通查询

# 查询所有列
select * from student ;

# 查询指定列
select name from student ;

比较运算符包括 <,<=,=,>=,!=,<>,between,in,not in,is

# 条件查询，列与值严格对应 数字不必加单引号，字符串必须加单引号
select * from student where id=2 and name='李四'； # 交集
select * from student where id=2 or name = '张三'；# 并集
select * from student where servlet >70;

# in
select * from student where age in (11,12);
#in 等同于
select * from student where age =11 or age = 12;
# not in,不等于
select * from student where age not in (11,12);

# 判空条件
select * from student where address is null;
select * from student where address='';
select * from student where address is null or address=''; 
# or可用||替代
select * from student where address is null || address=''; 

逻辑运算符包括not,!,or,||,and,&&

# and可用&&代替
select * from student where js between 75 and 90; #（包前包后）
select * from student where js>=75 and js<=90;
select * from student where js>=75 && js<=90;

通配符包括like模糊匹配，%通配任意字符，_通配单一字符

select * form user where 1; # where 1,1为true，0为false，因为所有行都存在都为真，所以都会查询到，where 0 则一行都查询不到

# 广义投影，投影就是上面的取列，广义投影是取列同时做运算，如下：age +1 为运算
select uid,name,age+1 from user where 1;

内置方法

• floor

floor 取余数（如1.3返回1,1.9也返回1）

select num/10 from mian; 把数字除以10得到余数
# 用floor 取余得到整数，在乘以10变回原来的大小
select floor(num/10)*10 from mian;先把数字变成小数，然后floor取余，在乘以10变回原
来的值。
# 应用：update修改(题目）
update mian set num=floor(num/10)*10 where num between 20 and 39;

• substring

substring 截取字符串

select goods_id,substring(goods_name,4) from goods where goods_name like '诺基亚%'
# 显示goods_id，goods_name(表中goods_name中诺基亚开头的行的第四个字符开始往后的部分)

concat

concat 字符串拼接

select goods_id,concat('htc',substring(goods_name,4)) from goods where goods_name
 like '诺基亚%';  
 # 然后将htc与截取的部分拼接

• max() 求最大
• count() 统计行数
• avg()求平均值
• min()求最小
• sum()求总和

进阶

#

# group by 分组 #根据id分组，求平均数
select avg(shop_price) from goods group by cat_id;

# order by排序 升序：asc（默认） 降序：desc
# 升序：数值：递增，字母：自然顺序（a-z）反之降序
# 按价格排序（升序）
select goods_id,goods_name,shop_price from goods order by shop_price ;
# 按价格降序（降序）
select goods_id,goods_name,shop_price from goods order by shop_price desc;
# 多序排列（用，隔开）
select goods_id,goods_name,shop_price from goods order  by shop_price asc,goods_id desc,
cat_id desc;

# limit 限制返回记录条数
# 按商店价格由高到低排序，取出排名前三的
select goods_id,goods_name,shop_price from goods order by desc limit 0,3;
# 按商店价格由高到低排序，取出第四第五行
select goods_id,goods_name,shop_price from goods order by desc limit 3,2；
3代表偏移量，也就是越过前三行，2表示取出条目，取出两行内容

# having HAVING 是 MySQL 中用于筛选分组数据的子句，它的作用是在 GROUP BY 语句的基础上对分组数据进行进一步过滤。必须在group by之后，数据多的场景HAVING 子句的性能可能较差
# 对分组数据进行条件判断，筛选出满足条件的分组。
# 进一步优化查询性能，减少数据检索的次数和范围。
#result 表中 查询出2门及2门以上不及格者的平均成绩
select name,sum(score<60) as gk,avg(score) from result group by name having gk>=2;
# 首先group by安装name分组，然后sum按分组进行统计score<60的出的结果重命名为gk，having进一步安装gk过滤

子查询

where

引子

# 查询每个栏目下最新的产品（goods_id最大即为最新）
selet max(goods_id),goods_name,shop_price from goods group by cat_id;
### 此时，会出现错误，goods_id最大没错，但名字和价格却和goods_id不匹配，因为名字和价格是按照goods_id栏目里第一个取出的，并不是取出的goods_id的实际名字和价格。
# 解法：那应该先给表格按照goods_id排序，那样最大的goods_id对应的名字和价格就是匹配的
selet max(goods_id),goods_name,shop_price from goods order by goods_id desc group by cat_id
### 但是，上面这种结构属于语法错误（order在group前面）
### 注意：where,group by,having,order by,limit  必须按照此顺序写
# 但反过来写发现还是不匹配，所以要用到where子查询

什么是where子查询

# 查询最新的产品（不按组）
select goods_id,goods_name,shop_price from goods order by goods_id desc limit 0,1；
# 可以换一种写法，不用limit
select goods_id,goods_name,shop_price from goods where goods_id=(
select max(goods_id) from goods); # 因为查询的是最新的产品也就是goods_id最大，当按照goods_id从大到小排序后，只要把最大的一行取出就对了，所有又加了一个子查询。

解决上面的问题

# 1.先把每个栏目下goods_id 最大的取出来
select max(goods_id),goods_name,cat_id from goods group by cat_id;
# 2.把最大的信息取出来（把上面的作为子查询）
select cat_id,goods_id,goods_name from goods where goods_id in (select max(goods_id) from goods group by cat_id);

from

select cat_id,goods_id,goods_name from goods where goods_id between 20 and 25
# 上面的查询结果作为一张新表tmp 再次被查询)
select cat_id,goods_name from (select cat_id,goods_id,goods_name from goods where goods_id
 between 20 and 25) as tmp group by cat_id;
# 和where不同在于where子查询作为一个数一个条件再次应用，而from作为一张表

exists

# category表是商品的类别对应cat_id goods表是商品详情
# 问题：将goods表中的商品名查找出相应的商品类别
# 思路：两张表中相同的是cat_id，只需把goods表中的cat_id与category表中的相同的显示出来
select * from category where exists (select * from goods where goods.cat_id=
category.cat_id); #显示category表的全部，条件是两张表cat_id相同的部分goods.cat_id表示 goods表的cat_id列

连接

内连接

boy表

hid	name
A	屌丝
B	杨过
C	郭靖

girl表

hid	name
B	小龙女
C	黄蓉
D	宅女

# 把两张表做比对，把boy表和girl表中相同的部分作为条件来比对其他部分
# 如图，相同的部分为hid作为条件，来比较name
select boy.hid,boy.bname,girl.hid,girl.gname from boy inner join girl on boy.hid=girl.hid;

# boy.hid,boy.bname   显示boy表中的hid和name 用逗号隔开 之所以hid和name之前都有点与前面的表名连接是因为boy表和girl表都有hid和name,所以用来区分，如果都不相同，则直接写inner join 表示把两张表连接起来.
# on boy.hid=girl.hid  条件为boy表中的hid 和girl表中的hid相同部分

## 多次链接
## 多次链接
# 查出 2006-6-1 到2006-7-1之间举行的所有比赛
select mid,t.tname as h,mres,t2.tname as g,matime from 
m inner join t
on m.hid=t.tid
inner join t as t2
on m.gid=t2.tid
where matime between '2006-06-1' and '2006-07-01';
# 必须给第二张表改名，否则多次链接不知道连的是哪个t表

左右连接

select boy.hid,boy.bname,girl.hid,girl.gname from boy left join girl on boy.hid=girl.hid;
# 和内连接区别在于inner换为left 右连接为right
# 左连接以左边数字为准，查询右边相关数据，左边表所以会全部显示，而右边标如果有查不到的补null
# 右连接与左连接相同
# 如：取出所有商品的商品名，栏目名，价格
select goods_name,cat_name,shop_price from 
goods left join category            
on goods.cat_id=category.cat_id;
# 取出第4个栏目下的商品的商品名,栏目名,价格
select goods_name,cat_name,shop_price from 
ecs_goods left join ecs_category
on ecs_goods.cat_id=ecs_category.cat_id
where goods.cat_id = 4;
# 前面已经把两张表合成一张了，后面只需查

union

# union查询就是把2条或多条sql的查询结果合并成1个结果集，与连接不同的是，连接是左右，而union是上下

# union语句必须满足一个条件；各语句取出的列数相同，但列名称未必要一致，显示结果以前面的名称
为准

# 注意：使用union时，完全相同的行将会被合并，合并是比较耗时的工作，一般不用union进行合并，使用"union all"可以避免合并，可以提高速度

# order by 只能用在union语句的最后，不能再两个子句中使用。
select goods_id,cat_id,goods_name from goods where cat_id=2 union select goods_id, cat_id,goods_name from goods2 where cat_id=4;

增

# 插入所有字段，一定要依次按顺序插入
insert into student values(1,'张三'，‘男’，20)；

# 插入部分字段
insert into student （id,name）values(2,'李四')；

改

# 修改所有数据（建议少用）
update student set gender=‘女’；

# 条件修改 修改id为1 的学生为男
update student set gender='男' where id =1;
# 多个字段用逗号分开
update student set gender=‘男’，age=30 where id=2;

修改mysql user表

# 修改mysql 用户信息 修改李四的密码
update auth.users set user_password=user_passwd=password('1234') where user_name='lisi';
# 刷新可生效 	
flush privileges;

删

# 删除所有数据（谨慎！）
delete from student；
# 条件删除
delete from student where id=2;