MySQL入门（三）：表操作
一、MySQL表数据类型
MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。 定义数据字段的类型对数据库的优化是非常重要的。
（一）数值类型
MySQL支持所有标准SQL数值数据类型。
这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。
在这里插入图片描述
（二）日期和时间类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
在这里插入图片描述
（三）字符串类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。
在这里插入图片描述
二、用SQL语句创建表
（一）通用语法
CREATE TABLE table_name (column_name column_type);
（二）语句解释
创建MySQL数据表需要以下信息：
- 表名
- 表字段名
- 定义每个表字段
（三）操作示例
CREATE TABLE runoob_tbl
(
runoob_id INT UNSIGNED AUTO_INCREMENT,
runoob_title VARCHAR(100) NOT NULL,
runoob_author VARCHAR(40) NOT NULL,
submission_date DATE,
PRIMARY KEY ( runoob_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
注意：
- RIMARY KEY关键字用于定义列为主键。 可以使用多列来定义主键，列间以逗号分隔。比如:primary key(order,item)表示这两个的组合值是唯一的 ，主键必须NOT NULL
- 如果不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为NULL ，就会报错。
- ENGINE 设置存储引擎，CHARSET 设置编码。
- auto_increment自动增量 必须和主键组合使用,默认情形起始值为1，每次增量为1
关于主键
一张表只能有一个字段可以使用对应的键，用来唯一的约束该字段里面的数据，不能重复。主键本身一定不为空，且不允许重复。
设定主键一共有三种方式：如何设定和删除主键
三、用SQL语句向表中添加数据
(一)通用语法
INSERT INTO table_name ( field1, field2,...fieldN )
VALUES
( value1, value2,...valueN );
注：如果数据是字符型，必须使用单引号或者双引号，如："value"。
（二）语句解释
MySQL 表中使用 INSERT INTO SQL语句来插入数据。
插入数据有三种方式：
- 插入完整的行；
- 插入行的一部分（指定列名）；
- 插入某些查询的结果（INSERT SELECT）;
注：不指定列名是一种危险的方式，因为其高度依赖于表中列的定义次序，还依赖于其容易获得的次序信息。因此为保险起见，应指定列名插入。
(三)操作示例
1.插入单行
insert into city (Name,CountryCode,District,Population)
values( 'Kabul' , 'AFG' , 'Kabol' , '1780000' );
2.插入多行
insert into city (Name,CountryCode,District,Population)
values( 'Zarul' , 'BFG' , 'cabol' , '180000' )；
values( 'Kabul' , 'AFG' , 'Kabol' , '1780000' );
3.插入某些查询数据
insert into city (Name,CountryCode,District,Population)
select Name,CountryCode,District,Population
from cityx
其实MySQL不关心名字对不对应，只要位置对应即可
四、用SQL语句删除表
（一）通用语法
DROP TABLE table_name ;
(二）不同类型
1.DROP
单独使用的时候是删除整个表
DROP TABLE <表名>;
drop和alter一起使用的时候可以指定column删除某个列
alter table test
drop column name;
2.DELETE
单独使用delete语句会留下表，而删除表中的数据 ，通常和where一起使用
delete from Product
where sale_price>=3000;
注：delete语句不能使用groupby,having,order by子句
另外，delete语句删除对象不是表或者列，而是行 。如果删除单个列的值可以采用update将其更新为Null。
update Product
set regist_date=null
where product_id='0008';
3.TRUNCATE
TRUNCATE TABLE <表名>
如果想从表中删除所有行，就是用TRUNCATE，速度很快。
小结：当不需要表时，用drop，当要保留该表，但要删除所有记录时，用truncate，当要删除部分记录时，用delete.
五、用SQL语句修改表
（一）修改表中列名
ALTER TABLE 表名 CHANGE 列名 新列名 列类型
（二）修改表中数据
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
- 可以同时更新一个或多个字段。
- 可以在 WHERE 子句中指定任何条件。
- 可以在一个单独表中同时更新数据。
（三）删除行和列
1.删除行
从一个表中删除（去掉）数据，使用DELETE语句。
2.删除列
ALTER TABLE 表名 DROP COLUMN 列名 ；
（四）新建行和列
1.新建行
INSERT...INTO 表名（字段名）VALUES（）；
2.新建列
ALTER TABLE 表名 ADD 列名 数据类型；
对比：alter和insert都有插入的作用，但是alter插入的是列（字段），insert插入的是行（记录）
此外，注意：
- 添加字段和删除字段不同，一个需要指明column一个不需要
- alter和update都有更改表中某列数据的作用，但是alter更注重的是该列的数据结构，update则改变列的内容。
- 当alter和drop一起使用删除某个列的时候，该列没有了，但update是该列变成null还是存在该列的
练习
项目三：超过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 course
(
student VARCHAR(50) NOT NULL,
class VARCHAR(50) NOT NULL
);
INSERT INTO course(student,class) VALUES ('A','Math');
INSERT INTO course(student,class) VALUES ('B','English');
INSERT INTO course(student,class) VALUES ('C','Math');
INSERT INTO course(student,class) VALUES ('D','Biology');
INSERT INTO course(student,class) VALUES ('E','Math');
INSERT INTO course(student,class) VALUES ('F','Computer');
INSERT INTO course(student,class) VALUES ('G','Math');
INSERT INTO course(student,class) VALUES ('H','Math');
INSERT INTO course(student,class) VALUES ('I','Math');
INSERT INTO course(student,class) VALUES ('A','Math');
select class from course group by class having count(distinct student) >=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 NOT NULL ,
n_ame VARCHAR(50) NOT NULL,
sex VARCHAR(50) NOT NULL,
salary INT NOT NULL
);
INSERT INTO salary(id,n_ame,sex,salary) VALUES (1,'A','m',2500);
INSERT INTO salary(id,n_ame,sex,salary) VALUES (2,'B','f',1500);
INSERT INTO salary(id,n_ame,sex,salary) VALUES (3,'C','m',5500);
INSERT INTO salary(id,n_ame,sex,salary) VALUES (4,'D','f',500);
UPDATE salary SET sex = IF(sex = 'm','f','m');
在这里插入图片描述
参考资料：
1.MySQL 教程
2.MYSQLTASK3---表的删除，更新，更改
3.Datawhale-MySQL-任务三
