Sql 完成对表中数据的CRUD的操作
前提：
- 创建数据库与表（以上一节博客为例：）
创建表Student【简单写法，不严谨】
create table Student(
sid int primary key,
sname varchar(31),
sex int,
age int
);
插入数据
-- insert into 表名(列名1，列名2，列名3) values(值1，值2，值3);
insert into student(sid,sname,sex,age) values(1,'zhangsan',1,23);
-- 简单写法: 如插入是全列名的数据，表名后面的列名可以省略
insert into 表名 values(值1，值2，值3);
insert into student values(2,'zhangsan',1,23);
注意： 如果插入的是部分列的话，列名不能省略
insert into student(sid,sname) values(3,'Lisi');
//insert into student value(3,'Lisi'); //错误写法
-- 批量插入数据:
insert into student values
(5,'zhangsan',1,23),
(6,'zhangsan',1,23),
(7,'zhangsan',1,23),
(8,'zhangsan',1,23);
-- 单条插入数据和批量插入的效率:
mysql> insert into student(sid,sname,sex,age) value(1,'zhangsan',1,23);
Query OK, 1 row affected (0.07 sec)
mysql> select * from student;
+-----+----------+------+------+
| sid | sname | sex | age |
+-----+----------+------+------+
| 1 | zhangsan | 1 | 23 |
+-----+----------+------+------+
1 row in set (0.00 sec)
mysql> insert into student value(2,'zhangsan',1,23);
Query OK, 1 row affected (0.10 sec)
mysql> select * from student;
+-----+----------+------+------+
| sid | sname | sex | age |
+-----+----------+------+------+
| 1 | zhangsan | 1 | 23 |
| 2 | zhangsan | 1 | 23 |+-----+----------+------+------+
2 rows in set (0.00 sec)
mysql> insert into student(sid,sname) value(3,'Lisi');
Query OK, 1 row affected (0.04 sec)
mysql> select * from student;
+-----+----------+------+------+
| sid | sname | sex | age |
+-----+----------+------+------+
| 1 | zhangsan | 1 | 23 |
| 2 | zhangsan | 1 | 23 |
| 3 | Lisi | NULL | NULL |
+-----+----------+------+------+
3 rows in set (0.00 sec)
删除数据
-- delete from 表名[where 条件]
delete from student where sid=10;
没有指定条件，则会将表中的数据一条一条的全部删除
delete from student
-- delete 与truncate 区别
delete: DML 一条一条的删除表中的数据
truncate： DDL 先删除表在重建表
关于哪条执行效率高： 具体要看表中的数据量
如果数据比较少，则delete 效率高，反之truncate 高校
打印如下
mysql> select * from student;
+-----+-----------+------+------+
| sid | sname | sex | age |
+-----+-----------+------+------+
| 1 | zhangsan | 1 | 23 |
| 2 | zhangsan | 1 | 23 |
| 3 | Lisi | NULL | NULL |
| 4 | wangwu | 1 | 63 |
| 5 | zhangsan | 1 | 23 |
| 6 | zhangsan | 1 | 23 |
| 7 | zhangsan | 1 | 23 |
| 8 | zhangsan | 1 | 23 |
| 9 | zhang san | 1 | 23 |
| 10 | 张三 | 1 | 23 |
+-----+-----------+------+------+
10 rows in set (0.00 sec)
mysql> show create student;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'student' at line 1
mysql> show create table student;+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLEstudent(
sidint(11) NOT NULL,
snamevarchar(31) DEFAULT NULL,
sexint(11) DEFAULT NULL,
ageint(11) DEFAULT NULL,
PRIMARY KEY (sid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
删除 where sid=10;
mysql> delete from student where sid=10;
Query OK, 1 row affected (0.09 sec)
mysql> select * from student;
+-----+-----------+------+------+
| sid | sname | sex | age |
+-----+-----------+------+------+
| 1 | zhangsan | 1 | 23 |
| 2 | zhangsan | 1 | 23 |
| 3 | Lisi | NULL | NULL |
| 4 | wangwu | 1 | 63 |
| 5 | zhangsan | 1 | 23 |
| 6 | zhangsan | 1 | 23 |
| 7 | zhangsan | 1 | 23 |
| 8 | zhangsan | 1 | 23 |
| 9 | zhang san | 1 | 23 |
+-----+-----------+------+------+
9 rows in set (0.00 sec)
更新数据
-- update 表名 set 列名=列名的值,列名2=列2的值[where 条件]
update student set sname='李四' where sid=5;
注意： 如果参数是字符串、日期要加上单引号
update student set sname='李四' where age=63;
注意：没有where的时候，执行以下代码会将 student 表中所有数据的 sname 改为 张龙，age 改为 23。【慎用】
update student set sname='张龙', age=23;
打印如下；
修改sname='李四' where sid=5;
mysql> update student set sname='李四' where sid=5;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student;
+-----+-----------+------+------+
| sid | sname | sex | age |
+-----+-----------+------+------+
| 1 | zhangsan | 1 | 23 |
| 2 | zhangsan | 1 | 23 |
| 3 | Lisi | NULL | NULL |
| 4 | wangwu | 1 | 63 |
| 5 | 李四 | 1 | 23 |
| 6 | zhangsan | 1 | 23 |
| 7 | zhangsan | 1 | 23 |
| 8 | zhangsan | 1 | 23 |
| 9 | zhang san | 1 | 23 |
+-----+-----------+------+------+
9 rows in set (0.00 sec)
查询数据
-- 语法
SELECT column_name,column_name
FROM table_name;
与
select * from table_name;
-- 查询表中的数据
从 "student" 表中选取所有列：
select * from student;
从 "student" 表中选取 "sname" 和 "sex" 列：
select sname,sex from student;
-- distinct： 去除重复数据
语法
SELECT DISTINCT column_name,column_name
FROM table_name;
select distinct age,sex from student;
查询打印如下
mysql> select * from student;
+-----+-----------+------+------+
| sid | sname | sex | age |
+-----+-----------+------+------+
| 1 | wanger | 0 | 3 |
| 2 | 小明 | 0 | 43 |
| 3 | 张龙 | NULL | 23 |
| 4 | 小吴 | 0 | 13 |
| 5 | 李四 | 1 | 23 |
| 6 | 王麻子 | 1 | 35 |
| 7 | zhaohu | 1 | 35 |
| 8 | 张龙 | 1 | 23 |
| 9 | 张龙 | 1 | 23 |
+-----+-----------+------+------+
9 rows in set (0.00 sec)
从 "student" 表中选取 "sname" 和 "sex" 列：
mysql> select sname,sex from student;+-----------+------+
| sname | sex |
+-----------+------+
| wanger | 0 |
| 小明 | 0 |
| 张龙 | NULL |
| 小吴 | 0 |
| 李四 | 1 |
| 王麻子 | 1 |
| zhaohu | 1 |
| 张龙 | 1 |
| 张龙 | 1 |
+-----------+------+
9 rows in set (0.00 sec)
distinct： 去除重复数据 select distinct age,sex from student;
mysql> select distinct age,sex from student;
+------+------+
| age | sex |
+------+------+
| 3 | 0 |
| 43 | 0 |
| 23 | NULL |
| 13 | 0 |
| 23 | 1 |
| 35 | 1 |
+------+------+
6 rows in set (0.00 sec)
查询数据补充
-- 商品分类：手机数码、鞋靴箱包
1、分类的id
2、分类的名称
3、分类的描述
创建表
create table category(
cid int primary key auto_increment,
cname varchar(10),
cdesc varchar(31)
);
插入数据
insert into category values (null,'手机数码','电子产品，三星出品');
insert into category values (null,'鞋靴箱包','made in china');
insert into category values (null,'香烟酒水','生活用品，茅台芙蓉王');
insert into category values (null,'红牛','饮料');
insert into category values (null,'三只松鼠','零食');
查询数据
select * from category;
select cname, cdesc from category;
select cname from category;
-- 所有商品
1、商品id
2、商品名称
3、商品价格
4、商品日期
5、商品分类id
****
****商品和商品分类：所属关系****
****
创建表
create table product(
pid int primary key auto_increment,
pname varchar(10),
prince double,
pdate timestamp,
con int
);
插入数据
insert into product values (null,'小米',998,null,1);
insert into product values (null,'特步',300,null,1);
insert into product values (null,'老村长',99,null,2);
insert into product values (null,'劲酒',88,null,3);
insert into product values (null,'小熊饼干',35,null,3);
insert into product values (null,'红牛',5,null,4);
insert into product values (null,'三只松鼠',135,null,5);
insert into product values (null,'王老吉',5,null,4);
insert into product values (null,'Mac',10000,null,1);
1）查询所有的商品
select * from product;
2）查询商品名称和商品价格
select pname, prince from product;
3）修改列名 prince -> price
alter table product change prince price double;
4）别名查询、as 关键字、as 关键字可以省略
-- 表别名：select p.pname,p.prince from product p; （主要用于多表查询）
select p. pname,p. price from product as p;
-- 列别名：select pname as 商品名称，price as 商品价格 from product；
select pname as 商品名称,price as 商品价格 from product;
as 可以省略：select pname 商品名称,price 商品价格 from product;
5）去掉重复的值
-- 查询商品所有的价格
select price from product;
select distinct price from product; 【去掉重复的】
6）select运算查询：仅仅字查询的结果上面做了运算
select *,price * 0.8 from product;
select *,price * 0.8 as 折后价 from product;
7）条件查询【where 关键字】
指定条件，确定要操作的记录
8）查询商品价格 > 60的所有商品
select * from product where price > 60;
9）where 后的条件写法
-- 关系运算符：
<> : 不等于 ： 标准的SQL 语法
!= : 不等于 ： 非标准的SQL 语法
select * from product where price != 99;
select * from product where price <> 99;
10）查询商品价格在10-100之间【两种写法】
select * from product where price > 10 and price < 100;
select * from product where price between 10 and 100;
11）查询商品价格在小于10 或者大于100
select * from product where price < 10 or price > 100;
12）like： 模糊查询
_ : 代表的是一个字符
% : 代表的是多个字符
-- 查询出名字中带有 ‘小’ 的所有商品 '%小%'
select * from product where pname like '%小%';
-- 查询第二个字是 “米”的所有商品 '_米%'
select * from product where pname like'_米%' ;
-- 查询第一个字是 “王”的所有商品 '王%'
select * from product where pname like '王%' ;
13）in 在某个范围中获得值
-- 查询出商品分类id 在（1，4，5）里面的所有商品
select * from product where con in(1,4,5);
14）排序 ： order by 关键字
-- 升序 asc: ascend 【默认排序方式】
-- 降序 desc:descend
查询所有商品，按照价格排序
select * from product order by price;
查询所有商品，按照价格降序排序
select * from product order by price desc;
查询出名字中带有 ‘小’ 的所有商品 '%小%',并按照价格降序排序
select * from product where pname like'%小%' order by price desc;
查询出名字中带有 ‘小’ 的所有商品 '%小%',并按照价格升序排序
select * from product where pname like'%小%' order by price asc;
15) 聚合函数
sum() : 求和
avg() ：求平均值
count() ：统计数量
max() ：最大值
min() ：最小值
-- 获取所有商品价格总和
select sum(price) from product;
-- 获取所有商品的平均价格
select avg(price) from product;
-- 获取所有商品的个数
select count(*) from product;
***********
注意：where 条件后面不能接聚合函数
***********
-- 查询出商品价格大于平均价格的所有商品
select * from product where price > (select avg(price) from product);
16）分组： group by
-- 根据con 字段分组，分组并统计商品个数【不能有where】
select con,count(*) from product group by con;
-- 根据con字段分组，分组统计每组商品的平均价格，并且商品评价价格 >60
第一步：select con ,avg(price)
第二步：from product group by con
第三步：having avg(price) > 60;
select con ,avg(price) from product group by con having avg(price) > 60;
**********
having 关键字，可以接聚合函数的，出现在分组之后
where 关键字，他是不可以接聚合函数的，出现在分组之前
**********
编写顺序
S...F...W...G...H...0
select...from...where...group by...having...order by
执行顺序
F...W...G...H...S...O
from...where...group by...having...select...order by
- 打印就不显示了
