mysql-查询

Mysql

DQL数据查询语言

使用频率最高的、系统大部分操作都是在查询

顺序有严格的要求、例如如果将limit写在order by前不允许

普通查询

-- SELECT * FROM 表名
SELECT * FROM `student`  -- 查询全部信息

-- SELECT 字段名,字段名 ... FROM 表名
SELECT `id`,`username`,`password` FROM `student`  -- 查询指定字段信息

-- SELECT 字段名 AS 别名,... FROM 表名 AS 别名
SELECT `id` AS 学号,`username` AS 用户名, `password` AS 密码 FROM `student` AS s -- 查询指定字段并设置别名

-- CONCAT()  拼接函数
-- SELEC 函数 FROM 表名
SELECT CONCAT('用户名：',`username`) AS 用户名 FROM `student` AS s

-- VERSION() 查看当前数据库的版本
SELECT VERSION()

-- SELECT DISTINCT 字段名 FROM 表名
SELECT DISTINCT `clazzid` FROM `student` -- 去重查询 重复的数据只显示一条

-- SELECT 表达式
SELECT @@AUTO_INCREMENT_INCREMENT -- 自增步长

where条件查询

检索数据中符合条件的值

搜索条件就是一个表达式或多个表达式组合而成

-- WHERE 条件查询
-- SELECT 字段名 FROM WHERE 条件1 逻辑运算法 条件2  ...  
SELECT * FROM `student` WHERE `username` = '小博'

-- BETWEEN 区间范围   &&   AND  都可以
SELECT * FROM `student` WHERE id BETWEEN 5 AND 10 -- 查询id为5 到 10的数据

-- NOT 不等于  != 不能与
SELECT * FROM `student` WHERE NOT id = 3 -- 查询id 不为 3 的数据

模糊查询

比较运算符

运算符	语法	描述
IS NULL	a IS NULL	a 如果是空 则为真
IS NOT NULL	a IS NOT NULL	a如果不是空 则为真
BETWEEN	a BETWEEN b AND c	a如果在b和c之间 则为真
LIKE	a LIKE b	如果a匹配b 则为真
IN	a IN(b,c,d)	a 如果和b、c、d其中一个匹配则为真

-- 模糊查询
-- _ 代表一个字符
SELECT * FROM `student` WHERE `username` LIKE '_博' 

-- % 代表多个字符
SELECT * FROM `student` WHERE `username` LIKE '%博'

-- IN() 返回一个或多个、只要是匹配
SELECT * FROM `student` WHERE `id` IN(1,2,3,4,5,6,7,8,9,10,11) 

-- 查询为空字符串或为空的数据
-- OR 或
-- IS NULL 为空
SELECT * FROM `student` WHERE `address` = '' OR `address` IS NULL

-- IS NOT NULL 不为空
SELECT * FROM `student` WHERE `address` IS NOT NULL

-- AND 和 且
SELECT * FROM `student` WHERE `address` IS NULL AND `id` < 10

联表查询

内连接（INNER JOIN）

-- 内连接
-- SELECT 字段名(字段名在两个表中重复时必须指定是哪个表中的) FROM 表 别名 INNER JOIN 表 别名 ON 连接表达式
SELECT s.*,`clazzname`,`version` FROM `student` s INNER JOIN `clazz` c ON s.clazzid = c.clazzid

INNER JOIN 表中只要有一个匹配、就返回行

左外连接(LEFT JOIN)

-- 左外连接
-- SELECT 字段名(字段名在两个表中重复时必须指定是哪个表中的) FROM 表 别名 LEFT JOIN 表 别名 ON 连接表达式
SELECT s.*,`clazzname`,`version` FROM `student` s LEFT JOIN `clazz` c ON s.clazzid = c.clazzid 

LEFT JOIN 会从左表中返回所行值、无论右表中是否匹配

右外连接(RIGHT JOIN)

-- 右外连接
-- SELECT 字段名(字段名在两个表中重复时必须指定是哪个表中的) FROM 表 别名 RIGHT JOIN 表 别名 ON 连接表达式
SELECT s.*,`clazzname`,`version` FROM `student` s RIGHT JOIN `clazz` c ON s.clazzid = c.clazzid 

RIGHT JOIN 会从右表中返回所有行值、无论左表中是否匹配

自连接

就是写在一个表中的关系、实质上可以写成两张表

-- 自连接
SELECT y.typename AS 父类,t.typename As 子类 FROM type AS t,type AS y WHERE t.pid = y.typeid 

分页和排序

排序

-- ORDER BY 排序 desc 降序 asc 升序(默认)
SELECT * FROM `student` ORDER BY `id` DESC

-- ORDER BY 排序 desc 降序 asc 升序(默认)
SELECT * FROM `student` ORDER BY `id` ASC

分页

-- LIMIT 起始位置(从0开始计算),每页显示的数量    
SELECT * FROM `student` ORDER BY `id` DESC LIMIT 0,5

-- 第一页 0,5
-- 第二页 5,5
-- 第三页 10,5

-- pageSize 页面大小   (n - 1) * pageSize 起始大小    n  当前页
-- 第n页 (n - 1) * pageSize, pageSize

-- 总页数 = 数据总数 / 页面大小 
-- 数据总数 = SELECT COUNT(*) FROM 表

pageSize 页面大小

(n - 1) * pageSize 起始大小

n 当前页

第n页 (n - 1) * pageSize, pageSize

总页数 = 数据总数 / 页面大小

数据总数 = SELECT COUNT(*) FROM 表

子查询

在WHERE 中 嵌套 一个子查询语句

-- 子查询   一个查询语句中嵌套另一个查询语句
SELECT * FROM `student` WHERE `clazzid` IN (SELECT clazzid FROM clazz WHERE clazzid = 1)

函数

数学函数

-- 数学函数
SELECT ABS(-10) -- 绝对值

SELECT CEILING(9.3) -- 向上取证

SELECT FLOOR(9.3) -- 向下取整

SELECT RAND() -- 随机数

SELECT SIGN(0) 0 -- 0
SELECT SIGN(-10) 负数 -- -1
SELECT SIGN(10) 整数 -- 1

字符串函数

-- 字符函数
SELECT CHAR_LENGTH('xiaobo') -- 字符串的长度

SELECT CONCAT('我','是','小','博') -- 字符串拼接

SELECT LOWER('XIAOBO') -- 字符串转成小写

SELECT UPPER('xiaobo') -- 字符串转成大写

SELECT REPLACE('xiaobo','xiao','XIAO') -- 字符替换

SELECT SUBSTR('xiaobo',2,3) -- 字符串截取

SELECT REVERSE('xiaobo') -- 反转

时间日期函数

-- 时间日期函数
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 COUNT(`pid`) FROM type -- 会忽略所有的NULL值

SELECT COUNT(*) FROM type -- 不会忽略NULL值 本质计算行数

SELECT COUNT(1) FROM type -- 不会忽略NULL值 本质计算行数

如果你的数据表没有主键，那么count(1)比count(*)快

如果有主键的话，那主键（联合主键）作为count的条件也比count()要快
如果你的表只有一个字段的话那count()就是最快的啦

count(*) count(1) 两者比较。主要还是要count(1)所相对应的数据字段。
如果count(1)是聚索引,id,那肯定是count(1)快。但是差的很小的。

因为count(),自动会优化指定到那一个字段。所以没必要去count(?)，用count(),sql会帮你完成优化的

分组和过滤

-- 分组
-- GROUP BY 分组字段
-- HAVING 过滤条件
SELECT s.`username`, SUM(`id`) AS '编号和',AVG(`id`) AS '平均编号',MAX(`id`) AS '最大编号', MIN(`id`) AS '最小编号' 
FROM `student` s INNER JOIN `clazz` c
WHERE s.clazzid = c.clazzid
GROUP BY s.`username`
HAVING AVG(`id`) > 10

数据库MD5加密

-- 插入时将密码设置MD5形式
-- MD5() 加密函数
INSERT INTO `student`(username,password,birthday,address,clazzid) VALUES('xiaobo',MD5('xiaobo'),'2001-04-06','辽宁省铁岭市昌图县',1),('xiaobo',MD5('xiaobo'),'2001-04-06','辽宁省铁岭市昌图县',1);

-- 更新时进行加密
UPDATE `student` SET `password` = MD5(`password`)

-- 查询MD5加密后的密码
SELECT * FROM student WHERE password = MD5('xiaobo')

正确的开始、微小的长进、然后持续、嘿、我是小博、带你一起看我目之所及的世界……