mysql基础
介绍：学习mysql语句之前首先了解一下mysql的一些基础操作
1.启动、停止、重启mysql服务器的命令
启动：sudo service mysql start
停止：sudo service mysql stop
重启：sudo service mysql restart2.使用命令行连接数据库服务器的命令是什么？
mysql -u 用户名 -p密码3.列出数据库的命令
查看所有数据库：show databases;
使用数据库：use 数据库名;
查看当前使用的数据库：select database();
创建数据库：create database 数据库名 charset=utf8;
删除数据库：drop database 数据库名;4.创建数据库
create database 数据库名 charset=utf8;5.列出表的命令
查看当前数据库中所有表：show tables;
查看表结构：desc 表名;6.列出数据操作语句的语法
增加：insert into 表名(列...) values(值...);
修改：update 表名 set 列=值,... where ...;
删除：delete from 表名 where ...;
查询：select * from 表名;mysql基础语句
介绍：mysql的一些基础语句（主要方面为查询）
# 创建表语句
create table students(
id int unsigned auto_increment primary key not null,
name varchar(20) not null,
chinese tinyint not null,
english tinyint not null,
math tinyint not null
);
insert into students values('张小明',89,78,90);
insert into students values(2,'李进',67,53,95);
insert into students values(3,'王五',87,78,77);
insert into students values(4,"李一",88,98,92);
insert into students values(5,"李来财",82,84,67);
insert into students values(6,"张进宝",55,85,45);
insert into students values(7,"黄蓉",75,65,30);
1) 查询表中所有学生的信息。
select * from students;
2) 查询表中所有学生的姓名和对应的英语成绩。
select name, english from students;
3) 过滤表中重复数据。
select distinct english from students;
4) 统计每个学生的总分。
select name,sum(chinese+english+math) as "总分" from students group by name;
5) 在所有学生总分数上加10分特长分。
select name,sum(chinese+english+math+10) from students group by name;
6) 使用别名表示学生分数。
select name,sum(chinese+english+math) as "总分" from students group by name;
7) 查询姓名为李一的学生成绩
select name,sum(chinese+english+math) from students where name="李一";
8) 查询英语成绩大于90分的同学
select * from students where english > 90;
9) 查询总分大于200分的所有同学
select * from students where (english+chinese+math) > 200;
10) 查询英语分数在 80－90之间的同学。
select * from students where english between 80 and 90;
11) 查询数学分数为89,90,91的同学。
select * from students where english in (89,90,91);
12) 查询所有姓李的学生英语成绩。
select name,english from students where name like "李%";
13) 查询数学分80并且语文分80的同学。
select name from students where math = 80 and chinese = 80;
14) 查询英语80或者总分200的同学
select * from students where english =80 or (english+chinese+math) > 200;
15) 对数学成绩排序后输出。
select name,math from students order by math desc;
16) 对总分排序后输出，然后再按从高到低的顺序输出
select name,(english+math+chinese) as a from students order by a desc;
17）对姓李的学生成绩排序输出
select name,(english+math+chinese) as a from students having name like "李%" order by a desc;msyql语句中关键字的用法
1）查询总成绩最高及最低的学生信息
# 最高成绩学生信息
select * from students where (english+math+chinese) = (select max(english+chinese+math) from students);
# 最低成绩学生信息
select * from students where (english+math+chinese) = (select max(english+chinese+math) from students);
2）查询每一个学生的平均的成绩
# 求平均值，保留两位小数
select name,round((chinese+english+math)/3, 2) from students order by name;
3）查询所有学生各科的平均成绩
select round(sum(chinese)/count(*), 2),round(sum(english)/count(*), 2),round(sum(math)/count(*), 2) from students;
4）查询数学成绩比李一同学高的学生成绩
# 先求出李一的数学成绩，再查询比较
select name,math from students where math > (select math from students where name="李一");
5）列出总成绩比学生“王五”高的所有学生姓名、总成绩
# 先求出王五的总成绩，再查找比王五总成绩高的人名及成绩
select name, (chinese+english+math) from students where (chinese + english + math) > (select sum(english+chinese+math) from students where name="王五");
6）列出总成绩高于所有学生平均成绩的学生信息
# 先求出所有学生平均成绩sum(chinese+english+math)/count(*),再求出总成绩高于平均成绩的信息
select * from students where (chinese+english+math) > (select sum(chinese + english + math)/count(*) from students);mysql进阶语句
创建表
CREATE TABLE emp
(EMPNO float(4) PRIMARY KEY, -- 员工编号
ENAME VARCHAR(10), -- 员工姓名
JOB VARCHAR(9), -- 员工职位
MGR float(4), -- 员工上级工号
HIREDATE DATE, -- 生日
SAL float(7,2), -- 薪水
COMM float(7,2), -- 年终奖
DEPTNO float(2) REFERENCES dept); -- 部门号
- 数据
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-02', 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);子查询
1) 列出薪金比员工“TURNER”多的所有员工姓名（ename）、员工薪资（sal）
SELECT ename,sal FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='TURNER');
2) 列出薪金高于公司平均薪金的所有员工姓名、薪金。
SELECT ename,sal FROM emp WHERE sal > (SELECT AVG(sal) FROM emp);
3) 列出与“SCOTT”从事相同工作的所有员工姓名、工作名称(不展示Scott的姓名、工作)
SELECT ename,job FROM emp WHERE job = (SELECT job FROM emp WHERE ename = 'scott') AND ename != 'scott';
4) 列出薪金高于30部门最高薪金的其他部门员工姓名、薪金、部门号。
SELECT ename,sal,deptno FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = '30') AND deptno != '30';
5) -- 查询薪资最高的员工编号、姓名、薪资
SELECT empno,ename,sal FROM emp WHERE sal = (SELECT MAX(sal) FROM emp);
6) 列出薪金高于本部门平均薪金的所有员工姓名、薪资、部门号、部门平均薪资。
SELECT emp.ename,emp.sal,emp.deptno,t.avgsal FROM emp ,(SELECT deptno,AVG(sal) avgsal FROM emp GROUP BY deptno) t WHERE emp.DEPTNO = t.deptno AND emp.sal > t.avgsal;
7) 列出所有部门的详细信息：部门名称、部门编号、部门人数。
SELECT d.dname,d.deptno,IFNULL(t.num,0) '部门人数' FROM (SELECT deptno,COUNT(*) num FROM emp GROUP BY deptno) t RIGHT JOIN dept d ON t.deptno = d.deptno;
8) -- 查询出king所在部门的工作年限最大的员工名字及入职时间
SELECT ename,HIREDATE FROM emp WHERE HIREDATE = (SELECT MIN(HIREDATE) FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'king'));
9) 查询出管理员工人数最多的人的名字和他管理的人的名字
SELECT a.ename '员工',a.empno '员工编号',b.ename '领导',b.empno '领导编号' FROM emp a,emp b WHERE a.mgr = b.empno AND b.empno IN(SELECT mgr FROM emp GROUP BY mgr HAVING COUNT(*) = (SELECT COUNT(*) num FROM emp GROUP BY mgr ORDER BY num DESC LIMIT 0,1));
10) 查询出工资成本最高的部门的部门号、部门名称、部门工资成本
SELECT emp.DEPTNO,dept.DNAME,SUM(sal + IFNULL(comm,0)) total FROM emp,dept WHERE emp.DEPTNO = dept.DEPTNO GROUP BY emp.DEPTNO,dept.DNAME HAVING total = (SELECT SUM(sal + IFNULL(comm,0)) total FROM emp GROUP BY deptno ORDER BY total DESC LIMIT 0,1);自连接
1) 列出所有员工的姓名及其直接上级的姓名。
解法1：SELECT a.ename,b.ename "上级" FROM emp a LEFT JOIN emp b ON a.mgr=b.empno;
解法2：SELECT ename '员工姓名',( SELECT ename FROM emp WHERE empno = se.mgr) '上级姓名' FROM emp se;
2) 列出受雇日期早于其直接上级的所有员工编号、员工姓名、员工入职时间、上级姓名、上级入职时间
SELECT a.empno,a.ename,a.hiredate,b.ename,b.hiredate FROM emp a,emp b WHERE a.mgr=b.empno AND a.hiredate<b.hiredate;左右连接
1) 查询所有的部门编号及部门下员工编号、员工姓名。
SELECT d.DEPTNO,e.EMPNO,e.ENAME FROM emp e RIGHT JOIN dept d ON e.DEPTNO = d.DEPTNO;
函数应用
1) 列出最低薪金大于1500的工作名称（job）以及最低薪金
SELECT ename,sal FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='TURNER');
SELECT job,MIN(sal) msal FROM emp GROUP BY job HAVING msal > 1500;
2) 列出在每个部门工作的员工数量、平均工资
SELECT deptno '部门号',COUNT(*) '员工数量' ,AVG(sal) '平均薪资' FROM emp GROUP BY deptno;
3) -- 查询每个部门的部门号、最高薪资
SELECT deptno,MAX(sal) maxSal FROM emp GROUP BY deptno;
4) 查询每种工作的工作名称和最低工资
SELECT job,MIN(sal) '最低薪资' FROM emp GROUP BY job;
5) 列出所有员工的员工姓名、年工资,按年薪从低到高排序。
SELECT ename,(sal*12) AS yearSal FROM emp ORDER BY yearSal asc;
6) -- 查询每个部门中薪资最高的员工姓名、薪资、部门号
SELECT e.ename,e.sal,e.deptno FROM emp e,(SELECT MAX(sal) sal,deptno FROM emp GROUP BY deptno) t WHERE e.sal = t.sal AND e.DEPTNO = t.deptno;
7) -- 查询不是领导的员工编号、员工姓名、员工职位
SELECT empno,ename,job FROM emp WHERE empno NOT IN(SELECT DISTINCT IFNULL(mgr,'') FROM emp);多表查询
1) 查询岗位（job）是 'CLERK' 的员工编号、员工姓名、所在部门名称
SELECT e.EMPNO,e.ENAME,d.DNAME FROM emp e,dept d WHERE e.DEPTNO = d.DEPTNO AND e.JOB = 'CLERK';
2) 列出所有员工的姓名、部门名称和工资。
SELECT emp.ENAME,dept.DNAME,emp.SAL FROM emp,dept WHERE emp.DEPTNO = dept.DEPTNO;
3) 查询至少有一个员工的部门编号、员工数量
SELECT b.deptno,COUNT(*) num FROM emp a,dept b WHERE a.deptno = b.deptno GROUP BY b.DEPTNO,b.DNAME HAVING num >=1;
4) -- 查询出没有员工的那个部门的部门编号和部门名称
SELECT deptno,dname FROM dept WHERE deptno NOT IN(SELECT DISTINCT deptno FROM emp);
5) 查询在部门“SALES”（销售部）工作的员工的姓名、部门编号
解法1：SELECT emp.ENAME,dept.DEPTNO FROM emp,dept WHERE emp.DEPTNO = dept.DEPTNO AND dept.DNAME = 'SALES';
解法2：SELECT ename,deptno FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE dname='SALES');
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
