数据增删改查(curd)
curd的解释: 代表创建（Create）、更新（Update）、读取（Retrieve）和删除（Delete）
01-增加 insert
- 全列插入
insert [into] 表名 values (值1,值2,...)
值和表的字段的顺序一一对应
主键占位符: 0/default/NULL
枚举: 枚举的原始值: 男,女,保密,中性, 枚举值: 1,2,3,4 SQL中默认从1开始
-- insert into students values (0,'鲁班',20,160.00,'男',1);
-- insert into students values (NULL,'小乔',20,160.00,2,1);
-- insert into students values (default,'大乔',20,160.00,2,1);
mysql> insert into students values (0,'鲁班',20,160.00,'男',1);
Query OK, 1 row affected (0.00 sec)
-- 结果
mysql> select * from students;
+----+--------+------+--------+--------+--------+
| id | name | age | high | gender | cls_id |
+----+--------+------+--------+--------+--------+
| 1 | 鲁班 | 20 | 160.00 | 男 | 1 |
| 2 | 小乔 | 20 | 160.00 | 女 | 1 |
| 3 | 大乔 | 20 | 160.00 | 女 | 1 |
+----+--------+------+--------+--------+--------+
3 rows in set (0.00 sec)
- 指定列插入
insert into 表名 (列1,...) values(值1,...)
值和列一一对应
-- insert into students (name, gender, cls_id) values ('张飞',1,2);
mysql> insert into students (name, gender, cls_id) values ('张飞',1,2);
Query OK, 1 row affected (0.00 sec)
-- 结果
mysql> select * from students;
+----+--------+------+--------+--------+--------+
| id | name | age | high | gender | cls_id |
+----+--------+------+--------+--------+--------+
| 1 | 鲁班 | 20 | 160.00 | 男 | 1 |
| 2 | 小乔 | 20 | 160.00 | 女 | 1 |
| 3 | 大乔 | 20 | 160.00 | 女 | 1 |
| 4 | 张飞 | 0 | 0.00 | 男 | 2 |
+----+--------+------+--------+--------+--------+
4 rows in set (0.00 sec)
- 多行插入 批量插入
insert into 表名 (列1,...) values (值1,...),(值1,...),...
-- insert into students (name, gender, cls_id) values ('关羽',1,2), ('刘备',1,2), ('赵云',1,2);
mysql> insert into students (name, gender, cls_id) values ('关羽',1,2), ('刘备',1,2), ('赵云',1,2);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
-- 结果
mysql> select * from students;
+----+--------+------+--------+--------+--------+
| id | name | age | high | gender | cls_id |
+----+--------+------+--------+--------+--------+
| 1 | 鲁班 | 20 | 160.00 | 男 | 1 |
| 2 | 小乔 | 20 | 160.00 | 女 | 1 |
| 3 | 大乔 | 20 | 160.00 | 女 | 1 |
| 4 | 张飞 | 0 | 0.00 | 男 | 2 |
| 5 | 曹操 | 50 | 156.00 | 男 | 1 |
| 6 | 孙权 | 40 | 166.00 | 男 | 1 |
| 7 | 貂蝉 | 18 | 168.00 | 女 | 1 |
| 8 | 关羽 | 0 | 0.00 | 男 | 2 |
| 9 | 刘备 | 0 | 0.00 | 男 | 2 |
| 10 | 赵云 | 0 | 0.00 | 男 | 2 |
+----+--------+------+--------+--------+--------+
10 rows in set (0.00 sec)
02-修改 update
- 全表更新
update [表名] set [列1=值1,列2=值2,...] where [条件]
-- where 表示修改的范围
-- update students set age = 18 where id = 4; -- sql中通过一个等于号表示相等
update students set age = 38 where id = 4;
mysql> update students set age = 38 where id = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- 结果
mysql> select * from students;
+----+--------+------+--------+--------+--------+
| id | name | age | high | gender | cls_id |
+----+--------+------+--------+--------+--------+
| 1 | 鲁班 | 20 | 160.00 | 男 | 1 |
| 2 | 小乔 | 20 | 160.00 | 女 | 1 |
| 3 | 大乔 | 20 | 160.00 | 女 | 1 |
| 4 | 张飞 | 38 | 0.00 | 男 | 2 |
| 5 | 曹操 | 50 | 156.00 | 男 | 1 |
| 6 | 孙权 | 40 | 166.00 | 男 | 1 |
| 7 | 貂蝉 | 18 | 168.00 | 女 | 1 |
| 8 | 关羽 | 0 | 0.00 | 男 | 2 |
| 9 | 刘备 | 0 | 0.00 | 男 | 2 |
| 10 | 赵云 | 0 | 0.00 | 男 | 2 |
+----+--------+------+--------+--------+--------+
10 rows in set (0.00 sec)
03-删除 delete
- 物理删除
delete from 表名 [where 条件判断]
-- delete from students where id = 8; -- 删除id为8的这一行
mysql> delete from students where id = 8;
Query OK, 1 row affected (0.00 sec)
-- 结果
mysql> select * from students;
+----+--------+------+--------+--------+--------+
| id | name | age | high | gender | cls_id |
+----+--------+------+--------+--------+--------+
| 1 | 鲁班 | 20 | 160.00 | 男 | 1 |
| 2 | 小乔 | 20 | 160.00 | 女 | 1 |
| 3 | 大乔 | 20 | 160.00 | 女 | 1 |
| 4 | 张飞 | 38 | 188.00 | 男 | 2 |
| 5 | 曹操 | 50 | 156.00 | 男 | 1 |
| 6 | 孙权 | 40 | 166.00 | 男 | 1 |
| 7 | 貂蝉 | 18 | 168.00 | 女 | 1 |
| 9 | 刘备 | 0 | 0.00 | 男 | 2 |
| 10 | 赵云 | 0 | 0.00 | 男 | 2 |
+----+--------+------+--------+--------+--------+
9 rows in set (0.00 sec)
- 全表删除
delete from [表名]
delete from students; (慎用)sql从入门到删除跑路
查询 select (DQL类型的语言, 在sql中最重要的就是查询)
- 查询所有字段
select * from [表名];
mysql> select * from students;
+----+--------+------+--------+--------+--------+
| id | name | age | high | gender | cls_id |
+----+--------+------+--------+--------+--------+
| 1 | 鲁班 | 20 | 160.00 | 男 | 1 |
| 2 | 小乔 | 20 | 160.00 | 女 | 1 |
| 3 | 大乔 | 20 | 160.00 | 女 | 1 |
| 4 | 张飞 | 38 | 188.00 | 男 | 2 |
| 5 | 曹操 | 50 | 156.00 | 男 | 1 |
| 6 | 孙权 | 40 | 166.00 | 男 | 1 |
| 7 | 貂蝉 | 18 | 168.00 | 女 | 1 |
| 9 | 刘备 | 0 | 0.00 | 男 | 2 |
| 10 | 赵云 | 0 | 0.00 | 男 | 2 |
+----+--------+------+--------+--------+--------+
9 rows in set (0.00 sec)
- 查询指定字段
select [列1,列2,…] from [表名];
mysql> select name, gender from students;
+--------+--------+
| name | gender |
+--------+--------+
| 鲁班 | 男 |
| 小乔 | 女 |
| 大乔 | 女 |
| 张飞 | 男 |
| 曹操 | 男 |
| 孙权 | 男 |
| 貂蝉 | 女 |
+--------+--------+
7 rows in set (0.00 sec)
- 使用 as 给字段起别名
select [字段] as [别名].... from [表名];
select name as 名字, gender as 性别 from students;
- 可以通过 as 给表起别名
select [别名.字段1,别名.字段2, .... ] from [表名] as [别名];
在当前的sql 语句中 临时的给students 起了一个别名叫做s
select name, gender from students;
select s.name, s.gender from students as s;
- sql语句完全的形式
select [表名.字段 .... ] from [表名];
select students.name, students.gender from students;
- 跨表查询
select students.name , classes.name from sutdnets, classes;
select python_test_1.students.name , python_test_1.classes.name from sutdnets, classes;
- 消除重复行
distinct 字段, 修饰所有需要查询的字段
如果distinct后有多个字段, 只有当查询的多列的查询结果完全相同才能去重
mysql> select gender from students; -- 查询班级学生的性别
+--------+
| gender |
+--------+
| 男 |
| 女 |
| 女 |
| 男 |
| 男 |
| 男 |
| 女 |
+--------+
7 rows in set (0.00 sec)
mysql> select distinct gender from students; -- 查询班级有多少种性别
+--------+
| gender |
+--------+
| 男 |
| 女 |
+--------+
2 rows in set (0.00 sec)
