SQL学习：增删改查

创建数据表

# 创建数据表：test_table,表内有数据列：ID、Name
CREATE TABLE test_table (
	ID int,
	Name varchar(255)
);

"""
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test_table     |
+----------------+
1 row in set (0.00 sec)
"""

增

# INSERT INTO `test_table`(`ID`, `Name`) VALUES ([value-1],[value-2])

# 添加测试数据
INSERT INTO `test_table`(`ID`, `Name`) VALUES (1,'张三');
INSERT INTO `test_table`(`ID`, `Name`) VALUES (1,'张三');
INSERT INTO `test_table`(`ID`, `Name`) VALUES (2,'李四');
INSERT INTO `test_table`(`ID`, `Name`) VALUES (2,'李四');
INSERT INTO `test_table`(`ID`, `Name`) VALUES (3,'王五');
"""
mysql> select * from test_table;
+------+--------+
| ID   | Name   |
+------+--------+
|    1 | 张三   |
|    1 | 张三   |
|    2 | 李四   |
|    2 | 李四   |
|    3 | 王五   |
+------+--------+
5 rows in set (0.00 sec)
"""

删

# 语法：DELETE FROM `test_table` WHERE 0

# 删除ID为3的数据
DELETE FROM `test_table` WHERE ID=3;
"""
mysql> delete from test_table where ID=3;
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_table;
+------+--------+
| ID   | Name   |
+------+--------+
|    1 | 张三   |
|    1 | 张三   |
|    2 | 李四   |
|    2 | 李四   |
+------+--------+
4 rows in set (0.00 sec)
"""

改

# 语法：UPDATE `test_table` SET `ID`=[value-1],`Name`=[value-2] WHERE 1

# 修改ID为3的数据name值为'王二'
UPDATE test_table SET name='王二' WHERE id=3;
"""
mysql> update test_table set name='王二' where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test_table;
+------+--------+
| ID   | Name   |
+------+--------+
|    1 | 张三   |
|    1 | 张三   |
|    2 | 李四   |
|    2 | 李四   |
|    3 | 王二   |
+------+--------+
5 rows in set (0.00 sec)
"""

查

# 语法：SELECT * FROM `test_table` WHERE 1
# 语法：SELECT `ID`, `Name` FROM `test_table` WHERE 1

# 查询表内所有数据
select * from test_table;
"""
mysql> select * from test_table;
+------+--------+
| ID   | Name   |
+------+--------+
|    1 | 张三   |
|    1 | 张三   |
|    2 | 李四   |
|    2 | 李四   |
|    3 | 王二   |
+------+--------+
5 rows in set (0.00 sec)
"""

# 查询表内ID为2的数据
select * from test_table where id=2;
"""
mysql> select * from test_table where id=2;
+------+--------+
| ID   | Name   |
+------+--------+
|    2 | 李四   |
|    2 | 李四   |
+------+--------+
2 rows in set (0.00 sec)

"""