【MySQL】基础（二）- 表操作

1. MySQL表数据类型

链接：MySQL数据类型

• 数值类型
• 日期和时间类型
• 字符串类型

2. 用SQL语句创建表

CREATE TABLE 表名称
(
列名称1 数据类型,
列名称2 数据类型,
列名称3 数据类型,
…
)

本例演示如何创建名为 “Person” 的表。
该表包含 5 个列，列名分别是：“Id_P”、“LastName”、“FirstName”、“Address” 以及 “City”：

CREATE TABLE Persons
(
Id_P int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

Id_P 列的数据类型是 int，包含整数。其余 4 列的数据类型是 varchar，最大长度为 255 个字符。

主键由primary key指定 ,主键的值必须唯一
但是可以有两个主键比如:primary key(order,item)表示这两个的组合值是唯一的

空的 “Persons” 表类似这样：

4. 用SQL语句向表中添加数据

SQL语言使用insert语句向数据库表格中插入或添加新的数据行。
Insert语句的使用格式如下：

insert into tablename
(first_column,…last_column)
values (first_value,…last_value);

例如：

insert into employee 
(firstname, lastname, age, address, city) 
values (‘Li’, ‘Ming’, 45, ‘No.77 Changan Road’, ‘Beijing”); 

简单来说，当向数据库表格中添加新记录时，在关键词insert into后面输入所要添加的表格名称，然后在括号中列出将要添加新值的列的名称。最后，在关键词values的后面按照前面输入的列的顺序对应的输入所有要添加的记录值。

5. 用SQL语句删除表

SQL中的语法

1、drop table 表名称
eg: drop table dbo.Sys_Test
2、truncate table 表名称
eg: truncate table dbo.Sys_Test
3、delete from 表名称 where 列名称 = 值
eg: delete from dbo.Sys_Test where test=‘test’

drop，truncate，delete区别

1、drop (删除表)：删除内容和定义，释放空间。简单来说就是把整个表去掉.以后要新增数据是不可能的,除非新增一个表。drop语句将删除表的结构被依赖的约束（constrain),触发器（trigger)索引（index);依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。

2、truncate (清空表中的数据)：删除内容、释放空间但不删除定义(保留表的数据结构)。与drop不同的是,只是清空表数据而已。
注意:truncate 不能删除行数据,要删就要把表清空。

3、delete (删除表中的数据)：delete 语句用于删除表中的行。delete语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存， 以便进行进行回滚操作。
truncate与不带where的delete ：只删除数据，而不删除表的结构（定义）

4、truncate table 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用delete。
如果要删除表定义及其数据，请使用 drop table 语句。

5、对于由foreign key约束引用的表，不能使用truncate table ，而应使用不带where子句的delete语句。由于truncate table 记录在日志中，所以它不能激活触发器。

6、执行速度，一般来说: drop> truncate > delete。

7、delete语句是数据库操作语言(dml)，这个操作会放到 rollback segement 中，事务提交之后才生效；如果有相应的 trigger，执行的时候将被触发。
truncate、drop 是数据库定义语言(ddl)，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger。

6. 用SQL语句修改表

6.1修改列名

alter table <表名> change <字段名> <字段新名称> <字段的类型>。
ALTER TABLE attence CHANGE attence_name NAME VARCHAR(20)

6.2 修改字段的长度：

ALTER TABLE 表名 MODIFY COLUMN 字段名 数据类型(修改后的长度)
ALTER TABLE attence MODIFY COLUMN id INT(20)

6.3 新增字段 ：

语法：
新增默认为空的字段
ALTER TABLE 表名 ADD COLUMN 字段名 字段类型 DEFAULT NULL;
新增不为空的字段
ALTER TABLE 表名ADD COLUMN 字段名 字段类型 NOT NULL;
例子：
ALTER TABLE attence ADD COLUMN attence_name VARCHAR(20) DEFAULT NULL;
ALTER TABLE attence ADD COLUMN age VARCHAR(20) NOT NULL;

6.4 删除字段：

语法：
ALTER TABLE 表名 DROP COLUMN 字段名;
例子：
ALTER TABLE attence DROP COLUMN age;

6.5 更改表的名字:

alter table student rename as stu;

6.6 批量增加字段：

方法一
可以使用事务
语法：

begin; //事务开始
alter table 表名 add 字段名 字段类型(长度);
alter table 表名 add 字段名 字段类型(长度);
alter table 表名 add 字段名 字段类型(长度);
alter table 表名 add 字段名 字段类型(长度);
commit;

例子：
begin; //事务开始
alter table em_day_data add f_day_house7 int(11);
alter table em_day_data add f_day_house8 int(11);
alter table em_day_data add f_day_house9 int(11);
alter table em_day_data add f_day_house10 int(11);
commit;

方法二
alter table 表名 add (字段1 类型(长度),字段2 类型(长度),字段3 类型(长度));
alter table em_day_data add (f_day_house11 int(11),f_day_house12 int(11),f_day_house13 int(11));

6.7 批量修改字段名称:

语法：
alter table 表 change 修改前字段名 修改后字段名称 int(11) not null,
change 修改前字段名 修改后字段名称 int(11) not null,
change 修改前字段名 修改后字段名称 int(11) not null,
change 修改前字段名 修改后字段名称 int(11) not null,
change 修改前字段名 修改后字段名称 int(11) not null

例子：
alter table em_day_data change f_day_house11 f_day_hour11 int(11) not null,
change f_day_house12 f_day_hour12 int(11) not null,
change f_day_house13 f_day_hour13 int(11) not null,
change f_day_house14 f_day_hour14 int(11) not null,
change f_day_house15 f_day_hour15 int(11) not null,
change f_day_house16 f_day_hour16 int(11) not null,
change f_day_house17 f_day_hour17 int(11) not null

7 作业

项目三：超过5名学生的课（难度：简单）
创建如下所示的courses 表 ，有: student (学生) 和 class (课程)。
例如,表:
±--------±-----------+
| student | class |
±--------±-----------+
| A | Math |
| B | English |
| C | Math |
| D | Biology |
| E | Math |
| F | Computer |
| G | Math |
| H | Math |
| I | Math |
| A | Math |
±--------±-----------+

编写一个 SQL 查询，列出所有超过或等于5名学生的课。
应该输出:
±--------+
| class |
±--------+
| Math |
±--------+
Note:
学生在每个课中不应被重复计算。

解答：

CREATE TABLE courses (
 student VARCHAR(255) PRIMARY KEY,
 class VARCHAR(255) 
);

INSERT INTO course(student,class) VALUES ('A','Math');
INSERT INTO course(student,class) VALUES ('B','English');
INSERT INTO courses VALUES ('C','Math');
INSERT INTO courses VALUES ('D','Biology');
INSERT INTO courses VALUES ('E','Math');
INSERT INTO courses(student,class) VALUES ('F','Computer');
INSERT INTO courses(student,class) VALUES ('G','Math');
INSERT INTO courses(student,class) VALUES ('H','Math');
INSERT INTO courses(student,class) VALUES ('I','Math');
INSERT INTO courses(student,class) VALUES ('A2','Math');

SELECT class FROM courses
GROUP BY class
HAVING COUNT(*)>=5;

项目四：交换工资（难度：简单）
创建一个 salary表，如下所示，有m=男性 和 f=女性的值 。
例如:

id	name	sex	salary
1	A	m	2500
2	B	f	1500
3	C	m	5500
4	D	f	500

交换所有的 f 和 m 值(例如，将所有 f 值更改为 m，反之亦然)。
要求使用一个更新查询，并且没有中间临时表。
运行你所编写的查询语句之后，将会得到以下表:

id	name	sex	salary
1	A	f	2500
2	B	m	1500
3	C	f	5500
4	D	m	500

解答：

CREATE TABLE salary(
    id INT PRIMARY KEY,
    name VARCHAR(255),
	sex VARCHAR(255),
	salary INT
);

INSERT INTO salary VALUES (1,'A','m',2500);
INSERT INTO salary VALUES (2,'B','f',1500);
INSERT INTO salary VALUES (3,'C','m',5500);
INSERT INTO salary VALUES (4,'D','f',500);

UPDATE salary 
SET
	sex=
	CASE WHEN sex='f'
	THEN 'm'
	ELSE 'f'
	END;

---