MySQL学习笔记7（库和表的管理、约束）

一、库和表的管理

#DDL数据定义语言
/*
关键词
create、alter、drop
库的操作：创建库（★）、删除库
表的操作：创建表（★）、修改表、删除表（★）、复制表
*/

1、库的操作

SHOW DATABASES;

1.创建库

CREATE DATABASE student;
CREATE DATABASE IF NOT EXISTS student;

2.删除库

DROP DATABASE student;
DROP DATABASE IF EXISTS student;

2、表的操作

1.创建表

#语法：
CREATE TABLE 表名(
字段名 字段类型【(长度)】 【约束】,
字段名 字段类型【(长度)】 【约束】,
字段名 字段类型【(长度)】 【约束】,
字段名 字段类型【(长度)】 【约束】
)

#案例：创建学员信息表
#学号、姓名、性别、邮箱、生日
CREATE TABLE stuinfo(
stuNo INT,#学号
stuName VARCHAR(20),#姓名
gender CHAR(1),#性别
email VARCHAR(50),#邮箱
borndate DATETIME #生日

)
SHOW TABLES;
DESC stuinfo;

2.修改表

#①修改表名
ALTER TABLE stuinfo RENAME TO student;
ALTER TABLE student RENAME stuinfo;
尖叫提示：to 是可以省略的

#②修改列名,不加类型 报错，需要加类型(修改列的名字)
ALTER TABLE stuinfo CHANGE COLUMN borndate birthday DATETIME;
ALTER TABLE stuinfo CHANGE COLUMN birthday borndate ; ×

change
#③修改列的类型
ALTER TABLE stuinfo MODIFY COLUMN gender VARCHAR(2);

#④添加新列
ALTER TABLE stuinfo ADD COLUMN phone VARCHAR(11);

#⑤删除列
ALTER TABLE stuinfo DROP COLUMN phone;

3.删除表

DROP TABLE stuinfo;
DROP TABLE IF EXISTS stuinfo;
SHOW TABLES;

4.表的复制

INSERT INTO stuinfo VALUES(2,‘少杰’,‘男’,’’,NOW());
SELECT * FROM stuinfo;

#①仅仅复制表的结构
CREATE TABLE newTable2 LIKE stuinfo;

#②复制表的结构+数据
CREATE TABLE newTable3
SELECT * FROM stuinfo;

CREATE TABLE newTable4
SELECT stuno,stuname
FROM stuinfo WHERE stuno=1;

二、约束

1、列级约束

#常见约束

NOT NULL：非空
该字段的值是必填的，如果不设置该约束，该字段默认是可以为空
DEFAULT:默认
该字段有默认值
CHECK:检查，mysql语法支持，但效果不支持
该字段值可以加以限制，比如年龄可以控制在0-120之间

PRIMARY KEY:主键
该字段值不可以重复
1、不可以为空
2、一个表中只能有一个主键，但是可以多个字段做组合主键
UNIQUE:唯一约束
该字段值不可以重复
1、可以为空
2、一个表中可以有多个唯一键，可以有组合唯一键

FOREIGN KEY:外键
用于限制多表的关系
1、从表的该字段值必须来自于主表的关联列的值
2、从表和主表的关联列的类型必须一样
3、主表的关联列必须是主键
4、在从表中设置外键
5、一个表中可以添加多个外键
6、插入数据时，先插入主表
删除数据时，先删除从表

分类：

	位置	支持
列级约束	列的后面	除了外键
表级约束	表的后面	除了非空和默认

#CREATE TABLE 表名(
#字段名 字段类型 列级约束,
#字段名 字段类型 列级约束,
#表级约束
#)

#1.添加列级约束 shift + f3
use test;
drop table if exists stuinfo;
create table stuinfo(
id int not null unique,#非空约束+唯一约束
stuname varchar(20) unique,#唯一约束
gender char default ‘男’,#默认约束
age int unsigned check (age between 0 and 120)#检查约束
);
desc stuinfo;
select * from stuinfo;

#插入默认值
insert into stuinfo(id,stuname,age) values(3,‘lily’,12);
insert into stuinfo values(5,‘jack’,default,900);

#唯一和主键
#1.关于插入null
insert into stuinfo values(7,null,default,900);

#2.关于插入多个唯一或主键
drop table if exists stuinfo;
create table stuinfo(
id int not null unique,#非空约束+主键约束
stuname varchar(20) unique,#唯一约束
gender char default ‘男’,#默认约束
age int unsigned check (age between 0 and 120)#检查约束
);

2、表级约束

#添加表级约束
#语法
create table stuinfo(
id int ,
stuname varchar(20),
gender char,
age int unsigned,
【constraint 约束名】约束类型(字段)
);

#案例
create table grade(
id int primary key,
gradename varchar(20)
)

drop table if exists stuinfo;
create table stuinfo(
id int not null,
stuname varchar(20),
gender char default ‘男’,
age int unsigned,
gradeid int,
primary key(id,stuname),#组合主键：id+stuname
constraint uq unique(age),#唯一键
constraint fk_stuinfo foreign key(gradeid) references grade(id)#外键约束
);

DESC stuinfo;

SELECT * FROM grade;
#①组合主键
INSERT INTO grade VALUES(1,‘一年级’),(2,‘二年级’),(3,‘三年级’);
INSERT INTO stuinfo VALUES(2,‘lucy’,‘男’,233,1);

#②外键
INSERT INTO stuinfo VALUES(3,‘lucy’,‘男’,13,10);

3、修改表时添加约束

#案例：
drop table if exists stuinfo;
create table stuinfo(
id int ,
stuname varchar(20),
gender char,
age int unsigned,
gradeid int
);
desc stuinfo;

#1.添加非空
ALTER TABLE stuinfo MODIFY COLUMN gender CHAR NOT NULL;

#2.添加默认
ALTER TABLE stuinfo MODIFY COLUMN age INT UNSIGNED DEFAULT 18;

#3.添加主键
#①方式一：列级约束的做法
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;

#②方式二：表级约束的做法
#语法：
#ALTER TABLE 表名 ADD [CONSTRAINT 约束名] 约束类型 (字段);

#案例：
ALTER TABLE stuinfo ADD PRIMARY KEY(id);

#4.添加唯一

#①方式一：列级约束的做法
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) UNIQUE;

#②方式二：表级约束的做法
ALTER TABLE stuinfo ADD CONSTRAINT uq_stuinfo UNIQUE(stuname);

#5.添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_grade FOREIGN KEY(gradeid) REFERENCES grade(id);

4、修改表时删除约束

#1.删除非空
ALTER TABLE stuinfo MODIFY COLUMN gender CHAR ;

#2.删除默认
ALTER TABLE stuinfo MODIFY COLUMN age INT UNSIGNED;

#3.删除主键
ALTER TABLE stuinfo MODIFY COLUMN id INT ;
ALTER TABLE stuinfo DROP PRIMARY KEY;

#4.删除唯一
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20);
ALTER TABLE stuinfo DROP INDEX uq_stuinfo;

#查看指定表的所有索引
SHOW INDEX FROM stuinfo;

#5.删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_grade;