0403任务三MySQL 基础 （二）- 表操作

一、学习内容

1、MySQL表数据类型

http://www.runoob.com/mysql/mysql-data-types.html
MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

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

2、用SQL语句创建表

表格（table）用来存储数据，表格是一种结构化文件。表格的行被称为记录，表格列被称为字段。表格字段属性：字段名称、字段数据类型、字段长度、字段约束。
2.1 创建表
创建表的时候，表中有字段，每一个字段有：
* 字段名
* 字段数据类型
* 字段长度限制
* 字段约束
创建表的语法：

create table tableName(
   columnName dataType(length),
   ………………..
   columnName dataType(length)
);

如：建立学生信息表，字段包括：学号、姓名、性别、出生日期、email、班级标识。

create table t_student(
	student_id int(10),
	student_name varchar(20),
	sex char(2),
	birthday date,
	email varchar(30),
	classes_id int(3)	
)

2.2 创建表加入约束
常见的约束：
1)非空约束，not null,针对某个字段设置其值不为空，如学生姓名不为空.
2)唯一约束，unique,它可以使某个字段的值不能重复，如email不能重复

create table t_student(
	student_id int(10),
	student_name varchar(20) not null,
	sex char(2),
	birthday date,
	email varchar(30) unique,
	classes_id int(3),
	constraint	fk_id foreign key(classes_id) references t_classes(classes_id)
);

3)主键约束，primary key，主键可以标识记录的唯一性，主键分为单一主键和复合（联合）主键，单一主键是由一个字段构成的，复合（联合）主键是由多个字段构成的。
列级约束：

表级约束：

复合约束：

d)外键约束，foreign key
外键主要是维护表之间的关系的，主要是为了保证参照完整性，如果表中的某个字段为外键字段，那么该字段的值必须来源于参照的表的主键。

外键名是fk_id,clases_id外键，参考列为t_classes表的classes_id列。

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

insert语法格式：
字段和值必须一一对应，个数必须相同，数据类型必须一致。

INSERT INTO tablename(columname1,columname2,columname3)
VALUES(value1,value2,value3);

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

--指定列名
INSERT INTO employee(id,name,phone) VALUES(01,'Tom',110110110); 
--不指定列名
INSERT INTO employee VALUES(02,'Jack',119119119);
--指定列名
INSERT INTO employee(id,name) VALUES(03,'Rose');

最好采用指定列名的方法，若省略表示将表中的所有字段全部加上，省略之后程序不健壮。

4、用SQL语句删除表

• delete删除表内数据。delete from 表名 where 删除条件;
• 清除表内数据，保存表结构，用 truncate。truncate table 表名;
• 删除表用 drop，什么都没有了。drop table 表名;
  1、当你不再需要该表时， 用 drop;
  2、当你仍要保留该表，但要删除所有记录时， 用 truncate;
  3、当你要删除部分记录时， 用 delete。

5、 用SQL语句修改表

• 对一列的修改
  1)增加一列(新建列)

ALTER TABLE 表名字 ADD COLUMN 列名字 数据类型 约束;
或：
ALTER TABLE 表名字 ADD 列名字 数据类型 约束;

新增加的列，被默认放置在这张表的最右边。如果要把增加的列插入在指定位置，则需要在语句的最后使用AFTER关键词(“AFTER 列1” 表示新增的列被放置在 “列1” 的后面)。如果想放在第一列的位置，则使用 **FIRST **关键词。
2)删除一列（删除列）

ALTER TABLE 表名字 DROP COLUMN 列名字;
或： ALTER TABLE 表名字 DROP 列名字;

3)重命名一列

ALTER TABLE 表名字 CHANGE 原列名 新列名 数据类型 约束;

重命名语句后面的数据类型不能省略，否则重命名失败。修改数据类型可能会导致数据丢失，所以要慎重使用。
4)改变数据类型
要修改一列的数据类型，除了使用刚才的 CHANGE 语句外，还可以用这样的 MODIFY 语句：

ALTER TABLE 表名字 MODIFY 列名字 新数据类型;

• 对表的内容修改
  1)修改表中的某个值

UPDATE 表名字 SET 列1=值1,列2=值2 WHERE 条件;

如把 TOM的age改为21，salary改为3000。

UPDATE employee SET age=21,salary=3000 WHERE name='Tom';

2)删除一行记录（删除行）

DELETE FROM 表名字 WHERE 条件;

3)新建行
就是插入了，INSERT

二、作业

1、项目三：超过5名学生的课（难度：简单）

创建表

CREATE TABLE courses(
	student VARCHAR(6),
	class VARCHAR(10)
);

插入值

INSERT INTO courses(student,class)
VALUES('A','Math');
INSERT INTO courses(student,class)
VALUES('B','English');
INSERT INTO courses(student,class)
VALUES('C','Math');
INSERT INTO courses(student,class)
VALUES('D','Biology');
INSERT INTO courses(student,class)
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('A','Math');

+---------+----------+
| student | class    |
+---------+----------+
| A       | Math     |
| B       | English  |
| C       | Math     |
| D       | Biology  |
| E       | Math     |
| F       | Computer |
| G       | Math     |
| H       | Math     |
| I       | Math     |
| A       | Math     |
+---------+----------+

编写一个 SQL 查询，列出所有超过或等于5名学生的课。学生在每个课中不应被重复计算。

select class from courses group by class
having 
    count(distinct student) >= 5; --distinct有重复记录去重
+-------+
| class |
+-------+
| Math  |
+-------+

2、项目四：交换工资（难度：简单）

创建一个 salary表，有m=男性 和 f=女性的值 。交换所有的 f 和 m 值(例如，将所有 f 值更改为 m，反之亦然)。要求使用一个更新查询，并且没有中间临时表。
创建表并插入值

CREATE TABLE salary(
	id int(2) primary key,
	name VARCHAR(6),
	sex CHAR(2),
	salary VARCHAR(10)
);

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