MySQL数据库的介绍

MySQL数据库
1.什么是数据库？

2.数据库的分类?具体含义？常见的数据库？

关系型数据库:
关系数据库，是建立在关系数据库模型基础上的数据库，借助于集合代数等概念和方法来处理数据库中的数据，同时也是一个被组织成一组拥有正式描述性的表格，该形式的表格作用的实质是装载着数据项的特殊收集体，这些表格中的数据能以许多不同的方式被存取或重新召集而不需要重新组织数据库表格。关系数据库的定义造成元数据的一张表格或造成表格、列、范围和约束的正式描述。每个表格（有时被称为一个关系）包含用列表示的一个或更多的数据种类。 每行包含一个唯一的数据实体，这些数据是被列定义的种类。当创造一个关系数据库的时候，你能定义数据列的可能值的范围和可能应用于那个数据值的进一步约束。而SQL语言是标准用户和应用程序到关系数据库的接口。其优势是容易扩充，且在最初的数据库创造之后，一个新的数据种类能被添加而不需要修改所有的现有应用软件。主流的关系数据库有oracle、db2、sqlserver、sybase、mysql 等。

关系数据库—标志需要使用表格来存储数据。
表格
1.列
2.每一个列需要对应的数据类型来限制需要被保存的数据值
3.需要使用约束，来约束每一个列中的数据
4.行就代表一个实体数据

非关系型数据库:
非关系型数据库，又被称为NoSQL（Not Only SQL )，意为不仅仅是SQL（ Structured QueryLanguage，结构化查询语言)，据维基百科介绍，NoSQL最早出现于1998 年，是由Carlo Storzzi最早开发的个轻量、开源、不兼容SQL 功能的关系型数据库，2009 年，在一次分布式开源数据库的讨论会上，再次提出了NOSQL 的概念，此时NOSQL主要是指I非关系型、分布式、不提供ACID (数据库事务处理的四个本要素)的数据库设计模式。同年，在亚特兰大举行的“NO:SQL(east)”讨论会上，对NOSQL 最普遍的定义是“非关联型的”，强调Key-Value 存储和文档数据库的优点，而不是单纯地反对RDBMS，至此，NoSQL 开始正式出现在世人面前。

常见的关系型数据库:

3.MySQL数据库服务器、数据库和表的关系？
MySQL简介:
MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件。
MySQL是一种关系数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库。
由于其社区版的性能卓越，搭配 PHP 和 Apache 可组成良好的开发环境。LAMP
LAMP–一套网站开发和运行环境
L–Linux
A–Apache Tomcat
M–MySQL
P–PHP

MySQL数据库服务器、数据库和表的关系
所谓数据库服务器，只是在机器上装了一个数据库管理程序，这个管理程序可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。
为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体的数据。
数据库服务器、数据库和表的关系如图所示：

MySQL数据库服务器就是一个数据库管理程序/一个数据库管理软件。
我们一个操作系统上安装一个MySQL数据库服务器，打开进入到MySQL数据库服务器上，就可以给这个MySQL数据库服务器创建一个/多个数据库，为每一个数据库创建一个/对个数据表，可以向数据表中保存实体数据。
通常情况下都是一个应用程序对应一个数据库，这个一个数据库中有好多张表，来存储数据。
4.MySQL数据库服务器下载和安装？【解决MySQL安装到最后一步未响应的三种方法】
MySQL数据库服务器下载
https://downloads.mysql.com/archives/community/

6.MySQL数据库服务器的安装和卸载
卸载MySQL数据库服务器
1、卸载MySQL
2、删除安装目录及数据存放目录
3、在注册表(regedit)查询mysql，全部删除,
注意的是注册表 cmd -> regedit
1.HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\Eventlog\Application\MySQL 目录
2.HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services\Eventlog\Application\MySQL 目录
3.HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application\MySQL 目录
4.HKEY_LOCAL_MACHINE\SYSTEM\CurrentControl001\Services\MYSQL 目录
5.HKEY_LOCAL_MACHINE\SYSTEM\CurrentControl002\Services\MYSQL 目录
6.HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MYSQL 目录
4、在c盘查询MySQL，全部删除 ;一般是在ProgramData文件夹下（该文件是隐藏的，需要设置为显示隐藏文件）和winbdows文件夹下
参看MySQL安装图解文件

连接登录数据库服务器
1.Mysql数据库自带的命令提示行
开始–所有程序–MySQL–自带的命令提示行–点击

2 在windows系统自带的命令行登录
开始–cmd–
1.连接登录本机数据库服务器
mysql -u用户名 -p登录密码

2.连接登录其他计算机的数据库服务器
mysql -h计算机ip地址 -u用户名 -p登录密码

注意：在登录数据库服务器之前一定要打开mySQL数据库服务。

打开mySQL数据库服务
在登陆数据库服务器的时候一定要保证mysql数据库服务是开启的，否则我们是无法登陆数据服务器。

1.控制面板–管理工具–服务–Mysql服务—启动

2.开始–cmd–
C:\Users\Administrator>net start 数据库服务名称

C:\Users\Administrator>net start MySQL5.5.62
MySQL5.5.62 服务正在启动 .
MySQL5.5.62 服务已经启动成功。

关闭mySQL数据库服务
1.控制面板–管理工具–服务–Mysql服务—停止

2.开始–cmd–
C:\Users\Administrator>net stop 数据库服务名称
C:\Users\Administrator>net stop MySQL5.5.62
MySQL5.5.62 服务正在停止.
MySQL5.5.62 服务已成功停止。

前面的这些操作都是在命令提示行下完成的，不管是Mysql数据库自带的命令行还是windows系统自带的命令行。在命令提示行下访问数据库不太方便，所以我们来安装一个有图形界面的数据库访问工具
有图形界面MySQL数据库访问工具
1.Navicat（http://www.navicat.com/）
2.SQLyog (https://www.webyog.com/)
3.MySQL-Front (http://www.mysqlfront.de/)

5.SQL语言
SQL语言:
1.SQL—Structured Query Language, 结构化查询语言
2.各厂商增强了过程性语言的特征
如Oracle的PL/SQL 过程性处理能力
SQL Server、Sybase的T-SQL
3.SQL是用来存取关系数据库的语言，具有查询、操纵、定义和控制关系型数据库的四方面功能。
1.基本数据库操作语句
SQL分类
1 DDL （数据定义语句）
数据定义语言 - Data Definition Language
用来定义数据库的对象，如数据表、视图、索引等
2 DML （数据操纵语句）
数据处理语言 - Data Manipulation Language
在数据库表中更新，增加和删除记录
如 update， insert， delete — 增删改
3 DCL （数据控制语句）
数据控制语言 – Data Control Language
指用于设置用户权限和控制事务语句
如grant，revoke，if…else，while，begin transaction
4 DQL （数据查询语句）
数据查询语言 – Data Query Language
Select

使用SQL语言
C:\Users\Administrator>mysql -uroot -p123456
ERROR 2003 (HY000): Can’t connect to MySQL server on ‘localhost’ (10061)

C:\Users\Administrator>net start MySQL5.5
MySQL5.5 服务正在启动 .
MySQL5.5 服务已经启动成功。

C:\Users\Administrator>mysql -uroot -p123456
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.27 MySQL Community Server (GPL)

Copyright © 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

//show databases–查看数据库服务器上的所有数据库
mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
±-------------------+
4 rows in set (0.01 sec)
//use test; – 选中自己需要使用的数据库
mysql> use test;
Database changed

// show tables; --查看数据库中的数据表
mysql> show tables;
Empty set (0.00 sec)
2.MySQL常用数据类型
字符串型 VARCHAR、CHAR
大数据类型BLOB、TEXT
数值型TINYINT 、SMALLINT、INT、BIGINT、FLOAT、DOUBLE
逻辑性 BIT
日期型DATE、TIME、DATETIME、TIMESTAMP
例如：
创建一个员工表employee1
具体字段描述如下：

 create table employee1 (
id int ,
 name varchar(20) not null,
 gender varchar(10),
 birthday date,
 entry_date date,
 job varchar(30),
 salary double,
 resume text
 );

查看表结构: desc 表名;
desc employee1 ;

3.MySQL定义表的字段的约束
1.定义主键约束:primary key:不允许为空，不允许重复
2.删除主键：alter table tablename drop primary key ;
3.主键自动增长 ：auto_increment
4.定义唯一约束:unique 例如：name varchar(20) unique
5.定义非空约束:not null 例如：salary double not null
例如：
创建一张员工表employee2，要求把id 设置成主键，并且自动增长。name不允许为空。

create table employee (
id int primary key auto_increment,
 name varchar(20) not null,
 gender varchar(10),
 birthday date,
 entry_date date,
 job varchar(30),
 salary double,
 resume text
 );

4.基本数据库表操作语句
一.删除表
删除employee1表
drop table employee1;
二.数据表的结构的修改：

1、在上面员工表的基本上增加一个image列。
  	  alter table employee2 add image varchr(20);
	2、修改job列，使其长度为60。
	  alter table employee2 modify job varchar(60);
	3、删除gender列。
	   alter table employee2 drop gender;
	4、表名改为user。
	   rename table employee2 to user;
	5、修改表的字符集为utf8
	   alter table user character set utf8;
	6、列名name修改为username
	    alter table user change name username varchar(20) not null;

三.查看表结构

1、查看数据库内的所有表
	   show tables;
	2、查看employee的建表语句
	   show create table employee1;
	3、查看employee的表结构
	   desc employee1;

5.INSERT 语句向表中插入数据
语法结构：
INSERT INTO table [(column [, column…])] VALUES (value [, value…]);
1.插入的数据应与字段的数据类型相同。
2.数据的大小应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。
3.在values中列出的数据位置必须与被加入的列的排列位置相对应。
4.字符和日期型数据应包含在单引号中。
5.插入空值：不指定或insert into table value(null)
例如：
向employee1中插入三个员工信息
6.update语句修改表中数据
语法结构：
UPDATE tbl_name
SET col_name1=expr1 [, col_name2=expr2 …]
[WHERE where_definition]
1.UPDATE语法可以用新值更新原有表行中的各列。
2.SET子句指示要修改哪些列和要给予哪些值。
3.WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。
例如：

1、将所有员工薪水修改为5000元。
	   UPDATE employee SET salary=5000;
	2、将姓名为’zs’的员工薪水修改为3000元。
	    UPDATE employee SET salary=3000 WHERE NAME='zs';
	3、将姓名为’ls’的员工薪水修改为4000元,job改为ccc。
	    UPDATE employee SET salary=4000,job='ccc' WHERE NAME='ls';
	4、将wangwu的薪水在原有基础上增加1000元。
	   UPDATE employee SET salary=salary+1000 WHERE NAME='wangwu';

7.delete语句删除表中数据
语法结构：
delete from tbl_name
[WHERE where_definition]
1.如果不使用where子句，将删除表中所有数据。
2.Delete语句不能删除某一列的值（可使用update）
3.使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句。
4.同insert和update一样，从一个表中删除记录将引起其它表的参照完整性问题，在修改数据库数据时，头脑中应该始终不要忘记这个潜在的问题。外键约束
例如：

1、删除表中名称为’zs’的记录。
	   DELETE FROM employee WHERE NAME='zs';
	2、删除表中所有记录。
	   DELETE FROM employee;

6.select语句
1.基本select语句
语法结构：
SELECT [DISTINCT] *|{column1, column2. column3…} FROM table;
1.select 指定查询哪些列的数据。
2.column指定列名。
3.*号代表查询所有列。
4.from指定查询哪张表。
5.DISTINCT可选，指显示结果时，是否剔除重复数据
例如：

CREATE TABLE exam(
		id INT PRIMARY KEY AUTO_INCREMENT,
		NAME VARCHAR(20) NOT NULL,
		chinese DOUBLE,
		math DOUBLE,
		english DOUBLE
	);
	INSERT INTO exam VALUES(NULL,'关羽',85,76,70);
	INSERT INTO exam VALUES(NULL,'张飞',70,75,70);
	INSERT INTO exam VALUES(NULL,'赵云',90,65,95);
	INSERT INTO exam VALUES(NULL,'刘备',97,50,50);
	INSERT INTO exam VALUES(NULL,'曹操',90,89,80);
	INSERT INTO exam VALUES(NULL,'司马懿',90,67,65);

1、查询表中所有学生的信息。
SELECT * FROM exam;
2、查询表中所有学生的姓名和对应的英语成绩。
SELECT NAME,english FROM exam;
3、过滤表中重复数据。
SELECT DISTINCT * FROM exam GROUP BY ;
24.在select语句中可使用表达式对查询的列进行运算
语法格式：
SELECT *|{column1｜expression, column2｜expression，…} FROM table;
例如：
统计每个学生的总分。
SELECT * ,(chinese+math+english) AS 总分 FROM exam;
25.在select语句中可使用as语句
语法格式：
SELECT column as 别名 from 表名;
例如：
使用别名表示学生分数。
SELECT id,NAME 名字, chinese 语文,math 数学, english 英语 FROM exam;

26.使用where子句，进行过滤查询
在where子句中经常使用的运算符

注意：Like语句中，% 代表零个或多个任意字符，_ 代表一个字符，例first_name like ‘_a%’;
例如：

1.查询姓名为刘备的学生成绩
SELECT NAME,chinese,math,english FROM exam WHERE NAME='刘备';
2.查询英语成绩大于90分的同学
SELECT * FROM exam WHERE english>90;
3.查询总分大于200分的所有同学
SELECT * FROM exam WHERE (chinese+math+english)>200;
4.查询英语分数在 80－90之间的同学。
SELECT * FROM exam WHERE english BETWEEN 80 AND 90;           
5.查询数学分数为89,75,91的同学。
SELECT * FROM exam WHERE math=85 OR math=75 OR math=91;
SELECT * FROM exam WHERE math IN(85,75,91);
6.查询所有姓刘的学生成绩。
SELECT * FROM exam WHERE NAME LIKE '刘%';
7.查询所有姓刘两个字的学生成绩。
SELECT * FROM exam WHERE NAME LIKE '刘_';
8.查询数学分>80并且语文分>80的同学。
SELECT * FROM exam WHERE math>80 OR chinese>80;
9.查询数学分>80 或者 语文分>80的同学。
SELECT * FROM exam WHERE math>80 AND chinese>80;

27.使用order by 子句排序查询结果。
语法结构：
SELECT column1, column2. column3…
FROM table;
order by column asc|desc
1.Order by 指定排序的列，排序的列即可是表中的列名，也可以是select 语句后指定的列名。
2.Asc 升序、Desc 降序
3.ORDER BY 子句应位于SELECT语句的结尾。
例如：

1.对数学成绩排序后输出。
SELECT * FROM exam ORDER BY math;
2.对总分排序按从高到低的顺序输出
	SELECT * FROM exam ORDER BY (chinese+math+english) DESC;	    
3.对姓刘的学生成绩排序输出
	    SELECT * FROM exam WHERE NAME LIKE '刘%' ORDER BY chinese;

28.SQL聚合函数
1.Count(列名)返回某一列，行的总数
语法结构：

Select count(*)|count(列名) from tablename
		[WHERE where_definition] 
2.Sum函数返回满足where条件的行的和
语法结构：
Select sum(列名)｛,sum(列名)…｝ from tablename
		[WHERE where_definition]  
注意：1.sum仅对数值起作用，否则会报错。
2.对多列求和，“，”号不能少。
3.AVG函数返回满足where条件的一列的平均值
语法结构：
Select avg(列名)｛,avg(列名)…｝ from tablename
		[WHERE where_definition]  
4.Max/min函数返回满足where条件的一列的最大/最小值
语法结构：
Select max(列名)/min(列名)　from tablename
		[WHERE where_definition] 

练习：

统计一个班级共有多少学生？
	   SELECT COUNT(*) AS 总人数 FROM exam;
统计数学成绩大于或等于90的学生有多少个？
	    SELECT COUNT(*) FROM exam WHERE math>90 OR math=90;
统计总分大于250的人数有多少？
	   SELECT COUNT(*) FROM exam WHERE (chinese+math+english)>250;
统计一个班级数学总成绩？
	    SELECT SUM(math) FROM exam ;
统计一个班级语文、英语、数学各科的总成绩
	    SELECT SUM(chinese) AS 语文,SUM(math) AS 数学,SUM(english) AS 英语 FROM exam;
统计一个班级语文、英语、数学的成绩总和 
	    SELECT SUM(chinese+math+english) FROM exam;
统计一个班级语文成绩平均分
	    SELECT AVG(chinese) 语文平均分 FROM exam;
求一个班级数学平均分？
	    SELECT AVG(math) FROM exam;
求一个班级总分平均分
	   SELECT AVG(chinese+math+english) FROM exam;
求班级最高分和最低分（数值范围在统计中特别有用）
	SELECT MAX(chinese+math+english) FROM exam;
	SELECT MIN(chinese+math+english) FROM exam;

分页查询

Select  [*/col]   from  tablename limit 参数1  ,  参数2
参数1---每页数据的起始值  (当前页码 - 1)* 每页记录数
参数2--每页记录数
select * from  t_exam;
--每页4条记录
--显示第2页数据
select  *  from  t_exam limit 4,4;
--显示第3页数据
select  *  from  t_exam limit 8,4;

29.GROUP BY子句可以将查询结果按属性列或属性列组合在行的方向上进行分组，每组在属性列或属性列组合上具有相同的值。

注意，使用GROUP BY 时，SELECT 子句中只能由以下部分组成：

1. 汇总函数
2. GROUP BY 中出现的列名
3. 1和2 通过函数和表达式的结果
   例如：

SELECT A_ID +COUNT(*) FROM C GROUP BY A_ID;    =>合法
SELECT A_DATE,COUNT(*) FROM C GROUP BY A_ID; =>不合法，

如图示中的按A_ID分组后，对于其他各列的查询只能是汇总查询，否则没有意义
练习：

CREATE TABLE orders(
	id INT,
	product VARCHAR(20),
	price FLOAT
	);
	INSERT INTO orders(id,product,price) VALUES(1,'电视',900);
	INSERT INTO orders(id,product,price) VALUES(2,'洗衣机',100);
	INSERT INTO orders(id,product,price) VALUES(3,'洗衣粉',90);
	INSERT INTO orders(id,product,price) VALUES(4,'桔子',9);
	INSERT INTO orders(id,product,price) VALUES(5,'洗衣粉',90);

查询购买了几类商品，并且每类总价大于100的商品
SELECT product,SUM(price) FROM orders WHERE price>100 GROUP BY product;
–having是对分组之后的结果进行条件查询
–having的查询条件必须在分组查询的结果中出现
select product,price from orders group by product having price > 100;
2.多表查询
2.1表与表之间的关系
定义外键约束
foreign key
foreign key(ordersid) references orders(id)
练习：
新建部门表department (id,name)，通过外键约束建立与员工表employee关系
多表设计中三种实体关系：

–一对一主键键关联设计
–创建用户信息表

create  table t_user(
u_id int  primary key auto_increment,
u_name varchar(20),
u_age int,
u_sex bit,
u_address varchar(30),
foreign key t_user(u_id) references t_card(c_id)
);

–创建用户省份证信息表

create table t_card(
c_id int primary key auto_increment,
c_number varchar(18),
c_arg varchar(10),
c_year int
);

–向用户省份证信息表中添加测试数据

insert  into t_card  values(null,'123456789012345678','陕西.西安',10);
insert  into t_card  values(null,'098765432112345678','陕西.铜川',10);

–向用户信息表中添加测试数据

insert  into t_user values(null,'zhangsan',23,true,'长安县');
insert  into t_user values(null,'lisi',24,false,'雁塔区');

–一对一外键关联设计
–创建用户信息表

create  table t_user(
u_id int  primary key auto_increment,
u_name varchar(20),
u_age int,
u_sex bit,
u_address varchar(30),
card_id int unique,
foreign key t_user(card_id) references t_card(c_id)
);

–创建用户省份证信息表

create table t_card(
c_id int primary key auto_increment,
c_number varchar(18),
c_arg varchar(10),
c_year int
);

–向用户省份证信息表中添加测试数据

insert  into t_card  values(null,'123456789012345678','陕西.西安',10);
insert  into t_card  values(null,'098765432112345678','陕西.铜川',10);

–向用户信息表中添加测试数据

insert  into t_user values(null,'zhangsan',23,true,'长安县',1);
insert  into t_user values(null,'lisi',24,false,'雁塔区',2);

一对多关联设计

–一对多关联设计
–注意：外键要设计在多方维护
–创建一个班级表

create table t_class(
c_id int primary key auto_increment,
c_number varchar(10),
c_name varchar(10)
);

–向班级表添加测试数据

insert  into  t_class values(null,'J20180903','javaEE');
insert  into  t_class values(null,'A20181010','Android');
insert  into  t_class values(null,'I20181111','IOS');

–创建学生表

create  table t_student(
s_id int primary key auto_increment,
s_name varchar(20),
s_age int,
s_sex bit,
s_address varchar(30),
class_id int,
foreign key t_student(class_id) references t_class(c_id)
);

–向学生表添加测试数据

insert  into  t_student values(null,'zhangsan',23,true,'西安',1);
insert  into  t_student values(null,'lisi',24,false,'北京',2);
insert  into  t_student values(null,'wangwu',25,true,'上海',3);
insert  into  t_student values(null,'zhangsansan',26,true,'西安南',1);
insert  into  t_student values(null,'lisisi',27,false,'北京北',2);
insert  into  t_student values(null,'wangwuwu',28,true,'上海东',3);

多对多关联设计

–多对多关联设计
–注意：需要有一个中间表来维护关联关系，保存的是不同表的主键
–创建角色表

create table t_role(
r_id int primary key auto_increment,
r_name varchar(10),
r_desc varchar(30)
);

–创建项目组表

create  table t_group(
g_id int primary key auto_increment,
g_name varchar(20),
g_desc varchar(30)
);

–创建中间表维护关联关系

create  table t_rolegroup(
z_id int primary key auto_increment,
role_id int,
group_id int
);

–通过修改表结构的方式添加外键

alter table t_rolegroup add constraint fk1 foreign key (role_id) references t_role(r_id);
alter table t_rolegroup add constraint fk2 foreign key (group_id) references t_group(g_id);

–向角色表中添加测试数据

insert  into t_role values(null,'java程序员','负责开发java程序');
insert  into t_role values(null,'测试员','负责测试程序');

–向项目组表中添加测试数据

insert  into t_group values(null,'CRM组','负责开发CRM系统');
insert  into t_group values(null,'ERP组','负责开发ERP系统');

–向中间表中添加测试数据

insert  into t_rolegroup values(null,1,1);
insert  into t_rolegroup values(null,1,2);
insert  into t_rolegroup values(null,2,1);
insert  into t_rolegroup values(null,2,2);

多表查询——笛卡尔积

就是将多张表中有意义的数据象做乘法运算一样保存在一张表中。
2.2外键的定义
建立外键的前提： 本表的列必须与外键类型相同(外键必须是外表主键)。

外键作用： 使两张表形成关联，外键只能引用外表中的列的值！

指定主键关键字： foreign key(列名)

引用外键关键字： references <外键表名>(外键列名)

事件触发限制: on delete和on update , 可设参数cascade(跟随外键改动), restrict(限制外表中的外键改动),set Null(设空值）,set Default（设默认值）,[默认]no action

例如：

outTable表 主键 id 类型 int

创建含有外键的表：

create table temp( 
id int, 
name char(20), 
foreign key(id) references outTable(id) on delete cascade on update cascade); 

说明：把id列 设为外键 参照外表outTable的id列 当外键的值删除 本表中对应的列筛除 当外键的值改变 本表中对应的列值改变。
2.3子查询
就是将一个查询语句包含在另一个查询语句中，那么这个被包含的查询语句就是子查询语句。
子查询练习：

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);
	INSERT INTO emp VALUES(7981,'MILLER','CLERK',7788,'1992-01-23',2600,500,20);
	
	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.单行子查询(> < >= <= = <>)
查询出高于10号部门的平均工资的员工信息

SELECT * FROM emp WHERE sal >(SELECT AVG(sal) FROM emp WHERE deptno=10 );

2.多行子查询(in not in any all)
查询出比10号部门任何员工薪资高的员工信息

SELECT * FROM emp WHERE sal > ANY(SELECT sal FROM emp WHERE deptno=10) AND deptno!=10;

3.多列子查询(实际使用较少)
和10号部门同名同工作的员工信息

SELECT * FROM emp WHERE (ename,job)IN(SELECT ename,job FROM emp WHERE deptno=10) AND deptno!=10;

4.select 后面接子查询
获取员工的名字和部门的名字

SELECT p.ename,d.dname FROM emp p,dept d WHERE p.deptno=d.deptno;

5.from 后面接子查询
查询emp表中经理信息

	SELECT * FROM emp e,(SELECT DISTINCT mgr FROM emp) AS jingli WHERE e.empno=jingli.mgr; 

6.where 后面接子查询
薪资高于10号部门平均工资的所有员工信息

SELECT * FROM emp WHERE sal>(SELECT AVG(sal) FROM emp WHERE deptno=10);

7. group by 后面接子查询
   有哪些部门的平均工资高于30号部门的平均工资

SELECT deptno, AVG(sal) AS bumen FROM emp GROUP BY deptno HAVING bumen > (SELECT AVG(sal) FROM emp WHERE deptno=30);

8.工资>JONES工资

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

9.查询与SCOTT同一个部门的员工

SELECT * FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename='SCOTT') AND ename!='SCOTT';

10.工资高于30号部门所有人的员工信息

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

11.查询工作和工资与MARTIN完全相同的员工信息

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

12.得出两个以上直接下属的员工信息

1.SELECT * FROM emp WHERE empno IN (SELECT mgr FROM emp GROUP BY mgr HAVING COUNT(mgr) >2);
	  2.SELECT * FROM emp e1 WHERE e1.empno IN (SELECT e2.mgr FROM emp e2 GROUP BY e2.mgr HAVING COUNT(*)>2);

7.联合查询
内连接查询 【inner join】
左连接查询 【left join】 左外连接查询 【left outer join】
右连接查询 【right join】 右外连接查询 【right outer join】
内连接与左连接和右连接查询的区别
–内连接：显示左边右边共有的
–左连接：左边有的，右边没有的为null
–右连接：右边有的，左边没有的为null
语法结构:
select [col1,col2…coln来自多张表【最好使用别名】]
from table1
inner join/
left outer join/left join/
right outer join/right join
table2
on table1.col = table2.col
where 查询条件

多表查询——连接查询
–1.内连接查询 【inner join】
–2.左外连接查询 【left outer join/left join】
–3.右外连接查询 【right outer join/right join】
–4.全连接查询【不解释】

–语法格式

/*
select  [col1,col2...coln来自多张表【最好使用别名】]
from table1 
inner  join/
left  outer join/left   join/
right outer join/right  join
table2 
on table1.col = table2.col
where 查询条件
*/

--创建一个班级表
create table t_class(
c_id int primary key auto_increment,
c_number varchar(10),
c_name varchar(10)
);
--向班级表添加测试数据
insert  into  t_class values(null,'J20180903','javaEE');
insert  into  t_class values(null,'A20181010','Android');
insert  into  t_class values(null,'I20181111','IOS');
--创建学生表
create  table t_student(
s_id int primary key auto_increment,
s_name varchar(20),
s_age int,
s_sex bit,
s_address varchar(30),
class_id int,
foreign key t_student(class_id) references t_class(c_id)
);
--向学生表添加测试数据
insert  into  t_student values(null,'zhangsan',23,true,'西安',1);
insert  into  t_student values(null,'lisi',24,false,'北京',2);
insert  into  t_student values(null,'wangwu',25,true,'上海',3);
insert  into  t_student values(null,'zhangsansan',26,true,'西安南',1);
insert  into  t_student values(null,'lisisi',27,false,'北京北',2);
insert  into  t_student values(null,'wangwuwu',28,true,'上海东',3);

--查询姓名是zhangsna的地址和班级名称
select 学生表.s_name,学生表.s_address,班级表.c_name 
from t_student as 学生表 
inner  join 
t_class as 班级表 
on 学生表.class_id = 班级表.c_id 
where 学生表.s_name='zhangsna';

--根据班级编号“I20181111”的所有学生信息
select 学生表.s_id ,
       学生表.s_name,
       学生表.s_age,
       学生表.s_sex,
       学生表.s_address 
from t_student as 学生表 
left  join 
t_class as 班级表 
on 学生表.class_id = 班级表.c_id 
where 班级表.c_number='I20181111';

--查询姓名是lisi的所有信息包括班级信息
select 学生表.s_id ,
       学生表.s_name,
       学生表.s_age,
       学生表.s_sex,
       学生表.s_address,
       班级表.c_id,
       班级表.c_number,
       班级表.c_name 
from t_student as 学生表 
right  join 
t_class as 班级表 
on 学生表.class_id = 班级表.c_id 
where 学生表.s_name='lisi';

–内连接与左连接和右连接查询的区别
–内连接：显示左边右边共有的
–左连接：左边有的，右边没有的为null
–右连接：左边没有的，右边有的为null

综合练习：
1查询员工编号为7788的员工名称,员工工资,部门名称,部门地址

SELECT e.*,d.loc FROM emp e,dept d WHERE e.empno=7788 AND e.deptno=d.deptno;

2.查询出高于本部门平均工资的员工信息-

SELECT *,AVG(sal) AS 平均工资 FROM emp GROUP BY deptno;
	SELECT * FROM emp e WHERE sal > (SELECT AVG(sal) AS 平均工资 FROM emp d GROUP BY deptno HAVING e.deptno=d.deptno);

-- 方式二:

	-- SELECT * FROM emp e1 WHERE e1.sal > (SELECT AVG(e2.sal) FROM emp e2 WHERE e1.deptno=e2.deptno  GROUP BY e2.deptno);

3.列出达拉斯加工作的人中,比纽约平均工资高的人

SELECT AVG(sal) FROM emp e INNER JOIN dept d ON e.deptno=d.deptno WHERE d.loc='NEW YORK';
	
	SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno WHERE  d.loc='DALLAS' 
	AND sal>(SELECT AVG(sal) FROM emp e INNER JOIN dept d ON e.deptno=d.deptno WHERE d.loc='NEW YORK');

-- 方法二

	SELECT * FROM emp WHERE deptno = (SELECT deptno FROM dept WHERE loc='DALLAS')
	AND sal > (SELECT AVG(sal) FROM emp WHERE deptno = (SELECT deptno FROM dept WHERE loc='NEW YORK'));

4.查询7369员工编号,姓名,经理编号和经理姓名

SELECT mgr FROM emp WHERE empno=7369;
	SELECT ename FROM emp WHERE empno =(SELECT mgr FROM emp WHERE empno=7369);
	
	SELECT empno,ename,(SELECT mgr FROM emp WHERE empno=7369) AS 经理编号 
	,(SELECT ename FROM emp WHERE empno =(SELECT mgr FROM emp WHERE empno=7369)) AS 经理姓名 FROM emp WHERE empno=7369;
	
	SELECT e1.empno,e1.ename,e1.mgr,mgrtable.ename FROM emp e1,emp mgrtable WHERE e1.mgr = mgrtable.empno AND e1.empno=7369; 

5.查询出各个部门薪水最高的员工所有信息

SELECT * FROM emp  WHERE sal=MAX(sal) GROUP BY deptno;
	
	SELECT * FROM emp e WHERE sal = (SELECT MAX(sal) AS 最高薪水 FROM emp e2 WHERE e.deptno=e2.deptno GROUP BY deptno );
	
	-- SELECT * FROM emp e1 WHERE e1.sal = (SELECT MAX(sal) FROM emp e2 WHERE e1.deptno = e2.deptno GROUP BY deptno);

作业：
面试题
CREATE TABLE test(
NAME CHAR(20),
kecheng CHAR(20),
fenshu CHAR(20)
);

INSERT INTO test VALUES
('张三','语文',81),
('张三','数学',75),
('李四','语文',76),
('李四','数学',90),
('王五','语文',81),
('王五','数学',82);
 请用一条Sql语句查处分数大于80的学生