MySQL学习（下）-阿里云开发者社区

4.DQL语言

DQL(数据查询语言)

1.查询数据库数据，如select语句

2.简单的单表查询或夺标的复杂查询和嵌套查询

3.是数据库语言中最核心最重要的语句

4.使用频率最高的语句

select语法

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[AS alias1][,table.field2[AS alias2]][,...]]}
FROM table_name [AS table_alias]
  [LEFT | RIGHT | INNER JOIN table_name2]  -- 联合查询
  [WHERE ...]  -- 指定结果需满足的条件
  [GROUP BY ...]  -- 指定结果按照哪几个字段来分组
  [HAVING]  -- 过滤分组的记录必须满足的次要条件
  [ORDER BY ...]  -- 指定查询记录按一个或多个条件排序
  [LIMIT {[OFFSET,]ROW_COUNT | row_countOFFSET OFFSET}];
   -- 指定查询的记录从哪条至哪条

[]代表可选，{}代表必选

指定查询字段

查询所有学生信息：
SELECT * FROM student;
SELECT studentno,studentname FROM student;

AS子句作为别名

作用：可给数据列取一个新的别名，给表取一个新的别名，可把经计算或总给的结果用另一个新名称来代替

SELECT studentno AS 学号,studentname AS 姓名 FROM student AS s;
SELECT CONCAT('姓名',studentname)AS 新姓名 FROM student;

distinct关键词使用

作用：去掉select查询返回的记录结果中重复的记录(返回所有列的值都相同,只返回一条)

 # 查看哪些同学参加了考试(学号) 去除重复项
SELECT * FROM result; -- 查看考试成绩
SELECT studentno FROM result; -- 查看哪些同学参加了考试
SELECT DISTINCT studentno FROM result; -- 了解:DISTINCT 去除重复项 , (默认是ALL)

使用表达式的列

数据库中的表达式：一般由文本值，列值，null，函数和操作符等组成

where条件语句

作用：用于检索数据表中符合条件的记录

搜索条件可由一个或多个逻辑表达式组成，结果一般为真或假

逻辑操作符

-- 满足条件的查询(where)
SELECT Studentno,StudentResult FROM result;
-- 查询考试成绩在95-100之间的
SELECT Studentno,StudentResult
FROM result
WHERE StudentResult>=95 AND StudentResult<=100;
-- AND也可以写成 &&
SELECT Studentno,StudentResult
FROM result
WHERE StudentResult>=95 && StudentResult<=100;
-- 模糊查询(对应的词:精确查询)
SELECT Studentno,StudentResult
FROM result
WHERE StudentResult BETWEEN 95 AND 100;
-- 除了1000号同学,要其他同学的成绩
SELECT studentno,studentresult
FROM result
WHERE studentno!=1000;
-- 使用NOT
SELECT studentno,studentresult
FROM result
WHERE NOT studentno=1000;

模糊查询：比较操作符

-- LIKE
-- 查询姓刘的同学的学号及姓名
-- like结合使用的通配符 : % (代表0到任意个字符) _ (一个字符)
SELECT studentno,studentname FROM student
WHERE studentname LIKE '刘%';
-- 查询姓刘的同学,后面只有一个字的
SELECT studentno,studentname FROM student
WHERE studentname LIKE '刘_';
-- 查询姓刘的同学,后面只有两个字的
SELECT studentno,studentname FROM student
WHERE studentname LIKE '刘__';
-- 查询姓名中含有 嘉 字的
SELECT studentno,studentname FROM student
WHERE studentname LIKE '%嘉%';
-- 查询姓名中含有特殊字符的需要使用转义符号 '\'
-- 自定义转义符关键字: ESCAPE ':'
-- IN
-- 查询学号为1000,1001,1002的学生姓名
SELECT studentno,studentname FROM student
WHERE studentno IN (1000,1001,1002);
-- 查询地址在北京,南京,河南洛阳的学生
SELECT studentno,studentname,address FROM student
WHERE address IN ('北京','南京','河南洛阳');
-- NULL 空
-- 查询出生日期没有填写的同学
-- 不能直接写=NULL , 这是代表错误的 , 用 is null
SELECT studentname FROM student
WHERE BornDate IS NULL;
-- 查询出生日期填写的同学
SELECT studentname FROM student
WHERE BornDate IS NOT NULL;
-- 查询没有写家庭住址的同学(空字符串不等于null)
SELECT studentname FROM student
WHERE Address='' OR Address IS NULL;

连接查询

join对比：

inner join:组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集

left join:左表的记录将会全部表现出来，而右表只会显示符合搜索条件的记录，右表记录不足的地方均为null

right join: 左边只会显示符合搜索条件的记录，而右表会全部显示出来，左表记录不足的地方均为null

全连接union：通过union连接的sql它们分别单独取出列数必须相同；不要求合并的列名称相同时，以第一个sql表列名为准

/*
连接查询
   如需要多张数据表的数据进行查询,则可通过连接运算符实现多个查询
内连接 inner join
   查询两个表中的结果集中的交集
外连接 outer join
   左外连接 left join
       (以左表作为基准,右边表来一一匹配,匹配不上的,返回左表的记录,右表以NULL填充)
   右外连接 right join
       (以右表作为基准,左边表来一一匹配,匹配不上的,返回右表的记录,左表以NULL填充)    
等值连接和非等值连接
自连接
*/
-- 查询参加了考试的同学信息(学号,学生姓名,科目编号,分数)
SELECT * FROM student;
SELECT * FROM result;
/*思路:
(1):分析需求,确定查询的列来源于两个类,student result,连接查询
(2):确定使用哪种连接查询?(内连接)
*/
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
-- 右连接(也可实现)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
RIGHT JOIN result r
ON r.studentno = s.studentno
-- 等值连接
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s , result r
WHERE r.studentno = s.studentno
-- 左连接 (查询了所有同学,不考试的也会查出来)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno
-- 查一下缺考的同学(左连接应用场景)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno
WHERE StudentResult IS NULL
-- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno

自连接

数据表与自身进行连接，需求:从一个包含栏目ID , 栏目名称和父栏目ID的表中查询父栏目名称和其他子栏目名称

排序和分页

语法 : ORDER BY

ORDER BY 语句用于根据指定的列对结果集进行排序。

ORDER BY 语句默认按照ASC升序对记录进行排序。

如果您希望按照降序对记录进行排序，可以使用 DESC 关键字

-- 查询 数据库结构-1 的所有考试结果(学号 学生姓名 科目名称 成绩)
-- 按成绩降序排序
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='数据库结构-1'
ORDER BY StudentResult DESC

分页：

-- 每页显示5条数据
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='数据库结构-1'
ORDER BY StudentResult DESC , studentno
LIMIT 0,5
-- 查询 JAVA第一学年 课程成绩前10名并且分数大于80的学生信息(学号,姓名,课程名,分数)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='JAVA第一学年'
ORDER BY StudentResult DESC
LIMIT 0,10

子查询

什么是子查询?

在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句，嵌套查询可由多个子查询组成,求解的方式是由里及外;子查询返回的结果一般都是集合,故而建议使用IN关键字;

-- 查询 数据库结构-1 的所有考试结果(学号,科目编号,成绩),并且成绩降序排列
-- 方法一:使用连接查询
SELECT studentno,r.subjectno,StudentResult
FROM result r
INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo`
WHERE subjectname = '数据库结构-1'
ORDER BY studentresult DESC;
-- 方法二:使用子查询(执行顺序:由里及外)
SELECT studentno,subjectno,StudentResult
FROM result
WHERE subjectno=(
   SELECT subjectno FROM `subject`
   WHERE subjectname = '数据库结构-1'
)
ORDER BY studentresult DESC;

5.MySQL函数

数据函数

SELECT ABS(-8); //绝对值

SELECT CEILING(9.4); //向上取整

SELECT FLOOR(9.4); //向下取整

SELECT RAND(); //随机数,返回一个0-1之间的随机数

SELECT SIGN(0); //符号函数: 负数返回-1,正数返回1,0返回0

字符串函数

SELECT CHAR_LENGTH(‘狂神说坚持就能成功’); //返回字符串包含的字符数

SELECT CONCAT(‘我’,‘爱’,‘程序’); //合并字符串,参数可以有多个

SELECT INSERT(‘我爱编程helloworld’,1,2,‘超级热爱’); //替换字符串,从某个位置开始替换某个长度

SELECT LOWER(‘KuangShen’); //小写

SELECT UPPER(‘KuangShen’); //大写

SELECT LEFT(‘hello,world’,5); //从左边截取

SELECT RIGHT(‘hello,world’,5); //从右边截取

SELECT REPLACE(‘狂神说坚持就能成功’,‘坚持’,‘努力’); //替换字符串

SELECT SUBSTR(‘狂神说坚持就能成功’,4,6); //截取字符串,开始和长度

SELECT REVERSE(‘狂神说坚持就能成功’); /*反转

日期和时间函数

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());

聚合函数

COUNT() 返回满足Select条件的记录总和数，如 select count(*) 【不建议使用 *，效率低】

SUM() 返回数字字段或表达式列作统计，返回一列的总和。

AVG() 通常为数值字段或表达列作统计，返回一列的平均值

MAX() 可以为数值字段，字符字段或表达式列作统计，返回最大的值。

MIN() 可以为数值字段，字符字段或表达式列作统计，返回最小的值

MD5加密

1.简介：MD5是计算机广泛使用的杂凑算法之一，是杂凑算法的基础原理

2.实现数据加密

//创建一个新的表bo
  CREATE TABLE `bo` (
  `id` INT(4) NOT NULL,
  `name` VARCHAR(20) NOT NULL,
  `pwd` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id`)
 ) ENGINE=INNODB DEFAULT CHARSET=utf8
 //插入一些数据
  INSERT INTO bo VALUES(1,'wuhu','123456'),(2,'aha','456789')
//对pwd这一些列数据进行加密
 update testmd5 set pwd = md5(pwd);
 //单独对某个用户的密码进行加密
  INSERT INTO bo VALUES(3,'yahu','123456')
 update bo set pwd = md5(pwd) where name = 'yahu';
 //插入新的数据进行加密
  INSERT INTO bo VALUES(4,'aha',md5('123456'));
  //查询登录用户信息(md5对比使用，查看用户输入密码后的面膜进行对比)
   SELECT * FROM testmd5 WHERE `name`='aha' AND pwd=MD5('123456');

数值函数：

abs(x) – 绝对值 abs(-10.9) = 10

format(x, d) – 格式化千分位数值 format(1234567.456, 2) = 1,234,567.46

ceil(x) – 向上取整 ceil(10.1) = 11

floor(x) – 向下取整 floor (10.1) = 10

round(x) – 四舍五入去整

mod(m, n) – m%n m mod n 求余 10%3=1

pi() – 获得圆周率

pow(m, n) – m^n

sqrt(x) – 算术平方根

rand() – 随机数

truncate(x, d) – 截取d位小数

时间日期函数：

– 时间日期函数

now(), current_timestamp(); – 当前日期时间

current_date(); – 当前日期

current_time(); – 当前时间

date(‘yyyy-mm-dd hh:ii:ss’); – 获取日期部分

time(‘yyyy-mm-dd hh:ii:ss’); – 获取时间部分

date_format(‘yyyy-mm-dd hh:ii:ss’, ‘%d %y %a %d %m %b %j’); – 格式化时间

unix_timestamp(); – 获得unix时间戳

from_unixtime(); – 从时间戳获得时间

字符串函数：

length(string) – string长度，字节

char_length(string) – string的字符个数

substring(str, position [,length]) – 从str的position开始,取length个字符

replace(str ,search_str ,replace_str) – 在str中用replace_str替换search_str

instr(string ,substring) – 返回substring首次在string中出现的位置

concat(string [,…]) – 连接字串

charset(str) – 返回字串字符集

lcase(string) – 转换成小写

left(string, length) – 从string2中的左边起取length个字符

load_file(file_name) – 从文件读取内容

locate(substring, string [,start_position]) – 同instr,但可指定开始位置

lpad(string, length, pad) – 重复用pad加在string开头,直到字串长度为length

ltrim(string) – 去除前端空格

repeat(string, count) – 重复count次

rpad(string, length, pad) --在str后用pad补充,直到长度为length

rtrim(string) – 去除后端空格

strcmp(string1 ,string2) – 逐字符比较两字串大小

聚合函数：

count()

sum();

max();

min();

avg();

group_concat()

其他常用函数：

md5();

default();

6.事务和索引

事务：将一组SQL语句放在同一批次内去执行，如果一个SQL语句出错，则该批次内所有的SQL都将被取消执行，MySQL事务数理只支持InnoDB和BDB数据表类型

事务的原则：

原子性：要么全部完成，要么全部不完成

一致性：事务必须始终保持系统处于一致的状态，不管在任何给定的时间并发事务有多少

隔离性：隔离状态执行事务，是它们好像是系统在给定时间内执行的唯一操作

持久性：在事务完成以后，该事务对数据库所作的更改便持久的保持在数据库之中，并不会被回滚

基本语法

-- 使用set语句来改变自动提交模式
SET autocommit = 0;   /*关闭*/
SET autocommit = 1;   /*开启*/
-- 注意:
--- 1.MySQL中默认是自动提交
--- 2.使用事务时应先关闭自动提交
-- 开始一个事务,标记事务的起始点
START TRANSACTION  
-- 提交一个事务给数据库
COMMIT
-- 将事务回滚,数据回到本次事务的初始状态
ROLLBACK
-- 还原MySQL数据库的自动提交
SET autocommit =1;
-- 保存点
SAVEPOINT 保存点名称 -- 设置一个事务保存点
ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名称 -- 删除保存点

测试：

A在线买一款价格为500元商品,网上银行转账.
A的银行卡余额为2000,然后给商家B支付500.
商家B一开始的银行卡余额为10000
//创建数据库shop和创建表account并插入2条数据
CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `shop`;
CREATE TABLE `account` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL,
`cash` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO account (`name`,`cash`)
VALUES('A',2000.00),('B',10000.00)
//转账实现
SET autocommit = 0; //关闭自动提交
START TRANSACTION;  //开始一个事务，标记事务的起始点
UPDATE account SET cash=cash-500 WHERE `name` = `A`;
UPDATE account SET cash=cash+500 WHERE `name` = `B`;
COMMIT; //提交事务
SET autocommit = 1;  //恢复自动提交

索引

索引的作用：提高查询速度，确保数据的唯一性，可以加速表和表之间的连接，实现表与表之间的参照完整性，使用分组和排序子句进行数据检索时，可以显著减少分组和排序的时间，全文检索字段进行搜索优化

分类

主键索引，唯一索引，常规索引，全文索引

主键索引：某一个属性组能唯一标识一条记录，最常见的的索引类型，确保数据记录的唯一性

唯一索引：避免同一个表中某数据列中的值重复，主键索引只能由一个，唯一索引可能有多个

常规索引：快速定位特定数据，index和key关键词都可以设置常规索引

全文索引：快速定位特定数据

备份

– 导出 -w可携带备份条件

导出一张表 – mysqldump -uroot -p123456 school student >D:/a.sql 　mysqldump -u用户名 -p密码库名表名 > 文件名(D:/a.sql)

导出多张表 – mysqldump -uroot -p123456 school student result >D:/a.sql 　mysqldump -u用户名 -p密码库名表1 表2 表3 > 文件名(D:/a.sql)

导出所有表 – mysqldump -uroot -p123456 school >D:/a.sql 　mysqldump -u用户名 -p密码库名 > 文件名(D:/a.sql)

导出一个库 – mysqldump -uroot -p123456 -B school >D:/a.sql 　mysqldump -u用户名 -p密码 -B 库名 > 文件名(D:/a.sql)

如有错误，请及时指正！如有侵权，联系删除

参考文章：狂神说

MySQL学习（下）

4.DQL语言

5.MySQL函数

6.事务和索引

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

MySQL学习（下）

4.DQL语言

5.MySQL函数

6.事务和索引

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像