MySQL笔记

MySQL
1.定义：数据库是长期储存在计算机内，有组织的，可共享的数据集合
2.分类：非数据化结构
半数据化结构
数据化结构
3.SQL（Structured Query Language)结构化查询语言
4.分类
DDL：数据库定义语言（定义数据库的一些组件 表 索引 视图 自增序列...）
​ DML：数据库操作语言（添加 删除 修改） CRUD
​ DQL：数据库查询语言 查询
​ DCL：数据库控制语言（权限 用户管理...） DBA工程师
5.操作数据库

创建数据库：create datebase tgf;
create datebase tgf default charst utf8 collate_general_ci;
使用数据库：use tgf;
删除数据库：drop tgf;
查看数据库：show datebases;
查看当前数据库下的表：show tables;

6.创建表
create table 表名（
列名 类型,
列名 类型,
列名 类型,
列名 类型
)default charset=utf8;

例：create table tb1(
id int not null auto_increment primary key, -- 不允许为空 & 主键 & 自增
name varchar(16) not null, -- 不允许为空
email varchar(32) null, -- 允许为空（默认）
age int default 3 -- 插入数据时，如果不给age列设置值，默认值：3
)default charset=utf8;

7.删除表：drop table 表名；
清空表：delete from 表名；或者truncate table 表名;（速度快、无法回滚撤销等）

• 添加列
  alter table 表名 add 列名 类型;
  alter table 表名 add 列名 类型 DEFAULT 默认值;
  alter table 表名 add 列名 类型 not null default 默认值;
  alter table 表名 add 列名 类型 not null primary key auto_increment;
• 删除列
  alter table 表名 drop column 列名;
• 修改列 类型
  alter table 表名 modify column 列名 类型;
• 修改列 类型 + 名称
  alter table tb change id nid int not null;
  alter table tb change id id int not null default 5;
  alter table tb change id id int not null primary key auto_increment;
  alter table tb change id id int; -- 允许为空，删除默认值，删除自增。
• 修改列 默认值
  ALTER TABLE 表名 ALTER 列名 SET DEFAULT 1000;
• 删除列 默认值
  ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;
• 添加主键
  alter table 表名 add primary key(列名);
• 删除主键
  alter table 表名 drop primary key;
  8.SQL-DQL
  关系模型中常用的概念：
  ​关系：可以理解为一张二维表，每个关系都具有一个关系名，就是通常说的表名
  ​元组：可以理解为二维表中的一行，在数据库中经常被称为记录
  ​属性：可以理解为二维表中的一列，在数据库中经常被称为字段
  ​域：属性的取值范围，也就是数据库中某一列的取值限制 关键字：一组可以唯一标识元组的属性，数据库中常称为主键，由一个或多个列组成
  关系模式：指对关系的描述。其格式为：关系名(属性1，属性2， ... ... ，属性N)，在数据库中成为表结构

查询语句通用格式：select 列名*N from 表名 where 查询条件1 and/or 查询条件2 group by 列 Having 分组条件 Order by 排序
9.null值查询
例：
--查询不发放津贴的员工信息
select * from emp where comm is null（is not null）;

10.select结果分页
--每次查询前N行
SELECT
*
FROM
emp
LIMIT 4;

--查询第N页,每页显示M个
select * from emp limit 0,3;
select * from emp limit 3,3;
select * from emp limit 6,3;
select * from emp limit (n-1)*M,M;

--查询薪资大于1000的逆序排列，然后显示前5条记录
select * from emp where sal >1000 order by sal desc limit 0,5 ;

10.字符串函数
-- 长度

• select ename,length(ename) from emp;
  -- 截取
• select ename,SUBSTR(ename,1,3) from emp;
• select * from emp where substr(ename,5,1)='S';
  -- 大小写
• select ename, upper(ename),lower(ename) from emp;
  -- 拼接
• select CONCAT(empno,'=',ename) from emp;
  -- 替换
• select ename,REPLACE(ename,'T','—') from emp
  -- 反转：字符串反转（逆序函数），返回与原始字符串顺序相反的字符串
  -reverse（）
  -- 删除字符串左右的空格
• trim（）
  -- 从左侧字截取字符串，返回字符串边上的若干个字符/从右侧字截取字符串，返回字符串边上的若干个字符
  -left/right
  --替换字符串函数
  -insert（）
  11.日期函数
  CURDATE和CURRENT, DATE 两个函数作用相同，返回当前系统的日期值
  CURTIME和CURRENT TIME 两个函数作用相同，返回当前系统的时间值
  NOW和SYSDATE 两个函数作用相同，返回当前系统的日期和时间值
  UNIX TIMESTAMP 获取UNX时间戳函数，返回-个以UNIX时间戳为基础的无符号整数
  FROM_ UNIXTIME 将UNIX时间戳转换为时间格式，与UNIX TIMESTAMP互为反函数
  MONTH 获取指定日期中的月份
  MONTHNAME 获取指定日期中的月份英文名称
  DAYNAME 获取指定日期对应的星期几的英文名称
  DAYOFWEEK 获取指定日期对应的- -周的索引位置值
  WEEK 获取指定日期是一年中的第几周， 返回值的范围是否为0-52或1-53
  DAYOFYEAR 获取指定日期是一年中的第几天,返回值范围是1~366
  DAYOFMONTH 获取指定日期是一个月中是第几天,返回值范围是1-31
  YEAR 获取年份，返回值范围是1970~ 2069
  TIME TO SEC 将时间参数转换为秒数
  SEC TO TIME 将秒数转换为时间，与TIME TO. SEC互为反函数
  DATE ADD和ADDDATE 两个函数功能相同，都是向日期添加指定的时间间隔
  DATE SUB和SUBDATE 两个函数功能相同，都是向日期减去指定的时间间隔
  ADDTIME 时间加法运算，在原始时间上添加指定的时间
  SUBTIME 时间减法运算，在原始时间上减去指定的时间
  DATEDIFF 获取两个日期之间间隔，返回参数1减去参数2的值
  DATE_ FORMAT 格式化指定的日期，根据参数返回指定格式的值
  WEEKDAY 获取指定日期在一周内的对应的工作日索引
  12.数字函数
  ABS 求绝对值
  SQRT 求二次方根
  MOD 求余数
  CEIL和CEILING . 两个函数功能相同，都是返回不小于参数的最小整数，即向上取整
  FLOOR 向下取整，返回值转化为一个BIGINT
  RAND 生成一个0~1之间的随机數，传入整数参数是，用来产生重复序列
  ROUND 对所传参数进行四舍五入
  SIGN 返回参数的符号
  POW和POWER 两个函数的功能相同，都是所传参数的次方的结果值
  SIN 求正弦值
  ASIN 求板正弦值，与函数SIN互为反函数
  COS 求余弦值
  ACOS 求反余弦值，与函数COS互为反函数
  TAN 求正切值
  ATAN 求反正切值，与函数TAN互为反函数
  COT 求余切值
  13.转换函数
  ---日期--》字符串
• date_format(date,expr)
• select DATE_FORMAT(sysdate(),'%Y-%m-%d %H:%i:%s');
  -- 字符串--》日期
• 要注意字符串和格式的匹配
• select STR_TO_DATE('2020-4-16 17:15:24','%Y-%c-%d %H:%i:%s');
• select STR_TO_DATE('5月2022年4日','%m月%Y年%d日');
  -- 数字--》字符串
• 直接拼接一个字符串即可，可以自动转换 lpad,concat
  -- 字符串--》数字
• 依靠函数提供的参数

14.其它函数
-- 空值的处理
if null(exp1,exp2) exp1!=null?exp1:exp2
select IFNULL(comm,888) from emp;
-- 加密算法
select MD5('123456');
select AES_ENCRYPT('123456','abcd'),AES_DECRYPT(AES_ENCRYPT('123456','abcd'),'abcd');

15.DQL单表关键字执行顺序

select: 我们要显示那些列的数据
from: 从那张表中获取数据
where: 从表中获取数据的时候进行行级的数据过滤
group by: 对数据进行分组处理，一组获取对应的结果
having: 组级过滤，组级过滤的数据必须是分组条件或者是组函数
order by: 排序 asc desc
执行的顺序(面试题)
from --> where -->group by -->having-->select -->order by

16.多表查询
a. 查询的两张表如果出现同名的列，我们需要将表名标注到列名前面
b. 如果是非同名的列，表名可加可不加，推荐加上
​ 为了书写方便，可以给表添加别名
​ 一般情况下取首字母，特殊情况下取它所代表的含义
​ 表的别名只在本次查询中生效
c. 如果表与表进行关联查询的时候，如果不添加关联条件，查询的总记录数就是a*b = 笛卡尔积
​ select * from emp,dept;
​ a 15 b 10 c 10 -->1500条
d. 多表查询的时候必须要加条件
​ 等值
​ 非等值

17.表与表关联的方式
因为表的关联条件和业务查询条件放在了一起，为了防止混淆于是提供了下面三种方式。

自然连接
natural join
select e.ename,d.dname from emp e natural join dept d ;
select e.ename,d.dname from emp e natural join dept d where e.sal > 2000 ;
查询薪资大于2000的员工姓名和部门名称
using
select e.ename,d.dname from emp e join dept d using(deptno);
select e.ename,d.dname from emp e join dept d using(deptno) where e.sal > 2000;
--查询每个员工所对应的薪资等级
on
select e.ename,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;
select e.ename,s.grade from emp e join salgrade s on(e.sal between s.losal and s.hisal);

18.表与表的外连接

当我们对两张表进行关联查询的时候，基于数据的原因导致其中一张表中的数据没办法被完全查询出来
外连接可以让没查询出来的数据也显示出来
因为我们写SQL的时候表总有左右之分 ,外连接也分为
​ 左外连接：显示左面表所有的数据
​ 右外连接：显示右面表所有的数据
例：
--统计每个部门的人数
select deptno,count(empno) from emp group by deptno;
select * from emp e join dept d using(deptno);
select * from emp e left join dept d using(deptno);
select * from emp e right join dept d using(deptno);
select deptno,count(e.empno) from emp e right join dept d using(deptno) group by deptno;

-------------------------全外连接
SELECT
deptno,
e.ename,
d.dname
FROM
emp e RIGHT JOIN dept d USING ( deptno )
UNION
SELECT
deptno,
e.ename,
d.dname
FROM
emp e LEFT JOIN dept d USING ( deptno );

-------------------------Oracle的全外连接使用 Full Join

19.表与表的自连接
我们要查询的两个字段同时处于一张表上，我们只能将一张表当做含有不同意义的两张表去处理
给相同的表取不同的简称（按照所代表的含义去取）
例：
--查询每个员工与其直属领导的名字
select e.ename,m.ename from emp e,emp m where e.mgr = m.empno;
select e.ename,m.ename from emp e join emp m on(e.mgr = m.empno);

20.SQL-DML插入
1、insert into 表名 values();
​ insert into dept values(50,'shujia','shanghai');
要求插入数据的数量，类型要和定义的表结构一致
2、insert into 表名(列名) values(值...);
3、insert into 表名(列名) select ....
例：
create table dp as select * from dept where 1<>1; -- 全量更新
insert into dept(deptno,dname) select empno ,ename from emp; -- 增量更新

21.SQL-DML删除
delete from 表名
delete from 表名 where 条件
truncate table emp;
截断表--不要使用--不要使用

22.SQL-DML修改
update 表名 set 列名=value ,列名=value
update 表名 set 列名=value ,列名=value where 条件

23.数据库事务
数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成
事务指的是数据库一种保护数据的方式
事务一般由增删改操作自动调用，事务根据数据库不同提交的时机也是不同的​ mysql数据库默认执行增删改就会提交事务​
我们可以设置为 手动提交 begin 或者 start transaction;
事务的特征
ACID原则：
原子性:事务是操作数据的最小单元，不可以再分
一致性:事务提交之后，整个数据库所看到数据都是最新的数据,所有人看到的数据都是一致的
隔离性:别人无法访问到我们未提交的数据，而且一旦这个数据被我修改，别人也无法进行操作
持久性:事务一旦被提交，数据库就进入到一个全新的状态,数据再也不能返回到上一个状态
commit; 提交
rollback; 回滚
24.事务的好处与坏处：
好处：方便
坏处：不能将多个SQL纳入到一个事务，不便于管理；当我们大批量插入数据的时候，数据库会频繁的开启关闭事务影响插入效率

25.事务的隔离级别
级别设置的越高，数据越安全，但是效率越低
读未提交
我们可以读取到别人未提交的数据
有可能产生脏读的问题
读已提交
只能读取别人提交后的数据
不能达到可重复读，但是可以避免脏读
有可能产生虚读或者幻读的情况
可重复读
当数据被我查询之后，别人就不能修改这个数据了
说明在我查询的时候已经有事务操作到这些数据，查询都会开启事务
但是不能防止别人查询别的数据
序列化 当前数据库只能存在一个事务，但我操作数据库的时候，别人是不能访问数据库 这时对于用户来讲数据相当安全，一般在倒库的时候才会开启这种级别
脏读
读取别人未提交的数据，这个数据是不安全的
虚读
第一次读取的数据，第二次在读取的时候有可能被被人修改了
幻读
第一次读取的数据，第二次多了一条或者少了一条

26.表table的约束
(1)主键约束 PRIMARY KEY
​ a: 主键值必须唯一标识表中的每一行，且不能为 NULL，即表中不可能存在有相同主键值的两行数据。
​ b: 主键分为单字段主键和多字段联合主键
​ c: 联合主键不能包含不必要的多余字段。当把联合主键的某一字段删除后，如果剩下的字段构成的主键仍然满足唯一性原则，那么这个联合主键是不正确的。这是最小化原则。
例：CREATE TABLE t_pk01(
id INT(11) PRIMARY KEY,
name VARCHAR(25),
deptId INT(11),
salary FLOAT
);

CREATE TABLE t_pk03(
tid INT(11),
cid INT(11),
salary FLOAT,
PRIMARY KEY(tid,cid)
);
-- ALTER TABLE <数据表名> ADD PRIMARY KEY(<字段名>);
ALTER TABLE t_pk02 ADD PRIMARY KEY(id);

-- ALTER TABLE <数据表名> DROP PRIMARY KEY;
ALTER TABLE t_pk04 DROP PRIMARY KEY;

(2)唯一性约束 unique
列中的值可以为空但是不能相同
例：
CREATE TABLE tb_unique01(
id INT(11) PRIMARY KEY,
name VARCHAR(22),
phonenum VARCHAR(11) UNIQUE,
location VARCHAR(50)
);

-- ALTER TABLE <数据表名> ADD CONSTRAINT <唯一约束名> UNIQUE(<列名>);
ALTER TABLE tb_unique01 ADD CONSTRAINT t_unique01_unique_location UNIQUE(location);

-- ALTER TABLE <表名> DROP INDEX <唯一约束名>;
ALTER TABLE tb_unique01 DROP INDEX unique_t_unique01_location;

(3)非空约束 not null
列中的值不能为null
例：
CREATE TABLE tb_null01(
id INT(11) PRIMARY KEY,
name VARCHAR(22) NOT NULL,
location VARCHAR(50)
);

-- ALTER TABLE <数据表名> CHANGE COLUMN <字段名> <字段名> <数据类型> NOT NULL;
ALTER TABLE tb_null01 CHANGE COLUMN location location VARCHAR(50) NOT NULL;

-- ALTER TABLE <数据表名> CHANGE COLUMN <字段名> <字段名> <数据类型> NULL;
ALTER TABLE tb_null01 CHANGE COLUMN location location VARCHAR(50) NULL;

(4)定义外键时，需要遵守下列规则： 主表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则主表与从表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。
必须为主表定义主键。 主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主表中，这个外键的内容就是正确的。 在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或唯一性键。
外键中列的数目必须和主表的主键中列的数目相同。
外键中列的数据类型必须和主表主键中对应列的数据类型相同。

27.索引

创建索引
-- CREATE INDEX indexName ON mytable(username);
Create Index index_dept_dname on dept(dname);
删除索引
Drop Index index_dept_dname on dept;
索引的分类
常规索引
​ 常规索引，也叫普通索引（index或key），它可以常规地提高查询效率。一张数据表中可以有多个常规索引。常规索引是使用最普遍的索引类型，如果没有明确指明索引的类型，我们所说的索引都是指常规索引。

​主键索引
​主键索引（PrimaryKey），也简称主键。它可以提高查询效率，并提供唯一性约束。一张表中只能有一个主键。被标志为自动增长的字段一定是主键，但主键不一定是自动增长。一般把主键定义在无意义的字段上（如：编号），主键的数据类型最好是数值。

唯一索引
​唯一索引（UniqueKey），可以提高查询效率，并提供唯一性约束。一张表中可以有多个唯一索引。

外键索引
外键索引（ForeignKey），简称外键，它可以提高查询效率，外键会自动和对应的其他表的主键关联。外键的主要作用是保证记录的一致性和完整性。

优点:
索引可以让MySQL快速地查找到我们所需要的数据，但这并不是索引的唯一作用。
​ 索引大大减少了MySQL服务器需要扫描的数据量。
​ 索引可以帮助服务器避免排序和临时表。
​ 索引可以将随机I/O变为顺序I/O。

缺点:
​ 影响数据库的增删改速度

28.视图
MySQL视图（View）是一种虚拟存在的表，同真实表一样，视图也由列和行构成，但视图并不实际存在于数据库中。 行和列的数据来自于定义视图的查询中所使用的表，并且还是在使用视图是动态生成的。
优点
​ 1) 定制用户数据，聚焦特定的数据
​ 2) 简化数据操作
​ 3) 提高数据的安全性
​ 4) 共享所需数据
​ 5) 更改数据格式
​ 6) 重用 SQL 语句
创建查看
-- CREATE VIEW <视图名> AS <SELECT语句>
--<视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。
-- <SELECT语句>：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。
CREATE VIEW v_swordsman AS
( SELECT tid, NAME FROM teacher ) UNION ALL
( SELECT sid, NAME FROM student );
SELECT * FROM v_swordsman
-- DESCRIBE 视图名；
DESCRIBE v_swordsman

-- SHOW CREATE VIEW 视图名;
SHOW CREATE VIEW v_swordsman

-- ALTER VIEW <视图名> AS <SELECT语句>
-- <视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。
-- <SELECT 语句>：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。
-- DROP VIEW IF EXISTS <视图名1> [ , <视图名2> …]

29.三范式
第一范式
数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组

第二范式
数据库中每一行数据必须依赖于主键，每一个行数据都要有主键
主键是一行数据的唯一性标识

第三范式
表中非主键的列要完全依赖于主键，不能出现部分属性依赖于其他属性
当出现传递依赖的时候要将非依赖于主键的列专门创建一张表进行管理

30.表与表之间的关系
一对一
多对一
多对多

31.JDBC连接数据库

1.首先注册驱动，两种方式
（1）
DriverManager.registerDriver(new Driver());
（2）
Class.forName("com.mysql.jdbc.Driver");

2.建立连接
Connection conn=DriverManager.getConnection(url,username,password);
这里的URL，username，password都是本地的
例：
String url="jdbc:mysql://192.168.253.10:3306/School?useUnicode=true&characterEncoding=utf8&useSSL=false";
String username="root";
String password="123456";
这里的URL格式一定要记住！！！

3.获取数据库对象
Statement stat = conn.createStatement();

4.执行SQL语句
这里的语句分为DDL和DQL两种
DDL不需要执行第五步
例：
int i = stat.executeUpdate("update student set sname='汤光峰' where sno=108");
DML需要执行第五步
例：
ResultSet resultSet = statement.executeQuery("select cno,cname,tno from course");

5.返回结果集（这里返回的结果所用到的方法重载，有两种）
while (resultSet.next()){
String cno = resultSet.getString("cno");
String cname = resultSet.getString("cname");
String tno = resultSet.getString("tno");
System.out.println("编号:"+cno+"姓名:"+cname+"性别:"+tno);
}
=while (resultSet.next()){
String cno = resultSet.getString(1);
String cname = resultSet.getString(2);
String tno = resultSet.getString(3);
System.out.println("编号:"+cno+"姓名:"+cname+"性别:"+tno);
}

6.释放资源
对象名.close（）

（1）.模糊查询
例1：
String sql="select id,username,password from user where username like ?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,"%汤光峰%");
ResultSet resultSet = preparedStatement.executeQuery();

（2）.prepareStatement方法的使用：（这里的预编译用于防止传入值不规范，所以先预编译一下，然后进行传值，起到安全作用）
//这里的问号相当于一个占位符，为了后面传值
String sql = "select username,password from user where username=? and password=?";
//创建预编译数据库操作对象
PreparedStatement pps = conn.prepareStatement(sql);
//将sql中的占位符附上值
//第一个参数表示给第几个问号传值，第二个参数表示具体的值，序号从1开始
pps.setString(1,username);
pps.setString(2,password);
//执行sql语句
ResultSet rs = pps.executeQuery();

32 悲观锁和乐观锁的概念
事务1–>读取到版本号1.1
事务2—>读取到版本号1.1
其中事务1先修改了,修改之后看了版本号是1.1 ,于是提交修改的数据,将版本号修改为1.2
其中事务2后修改的,修改之后准备提交的时候,发现版本号是1.2 ,和它最初读的版本号不一致。回滚。
悲观锁:事务必须排队执行。数据锁住了,不允许并发。 (行级锁: select后面添加for update )
乐观锁:支持并发,事务也不需要排队,只不过需要一个版本号。