Datawhale-MySQL-任务三

1.MySQL表数据类型:

• MySQL中定义数据字段的类型对你数据库的优化是非常重要的。
• MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。
• 具体类型介绍见菜鸟教程 | MySQL数据类型

2.用SQL语句创建表

创建方式：

一般有两种创建表的方法：
 多数 DBMS都具有交互式创建和管理数据库表的工具；
 表也可以直接用 SQL语句操纵。
需要注意的是，使用交互式工具时实际上就是使用 SQL语句。这些语句不是用户编写的，界面工具会自动生成并执行相应的 SQL语句（更改已有的表时也是这样）。

设定列类型，大小，约束

利用CREATE TABLE创建表，必须给出下列信息：

• 新表的名字，在关键字CREATE TABLE之后给出；
• 表列的名字和定义，用逗号分隔；
• 有的 DBMS还要求指定表的位置。

设定主键

一张表只能有一个字段可以使用对应的键，用来唯一的约束该字段里面的数据，不能重复。主键本身一定不为空，且不允许重复。
设定主键的三种方式:
① 创建表的时候在字段后面使用 primary key关键字；

create table my_default(
id int PRIMARY KEY,--使用关键字
name VARCHAR(20) not null unique key ,
gender varchar(4) DEFAULT '男'
)charset utf8;

② 在创建表的时候，在所有的字段之后使用 primary key(主键的字段 列表)来创建主键(如果有多个字段作为主键，称之为复合主键);

create table my_pri(
number char(10),
course char(10),
score TINYINT,
PRIMARY KEY(number,course)--复合主键
)charset utf8;

③ 创建表之后，为表追加主键。有两种方式，第一为修改表字段属性；第二直接追加。
方式一：

alter TABLE my_pri MODIFY number char(11) PRIMARY KEY;

方式二：

alter table table_name add primary key(column) ;

alter TABLE my_pri add PRIMARY KEY(number,course);--增加复合主键

alter table my_pri add CONSTRAINT pk_score PRIMARY KEY(score)--使用关键字CONSTRAINT 且指定主键名字pk_score 

alter table my_pri add CONSTRAINT  PRIMARY KEY(score)--使用关键字CONSTRAINT 且使用默认名字 

删除主键：

alter table my_default drop PRIMARY KEY; -- 删除主键使不需要根据主键名字删除，因为只有一个主键

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

语句解释

MySQL 表中使用 INSERT INTO SQL语句来插入数据。

多种添加方式（指定列名；不指定列名）

插入数据有三种方式：

• 插入完整的行；
• 插入行的一部分（指定列名）；
• 插入某些查询的结果（INSERT SELECT）。
  注：不指定列名插入高度依赖于表中列的定义次序，还依赖于其容易获得的次序信息。即使可以得到这种次序信息，也不能保证各列在下一次表结构变动后保持完全相同的次序。因此，编写依赖于特定列次序的 SQL语句是很不安全的，这样做迟早会出问题。因此指定列名插入，VALUES必须以其指定的次序匹配指定的列名，不一定按各列出现在表中的实际次序。其优点是，即使表的结构改变，这条INSERT语句仍然能正确工作。

4.用SQL语句删除表

DELETE

DELETE FROM table_name [WHERE Clause]

• 如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。
• 你可以在 WHERE 子句中指定任何条件
• 你可以在单个表中一次性删除记录。

DROP

DROP TABLE table_name

TRUNCATE

TRUNCATE table_name

不同方式的区别

1.drop和delete只是删除表的数据(定义),drop语句将删除表的结构、被依赖的约束(constrain)、触发器 (trigger)、索引(index);依赖于该表的存储过程/函数将保留,但是变为invalid状态。

2.delete语句是DML语言,这个操作会放在rollback segement中,事物提交后才生效;如果有相应的触发器(trigger),执行的时候将被触发。truncate、drop是DDL语言,操作后即 生效,原数据不会放到rollback中,不能回滚,操作不会触发trigger。

3.delete语句不影响表所占用的extent、高水线(high watermark)保持原位置不动。drop语句将表所占用的空间全部释放。truncate语句缺省情况下将空间释放到minextents的 extent,除非使用reuse storage。truncate会将高水线复位(回到最初)。

4.效率方面:drop > truncate > delete。

5.安全性:小心使用drop与truncate,尤其是在 没有备份的时候,想删除部分数据可使用delete需要带上where子句,回滚段要足够大,想删除表可以用drop,想保留表只是想删除表的所有数据、 如果跟事物无关可以使用truncate,如果和事物有关、又或者想触发 trigger,还是用delete,如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新导入、插入数据。

6.delete是DML语句,不会自动提交。drop/truncate都是DDL语句,执行后会自动提交。

7、drop一般用于删除整体性数据 如表，模式，索引，视图，完整性限制等；delete用于删除局部性数据 如表中的某一元组。

8、DROP把表结构都删了；DELETE只是把数据清掉

9.当你不再需要该表时， 用 drop；当你仍要保留该表，但要删除所有记录时， 用 truncate；当你要删除部分记录时（always with a WHERE clause), 用 delete.

5.用SQL语句修改表

修改列名

ALTER TABLE 表名 CHANGE 列名 新列名 列类型。

修改表中数据

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

删除行

从一个表中删除（去掉）数据，使用DELETE语句。有两种使用DELETE的方式：

• 从表中删除特定的行；
• 从表中删除所有行。
  注：不要省略 WHERE 子句:在使用 DELETE时一定要细心。因为稍不注意，就会错误地删除表中所有行。如果想从表中删除所有行，不要使用DELETE。可使用TRUNCATE TABLE语句，它完成相同的工作，而速度更快（因为不记录数据的变动）。

新建列

alter table 表名 add column 列名 列类型;

新建行

参考第三部分添加数据

6.作业

项目三

超过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:
学生在每个课中不应被重复计算。

---

作业代码:

项目四

交换工资（难度：简单）
创建一个 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

作业代码: