SQL语法之DQL

发表于 | 分类于
字数统计: 1,236 | 阅读时长 ≈ 6

本文主要介绍MySQL语法之DML(data query language)操作。

1.数据查询语言

用户能够查询数据库以及操作已有数据库中的数据。主要是SELECT。

2.SELECT查询

  • 常规查询
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    # 准备好2个表格：
    mysql> create table emp  (empno int ,ename varchar(20), sex char(1),birthday date , hiredate date, sal decimal(10,2), deptno tinyint(1), managerno int) engine=memory default charset=utf8;
    mysql> create table dept (deptno tinyint(1),deptname varchar(30),location varchar(50));	
    mysql> insert into emp values (1,'boss','m','1964-08-08','1995-01-01','20000','1','1'),(2,'zhangsan','m','1967-04-05','1995-04-11','15000','2','1'),(3,'lisi','f','1973-01-28','1998-11-21','13000','3','1'),(4,'wangwu','f','1975-06-03','1999-12-12','12000','4','1'),(5,'maliu','m','1982-08-18','2001-07-03','8000','2','2'),(6,'tianqi','f','1983-02-15','2002-11-01','7000','2','2'),(7,'mark','m','1984-08-12','2003-10-02','6500','3','3'),(8,'john','m','1985-09-14','2005-04-03','6000','3','3'),(9,'mm','f','1990-06-08','2008-09-13','4000','4','4');
    mysql> insert into dept values (1,'manager','beijing'),(2,'it','shenzhen'), (3,'sale','shanghai'), (4,'services','guangzhou');

    # 查看所有列，不推荐这么查询
    mysql> select * from emp;
    # 查看其中几列
    mysql> select ename,sal from emp limit 3;
    # 去除重复行
    mysql> select distinct deptno from emp;

    # 查询所有男员工的姓名和工资
    mysql> select ename,sal from emp where sex='m';
    # 查询工资大于8000的员工的所有信息
    mysql> select * from emp where sal>8000;
    # 查询工资在4000到8000之间的员工的所有信息（包含4000和8000的)
    mysql> select * from emp where sal>=4000 and sal<=8000;
    mysql> select * from emp where sal between 4000 and 8000;
    查询入职时间在2001那年的员工的所有信息

    mysql> select * from emp where hiredate>="2001-01-01" and hiredate<="2001-12-31";
    mysql> select * from emp where hiredate like "2001%";
    mysql> select * from emp where year(hiredate)=2001;
    mysql> select * from emp where substr(hiredate,1,4)=2001;
    mysql> select * from emp where hiredate regexp '2001.*';

  • 排序

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    # 最大工资，最小工资
    mysql> select * from emp order by sal;			# 以工资排序，默认升序排序
    mysql> select * from emp order by sal asc; 		# 加不加asc都是升序（ascend)
    mysql> select * from emp order by sal desc;		# desc表示降序排序(descend)

    mysql> select * from emp order by sex,sal;
    # 先按性别排，再按工资排。结果是女的都在一起，以工资从小到大排。男的都在一起，以工资从小到大排。
    mysql> select * from emp order by sex desc,sal desc;

    # 找出工资最低的三个人的姓名和工资
    mysql> select * from emp order by sal limit 3;

    # 找出工资最高的三个人的姓名和工资
    mysql> select * from emp order by sal desc limit 3;

    找出工资最低的女员工的姓名和工资
    mysql> select ename,sal from emp where sex='f' order by sal limit 1;

    #where和order by一起使用时，where在前，order by在后

    # 找出工资从高到低第三到第五的人的姓名和工资

    mysql> select ename,sal from emp order by sal desc limit 2,3;   # 从0开始，2是第三，3是三个人

  • 聚合和分组

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    # 统计记录条数
    mysql> select count(*) from emp;
    mysql> select count(distinct deptno) from emp;
    # 别名
    mysql> select count(distinct deptno) deptcount from emp;

    # 统计每个部门的人数
    mysql> select deptno,count(*) from emp group by deptno;

    # 统计男，女员工各有多少人
    mysql> select sex,count(*) from emp group by sex;

    # 统计每个部门里男女员工各有多少个
    mysql> select deptno,sex,count(*) from emp group by deptno,sex;

    # 查找部门人数大于2的部门号和人数
    mysql> select deptno,count(*) from emp group by deptno having count(*) >2;

    # 求每个部门的工资总额，最大工资，最小工资，平均工资
    mysql> select deptno,sum(sal),max(sal),min(sal),avg(sal) from emp group by deptno;
  • 表链接（多表查询)
    1
    2
    3
    # 查出员工姓名和其对应的工资，部门名，部门所在地，并显示

    mysql> select ename,sal,deptname,location from emp,dept where emp.deptno=dept.deptno;
  • join连接
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    INNER JOIN（内连接）：取得两个表中存在连接匹配关系的记录。
    mysql> select a.ename,a.sal,b.deptname,b.location from emp a INNER JOIN dept b on a.deptno=b.deptno;

    为了看到【左连接】和【右连接】的效果，插入两条数据：
    mysql> insert into dept(deptno, deptname, location) values (5,'Administration','beijing');   # 加一个行政部
    mysql> insert into dept(deptno, deptname, location) values (6,'technology','shenzhen');      # 加一个技术部

    LEFT JOIN（左连接）：取得左表（table1）完全记录，即是右表（table2）并无对应匹配记录
    mysql> select * from emp left join dept on emp.deptno=dept.deptno;

    RIGHT JOIN（右连接）：与 LEFT JOIN 相反，取得右表（table2）完全记录，即是左表（table1）并无匹配对应记录
    mysql> select * from emp right join dept on emp.deptno=dept.deptno;
  • 子查询
    1
    2
    3
    # 查出比wangwu工资高的人的姓名和工资

    mysql> select ename,sal from emp where sal>(select sal from emp where ename='wangwu');
  • 复制一个表：
    1
    2
    3
    4
    mysql> create table emp1 like emp;
    mysql> insert into emp1 select * from emp;
    或者
    mysql> create table emp2 select * from emp;
---------------- The End ----------------