MySQL快速入门（JDBC）

MySQL简版笔记

简版，省略某些内容，例如函数、运算符、7种JOIN的实现等。

利于快速学习进入JDBC之后开始学习JavaWeb和MyBatis

【目录见右侧大纲】

基本概念

数据库就是存储数据的“仓库”

字段和记录

一张表格，例如学生信息表，表头有学生姓名、学号、班级、出生日期、性别、籍贯等相关信息，这些表头就是字段

而里面每一个学生的数据内容就是记录

我们把多个记录组织在一起，就形成了一个“表”（Table），我们称之为数据表。

目前市场上常用的数 据库包括 SQL Server，MySQL、Oracle、DB2、Sybase 等。

在图形化数据库软件中运行SQL语句，运行什么就鼠标选中什么，如果直接点击运行则默认运行全部SQL语句。

数据类型

创建数据库、表的时候，什么类型适合就选用什么类型。大致分为以下四种：

bit（比特、位）是表示信息的最小单位

Byte（字节）1字节 = 8位

1Byte = 8 bit

1KB = 1024Byte

数值

类型	描述	字节大小
tinyint	最小的数据	1Byte
smallint	较小的数据	2Byte
mediumint	中等大小	3Byte
int	标准整数	4Byte
bigint	较大的数据	8Byte
float	浮点数	4Byte
double	浮点数	8Byte
decimal	字符串形式浮点数（金融计算时必用）

字符串

类型	描述	范围大小
char	固定大小的字符串	0~255
varchar	可变字符串	0~65535
tinytext	微型文本	2^8-1
text	文本串	2^16-1

日期时间

YYYY-MM-DD 年月日

HH:mm:ss 时分秒

类型	格式	描述
date	YYYY-MM-DD	日期格式
time	HH:mm:ss	时间格式
datetime	YYYY-MM-DD HH:mm:ss	最常用
timestamp	1970.1.1到现在的毫秒数	时间戳
year		表示年份

NULL

空，未知。

NULL 不等于 NULL

不要直接用NULL进行运算，跟谁运算都为NULL

如果需要跟NULL进行运算请使用 IS NULL 和 IS NOT NULL

字段属性

Unsigned

无符号的整数

声明了该列不能声明为负数

zerofill

0填充

不足的位数，使用0来填充。例如：

int(3) , 5=005

自增

默认在上一条记录+1

通常设计唯一主键

可以自定义自增的步长

非空

NOT NULL

不能为空

NULL 不填写默认为空

默认

设置默认值

例如设置性别sex不设置默认为男

必有字段

在标准项目中，以下字段必须包含。表示每一条记录存在的意义。

字段名称	描述
id	主键
version	乐观锁
is_delete	伪删除
gmt_create	创建时间
gmt_update	修改时间

约束

对表的一个或多个列的限制，以限制可以存储在该列中的值的类型。

SQL中有几种不同类型的约束，包括：

• NOT NULL
• PRIMARY KEY
• UNIQUE
• DEFAULT
• FOREIGN KEY
• CHECK

创建

创建数据库、数据表

省略命令连接和基本命令等内容。

创建数据库

设置字符编码&排序规则

CREATE DATABASE database_name;
CREATE DATABASE IF EXISTS database_name;
DROP DATABASE IF EXISTS database_name;
SELECT `user` FROM student;
# 创建一个名为 mydatabase 的数据库，并将字符集设置为 utf8mb4，排序规则设置为 utf8mb4_general_ci
CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

# 创建一个名为 mytable 的表，并将字符集设置为 utf8mb4，排序规则设置为 utf8mb4_general_ci
CREATE TABLE mytable ( id INT NOT NULL, name VARCHAR(50) NOT NULL ) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

# 将数据库 mydatabase 的字符集修改为 utf8mb4，排序规则修改为 utf8mb4_general_ci
ALTER DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

# 将表 mytable 的字符集修改为 utf8mb4，排序规则修改为 utf8mb4_general_ci
ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

CREATE DATABASE 创建数据库

IF EXISTS 即如果存在，一般用为 如果存在 则 执行 xxx。这里用于可以重复创建数据库。更多用法

DROP删除表。drop、truncate和delete的用法

CHARACTER SET指定字符集

COLLATE指定排序规则

着重号 规避关键字

ALTER DATABASE修改字符集

ALTER TABLE修改排序规则

创建表

CREATE TABLE 语句用于创建数据库中的表。

CREATE TABLE 表名称 (
列名称1 数据类型,
列名称2 数据类型,
列名称3 数据类型,
....
)

数据类型	描述
integer(size) int(size) smallint(size) tinyint(size)	仅容纳整数。在括号内规定数字的最大位数。
decimal(size,d) numeric(size,d)	容纳带有小数的数字。“size” 规定数字的最大位数。“d” 规定小数点右侧的最大位数。
char(size)	容纳固定长度的字符串（可容纳字母、数字以及特殊字符）。
varchar(size)	容纳可变长度的字符串（可容纳字母、数字以及特殊的字符）。在括号中规定字符串的最大长度。
date(yyyymmdd)	容纳日期。

举例：

创建表：学号int、登陆密码varchar(20)、姓名、性别varchar(2)、出生日期datetime、家庭住址、email

CREATE TABLE IF NOT EXISTS `student` (
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号', -- int类型最大位数为4，不能为空，自增，注释内容“学号”
    `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名', -- 不能为空，默认值“匿名”，注释内容“姓名”
    `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码', 
    `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
    `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
    `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
    `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
    PRIMARY KEY(`id`) -- 主键
)ENGINE=INNODB DEFAULT CHARSET=UTF8

NOT NULL

• 不能为空

AUTO_INCREMENT

• 自增（默认自增步长为1）

COMMENT

• 注释，知道这一字段是干什么的，为养成好习惯必写

PRIMARY KEY

• 主键。一般一张表只有一个主键。可以写在第一行 id 的后面，但为了明显看到主键是什么，一般写在最下面。

ENGINE

• 引擎InnoDB和MyISAM的区别
  • MyISAM
  • InnoDB（默认使用）

引擎区别

上面引入了InnoDB和MyISAM的区别，这里不再供述。可以知道的是

• MyISAM 节约空间，速度较快
• InnoDB 安全性高，支持事务处理、外键约束、多用户操作。

物理文件的区别:

​ 所有数据库文件都存储在data目录下，一个文件夹对应一个数据库。

MySQL引擎物理文件上的区别：

• InnoDB
  • *.frm
  • 上级目录的ibdate1
• MyISAM
  • *.frm 表结结构的定义文件
  • *.MYD 数据文件（data）
  • *.MYI 索引文件（index）

查看

SHOW CREATE DATABASE 数据库名; -- 查看创建数据库的语句
SHOW CREATE TABLE 表名; -- 查看数据表的定义语句
DESC 表名; -- 显示表结构

修改

ALTER

在已有的表中添加、修改或删除列

-- 修改表名 RENAME
ALTER TABLE 旧表名 RENAME AS 新表名
-- 添加字段（列）ADD
ALTER TABLE 要修改的表
ADD 字段名 字段数据类型

ALTER TABLE teachers
ADD age INT(11)
-- 删除列 DROP
ALTER TABLE 表名
DROP COLUMN 字段名
-- 修改字段数据类型 COLUMN
ALTER TABLE 表名
ALTER COLUMN 字段名 字段数据类型

-- 修改约束
ALTER TABLE 表名称 MODIFY 字段名 数据类型 NOT NULL;
-- 重命名字段
ALTER TABLE 表名 CHANGE age age1 INT(11)
-- 删除表的字段
ALTER TABLE 表名 DROP 字段名
-- 删除表（如果存在删除）
DROP TABLE IF EXISTS 表名

CHANGE修改字段类型和约束

MODIFY用来重命名

IF EXISTS如果存在，后面的才执行，否则不执行（上面也有讲到）

两者使用时的区别：

MODIFY COLUMN用于修改表中现有列的类型；

CHANGE COLUMN用于修改列名、类型和顺序；

CHANGE COLUMN可以添加默认值和约束。

外键

数据库级别的外键，数据库过多会造成困扰（例如一些数据添加不上、不能删除等）

此处外键内容仅作了解，因为每次删除或者修改都要考虑外键约束会导致开发的时候很痛苦。

并且阿里的Java规范中有一条规定

【强制】不得使用外键与级联，一切外键概念必须在应用层解决。

1. 外键是一种索引，是通过一张表中的一列指向另一张表的主键，使得这两张表产生关联

2. 减少单张表的冗余信息

3. 一张表中可以有一个外键也可以有多个外键

   使用外键后想要删除/更新主表的相关列会报错，因为从表正在使用。

   使用外键后若想向从表中插入新列，但是外键值主表中没有，会报错，更新从表的外键值，但是值不在从表中会报错。

创建外键：

-- 方法一：对已有的表进行修改
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY(外键字段名)
REFERENCES 外表表名(主键字段名)

-- 方法二：创建表时添加外键
CONSTRAINT 约束名 FOREIGN KEY(字段名) REFERENCES 主表（被引用列）

INSERT

在数据库表中插入记录

INSERT INTO table_name (column1,column2,...) VALUES (value1,value2,...);
-- column1，column2，...等表示表列的名称，而value1，value2，...等表示这些列的对应值

对于自增的字段可以省略。

插入的值要用逗号隔开，字符串要用单引号包含。

一定要确定字段和数据一一对应。

UPDATE

更新数据库表中的记录

UPDATE table_name SET column1_name = value1, column2_name = value2,...WHERE condition;
-- column1_name，column2_name，...是要更新其值的数据库表的列或字段的名称

# 更新单列
UPDATE employees SET emp_name = 'Sarah Ann Connor'
WHERE emp_id = 3;-- 如果不筛选条件则会修改所有
# 更新多列
UPDATE employees
SET salary = 6000, dept_id = 2
WHERE emp_id = 5;

# 条件可以是一个范围
UPDATE `student` SET `name`='未定义' WHERE id BETWEEN 2 AND 5

# 可以通过多个条件定位数据
UPDATE `student` SET `name`='未定义' WHERE name='xxx' AND sex='1' age=12

WHERE条件选择（在修改、查询等操作时使用WHERE进行筛选要操作那些数据）

==BETWEEN … AND …==闭合区间选择（两边都取得到）

删除

前面有引用过删除的几种方式

-- delete
DELETE FROM `table_name`;
DELETE FROM `table_name` WHERE id=1;
-- TRUNCATE
TRUNCATE `table_name`

两者区别：

• 共同点：都能删除数据，都不会删除表结构
• 不同点：
  • TRUNCATE 会重置自增列的计数，不会影响事务
  • DELETE 不会影响自增（但是↓）

使用DELETE删除并重启数据库后：

使用 InnoDB 引擎，自增列计数会被清除，因为存储在内存中，断电即失

使用 MyISAM 引擎，不会清除自增列计数，因为存储在文件中。

查询

SELECT和别名

SELECT employee_id emp_id,last_name AS lname FROM employees;
-- 查询常数
SELECT '文字',123,employee_id,last_name FROM employees;

AS：别名。在表后空格加上别名，也可以用AS和双引号""不要使用单引号（MySQL也可以用，但是不严谨，不推荐）

如果别名中有空格，就必须使用双引号，不然识别别名为空格前面的。

DISTINCT

去除重复行

例如查询员工部门，有很多员工是从属相同部门的。

SELECT DISTINCT department_id;

多表去重（保留不重复的，工资重复项比部门少，可以看到不同部门的工资区间情况）

SELECT DISTINCT department_id,salary FROM employees;

DESC

显示表结构

DESCRIBE employees;
DESC employees;

例如查询一张表中的数值类型，是否能为空，有无默认值，有无约束等。

这两个关键字都是一个意思。

模糊查询LIKE

%

代表不确定个数的字符（前面和后面又0个或多个不确定的字符）

练习：查询last_name中包含字符’a’的员工信息

SELECTF last_name
FROM employees
WHERE last_name LIKE '%a%';-- % 代表不确定个数的字符（前面和后面又0个或多个不确定的字符）
WHERE last_name LIKE 'a%'; -- 查询以a开头的

练习：查询last_name中包含字符’a’ 且 包含字符’e’ 的员工信息

SELECTF last_name
FROM employees
WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';
-- 写法2
WHERE last_name LIKE '%a%e%' OR last_name LIKE '%e%a%';

_

一个下划线代表一个不确定的字符

查询第二个字符是’a’的员工信息：

SELECTF last_name
FROM employees
WHERE last_name LIKE '__a%';-- 下划线不要有空格，不然就是以第x个字符后面有空格的条件进行查询

查询第二个字符是_下划线且第三个字符是a的员工信息

需要使用转义字符 \

SELECTF last_name
FROM employees
WHERE last_name LIKE '_\_a%';
-- 或者
WHERE last_name LIKE '_$_a%' ESCAPE '$';

REGEXP

(正则表达式)REGEXP运算符用来匹配字符串，常用通配符：

‘^’ 匹配以某字符为开头的字符串

‘$’ 匹配以某字符为结尾的字符串

‘.’ 匹配任何一个单字符

‘[ ]’ 匹配在方括号内的任何字符。例如匹配a或b或c写作[abc]，规定范围用-，例如匹配任何字母写作[a-z]，匹配任何数字写作[0-9]

‘*’ 匹配零个或多个在它前面的字符。例如 x* 匹配任何数量的x字符，[0-9]* 匹配任何数量的数字，而*匹配任何数量的任何字符

多表查询

将不同表的列组合成一张新的表给我们看，那么这表跟表之间需要有关联，不然内容对不上。

如果我们要查询员工表的的员工id和部门表的部门名称：

SELECT employee_id,department_name
FROM employees,departments
# 两个表的连接条件
WHERE employees.`department_id` = departments.department_id;

如果查询语句中出现了多个表中都存在的字段，则必须指明此字段所在的表：

SELECT employee_id,department_n