MySQL（连接查询、子查询、DDL语句、DML语句）

连接查询

连接查询：多张表联合起来查询数据
在这里使用SQL99语法

内连接

内连接中，A表和B表连接，二者没有主次关系，是平等的。

等值连接

select 
	...
from
	a
join
	b
on
	a和b的连接条件
where   //可以用where进行进一步的筛选
	筛选条件

例：查询每个员工所在部门名称，显示员工名和部门名？

select 
		e.ename,d.dname
	from
		emp e
	(inner) join   // 这个位置实际上省略了inner
		dept d
	on
		e.deptno = d.deptno;

非等值连接

例：找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级？

select 
	e.ename, e.sal, s.grade
from
	emp e
join
	salgrade s
on
	e.sal between s.losal and s.hisal;

自连接

自连接的理解技巧：把一张表看成两张表

例：查询员工的上级领导，要求显示员工名和对应的领导名？

select 
	a.ename as '员工名', b.ename as '领导名'
from
	emp a
join
	emp b
on
	a.mgr = b.empno; 

外连接

在外连接当中，两张表连接，产生了主次关系。

右外连接

right表示将join关键字右边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表。

例：

select 
	e.ename,d.dname
from
	emp e 
right (outer) join 
	dept d
on
	e.deptno = d.deptno;

左外连接

例：

select 
	e.ename,d.dname
from
	dept d 
left (outer) join 
	emp e
on
	e.deptno = d.deptno;

PS：任何一个右连接都有左连接的写法；
任何一个左连接都有右连接的写法。

多张表连接

select 
	...
from
	a
join
	b
on
	a和b的连接条件
join
	c
on
	a和c的连接条件
right join
	d
on
	a和d的连接条件

PS：一条SQL中内连接和外连接可以混合出现

union关键字：合并查询结果集

例：

select ename,job from emp where job = 'MANAGER'
union
select ename,job from emp where job = 'SALESMAN';

PS：union的效率要高一些，在减少匹配次数的情况下，还可以完成两个结果集的拼接。

注意：

• union在进行结果集合并的时候，要求两个结果集的列数相同
• 结果集合并时列和列的数据类型也要一致（MySQL不报错，但oracle报错）

子查询

子查询：select语句中嵌套select语句，被嵌套的select语句称为子查询。

	select
		... (select) ...
	from
		... (select) ...
	where
		... (select) ...

where子句中的子查询

例：找出比最低工资高的员工姓名和工资？

select 
	ename,sal 
from 
	emp 
where 
	sal > (select min(sal) from emp);

from子句中的子查询

理解技巧：将子查询的查询结果当做一张临时表

DDL语句

表的创建

create table 表名(
	字段名1 数据类型, 
	字段名2 数据类型, 
	字段名3 数据类型
);

PS：表名建议以t_ 或者 tbl_开始，可读性强。

MySQl中的数据类型

• varchar(最长255)
  可变长度的字符串
  比较智能，节省空间
  会根据实际的数据长度动态分配空间

• char(最长255)
  定长字符串
  不管实际的数据长度是多少
  分配固定长度的空间去存储数据
  使用不恰当的时候，可能会导致空间的浪费

• int(最长11)
  数字中的整数型，等同于java的int

• bigint
  数字中的长整型，等同于java中的long

• float
  单精度浮点型数据

• double
  双精度浮点型数据

• date
  短日期类型

• datetime
  长日期类型

• clob
  字符大对象
  最多可以存储4G的字符串
  比如：存储一篇文章，存储一个说明

• blob
  二进制大对象
  专门用来存储图片、声音、视频等流媒体数据

例：

create table t_student(
	no int,
	name varchar(32),
	sex char(1),
	age int(3),
	email varchar(255)
);

删除表

drop table t_student; 
// 当这张表不存在的时候会报错！

drop table if exists t_student;
// 不会报错

DML语句

insert语句

insert into 表名 (字段名1, 字段名2, 字段名3 ...) values (值1, 值2, 值3 ...);

注意：

• 字段名和值要一一对应
• insert语句但凡是执行成功了，那么必然会多一条记录。没有给其它字段指定值的话，默认值是NULL。
• insert语句中的“字段名”可以省略。但是省略相当于都写上了，所以值也要都写上！

数字格式化 format()

例：

select ename,format(sal, '$999,999') as sal from emp;

结果：

			+--------+-------+
			| ename  | sal   |
			+--------+-------+
			| SMITH  | 800   |
			| ALLEN  | 1,600 |
			| WARD   | 1,250 |
			| JONES  | 2,975 |
			| MARTIN | 1,250 |
			| BLAKE  | 2,850 |
			| CLARK  | 2,450 |
			| SCOTT  | 3,000 |
			| KING   | 5,000 |
			| TURNER | 1,500 |
			| ADAMS  | 1,100 |
			| JAMES  | 950   |
			| FORD   | 3,000 |
			| MILLER | 1,300 |
			+--------+-------+

日期的处理

MySQL的日期格式

• %Y 年
• %m 月
• %d 日
• %h 时
• %i 分
• %s 秒

str_to_date

str_to_date：将字符串varchar类型转换成date类型

insert into t_user(id,name,birth) values(1, 'zhangsan', str_to_date('01-10-1990','%d-%m-%Y'));

如果提供的日期字符串是以下格式，str_to_date函数就不需要了！！！

%Y-%m-%d

例：

insert into t_user(id,name,birth) values(2, 'lisi', '1990-10-01');

date_format

date_format：将date类型转换成具有一定格式的varchar字符串类型

在进行以下SQL语句时，进行了默认的日期格式化，自动将数据库中的date类型转换成varchar类型，并且采用的格式是MySQL默认的日期格式：’%Y-%m-%d’

select id,name,birth from t_user;

通过date_format函数可以将其转换为想要的日期格式，例如：

select id,name,date_format (birth, '%Y/%m/%d') as birth from t_user;

data和datatime类型的区别

date是短日期：只包括年月日信息
datetime是长日期：包括年月日时分秒信息

短日期默认格式：%Y-%m-%d
长日期默认格式：%Y-%m-%d %h:%i:%s

now() 函数：获取当前的时间（带有时分秒信息），是datetime类型的。

update（修改）

update 
	表名 
set 
	字段名1 = 值1, 字段名2 = 值2, 字段名3 = 值3 ... 
where 
	条件;

注意：没有条件限制会导致所有数据全部更新

delete（删除）

delete from 表名 where 条件;

注意：没有条件，整张表的数据会全部删除！