技术

MySQL学习笔记

为什么要用数据库：实现数据持久化；使用完整的管理系统统一管理，易于查询

数据库相关概念

DB：database，数据库，保存一系列有组织的数据

DBMS：database management system，数据库管理系统，数据库是通过DBMS创建和操作的容器

SQL：structure query language，结构化查询语言，与数据库通信的语言

SQL的优点：不是某个数据库的专有语言，几乎所有DBMS都支持SQL；简单易学；强有力，可以进行非常复杂高级的数据库操作

数据库的表与Java的相似之处：

  • 表中数据的存储方式——Java的类；
  • 表的列——Java中的属性；
  • 表的行——Java中的对象

mysql启动与停止：开始菜单以管理员身份运行命令提示符，命令net stop mysql和net start mysql

常用命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
#查看当前所有数据库
SHOW DATABASES;

#打开指定的库
USE 库名;

#查看当前库的所有表
SHOW TABLES;

#查看其他库的所有表
SHOW TABLES FROM 库名;

#查看表结构
DESC 表名;

#创建表
CREATE TABLE 表名(
    列名 列类型,
    列名 列类型,
);

基础查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
#基础查询语句，可以查询表中的字段/常量值/表达式/函数，查询结果是一个虚拟的表格，查询字段之间用逗号隔开
SELECT 查询内容 FROM 表名;

#查询所有字段
SELECT * FROM 表名;

#起别名（AS可以省略）
SELECT 原名 AS 别名;

#去重
SELECT DISTINCT 查询内容 FROM 表名;

#如果想要实现连接字符的效果，使用函数concat()
SELECT CONCAT(str1, str2);

注意：

  • 使用命令之前最好用USE命令打开指定的库

  • +号的作用：只能作为运算符，不能像Java里面连接字符串；如果其中一方为字符型，则尝试将字符型转换为数值型，转换成功则继续做加法运算，转换失败则将字符型转换为0；只要其中一方为null，则结果为null

条件查询

1
2
#条件查询语句
SELECT 查询内容 FROM 表名 WHERE 筛选条件;

筛选条件分类：

  • 条件表达式：> < = != <> >= <=；
  • 逻辑表达式：&& || ! AND OR NOT；
  • 模糊查询：LIKE，BETWEEN，AND，IN，IS NULL

LIKE：一般与通配符搭配使用，%代表任意多个字符，_代表任意单个字符

1
2
3
4
5
#查询姓名中包含字符ａ的员工信息
SELECT * FROM employees WHERE name LIKE ‘%a%’;

#查询姓名中第2个字符为a第4个字符为e的员工信息
SELECT * FROM employees WHERE name LIKE ‘_a_e’;

BETWEEN AND：包含临界值，并且两个临界值不可调换顺序

1
2
#查询年龄在30到40之间的员工信息
SELECT * FROM employees WHERE age BETWEEN 30 AND 40;

IN：判断某字段的值是否属于IN列表中的某一项

IS NULL/IS NOT NULL：判断值是否为null（安全等于<=>：普通的=不能判断值是否为null，安全等于可以）判断null还有一个IFNULL语句

排序查询

1
2
#排序查询语句，将查询到的内容排序后呈现出来
SELECT 查询内容 FROM 表名 ORDER BY 排序列表 ASC/DESC;

注意：

  • ASC/DESC如果不写，默认升序

  • ORDER BY可以支持多个字段，以及表达式/函数/别名等；一般是放在查询语句的最后面，LIMIT子句除外

常见函数

类似于Java的方法，分为单行函数和分组函数，分组函数做统计使用，又称统计函数/聚合函数

1
SELECT 函数名(实参列表);

字符函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#获取参数值的字节个数
LENGTH()

#拼接字符串
CONCAT()

#改变大小写
UPPER/LOWER()

#从指定索引处截取字符，注意mysql索引从1开始
SUBSTR()

#返回子串第一次出现的索引
INSTR()

#去掉空字符或特定字符
TRIM()

#用指定字符实现左/右填充指定长度
LPAD/RPAD()

#替换
REPLACE()

数学函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
#四舍五入
ROUND()

#向上取整，返回>=该数的最小整数
CEIL()

#向下取整
FLOOR()

#截断
TRUNCATE()

#取余，MOD(a,b)=a-a/b*b
MOD()

日期函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
#返回当前系统日期和时间
NOW()

#返回当前系统日期
CURDATE()

#返回当前系统时间
CURTIME()

#返回指定部分的年/月/日
YEAR/MONTH/DATE()

#将日期格式的字符转换成指定格式的日期
STR_TO_DATE()

#将日期转换成字符
DATE_FORMAT()

#返回两个日期相差的天数
DATEDIFF()

流程控制函数

1
2
3
4
5
6
7
8
#相当于三元运算符，if else的效果
IF()

#相当于switch case的效果
CASE() WHEN THEN ELSE END

#相当于多重if语句的效果
CASE WHEN THEN ELSE END

分组函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
#求和，可以和DISTINCT搭配实现去重，例如SUM(DISTINCT 查询内容)
SUM()

#平均值
AVG()

#最大值
MAX()

#最小值
MIN()

#计数，COUNT(*)统计结果集的行数
COUNT()

分组查询

例如查询每个部门的平均工资，查询每个工种的最高工资等；注意查询列表必须特殊，要求是分组函数和GROUP BY后出现的字段

1
2
3
4
5
6
SELECT 分组函数, 列表（跟在GROUP BY后面） 
FROM 表名 
[WHERE 筛选条件]
GROUP BY 分组列表 
[HAVING 筛选条件] 
[ORDER BY 排序子句];
分组查询中筛选条件的分类 数据源 位置 关键字
分组前筛选 原始表 GROUP BY的前面 WHERE
分组后筛选 分组后的结果集 GROUP BY的后面 HAVING

连接查询

又称多表查询，用于查询内容来自多个表时的情况

连接类型分类：内连接INNER（分为等值连接，非等值连接，自连接），外连接OUTER（分为左外连接LEFT，右外连接RIGHT，全外连接FULL），交叉连接CROSS

具体使用时，INNER和OUTER可以省略；SQL92语法用WHERE关键字连接，SQL99语法用ON连接

1
2
3
4
5
6
#SQL99语法
SELECT 查询列表 
FROM 表1 别名 [连接类型] 
JOIN 表2 别名 
ON 连接条件 
[WHERE 筛选条件];

注意：

  • 内连接的查询结果是两个表的交集
  • 外连接的查询结果=内连接的结果+主表中有而从表没有的记录
  • 左外连接LEFT JOIN左边的是主表，右外连接RIGHT JOIN右边的是主表
  • 交叉连接实现的是笛卡尔乘积

子查询

出现在其他语句中的SELECT语句，称为子查询或内查询

按子查询出现的位置进行分类：

  • SELECT后面仅仅支持标量子查询；
  • FROM后面支持表子查询；
  • WHERE/HAVING后面支持标量子查询/列子查询/行子查询；
  • EXISTS后面支持表子查询

按结果集的行列数不同进行分类：

  • 标量子查询的结果集为一行一列；
  • 列子查询的结果集为一列多行；
  • 行子查询的结果集为一行多列；
  • 表子查询的结果集为多行多列

注意：

  • 子查询最好放在小括号内
  • 放在FROM后面，将子查询结果充当一张表，要求必须起别名
  • EXISTS加一个完整查询语句，返回子查询结果是否存在，即0或1

分页查询

当要显示的数据，一页显示不全，需要分页查询

语法为语句末尾加LIMIT offset, size;，offset要显示条目的起始索引（从0开始），size要显示的条目个数

联合查询

将多条查询语句的结果合并成一个结果

1
查询语句1 UNION 查询语句2;

适用于要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致的情况
要求多条查询语句的查询列数是一致的

要求查询的每一列的类型和顺序最好一致；UNION默认去重，使用UNION ALL可以包含重复项

插入语句

1
2
3
4
5
#方式一
INSERT INTO 表名(列名) VALUES(值);

#方式二
INSERT INTO 表名 SET 列名=值, ...;

方式一支持插入多行，支持子查询，方式二均不支持

修改语句

1
2
3
4
5
#修改单表
UPDATE 表名 SET 列=值, ...;

#修改多表
UPDATE 表1 别名 INNER/RIGHT/LEFT JOIN 表2 别名 ON 连接条件 SET 列=值, ... WHERE 筛选条件;

删除语句

1
2
3
4
5
6
7
#删除单表
DELETE FROM 表名 WHERE 筛选条件;

#删除多表
DELETE 表1的别名, 表2的别名 FROM 表1 别名 INNER/RIGHT/LEFT JOIN 表2 别名 ON 连接条件 WHERE 筛选条件;

TRUNCATE TABLE 表名;

区别：DELETE可以加WHERE条件，TRUNCATE不可以；删除之后再插入数据，自增长列的值不同；DELETE删除有返回值，TRUNCATE没有

库的管理

1
2
3
4
5
6
7
8
9
10
11
#库的创建
CREATE DATABASE [IF NOT EXISTS] 库名;

#库的修改
RENAME DATABASE 原库名 TO 新库名;

#更改库的字符集
ALTER DATABASE 库名 CHARACTER SET 字符集;

#库的删除
DROP DATABASE [IF EXISTS] 库名;

表的管理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#表的创建
CREATE TABLE [IF NOT EXISTS] 表名(
  列名 列的类型 [(长度)约束],
  列名 列的类型 [(长度)约束],
  ...
);

#表的修改：添加列/删除列/修改列的类型或约束/修改列名
ALTER TABLE 表名 ADD/DROP/MODIFY/CHANGE COLUMN 列名 [列的类型 约束];

#表的删除
DROP TABLE [IF EXISTS] 表名;

#表的复制：
#只复制表的结构
CREATE TABLE 新表名 LIKE 旧表名;

#复制表的结构和数据
CREATE TABLE 新表名 SELECT * FROM 旧表名;

#只复制部分数据
CREATE TABLE 新表名 SELECT 列名 FROM 旧表名 WHERE 筛选条件;

常见约束

在创建表或修改表时添加约束，用于限制表中的数据

  • NOT NULL：非空，用于保证该字段值不为空

  • DEFAULT：默认，用于保证该字段值有默认值

  • PRIMARY KEY：主键，用于保证该字段值具有唯一性，并且非空

  • UNIQUE：唯一，用于保证该字段值具有唯一性，可以为空

  • CHECK：检查，mysql不支持

  • FOREIGN KEY：外键，用于限制两个表的关系，保证该字段值来自主表

标识列

又称自增长列，不用手动输入，系统提供的默认序列值，关键字AUTO_INCREMENT，一个表最多只能有一个标识列，可以通过SET方法设置标识列的步长

事务

事务是一个或一组语句组成的一个执行单元，要么全部执行，要么全部不执行，如果单元中某个语句执行失败，整个单元将会回滚，返回到食物开始之前的状态

隐式事务：没有明显的开启和结束的标记，比如INSERT，DELETE语句

显式事务：有明显的开启和结束的标记

1
2
3
4
SET autocommit=0; #开启事务，禁用自动提交功能
编写SQL语句
COMMIT;           #提交事务
ROLLBACK;         #回滚事务，可以配合SAVEPOINT使用

还有数据类型，视图，变量，存储过程等知识点没有整理，后会有期！