数据定义语言:库和表的管理和操作。
一、库的管理
1.库的创建create
语法:create database [if not exists] 库名;
#创建库books
CREATE DATABASE books;
#再次执行会报错,因为已经存在,所以进行容错性处理:
CREATE DATABASE IF NOT EXISTS books;
2.库的修改alter
#修改库名
RENAME DATABASE books TO 新库名;
#原来可以使用,但不安全,版本更新后取消该用法,若修改库名,去文件夹中修改。
#更改字符集
ALTER DATABASE books CHARACTER SET gbk;
3.库的删除drop
DROP DATABASE IF EXISTS books;
二、表的管理
1.表的创建
语法:
create table 表名(
列名 列的类型 [(长度)约束],
...
);
2.表的修改
①修改列名change
语法:alter table 表名 change column 列名 类型;
ALTER TABLE book CHANGE COLUMN publishDate pubDate DATETIME;
②修改列的类型或约束modify
语法:alter table 表名 modify column 列名 类型 约束;
ALTER TABLE book MODIFY COLUMN pubDate TIMESTAMP;
③添加新列或表级约束add
语法:alter table 表名 add column 列名 类型;
语法:alter table 表名 add 键约束(列名)/键约束 键名;
ALTER TABLE book ADD COLUMN annual DOUBLE;
④删除列或表级约束drop
语法:alter table 表名 drop column 列名;
语法:alter table 表名 add 键约束(列名)/键约束 键名;
ALTER TABLE book DROP COLUMN annual;
⑤修改表名rename to
语法:alter table 表名 rename to 新表名;
ALTER TABLE author RENAME TO book_author;
3.表的删除
DROP TABLE IF EXISTS book_author;
创建需要库/表的通用写法:
DROP DATABASE IF EXISTS 旧库名;
CREATE DATABASE 新库名;
DROP DATABASE IF EXISTS 旧表名;
CREATE DATABASE 新表名;
4.表的复制
①仅仅复制表的结构like
CREATE TABLE copy1 LIKE author;
②复制表的结构+全部数据
CREATE TABLE copy2
SELECT * FROM author;
③只复制部分数据 使用where筛选行
CREATE TABLE copy3
SELECT id,au_name
FROM author
WHERE nation='中国';
④仅仅复制部分字段
CREATE TABLE copy4
SELECT id,au_name
FROM author
WHERE 1=2;
#or
CREATE TABLE copy4
SELECT id,au_name
FROM author
WHERE 0;
库和表的管理案例:
注:跨库查询使用库名.表名
#创建表dept1
CREATE DATABASE test;
USE test;
CREATE TABLE IF NOT EXISTS dept1(
id INT(7),
NAME VARCHAR(25)
);
DESC dept1;
#将表departments中的数据插入新表dept2(跨库查询)
CREATE TABLE dept2
SELECT *
FROM myemployees.`departments`;
#创建表emp5
CREATE TABLE emp5(
id INT(7),
first_name VARCHAR(25),
last_name VARCHAR(25),
dept_id INT(7)
);
#将列last_name的长度增加到50
ALTER TABLE emp5 MODIFY COLUMN last_name VARCHAR(50);
DESC emp5;
#根据表employees创建employees2
CREATE TABLE employees2 LIKE myemployees.`employees`;
#删除表emp5
DROP TABLE IF EXISTS emp5;
#将表employees2重命名为emp5
ALTER TABLE employees2 RENAME TO emp5;
#在表dept和emp5中添加新列test_column,并检查所作的操作
ALTER TABLE dept ADD COLUMN test_column INT;
ALTER TABLE emp5 ADD COLUMN test_column INT;
#直接删除表emp5中的列dept_id
ALTER TABLE emp5 DROP COLUMN dept_id;
三、数据类型
数值型 | 整型:tinyint、smallint、mediumint、int/integer、bigint | |
小数 | 定点数:dec(M,D)/decimal(M,D) | |
浮点数:float、double | ||
字符型 | 较短文本:char、varchar | |
较长文本:text、blob(较长的二进制数据) | ||
日期型 |
1.数值型
(1)整型
分类:tinyint(1)、smallint(2)、mediumint(3)、int/integer(4)、bigint(8)
特点:
①如果不设置无符号还是有符号,默认是有符号,如果想设置无符号,需要添加unsigned关键字;
CREATE TABLE t_int( t1 INT, t2 INT UNSIGNED );
②如果插入的数值超出了整型的范围,会报out of range异常,并且插入临界值;
③如果不设置长度,会有默认的长度;
④长度代表了显示的最大宽度,可以超出,如果不够会用0来填充,但必须搭配zerofill使用!
(2)小数
浮点型:float(M,D)(4)、double(M,D)(8)
定点型:dec(M,D)/decimal(M,D)
特点:
①M代表整数部位+小数部位,D代表小数部位,如果超出范围,则插入临界值。
②M和D都可以省略,但如果是decimal,则m默认为10,d默认为0;如果是float和double,则会根据插入的数值的精度来决定。
③定点型的精确度较高,如果要求插入数值的精度较高,如货币运算等,则考虑使用定点型。
④原则:所选择的类型越简单越好,能保存数值的类型越小越好。
2.字符型
M代表最大字符数
char(M) | 固定长度字符 | 比较耗费空间 | 效率高 | M可省略,默认1 | 如性别 |
varchar(M) | 可变长度字符 | 比较节省空间 | 效率低 | M不可省略 | 如姓名 |
较短文本还包括:binary、varbinary、enum、set
binary、varbinary:保存较短的二进制
enum:保存枚举类型,如性别
CREATE TABLE tab_char( c1 ENUM('a','b','c') ); INSERT INTO tab_char VALUES('a'); INSERT INTO tab_char VALUES('A');#结果为a INSERT INTO tab_char VALUES('m');#结果为空 INSERT INTO tab_char VALUES('b');
set:与enum类似,但emun只能选一个,set可以选多个
CREATE TABLE tab_set( c1 SET('a','b','c') ); INSERT INTO tab_set VALUES('a'); INSERT INTO tab_set VALUES('A,b'); INSERT INTO tab_set VALUES('b,c,a');
3.日期类型单引号
date 4
datetime 8 范围较大1000——9999 只能反映插入时的当地时区
timestamp 4 范围较小1970——2038 容易受实际时区、版本和sqlmode的影响
time 3
year 1
datetime和timestamp的区别:
CREATE TABLE tab_time( t1 DATETIME, t2 TIMESTAMP ); INSERT INTO tab_time VALUES(NOW(),NOW());
查询插入后结果为:
修改当前时区:
SHOW VARIABLES LIKE 'time_zone';#查询当前市区 SET time_zone='+9:00'#将时区改为东九区
查询结果:
datetime为插入时的时区,timestamp受实际时区影响。
四、约束
含义:一种限制,用于限制表中的数据,为了保证表中的数据的准确和可靠性(一致性)。
分类:
not null:非空,用于保证该字段的值不能为空 比如姓名、学号
default:默认,用于保证该字段有默认值,比如,性别,省事
primary key:主键,用于保证该字段的值具有唯一性,非空,如学号,员工编号标识性
uniq:唯一,用于保证该字段的值具有唯一性,可以为空 比如座位号,可以为空但要唯一
check:检查(mysql不支持,不报错,但没效果),比如性别只能男或女不能是其他,或年龄 (要求必须为整)
foreign key:外键,用于限制两个表的关系,用于保证该字段的值必须来自于主表关联列的值,在从表添加外键约束,用于引用主表中的值,类型要一致比如学生的专业编号,员工的部门编号
添加约束的时机:
创建表时
修改表时
约束的添加分类:
列级约束:六大约束语法上都支持,但外键约束没有效果
表级约束:除了非空和默认,其他都支持
语法:
create table 表名(
列名 类型 列级约束,
列名 类型 列级约束,
表级约束
);
1.创建表时添加约束
①添加列级约束
语法:直接在字段名和类型后面追加约束类型即可。
只支持“:默认、非空、主键、唯一
CREATE DATABASE students;
USE students;
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,#默认
majorId INT REFERENCES major(id)
);
CREATE TABLE major(
id INT PRIMARY KEY,
majorName VARCHAR(20)
);
DESC stuinfo;
#查看stuinfo表中的所有索引,包括主键、外键、唯一
SHOW INDEX FROM stuinfo;
②添加表级约束
语法:在各个字段的最下面
【constraint 约束名 】约束类型(字段名)
通用写法:
主键、唯一键、非空、默认写在列级约束,外键写在表级约束。
CREATE TABLE stuinfo(
id INT,
stuName VARCHAR(20),
gender CHAR(1),
seat INT UNIQUE,
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)
);
SHOW INDEX FROM stuinfo;
主键和唯一键:
唯一性 是否允许为空 最多几个 是否允许组合
主键 √ × 最多一个 是,但不推荐
唯一 √ √(只能一个为空) 可以有几个 是,但不推荐
组合:primary key(id,name) id或name可以重复,但两个组合起来的结果不能重复。
两列组合成一列
外键特点:
要求在从表设置外键关系
从表的外键列的类型和主表的关联列的类型要求一致或兼容,名称无要求
主表的关联列必须是一个key(一般主键、唯一键)
插入数据时,应先插入主表,在插入从表;删除数据时,先删除从表,再删除主表。
2.修改表时添加约束
1.添加列级约束
alter table 表名 modify column 列名 类型 新约束;(反义约束)
2.添加表级约束
alter table 表名 add [constraint 约束名] 新约束(字段名)【一般应用于外键】
1.添加非空约束
#修改为非空
ALTER TABLE stuinfo MODIFY COLUMN VARCHAR(20) NOT NULL;
ALTER TABLE stuinfo MODIFY COLUMN VARCHAR(20);
2.添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
3.添加主键
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
#②表记约束
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 FOREIGN KEY(majorid) REFERENCES major(id);
#可以省略constraint fk_stuinfo_major
3.修改表时删除约束(类似于有设置了一遍约束)
1.删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;
#默认null,可写可不写
2.删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT;
3.删除主键
ALTER TABLE stuinfo MODIFY COLUMN id INT;
ALTER TABLE stuinfo DROP PRIMARY KEY;#推荐表级约束,主键唯一不用写列名
4.删除唯一键
ALTER TABLE stuinfo DROP INDEX seat;
5.删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;
主键起名字没效果,还是会显示PRIMARY
位置 支持的约束类型检查都不支持 是否可以起约束名
列级约束 列的后面 语法都支持,但外键没效果 不可以
表级约束 所有列的下面 非恐和默认不支持 可以(主键没效果)
五、标识列
又称为自增长列,可以不用手动插入值,系统提供默认的序列值
特点:
①标识列必须与key搭配使用
②一个表最多一个标识列
③标识列的类型只能是数值型
④可以设置步长和起始值
SHOW VARIABLES LIKE '%auto_increment%';#查询变量
auto_increment_increment:步长
auto_increment_offset:起始值
①修改步长
SET auto_incrementi_increment=3;#设置步长
②修改起始值:8.0版本前不支持,但可以设置第一行为所需起始值,后面设置为null,即可实现。
1.创建表时设置标识列
CREATE TABLE tab_identity(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
insert into tab_identity values(null,'john');
#在该字段插入null,之后的每一行会从默认起始值1开始自增长。
2.修改表时设置标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;
3.修改表时删除标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY;