10. DML语言
10.1 增加数据
value和values的区别(只在MySQL中成立)
插入多条数据时 value性能更好
插入一条数据时 values性能更好
实际没有太大的影响
10.1.1 全列插入
全列插入
insert into 表名 values(每一列的值按照顺序填写);
10.1.2 缺省插入
insert into 表名(字段1,字段2......) values(字段1的值,字段2的值.....);
注意点:
1、字段的书写顺序由自己定,可以不按照数据库表设计的顺序
2、插入的值与字段的顺序一一对应
3、主键设置成自动增长字段,该字段的值,可以填充0,null,default,都可以使得数据增长
4、字段设置有默认值的时候,该字段可以填充default或不写,值是默认值,如果填充的是null,字段值是空值
10.1.3 set插入
insert into 表名 set 字段1=值,字段2=值....;
不支持多数据插入
10.1.4 value和set的区别
方法一 支持多行插入 方法二 不支持多行插入
10.1.5 增加多条数据
全列插入的多条数据插入
insert into 表名 values(值1,值2......),(值1,值2......);
注意点:
1、多条数据之间以','隔开
缺省插入的多条插入
insert into staff(字段1,字段2,......) values(值1,值2.....),(值1,值2.....);
注意点:
1、字段的顺序不一定的,随意
2、字段与值需要一一对应
10.2 修改数据
10.2.1 单表数据修改
update 表名 set 字段名1=值1,字段2=值2... where 条件
10.3 删除数据
10.3.1 删除全部数据
delete from 表名
10.3.2 删除指定数据
delete from 表名 where 指定条件
10.3.3 truncate 清空数据
truncate 表名
注意点:
1、只是清空表数据,不会影响表结构
10.3.4 区别
1、delete可以使用条件筛选,truncate不可以
2、truncate的删除效率更高
3、delete 在删除的字段中存在自增长字段,删除之后的数据插入从断点开始(例如删除14,再次插入从15开始),truncate从1开
4、事务管理:truncate删除不能回滚,delete可以
10.4 查询数据
10.4.1 where引导的条件筛选语句
查询全部数据
select * from 表名
查询部分字段的全部数据
select 表名.字段名1,表名.字段名2 ... from 表名
查询符合条件的数据
select *(或者字段名) from 表名 where 条件
多个条件间用 ’and‘ ’or’ ‘not’ 连接
模糊查找
select 字段名 from 表名 where 字段名 like '%关键字_'
%表示任意多个字符
_表示任何一个字符
连续的数值范围使用between
不连续的数值范围使用in
空值查找 is null
条件查找中存在的运算符:
< > >= <= = != / and or not / is /between in
优先级:
小括号 not 比较运算符、逻辑运算符 and or(and比or先运算)
10.4.2 排序
select * from 表名 [筛选条件] order by 字段1 asc|desc,字段2 asc|desc... (降序 desc)
1.将数据按照字段1进行排序,如果字段1的数值相同,按照字段2进行排序 ...
2.默认的排序方式是升序
3.asc升序 desc降序
例:
select * from goods where goods.price > 9.9 order by goods.price,goods.id
10.4.3 聚合函数(快速统计)
1.查询总数
select count(*) from 表名
2.查询出商品的最高/最低价格
select max(字段名) from 表名
select min(字段名) from 表名
3.求平均结果
select avg(字段名) from 表名
select sum(字段名)/count(*) as 自定义命名 from 表名
10.4.4 分组 group by
将数据分组之后进行展示
select 需要展示的字段名 from 表名 group by 分组依据
只展示每个组的第一条数据
select group_concat(name) from 表名 group by 分组依据
展示全部的名字
group by
意义:将查询结果按照一个或者多个字段进行分组,字段值相同的是一组数据
group_concat:作为一个输出手段,用来放置每一种当前字段的值的集合
10.4.5 having
where 条件筛选,目前用where筛选原来的表中的原始数据
having 条件筛选,用于group by
10.4.6 分页
select * from 表名 limit start,count
limit start,count 从start位置开始,总共获取count条数据
10.5 多表查询
10.5.1 笛卡尔乘积现象
SELECT NAME,brandename FROM goods,brand
goods 17
brand 28
笛卡尔乘积现象:表1 M条,表2 N条,查询结果 M * N
现象的原因:没有指定连接条件
10.6 99语法和92语法
sql92 仅仅支持的是内连接
sql99 内连接 外连接(左右外连接,mysql全外连接中不支持) 交叉连接
select 查询字段 from 表名1 join 表名2... on 连接条件 where 筛选条件 group by order by
10.7 连接
10.7.1 外连接
select * from 表名1 left join 表名2
on 连接条件
以左表为主表,查询出右表与它有匹配的内容,如果有合并成一条数据,如果没有,则空值填充,所以位置有要求
以右表为主表,查询出左表与它有匹配的内容,如果有合并成一条数据,如果没有,则空值填充,所以位置有要求
不仅仅有双方都具有的数据,还包括主表特有的数据
10.8 自关联
select * from 表名 as 自定义名字1 inner join 表名 as 自定义名字2 on 连接条件 where 筛选条件;
10.9 子查询(内查询)
子查询:在一个select语句中,嵌入了另一个select语句,那么被嵌入的select语句称为子查询语句
主查询:主要的查询对象,第一条select语句
二者的关系:
1.子查询是嵌入到主查询中
2.子查询是辅助主查询的,要么充当条件,要么充当数据源
3.子查询是一个可以独立存在的语句,是一个完整的select语句
子查询的分类:
分类方法1(按照结果集的行列数)
1.标量子查询:子查询返回的结果是一个数据(一行一列)
2.列子查询:返回的结果是一列(一列多行)
3.行子查询:返回的结果是一行(一行多列)
4.表子查询:返回的结果是多行多列
分类方法2(按照子查询出现的位置)
1.select后面——标量子查询
2.from后面——表子查询
3.where或者having的后面
4.exists后面——相关子查询
子查询特点:
1.将子查询放在()中
2.子查询的优先级高于主查询
3.子查询一般放在条件的右侧,配合>,<等运算符
10.9.1 select后面
-- 查询每个品牌的信息以及品牌对应的商品个数
-- select brand.`id`,brand.`brandename`,count(*) from brand inner join goods on brand.`id`=goods.`brande_id` group by goods.`brande_id`;
1、查询每个品牌的信息
SELECT * FROM brand;
2、品牌对应的商品个数
SELECT goods.`brande_id`,COUNT(*),GROUP_CONCAT(goods.`name`) FROM goods GROUP BY goods.`brande_id`
3、将查询结果1和2进行拼接
SELECT brand.*,(
SELECT goods.`brande_id`,COUNT(*),GROUP_CONCAT(goods.`name`)
FROM goods
GROUP BY goods.`brande_id`
) FROM brand;
SELECT brand.*,(
SELECT COUNT(*) FROM goods WHERE goods.`brande_id`=brand.id
) AS 个数 FROM brand;
注意点:
select后面存在子查询的时候,只支持标量子查询(一行一列)
10.9.2 from后面
查询每个品牌的平均价格,价格等级
1、查询出每一个品牌的平均价格
SELECT goods.`brande_id`,AVG(goods.`price`) FROM goods GROUP BY goods.`brande_id`
2、将查询1的结果充当了数据源
SELECT * FROM (
SELECT goods.`brande_id`,AVG(goods.`price`) AS avg_price
FROM goods
GROUP BY goods.`brande_id`) AS avg_price
INNER JOIN
price_grade
ON
avg_price BETWEEN price_grade.`min_price` AND price_grade.`max_price`
注意点:
在from后面的子查询充当的是一个表(数据源),一定要给这个子查询结果起个名字,只有起名了,才能被当成一张表
10.9.3 exist
exists(完整的查询语句)
结果:
0或者1
SELECT * FROM brand WHERE brand.`brandename`='一小时'
SELECT EXISTS(SELECT * FROM brand WHERE brand.`brandename`='一小时')
SELECT EXISTS(SELECT * FROM brand WHERE brand.`brandename`='天猫一小时')
10.9.4 联合查询
查询语句1
union
查询语句2
注意点:
1.联合查询的过程中,查询的字段个数必须一致
2.尽量使得相同意义的字段放在同一列
3.使用union会自动去重
4.使用union all不会去重
10.10 查询的完整格式
select 字段1,字段2...
from 表名
where 条件筛选(在原始数据表中可以直接搜索到)
group by 分组条件1 desc|asc,分组条件2 desc|asc...
having 条件筛选
order by 字段1 asc|desc,字段2 asc|desc...
limit 起始位置,数量
执行顺序
from
where
group by
select distinct
having
order by
limit