很好的复习资料: SQL语句到底怎么写 ？

时间：2023-03-08 16:32:45

本文用到的数据库如下：

 CREATE DATABASE exam;

 /*创建部门表*/

 CREATE TABLE dept(

     deptno        INT     PRIMARY KEY,

     dname        VARCHAR(50),

     loc         VARCHAR(50)

 );

 /*创建雇员表*/

 CREATE TABLE emp(

     empno        INT     PRIMARY KEY,

     ename        VARCHAR(50),

     job        VARCHAR(50),

     mgr        INT,

     hiredate    DATE,

     sal        DECIMAL(7,2),

     COMM         DECIMAL(7,2),

     deptno        INT,

     CONSTRAINT fk_emp FOREIGN KEY(mgr) REFERENCES emp(empno) ,

     //CONSTRAINT fk_emp_dept FOREIGN KEY(deptno) REFERENCES dept(deptno);

     //本来应该添加这个外键约束，但是为了以后做案列所以不加这个约束，因为加了这个约束，那么emp表中deptno的值就必须是dept表中的主键的某一值

 );

 /*创建工资等级表*/

 CREATE TABLE salgrade(

     grade        INT     PRIMARY KEY,

     losal        INT,

     hisal        INT

 );

 /*创建学生表*/

 CREATE TABLE stu(

     sid        INT     PRIMARY KEY,

     sname        VARCHAR(50),

     age        INT,

     gander        VARCHAR(10),

     province    VARCHAR(50),

     tuition        INT

 );

 /*插入dept表数据*/

 INSERT INTO dept VALUES (10, '教研部', '北京');

 INSERT INTO dept VALUES (20, '学工部', '上海');

 INSERT INTO dept VALUES (30, '销售部', '广州');

 INSERT INTO dept VALUES (40, '财务部', '武汉');

 /*插入emp表数据*/

 INSERT INTO emp VALUES (1009, '曾阿牛', '董事长', NULL, '2001-11-17', 50000, NULL, 10);

 INSERT INTO emp VALUES (1004, '刘备', '经理', 1009, '2001-04-02', 29750, NULL, 20);

 INSERT INTO emp VALUES (1006, '关羽', '经理', 1009, '2001-05-01', 28500, NULL, 30);

 INSERT INTO emp VALUES (1007, '张飞', '经理', 1009, '2001-09-01', 24500, NULL, 10);

 INSERT INTO emp VALUES (1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000, NULL, 20);

 INSERT INTO emp VALUES (1013, '庞统', '分析师', 1004, '2001-12-03', 30000, NULL, 20);

 INSERT INTO emp VALUES (1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000, 3000, 30);

 INSERT INTO emp VALUES (1003, '殷天正', '销售员', 1006, '2001-02-22', 12500, 5000, 30);

 INSERT INTO emp VALUES (1005, '谢逊', '销售员', 1006, '2001-09-28', 12500, 14000, 30);

 INSERT INTO emp VALUES (1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000, 0, 30);

 INSERT INTO emp VALUES (1012, '程普', '文员', 1006, '2001-12-03', 9500, NULL, 30);

 INSERT INTO emp VALUES (1014, '黄盖', '文员', 1007, '2002-01-23', 13000, NULL, 10);

 INSERT INTO emp VALUES (1011, '周泰', '文员', 1008, '2007-05-23', 11000, NULL, 20);

 INSERT INTO emp VALUES (1001, '甘宁', '文员', 1013, '2000-12-17', 8000, NULL, 20);

 INSERT INTO emp VALUES (1015, '张三', '保洁员', 1009, '2001-09-01', 24500, 50000, 50);  //注意 ，在部门表中根本没有50部门

 /*插入salgrade表数据*/

 INSERT INTO salgrade VALUES (1, 7000, 12000);

 INSERT INTO salgrade VALUES (2, 12010, 14000);

 INSERT INTO salgrade VALUES (3, 14010, 20000);

 INSERT INTO salgrade VALUES (4, 20010, 30000);

 INSERT INTO salgrade VALUES (5, 30010, 99990);

 /*插入stu表数据*/

 INSERT INTO `stu` VALUES ('', '王永', '', '男', '北京', '');

 INSERT INTO `stu` VALUES ('', '张雷', '', '男', '辽宁', '');

 INSERT INTO `stu` VALUES ('', '李强', '', '男', '北京', '');

 INSERT INTO `stu` VALUES ('', '宋永合', '', '男', '北京', '');

 INSERT INTO `stu` VALUES ('', '叙美丽', '', '女', '北京', '');

 INSERT INTO `stu` VALUES ('', '陈宁', '', '女', '山东', '');

 INSERT INTO `stu` VALUES ('', '王丽', '', '女', '北京', '');

 INSERT INTO `stu` VALUES ('', '李永', '', '男', '北京', '');

 INSERT INTO `stu` VALUES ('', '张玲', '', '女', '广州', '');

 INSERT INTO `stu` VALUES ('', '啊历', '', '男', '山西', '');

 INSERT INTO `stu` VALUES ('', '王刚', '', '男', '湖北', '');

 INSERT INTO `stu` VALUES ('', '陈永', '', '男', '北京', '');

 INSERT INTO `stu` VALUES ('', '李雷', '', '男', '辽宁', '');

 INSERT INTO `stu` VALUES ('', '李沿', '', '男', '北京', '');

 INSERT INTO `stu` VALUES ('', '王小明', '', '男', '北京', '');

 INSERT INTO `stu` VALUES ('', '王小丽', '', '女', '北京', '');

 INSERT INTO `stu` VALUES ('', '唐宁', '', '女', '山东', '');

 INSERT INTO `stu` VALUES ('', '唐丽', '', '女', '北京', '');

 INSERT INTO `stu` VALUES ('', '啊永', '', '男', '北京', '');

 INSERT INTO `stu` VALUES ('', '唐玲', '', '女', '广州', '');

 INSERT INTO `stu` VALUES ('', '叙刚', '', '男', '山西', '');

 INSERT INTO `stu` VALUES ('', '王累', '', '男', '湖北', '');

 INSERT INTO `stu` VALUES ('', '赵安', '', '男', '北京', '');

 INSERT INTO `stu` VALUES ('', '关雷', '', '男', '辽宁', '');

 INSERT INTO `stu` VALUES ('', '李字', '', '男', '北京', '');

 INSERT INTO `stu` VALUES ('', '叙安国', '', '男', '北京', '');

 INSERT INTO `stu` VALUES ('', '陈浩难', '', '女', '北京', '');

 INSERT INTO `stu` VALUES ('', '陈明', '', '女', '山东', '');

 INSERT INTO `stu` VALUES ('', '孙丽', '', '女', '北京', '');

 INSERT INTO `stu` VALUES ('', '李治国', '', '男', '北京', '');

 INSERT INTO `stu` VALUES ('', '张娜', '', '女', '广州', '');

 INSERT INTO `stu` VALUES ('', '安强', '', '男', '山西', '');

 INSERT INTO `stu` VALUES ('', '王欢', '', '男', '湖北', '');

 INSERT INTO `stu` VALUES ('', '周天乐', '', '男', '北京', '');

 INSERT INTO `stu` VALUES ('', '关雷', '', '男', '辽宁', '');

 INSERT INTO `stu` VALUES ('', '吴强', '', '男', '北京', '');

 INSERT INTO `stu` VALUES ('', '吴合国', '', '男', '北京', '');

 INSERT INTO `stu` VALUES ('', '正小和', '', '女', '北京', '');

 INSERT INTO `stu` VALUES ('', '吴丽', '', '女', '山东', '');

 INSERT INTO `stu` VALUES ('', '冯含', '', '女', '北京', '');

 INSERT INTO `stu` VALUES ('', '陈冬', '', '男', '北京', '');

 INSERT INTO `stu` VALUES ('', '关玲', '', '女', '广州', '');

 INSERT INTO `stu` VALUES ('', '包利', '', '男', '山西', '');

 INSERT INTO `stu` VALUES ('', '威刚', '', '男', '湖北', '');

 INSERT INTO `stu` VALUES ('', '李永', '', '男', '北京', '');

 INSERT INTO `stu` VALUES ('', '张关雷', '', '男', '辽宁', '');

 INSERT INTO `stu` VALUES ('', '送小强', '', '男', '北京', '');

 INSERT INTO `stu` VALUES ('', '关动林', '', '男', '北京', '');

 INSERT INTO `stu` VALUES ('', '苏小哑', '', '女', '北京', '');

 INSERT INTO `stu` VALUES ('', '赵宁', '', '女', '山东', '');

 INSERT INTO `stu` VALUES ('', '陈丽', '', '女', '北京', '');

 INSERT INTO `stu` VALUES ('', '钱小刚', '', '男', '北京', '');

 INSERT INTO `stu` VALUES ('', '艾林', '', '女', '广州', '');

 INSERT INTO `stu` VALUES ('', '郭林', '', '男', '山西', '');

 INSERT INTO `stu` VALUES ('', '周制强', '', '男', '湖北', '');

数据库设计

很好的复习资料: SQL语句到底怎么写 ？

很好的复习资料: SQL语句到底怎么写 ？

很好的复习资料: SQL语句到底怎么写 ？

很好的复习资料: SQL语句到底怎么写 ？

.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语句的分类

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

数据库存储的数据类型

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

DDL

DDL _ 对库的操作

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

DDL _ 对表的操作

  1. 创建表
  CREATE TABLE  表名(

    列名 列类型,

    列名 列类型,

    ...

    列名 列类型

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

    ADD (

      列名 列类型,

      列名 列类型,

      ...

    );
  1. 修改_修改列类型 ALTER TABLE 表名 MODIFY 列名 列类型;
  2. 修改_修改列名：ALTER TABLE 表名 CHANGE 原列名 新列名 列类型;
  3. 修改_删除列：ALTER TABLE 表名 DROP 列名;
  4. 修改_修改表名称：ALTER TABLE 原表名 RENAME TO 新表名;

DML

where条件语法

  1. 条件必须是一个boolean类型的值或表达式
  2. where 属性判断
  3. 条件的运算符：
运算符 说明举例 运算符 说明举例
= 等于 != 不等于
<> 大于小于 > 大于
< 小于 >= 大于等于
<= 小于等于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;

很好的复习资料: SQL语句到底怎么写 ？

上述执行就可以发现 ， 查询出的人名中并没有张三这个人，因为张三的部门是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 ;

很好的复习资料: SQL语句到底怎么写 ？

很好的复习资料: SQL语句到底怎么写 ？

很好的复习资料: SQL语句到底怎么写 ？

如上代码所示， 外连接有左右之分，左链接 ，先查询出左表（以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的显示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 ;

相关文章