MySQL笔记2

1、查询结果去重
mysql> select distinct job from emp; //distinct关键词，去除重复记录
+—————-+
| job |
+—————-+
| CLERK |
| SALESMAN |
| MANAGER |
| ANALYST |
| PRESIDENT |
+—————-+
mysql> select ename,distinct job from emp; //这个语法是错误的，distinct只能出现在所有字段的前方
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near ‘distinct job from emp’ at line 1
mysql> select distinct deptno,job from emp; //当distinct出现在多个字段前方时，表示的是后面的字段联合起来去重
+————+—————-+
| deptno | job |
+————+—————-+
| 20 | CLERK |
| 30 | SALESMAN |
| 20 | MANAGER |
| 30 | MANAGER |
| 10 | MANAGER |
| 20 | ANALYST |
| 10 | PRESIDENT |
| 30 | CLERK |
| 10 | CLERK |
+————+—————-+
mysql> select count(distinct job) from emp; //统计工作岗位数
+——————————-+
| count(distinct job) |
+——————————-+
| 5 |
+——————————-+
2、连接查询
2.1、什么是连接查询？
实际开发中一般都不是在单表中查询数据，一般都是多张表联合查询取出最终的结果。
一般一个业务都会对应多张表，比如：学生和班级，起码两张表。
stuno stuname classno classname
01 zs 1 高三一班
02 ls 1 高三一班
…
465 ww 7 高三七班
…
如果将学生和班级信息存到一张表内，结果就向上表一样，数据存在大量的重复，导致数据的冗余。
2.2、连接查询的分类？
根据语法出现的年代，可以划分为SQL92和SQL99.
根据表的连接方式可以划分为内连接，外连接和全连接
内连接：
等值连接
非等值连接
mysql> select ename,dname from emp,dept; //尝试取出员工姓名和员工所在部门名称，但发生了笛卡尔积现象
+————+——————+
| ename | dname |
+————+——————+
| SMITH | ACCOUNTING |
| SMITH | RESEARCH |
| SMITH | SALES |
| SMITH | OPERATIONS |
| ALLEN | ACCOUNTING |
| ALLEN | RESEARCH |
| ALLEN | SALES |
| ALLEN | OPERATIONS |
| WARD | ACCOUNTING |
| WARD | RESEARCH |
| WARD | SALES |
| WARD | OPERATIONS |
| JONES | ACCOUNTING |
| JONES | RESEARCH |
| JONES | SALES |
| JONES | OPERATIONS |
| MARTIN | ACCOUNTING |
| MARTIN | RESEARCH |
| MARTIN | SALES |
| MARTIN | OPERATIONS |
| BLAKE | ACCOUNTING |
| BLAKE | RESEARCH |
| BLAKE | SALES |
| BLAKE | OPERATIONS |
| CLARK | ACCOUNTING |
| CLARK | RESEARCH |
| CLARK | SALES |
| CLARK | OPERATIONS |
| SCOTT | ACCOUNTING |
| SCOTT | RESEARCH |
| SCOTT | SALES |
| SCOTT | OPERATIONS |
| KING | ACCOUNTING |
| KING | RESEARCH |
| KING | SALES |
| KING | OPERATIONS |
| TURNER | ACCOUNTING |
| TURNER | RESEARCH |
| TURNER | SALES |
| TURNER | OPERATIONS |
| ADAMS | ACCOUNTING |
| ADAMS | RESEARCH |
| ADAMS | SALES |
| ADAMS | OPERATIONS |
| JAMES | ACCOUNTING |
| JAMES | RESEARCH |
| JAMES | SALES |
| JAMES | OPERATIONS |
| FORD | ACCOUNTING |
| FORD | RESEARCH |
| FORD | SALES |
| FORD | OPERATIONS |
| MILLER | ACCOUNTING |
| MILLER | RESEARCH |
| MILLER | SALES |
| MILLER | OPERATIONS |
+————+——————+
56 rows in set (0.00 sec) //共取出56个数据（56=144）
表的别名：
select e.ename，d.dname from emp e，dept d;
表的别名的好处？
1、可读性好
2、执行效率高
2.3、接下来我们要避免笛卡尔积现象：
思考：避免了笛卡尔积现象，会减小记录的匹配次数吗？
答曰，不会，次数不变，只不过最后只显示有效记录。
mysql> select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno; //这是避免笛卡尔积现象的写法，属于92语法，几乎过时
+————+——————+
| ename | dname |
+————+——————+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+————+——————+
14 rows in set (0.00 sec)
2.4、内连接之等值连接
上个案例仍未解决，因为上个方法避免了笛卡尔积现象，但属于92语法，已经过时。
SQL92：
select
e.ename，d.dname
from
emp e,dept d
where
e.deptno = d.deptno;
SQL99:
select
e.ename,d.dame
from
emp e
join
dept d
on
e.deptno = d.deptno;
SQL99语法的结构更清晰，表的连接条件和随后的过滤条件where分离了。
mysql> select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno; //这是新的语法，也可以避免笛卡尔积现象，而且结构更清晰
+————+——————+
| ename | dname |
+————+——————+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+————+——————+
2.5内连接之非等值连接：最大的特点是连接条件中的关系是非等量关系
mysql> select ename,sal,grade from emp join salgrade on sal between losal and hisal; //找出每个员工的工资等级，显示员工名，薪资，薪资等级
+————+————-+———-+
| ename | sal | grade |
+————+————-+———-+
| SMITH | 800.00 | 1 |
| ALLEN | 1600.00 | 3 |
| WARD | 1250.00 | 2 |
| JONES | 2975.00 | 4 |
| MARTIN | 1250.00 | 2 |
| BLAKE | 2850.00 | 4 |
| CLARK | 2450.00 | 4 |
| SCOTT | 3000.00 | 4 |
| KING | 5000.00 | 5 |
| TURNER | 1500.00 | 3 |
| ADAMS | 1100.00 | 1 |
| JAMES | 950.00 | 1 |
| FORD | 3000.00 | 4 |
| MILLER | 1300.00 | 2 |
+————+————-+———-+
14 rows in set (0.00 sec)
2.6、自连接：最大的特点是一张表看做两张表，自己连自己
mysql> select e.ename as ename,a.ename as mgrname from emp e join emp a on e.mgr = a.empno; //找出每个员工的上级领导，要求显示员工名和对应的上级领导名
+————+————-+
| ename | mgrname |
+————+————-+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+————+————-+
2.7、外连接？
内连接和外连接的区别？
内连接：
假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接
AB两张表没有主副之分，两张表是平等的。

外连接：
假设两张表AB进行连接，使用外连接的话，两表中有一张表是主表，有一张表是副表，主要查询主表中的数据，捎带查询副表，当副表中的数据没有和主表中的数
据匹配上，副表自动模拟出NULL与之匹配。
外连接分类？
左外连接：左边的表是主表。
右外连接：右边的表是主表。

mysql> select a.ename as ‘员工’,b.ename as ‘领导’ from emp a left (outer) join emp b on a.mgr = b.empno; //找出每个员工的上级领导，要求显示员工名和对应的上级
领导名，
没有领导则填null
+————+————+
| 员工 | 领导 |
+————+————+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| KING | NULL |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+————+————+
mysql> select a.ename as ‘领导’,b.ename as ‘员工’ from emp a right (outer) join emp b on a.empno = b.mgr; //需求与上个相同，只是换成右外连接方式了，没
有什么区别
+————+————+
| 领导 | 员工 |
+————+————+
| FORD | SMITH |
| BLAKE | ALLEN |
| BLAKE | WARD |
| KING | JONES |
| BLAKE | MARTIN |
| KING | BLAKE |
| KING | CLARK |
| JONES | SCOTT |
| NULL | KING |
| BLAKE | TURNER |
| SCOTT | ADAMS |
| BLAKE | JAMES |
| JONES | FORD |
| CLARK | MILLER |
+————+————+
//注意，以后的实际开发中，外连接用的多
mysql> select a.,b. from dept a left join emp b on a .deptno = b.deptno; //找出没有员工的部门名，第一步
+————+——————+—————+———-+————+—————-+———+——————+————-+————-+————+
| DEPTNO | DNAME | LOC | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+————+——————+—————+———-+————+—————-+———+——————+————-+————-+————+
| 20 | RESEARCH | DALLAS | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 30 | SALES | CHICAGO | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 30 | SALES | CHICAGO | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 20 | RESEARCH | DALLAS | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 30 | SALES | CHICAGO | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 30 | SALES | CHICAGO | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 10 | ACCOUNTING | NEW YORK | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 20 | RESEARCH | DALLAS | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 10 | ACCOUNTING | NEW YORK | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 30 | SALES | CHICAGO | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 20 | RESEARCH | DALLAS | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 30 | SALES | CHICAGO | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 20 | RESEARCH | DALLAS | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 10 | ACCOUNTING | NEW YORK | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
| 40 | OPERATIONS | BOSTON | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+————+——————+—————+———-+————+—————-+———+——————+————-+————-+————+
15 rows in set (0.05 sec)
mysql> select a. from dept a left join emp b on a .deptno = b.deptno where b.empno is null; //找出没有员工的部门名，第二步
+————+——————+————+
| DEPTNO | DNAME | LOC |
+————+——————+————+
| 40 | OPERATIONS | BOSTON |
+————+——————+————+
1 row in set (0.00 sec)
select a.ename,b.dname,c.grade from emp a left join dept b on a.deptno = b.deptno left join salgrade c on a.sal between c.losal and c.hisal;
//找出每个员工的部门名和薪资等级
+————+——————+———-+
| ename | dname | grade |
+————+——————+———-+
| SMITH | RESEARCH | 1 |
| ALLEN | SALES | 3 |
| WARD | SALES | 2 |
| JONES | RESEARCH | 4 |
| MARTIN | SALES | 2 |
| BLAKE | SALES | 4 |
| CLARK | ACCOUNTING | 4 |
| SCOTT | RESEARCH | 4 |
| KING | ACCOUNTING | 5 |
| TURNER | SALES | 3 |
| ADAMS | RESEARCH | 1 |
| JAMES | SALES | 1 |
| FORD | RESEARCH | 4 |
| MILLER | ACCOUNTING | 2 |
+————+——————+———-+
mysql> select a.ename,b.dname,c.grade,d.ename from emp a left join dept b on a.deptno = b.deptno
left join salgrade c on a.sal between c.losal and c.hisal
left join emp d on a.mgr = d.empno; //找出每个员工的部门名称，工资等级和领导名
+————+——————+———-+———-+
| ename | dname | grade | ename |
+————+——————+———-+———-+
| SMITH | RESEARCH | 1 | FORD |
| ADAMS | RESEARCH | 1 | SCOTT |
| JAMES | SALES | 1 | BLAKE |
| WARD | SALES | 2 | BLAKE |
| MARTIN | SALES | 2 | BLAKE |
| MILLER | ACCOUNTING | 2 | CLARK |
| ALLEN | SALES | 3 | BLAKE |
| TURNER | SALES | 3 | BLAKE |
| JONES | RESEARCH | 4 | KING |
| BLAKE | SALES | 4 | KING |
| CLARK | ACCOUNTING | 4 | KING |
| SCOTT | RESEARCH | 4 | JONES |
| FORD | RESEARCH | 4 | JONES |
| KING | ACCOUNTING | 5 | NULL |
+————+——————+———-+———-+
14 rows in set (0.00 sec)
3、子查询
3.1、子查询的用法
select
…(select)
from
…(select)
where
…(select)
3.2、where后嵌套子查询
mysql> select from emp where sal > (select avg(sal) from emp); //查询工资高于平均工资的员工信息
+———-+———-+—————-+———+——————+————-+———+————+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+———-+———-+—————-+———+——————+————-+———+————+
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 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 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
+———-+———-+—————-+———+——————+————-+———+————+
3.3、from后嵌套子查询
mysql> select dname,grade from (select avg(sal) as avgSal,deptno from emp group by deptno) a right join dept b on a.deptno = b.deptno left join salgrade c on a.avgSal between c.losal and c.hisal;
//找出每个部门的平均薪资的等级
+——————+———-+
| dname | grade |
+——————+———-+
| SALES | 3 |
| ACCOUNTING | 4 |
| RESEARCH | 4 |
| OPERATIONS | NULL |
+——————+———-+
mysql> select deptno,avg(grade) from emp e join salgrade s on e.sal between s.losal and s.hisal group by deptno;
//找出每个部门的平均的薪资等级
+——————+————+
| avg(grade) | deptno |
+——————+————+
| 2.8000 | 20 |
| 2.5000 | 30 |
| 3.6667 | 10 |
+——————+————+
3.4、select跟子查询
mysql> select e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dname from emp e;
+————+——————+
| ename | dname |
+————+——————+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+————+——————+
4、union（将查询结果集相加）
两张表上下拼接在一起
5、limit（分页查询核心！）
limit是MySQL特有的。不通用。（oracle中的叫做rownum）
limit用来取结果集中的部分数据
语法：
limit startIndex,lenth
案例：列出工资最高的五名员工
mysql> select ename ,sal from emp order by sal desc limit 0,5;
+———-+————-+
| ename | sal |
+———-+————-+
| KING | 5000.00 |
| FORD | 3000.00 |
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+———-+————-+
mysql> select ename,sal from emp order by sal desc limit 5;
+———-+————-+
| ename | sal |
+———-+————-+
| KING | 5000.00 |
| FORD | 3000.00 |
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+———-+————-+
案例：取工资排名4-9名的员工
mysql> select ename,sal from emp order by sal desc limit 3 , 6;
+————+————-+
| ename | sal |
+————+————-+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
+————+————-+
5.1、通用的标准分页SQL
第一页：0,3
第二页：3,3
第三页：6,3
第四页：9,3
第五页：12，3
第pageNo页：0+（pageNo-1）pageSize，pageSize
6、创建表
语法格式
create table 表名（
字段名1 数据类型，
字段名2 数据类型，
…
）；
MySQL中常见的数据类型：
int 整数型(java中的int)
bigint 长整型(java中的long)
float 浮点型(java中的flout，double)
char 定长字符串(对应String)
varchar 变长字符串（最长255个字符）（对应StringBuffer或者StringBuilder)
date 日期类型（对应Java中java.sql.data类型）
BLOB 二进制大对象（存图片，视频等流媒体信息）（Binary Large Object）(对应Object)
CLOB 字符大对象（存储较大文本，比如，可以存储4G的字符串）（Character Large Object）(对应Object)
……
char和varchar怎么选择？
变不变？
创建学生信息表
stuNo name gender classNo birthday
stuNo:bigint
name:varchar
gender:char
classNo:varchar
birthday:char
mysql> create table t_student(
-> stuNo bigint,
-> name varchar(255),
-> gender char(1),
-> classNo varchar(255),
-> birthday char(10)
);
7、insert语句插入数据
语法：
insert into 表名(字段名1,字段名2,…) values(值1，值2，…）
mysql> insert into t_student(stuNo,name,gender,classNo,birthday) values(01,’张三’,’男’,’高三一班’,’1999-06-19’);
Query OK, 1 row affected (0.02 sec) //插入一条学生信息
mysql> select from t_student;
+———-+————+————+———————+——————+
| stuNo | name | gender | classNo | birthday |
+———-+————+————+———————+——————+
| 1 | 张三 | 男 | 高三一班 | 1999-06-19 |
+———-+————+————+———————+——————+
8、删除表
mysql> drop table if exists t_student;
Query OK, 0 rows affected (0.08 sec)
9、表的复制
create table emp1 as select from emp;
10、将查询结果插入到一张表中，对表的结构有要求
insert into dept1 select * from dept;
11、修改数据：update
语法：
update 表名 set 字段名1=值1，字段名2=值2，… where 条件；
注意：没有条件整张表全部更新
12、删除语句
语法；
delete from 表名 where 条件；
truncate table 表名；
增删改查的术语：
CRUD
Create,Retrieve,Updata,Delete
13、约束(Constraint)
13.1、
常见的约束：
非空约束(not null) 不能为空
唯一约束(unique) 不能重复
主键约束(primary key) 不能为空且不能重复PK
外键约束(foreign key) FK
检查约束(check)(目前MySQL不支持该约束)
13.2、非空约束not null
drop table if exists t_user;
create table t_user(id int,username varchar(255) not null,password varchar(255));
insert into t_user(id,password) value(1,’123’);