MySQL学习(四)数据定义DDL语言
一、库的管理
-
库的创建
create database 【if not exists】 库名【 character set 字符集名】;#案例:创建库Books create database books; create database if not exists books;
-
库的修改
alter database 库名 character set 字符集名;#更改库的字符集 ALTER DATABASE books CHARACTER SET utf8; ALTER DATABASE books CHARACTER SET gbk;
库的重命名:
-
库的删除
drop database 【if exists】 库名;drop database if exists books;
二、表的管理
-
表的创建:
语法:
create table 表名( 列名 列的类型【(长度) 约束】, 列名 列的类型【(长度) 约束】, 列名 列的类型【(长度) 约束】, ... 列名 列的类型【(长度) 约束】 )
CREATE TABLE IF NOT EXISTS stuinfo( id INT PRIMARY KEY, stuname VARCHAR(20), sex CHAR(1), age INT DEFAULT 18, seat INT UNIQUE, majorid INT, CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键 );
-
表的修改:
语法:
alter table 表名 add|drop|modify|change column 列名 【列类型 约束】; 添加列:alter table 表名 add column 列名 类型 【first | after 字段名】; 修改列的类型或约束:alter table 表名 modify column 列名 新类型 【新约束】; 修改列名:alter table 表名 change column 旧列名 新列名 类型; 删除列:alter table 表名 drop column 列名; 修改表名:alter table 表名 rename 【to】 新表名;
-
修改列名
alter table 表名 change column 旧列名 新列名 类型;alter table book change column publishdate pubdate datetime;
-
修改列的类型或约束
alter table 表名 modify column 列名 新类型 【新约束】;#添加主键 alter table stuinfo modify column id int primary key;
-
添加新列
alter table 表名 add column 列名 类型 【first|after 字段名】;alter table author add column annual double;
-
删除列
alter table 表名 drop column 列名;alter table author drop column annual; ALTER TABLE book_author DROP COLUMN annual;
-
修改表名
alter table 表名 rename 【to】 新表名;alter table author rename to book_author;
-
-
表的删除:
drop table【if exists】 表名;drop table if exists book_author; drop table book_author;
-
表的复制:
-
仅仅复制表的结构:
create table 表名 like 旧表;create table copy_author like author;#没有数据
-
复制表的结构+数据
create table 表名 select 查询列表 from 旧表【where 筛选】;create table copy2_author select * from author;
-
只复制部分数据
create table copy3_author select id,au_name from author where nation = '中国';
-
三、常见数据类型
-
整型:
分类:
- tinyint、smallint、mediumint、int/integer、bigint
特点:
- 如果不设置无符号还是有符号,默认是有符号,如果想设置无符号,需要添加unsigned关键字
- 如果插入的数值超出了整型的范围,会报out of range异常,并且插入临界值
- 如果不设置长度,会有默认的长度
长度代表了显示的最大宽度,如果不够会用0在左边填充,但必须搭配zerofill使用!,此时默认为无符号的
create table tab_int( t1 int,#如果不设置长度,会有默认的长度,int型的默认长度为11 t2 int(7) unsigned ,#插入无符号整型 t3 int(7) zerofill #使用zerofill关键字,最大宽度如果不够会用0在左边填充 )
insert into tab_int values(123,123,123); select * from tab_int;
查询结果:
- tinyint、smallint、mediumint、int/integer、bigint
-
浮点型:
分类:
- 浮点型
float(M,D)
double(M,D) - 定点型
dec(M,D)
decimal(M,D)
特点:
- M代表整数部位+小数部位的个数,D代表小数部位
- 如果超出范围,则报out or range异常,并且插入临界值
- M和D都可以省略,但对于定点数,M默认为10,D默认为0
- 如果精度要求较高,则优先考虑使用定点数
create table tab_float( f1 float(5,2), f2 double(5,2), f3 decimal(5,2) ) insert into tab_float values(123.45,123.45,123.45); insert into tab_float values(123.456,123.456,123.456);# 超过小数位数,四舍五入 insert into tab_float values(123.4,123.4,123.4); select * from tab_float;
查询结果:
- 浮点型
-
字符型:
分类:
- 较短的文本:
char
varchar - 其他:
binary和varbinary用于保存较短的二进制
enum用于保存枚举
set用于保存集合 - 较长的文本:
text
blob(较大的二进制)
特点:
- char:固定长度的字符,写法为char(M),最大长度不能超过M,其中M可以省略,默认为1
- varchar:可变长度的字符,写法为varchar(M),最大长度不能超过M,其中M不可以省略
create table tab_char( c1 enum('a','b','c') ) INSERT INTO tab_char VALUES('a'); INSERT INTO tab_char VALUES('b'); INSERT INTO tab_char VALUES('c'); INSERT INTO tab_char VALUES('m');# 插入失败 INSERT INTO tab_char VALUES('A'); create table tab_set( s1 set('a','b','c','d') ); INSERT INTO tab_set VALUES('a'); INSERT INTO tab_set VALUES('A,B'); INSERT INTO tab_set VALUES('a,c,d');
- 较短的文本:
-
日期型:
分类:
- date只保存日期
- time 只保存时间
- year只保存年
- datetime保存日期+时间
- timestamp保存日期+时间
特点:
字节 范围 时区等的影响 datetime 8 1000——9999年 不受 timestamp 4 1970-2038年 受
create table tab_date( t1 datetime, t2 timestamp ); insert into tab_date values(now(),now()); select * from tab_date;
四、常见约束
含义:
- 一种限制,用于限制表中的数据,为了保证表中的数据的准确和可靠性
分类:六大约束
- not null:非空,用于保证该字段的值不能为空。比如姓名、学号等。
- default:默认,用于保证该字段有默认值。比如性别。
- primary key:主键,用于保证该字段的值具有唯一性,并且非空。比如学号、员工编号等。
- unique:唯一,用于保证该字段的值具有唯一性,可以为空。比如座位号。
- check:检查约束【mysql中不支持】。不日年龄、性别。
- foreign key:外键,用于限制两个表的关系,用于保证该字段的值必须来自于主表的关联列的值。在从表添加外键约束,用于应用主表中某列的值。比如学生表的专业编号,员工表的部门编号,员工表的工种编号。
约束添加的分类:
- 列级约束:六大约束语法上都支持,但外键约束没有效果
- 表级约束:除了非空、默认,其他的都支持
位置 支持的约束类型 是否可以起约束名 列级约束 列的后面 语法都支持,但 外键没有效果
不可以 表级约束 所有列的下面 默认和非空不支持
,其他支持可以(主键没有效果)
(一)创建表时添加约束
-
添加列级约束:
- 语法:直接在字段名和类型后面追加约束类型即可。
- 只支持:默认、非空、主键、唯一(除了外键都支持)
create table major( id int primary key, majorName varchar(20) ) create table stuinfo( id int primary key,#主键 stuName varchar(20) not null unique,# 非空,唯一 gender char(1) check(gender='男' or gender='女'),#检查约束 seat int unique,# 唯一约束 age int default 18,#默认约束 majorId int references major(id) #外键约束,但列级约束中没有效果 );
-
添加表级约束:
- 语法:在各个字段的最下面
【constraint 约束名】 约束类型(字段名) - 除了非空、默认,其他的都支持
create table stuinfo( id int, stuname varchar(20), gender char(1), seat 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)#外键 )
- 语法:在各个字段的最下面
-
通用的写法:
CREATE TABLE IF NOT EXISTS stuinfo( id INT PRIMARY KEY, stuname VARCHAR(20), sex CHAR(1), age INT DEFAULT 18, seat INT UNIQUE, majorid INT, CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#在表级约束中定义外键约束 );
-
外键特点
- 要求在从表设置外键关系
- 从表的外键列的类型和主表的关联列的类型要求一致或兼容,名称无要求
- 主表的关联列必须是一个key(一般是主键或唯一)
- 插入数据时,先插入主表,再插入从表;删除数据时,先删除从表,再删除主表
- 方式1:级联删除
删除的时候,主表和从表对应的行都删了。ALTER TABLE stuinfo ad CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;
- 方式2:级联置空
删除的时候,主表对应的行被删除了,从表引入的地方变为空值null。ALTER TABLE stuinfo ad CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;
- 方式1:级联删除
(二)修改表时添加约束
语法:
1、添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;
2、添加表级约束
alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;
-
添加非空约束
alter table stuinfo modify column stuname varchar(20) not null;
-
添加默认约束
alter table stuinfo modify column gender char(1) default '男';
-
添加主键
列级约束 alter table stuinfo modify column id int primary key; 表级约束 alter table stuinfo add primary key(id);
-
添加唯一
列级约束 alter table stuinfo modify column seat int unique; 表级约束 alter table stuinfo add unique(seat);
-
添加外键
alter table stuinfo add constraint fk_stuinfo_major foreign key(majorid) references major(id);
(三)修改表时删除约束
-
删除非空约束
alter table stuinfo modify column stuname varchar(20);
-
删除默认约束
alter table stuinfo modify column gender char(1);
-
删除主键
alter table stuinfo drop primary key;
-
删除唯一
alter table stuinfo drop index seat ;
-
删除外键
alter table stuinfo drop foreign key fk_stuinfo_major;
五、标识列(自增长)
又称为自增长列
含义:可以不用手动的插入值,系统提供默认的序列值
特点:
1、标识列必须和主键搭配吗?不一定,但要求是一个key 2、一个表可以有几个标识列?至多一个! 3、标识列的类型只能是数值型 4、标识列可以通过 SET auto_increment_increment=3;设置步长; 可以通过 手动插入值,设置起始值
-
创建表时设置标识列
drop table tab_identity; create table tab_identity( id int primary key auto_increment, name varchar(20) ); insert into tab_identity values(NULL,'john');# 插入null表示为自增长 SHOW VARIABLES LIKE '%auto_increment%'; #设置标识列的步长 SET auto_increment_increment = 3
-
修改表时设置标识列
alter table tab_identity modify column id int primary key auto_increment;
-
修改表时删除标识列
alter table tab_identity modify column id int;