MySQL数据库基础（五）：表中数据的操作（增删改查）

1.插入数据

1.1 插入数据

insert into 表名(列名1，列名2，列名3) values(值1，值2，值3);

1.2 插入数据中文乱码问题（报错1366）

停止MySQL服务

查看编码
路径：C:\Program Files\MySQL\MySQL Server 5.5\my.ini

启动mysql

修改user数据库和student表的字符集为gbk

修改编码为gbk，停止mysql

修改C:\Program Files\MySQL\MySQL Server 5.5\my.ini文件，最后Ctrl+s保存

• 在[client]下追加： default-character-set=gbk
• 在[mysqld]下追加： character-set-server=gbk
• 在[mysql]下追加： default-character-set=gbk

Ctrl+s保存时，会出现问题无法保存，这是由于我们的这个文件没有权限的原因

给它完全控制的权限，之后点两次确定。

启动mysql

此时，再去登录数据库，插入数据，不会再报错了

2. 删除数据

2.1 删除数据命令

delete from 表名 where 条件;

2.1.1 练习：删除student表中sid=1的数据

delete from student where sid=1;

2.1.2 练习：不加where条件的删除语句

delete from student;      #这条语句是直接删除了表中所有的数据

2.2 delete和truncate删除数据有什么差别？
2.2.1 delete与truncate

delete：DML，一条一条删除表中数据
truncate：DDL，先删除表在重建表

2.2.2 这两条哪个效率高？

具体看表中的数据量，数据比较少的话用delete高效。如果数据比较多，用truncate比较高效。

2.3 由于我们之前删除了表中数据，在这里我们在添加一些数据到表中
2.3.1在student表中批量插入一些数据

insert into student values
(1,'张三',1,20),
(2,'李四',1,21),
(3,'小敏',0,21),
(4,'小雪',0,20),
(5,'小明',1,19);

2.3.2 查看这个表中的数据

select * from student;

3.更新表数据

3.1 更新表数据命令

update 表名 set 列名=列的值，列名2=列的值2 where条件
## 如果参数是字符串、日期要加上单引号

3.1.1 练习：将sid为5的名字改为大明

update student set sname='大明' where  sid=5;

3.1.2 练习：不加where条件句（更新表所有的指定列的数据）

update student set sname='小明',sex=1;

4.查询数据（简单）

4.1 查询数据命令

select [distinct] * [列名1，列名2] from 表名 [where 条件];
distinct：去除重复数据

4.2 练习：创建一个商品分类实例

4.2.1 商品分类：手机数码，鞋靴箱包，香烟酒水，酸奶饼干，零食

1.分类的ID
2.分类的名称
3.分类描述

4.2.2 在user数据库中创建商品分类这个表

create table category(
	cid int primary key auto_increment,    
	cname varchar(10),
	cdesc varchar(31)   
);

auto_increment：自动增长ID

4.2.3 在category这个表中插入一些数据

insert into category values(null,'手机数码','电子产品');
insert into category values(null,'鞋靴箱包','生活用品');
insert into category values(null,'香烟酒水','黄鹤楼，二锅头');
insert into category values(null,'酸奶饼干','娃哈哈，纯甄');
insert into category values(null,'零食','瓜子，花生，辣条');

4.3 查看表中的所有数据

select * from 表名;

4.3.1 练习：查看category表中的所有数据

select * from category;

4.4 查看表中某些列的数据

select 列名1，列名2... from 表名;

4.4.1 练习：查看category表中cname,cdesc这两列的数据

select cname,cdesc from category;

4.5 创建一个所有商品的实例
4.5.1 所有商品

1.商品ID
2.商品名称
3.商品价格
4.生产日期
5.商品分类ID

4.5.2 在user数据中中创建商品这个表

商品和商品分类：从属关系

create table product(
	pid int primary key auto_increment,
	pname varchar(10),
	price double,
	pdate timestamp,
	cno int 
);

4.5.3 在product表中插入数据

insert into product values(null,'小米8',4999,null,1);
insert into product values(null,'锤子',66,null,1);
insert into product values(null,'阿迪',2000,null,2);
insert into product values(null,'老村长',88,null,3);
insert into product values(null,'劲酒',35,null,3);
insert into product values(null,'小熊饼干',3.5,null,4);
insert into product values(null,'娃哈哈',10,null,4);
insert into product values(null,'卫龙辣条',3,null,5);
insert into product values(null,'旺旺雪饼',2,null,5);

4.6 简单的查询
4.6.1 练习：查询所有商品

select * from product;

4.6.2 练习：查询商品名称和价格

select pname,price from product;

4.7 别名查询，as关键字，as可以省略

表别名查询：select 表别名.列名，表别名,列名2 from 表名 as 表别名；
列别名查询：select 列名 as 列别名，列名2 as 列别名2 from 表名；

4.7.1 练习：product表别名查询

select p.pname,p.price from product p;

4.7.2 练习：product表列别名的查询

select pname as 商品名称,price as 商品价格 from product;

去掉as结果是一样的

select pname as 商品名称,price  商品价格 from product;

4.8 去掉重复的值
4.8.1 去掉重复的值的命令

select distinct 列名 from 表名

4.8.2 练习：去掉price列中重复的值

我们表中没有重复的值，先插入一行价格重复的数据

insert into product values(null,'耐克',2000,null,2);

查询product表中商品价格price这列的数据

select price from product;

去掉重复价格

select distinct price from product;

4.9 select运算查询：仅在查询结果做了运算，不改变数据库数据

4.9.1 练习：把表product的商品价格price乘1.5倍，查询出来，包含其他列的数据

select *,price*1.5 from product;

4.9.2 练习：给price*1.5这一列起一个别名

select *,price*1.5 折后价 from product;

4.10 条件查询（where关键字）

4.10.1 where后的条件写法

关系运算符：>  >=  <  <=  =  !=  <>
		<>：不等于：标准SQL语法
		！=：不等于：非标准SQL语法

逻辑运算：and  or  not

4.10.2 练习：查询商品价格不等于2000的所有商品

select * from product where price <> 2000;
或
select * from product where price != 2000;

4.10.3 练习：查询商品价格>60元的商品

select * from product where price >60;

4.10.4 练习：查询商品价格在10-100之间

select * from product where price > 10 and price < 100;
或
select * from product where price between 10 and 100;

between … and …是包括10和100的

4.10.5 练习：查询商品价格小于100或者大于2000

select * from product where price < 100 or price > 900;

5. 查询数据（复杂）

5.1 like: 模糊查询

_：代表的是一个字符
%：代表的是多个字符

5.1.1 练习：查询出名字中带有饼的所有商品

select * from product where pname like '%饼%';

5.1.2 练习：查询第二个字符是熊的所有商品

select * from product where pname like '_熊%';

5.2 in：在某个范围中获得值

5.2.1 练习：查询商品ID在1,4,5里的所有商品

select * from product where cno in(1,4,5);

5.3 排序查询：order by 关键字

select * from 表名 order by 关键字；
关键字：asc:升序（默认） 、desc:降序

5.3.1 练习：查询所有商品，按价格进行排序

select * from product  order by price;

默认的排序方式是升序排列
5.3.2 练习：查询所有商品，按价格进行降序排序

select * from product  order by  price desc;

5.3.2 练习：查询名称中有‘小’的商品，按价格降序排序

select * from product where pname like '%小%' order by price desc;

5.4 聚合函数

sum（）：求和
avg（）：求平均值
count（）：统计数量
max（）：最大值
min（）：最小值
注意：where条件后不能加聚合函数

5.4.1 练习：查询所有商品价格的总和

select sum(price) from product;

5.4.2 练习：查询所有商品价格的平均价格

select avg(price) from product;

5.4.3 练习：查询所有商品个数

select count(*) from product;

5.4.4 练习：查询商品价格的最大值

select max(price) from product;

5.4.5 练习：查询商品价格大于平均价格的所有商品（子查询）

select * from product where price > (select avg(price) from product);

5.5 分组查询

group by
having关键字：可以接聚合函数，出现在分组之后
where关键字：不可以接聚合函数，出现在分组之前

5.5.1 练习：根据cno字段分组，分组后统计商品的个数

select cno,count(*) from product group by cno;

5.5.2 :练习：根据cno字段分组，分组后统计每组商品的平均价格，并且平均价格大于60

select cno,avg(price) 
from product group by cno
having avg(price) > 60;

6.SQL的编写顺序与执行顺序

编写顺序

select ..from .. where .. group by .. having .. order by

执行顺序

from .. where .. group by .. having .. sekect .. order by