mysql--DQL

迈不过友情╰ 2022-10-23 08:28 148阅读 0赞

1.  简单地查询语句(DQL)  
    语法格式:  
    select 字段名1, 字段名2… from 表名;
    
    提示:  
    1. 任何一条sql语句以分号(;)结尾.  
    2. sql语句不区分大小写.
    
    mysql> select ENAME, empno FROM emp;  
    ±-------±------+  
    | ENAME | empno |  
    ±-------±------+  
    | SMITH | 7369 |  
    | ALLEN | 7499 |  
    | WARD | 7521 |  
    | JONES | 7566 |  
    | MARTIN | 7654 |  
    | BLAKE | 7698 |  
    | CLARK | 7782 |  
    | SCOTT | 7788 |  
    | KING | 7839 |  
    | TURNER | 7844 |  
    | ADAMS | 7876 |  
    | JAMES | 7900 |  
    | FORD | 7902 |  
    | MILLER | 7934 |  
    ±-------±------+
    
    mysql> select  
    \-> empno, ename  
    \-> from  
    \-> emp;  
    (这么写可以区分开关键字)
    
    查看员工年薪? emp表中只有月薪.(字段可以参与数学运算)  
    mysql> select ename, sal \* 12 from emp;  
    ±-------±---------+  
    | ename | sal \* 12 |  
    ±-------±---------+  
    | SMITH | 9600.00 |  
    | ALLEN | 19200.00 |  
    | WARD | 15000.00 |  
    | JONES | 35700.00 |  
    | MARTIN | 15000.00 |  
    | BLAKE | 34200.00 |  
    | CLARK | 29400.00 |  
    | SCOTT | 36000.00 |  
    | KING | 60000.00 |  
    | TURNER | 18000.00 |  
    | ADAMS | 13200.00 |  
    | JAMES | 11400.00 |  
    | FORD | 36000.00 |  
    | MILLER | 15600.00 |  
    ±-------±---------+
    
    给查询结果的列重命名?  
    mysql> select ename, sal \* 12 as yearsal from emp;  
    ±-------±---------+  
    | ename | yearsal |  
    ±-------±---------+  
    | SMITH | 9600.00 |  
    | ALLEN | 19200.00 |  
    | WARD | 15000.00 |  
    | JONES | 35700.00 |  
    | MARTIN | 15000.00 |  
    | BLAKE | 34200.00 |  
    | CLARK | 29400.00 |  
    | SCOTT | 36000.00 |  
    | KING | 60000.00 |  
    | TURNER | 18000.00 |  
    | ADAMS | 13200.00 |  
    | JAMES | 11400.00 |  
    | FORD | 36000.00 |  
    | MILLER | 15600.00 |  
    ±-------±---------+
    
    as关键字可以省略.  
    mysql> select ename, sal \* 12 yearsal from emp;  
    ±-------±---------+  
    | ename | yearsal |  
    ±-------±---------+  
    | SMITH | 9600.00 |  
    | ALLEN | 19200.00 |  
    | WARD | 15000.00 |  
    | JONES | 35700.00 |  
    | MARTIN | 15000.00 |  
    | BLAKE | 34200.00 |  
    | CLARK | 29400.00 |  
    | SCOTT | 36000.00 |  
    | KING | 60000.00 |  
    | TURNER | 18000.00 |  
    | ADAMS | 13200.00 |  
    | JAMES | 11400.00 |  
    | FORD | 36000.00 |  
    | MILLER | 15600.00 |  
    ±-------±---------+
    
    别名中有中文?  
    select ename, sal \* 12 as 年薪 from emp;//错误  
    select ename, sal \* 12 as ‘年薪’ from emp;
    
    mysql> select ename, sal \* 12 as ‘年薪’ from emp;  
    ±-------±---------+  
    | ename | 年薪 |  
    ±-------±---------+  
    | SMITH | 9600.00 |  
    | ALLEN | 19200.00 |  
    | WARD | 15000.00 |  
    | JONES | 35700.00 |  
    | MARTIN | 15000.00 |  
    | BLAKE | 34200.00 |  
    | CLARK | 29400.00 |  
    | SCOTT | 36000.00 |  
    | KING | 60000.00 |  
    | TURNER | 18000.00 |  
    | ADAMS | 13200.00 |  
    | JAMES | 11400.00 |  
    | FORD | 36000.00 |  
    | MILLER | 15600.00 |  
    ±-------±---------+
    
    注意:标准sql语句中要求字符串使用单引号括起来.虽然mysql支持双引号,尽量别用,  
    兼容性降低.
    
    查询所有字段?  
    mysql> select \* from emp; (效率很低,实际开发中尽量别用)  
    ±------±-------±----------±-----±-----------±--------±--------±-------+  
    | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |  
    ±------±-------±----------±-----±-----------±--------±--------±-------+  
    | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |  
    | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |  
    | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |  
    | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |  
    | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |  
    | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |  
    | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |  
    | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |  
    | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |  
    | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |  
    | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |  
    | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |  
    | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |  
    | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |  
    ±------±-------±----------±-----±-----------±--------±--------±-------+
2.  条件查询  
    语法格式:  
    select  
    字段1, 字段2…  
    from  
    表名  
    where  
    条件;  
    执行顺序: 先from, 然后where, 最后select
    
    查询工资等于5000的员工姓名?  
    mysql> select ename from emp where sal = 5000;  
    ±------+  
    | ename |  
    ±------+  
    | KING |  
    ±------+
    
    查询SMITH的工资?  
    mysql> select sal from emp where ename = ‘SMITH’;  
    ±-------+  
    | sal |  
    ±-------+  
    | 800.00 |  
    ±-------+
    
    找出工资大于等于3000 的员工?  
    mysql> select ename, sal from emp where sal >= 3000;  
    ±------±--------+  
    | ename | sal |  
    ±------±--------+  
    | SCOTT | 3000.00 |  
    | KING | 5000.00 |  
    | FORD | 3000.00 |  
    ±------±--------+
    
    找出工资不等于3000的?  
    select ename, sal from emp where sal <> 3000;  
    或  
    select ename, sal from emp where sal != 3000;
    
    找出工资在1100和3000之间的员工,包括1100和3000?  
    select ename, sal from emp where sal >= 1100 and sal <= 3000;  
    或  
    select ename, sal from emp where sal between 1100 and 3000;(闭区间)
    
    注意:between and用在数值查询时必须左小右大.
    
    between and除了可以使用在数字方面之外, 还可以使用在字符串方面.  
    mysql> select ename from emp where ename between ‘A’ and ‘C’;  
    ±------+  
    | ename |  
    ±------+  
    | ALLEN |  
    | BLAKE |  
    | ADAMS |  
    ±------+  
    between and在查询字符串的时候是左闭右开,上面的例子,和C相匹配的字符串不作为查询结果.
    
    找出那些人津贴为空?  
    在数据库中NULL不是一个值,代表什么也没有,为空.  
    空不是一个值,不能用等号衡量.  
    必须使用is null或者 is not null  
    mysql> select ename, sal, comm from emp where comm is null;  
    ±-------±--------±-----+  
    | ename | sal | comm |  
    ±-------±--------±-----+  
    | SMITH | 800.00 | NULL |  
    | JONES | 2975.00 | NULL |  
    | BLAKE | 2850.00 | NULL |  
    | CLARK | 2450.00 | NULL |  
    | SCOTT | 3000.00 | NULL |  
    | KING | 5000.00 | NULL |  
    | ADAMS | 1100.00 | NULL |  
    | JAMES | 950.00 | NULL |  
    | FORD | 3000.00 | NULL |  
    | MILLER | 1300.00 | NULL |  
    ±-------±--------±-----+
    
    找出那些人没有津贴?(0.00或者NULL)  
    mysql> select ename, sal, comm from emp where comm is null or sal = 0;
    
    找出工作岗位是MANAGER和SALESMAN的员工?  
    select ename, job from emp where job = ‘MANAGER’ or job = ‘SALSMAN’;
    
    and和or联合使用:找出薪资大于1000的并且部门编号是20或30的员工?  
    select ename, sal, deptno from emp where sal > 1000 and deptno = 20 or deptno = 30;  
    //这么写不对,因为and优先级比or要高, sal > 1000 和deptno = 20 会组成一个条件组,deptno = 30会组成另外一个条件组.  
    select ename, sal, deptno from emp where sal > 1000 and (dept = 20 or dept = 30);  
    注意:当运算符的优先级不确定的时候,用小括号来自定义优先级.
    
    in等同于or:找出工作岗位是MANAGER和SALSMAN的员工?  
    select ename, job, from emp where job in (‘SALSMAN’, ‘MANAGER’);  
    select ename, job from emp where sal in (1000, 5000);//in后面的值不是区间,是具体的值.
    
    not in:不是这几个值的.
    
    模糊查询like:  
    找出名字当中含有o的?  
    (在模糊查询中,必须掌握两个特殊的符号,%,代表任意多个字符,\_代表任意一个字符)  
    select ename from emp where ename like ‘%o%’;
    
    找出名字中第二个祖母是A的?  
    select ename from emp where ename like ‘\_A%’;
    
    找出名字中有下划线的?(使用转义字符’’)  
    select ename from emp where ename like ‘%\_%’;
    
    找出名字中最后一个字母是T的?  
    select ename from emp where ename like ‘%T’;

13.排序  
注意:默认是升序.  
怎么制定升序或者降序? asc表示升序,desc表示降序.  
select ename, sal from emp order by sal;//升序  
select ename, sal from emp order by sal asc;//升序  
select ename, sal from emp order by sal desc;//降序  
asc：ascend  
desc：descend

按照工资降序排列,当工资相同的时候再按照名字升序排列?
    select ename, sal from emp order by sal desc, ename asc;
    注意:越靠前的字段越能起到主导作用.只有当前面的字段无法完成排序的时候,才会启用后面的字段.
    
    找出工作岗位是SALESMAN的员工,并且要求按照薪资的降序排列?
    select ename, sal from emp where JOB = 'SALESMAN'order by sal desc;
    
    [重要]
    select
    	*			步骤5
    from
    	tablename	步骤1
    where
    	条件 		步骤2
    group by
    	..			步骤3
    having
    	..			步骤4
    order by
    	...			步骤6

mysql--DQL

发表评论取消回复

还没有评论，来说两句吧...

相关阅读