当前位置：首页 > article >正文

MySQL-DDL/DML（数据定义/操作语言）

article 2024/11/14 15:59:22

数据定义语言(DDL-Data Definition Language)
代表关键字:create ,drop,alter

数据操作语言(DML-Data Manipulation Language)
代表关键字：insert,delete,update

1、表的操作

1.1 创建表

create table 表名(字段1,字段2,字段3.....字段n) [charset=字符集][type=表类型];

例如： create table student(id int,name varchar(50),sex char(2),age int);

其中：

a.字段的形式为：字段名字段类型 [字段属性…]

b.字符集包括：utf ,gbk,gb2312,big5等等，默认是数据库的字符集，可以不写

c.表类型包括：InnoDB,MyIsam,BDB等，默认是InnoDB,可以不写

注意事项：

a.表字段名不可以是中文

b.常用数据类型有：

中文	关键字	其他
字符串	varchar,char,text	name varchar(50),字符串类型必须写上长度
数字	int	age int;整数可以不用写长度
布尔	bit	0=false,1=true
时间	datetime,date	birthday datetime;不用写长度
浮点	float	money float;不用写长度

在这里插入图片描述

1.2 快速创建表【了解内容】

	mysql> create table emp2 as select * from emp;

	原理：
		将一个查询结果当做一张表新建！！！！！
		这个可以完成表的快速复制！！！！
		表创建出来，同时表中的数据也存在了！！！
	
	create table mytable as select empno,ename from emp where job = 'MANAGER';

1.3 将查询结果插入到一张表当中？【了解内容】

insert相关的！！！

create table dept_bak as select * from dept;
mysql> select * from dept_bak;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

insert into dept_bak select * from dept; //很少用！

mysql> select * from dept_bak;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

1.4 查看表结构

desc 表名;

所谓数据表的结构，其实就是一个表的每个字段的具体信息
在这里插入图片描述

1.5 查看表的创建语句

show create table 表名;

在这里插入图片描述

1.6 删除表

drop table 表名;

drop table if exists 表名;

在这里插入图片描述

1.7 字段操作（DDL）

1.7.1 添加字段

alter table 表名 add 字段名 字段类型 [字段属性][after某字段或first];

其中：
after某字段名：意思是，新加的字段，放在该现有字段的后面
first:表示新加的字段放在第一位（最前面）

例如：
在这里插入图片描述

1.7.2 修改字段

alter table 表名 change 旧字段名 新字段名 字段类型 [字段属性];

在这里插入图片描述
如果不修改字段名，而只是修改字段的其他信息，则可以使用：

alter table 表名 modify 要修改的字段名 字段类型 [字段属性];

在这里插入图片描述

1.7.3 删除字段

alter table 表名 drop 要删除的字段;

注意： MySql不支持下面这种批量删除
alter table 表名 drop COLUMN 要删除的字段1,drop COLUMN 要删除的字段2;
MySql不支持删除表中的全部字段，可用drop table 表名代替

在这里插入图片描述

1.7.4 修改表名

alter table 表名 rename 新的表名;

在这里插入图片描述

1.7.5 修改字符集

alter table  表名 charset="新的字符集";

在这里插入图片描述

2. 数据操作

数据都是存储在数据表中

数据的操作基本有4种，增（插入insert）,删(删除delete),改(修改update),查（查询select）.

即所谓的CRUD操作：create(创建)，retrieve（获取）,update(更新)，delete(删除)

2.1 插入数据（DML）

2.1.1、基本使用

insert into 表名(字段1,字段2....) values(数据1,数据2....);

在这里插入图片描述

--普通的,完整的添加数据
insert into student(id,name,sex,telphone,age) values(1,'张三','男','123455',18);
--查询表中的所有数据
select * from 表名;
--特殊的添加数据
 insert into student values(2,'里斯','男','123455',19);--添加一条完整的数据

一次性插入多条数据【掌握】

insert into student values(3,'王五','男','123456',19),(4,'小张','女','999',20);

insert into student select 5,'小李','女','999',21;--新增一条数据
insert into student select 6,'小周','女','888',22 union select 7 ,'小王','男','123',23;--一次性插入多条数据
--会出错的情况
insert into student(id,name,sex,telphone,age) values(1,'张三','男','123455','十八');--age字段是数字类型，而插入的值却是字符串类型，类型不匹配
insert into student(id,name,sex,telphone,age) values(1,'张三','男','123455');--插入罗列的字段和值的数量是不匹配的
insert into student values(3,'王五','男','123455');--值和字段数量不匹配

注意事项：

1.字符串和时间类型在使用的时候，是必须添加单引号，不要加成双引号了
2.在插入数据的时候，需要一一匹配，匹配包括数据类型，数量，顺序都必须一一匹配
3.在表后罗列字段的时候，是可以不写字段的。但是，如果不罗列字段，那么你的值就必须是这张表所有字段的值。比如表有10个字段，那么就的意义罗列10个值，且一一对应

2.1.2、插入数据insert （DML）

语法格式：
insert into 表名(字段名1,字段名2,字段名3…) values(值1,值2,值3);

注意：字段名和值要一一对应。什么是一一对应？
数量要对应。数据类型要对应。

	insert into t_student(no,name,sex,age,email) values(1,'zhangsan','m',20,'zhangsan@123.com');
	insert into t_student(email,name,sex,age,no) values('lisi@123.com','lisi','f',20,2);

	insert into t_student(no) values(3);

+------+----------+------+------+------------------+
| no   | name     | sex  | age  | email            |
+------+----------+------+------+------------------+
|    1 | zhangsan | m    |   20 | zhangsan@123.com |
|    2 | lisi     | f    |   20 | lisi@123.com     |
|    3 | NULL     | NULL | NULL | NULL             |
+------+----------+------+------+------------------+
insert into t_student(name) values('wangwu');
+------+----------+------+------+------------------+
| no   | name     | sex  | age  | email            |
+------+----------+------+------+------------------+
|    1 | zhangsan | m    |   20 | zhangsan@123.com |
|    2 | lisi     | f    |   20 | lisi@123.com     |
|    3 | NULL     | NULL | NULL | NULL             |
| NULL | wangwu   | NULL | NULL | NULL             |
+------+----------+------+------+------------------+
注意：insert语句但凡是执行成功了，那么必然会多一条记录。
没有给其它字段指定值的话，默认值是NULL。

	drop table if exists t_student;
	create table t_student(
		no int,
		name varchar(32),
		sex char(1) default 'm',
		age int(3),
		email varchar(255)
	);

	+-------+--------------+------+-----+---------+-------+
	| Field | Type         | Null | Key | Default | Extra |
	+-------+--------------+------+-----+---------+-------+
	| no    | int(11)      | YES  |     | NULL    |       |
	| name  | varchar(32)  | YES  |     | NULL    |       |
	| sex   | char(1)      | YES  |     | m       |       |
	| age   | int(3)       | YES  |     | NULL    |       |
	| email | varchar(255) | YES  |     | NULL    |       |
	+-------+--------------+------+-----+---------+-------+

	insert into t_student(no) values(1);
	mysql> select * from t_student;
	+------+------+------+------+-------+
	| no   | name | sex  | age  | email |
	+------+------+------+------+-------+
	|    1 | NULL | m    | NULL | NULL  |
	+------+------+------+------+-------+

	insert语句中的“字段名”可以省略吗？可以
		insert into t_student values(2); //错误的

		// 注意：前面的字段名省略的话，等于都写上了！所以值也要都写上！
		insert into t_student values(2, 'lisi', 'f', 20, 'lisi@123.com');
		+------+------+------+------+--------------+
		| no   | name | sex  | age  | email        |
		+------+------+------+------+--------------+
		|    1 | NULL | m    | NULL | NULL         |
		|    2 | lisi | f    |   20 | lisi@123.com |
		+------+------+------+------+--------------+

2.1.3、insert插入日期

	数字格式化：format
		select ename,sal from emp;
		+--------+---------+
		| ename  | sal     |
		+--------+---------+
		| SMITH  |  800.00 |
		| ALLEN  | 1600.00 |
		| WARD   | 1250.00 |
		| JONES  | 2975.00 |
		| MARTIN | 1250.00 |
		| BLAKE  | 2850.00 |
		| CLARK  | 2450.00 |
		| SCOTT  | 3000.00 |
		| KING   | 5000.00 |
		| TURNER | 1500.00 |
		| ADAMS  | 1100.00 |
		| JAMES  |  950.00 |
		| FORD   | 3000.00 |
		| MILLER | 1300.00 |
		+--------+---------+

		格式化数字：format(数字, '格式')
			select ename,format(sal, '$999,999') as sal from emp;
			+--------+-------+
			| ename  | sal   |
			+--------+-------+
			| SMITH  | 800   |
			| ALLEN  | 1,600 |
			| WARD   | 1,250 |
			| JONES  | 2,975 |
			| MARTIN | 1,250 |
			| BLAKE  | 2,850 |
			| CLARK  | 2,450 |
			| SCOTT  | 3,000 |
			| KING   | 5,000 |
			| TURNER | 1,500 |
			| ADAMS  | 1,100 |
			| JAMES  | 950   |
			| FORD   | 3,000 |
			| MILLER | 1,300 |
			+--------+-------+

	str_to_date：将字符串varchar类型转换成date类型
	date_format：将date类型转换成具有一定格式的varchar字符串类型。

	drop table if exists t_user;
	create table t_user(
		id int,
		name varchar(32),
		birth date // 生日也可以使用date日期类型
	);

	create table t_user(
		id int,
		name varchar(32),
		birth char(10) // 生日可以使用字符串，没问题。
	);

	生日：1990-10-11 （10个字符）

	注意：数据库中的有一条命名规范：
		所有的标识符都是全部小写，单词和单词之间使用下划线进行衔接。

	mysql> desc t_user;
	+-------+-------------+------+-----+---------+-------+
	| Field | Type        | Null | Key | Default | Extra |
	+-------+-------------+------+-----+---------+-------+
	| id    | int(11)     | YES  |     | NULL    |       |
	| name  | varchar(32) | YES  |     | NULL    |       |
	| birth | date        | YES  |     | NULL    |       |
	+-------+-------------+------+-----+---------+-------+

插入数据？

insert into t_user(id,name,birth) values(1, 'zhangsan', '01-10-1990'); // 1990年10月1日

	出问题了：原因是类型不匹配。数据库birth是date类型，这里给了一个字符串varchar。

	怎么办？可以使用str_to_date函数进行类型转换。
	str_to_date函数可以将字符串转换成日期类型date？
	语法格式：
		str_to_date('字符串日期', '日期格式')

	mysql的日期格式：
		%Y	年
		%m 月
		%d 日
		%h	时
		%i	分
		%s	秒

insert into t_user(id,name,birth) values(1, 'zhangsan', str_to_date('01-10-1990','%d-%m-%Y'));

str_to_date函数可以把字符串varchar转换成日期date类型数据，
通常使用在插入insert方面，因为插入的时候需要一个日期类型的数据，
需要通过该函数将字符串转换成date。

好消息？
如果你提供的日期字符串是这个格式，str_to_date函数就不需要了！！！
%Y-%m-%d
insert into t_user(id,name,birth) values(2, ‘lisi’, ‘1990-10-01’);

2.1.4、查询的时候可以以某个特定的日期格式展示吗？

	date_format
	这个函数可以将日期类型转换成特定格式的字符串。

	select id,name,date_format(birth, '%m/%d/%Y') as birth from t_user;
	+------+----------+------------+
	| id   | name     | birth      |
	+------+----------+------------+
	|    1 | zhangsan | 10/01/1990 |
	|    2 | lisi     | 10/01/1990 |
	+------+----------+------------+

	date_format函数怎么用？
		date_format(日期类型数据, '日期格式')
		这个函数通常使用在查询日期方面。设置展示的日期格式。
	
	mysql> select id,name,birth from t_user;
	+------+----------+------------+
	| id   | name     | birth      |
	+------+----------+------------+
	|    1 | zhangsan | 1990-10-01 |
	|    2 | lisi     | 1990-10-01 |
	+------+----------+------------+
	以上的SQL语句实际上是进行了**默认的日期格式化**，
	自动将数据库中的date类型转换成varchar类型。
	并且采用的格式是mysql默认的日期格式：'%Y-%m-%d'

	select id,name,date_format(birth,'%Y/%m/%d') as birth from t_user;
	
	java中的日期格式？
		yyyy-MM-dd HH:mm:ss SSS

2.1.5、date和datetime两个类型的区别？

date是短日期：只包括年月日信息。
datetime是长日期：包括年月日时分秒信息。

drop table if exists t_user;
create table t_user(
	id int,
	name varchar(32),
	birth date,
	create_time datetime
);


id是整数
name是字符串
birth是短日期
create_time是这条记录的创建时间：长日期类型

mysql短日期默认格式：%Y-%m-%d
mysql长日期默认格式：%Y-%m-%d %h:%i:%s

insert into t_user(id,name,birth,create_time) values(1,'zhangsan','1990-10-01','2020-03-18 15:49:50');

在mysql当中怎么获取系统当前时间？
	now() 函数，并且获取的时间带有：时分秒信息！！！！是datetime类型的。

	insert into t_user(id,name,birth,create_time) values(2,'lisi','1991-10-01',now());

2.2 查询数据

select 字段 from 表 [where 条件];

案例：

--查询不重复的列
select distinct depart from teacher;
--统计满足条件的数据行数
select count(*) from student where class='95031';
--查询Score表中的最高分的学生学号和课程号。
select * from score order by degree desc limit 0,1;--排序之后，读取数据是从索引0开始，截取第0-1个，但是不包含1
--查询所有的学生信息
 select id,name,sex,telphone,age from student;--查询出所有的学生信息
 select * from student;--查询出所有的学生信息
 select name,telphone from student;--只查出用户名和电话号码
--查询id=1的学生信息
 select * from student where id=1;
--查询所有的男学生
 select * from student where sex='男';
--查询年龄大于18的学生信息
 select * from student where age>18;
--查询性别为'男'且年龄大于20的学生
 select * from student where sex='男' and age>20;
--查询姓名为张三和里斯的学生
 select * from student where name='张三' or name='里斯';
 select * from student where name in('张三','里斯');
--查询年龄在19-23之间，包含19和23
 select * from student where age>=19 and age<=23;
 select * from student where age between 19 and 23;
--查询不是张三也不是里斯的所有学生
 select * from student where name<>'张三' and name<>'里斯';
 select * from student where name not in('张三','里斯');
--模糊查询，like
 select * from student where name like '小';--where name='小'
 select * from student where name like '小%';--name以小开头的所有学生信息
 select * from student where name like '%小';--name以小结尾的所有学生信息
 select * from student where name like '%小%';--name包含小的所有的学生信息
 select * from student where name like '%小%大%';--name 既包含小有包含大的所有学生信息
--查询所有以小开头的学生信息
 select * from student where name like '小%';
--排序查询
 select * from student order by id;--根据id进行查询，order by 默认是顺序，asc
 select * from student order by id asc;
 select * from student order by id desc;--根据id倒序查询
 --聚合查询
 count(),sum(),avg(),max(),min()

2.3 删除数据（DML）

2.3.1 delete删除数据（慢）

delete from 表 [where 条件];

说明：
A. 删除数据指的是删除表中的某些行，比如原来有10行，可以将其中的3行删除，则剩下7行

B. where条件表示删除数据所应满足的条件，含义跟select中的一样。

C. where 条件可以不写，如果不写，则会删除所有数据——通常都不会这么用

案例：

--删除id为7的数据
 delete from student where id=7;
--删除性别为男，且年龄小于20的学生信息
 delete from student where sex='男' and age<20;
 --删除表中第一条数据
 delete from student limit 1;

2.3.2 truncate快速删除表中数据

注意：
delete from 表 [where 条件];
这种方式删除数据缺点：效率较低；优点：支持回滚，后悔了，可以再恢复数据

delete语句删除数据的原理？（delete属于DML语句！！！）
表中的数据被删除了，但是这个数据在硬盘上的真实存储空间不会被释放！！！
这种删除缺点是：删除效率比较低。
这种删除优点是：支持回滚，后悔了可以再恢复数据！！！
truncate语句删除数据的原理？
这种删除效率比较高，表被一次截断，物理删除。
这种删除缺点：不支持回滚。
这种删除优点：快速。

用法：truncate table 表名; （这种操作属于DDL操作。）

大表非常大，上亿条记录？？？？
删除的时候，使用delete，也许需要执行1个小时才能删除完！效率较低。
可以选择使用truncate删除表中的数据。只需要不到1秒钟的时间就删除结束。效率较高。
但是使用truncate之前，必须仔细询问客户是否真的要删除，并警告删除之后不可恢复！

truncate是删除表中的数据，表还在！

删除表操作？
	drop table 表名; // 这不是删除表中的数据，这是把表删除。

2.4 修改数据（DML）

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

说明：
A. 修改数据指的是修改表的某些行的某些字段

B. where条件表示修改数据所应满足的条件，含义跟select中的一样

C. where条件可以不写，如果不写，则会修改所有数据——通常都不会这么用

案例：

--修改id为2的姓名为张三，电话号码为123456
update student set name='张三',telphone='123456' where id=2;

3. 数据类型

3.1 数据类型总图

在这里插入图片描述

在sql语句中，数字型数据不需要单引号引起来，而其他时间和字符串类型必须加单引号。其中，数字加单引号，也是可以的。

3.2 整数

整型数据类型包括：

tinyint:微整型
smallint:小整型
mediumint:中整型
int:整型
bigint:大整型

这些不同大小返回的整型信息如下表所示：
在这里插入图片描述
默认整数类型是带符号的，即可以有正负值，比如：

create table zhengxing(num1 int,num2 tinyint);
desc zhengxing;
insert into zhengxing values(-1,-1);--可以运行
insert into zhengxing values(-255,-255);--报错，超出范围

此时，num1和num2中都可以存储负数（但都不能超出范围）

不带符号的整数类型设置形式如下：

create table zhengxing1(num1 int unsigned,num2 tinyint unsigned);
insert into zhengxing1(-1,-1);--报错的，不能为负数
insert into zhengxing1 values(244,244);--可以运行的

3.3 小数

小数类型分为浮点小数和定点小数。

3.3.1 浮点小数

浮点小数是”不精确的小数”，包括float和double

float:

占用4字节存储空间，可称为”单精度浮点数”,约7位有效数字

double:

占用8字节存储空间，可称为”双精度浮点数”，约17位有效数字

3.3.2 定点小数

定点小数是”精度的小数”——它通过内部技巧，突破了”有些小数无法用二进制精确表示”的局限。其设定方式通常是这样的：decimal(M,D);

其中M表示该小数的总的有效位数(最大65),D表示该小数的小数点后的位数。

create table xiaoshu (num1 float,num2 double,num3 decimal(7,2));
insert into xiaoshu values(1,2,3);
insert into xiaoshu values(2,2,78.63123);
insert into xiaoshu values(3,4,98765.651234);

3.4 日期时间类型

日期时间类型包括如下几种：

date类型：
表示日期，格式类似这样：’0000-00-00’

time类型：
表示时间，格式类似这样：’00:00:00’

datetime类型：
表示日期时间，格式类似这样：’0000-00-00 00:00:00’

timestamp类型：
表示”时间戳”,其实就是一个整数数字，该数字是从”时间起点”到现在为止的”秒数”.”时间起点”是：1970-1-1 00:00:00

timestamp类型的字段，无需插入数据，而是会自动取得当前的日期时间（表示当前时刻）。而且，此类型字段会在数据被更新时，也同样自动取得当前的日期时间（表示修改的时刻）。

year类型：
表示年份，格式为：’0000’

create table shijian(t_time time,t_date date,t_datetime datetime,t_timestamp timestamp,t_year year);
insert into shijian(t_time,t_date,t_datetime,t_year) values('21:28','2023-10-16','2023-10-16 21:29:00','2023');
insert into shijian(t_time,t_date,t_datetime,t_year) values('2023-10-16 21:29:00','2023-10-16 21:29:00','2023-10-16 21:29:00','2023');

在这里插入图片描述

注意：timestamp在一个表中只能用一个字段，时间类型通常使用单引号引起来
特点总结，timestamp在新增和修改的时候，自动更新时间

3.5 字符串类型

字符串类型常用的包括：char,varchar,text,enum,set,分述如下：

3.5.1 定长字符char和变长字符varchar

定长字符类型char:

适用于存储的字符长度为固定长度的字符，比如中国邮政编码，中国身份证号码，手机号码等。设定形式：

字段名字 char(长度)

其特点是：
A. 存储的字符长度固定，最长可设定为255个字符
B. 如果实际写入的字符不足设定长度，内部会自动用空格填充到设定的长度
C. 相对varchar类型，其存取速度更快

变长字符类型varchar:

适用于存储字符长度经常不确定的字符，比如姓名，用户名，标题，内容，等大多数场合的字符。设定形式：

字段名称 varchar(长度)

其特点是：
A. 存储的字符长度是写入的实际长度，但不超过设定的长度。最长可设定为65532(字节)
B. 注：由于其最长的限制是字节数，因此存储中文和英文的实际字符个数是不同的。

英文：一个字符占一个字节

中文（gbk编码）：一个字符占2个字节

中文（utf8编码）：一个字符占三个字节

C. 如果实际写入的字符不足设定的长度，就按实际的长度存储

D. 相对于char字符串，其存取速度相对更慢

E. 注意：有些版本的数据库，即使数据太长，也会自动切断

3.5.2 长文本text、mediumtext、longtext

适用于存储”较长的文本内容”，比如文章内容。最长可存储65535个字符。

如果还需要存储更长的文本，可以使用mediumtext(1600万左右)或longtext(40亿左右)。

设定形式：

字段名称 text;

text类型的字段不能设置默认值

注意：text不能设置长度，text的数据不能存储在行中，

注意：now()当前时间

3.5.3 enum和set类型

enum类型和set类型都是用于存储”有给定值的可选字符”，比如类似表单中的单选，多选，下拉列表

enum类型（单选类型/枚举类型）：

enum类型通常用于存储表中的”单选项”的值。

设定形式：enum(‘选项值1’,’选项值2’,’选项值3’…)

这些选项值都对应了相应的”索引值”,类似索引数组的小标，但是从1开始的。

即这些选项的索引值分别为：1，2，3，4…

enum类型最多可设定65535个选项。

set类型（多选类型）：

set类型通常用于存储表单中的”多选项”的值.

设定形式：set(‘选项1’,’选项2’,’选项3’…);

这些选项值都对应了相应的”索引值”,其索引值从1开始，并”依次翻倍”。

即这些选项的索引值分别为：1，2，4，8，16…（其实就是2的n次方）

set类型最多可设定64个选项值。

示例：

### 4.8 字段属性

列属性是指定义或创建一个列的时候，可以给列额外添加的”附加特性”。

形式如下：

	**Create table 表名（列名 列类型 [列属性]）;**

说明：

A. 一个列可以有多个列属性

B. 多个列属性空格隔开就行

列属性包括以下这些：

A. null,not null

	1. 设定为空，或非空，表明该列数据是否可为空值(null)

B. default 

1. 用于设定列默认值（不给值或给空值null,就会自动使用该值）
2. 使用形式：default 默认值。

C. primary key

1. 用于设定主键
2. 主键就是一个表中数据的”关键值”，通过该关键值就可以找到该特定的数据行
3. 一个表的主键值不能重复（相等），比如文章表中的文章编号id,比如用户表中的用户名。
4. 主键字段必须有值（不能为空）。
5. 一个表只能有一个主键（但一个主键可以是一个字段或2个以上的字段联合构成）

D. auto_increment

1. 用于设定一个整数字段的值是”自增长的”，通常用于一个表中的数据行的编号（比如文章编号）。
2. 默认情况下自增长值从1开始
3. 一个表只能设定一个字段为自增长特性。

E. unique key

1. 用于设定”唯一键”的特性
2. 唯一键表示一个表中的某字段的值是”唯一的”,”不重复的”。
3. 唯一键有点类似primary key,但其值可以为空(null).
4. 一个表可以有多个唯一键

F. Comment

 - 用于设定字段的说明性内容，类似注释，但有不是注释（属于有效的代码）
 - 使用形式：comment 文字内容

3.5.4 clob和blob

clob

 	字符大对象
 	Character Large OBject:CLOB
 	最多可以存储4G的字符串。
 	比如：存储一篇文章，存储一个说明。
 	超过255个字符的都要采用CLOB字符大对象来存储。

blob

 	二进制大对象
 	Binary Large OBject:BLOB
 	专门用来存储图片、声音、视频等流媒体数据。
 	往BLOB类型的字段上插入数据的时候，例如插入一个图片、视频等，
 	你需要使用IO流才行。

3.6 字段属性

列属性是指定义或创建一个列的时候，可以给列额外添加的”附加特性”。

形式如下：

Create table 表名（列名列类型 [列属性]）;

说明：

A. 一个列可以有多个列属性

B. 多个列属性空格隔开就行

列属性包括以下这些：

A. null,not null

1. 设定为空，或非空，表明该列数据是否可为空值(null)

B. default

用于设定列默认值（不给值或给空值null,就会自动使用该值）
使用形式：default 默认值。

C. primary key

用于设定主键
主键就是一个表中数据的”关键值”，通过该关键值就可以找到该特定的数据行
一个表的主键值不能重复（相等），比如文章表中的文章编号id,比如用户表中的用户名。
主键字段必须有值（不能为空）。
一个表只能有一个主键（但一个主键可以是一个字段或2个以上的字段联合构成）

D. auto_increment

用于设定一个整数字段的值是”自增长的”，通常用于一个表中的数据行的编号（比如文章编号）。
默认情况下自增长值从1开始
一个表只能设定一个字段为自增长特性。

E. unique key

用于设定”唯一键”的特性
唯一键表示一个表中的某字段的值是”唯一的”,”不重复的”。
唯一键有点类似primary key,但其值可以为空(null).
一个表可以有多个唯一键

F. Comment

用于设定字段的说明性内容，类似注释，但有不是注释（属于有效的代码）
使用形式：comment 文字内容

create table user(id int auto_increment primary key,userName varchar(10) not null,sex enum('男','女') default '男' not null,money decimal(10,2));
insert into user(id,sex) values(1,'男'); --因为userName不允许为空，但是没有插入数据，所以报错，在新增数据的时候，如果要罗列字段的话，就必须包含表中所有的非空字段
insert into user(id,userName) values(1,'user1'); --如果设置了默认值，是可以不用去插入的
insert into user(id,userName) values(2,'user2'); --id这个字段设置了主键，主键是不可以重复的
insert into user(userName) values('user3'); --自增列，系统会自动生成，不需要手动添加的
insert into user values(null,'user4',default,10); --如果前面的没有罗列字段，又要满足一一对应，此时自增长列，可以手动添加一个数据，也可以不用写，让系统自动的来编写。默认值，可以手动添加值，也可以采用默认值，直接使用default关键字就可以了

4. 约束【非常重要】

4.1、什么是约束？

约束对应的英语单词：constraint
在创建表的时候，我们可以给表中的字段加上一些约束，来保证这个表中数据的
完整性、有效性！！！

约束的作用就是为了保证：表中的数据有效！！

4.2、约束包括哪些？

非空约束：not null
唯一性约束: unique
主键约束: primary key （简称PK）
外键约束：foreign key（简称FK）
检查约束：check（mysql不支持，oracle支持）

我们这里重点学习四个约束：
not null
unique
primary key
foreign key

4.3、非空约束：not null

非空约束not null约束的字段不能为NULL。

drop table if exists t_vip;
create table t_vip(
	id int,
	name varchar(255) not null  // not null只有列级约束，没有表级约束！
);
insert into t_vip(id,name) values(1,'zhangsan');
insert into t_vip(id,name) values(2,'lisi');

insert into t_vip(id) values(3);
ERROR 1364 (HY000): Field 'name' doesn't have a default value

小插曲：
	xxxx.sql这种文件被称为sql脚本文件。
	sql脚本文件中编写了大量的sql语句。
	我们执行sql脚本文件的时候，该文件中所有的sql语句会全部执行！
	批量的执行SQL语句，可以使用sql脚本文件。
	在mysql当中怎么执行sql脚本呢？
		mysql> source D:\course\03-MySQL\document\vip.sql
	
	你在实际的工作中，第一天到了公司，项目经理会给你一个xxx.sql文件，
	你执行这个脚本文件，你电脑上的数据库数据就有了！

4.4、唯一性约束: unique

唯一性约束unique约束的字段不能重复，但是可以为NULL。

	drop table if exists t_vip;
	create table t_vip(
		id int,
		name varchar(255) unique,
		email varchar(255)
	);
	insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
	insert into t_vip(id,name,email) values(2,'lisi','lisi@123.com');
	insert into t_vip(id,name,email) values(3,'wangwu','wangwu@123.com');
	select * from t_vip;

	insert into t_vip(id,name,email) values(4,'wangwu','wangwu@sina.com');
	ERROR 1062 (23000): Duplicate entry 'wangwu' for key 'name'

	insert into t_vip(id) values(4);
	insert into t_vip(id) values(5);

+------+----------+------------------+
| id   | name     | email            |
+------+----------+------------------+
|    1 | zhangsan | zhangsan@123.com |
|    2 | lisi     | lisi@123.com     |
|    3 | wangwu   | wangwu@123.com   |
|    4 | NULL     | NULL             |
|    5 | NULL     | NULL             |
+------+----------+------------------+
name字段虽然被unique约束了，但是可以为NULL。

新需求：name和email两个字段联合起来具有唯一性！！！！

		drop table if exists t_vip;
		create table t_vip(
			id int,
			name varchar(255) unique,  // 约束直接添加到列后面的，叫做列级约束。
			email varchar(255) unique
		);

	这张表这样创建是不符合我以上“新需求”的。
	这样创建表示：name具有唯一性，email具有唯一性。各自唯一。

	以下这样的数据是符合我“新需求”的。
	但如果采用以上方式创建表的话，肯定创建失败，因为'zhangsan'和'zhangsan'重复了。

insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(2,'zhangsan','zhangsan@sina.com');

怎么创建这样的表，才能符合新需求呢？

drop table if exists t_vip;
create table t_vip(
	id int,
	name varchar(255),
	email varchar(255),
	unique(name,email) // 约束没有添加在列的后面，这种约束被称为表级约束。
);
insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(2,'zhangsan','zhangsan@sina.com');
select * from t_vip;

name和email两个字段联合起来唯一！！！
insert into t_vip(id,name,email) values(3,'zhangsan','zhangsan@sina.com');
ERROR 1062 (23000): Duplicate entry 'zhangsan-zhangsan@sina.com' for key 'name'

什么时候使用表级约束呢？
需要给多个字段联合起来添加某一个约束的时候，需要使用表级约束。

unique 和not null可以联合吗？

		drop table if exists t_vip;
		create table t_vip(
			id int,
			name varchar(255) not null unique
		);

	mysql> desc t_vip;
	+-------+--------------+------+-----+---------+-------+
	| Field | Type         | Null | Key | Default | Extra |
	+-------+--------------+------+-----+---------+-------+
	| id    | int(11)      | YES  |     | NULL    |       |
	| name  | varchar(255) | NO   | PRI | NULL    |       |
	+-------+--------------+------+-----+---------+-------+

	在mysql当中，如果一个字段同时被not null和unique约束的话，
	该字段自动变成主键字段。（注意：oracle中不一样！）

insert into t_vip(id,name) values(1,'zhangsan');

insert into t_vip(id,name) values(2,'zhangsan'); // 错误了：name不能重复

insert into t_vip(id) values(2); // 错误了：name不能为NULL。

4.5、主键约束（primary key，简称PK）非常重要*****

主键约束的相关术语？
    主键约束：就是一种约束。
	主键字段：该字段上添加了主键约束，这样的字段叫做：主键字段
	主键值：主键字段中的每一个值都叫做：主键值。

什么是主键？有啥用？
	主键值是每一行记录的唯一标识。
	主键值是每一行记录的身份证号！！！

记住：任何一张表都应该有主键，没有主键，表无效！！

主键的特征：not null + unique（主键值不能是NULL，同时也不能重复！）

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

		drop table if exists t_vip;
		// 1个字段做主键，叫做：单一主键
		create table t_vip(
			id int primary key,  //列级约束
			name varchar(255)
		);
		insert into t_vip(id,name) values(1,'zhangsan');
		insert into t_vip(id,name) values(2,'lisi');

		//错误：不能重复
		insert into t_vip(id,name) values(2,'wangwu');
		ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

		//错误：不能为NULL
		insert into t_vip(name) values('zhaoliu');
		ERROR 1364 (HY000): Field 'id' doesn't have a default value

可以这样添加主键吗，使用表级约束？

		drop table if exists t_vip;
		create table t_vip(
			id int,
			name varchar(255),
			primary key(id)  // 表级约束
		);
		insert into t_vip(id,name) values(1,'zhangsan');

		//错误
		insert into t_vip(id,name) values(1,'lisi');
		ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

表级约束主要是给多个字段联合起来添加约束？

		drop table if exists t_vip;
		// id和name联合起来做主键：复合主键！！！！
		create table t_vip(
			id int,
			name varchar(255),
			email varchar(255),
			primary key(id,name)
		);
		insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
		insert into t_vip(id,name,email) values(1,'lisi','lisi@123.com');

		//错误：不能重复
		insert into t_vip(id,name,email) values(1,'lisi','lisi@123.com');
		ERROR 1062 (23000): Duplicate entry '1-lisi' for key 'PRIMARY'

在实际开发中不建议使用：复合主键。建议使用单一主键！
因为主键值存在的意义就是这行记录的身份证号，只要意义达到即可，单一主键可以做到。
复合主键比较复杂，不建议使用！！！

一个表中主键约束能加两个吗？

		drop table if exists t_vip;
		create table t_vip(
			id int primary key,
			name varchar(255) primary key
		);
		ERROR 1068 (42000): Multiple primary key defined

结论：一张表，主键约束只能添加1个。（主键只能有1个。）

主键值建议使用：
	int
	bigint
	char
	等类型。

	不建议使用：varchar来做主键。主键值一般都是数字，一般都是定长的！

主键除了：单一主键和复合主键之外，还可以这样进行分类？
	自然主键：主键值是一个自然数，和业务没关系。
	业务主键：主键值和业务紧密关联，例如拿银行卡账号做主键值。这就是业务主键！

	在实际开发中使用业务主键多，还是使用自然主键多一些？
		自然主键使用比较多，因为主键只要做到不重复就行，不需要有意义。
		业务主键不好，因为主键一旦和业务挂钩，那么当业务发生变动的时候，
		可能会影响到主键值，所以业务主键不建议使用。尽量使用自然主键。

在mysql当中，有一种机制，可以帮助我们自动维护一个主键值？

		drop table if exists t_vip;
		create table t_vip(
			id int primary key auto_increment, //auto_increment表示自增，从1开始，以1递增！
			name varchar(255)
		);
		insert into t_vip(name) values('zhangsan');
		insert into t_vip(name) values('zhangsan');
		insert into t_vip(name) values('zhangsan');
		insert into t_vip(name) values('zhangsan');
		insert into t_vip(name) values('zhangsan');
		insert into t_vip(name) values('zhangsan');
		insert into t_vip(name) values('zhangsan');
		insert into t_vip(name) values('zhangsan');
		select * from t_vip;

		+----+----------+
		| id | name     |
		+----+----------+
		|  1 | zhangsan |
		|  2 | zhangsan |
		|  3 | zhangsan |
		|  4 | zhangsan |
		|  5 | zhangsan |
		|  6 | zhangsan |
		|  7 | zhangsan |
		|  8 | zhangsan |
		+----+----------+

4.6、外键约束（foreign key，简称FK）非常重要*****

外键约束涉及到的相关术语：
	外键约束：一种约束（foreign key）
	外键字段：该字段上添加了外键约束
	外键值：外键字段当中的每一个值。

业务背景：
	请设计数据库表，来描述“班级和学生”的信息？

		第一种方案：班级和学生存储在一张表中？？？
		t_student
		no(pk)			    name		classno			classname
		----------------------------------------------------------------------------------
		1					jack			100			北京市大兴区亦庄镇第二中学高三1班
		2					lucy			100			北京市大兴区亦庄镇第二中学高三1班
		3					lilei			100			北京市大兴区亦庄镇第二中学高三1班
		4					hanmeimei	    100			北京市大兴区亦庄镇第二中学高三1班
		5					zhangsan		101			北京市大兴区亦庄镇第二中学高三2班
		6					lisi			101			北京市大兴区亦庄镇第二中学高三2班
		7					wangwu		    101			北京市大兴区亦庄镇第二中学高三2班
		8					zhaoliu		    101			北京市大兴区亦庄镇第二中学高三2班
		分析以上方案的缺点：
			数据冗余，空间浪费！！！！
			这个设计是比较失败的！
		
		第二种方案：班级一张表、学生一张表？？
		
		t_class 班级表
		classno(pk)			classname
		------------------------------------------------------
		100					北京市大兴区亦庄镇第二中学高三1班
		101					北京市大兴区亦庄镇第二中学高三1班
	
		t_student 学生表
		no(pk)			name				cno(FK引用t_class这张表的classno)
		----------------------------------------------------------------
		1					jack				100
		2					lucy				100
		3					lilei				100
		4					hanmeimei		    100
		5					zhangsan			101
		6					lisi				101
		7					wangwu			    101
		8					zhaoliu			    101

		当cno字段没有任何约束的时候，可能会导致数据无效。可能出现一个102，但是102班级不存在。
		所以为了保证cno字段中的值都是100和101，需要给cno字段添加外键约束。
		那么：cno字段就是外键字段。cno字段中的每一个值都是外键值。

		注意：
			t_class是父表
			t_student是子表

			删除表的顺序？
				先删子，再删父。

			创建表的顺序？
				先创建父，再创建子。

			删除数据的顺序？
				先删子，再删父。

			插入数据的顺序？
				先插入父，再插入子。

		思考：
			子表中的外键引用的父表中的某个字段，被引用的这个字段必须是主键吗？
			不一定是主键，但至少具有unique约束。

		测试：
			外键可以为NULL吗？
			外键值可以为NULL。

5、存储引擎（了解内容）

5.1、什么是存储引擎，有什么用呢？

存储引擎是MySQL中特有的一个术语，其它数据库中没有。（Oracle中有，但是不叫这个名字）
存储引擎这个名字高端大气上档次。
实际上存储引擎是一个表存储/组织数据的方式。
不同的存储引擎，表存储数据的方式不同。

5.2、怎么给表添加/指定“存储引擎”呢？

	show create table t_student;

	可以在建表的时候给表指定存储引擎。
	CREATE TABLE `t_student` (
	  `no` int(11) NOT NULL AUTO_INCREMENT,
	  `name` varchar(255) DEFAULT NULL,
	  `cno` int(11) DEFAULT NULL,
	  PRIMARY KEY (`no`),
	  KEY `cno` (`cno`),
	  CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`cno`) REFERENCES `t_class` (`classno`)
	) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8

	在建表的时候可以在最后小括号的")"的右边使用：
		ENGINE来指定存储引擎。
		CHARSET来指定这张表的字符编码方式。
	
		结论：
			mysql默认的存储引擎是：InnoDB
			mysql默认的字符编码方式是：utf8
	
	建表时指定存储引擎，以及字符编码方式。
	create table t_product(
		id int primary key,
		name varchar(255)
	)engine=InnoDB default charset=gbk;

5.3、怎么查看mysql支持哪些存储引擎呢？

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.36    |
+-----------+

命令： show engines \G

*************************** 1. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 8. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 9. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO

mysql支持九大存储引擎，当前5.5.36支持8个。版本不同支持情况不同。

5.4、关于mysql常用的存储引擎介绍一下

5.4.1 MyISAM存储引擎

它管理的表具有以下特征：
	使用三个文件表示每个表：

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

索引文件 — 存储表上索引（mytable.MYI）：索引是一本书的目录，缩小扫描范围，提高查询效率的一种机制。

 可被转换为压缩、只读表来节省空间

 提示一下：
 	对于一张表来说，只要是主键，
 	或者加有unique约束的字段上会自动创建索引。

 MyISAM存储引擎特点：
 	可被转换为压缩、只读表来节省空间
 	这是这种存储引擎的优势！！！！
 
 MyISAM不支持事务机制，安全性低。

5.4.2 InnoDB存储引擎

这是**mysql默认的存储引擎**，同时也是一个重量级的存储引擎。
InnoDB支持事务，**支持数据库崩溃后自动恢复机制**。
InnoDB存储引擎最主要的特点是：非常安全。

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

InnoDB最大的特点就是支持事务：
	以保证数据的安全。效率不是很高，并且也不能压缩，不能转换为只读，
	不能很好的节省存储空间。

5.4.3 MEMORY存储引擎

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

MEMORY 存储引擎管理的表具有下列特征：
	– 在数据库目录内，每个表均以.frm 格式的文件表示。
	– 表数据及索引被存储在内存中。（目的就是快，查询快！）
	– 表级锁机制。
	– 不能包含 TEXT 或 BLOB 字段。

MEMORY 存储引擎以前被称为HEAP 引擎。

MEMORY引擎优点：查询效率是最高的。不需要和硬盘交互。
MEMORY引擎缺点：不安全，关机之后数据消失。因为数据和索引都是在内存当中。

查看全文

http://www.kler.cn/a/314552.html