数据库MySQL使用

MySQL

理论知识

基本概念

DB

数据库，database，存储数据的仓库

DBMS

数据库管理系统，database management system，数据库是通过DBMS创建和操作的容器。常见的有MySQL，oracle，gbase，sqlserver等

SQL

结构化查询语言，structure query language，专门用来与数据库通信的语言
sql的优点：

• 不是某个特定数据库供应商的专有语言，技术所有DBMS都支持sql，只不过在细节上可能不一样
• 简单易学
• 可以灵活使用进行复杂和高级的数据库操作

存储特点

• 将数据放到表中，表在放到库中
• 一个库可以有多张表，每个表的名字具有唯一性
• 表中有一些特性，这些特性定义了数据在表中如何存储
• 表由列构成，也称为“字段”，所有表都有若干个列组成，每一列类似于Java中的一个成员变量
• 表中数据按行存储，每一行相当于一个对象

配置文件

my.ini

操作命令

启动mysql

• 第一种方法，右键我的电脑，选择管理，选择服务，找到mysql，可设置启动、关闭、自启

• 第二种方法，通过命令

  net start mysql 打开数据库

  net stop mysql 关闭数据库

数据库连接和退出

• mysql -h 主机名 -P 端口号 -u 用户名 -p
  h表示链接哪个主机（或服务器），P表示 端口号
  u表示用户名，p表示密码。此种方式可以不显示密码。也可以直接在-p后面加上密码
  注意：h，P，u和后面的参数直接空格可有可无，p和密码之间没有空格
  一般我们连的都是自己的电脑，所以主机和端口可以省略，写作：
  mysql -u root -proot123
• exit 退出

查看版本号

• 进入mysql的情况下：select version();
• 不进入mysql：mysql --version或mysql -V

SQL语句

==SQL 是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。==与其他程序设计语言（如 C语言、Java 等）不同的是，SQL 由很少的关键字组成，每个 SQL 语句通过一个或多个关键字构成。

SQL包含以下四部分：

1）数据定义语言（Data Definition Language，DDL）

用来创建或删除数据库以及表等对象，主要包含以下几种命令：

• DROP：删除数据库和表等对象
• CREATE：创建数据库和表等对象
• ALTER：修改数据库和表等对象的结构

2）数据操作语言（Data Manipulation Language，DML）

用来变更表中的记录，主要包含以下几种命令：

• SELECT：查询表中的数据
• INSERT：向表中插入新数据
• UPDATE：更新表中的数据
• DELETE：删除表中的数据

3）数据查询语言（Data Query Language，DQL）

用来查询表中的记录，主要包含 SELECT 命令，来查询表中的数据。

4）数据控制语言（Data Control Language，DCL）

用来确认或者取消对数据库中的数据进行的变更。除此之外，还可以对数据库中的用户设定权限。主要包含以下几种命令：

• GRANT：赋予用户操作权限
• REVOKE：取消用户的操作权限
• COMMIT：确认对数据库中的数据进行的变更
• ROLLBACK：取消对数据库中的数据进行的变更

语法规范

• 不区分大小写，但建议关键字大写，表名，列名小写

• 每条命令分号结尾

• 根据需要，命令可以换行缩进等

• 注释

  • 单行：#注释文字
  • 单行：-- 注释文字
  • 多行：/* 注释文字 */

数据库操作

查看数据库

show databases [like 数据库名];

模糊查询：%

• information_schema：主要存储了系统中的一些数据库对象信息，比如用户表信息、列信息、权限信息、字符集信息和分区信息等。
• mysql：MySQL 的核心数据库，类似于 SQL Server 中的 master 表，主要负责存储数据库用户、用户访问权限等 MySQL 自己需要使用的控制和管理信息。常用的比如在 mysql 数据库的 user 表中修改 root 用户密码。
• performance_schema：主要用于收集数据库服务器性能参数。
• sakila：MySQL 提供的样例数据库，该数据库共有 16 张表，这些数据表都是比较常见的，在设计数据库时，可以参照这些样例数据表来快速完成所需的数据表。
• sys：MySQL 5.7 安装完成后会多一个 sys 数据库。sys 数据库主要提供了一些视图，数据都来自于 performation_schema，主要是让开发者和使用者更方便地查看性能问题。
• world：world 数据库是 MySQL 自动创建的数据库，该数据库中只包括 3 张数据表，分别保存城市，国家和国家使用的语言等内容。

创建数据库

create databases [if not exists] 数据库名
[[default] character set <字符集名>] 
[[default] collate <校对规则名>];
1
2
3

• <数据库名>：创建数据库的名称。MySQL 的数据存储区将以目录方式表示 MySQL 数据库，因此数据库名称必须符合操作系统的文件夹命名规则，不能以数字开头，尽量要有实际意义。注意在 MySQL 中不区分大小写。
• IF NOT EXISTS：在创建数据库之前进行判断，只有该数据库目前尚不存在时才能执行操作。此选项可以用来避免数据库已经存在而重复创建的错误。
• [DEFAULT] CHARACTER SET：指定数据库的字符集。指定字符集的目的是为了避免在数据库中存储的数据出现乱码的情况。如果在创建数据库时不指定字符集，那么就使用系统的默认字符集。
• [DEFAULT] COLLATE：指定字符集的默认校对规则。

修改数据库

alter database [数据库名] { 
[default] character set <字符集名> |
[default] collate <校对规则名>};
1
2
3

删除数据库

drop database [if exists] 数据库名;

MySQL 安装后，系统会自动创建名为 information_schema 和 mysql 的两个系统数据库，系统数据库存放一些和数据库相关的信息，如果删除了这两个数据库，MySQL 将不能正常工作。

选择数据库

use 数据库名

查询当前所在库

select database();

数据表操作

创建表

create table [if not exists] 表名(
	字段1 数据类型[字段属性 | 约束] [索引][注释]，
	……
	字段n 数据类型[字段属性 | 约束] [索引][注释]
)[表类型][表字符集][注释];
1
2
3
4
5

• <表名>：指定要创建表的名称，在 CREATE TABLE 之后给出，必须符合标识符命名规则。表名称被指定为 db_name.tbl_name，以便在特定的数据库中创建表。无论是否有当前数据库，都可以通过这种方式创建。在当前数据库中创建表时，可以省略 db-name。如果使用加引号的识别名，则应对数据库和表名称分别加引号。例如，‘mydb’.‘mytbl’ 是合法的，但 ‘mydb.mytbl’ 不合法。

以下面表格为例，用命令创建表

查看库里面所有表

show table;

查看表结构

describe 表名

或

desc 表名

以sql语句的形式展示表结构

show create table 表名

修改表

alter table 表名[修改选项]

修改选项语法：

{ ADD COLUMN <列名> <类型>
| CHANGE COLUMN <旧列名> <新列名> <新列类型>
| ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }
| MODIFY COLUMN <列名> <类型>
| DROP COLUMN <列名>
| RENAME TO <新表名>
| CHARACTER SET <字符集名>
| COLLATE <校对规则名> }
1
2
3
4
5
6
7
8

练习：

1. 修改列名
2. 修改列的类型或约束
3. 添加新列
4. 删除列
5. 修改表名

注意：

​ first添加到首行

​ after 列名 添加到某一行之后

​ 添加时类型、约束等的写法和创建一样

删除表

DROP TABLE [IF EXISTS] 表名1 [ ,表名2, 表名3 ...]

null 为空 notnull不可为空

-r-m 还是-r-f 是删除linx全部的库 *号代表全部意思

数据操作

插入

insert into 表名（列名，,，) values(xxx,XXX,)

insert into 表名 values(自增要给一个null);

可以插入多行，可以支持子查询
下面的方式二不常见
insert into 表名 set 列名=值，列名=值，

删除

删除单行

delete from 表名 where 筛选条件

删除表中所有数据

truncate table 表名

修改

update 表名 set 列=新值，列=新值 where 筛选条件

查询

select 字段，字段 from 表名 where 筛选条件

条件运算符：> < = !=不等于 <>规范是用这个不等于 >= <=

SELECT userName from user_table where userid >5

逻辑运算符：and 和 or 或 not(不是)非

SELECT userName,sex from user_table where userid >5 AND sex='男'

SELECT userName,sex from user_table where userid >5 and NOT(sex='n')

模糊查询：like ‘xx%’ (%表示任意多个字符，_表示单个字符),

select * FROM user_table WHERE userName like 'J_ker'

select * FROM user_table WHERE userName like 'J%'

between and 在…之间/之间和

select * FROM user_table where userid BETWEEN 10 AND 19

查询所有当userid在10到19之间的所有数据

between 还具有数据比较功能
语法如下 expr BETWEEN min AND max

​ 当 expr 表达式的值大于或等于 min 且小于或等于 max 时, BETWEEN 的返回值为 1 ，否则返回 0 。利用这个功能，可以判断一个表达式或值否则在某个区间：
SELECT 1 BETWEEN 2 AND 3 返回0

​ in在…内

`select * from test where  id in (1,2,3,4)`
1

等同于 select * from test where (id=1 or id=2 or id=3 or id=4)

not in 不在…里 WHERE column NOT IN (列表项)

​ is null/is not null 是否为空/是否不会空

SELECT * from 表名 where 筛选条件 is not null;不为空语句

SELECT * from 表名 where 筛选条件 is null;不为空语句

查询常量、计算

查询常量 不会改变动的值 是常量

select 常量;

数据库里的加法不能拼接 只能做运算 还可以做减号 除号 乘号

函数

点击这里查看 http://c.biancheng.net/mysql/function/

MySQL 聚合函数

#查询每个班有多少人
select count(*) 
from student_table 
group by classid;

#查询每个班的最大年龄是多少
select max(age),classid 
from student_table
group by classid;

#查询每个班学生年龄最大值、最小值、平均值、总和，并按班级降序
#知识点 ：max min avg sum group by 分组 order by desc降序，别名
select max(age) 最大值,min(age) 最小值, avg(age) 平均值, sum(age) 总和, classid
from student_table
group by classid
order by classid desc;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

where 后面不可以添加函数

只能再group by后面添加

别名

as 空格

去重：distinct

拼接：concat ()

长度: length()

排序：order by

asc 升序

desc 降序

分组：group by 查询前操作

slect 分组函数, 列(要求出现在group by后面)
from 表
where 筛选条件
group by 分组的列表
order by 子局
1
2
3
4
5

分组后查询:having 查询后对数据进行操作

having可以和where子句可以在同一个select中一起使用

where–>group by–>having

限定查询几页: limit x/xx,xx.

多表查询

等值连接（92）92年指定的sql语句规则

select 列名 
from 表1，表2
where 筛选条件 【group by 分组】【having 筛选条件】【order by 排序列表】
1
2
3

等值连接是where后面用=号连接的是等值连接

非等值连接（92）

select 列名 
from 表1，表2
where 筛选条件 【group by 分组】【having 筛选条件】【order by 排序列表】
1
2
3

非等值连接 在where后面不用=号连接的是非等值连接

自连接

简单:自己连接自己

自联结顾名思义就是把一张表假设为两张一样的表，然后在做“多表查询”

---

sql99 99年指定的SQL语法规则

select 列名
from 表1 别名【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】
1
2
3
4
5
6
7
8

内连接（重要）（只显示符合条件的数据，相当于两张表的交集)

• inner

#查询人员和部门所有信息
select * from person inner join dept on person.did =dept.did;
1
2

外连接

用于查询一个表中有，另一个表没有的记录

查询结果为主表中的所有记录；

如果从表中有和主表匹配的，则显示匹配的值

如果从表中没有匹配的，显示null

外连接查询结果=内连接结果+主表有从表没有的记录

全外链接=内连接结果+主表有从表没有+从表有主表没有

• 左外：left 【outer】（左边表中的数据优先全部显示）

  #查询人员和部门所有信息
  select * from person left join dept on person.did =dept.did;
  1
  2

  效果:人员表中的数据全部都显示,而 部门表中的数据符合条件的才会显示,不符合条件的会以 null 进行填充.也就是优先显示left join**左边表的数据，

• 右外：right 【outer】（右边表中的数据优先全部显示）

  #查询人员和部门信息
  select * from person right join dept on person.did= dept.did;
  1
  2

  效果：与左外连接正好相反

• 全外：full 【outer】(显示左右表中全部数据)

  全连接查询：是在内连接的基础上增加 左右两边没有显示的数据
  　　注意: mysql并不支持全连接 full JOIN 关键字
  　　注意: 但是mysql 提供了 UNION 关键字.使用 UNION 可以间接实现 full JOIN 功能

  #查询人员和部门的所有数据
   
  SELECT * FROM person LEFT JOIN dept ON person.did = dept.did
  UNION
  SELECT * FROM person RIGHT JOIN dept ON person.did = dept.did;
  1
  2
  3
  4
  5

  union会自动将完全重复的数据去除掉

  union all会保留那些重复的数据；

  (SELECT username, age,s.classid,classname
  from student_table s LEFT OUTER JOIN class_table c
  on s.classid=c.classid)
  UNION all
  (SELECT username, age,s.classid,classname
  from student_table s RIGHT OUTER JOIN class_table c
  on s.classid=c.classid)
  1
  2
  3
  4
  5
  6
  7

交叉连接

• cross

不带WHERE条件子句，它将会返回被连接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积（例如：T_student和T_class，返回4*4=16条记录），如果带where，返回或显示的是匹配的行数。

  select *from T_student cross join T_class    //cross join 可以省略不写
  等于
   select *from T_student, T_class
1
2
3

子查询

出现在其他语句中的select语句

可以出现的位置:

• select 后面

• from 后面

• where或having 后面

• exists 后面

  都用( )括起来

exists 是否有这条数据 又返回1 没有返回0

单行单列 表示一个值

单行多列 表示显示一条单个数据

多行单列 表示多个列 找一个值

多行多列 表示一个表单

any some 随便一个 表示任一

all 全部 表示任意

联合查询

合并多个查询结果集

union

语法:

select ......
union
select ......
1
2
3

注意:

• 多条查询语句的查询列数要求一致

• 查询语句的每一列的类型和顺序最好一致

• union 默认去重 , 使用union all可以包含所有

数据类型

整数类型

提示：显示宽度和数据类型的取值范围是无关的。显示宽度只是指明 MySQL 最大可能显示的数字个数，数值的位数小于指定的宽度时会由空格填充。如果插入了大于显示宽度的值，只要该值不超过该类型整数的取值范围，数值依然可以插入，而且能够显示出来。例如，year 字段插入 19999，当使用 SELECT 查询该列值的时候，MySQL 显示的将是完整的带有 5 位数字的 19999，而不是 4 位数字的值。

关键字:unsigned

如果插入的数值超过范围,自动取临界值。

浮点型

浮点类型和定点类型都可以用(M, D)来表示，其中M称为精度，表示总共的位数；D称为标度，表示小数的位数。

浮点数类型的取值范围为 M（1～255）和 D（1～30，且不能大于 M-2），分别表示显示宽度和小数位数。M 和 D 在 FLOAT 和DOUBLE 中是可选的，FLOAT 和 DOUBLE 类型将被保存为硬件所支持的最大精度。DECIMAL 的默认 D 值为 0、M 值为 10。

日期和时间

YEAR 类型

YEAR 类型是一个单字节类型，用于表示年，在存储时只需要 1 个字节。可以使用各种格式指定 YEAR，如下所示：

• 以 4 位字符串或者 4 位数字格式表示的 YEAR，范围为 ‘1901’～’2155’。输入格式为 ‘YYYY’ 或者 YYYY，例如，输入 ‘2010’ 或 2010，插入数据库的值均为 2010。
• 以 2 位字符串格式表示的 YEAR，范围为 ‘00’ 到 ‘99’。‘00’～’69’ 和 ‘70’～’99’ 范围的值分别被转换为 2000～2069 和 1970～1999 范围的 YEAR 值。‘0’ 与 ‘00’ 的作用相同。插入超过取值范围的值将被转换为 2000。
• 以 2 位数字表示的 YEAR，范围为 1～99。1～99 和 70～99 范围的值分别被转换为 2001～2069 和 1970～1999 范围的 YEAR 值。注意，在这里 0 值将被转换为 0000，而不是 2000。

提示：两位整数范围与两位字符串范围稍有不同。例如，插入 3000 年，读者可能会使用数字格式的 0 表示 YEAR，实际上，插入数据库的值为 0000，而不是所希望的 3000。只有使用字符串格式的 ‘0’ 或 ‘00’，才可以被正确解释为 3000，非法 YEAR值将被转换为 0000。

TIME 类型

TIME 类型用于只需要时间信息的值，在存储时需要 3 个字节。格式为 HH:MM:SS。HH 表示小时，MM 表示分钟，SS 表示秒。

TIME 类型的取值范围为 -838：59：59～838：59：59，小时部分如此大的原因是 TIME 类型不仅可以用于表示一天的时间（必须小于 24 小时），还可能是某个事件过去的时间或两个事件之间的时间间隔（可大于 24 小时，或者甚至为负）。

可以使用各种格式指定 TIME 值，如下所示。

• ‘D HH：MM：SS’ 格式的字符串。还可以使用这些“非严格”的语法：‘HH：MM：SS’、‘HH：MM’、‘D HH’ 或 ‘SS’。这里的 D 表示日，可以取 0～34 之间的值。在插入数据库时，D 被转换为小时保存，格式为 “D*24+HH”。
• ‘HHMMSS’ 格式、没有间隔符的字符串或者 HHMMSS 格式的数值，假定是有意义的时间。例如，‘101112’ 被理解为’10：11：12’，但是 ‘106112’ 是不合法的（它有一个没有意义的分钟部分），在存储时将变为 00：00：00。

提示：为 TIME 列分配简写值时应注意：如果没有冒号，MySQL 解释值时，假定最右边的两位表示秒。（MySQL 解释 TIME 值为过去的时间而不是当前的时间）。例如，读者可能认为 ‘1112’ 和 1112 表示 11：12：00（即 11 点过 12 分钟），但MySQL 将它们解释为 00：11：12（即 11 分 12 秒）。同样 ‘12’ 和 12 被解释为00：00：12。相反，TIME 值中如果使用冒号则肯定被看作当天的时间，也就是说，‘11：12’ 表示 11：12：00，而不是 00：11：12。

DATE 类型

DATE 类型用于仅需要日期值时，没有时间部分，在存储时需要 3 个字节。日期格式为 ‘YYYY-MM-DD’，其中 YYYY 表示年，MM 表示月，DD 表示日。

在给 DATE 类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合 DATE 的日期格式即可。如下所示：

• 以 ‘YYYY-MM-DD’ 或者 ‘YYYYMMDD’ 字符中格式表示的日期，取值范围为 ‘1000-01-01’～’9999-12-3’。例如，输入 ‘2015-12-31’ 或者 ‘20151231’，插入数据库的日期为2015-12-31。
• 以 ‘YY-MM-DD’ 或者 ‘YYMMDD’ 字符串格式表示日期，在这里YY表示两位的年值。MySQL 解释两位年值的规则：‘00～69’ 范围的年值转换为 ‘2000_{2069’，'70}99’ 范围的年值转换为 ‘1970～1999’。例如，输入 ‘15-12-31’，插入数据库的日期为 2015-12-31；输入 ‘991231’，插入数据库的日期为 1999-12-31。
• 以 YYMMDD 数字格式表示的日期，与前面相似，00~69 范围的年值转换为 2000～2069，80～99 范围的年值转换为 1980～1999。例如，输入 151231，插入数据库的日期为 2015-12-31，输入 991231，插入数据库的日期为 1999-12-31。
• 使用 CURRENT_DATE 或者 NOW()，插入当前系统日期。

提示：MySQL 允许“不严格”语法：任何标点符号都可以用作日期部分之间的间隔符。例如，‘98-11-31’、‘98.11.31’、‘98/11/31’和’98@11@31’ 是等价的，这些值也可以正确地插入数据库。

DATETIME 类型

DATETIME 类型用于需要同时包含日期和时间信息的值，在存储时需要 8 个字节。日期格式为 ‘YYYY-MM-DD HH：MM：SS’，其中 YYYY 表示年，MM 表示月，DD 表示日，HH 表示小时，MM 表示分钟，SS 表示秒。

在给 DATETIME 类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合 DATETIME 的日期格式即可，如下所示。

• 以 ‘YYYY-MM-DD HH：MM：SS’ 或者 ‘YYYYMMDDHHMMSS’ 字符串格式表示的日期，取值范围为 ‘1000-01-01 00：00：00’～’9999-12-3 23：59：59’。例如，输入 ‘2014-12-31 05：05：05’ 或者 '20141231050505’，插入数据库的 DATETIME 值都为 2014-12-31 05：05：05。
• 以 ‘YY-MM-DD HH：MM：SS’ 或者 ‘YYMMDDHHMMSS’ 字符串格式表示的日期，在这里 YY 表示两位的年值。与前面相同，‘00～79’ 范围的年值转换为 ‘2000～2079’，‘80～99’ 范围的年值转换为 ‘1980～1999’。例如，输入 ‘14-12-31 05：05：05’，插入数据库的 DATETIME 为 2014-12-31 05：05：05；输入 141231050505，插入数据库的 DATETIME 为 2014-12-31 05：05：05。
• 以 YYYYMMDDHHMMSS 或者 YYMMDDHHMMSS 数字格式表示的日期和时间。例如，输入 20141231050505，插入数据库的 DATETIME 为 2014-12-31 05：05：05；输入 140505050505，插入数据库的 DATETIME 为 2014-12-31 05：05：05。

提示：MySQL 允许“不严格”语法：任何标点符号都可用作日期部分或时间部分之间的间隔符。例如，‘98-12-31 11：30：45’、‘98.12.31 11+30+35’、‘98/12/31 113045’ 和 ‘98@12@31 11³⁰45’ 是等价的，这些值都可以正确地插入数据库。

TIMESTAMP 类型

TIMESTAMP 的显示格式与 DATETIME 相同，显示宽度固定在 19 个字符，日期格式为 YYYY-MM-DD HH：MM：SS，在存储时需要 4 个字节。但是 TIMESTAMP 列的取值范围小于 DATETIME 的取值范围，为 '1970-01-01 00：00：01’UTC～’2038-01-19 03：14：07’UTC。在插入数据时，要保证在合法的取值范围内。

提示：协调世界时（英：Coordinated Universal Time，法：Temps Universel Coordonné）又称为世界统一时间、世界标准时间、国际协调时间。英文（CUT）和法文（TUC）的缩写不同，作为妥协，简称 UTC。

TIMESTAMP 与 DATETIME 除了存储字节和支持的范围不同外，还有一个最大的区别是：

• DATETIME 在存储日期数据时，按实际输入的格式存储，即输入什么就存储什么，与时区无关；
• 而 TIMESTAMP 值的存储是以 UTC（世界标准时间）格式保存的，存储时对当前时区进行转换，检索时再转换回当前时区。即查询时，根据当前时区的不同，显示的时间值是不同的。
• TIMESTAMP在msql 8以后做了很大改动

提示：如果为一个 DATETIME 或 TIMESTAMP 对象分配一个 DATE 值，结果值的时间部分被设置为 ‘00：00：00’，因此 DATE 值未包含时间信息。如果为一个 DATE 对象分配一个 DATETIME 或 TIMESTAMP 值，结果值的时间部分被删除，因此DATE 值未包含时间信息。

字符型

VARCHAR 和 TEXT 类型是变长类型，其存储需求取决于列值的实际长度（在前面的表格中用 L 表示），而不是取决于类型的最大可能尺寸。

例如，一个 VARCHAR(10) 列能保存一个最大长度为 10 个字符的字符串，实际的存储需要字符串的长度 L 加上一个字节以记录字符串的长度。对于字符 “abcd”，L 是 4，而存储要求 5 个字节。

TEXT 类型

TEXT 列保存非二进制字符串，如文章内容、评论等。当保存或查询 TEXT 列的值时，不删除尾部空格。

TEXT 类型分为 4 种：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。不同的 TEXT 类型的存储空间和数据长度不同。

• TINYTEXT 表示长度为 255（28-1）字符的 TEXT 列。
• TEXT 表示长度为 65535（216-1）字符的 TEXT 列。
• MEDIUMTEXT 表示长度为 16777215（224-1）字符的 TEXT 列。
• LONGTEXT 表示长度为 4294967295 或 4GB（232-1）字符的 TEXT 列。

ENUM 类型

ENUM 是一个字符串对象，值为表创建时列规定中枚举的一列值。其语法格式如下：

<字段名> ENUM( '值1', '值1', …, '值n' )
1

字段名指将要定义的字段，值 n 指枚举列表中第 n 个值。

ENUM 类型的字段在取值时，能在指定的枚举列表中获取，而且一次只能取一个。如果创建的成员中有空格，尾部的空格将自动被删除。

ENUM 值在内部用整数表示，每个枚举值均有一个索引值；列表值所允许的成员值从 1 开始编号，MySQL 存储的就是这个索引编号，枚举最多可以有 65535 个元素。

例如，定义 ENUM 类型的列（‘first’，‘second’，‘third’），该列可以取的值和每个值的索引如下表所示。

SET 类型

SET 是一个字符串的对象，可以有零或多个值，SET 列最多可以有 64 个成员，值为表创建时规定的一列值。指定包括多个 SET 成员的 SET 列值时，各成员之间用逗号,隔开，语法格式如下：

SET( '值1', '值2', …, '值n' )
1

与 ENUM 类型相同，SET 值在内部用整数表示，列表中每个值都有一个索引编号。当创建表时，SET 成员值的尾部空格将自动删除。

但与 ENUM 类型不同的是，ENUM 类型的字段只能从定义的列值中选择一个值插入，而 SET 类型的列可从定义的列值中选择多个字符的联合。

提示：如果插入 SET 字段中的列值有重复，则 MySQL 自动删除重复的值；插入 SET 字段的值的顺序并不重要，MySQL 会在存入数据库时，按照定义的顺序显示；如果插入了不正确的值，默认情况下，MySQL 将忽视这些值，给出警告。

二进制

转义字符(\)

用\转义

约束

在 MySQL 中，约束是指对表中数据的一种约束，能够帮助数据库管理员更好地管理数据库，并且能够确保数据库中数据的正确性和有效性。

例如，在数据表中存放年龄的值时，如果存入 200、300 这些无效的值就毫无意义了。因此，使用约束来限定表中的数据范围是很有必要的。

在 MySQL 中，主要支持以下 6 种约束：

1）主键约束 primary key

主键约束是使用最频繁的约束。在设计数据表时，一般情况下，都会要求表中设置一个主键。

主键是表的一个特殊字段，该字段能唯一标识该表中的每条信息。例如，学生信息表中的学号是唯一的。

一种是在字段后面写主键 另一种是表创建完成后面写上主键(把主键写在里面)

2）外键约束 foreign key

外键约束经常和主键约束一起使用，用来确保数据的一致性。

例如，一个水果摊，只有苹果、桃子、李子、西瓜 4 种水果，那么，你来到水果摊要买水果只能选择苹果、桃子、李子和西瓜，不能购买其它的水果。

外键一定是另一个表的主键

student class 1-5

8

s.classid = c.classid

用于限制两个表的关系，保证该字段的值必须来自于主表的关联列的值

3）唯一约束 unique

唯一约束与主键约束有一个相似的地方，就是它们都能够确保列的唯一性。与主键约束不同的是，唯一约束在一个表中可以有多个，并且设置唯一约束的列是允许有空值的，虽然只能有一个空值。

例如，在用户信息表中，要避免表中的用户名重名，就可以把用户名列设置为唯一约束。

4）检查约束 check(mysql不支持)

检查约束是用来检查数据表中，字段值是否有效的一个手段。

例如，学生信息表中的年龄字段是没有负数的，并且数值也是有限制的。如果是大学生，年龄一般应该在 18~30 岁之间。在设置字段的检查约束时要根据实际情况进行设置，这样能够减少无效数据的输入。

如 age int check（18,30），

5）非空约束 not null

非空约束用来约束表中的字段不能为空。例如，在学生信息表中，如果不添加学生姓名，那么这条记录是没有用的。

username varchar

前端检查数据是否合理

后端检查

数据库检查

6）默认值约束 default

默认值约束用来约束当数据表中某个字段不输入值时，自动为其添加一个已经设置好的值。

例如，在注册学生信息时，如果不输入学生的性别，那么会默认设置一个性别或者输入一个“未知”。

默认值约束通常用在已经设置了非空约束的列，这样能够防止数据表在录入数据时出现错误。

money int default 100

以上 6 种约束中，一个数据表中只能有一个主键约束，其它约束可以有多个。

理论上可以有多个，但是有多个主键，逆向工程生成，user ， primary

约束可以叠加不报错

分类

• 列级约束

  ​ 都支持,但外键约束没效果。

• 表级约束

  ​ 除了非空,默认其他都支持

官方写法

在定义字段的同时指定主键,语法格式如下:

<字段名><数据类型> primary key [默认值]
username varchar primary key default 1001 添加自增
1
2

或者是在定义完成所有字段之后指定主键,语法格式如下:

[constraint <约束名>] primary key [字段名]
1

其他约束类似

或者是在定义完成所有字段之后指定主键,这个比较正规的写法,语法格式如下:

[CONSTRAINT <约束名>] PRIMARY KEY [字段名]
[CONSTRAINT <约束名>] foreign key [字段名] references <主表名> 主键列1 [,主键列2,..]
[CONSTRAINT <约束名>] unique [字段名]

username varchar,


age int,
constraint pk primary key id,
constraint foreign key classid references class_table classid
1
2
3
4
5
6
7
8
9
10

修改表时添加约束

ALTER TABLE <数据表名> ADD PRIMARY KEY(<字段名>）；
ALTER TABLE <数据表名>ADD CONSTRAINT<外键名>
FOREIGN KEY(<列名>) REFERENCES<主表名>(<列名>)；
ALTER TABLE <数据表名> ADD CONSTRAINT<唯一约束名> UNIQUE(<列名>)；
ALTER TABLE tb_emp7 ADD CONSTRAINT<检查约束名> CHECK(<检查约束>)；
ALTER TABLE <数据表名> CHANGE COLUMN <字段名> <数据类型> DEFAULT<默认值>；
ALTER TABLE <数据表名>CHANGE COLUMN<字段名> <字段名><数据类型> NOT NULL;
1
2
3
4
5
6
7

删除约束

ALTER TABLE <数据表名> DROP PRIMARY KEY;
ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;
ALTER TABLE <表名> DROP INDEX <唯一约束名>;
ALTER TABLE <数据表名> DROP CONSTRAINT <检查约束名>;
ALTER TABLE <数据表名> CHANGE COLUMN <字段名> <字段名> <数据类型> DEFAULT NULL; 
ALTER TABLE <数据表名> CHANGE COLUMN <字段名> <字段名> <数据类型> NULL; 
1
2
3
4
5
6

查看表中约束:

SHOW CREATE TABLE<数据表名>;
1

事务简称(ACID)

transaction是一种机制,若干条数据库操作指令构成一个操作序列,该序列要么全部执行,要么全部不执行,事务时一个不可分割的逻辑单元。

事务具有四个特性,简称为acid

原子性（atomicity）

事务时一个完整的操作。事务的各元素是不可分的（原子的）。事务中的所有元素必须作为一个整体提交或者回滚。如果事务中的任何元素失败，则整个事务将失败

一致性（consistency）

当事务完成时，数据必须处于一致状态。也就是说，在事务开始之前,数据库中存储的数据处于一致状态。在正在进行的事务中，数据可能处于不一致的状态，如数据可能有部分被修改。然后，当事务成功完成时，数据必须再次回到已知的一致状态。通过事务对数据所做的修改不能损坏数据，或者说事务不能是数据存储于不稳定的状态。

隔离性（isolation）

对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。修改数据的事务可以在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据。

持久性（durability）

事务的持久性指不管系统是否发生了故障，事务处理的结果都是永久的。一个事务成功完成之后，他对数据库所作的改变是永久性的，即使系统出现故障也是如此。也就是说，一旦事务被提交，事务对数据所做的任何变动都会被永久地保留在数据库中。

执行流程

# 开始事务
begin;	或	start transaction;
#提交事务
commit;
#回滚
rollback;
1
2
3
4
5
6

注意:

1. 事务尽可能简短,事务的开启到结束会在数据库管理系统中保留大量资源,影响软件的性能
2. 事务中访问的数据量尽量最少
3. 查询数据时尽量不要使用事务,避免占用过多资源
4. 事务处理过程中尽量不要出现等待用户输入的操作

设置自动提交事务

通过命令查看事务当前状态

show variables like 'autocommit';
1

设置自动提交

set autocommit = 0|1|on|off;
1

0或off为关闭自动提交,即遇到commit或者rollback才会结束一个事务

1或on表示开启自动提交,每一条sql语句即为一个事务

默认都是开启，自动提交事务

JDBC事物

• 通过connection打开事务和提交
• 同一个connection

//开始事务
.setAutocommit(false);
//然后是执行代码
//提交事务
.commit();
//回滚
.rollback();
1
2
3
4
5
6
7

public class Test {
	static Connection connection=null;
	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			connection=(Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/xsh?useUnicode=true&characterEncoding=UTF-8", "root", "root");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static void update(int money,String name) throws SQLException {
		String sql="update test_table set money=? where username=?";
		PreparedStatement statement=(PreparedStatement) connection.prepareStatement(sql);
		statement.setInt(1,money);
		statement.setString(2,name);
		statement.executeUpdate();
	}
	
	public static int select(String name) throws SQLException {
		String sql="select * from test_table where username=?";
		PreparedStatement statement=(PreparedStatement) connection.prepareStatement(sql);
		statement.setString(1,name);
		ResultSet set=statement.executeQuery();
		int money=0;
		while (set.next()) {
			money=set.getInt("money");
		}
		return money;
	}
	//服务层
	public static void nametest(int m,String from,String to) throws SQLException {
		/*
		 * ccc向dddk转账500
		 * ccc-500
		 * ddd+500
		 */
		connection.setAutoCommit(false);
		try {
			update(select("ccc")-m, from);
			int a=5/0;
			update(select("ddd")+m, to);
			connection.commit();
		} catch (Exception e) {
			// TODO: handle exception
			connection.rollback();
		}
	}
	public static void main(String[] args) throws SQLException {
		//controller service dao
		nametest(500, "ccc", "ddd");
	}
}
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
46
47
48
49
50
51
52
53
54
55
56
57

隔离级别

如果没有隔离级别,并发访问会产生一些问题。

并发：两件事在同一个时间段内执行 举例：十点钟，妈妈出门，十二点回来，写完作业并且做好饭

并行：两件事同时刻执行 举例：边吃饭边洗脚

• 脏读 dirty read :读取到另一个事务未提交的更新数据,也就是脏数据

• 不可重复读 unrepeatable read ：对同一记录的两次读取不一致，因为另一事物对记录做了修改

• 幻读（虚度）phantom read：对同一张表的两次查询不一致，因为另一事物插入了一条记录

  不可重复读和幻读的区别：

  不可重复读是读取到了另外一个事务的更新

  幻读是读取到了另外一个事务的插入

一个事务和其他事务的隔离程度称为隔离级别,级别越高,数据一致性越好,但并发性越弱。

四大隔离级别

• serializable 串行化
  • 不会出现任何并发问题，因为对数据库的访问是串行，非并访问
  • 性能最差
• repeatable read 可重复读（mysql默认）
  • 防止脏读和不可重复读
  • 性能比上一个好
• read committed 读已提交数据（Oracle默认）
  • 防止脏读
  • 性能比上一个好
• read uncommitted 读未提交数据
  • 可能出现任何事物并发问题
  • 性能最好

四大隔离级别

查看隔离级别

select @@tx_isolation
1

或

show variables like '%tx_isolation%'
1

设置隔离级别

set [session | global] transaction isolation level [read uncommitted | read committed | repeatable read | serializable]
1

session和global区别

global是全局意思是把我整个配置全部都改掉而session是当前会话意思是当前的改掉

数据连接池

池化技术

常见数据库连接池

dbcp c3p0 druid

c3p0的使用

添加依赖

<dependency>
<groupId>com.mchange</groupId><artifactId>c3p0</artifactId><version>0.9.5.5</version>
</dependency>
1
2
3

//获得连接池对象
		ComboPooledDataSource dataSource=new ComboPooledDataSource();
		//配置四大参数
		dataSource.setDriverClass ("com.mysql.jdbc.Driver");
		dataSource.setJdbcUrl("jdbc:mysq1://1oca1host:3306/xsh");
		dataSource.setUser("root");
		dataSource.setPassword("root");
		//配置基本池参数
		dataSource.setAcquireIncrement(5);//链接用完了自动增量几个
		dataSource.setInitialPoolSize(20);
		dataSource.setMinPoolSize(2);
		dataSource.setMaxPoolSize(50);
		//获得链接
		Connection con=(Connection) dataSource.getConnection();
		System.out.println(con);


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

也可以使用xml或者properties来做基本配置。注意xml文件必须为c3p0-config.xml,且放在ser下

<?xm1 version="1.o" encoding="UTF-8"?>
<c3p0-config>
	<defau1t-config>
		<property name="jdbcurl">jdbc:mysql://localhost:3306h</property>
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="user">root</property>
		<property name="password">root</property>
		<property name="acquireIncrement">3</property>
		<property name="initialPoolSize">10</property>
		<property name="minPoolSize">2</property>
		<property name="maxPoolSize">10</property>
	</defau1t-config>
	<named-config name="oracle-config">
		<property name="jdbcurl">jdbc:mysql://localhost:3306h</property>
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="user">root</property>
		<property name="password">root</property>
		<property name="acquireIncrement">3</property>
		<property name="initialPoolSize">10</property>
		<property name="minPoolSize">2</property>
		<property name="maxPoolSize">10</property>
	</named-config>
</c3p0-config>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

代码写法不用变,会自动加载这个配置文件。默认走的就是default的配置，如需更换数据库，以字符串形式写上对应配置的名字传入构造即可

mysql存储过程

存储过程介绍

类似于java中的方法

含义:一组预先编译好的sql语句的集合,理解成批量处理语句

好处:

• 提高代码的重要性
• 简化操作
• 减少了编译次数并且减少了和数据库服务器的连接次数,提高了效率

创建语法

1.创建语法

CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体>
[过程参数[,…] ] 格式
[ IN | OUT | INOUT ] <参数名> <类型>

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
   存储过程体简称方法(一组合法的sql语句)
END
1
2
3
4
5
6
7
8

注意:

1.参数列表包含三部分

参数模式 参数名 参数类型

举例:

IN stunme varchar(20)

参数模式:

in:该参数可以作为输入,也就是说该参数需要调用方传入值

out:该参数可以作为输出,也就是说该参数可以作为返回值

inout:该参数可以作为输入又可以作为输出,也就是该参数既需要传入值,又可以返回值

2.如果存储体仅仅只有一句话,BEGIN END 可以省略

存储体过程中的每条生气了语句的结尾要求必须加分号

存储过程的结尾可以使用deImITER 重新设置

语法:

deImITER 结束标记

deImITER $

2.调用语法

CALL 存储过程名 (参数列表);

删除存储过程

语法:drop PROCEDURE 存储过程名

注意:每次删除只能删除一个

查看存储信息

show create PROCEDURE 存储名

变量是表达式语句中最基本的元素，可以用来临时存储数据。在存储过程和函数中都可以定义和使用变量。用户可以使用 DECLARE 关键字来定义变量，定义后可以为变量赋值。这些变量的作用范围是 BEGIN…END 程序段中。

下面将讲解如何定义变量和为变量赋值。

1. 定义变量

MySQL 中可以使用 DECLARE 关键字来定义变量，其基本语法如下：

DECLARE var_name[,…] type [DEFAULT value]

其中：

• DECLARE 关键字是用来声明变量的；
• var_name 参数是变量的名称，这里可以同时定义多个变量；
• type 参数用来指定变量的类型；
• DEFAULT value 子句将变量默认值设置为 value，没有使用 DEFAULT 子句时，默认值为 NULL。

例 1

下面定义变量 my_sql，数据类型为 INT 类型，默认值为 10。SQL 语句如下：

DECLARE my_sql INT DEFAULT 10;

MySQL 中可以使用 SET 关键字来为变量赋值，SET 语句的基本语法如下：

SET var_name = expr[,var_name = expr]…

其中：

• SET 关键字用来为变量赋值；
• var_name 参数是变量的名称；
• expr 参数是赋值表达式。

注意：一个 SET 语句可以同时为多个变量赋值，各个变量的赋值语句之间用逗号隔开。

例 2

下面为变量 my_sql 赋值为 30。SQL 语句如下：

SET my_sql=30;

MySQL 中还可以使用 SELECT…INTO 语句为变量赋值。其基本语法如下：

SELECT col_name […] INTO var_name[,…]
FROM table_name WEHRE condition

其中：

• col_name 参数表示查询的字段名称；
• var_name 参数是变量的名称；
• table_name 参数指表的名称；
• condition 参数指查询条件。

注意：当将查询结果赋值给变量时，该查询语句的返回结果只能是单行。

例 3

下面从 tb_student 表中查询 id 为 2 的记录，将该记录的 id 值赋给变量 my_sql。SQL 语句如下：

SELECT id INTO my_sql FROM tb_student WEHRE id=2；