Mysql基础:DQL数据库查询语言
1、基础概念
DB:数据库
DBMS:数据库管理系统(数据库软件),mysql是一种数据库管理系统。用来管理数据库。常见的数据库管理系统:mysql、oracle、DB2。
SQL:结构化查询语言,人与数据库交互的语言。
DBMS分为两类
- 基于共享文件系统的DBMS(Access)
- 基于客户机 服务器的DBMS(mysql,oracle)
1、mysql服务的启动和停止
-
启动服务:
net start 服务名
net start mysql
-
停止服务:
net stop 服务名
net stopmysql
2、mysql服务的登录和退出
-
使用mysql自带的客户端登录**(只适用于root用户)**
-
登录mysql服务:
mysql -h localhost -P 3306 -u root -p
说明:-h mysql服务器主机地址
-P mysql服务器端口号
-u mysql用户
-p 用户密码(后面不可以跟空格)
-
退出mysql服务
ctrl + c
quit;
exit;
3、mysql的配置文件
1、mysql的全局配置文件如下
mysql的客户端配置
mysql的服务器配置
修改完该配置文件的mysql
服务器必须重新启动。
4、mysql常用命令
-
show databases; 查看所有数据库
-
use 数据库名; 指定使用某个数据库
-
show tables [ from 数据库名 ];查看某个数据库中有哪些表,其中中括号的内容可以省略,表示查看本库有哪些表
-
select database();查看当前在哪个数据库中。
-
desc 表名;查看表的结构**(desc是description[描述]的意思)**
-
mysql version(); 查询数据库的版本。这个命令是在登录mysql服务器执行的。
-
mysql --version;在cmd中执行该命令。注意这里是双杠(–)
-
mysql -V;在cmd中执行该命令。
5、mysql语法规范
-
不区分大小写,建议关键字大写,表名、列名小写;
-
每条语句分号结尾。
-
注释
-
单行注释
-
#注释内容
-
– 注释内容 这里有个空格不能省略
-
-
-
多行注释
- /* 注释内容 */
2、sql语言
1、DQL语言学习,DQL(data query language
)
都是一堆查询语言
1、基础查询
语法:select 查询列表 from 表名
1、查询列表可以是啥:
-
表中的字段
SELECT book_name , sales , stock FROM book;
-
常量值
SELECT 100; SELECT 'Tom';
-
表达式
SELECT 100*98;
-
函数
SELECT VERSION();
查询的结果是一个虚拟的表格
1、给字段起别名
方式一:as
SELECT book_name AS '书名' FROM book;
方式二:使用空格
SELECT book_name '书名' FROM book;
1、查询结果去重(DISTINCT关键字)
SELECT book_name '书名' FROM book;
2、+号的作用
注意:mysql中的+
号只有一个运算符的功能。
- select 100+90;当两个操作都为数值型,则进行加法运算
- select ‘123’+20;当两个操作数的中的存在字符串时,则试图将字符串转化为数值类型,如果转化成功则继续做加法运算。
- select ‘tom’+100;如果转化失败则将字符串转换为数值0,进行运算。
- select null+100;只要其中一个操作数为null,结果就是null。
mysql如果想将多个字段连接为一个字段,则使用concat(str1,str2,…)函数进行操作
SELECT CONCAT(book_id,book_name) AS '书编号名' FROM book;
2、条件查询
select 查询列表 from 表名 where 筛选条件
执行顺序:先 from 表名,然后where 筛选条件,然后select查询。也就是先看数据库中是否存在该表,然后where进行数据的筛选,然后select将筛选后的数据显示出来。
筛选条件分类
-
条件表达式
-
大于 >
-
小于 <
-
等于 =
=
这个符号不能判断字段是等于null
SELECT * FROM stuinfo WHERE NAME=NULL; #该语句是不能查出任何结果的
-
不等于 <> 或者 != :这个符号不能判断字段是不等于
null
SELECT * FROM book WHERE book_price > 30;
-
-
逻辑表达式
- && and
- || or
- ! not
-
模糊查询
-
like
%:匹配任意多个字符(包含0个字符)
_:任意单个字
-
between and
- between and:包含临界值
-
in
- in 关键字等价于‘=’和‘or’的联合使用
- in不支持通配符
- in列表的值的类型必须统一或兼容
SELECT * FROM `user` WHERE usernameIN('LiBai','BaiJuYi','SunWuKong');
-
is null:由于
=
符号不能判断是否等于null
,所以使用is null
来判断字段是否为null
。SELECT * FROM stuinfo WHERE NAME IS NULL;
-
is not null :与 is null 的作用相反
SELECT * FROM stuinfo WHERE NAME IS NOT NULL;
-
安全等于 <=> :即可以判断
null
值,也可以判断非null
值。#判断等于NULL值 SELECT * FROM stuinfo WHERE `name` <=> NULL; #判断等于非NULL值 SELECT * FROM stuinfo WHERE `name` <=> 'tom';
-
3、排序查询
语法:
select 查询列表 from 表名 【wherr 筛选条件】order by 排序列表 【asc | desc】
asc 升序(默认,可省略)
desc 降序
order by 子句一般放在查询语句的最后,limit
子句除外。
order by
查询子句的顺序
首先from
表,然后【where
筛选条件】然后select
子句,最后order by
子句。即先看表是否存在,然后where进行数据筛选,然后select进行数据查询,最后order by将查询数据进行排序。
#普通排序
SELECT * FROM book ORDER BY book_price DESC;
#附加条件的排序查询
SELECT * FROM book WHERE stock > 20 ORDER BY book_price DESC;
#多个字段进行排序查询
SELECT * FROM book ORDER BY book_price DESC , sales ASC;
4、常见函数
概念:将逻辑语句封装在方法中,对外暴露方法名。
好处:重用性高
如何调用函数?
语法:select 函数名(实参列表) 【from 表名】
常见函数分类:
- 单行函数:做处理使用,有返回值
- 分组函数:做统计使用,又称统计函数,聚合函数,组函数。
1、单行函数
1、字符函数:处理字符,参数类型为字符
-
length( str ) :获取字符(串)所占字节个数,与字符编码有关
SELECT LENGTH('黄蓉');
-
concat(str1,str2,…):将所有的参数拼接在一起
SELECT CONCAT('李','_','无名'); SELECT CONCAT(book_name,'_',book_id)FROM book;
-
upper(str):将传入字符(串)全部大写
-
lower(str):将传入字符(串)全部小写
SELECT CONCAT(UPPER('li'),'_',LOWER('TOM'));
-
substr / substring:截取字符,mysql字符的索引都是从1开始的
#截取从下标4的开始到末尾的所有字符 SELECT SUBSTR('郭靖与黄蓉',4); #截取从下标1开始的长度为2的字符 SELECT SUBSTR('郭靖与黄蓉',1,2);
-
instr(str1,str2):返回str2在str1中第一次出现的索引,若没有出现返回0.
SELECT INSTR('郭靖与黄蓉','黄蓉') AS outIndex;
-
trim:去除字符首尾的某个字符
#去除字符首尾空格----->郭靖 SELECT TRIM(' 郭靖 ') AS out_put; #去除字符首尾的‘d’字符----->郭d靖 SELECT TRIM('d' FROM 'dddddd郭d靖dddd') AS out_put; #去除字符首尾的’dd‘字符,‘dd’字符为一个整体----->d郭d靖d SELECT TRIM('dd' FROM 'ddddddd郭d靖ddddd') AS out_put;
-
lpad:用指定字符实现左填充到指定长度,若字符原本长度大于指定长度,则进行有切割字符
SELECT LPAD('郭靖',10,'*');
SELECT LPAD('郭靖',1,'*');
SELECT LPAD('郭靖',10,'abc');
-
rpad:由于lpad相似,右填充
-
replace(str1,str2,str3):将str1中的所有str2替换为str3
SELECT REPLACE('张无忌与周芷若,周芷若,周芷若','周芷若','赵敏') AS out_put;
2、数学函数
-
round 四舍五入
#四舍五入取整 SELECT ROUND(1.55); SELECT ROUND(-1.55); #四舍五入,保留指定小数位数 SELECT ROUND(1.7563,2);
-
ceil:向上取整,返回大于等于该参数的最小整数
SELECT CEIL(1.00);# 1 SELECT CEIL(1.02);# 2 SELECT CEIL(-1.09);# -1
-
fllor:向下取整,返回小于等于该参数的最大整数
SELECT FLOOR(1.00);# 1 SELECT FLOOR(1.02);# 1 SELECT FLOOR(-1.09);# -2
-
truncate:数字截断,将指定位数后面的小数全部截取掉
#1.69 SELECT TRUNCATE(1.699,2); #-1.69 SELECT TRUNCATE(-1.699,2);
-
mod 取余,等价于%
#1 SELECT MOD(10,3); #1 SELECT 10%3; #1 SELECT MOD(10,-3); #-1 SELECT MOD(-10,3);
3、日期函数:处理日期,参数类型为日期
-
now():返回当前系统日期+时间
SELECT NOW();
-
curdate():返回当前日期,不包含时间
SELECT CURDATE();
-
curtime():返回当前时间,不包含日期
SELECT CURTIME();
-
获取日期的指定部分
SELECT YEAR(NOW()); SELECT YEAR('2018-09-09'); SELECT YEAR(`create_time`) FROM `order`; #返回月份,数字 SELECT MONTH(NOW()); #返回月份,英文 SELECT MONTHNAME(NOW());
-
str_to_date:将日期格式的字符转换成指定格式的日期
SELECT STR_TO_DATE('1998-08-17','%Y-%c-%d');
-
date_format:将日期转换为字符
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日');
4、其他函数:
#查看数据库版本
SELECT VERSION();
#查看当前使用的那个数据库
SELECT DATABASE();
#查看当前用户
SELECT USER();
5、流程控制函数
-
if(expr1,expr2,xepr3):如果expr1条件成立返回expr2的值,如果expr1不成立,返回expr3的值。
SELECT IF(10>5,'大于','小于');
-
case 语句
语法1:类似switch - case
case 要判断的字段或表达式 when 常量1 then 要显示的值1或语句1; when 常量2 then 要显示的值2或语句2; .... else 要显示的值n或语句n; end
举例:
/* 当书名为 西游记 显示 名著 当书名为 浪潮之巅 显示 计算机 当书名为 射雕英雄传 显示 小说 其他书籍显示 good book */ SELECT *, CASE book_name WHEN '西游记' THEN '名著' WHEN '浪潮之巅' THEN '计算机' WHEN '射雕英雄传' THEN '小说' ELSE 'good book' END FROM book;
语法2:类似多重if
case when 条件1 then 要显示的值1或语句1; when 条件2 then 要显示的值2或语句2; ..... else 要显示的值n或语句n; end
举例
/* 书的价钱小于等于 40,显示价钱为 廉价 书的价钱大于40小于80,显示价钱为 平价 书的价钱大于等于80,显示价钱为 昂贵 */ SELECT *, CASE WHEN book_price <=40 THEN '廉价' WHEN book_price >40 AND book_price <80 THEN '普通' WHEN book_price >=80 THEN '高价' END FROM book;
6、分组函数
分组函数做统计功能使用
1、分类
- sum:求和
- avg:平均值
- max:最大值
- min:最小值
- count:计算非空值个数
SELECT SUM(book_price) ,MAX(book_price) ,MIN(book_price) ,AVG(book_price) ,COUNT(book_price) FROM book;
2、分组函数特点
sum,avg 处理数值型
max,min,count可以处理任何类型
count 只计算非空值的个数
以上分组函数都忽略NULL值
3、count函数的详细介绍
#统计表中记录行数
SELECT COUNT(*) FROM book;
#往查询结果添加一列‘1’,然后统计其个数。也就是查询表记录行数
SELECT COUNT(1) FROM book;
#统计字段book_price非空值的个数
SELECT COUNT(book_price) FROM book;
#效率
MYISAM存储引擎下,count(*)的效率高
INNODB存储引擎下,count(*) 和 COUNT(1)效率差不多,但比count(字段)效率高
一般使用count(*)用作统计行数
注意:以上几种方式可以统计表的记录行数,但是他们有效率问题。
- MYISAM存储引擎下,count(*)的效率高
- INNODB存储引擎下,count() 和 COUNT(1)效率差不多,但比count(字段)效率高
- 一般使用count(*)用作统计行数
4、和分组函数一同查询的字段要求是group by后的字段
5、分组查询
语法:
SELECT 分组函数,列(要求出现在 GROUP BY 的后面)
FROM 表
【 WHERE 筛选条件 】
GROUP BY 分组的列表
【 ORDER BY 子句 】
#注意:查询列表必须特殊,要求是分组函数和 group by 后出现的字段
SELECT MAX(salary),job_id FROM employees GROUP BY job_id;
1、having对分组后的结果进行筛选
说明:
SELECT 分组函数,列(要求出现在 GROUP BY 的后面)
FROM 表
【 WHERE 筛选条件 】
GROUP BY 分组的列表
【 ORDER BY 子句 】
上面的语法中的 where 可以提供一个筛选条件,但是筛选条件中的字段必须是原表中存在的字段。
那么我们如果想对分组的结果进行筛选的时候必须使用 having 关键字了。
语法:
SELECT 分组函数,列(要求出现在 GROUP BY 的后面)
FROM 表
GROUP BY 分组的列表
HAVING 分组结果的列
【 ORDER BY 子句 】
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;
总结:
- where 只对分组前的原表进行条件筛选。
- having支队分组后的结果集进行条件筛选
分组查询的筛选条件分为两类
数据源 关键字 关键字位置
分组前筛选 原始表 where group by 子句前面
分组后筛选 分组后的结果集 having group by 子句后面
分组函数做条件肯定放在 having 子句中。
能用分组前筛选的优先使用分组前筛选,基于性能考虑。
2、按表达式或函数分组
SELECT COUNT(*) ,LENGTH(last_name)
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*)>5;
3、按多个字段分组
SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mSF7mOiA-1600522900614)(C:\Users\21232\AppData\Roaming\Typora\typora-user-images\1596547900606.png)]
4、分组查询添加排序
SELECT AVG(salary),department_id,job_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id,job_id
ORDER BY AVG(salary) DESC;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-O6qDoSZW-1600522900615)(C:\Users\21232\AppData\Roaming\Typora\typora-user-images\1596548138127.png)]
6、连接查询
连接查询又称多表查询
1、笛卡尔乘积
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Dprb4uNK-1600522900616)(C:\Users\21232\AppData\Roaming\Typora\typora-user-images\1596550367576.png)]
发生笛卡尔集错误现象的原因,因为没有有效的连接条件。
添加有效连接条件即可解决问题。
SELECT `name`,boyName FROM boys,beauty WHERE boyfriend_id=boys.id;
2、连接查询分类
1、按年代分类:
- sql92标准,仅仅支持内连接
- sql99标准【推荐】支持所有内连接,外连接(全外连接除外),交叉连接
2、按功能分类
- 内连接
- 等值连接
- 非等值连接
- 自连接
- 外连接
- 左外连接
- 右外连接
- 全外连接
- 交叉连接
3、SQL92内连接(SQL92仅支持内连接)
1、等值连接
SELECT last_name,department_name
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`
注意:如果给表名起了别名,则查询字段不能使用表的原名去限定。
2、非等值连接
SELECT salary,grade_level
FROM employees,job_grades
WHERE salary BETWEEN lowest_sal AND highest_sal;
3、自连接
在同一个表中进行等值连接
SELECT e.employee_id,e.last_name,m.`employee_id`,m.last_name
FROM employees AS e,employees AS m
WHERE e.`manager_id`=m.`employee_id`;
4、SQL99连接查询
SQL99支持的连接类型
- 内连接
- 外连接
- 左外
- 右外
- 全外
- 交叉连接
sql99语法:
SELECT 查询列表
FROM 表1 别名 【连接类型】
JOIN 表2 别名
ON 连接条件
【 WHERE 筛选条件 】
【 GROUP BY 分组 】
【 HAVING 筛选条件 】
【 ORDER BY 排序列表 】
连接类型分类
- 内连接:inner
- 外连接
- 左外:left 【outer】
- 右外:right 【outer】
- 全外:full 【outer】
- 交叉连接:cross
1、内连接
1、多表内连接
SELECT last_name,department_name,job_title
FROM employees
INNER JOIN departments ON employees.`department_id`=departments.`department_id`
INNER JOIN jobs ON employees.`job_id`=jobs.`job_id`
ORDER BY department_name DESC;
2、自连接
SELECT e.last_name,m.last_name
FROM employees e
INNER JOIN employees m ON e.`manager_id`=m.`employee_id`
WHERE e.`last_name` LIKE '%k%';
2、外连接
应用场景:用于查询一个表有,另一个表没有
外连接既具有主从表的关系,主表的所有记录都会被显示出来,从表要是能和主表匹配,则显示,若是无法匹配则显示null。
特点:
- 外连接的查询结果为主表的所有记录,如果从表中有和他匹配的,则显示匹配的值,如果从表中没有和他匹配的,则显示null。
- 外连接的查询结果=内连接结果+主表中有而从表没有的记录
- 左外连接,left join左边的是主表
- 右外连接,right join右边的是主表
- 左外和右外交换两个表的顺序,可以实现同样的效果
- 全外连接=内连接结果+表1中有但表2中没有的+表2中有但表1中没有的
示例:左外连接,右外连接
SELECT department_name,departments.`department_id`
FROM departments
LEFT OUTER JOIN employees
ON employees.`department_id` = departments.`department_id`
WHERE employees.`employee_id` IS NULL;
全外连接:mysql不支持全外连接,所以这里写一下语法。
3、交叉连接
交叉连接就是一个笛卡尔乘积。
SELECT *
FROM beauty
CROSS JOIN boys;
5、SQL99与SQL92大PK
- sql99支持的较多
- sql99可读性高
6、连接查询图示
- 内连接
- 左外、右外
- 全外(mysql不支持)
7、子查询(有难度)
子查询:出现在其他语句中的select语句,称为子查询或内查询。外部的查询语句称为主查询或外查询。
外部的语句可以是insert,update,delete,select。
1、分类:
-
按子查询出现的位置:
-
select 后面
:仅仅支持标量子查询
-
from 后面
:支持表子查询
-
where或haing后面★
:标量子查询(单行)√,列子查询(多行)√,行子查询
-
exists后面(相关子查询)
:支持表子查询
-
-
按结果集的行列数不同:
- 标量子查询(结果集只有一行一列)
- 列子查询(结果集只有一列多行)
- 行子查询(结果集有一行多列)
- 表子查询
2、where或having后面的子查询
1、标量子查询(单行子查询)
2、列子查询(多行子查询)
3、行子查询(多行多列)子查询
特点:
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询,一般搭配单行操作符(>、<、>=、<=、=、<>)使用
④列子查询,一般搭配多行操作符(in,any,some,all)使用
⑤子查询优先于主查询执行,主查询的条件用到了子查询的结果。
1、标量子查询
#案例一:谁的工资比Abel高?
#①查询Abel的工资
SELECT salary FROM employees WHERE last_name = 'Abel';
#②查询员工信息满足 salary >①的结果
SELECT *
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);
#案例:返回job_id与141号员工相同,salary比143号员工多的员工姓名、job_id和工资。
#①查询141号的job_id
SELECT job_id
FROM employees
WHERE employee_id = 141;
#②查询143号员工的salary
SELECT salary
FROM employees
WHERE employee_id=143;
#③查询员工的姓名、job_id、工资,要求job_id=① 并且salary>②
SELECT last_name,job_id,salary
FROM employees
WHERE job_id=(
SELECT job_id
FROM employees
WHERE employee_id = 141
)AND salary > (
SELECT salary
FROM employees
WHERE employee_id=143
);
SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT MIN(salary)
FROM employees
WHERE department_id=50
);
2、行子查询
SELECT *
FROM employees
WHERE (employee_id,salary)=(
SELECT MIN(employee_id ),MAX(salary)
FROM employees
);
3、放在select后面的子查询
SELECT d.*,(
SELECT COUNT(*)
FROM employees AS e
WHERE e.department_id=d.`department_id`
) AS 个数
FROM departments AS d;
4、放在from后面的子查询
#查询每个部门的平均工资等级
SELECT ag_dep.*,g.`grade_level`
FROM (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag BETWEEN g.`lowest_sal` AND g.`highest_sal`;
5、放在exists后面的子查询(相关子查询)
exists语法:exists(完整的查询语句)
exists结果为布尔类型,为1或0;
#案例:查询有员工名部门名
SELECT department_name
FROM departments
WHERE EXISTS(
SELECT *
FROM employees
WHERE employees.`department_id`=departments.`department_id`
);
8、分页查询☆
语法:
select 查询列表
from 表
【join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的字段
order by 排序字段】
limit offset,size;
offset要显示条目的起始索引(起始索引从0开始)
size 要显示的条目个数
特点:
①limit语句放在查询语句的最后
②公式
要显示的页数:page,每页的条目式:size
select 查询列表 from 表 limit (page-1)*size,size;
select 语句的顺序
④ select 查询列表
① from 表
②【join type join 表2 on 连接条件
③ where 筛选条件
⑤ group by 分组字段
⑥ having 分组后的筛选
⑦ order by 排序字段】
⑧ limit offset,size;
上面的每一步都会形成虚拟的表格。
9、联合查询
关键字:union,将多条查询语句的结果合并成一个结果
联合查询语法:
查询语句1
union
查询语句2
联合查询的特点:
①要求每条查询语句的查询列数相同。
②使用union关键字进行联合查询会去除重复列,使用union all关键字进行联合查询,不会去除重复列。
9、mysql函数
函数 | 作用 |
---|---|
ifnull(expr1,expr2) | 判断expr1是否为null,,如果为null,则返回expr2的值。不为null,返回本身。 |
datediff(expr1,expr2) | 计算expr1代表日期和expr2代表日期相差的天数 |
注意:
1、mysql字符必须使用单引号包围
2、mysql使用斜杠\
进行字符的转义。
SELECT * FROM `user` WHERE username LIKE '_\_%';
3、使用escape
关键字指定转义字符。(推荐使用)
SELECT * FROM `user` WHERE username LIKE '_$_%' ESCAPE '$';
from 表 limit (page-1)*size,size;
select 语句的顺序
④ select 查询列表
① from 表
②【join type join 表2 on 连接条件
③ where 筛选条件
⑤ group by 分组字段
⑥ having 分组后的筛选
⑦ order by 排序字段】
⑧ limit offset,size;
上面的每一步都会形成虚拟的表格。