MySql学习--基础->条件->排序->函数->分组->表连接

1.查看当前所有数据库

show databases;

2.打开指定的库

use 库名

3.查看当前库的所有表

show tables;

4.查看其他库的所有表

show tables from 库名;

5.创建表

create table 表名(
  列名 列类型,
  列名 列类型,
  ...
);

6.查看表结构

desc 表名;

7.查看服务器版本
方式一：登录到mysql服务端

select version();

方式二：没有登录到mysql服务端

mysql --version
student

1、基础查询

8.起别名（as可省略）

select sname as 姓名 from student;
select sname 姓名 from student;

9.查询字段

select sname as 姓名,classNum as 班级编号 from srudent;

10.查询所有字段

select * from student

11.查询常量

select 100 as 常量

12.查询表达式

select 100%98 as 表达式结果

13.查询函数

select version() as 函数结果

14.拼接字段
+号的作用：

select 100 + 90; #结果为190，在SQL中+为运算符，在java中，+号既可以做运算符，又可以做连接符
select '100' + 90; #结果为190，试图将字符型转化为数值型
select 'like' + 90; #结果为90，字符型转换不成数值型，便是0
select null + 90; #结果为0，只要一方为null，结果为null

concat函数的作用：拼接字符串

select concat(sname,ssex) as 姓名性别 from student

15.去重

select distinct classNum as 班级编号 from student

16.ifnull(args1,args2)函数

select ifnull(sbirthday,0) from student #查看学生表里的生日，如果生日为null，那么将用0代替null

2、条件查询

简单条件运算符：>、<、=、!=、<>、>=、<=
逻辑运算符：&&、||、!、and、or、not
模糊查询：like、between...and...、in、is null
简单查询：

select sname as 姓名 from student where classNum <> 102; #查询班级编号不等于102的学生姓名
select * from student where sno >= 03 and sno <= 07; #查询学生编号在03-07之间的学生信息
select * from student where sno < 03 or sno > 07 or classNum = 102;#查询学生编号不是在03-07之间的信息或者班级为102的学生信息

逻辑查询
还可以这样写：

select * from student where not(sno>=03 and sno <=07) or classNum = 102;

模糊查询：%表示通配符，可以表示任意多个字符，包括0个，_也表示通配符，不过下划线只能表示一个字符

select * from student where sname like '娃%'; #查询头部为娃的同学信息
select * from student where sname like '%娃'; #查询尾部为娃的同学信息
select * from student where sname like '%娃%'; #查询包含娃字符的同学信息
select * from student where sname like '_娃%'; #查询第二个字符为娃的同学信息

如果模糊查询条件里有的条件，比如查询第二个字符为的数据，那就要对第二个_进行转义

select * from student where sname like '_\_%';
select * from student where sname like '_$_%' escape '$'; #自定义转义字符

select * from student where sno between 03 and 05; #包含临界值，不要调换两个临界值的顺序，完全等价于>=03 <=05

select * from student where sno in(03,04,05); #in后面加的是个集合，in列表的值必须兼容或者一致

=和我<>符号不能判断null值，要用is null和is not null

select * from student where sbirthday is null; #只能判断null，不能判断其他数据类型

安全等于<=>，既可以判断Null又可以判断正常数据类型

select * from student where sbirthday <=> null;
select * from student where sname <=> '大娃';

3、 排序查询

select * from student order by sbirthday desc; #降序排列
select * from student order by sbirthday asc; #升序排列
select * from student order by sbirthday; #默认是升序

SELECT CONCAT(last_name,first_name) AS 姓名,department_id AS 部门号,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪 
FROM employees 
ORDER BY 年薪 DESC ,ORDER BY 姓名 ASC;
SELECT CONCAT(last_name,first_name) AS 姓名,salary
FROM employees
WHERE salary < 8000 OR salary > 17000 ORDER BY salary DESC;
SELECT * FROM employees
WHERE email LIKE '%e%' ORDER BY LENGTH(email) DESC ,ORDER BY partment_id ASC;

4、函数

单行函数：concat、length、ifnull等
分组函数：做统计使用，又称为统计函数、聚合函数、组函数

4.1、常见函数

length():查看里面的str的字节个数，utf8下一个英文占一个字节，一个中文占3个字节
select length('John');
select length('哈哈哈hahaha');

concat():拼接字符串
select concat(sname,classNum) as 姓名班级 from student;

upper():转大写 lower():转小写
select upper('John');
select lower('John');

substr():截取字符串
SQL中，索引从1开始，不像java索引从0开始
select substr('杨过和小龙女',4); #小龙女
select substr('杨过和小龙女',3,4); #和小龙女，3表示从哪里开始，4表示截取的个数，不是结束索引

instr():查询子字符串第一次出现的时候的索引
select instr('杨过和小龙女和杨过','杨过'); 

trim():去掉多余的空字符
select trim('    杨过    ');
select trim('a' from 'aaaaaa杨aaaaa过aaaaaaaaa');#去掉有效字符的前后的a字符，杨过中间的a为有效字符

lpad():左填充 rpad右填充
select lpad('白素贞',10,'*'); #*******白素贞，字符不够的左边填充*

replace():替换字符串
select replace('张无忌爱上了周芷若','周芷若','赵敏');

数学函数

round():四舍五入
select round(-1.55);
select round(-1.567,2); #保留两位

ceil():向上取整
select ceil(1.00); #1
select ceil(1.02); #2
select ceil(-1.02); #-1

floor():向下取整
select floor(1.00); #1
select floor(1.02); #1
select floor(-1.02); #-2

truncate():截断
select truncate(1.566666,1); #从小数点后一位进行截断，1.5

mod():取模就是去余数 mod(a,b) : a-a/b*b;被除数为正，结果为正，被除数为负，结果为负
select mod(10,3); #1
select 10%3; #1

日期函数

select now(); #返回系统当前日期+时间
select curdate(); #返回系统当前日期
select curtime(); #返回系统当前时间
select year(now()); #当前年份
select datediff('2020-04-06','2020-04-07'); #查看两个日期的相差天数

其他函数

select version(); #查看当前版本
select database(); #查看当前数据库
select user(); #查看当前用户

流程控制函数

if(表达式,true取值,false取值)
select if(10>5,'大','小');

case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
...
else 要显示的值n或语句n；
end
案例：查询员工的工资，要求
部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍
其他部门，显示的工资为原工资
select salary as 原始工资,department_id,
case department_id
when 30 then salary*1.1
when 40 then salary*1.2
when 50 then salary*1.3
else salary
end as 新工资
from employees

case
when 表达式1 then 要显示的值1或者语句1;
when 表达式2 then 要显示的值2或者语句2;
...
else 要显示的值n或者语句n
end as 等级
from employees
select salary,
case 
when salary>20000 then 'A'
when salary>15000 then 'B'
when salary>10000 then 'C'
else 'D'
end as 等级
from employees

SELECT NAME,price,
CASE
WHEN price>5000 THEN '贵'
WHEN price>3000 THEN '一般'
WHEN price>1000 THEN '便宜'
ELSE '小于1000'
END AS 等级
FROM route ORDER BY price desc

4.2、分组函数

特点：
1、sum、avg一般处理数值型
max、min、count可以处理任意类型
2、0+Null都等于Null，以上分组函数都忽略null值
3、可以和distinct配合使用
4、和分组行数一同查询的字段有限制，因为分组行数查询出来的是一行数据，字段却有很多行数据，最终只会显示一行，和分组函数一同查询的字段要求是group by 后的字段，group by的意思是分组，可以将下过单的用户分组，分别查询每个用户下单的平均值，不然为啥叫分组行数呢，哈哈哈

订单表
sum()：计算和
select sum(totalPrice) from orders
avg()：计算平均值
select avg(totalPrice) from orders
max()：计算最大值
select max(totalPrice) from orders
min()：计算最小值
select min(totalPrice) from orders
count()：计算该字段的非空个数
select count(totalPrice) from orders

可以合并：
SELECT SUM(totalPrice) 总价,ROUND(AVG(totalPrice)) 平均值,MAX(totalPrice) 最大值,MIN(totalPrice) 最小值,COUNT(totalPrice) 总个数 FROM orders
也可以查时间的最早和最晚
SELECT MAX(orderTime) 最晚下单时间,MIN(orderTime) 最早下单时间 FROM orders;
SELECT SUM(DISTINCT totalPrice) 去重后,SUM(totalPrice) 去重前 FROM orders;
image.png

查看有多少人下过单：去重、求和

SELECT COUNT(DISTINCT userId) FROM orders;
image.png

查看每个用户下单的钱的总和

SELECT SUM(totalPrice),userId FROM orders GROUP BY userId
image.png

查看最晚下单和最早下单相隔的天数

SELECT DATEDIFF(MAX(orderTime),MIN(orderTime)) FROM orders;
image.png

查看自己活了多少天

SELECT DATEDIFF(NOW(),'1998-07-24') AS 活了多少天
image.png

补充：

select count(*) from orders;
select count(1) from orders;

都表示查询所有行数的个数、
MYISAM存储引擎下，count()的效率高，因为该引擎下有计数器，可以直接返回行数
INNODB存储引擎下，count()和count(1)的效率差不多，比count(字段)要高一些

5、分组查询

基础格式

select 分组函数,分组字段 from 表名 [where 查询条件] group by 分组字段

简单分组查询：将一列字段进行分组，同样的放在一块

SELECT userId FROM orders GROUP BY userId
image.png

一般将分组函数和分组查询一块使用
比如查询每人的平均消费值

SELECT AVG(totalPrice) AS 平均消费,userId FROM orders GROUP BY userId
image.png

复杂一点的分组查询：
查询下单超过两单的用户
分析：第一、先按照用户分组，第二再去判断下单数是不是大于2
where后面指定查询条件指的是原表的，这里是在一个分组查询后的新表做判断，所用条件用having来连接，放在分组后
第一步

SELECT COUNT(*),userId FROM orders GROUP BY userId;
image.png

第二步

SELECT COUNT(*),userId FROM orders GROUP BY userId HAVING COUNT(*) > 2;
image.png

涉及到分组查询的条件时，如果条件在原表中有可以用where（在group by前面），如果在分组后的结果集中就用having（在group by后面）连接查询条件。

按每个字段分组
按用户id和旅游路线id进行分组，并查询每一组的平均值

SELECT AVG(totalPrice),userId,routeId FROM orders GROUP BY userId,routeId
image.png

添加排序

SELECT AVG(totalPrice),userId,routeId FROM orders GROUP BY userId,routeId ORDER BY AVG(totalPrice) desc
image.png

6、表连接

按功能分类
内连接：等值连接、非等值连接、自连接
外连接：左外连接、右外连接、全外连接
交叉连接

6.1、Sql92语法

6.1.1等值连接

查看订单编号（在订单表中）和下单账号名（在用户表中），并按用户名排序

SELECT orderNum,username FROM orders,USER WHERE orders.userId = user.id ORDER BY username;
image.png

查看订单编号和下单产品
有时候表名比较长，又多处会用到，就可以为表名取别名，跟为字段取别名一样，用as或者空格，同样可以区分不同表的同名字段，不过如果为表起了别名，就不能再使用原表名.字段名

SELECT o.orderNum,r.name FROM orders o,route r WHERE o.routeId = r.id;
image.png

实现三表连接

SELECT o.orderNum,u.username,r.name FROM orders o,USER u,route r WHERE o.userId = u.id AND o.routeId = r.id ORDER BY username;
image.png

6.1.2非等值连接

就是where后面的查询条件不再是等值判断，变成了区间判断比如：> < >= <= <> between and等等
查询订单表里下单的用户名以及价格大于10000的订单价格

SELECT username,totalPrice FROM USER u,orders o WHERE u.id = o.userId AND o.totalPrice > 10000;
image.png

6.1.3自连接：自己表连接自己表

把自己表当成多份表来使用
sql92语法练习

SELECT job_id,location_id FROM employees e,department d WHERE e.department_id = d.department_id AND e.department_id = 90;
SELECT e.last_name,d.department_name,d.location_id,l.city FROM employees e,department d,locations l 
WHERE e.department_id = d.department_id AND d.location_id = l.location_id AND e.commosion_pct IS NOT NULL;
SELECT e.last_name,e.job_id,e.department_id,d.department_name FROM employees e,department d,locations l
WHERE e.department_id = d.department_id AND d.location_id = l.location_id AND l.city = 'Toronto';
SELECT department_name,job_title,MIN(salary) FROM employees e,jobs j,department d 
WHERE e.department_id = d.department_id AND e.job_id = j.job_id GROUP BY department_name,job_title;
SELECT COUNT(*),country_id FROM department d,locations l 
WHERE d.location_id = l.location_id GROUP BY country_id HAVING COUNT(*) > 2;
SELECT e.last_name AS employees,e.employee_id AS "Emp#",m.last_name AS manager,m.employee_id AS "Mgr#"
FROM employees e,employees m WHERE e.manager_id = m.employee_id;

6.2、Sql99语法

select 查询列表
from 表1 别名 【连接条件】
join 表2 别名
on 【表连接条件】
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序条件】
【limit 选取个数】

表连接条件
内连接：inner
外连接：左外连接（left 【outer】）、右外连接（right【outer】）、全外连接（full 【outer】）
交叉连接：cross

6.2.1内连接（inner可以省略 ）

select 查询列表
from 表1 别名 inner
join 表2 别名
on 【表连接条件】
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序条件】
【limit 选取个数】

SELECT last_name,department_name 
FROM employees e INNER JOIN department d ON e.department_id = d.department_id

SELECT last_name,job_title
FROM employees e INNER JOIN jobs j ON e.job_id = j.job_id
WHERE last_name LIKE '%e%';

SELECT COUNT(*),city
FROM department d INNER JOIN locations l ON d.location.id = l.location_id
GROUP BY city HAVING COUNT(*) > 3

SELECT department_name,COUNT(*) AS 员工个数
FROM department d INNER JOIN employees e ON e.department_id = d.department_id
GROUP BY department_name HAVING COUNT(*) > 3
ORDER BY 员工个数 DESC;

SELECT e.last_name,d.department_name,j.job_title
FROM employees e 
INNER JOIN department d ON e.department_id = d.department_id
INNER JOIN jobs j ON e.job_id = j.job_id
ORDER BY department_name DESC;

6.2.3外连接

特点：
1.外连接的查询结果是主表中的所有记录
2.如果从表中有和它匹配的，则显示匹配的值，如果从表中没有和它匹配的，则显示为null
3.外连接查询结果=内连接结果+主表中有而从表中没有的记录
4.左外连接：left outer join：left左边的是主表；right outer join :right join 右边的是主表
左外连接左边的是主表，右外连接右边的是主表
内连接查的是两个表都有的数据
外连接可以查出一个表有另一个表没有的数据，没有的就用Null来填充

左外连接
SELECT s.name,s.destId,d.name
FROM scenery s
LEFT OUTER JOIN destination d ON s.destId = d.id;

SELECT d.id,d.name,COUNT(*) AS 景点个数
FROM destination d
LEFT OUTER JOIN scenery s ON d.id = s.destId
WHERE s.name IS NOT NULL
GROUP BY d.name;

右外连接
SELECT s.name,s.destId,d.name
FROM scenery s
RIGHT OUTER JOIN destination d ON s.destId = d.id;
image.png

6.2.4交叉连接

查的就是笛卡尔积

SELECT u.*,r.*
FROM USER u
CROSS JOIN role r;
image.png

练习

SELECT g.name,g.boyId,b.* 
FROM girl g
LEFT OUTER JOIN boy b ON g.boyId = b.id
WHERE g.id > 3;

SELECT city,d.*
FROM locations l
LEFT OUTER department d ON d.location_id = l.locationId
WHERE d.department_id IS NULL;

SELECT e.*,department_name
FROM employees e
LEFT OUT JOIN department d ON e.department_id =d.department_id
WHERE d.department_name = 'SAL' OR d.department_name = 'IT';
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 205,033评论 6赞 478
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 87,725评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 151,473评论 0赞 338
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,846评论 1赞 277
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,848评论 5赞 368
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,691评论 1赞 282
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,053评论 3赞 399
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,700评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 42,856评论 1赞 300
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,676评论 2赞 323
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,787评论 1赞 333
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,430评论 4赞 321
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,034评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,990评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,218评论 1赞 260
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,174评论 2赞 352
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,526评论 2赞 343

推荐阅读更多精彩内容

  • MySQL数据库学习day3
    回顾 字段类型(列类型)：数值型，时间日期型和字符串类型 数值型：整型和小数型(浮点型和定点型) 时间日期型：da...
    翊溪阅读 928评论 0赞 0
  • mysql数据库学习笔记
    基础知识: 1.数据库的连接mysql -u -p -h-u 用户名-p 密码-h host主机 2:库级知识2....
    网络安全自修室阅读 1,453评论 0赞 1
  • MySQL数据库学习day1
    什么是数据库？ 数据库：database，存储仓库的介质。 数据库：高效的存储和处理数据的介质（介质主要是两种：磁...
    翊溪阅读 935评论 0赞 5
  • MySQL数据库学习笔记
    数据库基础 数据库系统概述 数据库技术的发展 人工管理阶段 特点：1.数据不保存   2.使用应用程序管理数据  ...
    真不懂事阅读 795评论 0赞 0
  • MySQL数据库学习day6
    回顾 外键：关联关系(表与表之间：表中字段指向另一张表的主键) 联合查询：union，多表合并和单表不同查询条件 ...
    翊溪阅读 419评论 0赞 0