SQL学习（1）——表相关操作_sql对表的操作

原文链接：https://github.com/datawhalechina/wonderful-sql
教程demo：https://tour.pingcap.com/
视频链接：https://www.bilibili.com/video/BV1pZ4y117W3/?spm_id_from=333.788&vd_source=7f8a29f8895e96fac156aef0574baa6a

1. 数据库创建与删除

-- 创建语法
CREATE DATABASE < 数据库名称 > ;
-- 作业例子
create database task01;

-- 删除语法
Drop DATABASE < 数据库名称 > ;
1
2
3
4
5
6
7

2. 表的创建与删除

2.1. 数据类型

INTEGER 型
用来指定存储整数的列的数据类型（数字型），不能存储小数。

CHAR 型
用来存储定长字符串，当列中存储的字符串长度达不到最大长度的时候，使用半角空格进行补足，由于会浪费存储空间，所以一般不使用。

VARCHAR 型
用来存储可变长度字符串，定长字符串在字符数未达到最大长度时会用半角空格补足，但可变长字符串不同，即使字符数未达到最大长度，也不会用半角空格补足。

DATE 型
用来指定存储日期（年月日）的列的数据类型（日期型）。

2.2. 约束的设置

约束是除了数据类型之外，对列中存储的数据进行限制或者追加条件的功能。

NOT NULL是非空约束，即该列必须输入数据。

PRIMARY KEY是主键约束，代表该列是唯一值，可以通过该列取出特定的行的数据。

2.3. 创建新表

-- 语法
CREATE TABLE < 表名 >
( < 列名 1> < 数据类型 > < 该列所需约束 > ,
  < 列名 2> < 数据类型 > < 该列所需约束 > ,
  < 列名 3> < 数据类型 > < 该列所需约束 > ,
  < 列名 4> < 数据类型 > < 该列所需约束 > ,
  .
  < 该表的约束 1> , < 该表的约束 2> ,……);
  
-- 作业例子
CREATE TABLE Addressbook
(regist_no INT  NOT NULL,
 name  VARCHAR(128) NOT NULL,
 address VARCHAR(256) NOT NULL,
 tel_no  VARCHAR(20) ,
 mail_address VARCHAR(20) ,
 PRIMARY KEY (regist_no));

ALTER TABLE Addressbook ADD COLUMN postal_code CHAR(8);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

2.4. 删除表

需要特别注意的是，删除的表是无法恢复的，只能重新插入，请执行删除操作时要特别谨慎。
删除表的语法：

DROP TABLE < 表名 > ;

DROP TABLE Addressbook ;

-- 清空表内容
--优点：相比drop / delete，truncate用来清除数据时，速度最快。
TRUNCATE TABLE TABLE_NAME;

1
2
3
4
5
6
7
8

2.5. 表中列处理

ALTER TABLE 语句和 DROP TABLE 语句一样，执行之后无法恢复。误添加的列可以通过 ALTER TABLE 语句删除，或者将表全部删除之后重新再创建。

-- 插入新列
ALTER TABLE < 表名 > ADD COLUMN < 列的定义 >;
ALTER TABLE Addressbook ADD COLUMN postal_code VARCHAR(8);
-- 删除列
ALTER TABLE < 表名 > DROP COLUMN < 列名 >;
-- 删除表中特定的行（语法）
-- 一定注意添加 WHERE 条件，否则将会删除所有的数据
DELETE FROM product WHERE COLUMN_NAME='XXX';
1
2
3
4
5
6
7
8

2.6 修改表名字

修改表名的指令（RENAME）：
通常在RENAME 之后按照< 变更前的名称>、< 变更后的名称> 的顺序来指定表的名称。

ALTER TABLE productins RENAME TO productions;
1

3. 表中数据处理

3.1. 单行插入

完成表格创建后，可以向表格中插入数据

-- 基本语法
INSERT INTO <表名> (列1, 列2, 列3, ……) VALUES (值1, 值2, 值3, ……);  
-- 样例
-- 包含列清单
INSERT INTO Addressbook (regist_no,name,address,tel_no,mail_address,postal_code) 
VALUES (001,'zxx','DHU',139,'zxx.@dhu.com',201620);  
-- 省略列清单
INSERT INTO Addressbook VALUES (001,'zxx','DHU',139,'zxx.@dhu.com',201620);  
1
2
3
4
5
6
7
8

3.2. 多行插入

INSERT INTO Addressbook VALUES (001,'zxx','DHU',139,'zxx.@dhu.com',201620),
(002,'zxy','DHU',139,'zxx.@dhu.com',201620),
(003,'wzy','DHU',139,'zxx.@dhu.com',201620);  
1
2
3

3.3. 复制数据

可以使用INSERT … SELECT 语句从其他表复制数据。

-- 将地址表中的数据复制到地址复制表中
INSERT INTO Addressbookcopy (regist_no,name,address,tel_no,mail_address,postal_code)
SELECT regist_no,name,address,tel_no,mail_address,postal_code
  FROM Addressbook;  
1
2
3
4

3.4. 数据的更新与删除

3.4.1. 单列更新

-- 语法
UPDATE <表名>
   SET <列名> = <表达式> [, <列名2>=<表达式2>...]  
 WHERE <条件>  -- 可选，非常重要
 ORDER BY 子句  --可选
 LIMIT 子句; --可选
1
2
3
4
5
6

使用 update 时要注意添加 where 条件，否则将会将所有的行按照语句修改

-- 修改所有的注册编号
UPDATE Addressbook
  SET regist_no = 001;  
-- 仅修改部分人的编号
UPDATE Addressbook
  SET regist_no = regist_no * 10
WHERE name = 'zxx';  
1
2
3
4
5
6
7

3.4.2. 多列更新

UPDATE 语句的 SET 子句支持同时将多个列作为更新对象。

-- 基础写法，一条UPDATE语句只更新一列
UPDATE Addressbook
   SET regist_no = regist_no * 10
 WHERE name = 'wzy';
UPDATE Addressbook
   SET regist_no = regist_no + 10
 WHERE name = 'zxy';  
1
2
3
4
5
6
7

该写法可以得到正确结果，但是代码较为繁琐。可以采用合并的方法来简化代码。

-- 合并后的写法
UPDATE Addressbook
   SET regist_no = regist_no * 10,
       postal_code = postal_code +10
 WHERE name = 'zxx';  
1
2
3
4
5

更新报错：Error Code:1175.You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
mysql在执行删除更新语句时报这种错误，是因为在mysql在safe-updates模式中，如果你where后跟的条件不是主键id，那么就会出现这种错误。

解决方式有两种

-- 执行该命令更改mysql数据库模式。
SET SQL_SAFE_UPDATES = 0;

-- 在where判断条件中跟上主键id
UPDATE Addressbook
  SET regist_no = regist_no * 10
WHERE name = 'gyt'and regist_no>0;
1
2
3
4
5
6
7

4. 练习题Task01

-- 1.1
create database task01;
use task01;
CREATE TABLE Addressbook
(regist_no INT  NOT NULL,
 name  VARCHAR(128) NOT NULL,
 address VARCHAR(256) NOT NULL,
 tel_no  Char(10) ,
 mail_address char(10) ,
 PRIMARY KEY (regist_no));
 
 -- 1.2
ALTER TABLE Addressbook ADD COLUMN postal_code CHAR(8);
select * from Addressbook;

-- 1.3
-- 请补充如下 SQL 语句来删除 Addressbook 表。
drop  table  Addressbook;

-- 1.4 判断题
-- 是否可以编写 SQL 语句来恢复删除掉的 Addressbook 表？
-- 删除的表是无法恢复的，只能重新插入，请执行删除操作时要特别谨慎。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22