MySql的入门到入坟

系列文章目录

本章讲述Mysql的入门和使用。

---

---

前言

MySQl的优点：
1. 开源/免费/成本低
2. 性能高/移植性也好
3. 体积小/便于安装

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、数据库设计分析

1.　软件开发周期

• 需求分析阶段：分析客户的业务和数据处理需求;
• 要设计阶段：设计数据库的E-R模型图(概念模型)，确认需求信息的正确和完整;
• 详细设计阶段：将E-R图转换为多张表(物理模型)，进行逻辑设计，并应用数据库设计的三大范式进行审核;
• 代码编写阶段：选择具体数据库进行物理实现，并编写代码实现前端应用;
• 软件测试阶段：…
• 安装部署：…

2. 设计数据库的步骤

示例：Blog系统

基本功能：注册账号；发表和编辑文章；多用户支持；

• Step1：收集信息
  与该系统有关人员进行交流、坐谈，充分理解数据库需要完成的任务
• Step2：标识实体（Entity）
  标识数据库要管理的关键实体
  实体一般是名词：
  博主帐号：发表和管理文章、图片管理。
  文章类别
  文章
  文章回复
  照片分类
  照片
  短消息
• Step3：标识每个实体的属性（Attribute

• Step4：标识实体之间的关系（Relationship）
  关系一般是动词
  文章类别和帐号有主从关系：需要在文章类别对象中表明它是谁创建的；
  文章和文章类别有主从关系：需要表明文章对象是属于哪个类别的；
  文章回复和文章有主从关系：需要表明回复是属于哪个文章的；
  照片类别和帐号有主从关系：需要表明照片类别是哪个帐号创建的；
  照片和照片类别有主从关系：需要表明照片对象是属性哪个类别的；
  链接类别和帐号有主从关系：需要表明链接类别是哪个帐号创建的；
  链接和链接类别有主从关系：需要表明链接对象是属性哪个类别的；
  短消息类别和帐号有主从关系：一个短消息对象是发给哪个帐号的。

3. 绘制Ｅ－Ｒ图

名词含义：

示例：

4. 数据库设计范式

第一范式
如果每列都是不可再分的最小数据单元，则满足第一范式。
第一范式的目标是确保每列的原子性

第二范式
如果一个关系满足1NF，并且除了主键以外的其他列，都依赖于该主键，则满足第二范式。
第二范式要求每个表只描述一件事情

第三范式
如果一个关系满足2NF，并且非主键列都不传递依赖于主键列，则满足第三范式（3NF）
第三范式要求一个表中不能包含在其它表中已定义的非主键列

设计实践
三范式只作为数据库设计时的参考，实践中可能会违反它。
数据库设计时要考虑，表之间的关联查询越少越好。

二、MySql的入门和使用

1. 什么是MySql?

“MySQL是最流行的开放源码SQL数据库管理系统”。

发展史

• 最早起始于1979年，是Michael Widenius为瑞典TcX公司创建的UNIREG数据库工具
• 1996年5月 MySQL 1.0
• 1996年10月 MySQL 3.11.1 以用于Linux和Solaris系统的二进制分发形式发布
• 2008年1月16日 Sun收购了MySQL（10亿美元）
• 2009年4月20日 Oracle收购了Sun(74亿美元)
• 著名的MySQL用户
  Google、YouTube、雅虎财经网站的数据库、维基百科、腾讯QQ、朗讯、北电、美洲银行、诺基亚、阿尔卡特、西门子、思科、摩托罗拉、UPS、美国国防部、美国洛克希德-马丁公司、德国邮政、道琼斯、迪斯尼、戴姆勒-克莱斯勒 …

2. 启动和停止MySQL服务器服务

• 图形界面操作
  Windows命令：net start mysql、net stop mysql
• 从命令行启动服务器
  启动：mysqld -nt --console
  停止：mysqladmin -u root -p shutdown
• 卸载(MySQL 5.x)
  卸载程序：MySQL Server Instance Config Wizard
• 删除数据文件
  C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.1/Data/

3. MySql的基本使用

MySQL命令行客户端

• 连接服务器：
  本地登录可省略-h参数
  MySQL的超级管理员用户名是root

mysql [-h服务器地址] -u用户名 -p密码

• 断开：
  quit (或\q)、exit

常用数据库管理命令

• 列出当前服务器主机上的数据库

SHOW DATABASES [LIKE’pattern’];

• 把指定数据库作为默认(当前)数据库

USE db_name;

• 列出数据库中的对象

SHOW [FULL] TABLES [FROM db_name] [LIKE’pattern’];

• 获取指定表的列信息

{DESCRIBE | DESC} tbl_name

• MySQL中的SQL语句用\G结束，会按行垂直显示结果

4. DDL之库、表管理

SQL简介

SQL(Structured Query Language)结构化查询语言，在关系数据库上执行数据操作、数据检索以及数据维护的标准语言

官方发音：直接念三个字母。或简略的读[`si:kju:]（CQ）。

• SQL标准（ANSI/ISO）：
  SQL86、SQL92、SQL1999、SQL2003、SQL200N
• SQL语法：
  1. 关键字不区分大小写。
  2. 表名和列名不区分大小写。
  3. 语句以分号;结束
  4. 注释使用–

SQL分类

1. DDL—数据定义语言：定义对数据库对象的操作。
2. CREATE、ALTER、DROP、RENAME、TRUNCATE等。
3. DML—数据操纵语言：定义修改表的内容的操作。
   • SELECT查询语句：检索数据库表中存储的行。
   • INSERT、DELETE、UPDATE等
4. DCL—数据控制语言：定义修改数据库结构的操作权限。
   GRANT、REVOKE等。
5. TCL—事务控制语言：用于将对行所做的修改永久性地存储到表中，或取消这些修改操作。
   COMMIT、ROLLBACK、SAVEPOINT等

库管理

创建一个 数据库的语法

CREATE DATABASE [IF NOT EXISTS] db_name [DEFAULT CHARACTER SET charset_name];

删除指定库

DROP DATABASE [IF EXISTS] db_name;

表管理

建表语法

CREATE TABLE [IF NOT EXISTS] 表名(
	列名 列类型 [AUTO_INCREMENT] [default 默认值] [列约束],
	列名 列类型 [default 默认值] [列约束] [comment '描述信息'],  
	[表级约束]
	) [ENGINE=InnoDB] [DEFAULT CHARSET=UTF8];

查看某个表的建表语句
SHOW CREATE TABLE表名 \G
删除表
DROP TABLE [IF EXISTS] 表名;

列类型

MySQL支持多种列类型：

• 数值类型：M表示最大显示宽度， D表示小数点后面的位数
  • INTEGER[(M)] (INT)、 BIGINT[(M)]
  • DECIMAL(M[,D]) à 默认DECIMAL(10,0) 、FLOAT[(M,D)] 、DOUBLE[(M,D)]
    整数最大位数为65。小数最大位数(D)为30。内部以字符串形式存放，适合于表示货币等精度高的数据
• 日期/时间类型
  • DATETIME ：‘1000-01-01 00:00:00’到’9999-12-31 23:59:59’
  • TIMESTAMP： ‘1970-01-01 00:00:00’到’2038-01-09 03:14:07’
• 字符串(字符)类型
  • CHAR(M) ：最多256个字符 -> 定长
  • VARCHAR(M) ：最多65,536个字符 -> 变长
• 大对象
  • TEXT：最多65,535字符（LONGTEXT 最大4GB）
  • BLOB：最多65,535字节（LONGBLOB 最大4GB）

自增列

AUTO_INCREMENT：自动增长

AUTO_INCREMENT的列类型只能是整数类型
一个表只能有一个AUTO_INCREMENT列，且该列必须为主键的一部分。

• SELECT @@IDENTITY：获取最近一次向具有identity属性(即自增列)的表插入数据时对应的自增列的值。

@@IDENTITY是系统定义的全局变量

约束

类型：

1. 非空：NOT NULL
2. 唯一：UNIQUE
3. 主键：PRIMARY KEY（效果就是：非空+唯一）
4. 外键：FOREIGN KEY
5. 检查约束： CHECK (注意：目前MySQL的CHECK约束无效)

使用单独的语句添加外键约束

ALTER TABLE 表名    
	ADD CONSTRAINT 外键约束名    
	FOREIGN KEY (列名)    
	REFERENCES 主表名(列名) ;

删除外键约束

ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名;

建表示例：

• 运动员sporter（运动员编号 sid，姓名 name，性别 gender，入学时间join_time，所属系名department)
• 比赛项目item（项目编号 iid，项目名称 itemname，项目比赛地点 location）
• 成绩grade（运动员编号 sid，项目编号 iid，积分 mark）

MySQL客户端字符集设置

当客户端与服务器交互的过程中，为了保证数据不乱码，还需要设置连接字符集。

显示当前客户端的连接字符集：
SHOW VARIABLES LIKE 'character_set_client';

设置的两种方式：

1. 用SQL命令
   SET NAMES 字符集编码名;
   每次连接数据库都得执行一次。
2. 在my.ini中设置 (Linux是my.cnf)：
   [mysql]
   default-character-set=gbk

修改表结构

增加列
ALTER TABLE 表名 ADD 列名 列类型 [FIRST|AFTER 列名];
删除列
ALTER TABLE 表名 DROP 列名
修改列类型
ALTER TABLE 表名 MODIFY 列名 列类型 [FIRST|AFTER 列名];
修改列名
ALTER TABLE 表名 CHANGE 旧列名 新列名 列类型 [FIRST|AFTER 列名];
更改表名
RENAME TABLE 表名 TO 新表名;
ALTER TABLE 表名 RENAME 新表名;

5. DML 之数据表的CRUD操作

查询语句

语法：

SELECT [DISTINCT] {* | 列名 [as] 别名, 列名}
	FROM 表名 别名
	WHERE 条件 GROUP BY 分组列
	HAVING组过滤条件
	ORDER BY排序列 [ASC|DESC]
	LIMIT [offset_start,] row_count;

Scott中的表

常用的查询语句

查询指定表的所有列数据：
SELECT * FROM 表名;
查询指定表的指定列数据：
SELECT 列名, 列名2... FROM 表名;
为查询的列取别名：
SELECT 列名 [AS] 别名, ... FROM 表名;

使用DISTINCT消除重复内容

示例
员工表：

• 查询员工表的所有数据。
• 查询员工表中的员工编号、员工姓名、薪水。
• 查询员工表中的员工姓名和年薪。
• 查询员工表中的员工姓名、年薪和总奖金。列标题为“姓名”，“年薪”，“总奖金”。
• 查询员工表中的所有职位，不包括重复记录

查询条件(WHERE 子句)

条件过滤语句：

SELECT [DISTINCT] * | [列名 [别名],...]
FROM 表名 
WHERE 条件;

WHERE子句中的条件表达式：

• 可以包括运算符(算术、比较、逻辑)
• 可以使用()。
• 可以使用常量、列名、函数。

常用运算符

• 算术运算符：+、-、*、/
• 比较运算符：
  =、!=（或<>）、<、>、<=、>=、ANY、ALL
  其中ANY和ALL使用于多表查询
  例如：SELECT * FROM emp WHERE ename = ANY(SELECT ename FORM emp WHERE job =‘CLERK’);
• 逻辑运算符：AND、OR、NOT
• SQL 运算符的优先级从高到低的顺序是：算术、比较、逻辑（NOT、AND、OR

SQL操作符

SQL操作符可以对字符串、值列表、值范围以及空值进行模式匹配，来限定查询返回的行：

• IN (值1,值2…)：匹配值列表
• LIKE ‘模式字符串’：匹配指定的字符串模式。下划线字符（_）匹配一个字符。百分号字符（%）匹配任意个字符。可以使用在任何数据类型中
• BETWEEN 值1 AND 值2：匹配值范围。包括边界可用于数值类型和日期时间类型。
• IS NULL匹配空值。理解空值(null value)：未知的值。并不是一个空字符串。
• 注意：这些SQL操作符都可以和NOT组合使用：NOT IN(…)、NOT LIKE …、NOT BETWEENT. AND .、IS NOT NULL

示例：
1.查询出员工表中年薪大于10000的员工的信息。
2.查询出员工表中月薪不等于3000的员工的信息。
3.查询出员工表中没有奖金的员工的信息。
4.查询出员工表中月薪大于1500且还有奖金的员工的信息。
5.查询出员工表中月薪不大于1500或没有奖金的员工的信息。
6.查询出员工表中月薪在1500到3000之间的员工的信息。
7.查询出员工表中在1981年雇佣的员工的信息。
8.查询出员工表中名字为’SCOTT’的员工的信息。
9.查询出员工表中名字分别为’BLAKE’、‘KING’、'FORD’的员工的信息
10.查询出员工表中月薪为3000、5000的员工的信息。
11.查询出员工表中月薪不是3000和5000的员工的信息。
12.查询出员工表中名字以’A’开头的员工的信息。
13.查询出员工表中名字第三个字母为’O’的员工的信息。
14.查询出员工表中名字中含有’J’的员工的信息。

对结果进行排序

排序语句

SELECT [DISTINCT] * | [列名 [别名],...]
	FROM 表名 
	WHERE 条件
	ORDER BY 排序的列  [ASC | DESC],...;

ASC：升序（默认值）；
DESC：降序

示例：

• 查询员工的信息，按编号降序排列。
• 查询员工的信息，按月薪降序排列，如果月薪相同，则按雇佣日期降序排列。
• 查询员工的姓名、年薪，按年薪升序排列。
• 查询出部门编号为30的员工信息，按编号降序排列。

常用组函数

• 组函数对一组值执行计算，并返回单个值

函数名	说明
COUNT(列名)	行计数
AVG(数值类型列名)	平均值
SUM(数值类型列名)	求和
MAX(列名)	最大值
MIN(列名)	最小值

示例：
select count(*) from emp;

• 组函数都会忽略空值。
• 组函数不能和非分组的列混合使用

示例：

• 求出员工总人数。
• 求出公司每月要支付的月薪总数。
• 求出最高月薪、最低月薪。
• 求出公司的平均月薪

分组统计

语法：

SELECT [DISTINCT] *|[列名别名,...]
	FROM 表名
	[WHERE 条件]
	[GROUP BY 分组条件]
	[ORDERBY列名 [ASC]|[DESC], ...];

注意：

• 如果查询中包含一个组函数，而所选择的列不在组函数中，那么这些列就必须出现在GROUP BY子句中。
• 组函数不能出现在WHERE子句中。

示例：
员工表：

• 显示每个部门的员工数量。
• 显示每种职位的名及平均月薪。
• 显示部门平均月薪大于2000的部门编号及平均月薪。（？？？）

使用HAVING子句过滤组

语法：

SELECT [DISTINCT] *|[列名别名,...]
	FROM 表名 
	[WHERE 条件]
	[GROUP BY 分组条件 
	[HAVING 组过滤条件]]
[ORDER BY 列名 [ASC]|[DESC], ...];

HAVING子句必须出现在GROUP BY语句之中

示例：显示出平均月薪大于2000的部门编号及平均月薪

练习：查询出不是总裁（PRESIDENT）的职位名以及该职位的员工月薪总和，还要满足同职位的员工的月薪总和大于4500。输出结果按月薪的总和降序排列

分页查询（MySQL特有）

语法：

SELECT [DISTINCT] {* | 列名 [as] 别名, 列名}
	FROM 表名 别名
	WHERE 条件
	GROUP BY 分组列
	HAVING 组过滤条件
	ORDER BY 排序列 [ASC|DESC]
	LIMIT [offset_start,] row_count;

• offset_start：第一个要返回的记录行的偏移量。默认为0。
• row_count：要返回记录行的最大数目。

示例：

• 取出员工表中的前5条记录。
• 取出员工表中的第6到第10条记录

字符串常用函数

函数	功能
CONCAT(str1,str2,…)	连接字符串
INSERT(str,pos,len,newstr)	字符串str从第pos位置开始的len个字符替换为新串newstr
LOWER(str)	转成小写
UPPER(str)	转成大写
LENGTH(str)	返回字符串str中所有字符的字节的长度
CHAR_LENGTH(str)	返回字符串str的长度
LPAD(str,len,padstr)	返回字符串str, 其左边由字符串padstr填补到len（str+padstr**的长度）字符长度
RPAD(str,len,padstr)	返回字符串str, 其右边由字符串padstr填补到len字符长度
TRIM(str)	去掉字符串str前缀和后缀的空格
REPEAT(str,count)	返回str重复count次的结果
REPLACE(str,from_str,to_str)	用字符串to_str替换字符串str中所有的字符串from_str
SUBSTRING(str,pos,len)	返回从字符串str的pos位置起len个字符长度的子串

数值常用函数

函数	功能
ABS(X)	返回X的绝对值。
CEIL(X)	返回不小于X的最小整数值
FLOOR(X)	返回不大于X的最大整数值
MOD(X,Y)	返回x/y的模
RAND()	返回一个0~1之间的随机浮点值v（0 ≤ v ≤ 1.0）
ROUND(X,Y)	返回参数X的四舍五入的有Y位小数的值
TRUNCATE(X,Y)	返回数字X截断为y位小数的结果

日期时间常用函数

函数	功能
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前的日期和时间
WEEK(date)	返回指定日期为一年中的第几周
YEAR(date)	返回日期的年份
HOUR(time)	返回time的小时值
MINUTE(time)	返回time的分钟值
MONTHNAME(time)	返回date的月份名
DATE_FORMAT(date,fmt**)**	返回将日期按fmt格式化后的字符串,fmt格式语法参考MySQL的API文档
STR_TO_DATE(str,fmt)	返回将符合fmt格式的字符串转换成的日期
DATE_ADD(date, INTERVALexp type)	返回一个日期或时间值加上一个时间间隔的时间值
DATEDIFF(expr,expr2)	返回起始时间expr和结束时间exrp2之间的天数
timestampdiff(unit,begin,end)	返回一个单位为unit的从开始时间到结束时间的数值

流程函数

函数	功能
IF(expr1,expr2,expr3)	如果expr1是TRUE (expr1<> 0 and expr1 <>NULL)，则 IF()的返回值为expr2; 否则返回值则为expr3。
IFNULL(expr1,expr2)	假如expr1不为 NULL，则 IFNULL() 的返回值为expr1; 否则其返回值为expr2。
CASE WHEN [value] THEN result ELSE default END	如果value是真，返回result，否则返回default
CASE [expr] WHEN [value1] THEN result1 [WHEN [value2] THEN result2] [ELSE default] END	如果expr等于value1，返回result1，如果等于value2，返回result2，否则返回default

其他常用函数

函数	功能
DATABASE()	返回当前数据库名
VERSION()	返回当前数据库版本
USER()	返回当前登录用户名
INET_ATON(ip)	返回IP地址的数字表示
INET_NTOA(ip)	返回数字代表的IP地址
PASSWORD(str)	返回字符串str的加密版本,加密是单向的 (不可逆)。适用于MySQL数据库的用户密码加密
MD5(str)	返回字符串str的MD5值,该值以32位十六进制数字的二进制字符串的形式返回

6. 多表查询

多表查询：使用单个select语句从多个表中取出相关的数据, 也叫多表联合查询。

多表查询通常是建立在存在相互关系的主从(父子)表上的

SQL99标准：多表连接的语法:

SELECT [DISTINCT] *|[列名别名,...]
	FROM 主表名 [别名]
	JOIN_TYPE 从表名 [别名] ON 连接条件
	WHERE 查询条件
	GROUPBY 分组表达式
	HAVING 组过滤条件
	ORDERY BY 排序表达式;

其中JOIN_TYPE代表连接的类型：有内连接、外连接、交叉连接

内连接

语法：

SELECT [DISTINCT] *|[列名 别名,...]
	 FROM 主表名 别名
	 [INNER] JOIN 从表名 别名 **ON** **连接条件**
	 [WHERE 条件];

用处：

• 列出所有连接表中与连接条件相匹配的数据行。
• 使用表别名，可以简化语句。
  
  

内连接分类：

• 等值连接：在连接条件中使用等号(=)运算符来比较被连接列的列值
• 连接条件的列名相同时，可以使用USING(列名)来简化。
• 非等值连接：在连接条件中使用除等号运算符以外的其它比较运算符来比较被连接的列的列值。
  !=、>、>=、<、<=、LIKE、BETWEEN AND

练习：

• 查询出员工的姓名及其所在的部门名。
• 查询出月薪大于2000的员工姓名、月薪、受雇日期及其所在的部门名，输出结果按受雇日期排序。
• 查询出每个员工的姓名、月薪以及月薪等级。
• 查询出每个员工的姓名、职位、月薪、部门名称、部门位置、以及月薪的等级，结果按员工编号排序。

外连接

语法：

SELECT [DISTINCT] *|[列名 别名,...] 
	FROM 主表名 别名
	{LEFT|RIGHT|FULL} [OUTER] JOIN 从表名 别名 ON 连接条件
	[WHERE 条件];

• 不仅列出所有连接表中与连接条件相匹配的数据行，还列出左表(左外连接)、右表(右外连接)或两个表(全外连接)中符合WHERE过滤条件的数据行。
• 注意：MySQL还不支持全外连接。

联系：

• 查询出所有的部门编号、部门名及该部门下的所有员工的姓名。
• 查询出每个员工的编号、姓名、职位及它的领导的姓名及职位
• 自连接：参与连接的表都是同一张表。(通过给表取别名虚拟出)

交叉连接

语法 ：
SELECT ... FROM join_table CROSS JOIN join_table2;

• 没有ON子句和WHERE子句,它返回所有连接表中所有数据行的笛卡尔积。
• 其结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的
  数据行数

示例
SELECT * FROM emp CROSS JOIN dept;


多表 查询练习

查询出每个员工的姓名、职位、月薪、所属部门名、月薪等级及其领导的姓名、职位、所属部门名、
月薪等级

7. 子查询

• 单行单列子查询:
  单行比较操作符:= 、!= 、>、>=、<、<=
• 多行单列子查询:
  多行比较操作符:IN、NOT IN
  IN:检查指定值是否包含在一个值列表中。
• 多列子查询:子查询的结果中也可以返回多列。
  可以把子查询返回的结果当作一张新表的数据来使用。

练习：

1. 查询出月薪高于公司平均月薪的员工姓名、职位、月薪。
2. 查询出每个部门月薪最低的员工信息。
3. 查询出每个部门的编号、名称、员工数量、平均月薪。
4. 查询出每个月薪级别中最高月薪的员工信息

8. 集合操作符

集合操作符:合并多个查询结果

• UNION ALL:将多个查询结果合并到一个结果,有重复行
• UNION:将多个查询结果合并到一个结果中,没有重复行(并集)

不支持 INTERSECT (交集) 和 MINUS (差集)

练习 ：
•先创建一张表,表中只包含部门编号为30的员工的信息:
•CREATE TABLE emp30 AS SELECT * FROM emp WHERE deptno=30;
•SELECT * FROM emp UNION SELECT * FROM emp30;
•SELECT * FROM emp UNION ALL SELECT * FROM emp30;

9. 其他 DML语句

• 增:INSERT INTO 表名[(列名1, …列名n)] VALUES (值1, …值n);
  MySQL一次性插入多条记录
  INSERT INTO 表名[(列名1, ..列名n)] VALUES(值1, ..值n), (值1, ..值n);
• 改
  UPDATE 表名 SET 列名1=值1, ...列名n=值n [WHERE 条件];
• 删
  DELETE FROM 表名 [WHERE 条件];
• 截断表
  TRUNCATE [TABLE] tbl_name;

INSERT 插入数据

语法 :
INSERT INTO 表名[(列名1,列名2,...)] VALUES(值1, 值2,...);


指定列值时:
•字符串类型:加单引号。
•数字类型:直接使用字面值。
•日期时间类型:可以使用’yyyy-MM-dd HH:mm:ss’字符串格式;也可用now()获取当前时间。
•空值:用NULL关键字代替,或不列出该列名


示例 ：

• 标准语法 ，强烈推荐

INSERT INTO emp30(empno,ename,job,mgr,hiredate,sal,comm,deptno)
VALUES(8000,'张三','讲师', 7788, '12-11月-88', 7000,800,40);

• 简便做法
  INSERT INTO tomo_emp VALUES(8001,'李四','助教', 7788, '12-1月-89', 5000,400,40);

UPDATE 修改数据

语法：
UPDATE 表名 SET 列名=值[,列名2=值2,...] [WHERE条件];

没有指定修改条件,将修改表中的所有记录

示例 ：

• 把编号为8110的员工职位改成“testing”。
• 为部门编号为40的员工加薪8%。

DELETE删除数据

语法：
DELETE FROM 表名 [WHERE删除条件];

注意：不指定删除条件，会删除所有的行。

练习：

• 删除编号为8110的员工。
• 删除部门编号为40,50的所有员工。
• 如果要删除表的所有数据，建议TRUNCATE [TABLE] tbl_name;，它会重置表空间。

10. TCL(事务)

事务

数据库事务：一组相关的SQL语句，这组SQL语句对数据的改变要么同时成功，要么同时失败。

事务的ACID属性：

• 原子性(Atomic)：
  指整个数据库事务是不可分割的工作单元。原子性确保在事务中的所有操作要么都发生，要么都不发生。
• 一致性(Consistency)：
  一旦一个事务结束了(不管成功与否)，系统所处的状态和它的业务规则是一致的。即数据应当不会被破坏。
• 隔离性(Isolation)：
  指多个事务同时操作同一数据时，每个事务都有各自的完整数据空间。
• 持久性(Durability)：
  一旦事务完成，事务的结果应该持久化

MySQL中的事务控制

MySQL中，只有InnoDB支持事务，默认事务是自动提交的。
show variables like ‘autocommit’;


事务操作：

• SET AUTOCOMMIT=1 (默认) #自动提交事务
• SET AUTOCOMMIT=0 #手动提交事务
• START TRANSACTION #启动新事务(MySQL特有)
• COMMIT #提交事务
• ROLLBACK #回滚事务
• SAVEPOINT 回滚点 #设置回滚点

事务隔离级别

SQL标准定义了四种隔离级别：

• READ UNCOMMITTED：读未提交数据。脏读、不可重复读、幻读都可能发生。它的事务隔离性最低。
• READ COMMITTED：读已提交数据。不允许脏读。
• REPEATABLE READ：可重复读。不允许不可重复读，脏读。
• SERIALIZABLE：串行化。不允许任何并发事务问题。最严格的事务隔离性。

查看事务

• SELECT @@global.tx_isolation; – 系统级
• SELECT @@tx_isolation; – 会话级

设置事务

• set global transaction isolation level read committed;
• set session transaction isolation level read committed;

11. 索引，视图

索引管理

所有MySQL列类型都可以被索引，对相关列使用索引是提高SELECT操作性能的最佳途径。

MySQL提供多种索引类型供选择：都是以B-树的形式保存

• 普通索引
• 唯一性索引
• 主键索引
• 全文索引

创建索引

• 普通索引
  语法：
  CREATE INDEX 索引名ON表名 (列名[(length)]...);
• 唯一性索引：
  索引列的所有值都只能出现一次，即必须唯一
  语法：
  CREATE UNIQUE INDEX 索引名ON表名(列名[(length)]...);
• 主键索引
  在MySQL中，当你建立主键时，主键索引同时也已经建立起来了。不必重复设置。一个表只能有一个主键，也即只有一个主键索引
• 全文索引
  MySQL5.0以上版本只有MyISAM存储引擎支持FULLTEXT，并且只限于CHAR、VARCHAR和TEXT类型的列上创建。
  语法：
  CREATE FULLTEXT INDEX 索引名ON表名(列(length))

删除索引

删除语法：
DROP INDEX 索引名ON表名;

视图管理

MySQL从5.0.1版本提供视图功能

创建：
CREATE [OR REPLACE] VIEW 视图名ASSELECT语句;
修改：
ALTER VIEW 已存在的视图名ASSELECT语句;
删除：
DROP VIEW [IF EXISTS] 视图1,视图2...;

12. 数据的导入、导出

数据的导出

使用mysqldump客户端命令
mysqldump [选项] 数据库名 [表名]>目标文件名


常用选项

• 连接选项：-u用户名、-p密码、-h服务器IP或主机名、-P连接端口
• 输出内容选项
  -t 不包含数据表的创建语句
  -d 不包含数据
• 输出格式选项
  -c 使得导出文件中的INSERT语句包括字段名称，默认是不包括的。
• 字符集选项
  –default-character-set=字符集名设置导出的客户端字符集。
  示例：
  mysqldump -uroot -p -c --default-character-set=gbk test > d:\test.sql

注：命令结尾不可加 ;

数据的导入

mysql客户端命令
mysql –u用户名 -p密码 -D数据库 < sql文件名
SQL语句
source sql文件名

三、JDBC的使用

1. 概述

• JDBC(Java Database Connectivity)：是使用Java语言操作数据库系统的标准解决方案。它将不同数据库间各自差异API与标准SQL语句分开看待，实现数据库无关的Java操作接口。
• 开发人员使用JDBC统一的API，并专注于标准SQL语句，就可以避免直接处理底层数据库驱动程序与相关操作接口的差异性

JDBC程序工作原理

JDBC API：由Sun公司提供

• 作用：定义与数据库建立连接、执行SQL语句、处理结果的方法
• 内容：供程序员调用的接口与类。集成在java.sql和javax.sql包中

DriverManager类：由Sun公司提供

• 作用：载入各种不同的JDBC驱动程序

JDBC驱动程序：由数据库厂商提供

• 作用：针对具体的数据库产品实现JDBC API中的方法
  

JDBC规范版本

• JDBC1.0 &JDK1.1
• JDBC2.0 &JDK1.2, 1.3
• JDBC3.0 &JDK1.4, 1.5
• JDBC4.0 &JDK1.6
• JDBC4.1 &JDK1.7
• JDBC4.2 &JDK1.8
  http://java.sun.com/products/jdbc/download.html提供规范下载

JDBC API中的常用接口和类

JDBC数据类型对应的Java数据类型

2. 使用JDBC操作数据库

• 步骤一：加载JDBC驱动程序
• 步骤二：提供连接参数
• 步骤三：建立一个数据库的连接
• 步骤四：创建一个statement
• 步骤五：执行SQL语句
• 步骤六：处理结果
• 步骤七：关闭JDBC对象

Step1：加载JDBC驱动程序

根据不同的数据库加载对应厂商提供的驱动程序：

• 把厂商提供的驱动程序Jar包添加到classpath
• 在Java代码中显式加载数据库驱动程序类

try {
	Class.forName("com.mysql.jdbc.Driver"); //step1
	} catch (ClassNotFoundExceptioncnfe) {
	System.out.println("Error loading driver: "+cnfe);
}

不同的数据库，厂商提供的驱动类也不同

• MySQL5：com.mysql.jdbc.Driver
• MySQL8：com.mysql.cj.jdbc.Driver
• Oracle11g：oracle.jdbc.OracleDriver
• SQLServer2005：com.microsoft.sqlserver.jdbc.SQLServerDriver

Step2：创建数据库链接

连接URL：

• MySQL5：jdbc:mysql://主机名:端口/数据库名?参数=值&参数=值
  例：jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8
• Oracle11g：jdbc:oracle:thin:@主机名:端口:数据库SID例：jdbc:oracle:thin:@localhost:1521:ORCLSQL
• Server2005：jdbc:sqlserver://主机名:端口:DatabaseName=库名例：jdbc:sqlserver://localhost:1433;DatabaseName=BookDB

数据库的用户名和密码：
MySQL5：root/root
Oracle：scott/tige

示例：

 String url="jdbc:mysql://localhost:3306/test?serverTimezone=GMY%2B8";
 //url为字符串类型

Step3：建立一个数据库的连接

从DriverManager中获得Connection对象

• Connection 是数据库连接的具体代表对象
• 可以使用 DriverManager的getConneciton()方法传入指定的连接URL、用户名和密码来获得：
  示例：

try {
	Connectionconn=DriverManager.getConnection(url, "root","root");//数据库的用户名和密码都为root。
	    ...
	 } catch (SQLExceptionse) { ...}

驱动程序会自动通过DriverManager.registerDriver() 方法注册，这样DriverManager就可以跟厂商的驱动程序通信了。

java.sql.SQLException 是在处理JDBC时经常遇到的一个受检异常对象

Step4：创建一个statement

从Connection对象中获得Statement对象

//step4
Statement stmt=conn.createStatement();

Step5：执行SQL语句

获得Statement对象之后，可以使用Statement对象的以下方法来执行SQL：

• int executeUpdate(String sql)
  执行改变数据库内容的SQL。如INSERT、DELETE、UPDATE、CREATE TABLE、DROPTABLE等语句，返回本操作影响到的记录数。
• ResultSet executeQuery(String sql)
  执行查询数据库的SQL。如SELECT语句，返回查询到的结果集ResultSet对象

示例：

//step5
int rows = stmt.executeUpdate("INSERT INTO ...");//此处省略了Mysql语句
//ResultSet rs  = stmt.executeQuery("SELECT * FROM ...");

Step6：处理结果

• 执行更新操作时，返回的结果是本次操作影响到的记录数。
• 执行查询操作时，返回的结果是一个ResultSet对象。
• ResultSet是数据库结果集的数据表。ResultSet对象具有指向其当前数据行的光标。最初光标被置于第一行之前。
• 可以使用ResultSet的next()来移动光标到下一行，它会返回true或false表示是否有下一行记录。
• ResultSet对象上有两种方式可以从当前行获取指定列的值：(Xxx代表数据类型)
• getXxx(int columnIndex) ：使用列序号获取值。列从1开始编号。较为高效。
• getXxx(String columnLabel) ：使用列名称获取值

Step7：关闭JDBC对象

在操作完成后要把所使用到的JDBC对象全都显式关闭，以释放JDBC资源。

• 调用ResultSet的close()方法
• 调用Statement的close()方法，会自动关闭ResultSet
• 调用Connection的close()方法，会自动关闭Statement和ReslutSet对象

finally{
	if(null!=rs){
	try{    rs.close(); } catch(SQLExceptione) { e.printStackTrace(); } 
	 }
	 if(null!=stmt){
	 try{    stmt.close(); } catch(SQLExceptione) { e.printStackTrace(); }
    }if(null!=conn){
    try{    conn.close(); } catch(SQLExceptione) { e.printStackTrace(); }   
    }
 }

3. PreparedStatement的使用

Statement用于执行静态 (内容固定不变的) SQL语句。

PreparedStatement用于执行动态SQL语句。

• 可防止SQL注入。

• 动态SQL语句：SQL中会变动的部分用?作为参数占位符。
  SELECT * FROM emp WHERE empno=?
• 给参数占位符传递值
  PreparedStatement提供的setXxx(int parameterIndex, 值)方法

示例：

	String sql="SELECT * FROM EMP WHERE empno = ?";
	PrepareStatement Pstat=conn.executeQuery(sql);
	String tname = "%"+name+"%";
	pstat.setString(1,tname);//替换"？"

4. 事务(Transaction)控制

Connection接口中提供的方法

• void setTransactionIsolation(int level);
  设置事务隔离级别

• void setAutoCommit(false);
  设置事务自动提交模式

• void commit();
  提交事务

• void rollback();
  回滚事务

• Savepoint savepoint = conn.setSavepoint();
  // 设置 save point

• conn.rollback(savepoint);
  //回滚到保存点

• stmt.releaseSavepoint(savepoint);
  // 释放 save point

示例：

try { 
	 ...conn.setAutoCommit(false);    //设置自动提交事务为false
	 stmt=conn.createStatement();  
	 stmt.execute("...."); // SQL
	 stmt.execute("....");
	 stmt.execute("....");
	 conn.commit();     //提交事务
	 } catch(SQLExceptione) {
	 try {  
	 	conn.rollback();     // 回滚事务   
	 	 } catch (SQLExceptione1) {     
	 	      e1.printStackTrace();     
	 	  }e.printStackTrace();   
	 } finally{
	 conn.setAutoCommit(true);    //设置自动提交事务为true
}

5. DAO模式（一种工厂模式）

DAO(Data Access Object)模式：数据访问对象，它是标准的JavaEE设计模式之一。

使用DAO模式可以把底层的数据访问操作和上层的业务逻辑分开。

一个典型的DAO实现有下列几个组件:

• DAO接口
• DAO接口的具体实现类
• DAO工厂类

DAO接口

/** DAO接口：用于声明针对某个实体的CRUD操作*/
public interface DeptDao{
	public Dept save(Dept dept);
	public void delete(int id);
	public void update(Dept dept);
	public Dept findOne(int id);
	public List<Dept> findAll();
	public long count();
	public Page<Dept> findAll(int page, int size);//分页模式
	}

DAO实现类

/**DAO实现类：针对特定的数据库产品来实现DAO接口规定的功能*/
public class DeptDaoImpl implements DeptDao {
	public void save(Dept dept) {
		String sql="INSERT INTO dept(name,description,created_time) VALUES(?,?,?)";
		String sql="SELECT @@IDENTITY";
		Connection conn=null;
		try {
		conn=DbHelper.getConn();
		PreparedStatement pstmt=conn.prepareStatement(sql);
		pstmt.setString(1, dept.getName());
		pstmt.setString(2, dept.getDescription());
		Date ct=dept.getCreated_time();
		if(ct!=null){
			pstmt.setTimestamp(3, newTimestamp(ct.getTime()));            
			}
		pstmt.executeUpdate();        
		} catch (SQLExceptione) {
			e.printStackTrace();        
		} finally{
		DbHelper.close(conn);        
		}    
			}
}

DAO工厂类

/**DAO工厂类: 根据classpath下的dao.properties文件中的配置来产生指定的具体DAO实现类的实例*/
public class DaoFactory {
private static Properties props=new Properties();
static{
	try {
		InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream("dao.properties");
		props.load(is);        
		} catch (IOException e) {
		System.err.println("在classpath下未找到dao.properties文件，请检查!");
		e.printStackTrace();        
		}    
	}
	private DaoFactory(){}
	/**
	* 根据传递的dao名称创建出它的实现类的实例
	* @param daoName 在dao.properties文件中配置的dao名
	* @return 成功，返回实现类的实例。否则,返回null
	*/
	public static Object  getInstance(String daoName){
	Object obj=null;
	//根据传递过来的dao名称从属性集中获取它对应的类的全限定名
	String className = props.getProperty(daoName);
   //根据“类的全限定名”来创建出一个实例 --->反射技术
   try {
   //加载指定的字节码，并调用它的默认构造方法来创建出一个实例
   obj=Class.forName(className).newInstance();        
   } catch (Exception e) {
   e.printStackTrace();        
   }
   return obj;    
  }
}

配置文件
#dao.properties配置文件
deptDao=cn.itvk.dao.impl.DeptDaoImpl

6. 数据源和连接池

javax.sql.DataSource接口：数据源，作为DriverManager工具类的替代项，是用于获取数据库连接的首选方式。

• Connection getConnection() throws SQLException

连接池(connection pool)：数据源的实现方式之一。

• 为数据库连接提供一个“缓冲池”：对连接重复使用，提高数据库访问的效率。
• 常用连接池组件有：Apache的DBCP，C3P0，druid
  
  

C3P0的使用

ComboPooledDataSource cpds=new ComboPooledDataSource();
cpds.setDriverClass( "com.mysql.jdbc.Driver" );
cpds.setJdbcUrl( "jdbc:mysql://localhost:3306/test" );
cpds.setUser("root");
cpds.setPassword("root");
// the settings below are optional -- c3p0 can work with defaults
cpds.setMinPoolSize(5);                                    
cpds.setAcquireIncrement(5);
cpds.setMaxPoolSize(20);

C3P0常用参数

#当连接池中的连接耗尽时，c3p0一次同时创建的连接数。Default: 3
acquireIncrement=2
#初始化时创建的连接数。Default: 3
initialPoolSize=2
#连接池中保留的最大连接数。 Default: 15
maxPoolSize=10
#连接池中保留的最小连接数。 Default: 3
minPoolSize=2
#最大空闲时间秒值，超过空闲时间未使用的连接将被丢弃。若为0则永不丢弃。Default: 0
maxIdleTime=300
#用以控制数据源内加载的PreparedStatement数量。Default: 0
maxStatements=100
#当连接池中的连接用完时，客户端调用getConnection()后等待获取新连接的时间毫秒值。
#若设为0则无限期等待。Default: 0
checkoutTimeout=5000

druid连接池

1. 什么是Druid连接池

Druid连接池是阿里巴巴开源的数据库连接池项目。

Druid连接池为监控而生，内置强大的监控功能，监控特性不影响性能。

功能强大，能防SQL注入，内置Loging能诊断Hack应用行为。

2. 代码仓库、文档、下载

• Github项目地址https://github.com/alibaba/druid

• 文档

• 下载

• 监控DEMO(自行百度)

3. 竞品对比

4. druid常用配置参数

driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF8
username=root
password=root
#druid配置初始化大小、最小、最大
initialSize=1
minIdle=10
maxActive=20
#配置获取连接等待超时的时间
maxWait=60000
#配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
timeBetweenEvictionRunsMillis=60000
# 配置一个连接在池中最小生存的时间，单位是毫秒
minEvictableIdleTimeMillis=300000
validationQuery=SELECT  'x'
testWhileIdle=true
testOnBorrow=false
testOnReturn=false
#配置监控统计拦截的filters
filters=stat
#其他配置
maxOpenPreparedStatements=20
removeAbandoned=true
removeAbandonedTimeout=1800
logAbandoned=true

5. druid的使用

//全自动加载
Javax.sql.DataSource dds=DruidDataSourceFactory.createDataSource(prop);
//手动配置加载
//druid连接池--数据库连接的基本参数
DruidDataSource dds=new DruidDataSource();
dds.setDriverClassName(props.getProperty("driverClass"));
dds.setUrl(props.getProperty("url"));
dds.setUsername(props.getProperty("user"));
dds.setPassword(props.getProperty("password"));

/*加载druid连接池的特有配置参数*/
dds.configFromPropety(props);
dds.init();

7. commons-dbutils.jar包

Apache组织提供的一个开源JDBC工具类库，能让我们更简单的使用JDBC。


常用的接口和类

• DbUtils类：JDBC工具类。线程安全的
• QueryRunner类：SQL执行器。线程安全的
• ResultSetHandler接口及实现类：结果集处理器。线程安全的

DBUtils类

提供一些加载驱动、打印异常信息、关闭操作数据库对象和事务控制的方法。

• public static boolean loadDriver(String driverClassName)
• public static void printStackTrace(SQLException e)
• public static void close(…) throws java.sql.SQLExceptionpublic static void closeQuietly(…)
• public static void commitAndCloseQuietly(Connection conn)
• public static void rollbackAndCloseQuietly(Connection conn)

QueryRunner类

SQL执行器,常用构造方法有：

• public QueryRunner()
• public QueryRunner(boolean pmdKnownBroken)
  是否不支持ParameterMetaData。Oracle就需要传入true值。
• public QueryRunner(DataSource ds)
  使用数据源构建一个QueryRunner的实例。执行SQL操作时，无须传入Connection参数，使用完毕也会自动关闭Connection对象。
• public QueryRunner(DataSource ds, boolean pmdKnownBroken)

常用方法
含Connection

• public T insert(Connection conn, String sql, ResultSetHandler rsh,Object… params) throwsSQLException
• public int update(Connection conn, String sql, Object… params) throws SQLException
• public T query(Connection conn, String sql, ResultSetHandler rsh,Object… params) throwsSQLException
• public int[] batch(Connection conn, String sql, Object[][] params) throws SQLException

不含Connection

• public T insert(String sql, ResultSetHandler rsh,Object… params) throws SQLException
• public int update(String sql, Object… params) throws SQLException
• public T query(String sql, ResultSetHandler rsh,Object… params) throws SQLException
• public int[] batch(String sql, Object[][] params) throws SQLException

ResultSetHandler接口

结果集处理器，常用的实现类有：(全部都是线程安全的)

• ScalarHandler类：
  将结果集中某一条记录的其中某一列的数据存成Object。
• BeanHandler类：
  将结果集中的第一行数据封装到一个对应的JavaBean实例中。注意：表中的列名（别名）一定要与对应JavaBean的属性名相同。
• BeanListHandler类：
  将结果集中的每一行数据都封装到一个对应的JavaBean实例中，再存放到List里。

---

总结

提示：这里对文章进行总结：
例如：以上就是今天要讲的内容，本文仅仅简单介绍了pandas的使用，而pandas提供了大量能使我们快速便捷地处理数据的函数和方法。