数据库查询
数据库（查询）
一，DQL数据库查询语言
DROP TABLE IF EXISTS student
go
CREATE TABLE student(
id INT(10) PRIMARY KEY,
`name` VARCHAR(10),
age INT(10) NOT NULL,
gender VARCHAR(2)
)
go
DROP TABLE IF EXISTS course
go
CREATE TABLE course(
id INT(10) PRIMARY KEY,
`name` VARCHAR(10),
t_id INT(10)
);
go
DROP TABLE IF EXISTS teacher
go
CREATE TABLE teacher(
id INT(10) PRIMARY KEY,
`name` VARCHAR(10)
);
go
DROP TABLE IF EXISTS score
go
CREATE TABLE score(
s_id INT(10),
score INT(10),
c_id INT(10),
PRIMARY KEY(s_id,c_id)
);单表查询
SELECT * FROM student
别名
SELECT id 学号,NAME 姓名 FROM student
SELECT id AS 学号,NAME AS 姓名 FROM student
#别名 列名后加去重
#表中有重复记录，用distinct，只显示一次
SELECT DISTINCT NAME FROM student列运算
SELECT id,age*10 FROM student
空值函数
#空值(null)和任何值做计算都为空,函数ifnull()
SELECT IFNULL(sal,0) FROM employee
#如果薪资是null就为0
-- 把字符串做加减乘除，会把字符串当作0条件查询
SELECT id,NAME FROM student
WHERE id IN(1,2,3) OR id BETWEEN 3 AND 5 模糊查询
SELECT id,NAME FROM student
WHERE NAME LIKE '张%'
# %匹配多个字符 _匹配一个字符
-- 这里需要加 ESCAPE 关键字进行转义。
-- ESCAPE 后面跟着一个字符，里面写着什么，
-- MySQL就把那个符号当做转义符，一般写成"/"；
-- 把这个字符写在你需要转义的那个%号前就；
-- SELECT * from app_info where appName LIKE '%/_%' ESCAPE '/';查询方法
locate()函数
#查询位置
SELECT LOCATE('a','bnma');#4
SELECT LOCATE(0,'123')#0
SELECT LOCATE(NULL,'123')#null
SELECT LOCATE('a',NULL)#null
-- 用LOCATE关键字进行模糊匹配，等同于："like '%网%'"
SELECT * FROM app_info WHERE LOCATE('网', `appName`) > 0;
-- 用LOCATE关键字进行模糊匹配, 从第二个字符开始匹配"网"，则"网易云游戏、网来商家"等数据就被过滤了
SELECT * FROM app_info WHERE LOCATE('网', `appName`, 2) > 0;
position方法
-- 用POSITION关键字进行模糊匹配，等同于："like '%网%'"
SELECT * from app_info where POSITION( '网' IN `appName`);
-- 这个方法可以理解为locate(substr，str)方法的别名，
-- 因为它和locate(substr，str)方法的作用是一样的。
instr()方法
-- 返回字符串str中第一次出现子字符串substr的位置。
-- INSTR()与LOCATE()的双参数形式相同，只是参数的顺序相反。
-- 用INSTR关键字进行模糊匹配，功能跟like一样 ,等同于："like '%网%'"
SELECT * from app_info where INSTR(`appName`, '网');
-- instr函数作用，一般用于检索某字符在某字符串中的位置,等同于："like '%网%'"
SELECT * from app_info where INSTR(`appName`, '网') > 0;二，MySQL基于regexp、rlike的正则匹配查询
MySQL中的regexp和rlike关键字属于同义词，功能相同。本文以regexp为准。
REGEXP 不支持通配符"%、_"，支持正则匹配规则
-- REGEXP '网' 等同于 like '%网%'
SELECT * from app_info where appName REGEXP '网';
-- 等同于
SELECT * from app_info where appName like '%网%';regexp中的 OR : |
功能：可以搜索多个字符串之一，相当于 or
-- 支持 "|" ‘或’符号，匹配包含“中国”或“互联网”或“大学”的数据，支持叠加多个
SELECT * from app_info where appName REGEXP '中国|互联网|大学';
-- 匹配同时命中“中国”、“网”的数据可以用".+"连接，代表中国xxxx网，中间允许有任意个字符，顺序不能反。
SELECT * from app_info where appName REGEXP '中国.+网';REGEXP中的正则匹配 : []
功能：匹配[]符号中几个字符之一，支持解析正则表达式
-- 匹配包含英文字符的数据，默认不区分大小写情况下
SELECT * from app_info where appName REGEXP '[a-z]';
-- 匹配包含大写英文字符的数据，默认忽略大小写，需要加上"BINARY"关键字。如where appName REGEXP BINARY 'Hello'
-- （从版本3.23.4后）不区分大小写 。
-- 查询以5、6、7其中一个为开头的数据
SELECT * from app_info where appName REGEXP '^[5|6|7]';
-- 查询appName字节长度为10，任意内容的数据
SELECT * from app_info where appName REGEXP '^.{10}$';
-- 查询appName字节长度为10，且都为大写英文的数据，加上BINARY即可
SELECT * from app_info where appName REGEXP BINARY '^[A-Z]{10}$';
排序
-- 降序
SELECT * FROM student ORDER BY age DESC
-- 升序，默认是升序
SELECT * FROM student ORDER BY age ASC
-- 使用多列为排序条件：当第一个排序条件相同时，根据第二个条件查询
SELECT * FROM student ORDER BY age ASC ,id DESC
聚合函数
count
查询满足条件的记录行数，为空不进行统计
select count(列名) from 表;
max
查询满足条件的最大值
select max(age) from student where name='1'
min
查询满足条件的最小值
select min(age) from student where name='1'
sum
求和
select sum(age) from student where name='1'
avg
平均数
select sum(score) from course where c_id=3
SQL 中增加 HAVING 子句，WHERE 关键字无法与聚合函数一起使用。
分组查询
select 分组列名，聚合函数，聚合函数2... from 表名
where 查询条件
group by 分组列名
having 聚合函数或列名（条件）
-- 查询得的列只是分组后的列 
分页查询
limit
用来限定查寻结果的起始行，以及总行数，在sql语句最后位置
select * from student limit 4,3;
-- 从第四行开始，包含3行
select * from student limit 4;
-- 若至于一个参数，从从起始行开始查找4条记录
MySql,Oracle,SqlServer的分页关键字
- mysql：limit
- oracle：rownum
- sqlserver：top
查找student表中有没有交‘小红’的人
select * from student where name ='小红'
select count(id) from student where name ='小红'
select count(id) from student where name ='小红' limit 1
三，多表查询
笛卡尔积
SELECT * FROM student,teacher
多表连接的方式
1.内连接
2.外连接
3.全连接
4.子查询
SQL92语法
#内连接，查询姓名，成绩
SELECT stu.`name` 学号,sc.score 成绩
FROM student stu,score sc
WHERE stu.`id`=sc.`s_id`;多表查询中，为null的值会过滤掉，-- 先生成笛卡尔积，在做筛选
-- 查询学号，分数，课程
SELECT stu.`name` 学号,sc.score 成绩,c.name 科目
FROM student stu,score sc,course c
WHERE stu.`id`=sc.`s_id` AND c.id = sc.c_id; 案例：
-- 查询学号，分数，课程
SELECT stu.`name` 学号,sc.score 成绩,c.name 科目
FROM student stu,score sc,course c
WHERE stu.`id`=sc.`s_id` AND c.id = sc.c_id;
go
-- 查询学号，姓名，年龄，科目，student,score,course
-- 筛选小红与小军的java成绩
SELECT stu.id ,stu.name,stu.age,c.name,sc.score
FROM student stu,score sc,course c
WHERE stu.`id`=sc.`s_id` AND c.id = sc.c_id
AND (stu.name IN ('a','b')) AND c.name='java'
go
-- 查询学号，姓名，年龄，科目，student,score,course
-- 找出最高最低分，按照科目分组
SELECT c.name,MAX(sc.score),MIN(sc.score)
FROM student stu,score sc,course c
WHERE stu.`id`=sc.`s_id` AND c.id = sc.c_id
GROUP BY c.name
SQL99语法
内连接
使用join关键字，使用on确定连接条件，where只做筛选条件
-- 内连接
SELECT t.*,c.*
FROM teacher t inner JOIN course c ON t.id=c.t_id-- 但为空的值任然不显示
外连接
内外连接的区别
- 对于内连接的两个表，驱动表在被驱动表找不到与之匹配的记录，最终结果不会出现在结果集中
- 对于外连接的两个表，即使驱动表的记录在被驱动表中找不到与之匹配的记录，也会把该记录显示
- 针对不同驱动表的位置，分为左外连和右外连接
- 对于左外连，左边的表为主，左边的表的记录会完整的显示
- 对于右外连，右边的表为主，右边的表的记录会完整的显示
外连接关键字【outter join】,也可省略outter
左外连接
-- 左外连接
SELECT t.*,c.*
FROM teacher t LEFT JOIN course c ON t.id=c.t_id右外连接
-- 右外连接
SELECT t.*,c.*
FROM teacher t right JOIN course c ON t.id=c.t_id
全连接
mysql不支持全连接，oracle支持全连接
SELECT t.*,c.*
FROM teacher t FULL JOIN course c ON t.id=c.t_id
联合查询
-- 联合查询
SELECT t.*,c.*
FROM teacher t LEFT JOIN course c ON t.id=c.t_id
UNION
SELECT t.*,c.*
FROM teacher t RIGHT JOIN course c ON t.id=c.t_id
浙公网安备 33010602011771号