MySQL之数据类型、建表以及约束

一、数据类型介绍

MySQL中定义数据字段的类型对你的数据库的优化是非常重要的。

MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

1、字符类型：char、varchar、text、blob

2、数值类型：int、bigint、float、decimal

         int — int

        bigint —> long

        float —> 成绩

        decimal —> 货币类型(精度,小数)

3、日期类型：date time datetime timestamp

        date —> yyyy:MM:dd HH:mm:ss

        time —> HH:mm:ss

        datetime —> yyyy:MM:dd

        timestamp（时间戳） —> 长整数

二、建表、删表语法

1、表的创建

语法： create table 表名(

列名 列的类型【(长度) 约束】,

列名 列的类型【(长度) 约束】,

... )

例子：

create table mwy(

id int  PRIMARY KEY,  #编号，主键

name varchar(20)  #姓名

)

2、查看表结构 

语法：DESC  表名

例子： DESC mwy

 3、表的修改

 语法：alter table 表名 add|drop|modify|change column 列名 【列类型 约束】;

①、修改列名和类型和约束

语法：ALTER TABLE 表名 CHANGE COLUMN 原列名 修改后列名  类型 约束;

例子：ALTER TABLE mwy CHANGE COLUMN name uname varchar(10) not null;

②、添加新列

语法：ALTER TABLE 表名 ADD COLUMN 新增的列名 类型;

例子：ALTER TABLE mwy ADD COLUMN age int;

③、删除列

语法：ALTER TABLE 表名 DROP COLUMN 列名; 

例子：ALTER TABLE mwy DROP COLUMN age;

④、修改表名

语法：ALTER TABLE 原表名 RENAME TO 修改后表名;
例子：ALTER TABLE mwy RENAME TO u_mwy;

⑤、表的删除

语法：DROP TABLE IF EXISTS 表名;

例子：DROP TABLE IF EXISTS mwy;

⑥、查看同库所有表

语法：SHOW TABLES;

⑦、表的复制

（1）、复制表的结构

语法：CREATE TABLE copy(复制后的表） LIKE 复制的表;

（2）、复制表的结构+数据

语法：CREATE TABLE copy(复制后的表) as select * from 复制的表;

例子：CREATE TABLE u_mwy_copy as select * from u_mwy;

（3）、复制表的结构+数据,只要相关列

语法：CREATE TABLE copy(复制后的表) as select 需要的列 from 复制的表;

例子：CREATE TABLE u_mwy_copy as select uname from u_mwy;

3、主键(自动增长)、外键、非空等约束的使用

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

分类：六大约束

①、NOT NULL：非空，用于保证该字段的值不能为空。

例如：姓名、学号等

②、DEFAULT:默认，用于保证该字段有默认值。

例如：性别

③、PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空。

例如：学号、员工编号等

④、UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空。

例如：座位号

⑤、CHECK:检查约束{mysql中不支持】。

 例如：年龄、性别

⑥、FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值在从表添加外键约束，用于引用主表中某列的值。

 例如：学生表的专业编号，员工表的部门编号，员工表的工种编号

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

⑧、约束的添加分类：

列级约束：六大约束语法上都支持，但外键约束没有效果

例子：CREATE TABLE mwy(

id int PRIMARY KEY,

name varchar(10)
)

表级约束：除了非空、默认，其他的都支持

例子：CREATE TABLE mwy(

id int,

name varchar(10)

PRIMARY KEY(id)
)

语法：CREATE TABLE 表名(
字段名 字段类型 列级约束,
字段名 字段类型,
表级约束
)

4、修改表时添加约束

①、添加列级约束

语法：alter table 表名 modify column 字段名 字段类型 新约束;

例子：添加非空约束

ALTER TABLE mwy MODIFY COLUMN name VARCHAR(20) NOT NULL;

例子：添加主键

ALTER TABLE mwy MODIFY COLUMN id INT PRIMARY KEY;

例子：添加唯一

ALTER TABLE mwy MODIFY COLUMN seat INT UNIQUE;

②、添加表级约束

语法：alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;

例子：添加主键

ALTER TABLE mwy ADD PRIMARY KEY(id);

例子：添加外键

ALTER TABLE mwy ADD CONSTRAINT u_mwy FOREIGN KEY(id) REFERENCES major(uid);

例子：添加唯一

ALTER TABLE mwy ADD UNIQUE(seat);

5、修改表时删除约束

①、删除非空约束

ALTER TABLE mwy MODIFY COLUMN name VARCHAR(20) NULL;

②、删除默认约束

ALTER TABLE mwy MODIFY COLUMN age INT ;

③、删除主键

ALTER TABLE mwy DROP PRIMARY KEY;

④、删除唯一

ALTER TABLE mwy DROP INDEX seat;

⑤、删除外键

ALTER TABLE mwy DROP FOREIGN KEY u_mwy;

6、查询表的索引

语法：SHOW INDEX FROM 表名;

例子：SHOW INDEX FROM mwy;