数据库学习总结_too big scale 50 specified for column sampletime m

数据库

第一节 MySQL介绍和安装

mysql安装简单总结

#1、下载：MySQL Community Server 5.7.16
http://dev.mysql.com/downloads/mysql/

#2、解压
如果想要让MySQL安装在指定目录，那么久将解压后的文件夹移动到指定目录，如：C:\mysql-5.7.16-winx64

#3、添加环境变量
【右键计算机】--》【属性】--》【高级系统设置】--》【高级】--》【环境变量】--》【在第二个内容框中找到变量名为Path 的一行，双击】--》【将MySQL的bin目录路径追加到变值中，用; 分割】

#4、初始化
mysqld --initialize-insecure

#5、启动MySQL服务
mysqld # 启动MySQL服务

#6、启动MySQL客户端并连接MySQL服务
mysql -u root -p # 连接MySQL服务器

#7、将mysql添加系统服务
	注意：--install前，必须用mysql启动命令的绝对路径
	# 制作MySQL的Windows服务，在终端执行此命令：
	"c:\mysql-5.7.16-winx64\bin\mysqld" --remove
	
	注册成服务之后，以后再启动和关闭MySQL服务时，仅需执行如下命令：
	# 启动MySQL服务
	net start mysql
	
	# 关闭MySQL服务
	net stop mysql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

如果忘记密码怎么办？

• 1.找到my.ini 文件

    **my.ini 文件为 MySQl 设置文件， 如果你是默认的安装地址，文件在 			                C:\ProgramData\MySQL\MySQL Server 5.7 下**
  
    **但是ProgramData 常规状态下是隐藏的 **
  1
  2
  3

• **2.设置权限认证跳过 也就是在 [mysqld] 下 加上 skip-grant-tables **

[mysqld]
skip-grant-tables
1
2

• 3.重启 mysql 服务
  这里可以直接在命令行中连续输入 或者在服务里找到mysql 服务重启

  net stop mysql
  net start mysql
  1
  2

  **重启后， 以 mysql -uroot -p 登陆 会发现我们可以不需要密码就可以登陆 ****

  • 4.重新设置密码

  首先先选择 mysql 数据库

  use mysql
  1

  • 然后更新 password

    update user set authentication_string = password ( 'new-password' ) where user = 'root' ; 
    1

• 5.在 my.ini 文件中去掉 加上的 skip-grant-tables

• 6.重启 mysql 服务

• 7.以新密码登陆命令行进入 mysql 环境

mysql -uroot -p
1

修改字符集编码

• 在mysql的安装目录下面创建一个 my.ini 配置文件（unxi系统是 my.cnf 文件），文件中写上以下内容：

[mysql]
character_set_server=utf8
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
1
2
3
4
5
6

第二节 MySQL库表详细操作

1.创建数据库

• 语法

  create database 数据库名 charset utf8;
  1

  • 数据库命名规则
    • 可以由字母、数字、下划线、@、#、$
    • 区分大小写
    • 唯一性
    • 不能使用关键字，如 create select
    • 不能单独使用数字
    • 最长128位

2.数据库相关操作

1.查看数据库

show databases;
show create database db1;
select database();
1
2
3

2.选择数据库

use 数据库名
1

3.删除数据库

drop database 数据库名；
1

4.修改数据库

alter database db1 charset utf8;
1

3.数据表相关操作

引擎简单使用

1.创建表时指定引擎

create table innodb_t2(id int)engine=innodb;
1

2.在配置文件中指定默认的存储引擎

linux:vim /etc/my.cnf   windows:my.ini文件
[mysqld]
default-storage-engine=INNODB #配置默认引擎，现在用的mysql默认基本都是InnoDB,所以其实都可以不用配置了
innodb_file_per_table=1 #表示独立表空间存储，可以不写
1
2
3
4

3.不同引擎在创建表的时候生成文件不同

创建四个表，分别使用innodb, myisam, memory, blackhole存储引擎， 进行插入数据测试

create table t1(id int)engine=innodb;
create table t2(id int)engine=myisam;
create table t3(id int)engine=memory;
create table t4(id int)engine=blackhole;
1
2
3
4

建表语法

#语法
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);

#注意：
1.在同一张表中，字段名是不能相同
2.宽度和约束条件可选、非必选，宽度指的就是字段长度约束，例如：char(10)里面的10
3.字段名和类型是必须的
1
2
3
4
5
6
7
8
9
10
11

示例代码

create database db1 charset utf8;

use db1;

create table t1(
id int,
name varchar(50),
sex enum('male','female'),
age int(3)
);

show tables; #查看db1库下所有表名

desc t1; #查看表结构

select id,name,sex,age from t1;

select * from t1;

select id,name from t1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

插入数据

insert into t1 values
(1,'zhao',18,'male'),
(2,'sb',81,'female');

insert into t1(id) values
(3),
(4);

select * from t1;
1
2
3
4
5
6
7
8
9

查看表结构

describe t1; #查看表结构，可简写为：desc 表名

show create table t1\G; #查看表详细结构，可加\G
1
2
3

MySQL的基本数据类型

• **数值类型 **

1、整数类型

	整数类型：tinyint,samllint,mediumint,int,bigint
	
	作用：存储年龄，等级，id，各种号码等
1
2
3
4
5

1.float[(m,d)][unsigned][zerofill]
	
	定义：
		单精度浮点数（非精准小数值），m是整数部分+小数部分的总个数，d是小数点后个数。m最大值为255，d最大值为30，例如：float(255,30)
		
	有符号：
		 -3.402823466E+38 to -1.175494351E-38,
		 1.175494351E-38 to 3.402823466E+38
	无符号：
		 1.175494351E-38 to 3.402823466E+38
	精确度：
		 **** 随着小数的增多，精度变得不准确 ****
	
    
2.double[(m,d)][unsigned][zerofill]

	定义：
		双精度浮点数（非准确小数值），m是整数部分+小数部分的总个数，d是小数点后个数。m最大值也为255，d最大值也为30
	
	有符号：
		 -1.7976931348623157E+308 to -2.2250738585072014E-308
		 2.2250738585072014E-308 to 1.7976931348623157E+308
	无符号：
		 2.2250738585072014E-308 to 1.7976931348623157E+308
	精准度：
		 **** 随着小数的增多，精度比float要高，但也会变得不准确 ****
		 

3.decimal[(m[,d])][unsigned][zerofill]

	定义：
		准确的小数值，m是整数部分+小数部分的总个数（负号不算），d是小数点后个数。m最大值为65，d最大值为30.比float和double的整数个数少，但是小数位数都是30位
		
	精确度：
		**** 随着小数的增多，精度始终准确 ****
		对于精确数值计算时需要用此类型
		decimal能够存储精确值的原因在于其内部按照字符串存储。
		

精度从高到低：decimal、double、float
	decimal精度高，但是整数位数少
	float和double精度低，但是整数位数多
	
float已经满足绝大多数的场景了，但是什么导弹、航线等要求惊动护非常高，所以还是需要按照业务场景自行选择，如果又要精度高又要整数位数多，那么你可以直接用字符串来存。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

浮点型测试

#float
create table t1(x float(256,31)); #报错
# ERROR 1425 (42000): Too big scale 31 specified for column 'x'. Maximum is 30. 

create table t1(x float(256,30)); #报错
# ERROR 1439 (42000): Display width out of range for column 'x' (max = 255) 

create table t1(x float(255,30)); #建表成功


#double
create table t2(x double(255,30)); #建表成功


#decimal
create table t3(x decimal(66,31)); #报错
# ERROR 1425 (42000): Too big scale 31 specified for column 'x'. Maximum is 30. 

create table t3(x decimal(66,30)); #报错
# ERROR 1426 (42000): Too-big precision 66 specified for 'x'. Maximum is 65. 

create table t3(x decimal(65,30)); #建表成功


show tables;

insert into t1 values(1.1111111111111111111111111111111); #小数点后31个1

insert into t2 values(1.1111111111111111111111111111111);

insert into t3 values(1.1111111111111111111111111111111);

select * from t1; #随着小数的增多，精度开始不准确

select * from t2; #精度比float要准确点，但随着小数的增多，同样变得不准确

select * from t3; #精度始终准确，d为30，于是只留了30位小数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

日期类型

类型：date, time, datetime, timestamp, year

作用：存储用户注册时间，文章发布时间，员工入职时间，出生时间，过期时间等

year
	YYYY (范围：1901/2155) 例：2018
	
date
	YYYY-MM-DD (范围：1000-01-01/9999-12-31) 例：2018-01-01
	
time
	HH:MM:SS (范围：'-838:59:59'/'838:59:59') 例：12:09:32
	
datetime
	YYYY-MM-DD HH:MM:SS (范围：1000-01-01 00:00:00/9999-12-31 23:59:59    Y)
	例：2018-01-01 12:09:32
	
timestamp
	YYYYMMDD HHMMSS (范围：1970-01-01 00:00:00/2037 年某时)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

日期类型测试

year:
	create table t10(born_tear year); #无论year指定何种宽度，最后都默认是year(4)
	insert into t10 values
	(1900),
	(1901),
	(2155),
	(2156);
	
	select * from t10;
	
date,time,datetime:
	create table t11(d date,t time,dt datetime);
	desc t11;
	
	insert into t11 values(now(),now(),now());
	select * from t11;
	
timestamp:
	create table t12(time timestamp);
	insert into t12 values();
	insert into t12 values(null);
	select * from t12;
	
	
==============注意啦，注意啦，注意啦==============
	1.单独插入时间时，需要以字符串的形式，按照对应的格式插入
	2.插入年份时，尽量使用4位值
	3.插入两位年份时，<=69,以20开头，比如50，结果2050
				   >=70,以19开头，比如71，结果1971
				   
	create table t12(y year);
	insert into t12 values
	(50),
	(71);
	
	select * from t12;
	
==============综合练习==============

	create table student(
    id int,
    name varchar(20),
    born_year year,
    birth date,
    class_time time,
    reg_time datetime
    );
    
    insert into student values
    (1,'sb1',"1995","1995-11-11","11:11:11","2017-11-11 11:11:11"),
    (2,'sb2',"1997","1997-12-12","12:12:12","2017-12-12 12:12:12"),
    (3,'sb3',"1998","1998-01-01","13:13:13","2017-01-01 13:13:13");
    
    select * from student;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

字符串类型

类型：char, varchar

作用：名字，信息等等

char和varchar性能对比：

​ 以char(5)和varchar(5)来比较，假入我要存三个人名：sb, ssb1, ssbb2

​ char:（存储块，浪费空间，安全性低）

​ 优点：简单粗暴，不管你是多长的数据，我就按照规定的长度来存，5个5个的存，三个人名就会 类似这种存储：sb ssb1 ssbb2,中间是空格补全，取数据的时候5个5个的取，简单粗暴速度快

​ 缺点：貌似浪费空间，并且我们将来存储的数据的长度可能会参差不齐

​ varchar:（存储慢，节省空间，安全性高）

​ varchar类型不定长存储数据，更为精简和节省空间

​ 例如存上面三个人名的时候类似于是这样的：sbssb1ssb2, 连着的

​ 优点：节省了一些硬盘空间，一个accsii吗的字符用一个bytes长度就能表示，但是也并不一定比char省，看一下官网给出的一个表格对比数据，当你存的数据正好是你规定的长度的时候，varchar反而占用的空间比char要多。

缺点：存取速度都慢

其他的字符串类型：binary、varbinary、blob、text

枚举和集合

字段的值只能在给定范围中选择，如单选框，多选框，如果你在英语程序或者前端不做选项限制，在MySQL的字段里面也能做 限制

enum单选 只能在给定的范围内选一个值，如性别sex 男 male/女 female

set多选 在给定的范围内可以选择一个或一个以上的值（爱好1，爱好2，爱好3…）

	枚举类型（enum）
		ENUM列最多可以有65535个不同的元素。(实际限制小于3000)
		示例：
			create table shirts(
             name varchar(40),
             size enum('x-small','small','medium','large','x-large')
            );
            insert into shirts(name,size) values
            ('dress shirt','large'),
            ('t-shirt','medium'),
            ('polo shirt','small');
            
     集合类型（set）
     	一个SET列最多可以有64个不同的成员。
     	示例：
     		create table myset(col set('a','b','c','d'));
     		insert into myset(col) values
     		('a,d'),
     		('d,a'),
     		('a,d,a'),
     		('a,d,d'),
     		('d,a,d');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

测试

create table consumer(
name varchar(50),
sex enum('male','female'),
level enum('vip1','vip2','vip3','vip4','vip5'). #在指定范围内，多选一
hobby set('play','music','read','study') #在指定范围内，多选多
);

insert into consumer values
('xiaogui','male','vip5','read,study'),
('taibai','female','vip1','girl');

select * from consumer;
1
2
3
4
5
6
7
8
9
10
11
12

表的完整性约束

介绍

约束条件与数据类型的宽度一样，都是可选参数

作用：用于保证数据库的完整性和一致性

主要分为：

primary key (pk)   #标识该字段为该表的主键，可以唯一的表示记录
foreign key (fk)   #标识该字段为该表的外键
not null           #标识该字段不能为空
unique key (uk)    #标识该字段的值是唯一的
auto_increment     #标识该字段的值自动增长（整数类型，而且为主键）
default            #为该字段设置默认值

unsigned           #无符号
zerofill           #使用0填充
1
2
3
4
5
6
7
8
9

说明

1.是否允许为空，默认null,可设置not null,字段不允许为空，必须赋值
2.字段是否有默认值，缺省的默认值是null,如果插入记录时不给字段赋值，此字段使用默认值
sex enum('male','female') not null default 'male'
age int unsigned not null default 20 必须为正值（无符号） 不允许为空 默认值是20
3.是否是key
主键 primary key
外键 foreign key
索引 （index,unique...）
1
2
3
4
5
6
7
8

not null与default

是否可空，null表示空，非字符串 not null - 不可空 null- 可空

测试

==================not null====================
create table t1(if int); #id字段默认可以插入空
desc t1;

insert into t1 values(); #可以插入空


create table t2(id int not null); #设置字段id不为空
desc t2;

insert into t2 values(); #报错，不能插入空
# ERROR 1364 (HY000): Field 'id' doesn't have a default value


==================default====================
#设置id字段有默认值后，则无论id字段是null还是not null,都可以插入空，插入默认值哦填入default指定的默认值

create table t3(id int default 1);
alter table t3 modify id int not null default 1;


====================综合练习====================
create table student(
name varchar(20) not null,
age int(3) unsigned not null default 18,
sex enum('meal','female') default 'male',
hobby set('play','study','read','music') default 'play,music'
);
desc student;

insert into student(name) values('zhao');
select * from student;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

注意一点：如果是非严格模式，int类型不传值的话会默认为0，因为null不是int类型的，字段是int类型，所以它会自动将null变为0

unique

独一无二，唯一属性：id，身份证号等

是一种key，唯一键，是在数据类型之外的附加属性，其实还有加速查询的作用，后面再讲这个。

创建unique

============设置唯一约束============
方法一：
create table department1(
id int,
name varchar(20) unique,
comment varchar(100)
);

方法二：
create table department2(
id int,
name varchar(20),
comment varchar(100),
constraint uk_name unique(name)  #constraint是约束关键字，uk_name自己取的名字
);

insert into department1 values(1,'IT','技术');

insert into department1 values(1,'IT','技术'); #报错，name必须唯一
# ERROR 1062 (23000): Duplicate entry 'IT' for key 'name'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

创建联合唯一

create table service(
id int primary key auto_increment, #auto_increment 定义这个字段为自动增长，即如果INSERT时不赋值，则自动加1
name varchar(20),
host varchar(15) not null,
port int not null,
unique(host,port) #联合唯一
);

insert into service values
(1,'nginx','192.168.0.10',80),
(2,'haproxy','192.168.0.20',80),
(3,'mysql','192.168.0.30',3306)
;

insert into service(name,host,port) values('nginx','192.168.0.10',80); #报错
# ERROR 1062 (23000): Duplicate entry '192.168.0.10-80' for key 'host'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

primary key

从约束角度看primary key字段的值不为空且唯一，那我们直接使用not null+unique不就可以了吗，要它干什么？

​ 主键primary key是innodb存储引擎组织数据的依据，innodb称之为索引组织表，一张表中必须有且只有一个主键。

​ 一个表中可以：

​ 单列做主键 多列做主键（复合主键或者叫做联合主键）

在没有设置主键的时候，not null+unique会被默认当成主键

create table t1(id int not null unique);

desc t1;
1
2
3

单列主键测试

=============单列做主键=============
#方法一：not null+unique
create table department1(
id int not null unique, #主键
name varchar(20) not null unique,
comment varchar(100)
);

desc department1;


#方法二：在某一个字段后用primary key
create table department2(
id int primary key, #主键
name varchar(20),
comment varchar(100)
);

desc department2;


#方法三：在所有字段后单独定义primary key
create table department3(
id int,
name varchar(20),
comment varchar(100),
constraint pk_name primary key(id) #创建主键并为其命名pk_name
);

desc department3;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

联合主键解释

联合主键
		和联合唯一是类似的，
		create table t10(
        id int,
        port int,
        primary key(id,port)
        );
        
        desc t10;
1
2
3
4
5
6
7
8
9

多列（联合）主键测试

===================多列做主键===================
create table service(
ip varchar(15),
port char(5),
service_name varchar(10) not null,
primary key(ip,port)
);

desc service;

insert into service values
('172.16.45.10','3306','mysqld'),
('172.16.45.11','3306','mariadb')
;

insert into service values('172.16.45.10','3306','nginx'); #报错
# ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

auto_increment

​ 约束字段为自动增长，被约束的字段必须同时被key约束，也就是说只能给约束成key的字段嘉自增属性，默认起始位置为1，步长也为1。

auto_increment测试

#不指定id，则自动增长
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);

desc student;

insert into student(name) values
('egon'),
('alex')
;

select * from student;


#也可以指定id
insert into student values(4,'asb','female');

insert into student values(7,'wsb','female');

select * from student;


#对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长
delete from student;
select * from student;

insert into student(name) values('ysb');
select * from student;


#应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它,这个清空表之后在插入就是从1开始了
truncate student;

insert into student(name) values('egon');
select * from student;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

foreign key

快速理解foreign key（外键其实就是标明表和表之间的关系，表和表之间如果有关系的画就三种：一对一，多对一，多对多）

总结一下foreign key的下面的几个约束作用：

​ 1.先要建立被关联的表才能建立关联表

​ 2.在插入数据记录的时候，要先想被关联表中插入数据，才能王关联表里面插入数据

​ 3.更新或者删除数据的时候，都需要考虑关联表和被关联表的关系

​ 解决方案：

​ a.删除表的时候，先删除关联表，再删除被关联表

​ b.重建表的时候，在加外键关联的时候加上这两句：on delete cascade 和 on update cascade

一对多简单示例

=====================一对多=====================
create table press(
id int primary key auto_increment,
name varchar(20)
);

create table book(
id int primary key auto_increment,
name varchar(20),
press_id int not null,
foreign key(press_id) references press(id)
on delete cascade
on update cascade
);

insert into press(name) values
('北京工业地雷出版社'),
('人民音乐不好听出版社'),
('知识产权没有用出版社')
;

insert into book(name,press_id) values
('九阳神功',1),
('九阴真经',2),
('九阴白骨爪',2),
('独孤九剑',3),
('降龙十八掌',2),
('葵花宝典',3)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

多对多简单示例

=====================多对多=====================
create table authoe(
id int primary key auto_increment,
name varchar(20)
);

#这张表就存放作者表与书表的关系，即查询二者的关系查这表就行了
create table author2book(
id int not null unique auto_increment,
author_id int not null,
book_id int not null,
constraint fk_author foreign key(author_id) references author(id)
on delete cascade
on update cascade,
constraint fk_book foreign key(book_id) references book(id)
on delete cascade
on update cascade,
primary key(author_id,book_id)
);

#插入四个作者，id一次排开
insert into author(name) values('egon'),('alex'),('yuanhao'),('wpq');


#每个作者与自己的代表作如下
1 egon:
	1 九阳神功
	2 九阴真经
	3 九阴白骨爪
	4 独孤九剑
	5 降龙十八掌
	6 葵花宝典

2 alex:
	1 九阳神功
	6 葵花宝典
	
3 yuanhao:
	4 独孤九剑
	5 降龙十八掌
	6 葵花宝典
	
4 wpq:
	1 九阳神功
	
	
insert into author2book(author_id,book_id) values
(1,1),
(1,2),
(1,3),
(1,4),
(1,5),
(1,6),
(2,1),
(2,6),
(3,4),
(3,5),
(3,6),
(4,1);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

一对一简单示例

#一定是student来foreign key表customer，这样就保证了：
#1 学生一定是一个客户
#2 客户不一定是学生，但有可能成为一个学生

create table customer(
id int primary key auto_increment,
name varchar(20) not null,
qq varchar(10) not null,
phone char(16) not null
);

create table student(
id int primary key auto_increment,
class_name varchar(20) not null,
customer_id int unique, #该字段一定要是唯一的
foreign key(customer_id) references customer(id) #外键的字段一定要保证unique
on delete cascade
on update cascade
);

#增加客户
insert into customer(name,qq,phone) values
('李飞机','31811231',13811341220),
('王大炮','123123123',15213146809),
('守榴弹','283818181',1867141331),
('吴坦克','283818181',1851143312),
('赢火箭','888818181',1861243314),
('战地雷','112312312',18811431230)
;

#增加学生
insert into student(class_name,customer_id) values
('脱产3班',3),
('周末19期',4),
('周末19期',5)
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

修改表 alter table

语法
1. 修改表名
	alter table 表名
	rename 新表名;
	
	
2. 增加字段
	alter table 表名
	add 字段名 数据类型 [完整性约束条件...], #注意这里可以通过逗号来分割，一下添加多个约束条件
	add 字段名 数据类型 [完整性约束条件...];
	
	alter table 表名
	add 字段名 数据类型 [完整性约束条件...] first; #添加这个字段的时候，把它放到第一个字段位置去。
	
	alter table 表名
	add 字段名 数据类型 [完整性约束条件...] after 字段名; #after是放到后的这个字段的后面去了，我们通过一个first和一个after就可以将新添加的字段放到表的任意字段位置了。
	
	
3. 删除字段
	alter table 表名
	drop 字段名;
	
	
4. 修改字段
	alter table 表名
	modiey 字段名 数据类型 [完整性约束条件...];
	
	alter table 表名
	change 旧字段名 新字段名 旧数据结构 [完整性约束条件...]; #change比modify还多了个改名字的功能，这一句是只改了一个字段名
	
	alter table 表名
	change 旧字段名 新字段名 新数据类型 [完整性约束条件...]; #这一句除了改了字段名，还改了数据类型、完整性约束等等的内容
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

给一个字段添加外键属性的语句：alter table 表2名 add foreign key（表2的一个字段） references 表1名（表1的一个字段）;

注意一点：在mysql里面表名是不区分大小写的

简单示例：

示例：
1. 修改存储引擎
alter table service
engine=innodb;

2. 添加字段
alter table student10
add name varchar(20) not null,
add age int(3) not null default 22;

alter table student10
add stu_num varchar(10) not null after name;  #添加name字段之后

alter table student10
add sex enum('male','female') default 'male' first;  #添加到最前面

3. 删除字段
alter table studnet10
drop sex;

alter table service
drop mac;

4. 修改字段类型modify
alter table student10
modify age int(3);

alter table student10
modify id int(11) not null primary key auto_increment;  #修改为主键

5. 增加约束（约束已有的主键增减auto_increment）
alter table student10 modify id int(11) not null primary key auto_increment; #报错
# ERROR 1068 (42000): Multiple primary key defined

alter table student10 modify id int(11) not null auto_increment;

6. 对已经存在的表增加复合主键
alter table service2
add primary key(host_ip,port);

7. 增加主键
alter table student1
modify name varchar(10) not null primary key;

8. 增加主键和自动增长
alter table student1
modify id int not null primary key auto_increment;

9. 删除主键
a. 删除自增约束
alter table student10 modify id int(11) not null;

b. 删除主键
alter table student10
drop primary key;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

第三节 MySQL行（记录）的详细操作

MySQL数据操作：DML

1.插入数据insert

语法

1. 插入完整数据（顺序插入）
	语法一：
	insert into 表名(字段1,字段2,字段3...字段n) values(值1,值2,值3...值n); #指定字段来再擦汇入数据，插入的值要和你前面的字段相匹配
	
	语法二：
	insert into 表名 values(值1,值2,值3...值n); #不指定字段的画，就按照默认的几个字段来插入数据
	
2. 指定字段插入数据
	语法：
	inser into 表名(字段1,字段2,字段3...) values(值1,值2,值3...);
	
3. 插入多条记录
	语法：#插入多条记录用逗号来隔开
	insert into 表名 values
	(值1,值2,值3...值n),
	(值1,值2,值3...值n),
	(值1,值2,值3...值n);
	
4. 插入查询结果
	语法：
	insert into 表名(字段1,字段2,字段3...字段n)
	select(字段1,字段2,字段3...字段n) from 表2
		where ...; #将从表2里面查询出来的结果来插入到我们的表中，但是注意查询出来的数据要和我们前面指定的字段要对应好
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

2.更新数据update

语法：
	update 表名 set
		字段1=值1,  #注意语法，可以同时来修改多个值，用逗号分隔
		字段2=值2,
		where condition; #更改那些数据，通过where条件来定位到符合条件的数据
		
示例：
	update mysql.user set password=password('123')
		where user='root' and host='localhost'; #这句话是对mysql这个库中的user表中的user字段为'root'并且host字段为'localhost'的这条记录的password字段的数据进行修改，将password字段的那个数据改为password('123')这个方法对123加工后的密码数据，password()这个方法是mysql提供的密码进行加密用的方法。
	定位到某个记录，并把这个记录中的某项内容更改掉
1
2
3
4
5
6
7
8
9
10

3.删除数据delete

语法：
	delete from 表名
		where condition; #删除符合条件的一些记录
	delete from 表名; 如果不加where条件，意思是将脸所有的内容啥都删掉，但是清空所有的内容，一=一般我们用truncate,能够将id置为零，delete不能将id置为零，在=再插入数据的时候，会按照之前的数据记录的id数继续递增

示例：
	delete from mysql.user
		where password='123';
1
2
3
4
5
6
7
8

4.查询数据select

select * from, 这个select * 指的是要查询所有字段的数据

select distinct 字段1,字段2... from 库名.表名 #from后面是说从库的某个表中去找数据，mysql会去找到这个库对应的文件夹下去找到你表名对应的那个数据文件，找不到就直接报错了，找到了就继续后面的操作
	where 条件  #从表中钊符合条件的数据记录，where后面跟的是你的查询条件
	group by field(字段) #分组
    having 筛选  #过滤，过滤之后执行select后面的字段筛选，就是说我要确定一下需要哪个zidaun的数据，你查询的zidaun数据进去去重，然后在进行下面的操作
    order by field(zidaun)  #将结果按照后面的字段进行排序
    limit 限制条数  #将最后的结果加一个限制条数，就是说我要过滤或者说闲着查询出来的数据记录的条数
    
关于上面的这些内容，我们在下面一个一个的来详细解释
1
2
3
4
5
6
7
8
9
10

关键字的执行优先级（重点）

1.找到表：from

2.拿着where指定的约束条件，去文件/表中取出一条条记录

3.将取出的一条条记录进行分组group by,如果没有group by,则整体作为一组

4.将分组的结果进行having过滤

5.执行select

6.去重

7.将结果按照条件排序：order by

8.限制结果的显示条数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

简单查询

#我们来创建一个员工表，然后对员工表进行一个简单的查询，来看一下效果，下面是员工表的字段
company.employee
	员工id	id				int
	姓名		emp_name		varchar
	性别		sex				enum
	年龄		age				int
	入职日期   hire_date        date
	岗位		post			varchar
	职位描述   post_comment     varchar
	薪水		salary			double
	办公室     office			int
	部门编号   depart_id        int
	
	
#创建表
create table employee(
id int not null unique auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
salary double(15,2),
office int, #一个部门一个屋子
depart_id int
);

#查看表结构
desc employee;

#插入记录
#三个部门：教学，销售，运营
insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部，全部是老师
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龙','male',48,'20101111','teacher',10000,401,1),

('歪歪','female',48,'20150311','sale',3000.13,402,2), #以下是销售部门
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('丁丁','female',18,'20110312','sale',1000.37,402,2),
('星星','female',18,'20160513','sale',3000.29,402,2),
('格格','female',28,'20170127','sale',4000.33,402,2),

('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3)
;

#ps: 如果在windows系统中，插入中文字符，select的结果为空白，可以将所有的字符编码统一设置成gbk
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

查询操作示例

简单查询
	select id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id
	from employee;
	
	select * from employee; #不推荐用*，查询的时候*的效率低，至于为什么低，后面后降到，先知道一下就行了
	
	select name,salary from employee;
	
#避免重复distinct
	select post from employee; #直接这样查询我们会看到很多重复的内容
	
	select distinct post from employee; #对查询出来的记录进行去重
	
	select distinct post,salary from employee; #但是如果这样写，你会发现，貌似没用起到根据post来去重是效果，因为你的去重条件编程了post和salary两个字段的数据，只有他俩合起来是一个重复记录的时候才会去重
	
	看一下下面这两句的效果就明白了：
	select post,sex from employee;
	select distinct post,sex from employee;
	
#通过四则运算查询
	select name,salary*12 from employee; #查询每个人的年薪
	
	#指定这个虚拟表的标题，通过as+新字段名来指定
	
	select name,salary+12 as Annual_salary from employee; #as + 新字段名，就是起一个别名的意思，上面的那个salary*12的字段名也是一个别名，只不过不直观，是mysql字段给你写的
	
	select name,salary*12 Annual_salary from employee;
	#除了乘法以外，加减乘除都是可以的
	
#自定义显示格式，自己规定查询结果的显示格式
	concat() 函数用于连接字符串
	select concat('姓名：',name,'年薪：',salary*12) as Annual_salary
	from employee;
	
	select concat('姓名：',name,'年薪：',salary*12) as Annual_salary,concat('性别：',sex)
	from employee; #还可以这样分成两列
	
	concat_ws() 第一个参数为分隔符来进行字符串拼接
	select concat_ws(':',name,salary*12) as Annual_salary #通过冒号来将name和salary连接起来
	from employee;
	
	#上面这个效果我们也可以通过concat来实现：
	select concat(name,':',salary*12) as Annual_salary from employee;
	
	结合case语句：结合条件来对查询的结果进行一些加工操作
	select
		(
        	case
            when name = 'egon' then
            	name
            when name = 'alex' then
            	concat(name,'_bigsb')
            else
            	concat(name,'sb')
            end
        ) as new_name,sex
    from
    	employee;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

where约束

where语句中可以使用：

• 1.比较运算符：> < >= <= <> !=
• 2.between 80 and 100 值在80到100之间
• 3.in(80,90,100) 值是80或90或100
• 4.like 'egon%'

pattern可以是%或_,
	%表示任意多字符
	_表示一个字符
1
2
3

• 5.逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not

示例如下：

#1:单条件查询
	select name from employee
		where post = 'sale';
		
#2:多条件查询
	select name,salary from employee
		where post = 'teacher' and salry>10000;
		
#3:关键字between and 写的是一个区间
	select name,salary from employee
		where salary between 10000 and 20000; #就是salary>=10000 and salary<=20000的数据
		
	select name,salary from employee
		where salary not betweent 10000 and 20000; #加个not，就是不在这个区间内，薪资小于10000的或者薪资大于20000的，注意没有等于
		
#4:关键字is null(判断某个字段是否为null不能用等号，需要用is) 判断null只能用is
	select name,post_comment from employee
		where post_comment is null;
		
	select name,post_comment from employee
		where post_comment is not null;
		
	select name,post_comment from employee
		where post_comment = ''; #注意''是空字符串，不是null，两个是不同的东西，null是啥也没有，''是空的字符串的意思，是一种数据类型，null是另外一种数据类型
	ps:
		执行
		update employee set post_comment = '' where id = 2;
		再用上条查看，就会有结果了
		
#5:关键字in集合查询
	select name,salary from employee
		where salary = 3000 or salary = 4000 or salary = 9000; #这样写是不是太麻烦了，写一大堆的or,下面我们用in这个简单的写法来搞
		
	select name,salary from employee
		where salary in(3000,3500,4000,9000);
		
	select name,salary from employee
		where salary not in(3000,3500,4000,9000);
		
#6:关键字like模糊查询，模糊匹配，可以结合通配符来使用
	通配符'%' #匹配任意所有字符
	select * from employee
		where name like 'eg%';
		
	通配符'_' #匹配任意一个字符
	select * from employee
		where name like 'al__'; #注意我这里写的两个_,用1个的画，匹配不到alex，因为al后面还有两个字符ex。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

分组查询group by

为什么要分组？

#1、首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的

#2、分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等

#3、为何要分组呢？是因为我们有时候会需要以组为单位来统计一些数据或者进行一些计算的，对不对，比方说下面的几个例子
	取每个部门的最高工资
	取每个部门的员工数
	取男人数和女人数
	
	小窍门：'每'这个字后面的字段，就是我们分组的依据，只是个小窍门，但是不能表示所有订单情况，看上面第三个分组，没有'每'字，这个就需要我们通过语句来自行判断分组依据了
	
#4、大前提：
	可以按照任意字段分组，但是分组完毕后，比如group by post, 只能查看post字段，如果想查看组内信息，需要借助聚合函数
	
#注意一点，在查询语句里面select 字段 from 表，这几项是必须要有的，其他说明where、group by等等都是可有可无的

单独使用group by关键字分组
	select post from employee
	group by post;
	注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数
	
group by关键字和group_concat()函数一起使用，比如说我想按部门分组，每个组有哪些员工，都显示出来，怎么搞
	select post,group_concat(name) from employee
	group by post; #按照岗位分组，并查看组内所有成员名，通过逗号拼接在一起
	
	select post,group_concat(name,':',salary) as emp_members from employee
	group by post;
	
group by一般都会与聚合函数一起使用，聚合是什么意思：聚合就是将分组的数据聚集到一起，合并起来搞事情，拿到最后的结果
	select post,count(id) as count from employee
	group by post; #按照岗位分组，并查看每个组有多少人，每个人都有唯一的id号，我count是计算一下每组有多少id记录，通过这个id记录我就知道没个组有多少人了
	
关于聚合函数，mysql提供了以下几种聚合函数：count、max、min、avg、sum等，上面的group_concat也算是一个聚合函数了，做字符串拼接的操作
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

注意：

如果我们用设置了unique约束的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义
多条记录之间的某个字段值相同，该字段通常用来作为分组的依据
1
2

聚合函数

#强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组

示例：
	select count(*) from employee; #count是统计个数用的
	select count(*) from employee
	where depart_id = 1; #后面跟where条件的意思是统计一下满足depart_id = 1这个的索引记录的个数
	select max(salary) from employee; #max() 统计分组后每组的最大值，这里没有写group by,那么就是统计整个表中所有记录中薪资最大的，薪资的值
	select min(salary) from employee;
	select avg(salary) from employee;
	select sum(salary) from employee;
	select sum(salary) from employee where depart_id = 3;
1
2
3
4
5
6
7
8
9
10
11

having过滤

having与where不一样的地方在于！！！！！！

having的语法格式和where是一模一样的，只不过having是在分组之后进行的进一步的过滤，where不能使用聚合函数，having是可以使用聚合函数的
#！！！执行优先级从高到低：where > group by > having
#1. where 发生在分组group by之前，因而where中可以有任意字段，但是绝对不能使用聚合函数。

#2. having发生在分组group by之后，因而having中可以使用分组的字段，无法直接渠道其他字段，having是可以使用聚合函数
1
2
3
4
5

having简单测试

#来个需求：统计各部门年龄在30岁及以上的员工的平均薪资，并且保留平均工资大于10000的部门
答案：
select post,avg(salary) as new_sa from employee
where age >= 30
group by post
having avg(salary) > 10000;

然后我们看这样一句话：
select * from employee
having avg(salary) > 10000;
只要一运行就会报错：
	̴mysql> select * from employee having avg(salary) > 10000; ̴̴̴̴ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
	
是因为having只能在group by后面运行
1
2
3
4
5
6
7
8
9
10
11
12
13
14

distinct去重

select id,count(distinct post) from employee; #报错
报错了：是因为distinct不能返回其他的字段，只能返回目标字段

select count(distinct post) from employee;
1
2
3
4

查询排序order by

按单列排序
	select * from employee order by salary; #默认是升序排列
	select * from employee order by salary asc; #升序
	select * from employee order by salary sesc; #降序
	
按多列排序：先按照age升序，如果年纪相同，则按照薪资降序
	select * from employee
	order by age, #注意排序的条件用逗号分隔
	salary desc;
1
2
3
4
5
6
7
8
9

limit显示查询的记录数

示例：
	#取出工资最高的前三位
	select * from employee
	order by salary desc
	limit 3; #默认初始位置为0，从第一条开始顺序取出三条
	
	select * from employee
	order by salary desc
	limit 0,5; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条
	
	select * from employee
	order by salary desc
	limit 5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条
1
2
3
4
5
6
7
8
9
10
11
12
13

正则表达式查询

select * from employee
where name regexp '^ale';

select * from employee
where name regexp 'on$';

select * from employee
where name regexp 'm{2}';

小结：对字符串匹配的方式
where name = 'egon';
where name like 'yua%';
where name regexp 'on$';
1
2
3
4
5
6
7
8
9
10
11
12
13

5.多表查询

#建表
#部门表
create table department(
id int,
name varchar(20)
);

#员工表，之前我们学过foreign key，强行加上约束关联，但是我下面这个表并没有直接加foreign key,这两个表我只是让它们在逻辑意义上有关系，并没有嘉foreign key来强制两表建立关系，为什么要这样搞，是有些效果要给大家演示一下
#所以，这两个表是不是先建立哪个表都行啊，如果有foreign key的话，是不是就需要注意表建立的顺序了。那我们来建表。
create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);

#给两个表插入一些数据
insert into department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'); #注意这一条数据，在下面的员工表里面没有对应这个部门的数据

insert into employee(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',20,200),
('jingliyang','female',18,204); #注意这条数据的dep_id字段的值，这个204，在上面的部门表里面也没有对应的部门id。所以两者都含有一条双方没有涉及到的数据，这都是为了演示一下效果设计的昂


#查看表结构和数据
desc department;

desc employee;

select * from department;

select * from employee;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

多表连接查询

#重点：外链接语法

select 字段列表
	from 表1 inner|left|right join 表2
	on 表1.字段 = 表2.字段;
1
2
3
4
5

交叉连接：不适用于任何匹配条件。生成笛卡尔积

补充一点：select查询表的时候，后面可以跟多张表一起查询下：

select * from department,employee;

我们让employee表在前面看看结果，注意看结果表的字段
select * from employee,department;
1
2
3
4

内连接：只连接匹配的行

#我们要找的数据就是员工表里面dep_id字段的值和部门表里面id字段的值能对应上的那些数据啊，所以你看下面的写法：
select * from employee,department
where employee.dep_id = department.id;

拿到了我们想要的结果。

但是你看，我们左表employee表中的dep_id为204的那个数据没有了，右表department表的id为203的数据没有了，因为我们现在要的就是两表能对应上的数据一起查出来，那个204和203都对应不上。

#再看一个需求，我要查出技术部的员工的名字
select name from employee,department
where employee.dep_id = department.id
and department.name = '技术'; #报错
# ERROR 1052 (23000): Column 'name' in field list is ambiguous

#上面的4直接就报错了，因为select后面直接写的name，在两个表合并起来的表中，是有两个name字段的，直接写name是不行的，要加上表名，再看：
select employee.name from employee,department
where employee.dep_id = department.id
and department.name = '技术';

结果就没问题了
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

但是你看上面的代码有没有上面不太好的地方，是让我们能够完成我们的事情，但是代码可读性不好，所以以后不要这么写

所以mysql为我们提供了一些专门做连表操作的方法

外连接之左连接：优先显示左表全部记录

#以左表为准，即找出所有员工信息，当然包括没有部门的员工
#本质就是：在内连接的基础上增加左边有右边没有的结果 #注意语法：
select employee.id,employee.name,department.name as depart_name from employee
left join department
on employee.dep_id = department.id;
1
2
3
4
5

外连接之右连接：优先显示右表全部记录

#以右表为准，即找出所有部门信息，包括没有员工的部门
#本质就是：在内连接的基础上增加右边有左边没有的结果
select employee.id,employee.name,department.name as depart_name from employee
right join department
on employee.dep_id = department.id;
1
2
3
4
5

全外连接：显示左右两个表全部记录

全外连接：在内连接的基础上增加左边有和右边没有的和右边有左边没有的结果
#注意：mysql不知处全外连接 full join
#强调：mysql可以使用此种方式间接实现全外连接
select * from employee
left join department
on employee.dep_id = department.id
union
select * from employee
right join department
on employee.dep_id = department.id
;

+------+------------+--------+------+--------+------+--------------+

#注意 union与union all的区别：union会去掉相同的记录，因为union all是left join 和 right join合并，所以有重复的记录，通过union就将重复的记录去重了。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

子查询

#我们之前的做法是：先连表
select * from employee
inner join department
on employee.dep_id = department.id;

#然后根据连表的结果进行where过滤，将select*改为select employee.name

select employee.name from employee
inner join department
on employee.dep_id = department.id
where department.name = '技术';
1
2
3
4
5
6
7
8
9
10
11

看操作

#首先从部门表里面找到技术部门对应的id
select id from department
where name = '技术';

#那我们把上面的查询结果用括号括起来，它就表示一条id=200的数据，然后我们通过员工表来查询dep_id=这条数据作为条件来查询员工的name
select name from employee
where dep_id = (select id from department
                where name = '技术');
1
2
3
4
5
6
7
8

子查询：
#1: 子查询是将一个查询语句嵌套在另一个查询语句中。
#2: 内层查询语句的查询结果，可以为外层查询语句提供查询条件。
#3: 子查询中可以包含：in、not in、any、all、exists 和 not exists等关键字
#4: 还可以包含比较运算符：=、!=、>、<等
1
2
3
4
5

带in关键字的子查询示例

#查询员工平均年龄在25岁以上的部门名，可以用连表，也可以用子查询，我们用子查询来搞一下
select id,name from department
where id in
(select dep_id from employee
 group by dep_id
 having avg(age) > 25
);

#连表来搞一下上面这个需求
select department.name from department
inner join employee
on department.id = employee.dep_id
group by department.name
having avg(age) > 25;


#查看技术部员工姓名
select name from employee
where dep_id in
(select id from department
 where name = '技术'
);

#查看不足1人的部门名(子查询得到的是有人的部门id)
select name from department
where id not in
(select distinct dep_id from employee
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

带比较运算符的子查询示例

#比较运算符：=、!=、>、>=、<、<=、<>
#查询大于所有人平均年龄的员工名与年龄
select name,age from emp
where age > (select avg(age) from emp);

#查询大于部门内平均年龄的员工名、年龄
select t1.name,t1.age from emp t1
inner join
(select dep_id,avg(age) avg_age from emp
 group by dep_id) t2
 on t1.dep_id = t2.dep_id
 where t1.age > t2.avg_age;
1
2
3
4
5
6
7
8
9
10
11
12

带exists关键字的子查询示例

exists关键字表示存在。在使用exists关键字时，内层查询语句不返回查询的记录。而是返回一个真假值。True或False 当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询。还可以写not exists,和exists的效果就是反的

#department表中存在dep_id = 203,True
select * from employee
where exists
(select id from department
 where id = 200
);

#department表中存在dep_id = 205,False
select * from employee
where existx
(select id from department
 where id = 204);
1
2
3
4
5
6
7
8
9
10
11
12

MySQL测试题

1. 单表查询测试
	a) 构建以下数据表
	
	create table stu(
    name varchar(20) not null,
    subject varchar(20) not null,
    score int(20) not null
    );
    
    insert into stu values
    ('张三','数学',90),
    ('张三','语文',50),
    ('张三','地理',40),
    ('李四','语文',55),
    ('李四','政治',45),
    ('王武','政治',30),
    ('赵六','语文',100),
    ('赵六','地理',99),
    ('赵六','政治',98)
    ;
    
    b) 查询2门以及两门以上的不及格者的平均成绩
    
    select name,sum(score < 60) as sumsc,avg(score) from stu
    group by name
    having sumsc >= 2;
    
2. 多表查询测试
	按照给出的表结构按要求，写出sql语句
	
create table m(
matchID int primary key auto_increment comment '主键', #comment是注释
hostTeamID int(20) not null comment '主队的ID',
guestTeamID int(20) not null comment '客队的ID',
matchResult varchar(20) comment '比赛结果，如（2:0）',
matchTime date comment '比赛开始时间'
);

create table t(
teamID int primary key auto_increment comment '主键',
teamName varchar(20) comment '队伍名称'
);

#查看字段注释的方法
show full columns from m;

insert into m(hostTeamID,guestTeamID,matchResult,matchTime) values
(1,2,'2:0',"2006-05-21"),
(2,3,'1:2',"2006-06-21"),
(3,1,'2:5',"2006-06-25"),
(2,1,'3:2',"2006-07-21")
;

insert into t(teamName) values
('国安'),
('申花'),
('公益联队')
;

select m.*,t1.teamName,t2.teamName from m
left join t as t1
on m.hostTeamID = t1.teamID
left join t as t2
on m.guestTeamID = t2.teamID
where m.matchTime between '2006-06-01' and '2006-07-01';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

四五节过

第六节 索引

1.介绍

什么是索引？

​ 索引在MySQL中也叫做"键"或者"key" （primary key,unique key, 还有一个index key）,是存储引擎用于快速找到记录的一种数据结构。

2.索引原理

索引的影响

1. 在表中有大量数据的前提下，创建索引速度会很慢
2. 在索引创建完毕后，对表的查询性能会发幅度提升，但是写性能会降低
1
2

3.索引的数据结构

B+树

4.聚集索引与辅助索引

1.聚集索引

聚集索引的好处之一：它对主键的排序查找和范围查找速度非常快，叶子节点的数据就是用户所要查询的数据。如用户需要查找一张表，查询最后的10位用户信息，由于B+树索引是双向链表，所以用户可以快速找到最后一个数据页，并取出10条记录

示例

参照第六小姐测试索引的准备阶段来创建出表s1
desc s1; #最开始没有主键

explain select * from s1
order by id
desc limit 10; #Using filesort,需要二次排序

alter table s1
add primary key(id); #添加主键

explain select * from s1
order by id
desc limit 10; #基于主键的聚集索引在创建完毕后就已经完成了排序，无需二次排序
1
2
3
4
5
6
7
8
9
10
11
12
13

聚集索引的好处之二：范围查询（range query）, 即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可

示例

alter table s1
drop primary key;

desc s1;

explain select * from s1
where id < 1 and id < 1000000; #没有聚集索引，预估需要检索的rows数如下，explain就是预估一下你的sql的执行效率

alter table s1
add primary key(id);

explain select * from s1
where id > 1 and id < 1000000; #有聚集索引，预估需要检索的rows数如下
1
2
3
4
5
6
7
8
9
10
11
12
13

2.辅助索引

就是我们在查询的时候，where后面需要写id之外的其他字段名称来进行查询，比如说是where name=xx,

没法用到主键的索引的效率，怎么办，就需要我们添加辅助索引了，给name添加一个辅助索引。

表中除了聚集索引外其他索引都是辅助索引（也称为非聚集索引）（unique key啊、index key啊），与聚集索引的区别是：辅助索引的叶子节点不包含行记录的全部数据。

5.索引管理

语法

#方法一：创建表时
	create table 表明(
    字段名1 数据类型 [完整性约束条件...],
    字段名2 数据类型 [完整性约束条件...],
    [unique | fulltext | spatial] index | key
    [索引名] (字段名[(长度)] [asc | desc])
    );
    
#方法二：create在已存在的表上创建索引
	create [unique | fulltext |spatial] index 索引名
	on 表名 (字段名[(长度)] [asc | desc]);
	
#方法三：alter table在已存在的表上创建索引
	alter table 表名
	add [unique | fulltext |spatial] index
	索引名 (字段名[(长度)] [asc | desc]);
	
#删除索引：
	drop index 索引名 on 表名字;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

常用操作：

添加主键索引：
	创建的时候添加：添加索引的时候要注意，给字段里面数据大小比较小的字段添加，给字段里面的数据区分度高的字段添加。
	聚集索引的添加方式
创建的是添加
create table t1(
Id int primary key,
);

create table t1(
Id int,
primary key(id)
);

表创建完了之后添加
alter table 表名
add primary key(id)

删除主键索引
alter table 表名
drop primary key;


唯一索引：
create table t1(
Id int unique,
);

create table t1(
Id int,
unique key uni_name(id)
);

表创建好之后添加唯一索引：
alter table s1
add unique key u_name(id);

删除：
alter table s1
drop index u_name;


普通索引：
创建：
create table t1(
Id int,
index index_name(id)
);

alter table s1
add index index_name(id);

create index index_name on s1(id);

删除：
alter table s1
drop index u_name;

drop index 索引名 on 表名字;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

简单示例

#方式一
create table t1(
id int,
name char,
age int,
sex enum('male','female'),
unique key uni_id(id),
index ix_name(name) #index没有key
);

#方式二
create index ix_age on t1(age);

#方式三
alter table t1 add index ix_sex(sex);

#查看
show create table t1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

6.索引测试

#1. 准备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);

#2. 创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
begin
	declare i int default 1;
	while(i<3000000)do
		insert into s1 values(i,'egon','male',concat('egon',i,'@oldboy'));
		set i=i+1;
	end while;
end$$ #$$结束
delimiter ; #重新声明分号为结束符号

#3. 查看存储过程
show create procedure auto_insert1\G

#4. 调用存储过程
call auto_insert1();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

在没有索引的前提下测试查询速度

#无索引：查询速度很慢
select * from s1 where id=333333333;
1
2

在表中已经存在大量数据的前提下，为某个字段建立索引，建立速度会很慢

create index a on s1(id);
1

在索引建立完毕后，以该字段为查询条件时，查询速度提升明显

select * from s1 where id=333333333;
1

7.慢查询优化的基本步骤

1.先运行看看是否真的很慢，注意设置sql_no_cache
2.where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
3.explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
4.order by limit 形式的sql语句让排序的表优先查
5.了解业务方使用场景
6.加索引时参照建索引的几大原则
7.观察结果，不符合预期继续从0分析
1
2
3
4
5
6
7

第七节 创建用户和授权

不想写这节

第八节 MySQL数据备份与还原

1.首先外面先创建一个名为crm2的库

	create database crm2;
	show create database crm2;
2.切换到crm2库下
	use crm2;
3.创建两张表，student表和class表
	create table student(
    id int primary key,
    name char(8) not null,
    age int,
    class_id int not null
    );
    
    create table class(
    id int primary key,
    cname char(20) not null
    );
    
4.给两张表插入一些数据
	insert into class values(1,'一班'),(2,'二班');
	insert into student values(1,'Jaden',18,1),(2,'太白',45,1),(3,'彦涛',30,2);
	
5.查看一下两个表的数据
	select * from student;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

通过mysqldump指令进行备份，在cdm窗口下执行下面的指令，注意不是进入mysql里面输入的，是在外面。

C:WINDOWS\system32>mysqldump -h 127.0.0.1 -uroot -p crm2 > E:\数据库备份练习\crm2.sql
1

然后外面就会发现在这个’E:\数据库备份练习’路径下面就有了crm2.sql文件 然后外面通过nodepad++（随便一个文本编辑器就可以），打开看看里面的内容：

-- MySQL dump 10.13  Distrib 8.0.12, for Win64 (x86_64)
--
-- Host: 127.0.0.1    Database: crm2
-- ------------------------------------------------------
-- Server version	8.0.12

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
 SET NAMES utf8mb4 ;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `class`
--

DROP TABLE IF EXISTS `class`; --如果之前存在class表，就将之前的class表删除
/*!40101 SET @saved_cs_client     = @@character_set_client */;
 SET character_set_client = utf8mb4 ;
CREATE TABLE `class` ( --创建表
  `id` int(11) NOT NULL,
  `cname` char(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `class`
--

LOCK TABLES `class` WRITE; --锁表
/*!40000 ALTER TABLE `class` DISABLE KEYS */;
INSERT INTO `class` VALUES (1,'一班'),(2,'二班'); --插入数据
/*!40000 ALTER TABLE `class` ENABLE KEYS */;
UNLOCK TABLES; --解锁

--
-- Table structure for table `student`
--

DROP TABLE IF EXISTS `student`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
 SET character_set_client = utf8mb4 ;
CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `name` char(8) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `class_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `student`
--

LOCK TABLES `student` WRITE;
/*!40000 ALTER TABLE `student` DISABLE KEYS */;
INSERT INTO `student` VALUES (1,'Jaden',18,1),(2,'太白',45,1),(3,'彦涛',30,2);
/*!40000 ALTER TABLE `student` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2019-12-15 23:39:18
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77

恢复数据

如果mysqldump的时候没有使用-B参数，则需要创建crm2这个数据库

1.连接到数据库中，并创建crm2这个库
mysql -uroot -proot
create database crm2;

2.退出mysql或者重新启动一个cmd窗口，然后执行
	mysql -uroot -p 库名 < mysqldump出来的那个sql文件你的路径
	例如：mysql -uroot -proot crm2 < E:\数据库备份练习\crm2.sql
	
3.这样就恢复好了，外面连接上数据库并查看里面的内容：
	mysql -uroot -proot
	use crm2;
	show tables;
	
	select * from student;
	
	desc student;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

如果mysqldump的时候使用了-B参数，则不需要重新创建crm2这个数据库

1.mysqldump -uroot -p -B crm2 > E:\数据库备份练习\crm2.sql

2.在cmd窗口下执行：mysql -uroot -p < E:\数据库备份练习\crm2.sql

3.查看一下是否恢复了：
	show databases;
	
	use crm2;
	
	show tables;
	
	select * from class;
	
	desc student;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

多个数据库备份

C:WINDOWS\system32>mysqldump -uroot -p -B crm2 mysql > E:\数据库备份练习\crm2mysql.sql
1

就是多个库名用空格分开，这样备份出来的sql文件还是一个，也就是这两个库都备份到一个问价那里面了。

我们只想恢复其中的一个 那是不可能的(._{_}.)

要想只恢复一个数据库，只能进行单库备份