数据的增删改查↓↓↓
一、增
-
增加一条数据:
语法:insert into 表名称 [(column1,column2,…)] values(value1,value2,…) -
增加多条数据:
语法:insert into 表名称 [(column1,column2,…)] values(value1,value2,…),(value1,value2,…),…;> insert into commoditytype values(1,"书籍"); > insert into commoditytype values(3,"文具"); > insert into commoditytype values(null,"玩具"); > insert into commoditytype (ct_name) values("服装"); > insert into commoditytype (ct_name,ct_id) values("食物",null); > insert into commoditytype (ct_id,ct_name) values(2,"饮料"); > insert into commodity values (1,"mysql学习","China",1,30,50,80); > insert into commodity values (null,"天天向上","China",1,30,50,100); > insert into commodity values (null,"python学习","China",1,30,50,default); > insert into commodity values (null,"python学习","China",1,30,50,null);#如给空就是null,不会写默认值,要默认值必须是default > insert into commodity values (null,"python学习","China",1,30,50);#会报错,列数不匹配 #插入前 desc 表名;看结构 > insert into customer values(1,'哪吒','123456','男',"陈塘关"), (2,"大圣","123123","男","花果山"), (3,"白素贞","123321","女","西湖"), (4,"杨戬","123111","男","天庭"); > insert into orders values(1,1,1,1),(2,3,3,5),(3,2,2,3);
注意:
- 如果列名称[(column1,column2,…)]没写,那么value后面的这个值,必须依次与每一列对应,否则长度对不上
- 如果列名称[(column1,column2,…)]写了,后面的值要与前面的值一一对应,不要求必须是与表格顺序一定相同
二、删
-
delete
语法:delete from 表名称 [where 条件] #如果不写条件会删除所有 -
truncate
语法:truncate table 表名 #删除所有信息,包括自增的主键值#如:删除杨戬 > delete from customer where cu_id=4; > delete from customer where cu_name='白素贞’;#这种删除要慎用,名字可能有重名 > delete from commodity where c_num>90; #区间删除
三、改
- 更新数据
语法:update 表名称 set column1=value1,column2=value2,… [where 条件]#如: ->update commodity set c_num=70 where c_id=6; ->update commodity set c_num=70,c_outprice=40 where c_id=6;
四、查
-
单表查询
查询所有数据:
语法:select * from 表名称#如: > select c_id,c_name from commodity; > truncate commodity; #操作:将商品表清空,插入大量myshop数据,把1改为玩具 2文具 3书籍 > update commoditytype set ct_name='玩具' where ct_id=1; > update commoditytype set ct_name='文具' where ct_id=2; > update commoditytype set ct_name='书籍' where ct_id=3;
-
查询常量
> select 'hello'; > select 'hello' as '你好';
-
自定义显示名称
> select c_name as '商品名称',c_inprice as '进价',c_outprice as '售价' from commodity;
-
查询时外键作为条件
#查询所有玩具 > select * from commodity where c_type=1; #当不知道玩具类型号时候的子查询 > select * from commodity where c_type=(select ct_id from commoditytype where ct_name='玩具');
-
查询所有商品筛选后的数据(distinct 用于去重)
->select c_type as '类型' from commodity; #没有去重效果 ->select distinct c_type as '类型' from commodity; #有去重效果
-
四则运算
+、-、*、/#查看每个商品的利润,显示:名称、进价、售价、利润 > select c_name as '商品名称',c_inprice as '进价',c_outprice as '售价',c_outprice-c_inprice as '利润' from commodity; #查看每个商品的总成本,显示:名称、总成本 > select c_name as '商品名称',c_inprice*c_num as '总成本' from commodity;
-
比较运算符
>、 <、 =、 >=、 <=、 !=#查询进价大于100的 > select * from commodity where c_inprice>100; #查询利润大于100的 > select * from commodity where (c_outprice-c_inprice)>100; > select c_name,(c_outprice-c_inprice) as '利润' from commodity where (c_outprice-c_inprice)>100; #查询利润大于成本的 > select c_name,(c_outprice-c_inprice) as '利润',c_inprice as '成本' from commodity where (c_outprice-c_inprice)>c_inprice; > select c_name,(c_outprice-c_inprice) as '利润',c_inprice as '成本' from commodity where (c_outprice-c_inprice)/c_inprice>1;
-
逻辑运算
and、 or、 not#查询产地是中国,且库存大于50 > select c_name,c_madein,c_num from commodity where c_madein='中国' and c_num>50; #查询产地是中国,且库存在50到100之间的 > select c_name,c_madein,c_num from commodity where c_madein='中国' and c_num>=50 and c_num<=100; > select c_name,c_madein,c_num from commodity where c_madein='中国' and c_num between 50 and 100; #between and 在...之间 #查询产地是中国,且库存在不在50到100之间的 > select c_name,c_madein,c_num from commodity where c_madein='中国' and c_num not between 50 and 100; #因为查询要求,临时增加 > insert into commoditytype values(4,'服装'); > insert into commoditytype values(5,'食品'); > insert into commodity values(null,"T恤","China",4,50,60,default); > insert into commodity values(null,"棉袄","China",4,50,40,default); > insert into commodity values(null,"海鲜","东北",5,60,80,default); > insert into commodity values(null,"烧烤","上海",5,70,60,default); #查询商品类型是服装或食品 > select c_id,c_name,c_type from commodity where c_type=4 or c_type=5; > select c_id,c_name,c_type from commodity where c_type in (4,5); #关键词 in 在什么里面 #查询产地是日本或美国的 > select c_id,c_name,c_madein from commodity where c_madein in('美国','日本');
-
值的匹配(模糊查询)
模糊查询:like 、通配符:_ 一个字符 ,% 多个字符 (#一般和通配符一块儿使用)
例如:_小:表示两个字符 x小 、%小:表示多个字符 xxxxxxx…小 、%小% 表示字符中有小字#查询产地是_国的 ->select * from commodity where c_madein like '_国'; #查询商品结尾是"小"字的 ->select * from commodity where c_name like '%小'; #查询商品中包含"具"字的 ->select * from commodity where c_name like '%具%'; #查询商品开头是"乐"字的 ->select * from commodity where c_name like '乐%'; #查出商品中同时包含'中'和'H'的 ->select * from commodity where c_name like '%中%H%' or '%H%中%'; ->select * from commodity where c_name like '%中%' and c_name like '%H%';
-
排序
语法:select 要查询的内容 from 表名称 order by 列名称
order by 列名称 asc 升序(默认)(ascending)
order by 列名称 desc 降序(descending)#查询所有商品的售价,降序排序 > select c_name,c_outprice from commodity order by c_outprice desc; #查询产地是中国的,所有商品的售价,降序排序 > select c_name,c_madein,c_outprice from commodity where c_madein='中国' order by c_outprice desc;
-
限制查询条数
语法:limit [起始值,]总条数 #起始值默认0#查询三条数据 > select * from commodity limit 3; #从第二条开始,一共查询三条数据 > select * from commodity limit 2,3;
-
聚合函数
count(XXX) 计算XXX的数量#得到所有数据的数量 > select count(*) from commodity; > select (select count(*) from commodity)*2 as "aaa"; #可以当成一个数字来计算,记得前面还要一个select #查询所有玩具的数量 > select count(*) as '玩具的数量' from commodity where c_type=1; > select count(c_name) as '玩具的数量' from commodity where c_type=1;
avg() 求平均值
#求进价的均值 > select avg(c_inprice) as '进价均值' from commodity;
sum() 求和
#所有商品的库存量 > select sum(c_num) as '商品库存量' from commodity;
max() 求最大值
#售价的最大值 > select max(c_outprice) as '售价最大值' from commodity;
min() 求最小值
#售价的最小值 > select min(c_outprice) as '售价最小值' from commodity;
注意:这些函数都是默认 is not null 处理的,也就是说,如果某个参与计算的值如果本身是null,计算的时候是不被算入计算行列的
-
having 关键词
where和having的区别:- where 筛选针对已经有的数据,也就是数据库中原本就有的数据
- having 筛选聚合函数处理过的数据
- where 的优先级大于 having
#按照商品类型看进价均价 > select c_type,avg(c_inprice) from commodity group by c_type; #按照商品类型看均价大于50的 > select c_type,avg(c_inprice) from commodity group by c_type having avg(c_inprice)>50; #小练习 > select c_type,avg(c_inprice) from commodity; > select c_type,c_inprice from commodity where c_inprice>100; > select c_type,avg(c_inprice) from commodity where c_inprice>100; > select c_type,avg(c_inprice) from commodity group by c_type; #进价大于100的分类后求均值 > select c_type,avg(c_inprice) from commodity where c_inprice>100 group by c_type;
-
联表查询(多表查询)
#查询出所有玩具 > select * from commodity where c_type=1; > select * from commodity where c_type=(select ct_id from commoditytype where ct_name='玩具');
内连接查询:
语法:select xxx from 表1 inner join 表2 on 条件#查询出所有玩具 > select * from commodity inner join commoditytype on c_type=ct_id where ct_name='玩具'; #相当于把两张表拼接到一起 > select c_id,c_name,ct_name from commodity inner join commoditytype on c_type=ct_id where ct_name='玩具';
外连接查询:
left join 左连接,以左边为主表
right join 右连接,以右边为主表
#主表会全部显示,查不到为null
#去Navicat在commoditytype表中添加两类6 电器、7 交通工具,方便下面左右表为主显示对比查看#以commoditytype表为主 >select * from commodity right join commoditytype on c_type=ct_id; #以commodity表为主 >select * from commodity left join commoditytype on c_type=ct_id; #自定义显示 >select ct_id,c_id,ct_name,c_name from commodity left join commoditytype on c_type=ct_id; >select ct_id,c_id,ct_name,c_name from commodity right join commoditytype on c_type=ct_id;
子查询:
就是 把某个表中查出来的数据作为我们的查询条件
#案例1:
1.1 问书籍的ct_id是多少?
->select ct_id from commoditytype where ct_name=‘书籍’;
1.2 我们知道书籍对应id为3,我们怎么从commodity中查出所有书籍
->select * from commodity where c_type=3;
1.3 更多的时候不知道具体对应的数值,所以把1.1,1.2写在一起,即子查询
->select * from commodity where c_type=(select ct_id from commoditytype where ct_name=‘书籍’);
#案例2:
2.1 计算书籍的平均售价,最终显示类型和价格
->select c_type,avg(c_outprice) from commodity where c_type=(select ct_id from commoditytype where ct_name=‘书籍’ );
#案例3:
3.1 查询售价大于均价的书籍
->select * from commodity where c_outprice>(select avg(c_outprice) from commodity) and c_type=(select ct_id from commoditytype where ct_name=‘书籍’);
3.2 售价大于书籍均价的所有书籍
取出均价大于均价为40的书籍
->select * from commodity where c_outprice>40 and c_type=3;
->select * from commodity where c_type=3 having c_outprice>40;
求书籍的均价
->select avg(c_outprice) from commodity where c_type=3;
求书籍的id
->select ct_id from commoditytype where ct_name=‘书籍’;
总结:根据上面拓展#1. select * from commodity where c_outprice>(select avg(c_outprice) from commodity where c_type=(select ct_id from commoditytype where ct_name='书籍')) and c_type=(select ct_id from commoditytype where ct_name='书籍'); #2. select * from commodity where c_type=(select ct_id from commoditytype where ct_name='书籍') having c_outprice>(select avg(c_outprice) from commodity where c_type=(select ct_id from commoditytype where ct_name='书籍'));
-
单行单列,用 =
#查出书籍的id > select ct_id from commoditytype where ct_name="书籍"; +-------+ | ct_id | +-------+ | 3 | +-------+
单列多行,用 in
#查询书籍或服装的商品 > select ct_id from commoditytype where ct_name='书籍' or ct_name='服装'; +-------+ | ct_id | +-------+ | 3 | | 4 | +-------+ #用in > select * from commodity where c_type in (select ct_id from commoditytype where ct_name='书籍' or ct_name='服装');
多行多列
> select max(c_inprice) from commodity group by c_type; +----------------+ | max(c_inprice) | +----------------+ | 1200 | | 160 | | 217 | | 50 | | 70 | +----------------+
any(多行)任意一个
> select c_id ,c_name,c_type,c_inprice from commodity where c_inprice>any(select max(c_inprice) from commodity group by c_type);
all(多行)所有
> select c_id ,c_name,c_type,c_inprice from commodity where c_inprice>all(select max(c_inprice) from commodity group by c_type);
综合练习:
如:
查询数据表中 2门 以及2门以上不及格的人 整体的平均成绩方法一:
#判断出来是布尔值,大于六十等于1,小于六十等于0
->select marks<60 from scores2;
#根据名字分组求和,判断每个人有多少门课不及格的
-> select name,sum(marks<60) from scores2 group by name; #布尔值求和
#用having对聚合函数进行判断,找出不及格人数在两门及以上的人
->select name,sum(marks<60) as k from scores2 group by name having k>=2;
#只筛选出姓名
->select name from scores2 group by name having sum(marks<60)>=2;
#用in列出这些两门及以上不及格人的成绩信息
->select * from scores2 where name in (select name from scores2 group by name having sum(marks<60)>=2);
#分组计算每个人整体的平均成绩
->select name,avg(marks) from scores2 where name in (select name from scores2 group by name having sum(marks<60)>=2) group by name;方法二:
#求所有人的平均分
select name,avg(marks) from scores2;
#求每个人的平均成绩
select name,avg(marks) from scores2 group by name;
#显示每个人小于60课的数量
select name,avg(marks),sum(marks<60) from scores2 group by name;
#显示每个人小于60课的数量为2门及以上的平均成绩
select name,avg(marks),sum(marks<60) from scores2 group by name having sum(marks<60)>=2;
#最终显示
select name,avg(marks) from scores2 group by name having sum(marks<60)>=2;方法三:
#查出成绩小于60的人
select name,marks from scores2 where marks<60;
#查出每个人不及格的课数
select name,marks,count(name) from scores2 where marks<60 group by name;
#找出不及格门数大于等于2门的
select name,marks,count(name) from scores2 where marks<60 group by name having count(name)>=2;
#只查出名字
select name from scores2 where marks<60 group by name having count(name)>=2;
#用in列出这些两门及以上不及格人的成绩信息
->select * from scores2 where name in (select name from scores2 group by name having sum(marks<60)>=2);
#分组计算每个人整体的平均成绩
->select name,avg(marks) from scores2 where name in (select name from scores2 group by name having sum(marks<60)>=2) group by name;注意:如果两个表的列名相同,在联表查询的时候会出现歧义,可以用 表名.列名 来区分各自的列名
五、拓展内容
****若创建表时,忘记给编码,处理办法:
- 查看所有字段的编码格式
语法:show full columns from 表名称> show full columns from goods;
- 查看表的编码格式
语法:show create table 表名称 - 修改数据库的编码格式
语法:alter database 数据库名称 character set 具体编码> alter database goods character set utf8;
- 修改数据表的编码格式
语法:alter table 表名称 character set 具体编码> alter table commoditytype character set utf8;
- 修改字段的编码格式
语法:alter table 表名称 modify 字段名 列类型 character set 具体编码> alter table commoditytype modify name varchar(20) character set utf8;
- 调整数据表字段的顺序
6.1 调字段名到最前面
语法:alter table 表名称 modify 字段名 列类型 first
6.2 调整 x字段名 到 y字段名的后面
语法:alter table 表名称 modify x 列类型 after y
6.3 增加新列指明位置
alter table 表名称 add x 列类型 after y
完~~~~^_^