MySQL(2/3)

select和别名的使用

主要是用以查询数据

语法：select 字段 from 库名

-- *代表全部字段
select * from `student`;
-- 可以查询多个字段，并使用as起别名，as可以省略
select `id` as bbb ,`name` as hhh from `student`;
-- 可以使用函数concat(a,b)
-- 列名为as后的新名字，若没有起别名就是concat(a,b)的形式，concat连接显示ab
select concat('姓名：',name) as 新名字 from `student`;

除此之外还有以下功能

select version();  -- 查询版本  对应函数
select 100-1 as result;  -- 查询计算结果  对应表达式
select @@auto_increment_increment;  --查询自增步长  对应变量

---

去重及据库的表达式

作用：去除select结果中重复的数据，重复数据只显示一条

语法：select distinct 字段 from 库名

---

where子句之逻辑运算符

其中还有between....and...区间查询

where子句可以跟随在select等语句之后作为约束条件

---

联表查询join..on

引出三种联表方法（理论上有七种）

语法：select 

1.leftJoin

左表有的必须呈现，左表没有的不呈现

2.innerjoin

二者都有的才呈现

3.rightjoin

右表有的必须呈现，右表没有的不呈现

select s.id,name,class,stugrade
from student as s
inner join result as r
on s.id=r.id

select s.id,name,class,stugrade
from student as s
left join result as r
on s.id=r.id

select s.id,name,class,stugrade
from student as s
right join result as r
on s.id=r.id

两个表有相同字段要区别名来标注使用谁的字段

join...on...            on后面是限制条件与where相似

---

分页和排序

分页limit

排序order by

语法

升序asc

降序desc

排序

在查询记录中进行排序

语法：order by 表名 asc/desc

分页

在查询记录中进行分页

语法：limit  起始值，页面大小

第n页：limit   (n-1)*pageSize,pagesize

子查询和嵌套查询

子查询指在一个查询语句中嵌套一个查询语句，可以多层嵌套

一般是在where后进行

以下是inner join与子查询处理方法对比

-- 连表查询 高等数学分数大于80的学生
select s.id,`name`,class from student s
inner join result res
on s.id = res.id
inner join object obj
on res.objectNo = obj.objectNo
where objectName = '高等数学'
order by stugrade desc

-- 子查询 高等数学分数大于80的学生
select id,`name`,class from student where id in(
	select id from result where stugrade>80 and objectNo = (
		select objectNo from object where objectName = '高等数学'
	)
)

MySQL常用函数

-- ====== 常用函数 ======
-- 数学运算
select ABS(-8)  -- 取绝对值
select CEILING(9.4)  -- 向上取整
select FLOOR(9.4)  -- 向下取整
select RAND()  -- 返回一个0-1之间的随机数
select SIGN(10)  -- 返回一个数字判断数字符号  -1为负数，1为正数，0为0

-- 字符串函数
select CHAR_LENGTH('欲买桂花同载酒')  -- 返回字符串长度
select CONCAT('终不','似','少年游')  -- 连接字符串
select INSERT('我爱编程helloworld',1,2,'超级热爱')  -- 从1到2替换成语句2
select LOWER('WuYu')  -- 转换小写
select UPPER('WuYu')  -- 转换大写
select INSTR('wuyu','y')  -- 返回第一次出现字符的索引
select REPLACE('狂神说坚持就能成功','坚持','努力')  -- 替换指定的字符串
select SUBSTR('狂神说坚持就能成功',4,6)  -- 返回指定的字符串(原字符串,截取位置,截取长度)
select REVERSE('上马')  -- 反转字符串

-- 查询姓 周的学生 将周替换成州
select REPLACE(`name`,'周','州') from student
where `name` like '周%'

-- 时间和日期函数
select current_date()  -- 获取当前日期
select curdate()  -- 获取当前日期
select now()  -- 获取当前时间
select localtime  -- 获取本地时间
select sysdate()  -- 系统时间

select year(now())
select month(now())
select day(now())
select hour(now())
select minute(now())
select second(now())

-- 获取使用系统使用者
select system_user()
select user()
-- 获取版本
select version()

---

聚合函数及分组(group by)过滤(having)

分组：

语法：group by 列名     通过某列进行分组展示

如果有group by 就

过滤：

语法：having 过滤条件

having   -- 过滤分组的记录必须满足的次要条件

必须在group by 后方

where必须在group by前方

注意：where后面语句不能使用聚合函数，而having可以

---

拓展之数据库级别的MD5加密

函数：MD5(加密内容)

语句：update into 表名 set 列名=MD5('列名') -- 加密所有密码

可以在插入信息的时候加密保证信息的安全

如何校验？

        --  比对加密后的值是否相同

---

事务ACID原则、脏读、不可重复读、幻读

两个相关的SQL语句如果一个执行失败，那么会导致数据错误‘

事务原则：ACID原则  原子性、一致性、隔离性、持久性

1.原子性(Atomicity)

要么都成功，要么都失败

2.一致性(Consistency)

事务前后的数据完整性要保证一直

3.持久性(Durability)

事务一旦提交就不可逆，被持久化到数据库中，未被提交数据不会变化

4.隔离性(lsolation)

事务的隔离性是多个用户同时访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，事务之间要相互隔离。

-----  隔离所导致的一些问题

1.脏读

指一个事务读取了另外一个事务未提交的数据

2.不可重复读

在一个事务内读取表中的某一行数据，多次读取结果不同。（不一定是错误的，只是某些场合不对）

3.虚读（幻读）

指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致

---

测试事务实现转账

---  mysql是默认开启事务自动提交的

set autocommit = 0     ----        关闭

set autocommit = 1     ----        开启(默认)

-- 模拟银行转账
create database bank character set utf8 collate utf8_general_ci

create table `account`(
	`id` int(3) not null auto_increment,
	`name` varchar(20) not null,
	`money` decimal(9,2) not null,
	primary key(`id`)
)engine=innodb default charset=utf8   -- 创建表

insert into account(`name`,`money`) values ('A',1000.00),('B',500.00)

set autocommit = 0  -- 关闭mysql事务自动提交
start transaction -- 开启一个事务(一组事务)
update account set money=money-500 where `name` = 'B'  -- B减500
update account set money=money+500 where `name` = 'A'  -- A加500

commit;   -- 提交事务,被持久化
rollback;  -- 回滚

set autocommit = 1;  -- 恢复默认提交

回滚

        -- 当一组事务有一个执行失败时进行回滚，将数据保持原样

执行成功则提交