mysql数据库笔记（十） 建表

mysql数据库 建表

如何查询创建表的sql语句

#查看s_emp表的建表语句
show create table s_emp;

建表sql的格式

#格式1：
create table 表名(
	列名1 数据类型 列级约束,
	列名2 数据类型 列级约束,
	列名3 数据类型 列级约束,
	列名4 数据类型 列级约束
);
#格式2：
create table 表名(
	列名1 数据类型 列级约束,
	列名2 数据类型 列级约束,
	列名3 数据类型 列级约束,
	列名4 数据类型 列级约束,
	表级约束1,
	表级约束2
);

根据以上格式,可以看出,建表过程中,需要以下几种东西:

1. 关键字

2. 表名

3. 列名

4. 数据类型

5. 约束

   ​ 其中,约束分为列级约束(因为是跟在列的声明后面写的)和表级约束(因为是在全部列声明完之后写的),列级约束和表级约束都是对列中的值进行约束的,例如:列的值不能为空,列的值必须是唯一的。
   注:列级约束也称为行级约束。

6. 固定格式

同时,列级约束和表级约束都是可选的,也就是都可以写也可以不写。例如:

#格式1：
create table 表名(
	列名1 数据类型,
	列名2 数据类型,
	列名3 数据类型,
	列名4 数据类型
);

表名的要求:

• 表名中只能出现A–Z, a–z, 0–9, _, $, #。
• 不能和数据库中已有对象的名字重复。
• 不能是数据库中的关键字。

列的常用数据类型

数值类型

• int或integer类型 ： 大整数值。数值范围：(0，4 294 967 295)
  • int(n): n表示整数的位数。
• float类型： 单精度的浮点数。
• double类型：双精度的浮点数。
• **decimal(m,d)**类型：m表示精度，范围为0-65。 d表示小数点位数。范围为0-30。
  • decimal(4,2) : 表示 该列可以存储4位数字，小数位2位。存储范围是：[-99.99,99.99]。

#使用数值类型创建t_number表
create table t_number(
	col int,
    col2 Integer,
    col3 float,
    col4 double,
    col5 decimal(4,2)
);
#测试 ： 使用Navicat中打开表选项插入数据进行测试

时间类型

• datetime类型：表示日期和时间。8个字节存储数据。

• date类型：表示日期值

• time类型：表示时间值

• timestamp类型：表示时间戳。

  • 同一张表中只能创建一个时间戳类型的列。
  • 时间戳（timestamp）：一个字符序列，唯一地标识某一刻的时间。
  • 4个字节INT类型来存放时间戳数据。
  • default current_timestamp 表示插入记录时未指定具体时间数据则将该时间戳字段值设置为当前时间。
  • on update current_timestamp表示如更新记录时未指定具体时间数据则将该时间戳字段值设置为当前时间。

#使用时间类型创建t_datetime表
create table t_datetime(
	col datetime,
    col2 date,
    col3 time,
    col4 timestamp
);
#插入数据自动生成时间戳值
create table t_datetime2(
	col datetime,
    col2 date,
    col3 time,
    col4 timestamp default current_timestamp
);
#更新数据时自动生成时间戳值
create table t_datetime3(
	col datetime,
    col2 date,
    col3 time,
    col4 timestamp on update current_timestamp,
);

字符串类型

• char：长度0-255字节，定长字符串
  • char(n):表示最多存储n个字符。
• varchar：长度0-65535 字节，变长字符串
  • varchar(n):表示存储的字符的个数。
• text： 0-65 535字节，长文本数据
• enum: 枚举类型

#使用字符串类型创建表t_char表
create table s_char(
    col char(5),
    col2 varchar(2),
    col3 enum('a','b','c')
);

列的约束

#查看表中的约束
select * from information_schema.KEY_COLUMN_USAGE a where a.TABLE_NAME = 's_emp';
# 
desc s_emp;

列的约束就是对这个列中的值的要求

1. 主键约束 primary key
2. 外键约束 foreign key
3. 唯一约束 unique
4. 非空约束 not null
5. 默认约束 default

列级约束/行级约束

在列的后面直接加上的约束

create table 表名(
    列名1 数据类型 列级约束,
    列名2 数据类型 列级约束,
    列名3 数据类型 列级约束,
    列名4 数据类型 列级约束
);

表级约束

在所有列声明以后加的约束。

create table 表名(
    列名1 数据类型 列级约束,
    列名2 数据类型 列级约束,
    列名3 数据类型 列级约束,
    列名4 数据类型 列级约束,
    表级约束1,
    表级约束2
);

建表实例

#普通的建表例子
create table t_student(
    id number primary key,
    name varchar2(200) not null,
    birthday date
);
#删除表信息
drop table t_student
#使用四种列级约束 主键约束 非空约束 唯一约束 默认约束
create table t_student(
	id int primary key, -- 学号
	name varchar(10) not null, -- 姓名
	gender enum('m','f'), -- 性别
	birthday date, -- 出生日期
    address varchar(50) default '江西省南昌市', -- 户籍
	email varchar(50) unique -- 电子邮箱
);

主键自增

# 创建表 t_test. 主键自增
create table t_test(
	id int primary key auto_increment,
    name varchar(10) not null
);
# 观察当插入数据是，主键id值的变化。

使用列级约束 声明 外键约束

#创建顾客表
create table t_customer(
  id int primary key,
  name varchar(20)
);
#创建订单表
create table t_order(
  id int primary key,
  content VARCHAR(100),
  customer_id int references t_customer(id) 
);
#注意:订单表中的外键列customer_id的值,是引用自顾客表t_customer中的主键列id的值。
#当删除t_customer表时，出现什么问题？

使用表级约束

#使用表级约束创建学生表
create table t_student(
  id int,
  name VARCHAR(10) not null,
  gender enum('f','m'),
  birthday date,
  address VARCHAR(50),
  email VARCHAR(50),
  PRIMARY key(id),
  UNIQUE(email) 
);
# 非空和默认约束不能使用表级约束
#创建客户表
create table t_customer(
  id int,
  name varchar(20),
  PRIMARY key(id)
);
#创建订单表
create table t_order(
  id int primary key,
  content VARCHAR(100),
  customer_id int,
  FOREIGN key(id) REFERENCES t_customer (id) 
);
#注:非空约束(not null)不能声明成表级约束

表级约束和列级约束对比

1. 表级约束和列级约束所写的位置不一样
2. not null约束不能用表级约束来声明
3. 表级约束和列级约束声明语法稍有所不同
4. 如果要声明的约束为联合主键、联合外键、联合唯一的时候,就一定要用表级约束.

constraint关键字

1. constraint是约束的意思。
2. 建表的时候可以给约束起一个名字,这个名字起的规律一般会是:表名_列名_约束类型。
3. 如果没有给约束起名字,那么系统也会给这个约束起一个默认的名字。
4. 将来我们可以根据我们之前给约束起好的名字而找到这个约束,然后进行修改。

#列级约束 起约束名字(修改)
create table t_student(
    id int primary key,
    name varchar(10) not null,
    email varchar(30) unique,
    birthday date
);
#表级约束 起约束名字

特殊的建表

建立一张表和s_dept一模一样的表

#s_dept的表结构和表中的数据全部复制过来
create table test1
 as
select * from s_dept;
#只拿来s_dept的表结构,没有数据
#方式1：
create table test2
as
select * from s_dept
where 1=2;
#方式2：
create table test3 like s_emp;
#只复制表中某几个列以及数据
create table test4
as
select id,last_name,salary from s_emp;