MySQL Day 02

1、数据约束（！）

1.1、默认值的情况（这种情况多见）

--在插入数据的时候，若没有给定当前字段赋值，则使用默认值自动填充
--default
mysql > creat table person2(
	id int,
	name varchar(30)
	nationality char(20) default "PRC"
    );

--一下情况会出现默认值
mysql > insert into person2(id, name) values(3, "萧敬腾");

1.2、非空

--not null
--修饰字段
mysql> create table person3(
    -> id int not null,
    -> name varchar(30) not null,
    -> info text
    -> );
    
--错误提示
ERROR 1364 (HY000): Field 'id' doesn't have a default value

1.3、唯一

--unique
--要求修饰的字段下面的数据不能有重复的

mysql> create table person4(
    -> id int,
    -> name varchar(30) unique,
    -> info text
    -> );
    
--错误提示
ERROR 1062 (23000): Duplicate entry '000' for key 'name'

1.4、主键（重点）

--是unique和not null的组合，唯一且非空
--primary key
--主键一般是该行数据的唯一的一个索引，并且修饰的这个字段只能类似于id字段
--1.设计数据库的主键时，一般将一个与业务逻辑无关的字段设置为主键
--2.

mysql> create table person5(
    -> id int primary key,
    -> name varchar(30),
    -> info text
    -> );
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| info  | text        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

1.5、自增长

--插入数据的时候会自己增加，一般和主键一起使用类似于id的字段
--auto_increment
--修饰的数据类型必须是整型
mysql> create table person6(
    -> id int primary key auto_increment,
    -> name varchar(30),
    -> info text
    -> );

1.6、外键的约束（了解）

--员工表
--timestamp 时间戳：从1970年1月1日0点0分0秒到现在的秒数

mysql> create table employee(
    -> id int primary key auto_increment,
    -> empName varchar(30) not null,
    -> deptName varchar(30) not null,
    -> regTime timestamp default current_timestamp
    -> );
Query OK, 0 rows affected (0.23 sec)

mysql> insert into employee(empName, deptName) values("成龙", "电影");
Query OK, 1 row affected (0.09 sec)

mysql> insert into employee(empName, deptName) values("周杰伦", "歌曲");
Query OK, 1 row affected (0.05 sec)

mysql> insert into employee(empName, deptName) values("梅兰芳", "戏曲");
Query OK, 1 row affected (0.05 sec)

mysql> insert into employee(empName, deptName) values("林俊杰", "歌曲");
Query OK, 1 row affected (0.05 sec)

mysql> insert into employee(empName, deptName) values("孙红雷", "电影");
Query OK, 1 row affected (0.07 sec)

mysql> insert into employee(empName, deptName) values("程砚秋", "戏曲");
Query OK, 1 row affected (0.07 sec)

mysql> insert into employee(empName, deptName) values("荀慧生", "戏曲");
Query OK, 1 row affected (0.06 sec)

mysql> insert into employee(empName, deptName) values("尚小云", "戏曲");
Query OK, 1 row affected (0.05 sec)

mysql> insert into employee(empName, deptName) values("阿信", "歌曲");
Query OK, 1 row affected (0.05 sec)

mysql> insert into employee(empName, deptName) values("王菲", "歌曲");
Query OK, 1 row affected (0.07 sec)

mysql> insert into employee(empName, deptName) values("陈道明", "电影");
Query OK, 1 row affected (0.05 sec)

mysql> insert into employee(empName, deptName) values("黄秋生", "电影");
Query OK, 1 row affected (0.05 sec)

--不直观，有数据冗余
--使用两个表进行关联
mysql> create table dept(
    -> id int primary key auto_increment,
    -> deptName varchar(30) not null
    -> );
Query OK, 0 rows affected (0.23 sec)

mysql> create table emp(
    -> id int primary key auto_increment,
    -> empName varchar(30) not null,
    -> deptId int not null,
    -> regTime timestamp default current_timestamp
    -> );
Query OK, 0 rows affected (0.22 sec)

--外键的约束
mysql> create table emp(
    -> id int primary key auto_increment,
    -> deptId int not null,
    -> regTime timestamp default current_timestamp,
    -> constraint fk_emp_dept foreign key(deptId) references dept(id)
    -> );--使用外键约束的目的，让员工表中的deptId和部门表中的id进行关联
    --fk_emp_dept 外键名称
    --foreign key(deptId) 声明当前表中的哪一个字段需要作为外键
    --references dept(id) 关联到dept表里的id字段
    
    
--总结：外键约束之后会有如下特点
	--1.先添加主表（部门表），再添加从表（员工表）
	--2.先删除从表，再添加主表
	--3.先修改从表，再修改主表
	--使用外键约束之后，数据表会变得非常乱，不利于思考

1.7、级联操作

--使用外键的时候，操作从表和主表都会存在问题，可以使用级联
--级联删除 on delete cascade
--级联修改 on update cascade

--写在外键的创建后面

2、联表查询

2.1、内连接和外连接

--员工表和部门表是有一定的联系的，我们期望的查询结果是带有部门的某个人

--例如查黄秋生，期望结果如下
--7 黄秋生 电影 时间戳

--考虑：
--1.需要查什么
--2.从哪里查
--3.查询条件是什么

--期望获取员工编号，员工名字， 所属部门名字
mysql > select emp.id as "编号", emp.empName as "姓名", dept.deptName as "所属部门", regTime as "注册时间" from emp, dept where emp.dept = dept.id;

+--------+-----------+--------------+---------------------+
| 编号   | 姓名      | 所属部门     	| 注册时间            |
+--------+-----------+--------------+---------------------+
|      1 | 成龙      | 电影         | 2020-06-03 15:07:53 |
|      7 | 黄秋生    | 电影         | 2020-06-03 15:14:12 |
|      8 | 陈道明    | 电影         | 2020-06-03 15:14:16 |
|      9 | 孙红雷    | 电影         | 2020-06-03 15:14:41 |
|      2 | 王菲      | 歌曲         | 2020-06-03 15:08:07 |
|     10 | 周杰伦    | 歌曲         | 2020-06-03 15:17:13 |
|     11 | 阿信      | 歌曲         | 2020-06-03 15:17:20 |
|     12 | 林俊杰    | 歌曲         | 2020-06-03 15:17:31 |
|      3 | 梅艳芳    | 戏曲         | 2020-06-03 15:13:35 |
|      4 | 荀慧生    | 戏曲         | 2020-06-03 15:13:44 |
|      5 | 尚小云    | 戏曲         | 2020-06-03 15:13:55 |
|      6 | 程砚秋    | 戏曲         | 2020-06-03 15:14:03 |
+--------+-----------+--------------+---------------------+

--给表起个别名
mysql> select e.id as "编号", e.empName as "姓名", d.deptName as "所属部门", regTime as "注册时间" from emp e, dept d where e.deptId = d.id;
--结果与上面相同



--内连接的查询 inner join on

mysql > select e.id as "编号", e.empName as "姓名", d.deptName as "所属部门", regTime as "注册时间" 
from employee e 
inner join dept d --使用内连接查询，连接了另外一张表
on e.deptId = d.id;

--外连接（了解）
--左外连接查询
--left outer join
--左边的表全部展示，右边的表去匹配左边的表。匹配到就显示数据，匹配不到就填充。
mysql> select d.deptName, e.empName
    -> from dept d
    -> left outer join emp e
    -> on e.deptId = d.id;