[推荐]ORACLE SQL:精粹查询练手(不懂装懂,长久饭桶!)

方今,将线上MySQL数据库进级到了5.7。思考到恐怕发生的不包容性,在进级以前,确实也是恐怖,就算测验景况,开荒情状早在五个月前就已提前进级。

SQL语句分类:

[推荐]ORACLE SQL:

依据先前时代的调查钻探和对象的上报,与付出有关的显要有两点:

1.DDL DATA define LANGUAGE 数据定义语句: CREATE    ALTE汉兰达 ;

 

sql_mode

2.DML DATA manipulation LANGUAGE 数据操作语句  增加和删除改语句  INSERT / 
DELETE /UPDATE ;

优异查询练手(不懂装懂,长久饭桶!)

MySQL 5.6中,其暗中同意值为”NO_ENGINE_SU
BSTITUTION”,可领略为非严加情势,举个例子,对自增主键插入空字符串”,纵然提示warning,但并不影响自增主键的变型。

3.DQL DATA QUETucsonY LANGUAGE  数据查询语句  SELECT ;

——通过文化分享树立个人品牌。

但在MySQL
5.7中,其就调节为了严酷格局,对于地点这几个,其不会唤醒warning,而是径直报错。

一、增删改

 

分组求最值

1.增加 INSERT

 正文与大家一同斟酌与分享ORACLE
SQL的片段常用杰出查询,迎接大家补充,同不经常间您感觉有那几个卓绝的也可共享出来。在本文中,对每三个主题材料,你只要认为有如何越来越好的化解方法也接待你即刻提议。沟通与分享技能共同提升嘛,感激!

分组求最值的一些写法在MySQL5.7中得不到预期结果,那一点,相对来讲比较遮盖。

方式1:

 

 

语法:INSERT INTO 表名 (字段1,字段2。。。) VALUES(值1,值2。。。);


里面,第一点是可控的,毕竟能够调动参数。而第二点,却是不可控的,未有参数与之生死相依,要求支付Review代码。

mysql> INSERT INTO emp (empno,ename,deptno) VALUES(1,’tom’,1010);

 

 

主意2:插入部分字段的值,字段的个数和一一要与值的个数和一一一致

正文使用的实例表结构与表的数量如下:

scott.emp职员和工人表结构如下:

部门表

Name     Type         Nullable Default Comments 
——– ———— ——– ——- ——– 
EMPNO    NUMBER(4)                       员工号       
ENAME    VARCHA奇骏2(10) Y                  职员和工人姓名       
JOB      VARCHAR2(9)  Y                  工作       
MGSportage      NUMBEEscort(4)    Y                  上级编号       
HIREDATE DATE         Y                  雇佣日期       
SAL      NUMBER(7,2)  Y                  薪金       
COMM     NUMBER(7,2)  Y                  佣金       
DEPTNO   NUMBESportage(2)    Y                  部门编号

scott.dept

提示:工资=薪金+佣金

Name   Type         Nullable Default Comments 
—— ———— ——– ——- ——– 
DEPTNO NUMBE中华V(2)                         部门编号        
DNAME  VARCHA昂科雷2(14) Y                    部门名称     
LOC    VARCHAR2(13) Y                    地点   

 

scott.emp表的水保数量如下:

 

 

SQL> select * from emp;
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
—– ———- ——— —– ———– ——— ——— ——
 7369 SMITH      CLERK      7902 1980-12-17     800.00               20
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987-4-19     4000.00               20
 7839 KING       PRESIDENT       1981-11-17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30
 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20
 7900 JAMES      CLERK      7698 1981-12-3      950.00               30
 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20
 7934 MILLER     CLERK      7782 1982-1-23     1300.00               10
  102 EricHu     Developer  1455 2011-5-26 1   5500.00     14.00     10
  104 huyong     PM         1455 2011-5-26 1   5500.00     14.00     10
  105 WANGJING   Developer  1455 2011-5-26 1   5500.00     14.00     10
 
17 rows selected

 

 

 

Scott.dept表的幸存数量如下:

 

 

SQL> select * from dept;
 
DEPTNO DNAME          LOC
—— ————– ————-
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON
    50 50abc          50def
    60 Developer      HaiKou
 
6 rows selected

 

 

 

 

用SQL实现以下难题列表:

 

1.列出最少有一个职员和工人的装有单位。
2.列出薪给比“SMITH”多的具备职员和工人。
3.列出装有职员和工人的人名及其直接上级的姓名。
4.列出受雇日期早于其一向上级的持有员工。
5.列出机关名称和这么些单位的职员和工人音信,同有的时候间列出这一个从没职员和工人的机关
6.列出富有“CLERubiconK”(办事员)的人名及其部门名称。
7.列出最低薪俸陵高校于1500的各样职业。
8.列出在机构“SALES”(出卖部)专门的学问的职工的真名,假定不知情发售部的部门编号。
9.列出薪酬高于集团平均薪资的全部职工。
10.列出与“SCOTT”从事同样专门的学业的富有职工。
11.列出报酬等于部门30中职员和工人的薪资的有着员工的全名和薪俸。
12.列出薪酬高于在机构30专门的职业的保有员工的薪水的职工姓名和工资。
13.列出在每一个机构办事的职工数量、平均薪水和平均服务期限。
14.列出具备职员和工人的全名、部门名称和薪俸。
15.列出全部机关的详细音讯和机关人数。
16.列出各类办事的最低薪酬。
17.列出各种部门的MANAGE奇骏(老董)的最低薪水。
18.列出全数职工的年工资给,按年收入从低到高排序。

 

 

 

各答案如下,应接我们付出不出的解答方法。

 

  

——–1.列出足足有二个职员和工人的具有机关。———
SQL> select dname from dept where deptno in(select deptno from emp); 

上面具体来造访

测验数据

mysql> select * from emp;
+-------+----------+--------+--------+
| empno | ename    | sal    | deptno |
+-------+----------+--------+--------+
|  1001 | emp_1001 | 100.00 |     10 |
|  1002 | emp_1002 | 200.00 |     10 |
|  1003 | emp_1003 | 300.00 |     20 |
|  1004 | emp_1004 | 400.00 |     20 |
|  1005 | emp_1005 | 500.00 |     30 |
|  1006 | emp_1006 | 600.00 |     30 |
+-------+----------+--------+--------+
6 rows in set (0.00 sec)

在那之中,empno是员工编号,ename是职工姓名,sal是工薪,deptno是职员和工人所在部门号。

 

业务的须要是,求出种种单位福建中华南理艺术大学程公司资最高的职员和工人的连带新闻。

在MySQL5.6中,大家能够透过上边这些SQL来贯彻,

SELECT
    deptno,ename,sal 
FROM
    ( SELECT * FROM emp ORDER BY sal DESC ) t 
GROUP BY
    deptno;

 

结果如下,可以看看,其确实达成了预期效果与利益。

+--------+----------+--------+
| deptno | ename    | sal    |
+--------+----------+--------+
|     10 | emp_1002 | 200.00 |
|     20 | emp_1004 | 400.00 |
|     30 | emp_1006 | 600.00 |
+--------+----------+--------+

 

再来看看MySQL5.7的结果,竟然不平等。

+--------+----------+--------+
| deptno | ename    | sal    |
+--------+----------+--------+
|     10 | emp_1001 | 100.00 |
|     20 | emp_1003 | 300.00 |
|     30 | emp_1005 | 500.00 |
+--------+----------+--------+

 

其实,在MySQL5.7中,对该SQL举行了改写,改写后的SQL可通过explain(extended)

  • show warnings查看。

    mysql> explain select deptno,ename,sal from (select * from emp order by sal desc) t group by deptno;
    +—-+————-+——-+————+——+—————+——+———+——+——+———-+—————–+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +—-+————-+——-+————+——+—————+——+———+——+——+———-+—————–+
    | 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | Using temporary |
    +—-+————-+——-+————+——+—————+——+———+——+——+———-+—————–+
    1 row in set, 1 warning (0.00 sec)

    mysql> show warnings\G
    *** 1. row ***

    Level: Note
     Code: 1003
    

    Message: / select#1 / select slowtech.emp.deptno AS deptno,slowtech.emp.ename AS ename,slowtech.emp.sal AS sal from slowtech.emp group by slowtech.emp.deptno
    1 row in set (0.00 sec)

 

从改写后的SQL来看,其清除了子查询,导致结果不能贯彻预期效应,官方也印证了那或多或少,

 

广大人或者不认为然,以为没人会那样写,但在著名的stackoverflow中,该兑现的点赞数就有1十五个-总之其受众之广,稍差于前边提到的“方法二”(点赞数206个)。 

 

内需小心的是,该SQL在5.7中是不可能一向运维的,其会提示如下错误:

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 't.ename' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

 

这个与sql_mode有关,在MySQL 5.7中,sql_mode调节为了

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

其中,ONLY_FULL_GROUP_BY与group
by语句关于,其须要select列表里只可以出现分组列(即group
by前边的列)和聚合函数(sum,avg,max等),那也是SQL92的正儿八经。

 

但在专门的工作中,却时常看看开采访编写出上面这种SQL。

mysql> select deptno,ename,max(sal) from emp group by deptno;
+--------+----------+----------+
| deptno | ename    | max(sal) |
+--------+----------+----------+
|     10 | emp_1001 |   200.00 |
|     20 | emp_1003 |   400.00 |
|     30 | emp_1005 |   600.00 |
+--------+----------+----------+
3 rows in set (0.01 sec)

 实在不知道,这里的ename在事情层有啥意义,毕竟,他并不是工薪最高的那位职员和工人。

 

mysql> INSERT INTO emp (empno,ename) VALUES(2,’tom’);

DNAME

RESEARCH
SALES
ACCOUNTING
——–或——–
SQL> select dname from dept where deptno in(select deptno from emp group by deptno having count(deptno) >=1); 

分组求最值,MySQL的落到实处情势

实在分组求最值是二个很布满的急需。在职业中,也会有时被开拓同事问到。
上面具体来探望,MySQL中有怎么样完毕方式。

方法1

SELECT
    e.deptno,
    ename,
    sal 
FROM
    emp e,
    ( SELECT deptno, max( sal ) maxsal FROM emp GROUP BY deptno ) t 
WHERE
    e.deptno = t.deptno 
    AND e.sal = t.maxsal;

 

方法2

SELECT
    a.deptno,
    a.ename,
    a.sal 
FROM
    emp a
    LEFT JOIN emp b ON a.deptno = b.deptno 
    AND a.sal < b.sal 
WHERE
    b.sal IS NULL;

 那三种实现方式,其实是通用的,不独有适用于MySQL,也适用于别的主流关系型数据库。

 

方法3
MySQL 8.0出产了深入分析函数,其也可完结类似作用。

SELECT
    deptno,
    ename,
    sal 
FROM
    (
    SELECT
        deptno,
        ename,
        sal,
        LAST_VALUE ( sal ) OVER ( PARTITION BY deptno ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) maxsal 
    FROM
        emp 
    ) a 
WHERE
    sal = maxsal;

 

方法3:省略字段,可是值的个数和一一要和表里的相同;

DNAME

ACCOUNTING
RESEARCH
SALES

——–2.列出薪俸比“SMITH”多的享有职员和工人。———-
SQL> select * from emp where sal > (select sal from emp where ename = ‘SMITH’);
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
—– ———- ——— —– ———– ——— ——— ——
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987-4-19     4000.00               20
 7839 KING       PRESIDENT       1981-11-17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30
 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20
 7900 JAMES      CLERK      7698 1981-12-3      950.00               30
 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20
 7934 MILLER     CLERK      7782 1982-1-23     1300.00               10
  102 EricHu     Developer  1455 2011-5-26 1   5500.00     14.00     10
  104 huyong     PM         1455 2011-5-26 1   5500.00     14.00     10
  105 WANGJING   Developer  1455 2011-5-26 1   5500.00     14.00     10
 16 rows selected

——–3.列出装有职员和工人的姓名及其直接上级的姓名。———-
SQL> select a.ename,(select ename from emp b where b.empno=a.mgr) as boss_name from emp a; 
ENAME      BOSS_NAME
———- ———-
SMITH      FORD
ALLEN      BLAKE
WARD       BLAKE
JONES      KING
MARTIN     BLAKE
BLAKE      KING
CLARK      KING
SCOTT      JONES
KING       
TURNER     BLAKE
ADAMS      SCOTT
JAMES      BLAKE
FORD       JONES
MILLER     CLARK
EricHu     
huyong     
WANGJING    
17 rows selected

——–4.列出受雇日期早于其向来上级的兼具职员和工人。———-
SQL> select a.ename from emp a where a.hiredate<(select hiredate from emp b where b.empno=a.mgr); 

几种完结格局的习性相比

因上面测量检验案例的数据量太小,两种完结方式的结果都是秒出,仅凭实行计划很难直观地收看完毕情势的优劣。

上边换上数据量越来越大的测量试验数据,官方示例数据库employees中的dept_emp表,

表的连带音讯如下,当中emp_no是职员和工人编号,dept_no是部门编号,from_date是入职日期。

mysql> show create table dept_emp\G
*************************** 1. row ***************************
       Table: dept_emp
Create Table: CREATE TABLE `dept_emp` (
  `emp_no` int(11) NOT NULL,
  `dept_no` char(4) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  KEY `dept_no` (`dept_no`,`from_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> select count(*) from dept_emp;
+----------+
| count(*) |
+----------+
|   331603 |
+----------+
1 row in set (0.09 sec)

mysql> select * from dept_emp limit 1;
+--------+---------+------------+------------+
| emp_no | dept_no | from_date  | to_date    |
+--------+---------+------------+------------+
|  10001 | d005    | 1986-06-26 | 9999-01-01 |
+--------+---------+------------+------------+
1 row in set (0.00 sec)

 

方法1

mysql> select d.dept_no,d.emp_no,d.from_date from dept_emp d, (select dept_no,max(from_date) max_hiredate from dept_emp group by dept_no) t where d.dept_no=t.dept_no and d.from_date=t.max_hiredate;
…
12 rows in set (0.00 sec)

mysql> explain select d.dept_no,d.emp_no,d.from_date from dept_emp d, (select dept_no,max(from_date) max_hiredate from dept_emp group by dept_no) t where d.dept_no=t.dept_no and d.from_date=t.max_hiredate;
+----+-------------+------------+------------+-------+---------------+---------+---------+--------------------------+------+----------+----------------------
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref                      | rows | filtered | Extra                
+----+-------------+------------+------------+-------+---------------+---------+---------+--------------------------+------+----------+----------------------
|  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL                     |    9 |   100.00 | Using where          
|  1 | PRIMARY     | d          | NULL       | ref   | dept_no       | dept_no | 19      | t.dept_no,t.max_hiredate |    5 |   100.00 | NULL                 
|  2 | DERIVED     | dept_emp   | NULL       | range | dept_no       | dept_no | 16      | NULL                     |    9 |   100.00 | Using index for group-by
+----+-------------+------------+------------+-------+---------------+---------+---------+--------------------------+------+----------+----------------------

 

方法2

mysql> explain select a.dept_no,a.emp_no,a.from_date from dept_emp a left join dept_emp b on a.dept_no=b.dept_no and a.from_date < b.from_date where b.from_date is null;
+----+-------------+-------+------------+------+---------------+---------+---------+--------------------+--------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref                | rows   | filtered | Extra                    |
+----+-------------+-------+------------+------+---------------+---------+---------+--------------------+--------+----------+--------------------------+
|  1 | SIMPLE      | a     | NULL       | ALL  | NULL          | NULL    | NULL    | NULL               | 331008 |   100.00 | NULL                     |
|  1 | SIMPLE      | b     | NULL       | ref  | dept_no       | dept_no | 16      | slowtech.a.dept_no |  41376 |    19.00 | Using where; Using index |
+----+-------------+-------+------------+------+---------------+---------+---------+--------------------+--------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)

 

方法3

mysql> select dept_no,emp_no,from_date from ( select dept_no,emp_no,from_date,last_value(from_date) over(partition by dept_no order by from_date rows between unbounded preceding and unbounded following) max_hiredate from dept_emp) a where from_date=max_hiredate;
…
12 rows in set (1.57 sec)

mysql> desc select dept_no,emp_no,from_date from ( select dept_no,emp_no,from_date,last_value(from_date) over(partition by dept_no order by from_date rows between unbounded preceding and unbounded following) max_hiredate from dept_emp) a where from_date=max_hiredate;
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 331008 |   100.00 | Using where    |
|  2 | DERIVED     | dept_emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 331008 |   100.00 | Using filesort |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
2 rows in set, 2 warnings (0.00 sec)

 

从实行时间上看,

主意1的年月最短,在有复合索引(deptno,
fromdate)的情状下,结果瞬间就出来了,即便在尚未索引的情形下,也只消耗了0.75s。

主意2的日子最长,3个钟头照旧没出结果。同样的数目,同样的SQL,放到Oracle查,也消耗了87分49秒。

主意3的岁月相比较原则性,无论是不是留存索引,都维持在1.5s左右,比办法1的耗费时间要久。

这里,对前面提到的,MySQL
5.7中不再包容的达成方式也做了个测验,在尚未此外索引的场所下,其安静在0.7s(质量并不弱,怪不得有人使用),而同样情形下,方法1平稳在0.5s(哈,MySQL
5.6居然比8.0还快)。但与办法1例外的是,其不恐怕透过索引进行优化。

 

从实践陈设上看,

方法1, 先将group
by的结果放到有的时候表中,然后再将该不经常表作为驱动表,来和dept_emp表举办关联合检查询。驱动表小(唯有9条记下),关联列又有目录,无怪乎,结果能秒出。

方法2, 两表关联。其犯了SQL优化中的多个禁忌。

   1. 驱动表太大,其有331603条记下。

   2. 被驱动表纵然也许有目录,但从推行铺排上看,其只利用了复合索引 
(dept_no,
from_date)中的dept_no,而dept_no的采取率又太低,毕竟独有9个机关。

艺术3,
先把分析的结果放到三个偶然表中,然后再对该偶然表进行拍卖。其进展了一遍全表扫描,二遍是本着dept_emp表,一次是本着一时表。

 

因而,对于分组求最值的供给,提出选拔格局1,其不但符合SQL标准,查询质量上也是最佳的,特别是在联合索引的景况下。

 

PS:

经大神指导,对在此之前涉嫌的,MySQL
5.7中不再宽容的兑现情势,实际能够透过调度optimizer_switch来加以规避

set optimizer_switch='derived_merge=off';

derived_merge是MySQL 5.7引进的,其会企图将Derived
Table(派生表,from后边的子查询),视图引用,公用表表达式(Common table
expressions)与外层查询进行合併。如,

SELECT *
  FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived_t2
          ON t1.f2=derived_t2.f1
  WHERE t1.f1 > 0;

改写为

SELECT *
 FROM t1 JOIN (SELECT DISTINCT f1 FROM t2) AS derived_t2
         ON t1.f1=derived_t2.f1;

 

mysql> INSERT INTO emp VALUES (3,’bob’,1002);

ENAME

SMITH
ALLEN
WARD
JONES
BLAKE
CLARK 
6 rows selected

——–5.列出单位名称和那么些单位的职工信息,同时列出那多少个从没员工的部门———-
SQL> select a.dname,b.empno,b.ename,b.job,b.mgr,b.hiredate,b.sal,b.deptno
  2  from dept a left join emp b on a.deptno=b.deptno;
 
DNAME          EMPNO ENAME      JOB         MGR HIREDATE          SAL DEPTNO
————– —– ———- ——— —– ———– ——— ——
RESEARCH        7369 SMITH      CLERK      7902 1980-12-17     800.00     20
SALES           7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00     30
SALES           7521 WARD       SALESMAN   7698 1981-2-22     1250.00     30
RESEARCH        7566 JONES      MANAGER    7839 1981-4-2      2975.00     20
SALES           7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00     30
SALES           7698 BLAKE      MANAGER    7839 1981-5-1      2850.00     30
ACCOUNTING      7782 CLARK      MANAGER    7839 1981-6-9      2450.00     10
RESEARCH        7788 SCOTT      ANALYST    7566 1987-4-19     4000.00     20
ACCOUNTING      7839 KING       PRESIDENT       1981-11-17    5000.00     10
SALES           7844 TURNER     SALESMAN   7698 1981-9-8      1500.00     30
RESEARCH        7876 ADAMS      CLERK      7788 1987-5-23     1100.00     20
SALES           7900 JAMES      CLERK      7698 1981-12-3      950.00     30
RESEARCH        7902 FORD       ANALYST    7566 1981-12-3     3000.00     20
ACCOUNTING      7934 MILLER     CLERK      7782 1982-1-23     1300.00     10
ACCOUNTING       102 EricHu     Developer  1455 2011-5-26 1   5500.00     10
ACCOUNTING       104 huyong     PM         1455 2011-5-26 1   5500.00     10
ACCOUNTING       105 WANGJING   Developer  1455 2011-5-26 1   5500.00     10
50abc                                                                 
OPERATIONS                                                            
Developer                                                          
 
20 rows selected

——–6.列出富有“CLEEvoqueK”(办事员)的真名及其部门名称。———-
SQL> select a.ename,b.dname from emp a join dept b on a.deptno=b.deptno and a.job=’CLERK’; 
ENAME      DNAME
———- ————–
SMITH      RESEARCH
ADAMS      RESEARCH
JAMES      SALES
MILLER     ACCOUNTING

——–7.列出最低薪给陵高校于1500的种种工作。———-
SQL> select distinct job as HighSalJob from emp group by job having min(sal)>1500; 

2.删除 DELETE

HIGHSALJOB

ANALYST
Developer
MANAGER
PM
PRESIDENT

——–8.列出在机构“SALES”(贩卖部)事业的职工的真名,假定不亮堂发售部的单位编号。———-
SQL> select ename from emp where deptno=(select deptno from dept where dname=’SALES’); 

办法1:删除表中的保有数据;

ENAME

ALLEN
WARD
MARTIN
BLAKE
TURNER
JAMES 
6 rows selected

——–9.列出薪酬高于集团平均工资的富有职员和工人。———-
SQL> select ename from emp where sal>(select avg(sal) from emp); 

语法:DELETE FROM  表名;

ENAME

JONES
BLAKE
SCOTT
KING
FORD
EricHu
huyong
WANGJING 
8 rows selected

——–10.列出与“SCOTT”从事一样职业的有所职工。——–
SQL> select ename from emp where job=(select job from emp where ename=’SCOTT’);

mysql> DELETE FROM emp;

 ENAME

SCOTT
FORD

——–11.列出薪俸等于部门30中职员和工人的薪金的具有职工的姓名和工资。———
SQL> select a.ename,a.sal from emp a where a.sal in (select b.sal
  2  from emp b where b.deptno=30) and a.deptno<>30; 
ENAME            SAL
———- ———

——–12.列出薪给高于在机关30干活的享有职工的薪饷的职员和工人姓名和报酬。———
SQL> select ename,sal from emp where sal>(select max(sal) from emp where deptno=30); 
ENAME            SAL
———- ———
JONES        2975.00
SCOTT        4000.00
KING         5000.00
FORD         3000.00
EricHu       5500.00
huyong       5500.00
WANGJING     5500.00 
7 rows selected

——–13.列出在各种机关办事的职员和工人数量、平均工资和平均服务时间限制。———
SQL> select (select b.dname from dept b where a.deptno=b.deptno) as deptname ,count(deptno) as deptcount,avg(sal) as deptavgsal
  2  from emp a group by deptno; 
DEPTNAME        DEPTCOUNT DEPTAVGSAL
————– ———- ———-
ACCOUNTING              6 4208.33333
RESEARCH                5       2375
SALES                   6 1566.66666

——–14.列出全数职工的全名、部门名称和报酬。———
SQL> select a.ename,(select b.dname from dept b where b.deptno=a.deptno) as deptname,sal from emp a; 
 
ENAME      DEPTNAME             SAL
———- ————– ———
SMITH      RESEARCH          800.00
ALLEN      SALES            1600.00
WARD       SALES            1250.00
JONES      RESEARCH         2975.00
MARTIN     SALES            1250.00
BLAKE      SALES            2850.00
CLARK      ACCOUNTING       2450.00
SCOTT      RESEARCH         4000.00
KING       ACCOUNTING       5000.00
TURNER     SALES            1500.00
ADAMS      RESEARCH         1100.00
JAMES      SALES             950.00
FORD       RESEARCH         3000.00
澳门金沙vip,MILLER     ACCOUNTING       1300.00
EricHu     ACCOUNTING       5500.00
huyong     ACCOUNTING       5500.00
WANGJING   ACCOUNTING       5500.00
 
17 rows selected

——–15.列出全数单位的详细音信和机关人数。———
SQL> select a.deptno,a.dname,a.loc,(select count(deptno) from emp b where b.deptno=a.deptno group by b.deptno) as deptcount from dept a; 
DEPTNO DNAME          LOC            DEPTCOUNT
—— ————– ————- ———-
    10 ACCOUNTING     NEW YORK               6
    20 RESEARCH       DALLAS                 5
    30 SALES          CHICAGO                6
    40 OPERATIONS     BOSTON        
    50 50abc          50def         
    60 Developer      HaiKou     
 
6 rows selected

——–16.列出各个办事的最低工资。———
SQL> select job,avg(sal) from emp group by job;
 
JOB         AVG(SAL)
——— ———-
ANALYST         3500
CLERK         1037.5
Developer       5500
MANAGER   2758.33333
PM              5500
PRESIDENT       5000
SALESMAN        1400
 
7 rows selected

——–17.列出各类部门的MANAGE奇骏(首席执行官)的最低薪给。——–
SQL> select deptno,min(sal) from emp where job=’MANAGER’ group by deptno;
 
DEPTNO   MIN(SAL)
—— ———-
    10       2450
    20       2975
30       2850

——–18.列出全部职员和工人的年收入水,按每月收入从低到高排序。———
SQL> select ename,(sal+nvl(comm,0))*12 as salpersal from emp order by salpersal;
 
ENAME       SALPERSAL
———- ———-
SMITH            9600
JAMES           11400
ADAMS           13200
MILLER          15600
TURNER          18000
WARD            21000
ALLEN           22800
CLARK           29400
MARTIN          31800
BLAKE           34200
JONES           35700
FORD            36000
SCOTT           48000
KING            60000
EricHu          66168
huyong          66168
WANGJING        66168
 
17 rows selected

 

 

措施2:删除相符条件的多少;

语法: DELETE FROM 表名 WHERE 条件;

mysql> DELETE FROM emp WHERE ename =’tom’;

操作符:

=等于

<小于

<=小于等于

>大于

>=大于等于

<>不等于  !=

BETWEEN 下限值 AND  上线值

介怀:下限值和上线值是总结的。

如:mysql> DELETE FROM emp  WHERE empno BETWEEN 2 AND 4;  包含2和4;

逻辑表明式:

连日来大家的口径表明式的

AND  并且  全数的标准都要满意的事态下才会去相配。

mysql> DELETE FROM emp WHERE empno>=2 AND empno<=4;

O翼虎 只怕    满足当中之一的标准都能同盟。

mysql> DELETE FROM emp WHERE empno=1 OR empno =5;

3.更新数据UPDATE

不带条件能够退换全数字段的值:

mysql> UPDATE emp SET deptno =1001;

带条件,改变切合条件的数目:

mysql> UPDATE emp SET deptno =1002 WHERE ename=’tom’;

退换四个字段的值

mysql> UPDATE emp SET empno=5,deptno=1003 WHERE ename =’tom’;

/*二、轻易询问DQL (data query language)

1.询问全体列的具有数据;

使用*的频率相对相当的低,

select * from 表名;

select sid,sname ,birthday from 表名;

2.查询钦定列的多寡;

select 字段1,字段2.。。。from  表名;

3.去重查询

SELECT DISTINCT sname FROM student;

4.列盘算:数值列能够举办加减乘除运算

SELECT sid,sname ,sal+comm  FROM student;

5.字段的别称 ,as能够省略,小名的引号能够回顾

SELECT sid AS 编号,sname AS 姓名,birthday AS 生日 FROM student;

6.准则查询

SELECT * FROM student WHERE birthday >’2000-02-01’AND sex=’女’ OR
sid=1 ;

in关键词:

字段的值切合括号里的任何三个就满足条件

SELECT * FROM student WHERE sid IN(1,4,9);

SELECT * FROM student WHERE sname IN(‘tom’,’bob’) ;

not in: 不是括号里的别的三个就满足条件

is null :值为null;

is not null:值不为null;

7.模糊查询  like

%  代表私行八个字符

_  代表叁个字符

select * from student where sname like ‘张’;

select * from student where sname like ‘___张’;

8.排序  order by

asc :ascend 升序,默认为升序;

desc : descend 降序;

select * from student order by sal asc;

按四个字段排序:

语法: select * from 表名 order by 字段1 desc ,字段2  desc;

select * from student order by sex desc ,sal  desc;

9.limit 限量重返的条数  日常用于数据分页

limit m,n;  m代表开始的目录,n代表个数;

SELECT * FROM emp LIMIT 15,5;

limit n;  n代表个数,索引从0初始;

  1. null和其余值总括都得null;

函数:ifnull(字段,值); 若该字段中有值为null;

SELECT ename ,sal+IFNULL(comm,0) FROM emp;

函数:concat(字段,值或字段);能够把字段的值拼接起来;

SELECT ename ,CONCAT(sal,’元’) sal FROM emp;

*/

1.创立学生表student:学生编号sid 主键自拉长,姓名sname 非空,性别sex 
非空,生日birthday ,年龄age ,班级编号cid ;

mysql> create table student(sid int primary key auto_increment,sname
varchar(10)

not null,sex varchar(10) not null,birthday date,age int,cid int);

Query OK, 0 rows affected (0.03 sec)

那是学生表student:

+———-+————-+——+—–+———+—————-+

| Field    | Type        | Null | Key | Default | Extra          |

+———-+————-+——+—–+———+—————-+

| sid      | int(11)    | NO  | PRI | NULL    | auto_increment |

| sname    | varchar(10) | NO  |    |        |                |

| sex      | varchar(10) | NO  |    |        |                |

| birthday | date        | YES  |    | NULL    |                |

| age      | int(11)    | YES  |    | NULL    |                |

| cid      | int(11)    | YES  |    | NULL    |                |

+———-+————-+——+—–+———+—————-+

2.成立班级表class: 班级编号 cid 主键 ,班级名称cname 非空;

mysql> create table class(cid int primary key,cname varchar(10) not
null);

Query OK, 0 rows affected (0.01 sec)

mysql> desc class;

+——-+————-+——+—–+———+——-+

| Field | Type        | Null | Key | Default | Extra |

+——-+————-+——+—–+———+——-+

| cid  | int(11)    | NO  | PRI |        |      |

| cname | varchar(10) | NO  |    |        |      |

+——-+————-+——+—–+———+——-+

3.给学员表加多一个字段:住址address;

mysql> alter table student add column address varchar(10);

Query OK, 0 rows affected (0.09 sec)

Records: 0  Duplicates: 0  Warnings: 0

那是充实的字段address:

mysql> desc student;

+———-+————-+——+—–+———+—————-+

| Field    | Type        | Null | Key | Default | Extra          |

+———-+————-+——+—–+———+—————-+

| sid      | int(11)    | NO  | PRI | NULL    | auto_increment |

| sname    | varchar(10) | NO  |    |        |                |

| sex      | varchar(10) | NO  |    |        |                |

| birthday | date        | YES  |    | NULL    |                |

| age      | int(11)    | YES  |    | NULL    |                |

| cid      | int(11)    | YES  |    | NULL    |                |

| address  | varchar(10) | YES  |    | NULL    |                |

+———-+————-+——+—–+———+—————-+

7 rows in set (0.01 sec)

4.修改学生表性别字段为gender;

mysql> alter table student change sex gender varchar(10);

Query OK, 0 rows affected (0.02 sec)

Records: 0  Duplicates: 0  Warnings: 0

修改后的student表:

mysql> desc student;

+———-+————-+——+—–+———+—————-+

| Field    | Type        | Null | Key | Default | Extra          |

+———-+————-+——+—–+———+—————-+

| sid      | int(11)    | NO  | PRI | NULL    | auto_increment |

| sname    | varchar(10) | NO  |    |        |                |

| gender  | varchar(10) | YES  |    | NULL    |                |

| birthday | date        | YES  |    | NULL    |                |

| age      | int(11)    | YES  |    | NULL    |                |

| cid      | int(11)    | YES  |    | NULL    |                |

| address  | varchar(10) | YES  |    | NULL    |                |

+———-+————-+——+—–+———+—————-+

7 rows in set (0.01 sec)

5.给birthday加多暗中认可值为两千-01-01;

mysql> alter table student modify birthday date default ‘2000-01-01’;

Query OK, 0 rows affected (0.03 sec)

Records: 0  Duplicates: 0  Warnings: 0

退换birthday的暗中同意值: