库与表的管理以及常见约束

一、库相关操作

1.1创建数据库
CREATE DATABASE 数据库名 charset utf8;
1.2查看数据库
show databases;
show create database db1;
select database();
1.3选择数据库
USE 数据库名
1.4删除数据库
DROP DATABASE 数据库名;
1.5修改数据库
alter database db1 charset utf8;

二、表相关操作

2.1创建表

#语法：
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);

#注意：
1. 在同一张表中，字段名是不能相同
2. 宽度和约束条件可选
3. 字段名和类型是必须的

2.2查看表结构

desc t1;
show create table t1\G; #查看表详细结构，可加\G

2.3修改表

语法：
1. 修改表名
ALTER TABLE 表名
RENAME 新表名;

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

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

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

2.4复制表

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

只复制表结构
mysql> select * from service where 1=2; //条件为假，查不到任何记录
Empty set (0.00 sec)
mysql> create table new1_service select * from service where 1=2;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> create table t4 like employees;

2.5删除表

DROP TABLE 表名;

三、常见约束

含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性

分类：六大约束

　　not null:非空，用于保证该字段的值不能为空
　　default:默认，用于保证该字段有默认值
　　primary key:主键，用于保证该字段的值具有唯一性，并且飞空
　　unique:唯一，用于保证该字段的值具有唯一性，可以为空
　　check：检查约束[mysql中不支持]
　　foreign key:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表添加外键约束，用于引用主表中某列的值
添加约束的时机：

　　1.创建表时

　　2.修改表时

约束的添加分类：
　　列级约束
　　　　六大约束语法上都支持，但外键约束没有效果
　　表级约束
　　　　除了非空、默认，其他的都支持
语法：
　　直接在字段名和类型后面追加约束类型即可
只支持：默认、非空、主键、唯一
外键：
　　1.要求在从表设置外键关系
　　2.从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
　　3.主表的关联列必须是一个key(一般是主键或唯一)
　　4.插入数据时，先插入主表，再插入从表
　　删除数据时，先删除从表，再删除主表

主键与唯一键的区别：
　　　　保证唯一性 　　　　 是否允许为空 　　　　 一个表中可以有多少个 　　　　 是否允许组合
主键 　　 √ 　　　　　　　　 × 　　　　　　　　　　 至多有一个 　　　　　　　　　　 √，但不推荐
唯一 　　 √ 　　　　　　　　 √ 　　　　　　　　　　 可以有多个 　　　　　　　　　　 √，但不推荐

1.创建表时添加约束

1.添加列级约束
create database students;
use students;
create table major(
    id int primary key,
    majorname varchar(20)
);
create table stuinfo(
    id int primary key,
    stuName varchar(20) not null,
    gender char(1) check(gender="男" or gender="女"),#没效果，加加看语法
    seat int unique,
    age int default 18
);
desc stuinfo;#查看下表结构

2.添加表级约束
drop table if exists stuinfo;
create table stuinfo(
    id int,
    stuName varchar(20),
    gender char(1),
    seat int,
    age int,
    majorid int,
    constraint pk primary key(id),#主键
    constraint uq unique(seat),#唯一键
    constraint ck check(gender="男" or gender="女"),#检查
    constraint fk_stuinfo_major foreign key(majorid) references major(id)#外键
)

2.修改表时添加约束

/*
1.添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;
2.添加表级约束
alter table 表名 add [constraint 约束名] 约束类型(字段名) 外键的引用;
*/

drop table if exists stuinfo;
create table stuinfo(
    id int,
    stuName varchar(20),
    gender char(1),
    seat int,
    age int,
    majorid int
)
1.添加非空约束
alter table stuinfo modify column stuname varchar(20) not null;
2.添加默认约束
alter table stuinfo modify column age int default 18;
3.添加主键
3.1列级约束
alter table stuinfo modify column id int primary key;
3.2表级约束
alter table stuinfo add    primary key(id);
4.添加唯一约束
alter table stuinfo modify column seat int unique;
alter table stuinfo add unique(seat);
5.添加外键
alter table stuinfo add constraint fk_stuinfo_major foreign key(majorid) references major(id);

3.修改表时删除约束

1.删除非空约束
alter table stuinfo modify column stuName varchar(20) null;
2.删除默认约束
alter table stuinfo modify column age int;
3.删除主键
alter table stuinfo drop primary key;
4.删除唯一
alter table stuinfo drop index seat;
5.删除外键
alter table stuinfo drop foreign key majorid;