數(shù)據(jù)增刪改查(curd)
curd的解釋: 代表創(chuàng)建（Create）、更新（Update）、讀?。≧etrieve）和刪除（Delete）
01-增加 insert
- 全列插入
insert [into] 表名 values (值1,值2,...)
值和表的字段的順序一一對(duì)應(yīng)
主鍵占位符: 0/default/NULL
枚舉: 枚舉的原始值: 男,女,保密,中性, 枚舉值: 1,2,3,4 SQL中默認(rèn)從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)
-- 結(jié)果
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,...)
值和列一一對(duì)應(yīng)
-- 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)
-- 結(jié)果
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 ('關(guān)羽',1,2), ('劉備',1,2), ('趙云',1,2);
mysql> insert into students (name, gender, cls_id) values ('關(guān)羽',1,2), ('劉備',1,2), ('趙云',1,2);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
-- 結(jié)果
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 | 孫權(quán) | 40 | 166.00 | 男 | 1 |
| 7 | 貂蟬 | 18 | 168.00 | 女 | 1 |
| 8 | 關(guān)羽 | 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中通過一個(gè)等于號(hào)表示相等
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
-- 結(jié)果
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 | 孫權(quán) | 40 | 166.00 | 男 | 1 |
| 7 | 貂蟬 | 18 | 168.00 | 女 | 1 |
| 8 | 關(guān)羽 | 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)
-- 結(jié)果
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 | 孫權(quán) | 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 | 孫權(quán) | 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 |
+--------+--------+
| 魯班 | 男 |
| 小喬 | 女 |
| 大喬 | 女 |
| 張飛 | 男 |
| 曹操 | 男 |
| 孫權(quán) | 男 |
| 貂蟬 | 女 |
+--------+--------+
7 rows in set (0.00 sec)
- 使用 as 給字段起別名
select [字段] as [別名].... from [表名];
select name as 名字, gender as 性別 from students;
- 可以通過 as 給表起別名
select [別名.字段1,別名.字段2, .... ] from [表名] as [別名];
在當(dāng)前的sql 語句中 臨時(shí)的給students 起了一個(gè)別名叫做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;
- 消除重復(fù)行
distinct 字段, 修飾所有需要查詢的字段
如果distinct后有多個(gè)字段, 只有當(dāng)查詢的多列的查詢結(jié)果完全相同才能去重
mysql> select gender from students; -- 查詢班級(jí)學(xué)生的性別
+--------+
| gender |
+--------+
| 男 |
| 女 |
| 女 |
| 男 |
| 男 |
| 男 |
| 女 |
+--------+
7 rows in set (0.00 sec)
mysql> select distinct gender from students; -- 查詢班級(jí)有多少種性別
+--------+
| gender |
+--------+
| 男 |
| 女 |
+--------+
2 rows in set (0.00 sec)
