数据库之约束和表操作

约束
auto_increment    自增长
    只能有一个自增字段，并且必须约束为key
    针对所有表的配置
        设置自增的初始值--auto_increment-offset
            会话级别（当前会话中） ---- set session auto_increment_offset=初始值
            全局级别 --- set global auto_increment_offset=初始值

        设置自增的步长--auto_increment_increment
            会话级别（当前会话中） ---- set session auto_increment_increment=初始值
            全局级别 --- set global auto_increment_increment=初始值


    show variables like '%incre%'; -- 查看跟incre相关的信息
    设置完之后要退出重新进
    初始值要小于等于步长---初始值如果大于步长，那么初始值就会被忽略


    针对一个表的
        创建字段后加auto_increment=初始值

------------------t1------------------
create table t1(
    id int primary key auto_increment,
    name varchar(20)
    );
desc t1;
insert into t1(name) values('alex'),('egon');
show create table t1;


结果：
mysql> create table t1(
    ->     id int primary key auto_increment,
    ->     name varchar(20)
    ->     );
Query OK, 0 rows affected (0.25 sec)

mysql> desc t1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.02 sec)

mysql> show create table t1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                              |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


------------------t2------------------
字段最后面设置初始值
create table t2(
    id int primary key auto_increment,
    name varchar(20)
    )auto_increment=5;
desc t2;
insert into t2(name) values('alex'),('egon');
show create table t2;

结果：

mysql> create table t2(
    ->     id int primary key auto_increment,
    ->     name varchar(20)
    ->     )auto_increment=5;
Query OK, 0 rows affected (0.25 sec)

mysql> desc t2;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.02 sec)

mysql> insert into t2(name) values('alex'),('egon');
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show create table t2;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                              |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)





------------------t3------------------
更改全局的自增初始值和步长，注意初始值不能大于步长。
会话级别的就是当前会话中

set global auto_increment_offset=2;
set global auto_increment_increment=2;
注意设置完之后要退出重新进才能生效

create table t3(
    id int primary key auto_increment,
    name varchar(20)
    );
desc t3;
insert into t3(name) values('alex'),('egon');
show create table t3;
select * from t3;

结果：
mysql> set global auto_increment_offset=2;
Query OK, 0 rows affected (0.03 sec)

mysql> set global auto_increment_increment=2;
Query OK, 0 rows affected (0.00 sec)

mysql> \q
Bye

mysql> use day43;
Database changed
mysql> create table t3(
    ->     id int primary key auto_increment,
    ->     name varchar(20)
    ->     );
Query OK, 0 rows affected (0.30 sec)

mysql> desc t3;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.03 sec)

mysql> insert into t3(name) values('alex'),('egon');
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show create table t3;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                              |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t3    | CREATE TABLE `t3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> select * from t3;
+----+------+
| id | name |
+----+------+
|  2 | alex |
|  4 | egon |
+----+------+
2 rows in set (0.00 sec)

 

froeign key   外键
    先创建被关联的表，
    constraint 自己起个名字（可以不写） foreign key(要关联的字段) references 被关联的表(被关联的表的字段)
    on delete cascade  # 被关联的表删记录同时删
    on update cascade,   # 被关联的表更新记录同时更新

    多对一：一个表的多条记录对应被关联表的一条记录
        单向的foreign key 就能实现

    多对多：双向的多对一
        双向的foreign key
        建第三张表，用来放两个表的关系字段

    一对一：一个表的一条记录对应被关联表的一条记录
    foreign key 加 unique 去实现


    如何找两张表的关系
        1--先站在一张表上看
        2--看两张表的信息
        3--找到两张表的关系

    复制表
        复制表结构＋记录 （key不会复制: 主键、外键和索引）
        create table new_service select * from service;

        只复制表结构
        select * from service where 1=2;        //条件为假，查不到任何记

        like 关键字 复制表结构，主键可以拷贝，外键不拷贝，目录也不拷贝
        create table t4 like employees;

    concat() -- 字符串拼接
    例：select concat('名字:',id ,'分数:',number) from score;

------------------dep和emp------------------

先创建被关联的表
create table dep(
    id int primary key auto_increment,
    name char(20) not null,
    comment varchar(50)
)auto_increment=101;

insert into dep(name,comment) values
    ('IT','aaaaaa'),
    ('Sale','ssssssssss');

select * from dep;

要关联上面的表
create table emp(
    id int primary key auto_increment,
    name char(20) not null,
    sex enum('male','female'),
    dep_id int,
    foreign key(dep_id) references dep(id) on delete cascade on update cascade
);

insert into emp(name,sex,dep_id) values
    ('alex','male',101),
    ('egon','male',102),
    ('yh','male',101),
    ('eva','female',102);


select * from emp;






结果：
mysql> create table dep(
    ->     id int primary key auto_increment,
    ->     name char(20) not null,
    ->     comment varchar(50)
    -> )auto_increment=101;
Query OK, 0 rows affected (0.32 sec)

mysql> insert into dep(name,comment) values
    ->     ('IT','aaaaaa'),
    ->     ('Sale','ssssssssss');
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from dep;
+-----+------+------------+
| id  | name | comment    |
+-----+------+------------+
| 101 | IT   | aaaaaa     |
| 102 | Sale | ssssssssss |
+-----+------+------------+
2 rows in set (0.00 sec)



mysql> create table emp(
    ->     id int primary key auto_increment,
    ->     name char(20) not null,
    ->     sex enum('male','female'),
    ->     dep_id int,
    ->     foreign key(dep_id) references dep(id) on delete cascade on update cascade
    -> );
Query OK, 0 rows affected (0.19 sec)

mysql> insert into emp(name,sex,dep_id) values
    ->     ('alex','male',101),
    ->     ('egon','male',102),
    ->     ('yh','male',101),
    ->     ('eva','female',102);
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from emp;
+----+------+--------+--------+
| id | name | sex    | dep_id |
+----+------+--------+--------+
|  1 | alex | male   |    101 |
|  2 | egon | male   |    102 |
|  3 | yh   | male   |    101 |
|  4 | eva  | female |    102 |
+----+------+--------+--------+
4 rows in set (0.00 sec)




删除一个部门时 ，相对应的员工信息也应被删掉

delete from dep where id=102;
select * from dep;
select * from emp;

结果：
mysql> delete from dep where id=102;
Query OK, 1 row affected (0.23 sec)

mysql> select * from dep;
+-----+------+---------+
| id  | name | comment |
+-----+------+---------+
| 101 | IT   | aaaaaa  |
+-----+------+---------+
1 row in set (0.00 sec)

mysql> select * from emp;
+----+------+------+--------+
| id | name | sex  | dep_id |
+----+------+------+--------+
|  1 | alex | male |    101 |
|  3 | yh   | male |    101 |
+----+------+------+--------+
2 rows in set (0.00 sec)

 

表操作
    1. 修改表名
        ALTER TABLE 表名 RENAME 新表名;

    2. 增加字段
        ALTER TABLE 表名 ADD 字段名  数据类型 [完整性约束条件…],
        ALTER TABLE 表名 ADD 字段名  数据类型 [完整性约束条件…];
        ALTER TABLE 表名 ADD 字段名  数据类型 [完整性约束条件…]  FIRST;
        ALTER TABLE 表名 ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;

    3. 删除字段
          ALTER TABLE 表名 DROP 字段名;

    4. 修改字段
        ALTER TABLE 表名 MODIFY  字段名 数据类型 [完整性约束条件…];
        ALTER TABLE 表名 CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
        ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

    5. 修改存储引擎
        alter table 表名 engine=存储引擎;

    6. 增加约束（针对已有的主键增加auto_increment）
        alter table 表名 modify 字段 字段类型 not null auto_increment;

    6. 对已经存在的表增加复合主键
        alter table 表名 add primary key(字段,字段);

    7. 增加主键
        alter table 表名 modify 字段 字段类型 not null primary key;

    8. 增加主键和自动增长
        alter table 表名 modify 字段 字段类型 not null primary key auto_increment;

    9. 删除主键
        a. 删除自增约束
            alter table 表名 modify 字段 字段类型 not null;

        b. 删除主键
            alter table 表名 drop primary key;

作业：

关系明细：
1--学生和课程  多对多

2--学生和班级  多对多

3--老师和课程  多对一

4--班级和课程 一对一

先创建course
create table course(
    id int primary key auto_increment,
    name char(10) not null
)engine=innodb;
insert into course(name) values('python'),('linux'),('go'),('java');
select * from course;

结果：
mysql> create table course(
    ->     id int primary key auto_increment,
    ->     name char(10) not null
    -> )engine=innodb;
Query OK, 0 rows affected (0.21 sec)

mysql> insert into course(name) values('python'),('linux'),('go'),('java');
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from course;
+----+--------+
| id | name   |
+----+--------+
|  1 | python |
|  2 | linux  |
|  3 | go     |
|  4 | java   |
+----+--------+
4 rows in set (0.00 sec)

创建class -- 和课程是一对一
create table class(
    id int primary key auto_increment,
    caption char(10),
    c_id int,
    foreign key(c_id) references course(id) on delete cascade on update cascade
)engine=innodb;
insert into class(caption,c_id) values('s7',2),('s5',1),('s6',4),('s4',3);
select * from class;

结果：
mysql> create table class(
    ->     id int primary key auto_increment,
    ->     caption char(10),
    ->     c_id int,
    ->     foreign key(c_id) references course(id) on delete cascade on update cascade
    -> )engine=innodb;
Query OK, 0 rows affected (0.22 sec)

mysql> insert into class(caption,c_id) values('s7',2),('s5',1),('s6',4),('s4',3);
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from class;
+----+---------+------+
| id | caption | c_id |
+----+---------+------+
|  1 | s7      |    2 |
|  2 | s5      |    1 |
|  3 | s6      |    4 |
|  4 | s4      |    3 |
+----+---------+------+
4 rows in set (0.00 sec)

创建teacher  -- 和课程是多对一的
create table teacher(
    id int primary key auto_increment,
    name char(10),
    sex enum('male','female'),
    c_id int,
    foreign key(c_id) references course(id) on delete cascade on update cascade
)engine=innodb;
insert into teacher(name,sex,c_id) values
    ('alex','male',1),
    ('egon','male',2),
    ('eva','female',1),
    ('yh','male',3),
    ('wpq','male',4);
select * from teacher;

结果：
mysql> create table teacher(
    ->     id int primary key auto_increment,
    ->     name char(10),
    ->     sex enum('male','female'),
    ->     c_id int,
    ->     foreign key(c_id) references course(id) on delete cascade on update cascade
    -> )engine=innodb;
Query OK, 0 rows affected (0.17 sec)

mysql> insert into teacher(name,sex,c_id) values
    ->     ('alex','male',1),
    ->     ('egon','male',2),
    ->     ('eva','female',1),
    ->     ('yh','male',3),
    ->     ('wpq','male',4);
Query OK, 5 rows affected (0.03 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from teacher;
+----+------+--------+------+
| id | name | sex    | c_id |
+----+------+--------+------+
|  1 | alex | male   |    1 |
|  2 | egon | male   |    2 |
|  3 | eva  | female |    1 |
|  4 | yh   | male   |    3 |
|  5 | wpq  | male   |    4 |
+----+------+--------+------+
5 rows in set (0.00 sec)

创建student  和 课程，班级都是多对多的
create table student(
    id int primary key auto_increment,
    name char(10),
    sex enum('male','female')
)engine=innodb;
insert into student(name,sex) values
    ('joe','male'),
    ('july','female'),
    ('lily','female'),
    ('json','male'),
    ('mark','male'),
    ('mike','male');
select * from student;

结果：
mysql> create table student(
    ->     id int primary key auto_increment,
    ->     name char(10),
    ->     sex enum('male','female')
    -> )engine=innodb;
Query OK, 0 rows affected (0.17 sec)

mysql> insert into student(name,sex) values
    ->     ('joe','male'),
    ->     ('july','female'),
    ->     ('lily','female'),
    ->     ('json','male'),
    ->     ('mark','male'),
    ->     ('mike','male');
Query OK, 6 rows affected (0.10 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+------+--------+
| id | name | sex    |
+----+------+--------+
|  1 | joe  | male   |
|  2 | july | female |
|  3 | lily | female |
|  4 | json | male   |
|  5 | mark | male   |
|  6 | mike | male   |
+----+------+--------+
6 rows in set (0.00 sec)

class和student关系表
create table class_stu(
    id int primary key auto_increment,
    class_id int,
    stu_id int,
    foreign key(class_id) references class(id) on delete cascade on update cascade,
    foreign key(stu_id) references student(id) on delete cascade on update cascade
);
insert into class_stu(class_id,stu_id) values
    (1,2),
    (1,5),
    (2,4),
    (2,1),
    (3,4),
    (3,6),
    (4,3),
    (4,2);
select * from class_stu;

结果：
mysql> create table class_stu(
    ->     id int primary key auto_increment,
    ->     class_id int,
    ->     stu_id int,
    ->     foreign key(class_id) references class(id) on delete cascade on update cascade,
    ->     foreign key(stu_id) references student(id) on delete cascade on update cascade
    -> );
Query OK, 0 rows affected (0.30 sec)

mysql> insert into class_stu(class_id,stu_id) values
    ->     (1,2),
    ->     (1,5),
    ->     (2,4),
    ->     (2,1),
    ->     (3,4),
    ->     (3,6),
    ->     (4,3),
    ->     (4,2);
Query OK, 8 rows affected (0.25 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> select * from class_stu;
+----+----------+--------+
| id | class_id | stu_id |
+----+----------+--------+
|  1 |        1 |      2 |
|  2 |        1 |      5 |
|  3 |        2 |      4 |
|  4 |        2 |      1 |
|  5 |        3 |      4 |
|  6 |        3 |      6 |
|  7 |        4 |      3 |
|  8 |        4 |      2 |
+----+----------+--------+
8 rows in set (0.00 sec)

course 和 studnet 关系表
create table course_stu(
    id int primary key auto_increment,
    stu_id int,
    course_id int,
    foreign key(stu_id) references student(id) on delete cascade on update cascade,
    foreign key(course_id) references course(id) on delete cascade on update cascade
 );
insert into course_stu(stu_id,course_id) values
    (1,1),
    (1,2),
    (2,3),
    (3,4),
    (3,2),
    (4,4),
    (4,1),
    (5,3),
    (6,2);
select * from course_stu;

结果：
mysql> create table course_stu(
    ->     id int primary key auto_increment,
    ->     stu_id int,
    ->     course_id int,
    ->     foreign key(stu_id) references student(id) on delete cascade on update cascade,
    ->     foreign key(course_id) references course(id) on delete cascade on update cascade
    ->  );
Query OK, 0 rows affected (0.33 sec)

mysql> insert into course_stu(stu_id,course_id) values
    ->     (1,1),
    ->     (1,2),
    ->     (2,3),
    ->     (3,4),
    ->     (3,2),
    ->     (4,4),
    ->     (4,1),
    ->     (5,3),
    ->     (6,2);
Query OK, 9 rows affected (0.02 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> select * from course_stu;
+----+--------+-----------+
| id | stu_id | course_id |
+----+--------+-----------+
|  1 |      1 |         1 |
|  2 |      1 |         2 |
|  3 |      2 |         3 |
|  4 |      3 |         4 |
|  5 |      3 |         2 |
|  6 |      4 |         4 |
|  7 |      4 |         1 |
|  8 |      5 |         3 |
|  9 |      6 |         2 |
+----+--------+-----------+
9 rows in set (0.00 sec)