mysql2307笔记

mysql官网下载地址：https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.33-winx64.zip

向日葵远程：https://sunlogin.oray.com/

mysql -uroot -proot
-u username
-p password
默认的用户名和密码都是root

一、SQL

1、SQL（Structured Query Language）

结构化查询语言，用于访问和处理数据库的标准的计算机语言

2、语法特点

（1）SQL对关键字的大小写不敏感（windows）
（2）SQL语句可以单行或者多行书写，每行以分号结束
（3）SQL注释

#单行注释1，#后面没有空格
-- 单行注释2，--后面有空格

/*
多行注释
多行注释
*/

3、数据库系统

DataBase Mangement System

（1）关系型数据库（RDBMS）

①Oracle
②MySQL
③SQL Server
④PostgreSQL
⑤SQLite

（2）非关系数据库（NoSQL）

①Redis
②MongoDB
③Elasticsearch
④Cassandra
⑤Hbase

4、MySQL数据库基本操作-DDL

DDL(Data Definition Language) 数据定义语言，包括：对数据库的常用操作，对表结构的常用操作、修改表结构

（1）对数据库的常用操作

功能	SQL
查看所有的数据库	show databases;
创建数据库	create database [if not exists] mydb1 [charset=utf8]
切换（选择要操作的数据库）	use mydb1;
查看当前所在库	select database();
删除数据库	drop database [if exists] mydb1;
修改数据库编码	alter database mydb1 character set utf8

（2）对数据库的常用操作-创建表

创建表格式：

create table [if not exists] 表名(
	字段名1 类型[(宽度)] [约束条件] [comment '字段说明'],
	字段名2 类型[(宽度)] [约束条件] [comment '字段说明'],
	字段名3 类型[(宽度)] [约束条件] [comment '字段说明']
) [表的一些设置];

注：
（1）常见的数据类型有：①数值类型；②日期和时间类型；③字符串类型
①数值类型

类型	大小	用涂
TINYINT	1byte	小整数值
INT或INTEGER	4byte	大整数值
DOUBLE	8byte	浮点数值

②字符串类型

类型	大小	用途
CHAR	0-255byte	定长字符串
VARCHAR	0-65535byte	变长字符串

③日期类型

类型	大小(byte)	格式	用途
DATE	3byte	YYYY-MM-DD	日期值
DATETIMESTAMP	8byte	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMSTAMP	4byte	YYYYMMDD HHMMSS	混合日期和时间值，时间戳
（2）如果是字符串类型，会有宽度的限制，宽度填多少就代表字符串有多长；如果是数值类型(比如INT(1))，1不代表数据的长度，如果插入了大于显示宽度的值，只要该值不超过该类型的取值范围，数值依然显示出来

create table emp(
	eid int(1) comment '员工编号',
	ename varchar(1) comment '员工姓名'
);

insert into emp values
(-2147483649,'A'),
(21333,'B');

（3）对表结构的常用操作

功能	SQL
查看当前数据库的所有表名称	show tables;
查看指定某个表的创建语句	show create table 表名;
查看表结构	desc 表名;
删除表	drop table 表名;

（4）对表结构的常用操作-其他操作

①修改表添加列

语法格式：

alter table 表名 add 列名 类型(长度) [约束];

②修改列名和类型

语法格式：
alter table 表名 change 旧列名 新列名 类型(长度) [约束];

③修改表删除列

语法格式：

alter table 表名 drop 列名;

④修改表名

语法格式：

rename table 表名 to 新表名;

-- 查询emp所有的数据
SELECT * FROM emp;

SHOW DATABASES;

create database if not exists
mydb1;

drop database if exists mysql07;

drop database if exists mydb1;

create database if not exists mysql08;

create table if not exists class
(id int comment '学号',
c_name varchar(20) comment '课程名称');

#创建mydb1数据库
create database if not exists mydb1;

#创建student表
create table if not exists student(
	sid int comment '学号',
	name varchar(20) comment '姓名',
	gender varchar(20) comment '性别',
	age int comment '年龄',
	birth date comment '出生日期',
	address varchar(20) comment '家庭地址'
);

#查看所有的表
show tables;

#查看student表创建语句
show create table student;

#查看student表结构
desc student;

#删除student表
drop table student;

#添加字段dept
alter table student add `dept` varchar(20);

#修改dept字段为department，并设置varchar(30)
alter table student 
change `dept` department varchar(30);

#删除department字段
alter table student drop department;

#将student表名改为stu
rename table student to stu;

#添加字段score DOUBLE
alter table stu add `score` double;

#将stu表名改为student
rename table stu to student;

5、MySQL数据库基本操作-DML

DML是指数据操作语言，Data Manipulation Language，用来对数据库表中数据记录进行更新、删除、插入等操作
关键字：insert（插入）、delete（删除）、update（更新）

（1）数据插入

语法格式：
#向表中指定某些列插入数据
insert into 表名(列名1,列名2,列名3...) values (值1,值2,值3...);

#向表中所有列插入数据
insert into 表名 values (值1,值2,值3...);

#向student表中sid name gender age birth字段添加数据
insert into student (sid,name,gender,age,birth)
values (1001,'张三','男',20,'1994-12-01');

#向student表中所有字段添加数据
insert into student
values (1002,'赵敏','女',18,'1995-12-03','上海',88.0);

注：两种格式均可，SQL语句以分号结尾，可以在一条insert语句中同时插入多个列表值，values只需要写一次，括号之间用逗号隔开

#向stduent表中所有字段添加多个数据
insert into student
values 
(1003,'张三丰11','男',88,'1918-08-13','北京',90.0),
(1004,'张无忌111111111111111111','男',36,'1983-07-11','北京',83.0);

（2）数据修改

语法格式：

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

#将所有学生的地址修改为重庆
update student set address = '重庆';

#将sid为1004的学生的地址修改为北京
update student set address = '北京' where sid = 1004;

insert into student values
(1005,'沙师弟','男',33,'1986-03-18','上海',82.5);

#将sid为1005学生的地址修改为北京，成绩修改为100
update student set address = '北京',score = 100 where sid = 1005;

（3）数据删除

语法格式：
delete from 表名 [where 条件];

truncate table 表名 
或者 
truncate表名

#删除sid为1004的学生数据
delete from student where sid = 1004;

#删除表所有数据
delete from student;

insert into student
values 
(1003,'张三丰11','男',88,'1918-08-13','北京',90.0),
(1004,'张无忌1111111111111111','男',36,'1983-07-11','北京',83.0);
#清空表数据
truncate table student;
或者
truncate student;

注：delete和truncate原理不同，delete只删除内容，而truncate类似于drop table，可以理解为将整个表删除，然后再创建该表

6、Mysql约束

何为约束，constraint，约束实际上就是表中数据的限制条件
作用：为了保证表中的记录完整性和有效性，比如用户表的有些列（手机号）的值不能为空，有些列（身份证号）值不能重复

分类：
（1）主键约束（primary key） PK
（2）自增长约束（auto_increment）
（3）非空约束（not null）
（4）唯一性约束（unique）
（5）默认约束（default）
（6）零填充约束（zerofill）
（7）外键约束（foreign key） FK

（1）主键约束

可以加到某个列上，也可以加到多个列上

①概念

a. 主键约束相当于唯一约束 + 非空约束和组合，即主键约束列不允许重复，也不允许出现空值
b.每个表最多允许一个主键

②添加单列主键：

a.在定义字段的同时指定主键

语法格式：
create table 表名(
	...
	<字段名><数据类型> primary key
	...
)

b.在定义完字段之后指定主键

语法格式：
create table 表名(
	...
	[constraint <约束名>] primary key [字段名]
);

③添加多列主键（联合主键）

联合主键：一张表中多个字段组成的
联合主键也是一个主键

语法格式：
create table 表名(
	...
	primary key (字段1,字段2,...,字段n)
);

实现：
create table emp3(
	name varchar(20) comment '姓名',
	dept_id int comment '部门编号',
	salary double comment '薪资',
	primary key (name,dept_id)
);

delete from emp3;

insert into emp3 values
('111',10,5000),
('112',10,6000),
('113',10,7000),
('111',20,8000),
('112',30,9000);

多个键作为主键时其中的值不能完全相同，允许其中几个相同，但是不能出现NULL即空值

④通过修改表结构添加主键

主键约束不仅可以在创建表的同时创建，也可以在修改表时添加

语法格式：
create table 表名(
	...
);
alter table <表名> add primary key(字段列表);

实现：
create table emp4(
	eid int,
	name varchar(20),
	dept_id int,
	salary double
);

#给emp4表添加主键
alter table emp4 add primary key (eid);

⑤删除主键

格式：
alter table <数据表名> drop primary key;

实现：
#删除emp1表中主键
alter table emp1 drop primary key;

#删除emp3表中的联合主键
alter table emp3 drop primary key;

#主键实现
create table emp1 (
	eid int primary key comment '编号',
	name varchar(20) comment '姓名',
	dept_id int comment '部门编号', 
	slary double
)

insert into emp1 values 
(1001,'张三','5003',3000.0);

insert into emp1 values
(1002,'张三丰','4002',5000.0);

create table emp2(
	eid INT comment '编号',
	name varchar(20) comment '姓名',
	deptId INT comment '部门编号',
	salary double comment '薪资',
	constraint pk1 primary key(eid)
);

insert into emp2 values
(1001,'张安',10,6000);

insert into emp2 values
(1002,'杰森',40,6000);

（2）自增长约束

自增长约束，当字段设置为自增长约束后，在插入数据时，不需要用户输入数据，而由数据库系统根据定义自动赋值，每增加一条记录，该字段会以相同的步长进行增长，一般是放在主键上

语法格式：
字段名 数据类型 auto_increment

#创建madb1数据库
create database if not exists madb1 charset = utf8;

create table t_user1(
	id int primary key auto_increment,
	name varchar(20)
);

insert into t_user1 values
(NULL,'张三');

insert into t_user1 (name) values
('李四');

①特点

a. auto_increment的初始值是1，每新增一条记录，字段值自动加1
b. 一个表中只能有一个字段使用auto_incremen
c. auto_increment约束的字段必须具备NOT NULL属性
d. 支持的类型（TINYINT、SMALLINT、INT、BIGINT）等
e. 如果最大值达到上限，auto_increment就会失效

②指定自增字段的初始值

方式1，创建表时指定
语法格式：
create table 表名(
	... primary key auto_increment,
	...
) auto_increment=10;

方式2，创建表之后指定
语法格式：
create table 表名(
	... primary key auto_increment,
	...
);
alter table 表名 auto_increment = 值;

#指定自增字段的初始值-方式1
create table t_user2(
	id int primary key auto_increment,
	name varchar(20)
) auto_increment=100;

insert into t_user2 VALUES
(NULL,'aaa'),
(NULL,'aab');

#指定自增字段的初始值-方式2
create table t_user3(
	id int primary key auto_increment,
	name varchar(20)
);

alter table t_user3 auto_increment = 2147483646;

insert into t_user3 values 
(NULL,'aac'),
(NULL,'aad');

insert into t_user3 values
(NULL,'aae');

③delete和truncate区别

delete数据之后自动增长从断点开始
truncate数据之后自动增长从默认起始值开始

#auto_increment delete和truncate区别
#delete操作
truncate t_user3;

insert into t_user3 values 
(NULL,'aac'),
(NULL,'aad');

delete from t_user3;

insert into t_user3 values 
(NULL,'aae'),
(NULL,'aaf'),
(NULL,'aag');

#truncate操作
truncate t_user3;

insert into t_user3 values 
(NULL,'aac'),
(NULL,'aad');

truncate t_user3;

insert into t_user3 values 
(NULL,'aae'),
(NULL,'aaf'),
(NULL,'aag');

（3）非空约束（not null）

①添加非空约束

语法格式：
方式1：<字段名> <数据类型> not null
方式2：alter table 表名 modify 字段 类型 not null;

#添加非空约束-方式1
create table t_user6(
	id int,
	name varchar(20) not null,
	address varchar(20) not null
)

#添加非空约束-方式2
create table t_user7 (
	id int,
	name varchar(20),
	address varchar(20)
);

alter table t_user7 modify name varchar(20) not null;
alter table t_user7 modify address varchar(20) not null;

insert into t_user6(id) values(1001); #不可以
insert into t_user6(id,name,address) values(1001,NULL,NULL); #不可以
insert into t_user6(id,name,address) values(1001,'NULL','NULL');#可以（字符串：NULL）
insert into t_user6(id,name,address) values(1001,'','');#可以（空串）

单纯的NULL表示空值，加上引号会变成字符串

②删除非空约束

语法格式：
alter table 表名 modify 字段名 类型;

#删除之前无法添加
insert into t_user7 (id) VALUES
(10);

#删除t_user7表上的非空约束
alter table t_user7 modify name varchar(20);
alter table t_user7 modify address varchar(20);

#删除之后可以添加
insert into t_user7 (id) values (11);

（4）唯一性约束

唯一约束（Unique Key）是指所有记录中字段的值不能重复，添加唯一约束的列可以为NULL

①添加唯一性约束

语法：
方式1：<字段名> <数据类型> unique
方式2：alter table 表名 add constraint 约束名 unique(列);

#创建表时指定唯一性约束
create table t_user8(
	id int,
	name varchar(20),
	phone_number varchar(20) unique
);

#测试唯一性约束
insert into t_user8 values
(10,'aaa','1311112222');
insert into t_user8 values 
(11,'aab','1311112222');

#NULL和任何值都不相同，NULL!=NULL
insert into t_user8 VALUES
(12,'aac',NULL);

insert into t_user8 VALUES
(13,'aad',NULL);

insert into t_user8 (id,name) VALUES
(14,'aba');

②删除唯一性约束

语法格式：
alter table <表名> drop index <唯一约束名>
alter table <表名> drop index <列名>

create table t_user9(
	id int,
	name varchar(20),
	phone_number varchar(20)
);

#添加唯一性约束-方式2
alter table t_user9 add constraint upn unique(phone_number);

#测试唯一性约束
insert into t_user9 values
(20,'ccc','13211115555'),
(21,'cca','13211115555');

#删除唯一性约束
#如果有约束名，不能用列名来代替约束名
alter table t_user9 drop index phone_number;

alter table t_user9 drop index upn;

#再次测试
insert into t_user9 values
(20,'ccc','13211115555'),
(21,'cca','13211115555');

（5）默认约束

默认值约束用来指定某列的默认值

①添加默认约束

语法格式：
方式1：<字段名> <数据类型> default <默认值>;
方式2：alter table 表名 modify 列名 类型 default 默认值

#添加默认约束-方式1
create table t_user10(
	id int,
	name varchar(20),
	address varchar(20) default '北京'
)

insert into t_user10 (id,name)
values (10,'aac'),(11,'ace');

②删除默认约束

语法格式：
alter table <表名> modify column 字段名 类型 default null;

#删除默认约束
alter table t_user10 modify COLUMN
address varchar(20) default null;

insert into t_user10 (id,name)
values (13,'aat');

（6）零填充约束

zero fill：
①插入数据时，当该字段的值长度小于定义的长度时，会在该值的前面补上相应的0
②zerfill默认为int(10)

①添加零填充约束

语法格式：
create table 表名(
	字段名1 类型 zero fill,
	...
)

②删除零填充约束

语法格式：

alter table 表名 modify 字段名 类型;

create table t_user11(
	id INT zerofill,
	name varchar(20)
);

insert into t_user11 VALUES
(31,'aac'),(32,'ddd');

select * from t_user11;

#删除零填充约束
alter table t_user11 modify id int;

7、MySQL基本操作-DQL

DQL：Data Query Language，数据查询语言

语法格式：

select 
	[all|distinct]
	<目标列的表达式1> [别名1],
	<目标列的表达式2> [别名2]...
from <表名或视图名> [别名],<表名或视图名> [别名]...
[where<条件表达式>]
[group by <列名>]
[having <条件表达式>]
[order by <列名> [asc|desc]]
[limit <数字或者列表>];	

简化版语法

select *|列名 from 表名 where 条件;

create database if not exists mydb2;

use mydb2;

#商品表
create table product (
	pid int PRIMARY KEY auto_increment comment '编号',
	pname varchar(20) comment '名称',
	price double comment '价格',
	category_id varchar(20) comment '分类编号'
);

insert into product values
(null,'海尔洗衣机',5000,'c001'),
(null,'美的空调',2000,'c001'),
(null,'格力空调',5000,'c001'),
(null,'九阳电饭煲',5000,'c001'),
(null,'啄木鸟衬衣',300,'c002'),
(null,'恒源祥西裤',800,'c002'),
(null,'花花公子夹克',440,'c002'),
(null,'海澜之家卫衣',180,'c002'),
(null,'杰克琼斯运动裤',430,'c002'),
(null,'兰蔻面霜',300,'c003'),
(null,'雅诗兰黛精华水',200,'c003'),
(null,'香奈儿香水',350,'c003'),
(null,'SK-II神仙水',350,'c003'),
(null,'资生堂粉底液',180,'c003'),
(null,'老北京方便面',18,'c004'),
(null,'良品铺子海带',17,'c004'),
(null,'三只松鼠坚果',88,null);

（1）运算符

①算术运算符

运算符	说明
+	加法
-	减法
*	乘法
/ 或 DIV	除法
%或MOD	取模，求余数

select 5+2;
select 5-2;
select 5*2;
select 5/2;
#和java里面一样，没有四舍五入
select 5 DIV 2;

#%，求余数
#大取小
select 5%2;
select 5 MOD 2;

#小取大，直接得到最小的那个数
select 2%5;
select 2 MOD 5;

#将每件商品的价格加10
#as关键字后面是别名,as可省略掉
select pname,price,price+10 as new_price from product;

#将所有商品的价格上调10%
select pname,price*1.1 as new_price from product;
select pname,price+price*0.1 as new_price from product;

②比较运算符

运算符	说明
=	等于
<和<=	小于和小于等于
>和>=	大于和大于等于
<=>	安全的等于
<>或!=	不等于
IS NULL	判断一个值是否为NULL
IS NOT NULL	判断一个值是否不为NULL
LEAST	当有两个或多个参数时，返回最小值
GREATEST	当有两个或多个参数时，返回最大值
BETWEEN AND	判断一个值是否介于两个值之间
IN	判断一个值是否在IN列表中的任意一个值
NOT IN	判断一个值是否不是IN列表中的任意一个值
LIKE	通配符匹配，模糊查询

#使用least求最小值
select least(10,20); #10
select least(10,20,30); #10
select least(10,null,30); #null

#使用greatest求最大值
select greatest(10,20,30); #30
select greatest(10,null,30) #null

#1、查询商品名称为'海尔洗衣机'的商品所有信息
# *代表所有字段
select * from product where pname = '海尔洗衣机';

#2、查询价格为800的商品
select * from product where price = 800;

#3、查询价格不是800的所有商品
select * from product where price != 800;
select * from product where price <> 800;
select * from product where price not in (800);
select * from product where not (price = 800);

#4、查询商品价格大于60元的所有商品信息
select * from product where price >= 60;

#5、查询商品价格在200到1000之间的所有商品
#betwee 200 and 1000
select * from product where price between 200 and 1000;
select * from product where price >=200 and price <=1000;

#6、查询商品价格是200或800的所有商品
select * from product where price = 200 || price = 800;
select * from product where price = 200 or price = 800;
select * from product where price in (200,800);

#7、查询含有"裤"字的所有商品
#	LIKE 模糊查询
#	%用来匹配多个任意字符
# _用来匹配单个任意字符
select * from product where pname like '裤';

#8、查询以"海"字开头的所有商品
select * from product where pname like "海%";

#9、查询第二个字为"蔻"字的所有商品
select * from product where pname like '_蔻%';

#10、查询category_id为null的商品
#如果是null的匹配，不能用=，需要用is
select * from product where category_id = null;
select * from product where category_id = 'null'; 
select * from product where category_id is null;

#11、查询category_id不为null的商品
select * from product where category_id is not null;

（2）排序查询

语法格式：
select 
	字段名1,字段名2,.....
from 表名
order by 字段名1 [asc|desc], 字段名2[asc|desc]...

特点：
①asc代表升序，desc代表降序，如果不写默认是升序
②order by用于字句中可以支持单个字段，多个字段名，表达式，函数，别名
③order by字句，放在查询语句的最后面，LIMIT子句除外

#1、使用价格排序（降序）
select * from product order by price desc;

#2、在价格排序（降序）的基础上，以分类编号排序（降序）
select * from product order by price desc,category_id desc;

#3、显示商品的价格（去重复，关键字distinct），并按价格排序（降序）
select distinct price from product order by price desc

（3）聚合查询

之前所做的查询都是横向查询，而聚合函数查询时纵向查询，另外聚合函数会忽略空值

聚合函数	作用
count()	统计行数
sum()	统计列的和
max()	求列的最大值
min()	求列的最小值
avg()	求列的平均值

#尽量使用count(*)，原因：当使用某列时，某列包含空值，会不进行计算
select count(*) from product;
select count(category_id) from product;

select sum(price) from product;

select max(price) from product;

select min(price) from product;

select avg(price) from product;

（4）分组查询

指使用group by子句对查询信息进行分组

语法格式：
select 字段1,字段2,... from 表名 group by 分组字段 having 分组条件;

#1、统计各个分类商品的个数
select category_id,count(*) from product group by category_id;

select category_id,pname,count(*) from product group by category_id;

select category_id,count(*),price from product group by category_id;

注：如果要进行分组的化，则在SELECT子句之后，只能出现分组的字段和统计函数，其他的字段不要出现

对分组的结果进行筛选要使用having，不能使用where

#2、统计各个分类商品的个数，且只显示个数大于4的信息
select category_id,count(*) from product 
group by category_id having count(*) > 4;

use mydb2;

create table stu(
	id int primary key auto_increment,
	name varchar(20),
	gender varchar(20),
	chinese int,
	english int,
	math int
);

insert into stu values
(null,'张明','男',89,78,90),
(null,'李进','男',67,53,95),
(null,'王五','女',87,78,77),
(null,'李一','女',88,98,92),
(null,'李财','男',82,84,67),
(null,'张宝','男',55,85,45),
(null,'黄蓉','女',75,65,30),
(null,'郭襄','女',85,72,91);

作业：

#1、查询表中所有学生的信息
select * from stu;

#2、查询表中所有学生的姓名和对应的英语成绩
select name,english from stu;

#3、过滤表中的重复数据
select distinct * from stu;

#4、统计每个学生的总分
select chinese+english+math sumScore,name from stu;

#5、在所有学生总分数上加10分特长分
select chinese+english+math+10 sumScore,name from stu;

#6、使用别名表示学生分数
select name,chinese as '语文成绩',math as '数学成绩',
english as '英语成绩' from stu;

#7、查询英语成绩大于90分的同学
select * from stu where english > 90;

#8、查询总分大于200分的所有同学
select * from stu where chinese+math+english > 200;

#9、查询英语分数在80-90之间的同学
select * from stu where chinese between 80 and 90;
select * from stu where chinese >= 80 and chinese <= 90;
select * from stu where chinese >= 80 && chinese <= 90;

#10、查询英语分数不在80-90之间的同学
select * from stu where chinese not between 80 and 90;
select * from stu where chinese <=80 OR chinese >=90;
select * from stu where chinese <=80 || chinese >=90;

#11、查询数学分数为89，90，91的同学
select * from stu where math in (89,90,91);
select * from stu where math=89 OR math=90 OR math=91;
select * from stu where math >= 89 AND math <= 91;

#12、查询所有姓李的学生英语成绩
select * from stu where name like '李_';

#13、查询数学分数为80并且语文分数为80的同学
select * from stu where math = 80 AND chinese = 80;

#14、对数学成绩降序排序后输出
select * from stu order by math desc;

#15、对总分排序后输出，然后再按从高到低的顺序输出
select *,chinese+english+math sumScore from stu 
order by chinese+english+math desc;

#16、对姓李的学生总成绩排序输出
select *,chinese+math+english sumScore from stu where name like '李%' 
order by chinese+english+math desc;

#17、查询男生和女生分别有多少人，并将人数降序排序输出，查询出人数大于4的性别人数信息
select gender,count(*) from stu group by gender 
having count(*) > 4 order by count(*) desc;

（5）分页查询-limit

语法格式：
#方式1：显示前n条
select 字段1,字段2... from 表名 limit n;

#方式2：分页显示
select 字段1,字段2... from 表名 limit m,n;
m：整数，（当前页码值-1）* 每页显示条数
n：整数，每页显示条数

#方式1：显示前n条
select 字段1,字段2... from 表名 limit n;

#方式2：分页显示
select 字段1,字段2... from 表名 limit m,n;
m：整数，（当前页码值-1）* 每页显示条数，表示从第几条索引开始
n：整数，每页显示条数

#查询product表的前5条记录
select * from product limit 5;

#查询product表第3页数据，每页显示5条
select * from product limit 10,5;

#查询product表第1页数据，每页显示17条
select * from product limit 0,17;
select * from product limit 17;

#查询product表，从第八条开始，每页显示5条
select * from product limit 7,5;

（6）INSERT INTO SELECT语句

将一张表的数据导入到另一张表中

语法格式；
insert into 表名2(字段名1,字段2,...) select value1,value2,... from 表名1;
或
insert into 表名2 select * from 表名1;

create table product2(
	pname varchar(20),
	price DOUBLE
);

insert into product2(pname,price)
select pname,price from product;

8、MySQL多表操作

多表关系：一对一，一对多/多对一，多对多

（1）一对一关系

一个学生只有一张身份证；一张身份证只能对应一个学生
在任意表中添加唯一外键，指向另一方的主键，确保一对一关系
一般一对一关系很少见，遇到一对一的关系的表最好是合并表

（2）一对多/多对一关系

部门和员工
一个部门有多个员工，一个员工只能对应一个部门
实现原则：在多的一方建立外键，指向一的一方建立主键

（3）多对多关系

学生和课程
一个学生可以选择多门课程，一个课程也可以被多个学生选择
多对多关系需要借助第三张中间表，中间表至少要包含两个字段，将多对多的关系，拆成一对多的关系，中间表至少要有两个外键，这两个外键分别指向原来的那两张表的主键

（4）外键约束

foreign key

①外键约束：一对多/多对一

语法格式：
方式1：创建表时添加
create table 表名(
	字段1 类型,
	...
	[constraint <外键名>] foreign key (字段名) references 主表名 (字段名)
);

方式2：创建表之后添加
alter table 从表名 add constraint 外键名 foreign key(字段名) references 主表名 (字段名)

create database if not exists mydb3 charset = utf8;

use mydb3;

create table if not exists dept(
 deptno varchar(20) primary key,
 name varchar(20)
);

create table if not exists emp(
 eid varchar(20) primary key,
 ename varchar(20),
 age int,
 dept_id varchar(20),
 #从表的外键指向主表的主键
 constraint emp_fk foreign key (dept_id) references dept (deptno)
);

#如果要删除或修改deptno，需要先删除或修改emp下面deptno对应的所有员工

#删除deptno=1001的部门
delete from dept
where deptno=1001;

#把deptno=1001修改为1003
update dept set deptno = 1003 where deptno = 1001;

#将emp表下面1001的员工全部修改为1004
update emp set dept_id = 1004 where dept_id = 1001;

#删除dept表里的1001部门
delete from dept
where deptno=1001;

注：
①必须要先给主表添加数据
②给从表添加数据时，外键列的值不能随便写，必须要依赖主表的主键列
③主表的数据被从表依赖，不能删除
④从表的数据可以随便删

②外键约束：多对多关系

#创建中间表student_score/score 从表
create table score(
	sid int,
	cid int,
	score double
);

#创建学生表
create table student(
	sid int primary key,
	name varchar(4),
	age int,
	gender varchar(1)
);

#创建课程表
create table course(
	cid int primary key,
	cname varchar(20)
);

#建立外键约束（2次）
alter table score add constraint score_sfk foreign key (sid) references student (sid);
alter table score add constraint score_cfk foreign key (cid) references course (cid);

#给学生表添加数据
insert into student values
(1,'小龙女',18,'女'),
(2,'阿紫',19,'女'),
(3,'周芷若',20,'男');

#给课程表添加数据
insert into course values
(1,'语文'),
(2,'数学'),
(3,'英语');

#给中间表添加数据
insert into score values
(1,1,75),
(1,2,83),
(2,1,67),
(2,3,80),
(3,2,90),
(3,3,80);

（5）多表操作之多表联合查询

create table if not exists dept3(
	deptno varchar(20) primary key,
	name varchar(20)
);

create table if not exists emp3(
	eid varchar(20) primary key,
	ename varchar(20),
	age int,
	dept_id varchar(20)
);

insert into dept3 values 
('1001','研发部'),
('1002','销售部'),
('1003','财务部'),
('1004','人事部');

insert into emp3 values
('1','乔峰',20,'1001'),
('2','段誉',21,'1001'),
('3','虚竹',23,'1001'),
('4','阿紫',18,'1001'),
('5','扫地僧',85,'1002'),
('6','李秋水',33,'1002'),
('7','鸠摩智',50,'1002'),
('8','天山童姥',60,'1003'),
('9','慕容博',58,'1003'),
('10','丁春秋',71,'1003');

多表查询就是同时查询两个或者两个以上的表

①交叉连接查询（笛卡尔积）

语法格式：
select * from 表1,表2,表3;

a. 返回被连接的两个表所有数据行
b. 可以理解为一张表的每一行去和另外一张表的任意一行进行匹配
c. 假设A表有m行数据，B表有n行数据，则返回m*n行数据

select * from dept3,emp3;

②内连接查询（inner join，inner可以省略）

求多张表的交集

隐式内连接：select * from 表名1,表名2 where 条件;
显式内连接：select * from A inner join B on 条件;

#查询每个部门的所属员工
select * from dept3,emp3
where dept3.deptno = emp3.dept_id;

select * from dept3 inner join emp3
on dept3.deptno = emp3.dept_id;

③外连接查询（outer join，outer可以省略）

外连接分为左外连接（left join），右外连接（right join），全外连接（full join）

格式：
	左外连接：
	select * from A left join B on 条件;
	右外连接：
	select * from A right join B on 条件;
	全外连接：
	select * from A full join B on 条件;

以左外连接为例，左边的数据会全部输出，右边的如果有对应的数据即相交的一部分数据会处处，剩下的没有对应的部分会补全null

#外连接查询
#查询哪些部门有员工，哪些部门没有员工
select * from dept3 left join emp3 
on dept3.deptno = emp3.dept_id;
#等价于
select * from emp3 right join dept3
on dept3.deptno = emp3.dept_id;

#查询哪些员工属于哪些部门
select * from emp3 left join dept3
on emp3.dept_id = dept3.deptno;
#等价于
select emp3.*,dept3.* from dept3 right join emp3
on emp3.dept_id = dept3.deptno;

#mysql不支持全外连接，使用左连接 union 右连接代替
select * from dept3 full join emp3
on emp3.dept_id = dept3.deptno;

select * from dept3 left join emp3 on dept3.deptno = emp3.dept_id
union 
select * from dept3 right join emp3 on dept3.deptno = emp3.dept_id;

注：oracle支持full join，mysql不支持，但可以用union去代替

④子查询

是指包含select嵌套的查询

#1、查询emp3最大年龄
select max(age) from emp3;

#2、找出和最大年龄相等的员工信息
select * from emp3
where age=(select max(age) from emp3);

#3、查询研发部20岁以下的员工信息，包括员工号、员工名字、部门名字
#第一种方式
select eid,ename,name
from 
(select deptno,name from dept3 where name = '研发部') as a,
(select eid,ename,dept_id from emp3 where age < 20) as b
where a.deptno = b.dept_id;

#第二种方式
select eid,ename,name
from 
(select deptno,name from dept3 where name = '研发部') as a
join
(select eid,ename,dept_id from emp3 where age < 20) as b
on a.deptno = b.dept_id;

#4、查询研发部和销售部的员工信息，包含员工号，员工名字
#子查询
select eid,ename from emp3
where dept_id in
(select deptno from dept3 where name = '研发部' or name = '销售部');

⑤表关联查询、自查询

语法格式：
select 字段列表 from 表1 as a,表1 as b where 关联条件;
或
select 字段列表 from 表1 as a join 表1 as b on 关联条件;

#查询出研发部下面的员工信息（eid,ename,age）以及部门名字
#第一种方式
select eid,ename,age,name from dept3,emp3 
where dept3.name='研发部' and dept3.deptno = emp3.dept_id;

#第二种方式
select eid,ename,age,name from emp3 join dept3
on dept3.name = '研发部' and emp3.dept_id = dept3.deptno;

create database test1 charset = utf8;

use test1;

create table dept(
deptno int PRIMARY KEY,
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');

CREATE TABLE emp(
    empno INT primary key,
    ename VARCHAR(10),
    job VARCHAR(9),
    mgr INT,
    hiredate DATE,
    sal double,
    comm double,
    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, '1982-12-09', 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, '1983-01-12', 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);

CREATE TABLE  salgrade
(
 grade INT PRIMARY KEY,
 losal INT,
 hisal INT);

INSERT INTO salgrade VALUES(1,700,1200);
INSERT INTO salgrade VALUES(2,1201,1400);
INSERT INTO salgrade VALUES(3,1401,2000);
INSERT INTO salgrade VALUES(4,2001,3000);
INSERT INTO salgrade VALUES(5,3001,9999);

#添加部门和员工之间的主外键关系
alter table emp add constraint foreign key
emp(deptno) references dept(deptno);

#1、查询拥有员工的部门名和部门号
select emp.deptno,dept.dname 
from 
emp join dept
on emp.deptno = dept.deptno;

#2、查询工资比SMITH高的员工信息
select * from emp
where sal > 
(select sal from emp
where ename = 'SMITH');

#3、查询员工编号和所属经理的姓名
select e1.empno,e2.ename as 所属经理姓名 
from emp e1,emp e2
where e1.mgr = e2.empno;

#4、查询雇员的雇佣日期早于其经理雇佣日期的员工编号、姓名和所属经理姓名'
select e1.empno,e1.ename,e2.ename as 所属经理姓名
from emp e1 join emp e2
on e1.mgr = e2.empno and e1.hiredate < e2.hiredate;