MySQL的建表和约束

一、数据类型

MySQL中定义数据字段的类型对数据库的优化是非常重要的。
MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

1.字符类型：Char(10)定长  Varchar(10)  text  blob   'abc'
2.数值类型：int  bigint  float  decimal

decimal(p,a)  p为精度    a为小数位数   decimal(5,2)  例如 999.99

 注意：建表时 整数部分超出会报错，小数部分超出，会四舍五入

3.日期类型：Date                  yyyy-MM-dd HH:mm:ss.SSS 例如：1970-1-1 0:0:0.000 
                     time                   HH:mm:ss.SSS
                     datetime            yyyy-MM-dd
                     timestamp         长整数   545445454523  

二、建表和约束

1.约束

1.1 主键约束(PRIMARY KEY)
  
主键约束：唯一、不重复、不为空；每个表都应该有一个主键，并且每一个表只能有一个主键。

语法：CREATE TABLE 表名称(
   列名称 数据类型 Primary Key,

......
    );

自动增长直接在设计表勾选即可

也可以使用字段 auto_increment

1.2 外键约束

 外键约束：外键创建在从表（副表）中，从表中的FOREIGN KEY指向主表中的PRIMARY KEY。

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

主表：CREATE TABLE 表名称(
      列名称 数据类型 Primary Key,
        ...
    );    

从表：CREATE TABLE 表名称(
        ...
        外键名称 数据类型,
        FOREIGN KEY(外键名称) REFERENCES 主表名称(主键名称),
        ...
    );

如果是已经存在的两个表

 ALTER TABLE 表名 ADD FOREIGN KEY(外键名称) REFERENCES 主表名称(主键名称);

例如：

ALTER TABLE t_student ADD FOREIGN KEY(tid) REFERENCES t_teacher(tid);

主表为t_teacher表

删除表外键：
ALTER TABLE 表名 DROP FOREIGN KEY 约束名;

约束名可在表的设计 ---->外键 的第一列

1.3 非空值约束

非空值约束：用于确保当前列的值不为空值，非空约束只能出现在表对象的列上。

语法：CREATE TABLE 表名称(
        列名称 数据类型 NOT NULL,
        ...
    );    

1.4 唯一约束（UNIQUE）

新增唯一约束：
  ALTER TABLE 表名 ADD UNIQUE INDEX(列名称1[,列名称2,..]);

删除唯一约束：其实就是删除指定的唯一约束的索引名称
  ALTER TABLE 表名 DROP INDEX 唯一约束缩影名称;

1.5 默认约束(DEFAULT)

    默认值约束：指定列的默认值。
    语法：CREATE TABLE 表名称(
        列名称1 数据类型 DEFAULT '默认值',
        列名称2 数据类型,
        列名称3 数据类型,
        ...
    );   

三、修改表结构

1）新增语法：alter table 表名称 add column 列名 数据类型(约束);
2）删除语法：alter table 表名称 drop column 列名;
3）修改语法：alter table 表名称 modify column 列名 数据类型;     

注：ALTER TABLE 语句用于在已有的表中添加、修改或删除列。

①修改列名
ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;
​例如：将sname列改为bname

 ALTER TABLE t_student CHANGE COLUMN sname bname varchar(20);
②修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;
​
③添加新列
ALTER TABLE author ADD COLUMN annual （字段类型）;
​
④删除列
ALTER TABLE book_author DROP COLUMN annual;
​
⑤修改表名
ALTER TABLE author RENAME TO book_author;
​

查询表结构
DESC book;

查看t_student中的所有索引，包括主键、外键、唯一
SHOW INDEX FROM t_student;

四、数据库四大语言

ddl  数据库结构定义语言  alert  creat
dml 数据库数据操作语言  insert update delete
dql 数据库数据查询语言   select
dcl  数据库事务语言  rollback commit 

五、表的复制

1.仅仅复制表的结构
CREATE TABLE copy LIKE author;
​
2.复制表的结构+数据
CREATE TABLE copy2
SELECT * FROM author;
​
3.只复制部分数据
CREATE TABLE copy3
SELECT id,au_name
FROM author
WHERE nation='中国';
​
​
4.仅仅复制某些字段
CREATE TABLE copy4
SELECT id,au_name
FROM author
WHERE 0=1;