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中有几种不同类型的约束,包括:
创建
创建数据库、数据表
省略命令连接和基本命令等内容。
创建数据库
设置字符编码&排序规则
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规范中有一条规定
【强制】不得使用外键与级联,一切外键概念必须在应用层解决。
-
外键是一种索引,是通过一张表中的一列指向另一张表的主键,使得这两张表产生关联
-
减少单张表的冗余信息
-
一张表中可以有一个外键也可以有多个外键
使用外键后想要删除/更新主表的相关列会报错,因为从表正在使用。
使用外键后若想向从表中插入新列,但是外键值主表中没有,会报错,更新从表的外键值,但是值不在从表中会报错。
创建外键:
-- 方法一:对已有的表进行修改
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