mysql学习笔记(二)------数据的增删改查

程序员文章站 2022-03-03 17:42:42

...

数据的增删改查↓↓↓

一、增

增加一条数据：
语法：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

完~~~~^_^

上一篇：mysql学习笔记(二)------数据库和表

上一篇： npm安装express时出现npm WARN [email protected] No descriptionnpm WARN [email protected] No repos

下一篇： MySQL常用系统函数

mysql学习笔记(二)------数据的增删改查

一、增

二、删

三、改

四、查

五、拓展内容

python数据库-MySQL数据库的增删改查基本操作(49)

mysql数据库表的创建以及字段的增删改查操作及一些常用的查询命令介绍

数据库学习（MySQL）：JDBC的简单增删改查实现

MySql+Socket 完成数据库的增查Demo

数据库笔记6--基本SQL语言的增删改查与DDL(哈工大)

MySQL处理重复数据的学习笔记

MySQL数据库的增删改查

mysql 学习笔记（5）-- 增删改查

lucene全文检索实例二(实现对10万条数据检索文件的增删改查操作)

lucene全文检索实例二(实现对10万条数据检索文件的增删改查操作)