sql语句用法

mysql基础

介绍：学习mysql语句之前首先了解一下mysql的一些基础操作

1.启动、停止、重启mysql服务器的命令

启动：sudo service mysql start
停止：sudo service mysql stop
重启：sudo service mysql restart

2.使用命令行连接数据库服务器的命令是什么？

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');