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

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

news 2024/11/15 8:36:48

数据定义语言(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插入日期

	数字格式化：formatselect 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:%sinsert 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和23select * 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('张三','里斯');
--模糊查询，likeselect * 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 默认是顺序，ascselect * 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 key1. 用于设定主键
2. 主键就是一个表中数据的”关键值”，通过该关键值就可以找到该特定的数据行
3. 一个表的主键值不能重复（相等），比如文章表中的文章编号id,比如用户表中的用户名。
4. 主键字段必须有值（不能为空）。
5. 一个表只能有一个主键（但一个主键可以是一个字段或2个以上的字段联合构成）D. auto_increment1. 用于设定一个整数字段的值是”自增长的”，通常用于一个表中的数据行的编号（比如文章编号）。
2. 默认情况下自增长值从1开始
3. 一个表只能设定一个字段为自增长特性。E. unique key1. 用于设定”唯一键”的特性
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'//错误：不能为NULLinsert 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个。）

主键值建议使用：intbigintchar等类型。不建议使用：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_studentno(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				1002					lucy				1003					lilei				1004					hanmeimei		    1005					zhangsan			1016					lisi				1017					wangwu			    1018					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默认的存储引擎是：InnoDBmysql默认的字符编码方式是：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常用的存储引擎介绍一下

MyISAM存储引擎？
它管理的表具有以下特征：
使用三个文件表示每个表：

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

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

 可被转换为压缩、只读表来节省空间提示一下：对于一张表来说，只要是主键，或者加有unique约束的字段上会自动创建索引。MyISAM存储引擎特点：可被转换为压缩、只读表来节省空间这是这种存储引擎的优势！！！！MyISAM不支持事务机制，安全性低。

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

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

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

MEMORY 存储引擎管理的表具有下列特征：– 在数据库目录内，每个表均以.frm 格式的文件表示。– 表数据及索引被存储在内存中。（目的就是快，查询快！）– 表级锁机制。– 不能包含 TEXT 或 BLOB 字段。MEMORY 存储引擎以前被称为HEAP 引擎。MEMORY引擎优点：查询效率是最高的。不需要和硬盘交互。
MEMORY引擎缺点：不安全，关机之后数据消失。因为数据和索引都是在内存当中。

查看全文

http://www.mrgr.cn/news/29201.html