Mysql系列（一）Mysql语法扫盲

最新推荐文章于 2024-07-12 16:56:26 发布

雨剑yyy

最新推荐文章于 2024-07-12 16:56:26 发布

阅读量239

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/csdn_20150804/article/details/103281194

版权

mysql 专栏收录该内容

5 篇文章 1 订阅

订阅专栏

1.概述

本文简单整理下mysql一些平时不容易注意到的语法规则，特别是针对没有系统学习过mysql语法的同学。
SQL，一般发音为 sequel，SQL 的全称 Structured Query Language.

2.常用系统命令

2.1 查看mysql版本

mysql --version ;
mysql -V ;

2.2 连接mysql

mysql -uroot -p123456
其中root是用户名，123456是密码。

3. 库表相关操作

-- 连接上mysql后，使用此命令查看当前使用的数据库。
select database();

-- 显示所有数据库
show databases;

--选择默认使用的数据库
use 数据库名

-- 显示当前库中的表
show tables

-- 查看指定数据库的中的表
show tables from 数据库名;

-- 查看表结构
desc 表名

-- 查看建表语句
show create table  表名

4. 查询相关

4.1 sql执行顺序

sql在执行时，按照如下顺序执行：

from
join on
where
group by
having
select
orderby
limit

4.2 like

占位符：
除了%，
还有 _ ，比如查询二个字符为A的：like ‘_A%’;

4.3 between … and …操作符

查询sql在1600和3000之间的，包含1600和3000:

where sal between 1600 and 3000;

5 排序相关

5.1 默认升序

排序采用 order by 子句，排序字段可以放多个，多个采用逗号间隔，order by 默认采用升序。

5.2 使用位置排序（不推荐）

order by 5 desc
使用第5个字段进行降序。

6. 单行处理函数

lower、upper、substr、length、trim、str_to_date、date_format、Format、round、rand、case … when … then …else …end、ifnull

-- 将name转为小写
select lower(name) from emp;
-- 将name转为大写
select upper(name) from emp;

--截取ename的第1个字符
select * from emp where substr(ename, 1, 1)=upper('m');

-- 计算字段长度
select length(ename), ename from emp where length(ename)=5;

-- 去除字段前后空格
select * from emp where job=trim(upper('manager '));

-- str_to_date 可以将字符串转换成日期
select * from emp where HIREDATE='1981-02-20';
等同于：
select * from emp where HIREDATE=str_to_date('1981-02-20','%Y-%m-%d');

--将入职日期格式化成yyyy-mm-ddhh:mm:ss
select empno, ename, date_format(hiredate, '%Y-%m-%d %H:%i:%s') as hiredate from emp;

-- 查询员工薪水加入千分位和保留两位小数
select empno,ename,Format(sal,2)from emp;

-- 四舍五入
select round(123.56);

-- 生成随机数
select rand();

-- case ... when ... then .....else ...end
select e.*,sal ,case job when 'salesman' then sal*1.1 when 'clerk' then sal*1.2 else sal end as new_sal from emp e;

-- 如果 comm 为 null 就替换为 0
select ifnull(comm,0) from emp;

7. 分组查询

7.1 分组查询函数

count、sum、avg、max、min

-- 
select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;

7.2 分组查询

group by：
以上的分组函数，实际上必须关联group by使用；有些时候没有写group by，也行，但是mysql实际带上了，默认对整个表分组的。

having：

having必须放在group by 后面。

8.连接查询

两种版本的语法规范 sql92，sql99。
sql92：
select from a，b where a.x = b.x

sql99：
select from a
join b on a.x = b.x

建议使用sql99版本。

8.1 连接类型

内链接：
表 1 inner join 表 2 on 关联条件
或者
表 1 join 表 2 on 关联条件

外连接：
right join
left join
表1 left outer join 表2 on 关联条件，outer可以省略。

外连接结果数一定 >= 内连接。

8.2 数据准备

表初始化数据：
准备两张表，用户表user，角色表role；其中，user表中包含外键role_id，即role表的id，具体数据如下：
在这里插入图片描述
user表：

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `role_id` int(11) NOT NULL COMMENT '角色id（1皇上；2妃子；3太监；4侍卫；5大臣）',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', '赵氏', '16', '2');
INSERT INTO `user` VALUES ('2', '钱氏', '17', '2');
INSERT INTO `user` VALUES ('3', '孙氏', '18', '2');
INSERT INTO `user` VALUES ('4', '李太监', '20', '3');
INSERT INTO `user` VALUES ('5', '周太监', '30', '3');
INSERT INTO `user` VALUES ('6', '吴侍卫', '25', '4');
INSERT INTO `user` VALUES ('7', '郑侍卫', '25', '4');
INSERT INTO `user` VALUES ('8', '王大臣', '25', '5');

role表：

DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '角色类型名',
  `job` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '角色职责',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of role
-- ----------------------------
INSERT INTO `role` VALUES ('1', '皇上', '享受妃子');
INSERT INTO `role` VALUES ('2', '妃子', '伺候皇上');
INSERT INTO `role` VALUES ('3', '太监', '伺候妃子');
INSERT INTO `role` VALUES ('4', '侍卫', '保卫皇宫');

8.3 inner join

inner join 也可以简写维join；
select * from user u inner join role r on u.role_id=r.id ;
在这里插入图片描述
内连接结果：两表交集

8.4 left join

select * from user u left join role r on u.role_id=r.id ;
在这里插入图片描述
左连接（左外连接）结果：左表（user）

8.5 right join

右连接（右外连接）结果：右表（role）
select * from user u right join role r on u.role_id=r.id ;
在这里插入图片描述

8.6 on 和where区别

在使用以上这3种join时，一些额外条件的位置需要注意：
比如：

select u.*,r.* from user u 
left join role r on u.role_id=r.id and r.name='太监'

和
select u.*,r.* from user u 
left join role r on u.role_id=r.id 
where
 r.name='太监'

这两个sql的结果是不同的，及时结果相同，有时查询效率也是不同的。

这里直接下结论：

在使用left join、right join ，额外条件建议放在where后，而不是on 后；
在使用inner join 是，额外条件放在where后和on后，效果相同；

9. 子查询

9.1 在 where 语句中使用子查询

select empno, ename from emp where empno in(select mgr from emp where mgr is not null);

9.2 在 from 语句中使用子查询

select e.empno, e.ename from emp e join (select distinct mgr from emp where mgr is not null) m on e.empno=m.mgr;

9.3 在 select 语句中使用子查询

select e.ename, (select d.dname from dept d where e .deptno=d.deptno) as dname from emp e;

10. union 合并集合

select * from emp where job in('MANAGER', 'SALESMAN');

等同于：

select * from emp where job='MANAGER' 
union 
select*fromempwherejob='SALESMAN'

11. limit

从第2条开始取3条数据：

select * from emp limit 1,3;

12. 表相关操作

12.1 添加字段

alter table t_student add contact_tel varchar(40);

12.2 修改字段

alter table t_student modify student_name varchar(100) ;

12.3 删除字段

alter table t_student drop contact_tel;

13 数据库锁相关查询

set global innodb_status_output_locks=1;
show engine innodb status\G;

–附件

初始化sql

DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEPT;
DROP TABLE IF EXISTS SALGRADE;

CREATE TABLE DEPT
       (DEPTNO int(2) not null ,
	DNAME VARCHAR(14) ,
	LOC VARCHAR(13),
	primary key (DEPTNO)
	);
CREATE TABLE EMP
       (EMPNO int(4)  not null ,
	ENAME VARCHAR(10),
	JOB VARCHAR(9),
	MGR INT(4),
	HIREDATE DATE  DEFAULT NULL,
	SAL DOUBLE(7,2),
	COMM DOUBLE(7,2),
	primary key (EMPNO),
	DEPTNO INT(2) 
	);

CREATE TABLE SALGRADE
      ( GRADE INT,
	LOSAL INT,
	HISAL INT );

INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
10, 'ACCOUNTING', 'NEW YORK'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
20, 'RESEARCH', 'DALLAS'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
30, 'SALES', 'CHICAGO'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
40, 'OPERATIONS', 'BOSTON'); 
commit;
 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7369, 'SMITH', 'CLERK', 7902,  '1980-12-17'
, 800, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7499, 'ALLEN', 'SALESMAN', 7698,  '1981-02-20'
, 1600, 300, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7521, 'WARD', 'SALESMAN', 7698,  '1981-02-22'
, 1250, 500, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7566, 'JONES', 'MANAGER', 7839,  '1981-04-02'
, 2975, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7654, 'MARTIN', 'SALESMAN', 7698,  '1981-09-28'
, 1250, 1400, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7698, 'BLAKE', 'MANAGER', 7839,  '1981-05-01'
, 2850, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7782, 'CLARK', 'MANAGER', 7839,  '1981-06-09'
, 2450, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7788, 'SCOTT', 'ANALYST', 7566,  '1987-04-19'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7839, 'KING', 'PRESIDENT', NULL,  '1981-11-17'
, 5000, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7844, 'TURNER', 'SALESMAN', 7698,  '1981-09-08'
, 1500, 0, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7876, 'ADAMS', 'CLERK', 7788,  '1987-05-23'
, 1100, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7900, 'JAMES', 'CLERK', 7698,  '1981-12-03'
, 950, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7902, 'FORD', 'ANALYST', 7566,  '1981-12-03'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7934, 'MILLER', 'CLERK', 7782,  '1982-01-23'
, 1300, NULL, 10); 
commit;
 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
1, 700, 1200); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
2, 1201, 1400); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
3, 1401, 2000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
4, 2001, 3000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
5, 3001, 9999); 
commit;

34道练习

-- 1.取得每个部门最高薪水的人员名称
select e.ename, e.sal,e.DEPTNO from EMP e
join (select max(sal)as sal,DEPTNO from EMP
group by DEPTNO) tem on tem.DEPTNO=e.DEPTNO and e.SAL= tem.sal;

-- 2.哪些人的薪水在部门的平均薪水之上
select emp.ename,emp.sal from emp
join(select avg(sal)as sal,deptno from emp
     group by deptno) tem on tem.deptno=emp.deptno and emp.sal> tem.sal;

-- 3.取得部门中(所有人的)平均的薪水等级
select emp.deptno,avg(sg.grade) from emp
    join salgrade sg on emp.sal between sg.losal and sg.hisal
group by emp.deptno;

-- 4.不准用组函数(Max)，取得最高薪水(给出两种解决方案)
select sal from emp
order by sal desc limit 1;

select sal from emp where sal not in(select distinct a.sal from emp a join emp b on a.sal < b.sal);

-- 5.取得平均薪水最高的部门的部门编号(至少给出两种解决方案)
select deptno from emp
group by deptno order by avg(sal) desc limit 1;

-- 6.取得平均薪水最高的部门的部门名称
select dname from dept where deptno=(select deptno from emp
group by deptno order by avg(sal) desc limit 1);

-- 7.求平均薪水的等级最低的部门的部门名称
select dname from dept where deptno=(select emp.deptno from emp
 join salgrade sg on emp.sal between sg.losal and sg.hisal
group by emp.deptno order by avg(sg.grade) limit 1);

-- 8.取得比普通员工(员工代码没有在 mgr 字段上出现的)的最高薪水还要高的 领导人姓名
select ename,sal from emp where sal>
(select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null));

-- 9.取得薪水最高的前五名员工
select * from emp order by sal desc limit 5;

-- 10.取得薪水最高的第六到第十名员工
select * from emp order by sal desc limit 5,5;

-- 11.取得最后入职的 5 名员工
select * from emp order by hiredate desc limit 5;

-- 12.取得每个薪水等级有多少员工
select sg.grade,count(*) from emp
join salgrade sg on  emp.sal between sg.losal and sg.hisal
group by sg.grade;

-- 13.

-- 14.列出所有员工及领导的姓名
select a.ename,b.ename from emp a
join  emp b  on a.mgr=b.empno;

-- 15.列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
select a.empno,a.ename,d.dname from emp a
join  emp b  on a.mgr=b.empno and a.hiredate <b.hiredate
join dept d on a.deptno =d.deptno;

-- 16.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门.
select d.dname,emp.* from dept d
left join emp  on emp.deptno =d.deptno
order by d.dname;

-- 17.列出至少有 5 个员工的所有部门
select d.dname ,count(*) from dept d
 join emp  on emp.deptno =d.deptno
group by d.dname
having count(*)>4;

-- 18.列出薪金比"SMITH"多的所有员工信息.
select * from emp
where sal>(select sal from emp where ename='SMITH');

-- 19.列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数.
select e.ename,d.dname ,tem.cc from emp  e
join dept d on e.deptno = d.deptno and e.JOB='CLERK'
join (select d.dname ,count(*) as cc  from dept d
       join emp  on emp.deptno =d.deptno
      group by d.dname) tem on d.dname=tem.dname;

-- 20.列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数.
select job,count(*) from emp group by job having min(sal)>1500;

-- 21.列出在部门"SALES"<销售部>工作的员工的姓名,假定不知道销售部的部 门编号
select ename from emp where deptno=
(select deptno from dept where dname='SALES');

-- 22.列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等 级
select e.ename,d.dname ,e.mgr ,e.sal,g.grade  from emp e
join dept d on e.deptno=d.deptno
join salgrade g on e.sal between g.losal and g.hisal  and e.sal >(select avg(sal) from emp);

-- 23.列出与"SCOTT"从事相同工作的所有员工及部门名称
select e.ename,d.dname  from emp e
 join dept d on e.deptno=d.deptno and e.job=(select job from emp where ename='SCOTT')
and e.ename<>'SCOTT';

-- 24. 列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金
select ename,sal,deptno from emp
where sal in (select  distinct sal  from emp where deptno =30)
and deptno <> 30;

-- 25.列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金.部门名 称
select ename,sal,dname from emp
join dept on emp.deptno=dept.deptno
where sal > (select  max(sal)   from emp where deptno =30)
  and emp.deptno <> 30;

-- 26.列出在每个部门工作的员工数量,平均工资和平均服务期限
select d.dname,tem.* from dept d
join(select DEPTNO,count(*),avg(sal),avg(years) from emp a
    join (select empno,timestampdiff(YEAR ,HIREDATE,now()) as years from emp) b on a.empno=b.empno
     group by deptno) tem on  d.deptno=tem.deptno;

-- 27.列出所有员工的姓名、部门名称和工资
select e.ename,d.dname,e.sal from emp e
join dept d on e.deptno =d.deptno order by d.DNAME,e.empno ;

-- 28.列出所有部门的详细信息和人数
select d.*,count(ename) as '人数' from dept d
left join emp e on d.deptno =e.deptno
group by d.deptno;

-- 29.列出各种工作的最低工资及从事此工作的雇员姓名
select * from emp a
join(select job, min(sal) minsal from emp
     group by job) b on a.job=b.job and a.sal=b.minsal;

-- 30.列出各个部门的 MANAGER(领导)的最低薪金
select deptno ,min(sal) from emp  where empno in(select distinct mgr from emp where mgr is not null)
group by deptno;

-- 31.列出所有员工的年工资,按年薪从低到高排序
select ename, (sal+ifnull(COMM,0))*12 as income from emp order by income;

-- 32.求出员工领导的薪水超过 3000 的员工名称与领导名称
select * from emp where sal > 3000 and empno in(select distinct mgr from emp where mgr is not null);

select a.ename,b.ename from emp a
join emp b on a.MGR=b.empno and b.SAL>3000;

-- 33.求出部门名称中,带'S'字符的部门员工的工资合计、部门人数
select d.dname ,count(e.ename),ifnull(sum(e.sal),0) from  emp  e
right join dept d on  e.deptno = d.deptno
where  d.dname like '%S%'
group by d.dname;

-- 34.给任职日期超过 30 年的员工加薪 10%
update emp set sal = sal * 1.1 where timestampdiff(YEAR, hiredate, now()) > 30;

雨剑yyy

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
Mysql系列（一）Mysql语法扫盲

准备两张表，用户表user，角色表role；其中，user表中包含外键role_id，即role表的id，具体数据如下：表初始化数据：user表：DROP TABLE IF EXISTS `user`;CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) CHARACTER ...
复制链接

扫一扫