1、DQL查询数据
1.1、DQL
(Data Query LANGUAGE : 数据查询语言)
- 所有的查询操作都用它 Select
- 简单的查询，复杂的查询它都能做~
- 数据库中最核心的语言，最重要的语句
- 使用频率最高的语句
1.2、指定查询字段
SELECT * FROM student
SELECT * FROM result
-- 查询指定字段
SELECT `studentNo` ,`studentname` FROM student
-- 别名 给结果起一个名字
SELECT `studentNo` AS 学号,`studentname` AS 姓名 FROM student
-- 函数 Concat(a,b)
SELECT CONCAT('姓名:',studentname) AS 新名字 FROM student语法：SELECT 字段,... FROM 表
去重
作用：去除SELECT 查询出来的结果中重复的数据，重复的数据只显示一条
-- 去重
SELECT * FROM result
SELECT `studentno` FROM student
SELECT DISTINCT `studentno` FROM student数据库的列
SELECT VERSION() -- 查询系统版本
SELECT 90-2 AS 结果 -- 用来计算
SELECT @@AUTO_increment_increment -- 查询自增步长
-- 学员考试成绩 +1 查看
SELECT `studentno`,`studentresult`+1 AS '提分' FROM result数据库中的表达式： 文本值，列，Null，函数，计算表达式，系统变量….
select 表达式 from 表
1.3 where条件子句
作用：检索数据中== 符合条件== 的值
逻辑运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| and && | A and B A &&B | 逻辑与，两个都为真，结果为真 |
| or || | A or B A || B | 逻辑或，其中一个为真，则结果为真 |
| not ! | not A !A | 逻辑非， 真为假，假为真 |
SELECT Studentno ,`studentresult` FROM result
-- 查询考试成绩在95-100之间
SELECT studentno,studentresult FROM result
WHERE studentresult >=95 AND StudentResult <=100
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
-- select not studentno =1000模糊查询: 比较运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| IS NULL | A IS NULL | 如果操作符为null,结果为真 |
| IS NOT NULL | A IS NOT NULL | 如果操作符不为null,结果为真 |
| BETWEEN | A BETWEEN B AND C | 若a 在 b 和c 之间，则结果为真 |
| LIKE | A LIKE B | SQL 匹配，如果a匹配b，则结果为真 |
| IN | A IN (A1,A2…) | 假设a在a1，或者a2…. 其中的某一个值中，结果为真 |
========================like======================================
-- 查询姓刘的同学
-- like 结合 %% 代表0到任意个字符
SELECT studentname FROM student
WHERE studentname LIKE '刘%'
-- 查询姓刘的同学,查询名字只有一个字的
SELECT studentname FROM student
WHERE studentname LIKE '刘__'
-- 查询名字中间有文字的同学
SELECT studentname FROM student
WHERE studentname LIKE '%文%'
===========================in========================
-- 查询1001,1002,1003 学员
SELECT studentno ,studentname FROM student
WHERE studentno IN (1001,1002,1003)
-- 查询在北京的学生
SELECT studentno ,studentname FROM student
WHERE address IN ('河南洛阳')
=========================null not null ===============
SELECT studentno ,studentname FROM student
WHERE address ='' OR Address IS NULL
-- 查询 有出生日期的学生 不为空
SELECT studentno ,studentname FROM student
WHERE borndate IS NOT NULL
1.4 联表查询
JOIN对比
| 操作 | 描述 |
|---|---|
| inner join | 如果表中有一个匹配 就返回 |
| left join | 会从左表中返回所有的值，即使右表中没有匹 |
| right join | 会从右表中返回所有的值，即使左表中没有匹配 |
============ 联表查询========
-- 查询参加了 考试的同学 学号 姓名 科目编号 分数
SELECT * FROM student
SELECT * FROM result
/* 思路 1. 分析需求，分析查询的字段来自哪些表，（连接查询） 2. 确定使用哪种连接查询？ 7种 确定交叉点（这两个表中哪个数据是相同的） 判断的条件 ： 学生表的中 studentNo = 成绩表 studentNo */
-- join （连接的表） on（判断的条件） 连接查询
-- where 等值查询
--innner join
SELECT s.studentno ,studentname,subjectno , StudentResult
FROM student AS s
INNER JOIN result AS r
WHERE s.studentno=r.studentno
-- right join
SELECT s.studentno ,studentname,subjectno , StudentResult
FROM student AS s
RIGHT JOIN result AS r
ON s.studentno=r.studentno
-- left join
SELECT s.studentno ,studentname,subjectno , StudentResult
FROM student AS s
LEFT JOIN result AS r
ON s.studentno=r.studentno
-- 查询缺考的同学
SELECT s.studentno ,studentname,subjectno , StudentResult
FROM student AS s
LEFT JOIN result AS r
ON s.studentno=r.studentno
WHERE studentresult IS NULL
-- 查询了参加考试的同学信息： 学号，学生姓名，科目名，分数）
/* 思路 1. 分析需求，分析查询的字段来自哪些表， student、result、subject（连接查询） 2. 确定使用哪种连接查询？ 7种 确定交叉点（这两个表中哪个数据是相同的） 判断的条件 ： 学生表的中 studentNo = 成绩表 studentNo */
SELECT s.studentno ,studentname , StudentResult,subjectname
FROM student AS s
RIGHT JOIN result AS r
ON s.studentno=r.studentno
INNER JOIN `subject` AS t
ON r.subjectno =t.subjectno自连接
自己的表和自己的表连接，核心：一张表拆为两张一样的表即可
父类
| categoryid | categoryName |
|---|---|
| 2 | 信息技术 |
| 3 | 软件开发 |
| 5 | 美术设计 |
子类
| pid | categoryid | categoryName |
|---|---|---|
| 3 | 4 | 数据库 |
| 2 | 8 | 办公信息 |
| 3 | 6 | web开发 |
| 5 | 7 | ps技术 |
操作：查询父类对应的子类关系
| 父类 | 子类 |
|---|---|
| 信息技术 | 办公信息 |
| 软件开发 | 数据库 |
| 软件开发 | web开发 |
| 美术设计 | ps技术 |
-- 查询父子信息: 把一张表看为两个一模一样的表
SELECT a.`categoryName` AS '父栏目',b.`categoryName` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid` = b.`pid`1.5 分页和排序
排序
-- 排序 ： 升序 ASC ， 降序DESC
-- ORDER BY 通过那个字段排序，怎么排
-- 查询的结果根据 成绩降序 排序
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM student s
INNER JOIN `result` r
ON s.StudentNo = r.StudentNo
INNER JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE subjectName = '数据库结构-1'
ORDER BY StudentResult DESC
-- ORDER BY StudentResult ASC分页
`分页，每页只显示五条数据
语法： limit 起始值，页面的大小
第一页 limit 0,5 （1-1）*5
第二页 limit 5,5 （2-1）*5
第三页 limit 10,5 （3-1）*5
第N页 limit 0,5 （n-1）* pageSize，pageSize
【pageSize：页面大小】
【(n-1)* pageSize:起始值】
【n ：当前页 】
【数据总数/页面大小 = 总页数】
-- 查询 JAVA第一学年的 课程成绩排名前10的学生 ,并且分数大于80分的学生信息(学号,姓名,课程名称,分数)
SELECT a.studentno ,studentname,studentresult ,subjectname
FROM student AS a
RIGHT JOIN result AS b
ON a.studentno=b.studentno
INNER JOIN `subject` AS c
ON b.subjectno=c.subjectno
WHERE subjectname='JAVA第一学年' AND studentresult > 80
ORDER BY StudentResult DESC
LIMIT 0,104.6 子查询
本质 ：在where语句中嵌套一个子查询语句
-- 1、查询 数据库结构-1 的所有考试结果（学号，科目编号，成绩），降序排列
-- 查询数据库 连接查询
SELECT studentno ,a.subjectno, studentresult
FROM result AS a
INNER JOIN `subject` AS b
ON a.subjectno = b.subjectno
WHERE subjectname ='数据库结构-1'
ORDER BY studentresult DESC
-- 方式二： 使用子查询（由里及外）
-- 查询所有数据库结构-1 的学生学号
SELECT studentno ,subjectno, studentresult
FROM result
WHERE subjectno=(
SELECT subjectno FROM `subject`
WHERE subjectname='数据库结构-1'
)
ORDER BY studentresult DESC
-- 查询课程为 高等数学-2 且分数不小于 80 的同学的学号和姓名
SELECT a.studentno,studentname
FROM student AS a
INNER JOIN result AS b
ON a.studentno= b.studentno
WHERE studentresult >=80 AND subjectno=(
SELECT subjectno
FROM `subject`
WHERE subjectname='高等数学-2')
-- 高等数学-2 分数不小于80分的学生的学号和姓名
SELECT A.studentno, studentname
FROM student a
INNER JOIN result b
ON a.studentno=b.studentNO
INNER JOIN `subject` c
ON b.subjectno=c.subjectno
WHERE subjectname='高等数学-1' AND StudentResult >=80
-- 再改造
SELECT studentno, studentname FROM student WHERE studentno IN (
SELECT studentno FROM result WHERE StudentResult >=80 AND subjectno =(
SELECT subjectno FROM `subject` WHERE subjectname='高等数学-2')
)
4.7 分组和过滤
-- 查询不同课程的平均分，高分，低分，平均分大于80
-- 核心： （根据不同的课程分组）
SELECT SubjectName, AVG(StudentResult) AS 平均分,MAX(StudentResult) AS 高 分,MIN(StudentResult) AS 低分
FROM result r
INNER JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
GROUP BY r.SubjectNo -- 通过什么字段来分组
HAVING 平均分>80MySQL函数
官网: https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html
5.1 常用函数
============== 常用函数=====================
-- 数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 返回一个0-1 的随机数
SELECT SIGN(10) -- 判断一个数的符号0-0 负数返回-1 正数返回 1
-- 字符串函数
SELECT CHAR_LENGTH('zsxdfgh') --fa
SELECT CONCAT('我','爱','你') -- 拼接字符
SELECT INSERT('我爱你',2,1,'不爱') -- 查询替换
SELECT UPPER('a')-- 转大写
SELECT LOWER('A') -- 转小写
SELECT INSTR('asdfgyh','h') -- 返回第一次出现子串的索引
SELECT REPLACE('sdfgh','d','A') -- 替换出现的指定字符串
SELECT SUBSTR('12345678',4,2) -- 返回指定字符串, 截取位置,截取长度
SELECT REVERSE('123456789') -- 反转字符串
-- 查询姓周的同学 替换为邹
SELECT REPLACE (studentname, '周','杨')
FROM student
WHERE studentname 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 --获取系统版本5.2聚合函数
| 函数名称 | 描述 |
|---|---|
| COUNT() | 计数 |
| SUM() | 求和 |
| AVG() | 平均值 |
| MAX() | 最大值 |
| MIN() | 最小 |
-- =========== 聚合函数 ===============
-- 都能够统计 表中的数据 （ 想查询一个表中有多少个记录，就使用这个count() ）
SELECT COUNT(`BornDate`) FROM student;
-- Count(字段)，会忽略所有的 null 值
SELECT COUNT(*) FROM student;
-- Count（*），不会忽略 null 值， 本质 计算所有列行数
SELECT COUNT(1) FROM result;
-- Count（1），不会忽略忽略所有的 null 值 本质 计算 行数
SELECT SUM(`StudentResult`) AS 总和 FROM result
SELECT AVG(`StudentResult`) AS 平均分FROM result
SELECT MAX(`StudentResult`) AS 高分 FROM result
SELECT MIN(`StudentResult`) AS 低分 FROM resul5.3、数据库级别的MD5加密
-- =========测试MD5 加密=======
CREATE TABLE `testmd5`(
`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 testmd5 VALUES(1,'zhangsan','123456'),(2,'lisi','123456'),(3,'wangwu','123456')
-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id = 1
UPDATE testmd5 SET pwd=MD5(pwd) -- 加密全部的密码
-- 插入的时候加密
INSERT INTO testmd5 VALUES(4,'xiaoming',MD5('123456'))
-- 如何校验：将用户传递进来的密码，进行md5加密，然后比对加密后的值
SELECT * FROM testmd5 WHERE `name`='xiaoming' AND pwd=MD5('123456')6.事务
事务原则 ： ACID 原则 原子性，一致性，隔离性，持久性
参考博客连接 ： https://blog.csdn.net/dengjili/article/details/82468576
执行事务
-- mysql 是默认开启事务自动提交的
SET autocommit = 0 /* 关闭 */
SET autocommit = 1 /* 开启（默认的） */
-- 手动处理事务
SET autocommit = 0 -- 关闭自动提交
-- 事务开启
START TRANSACTION -- 标记一个事务的开始，从这个之后的 sql 都在同一个事务内
INSERT xx
INSERT xx
-- 提交： 持久化 （成功！）
COMMIT
-- 回滚： 回到的原来的样子 （失败！）
ROLLBACK
-- 事务结束
SET autocommit = 1 -- 开启自动提交
-- 了解
SAVEPOINT 保存点名 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 撤销保存点模拟转账
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci -- = 模拟事务操作
SET autocommit =0
START TRANSACTION -- 开启事务
UPDATE account SET money=money-500 WHERE `name`='A'
UPDATE account SET money=money+500 WHERE `name`='B'
COMMIT;
-- ROLLBACK;
SET autocommit=17. 索引
MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。 0.5s 0.00001s
提取句子主干，就可以得到索引的本质：索引是数据结构
7.1 索引分类
- 主键索引 （PRIMARY KEY ）
- 唯一的标识，主键不可重复，只能有一个列作为主键
- 唯一索引 （UNIQUE KEY）
- 避免重复的列出现，唯一索引可以重复，多个列都可以标识位 唯一索引
- 常规索引 （KEY/INDEX）
- 默认的， index。key 关键字来设置
- 全文索引 （FullText）
- 在特定的数据库引擎下才有，MyISAM 快速定位数据
基础语法
-- 索引的使用
-- 1、在创建表的时候给字段增加索引
-- 2、创建完毕后，增加索引
-- 显示所有的索引信息
SHOW INDEX FROM student
-- 增加一个全文索引 （索引名） 列名
ALTER TABLE school.student ADD FULLTEXT INDEX `studentName`(`studentName`);
-- EXPLAIN 分析sql执行的状况
EXPLAIN SELECT * FROM student; -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(studentName) AGAINST('刘');索引在小数据量的时候，用户不大，但是在大数据的时候，区别十分明显
- 索引不是越多越好
- 不要对进程变动数据加索引
- 小数据量的表不需要加索引
- 索引一般加在常用来查询的字段上
索引的数据结构
Hash 类型的索引~
Btree ： InnoDB 的默认数据结构
阅读：http://blog.codinglabs.org/articles/theory-of-mysql-index.html
扫一扫
5万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
