mysql_node

一、数据库概述

• 数据库（DataBase，DB）：指长期保存在计算机的存储设备上，按照一定规则组织起来，可以被各种用户或应用共享的数据集合。(文件系统)
• 数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中的数据。

数据库软件应该为数据库管理系统，数据库是通过数据库管理系统创建和操作的。

数据库：存储、维护和管理数据的集合。

二、数据库的安装与配置

卸载

	1.停止mysql服务   net stop mysql       启动mysql服务  net start mysql
	2.卸载mysql
	3.找到mysql 安装目录下的 my.ini   datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"

停止MySQL

1添加删除程序中卸载MySQL
2到安装目录删除MySQL
3删除：C:\Documents and Settings\All Users\Application Data\MySQL
C:\ProgramData\MySQL
4查看注册表：
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services

搜索mysql，找到一律干掉！
不需要重启电脑(win xp)，直接重新安装即可
* 修改密码
运行cmd
* 安装成功了打开cmd --> mysql -uroot -p你的密码
* 修改mysql root用户密码
1) 停止mysql服务 运行输入services.msc 停止mysql服务
或者 cmd --> net stop mysql
2) 在cmd下 输入 mysqld --skip-grant-tables 启动服务器 光标不动 （不要关闭该窗口）
3) 新打开cmd 输入mysql -u root -p 不需要密码
use mysql;
update user set password=password(‘abc’) WHERE User=‘root’;
4) 关闭两个cmd窗口 在任务管理器结束mysqld 进程
5) 在服务管理页面 重启mysql 服务
密码修改完成

数据库中一行记录与对象之间的关系

列：字段
行：一条记录(实体)

public class User{
private int id;
private String name;
private int age;
}

三、sql概述

SQL：Structure Query Language。（结构化查询语言）
SQL被美国国家标准局（ANSI）确定为关系型数据库语言的美国标准，后来被国际化标准组织（ISO）采纳为关系数据库语言的国际标准。

各数据库厂商都支持ISO的SQL标准。普通话
各数据库厂商在标准的基础上做了自己的扩展。方言

四、Sql的分类

DDL**（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等； CREATE、 ALTER、DROP
DML***（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）； INSERT、 UPDATE、 DELETE
DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别；
DQL*****（Data Query Language）：数据查询语言，用来查询记录（数据）。SELECT
注意：sql语句以;结尾

4.1 DDL:操作数据库、表、列等

使用的关键字：CREATE、 ALTER、 DROP

4.1.1操作数据库

创建

create database mydb1;
Create database mydb2 character set gbk;
Create database mydb3 character set gbk COLLATE gbk_chinese_ci;

查询
查看当前数据库服务器中的所有数据库

show databases;

修改
查看服务器中的数据库，并把mydb2的字符集修改为utf8;

alter database mydb2 character set utf8;

删除

drop database mydb3;

其他：
查看当前使用的数据库

select database();

切换数据库

use mydb2;

4.1.2操作数据表

• 语法：

    	create table 表名(
    		字段1 字段类型,
    		字段2 字段类型,
    		...
    		字段n 字段类型
    	);
  

• 常用数据类型：
  int：整型
  double：浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99；
  char：固定长度字符串类型； char(10) 'abc ’
  varchar：可变长度字符串类型；varchar(10) ‘abc’
  text：字符串类型;
  blob：字节类型；
  date：日期类型，格式为：yyyy-MM-dd；
  time：时间类型，格式为：hh:mm:ss
  timestamp：时间戳类型 yyyy-MM-dd hh:mm:ss 会自动赋值
  datetime:日期时间类型 yyyy-MM-dd hh:mm:ss

当前数据库中的所有表
show tables;
查看表的字段信息
desc表名;
降序排列（descend 的缩写）
asc
升序排列（ascend 的缩写）
在上面员工表的基本上增加一个image列。
alter table 表名add字段 字段类型;
修改job列，使其长度为60。
alter table 表名 modify 字段 字段类型;
删除image列,一次只能删一列。
alter table 表名 drop 字段名image;
表名改为user。
rename table 旧表名 to user;
查看表格的创建细节
show create table 表名;
show create database db_news;
修改表的字符集为gbk
alert table 表名character set gbk;
列名name修改为username
alter table 表名 change name username varchar(100);
删除表
drop table user ;

4.2 DML操作(重要)

查询表中的所有数据

select * from 表名;

DML是对表的数据进行增、删、改的操作。不要与DDL混淆了。
insert 、update、 delete

小知识： 在mysql中，字符串类型和日期类型都要用单引号括起来。‘tom’ ‘2015-09-04’ 空值：null

4.2.1插入操作：insert:

语法： insert into 表名(列名1，列名2 ...）values(列值1，列值2...);

注意：列名与列值的类型、个数、顺序要一一对应。

可以把列名当做java中的形参，把列值当做实参。
值不要超出列定义的长度。
如果插入空值，请使用null
插入的日期和字符一样，都使用引号括起来。

练习 ：

create table emp(
id int,
name varchar(100),
gender varchar(10),
birthday date,
salary float(10,2),
entry_date date,
resume text
);

insert into emp(id,name,gender,birthday,salary,entry_date,resume)
		values(1,'zhangsan','female','1990-5-10',10000,'2015-5-5-','good girl');

INSERT INTO emp(id,name,gender,birthday,salary,entry_date,resume)
VALUES(2,'lisi','male','1995-5-10',10000,'2015-5-5','good boy');

INSERT INTO emp(id,name,gender,birthday,salary,entry_date,resume)
VALUES(3,'wangwu','male','1995-5-10',10000,'2015-5-5','good boy');

批量插入：

insert into emp values
(4,'zs','m','2015-09-01',10000,'2015-09-01',NULL),
(5,'li','m','2015-09-01',10000,'2015-09-01',NULL),
(6,'ww','m','2015-09-01',10000,'2015-09-01',NULL);

4.2.2 修改操作 update:

	语法：update 表名 set 列名1=列值1，列名2=列值2 ··· where 列名=值

练习：

将所有员工薪水修改为5000元。

update emp set salary=5000 

将姓名为’zs’的员工薪水修改为3000元。

update emp set salary=3000 where name=’ zhangsan’;

将姓名为’aaa’的员工薪水修改为4000元,job改为ccc。

 update emp set salary=4000,gender='female' where name='lisi';

将wu的薪水在原有基础上增加1000元。

 update emp set salary=salary+1000 where gender='male';	

4.2.3 删除操作 delete

语法 ： delete from 表名 where 列名=值

练习 ：

删除表中名称为’zs’的记录。

delete from emp where name=‘zs’;

删除表中所有记录。

delete from emp;

使用truncate删除表中记录。

truncate table emp;

delete 删除表中的数据，表结构还在;删除后的数据可以找回
truncate 删除是把表直接DROP掉，然后再创建一个同样的新表。
删除的数据不能找回。执行速度比DELETE快。

4.3 DQL操作

DQL数据查询语言 （重要）
数据库执行DQL语句不会对数据进行改变，而是让数据库发送结果集给客户端。
查询返回的结果集是一张虚拟表。

查询关键字：select
语法：select 列名 from表名
语法：

select列名
   	 from表名
 	 where condition /*行条件*/
 	 group by grouping_columns /*对结果分组*/
 	 having condition /*分组后的行条件*/
 	 order by sorting_columns /*对结果分组*/
 	 limit offset_start, row_count /*结果限定*/

创建学生表：stu

create table stu (
	sid CHAR(6),
	sname VARCHAR(50),
	age INT,
	gender VARCHAR(50)
);

INSERT INTO stu VALUES('S_1001', 'liuYi', 35, 'male');
INSERT INTO stu VALUES('S_1002', 'chenEr', 15, 'female');
INSERT INTO stu VALUES('S_1003', 'zhangSan', 95, 'male');
INSERT INTO stu VALUES('S_1004', 'liSi', 65, 'female');
INSERT INTO stu VALUES('S_1005', 'wangWu', 55, 'male');
INSERT INTO stu VALUES('S_1006', 'zhaoLiu', 75, 'female');
INSERT INTO stu VALUES('S_1007', 'sunQi', 25, 'male');
INSERT INTO stu VALUES('S_1008', 'zhouBa', 45, 'female');
INSERT INTO stu VALUES('S_1009', 'wuJiu', 85, 'male');
INSERT INTO stu VALUES('S_1010', 'zhengShi', 5, 'female');
INSERT INTO stu VALUES('S_1011', 'xxx', NULL, NULL);

雇员表：emp

CREATE TABLE emp(
	empno		INT,
	ename		VARCHAR(50),
	job		VARCHAR(50),
	mgr		INT,
	hiredate	DATE,
	sal		DECIMAL(7,2),
	comm		decimal(7,2),
	deptno		INT
) ;

INSERT INTO emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

部门表：dept

CREATE TABLE dept(
	deptno		INT,
	dname		varchar(14),
	loc		varchar(13)
);
INSERT INTO dept values(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept values(20, 'RESEARCH', 'DALLAS');
INSERT INTO dept values(30, 'SALES', 'CHICAGO');
INSERT INTO dept values(40, 'OPERATIONS', 'BOSTON');

1基础查询

1.1查询所有列

select * from 表名;

1.2查询指定列

select  字段1,字段2,字段3  from 表名;

2条件查询

2.1条件查询介绍

条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字：
 =、!=、<>、<、<=、>、>=；
 between…and；
 in(set)；
 IS NULL； IS NOT NULL
 AND；
 OR；
 NOT；

2.2查询性别为女，并且年龄50的记录

　　select * from stu 
	where gender='female' and ge<50;

2.3查询学号为S_1001，或者姓名为liSi的记录

	select * from stu 
		where sid ='S_1001' or sname='liSi';

2.4查询学号为S_1001，S_1002，S_1003的记录

select * from stu 
		where sid in('S_1001','S_1002','S_1003');

2.5查询学号不是S_1001，S_1002，S_1003的记录

select * from tab_student 
		where s_number not in('S_1001','S_1002','S_1003');

2.6查询年龄为null的记录

select * from stu 
where age is null;

2.7查询年龄在20到40之间的学生记录

select * from stu
		where age>=20 and age<=40;
或者
select * from stu 
		where age between 20 and 40;

2.8查询性别非男的学生记录

select * from stu 
		where gender!='male';
或者
select * from stu 
		where gender<>'male';
或者
select * from stu 
		where not gender='male';

2.9查询姓名不为null的学生记录

select * from stu 
		where sname is not null;
或者
select * from stu 
		where not sname is null;

3模糊查询

当想查询姓名中包含a字母的学生时就需要使用模糊查询了。模糊查询需要使用关键字like。
通配符:
_ 任意一个字符
%：任意0~n个字符

‘%张%’ ‘张_’

3.1查询姓名由5个字母构成的学生记录

select * from stu 
		where sname like '_____';

模糊查询必须使用like关键字。其中 “”匹配任意一个字母，5个“”表示5个任意字母。

3.2查询姓名由5个字母构成，并且第5个字母为“i”的学生记录

select * from stu 
		where sname like '____i';

3.3查询姓名以“z”开头的学生记录

select * from stu 
		where sname like 'z%';
其中“%”匹配0~n个任何字母。

3.4查询姓名中第2个字母为“i”的学生记录

select * from stu 
		where sname like '_i%';

3.5查询姓名中包含“a”字母的学生记录

select * from stu 
		where sname like '%a%';

4字段控制查询

4.1去除重复记录

去除重复记录（两行或两行以上记录中系列的上的数据都相同），例如emp表中sal字段就存在相同的记录。
当只查询emp表的sal字段时，那么会出现重复记录，那么想去除重复记录，需要使用 distinct 明显的，区别的：

select distinct sal from emp;

4.2查看雇员的月薪与佣金之和

因为sal和comm两列的类型都是数值类型，所以可以做加运算。如果sal或comm中有一个字段不是数值类型，那么会出错。

select *,sal+comm from emp;

comm列有很多记录的值为NULL，因为任何东西与NULL相加结果还是NULL，所以结算结果可能会出现NULL。下面使用了把NULL转换成数值0的函数ifnull：

select *,sal+IFNULL(comm,0) from emp;

4.3给列名添加别名

在上面查询中出现列名为sal+IFNULL(comm,0)，这很不美观，现在我们给这一列给出一个别名，为total：

select *, sal+IFNULL(comm,0) as total from emp;

给列起别名时，是可以省略AS关键字的：复杂别名需要加单引号

5排序 order by 列名 asc(默认) desc

5.1查询所有学生记录，按年龄升序排序

select * from stu 
		order by sage asc;
或者
select * from stu 
		order by sage;

5.2查询所有学生记录，按年龄降序排序

select * from stu 
		order by sage desc;

5.3　查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序

select * from emp
		order by sal desc,empno asc;

6聚合函数 sum avg max min count

聚合函数是用来做纵向运算即字段运算的函数：
 count()：统计指定列不为NULL的记录行数；
 max()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
 min()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
 sum()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
 avg()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

6.1COUNT

当需要纵向统计时可以使用COUNT()。
 查询emp表中记录数：

SELECT COUNT(*) AS cnt FROM emp;

 查询emp表中有佣金的人数：

SELECT COUNT(comm) cnt FROM emp;

注意，因为count()函数中给出的是comm列，那么只统计comm列非NULL的行数。

 查询emp表中月薪大于2500的人数：

SELECT COUNT(*) FROM emp
WHERE sal > 2500;

 统计月薪与佣金之和大于2500元的人数：

SELECT COUNT(*) AS cnt FROM emp WHERE sal+IFNULL(comm,0) > 2500;

 查询有佣金的人数，有领导的人数：

SELECT COUNT(comm), COUNT(mgr) FROM emp;

6.2SUM和AVG

当需要纵向求和时使用sum()函数。
 查询所有雇员月薪和：

SELECT SUM(sal) FROM emp;

 查询所有雇员月薪和，以及所有雇员佣金和：

SELECT SUM(sal), SUM(comm) FROM emp;

 查询所有雇员月薪+佣金和：

SELECT SUM(sal+IFNULL(comm,0)) FROM emp;

 统计所有员工平均工资：

SELECT AVG(sal) FROM emp;

6.3MAX和MIN

 查询最高工资和最低工资：

SELECT MAX(sal), MIN(sal) FROM emp;

7分组查询

当需要分组查询时需要使用GROUP BY子句，例如查询每个部门的工资和，这说明要使用部门来分组。
注：凡和聚合函数同时出现的列名，一定要写在group by 之后

7.1　分组查询

 查询每个部门的部门编号和每个部门的工资和：

select deptno, SUM(sal) from emp
GROUP BY deptno;

查询语句中有 组字段，sum函数，按组求和，是先查出所有的行再按组求和

 查询每个部门的部门编号以及每个部门的人数：

SELECT deptno,COUNT(*) FROM emp
GROUP BY deptno;

 查询每个部门的部门编号以及每个部门工资大于1500的人数：

SELECT deptno,COUNT(*) FROM emp
		WHERE sal>1500
		GROUP BY deptno;

7.2　HAVING子句-重点理解
 查询工资总和大于9000的部门编号以及工资和：

SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno
HAVING SUM(sal) > 9000;

注：having与where的区别:
1. having是在分组后对数据进行过滤.
where是在分组前对数据进行过滤

2 .having后面可以使用聚合函数(统计函数)
where后面不可以使用聚合函数。

WHERE是对分组前记录的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分组；而HAVING是对分组后数据的约束。</>

8LIMIT 方言

limit用来限定查询结果的起始行，以及总行数。

8.1查询5行记录，起始行从0开始

SELECT * FROM emp LIMIT 0, 5;

注意，起始行从0开始，即第一行开始！

8.2查询10行记录，起始行从3开始

SELECT * FROM emp LIMIT 3, 10;

8.3分页查询

如果一页记录为10条，希望查看第3页记录应该怎么查呢？
 第一页记录起始行为0，一共查询10行；
 第二页记录起始行为10，一共查询10行；
 第三页记录起始行为20，一共查询10行；

8.4查询代码的书写顺序和执行顺序

	查询语句书写顺序：select – from- where- group by- having- order by-limit
	查询语句执行顺序：from - where -group by - having - select - order by-limit	

五、数据的完整性

作用：保证用户输入的数据保存到数据库中是正确的。

确保数据的完整性 = 在创建表时给表中添加约束

完整性的分类：

实体完整性
域完整性
引用完整性

1、实体完整性

实体：即表中的一行(一条记录)代表一个实体（entity）
实体完整性的作用：标识每一行数据不重复。
约束类型： 主键约束（primary key） 唯一约束(unique) 自动增长列(auto_increment)

1.1主键约束（primary key）

特点：数据唯一，且不能为null
例：

第一种添加方式：

CREATE TABLE student(
Id int primary key,
Name varchar(50)
);

第二种添加方式：此种方式优势在于，可以创建联合主键

CREATE TABLE student(
id int,
Name varchar(50),
Primary key(id)
);
CREATE TABLE student(
id int,
Name varchar(50),
Primary key(id，name)
);

第三种添加方式：

CREATE TABLE student(
Id int,
Name varchar(50)
);
alter table student add primary key(id);

1.2唯一约束(unique)：

CREATE TABLE student(
Id int primary key,
Name varchar(50) unique
);

1.3自动增长列(auto_increment)

给主键添加自动增长的数值，列只能是整数类型，但是如果删除之前增长的序号，后面再添加的时候序号不会重新开始，而是会接着被删除的那一列的序号

CREATE TABLE student(
Id int primary key auto_increment,
Name varchar(50)
);

INSERT INTO student(name) values(‘tom’);

2、域完整性

域完整性的作用：限制此单元格的数据正确，不对照此列的其它单元格比较
域代表当前单元格
域完整性约束：数据类型 非空约束（not null） 默认值约束(default)
Check约束（mysql不支持） check();

1.1 数据类型:（数值类型、日期类型、字符串类型）

1.2 非空约束：not null

CREATE TABLE student(
Id int pirmary key,
Name varchar(50) not null,
Sex varchar(10)
);

INSERT INTO student values(1,’tom’,null);

1.3 默认值约束 default

CREATE TABLE student(
Id int pirmary key,
Name varchar(50) not null,
Sex varchar(10) default ‘男’
);

insert into student1 values(1,'tom','女');
insert into student1 values(2,'jerry',default);

3、引用完整性（参照完整性）

要有外键必须先有主键，主键和外键的类型必须一致

外键约束：FOREIGN KEY

例：

CREATE TABLE student(
sid int pirmary key,
name varchar(50) not null,
sex varchar(10) default ‘男’
);

create table score(
	id int,
	score int,
	sid int , -- 外键列的数据类型一定要与主键的类型一致
	CONSTRAINT fk_score_sid foreign key (sid) references student(id)
);

constraint 约束

约束名称(必须唯一) foreign key (本表的外键字段) references 主表(主键)

第二种添加外键方式。

ALTER TABLE score1 ADD CONSTRAINT fk_stu_score(约束名称,必须唯一) FOREIGN KEY(sid) REFERENCES stu(id);  

4、表与表之间的关系

 一对一：例如t_person表和t_card表，即人和身份证。这种情况需要找出主从关系，即谁是主表，谁是从表。人可以没有身份证，但身份证必须要有人才行，所以人是主表，而身份证是从表。设计从表可以有两种方案：

 在t_card表中添加外键列（相对t_user表），并且给外键添加唯一约束；

 给t_card表的主键添加外键约束（相对t_user表），即t_card表的主键也是外键。

 一对多（多对一）：最为常见的就是一对多！一对多和多对一，这是从哪个角度去看得出来的。t_user和t_section的关系，从t_user来看就是一对多，而从t_section的角度来看就是多对一！这种情况都是在多方创建外键！

 多对多：例如t_stu和t_teacher表，即一个学生可以有多个老师，而一个老师也可以有多个学生。这种情况通常需要创建中间表来处理多对多关系。例如再创建一张表t_stu_tea表，给出两个外键，一个相对t_stu表的外键，另一个相对t_teacher表的外键。

六、多表查询（重要）

多表查询有如下几种：
 合并结果集；union 、 union all
 连接查询
 内连接 [inner] join on
 外连接 outer join on
 左外连接 left [outer] join
 右外连接 right [outer] join
 全外连接（MySQL不支持）full join
 自然连接 natural join
 子查询

1　合并结果集

1. 作用：合并结果集就是把两个select语句的查询结果合并到一起！
2. 合并结果集有两种方式：
    UNION：去除重复记录，例如：SELECT * FROM t1 UNION SELECT * FROM t2；
    UNION ALL：不去除重复记录，例如：SELECT * FROM t1 UNION ALL SELECT * FROM t2。

要求：被合并的两个结果：列数、列类型必须相同。

2、连接查询 （非常重要）

连接查询就是求出多个表的乘积，例如t1连接t2，那么查询出的结果就是t1*t2。

连接查询会产生笛卡尔积，假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。

那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢，当然是通过条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。

你能想像到emp和dept表连接查询的结果么？emp一共14行记录，dept表一共4行记录，那么连接后查询出的结果是56行记录。
也就你只是想在查询emp表的同时，把每个员工的所在部门信息显示出来，那么就需要使用主外键来去除无用信息了。

使用主外键关系做为条件来去除无用信息

select * from emp,dept where emp.deptno=dept.deptno;

上面查询结果会把两张表的所有列都查询出来，也许你不需要那么多列，这时就可以指定要查询的列了。

SELECT emp.ename,emp.sal,emp.comm,dept.dname 
FROM emp,dept 
WHERE emp.deptno=dept.deptno;

还可以为表指定别名，然后在引用列时使用别名即可。

SELECT e.ename,e.sal,e.comm,d.dname 
FROM emp AS e,dept AS d
WHERE e.deptno=d.deptno;

2.1　内连接

上面的连接语句就是内连接，但它不是SQL标准中的查询方式，可以理解为方言！SQL标准的内连接为：

SELECT * 
FROM emp e 
INNER JOIN dept d 
ON e.deptno=d.deptno;

内连接的特点：查询结果必须满足条件。例如我们向emp表中插入一条记录：

其中deptno为50，而在dept表中只有10、20、30、40部门，那么上面的查询结果中就不会出现“张三”这条记录，因为它不能满足e.deptno=d.deptno这个条件。

2.2　外连接（左连接、右连接）

外连接的特点：查询出的结果存在不满足条件的可能。

左外连接：

SELECT * FROM emp e 
LEFT OUTER JOIN dept d 
ON e.deptno=d.deptno;

左连接是先查询出左表（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL。

这么说你可能不太明白，我们还是用上面的例子来说明。其中emp表中“张三”这条记录中，部门编号为50，而dept表中不存在部门编号为50的记录，所以“张三”这条记录，不能满足e.deptno=d.deptno这条件。但在左连接中，因为emp表是左表，所以左表中的记录都会查询出来，即“张三”这条记录也会查出，但相应的右表部分显示NULL。

右外连接

右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。

例如在dept表中的40部门并不存在员工，但在右连接中，如果dept表为右表，那么还是会查出40部门，但相应的员工信息为NULL。

SELECT * FROM emp e 
RIGHT OUTER JOIN dept d 
ON e.deptno=d.deptno;

连接查询心得：

连接不限与两张表，连接查询也可以是三张、四张，甚至N张表的连接查询。通常连接查询不可能需要整个笛卡尔积，而只是需要其中一部分，那么这时就需要使用条件来去除不需要的记录。这个条件大多数情况下都是使用主外键关系去除。

两张表的连接查询一定有一个主外键关系，三张表的连接查询就一定有两个主外键关系，

所以在大家不是很熟悉连接查询时，首先要学会去除无用笛卡尔积，那么就是用主外键关系作为条件来处理。如果两张表的查询，那么至少有一个主外键条件，三张表连接至少有两个主外键条件。

3、自然连接

大家也都知道，连接查询会产生无用笛卡尔积，我们通常使用主外键关系等式来去除它。而自然连接无需你去给出主外键等式，它会自动找到这一等式：

 两张连接的表中名称和类型完全一致的列作为条件，
例如emp和dept表都存在deptno列，并且类型一致，所以会被自然连接找到！
当然自然连接还有其他的查找条件的方式，但其他方式都可能存在问题！

SELECT * FROM emp NATURAL JOIN dept;
SELECT * FROM emp NATURAL LEFT JOIN dept;
SELECT * FROM emp NATURAL RIGHT JOIN dept;

4、子查询（非常重要）

一个select语句中包含另一个完整的select语句。

子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了。

• 子查询出现的位置：
  where后，作为条为被查询的一条件的一部分；
  from后，作表；
• 当子查询出现在where后作为条件时，还可以使用如下关键字：
  any
  all
• 子查询结果集的形式：
  单行单列（用于条件）
  单行多列（用于条件）
  多行单列（用于条件）
  多行多列（用于表）

练习：
1、工资高于JONES的员工。
分析：
查询条件：工资>JONES工资，其中JONES工资需要一条子查询。

第一步：查询JONES的工资
SELECT sal FROM emp WHERE ename=‘JONES’

第二步：查询高于甘宁工资的员工

SELECT * FROM emp WHERE sal > (${第一步})

结果：

SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='JONES')

2、查询与SCOTT同一个部门的员工。

 子查询作为条件
 子查询形式为单行单列

3、工资高于30号部门所有人的员工信息
分析：

SELECT * FROM emp WHERE sal>(SELECT MAX(sal) FROM emp WHERE deptno=30);

查询条件：工资高于30部门所有人工资，其中30部门所有人工资是子查询。高于所有需要使用all关键字。

第一步：查询30部门所有人工资

SELECT sal FROM emp WHERE deptno=30;

第二步：查询高于30部门所有人工资的员工信息

SELECT * FROM emp WHERE sal > ALL (${第一步})

结果：

SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30)

 子查询作为条件
 子查询形式为多行单列（当子查询结果集形式为多行单列时可以使用ALL或ANY关键字）

4、查询工作和工资与MARTIN（马丁）完全相同的员工信息
分析：
查询条件：工作和工资与MARTIN完全相同，这是子查询

第一步：查询出MARTIN的工作和工资

SELECT job,sal FROM emp WHERE ename='MARTIN'

第二步：查询出与MARTIN工作和工资相同的人

SELECT * FROM emp WHERE (job,sal) IN (${第一步})

结果：

SELECT * FROM emp WHERE (job,sal) IN (SELECT job,sal FROM emp WHERE ename='MARTIN')

5、有2个以上直接下属的员工信息

SELECT * FROM emp WHERE empno IN(SELECT mgr FROM emp GROUP BY mgr HAVING COUNT(mgr)>=2);

 子查询作为条件
 子查询形式为单行多列

6、查询员工编号为7788的员工名称、员工工资、部门名称、部门地址

分析：（无需子查询）
查询列：员工名称、员工工资、部门名称、部门地址
查询表：emp和dept，分析得出，不需要外连接（外连接的特性：某一行（或某些行）记录上会出现一半有值，一半为NULL值）
条件：员工编号为7788

第一步：去除多表，只查一张表，这里去除部门表，只查员工表

SELECT ename, sal FROM emp e WHERE empno=7788

第二步：让第一步与dept做内连接查询，添加主外键条件去除无用笛卡尔积

SELECT e.ename, e.sal, d.dname, d.loc 
FROM emp e, dept d 
WHERE e.deptno=d.deptno AND empno=7788

第二步中的dept表表示所有行所有列的一张完整的表，这里可以把dept替换成所有行，但只有dname和loc列的表，这需要子查询。
第三步：查询dept表中dname和loc两列，因为deptno会被作为条件，用来去除无用笛卡尔积，所以需要查询它。

SELECT dname,loc,deptno FROM dept;

第四步：替换第二步中的dept

SELECT e.ename, e.sal, d.dname, d.loc 
FROM emp e, (SELECT dname,loc,deptno FROM dept) d 
WHERE e.deptno=d.deptno AND e.empno=7788

 子查询作为表
 子查询形式为多行多列

5、自连接:自己连接自己，起别名

求7369员工编号、姓名、经理编号和经理姓名

	SELECT e1.empno , e1.ename,e2.mgr,e2.ename 
	FROM emp e1, emp e2 
	WHERE e1.mgr = e2.empno AND e1.empno = 7369;

练习：
求各个部门薪水最高的员工所有信息

	select e.* from emp e,

–部门最高工资

(select max(sal) maxsal,deptno from emp
group by deptno) a
where e.deptno = a.deptno
and e.sal =a.maxsal

七、MySQL中的函数

一、数学函数

ABS(x)   返回x的绝对值
BIN(x)   返回x的二进制（OCT返回八进制，HEX返回十六进制）
CEILING(x)   返回大于x的最小整数值
EXP(x)   返回值e（自然对数的底）的x次方
FLOOR(x)   返回小于x的最大整数值
GREATEST(x1,x2,...,xn)返回集合中最大的值
LEAST(x1,x2,...,xn)      返回集合中最小的值
LN(x)                    返回x的自然对数
LOG(x,y)返回x的以y为底的对数
MOD(x,y)                 返回x/y的模（余数）
PI()返回pi的值（圆周率）
RAND()返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。
ROUND(x,y)返回参数x的四舍五入的有y位小数的值
SIGN(x) 返回代表数字x的符号的值
SQRT(x) 返回一个数的平方根
TRUNCATE(x,y)            返回数字x截短为y位小数的结果

二、聚合函数(常用于GROUP BY从句的SELECT查询中)

AVG(col)返回指定列的平均值
COUNT(col)返回指定列中非NULL值的个数
MIN(col)返回指定列的最小值
MAX(col)返回指定列的最大值
SUM(col)返回指定列的所有值之和
GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果

三、字符串函数

ASCII(char)返回字符的ASCII码值
BIT_LENGTH(str)返回字符串的比特长度
CONCAT(s1,s2...,sn)将s1,s2...,sn连接成字符串
CONCAT_WS(sep,s1,s2...,sn)将s1,s2...,sn连接成字符串，并用sep字符间隔
INSERT(str,x,y,instr) 将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果
FIND_IN_SET(str,list)分析逗号分隔的list列表，如果发现str，返回str在list中的位置
LCASE(str)或LOWER(str) 返回将字符串str中所有字符改变为小写后的结果
LEFT(str,x)返回字符串str中最左边的x个字符
LENGTH(s)返回字符串str中的字符数
LTRIM(str) 从字符串str中切掉开头的空格
POSITION(substr,str) 返回子串substr在字符串str中第一次出现的位置
QUOTE(str) 用反斜杠转义str中的单引号
REPEAT(str,srchstr,rplcstr)返回字符串str重复x次的结果
REVERSE(str) 返回颠倒字符串str的结果
RIGHT(str,x) 返回字符串str中最右边的x个字符
RTRIM(str) 返回字符串str尾部的空格
STRCMP(s1,s2)比较字符串s1和s2
TRIM(str)去除字符串首部和尾部的所有空格
UCASE(str)或UPPER(str) 返回将字符串str中所有字符转变为大写后的结果

四、日期和时间函数

CURDATE()或CURRENT_DATE() 返回当前的日期
CURTIME()或CURRENT_TIME() 返回当前的时间
DATE_ADD(date,INTERVAL int keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE_ADD(CURRENT_DATE,INTERVAL 6 MONTH);
DATE_FORMAT(date,fmt)  依照指定的fmt格式格式化日期date值
DATE_SUB(date,INTERVAL int keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE_SUB(CURRENT_DATE,INTERVAL 6 MONTH);
DAYOFWEEK(date)   返回date所代表的一星期中的第几天(1~7)
DAYOFMONTH(date)  返回date是一个月的第几天(1~31)
DAYOFYEAR(date)   返回date是一年的第几天(1~366)
DAYNAME(date)   返回date的星期名，如：SELECT DAYNAME(CURRENT_DATE);
FROM_UNIXTIME(ts,fmt)  根据指定的fmt格式，格式化UNIX时间戳ts
HOUR(time)   返回time的小时值(0~23)
MINUTE(time)   返回time的分钟值(0~59)
MONTH(date)   返回date的月份值(1~12)
MONTHNAME(date)   返回date的月份名，如：SELECT MONTHNAME(CURRENT_DATE);
NOW()    返回当前的日期和时间
QUARTER(date)   返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE);
WEEK(date)   返回日期date为一年中第几周(0~53)
YEAR(date)   返回日期date的年份(1000~9999)

一些示例：
获取当前系统时间：

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP());
SELECT EXTRACT(YEAR_MONTH FROM CURRENT_DATE);
SELECT EXTRACT(DAY_SECOND FROM CURRENT_DATE);
SELECT EXTRACT(HOUR_MINUTE FROM CURRENT_DATE);

返回两个日期值之间的差值(月数)：

SELECT PERIOD_DIFF(200302,199802); 

在Mysql中计算年龄：

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(birthday)),'%Y')+0 AS age FROM employee;

这样，如果Brithday是未来的年月日的话，计算结果为0。
下面的SQL语句计算员工的绝对年龄，即当Birthday是未来的日期时，将得到负值。

SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(birthday, '%Y') -(DATE_FORMAT(NOW(), '00-%m-%d') <DATE_FORMAT(birthday, '00-%m-%d')) AS age from employee

五、加密函数

AES_ENCRYPT(str,key)  返回用密钥key对字符串str利用高级加密标准算法加密后的结果，调用AES_ENCRYPT的结果是一个二进制字符串，以BLOB类型存储
AES_DECRYPT(str,key)  返回用密钥key对字符串str利用高级加密标准算法解密后的结果
DECODE(str,key)   使用key作为密钥解密加密字符串str
ENCRYPT(str,salt)   使用UNIXcrypt()函数，用关键词salt(一个可以惟一确定口令的字符串，就像钥匙一样)加密字符串str
ENCODE(str,key)   使用key作为密钥加密字符串str，调用ENCODE()的结果是一个二进制字符串，它以BLOB类型存储
MD5()    计算字符串str的MD5校验和
PASSWORD(str)   返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法。
SHA()    计算字符串str的安全散列算法(SHA)校验和

示例：
复制代码 代码如下:

SELECT ENCRYPT('root','salt');
SELECT ENCODE('xufeng','key');
SELECT DECODE(ENCODE('xufeng','key'),'key');#加解密放在一起
SELECT AES_ENCRYPT('root','key');
SELECT AES_DECRYPT(AES_ENCRYPT('root','key'),'key');
SELECT MD5('123456');
SELECT SHA('123456');

六、控制流函数

MySQL有4个函数是用来进行条件操作的，这些函数可以实现SQL的条件逻辑，允许开发者将一些应用程序业务逻辑转换到数据库后台。

MySQL控制流函数：

CASE WHEN[test1] THEN [result1]...ELSE [default] END如果testN是真，则返回resultN，否则返回default
CASE [test] WHEN[val1] THEN [result]...ELSE [default]END  如果test和valN相等，则返回resultN，否则返回default

IF(test,t,f)   如果test是真，返回t；否则返回f
IFNULL(arg1,arg2) 如果arg1不是空，返回arg1，否则返回arg2
NULLIF(arg1,arg2) 如果arg1=arg2返回NULL；否则返回arg1
这些函数的第一个是IFNULL()，它有两个参数，并且对第一个参数进行判断。如果第一个参数不是NULL，函数就会向调用者返回第一个参数；如果是NULL,将返回第二个参数。

如：

SELECT IFNULL(1,2), IFNULL(NULL,10),IFNULL(4*NULL,'false');

NULLIF()函数将会检验提供的两个参数是否相等，如果相等，则返回NULL，如果不相等，就返回第一个参数。
如：

SELECT NULLIF(1,1),NULLIF('A','B'),NULLIF(2+3,4+1);

和许多脚本语言提供的IF()函数一样，MySQL的IF()函数也可以建立一个简单的条件测试，这个函数有三个参数，第一个是要被判断的表达式，如果表达式为真，IF()将会返回第二个参数，如果为假，IF()将会返回第三个参数。
如：

SELECTIF(1<10,2,3),IF(56>100,'true','false');

IF()函数在只有两种可能结果时才适合使用。然而，在现实世界中，我们可能发现在条件测试中会需要多个分支。在这种情况下，MySQL提供了CASE函数，它和PHP及Perl语言的switch-case条件例程一样。
CASE函数的格式有些复杂，通常如下所示：

复制代码 代码如下:

CASE [expression to be evaluated]
WHEN [val 1] THEN [result 1]
WHEN [val 2] THEN [result 2]
WHEN [val 3] THEN [result 3]
......
WHEN [val n] THEN [result n]
ELSE [default result]
END

这里，第一个参数是要被判断的值或表达式，接下来的是一系列的WHEN-THEN块，每一块的第一个参数指定要比较的值，如果为真，就返回结果。所有的WHEN-THEN块将以ELSE块结束，当END结束了所有外部的CASE块时，如果前面的每一个块都不匹配就会返回ELSE块指定的默认结果。如果没有指定ELSE块，而且所有的WHEN-THEN比较都不是真，MySQL将会返回NULL。

CASE函数还有另外一种句法，有时使用起来非常方便，如下：
CASE
复制代码 代码如下:

WHEN [conditional test 1] THEN [result 1]
WHEN [conditional test 2] THEN [result 2]
ELSE [default result]
END

这种条件下，返回的结果取决于相应的条件测试是否为真。
示例：
复制代码 代码如下:

mysql>SELECT CASE 'green'
     WHEN 'red' THEN 'stop'
     WHEN 'green' THEN 'go' END;
SELECT CASE 9 WHEN 1 THEN 'a' WHEN 2 THEN 'b' ELSE 'N/A' END;
SELECT CASE WHEN (2+2)=4 THEN 'OK' WHEN(2+2)<>4 THEN 'not OK' END ASSTATUS;
SELECT Name,IF((IsActive = 1),'已激活','未激活') AS RESULT FROMUserLoginInfo;
SELECT fname,lname,(math+sci+lit) AS total,
CASE WHEN (math+sci+lit) < 50 THEN 'D'
WHEN (math+sci+lit) BETWEEN 50 AND 150 THEN 'C'
WHEN (math+sci+lit) BETWEEN 151 AND 250 THEN 'B'
ELSE 'A' END
AS grade FROM marks;
SELECT IF(ENCRYPT('sue','ts')=upass,'allow','deny') AS LoginResultFROM users WHERE uname = 'sue';#一个登陆验证

七、格式化函数

DATE_FORMAT(date,fmt)  依照字符串fmt格式化日期date值
FORMAT(x,y)   把x格式化为以逗号隔开的数字序列，y是结果的小数位数
INET_ATON(ip)   返回IP地址的数字表示
INET_NTOA(num)   返回数字所代表的IP地址
TIME_FORMAT(time,fmt)  依照字符串fmt格式化时间time值

其中最简单的是FORMAT()函数，它可以把大的数值格式化为以逗号间隔的易读的序列。
示例：
复制代码 代码如下:

SELECT FORMAT(34234.34323432,3);
SELECT DATE_FORMAT(NOW(),'%W,%D %M %Y %r');
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');
SELECT DATE_FORMAT(19990330,'%Y-%m-%d');
SELECT DATE_FORMAT(NOW(),'%h:%i %p');
SELECT INET_ATON('10.122.89.47');
SELECT INET_NTOA(175790383);

八、类型转化函数

为了进行数据类型转化，MySQL提供了CAST()函数，它可以把一个值转化为指定的数据类型。类型有：

BINARY,CHAR,DATE,TIME,DATETIME,SIGNED,UNSIGNED

示例：
复制代码 代码如下:

SELECT CAST(NOW() AS SIGNED INTEGER),CURDATE()+0;
SELECT 'f'=BINARY 'F','f'=CAST('F' AS BINARY);

九、系统信息函数

DATABASE()   返回当前数据库名
BENCHMARK(count,expr)  将表达式expr重复运行count次
CONNECTION_ID()   返回当前客户的连接ID
FOUND_ROWS()   返回最后一个SELECT查询进行检索的总行数
USER()或SYSTEM_USER()  返回当前登陆用户名
VERSION()   返回MySQL服务器的版本

示例：
复制代码 代码如下:

SELECT DATABASE(),VERSION(),USER();
SELECTBENCHMARK(9999999,LOG(RAND()*PI()));#该例中,MySQL计算LOG(RAND()*PI())表达式9999999次。

八、MySQL数据库的备份与恢复

1生成SQL脚本 导出数据

在控制台使用mysqldump命令可以用来生成指定数据库的脚本文本，
但要注意，脚本文本中只包含数据库的内容，而不会存在创建数据库的语句！所以在恢复数据时，还需要自已手动创建一个数据库之后再去恢复数据。

mysqldump –u用户名 –p密码 数据库名>生成的脚本文件路径
mysqldump -uroot -p321210 db_news>C:\Users\fish>my.sql

现在可以在C盘下找到mydb1.sql文件了！
注意，mysqldump命令是在Windows控制台下执行，无需登录mysql！！！

2执行SQL脚本 恢复数据

前提：必须先创建数据库名

执行SQL脚本需要登录mysql，然后进入指定数据库，才可以执行SQL脚本！！！
执行SQL脚本不只是用来恢复数据库，也可以在平时编写SQL脚本，然后使用执行SQL 脚本来操作数据库！大家都知道，在黑屏下编写SQL语句时，就算发现了错误，可能也不能修改了。所以我建议大家使用脚本文件来编写SQL代码，然后执行之！

SOURCE C:\mydb1.sql

注意，在执行脚本时需要先行核查当前数据库中的表是否与脚本文件中的语句有冲突！例如在脚本文件中存在create table a的语句，而当前数据库中已经存在了a表，那么就会出错！

还可以通过下面的方式来执行脚本文件：

mysql -uroot -p123 mydb1<c:\mydb1.sql
mysql –u用户名 –p密码 数据库<要执行脚本文件路径
 source C:\Users\fish\my.sql 

这种方式无需登录mysql！

注意：在CMD下 命令不能加;