7.库和表的管理、常见数据类型、常见约束、标识符

DDL：data define language 数据定义语言：针对表的定义
/*

库和表的管理
一、库的管理
创建、修改、删除
二、表的管理
创建：create
修改：alter
删除：drop 删除表（delete和truncate删除表内数据）

*/

#一、库的创建
#1.库的创建
/*
语法：
create database [if not exists]库名；

*/
#案例：创建库Books
CREATE DATABASE books;

#库的修改
更改库的字符集（查看当前数据库的字符集 点击数据库名右键-修改数据库）
ALTER DATABASE books CHARACTER SET gbk;

#库的删除
DROP DATABASE IF EXISTS books;

#二、表的管理
#1.表的创建☆
/*
create table 表名（
列名 列的类型【(长度) 约束】,
列名 列的类型【(长度) 约束】,
……
列名 列的类型【(长度) 约束】,
);

*/

#创建表book
CREATE TABLE book(
                id INT,#编号
                bname VARCHAR(20),#书名
　　　　 price DOUBLE,#价格
　　　　 authorid INT,#作者编号
　　　　 publishDate DATETIME#出版日期
);

#创建表author
CREATE TABLE author(
　　　　 id INT,
　　　　 au_name VARCHAR(20),
　　　　 nation VARCHAR(10)
);

#2.表的修改
/*
语法：
alter table add|drop|modify(修改类型或约束)|change(修改表名) column 列名【列类型 约束】
改表名；alter table 表名 rename to 表名
*/
#①修改列名
ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;

#②修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubDate TIMESTAMP;

#③添加新列
ALTER TABLE author ADD COLUMN annual DOUBLE;

#④删除列
ALTER TABLE author DROP COLUMN annual DOUBLE;

#⑤修改表名(重命名)
ALTER TABLE author RENAME TO book_author;

#3.表的删除
DROP TABLE IS EXISTS book_author;

#4.表的复制

INSERT INTO author 
VALUE(1,'村上春树','日本'),
(2,'莫言','中国'),
(3,'冯唐','中国'),
(4,'金庸','中国');

#① 仅仅复制表的结构
CREATE TABLE copy LIKE author;

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

#复制部分数据

CREATE TABLE copy3
SELECT id,au_name 
FROM author
WHERE nation='中国'

#复制部分结果

CREATE TABLE copy4
SELECT id,au_name 
FROM author
WHERE 0;

常见的数据类型
/*
数值型：
　　整型
　　小数：定点数
　　浮点数
字符型：
　　　　较短的文本：char、varchar
　　　　较长的文本：text、blob（较长的二进制数据）
日期型：

*/

#一、整型
/*
分类
              tinyint、smallint、mediumint、int、bigint
字节数     1              2              3              4        8

特点：
①如果不设置无符号和有符号默认为有符号，默认是有符号，需要添加unsigned关键字
②如果插入的数值超出了整型的范围，会报out of range异常，并且插入临界值
③如果不设置长度，会有默认的长度
   长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofull使用

*/

#1.如何设置无符号和有符号

DROP TABLE IF EXISTS tab_int;
CREATE TABLE tab_int(
　　t1 INT,
　　t2 INT UNSIGNED);

#二、小数
/*
分类：
浮点型：
　　flaot(M,D)
　　double(M,D)
定点型：dec/decimal(M,D)

 

特点：
①M和D：
　　M：整数部位+小数部位
　　D：小数部位
如果超过范围，则插入临界值

②M和D可以省略
如果是decimal，则M默认值为10，D的默认值为0
如果是float和double，则会根据插入数值的精确度来决定精度

③定点型的精度较高，如果要求插入数值的精度较高如货币运算等则考虑使用
*/

#原则
所原则的类型越简单越好，能保存的数值的类型越小越好

#三、字符型
/*
较短的文本
　　char
　　varchar
其他：
　　binary和varbinary用于保存较短的二进制
　　enum类：枚举类型，要求插入的值必须属于指定列表中指定值之一
　　set类：集合

较长的文本：
　　text
　　blob（较大的二进制）

特点：
　　写法                           M的意思                                         特点                        空间的耗费   效率
char char(M)                  最大的字符数(可以省略默认为1)       固定长度的字符      比较耗费        高
varchar varchar(M)        最大的字符数(不可以省略)                可变长度的字符       比较节省       低

*/

#四、日期型
/*
date 只保存日期
time 只保存时间
year 会保存年

datetime 日期+时间
timestamp 日期+时间

特点
                   字节        范围        时区等影响
datatime       8      1000-9999       不受
timestamp    4      1970-2038        受

*/

常见约束
/*

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

分类：六大约束
　　not null ：非空，用于保证该字段的值不能为空，比如姓名、学号
　　default: 默认，用于保证该字段有默认值
　　primary key: 主键 用于保证该字段的值具有唯一性，且非空
　　unique:唯一，用于保证该字段的值具有唯一性
　　check: 检查约束 【MySQL不支持】
　　foreigh key: 用于约束两个表的关系，用于保证字段值必须来自主表的关联列的值
　　　　　　　　在从表中添加外键约束，用于引用主表中的某列的值

添加约束的时机：
1.创建表时
2.修改表时

约束的添加分类
列级约束：
　　六大约束语法上都支持，但外键约束没有效果
表级约束：
　　除了非空、默认，其他的都支持

主键和唯一的对比

　　　保证唯一性  是否为空        一个表可以有多少个   是否允许组合
主键 　 是                 否                 至多一个                     是，但不推荐
唯一 　 是                 是                 可以有多个                 是，但不推荐

外键：
1.要求在从表设置外键关系
2.从表中外键列类型和主表的关联列类型相同或兼容
3.主表的关联列必须是一个key（一般是主键或唯一键）
4.插入数据时，先插入主表，在插入从表
   删除数据是，先删除从表，再删除主表

*/

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)  );

#添加表的约束
/*
语法：在各个字段的最下面
【constraint 约束名】 约束类型(字段名)
*/

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)   ）

#通用的写法

CREATE TABLE IF EXISTS stuinfo(
　　　　id INT PRIMARY KEY,#主键
　　　　stuName VARCHAR(20) NOT NULL,#非空
　　　　gender CHAR(1) ,
　　　　seat INT UNIQUE,#唯一约束
　　　　age INT DEFAULT 18,#默认
　　　　majorid INT， #外键
　　　　CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id))

#二、修改表时添加约束
1.添加列级约束
# alter table 表名 modify column 字段名 字段类型 新约束
2.添加表的约束
#alter table 表名 add【constraint 约束名】 约束类型(字段) 【外键引用】

#添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL

#删除约束
#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 fk_stuinfo_major

标识符
又称为自增长列
含义：可以不用手动的插入值，系统提供默认的序列值
在字段后面追加auto-increment

特点：
1，标识列不一定必须要和主键搭配，但要求是一个key
2.一个表中只能有一个自增长列
3.标识列的类型只能是数值型
4.标识列可以通过 set auto_increment_increment=2；设置步长
   可以通过 手动插入值，设置起始值