MYSQL复习资料

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

本文用到的数据库如下：

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 ('1', '王永', '23', '男', '北京', '1500');
INSERT INTO `stu` VALUES ('2', '张雷', '25', '男', '辽宁', '2500');
INSERT INTO `stu` VALUES ('3', '李强', '22', '男', '北京', '3500');
INSERT INTO `stu` VALUES ('4', '宋永合', '25', '男', '北京', '1500');
INSERT INTO `stu` VALUES ('5', '叙美丽', '23', '女', '北京', '1000');
INSERT INTO `stu` VALUES ('6', '陈宁', '22', '女', '山东', '2500');
INSERT INTO `stu` VALUES ('7', '王丽', '21', '女', '北京', '1600');
INSERT INTO `stu` VALUES ('8', '李永', '23', '男', '北京', '3500');
INSERT INTO `stu` VALUES ('9', '张玲', '23', '女', '广州', '2500');
INSERT INTO `stu` VALUES ('10', '啊历', '18', '男', '山西', '3500');
INSERT INTO `stu` VALUES ('11', '王刚', '23', '男', '湖北', '4500');
INSERT INTO `stu` VALUES ('12', '陈永', '24', '男', '北京', '1500');
INSERT INTO `stu` VALUES ('13', '李雷', '24', '男', '辽宁', '2500');
INSERT INTO `stu` VALUES ('14', '李沿', '22', '男', '北京', '3500');
INSERT INTO `stu` VALUES ('15', '王小明', '25', '男', '北京', '1500');
INSERT INTO `stu` VALUES ('16', '王小丽', '23', '女', '北京', '1000');
INSERT INTO `stu` VALUES ('17', '唐宁', '22', '女', '山东', '2500');
INSERT INTO `stu` VALUES ('18', '唐丽', '21', '女', '北京', '1600');
INSERT INTO `stu` VALUES ('19', '啊永', '23', '男', '北京', '3500');
INSERT INTO `stu` VALUES ('20', '唐玲', '23', '女', '广州', '2500');
INSERT INTO `stu` VALUES ('21', '叙刚', '18', '男', '山西', '3500');
INSERT INTO `stu` VALUES ('22', '王累', '23', '男', '湖北', '4500');
INSERT INTO `stu` VALUES ('23', '赵安', '23', '男', '北京', '1500');
INSERT INTO `stu` VALUES ('24', '关雷', '25', '男', '辽宁', '2500');
INSERT INTO `stu` VALUES ('25', '李字', '22', '男', '北京', '3500');
INSERT INTO `stu` VALUES ('26', '叙安国', '25', '男', '北京', '1500');
INSERT INTO `stu` VALUES ('27', '陈浩难', '23', '女', '北京', '1000');
INSERT INTO `stu` VALUES ('28', '陈明', '22', '女', '山东', '2500');
INSERT INTO `stu` VALUES ('29', '孙丽', '21', '女', '北京', '1600');
INSERT INTO `stu` VALUES ('30', '李治国', '23', '男', '北京', '3500');
INSERT INTO `stu` VALUES ('31', '张娜', '23', '女', '广州', '2500');
INSERT INTO `stu` VALUES ('32', '安强', '18', '男', '山西', '3500');
INSERT INTO `stu` VALUES ('33', '王欢', '23', '男', '湖北', '4500');
INSERT INTO `stu` VALUES ('34', '周天乐', '23', '男', '北京', '1500');
INSERT INTO `stu` VALUES ('35', '关雷', '25', '男', '辽宁', '2500');
INSERT INTO `stu` VALUES ('36', '吴强', '22', '男', '北京', '3500');
INSERT INTO `stu` VALUES ('37', '吴合国', '25', '男', '北京', '1500');
INSERT INTO `stu` VALUES ('38', '正小和', '23', '女', '北京', '1000');
INSERT INTO `stu` VALUES ('39', '吴丽', '22', '女', '山东', '2500');
INSERT INTO `stu` VALUES ('40', '冯含', '21', '女', '北京', '1600');
INSERT INTO `stu` VALUES ('41', '陈冬', '23', '男', '北京', '3500');
INSERT INTO `stu` VALUES ('42', '关玲', '23', '女', '广州', '2500');
INSERT INTO `stu` VALUES ('43', '包利', '18', '男', '山西', '3500');
INSERT INTO `stu` VALUES ('44', '威刚', '23', '男', '湖北', '4500');
INSERT INTO `stu` VALUES ('45', '李永', '23', '男', '北京', '1500');
INSERT INTO `stu` VALUES ('46', '张关雷', '25', '男', '辽宁', '2500');
INSERT INTO `stu` VALUES ('47', '送小强', '22', '男', '北京', '3500');
INSERT INTO `stu` VALUES ('48', '关动林', '25', '男', '北京', '1500');
INSERT INTO `stu` VALUES ('49', '苏小哑', '23', '女', '北京', '1000');
INSERT INTO `stu` VALUES ('50', '赵宁', '22', '女', '山东', '2500');
INSERT INTO `stu` VALUES ('51', '陈丽', '21', '女', '北京', '1600');
INSERT INTO `stu` VALUES ('52', '钱小刚', '23', '男', '北京', '3500');
INSERT INTO `stu` VALUES ('53', '艾林', '23', '女', '广州', '2500');
INSERT INTO `stu` VALUES ('54', '郭林', '18', '男', '山西', '3500');
INSERT INTO `stu` VALUES ('55', '周制强', '23', '男', '湖北', '4500');

.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-07-18 21:24 yuancr 阅读(193) 评论(0) 收藏举报

刷新页面返回顶部

yuancr