SQL(五) - 表的创建以及操作_sql 建表详解

创建表

建表语句的语法格式：

create table 表名(
			字段名1 数据类型,
			字段名2 数据类型,
			字段名3 数据类型,
			....
			);
1
2
3
4
5
6

MySql常用数据类型

BLOB 二进制大对象（存储图片、视频等流媒体信息） Binary Large OBject （对应java中的Object）
CLOB 字符大对象（存储较大文本，比如，可以存储4G的字符串。） Character Large OBject（对应java中的Object）
补充：
常用的数据类型:

• 整数：int，bit
• 小数：decimal
• 字符串：varchar,char
• 日期时间: date, time, datetime
• 枚举类型(enum)

常见的约束:

• 主键约束 primary key
• 非空约束 not null
• 惟一约束 unique
• 默认约束 default
• 外键约束 foreign key

数据类型和约束保证了表中数据的准确性和完整性

表名在数据库当中一般建议以：t_或者tbl_开始。

实例：创建学生表：
学生信息包括：
学号、姓名、性别、班级编号、生日
学号：bigint
姓名：varchar
性别：char
班级编号：int
生日：char

create table t_student(
			no bigint,
			name varchar(255),
			sex char(1),
			classno varchar(255),
			birth char(10)
		);
1
2
3
4
5
6
7

查看t_student表的结构：

desc t_student;
1

创建表

向表中插入数据

单行插入

语法格式：

		insert into 表名(字段名1,字段名2,字段名3,....) values(值1,值2,值3,....)
1

要求：字段的数量和值的数量相同，并且数据类型要对应相同。

insert into t_student(no,name,sex,classno,birth) values(1,'zhangsan','1','gaosan1ban');  //报错
1

报错：ERROR 1136 (21S01): Column count doesn’t match value count at row 1

insert into t_student(no,name,sex,classno,birth) values(1,'zhangsan','1','gaosan1ban', '1950-10-12');
1

insert into t_student(name,sex,classno,birth,no) values('lisi','1','gaosan1ban', '1950-10-12',2);

1
2

insert into t_student(name) values('wangwu'); // 除name字段之外，剩下的所有字段自动插入NULL。
1

注意：除name字段之外，剩下的所有字段自动插入NULL。

insert into t_student(no) values(3); 
1

删除之前创建的表：

drop table if exists t_student; // 当这个表存在的话删除。
1

重新创建刚才的t_student表：

create table t_student(
		no bigint,
		name varchar(255),
		sex char(1) default 1,
		classno varchar(255),
		birth char(10)
	);
1
2
3
4
5
6
7

插入数据：

insert into t_student(name) values('zhangsan');
1

需要注意的地方：

• 当一条insert语句执行成功之后，表格当中必然会多一行记录。
• 即使多的这一行记录当中某些字段是NULL，后期也没有办法在执行
• insert语句插入数据了，只能使用update进行更新。

字段可以省略不写，但是后面的value对数量和顺序都有要求。

insert into t_student values(1,'jack','0','gaosan2ban','1986-10-23');
1

insert into t_student values(1,'jack','0','gaosan2ban'); //报错
1

报错： ERROR 1136 (21S01): Column count doesn’t match value count at row 1

一次插入多行数据

insert into t_student
		(no,name,sex,classno,birth) 
values
		(3,'rose','1','gaosi2ban','1952-12-14'),
		(4,'laotie','1','gaosi2ban','1955-12-14');
1
2
3
4
5

表的复制

语法：

create table 表名 as select语句;
		//  将查询结果当做表创建出来。
1
2

案例1：复制emp表

create table emp1 as select * from emp;
1

案例2：复制emp表中的部分字段

create table emp2 as select ename,sal from emp;
1

将查询结果插入到一张表中

语法：

insert into 表名 select语句;
		//  将查询结果当做表创建出来。
1
2

1.复制dept表格

create table dept1 as select * from dept;
1

2.将dept的查询结果插入到dept1z中

insert into dept1 select * from dept;
1

修改数据：update

语法格式：

update 表名 set 字段名1=值1,字段名2=值2... where 条件;
1

注意：没有条件整张表数据全部更新。

案例：将部门10的LOC修改为SHANGHAI，将部门名称修改为RENSHIBU

update dept1 set loc = 'SHANGHAI', dname = 'RENSHIBU' where deptno = 10;
1

注意：要修该多个字段需要用逗号‘，’隔开，而不是and。

更新所有记录

update dept1 set loc = 'x', dname = 'y';
1

删除数据：delete

语法格式：

delete from 表名 where 条件;
1

注意：没有条件全部删除。
1

删除10部门数据？

delete from dept1 where deptno = 10;
1

删除所有记录？

delete from dept1;
1

怎么删除大表中的数据？（重点）删库跑路，哈哈哈哈

truncate table 表名; // 表被截断，不可回滚。永久丢失。
1

删除表？

drop table 表名; // 这个通用。
drop table if exists 表名; // oracle不支持这种写法。
1
2

表结构的修改（很少用，可以直接用工具）

对于表结构的修改，这里不讲了，大家使用工具完成即可，因为在实际开发中表一旦
设计好之后，对表结构的修改是很少的，修改表结构就是对之前的设计进行了否定，即使
需要修改表结构，我们也可以直接使用工具操作。修改表结构的语句不会出现在Java代码当中。
出现在java代码当中的sql包括：insert delete update select（这些都是表中的数据操作。）

增删改查有一个术语：CRUD操作
Create（增） Retrieve（检索） Update（修改） Delete（删除）

数据约束

约束是指数据在数据类型限定的基础上额外增加的要求.

常见的约束如下:

• 主键 primary key: 物理上存储的顺序. MySQL 建议所有表的主键字段都叫 id, 类型为 int unsigned.
• 非空 not null: 此字段不允许填写空值.
• 惟一 unique: 此字段的值不允许重复.但可以为null
• 默认 default: 当不填写字段对应的值会使用默认值，如果填写时以填写为准.
• 外键 foreign key: 对关系字段进行约束, 当为关系字段填写值时, 会到关联的表中查询此值是否存在, 如果存在则填写成功, 如果不存在则填写失败并抛出异常.
• 检查约束(check)：注意Oracle数据库有check约束，但是mysql没有，目前mysql不支持该约束。

主键：
主键相关的术语？

• 主键约束 : primary key
• 主键字段 : id字段添加primary key之后，id叫做主键字段
• 主键值 : id字段中的每一个值都是主键值。

主键有什么作用？

• 表的设计三范式中有要求，第一范式就要求任何一张表都应该有主键。
• 主键的作用：主键值是这行记录在这张表当中的唯一标识。（就像一个人的身份证号码一样。）

主键的分类？
根据主键字段的字段数量来划分：

• 单一主键（推荐的，常用的。）
• 复合主键(多个字段联合起来添加一个主键约束)（复合主键不建议使用，因为复合主键违背三范式。）

根据主键性质来划分：

• 自然主键：主键值最好就是一个和业务没有任何关系的自然数。（这种方式是推荐的）
• 业务主键：主键值和系统的业务挂钩，例如：拿着银行卡的卡号做主键，拿着身份证号码作为主键。（不推荐用）

最好不要拿着和业务挂钩的字段作为主键。因为以后的业务一旦发生改变的时候，主键值可能也需要随着发生变化，但有的时候没有办法变化，因为变化可能会导致主键值重复。

注意：

• 一张表的主键约束只能有1个。（必须记住）
• mysql提供主键值自增auto_increment：（非常重要。）
• Oracle当中也提供了一个自增机制，叫做：序列（sequence）对象。

怎么给一张表添加主键约束呢？

drop table if exists t_user;
create table t_user(
	id int primary key,  // 列级约束
	username varchar(255),
	email varchar(255)
		);
1
2
3
4
5
6

使用表级约束方式定义主键：

drop table if exists t_user;
create table t_user(
	id int,
	username varchar(255),
	primary key(id)
	);
1
2
3
4
5
6

以下内容是演示以下复合主键，不需要掌握：

drop table if exists t_user;
create table t_user(
	id int,
	username varchar(255),
	password varchar(255),
	primary key(id,username)
);
1
2
3
4
5
6
7

外键约束

关于外键约束的相关术语：

• 外键约束: foreign key
• 外键字段：添加有外键约束的字段
• 外键值：外键字段中的每一个值。

业务背景：

请设计数据库表，用来维护学生和班级的信息？

		第一种方案：一张表存储所有数据
		no(pk)			name			classno			classname
		-------------------------------------------------------------------------------------------
		1					zs1				101				北京大兴区经济技术开发区亦庄二中高三1班
		2					zs2				101				北京大兴区经济技术开发区亦庄二中高三1班
		3					zs3				102				北京大兴区经济技术开发区亦庄二中高三2班
		4					zs4				102				北京大兴区经济技术开发区亦庄二中高三2班
		5					zs5				102				北京大兴区经济技术开发区亦庄二中高三2班
		缺点：冗余。【不推荐】

		第二种方案：两张表（班级表和学生表）
		t_class 班级表
		cno(pk)		cname
		--------------------------------------------------------
		101		北京大兴区经济技术开发区亦庄二中高三1班
		102		北京大兴区经济技术开发区亦庄二中高三2班

		t_student 学生表
		sno(pk)		sname				classno(该字段添加外键约束fk)
		------------------------------------------------------------
		1				zs1				101
		2				zs2				101
		3				zs3				102
		4				zs4				102
		5				zs5				102
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

将以上表的建表语句写出来：

	t_student中的classno字段引用t_class表中的cno字段，此时t_student表叫做子表。t_class表叫做父表。

	顺序要求：
		删除数据的时候，先删除子表，再删除父表。
		添加数据的时候，先添加父表，在添加子表。
		创建表的时候，先创建父表，再创建子表。
		删除表的时候，先删除子表，在删除父表。
	
	drop table if exists t_student;
	drop table if exists t_class;

	create table t_class(
		cno int,
		cname varchar(255),
		primary key(cno)
	);

	create table t_student(
		sno int,
		sname varchar(255),
		classno int,
		primary key(sno),
		foreign key(classno) references t_class(cno)
	);

	insert into t_class values(101,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
	insert into t_class values(102,'yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy');

	insert into t_student values(1,'zs1',101);
	insert into t_student values(2,'zs2',101);
	insert into t_student values(3,'zs3',102);
	insert into t_student values(4,'zs4',102);
	insert into t_student values(5,'zs5',102);
	insert into t_student values(6,'zs6',102);
	select * from t_class;
	select * from t_student;

	insert into t_student values(7,'lisi',103);
	ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`bjpowernode`.INT `t_student_ibfk_1` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))

* 外键值可以为NULL？
	外键可以为NULL。

* 外键字段引用其他表的某个字段的时候，被引用的字段必须是主键吗？
	注意：被引用的字段不一定是主键，但至少具有unique约束。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

存储引擎（仅了解）

1.完整的建表语句

CREATE TABLE `t_x` (
	`id` int(11) DEFAULT NULL
		) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1
2
3

	注意：在MySQL当中，凡是标识符是可以使用飘号括起来的。最好别用，不通用。

	建表的时候可以指定存储引擎，也可以指定字符集。

	mysql默认使用的存储引擎是InnoDB方式。
	默认采用的字符集是UTF8
1
2
3
4
5
6

2.什么是存储引擎呢？

• 存储引擎这个名字只有在mysql中存在。（Oracle中有对应的机制，但是不叫做存储 引擎。Oracle中没有特殊的名字，就是“表的存储方式”）
• mysql支持很多存储引擎，每一个存储引擎都对应了一种不同的存储方式。
• 每一个存储引擎都有自己的优缺点，需要在合适的时机选择合适的存储引擎。

3.查看当前mysql支持的存储引擎？

show engines \G
1

MyISAM存储引擎

• MyISAM存储引擎是MySQL最常用的引擎。

它管理的表具有以下特征：

1.使用三个文件表示每个表：

• 格式文件 — 存储表结构的定义（mytable.frm）
• 数据文件 — 存储表行的内容（mytable.MYD）
• 索引文件 — 存储表上索引（mytable.MYI）

2.灵活的AUTO_INCREMENT字段处理
3.可被转换为压缩、只读表来节省空间

优点：可被压缩，节省存储空间。并且可以转换为只读表，提高检索效率。
缺点：不支持事务，不支持外键。

InnoDB存储引擎

• InnoDB存储引擎是MySQL的缺省引擎。

它管理的表具有下列主要特征：

• 每个InnoDB表在数据库目录中以.frm格式文件表示
• InnoDB表空间tablespace被用于存储表的内容
• 提供一组用来记录事务性活动的日志文件
• 用COMMIT(提交)、SAVEPOINT及ROLLBACK(回滚)支持事务处理
• 提供全ACID兼容
• 在MySQL服务器崩溃后提供自动恢复
• 多版本（MVCC）和行级锁定
• 支持外键及引用的完整性，包括级联删除和更新

优点：支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障。

表的结构存储在xxx.frm文件中数据存储在tablespace这样的表空间中（逻辑概念），无法被压缩，无法转换成只读。这种InnoDB存储引擎在MySQL数据库崩溃之后提供自动恢复机制。InnoDB支持级联删除和级联更新。

MEMORY存储引擎

• 使用MEMORY存储引擎的表，其数据存储在内存中，且行的长度固定，这两个特点使得MEMORY存储引擎非常快。

MEMORY存储引擎管理的表具有下列特征：

• 在数据库目录内，每个表均以.frm格式的文件表示。
• 表数据及索引被存储在内存中。
• 表级锁机制。
• 不能包含TEXT或BLOB字段。
• MEMORY存储引擎以前被称为HEAP引擎。

缺点：不支持事务。数据容易丢失。因为所有数据和索引都是存储在内存当中的。
优点：查询速度最快。

选择合适的存储引擎

1. MyISAM表最适合于大量的数据读而少量数据更新的混合操作。MyISAM表的另一种适用情形是使用压缩的只读表。
2. 如果查询中包含较多的数据更新操作，应使用InnoDB。其行级锁机制和多版本的支持为数据读取和更新的混合操作提供了良好的并发机制。
3. 可使用MEMORY存储引擎来存储非永久需要的数据，或者是能够从基于磁盘的表中重新生成的数据。