#DDL /* 数据定义语言 库和表的管理 一、库的管理 创建、修改、删除 二、表的管理 创建、修改、删除 创建 create 修改 alter 删除 drop */ #一、库的管理 #1、库的创建 /* CREATE DATABASE 库名; */ CREATE DATABASE books; SHOW DATABASES; #如果没有再创建 CREATE DATABASE IF NOT EXISTS books; #2、库的修改 /* 一般地,不对库做修改 */ #更改库的字符集 ALTER DATABASE books CHARACTER SET gbk; #3、库的删除 DROP DATABASE IF EXISTS books; #二、表的管理 #1、表的创建 /* CREATE TABLE 表名( 列名 列的类型 【长度,约束】, 列名 列的类型 【长度,约束】, ... 列名 列的类型 【长度,约束】 ) */ USE books; #案例1:创建book表 CREATE TABLE book( id int, bname varchar(50), price double, author_id int, publish_date datetime ); CREATE TABLE author( id int, au_name varchar(20), nation varchar(20) ); #2、表的修改 /* ALTER TABLE 表名 change|modify|add|drop|rename COLUMN 列名 【列类型 约束】 */ #修改列名 ALTER TABLE book CHANGE COLUMN publish_date pub_date DATETIME; #修改列的类型或约束 ALTER TABLE book MODIFY COLUMN pub_date TIMESTAMP; #添加新列 ALTER TABLE author ADD COLUMN annual DOUBLE; #删除列 ALTER TABLE author DROP COLUMN annual; #修改表名 ALTER TABLE author RENAME TO book_author; DESC book; #3、表的删除 DROP TABLE IF EXISTS book_author; #4、表的复制 INSERT INTO book_author VALUES(1, ‘莫言‘, ‘中国‘), (2, ‘村上春树‘, ‘日本‘); #仅仅复制表的结构 CREATE TABLE copy LIKE book_author; #复制表的结构+数据 CREATE TABLE copy2 SELECT * FROM book_author; #只复制部分数据 CREATE TABLE copy3 SELECT id, au_name FROM book_author WHERE nation=‘中国‘; #只复制部分字段 #设置一个不等条件 CREATE TABLE copy4 SELECT id, au_name FROM book_author WHERE 0; #常见数据类型 /* 数值型: 整型 小数:定点数、浮点数 字符型: 较短的文本:char, varchar 较长的文本:text, blob(较长的二进制数据) 日期型 */ #1、整型 #tinyint, smallint, mediumint, int/integer, bigint #默认有符号;无符号需追加unsigned #插入数值超过范围会报warning,并且插入临界值 #长度代表显示的宽度,不设置长度会有默认值(zerofill,不够宽度用0填充) DROP TABLE IF EXISTS tab_int; CREATE TABLE tab_int( t1 int, t2 int unsigned ); INSERT INTO tab_int(t1) VALUES(-123); #能够插入,说明int默认有符号 #2、浮点型 #float(M, D) 最常用, double(M, D) #3、定点型 #decimal(M, D) 精度较高 /* M:整数部位+小数部位 D:小数部位 如果超过范围,插入临界值 */ #4、字符型 #较短文本: #固定长度的字符:var(M),效率高;可变长度的字符:varchar(M),节省空间,效率低;M:最多的字符数 #binary/varbinary 保存较短的二进制;enum 保存枚举;set 保存集合 #较长文本: #text, blob(图片等较大的二进制) #5、日期型 #日期值必须用单引号引起来 #date, datetime, timestamp, time, year #timestamp的属性和实际时区有关,更能反应实际的日期 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 表名( 字段名 字段类型 列级约束1 列级约束2, 字段名 字段类型 列级约束, ..., 表级约束 ); */ CREATE DATABASE students; USE students; #添加列级约束 CREATE TABLE stuinfo( id int primary key, stu_name varchar(20) not null, gender char(1) check(gender=‘男‘ or gender=‘女‘), seat int unique, age int default 18, major_id int ); CREATE TABLE major( id int primary key, major_name varchar(20) ); DESC stuinfo; #添加表级约束 #【constraint 约束名】 约束类型(字段名) DROP TABLE IF EXISTS stuinfo; CREATE TABLE IF NOT EXISTS stuinfo( id int, stu_name varchar(20) not null, gender char(1), seat int, age int default 18, major_id int, constraint pk primary key(id), constraint uq unique(seat), constraint ck check(gender=‘男‘ or gender=‘女‘), constraint fk_stuinfo_major foreign key(major_id) references major(id) ); SHOW INDEX FROM stuinfo; /* 主键和唯一的对比: 都具有唯一性; 主键不允许为空、唯一允许为空; 一个表中最多只能有一个主键; 都允许组合,但都不推荐 外键: 1、要求在从表设置外键关系 2、从表的外键列的类型和主表的关联列的类型要一致或兼容 3、主表的关联列必须是一个key(一般是主键或唯一) 4、插入数据时,先插入主表,再插入从表;删除数据时,先删除从表,再删除主表 */ #修改表时添加约束 DROP TABLE IF EXISTS stuinfo; CREATE TABLE IF NOT EXISTS stuinfo( id int, stu_name varchar(20), gender char(1) check(gender=‘男‘ or gender=‘女‘), seat int unique, age int, major_id int ); #1、添加非空约束 ALTER TABLE stuinfo MODIFY COLUMN stu_name varchar(20) not null; #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 UNIQUEY(id); #5、添加外键 ALTER TABLE stuinfo ADD CONSTRAINT fk_major_id FOREIGN KEY(major_id) REFERENCES major(id); #修改表时删除约束 #和添加约束语法一致,把后面的约束去掉再执行一次就行了 #删除主键 ALTER TABLE stuinfo DROP PRIMARY KEY; #删除唯一 ALTER TABLE stuinfo DROP INDEX seat; #删除外键 ALTER TABLE stuinfo DROP FOREIGN KEY fk_major_id;