MySQL

MySQL介绍

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。

MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库。

MySQL数据类型

整型

MySQL中整型也有不同的类型。它们分别是tinyint、smallint、mediumint、int、bigint，不同的int类型存储的数字范围是不同的。如图：

根据图片我们知道，int类型数字范围有包括正负号和只有整数的，那么MySQL默认整型的取值范围是哪儿个呢？我们直接检验下：

结论：默认MySQL的int类型都是使用正负号范围的。

但是，我们如果想要使用整数范围的int类型，可以给字段约束条件(unsigned)，如下：

mysql> create table t1(id tinyint unsigned);

整型类型括号内数字的作用

我们看以下例子：

mysql> create table foo(id int(3));
mysql> insert into foo values(4444444);

mysql> select * from foo;
+---------+
| id      |
+---------+
| 4444444 |
+---------+
1 row in set (0.00 sec)

我们发现数字正常显示，其实：

在整型中括号内的数字并不是用来限制存储的长度，而是用来控制展示的长度。我们以后在定义整型字段的时候，不需要自己添加数字，使用默认的就可以。

我们给类型加个zerofill（展示的数据长度不够使用0填充）测试下：

create table t1(id int(3) zerofill);
insert into t1 values(4);

mysql> select * from t14;
+------+
| id   |
+------+
|  004 |
+------+

浮点型

浮点型有float、double、decimal，它们范围和精度各不相同。

我们实际检验下它们的精确度，

mysql> create table t1(id float(255,30));  # 总共255位,小数位占30位
mysql> create table t2(id double(255,30)); # 总共255位,小数位占30位
mysql> create table t3(id decimal(65,30)); # 总共65位,小数位占30位
mysql> insert into t1 values(1.11111111111111111111111);
mysql> insert into t2 values(1.11111111111111111111111);
mysql> insert into t3 values(1.11111111111111111111111);

插入相同数据到各表中后，查看：

结论：精确度从高到低为 decimal > double > float

在实际应用场景中我们根据情况而定选择合适的数据类型，一般保留两位小数float也足够了。

字符类型

字符类型有char、varchar等。看单词的命名多少可以猜出varchar是变化的char，那么char是固定长度的。char和varchar使用时必须使用()并填入数字确定字符类型的长度，我们先简单看看它们如何使用。

mysql> create table t1(id int,name char(4));
mysql> create table t2(id int,name varchar(4));
mysql> insert into t1 values(1,'ii');
mysql> insert into t2 values(1,'ii');
mysql> insert into t1 values(2,'xxxxx');
mysql> insert into t2 values(2,'xxxxx');

上述我们为char和varchar设置长度为4个字符，但是我们插入表数据'xxxxx'为5个字符，mysql自动为我们做了截取，这不太合理。也就是说，我们需要在插入数据超出给定范围时报错，那么这该如何设置呢？

1.修改mysql配置文件（my-default.ini或自定义配置文件）

# 默认5.6及以上版本，mysql都已经配置了STRICT_TRANS_TABLES
sql_mode=STRICT_TRANS_TABLES

2.使用命令修改

mysql> show variables like '%mode%'
+----------------------------+------------------------+
| Variable_name              | Value                  |
+----------------------------+------------------------+
| binlogging_impossible_mode | IGNORE_ERROR           |
| block_encryption_mode      | aes-128-ecb            |
| gtid_mode                  | OFF                    |
| innodb_autoinc_lock_mode   | 1                      |
| innodb_strict_mode         | OFF                    |
| pseudo_slave_mode          | OFF                    |
| slave_exec_mode            | STRICT                 |
| sql_mode                   | NO_ENGINE_SUBSTITUTION |
+----------------------------+------------------------+
8 rows in set (0.03 sec)

mysql> set gloabl sql_mode = 'STRICT_TRANS_TABLES'

我们关闭mysql服务再次启动服务，然后重新打开mysql客户端。

mysql> insert into t1 values(3,'hhhhhhh');
ERROR 1406 (22001): Data too long for column 'name' at row 1

此时，插入数据过长就会报错。

char与varchar区别

char为定长类型，超出长度报错，少于给定长度也会补空格填充，再存储到硬盘中。
varchar为变长类型，超出长度报错，它有几个字符就占到多少字符。

我们通过char_length()查看一下上述例子中插入字符ii到两表中的长度，

我们通过char_length()得到字符长度都是2，并不像我开头描述的那样，其实这是因为查询时mysql自动为我们去除了空格字符。我们通过设置sql_mode=pad_char_to_full_length来验证我们的想法，设置后退出mysql客户端重新登录。

mysql> set global sql_mode = 'strict_trans_tables,pad_char_to_full_length'

mysql> insert into t1 values(3,'ab');
Query OK, 1 row affected (0.35 sec)

mysql> insert into t2 values(3,'ab');
Query OK, 1 row affected (0.11 sec)

mysql> select char_length(name) from t1 where id=3;
+-------------------+
| char_length(name) |
+-------------------+
|                 4 |
+-------------------+
1 row in set (0.00 sec)

mysql> select char_length(name) from t2 where id=3;
+-------------------+
| char_length(name) |
+-------------------+
|                 2 |
+-------------------+
1 row in set (0.00 sec)

那么char与varchar各有什么优劣势呢？

char是整存整取，速度快；varchar存储更节省空间，但是存取速度较char慢。因为varchar是可变长类型，所以取数据不能像char一样直接整取，数据长度参差不齐，因此varchar在存储数据时会为每个数据前加1bytes的数据（此数据为接下来要取数据的长度），这样读取数据前需要先读取这1bytes的数据来获得接下来要取的数据的长度，速度自然也就较char慢了。

枚举与集合类型

枚举

数据类型为枚举的字段在插入数据时，只能使用创建表时定义的那些中的一个。

mysql> create table user(
mysql> id int,
mysql> name varchar(32),
mysql> gender enum('male','female','others')
mysql> );
mysql> insert into user values(1,'xie','男');    # 报错
mysql> insert into user values(1,'lin','male');  # 正常

数据类型为集合的字段在插入数据时，可以使用表创建时定义的那些中的多个。

mysql> create table user(
mysql> id int,
mysql> name varchar(32),
mysql> hobby set('basketball','football','yolleyball')
mysql> );
mysql> insert into user values(1,'xie','basketball,football');

日期类型

日期类型有date、datetime、time、yea、timestampr等类型。

类型	格式	示例
DATE	YYYY-MM-DD	2022-2-18
TIME	hh:mm:ss[.uuuuuu]	12:59:59.123456
DATETIME	YYYY-MM-DD hh:mm:ss[.uuuuuu]	2022-2-18 12:59:59.123456
TIMESTAMP	YYYY-MM-DD hh:mm:ss[.uuuuuu]	2022-2-18 12:59:59.123456
YEAR	YYYY	2022

示例：

mysql> create table client(
mysql> id int,
mysql> name varchar(32),
mysql> reg_time date,
mysql> birth datetime,
mysql> study_time time,
mysql> join_time year
);

mysql> insert into client values(1,'xie','2022-2-18','2022-2-18 12:59:59','12:59:59',2022);

约束条件

约束条件相当于在字段类型的基础上添加额外的约束。

UNSIGNED 数字无符号，即0开始
ZEROFILL 展示数字长度小于设置的大小用0填充
NOT NULL 或 NULL（默认）非空或允许为空。使用NULL约束条件的字段不传值时默认为NULL，设置为NOT NULL不传值便会报错。
DEFAULT 默认值，即插入数据不给该字段传值时的默认值。

UNIQUE

# 单列唯一
# 设置约束条件unique的字段其值只能唯一，不能重复
create table t1(
	id int,
	name varchar(32) unique
);

# 联合唯一(必须放在最后)
# 联合的字段其值的组合只能唯一，不能重复
create table t2(
	id int,
	host varchar(32),
	port int,
	unique(host,port)
);

PRIMARY KEY（主键）
primary key从约束层面上来说，相当于是NOT NULL+UNIQUE。且在此基础上查询速度更快。
InnoDB存储引擎规定表有且只有一个主键。InnoDB是通过主键来构建表的，如果未设置主键和约束条件，InnoDB会采用隐藏的字段作为主键，但不再加快查询速度。如果未设置主键，但是设置了NOT NULL和UNIQUE的字段将自动升级为主键。

如上图可知，设置了多个非空且唯一，只将最前的字段升级为主键。
一般我们把表的id设置成主键。

AUTO_INCREMENT
自增，只能用于数字类型。一般配合主键使用，设置此约束条件的字段在我们插入新行后会自动+1。
AUTO_INCREMENT设置的字段在我们删除一条数据再插入一条数据后，其自增数不会自减。此处看以下的操作：

create table foo(
id int primary key auto_increment,
name varchar(32),
age int
);

insert into foo(name,age) values('xie',17),('lin',14),('boy',23),('kavi',34);

select * from foo;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | xie  |   17 |
|  2 | lin  |   14 |
|  3 | boy  |   23 |
|  4 | kavi |   34 |
+----+------+------+

delete from foo where id=4;
insert into foo(name,age) values('gane',20);

 select * from foo;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | xie  |   17 |
|  2 | lin  |   14 |
|  3 | boy  |   23 |
|  5 | gane |   20 |
+----+------+------+

我们看到直接从5开始，自增数并不会自减哦！并且我们使用delete清空表数据再插入数据，id还是从使用过的最大数字开始自增。如果您想要id重新开始，那么就使用truncate 表名清空表数据并且重置主键值。

FOREIGN KEY（外键）
foreign key就是表与表之间的某种约定的关系，由于这种关系的存在，能够让表与表之间的数据，更加的完整，关连性更强。foreign key是一列或多列的组合, 其定义必须和父表的父键保持一致。每一个非空的foreign key都必须在父表的父键里面找到对应的值。

针对外键使用表与表之间存在三种关系，分别是一对多、多对多、一对一。
- 一对多：
  假设有一张员工表和一张部门表，这两张表都是比较纯粹的（比如员工表只含员工的信息，没有其他杂质信息）。我们从员工方面看，一个员工只能是在一个部门；我们再从部门方面看，一个部门可以有多个员工，此时两表就是一对多的关系，并且外键要在多的这方创建（员工表）。
  
  以上述例子创建外键关联表：
```
# 部门表
create table dep(
id int primary key auto_increment,
dep_name varchar(32),
dep_desc varchar(32)
);

# 员工表
create table emp(
id int primary key auto_increment,
name varchar(32),
age int,
gender enum('male','female','none'),
dep_id int,
foreign key(dep_id) references dep(id)
on update cascade  # 级联更新（更新dep表id此表外键dep_id随之更新）
on delete cascade  # 级联删除（删除dep表数据，被删除的id与此表对应的dep_id的行也会删除）
);
```
- 多对多：
  假设有一张作者信息表和一张书籍信息表（两张表都是纯粹的）。同样的，我们从作者表看，一个作者可以写多本书；再从书籍表看，一本书可以有多个作者（合写）。因此这两张表就是多对多关系。创建多对多关系的表的外键我们需要额外创建一张表来存储作者和书籍的关系。
  
  以上述例子创建外键关联表：
```
# 书籍表
create table book(
id int primary key auto_increment,
book_name varchar(32),
book_desc varchar(32)
);

# 作者表
create table author(
id int primary key auto_increment,
author_name varchar(32),
age int,
gender enum('male','female') default 'male'
);

# 书籍与作者关系表
create table book2author(
id int primary key auto_increment,
book_id int,
author_id int,
foreign key(book_id) references book(id)
on update cascade
on delete cascade,
foreign key(author_id) references author(id)
on update cascade
on delete cascade
);
```
- 一对一
  假设我们有一张作者表和一张作者详细信息表。无论站在哪边看，此时作者和作者详细信息是一对一的，它们是一对一关系。创建一对一关系的表的外键无论哪儿张表都可以，只是此外键必须加唯一约束。一般的，我们在查询频率高的表创建外键。
  
  以上述例子创建外键关联表：
```
# 作者信息表
create table author(
id int primary key auto_increment,
name varchar(32),
age int,
author_id int unique,
foreign key(author_id) references author_detail(id)
on update cascade
on delete cascade
);

# 作者详情信息表
create table author_detail(
id int primary key auto_increment,
phone varchar(32),
address varchar(32)
);
```
外键使用注意事项：
1.在创建关联其他表的表的时候，需要先创建被关联表(没有外键字段的表)。
2.在关联表中插入新数据时，先确保被关联表中有数据。
3.在关联表中插入的外键字段值只能是被关联表中已经存在的数据。

SQL

数据定义语句

库相关

# 创建表
create database 库名 charset 字符集 collate 排序规则;
# 查看所有库
show databases;
# 查看建库的基本命令（建库语句）
show create database 库名;
# 修改库
alter database 库名 charset 字符集;
# 删除库
drop database 库名;

表相关

# 创建表
create table 表名(
字段名1 字段数据类型1 约束条件1,
字段名2 字段数据类型2 约束条件2,
...
);

# 查看当前使用库的所有表
show tables;
# 查看创建表的基本命令
show create table 表名;

# 删除表
drop table 表名;

# 修改表
alter table 表名 change 旧字段名 新字段名 段数据类型 新约束条件;
alter table 表名 modify 字段名 数据类型 约束条件;
# 表添加新字段
alter table 表名 add 字段名 数据类型 约束条件;
alter table 表名 add 字段名 数据类型 约束条件 first;
alter table 表名 add 字段名 数据类型 约束条件 after 字段名;
# 表删除字段
alter table 表名 drop 字段名;

数据操作语句

为了接下来的演示，先创建一张表：

create table student(
id int primary key auto_increment,
name varchar(32),
age int,
gender enum('male','female') default 'male'
)

insert

insert into student values(1,'xie',18,'male');
# 插入多行且针对性地插入
insert into student(name,age,gender) values('lin',15,'female'),('tony',16,'male');

update

update student set name='six' where id=1;

PS: update语句必须加where筛选条件

delete

delete from student where id=2;

扩展

delete drop truncate 都可以删除表，它们有什么区别？

1、delete 执行的是逐行删除，数据行多时操作慢。并且delete没有真正从磁盘删除，只是在存储层面打标记，磁盘空间不立即释放。HWM高水位线（）不会降低。（自增列继续）
2、drop 将表结构（元数据）和数据行物理层次删除。
3、truncate 清空表段中的所有数据页。物理层次删除全表数据磁盘空间立即释放，HWM高水位会降低。（自增列重新开始）

数据查询语句

select可以直接使用，比如用来查看内置函数执行结果。

select database(); # 查看当前所在库
select now();      # 查看当前时间
select user();     # 查看当前登录用户

除此之外，select一般配合其他子句使用。为了更好说明子句的使用，我们先准备好数据，

# 数据准备
create table emp(
  id int primary key auto_increment,
  name varchar(20) not null,
  gender enum('male','female') not null default 'male',
  age int(3) unsigned not null default 0,
  hire_date date not null,
  post varchar(50),
  post_comment varchar(100),
  salary double(15,2),
  office int,
  depart_id int
);

#插入记录
#三个部门：教学，销售，运营
insert into emp(name,gender,age,hire_date,post,salary,office,depart_id) values
('xie','male',18,'20170301','teacher',7300.33,401,1),
('tom','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('tony','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jack','female',18,'20110211','teacher',9000,401,1),
('jenny','male',18,'19000301','teacher',30000,401,1),
('sank','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);

from

from子句控制查询来源表

select * from emp;           # 查询表中所有数据
select name,gender from emp; # 查询表中所有数据的name、gender字段

where

where子句为筛选功能，where子句后面可以跟一些逻辑运算符（and、between、or、not、in）或者like关键字。where + like关键字为模糊查询，like后面跟字符串，字符串中可以使用%（匹配任意个数字符）或_(匹配单个任意字符)配合使用。

# 1.查询id大于等于3小于等于6的数据
select id from emp where id>=3 and id<=6;
select id from emp where id between 3 and 6;

# 2.查询薪资是20000或者18000或者17000的数据
select * from emp where salary=20000 or salary=18000 or salary=17000;
select * from emp where salary in (20000,18000,17000);

# 3.查询员工姓名中包含o字母的员工姓名和薪资
select name,salary from emp where name like '%o%';

# 4.查询员工姓名是由四个字符组成的员工姓名与其薪资
select name,salary from emp where name like '____';
select name,salary from emp where char_length(name)=4;

# 5.查询id小于3或者大于6的数据
select * from emp where id<3 or id>6
select * from emp where id not between 3 and 6;

# 6.查询薪资不在20000，18000，17000范围的数据
select * from emp where salary not in (20000,18000,17000);

# 7.查询岗位描述为空的员工名与岗位名
select name,post from emp where post_comment is null; # 针对null不能用等号，只能用is

group by

group by为分组,指定某条件分组查询分组后的所有字段值为每组的第一行数据。

使用分组的意义是比较各组之间数据差别，一般配合聚合函数使用，如max()、min()、avg()、count()、sum()、group_concat()。而且分组应该只能查询作为分组依据的数据，不能直接获取其他字段值，我们给sql_mode配置only_full_group_by.

# 追加到sql_mode
set global sql_mode='STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH,ONLY_FULL_GROUP_BY';

配置完毕后退出客户端重登，此时查询依据以外字段会报错。当然你也可以直接在配置文件中配置sql_mode。

接下来介绍聚合函数使用，聚合函数以组为单位统计组中数据。

# 1.获取每个部门的最高工资
select post,max(salary) from emp group by post;
+-----------+-------------+
| post      | max(salary) |
+-----------+-------------+
| operation |    20000.00 |
| sale      |     4000.33 |
| teacher   |  1000000.31 |
+-----------+-------------+

# 2.获取每个部门的最低工资
select post,min(salary) from emp group by post;
+-----------+-------------+
| post      | min(salary) |
+-----------+-------------+
| operation |    10000.13 |
| sale      |     1000.37 |
| teacher   |     2100.00 |
+-----------+-------------+

# 3.获取每个部门的平均工资
select post,avg(salary) from emp group by post;
+-----------+---------------+
| post      | avg(salary)   |
+-----------+---------------+
| operation |  16800.026000 |
| sale      |   2600.294000 |
| teacher   | 133775.080000 |
+-----------+---------------+

# 4.获取每个部门的工资总和
select post,sum(salary) from emp group by post;
+-----------+-------------+
| post      | sum(salary) |
+-----------+-------------+
| operation |    84000.13 |
| sale      |    13001.47 |
| teacher   |  1070200.64 |
+-----------+-------------+

# 5.获取每个部门的人数
# 统计个数建议以非空字段为count聚合函数的参数
select post,count(id) from emp group by post;
+-----------+-----------+
| post      | count(id) |
+-----------+-----------+
| operation |         5 |
| sale      |         5 |
| teacher   |         8 |
+-----------+-----------+

# 6.以部门分组并且查看各部门的员工姓名
select post,group_concat(name) from emp group by post;
+-----------+------------------------------------------------+
| post      | group_concat(name)                             |
+-----------+------------------------------------------------+
| operation | 程咬铁,程咬铜,程咬银,程咬金,僧龙               |
| sale      | 拉拉,乐乐,西西,呵呵,哈哈                       |
| teacher   | sank,jenny,jack,owen,tony,kevin,tom,xie        |
+-----------+------------------------------------------------+

having

having是过滤与where相似，唯一不同是where筛选在分组之前，having过滤在分组之后。

select post,avg(salary) from emp where salary>=10000 group by post;
+-----------+---------------+
| post      | avg(salary)   |
+-----------+---------------+
| operation |  16800.026000 |
| teacher   | 346666.770000 |
+-----------+---------------+

select post,avg(salary) from emp group by post having avg(salary)<10000;
+------+-------------+
| post | avg(salary) |
+------+-------------+
| sale | 2600.294000 |
+------+-------------+

distinct

distinct对重复数据去重，我们直接看例子：

 select distinct age from emp;
+-----+
| age |
+-----+
|  18 |
|  78 |
|  81 |
|  73 |
|  28 |
|  48 |
|  38 |
+-----+

order by

order by为排序，默认为升序（asc）。也可以在指定排序依据字段后面加desc改为降序。

select * from emp where salary<10000 order by salary;
+----+--------+--------+-----+------------+---------+--------------+---------+--------+-----------+
| id | name   | gender | age | hire_date  | post    | post_comment | salary  | office | depart_id |
+----+--------+--------+-----+------------+---------+--------------+---------+--------+-----------+
| 11 | 西西   | female |  18 | 2011-03-12 | sale    | NULL         | 1000.37 |    402 |         2 |
| 10 | 呵呵   | female |  38 | 2010-11-01 | sale    | NULL         | 2000.35 |    402 |         2 |
|  5 | owen   | male   |  28 | 2012-11-01 | teacher | NULL         | 2100.00 |    401 |         1 |
|  9 | 哈哈   | female |  48 | 2015-03-11 | sale    | NULL         | 3000.13 |    402 |         2 |
| 12 | 乐乐   | female |  18 | 2016-05-13 | sale    | NULL         | 3000.29 |    402 |         2 |
|  4 | tony   | male   |  73 | 2014-07-01 | teacher | NULL         | 3500.00 |    401 |         1 |
| 13 | 拉拉   | female |  28 | 2017-01-27 | sale    | NULL         | 4000.33 |    402 |         2 |
|  1 | xie    | male   |  18 | 2017-03-01 | teacher | NULL         | 7300.33 |    401 |         1 |
|  3 | kevin  | male   |  81 | 2013-03-05 | teacher | NULL         | 8300.00 |    401 |         1 |
|  6 | jack   | female |  18 | 2011-02-11 | teacher | NULL         | 9000.00 |    401 |         1 |
+----+--------+--------+-----+------------+---------+--------------+---------+--------+-----------+

select * from emp where salary<10000 order by salary desc;
+----+--------+--------+-----+------------+---------+--------------+---------+--------+-----------+
| id | name   | gender | age | hire_date  | post    | post_comment | salary  | office | depart_id |
+----+--------+--------+-----+------------+---------+--------------+---------+--------+-----------+
|  6 | jack   | female |  18 | 2011-02-11 | teacher | NULL         | 9000.00 |    401 |         1 |
|  3 | kevin  | male   |  81 | 2013-03-05 | teacher | NULL         | 8300.00 |    401 |         1 |
|  1 | xie    | male   |  18 | 2017-03-01 | teacher | NULL         | 7300.33 |    401 |         1 |
| 13 | 拉拉   | female |  28 | 2017-01-27 | sale    | NULL         | 4000.33 |    402 |         2 |
|  4 | tony   | male   |  73 | 2014-07-01 | teacher | NULL         | 3500.00 |    401 |         1 |
| 12 | 乐乐   | female |  18 | 2016-05-13 | sale    | NULL         | 3000.29 |    402 |         2 |
|  9 | 哈哈   | female |  48 | 2015-03-11 | sale    | NULL         | 3000.13 |    402 |         2 |
|  5 | owen   | male   |  28 | 2012-11-01 | teacher | NULL         | 2100.00 |    401 |         1 |
| 10 | 呵呵   | female |  38 | 2010-11-01 | sale    | NULL         | 2000.35 |    402 |         2 |
| 11 | 西西   | female |  18 | 2011-03-12 | sale    | NULL         | 1000.37 |    402 |         2 |
+----+--------+--------+-----+------------+---------+--------------+---------+--------+-----------+

limit

limit限制查询行数，启到分页作用。

# 查询前5行
select * from emp limit 5;
+----+-------+--------+-----+------------+---------+--------------+------------+--------+-----------+
| id | name  | gender | age | hire_date  | post    | post_comment | salary     | office | depart_id |
+----+-------+--------+-----+------------+---------+--------------+------------+--------+-----------+
|  1 | xie   | male   |  18 | 2017-03-01 | teacher | NULL         |    7300.33 |    401 |         1 |
|  2 | tom   | male   |  78 | 2015-03-02 | teacher | NULL         | 1000000.31 |    401 |         1 |
|  3 | kevin | male   |  81 | 2013-03-05 | teacher | NULL         |    8300.00 |    401 |         1 |
|  4 | tony  | male   |  73 | 2014-07-01 | teacher | NULL         |    3500.00 |    401 |         1 |
|  5 | owen  | male   |  28 | 2012-11-01 | teacher | NULL         |    2100.00 |    401 |         1 |
+----+-------+--------+-----+------------+---------+--------------+------------+--------+-----------+

# 从第5行开始往下查询5行
select * from emp limit 5,5;
+----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| id | name   | gender | age | hire_date  | post    | post_comment | salary   | office | depart_id |
+----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+
|  6 | jack   | female |  18 | 2011-02-11 | teacher | NULL         |  9000.00 |    401 |         1 |
|  7 | jenny  | male   |  18 | 1900-03-01 | teacher | NULL         | 30000.00 |    401 |         1 |
|  8 | sank   | male   |  48 | 2010-11-11 | teacher | NULL         | 10000.00 |    401 |         1 |
|  9 | 哈哈   | female |  48 | 2015-03-11 | sale    | NULL         |  3000.13 |    402 |         2 |
| 10 | 呵呵   | female |  38 | 2010-11-01 | sale    | NULL         |  2000.35 |    402 |         2 |
+----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+

regexp

regexp为正则匹配。

# 匹配员工姓名以n结尾的行
select * from emp where name regexp '^.*n$';
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
| id | name  | gender | age | hire_date  | post    | post_comment | salary  | office | depart_id |
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
|  3 | kevin | male   |  81 | 2013-03-05 | teacher | NULL         | 8300.00 |    401 |         1 |
|  5 | owen  | male   |  28 | 2012-11-01 | teacher | NULL         | 2100.00 |    401 |         1 |
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+

join

join用于多表连接查询。为此我们做一些数据准备：

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

create table emp(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);

insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'),
(205,'保洁')
;

insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('egon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);

join总共有四种用法，分别是inner join、left join、right join、union。

内连接

# 语法：SELECT 字段名 FROM A INNER JOIN B ON A.X = B.Y;

select * from emp inner join dep on emp.dep_id = dep.id;
+----+-------+--------+------+--------+-----+--------------+
| id | name  | sex    | age  | dep_id | id  | name         |
+----+-------+--------+------+--------+-----+--------------+
|  1 | jason | male   |   18 |    200 | 200 | 技术         |
|  2 | egon  | female |   48 |    201 | 201 | 人力资源     |
|  3 | kevin | male   |   18 |    201 | 201 | 人力资源     |
|  4 | nick  | male   |   28 |    202 | 202 | 销售         |
|  5 | owen  | male   |   18 |    203 | 203 | 运营         |
+----+-------+--------+------+--------+-----+--------------+

外连接
外连接 LEFT JOIN 以左表为基准展示查询结果，无值以NULL填充，
外连接 RIGHT JOIN 以右表为基准展示查询结果，无值以NULL填充。

# 左外连接语法：SELECT 字段名 FROM A LEFT JOIN B ON A.X = B.Y;

select * from emp left join dep on emp.dep_id = dep.id;
+----+-------+--------+------+--------+------+--------------+
| id | name  | sex    | age  | dep_id | id   | name         |
+----+-------+--------+------+--------+------+--------------+
|  1 | jason | male   |   18 |    200 |  200 | 技术         |
|  2 | egon  | female |   48 |    201 |  201 | 人力资源     |
|  3 | kevin | male   |   18 |    201 |  201 | 人力资源     |
|  4 | nick  | male   |   28 |    202 |  202 | 销售         |
|  5 | owen  | male   |   18 |    203 |  203 | 运营         |
|  6 | jerry | female |   18 |    204 | NULL | NULL         |
+----+-------+--------+------+--------+------+--------------+

# 右外连接语法：SELECT 字段名 FROM A RIGHT JOIN B ON A.X = B.Y;

select * from emp right join dep on emp.dep_id = dep.id;
+------+-------+--------+------+--------+-----+--------------+
| id   | name  | sex    | age  | dep_id | id  | name         |
+------+-------+--------+------+--------+-----+--------------+
|    1 | jason | male   |   18 |    200 | 200 | 技术         |
|    2 | egon  | female |   48 |    201 | 201 | 人力资源     |
|    3 | kevin | male   |   18 |    201 | 201 | 人力资源     |
|    4 | nick  | male   |   28 |    202 | 202 | 销售         |
|    5 | owen  | male   |   18 |    203 | 203 | 运营         |
| NULL | NULL  | NULL   | NULL |   NULL | 205 | 保洁         |
+------+-------+--------+------+--------+-----+--------------+

全连接

语法 ：
SELECT 字段名 FROM A LEFT JOIN B ON A.X = B.Y
UNION
SELECT 字段名 FROM A RIGHT JOIN B ON A.X = B.Y;

# 查看员工及员工所在部门信息
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;
+------+-------+--------+------+--------+------+--------------+
| id   | name  | sex    | age  | dep_id | id   | name         |
+------+-------+--------+------+--------+------+--------------+
|    1 | jason | male   |   18 |    200 |  200 | 技术         |
|    2 | egon  | female |   48 |    201 |  201 | 人力资源     |
|    3 | kevin | male   |   18 |    201 |  201 | 人力资源     |
|    4 | nick  | male   |   28 |    202 |  202 | 销售         |
|    5 | owen  | male   |   18 |    203 |  203 | 运营         |
|    6 | jerry | female |   18 |    204 | NULL | NULL         |
| NULL | NULL  | NULL   | NULL |   NULL |  205 | 保洁         |
+------+-------+--------+------+--------+------+--------------+

连表查询练习

数据准备

/*
 数据导入：
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50624
 Source Host           : localhost
 Source Database       : sqlexam

 Target Server Type    : MySQL
 Target Server Version : 50624
 File Encoding         : utf-8

 Date: 10/21/2016 06:46:46 AM
*/

SET NAMES utf8;
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`
-- ----------------------------
BEGIN;
INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班');
COMMIT;

-- ----------------------------
--  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`
-- ----------------------------
BEGIN;
INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2');
COMMIT;

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

-- ----------------------------
--  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`
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张一'), ('5', '女', '1', '张二'), ('6', '男', '1', '张四'), ('7', '女', '2', '铁锤'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '刘三'), ('14', '男', '3', '刘一'), ('15', '女', '3', '刘二'), ('16', '男', '3', '刘四');
COMMIT;

-- ----------------------------
--  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`
-- ----------------------------
BEGIN;
INSERT INTO `teacher` VALUES ('1', '张磊老师'), ('2', '李平老师'), ('3', '刘海燕老师'), ('4', '朱云海老师'), ('5', '李杰老师');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

练习

-- 1、查询所有的课程的名称以及对应的任课老师姓名
SELECT
	course.cid,
	teacher.tname 
FROM
	course
	INNER JOIN teacher ON course.teacher_id = teacher.tid;


-- 2、查询平均成绩大于八十分的同学的姓名和平均成绩
SELECT
	student.sname,
	t.avg_num 
FROM
	student
	INNER JOIN ( SELECT student_id, AVG( num ) AS avg_num FROM score GROUP BY student_id HAVING AVG( num )> 80 ) AS t ON student.sid = t.student_id;

-- 3、查询没有报李平老师课的学生姓名
SELECT
	sname
FROM
	student
WHERE
	sid NOT IN (
	SELECT DISTINCT
		student_id
	FROM
		score
	WHERE
	course_id IN ( SELECT cid FROM course INNER JOIN teacher ON course.teacher_id = teacher.tid WHERE teacher.tname = '李平老师' ));

-- 4、查询没有同时选修物理课程和体育课程的学生姓名
SELECT
	sname
FROM
	student
WHERE
	student.sid NOT IN (
	SELECT
		score.student_id
	FROM
		score
	WHERE
		course_id IN (
		SELECT
			cid
		FROM
			course
		WHERE
		cname IN ( '体育', '物理' ))
	GROUP BY
		student_id
	HAVING
		COUNT( course_id ) = 2
	);

-- 5、查询挂科超过两门(包括两门)的学生姓名和班级
SELECT
	sname,
	caption 
FROM
	class
	INNER JOIN student ON class.cid = student.class_id 
WHERE
	sid IN ( SELECT student_id FROM score WHERE num < 60 GROUP BY student_id HAVING COUNT( course_id )>= 2 );

存储引擎

存储引擎可以看成是处理数据的不同方式。好的很抽象，比如我们拿着一袋苹果需要储藏，可以直接放在桌上，或放到冰箱，不同的储藏方式代表不同的存储引擎。

我们使用命令查看下mysql支持的存储引擎。

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

主要的存储引擎详解

MyISAM

MySQL5.5之前默认的存储引擎。不支持事务、行级锁和外键，针对数据的操作较于InnoDB不够安全，但是数据的存取速度较于InnoDB更快。

我们使用MyISAM存储引擎创建表：
```
create table t1(id int) engine=myisam;
```
使用MyISAM存储引擎创建表会创建三个文件：
1. .frm 表结构文件
2. .MYD 表数据文件
3. .MYI 表索引文件(索引是用来加快数据查询的)
InnoDB

MySQL5.6开始的默认存储引擎。支持事务、行级锁和外键针对数据的操作更加的安全。

我们使用InnoDB存储引擎创建表：
```
create table t2(id int) engine=InnoDB;
```
使用InnoDB存储引擎创建表会创建两个文件：
1. .frm 表结构文件
2. .ibd 表数据和表索引文件
MEMORY

基于内存存取数据，适用于临时表。速度最快但是服务停止数据立刻丢失。

我们使用MEMORY存储引擎创建表：
```
create table t3(id int) engine=MEMORY;
```
使用InnoDB存储引擎创建表只创建.frm文件（表结构文件）
BLACKHOLE

写入其中的数据都会立刻消失类似于垃圾处理站。

我们使用BLACKHOLE存储引擎创建表：
```
create table t3(id int) engine=BLACKHOLE;
```
使用BLACKHOLE存储引擎创建表只创建.frm表结构文件。

pymysql

在python中通过模块pymysql操作数据库，首先通过pip3 install pymysql下载模块。

接着我们编写连接数据库操作的基本代码：

import pymysql

if __name__ == '__main__':
    # 创建数据库连接对象
    conn = pymysql.connect(
        host='127.0.0.1',  # ip地址
        port=3306,         # 端口号
        user='root',       # 用户名
        password='',       # 密码
        database='xzj',    # 数据库
        charset='utf8',    # 字符编码
        autocommit=True    # 是否自动提交事务
    )
    # 获得游标对象，指定游标类型DictCursor使查询结果返回为字典数据
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    # execute执行sql语句,返回值表示sql语句影响的行数
    affect_rows = cursor.execute('select * from student')
    # fetch**函数返回执行结果数据，默认不设置cursor类型返回元组
    print(cursor.fetchall())
    # 关闭连接
    conn.close()

上面只是基本示例，我们再说明下常用函数。

# 执行单行
cursor.execute('select * from t')
# 批量执行
cursor.executemany(
	'insert into t(name,age) values(%s,%s)',
	(('xie',18),('lin',20))
)

# 调用以下fetch**函数会移动光标
cursor.fetchone()   # 查询一行，光标到底时返回none
cursor.fetchall()   # 查询所有，光标到底时默认返回()空元组
cursor.fetchmany(5) # 查询5行，光标到底时默认返回()空元组

# 移动光标，第一个参数是移动行数，第二个参数是模式
# relative：相对当前位置移动n行
# absolute：从顶行开始移动n行
cursor.scroll(0,'relative')
cursor.scroll(0,'absolute')

posted @ 2022-02-18 19:42 它叫鸮阅读(55) 评论(0) 编辑收藏举报

刷新页面返回顶部

它叫鸮