《JavaWeb从入门到改行》很好的复习资料: SQL语句到底怎么写？

本文用到的数据库如下：

  1 CREATE DATABASE exam;
  2 /*创建部门表*/
  3 CREATE TABLE dept(
  4     deptno        INT     PRIMARY KEY,
  5     dname        VARCHAR(50),
  6     loc         VARCHAR(50)
  7 );
  8 
  9 /*创建雇员表*/
 10 CREATE TABLE emp(
 11     empno        INT     PRIMARY KEY,
 12     ename        VARCHAR(50),
 13     job        VARCHAR(50),
 14     mgr        INT,
 15     hiredate    DATE,
 16     sal        DECIMAL(7,2),
 17     COMM         DECIMAL(7,2),
 18     deptno        INT,
 19     CONSTRAINT fk_emp FOREIGN KEY(mgr) REFERENCES emp(empno) ,
 20     //CONSTRAINT fk_emp_dept FOREIGN KEY(deptno) REFERENCES dept(deptno);
 21     //本来应该添加这个外键约束，但是为了以后做案列所以不加这个约束，因为加了这个约束，那么emp表中deptno的值就必须是dept表中的主键的某一值
 22 );
 23 
 24 /*创建工资等级表*/
 25 CREATE TABLE salgrade(
 26     grade        INT     PRIMARY KEY,
 27     losal        INT,
 28     hisal        INT
 29 );
 30 
 31 /*创建学生表*/
 32 CREATE TABLE stu(
 33     sid        INT     PRIMARY KEY,
 34     sname        VARCHAR(50),
 35     age        INT,
 36     gander        VARCHAR(10),
 37     province    VARCHAR(50),
 38     tuition        INT
 39 );
 40 
 41 /*插入dept表数据*/
 42 INSERT INTO dept VALUES (10, '教研部', '北京');
 43 INSERT INTO dept VALUES (20, '学工部', '上海');
 44 INSERT INTO dept VALUES (30, '销售部', '广州');
 45 INSERT INTO dept VALUES (40, '财务部', '武汉');
 46 
 47 /*插入emp表数据*/
 48 INSERT INTO emp VALUES (1009, '曾阿牛', '董事长', NULL, '2001-11-17', 50000, NULL, 10);
 49 INSERT INTO emp VALUES (1004, '刘备', '经理', 1009, '2001-04-02', 29750, NULL, 20);
 50 INSERT INTO emp VALUES (1006, '关羽', '经理', 1009, '2001-05-01', 28500, NULL, 30);
 51 INSERT INTO emp VALUES (1007, '张飞', '经理', 1009, '2001-09-01', 24500, NULL, 10);
 52 INSERT INTO emp VALUES (1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000, NULL, 20);
 53 INSERT INTO emp VALUES (1013, '庞统', '分析师', 1004, '2001-12-03', 30000, NULL, 20);
 54 INSERT INTO emp VALUES (1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000, 3000, 30);
 55 INSERT INTO emp VALUES (1003, '殷天正', '销售员', 1006, '2001-02-22', 12500, 5000, 30);
 56 INSERT INTO emp VALUES (1005, '谢逊', '销售员', 1006, '2001-09-28', 12500, 14000, 30);
 57 INSERT INTO emp VALUES (1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000, 0, 30);
 58 INSERT INTO emp VALUES (1012, '程普', '文员', 1006, '2001-12-03', 9500, NULL, 30);
 59 INSERT INTO emp VALUES (1014, '黄盖', '文员', 1007, '2002-01-23', 13000, NULL, 10);
 60 INSERT INTO emp VALUES (1011, '周泰', '文员', 1008, '2007-05-23', 11000, NULL, 20);
 61 INSERT INTO emp VALUES (1001, '甘宁', '文员', 1013, '2000-12-17', 8000, NULL, 20);
 62 INSERT INTO emp VALUES (1015, '张三', '保洁员', 1009, '2001-09-01', 24500, 50000, 50);  //注意 ，在部门表中根本没有50部门
 63 
 64 /*插入salgrade表数据*/
 65 INSERT INTO salgrade VALUES (1, 7000, 12000);
 66 INSERT INTO salgrade VALUES (2, 12010, 14000);
 67 INSERT INTO salgrade VALUES (3, 14010, 20000);
 68 INSERT INTO salgrade VALUES (4, 20010, 30000);
 69 INSERT INTO salgrade VALUES (5, 30010, 99990);
 70 
 71 /*插入stu表数据*/
 72 INSERT INTO `stu` VALUES ('1', '王永', '23', '男', '北京', '1500');
 73 INSERT INTO `stu` VALUES ('2', '张雷', '25', '男', '辽宁', '2500');
 74 INSERT INTO `stu` VALUES ('3', '李强', '22', '男', '北京', '3500');
 75 INSERT INTO `stu` VALUES ('4', '宋永合', '25', '男', '北京', '1500');
 76 INSERT INTO `stu` VALUES ('5', '叙美丽', '23', '女', '北京', '1000');
 77 INSERT INTO `stu` VALUES ('6', '陈宁', '22', '女', '山东', '2500');
 78 INSERT INTO `stu` VALUES ('7', '王丽', '21', '女', '北京', '1600');
 79 INSERT INTO `stu` VALUES ('8', '李永', '23', '男', '北京', '3500');
 80 INSERT INTO `stu` VALUES ('9', '张玲', '23', '女', '广州', '2500');
 81 INSERT INTO `stu` VALUES ('10', '啊历', '18', '男', '山西', '3500');
 82 INSERT INTO `stu` VALUES ('11', '王刚', '23', '男', '湖北', '4500');
 83 INSERT INTO `stu` VALUES ('12', '陈永', '24', '男', '北京', '1500');
 84 INSERT INTO `stu` VALUES ('13', '李雷', '24', '男', '辽宁', '2500');
 85 INSERT INTO `stu` VALUES ('14', '李沿', '22', '男', '北京', '3500');
 86 INSERT INTO `stu` VALUES ('15', '王小明', '25', '男', '北京', '1500');
 87 INSERT INTO `stu` VALUES ('16', '王小丽', '23', '女', '北京', '1000');
 88 INSERT INTO `stu` VALUES ('17', '唐宁', '22', '女', '山东', '2500');
 89 INSERT INTO `stu` VALUES ('18', '唐丽', '21', '女', '北京', '1600');
 90 INSERT INTO `stu` VALUES ('19', '啊永', '23', '男', '北京', '3500');
 91 INSERT INTO `stu` VALUES ('20', '唐玲', '23', '女', '广州', '2500');
 92 INSERT INTO `stu` VALUES ('21', '叙刚', '18', '男', '山西', '3500');
 93 INSERT INTO `stu` VALUES ('22', '王累', '23', '男', '湖北', '4500');
 94 INSERT INTO `stu` VALUES ('23', '赵安', '23', '男', '北京', '1500');
 95 INSERT INTO `stu` VALUES ('24', '关雷', '25', '男', '辽宁', '2500');
 96 INSERT INTO `stu` VALUES ('25', '李字', '22', '男', '北京', '3500');
 97 INSERT INTO `stu` VALUES ('26', '叙安国', '25', '男', '北京', '1500');
 98 INSERT INTO `stu` VALUES ('27', '陈浩难', '23', '女', '北京', '1000');
 99 INSERT INTO `stu` VALUES ('28', '陈明', '22', '女', '山东', '2500');
100 INSERT INTO `stu` VALUES ('29', '孙丽', '21', '女', '北京', '1600');
101 INSERT INTO `stu` VALUES ('30', '李治国', '23', '男', '北京', '3500');
102 INSERT INTO `stu` VALUES ('31', '张娜', '23', '女', '广州', '2500');
103 INSERT INTO `stu` VALUES ('32', '安强', '18', '男', '山西', '3500');
104 INSERT INTO `stu` VALUES ('33', '王欢', '23', '男', '湖北', '4500');
105 INSERT INTO `stu` VALUES ('34', '周天乐', '23', '男', '北京', '1500');
106 INSERT INTO `stu` VALUES ('35', '关雷', '25', '男', '辽宁', '2500');
107 INSERT INTO `stu` VALUES ('36', '吴强', '22', '男', '北京', '3500');
108 INSERT INTO `stu` VALUES ('37', '吴合国', '25', '男', '北京', '1500');
109 INSERT INTO `stu` VALUES ('38', '正小和', '23', '女', '北京', '1000');
110 INSERT INTO `stu` VALUES ('39', '吴丽', '22', '女', '山东', '2500');
111 INSERT INTO `stu` VALUES ('40', '冯含', '21', '女', '北京', '1600');
112 INSERT INTO `stu` VALUES ('41', '陈冬', '23', '男', '北京', '3500');
113 INSERT INTO `stu` VALUES ('42', '关玲', '23', '女', '广州', '2500');
114 INSERT INTO `stu` VALUES ('43', '包利', '18', '男', '山西', '3500');
115 INSERT INTO `stu` VALUES ('44', '威刚', '23', '男', '湖北', '4500');
116 INSERT INTO `stu` VALUES ('45', '李永', '23', '男', '北京', '1500');
117 INSERT INTO `stu` VALUES ('46', '张关雷', '25', '男', '辽宁', '2500');
118 INSERT INTO `stu` VALUES ('47', '送小强', '22', '男', '北京', '3500');
119 INSERT INTO `stu` VALUES ('48', '关动林', '25', '男', '北京', '1500');
120 INSERT INTO `stu` VALUES ('49', '苏小哑', '23', '女', '北京', '1000');
121 INSERT INTO `stu` VALUES ('50', '赵宁', '22', '女', '山东', '2500');
122 INSERT INTO `stu` VALUES ('51', '陈丽', '21', '女', '北京', '1600');
123 INSERT INTO `stu` VALUES ('52', '钱小刚', '23', '男', '北京', '3500');
124 INSERT INTO `stu` VALUES ('53', '艾林', '23', '女', '广州', '2500');
125 INSERT INTO `stu` VALUES ('54', '郭林', '18', '男', '山西', '3500');
126 INSERT INTO `stu` VALUES ('55', '周制强', '23', '男', '湖北', '4500');

数据库设计

emp
dept
salgrade
stu

.sql文件下载

单表查询与多表查询案列

单表查询练习

/*1\. 查询出部门编号为30的所有员工*/
SELECT *
FROM emp
WHERE deptno=30;

/*2\. 查询所有销售员的姓名、编号和部门编号。*/
SELECT ename, empno, deptno
FROM emp
WHERE job='销售员';

/*3\. 找出奖金高于工资的员工。*/
SELECT *
FROM emp
WHERE comm > sal

/*4\. 找出奖金高于工资60%的员工。*/
SELECT *
FROM emp
WHERE comm > sal*0.6;

/*5\. 找出部门编号为10中所有经理，和部门编号为20中所有销售员的详细资料。*/
SELECT *
FROM emp
WHERE (deptno=10 AND job='经理') OR (deptno=20 AND job='销售员')

/*6\. 找出部门编号为10中所有经理，部门编号为20中所有销售员，还有即不是经理又不是销售员但其工资大或等于20000的所有员工详细资料。*/
SELECT *
FROM emp
WHERE (deptno=10 AND job='经理') OR (deptno=20 AND job='销售员') OR (job NOT IN ('经理', '销售员') AND sal >= 20000)

/*8\. 无奖金或奖金低于1000的员工。*/
SELECT *
FROM emp
WHERE comm IS NULL OR comm < 1000

/*9\. 查询名字由三个字组成的员工。*/
SELECT *
FROM emp
WHERE ename LIKE '___'

/*10.查询2000年入职的员工。*/
SELECT *
FROM emp
WHERE hiredate LIKE '2000-%'

/*11\. 查询所有员工详细信息，用编号升序排序*/
SELECT * 
FROM emp
ORDER BY empno

/*12\. 查询所有员工详细信息，用工资降序排序，如果工资相同使用入职日期升序排序*/
SELECT *
FROM emp
ORDER BY sal DESC, hiredate ASC

/*13\. 查询每个部门的平均工资*/
SELECT deptno, AVG(sal)
FROM emp
GROUP BY deptno

/*14\. 查询每个部门的雇员数量。*/
SELECT deptno, COUNT(*)
FROM emp
GROUP BY deptno

/*15\. 查询每种工作的最高工资、最低工资、人数*/
SELECT job, MAX(sal), MIN(sal), COUNT(*)
FROM emp
GROUP BY job

多表查询练习

/*
1. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。
列：d.deptno, d.dname, d.loc, 部门人数
表：dept d, emp e
条件：e.deptno=d.deptno
*/
SELECT d.*, z1.cnt 
FROM dept d, (SELECT deptno, COUNT(*) cnt FROM emp GROUP BY deptno) z1
WHERE d.deptno = z1.deptno

/*
3. 列出所有员工的姓名及其直接上级的姓名。
列：员工姓名、上级姓名
表：emp e, emp m
条件：员工的mgr = 上级的empno
*/

SELECT e.ename, IFNULL(m.ename, 'BOSS') 领导
FROM emp e LEFT OUTER JOIN emp m
ON e.mgr=m.empno


/*
4. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。
列：e.empno, e.ename, d.dname
表：emp e, emp m, dept d
条件：e.hiredate<m.hiredate
*/

SELECT e.empno, e.ename, d.dname
FROM emp e, emp m, dept d
WHERE e.mgr=m.empno AND e.hiredate<m.hiredate AND e.deptno=d.deptno

/*
5. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
列：* 
表：emp e, dept d
条件：e.deptno=d.deptno
*/
SELECT *
FROM emp e RIGHT OUTER JOIN dept d
ON e.deptno=d.deptno

/*
7. 列出最低薪金大于15000的各种工作及从事此工作的员工人数。
列：job, count(*)
表：emp e
条件：min(sal) > 15000
分组：job
*/
SELECT job, COUNT(*)
FROM emp e
GROUP BY job
HAVING MIN(sal) > 15000


/*
8. 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。
列：e.ename
表：emp
条件：e.deptno=(select deptno from dept where dname='销售部')
*/

SELECT *
FROM emp e
WHERE e.deptno=(SELECT deptno FROM dept WHERE dname='销售部')



/*
9. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。
列：* 
表：emp e
条件：sal>(查询出公司的平均工资)
*/
SELECT e.*, d.dname, m.ename, s.grade
FROM emp e, dept d, emp m, salgrade s
WHERE e.sal>(SELECT AVG(sal) FROM emp) AND e.deptno=d.deptno AND e.mgr=m.empno AND e.sal BETWEEN s.losal AND s.hisal

---------------

SELECT e.*, d.dname, m.ename, s.grade
FROM 
  emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno
        LEFT OUTER JOIN emp m ON e.mgr=m.empno
        LEFT OUTER JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
WHERE e.sal>(SELECT AVG(sal) FROM emp)


SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM salgrade;



/*
10.列出与庞统从事相同工作的所有员工及部门名称。
列：e.*, d.dname
表：emp e, dept d
条件：job=(查询出庞统的工作)
*/

SELECT e.*, d.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno AND job=(SELECT job FROM emp WHERE ename='庞统')



/*
11.列出薪金高于在部门30工作的所有员工的薪金　的员工姓名和薪金、部门名称。
列：e.ename, e.sal, d.dname
表：emp e, dept d
条件；sal>all (30部门薪金)
*/
SELECT e.ename, e.sal, d.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno AND sal > ALL (SELECT sal FROM emp WHERE deptno=30)

/*
13.查出年份、利润、年度增长比
*/
SELECT y1.*, IFNULL(CONCAT((y1.zz-y2.zz)/y2.zz*100, '%'), '0%') 增长比
FROM tb_year y1 LEFT OUTER JOIN tb_year y2
ON y1.year=y2.year+1;

写在前面：文章以MySQL为软件，关于MySQL这个软件的基本应用，请点击这儿学习

什么是SQL

SQL : Structured Query Language (结构化查询语言)。作用是：客户端使用SQL来操作服务器。
SQL标注：是由国际标准化组织（ISO）制定的，对DBMS的统一操作方式。
SQL方言：每个DBMS都除了会遵循标准之外，会有自己的方言。也就是自己所以特有的特殊的语句或者句法。
SQL语法：

SQL语句可以在单行或者多行书写，以分号结束，没有写分号，可以换行；
可以使用空格或者缩进来增强语句的可读性；
MySQL 是不区分大小写的，不过建议大写。
数据库中所有的字符串类型都需要用单引号，不能使用双引号

SQL语句的分类

DDL （Data Definition Language ）：数据定义语言，用来对数据库或表的结构进行操作。也就是创建、删除、修改库和表结构
DML (Data Manipulation Language ) : 数据操作语言，用来对表的记录进行更新，也就是对表的记录进行增、删、改
DQL (Dat Query Language ) : 数据查询语言，用来对表的记录进行查询
DCL (Dat Control Language ) : 数据控制语言，用来定义访问权限和安全级别

数据库存储的数据类型

点击这里下载数据类型文档

DDL

DDL _ 对库的操作

查看所有数据库： SHOW DATABASES ;
切换（选择要操作的）数据库：USE 数据库名 ;
创建数据库：CREATE DATABASE mydb1 [CHARSET=utf8] //方框号是表示可选择的。语句中没有方框号
删除数据库：DROP DATABASE mydb1
修改数据库编码：ALTER DATABASE mydb1 CHARACTER SET utf8

DDL _ 对表的操作

创建表

  CREATE TABLE  表名(
    列名 列类型,
    列名 列类型,
    ...
    列名 列类型
  );

查看当前数据库中所有表名称：SHOW TABLES;
查看表结构：DESC 表名;
删除表：DROP TABLE 表名;
修改_添加列：

    ALTER TABLE 表名 
    ADD (
      列名 列类型,
      列名 列类型,
      ...
    );

修改_修改列类型 ALTER TABLE 表名 MODIFY 列名列类型;
修改_修改列名：ALTER TABLE 表名 CHANGE 原列名新列名列类型;
修改_删除列：ALTER TABLE 表名 DROP 列名;
修改_修改表名称：ALTER TABLE 原表名 RENAME TO 新表名;

DML

where条件语法

条件必须是一个boolean类型的值或表达式
where 属性判断
条件的运算符：

运算符	说明举例	运算符	说明举例
=	等于	!=	不等于
<>	大于小于	>	大于
<	小于	>=	大于等于
<=	小于等于WHERE age >= 18 AND age <= 80	between..and	WHERE age BETWEEN 18 AND 80
in(...)	WHERE name IN ('zhangSan', 'liSi')包括zhangSan和liSi	is null	WHERE age IS NULL；选中age为NULL
not	非	or	或者
and	和

WHERE age >= 18 AND age <= 80 选中年龄在大于18和小于80
WHERE age BETWEEN 18 AND 80 选中年龄在大于18和小于80

WHERE name='zhangSan' OR name='liSi' 选中名字是zhanSan或者liSi的
WHERE name IN ('zhangSan', 'liSi') ; 选中名字是zhangSan和liSi
WHERE age IS NULL 选择年龄值为空的
WHERE age IS NOT NULL选择年龄值不为空的

插入数据

INTERT INTO 表名(列名1,列名2, ...) VALUES(列值1, 列值2, ...);
值的顺序和个数必须与前面指定的列对应，没有给出具体指自动插入null。如果没有给出列名，表示插入所有列

INSERT INTO stu(
  number, name, age, gender
)
VALUES(
  '11111111', 'zhangSan', 28, 'male'
);

修改数据

UPDATE 表名 SET 列名1=列值1, 列名2=列值2, ... [WHERE 条件]
方框号表示可以指定条件，实际语句中没有方框号。如果不加条件，就是所有记录的该列名的值全部被修改

UPDATE stu SET number=55, name='liSi'  WHERE age BETWEEN 18 AND 80 //把年龄在18~80之间的所有学生number改成55，name改成liSi

删除数据

DELETE FROM 表名 [WHERE 条件]; 常用删除语句
TRUNCATE TABLE 表名 : TRUNCATE是DDL语句，它是先删除drop该表，再create该表。而且无法回滚！！！

DCL 超级用户root

创建用户

CREATE USER 用户名@IP地址 IDENTIFIED BY '密码'; 用户只能在指定的IP地址上登录
CREATE USER 用户名@'%' IDENTIFIED BY '密码'; 用户可以在任意IP地址上登录
本机的IP地址可以使用localhost

用户授权

GRANT 权限1, … , 权限n ON 数据库.* TO 用户名@IP地址 给用户分派在指定的数据库上的指定的权限
GRANT ALL ON 数据库.* TO 用户名@IP地址; 给用户分派指定数据库上的所有权限
权限有： create、alter、drop、insert、update、delete、select
例如；GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON mydb1.* TO user1@localhost;

撤销授权

REVOKE 权限1, … , 权限n ON 数据库.* FROM 用户名@IP地址;撤消指定用户在指定数据库上的指定权限
例如；REVOKE CREATE,ALTER,DROP ON mydb1.* FROM user1@localhost;

查看权限

SHOW GRANTS FOR 用户名@IP地址查看指定用户的权限

删除用户

DROP USER 用户名@IP地址删除用户

DQL_数据库查询语言

基础查询

查询所有的列； select * from 表名 ;
查询指定的列： select 列1 ，列2 ,...,列n from 表名;
如果有重复记录，则只显示一个记录：select distinct * from 表名 ;

列运算

列运算只是对显示做了运算，并没有修改数据库中的内容

加减乘除： select 列1+列2 from 表名 ; 列可以加减乘除数字。字符串当0处理。如果某一列中有记录为NULL，则需要用ifnull(有null值的列，0)将null值转换为0，当然用ifnull(有null值的列，'XXX')可以将null值转换成字符串XXX
给列起一个别名，这个别名只是对显示有效，并没有修改数据库中的真实列名： SELECT 列1原名 AS 列1别名, 列2原名 AS 列2别名 FROM 表名; 其中as是可以省略的
字符串的连续运算： select concat('我叫',name,'我的工作是',job) from 表名 ; 其中name和job都是表的列。

条件查询_where

使用where关键字作为条件，可参考where的条件运算符

模糊查询_like

查询员工表emp中姓名ename列中,姓张的，并且只有两个字的所有员工； select * from emp where ename like '张_' ;,_的代表一个模糊的任意字符，一个下划线匹配一个字符。
查询员工表emp中姓名ename列中,只有三个字的所有员工；select * from emp where ename like '___' ;
查询员工表emp中姓名ename列中, 姓张的所有员工： select * from emp where ename like '张%' ; 其中%号匹配多个字符
查询员工表emp中姓名ename列中，最后一字是林的所有员工： select * from emp where ename like '%林' ;
查询员工表emp中姓名ename列中，带小的所有员工：select * from emp where ename like '%小%' ;

排序_order by

升序--根据年龄由小到大对所有记录进行排序： select * from where emp order by age asc ; age为表的列，其中asc可以省略
降序--根据工资(sal)由大到小对所有记录进行排序： select * from where emp order by sal desc ; ， desc不可以省略
使用多列作为排序条件---根据年龄对所有进行升序，如果年龄相同的某些记录，根据工资对这些记录进行降序排序： select * from where emp order by age asc , sal desc ;

聚合函数

聚合函数是用来做某列的纵向运算

查询行数：查询emp表中所有列都不为NULL的记录的行数： select count(*) from emp ;
查最大值/最小值：查询工资（sal）最大是多少 : select max(sal) from emp ;
查询和，NULL和字符串都当成0计算 : 查询所有人工资的和：select sum(sal ) from emp ;
查询平均值,NULL和字符串都当成0计算：查询所有人的平均工资：select avg(sal) from emp ;
综合一下： select count(*) 人数，sum(sal) 总和,max(sal) 最高工资,avg(sal ) 平均工资 from emp ;

分组查询

分组查询的意思是把记录用某一列进行分组，然后查询信息，查的信息都是组信息，而不是个人信息，组信息一般都是用聚合函数计算出来的

使用部门(deptno)分组，查询部门编号和每个部门的记录数： select deptno,count(*) from emp group by deptno ;
使用工作（job）分组，查询每种工作的最高工资：select job ,max(sal) from emp group by job ;
组条件：分组前用where，分组后用having
使用部门(deptno)分组，查询每组记录数，条件为记录数的工资大于15000（分组前的条件）:select deptno ,count(*) from emp where sal>15000 group by deptno ;
使用部门(deptno)分组，查询每组记录数，条件为每组中的记录工资大于15000(分组前的条件),并且记录数要大于等于2(分组后的条件):select deptno ,count(*) from emp where sal>15000 group by deptno having count(*) >=2 ;

limit子句方言

limit子句是mysql的方言，也就是mysql所特有的语言，作用是用来限定查询结果的起始行，以及总行数

查询起始行为第五行，一共查询3行记录: select * from emp limit 4,3 ; ，其中4表示第5行，第一行是从0开始的，3表示一共查询3条记录
这个功能用在分页查询上非常的方便

关键字的书写顺序

用上所有的关键字的关键字书写顺序和执行顺序是： select 、from、 where、group by 、having、order by

约束

主键约束与其相关

当表的某一列被指定为主键后，该列就不能为空（非空）、不能出现重复值（唯一性）、而且能够被引用（外键）
定义主键 PRIMARY KEY ，主键自增长AUTO_INCREMENT, 某列非空 NOT NULL ，唯一约束NOT NULL UNIQUE

CREATE TABLE stu(
		sid     INT     PRIMARY KEY AUTO_INCREMENT,   //定义主键，自增长
		sname	VARCHAR(20) NOT NULL UNIQUE,        //定义名字唯一性（不重复）
		age		INT    NOT NULL,                //定义年龄非空
		gender	VARCHAR(10)
  );

修改表时指定主键： ALTER TABLE stu ADD PRIMARY KEY (sid ) ;
修改表时设置主键自增长：ALTER TABLE stu CHANGE sid sid INT AUTO_INCREMENT;
修改表时删除主键自增长：ALTER TABLE stu CHANGE sid sid INT;
删除主键： ALTER TABLE stu DROP PRIMARY KEY ;

建立多对一外键约束

员工表（
   员工编号 ，主键
   员工姓名 ，
   。。。。
   员工部门编号 （值是10,20,30，null）  
   CONSTRAINT fk_员工表_部门表 FOREIGN KEY (员工部门编号) REFERENCES 部门表(部门编号)  ；  //添加外键约束
）;
部门表（
    部门编号 ，主键  
    部门名称
）；

如上代码，员工和所属部门是多对一的关系。员工表中部门这一列就是员工表的外键。
外键必须是表（另一个表或者本表都可以）的主键值，即外键引用主键。外键内容可以重复、可以为空。一张表可以有多个外键。
外键的取值必须在部门表的主键值的范围之内，不能出现部门表主键没有的值，否则会报错误。
修改表时添加外键约束：ALTER TABLE 员工表 ADD CONSTRAINT fk_员工表_部门表 FOREIGN KEY(员工部门编号) REFERENCES 部门表(部门编号);
修改表时删除外键约束： ALTER TABLE 员工表 DROP FOREIGN KEY fk_员工表_部门表 ;

建立一对一外键约束

需要要其中一张表的主键即是主键又是外键

丈夫表（
   丈夫编号 ，主键
   丈夫姓名 ，
）;
妻子表（
    妻子编号 ，主键+外键  
    妻子名字
    CONSTRAINT fk_妻子表_丈夫表 FOREIGN KEY (妻子编号) REFERENCES 丈夫表(丈夫编号)  ；  //添加外键约束
）；

如上代码，丈夫和妻子是一对一的关系。妻子表中的妻子编号即是主键又是外键。

建立多对多外键约束

需要使用三张表，在中间表中使用两个外键，分别引用其他两个表的主键。

学生表(
    学生编号 ，  主键
    学生姓名 
);
教师表(
    教师编号 ， 主键
    教师姓名 
);
中间表(
    中间表学生编号  ，
    中间表教师编号
    CONSTRAINT fk_学生表_教师表_添加学生外键 FOREIGN KEY(中间表学生编号) REFERENCES 学生表(学生编号),   //添加外键约束
    CONSTRAINT fk_学生表_教师表_添加教师外键 FOREIGN KEY(中间表教师编号) REFERENCES 教师表(教师编号)     //添加外键约束
);

如上述代码，学生和教师是多对多的关系。中间表中的每条记录都是来说明学生和教师之间的关系。

多表查询

连接查询

内连接

/*
  方言 ， MySQL所特有
  查询 员工姓名 工资 和所在部门名称
*/
SELECT e.ename,e.sal,d.dname
FROM  emp AS e,dept AS d
WHERE e.deptno=d.deptno;

/*
  标准  ，以后建议都用标准
  查询 员工姓名 工资 和所在部门名称
*/
SELECT e.ename,e.sal,d.dname
FROM  emp AS e INNER JOIN dept AS d
ON e.deptno=d.deptno;

内连接
上述执行就可以发现，查询出的人名中并没有张三这个人，因为张三的部门是50，而在部门表中并没有50这个部门。如果想要显示张三，并且对应部门的名称显示NULL 的话，需要外连接
外连接

/*
   左链接 ，先查询出左表（以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL  
*/
SELECT e.ename,e.sal,d.dname
FROM emp AS e LEFT OUTER JOIN dept d
ON e.deptno=d.deptno ;

/*
   右链接 ，先查询出右表（以右表为主），然后查询左表，左表中满足条件的显示出来，不满足条件的显示NULL  
*/
SELECT e.ename,e.sal,d.dname
FROM emp AS e RIGHT OUTER JOIN dept d
ON e.deptno=d.deptno ;
/*
   全外连接 
SELECT e.ename,e.sal,d.dname
FROM emp AS e FULL OUTER JOIN dept d
ON e.deptno=d.deptno ;
但是MySQL数据库不支持，可以用UNION ALL  的方式来解决
*/
SELECT e.ename,e.sal,d.dname
FROM emp AS e LEFT OUTER JOIN dept d
ON e.deptno=d.deptno 
UNION ALL
SELECT e.ename,e.sal,d.dname
FROM emp AS e RIGHT OUTER JOIN dept d
ON e.deptno=d.deptno ;

如上代码所示，外连接有左右之分，左链接，先查询出左表（以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL，比如，员工中的张三，先把张三查询出来，然后右表（部门表）中并没有50号部门，所以显示NULL 。右链接，先查询出右表（以右表为主），然后查询左表，左表中满足条件的显示出来，不满足条件的显示NULL ，比如部门表中有个40号财务部门，先查询出来，但是左表（员工）中并没有40号部门的员工，所以左侧都显示为ＮＵＬＬ．

子查询

子查询就是嵌套查询，select中包含多个select 。子查询出现的位置，在where后，作为条件的一部门；在from后，作为被查询的一条表。当子查询出现在where后作为条件时，可以用any、all 关键字。
子查询的结果集的形式有：单行单列、单行多列、多行单列、多行多列

/*
  子查询案例一 ： 查询工资高于关羽工资的员工
  子查询作为条件 。 形式为单行单列
  子条件是关羽的工资
*/
SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename="关羽") ;
/*
   子查询案例二 ： 工资高于30部门所有人的员工信息
   子查询作为条件  。 形式为多行单列
   子条件是30部门所有人的工资
*/
SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30) ;
/*
   子查询案例三 ： 查询工作和工资与殷天正完全相同的员工
   子查询作为条件 。  形式为单行多列
   子条件是殷天正的工作和工资
*/
SELECT * FROM emp WHERE (job,sal) IN (SELECT job,sal FROM emp WHERE ename="殷天正"); 
/*
   子查询案例四 ： 查询员工编号为1006的员工名称、部门名称、员工工资、部门地址
   子查询 作为表。 形式为多行多列
   子条件是部门表中的部门名称、部门地址 ， 部门编号作为去除笛卡尔积的条件也必须被查询
*/
SELECT e.ename,e.sal,d.dname,d.loc
FROM emp AS e , (SELECT dname ,loc ,deptno FROM dept) AS d 
WHERE e.deptno=d.deptno AND e.empno=1006 ;

posted @ 2017-06-30 16:51 阿斯兰。ｌīōń 阅读(1749) 评论(2) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

阿斯兰。ｌīōń

QQ： 425680992

《JavaWeb从入门到改行》很好的复习资料: SQL语句到底怎么写 ？

单表查询与多表查询 案列