SQL
什么是SQL?
- SQL 指结构化查询语言
- SQL 使我们有能力访问数据库
- SQL 是一种 ANSI 的标准计算机语言
注:SQL只是一种标准,每一个数据库软件基本都有特有的SQL语句
SQL语言规则
- SQL 可以写在一行或者多行。为了提高可读性,各子句分行写,必要时使用缩进
- 每条命令以 ; 或 \g 或 \G 结束
- 关键字不能被缩写也不能分行
- 关于标点符号
- 必须保证所有的()、单引号、双引号是成对结束的
- 必须使用英文状态下的半角输入方式
- 字符串型和日期时间类型的数据可以使用单引号(’ ')表示
- 列的别名,尽量使用双引号(" "),而且不建议省略as
SQL语言规范
- MySQL 在 Windows 环境下是大小写不敏感的
- MySQL 在 Linux 环境下是大小写敏感的
- 数据库名、表名、表的别名、变量名是严格区分大小写的
- 关键字、函数名、列名(或字段名)、列的别名(字段的别名) 是忽略大小写的。
- 推荐采用统一的书写规范:
- 数据库名、表名、表别名、字段名、字段别名等都小写
- SQL 关键字、函数名、绑定变量等都大写
MySQL
mysql是开源免费的中小型关系型数据库
以下笔记根据尚硅谷宋红康课程记录
第一部分 基础语法
这一部分记录MySQL语法,即各种语句关键字的用法
这些语句分类并不固定
1. 数据库数据类型
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
INT或INTEGER | 4 Bytes | (-2 147 483 648,2 147 483 647) | (0,4 294 967 295) | 大整数值 |
BIGINT | 8 Bytes | (-9,223,372,036,854,775,808,9 223 372 036 854 775 807) | (0,18 446 744 073 709 551 615) | 极大整数值 |
DECIMAL | DECIMAL(M,D) ,M表示总位数,D小数位数 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
DATETIME | 8 | ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’ | YYYY-MM-DD hh:mm:ss | 混合日期和时间值 |
CHAR | 0-255 bytes | 定长字符串 | ||
VARCHAR | 0-65535 bytes | 变长字符串 | ||
TEXT | 0-65 535 bytes | 长文本数据 |
2. DDL
Data Definition Languages、数据定义语言
这些语句定义了不同的数据库、表、视图、索 引等数据库对象,还可以用来创建、删除、修改数据库和数据表的结构
主要的语句关键字包括 CREATE 、 DROP 、 ALTER 等
① 数据库操作
a. 创建数据库
CREATE DATABASE `mydatabase`;
# 手动使用字符集
CREATE DATABASE `mydatabase` CHARACTER SET 'utf8';
# 如果表不存在就创建
CREATE DATABASE IF NOT EXISTS `mydatabase` CHARACTER SET 'utf8';
b. 修改数据库
# 修改字符集
ALTER DATABASE `database` CHARACTER SET 'utf8';
# 修改数据库名称
SHOW CREATE DATABASE `database`;
c. 查看数据库
# 查看所有的数据库
SHOW DATABASE;
# 查看字符编码
SHOW VARIABLES LIKE 'character%';
d.导入导出数据库
# 导出数据库
-- 先退出MySQL
mysqldump -u账号 -p密码 数据库名 > 绝对路径名;
# 导入外部数据库
-- 先登录MySQL
-- 选择数据库
source 存储数据文件的路径;
e. 删除数据库
# 删除数据库
DROP 数据库名;
② 表操作
a. 创建表
# 创建表结构
CREATE TABLE 表名(
字段1 数据类型 字段约束 注释,
字段2 数据类型 字段约束 注释,
...
);
b. 修改表结构
# 修改表名
ALTER TABLE 表名 RENAME TO 表名2;
# 移除主键
ALTER TABLE 表名 DROP primary key;
# 添加主键
ALTER TABLE 表名 ADD primary key(键);
# 修改列类型
ALTER TABLE 表名 MODIFY 列名 varchar(16);
# 修改列名称
ALTER TABLE 表名 CHANGE old_name new_name int;
# 添加一个字段
ALTER TABLE 表名 ADD 字段名 约束;
c. 查看表结构
DESC 表名;
d. 删除表
# 删除表
DROP TABLE IF EXISTS 表名;
e. 清空表数据
清空表数据同时保留表结构
TRUNCATE TABLE 表名; # 不可回滚
DELETE FROM 表名; # 可回滚,该操作是DML
3. DML
Data Manipulation Language、数据操作语言
这些语句用于添加、删除、更新和查询数据库记 录,并检查数据完整性。
主要的语句关键字包括 INSERT 、 DELETE 、 UPDATE 、 SELECT 等。
SELECT是SQL语言的基础,最为重要
① SELECT
select是SQL中最常用的关键字,他代表查询
a. select七大子句
书写sql的顺序
select : 查询目标
from : 从那个表中筛选
join ... on : 关联多表查询,去除多余的项
where : 从表中筛选的条件
group by : 分组依据
having : 在统计结果中再次筛选
order by : 排序
后边跟排序的关键字的列名,desc 从大到小(默认),asc从小到大
limit : 分页
limit((pageNo-1)*pageSize,pageSize); -- 显示从x开始的之后y条
sql语句的执行顺序
from -> join ... on -> where -> group by -> (分组函数,分组函数中的distinct) -> having -> select ->distinct -> order by -> limit -> union
表一旦起了别名,会覆盖原有表名,之后只能使用别名
字段起了别名,只有执行顺序在select之后的才能使用别名,且别名不会覆盖,使用原字段名也是可以的
MySQL对别名进行了优化,即使group by ,having的执行顺序在select之前,也能使用别名,但其他数据库不可
使用where对数据进行筛选,用having对查找之后的结果集进行筛选
b. 关键字举例
最基本结构:SELECT (字段1)… FROM 表名
#1. 查询员工表的信息 "*"代表所有
SELECT * FROM employees;
#2. AS 起别名 可以省略
SELECT first_name AS '名',last_name '姓' FROM employees;
#3. distinct 去除重复数据
SELECT DISTINCT department_id FROM employees;
#4. 着重号“``” 当我们的表名、列名与系统关键字冲突时可以使用着重号区分
SELECT * FROM `order`;
#5. 常数参与查询 结果集的每一行都会添加“常数值”(就是加一列常数)
SELECT *,'llz' FROM `order`;
#6. WHERE 条件过滤 查询条件一般伴随逻辑运算
# 查询last_name未指定字段的数据
SELECT * FROM employees WHERE last_name = "King";
#7. ORDER BY 排序,可多级排序
# 查询人员信息,按部门升序排序,如果部门一致按薪资降序排序(默认为ASC,升序)
SELECT employee_id,salary,department_id FROM employees ORDER BY department_id ASC,salary DESC;
#8. LIMIT 获取部分(第一个参数为偏移量,0就是第一条数据;第二个参数是获取长度)
# 查询前两条数据
SELECT * FROM emp LIMIT 0,2;
#9. INNER JOIN ... ON 内连接,多表查询中每个表根据WHERE条件显示满足要求的数据
SELECT e1.employee_id,e1.last_name,e2.employee_id AS 上级ID,e2.last_name AS 上级名称 FROM employees e1 INNER JOIN employees e2 ON e1.manager_id = e2.employee_id;
#10. LEFT JOIN ... ON 左外连接,多表查询中会有一个主表显示所有数据,其他表根据条件向主表链接
SELECT e1.employee_id,e1.last_name,e2.employee_id AS 上级ID,e2.last_name AS 上级名称 FROM employees e1 LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id;
#11. 自连接,将自己作为第二张表链接,内部需要有自引用的字段
SELECT e1.employee_id,e1.last_name,e2.employee_id AS 上级ID,e2.last_name AS 上级名称 FROM employees e1,employees e2 WHERE e1.manager_id = e2.employee_id;
#12. GROUP BY 分组,根据指定字段进行数据分组
# 查询每个部门的平均薪资 注:SELECT后的字段必须出现在 GROUP BY之后
SELECT department_id,AVG(salary) FROM employees GROUP BY department_id
#13. HIVING 过滤条件,和WHERE功能一致,但是使用在GROUP BY之后
# 有聚合函数的条件必须生命在HAVING中
SELECT department_id,AVG(salary) avgs FROM employees GROUP BY department_id HAVING avgs >= 10000
#14. union 合并,将两端sql的查询结果进行合并(需要字段名称一致)
select 字段列表
from table_a [as 别名] right join table_b [as 别名]
on -- 连接的条件
[where] -- 筛选条件
union
select 字段列表
from table_a [as 别名] left join table_b [as 别名]
on -- 连接的条件
[where] -- 筛选条件
② INSERT
插入表数据
# 插入所有的列 (要求字段数量与表的列数相一致)
insert into 表名 values(字段值1,字段值2,,,);
# 插入部分列 要求列名对应
insert into 表名(字段1,字段2...) values(字段值1,字段值2...);
insert into 表名 set 字段1=字段值1,字段2=字段值2;
-- 批量插入
insert into 表名 values (值1,值2...),(值1,值2...),(),...;
③ UPDATE
修改表数据
update 表名 set 字段1=字段值1,字段2=字段值2 [where];
④ DELETE
删除表中数据
delete from 表名 [where]; -- 一条一套的删除
4. DCL
Data Control Language、数据控制语言
这些语句用于定义数据库、表、字段、用户的访问权限和 安全级别。
主要的语句关键字包括 GRANT 、 REVOKE 、 COMMIT 、 ROLLBACK 、 SAVEPOINT 等。
第二部分 逻辑运算
这一部分主要记录逻辑运算符以及逻辑运算关键字
1. 基础运算符
大于:>
小于:<
大于等于:>=
小于等于:<=
等于:= 不能进行null判断
不等于:!= 或 <>
安全等于:<=> 可以进行null判断
逻辑与:&& 或 and
逻辑或:|| 或 or
逻辑非:! 或 not 放到要判断的字段前边
逻辑异或:^ 或 xor 相同为false,相异为true
注:满足逻辑表达式返回 1,不满足返回 0 ;null参与的所有运算结果都为null(除了“<=>”)
2. 逻辑运算关键字/函数
① IS NULL / IS NOT NULL
用于判断该值是否为NULL
# 查找“commission_pct”不为NULL的数据行
SELECT * FROM employees WHERE commission_pct IS NOT NULL;
② LEAST(…) / GREATEST(…)
在其参数列中获取最大或者最小值
注:如果是字符串比较,会以字母排序为标准,不会以字符串长度为标准。例如:“acd"与"abcdef" 会认为 ”abcdef“小
# 查找 “first_name”与“last_name”中字母排序小的值
SELECT LEAST(first_name,last_name) FROM employees;
③ BETWEEN X1 AND X2
这表示一个区间,返回 X1-X2 中的数据。左闭右开
X1表示下限,X2表示上限,调换则查不出数据
# 说获取薪资在6000-8000的人员信息
SELECT * FROM employees WHERE salary BETWEEN 6000 AND 8000;
# 说获取薪资 不在6000-8000的人员信息
SELECT * FROM employees WHERE salary NOT BETWEEN 6000 AND 8000;
④ IN(…) / NOT IN(…)
指定值在不在其中,离散值判断
# 查找薪资为 6000、8000 的人员信息
SELECT * FROM employees WHERE salary IN(6000,8000);
# 查找薪资 不为6000、8000 的人员信息
SELECT * FROM employees WHERE salary NOT IN(6000,8000);
⑤ LIKE
俗称”模糊查询“,通常需要搭配通配符进行逻辑判断
%:代表 0-n个 任意字符
_ :代表 1 个任意字符
\ : 代表转义字符,用在通配符前,使其失去意义只代表一个字符
'%a%' 包含字符 a 的数据
'a%' 第一个字符为 a 的数据
'%a' 最后一个字符为 a 的数据
"_a%" 第二个字符为 a 的数据
"_\_a%" 第二个字符为 _ 第三个字符为 a 的数据
s
# 查询第二个字符为 _ 第三个字符为 a 的数据
SELECT * FROM employees WHERE last_name LIKE '_\_a%';
⑥ ANY / ALL
任一 / 全部
# 查询员工薪资满足任意条件(>1000或>2000)的员工信息 ANY满足任意条件
SELECT * FROM emp WHERE salary > ANY (1000,2000)
# 查询员工薪资满足所有条件(>1000或>2000)的员工信息 ALL满足所有条件
SELECT * FROM emp WHERE salary > ALL (1000,2000)
第三部分 函数
函数在计算机语言的使用中贯穿始终,函数的作用是什么呢?它可以把我们经常使用的代码封装起来, 需要的时候直接调用即可。这样既 提高了代码效率 ,又 提高了可维护性 。在 SQL 中我们也可以使用函数 对检索出来的数据进行函数操作。使用这些函数,可以极大地 提高用户对数据库的管理效率 。
SQL函数有两种
- 单行函数
- 操作数据对象
- 接受参数返回一个结果
- 只对一行进行变换
- 每行返回一个结果
- 可以嵌套
- 参数可以是一列或一个值
1. 数值函数
这一部分主要讲数值相关的函数,都是单行函数
① 基本数值函数
函数 | 语义 |
---|---|
ABS(x) | 返回x的绝对值 |
SIGN(X) | 返回X的符号。正数返回1,负数返回-1,0返回0 |
PI() | 返回圆周率的值 |
CEIL(x),CEILING(x) | 返回大于或等于某个值的最小整数 |
FLOOR(x) | 返回小于或等于某个值的最大整数 |
LEAST(e1,e2,e3…) | 返回列表中的最小值 |
GREATEST(e1,e2,e3…) | 返回列表中的最大值 |
MOD(x,y) | 返回X除以Y后的余数 |
RAND() | 返回0~1的随机值 |
RAND(x) | 返回0~1的随机值,其中x的值用作种子值,相同的X值会产生相同的随机数 |
ROUND(x) | 返回一个对x的值进行四舍五入后,最接近于X的整数 |
ROUND(x,y) | 返回一个对x的值进行四舍五入后最接近X的值,并保留到小数点后面Y位 |
TRUNCATE(x,y) | 返回数字x截断为y位小数的结果 |
SQRT(x) | 返回x的平方根。当X的值为负数时,返回NULL |
② 三角函数
函数 | 语义 |
---|---|
RADIANS(x) | 将角度转化为弧度,其中,参数x为角度值 |
DEGREES(x) | 将弧度转化为角度,其中,参数x为弧度值 |
SIN(x) | 返回x的正弦值,其中,参数x为弧度值 |
ASIN(x) | 返回x的反正弦值,即获取正弦为x的值。如果x的值不在-1到1之间,则返回NULL |
COS(x) | 返回x的余弦值,其中,参数x为弧度值 |
ACOS(x) | 返回x的反余弦值,即获取余弦为x的值。如果x的值不在-1到1之间,则返回NULL |
TAN(x) | 返回x的正切值,其中,参数x为弧度值 |
ATAN(x) | 返回x的反正切值,即返回正切值为x的值 |
ATAN2(m,n) | 返回两个参数的反正切值 |
COT(x) | 返回x的余切值,其中,X为弧度值 |
③ 指数与对数
函数 | 语义 |
---|---|
POW(x,y),POWER(X,Y) | 返回x的y次方 |
EXP(X) | 返回e的X次方,其中e是一个常数,2.718281828459045 |
LN(X),LOG(X) | 返回以e为底的X的对数,当X <= 0 时,返回的结果为NULL |
LOG10(X) | 返回以10为底的X的对数,当X <= 0 时,返回的结果为NULL |
LOG2(X) | 返回以2为底的X的对数,当X <= 0 时,返回NULL |
④ 进制转换
函数 | 语义 |
---|---|
BIN(x) | 返回x的二进制编码 |
HEX(x) | 返回x的十六进制编码 |
OCT(x) | 返回x的八进制编码 |
CONV(x,f1,f2) | 返回f1进制数变成f2进制数 |
2. 字符串函数
函数 | 语义 |
---|---|
CHAR_LENGTH(s) | 返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同 |
LENGTH(s) | 返回字符串s的字节数,和字符集有关 |
CONCAT(s1,s2,…,sn) | 连接s1,s2,…,sn为一个字符串 |
REPLACE(str, a, b) | 用字符串b替换字符串str中所有出现的字符串a |
UPPER(s) 或 UCASE(s) | 将字符串s的所有字母转成大写字母 |
LOWER(s) 或LCASE(s) | 将字符串s的所有字母转成小写字母 |
TRIM(s) | 去掉字符串s开始与结尾的空格 |
REVERSE(s) | 返回s反转后的字符串 |
NULLIF(value1,value2) | 比较两个字符串,如果value1与value2相等,则返回NULL,否则返回 value1 |
SUBSTRING_INDEX(str,delim,count) | 获取字符串中指定段落获取字符串中指定段落 |
① SUBSTRING_INDEX
通过delim将字符串str进行分割,
如果count是整数就从左往右数,返回第count个分隔符左边的全部内容;
如果count是负数就从右往左数,返回第count个分隔符右边的全部内容。
例如:
180cm,75kg,27,male SUBSTRING_INDEX(profile, ',', 1) 结果为 180cm
180cm,75kg,27,male SUBSTRING_INDEX(profile, ',', -1) 结果为 male
3. 时间日期函数
函数 | 语义 |
---|---|
CURDATE() ,CURRENT_DATE() | 返回当前日期,只包含年、月、日 |
CURTIME() , CURRENT_TIME() | 返回当前时间,只包含时、分、秒 |
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP() | 返回当前系统日期和时间 |
UTC_DATE() | 返回UTC(世界标准时间)日期 |
UTC_TIME() | 返回UTC(世界标准时间)时间 |
UNIX_TIMESTAMP(date) | 将时间date以UNIX时间戳的形式返回。 |
FROM_UNIXTIME(timestamp) | 将UNIX时间戳的时间转换为普通格式的时间 |
YEAR(date) / MONTH(date) / DAY(date) | 返回具体的日期值 |
HOUR(time) / MINUTE(time) /SECOND(time) | 返回具体的时间值 |
MONTHNAME(date) | 返回月份:January,… |
DAYNAME(date) | 返回星期几:MONDAY,TUESDAY…SUNDAY |
WEEKDAY(date) | 返回周几,注意,周1是0,周2是1,。。。周日是6 |
QUARTER(date) | 返回日期对应的季度,范围为1~4 |
DAYOFYEAR(date) | 返回日期是一年中的第几天 |
DAYOFMONTH(date) | 返回日期位于所在月份的第几天 |
DAY(X),MONTH(X),YEAR(X) | 获取指定日期的年/月/日 X为一个日期字符串 |
① date_add
data_add(date,interval X type)
对日期的某一项进行加
date表示指定时间日期,X表示一个数字(加多少),type表示是年月日类型
例如:
- date_add(curdate(),interval 1 month) 为指定时间添加一个月
- date_add(curdate(),interval 1 month) 为指定时间添加一天
4. 流程控制
函数 | 语义 |
---|---|
IF(value,value1,value2) | 如果value的值为TRUE,返回value1,否则返回value2 |
IFNULL(value1, value2) | 如果value1不为NULL,返回value1,否则返回value2 |
CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2… [ELSE resultn] END | 相当于Java的if…else if…else… |
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 … [ELSE 值n] END | 相当于Java的switch…case… |
① CASE WHEN(if…else…)
case when 逻辑判断式1 then 返回结果1
when 逻辑判断式1 then 返回结果2
when 逻辑判断式2 then 返回结果3
else 返回结果4 end [as 别名]
② CAS field WHEN(switch…case…)
case 列名 when 值1 then 别名1
when 值2 then 别名2
else 别名3 (若没有else,则未被考虑到的的值为null)
end;
5. 聚合函数
函数 | 语义 |
---|---|
AVG(X) | 计算X列的平均值 |
SUM(X) | 计算X列的总合 |
MAX(X) | 计算X列的最大值 |
MIN(X) | 计算X列的最小值 |
COUNT(X) | 计算X列的记录数 |
注:除count外其他只适用于数值类型列,并且计算时不包括NULL
6. 其他
函数 | 语法 |
---|---|
VERSION() | 返回当前MySQL的版本号 |
CONNECTION_ID() | 返回当前MySQL服务器的连接数 |
DATABASE(),SCHEMA() | 返回MySQL命令行当前所在的数据库 |
USER(),CURRENT_USER()、SYSTEM_USER(), SESSION_USER() | 返回当前连接MySQL的用户名,返回结果格式为 “主机名@用户名” |
CHARSET(value) 返 | 回字符串value自变量的字符集 |
第五部分 索引
**概述:**索引是一种可以加快数据检索的数据结构。就好比字典的目录。索引的实现与数据库引擎直接相关,Innodb索引实现为B+树。
优点
- 提高检索效率,降低数据库IO成本**(主要原因)**
- 通过创建唯一索引,可以实现可以保证数据库每一行数据库的唯一性
- 在实现数据参考完整性方面,可以加速表与表之间的连接
- 在分组和排序查找时,可显著减少时间,降低cpu消耗
缺点
- 创建索引和维护索引需要成本
- 索引需要占用磁盘空间
- 索引会降低数据维护效率,对表的增删改都需要动态修改索引。
事务
基本特性
- 原子性
事务是不可分割的一段操作,最经典的就是转账例子
底层可以通过undo log日志来实现,也就是反向执行刚刚执行的sql
- 一致性
经过一系列事务操作能得到预期正确的结果。就是数据的变化是根据过程来的,不是瞎变的
帮正其他三个特性,基本就可以保证一致性,还有一些可能需要程序完成
- 隔离性
事务在提交之前,不可见
MVCC
- 持久性
事务提交之后,可以永久的存储
依靠redo log+内存,MySQL在保存数据的时候会同时更改内存和redo log
-- 开启事务
start transaction -- 仅作用一次
set autocommit = false/0; -- 长期生效,之后每次DML操作都需要提交事务
-- 回滚事务
rollback
-- 提交事务
commit
事务并发问题
脏读
对于两个事务T1,T2,T1读取了已经被T2更新但还没被提交的字段。之后,若T2回滚,T1读取的内容就是临时且无效的。
不可重复读
对于两个事务T1,T2,T1读取了一个字段,然后T2更新/修改了该字段。之后,T1再次读取了该字段,发现值不同了。
幻读
对于两个事务T1,T2,T1从一个表中读取了一个 字段,然后T2在该表中插入/删除了一些新的字段。之后,如果T1再次读取同一个表,就会多/少几行
隔离级别
- read uncommit 读未提交
允许A事务读取其他事务未提交和已提交的数据。会出现脏读,不可重复读以及幻读。
- read commit 读已提交,不可重复度
只能读取已提交的数据;不可重复读的意思是他有可能读两次,数据不一样。也就是说在同一个事务中可能读到不同的数据,可以避免脏读,但是依然有不可重复读和幻读的问题。
- repeatable read 可重复读
可重复读,在事务开启期间,禁止其他事务对该字段进行修改,读的数据都以第一次结果为主,可以避免脏读和不可重复读,但不能避免产生幻读。
- serializable
确保事务可以从一个表中读取相同的行,相同的记录;在这个事务持续期间,禁止其他事务对该表进行插入,更新,删除操作。所有并发问题都可以解决,但性能十分低下。
Oracle支持两种事务隔离级别:READ-COMMITED,SERIALIZABLE。Oracle默认的事务隔离级别为:READ COMMITED
MySQL支持4种事务隔离级别。MySQL默认的事务隔离级别为:REPEATABLE-READ。在MySQL中REPEATABLE-READ的隔离级别也可以避免幻读了。
-- 查看默认的隔离级别
select @@tx_isolation;
索引
用来快速查找那些带有特定值的记录,继而快速查找到表中的数据
原理
就是把无序的数据变成有序,提高查找效率
1.把创建了索引的列进行内容排序
2.生成倒排表
3.拼接上数据地址
4.查询时,先找索引,然后再去相应的数据
设计原则
更小,更快 查询更快,占用空间更小
- 数据少,没必须要建立索引,毕竟要消耗资源
- 索引最好是条件列
- 频繁更新,没必要建立索引
- 索引要短,长的字段可以取什么的前缀
结构
- HashCode(基本不用)
底层维护一个数组+链表
优点:
单一查询,效率很高
缺点:
内容无序,数据之间基本没有关联
对模糊查询极不友好
可能存在hashCode冲突很难受
- B+,也就是多叉树树
底层是一个多叉树,每一个叶子可以有多个数据
优点:
数据有序,并且每一个叶子,同级节点都是有联系的
对模糊查询很友好
总之就是好处一堆
约束类型
主键约束: 用于区分此条数据与其他数据
建表时 : 直接写到字段后,字段都写完后通过primary key(键)设置
建表后 : 添加主键
一张表只能有一个主键
主键唯一且不可重复也不能为空
创建一个主键后就会随之创建一个索引
删除表同时也会删除主键
删除主键时会同时删除索引,但非空约束并未去除
非空约束: 设置非空约束的字段,在插入数据时不能为null
建表时 : 直接写 not null 到字段后
建表后 : 重新定义该列,在字段后多加上not null即可
唯一键约束: 保证值在整张表内是唯一的
建表时 : 直接写 unique key 到字段后,在最后通过unique key(键1,键2……);
建表后 : 添加唯一约束
创建一个唯一约束后也会随之创建一个索引
删除索引之后唯一约束就消失了
如果是联合唯一约束,则对应约束的值都一样才会被拒绝
默认值约束: 如果没有给某个字段赋值,则会采用默认值
建表时 : 关键字default后写入默认值
如想要使用默认值直接不赋值即可
设置默认值约束后可以接受null
自增约束: 能够自己增长的整数
建表时 : 直接写关键字auto_increment到字段后即可
建表后 : 重新定义该列的同时添加主键自增
只有主键,外键或唯一键才可以使用自增约束
如果指定一个较大的是,那么从较大的数开始自增
如果设置主键自增则可以给主键设null值(主键是自增的值,不是null)
外键约束: 主表:
从表: 在从表中设置主键关联主表的主键
建表时 : 在字段后添加 references 表名(设为外键的列名)
设置主键时,必须关联主表的主键或有唯一约束的键
设置外键约束从表数据的新增受到主表数据的约束
设置外键约束后主表数据关联的列的删除和修改收到影响
外键约束设置在从表
当确认删除外键时 desc 表名 可能依然存在外键的标识
外键的级联策略
restrict : 严格的(默认),对主表和从表同时进行约束
no action : 不做任何策略
cascade : 级联,主表数据的删除和更改会带动从表数据进行删除和更改
set null : 当主表数据发生改变,从表数据改为null
分类 创建约束时可以使用sql或者使用工具
键约束(表级约束)
主键约束
唯一键约束
外键约束
列级约束
自增约束
默认值约束
非空约束
解决编码问题
找到安装MySQL的数据目录,找到my.ini文件
进入文件编辑[mysql]里 66行左右
# default-character-set=
改为
default-character-set=utf8
[mysqld] 99行左右
# character-set-server=
改为
character-set-server=utf8
记得重启服务
数据库设计的三大范式
第一范式
最基本的范式
数据库表每一列都是不可分割的基本数据项,同一列中不能有多个值
简单说就是要确保每列保持原子性
第一范式的合理遵循需要根据系统的实际需求来定
第二范式
确保数据库表中的每一列都和主键相关,而不能之和主键的某一部分相关(主要针对联合主键而言)
在一个数据库表中只能保存一种数据,不可以把多种数据存放在同一张数据库表中
第三范式
确保数据库表中的每一列都和主键相关,而不能间接相关
属性不依赖于其他非主属性
范式的优缺点
优点
结构合理
冗余较小
尽量避免插入删除修改异常
缺点
性能降低
多表查询比单表查询速度慢
数据库设计应该根据当前情况和需求做出灵活的处理
在实际设计中,要整体遵循范式理论
如果在某些特定的情况下还死死遵循范式也是不可取的,因为可能降低数据库的效率,此时可以适当增加冗余而提高性能。
第七部分 linux安装mysql
以安装mysql8为例
a. 从官网下载linux版本的mysql8.x https://dev.mysql.com/downloads/mysql/
b. 使用xftp软件将安装包放入Linux,解压
tar -xvf mysql-8.0.30-linux-glibc2.12-x86_64.tar.xz
c. 将安装包移动到 /usr/local/
下,一定要移动到这下面,不然不能初始化
mv mysql-8.0.30-linux-glibc2.12-x86_64 /usr/local/mysql-8.0
d. 在mysql-8.0下创建data目录
cd mysql-8.0
mkdir data
e. 创建mysql用户/组
groupadd mysql
useradd -g mysql mysql
f. 修改mysql-8.0目录所属用户以及组
chown -R mysql.mysql /usr/local/mysql-8.0
g. 进入bin目录,进行初始化
cd bin
./mysqld --user=mysql --basedir=/usr/local/mysql-8.0 --datadir=/usr/local/mysql-8.0/data/ --initialize
数据库初始密码为初始化最后的随机字段
h. 修改mysql配置文件
vim /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql-8.0
datadir=/usr/local/mysql-8.0/data
socket=/tmp/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
#[mysqld_safe]
#log-error=/var/log/mariadb/mariadb.log
#pid-file=/var/run/mariadb/mariadb.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
j. 将mysql服务加入到系统中
cd ..
cp -a ./support-files/mysql.server /etc/init.d/mysql
k. 授权以及添加服务
chmod +x /etc/init.d/mysql
chkconfig --add mysql
i. 启动mysql
service mysql start
l. 将mysql命令添加到服务
ln -s /usr/local/mysql-8.0/bin/mysql /usr/bin
m. 登陆mysql
mysql -uroot -p
输入初始化密码
n. 登陆成功后,修改root密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
flush privileges;
o. 修改远程连接
use mysql;
update user set host='%' where user='root';
flush privileges;
第八部分 主从配置
在这里以两台mysql互为主从为例
前提,按照第七部分安装两台mysql
1. 修改配置文件
vim /etc/my.cnf
// 在主机上配置
# 配置二进制日志文件,当主机进行写操作时,执行命令放入二进制文件中
log_bin=binlog
# 必须配置一个server-id 当前mysql实例在集群唯一识别
server-id=100
// 从机配置
server-id=101
重启mysql服务
2. 查看主从状态
进入主机的mysql服务
show master status; 查看当前主机状态
注:file是主机配置文件 postion 是当前偏移量
3. 配置主从状态
进入从机的mysql服务
show slave status \G;
查看当前主从配置,应该是什么都没有
change master to master_host='192.168.37.101',master_user='root',master_password='123456',master_log_file='binlog.000002',master_log_pos=959;
master_log_file 主从配置文件,masterr_log_pos 当前偏移量
开启从机服务
start slave;
查看主从状态
show slave status \G;
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.37.101 // 主机信息
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000002 // 配置文件
Read_Master_Log_Pos: 753 // 偏移量
Relay_Log_File: redis01-relay-bin.000002
Relay_Log_Pos: 323
Relay_Master_Log_File: binlog.000002
Slave_IO_Running: Yes // 重要
Slave_SQL_Running: Yes // 只要这两个为yes 一般都是配置成功
4. 主从错误
① uuid冲突
如果是克隆出来的mysql会出现,UUID冲突错误
cat /var/lib/mysql/auto.cnf // 这里存放该mysql的uuid
rm -rf /var/lib/mysql/auto.cnf // 删除
service mysqld restart // 重启 自动生成
cat /var/lib/mysql/auto.cnf
② sql线程异常
当主从出现同步异常时,会导致sql线程异常。
例如:主机删除一个从机不存在的东西;从机就会出现sql异常
解决
- 停止从机服务 stop slave;
- 重置从机服务 reset slave;
- 重新设置主从服务 change master to …
5. 原理
6. 集成 mycat
第十部分 Java操作数据库
这一部分记录Java操作数据,主要内容是JDBC
1. JDBC
最原始的JDBC
操作流程
- 引入MySQL驱动(在项目中加入
mysql-connector-java-8.0.29.jar
根据自己的MySQL版本而定) - 编写代码
第一代 代码
// 1.加在驱动,可以不写,要注意数据库版本
Class.forName("com.mysql.cj.jdbc.Driver");
// 2.数据库链接url
String url ="jdbc:mysql://localhost:3306/test_local2";
// 3.通过指定的驱动管理器获取数据库链接
Connection connection = DriverManager.getConnection(url, "account", "passWord");
// 4.从连接中获取sql执行器
Statement statement = connection.createStatement();
// 5.编写sql,如果有参数就拼接
String sql = "create table llz(name varchar(255),age int);";
// 6.1.执行修改语句使用executeUpdate
int i = statement.executeUpdate(sql);
// 6.2.1.执行查询语句
ResultSet resultSet = statement.executeQuery(sql);
// 6.2.1.从结果集中获取数据
while (resultSet.next()){
System.out.print(resultSet.getString("name")+" ");
System.out.print(resultSet.getString("p_date")+" ");
System.out.println(resultSet.getString("price"));
}
// 关闭资源
statement.close();
connection.close();
注:这种方法会出现SQL注入问题
第二代 避免SQL注入代码
// 1. 获取链接
Connection connection = DriverManager.getConnection(url, "root", "root");
// 2. 编写sql,参数使用 “?” 代替
String sql = "select * from shop where name = ?";
// 3. 获取sql执行对象,要传递sql,该对象有预编译功能,可避免sql注入
PreparedStatement statement = connection.prepareStatement(sql);
// 4. 为sql中的参数复制
statement.setString(1,"llz");
// 5. 执行sql,这一步和第一代代码一样
ResultSet resultSet = statement.executeQuery();
while (resultSet.next()){
System.out.print(resultSet.getString("name")+" ");
System.out.print(resultSet.getString("p_date")+" ");
System.out.println(resultSet.getString("price"));
}
2. Druid连接池
数据库连接池,主要为提高执行效率
引入相关Jar
druid-1.0.9.jar
代码
// 1.声明配置文件对象 并 利用ClassLoader加载读取数据库配置文件
Properties properties = new Properties();
InputStream inputStream = app.class.getClassLoader().getResourceAsStream("druid.properties");
properties.load(inputStream);
// 2.调用Druid提供的DruidDataSourceFactory对象获取DataSource
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
// 3.通过 DataSource获取数据库链接
Connection connection = dataSource.getConnection();
// 4.接下来步骤就和JDBC一致了 此时的Connection就是连接池中的链接
。。。
3. dbutils
为了方便查询后数据操作引用的工具类
引入依赖
commons-dbutils-1.6.jar
代码
// 1. 获取数据库链接
Connection connection = DruidUtils.getConnection();
// 2.编写sql
String sql = "SELECT * FROM shop";
// 3.创建工具对象QueryRunner()
QueryRunner queryRunner = new QueryRunner();
// 4.创建一个与数据库表字段一致的JavaBean对象,用于数据映射
// 5.声明handler shop.class为映射对象的class
BeanListHandler<shop> handler = new BeanListHandler<>(shop.class);
// 6.调用方法执行sql
List<shop> shopList = queryRunner.query(connection, sql, handler);
// 7.处理结果
for (shop shop : shopList) {
System.out.println(shop);
}
底层实现
//所有类通用的多条数据查询方法
public static <T> List<T> AllTableQuery(Class<T> classzz, String sql, Object... args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
ps = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
//执行,获取结果集 数据放在结果集中,数据的列放在结果集的元数据中,元数据用来修改结果集
rs = ps.executeQuery();
//获取结果集的元数据
ResultSetMetaData rsmd = rs.getMetaData();
//获取列数
int columnCount = rsmd.getColumnCount();
ArrayList<T> list = new ArrayList<>();
while (rs.next()) {
//通过反射创建对象
T t = classzz.newInstance();
//处理结果集中一行数据中的列:给t对象指定的属性赋值
for (int i = 0; i < columnCount; i++) {
//获取每个列的列值:通过ResultSet
Object columnValue = rs.getObject(i + 1);
//获取每个列的列名:通过ResultSEtMetaData
//获取列名:getColumnName--不推荐使用
//获取别名:getColumnLabel
String columnLabel = rsmd.getColumnLabel(i + 1);
//通过反射,将对象指定名columnName的属性赋值为指定的值columnValue;
Field field = classzz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columnValue);
}
list.add(t);
}
return list;
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtils.close(conn, ps, rs);
}
return null;
}
= null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
ps = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
//执行,获取结果集 数据放在结果集中,数据的列放在结果集的元数据中,元数据用来修改结果集
rs = ps.executeQuery();
//获取结果集的元数据
ResultSetMetaData rsmd = rs.getMetaData();
//获取列数
int columnCount = rsmd.getColumnCount();
ArrayList<T> list = new ArrayList<>();
while (rs.next()) {
//通过反射创建对象
T t = classzz.newInstance();
//处理结果集中一行数据中的列:给t对象指定的属性赋值
for (int i = 0; i < columnCount; i++) {
//获取每个列的列值:通过ResultSet
Object columnValue = rs.getObject(i + 1);
//获取每个列的列名:通过ResultSEtMetaData
//获取列名:getColumnName--不推荐使用
//获取别名:getColumnLabel
String columnLabel = rsmd.getColumnLabel(i + 1);
//通过反射,将对象指定名columnName的属性赋值为指定的值columnValue;
Field field = classzz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columnValue);
}
list.add(t);
}
return list;
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtils.close(conn, ps, rs);
}
return null;
}