数据库笔记2

发表于 | 更新于 | 分类于 | 评论数：

mysql安装

mysql

1
MySQL是一种关系数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性. 由于MYSQL是一款开放源码软件,因此可以大大降低总体拥有成本.现在我们来介绍下MYSQL的SQL原生态语句

linux mysql数据库安装

如何安装数据库

1
2
3
4
5
6
先下载mysql
将下载好的文件解压到指定文件
将bin绝对路径放入系统环境变量中，并保存
初始化mysql，mysqld --install添加到系统服务
服务端：mysqld
客户端：mysql -uroot -p（开始默认没有密码）-u 用户名  -p 密码  -h 服务器的ip  -P端口号   在本机上执行客户端不需要指的 -h -P

编译安装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
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
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
#cmake
#定制功能：存储引擎、字符集、压缩
#定制安装位置、数据存放位置、文件位置（socket）
#克隆一个模板机（使用CentOS6），克隆完做快照
#IP 10.0.0.52 主机名db02
#下载5.6.36包
[root@db02 ~]# wget -q https://mirrors.sohu.com/mysql/MySQL-5.6/mysql-5.6.36.tar.gz
#安装epel源
[root@db02 ~]# wget -O /etc/yum.repos.d/epel.repo https://mirrors.aliyun.com/repo/epel-6.repo
#安装依赖包
[root@db02 ~]# yum install -y ncurses-devel libaio-devel
#安装cmake
[root@db02 ~]# yum install -y cmake
#创建用户
[root@db02 ~]# useradd mysql -s /sbin/nologin -M
#修改hosts
[root@db02 ~]# vim /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
172.16.1.52 db02
#解压MySQL源码包
[root@db02 tools]# tar xf mysql-5.6.36.tar.gz
#进入MySQL目录
[root@db02 tools]# cd mysql-5.6.36
#生成
[root@db02 mysql-5.6.36]#
#程序存放位置
cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.6.38 \
#数据存放位置
-DMYSQL_DATADIR=/application/mysql-5.6.38/data \
#socket文件存放位置
-DMYSQL_UNIX_ADDR=/application/mysql-5.6.38/tmp/mysql.sock \
#使用utf8字符集
-DDEFAULT_CHARSET=utf8 \
#校验规则
-DDEFAULT_COLLATION=utf8_general_ci \
#使用其他额外的字符集
-DWITH_EXTRA_CHARSETS=all \
#支持的存储引擎
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
#禁用的存储引擎
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
#启用zlib库支持（zib、gzib相关）
-DWITH_ZLIB=bundled \
#启用SSL库支持（安全套接层）
-DWITH_SSL=bundled \
#启用本地数据导入支持
-DENABLED_LOCAL_INFILE=1 \
#编译嵌入式服务器支持
-DWITH_EMBEDDED_SERVER=1 \
# mysql5.6支持了google的c++mock框架了，允许下载，否则会安装报错。
-DENABLE_DOWNLOADS=1 \
#禁用debug（默认为禁用）
-DWITH_DEBUG=0
#编译
[root@db02 mysql-5.6.36]# make
#安装
[root@db02 mysql-5.6.36]# make install
#做软链接
[root@db02 mysql-5.6.36]# ln -s /application/mysql-5.6.38/ /application/mysql
#拷贝配置文件
[root@db02 mysql-5.6.36]# cp support-files/my*.cnf /etc/my.cnf
#拷贝mysql启动脚本
[root@db02 mysql-5.6.36]# cp support-files/mysql.server /etc/init.d/mysqld
#进入MySQL初始化脚本目录
[root@db02 mysql-5.6.36]# cd /application/mysql/scripts/
#初始化MySQL
[root@db02 mysql-5.6.36]# ./mysql_install_db --basedir=/application/mysql/ --datadir=/application/mysql/data --user=mysql
#授权
[root@db02 mysql-5.6.36]# chown -R mysql.mysql /application/mysql/
#给启动脚本授权700
[root@db02 mysql-5.6.36]# chmod 700 /etc/init.d/mysqld
#systemd管理mysql启动
[root@db02 mysql-5.6.36]# vim /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=https://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
#设置开机自启动C6
[root@db02 mysql-5.6.36]# chkconfig mysqld on
#设置开机自启动C7
[root@db02 mysql-5.6.36]# systemctl enable mysqld
#启动MySQLC6
[root@db02 mysql-5.6.36]# /etc/init.d/mysqld start
#启动MySQLC7
[root@db02 mysql-5.6.36]# systemctl start mysqld
#创建tmp目录（5.6.36版本不会自动创建tmp目录）
[root@db02 mysql-5.6.36]# mkdir /application/mysql-5.6.36/tmp
#添加环境变量
[root@db02 ~]# echo 'PATH=/application/mysql/bin/:$PATH' >>/etc/profile
#个人推荐
[root@db02 ~]# echo 'PATH=/application/mysql/bin/:$PATH' >/etc/profile.d/mysql.sh
[root@db02 ~]# source /etc/profile.d/mysql.sh
#设置MySQL密码
[root@db02 ~]# mysqladmin -uroot password 'oldboy123'
#MySQL登陆
[root@db02 ~]# mysql -uuser -ppassword -Ssocket -hhost
#MySQL基本操作及基本优化
#查看库
mysql> show databases;
#删库
mysql> drop database test;
#使用库
mysql> use mysql
#查看表
mysql> show tables;
#查看当前所在库
mysql> select database();
#查看mysql用户
mysql> select user,host from user;
mysql> select user,host,password from user;
#删除用户
mysql> select user,host from user;
mysql> drop user ''@'db02';
mysql> drop user root@db02;
mysql> drop user root@'::1';
mysql> drop user root@'127.0.0.1';

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
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
1.解压安装包
[root@db02 ~]# tar xf mysql-5.6.40-linux-glibc2.12-x86_64.tar.gz 

2.创建MySQL安装目录
[root@db02 ~]# mkdir /application

3.移动MySQL程序到安装目录下并改名
[root@db02 ~]# mv mysql-5.6.40-linux-glibc2.12-x86_64 /application/mysql-5.6.40

4.做软连接
[root@db02 ~]# ln -s /application/mysql-5.6.40 /application/mysql

5.进入其他文件目录
[root@db02 ~]# cd /application/mysql/support-files

6.拷贝配置文件
[root@db02 support-files]# cp my-default.cnf /etc/my.cnf		##覆盖

7.拷贝启动脚本
[root@db02 support-files]# cp mysql.server /etc/init.d/mysqld

8.进入初始化目录
[root@db02 support-files]# cd ../scripts/

9.创建MySQL用户
[root@db02 scripts]# useradd mysql -s /sbin/nologin -M

10.安装初始化依赖包
[root@db02 scripts]# yum install -y autoconf libaio-devel

11.初始化
[root@db02 scripts]# ./mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data

12.修改文件路径
[root@db02 scripts]# sed -i 's#/usr/local#/application#g' /etc/init.d/mysqld /application/mysql/bin/mysqld_safe

13.启动MySQL
[root@db02 scripts]# /etc/init.d/mysqld start

14.添加环境变量
[root@db02 scripts]# vim /etc/profile.d/mysql.sh
export PATH="/application/mysql/bin:$PATH"

15.给root用户设置密码
[root@db02 scripts]# mysqladmin -uroot -p password '123'

16.连接MySQL
[root@db01 ~]# mysql -uroot -p123

17.查看MySQL中所有用户
mysql> select user,host from mysql.user;

18.删除用户
mysql> drop user root@'db01';
mysql> drop user root@'::1';
mysql> drop user ''@'db01';
mysql> drop user ''@'localhost';
mysql> drop user root@'localhost';
mysql> drop user root@'127.0.0.1';

使用systemd管理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
使用systemd管理MySQL
[root@db01 ~]# vim /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=https://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000

[root@db01 ~]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS! 
[root@db01 ~]# systemctl start mysqld

[root@db01 ~]# systemctl enable mysqld

[root@db01 ~]# vim /etc/my.cnf
basedir = /application/mysql
datadir = /application/mysql/data

给root用户设置密码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
###给root用户设置密码
[root@db02 scripts]# mysqladmin -uroot -p password '123'

###连接MySQL
[root@db02 scripts]# mysql -uroot -p123

###查看MySQL中用户
mysql> select user,host from mysql.user;

###删除用户
mysql> drop user root@'db01';
mysql> drop user root@'::1';

mysql> drop user ''@'db01';
msyql> drop user ''@'localhost';

01.误删除root用户解决方法

​ 方法一：

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
1.停止数据库
[root@db02 scripts]# /etc/init.d/mysqld stop

2.跳过授权表，跳过网络启动数据库
[root@db02 scripts]# mysqld_safe --skip-grant-tables --skip-networking &

3.连接数据库
[root@db02 scripts]# mysql

4.插入新root用户
insert into mysql.user values ('localhost','root',PASSWORD('123'),
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'',
'',
'',
'',0,0,0,0,'mysql_native_password','','N');

5.重启MySQL
[root@db01 ~]# mysqladmin shutdown 
[root@db01 ~]# /etc/init.d/mysqld start

​ 方法二：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
1.停止数据库
[root@db02 ~]# /etc/init.d/mysqld stop

2.跳过授权表，跳过网络启动数据库
[root@db02 ~]# mysql_safe --skip-grant-tables --skip-networking &

3.连接数据库
[root@db02 ~]# mysql

4.刷新授权表
mysql> flush pribileges;

5.创建root超级用户
mysql> grant all on *.* to root@'localhost' identfied by '123' with grant option;

02.客户端与服务器模型

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
1.mysql是一个典型的C/S服务结构
	mysql自带的客户端程序(/application/mysql/bin)
		mysql
		mysqladmin
		mysqldump
		
2.mysqld是一个二进制程序，后台的守护进程
		单进程
		多线程

3.应用程序连接MySQL的方式
	a.TCP/IP连接
		mysql -uroot -p123 -h127.0.0.1（TCP/IP）
		1）并不是所有的-h都是TCP/IP连接
		2）所有的远程连接都是TCP/IP连接
	b.socket连接
		mysql -uroot -p123 -s /tmp/mysql.sock
		mysql -uroot -p123
		mysql -uroot -p123 -hlocalhost
		1）MySQL默认连接方式是socket连接
		2）socket连接速度快，因为不需要建立三次握手

03.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
什么是实例
	1.MySQL的后台进程+线程+与分配的内存结构
	2.MySQL在启动的过程中会启动后台守护进程，并生成工作线程，与分配内存结构供MySQL处理数据使用
	
mysqld服务器程序结构
	1.连接层做的：
		a.验证用户的合法性(ip,端口,用户名,密码)
		b.提供两种连接方式(socket,TCP/IP)
		c.提供一个与SQL层交互的专用线程
	
	2.SQL层做的：
		a.接收连接层传来的SQL语句
		b.检查语法
		c.检查语义。（DDL，DML）DQL，DCL
		  检查：show，select，grant
		d.解释器，解析SQL语句，生成多种执行计划
		e.优化器，根据多种执行计划，选择最优方式
		f.执行器，执行优化器传来的最优方式SQL
			提供跟存储引擎层交互的线程
			接收返回数据，优化成表的形式返回SQL
		g.将数据存入缓存
		h.记录日志，binlog
	
	3.存储引擎层做的：
		a.接收SQL层传来的SQL语句
		b.去磁盘找到要找的数据
		c.提供一个与SQL交互的线程，返回SQL层，结构化成表的形式

04.MySQL的结构

1
2
3
4
5
6
7
8
9
10
11
12
13
	1.库
	2.表：元数据+真实数据行
	3.元数据：列+其它属性（行数+占用空间大小+权限）
	4.列：列名字+数据类型+其他约束（非空、唯一、主键、非负数、自增长、默认值）
	
MySQL逻辑结构与Linux系统对比
MySQL						 Linux
库							目录
show databases;				 ls -l /
use mysql					 cd /mysql
表							文件
show tables;				 ls
二维表=元数据+真实数据行		  文件=文件名+文件属性

windows mysql数据库安装

直接解压

在本地设置MYSQL账号

创建my.ini文件

1
2
3
4
5
6
7
8
9
[mysqld]
character-set-server=utf8 #库的编码模式设置为utf8
collation-server=utf8_general_ci
[client]
default-character-set=utf8
user=root # 账号名为root
[mysql]
default-character-set=utf8 # 每创建一个库,默认的编码为utf8
将MYSQL安装的目标文件添加到环境变量中,在CMD中直接操作MYSQL数据库.打开MYSQL时无需输入账号密码,默认从配置文件my.ini中读取

将模式设置为严格模式

1
2
3
4
set global sql_mode= "strict_trans_tables,only_full_group_by"; #修改为严格模式,只能查看到分组后的字段,配合聚合函数.其余操作会报错
例如:
select * from emo group by post;  #会报错,原因是分组之后在去查看某一条数据毫无意义
强调: 模式设置为严格模式需要重新启动MYSQL
1
2
3
ps：修改sql_mode为严格模式，必须重启客户端才能生效
    set global sql_mode="strict_trans_tables";
    select @@sql_mode;

注意

1553158125334

将MYSQL设置为后台自动运行

img

数据库服务器中存放的是

库(文件加) 表(文件) 表里面是记录(一行数据)

破解mysql密码,跳过配置文件

1 关闭mysql

1553158668557

2 重新启动

1553158709865

3 输入命令

1553158727305

4 关闭MySQL,正常启动

1553158742067

账号的创建(了解知识点)

跟权限有关的几张表

user,db,table_priv,columns_priv

只创建账号

1553158824495

创建账号并且授权（只有root账号才能为其他账号授权grant）

1553158838749

删除账号

1553158874771

MYSQL原生态SQL语句

注释

commnt

SQL语句规范第一条:

CREATE DATABASE oldboy CHARSET utf8mb4;

1.关键字大写(非必须),字面量小写(必须)

2.库名字,只能是小写,不能有数字开头,不能是预留关键字

3.库名字必须和业务名有关,例如his_user;

4.必须加字符集.

​ 表
CREATE TABLE
DROP TABLE
ALTER TABLE

CREATE TABLE t1 (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT ‘用户ID’,
sname VARCHAR(20) NOT NULL COMMENT ‘用户姓名’,
gender ENUM(‘f’,’m’,’u’) NOT NULL DEFAULT ‘u’ COMMENT ‘用户性别’,
telnum CHAR(11) NOT NULL UNIQUE COMMENT ‘手机号’,
tmdate DATETIME NOT NULL DEFAULT NOW() COMMENT ‘录入时间’
)ENGINE INNODB CHARSET utf8mb4;

DDL数据定义语言(创建) DCL数据控制语言 DML数据操作语言(增,删,改) DQL数据查询

库(create/drop/alter/show)

就是一个文件夹

创建规则

1
2
3
4
5
6
可以由字母、数字、下划线、＠、＃、＄
区分大小写
唯一性
不能使用关键字如 create select
不能单独使用数字
最长128位

增

1
2
3
create database 数据库名字 charset 编码格式（不需要加引号）;
实例：
mysql> create database zhang charset utf8;

1553159072394

删除

1
2
3
4
删：
drop database 数据库名;
实例：
mysql> drop database vv;

1553159108805

改

1
2
3
4
改：
alter database 需要修改的数据库名字 charset 字符编码;
实例：
mysql> alter database zhang charset utf8;

1553159119304

查

查所有库

1
2
3
4
5
6
7
8
9
10
11
12
13
show datebases; 
		实例：
		mysql> show databases;
		+--------------------+
		| Database           |
		+--------------------+
		| information_schema |
		| mysql              |
		| performance_schema |
		| test               |
		| zhang              |
		+--------------------+
		5 rows in set (0.00 sec)

查已经创建的库

1
2
3
4
5
6
7
8
9
show create database 创建的数据库名字;
		实例：
		mysql> show create database zhang;
		+----------+---------------------------------------------------------------+
		| Database | Create Database                                               |
		+----------+---------------------------------------------------------------+
		| zhang    | CREATE DATABASE `zhang` /*!40100 DEFAULT CHARACTER SET gbk */ |
		+----------+---------------------------------------------------------------+
		1 row in set (0.00 sec)

表的操作首先要选择库文件

进入库中

选择当前所在的库

1
2
3
use db1;

select database(); 选择当前所在的库

表(create/drop;truncate/alter/show;desc)

增：

不需要切换

1
creat table db1.t1(id int,name char);

需要切换use

1
2
3
4
5
create table 表名（attr1 type,attr2 type......）;
实例：
mysql> use zhang
Database changed
mysql> create table f1(id int(11),name char(4));

1553161619658

删：(彻底)

1
2
3
drop table 表名;
实例：
mysql> drop table f2;

删:( 注意：只是清空表内内容，表结构还在)

1
2
3
truncate table 表名; 
实例：
mysql> truncate table f2;

清空表的时候应该用truncate，不会保留id字段

改：

1.添加属性(字段)

1
2
3
alter table 表名 add 属性 类型;  
		实例：
		mysql> alter table f1 add sex char(11);

2.删除属性(字段)

1
2
3
alter table 表名 drop 属性;     
		实例：
		mysql> alter table f1 drop age;

3.改表属性的数据类型(字段)

1
2
3
alter table 表名 modify addr 新类型; 
		实例：
		mysql> alter table f1 modify name char(5);

4.覆盖修改表中的某个属性(字段)

1
2
3
alter table 表名 change 老属性 新属性 新属性的类型;   
		实例：
		mysql> alter table f1 change sex mysex char(4);

5.改表的字符编码

1
2
3
alter table 表名 charset utf8;
		实例：
		mysql> alter table f1 charset gbk;

6.修改表名

1
2
3
rename table 表名 to 新表名; 
		实例：
		mysql> rename table f1 to f2;

查：

1.查所有的表

1
2
3
4
5
6
7
8
9
10
1.show tables;
	实例：
	mysql> show tables
	-> ;
	+-----------------+
	| Tables_in_zhang |
	+-----------------+
	| f1              |
	+-----------------+
	1 row in set (0.00 sec)

2.查创建的表

1
2
3
4
5
6
7
8
9
10
11
12
13
2.show create table 表名;
	实例：
	mysql> show create table f1;
	+-------+----------------------------------------------------------------------+
	| Table | Create Table                                                                                                                                                                          |
	+-------+----------------------------------------------------------------------+
	| f1    | CREATE TABLE `f1` (
	  `id` int(11) DEFAULT NULL,
	  `name` char(5) CHARACTER SET utf8 DEFAULT NULL,
	  `mysex` char(4) CHARACTER SET utf8 DEFAULT NULL
	) ENGINE=InnoDB DEFAULT CHARSET=gbk |
	+-------+----------------------------------------------------------------------+
	1 row in set (0.00 sec)

3.查表结构

1
2
3
4
5
6
7
8
9
10
11
12
3.desc 表名;
	实例：
	mysql> desc f1
		-> ;
	+-------+---------+------+-----+---------+-------+
	| Field | Type    | Null | Key | Default | Extra |
	+-------+---------+------+-----+---------+-------+
	| id    | int(11) | YES  |     | NULL    |       |
	| name  | char(5) | YES  |     | NULL    |       |
	| mysex | char(4) | YES  |     | NULL    |       |
	+-------+---------+------+-----+---------+-------+
	3 rows in set (0.02 sec)

拷贝

1
2
3
4
5
6
create table copy_table select *from customer ;
拷贝结构 与数据
create table copy_table select *from customer where 0 > 1;
仅拷贝结构

共同点： 索引 不能拷贝   描述不能拷贝（自增）

1553161969018

查看当前所在库

1
select batabase;

记录(insert/delete/update/select)

增：

1
2
3
4
5
6
添加多个记录，记录的类型必须和表属性一致
insert into 表名 value（rec1 , rec2 ......）
insert into 表名 values（rec1 , rec2 ......），（rec1 , rec2 ......）

实例：
mysql> insert into f2 values(1,'zcg','male');

1553162538854

删：use

1
2
3
4
5
6
7
8
delete from 表名 （where 条件）;没有条件就全删

实例：
mysql> delete from f2;
Query OK, 2 rows affected (0.35 sec)

mysql> select * from f2;
Empty set (0.00 sec)

不use

1553162646852

改: 没有条件就全删

1
2
3
4
update 表名 set addr=rec (where 条件）; 没有条件就全删

实例:
mysql> update f2 set name='ljy' where id=1;

查：

查所有

1
2
3
4
5
6
7
8
9
10
1.select * from 表名;
		实例：
		mysql> select * from f2;
		+------+------+-------+
		| id   | name | mysex |
		+------+------+-------+
		|    1 | zcg  | male  |
		|    2 | wjs  | fema  |
		+------+------+-------+
		2 rows in set (0.00 sec)

查一个单个属性

1
2
3
4
5
6
7
8
9
10
2.select 表属性 from 表名;
		实例：
		mysql> select id from f2;
		+------+
		| id   |
		+------+
		|    1 |
		|    2 |
		+------+
		2 rows in set (0.00 sec)

1553161149276

创建表的完整语法

1553162959359

数据类型

1 整形

默认为有符号，即数字前有正负号

可以设置create table t4(x tinyint unsigned);无符号类型

强调：

整型类型后面的宽度限制的根本不是存储宽度，限制的是显示宽度

create table t5(id int(1));
create table t6(id int(5));

2.浮点型

三种格式的精度不一样

float(255,30)
double(255,30)
decimal(65,30)

格式

1
2
3
create table t8(x float(255,30));
create table t9(x double(255,30));
create table t10(x decimal(65,30));

3 日期类型

year

1
2
3
4
5
6
7
8
9
10
11
12
13
14
============year===================

create table t_year(born_year year);
insert into  t_year values (1901),
                           (2155);

select * from t_year;
+-----------+
| born_year |
+-----------+
|      1901 |
|      2155 |
+-----------+
2 rows in set (0.00 sec)

date time datetime

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
============date,time,datetime===========

create table t_mul(d date,t time,dt datetime);

insert into t_mul values(now(),now(),now());

select * from t_mul;


mysql> select * from t_mul;
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2017-08-01 | 19:42:22 | 2017-08-01 19:42:22 |
+------------+----------+---------------------+
1 row in set (0.00 sec)

timestamp

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create table t_stamp(t TIMESTAMP);

insert into t_stamp values();
insert into t_stamp values(NULL );

select * from t_stamp;

+---------------------+
| t                   |
+---------------------+
| 2017-08-01 19:46:24 |
| 2017-08-01 19:46:24 |
+---------------------+
2 rows in set (0.00 sec)

实际场景

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
/*  
    在实际应用的很多场景中，MySQL的这两种日期类型都能够满足我们的需要，存储精度都为秒，
    但在某些情况下，会展现出他们各自的优劣。下面就来总结一下两种日期类型的区别。

1.DATETIME的日期范围是1001——9999年，TIMESTAMP的时间范围是1970——2038年。

2.DATETIME存储时间与时区无关，TIMESTAMP存储时间与时区有关，显示的值也依赖于时区。
  在mysql服务器，操作系统以及客户端连接都有时区的设置。

3.DATETIME使用8字节的存储空间，TIMESTAMP的存储空间为4字节。因此，TIMESTAMP比DATETIME的
  空间利用率更高。

4.DATETIME的默认值为null；TIMESTAMP的字段默认不为空（not null）,默认值为当前时间
 （CURRENT_TIMESTAMP），如果不做特殊处理，并且update语句中没有指定该列的更新值，则默
  认更新为当前时间。
  
  */
1
2
3
4
year 1999  #年
date 1999-11-11#年月日
time 08:30:00#时分秒
datetime/timestamp 1999-11-11 08:30:00#年月日时分秒

实列

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
create table student(
    id int primary key auto_increment,
    name char(16),
    born_year year,
    birth date,
    class_time time,
    reg_time datetime
);
insert into student(name,born_year,birth,class_time,reg_time) values
('egon1',now(),now(),now(),now());

insert into student(name,born_year,birth,class_time,reg_time) values
('egon1',2000,20001111,now(),now());

insert into student(name,born_year,birth,class_time,reg_time) values
('egon1',2000,'2000-11-11',083000,now());

insert into student(name,born_year,birth,class_time,reg_time) values
('egon1',2000,'2000-11-11',"08:30:00",20171111111111);

insert into student(name,born_year,birth,class_time,reg_time) values
('egon1',2000,'2000-11-11',"08:30:00","2017-11-11 11:11:11");

create table t11(x timestamp);
create table t12(x datetime not null default now());

timestamp与datetime的区别

1553163448508

4.字符类型

注意：宽度指限制的是字符个数

1
2
3
4
char：定长
    char(5)
varchar：变长
    varchar(5) #一般用变长

相同点宽度指的都是最大存储的字符个数，超过了都无法正常存储

1
2
3
4
5
6
7
8
如何选择
    char
        存取效率高
        浪费存储空间
    varchar
        存取效率低于char
        节省存储空间
    使用起来感受不到区别 通常用的是char

1553163633944

设置

1553163693858

注意点

1553163711210

5 枚举与集合类型

字段的值只能在给定范围中选择，如单选框，多选框 enum 单选 只能在给定的范围内选一个值，如性别 sex 男male/女female set 多选 在给定的范围内可以选择一个或一个以上的值（爱好1,爱好2,爱好3…）

先创建

1553163944717

枚举enum:多选一

1553163959431

集合set: 多选多

1553163967151

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
/*
枚举类型（enum）
An ENUM column can have a maximum of 65,535 distinct elements. 
(The practical limit is less than 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）
A SET column can have a maximum of 64 distinct members.
示例：
    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');
                                    
                                    
*/

concat(字符串相加)

1
2
insert into s1 values(n,concat('egon',n),
       'male',concat('egon',n,'@163.com'));请看存储过程

表的约束

not null与default

1553164228333

unique key唯一

创建方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
方法一：
create table t2(x int unique); #设置x的值唯一，不能重复
方法二：
create table t3(
    x int,
    y varchar(5),
    unique key(x)
);
方法三
create table t4(
    x int,
    y varchar(5),
    constraint uni_x unique key(x)#可以设置唯一的键为uni_x但没什么用，只是表内部的一种表现方式
);

联合唯一

1
2
3
4
5
6
7
8
9
联合唯一
create table service(
    ip varchar(15),
    port int,
    unique key(ip,port)#两者联合起来唯一就可以
);
insert into service values
('1.1.1.1',3306),
('1.1.1.1',3306);

primary key(主键)

站在约束角度看primary key=not null unique不为空且唯一 ，另外还有索引优化

注意点

以后但凡建表，必须注意：
1、必须有且只有一个主键
2、通常是id字段被设置为主键

写法

1
2
3
4
create table t5(
    id int primary key auto_increment
	#一般表的固定写法，设置id为主键，且自增长
);

foreign key

外键：用来在表与表之间建立关系的。限制关联表某一个字段的值必是来自于被关联表的一个字段的

注意

1、被关联的字段必须是一个key，通常是id字段

2、创建表时：必须先建立被关联的表，才能建立关联表

3、插入记录时：必须先往被关联的表插入记录，才能往关联表中插入记录

创建表2个相互关联的表

references是指向的意思

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
创建表时：必须先建立被关联的表，才能建立关联表
create table dep(
    id int primary key auto_increment,
    dname varchar(20),
    info varchar(50)
);
create table emp(
    id int primary key auto_increment,
    name varchar(15),
    age int,
    dep_id int,
    foreign key(dep_id) references dep(id)
    on update cascade
    on delete cascade
);

插入2个相互关联表的数据

1
2
3
4
5
6
7
8
9
10
insert into dep(dname,info) values
('IT','技术能力有限部门xxx'),
('Sale','文化程度不高'),
('HR','招不到人部门');
insert into emp(name,age,dep_id) values
('egon',18,1),
('alex',28,2),
('wsj',38,2),
('lxx',30,1),
('xiaohou',18,3);

删除表

ps:删除时：应该先删除关联表emp中的记录，再删除被关联表对应的记录

找两张表的关系的窍门

1553165055967

1553165063303

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create table author(
    id int primary key auto_increment,
    name varchar(16),
    age int
);
create table book(
    id int primary key auto_increment,
    bname varchar(20),
    price int
);
create table author2book(
    id int primary key auto_increment,
    author_id int,
    book_id int,
    foreign key(author_id) references author(id) on update cascade on delete cascade,
    foreign key(book_id) references book(id) on update cascade on delete cascade
);

一对一:

无需参考窍门，左表的一条数据唯一对应右表的一条记录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
一对一 ******
生活中的一对一
    客户表， 学员表
    通过分析
    一个客户只对应一个学员
    一个学员只对应一个客户
    所以确定关系为一对一

    在mysql中通过外键来建立一对一

    create table customer(id int primary key auto_increment,name char(10),phone char(11),sex char(1));
    create table student(id int primary key auto_increment,name char(10),class char(11),sex char(1),c_id int,
    foreign key(c_id) references  customer(id) on update cascade on delete cascade
    );

补充存储引擎

1553164641671

1
2
3
4
5
6
7
8
# 补充存储引擎：
create table t12(x int)engine='myisam';
#不支持事务、表锁设计、支持全文索引,主要面向一些 OLAP 数 据库应用
create table t13(x int)engine='innodb';#一般常用
create table t14(x int)engine='memory';
#存储在内存当中，服务器断开或者断电丢失，无法保存
create table t15(x int)engine='blackhole';
#这个是黑洞模式，无法保存数据

单表查询

准备的表和数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE TABLE emp(
    id       INT PRIMARY KEY AUTO_INCREMENT,
    name     VARCHAR(20),
    gender   ENUM("male","female","other"),
    age      TINYINT,
    dep      VARCHAR(20),
    city     VARCHAR(20),
   salary    DOUBLE(7,2)
);


INSERT INTO emp (name,gender,age,dep,city,salary) VALUES
                ("yuan","male",24,"教学部","河北省",8000),
                ("egon","male",34,"保安部","山东省",8000),
                ("alex","male",28,"保洁部","山东省",10000),
                ("景丽阳","female",22,"教学部","北京",9000),
                ("张三", "male",24,"教学部","河北省",6000),
                ("李四", "male",32,"保安部","北京",12000),
                ("王五", "male",38,"教学部","河北省",7000),
                ("赵六", "male",19,"保安部","河北省",9000),
                ("猪七", "female",24,"保洁部","北京",9000);

SELECT  * FROM emp;

产生

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT  * FROM emp;
+----+-----------+--------+------+-----------+-----------+----------+
| id | name      | gender | age  | dep       | city      | salary   |
+----+-----------+--------+------+-----------+-----------+----------+
|  1 | yuan      | male   |   24 | 教学部    | 河北省    |  8000.00 |
|  2 | egon      | male   |   34 | 保安部    | 山东省    |  8000.00 |
|  3 | alex      | male   |   28 | 保洁部    | 山东省    | 10000.00 |
|  4 | 景丽阳    | female |   22 | 教学部    | 北京      |  9000.00 |
|  5 | 张三      | male   |   24 | 教学部    | 河北省    |  6000.00 |
|  6 | 李四      | male   |   32 | 保安部    | 北京      | 12000.00 |
|  7 | 王五      | male   |   38 | 教学部    | 河北省    |  7000.00 |
|  8 | 赵六      | male   |   19 | 保安部    | 河北省    |  9000.00 |
|  9 | 猪七      | female |   24 | 保洁部    | 北京      |  9000.00 |
+----+-----------+--------+------+-----------+-----------+----------+
9 rows in set (0.00 sec)

练习单表查询

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
-- 查询男女员工各有多少人

 

SELECT gender 性别,count(*) 人数 FROM emp5 GROUP BY gender;

 

-- 查询各个部门的人数

 

SELECT dep 部门,count(*) 人数 FROM emp5 GROUP BY dep;

 

-- 查询每个部门最大的年龄

 

SELECT dep 部门,max(age) 最大年纪 FROM emp5 GROUP BY dep;

 

-- 查询每个部门年龄最大的员工姓名

 

SELECT * FROM emp5 WHERE age in (SELECT max(age) FROM emp5 GROUP BY dep);

 

-- 查询每个部门的平均工资

 

SELECT dep 部门,avg(salary) 最大年纪 FROM emp GROUP BY dep;

 

--  查询教学部的员工最高工资:

 

SELECT dep,max(salary) FROM emp11 GROUP BY dep HAVING dep="教学部";

 

-- 查询平均薪水超过8000的部门

 

SELECT dep,AVG(salary) FROM  emp GROUP BY dep HAVING avg(salary)>8000;

 

--  查询每个组的员工姓名

SELECT dep,group_concat(name) FROM emp GROUP BY dep;

 

-- 查询公司一共有多少员工(可以将所有记录看成一个组)

 

SELECT COUNT(*) 员工总人数 FROM emp;

 

　　　　　　　　　　　　      -- KEY: 查询条件中的每个后的词就是分组的字段

步骤

1
2
3
4
5
6
7
8
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
select distinct 字段1,字段2,字段3,... from 库名.表名
                    where 约束条件
                    group by 分组依据
                    having 过滤条件
                    order by 排序的字段
                    limit 限制显示的条数


# 必须要有的关键字如下：
select * from t1;

关键字的执行顺序

1
2
3
4
5
6
7
8
9

# 关键字执行的优先级
from 打开文件
where #表的全局帅选
group by#分组
having  #分组之后的过滤
distinct  #去重
order by # 排序
limit  #限制条目

单表简单的查询语法

1553165885606

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select * from t1;
select id,name,sex from t1;

select distinct post from emp;
将表中的部门去重
select name,salary*12 as annual_salary from emp;
单表可以进行四则运算
select concat('名字: ',name) as new_name,concat("年龄: ",age) as new_age from emp;

#定义显示格式
#可将查询结果进行一个拼接显示
select concat(name,":",age) from emp;
select concat(name,":",age,":",sex) from emp;
select concat_ws(":",name,age,sex) as info from emp;
concat_ws#可以进行一个格式化的操作

where和between和in

1553165981228

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
where字句中可以使用：

1. 比较运算符：> < >= <= <> !=
2. between 80 and 100 值在10到20之间
3. in(80,90,100) 值是10或20或30
4. like 'egon%'
    pattern可以是%或_，
    %表示任意多字符
    _表示一个字符 
5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not

select * from emp where id >= 10 and id <=15;
 # 等同于select * from emp where id between 10 and 15;
select * from emp where id = 6 or id = 9 or id = 12; 
# 等同于select * from emp where id in (6,9,12);

group by 分组

什么是分组

1
2
3
4
5
6
7
8
9
10
11
#3、group by分组
# 什么分组：按照所有记录相同的部分进行归类，一定区分度低的字段
# 为何要分组：当我们要以组为单位进行统计时就必须分组，分组的目的是为了以组为单位进行统计的，再去考虑单条记录毫无意义
# set global sql_mode="strict_trans_tables,only_full_group_by";
# 注意:分组之后，只能查到分组的字段以及组内多条记录聚合的成果


小窍门：‘每’这个字后面的字段，就是我们分组的依据


可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助于聚合函数

ONLY_FULL_GROUP_BY

1
2
3
4
ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义，简单的说来，在ONLY_FULL_GROUP_BY模式下，target list中的值要么是来自于聚集函数的结果，要么是来自于group by list中的表达式的值。

设置方式
 set global sql_mode='ONLY_FULL_GROUP_BY';

聚合函数(和分组搭配使用)

1553166173976

用法

1553166187928

1
2
3
4
5
6
7
8
9
10
11
SELECT COUNT(*) FROM employee;
    SELECT COUNT(*) FROM employee WHERE depart_id=1;
    SELECT MAX(salary) FROM employee;
    SELECT MIN(salary) FROM employee;
    SELECT AVG(salary) FROM employee;
    SELECT SUM(salary) FROM employee;
    SELECT SUM(salary) FROM employee WHERE depart_id=3;



select post,group_concat(name) from employee group by post;

having 和where的区别

1
2
3
4
#！！！执行优先级从高到低：where > group by > having 
#1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。

#2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数

1553166377983

is null

判断是否是空

1
2
3
4
5
6
7
8
9
10
11
12
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
  ps：
      执行
      update employee set post_comment='' where id=2;
      再用上条查看，就会有结果了

order by 排序

asc升序,desc降序

1553166493892

1
2
3
select * from emp order by age asc; # 默认asc升序-》从小到大

select * from emp order by age desc;# desc降序-》从大到小

limit 限制显示的条件限制显示条目

1553166656249

1
2
3
4
5
#6、limit 限制显示的条件
select * from emp limit 3;

#薪资最高那个人的详细信息
select * from emp order by salary desc limit 1;
1
2
3
# 分页显示
select * from emp limit 0,5; # 从0开始往后取5条
select * from emp limit 5,5; #从5开始往后取5条

正则表达式

1
2
#正则表达式
select * from emp where name regexp "^jin.*(g|n)$";

1553166829738

多表查询

创建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATE TABLE emp(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    salary DOUBLE(7,2),
    dep_id INT
);

INSERT INTO emp (name,salary,dep_id) VALUES ("张三",8000,2),
                                            ("李四",12000,1),
                                            ("王五",5000,2),
                                            ("赵六",8000,3),
                                            ("猪七",9000,1),
                                            ("周八",7000,4),
                                            ("蔡九",7000,2);

CREATE TABLE dep(
      id INT PRIMARY KEY AUTO_INCREMENT,
      name VARCHAR(20)
);


INSERT INTO dep (name) VALUES ("教学部"),
                              ("销售部"),
                              ("人事部");

产生结果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> select * from emp;
+----+--------+----------+--------+
| id | name   | salary   | dep_id |
+----+--------+----------+--------+
|  1 | 张三   |  8000.00 |      2 |
|  2 | 李四   | 12000.00 |      1 |
|  3 | 王五   |  5000.00 |      2 |
|  4 | 赵六   |  8000.00 |      3 |
|  5 | 猪七   |  9000.00 |      1 |
|  6 | 周八   |  7000.00 |      4 |
|  7 | 蔡九   |  7000.00 |      2 |
+----+--------+----------+--------+
7 rows in set (0.00 sec)

mysql> select * from dep;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 教学部    |
|  2 | 销售部    |
|  3 | 人事部    |
+----+-----------+
3 rows in set (0.00 sec)

笛卡尔积

条件(2张表有对应关系)

简单粗暴的对应关系

1
2
3
4
5
select * from emp,dep;

select * from emp,dep where emp.dep_id = dep.id;

select * from emp,dep where emp.dep_id = dep.id and dep.name = "技术";

select * from emp,dep;

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
mysql> select * from emp,dep;
+----+--------+----------+--------+----+-----------+
| id | name   | salary   | dep_id | id | name      |
+----+--------+----------+--------+----+-----------+
|  1 | 张三   |  8000.00 |      2 |  1 | 教学部    |
|  1 | 张三   |  8000.00 |      2 |  2 | 销售部    |
|  1 | 张三   |  8000.00 |      2 |  3 | 人事部    |
|  2 | 李四   | 12000.00 |      1 |  1 | 教学部    |
|  2 | 李四   | 12000.00 |      1 |  2 | 销售部    |
|  2 | 李四   | 12000.00 |      1 |  3 | 人事部    |
|  3 | 王五   |  5000.00 |      2 |  1 | 教学部    |
|  3 | 王五   |  5000.00 |      2 |  2 | 销售部    |
|  3 | 王五   |  5000.00 |      2 |  3 | 人事部    |
|  4 | 赵六   |  8000.00 |      3 |  1 | 教学部    |
|  4 | 赵六   |  8000.00 |      3 |  2 | 销售部    |
|  4 | 赵六   |  8000.00 |      3 |  3 | 人事部    |
|  5 | 猪七   |  9000.00 |      1 |  1 | 教学部    |
|  5 | 猪七   |  9000.00 |      1 |  2 | 销售部    |
|  5 | 猪七   |  9000.00 |      1 |  3 | 人事部    |
|  6 | 周八   |  7000.00 |      4 |  1 | 教学部    |
|  6 | 周八   |  7000.00 |      4 |  2 | 销售部    |
|  6 | 周八   |  7000.00 |      4 |  3 | 人事部    |
|  7 | 蔡九   |  7000.00 |      2 |  1 | 教学部    |
|  7 | 蔡九   |  7000.00 |      2 |  2 | 销售部    |
|  7 | 蔡九   |  7000.00 |      2 |  3 | 人事部    |
+----+--------+----------+--------+----+-----------+
21 rows in set (0.00 sec)

内连接: 只取两张表有对应关系的记录

1
2
3
4
5
6
#2、内连接：只取两张表有对应关系的记录
select * from emp inner join dep on emp.dep_id = dep.id;


select * from emp inner join dep on emp.dep_id = dep.id
                       where dep.name = "技术";

1553172129044

select * from emp inner join dep on emp.dep_id = dep.id;

1
2
3
4
5
6
7
8
9
10
11
+----+--------+----------+--------+----+-----------+
| id | name   | salary   | dep_id | id | name      |
+----+--------+----------+--------+----+-----------+
|  1 | 张三   |  8000.00 |      2 |  2 | 销售部     |
|  2 | 李四   | 12000.00 |      1 |  1 | 教学部     |
|  3 | 王五   |  5000.00 |      2 |  2 | 销售部     |
|  4 | 赵六   |  8000.00 |      3 |  3 | 人事部     |
|  5 | 猪七   |  9000.00 |      1 |  1 | 教学部     |
|  7 | 蔡九   |  7000.00 |      2 |  2 | 销售部     |
+----+--------+----------+--------+----+-----------+
6 rows in set (0.00 sec)

这时，我们就可以利用两张表中所有的字段进行查询了 

1
2
3
4
5
6
7
8
9
-- 查询李四所在的部门名称

SELECT emp.name,dep.name FROM  emp INNER JOIN dep ON emp.dep_id=dep.id WHERE emp.name="李四";

-- 查询销售部所有员工姓名以及部门名称

-- SELECT name FROM  emp WHERE dep_id in (SELECT id FROM dep WHERE name="销售部");

SELECT emp.name,dep.name FROM  emp INNER JOIN dep ON emp.dep_id=dep.id WHERE dep.name="销售部";

左连接: 在内连接的基础上保留左表没有对应关系的记录

1
select * from emp left join dep on emp.dep_id = dep.id;

1553172147156

select * from emp left join dep on emp.dep_id = dep.id;

查询结果

1
2
3
4
5
6
7
8
9
10
11
12
+----+--------+----------+--------+------+-----------+
| id | name   | salary   | dep_id | id   | name      |
+----+--------+----------+--------+------+-----------+
|  2 | 李四   | 12000.00 |      1 |    1 | 教学部    |
|  5 | 猪七   |  9000.00 |      1 |    1 | 教学部    |
|  1 | 张三   |  8000.00 |      2 |    2 | 销售部    |
|  3 | 王五   |  5000.00 |      2 |    2 | 销售部    |
|  7 | 蔡九   |  7000.00 |      2 |    2 | 销售部    |
|  4 | 赵六   |  8000.00 |      3 |    3 | 人事部    |
|  6 | 周八   |  7000.00 |      4 | NULL | NULL      |
+----+--------+----------+--------+------+-----------+
7 rows in set (0.00 sec)

右连接: 在内连接的基础上保留右表没有对应关系的记录

1
select * from emp right join dep on emp.dep_id = dep.id;

1553172157453

select * from emp right join dep on emp.dep_id = dep.id;

1
2
3
4
5
6
7
8
9
10
11
12
mysql> SELECT * FROM emp RIGHT JOIN dep ON dep.id=emp.dep_id;
+------+--------+----------+--------+----+-----------+
| id   | name   | salary   | dep_id | id | name      |
+------+--------+----------+--------+----+-----------+
|    1 | 张三   |  8000.00 |      2 |  2 | 销售部    |
|    2 | 李四   | 12000.00 |      1 |  1 | 教学部    |
|    3 | 王五   |  5000.00 |      2 |  2 | 销售部    |
|    4 | 赵六   |  8000.00 |      3 |  3 | 人事部    |
|    5 | 猪七   |  9000.00 |      1 |  1 | 教学部    |
|    7 | 蔡九   |  7000.00 |      2 |  2 | 销售部    |
+------+--------+----------+--------+----+-----------+
6 rows in set (0.00 sec)

全连接：在内连接的基础上保留左、右面表没有对应关系的的记录

1
2
3
select * from emp left join dep on emp.dep_id = dep.id
union
select * from emp right join dep on emp.dep_id = dep.id;

1553172175393

子查询

就是将一个查询语句的结果用括号括起来当作另外一个查询语句的条件去用

1
2
select * from emp where dep_id in (select id from dep 
where name = "技术" or name = "人力资源");

每个部门最新入职的员工

1
2
3
4
5
6
7
8
# 每个部门最新入职的员工
select t1.id,t1.name,t1.hire_date,t1.post,t2.* from emp as t1
inner join
(select post,max(hire_date) as max_date from emp group by post) as t2
#找出每个部门中最大的时间，即最新时间
on t1.post = t2.post
where t1.hire_date = t2.max_date
;

1553172744768

Mysql–用户相关

MySQL用户操作

创建账号

1
此处的主机地址 不是服务器地址 而是表示 这个账户可以在那台电脑上登录
1
2
3
4
5
6
7
8
9
10
11
方法一： CREATE USER语句创建
        CREATE USER "用户名"@"IP地址" IDENTIFIED BY "密码";

方法二： INSERT语句创建
        INSERT INTO mysql.user(user,host, password,ssl_cipher,x509_issuer,x509_subject)
        VALUES(‘用户名’,’IP地址’,password(‘密码’),’’,’’,’’);
        FLUSH PRIVILEGES;

方法三： GRANT语句创建
        GRANT SELECT ON *.* TO 用户名@’IP地址’ IDENTIFIED BY ‘密码’;
        FLUSH PRIVILEGES;
1
2
3
4
5
6
7
8
9
# 2、只创建账号
#create user tom@"客户端的ip" identified by "123";
create user tom@"192.168.15.%" identified by "123";
create user tom@"%" identified by "123";

#客户端：192.168.15.13               服务端：192.168.15.90

create user tom@"192.168.15.13 " identified by "123";
mysql -utom -p"123" -h 192.168.15.90 -P 3306

删除用户

1
2
3
4
5
drop user "tom"@"192.168.15.90";
drop user "jack"@"192.168.15.90";
drop user "rose"@"192.168.15.90";
drop user "lili"@"192.168.15.90";
flush privileges;
1
2
3
4
5
6
7
8
9
方法一：DROP USER语句删除

       DROP USER "用户名"@"IP地址";

方法二：DELETE语句删除

       DELETE FROM mysql.user 
            WHERE user=’用户名’ AND host=’IP地址’;
       FLUSH PRIVILEGES;

修改密码

(root用户修改自己密码：)

1
2
3
4
5
6
7
8
9
10
11
方法一：
    # mysqladmin -uroot -p123 password 'new_password'    //123为旧密码

方法二：
    UPDATE mysql.user SET password=password(‘new_password’)
           WHERE user=’root’ AND host=’localhost’;
    FLUSH PRIVILEGES;

方法三：
    SET PASSWORD=password(‘new_password’);
    FLUSH PRIVILEGES;

root修改其他用户密码：

1
2
3
4
5
6
7
8
9
10
11
12
方法一：
    SET PASSWORD FOR 用户名@’IP地址’=password(‘new_password’);
    FLUSH PRIVILEGES;

方法二：
    UPDATE mysql.user SET password=password(‘new_password’)
        WHERE user=’用户名’ AND host=’IP地址’;
    FLUSH PRIVILEGES;

方法三：
    GRANT SELECT ON *.* TO user3@’localhost’ IDENTIFIED BY ‘yuan’; 
    FLUSH PRIVILEGES;

普通用户修改自己密码：

1
SET password=password(‘new_password’);

丢失root用户密码：

1
2
3
4
5
6
7
# vim /etc/my.cnf
skip-grant-tables
# service mysqld restart
# mysql -uroot
mysql> UPDATE mysql.user SET password=password(‘new_password’)
WHERE user=’root’ AND host=’localhost’;
mysql> FLUSH PRIVILEGES;

修改用户名：

1
rename user '用户名'@'IP地址' to '新用户名'@'IP地址';

MySQL权限管理

1、跟权限有关的几张表

user->db->table_priv->columns_priv

user是全局帐号的一个操作权限，
db 是某一个库文件的操作权限，
table_oriv 是某一个表的操作权限，
columns_priv 是表字段的操作权限。

创建账号并且授权

–语法格式：

1
grant 权限列表  on 库名.表名 to 用户名@'客户端主机'  [identified by '密码'  with option参数];
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 给该用户授予对数据库db1下的表tb1所有的操作权限
grant all privileges on db1.tb1 TO '用户名'@'IP'  

-- 给该用户授予对数据库db1下所有表进行查操作的权限
grant select on db1.* TO '用户名'@'IP'   
  
-- 给该用户授予对所有数据库下所有表进行查和增的操作     
grant select,insert on *.* TO '用户名'@'IP'   
    
-- 用户只能在该IP段下才能(通配符%表示任意)对所有数据库下所有表进行查操作
grant select on *.* TO '用户名'@'192.168.1.%'     
  
-- 用户能用户可以在任意IP下(通配符%表示任意)对所有数据库下所有表进行查操作    
grant select on *.* TO '用户名'@'%'
1
2
3
4
5
6
7
8
9
10
11
# *.* ===> mysql.user
grant all on *.* to "tom"@"192.168.15.90" identified by "123";#all代表除了grant以外的所有权限

# db1.* ===> mysql.db
grant all on db1.* to "jack"@"192.168.15.90" identified by "123";#all代表除了grant以外的所有权限

# db1.t1 ===> mysql.tables_priv
grant all on db1.t1 to "rose"@"192.168.15.90" identified by "123";#all代表除了grant以外的所有权限

# db1.t1(id) ===> mysql.columns_priv
grant select(id),update(name) on db1.t1 to "lili"@"192.168.15.90" identified by "123";

View Code

with_option参数：

1
2
3
4
5
6
7
GRANT OPTION：                                    -- 授权选项


    MAX_QUERIES_PER_HOUR：                        -- 定义每小时允许执行的查询数
    MAX_UPDATES_PER_HOUR：                        -- 定义每小时允许执行的更新数
    MAX_CONNECTIONS_PER_HOUR：                    -- 定义每小时可以建立的连接数
    MAX_USER_CONNECTIONS：                        -- 定义单个用户同时可以建立的连接数

View Code

示例: 限制用户每小时的查询数量

1
mysql> grant select on *.* to '用户名'@'IP地址' identified by '123456' with max_queries_per_hour 5;

查看权限

1
show grants for '用户'@'IP地址'

回收权限

1
2
--语法：
    REVOKE 权限列表 ON 库名.表名 FROM 用户名@‘客户端主机’

示例：

1
2
REVOKE DELETE ON *.*  FROM 用户名@’%’;             -- 回收部分权限
REVOKE ALL PRIVILEGES FROM 用户名@’%’;             -- 回收所有权限

修改权限之后一点要刷新

1
flush privileges;

注解 flush privileges：

1
2
3
4
/*
flush privileges 命令本质上的作用是将当前user和privilige表中的用户信息/权限设置从mysql库(MySQL数据库的内置库)中提取到内存里。
MySQL用户数据和权限有修改后，搜索希望在"不重启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
视图是什么?
    本质是一张虚拟的表
    他的数据来自select语句


有什么用?
    原表安全
    案例:  在一公司中需要一张表保存所有人的薪资信息
           这个表不是所有都能午全看到   老板 财务 可以
           某一个员工 只能看到自己的信息
           所以不能把整个表的信息开发给这个员工

    功能1,影藏部分数据 开放指定的数据
    功能2,因为视图可以将查询结果保存特性 我可以用视图 来达到减少书写sql的次数
        例如:select *from emp where dept_id = (select id from dept where name = "市场");
            要查询市场的人
            将查询结果作为一个视图 以后在使用到这个需求 就直接查看视图

如何使用
    创建视图
    create view test_view as select *from t1;

特点:
    1.每次对视图进行的查询 其实都是再次执行了 as 后面的查询语句
    2.可以对视图进行修改  修改会同步到原表
    3.视图是永久存储的  存储的不是数据  而就是一条 as sql语句

基本不用 因为 你得程序开放的数据  不是开放sql语句  而开放的是查询结果

1.为什么使用视图

​ 为了提高复杂SQL语句的复用性和表操作的安全性（例如：工资字段不想展示给所有能查看该查询结果的人），MySQL提供了视图特性。所谓视图，本质上是一种虚拟表，其内容与真实的表相似，包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储数据值的形式存在。行和列的数据来自定义视图的查询所引用基本表，并且在具体使用视图时动态生成。
视图有如下特点; 

1. 视图的列可以来自不同的表，是表的抽象和逻辑意义上建立的新关系。 
2. 视图是由基本表（实表）产生的表（虚表）。 
3. 视图的建立和删除不影响基本表。 
4. 对视图内容的更新（添加、删除和修改）直接影响基本表。 
5. 当视图来自多个基本表时，不允许添加和删除数据。

2.创建视图

在创建视图时，首先要确保拥有CREATE VIEW的权限，并且同时确保对创建视图所引用的表也具有相应的权限。
2.1 创建视图的语法形式： 虽然视图可以被看成是一种虚拟表，但是其在物理上是不存在的，即数据库管理系统没有专门的位置为视图存储数据。根据视图的概念发现其数据来源于查询语句，因此创建视图的语法为：

CREATE VIEW view_name AS 查询语句
//说明：和创建表一样，视图名不能和表名、也不能和其他视图名重名。视图的功能实际就是封装了复杂的查询语句。

示例：

1
2
3
4
5
6
7
8
9
10
use view_test;  //选择一个自己创建的库
create table t_product( //创建表
  id int primary key,
  pname varchar(20),
  price decimal(8,2)
); 
insert into t_product values(1,'apple',6.5); //向表中插入数据
insert into t_product values(2,'orange',3); //向表中插入数据
create view view_product as select id,pname from t_product; //创建视图
select * from view_product;

结果为：

img
//其实在创建视图时实际代码里写的是一个表查询语句，只不过把这个查询语句封装起来重新起了一个名字，方便可以重复使用。
//再者，安全性方面可以隐藏一些不希望看到的字段，比如这里的价格字段。
//注意：在SQL语句的命名规范中，视图一般以view_xxx或者v_xxx的样式来命名。视图的查询语句和表的查询语句相同。

2.2 创建各种视图： 由于视图的功能实际上时封装查询语句，那么是不是任何形式的查询语句都可以封装在视图中呢？

封装使用聚合函数（SUM、MIN、MAX、COUNT等）查询语句的视图： 示例：
首先准备需要用到的两张表及其初始化数据;

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
CREATE TABLE t_group(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20)
);
CREATE TABLE t_student(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20),
  sex CHAR(1),
  group_id INT,
  FOREIGN KEY (group_id) REFERENCES t_group (id)
);
//t_group表中插入数据
INSERT INTO t_group (NAME) VALUES('group_1');
INSERT INTO t_group (NAME) VALUES('group_2');
INSERT INTO t_group (NAME) VALUES('group_3');
INSERT INTO t_group (NAME) VALUES('group_4');
INSERT INTO t_group (NAME) VALUES('group_5');
//t_student表中插入数据
INSERT INTO t_student (NAME,sex,group_id) VALUES('yuan_1','M',1);
INSERT INTO t_student (NAME,sex,group_id) VALUES('yuan_2','M',1);
INSERT INTO t_student (NAME,sex,group_id) VALUES('yuan_3','M',2);
INSERT INTO t_student (NAME,sex,group_id) VALUES('yuan_4','W',2);
INSERT INTO t_student (NAME,sex,group_id) VALUES('yuan_5','W',2);
INSERT INTO t_student (NAME,sex,group_id) VALUES('yuan_6','W',2);
INSERT INTO t_student (NAME,sex,group_id) VALUES('yuan_7','M',3);
INSERT INTO t_student (NAME,sex,group_id) VALUES('yuan_8','W',4);
INSERT INTO t_student (NAME,sex,group_id) VALUES('yuan_9','W',4);
================================================================
mysql> create view view_test2 as select count(name) from t_student;
Query OK, 0 rows affected (0.71 sec)
mysql> select * from view_test2;
+-------------+
| count(name) |
+-------------+
|      9      |
+-------------+
1 row in set (0.01 sec)

2.2.3 封装了实现排序功能（ORDER BY）查询语句的视图：

示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> create view view_test3 as select name from t_student order by id desc;
Query OK, 0 rows affected (0.06 sec)
mysql> select * from view_test3;
+--------+
| name   |
+--------+
| yuan_9 |
| yuan_8 |
| yuan_7 |
| yuan_6 |
| yuan_5 |
| yuan_4 |
| yuan_3 |
| yuan_2 |
| yuan_1 |
+--------+
9 rows in set (0.00 sec)

2.2.4 封装了实现表内连接查询语句的视图：

示例：（第二组学生的姓名）

1
2
3
4
5
6
7
8
9
10
11
12
mysql> create view view_test5 as select s.name from t_student s,t_group g where s.group_id=g.id and g.id=2;
Query OK, 0 rows affected (0.07 sec)
mysql> select * from view_test5;
+--------+
| name   |
+--------+
| yuan_3 |
| yuan_4 |
| yuan_5 |
| yuan_6 |
+-------+
4 rows in set (0.00 sec)

2.2.6 封装了实现子查询相关查询语句的视图：

示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> create view view_test7 as select s.name from t_student s where s.id in(select id from t_group);
Query OK, 0 rows affected (0.08 sec)
mysql> select * from view_test7;
+--------+
| name   |
+--------+
| yuan_1 |
| yuan_2 |
| yuan_3 |
| yuan_4 |
| yuan_5 |
+--------+
5 rows in set (0.00 sec)

2.2.7 封装了实现记录联合（UNION和UNION ALL）查询语句的视图：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> create view view_test8 as select id,name from t_student union all select id,name from t_group;
Query OK, 0 rows affected (0.08 sec)
mysql> select * from view_test8;
+----+---------+
| id | name    |
+----+---------+
|  1 | yuan_1  |
|  2 | yuan_2  |
|  3 | yuan_3  |
|  4 | yuan_4  |
|  5 | yuan_5  |
|  6 | yuan_6  |
|  7 | yuan_7  |
|  8 | yuan_8  |
|  9 | yuan_9  |
|  1 | group_1 |
|  2 | group_2 |
|  3 | group_3 |
|  4 | group_4 |
|  5 | group_5 |
+----+---------+
14 rows in set (0.01 sec)

3.查看视图

3.1 SHOW TABLES语句查看视图名： 执行SHOW TABLES 语句时不仅可以显示表的名字，同时也是显示出视图的名字。

示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> show tables;
+-----------------+
| Tables_in_test2 |
+-----------------+
| goods           |
| order           |
| orders          |
| person          |
| t_group         |
| t_product       |
| t_student       |
| view_product    |
| view_test2      |
| view_test3      |
| view_test5      |
| view_test7      |
| view_test8      |
+-----------------+
13 rows in set (0.00 sec

3.2 SHOW TABLE STATUS语句查看视图详细信息：

和SHOW TABLES语句一样，SHOW TABLE STATUS语句不仅会显示表的详细信息，同时也会显示视图的详细信息。

语法如下：
SHOW TABLE STATUS [FROM db_name] [LIKE ‘pattern’]
//参数db_name用来设置数据库，SHOW TABLES STATUS表示将显示所设置库的表和视图的详细信息。
//设置LIKE关键字，可以查看某一个具体表或视图的详细信息。例如： SHOW TABLE STATUS FROM zhaojd LIKE ‘t_group’ \G

3.3 SHOW CREATE VIEW语句查看视图定义信息：

语法为：
SHOW CREATE VIEW viewname;

3.4 DESCRIBE | DESC 语句查看视图定义信息：

语法为：
DESCRIBE | DESC viewname;

3.5 通过系统表查看视图信息： 当MySQL安装成功后，会自动创建系统数据库infomation_schema。在该数据库中存在一个包含视图信息的表格，可以通过查看表格views来查看所有视图的相关信息。

示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> use information_schema;
Database changed

mysql> select * from views where table_name='view_test7' \G;
*************************** 1. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: test2
          TABLE_NAME: view_test7
     VIEW_DEFINITION: select `s`.`NAME` AS `name` from `test2`.`t_student` `s` 
   　　　　　　　　　　　 where `s`.`id` in (select `test2`.`t_group`.`id` from `test2`.`t_group`)
        CHECK_OPTION: NONE
        IS_UPDATABLE: YES
             DEFINER: root@localhost
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
1 row in set (0.00 sec)

4.删除视图

在删除视图时首先要确保拥有删除视图的权限。

语法为：
DROP VIEW view_name [,view_name] ……
//从语法可以看出，DROP VIEW一次可以删除多个视图
示例：

1
2
3
mysql> drop view view_test1,view_test2;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;

5.修改视图

5.1 CREATE OR REPLACE VIEW语句修改视图： 对于已经创建好的表，尤其是已经有大量数据的表，通过先删除，然后再按照新的表定义重新建表的方式来修改表，需要做很多额外的工作，例如数据的重载等。可是对于视图来说，由于是“虚表”，并没有存储数据，所以完全可以通过该方式来修改视图。

实现思路就是：先删除同名的视图，然后再根据新的需求创建新的视图即可。

1
2
DROP VIEW view_name; 
CREATE VIEW view_name as 查询语句;

但是如果每次修改视图，都是先删除视图，然后再次创建一个同名的视图，则显得非常麻烦。于是MySQL提供了更方便的实现替换的创建视图的语法，完整语法为：

CREATE OR REPLACE VIEW view_name as 查询语句；

5.2 ALTER语句修改视图：

语法为：

ALTER VIEW view_name as 查询语句;

6.利用视图操作基本表

再MySQL中可以通常视图检索基本表数据，这是视图最基本的应用，除此之后还可以通过视图修改基本表中的数据。
6.1检索（查询）数据： 通过视图查询数据，与通过表进行查询完全相同，只不过通过视图查询表更安全，更简单实用。只需要把表名换成视图名即可。
6.2利用视图操作基本表数据： 由于视图是“虚表”，所以对视图数据进行的更新操作，实际上是对其基本表数据进行的更新操作。在具体更新视图数据时，需要注意以下两点; 

1. 对视图数据进行添加、删除直接影响基本表。 
2. 视图来自于多个基本表时，不允许添加、删除数据。 
视图中的添加数据操作、删除数据操作、更新数据操作的语法同表完全相同。只是将表名换成视图名即可。

1553228331508

Mysql–内部

触发器

什么是触发器？

触发器是数据库的一个程序，他是用来监听着数据表的某个行为，一旦数据表的这个行为发生了，马上执行相应的sql语句

触发器的语法结构：

create trigger 触发器的名称触发器事件 on 监听的表名 for each row 行为发生后执行的sql语句

注意：行为发生后执行的sql语句可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句。

触发器事件两个点：

  • 触发器事件发生的时间—–是在监听的表的行为 after before 常用的是after
  • 触发器执行的内容：增删改

1553189172349

1553189217089

建立一个表

1
2
3
4
5
6
7
8
CREATE TABLE cmd (
        id INT PRIMARY KEY auto_increment,
        USER CHAR (32),
        priv CHAR (10),
        cmd CHAR (64),
        sub_time datetime, #提交时间
        success enum ('yes', 'no') #0代表执行失败
);

建立一个要被触发的表

1
2
3
4
CREATE TABLE errlog (
        id INT PRIMARY KEY auto_increment,
        err_id int
);

建立触发器

1
2
3
4
5
6
7
8
delimiter $$  #把;变换成$$,让下面的代码完整运行
CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW
BEGIN
        if NEW.success = 'no' then   # NEW(新的信息,也就是插入cmd的信息)
                insert into errlog(err_id) values(NEW.id);  # 插入语法.当上面的条件成立
        end if;
END $$  # 制造触发器完毕
delimiter ; # 将符号还原成;

插入数据

1
2
3
4
5
6
7
8
9
10
11
12
INSERT INTO cmd (
        USER,
        priv,
        cmd,
        sub_time,
        success
)
VALUES
        ('egon','0755','ls -l /etc',NOW(),'yes'),
        ('egon','0755','cat /etc/passwd',NOW(),'no'),
        ('egon','0755','useradd xxx',NOW(),'no'),
        ('egon','0755','ps aux',NOW(),'yes');

删除触发器

1
drop trigger tri_after_insert_cmd;

我们常用的操作数据库语言SQL语句在执行的时候需要要先编译，然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。

触发器案例研究

一旦生成订单，对应的库存表要减去相应的数据

(1）建两张表 ：一个商品goods表 一个订单order表

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
-- 创建表


create table goods( goods_id int primary key auto_increment,
                    goods_name varchar(64),
                    shop_price decimal(10,2),
                    goods_number int)charset=utf8;

create table orders(goods_id int primary key auto_increment,
                    goods_name varchar(64),
                    buy_number int)charset=utf8;
-- 插入数据

insert into goods values(null,'nokiaN85',2000,35),
                        (null,'iphone4S',4500,30),
                        (null,'Lnmia',5000,40),
                        (null,'samsung',4200,20); 

mysql> select*from goods;
+---------------+-------------------+-------------------+----------------------+
| goods_id      | goods_name        | shop_price        | goods_number         |
+---------------+-------------------+-------------------+----------------------+
|        1      | nokiaN85          |    2000.00        |           35         |
|        2      | iphone4S          |    4500.00        |           30         |
|        3      | Lnmia             |    5000.00        |           40         |
|        4      | samsung           |    4200.00        |           20         |
+---------------+-------------------+-------------------+----------------------+

(2)创建触发器

1
2
3
4
5
6
create trigger alter_goods_number after insert on orders for each row
               update goods set goods_number=goods_number-5 where goods_id=1;

insert into orders values(1,'nokiaN85',5);

select*from goods;

new和old的使用

1
2
3
在 INSERT 型触发器中，NEW 用来表示将要（BEFORE）或已经（AFTER）插入的新数据；
在 UPDATE 型触发器中，OLD 用来表示将要或已经被修改的原数据，NEW 用来表示将要或已经修改为的新数据；
在 DELETE 型触发器中，OLD 用来表示将要或已经被删除的原数据；

关键词new 的使用

1
2
3
4
5
create trigger alter_goods_number after insert on orders for each row
      update goods set goods_number=goods_number-new.buy_number 
      where where goods_id=new.goods_id;

insert into orders values(1,'samsung',10);

关键词old 的使用：

1
2
3
4
5
6
create trigger back_goods_number after delete on orders for each row

   update goods set goods_number=goods_number+old.buy_number  
   where goods_id=old.goods_id;

delete from orders where goods_id=7;

更新 (update将之前下的订单撤销，再重新下订单)

1
2
3
4
5
create trigger update_goods_number after update on orders for each row
       update goods set goods_number=goods_number+old.buy_number-new.buy_number 
       where where goods_id=new.goods_id;

update orders set buy_number = 10;

查看触发器

1
SHOW TRIGGERS [FROM schema_name];

其中，schema_name 即 Schema 的名称，在 MySQL 中 Schema 和 Database 是一样的，也就是说，可以指定数据库名，这样就 不必先USE database_name了

删除触发器

和删除数据库、删除表格一样，删除触发器的语法如下：

1
DROP TRIGGER [IF EXISTS][schema_name.]trigger_name

触发器的执行顺序

我们建立的数据库一般都是 InnoDB 数据库，其上建立的表是事务性表，也就是事务安全的。这时，若SQL语句或触发器执行失败，MySQL 会回滚事务，有：

①如果 BEFORE 触发器执行失败，SQL 无法正确执行。
②SQL 执行失败时，AFTER 型触发器不会触发。
③AFTER 类型的触发器执行失败，SQL 会回滚。

一、存储过程

方案

1553176568981

　　存储过程（Stored Procedure）是

在大型数据库系统中，一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户
通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。而我们常用的操作数据库语言SQL语句在执行的时
候需要要先编译，然后执行，所以执行的效率没有存储过程高。

　　存储过程优点如下：

重复使用。存储过程可以重复使用，从而可以减少数据库开发人员的工作量。提高性能。存储过程在创建的时候在进行了编译，将来使用的时候不再重新翻译。一般的SQL语句每执行一次就需要编译一次，所以使用存储过程提高了效率。减少网络流量。存储过程位于服务器上，调用的时候只需要传递存储过程的名称以及参数就可以了，因此降低了网络传输的数据量。安全性。参数化的存储过程可以防止SQL注入式攻击，而且可以将Grant、Deny以及Revoke权限应用于存储过程。

　　存储过程简单语法：

1
2
3
4
5
6
7
8
CREATE PROCEDURE 存储过程名称(

　　    输入输出类型 变量名称 类型,
       ...　　
)
BEGIN
　　-- 声明， 语句要完成的操作，增删改查。。。
END

二、参数介绍示例

无参存储过程

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
# 无参
delimiter $$
create procedure p1()
BEGIN
    select * from blog;
END $$
delimiter ;

create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);

call db5.s1;

delimiter $$
create procedure p2()
BEGIN
    declare n int default 1;
    while (n < 100) do
        insert into s1 values(n,concat('egon',n),
        'male',concat('egon',n,'@163.com'));
        set n=n+1;
    end while;
END $$
delimiter ;

call p2()

1553190683630

有参存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
delimiter $$
create procedure p3(
    in n int,
    out res int
)
BEGIN
    select * from blog where id > n;
    set res = 0;
END $$
delimiter ;

# 直接在mysql中调用：

mysql> set @x=111;
mysql> call p3(3,@x);
mysql> select @x;
+------+
| @x   |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

1553191558675

只带IN（输入参数）的存储过程

　　表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DROP PROCEDURE IF EXISTS proc_person_findById;

-- 创建存储过程
 CREATE PROCEDURE proc_person_findById(
   in n int
)
BEGIN
     SELECT * FROM person where id=n;
END;

-- 定义变量
SET @n=2;
-- 调用存储过程
CALL proc_person_findById(@n);

View Code

3、只带OUT（输出参数）的存储过程

　　该值可在存储过程内部被改变，并可返回。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
DELIMITER !

CREATE PROCEDURE Procdure_out(
  OUT n INT(11)
)

BEGIN
    SELECT COUNT(*) INTO n FROM person;
END!

DELIMITER ;

CALL Procdure_out(@n);

SELECT @n AS "总数";

View Code

4、带IN（输入参数）和OUT（输出参数）,调用时指定，并且可被改变和返回

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
DELIMITER !

DROP PROCEDURE IF EXISTS proc_person_findInfoById; 

-- 创建存储过程
CREATE PROCEDURE proc_person_findInfoById(
    IN n INT(11), 
    OUT pusername VARCHAR(255), 
    OUT page INT(11)
)
BEGIN 
     SELECT username, age INTO pusername, page FROM person WHERE id=n;
END!

DELIMITER ;

-- 定义变量
SET @id=1;
-- 调用存储过程
CALL proc_person_findInfoById(@id,@username, @age);
SELECT @username as '用户名', @age '年龄';

View Code

5、带INOUT（输入输出）参数的存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
DELIMITER !

DROP PROCEDURE IF EXISTS proc_person_findInfoById; 

-- 创建存储过程
CREATE PROCEDURE proc_person_findInfoById(
    INOUT n INT
)
BEGIN 
     SELECT age INTO n FROM person WHERE id=n;
END!

DELIMITER ;

-- 定义变量
SET @id=2;
-- 调用存储过程
CALL proc_person_findInfoById(@age);
SELECT @age '年龄';

View Code

注意：关于输入输出参数

IN为输入， 定义参数时，可以不加，不加则默认为输入参数。OUT为输出，定义参数时，必须加上。INOUT为输入和输出，必须加上。表示该参数可以输入也可在处理后存放结果进行输出。

pymysql调用存储过程

cursor.callproc(‘p4’,(3,111))
调用pymysql里的callproc方法,只有在这端的连接上才能查询到那个变量

#set @_p4_0 = 3; set @_p4_1 = 111

print(cursor.fetchall())
cursor.execute(‘select @_p4_1;’)
print(cursor.fetchone())

1553192221910

1553192419145

事务

1553224301567

1553224315213

pymysql模块

连接msyql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
import pymysql

client = pymysql.connect(
    ip='127.0.0.1',  # IP
    port=3306,  # 端口
    user='root',  # 账号
    password='123',  # 密码
    database='db6',  # 库
    charset='utf8'  # 字符编码
)

cursor = client.cursor(pymysql.cursors.DictCursor)  # 拿到游标,将拿到的信息转换成字典
user_info = [
    (3, "alex"),
    (4, "lxx"),
    (5, "yxx")
]

user_info插入列表里的数据增

方法一

1
2
3
for user in user_info:
   sql = 'insert into t1 values(%s,"%s");' % (user[0], user[1])
   res = cursor.execute(sql)  # 拼接sql语句

方法二

1
2
sql='insert into t1 values(%s,%s);'
cursor.executemany(sql,userinfo)

删除一条信息

1
cursor.execute('delete from t1 where id=3;')

更改信息

1
2
3
cursor.execute("update moneyTable set money = money - 50 where name = '小明'")
    #如果小花的账户出问题了 无法更新数据 那就需要回滚
   cursor.execute("update moneyTable set money = money + 50 where name = '小花'")

查询语句

1
2
3
4
5
6
7
8
9
10
user_name = input('请输入账号名:').strip()
user_password = input('请输入密码:').strip()

sql = 'select id from user where name=%s and pwd=%s;'
rows = cursor.execute(sql, (user_name, user_password))

if rows:
    print('登陆成功')
else:
    print('账号或者密码错误')

查询语句把结果拿到

1
2
3
4
5
6
sql = 'select id from user where id>3;'
rows = cursor.execute(sql)
print(cursor.fetchall())  # 全部拿到,拿过一次第二次拿就没有
print(cursor.fetchone())  # 拿一条信息
cursor.scroll(-1) # 游标回退1
print(cursor.fetchmany(2))  # 拿2条信息

控制指针,有2种方式(这样就可以实现重复读取信息)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
cursor.scroll(0, mode='absolute')  # 绝对位置移动(从行首开始)
cursor.scroll(3, mode='relative')  # 相对当前位置移动(相对当前的位置往后移动3条信息)

try:
    cursor.execute(sql)
    cursor.execute(sql)
    cursor.execute(sql)
    client.commit()
except Exception as e:
    client.rollback()  # 回溯, 如果上面的sql语句出现了错误那么就会回溯到没有插入数据的时候

client.commit()  # 要想成功执行SQL语句,必须调用commit插入到数据库
cursor.close()  # 关闭MYSQL客户端
client.close()  # 关闭连接

sql注入问题

–注入

1
2
最终产生的sql  select *from user where user = 'yy' -- ' and pwd = '987657890';
-- 用于mysql注释  意思是 后面的内容忽略掉

or注入

1
"select *from user where user = 'axxax' or 1=1;

只能避免 黑客 从你的客户端软件注入 sql

黑客模拟客户端发送真正请求

1
2
但是无法避免  中间人攻击(在你的客户端和服务器中间加一个中转服务器)
# 这样就绕过了客户端的输入限制   此时 只能将 sql合法性验证放在服务器端

总结:

python如何避免sql注入? 把你的slq(用户输入的)参数 放execute函数的arg参数中 让pymysql 自动帮你屏蔽注入攻击

索引

索引:

本质都是通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

对于Innodb，主键毫无疑问是一个聚集索引。但是当一个表没有主键，或者没有一个索引，Innodb会如何处理呢。请看如下规则:

  • 如果一个主键被定义了，那么这个主键就是作为聚集索引。
  • 如果没有主键被定义，那么该表的第一个唯一非空索引被作为聚集索引。

  • 如果没有主键也没有合适的唯一索引，那么innodb内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是一个6个字节的列，该列的值会随着数据的插入自增。

    解释：什么叫索引项的排序方式和表中数据记录排序方式一致呢？ 我们把一本字典看做是数据库的表，那么字典的拼音目录就是聚集索引，它按照A-Z排列。实际存储的字也是按A-Z排列的。这就是索引项的排序方式和表中数据记录排序方式一致。

二 索引语法

创建索引

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
78
--创建表时
--语法：
    CREATE TABLE 表名 (
                字段名1  数据类型 [完整性约束条件…],
                字段名2  数据类型 [完整性约束条件…],
                [UNIQUE]   INDEX | KEY
                [索引名]  (字段名[(长度)]  [ASC |DESC])
                );

--------------------------------

--创建普通索引示例：

    CREATE TABLE emp1 (
        id INT,
        name VARCHAR(30) ,
        resume VARCHAR(50),
        INDEX index_emp_name (name)
    --KEY index_dept_name (dept_name)
        );


--创建唯一索引示例：

    CREATE TABLE emp2 (
        id INT,
        name VARCHAR(30) ,
        bank_num CHAR(18) UNIQUE ,
        resume VARCHAR(50),
        UNIQUE INDEX index_emp_name (name)
        );

--创建全文索引示例：

    CREATE TABLE emp3 (
        id INT,
        name VARCHAR(30) ,
        resume VARCHAR(50),
        FULLTEXT INDEX index_resume (resume)
        );

--创建多列索引示例：

    CREATE TABLE emp4 (
        id INT,
        name VARCHAR(30) ,
        resume VARCHAR(50),
        INDEX index_name_resume (name,resume)
        );



---------------------------------
复制代码
添加和删除索引
复制代码
---添加索引

    ---CREATE在已存在的表上创建索引
      CREATE  [UNIQUE]  INDEX  索引名
              ON 表名 (字段名[(长度)]  [ASC |DESC]) ;
    
    ---ALTER TABLE在已存在的表上创建索引
    
      ALTER TABLE 表名 ADD  [UNIQUE] INDEX
                    索引名 (字段名[(长度)]  [ASC |DESC]) ;
    
    
     CREATE INDEX index_emp_name on emp1(name);
     ALTER TABLE emp2 ADD UNIQUE INDEX index_bank_num(band_num);


-- 删除索引
    
    语法：DROP INDEX 索引名 on 表名
    
    DROP INDEX index_emp_name on emp1;
    DROP INDEX bank_num on emp2;

添加和删除索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
---添加索引

    ---CREATE在已存在的表上创建索引
      CREATE  [UNIQUE]  INDEX  索引名
              ON 表名 (字段名[(长度)]  [ASC |DESC]) ;
    
    ---ALTER TABLE在已存在的表上创建索引
    
      ALTER TABLE 表名 ADD  [UNIQUE] INDEX
                    索引名 (字段名[(长度)]  [ASC |DESC]) ;
    
    
     CREATE INDEX index_emp_name on emp1(name);
     ALTER TABLE emp2 ADD UNIQUE INDEX index_bank_num(band_num);


-- 删除索引
    
    语法：DROP INDEX 索引名 on 表名
    
    DROP INDEX index_emp_name on emp1;
    DROP INDEX bank_num on emp2;

三 索引测试实验

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 Indexdb.t1(id int,name varchar(20));


--存储过程

delimiter $$
create procedure autoinsert() 
BEGIN
declare i int default 1;
while(i<500000)do
insert into Indexdb.t1 values(i,'yuan');
set i=i+1;
end while;
END$$

delimiter ;

--调用函数
call autoinsert();

-- 花费时间比较：
-- 创建索引前
   select * from Indexdb.t1 where id=300000;--0.32s
-- 添加索引 
   create index index_id on Indexdb.t1(id);
-- 创建索引后
   select * from Indexdb.t1 where id=300000;--0.00s

table

1553241983517

1553229877558

1553238138427

1553238271498

1553239843193

1553240354720

1
2
3
4
5
索引遵循：最左前缀匹配原则
应该对哪些字段做索引：
#1、应该对数据量较小字段做索引
#2、应该对区分度高的字段做索引
#3、索引字段不要参与运算

1553240612030

1553240753508

全部搞索引插入就会变慢

基本函数

1、数学函数

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

2、聚合函数

常用于GROUP BY从句的SELECT查询中

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

3、字符串函数

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

4、日期和时间函数

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

5、格式化函数

1
2
3
4
5
6
DATE_FORMAT(date,fmt)  依照字符串fmt格式化日期date值
FORMAT(x,y)   把x格式化为以逗号隔开的数字序列，y是结果的小数位数
INET_ATON(ip)   返回IP地址的数字表示
INET_NTOA(num)   返回数字所代表的IP地址
TIME_FORMAT(time,fmt)  依照字符串fmt格式化时间time值
其中最简单的是FORMAT()函数，它可以把大的数值格式化为以逗号间隔的易读的序列。

示例：

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

学生选课系统表练习

设计表关系

img

创建表和插入数据

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
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177

/*
Navicat MySQL Data Transfer

Source Server         : localhost_3306
Source Server Version : 50719
Source Host           : localhost:3306
Source Database       : oldbo

Target Server Type    : MYSQL
Target Server Version : 50719
File Encoding         : 65001

Date: 2017-07-26 15:46:16
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for class
-- ----------------------------
DROP TABLE IF EXISTS class;
CREATE TABLE class (
  cid int(11) NOT NULL AUTO_INCREMENT,
  caption varchar(32) NOT NULL,
  PRIMARY KEY (cid)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO class VALUES ('1', '三年二班');
INSERT INTO class VALUES ('2', '三年三班');
INSERT INTO class VALUES ('3', '一年二班');
INSERT INTO class VALUES ('4', '二年九班');

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS course;
CREATE TABLE course (
  cid int(11) NOT NULL AUTO_INCREMENT,
  cname varchar(32) NOT NULL,
  teacher_id int(11) NOT NULL,
  PRIMARY KEY (cid),
  KEY fk_course_teacher (teacher_id),
  CONSTRAINT fk_course_teacher FOREIGN KEY (teacher_id) REFERENCES teacher (tid)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO course VALUES ('1', '生物', '1');
INSERT INTO course VALUES ('2', '物理', '2');
INSERT INTO course VALUES ('3', '体育', '3');
INSERT INTO course VALUES ('4', '美术', '2');

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS score;
CREATE TABLE score (
  sid int(11) NOT NULL AUTO_INCREMENT,
  student_id int(11) NOT NULL,
  course_id int(11) NOT NULL,
  num int(11) NOT NULL,
  PRIMARY KEY (sid),
  KEY fk_score_student (student_id),
  KEY fk_score_course (course_id),
  CONSTRAINT fk_score_course FOREIGN KEY (course_id) REFERENCES course (cid),
  CONSTRAINT fk_score_student FOREIGN KEY (student_id) REFERENCES student (sid)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO score VALUES ('1', '1', '1', '10');
INSERT INTO score VALUES ('2', '1', '2', '9');
INSERT INTO score VALUES ('5', '1', '4', '66');
INSERT INTO score VALUES ('6', '2', '1', '8');
INSERT INTO score VALUES ('8', '2', '3', '68');
INSERT INTO score VALUES ('9', '2', '4', '99');
INSERT INTO score VALUES ('10', '3', '1', '77');
INSERT INTO score VALUES ('11', '3', '2', '66');
INSERT INTO score VALUES ('12', '3', '3', '87');
INSERT INTO score VALUES ('13', '3', '4', '99');
INSERT INTO score VALUES ('14', '4', '1', '79');
INSERT INTO score VALUES ('15', '4', '2', '11');
INSERT INTO score VALUES ('16', '4', '3', '67');
INSERT INTO score VALUES ('17', '4', '4', '100');
INSERT INTO score VALUES ('18', '5', '1', '79');
INSERT INTO score VALUES ('19', '5', '2', '11');
INSERT INTO score VALUES ('20', '5', '3', '67');
INSERT INTO score VALUES ('21', '5', '4', '100');
INSERT INTO score VALUES ('22', '6', '1', '9');
INSERT INTO score VALUES ('23', '6', '2', '100');
INSERT INTO score VALUES ('24', '6', '3', '67');
INSERT INTO score VALUES ('25', '6', '4', '100');
INSERT INTO score VALUES ('26', '7', '1', '9');
INSERT INTO score VALUES ('27', '7', '2', '100');
INSERT INTO score VALUES ('28', '7', '3', '67');
INSERT INTO score VALUES ('29', '7', '4', '88');
INSERT INTO score VALUES ('30', '8', '1', '9');
INSERT INTO score VALUES ('31', '8', '2', '100');
INSERT INTO score VALUES ('32', '8', '3', '67');
INSERT INTO score VALUES ('33', '8', '4', '88');
INSERT INTO score VALUES ('34', '9', '1', '91');
INSERT INTO score VALUES ('35', '9', '2', '88');
INSERT INTO score VALUES ('36', '9', '3', '67');
INSERT INTO score VALUES ('37', '9', '4', '22');
INSERT INTO score VALUES ('38', '10', '1', '90');
INSERT INTO score VALUES ('39', '10', '2', '77');
INSERT INTO score VALUES ('40', '10', '3', '43');
INSERT INTO score VALUES ('41', '10', '4', '87');
INSERT INTO score VALUES ('42', '11', '1', '90');
INSERT INTO score VALUES ('43', '11', '2', '77');
INSERT INTO score VALUES ('44', '11', '3', '43');
INSERT INTO score VALUES ('45', '11', '4', '87');
INSERT INTO score VALUES ('46', '12', '1', '90');
INSERT INTO score VALUES ('47', '12', '2', '77');
INSERT INTO score VALUES ('48', '12', '3', '43');
INSERT INTO score VALUES ('49', '12', '4', '87');
INSERT INTO score VALUES ('52', '13', '3', '87');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS student;
CREATE TABLE student (
  sid int(11) NOT NULL AUTO_INCREMENT,
  gender char(1) NOT NULL,
  class_id int(11) NOT NULL,
  sname varchar(32) NOT NULL,
  PRIMARY KEY (sid),
  KEY fk_class (class_id),
  CONSTRAINT fk_class FOREIGN KEY (class_id) REFERENCES class (cid)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO student VALUES ('1', '男', '1', '理解');
INSERT INTO student VALUES ('2', '女', '1', '钢蛋');
INSERT INTO student VALUES ('3', '男', '1', '张三');
INSERT INTO student VALUES ('4', '男', '1', '张一');
INSERT INTO student VALUES ('5', '女', '1', '张二');
INSERT INTO student VALUES ('6', '男', '1', '张四');
INSERT INTO student VALUES ('7', '女', '2', '铁锤');
INSERT INTO student VALUES ('8', '男', '2', '李三');
INSERT INTO student VALUES ('9', '男', '2', '李一');
INSERT INTO student VALUES ('10', '女', '2', '李二');
INSERT INTO student VALUES ('11', '男', '2', '李四');
INSERT INTO student VALUES ('12', '女', '3', '如花');
INSERT INTO student VALUES ('13', '男', '3', '刘三');
INSERT INTO student VALUES ('14', '男', '3', '刘一');
INSERT INTO student VALUES ('15', '女', '3', '刘二');
INSERT INTO student VALUES ('16', '男', '3', '刘四');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS teacher;
CREATE TABLE teacher (
  tid int(11) NOT NULL AUTO_INCREMENT,
  tname varchar(32) NOT NULL,
  PRIMARY KEY (tid)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO teacher VALUES ('1', '张磊老师');
INSERT INTO teacher VALUES ('2', '李平老师');
INSERT INTO teacher VALUES ('3', '刘海燕老师');
INSERT INTO teacher VALUES ('4', '朱云海老师');
INSERT INTO teacher VALUES ('5', '李杰老师');
SET FOREIGN_KEY_CHECKS=1;

查询条件

1、查询所有的课程的名称以及对应的任课老师姓名

2、查询学生表中男女生各有多少人

3、查询物理成绩等于100的学生的姓名

4、查询平均成绩大于八十分的同学的姓名和平均成绩

5、查询所有学生的学号，姓名，选课数，总成绩

6、 查询姓李老师的个数

7、 查询没有报李平老师课的学生姓名

8、 查询物理课程比生物课程高的学生的学号

9、 查询没有同时选修物理课程和体育课程的学生姓名

10、查询挂科超过两门(包括两门)的学生姓名和班级

11 、查询选修了所有课程的学生姓名

12、查询李平老师教的课程的所有成绩记录

13、查询全部学生都选修了的课程号和课程名

14、查询每门课程被选修的次数

15、查询之选修了一门课程的学生姓名和学号

16、查询所有学生考出的成绩并按从高到低排序（成绩去重）

17、查询平均成绩大于85的学生姓名和平均成绩

18、查询生物成绩不及格的学生姓名和对应生物分数

19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名

答案

1、

1
SELECT cname,tname FROM course LEFT JOIN teacher on course.teacher_id=teacher.tid

2、

1
SELECT gender 性别 ,COUNT(gender) 人数 from student GROUP BY gender;

3、

1
2
3
SELECT sname from student where sid in (SELECT student_id from score LEFT JOIN course

              ON  score.course_id=course.cid WHERE num=100 AND course.cname="物理");

4、SELECT sname 姓名,平均成绩 from student RIGHT JOIN

​ (SELECT student_id,avg(num) 平均成绩

​ from score GROUP BY student_id

​ HAVING avg(num)>80) as A

​ on student.sid=A.student_id;

5、SELECT student_id 学号,sname 姓名,COUNT(course_id) 课程数,SUM(num) 总分 FROM score

​ LEFT JOIN student ON score.student_id=student.sid GROUP BY student_id;

7、SELECT * FROM student

​ WHERE sid not in

​ (SELECT student_id FROM score WHERE course_id in

​ (SELECT cid FROM course LEFT JOIN teacher ON teacher.tid=course.teacher_id

​ WHERE tname like “李平%”))

8、SELECT * FROM

​ (SELECT * FROM score LEFT JOIN course

​ ON score.course_id=course.cid WHERE cname=”生物”) as A

​ INNER JOIN

​ (SELECT * FROM score LEFT JOIN course

​ ON score.course_id=course.cid WHERE cname=”物理”) as B

​ ON A.student_id=B.student_id

​ WHERE a.num<b.num

9、SELECT sname from student

​ WHERE sid NOT IN (SELECT student_id FROM score LEFT JOIN course

​ ON score.course_id=course.cid

​ WHERE cname=’体育’ OR cname=’物理’

​ GROUP BY student_id

​ HAVING COUNT(course_id)>1) ;

10、SELECT sname,gender from student

​ WHERE sid in (SELECT student_id from score WHERE num<60

​ GROUP BY student_id

​ HAVING COUNT(student_id)>1);

11、SELECT sid 学号,sname 姓名 from student

​ WHERE sid in (SELECT student_id FROM score

​ GROUP BY student_id

​ HAVING COUNT(student_id)!= (SELECT COUNT(cid) FROM course))

12、SELECT * FROM score

​ WHERE course_id NOT IN (SELECT cid from teacher INNER JOIN course

​ on teacher.tid=course.teacher_id

​ WHERE teacher.tname LIKE “李平%”);

13、SELECT course_id from score

​ GROUP BY course_id

​ HAVING COUNT(course_id)=(SELECT COUNT(sid) FROM student)

14、– SELECT course_id,COUNT(course_id) from score GROUP BY course_id

​ SELECT cname,COUNT(course_id) FROM score LEFT JOIN course

​ ON score.course_id=course.cid GROUP BY course_id

15、SELECT sid,sname FROM student

​ WHERE sid in (SELECT student_id from score

​ GROUP BY student_id

​ HAVING COUNT(sid)=1);

16、SELECT DISTINCT num FROM score ORDER BY num DESC；

17、SELECT sname from student

​ WHERE sid in (SELECT student_id FROM score

​ GROUP BY student_id

​ HAVING AVG(num)>85)

​ 注意：用这种方式我们无法查看对应的平均成绩，所以不得不使用连表：

​ SELECT sname,AVG(num) FROM student LEFT JOIN score

​ ON student.sid=score.student_id

​ GROUP BY student_id HAVING AVG(num)>85;

18、SELECT sname 姓名,num 生物成绩 FROM score

​ LEFT JOIN course ON score.course_id=course.cid

​ LEFT JOIN student ON score.student_id=student.sid

​ WHERE score.num<60 AND cname=”生物”；

19、 SELECT student_id,avg(num),sname FROM score

​ LEFT JOIN student ON score.student_id=student.sid

​ WHERE course_id in (SELECT cid FROM course

​ LEFT JOIN teacher ON teacher.tid=course.teacher_id WHERE tname LIKE “李平%”)

​ GROUP BY student_id

​ ORDER BY AVG(num) DESC

​ LIMIT 1

图灵python大海老师 wechat
python分享公众号
坚持原创技术分享，您的支持将鼓励我继续创作！