MySQL数据库（安装+增删改查）
一. 安装
下载地址 : https://dev.mysql.com/downloads/mysql/
1. 安装步骤
(1) 选择5.7版本
(2) 针对操作系统的不同下载不同的版本
(3) 解压
将解压后的文件夹解压到你所指定的目录
(4) 添加环境变量
【右键计算机】--》【属性】--》【高级系统设置】--》【高级】--》【环境变量】--》【在第二个内容框中找到 变量名为Path 的一行，双击】 --> 【将MySQL的bin目录路径追加到变值值中，用 ； 分割】
window10系统可以在点击Path后,可以选择添加,把MySQL的bin目录粘贴到新的行里.
(5) 初始化
添加完环境变量后, 一定要先初始化,用管理员权限打开cmd窗口,写入按回车 : mysqld --initialize-insecure 按回车然后等待初始化完成,在mysql-5.7.23-winx64文件下出现一个新的文件夹data
(6) 启动mysql服务端
在cmd(管理员身份)输入 mysqld, 启动MySQL服务
(7) 启动mysql客户端并连接mysql服务端(新开一个cmd窗口)
再开启一个新的cmd窗口,输入 mysql -u root -p 命令,按enter出现输入密码行,不用输入,继续按enter.
(8) install mysql
在install之前复制bin文件夹的绝对路径,例如: D:\mysql-5.7.23-winx64\mysql-5.7.23-winx64\bin
关闭任务管理器里关闭mysqld.exe程序进程.
用管理员权限再开启一个cmd窗口,
开启MySQL的window服务,输入 : D:\mysql-5.7.23-winx64\mysql-5.7.23-winx64\bin\mysqld --install 按enter键
移除MySQL的window服务,输入 : D:\mysql-5.7.23-winx64\mysql-5.7.23-winx64\bin\mysqld --remove 按enter键
在服务里面查看是否有MySQL
鼠标右键有启动,停止等选项
也可以在cmd窗口进行操作 :
在cmd窗口开启MySQL服务 : net start MySQL (以管理员身份开启cmd)
在cmd窗口关闭MySQL服务 : net stop MySQL
(9) 统一字符编码
进入mysql客户端，执行\s,查看编码格式
把编码格式改成utf-8,执行以下操作:
1）my.ini文件是mysql的配置文件，在D:\mysql-5.7.23-winx64\mysql-5.7.23-winx64(安装路径)文件下创建my.ini文件
2) 把下面的代码拷贝到my.ini文件里,并保存.
#mysql5.5以上：修改方式为
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
3）以管理员身份重启服务, 执行如下命令
C:\Windows\system32>net stop MySQL # 先停止服务
MySQL 服务正在停止..
MySQL 服务已成功停止。
C:\Windows\system32>net start MySQL
MySQL 服务正在启动 .
MySQL 服务已经启动成功。
4）在cmd中输入mysql进入mysql环境，执行\s,显示编码格式都为utf-8,表示成功.
二. 库的操作
1. 系统数据库
执行下面命令,查看系统的数据库
show databases;
nformation_schema： 虚拟库，不占用磁盘空间，存储的是数据库启动后的一些参数，如用户表信息、列信息、权限信息、字符信息等
performance_schema： MySQL 5.5开始新增一个数据库：主要用于收集数据库服务器性能参数，记录处理查询请求时发生的各种事件、锁等现象
mysql： 授权库，主要存储系统用户的权限信息
test： MySQL数据库系统自动创建的测试数据库
2. 创建数据库
语法:
CREATE DATABASE 数据库名;
命名规则:
可以由字母、数字、下划线、＠、＃、＄
区分大小写
唯一性
不能使用关键字如 create select
不能单独使用数字
最长128位
# 基本上跟python或者js的命名规则一样
3. 数据库的相关操作
#查看数据库 show databases; #查看当前库 show create database db1; #查看所在的库 select database(); #选择数据库 use 数据库名 #删除数据库 DROP DATABASE 数据库名; # 修改数据库 alter database db1 charset utf8;
三. 表的操作
1. 存储引擎
现实生活中我们用来存储数据的文件有不同的类型，每种文件类型对应各自不同的处理机制：比如处理文本用txt类型，处理表格用excel，处理图片用png等数据库中的表也应该有不同的类型，表的类型不同，会对应mysql不同的存取机制，表类型又称为存储引擎. MySql数据库提供了多种存储引擎, 用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎.
mysql> show engines\G; # 查看所有支持的引擎 mysql> show variables like 'storage_engine%'; # 查看正在使用的存储引擎 create table t1(id int)engine=innodb; # 默认不写就是innodb
2. 创建表
表相当于文件，表中的一条记录就相当于文件的一行内容，不同的是，表中的第一条记录有对应的标题，称为表的字段.
语法:
create table 表名( 字段名1 类型[(宽度) 约束条件], 字段名2 类型[(宽度) 约束条件], 字段名3 类型[(宽度) 约束条件] ); #注意： 1. 在同一张表中，字段名是不能相同 2. 宽度和约束条件可选 3. 字段名和类型是必须的
步骤 :
(1) 创建数据库
CREATE DATABASE db1;
(2) 使用数据库
USE db1;
(3) 创建表
create table t1( id int, # 字段id name varchar(50), # 字段 name age int() # 字段age );
(4) 插入表的记录
insert into t1 values # values 也可以写成values(id,name,age)括号呢可以指定字段进行插入 (1,'jack',18), # 插入多条记录,用逗号隔开 (2,'tom',22);
(5) 对记录修改
update db1.t1 set name='ben'; update db1.t1 set name='steve' where id=2; # 指定id为2的记录
(6) 删除记录
delete from t1; delete from t1 where id=2;
3. 表的其它操作
(1) 查询表的存储数据
语法 : select * from 表名;
mysql> select * from t1; +------+-------+------+ | id | name | age | +------+-------+------+ | 1 | jack | 18 | | 2 | tom | 22 | +------+-------+------+ 2 rows in set (0.02 sec)
(2) 查询表的结构
语法 : desc 表名
mysql> desc t1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | age | int() | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ rows in set (0.16 sec)
(3) 查看表的详细结构
mysql> show create table a1\G; *************************** 1. row *************************** Table: a1 Create Table: CREATE TABLE `a1` ( `id` int(11) DEFAULT NULL, `name` varchar(50) DEFAULT NULL, `age` int(3) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 row in set (0.00 sec)
(4) 复制表
1) 新创建一个数据库db2
create database db2;
2) 使用db2
use db2;
3) 复制db1中的表t1
# 这就是复制表的操作（既复制了表结构，又复制了记录） mysql> create table t2 select * from db1.t1; Query OK, 2 rows affected (0.03 sec)
4) 查看db2中的表t2
#再去查看db3文件夹下的t3表发现 跟db3文件下的t1表数据一样 mysql> select * from db3.b1; +------+-------+------+ | id | name | age | +------+-------+------+ | 1 | jack | 18 | | 2 | tom | 22 | +------+-------+------+ 2 rows in set (0.00 sec)
# 查看表结构 mysql> desc t2; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | age | int() | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.02 sec)
只拷贝表结构,不要记录:
#在db2数据库下新创建一个t2表，给一个where条件，条件要求不成立，条件为false，只拷贝表结构 mysql> create table t2 select * from db2.a1 where 1>5; # where 为条件判断,只要where后面的条件为假即可 Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0
#查看表结构中的数据，发现是空数据 mysql> select * from t2; Empty set (0.00 sec)
使用like也是只拷贝表结构，不拷贝记录
mysql> create table t2 like db1.t1; Query OK, 0 rows affected (0.01 sec)
(5) 删除表
drop table 表名;
四. 数据类型
1. mysql 常用数据类型
#1. 数字： 整型：tinyint int bigint 小数： float ：在位数比较短的情况下不精准 double ：在位数比较长的情况下不精准 0.000001230123123123 存成：0.000001230000 decimal：（如果用小数，则用推荐使用decimal） 精准 内部原理是以字符串形式去存 #2. 字符串： char（10）：简单粗暴，浪费空间，存取速度快 root存成root000000 varchar：精准，节省空间，存取速度慢 sql优化：创建表时，定长的类型往前放，变长的往后放 比如性别 比如地址或描述信息 >255个字符，超了就把文件路径存放到数据库中。 比如图片，视频等找一个文件服务器，数据库中只存路径或url。 #3. 时间类型： 最常用：datetime #4. 枚举类型与集合类型 enum 和set
2. 数值类型
整数类型：TINYINT SMALLINT MEDIUMINT INT BIGINT
作用：存储年龄，等级，id，各种号码等
======================================================= tinyint[(m)] [unsigned] [zerofill] 小整数，数据类型用于保存一些范围的整数数值范围： 有符号： -128 ～ 127 无符号：0～ 255 PS： MySQL中无布尔值，使用tinyint(1)构造。 ======================================================= int[(m)][unsigned][zerofill] 整数，数据类型用于保存一些范围的整数数值范围： 有符号： -2147483648 ～ 2147483647 无符号：0 ～ 4294967295 ======================================================= bigint[(m)][unsigned][zerofill] 大整数，数据类型用于保存一些范围的整数数值范围： 有符号： -9223372036854775808 ～ 9223372036854775807 无符号：0 ～ 18446744073709551615
注意:
1) 整数类型都是默认有符号的,可以设置成无符号
mysql> create table t3(x tinyint unsigned); # 申明数据类型后加上 unsigned 变为无符号
2) int类型后面的存储是显示宽度，而不是存储宽度. 整形类型，其实没有必要指定显示宽度，使用默认的就ok
mysql> create table t3(id int(1) unsigned); #插入255555记录也是可以的 mysql> insert into t3 values(255555); mysql> select * from t3; +--------+ | id | +--------+ | 255555 | +--------+ ps:以上操作还不能够验证，再来一张表验证用zerofill 用0填充 # zerofill 用0填充 mysql> create table t4(id int(5) unsigned zerofill); mysql> insert into t4 value(1); Query OK, 1 row affected (0.00 sec) #插入的记录是1，但是显示的宽度是00001 mysql> select * from t4; +-------+ | id | +-------+ | 00001 | +-------+ row in set (0.00 sec)
3. 浮点型
定点数类型: DEC等同于DECIMAL
浮点类型：FLOAT DOUBLE
作用：存储薪资、身高、体重、体质参数等
-------------------------FLOAT------------------- FLOAT[(M,D)] [UNSIGNED] [ZEROFILL] #参数解释：单精度浮点数（非准确小数值），M是全长，D是小数点后个数。M最大值为255，D最大值为30 #有符号： -3.402823466E+38 to -1.175494351E-38, 1.175494351E-38 to 3.402823466E+38 #无符号： 1.175494351E-38 to 3.402823466E+38 #精确度： **** 随着小数的增多，精度变得不准确 **** -------------------------DOUBLE----------------------- DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL] #参数解释： 双精度浮点数（非准确小数值），M是全长，D是小数点后个数。M最大值为255，D最大值为30 #有符号： -1.7976931348623157E+308 to -2.2250738585072014E-308 2.2250738585072014E-308 to 1.7976931348623157E+308 #无符号： 2.2250738585072014E-308 to 1.7976931348623157E+308 #精确度： ****随着小数的增多，精度比float要高，但也会变得不准确 **** ====================================== --------------------DECIMAL------------------------ decimal[(m[,d])] [unsigned] [zerofill] #参数解释：准确的小数值，M是整数部分总个数（负号不算），D是小数点后个数。 M最大值为65，D最大值为30。 #精确度： **** 随着小数的增多，精度始终准确 **** 对于精确数值计算时需要用此类型 decaimal能够存储精确值的原因在于其内部按照字符串存储。
验证三种浮点数类型建表
#1验证FLOAT类型建表： mysql> create table t5(x float(256,31)); ERROR 1425 (42000): Too big scale 31 specified for column 'x'. Maximum is 30. mysql> create table t5(x float(256,30)); ERROR 1439 (42000): Display width out of range for column 'x' (max = 255) mysql> create table t5(x float(255,30)); #建表成功 Query OK, 0 rows affected (0.03 sec) #2验证DOUBLE类型建表： mysql> create table t6(x double(255,30)); #建表成功 Query OK, 0 rows affected (0.03 sec) #3验证deimal类型建表： mysql> create table t7(x decimal(66,31)); ERROR 1425 (42000): Too big scale 31 specified for column 'x'. Maximum is 30. mysql> create table t7(x decimal(66,30)); ERROR 1426 (42000): Too big precision 66 specified for column 'x'. Maximum is 65. mysql> create table t7(x decimal(65,30)); #建表成功 Query OK, 0 rows affected (0.00 sec)
4. 日期类型
DATE TIME DATETIME TIMESTAMP YEAR
作用：存储用户注册时间，文章发布时间，员工入职时间，出生时间，过期时间等
语法： YEAR YYYY（1901/2155） DATE YYYY-MM-DD（1000-01-01/9999-12-31） TIME HH:MM:SS（'-838:59:59'/'838:59:59'） DATETIME YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59 Y） TIMESTAMP YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）
5. 字符串类型
# 注意：char和varchar括号内的参数指的都是字符的长度 # char类型：定长，简单粗暴，浪费空间，存取速度快 字符长度范围：0-255（一个中文是一个字符，是utf8编码的3个字节） 存储： 存储char类型的值时，会往右填充空格来满足长度 例如：指定长度为10，存>10个字符则报错，存<10个字符则用空格填充直到凑够10个字符存储 检索： 在检索或者说查询时，查出的结果会自动删除尾部的空格，除非我们打开pad_char_to_full_length SQL模式（设置SQL模式：SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH'; 查询sql的默认模式：select @@sql_mode;） # varchar类型：变长，精准，节省空间，存取速度慢 字符长度范围：0-65535（如果大于21845会提示用其他类型 。mysql行最大限制为65535字节，字符编码为utf-8：https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html） 存储： varchar类型存储数据的真实内容，不会用空格填充，如果'ab ',尾部的空格也会被存起来 强调：varchar类型会在真实数据前加1-2Bytes的前缀，该前缀用来表示真实数据的bytes字节数（1-2Bytes最大表示65535个数字，正好符合mysql对row的最大字节限制，即已经足够使用） 如果真实的数据<255bytes则需要1Bytes的前缀（1Bytes=8bit 2**8最大表示的数字为255） 如果真实的数据>255bytes则需要2Bytes的前缀（2Bytes=16bit 2**16最大表示的数字为65535） 检索： 尾部有空格会保存下来，在检索或者说查询时，也会正常显示包含空格在内的内容
char填充空格来满足固定长度，但是在查询时却会删除尾部的空格，修改sql_mode让其现出原形。
length()：查看字节数; char_length():查看字符数
# 创建t1表,分别指明字段x为char类型，字段y为varchar类型 mysql> create table t1(x char(5),y varchar(4)); Query OK, 0 rows affected (0.16 sec) # char存放的是5个字符，而varchar存4个字符 mysql> insert into t1 values('你瞅啥 ','你瞅啥 '); Query OK, 1 row affected (0.01 sec) # 在检索时char将自己浪费的2个字符给删掉了，装的好像自己没浪费过空间一样，而varchar很老实，存了多少，就显示多少 mysql> select x,char_length(x),y,char_length(y) from t1; +-----------+----------------+------------+----------------+ | x | char_length(x) | y | char_length(y) | +-----------+----------------+------------+----------------+ | 你瞅啥 | 3 | 你瞅啥 | 4 | +-----------+----------------+------------+----------------+ row in set (0.02 sec) #略施小计，让char现原形 mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH'; Query OK, 0 rows affected (0.00 sec) #查看当前mysql的mode模式 mysql> select @@sql_mode; +-------------------------+ | @@sql_mode | +-------------------------+ | PAD_CHAR_TO_FULL_LENGTH | +-------------------------+ row in set (0.00 sec) #原形毕露了吧。。。。 mysql> select x,char_length(x) y,char_length(y) from t1; +-------------+------+----------------+ | x | y | char_length(y) | +-------------+------+----------------+ | 你瞅啥 | 5 | 4 | +-------------+------+----------------+ row in set (0.00 sec) # 查看字节数 #char类型：3个中文字符+2个空格=11Bytes #varchar类型:3个中文字符+1个空格=10Bytes mysql> select x,length(x),y,length(y) from t1; +-------------+-----------+------------+-----------+ | x | length(x) | y | length(y) | +-------------+-----------+------------+-----------+ | 你瞅啥 | 11 | 你瞅啥 | 10 | +-------------+-----------+------------+-----------+ row in set (0.02 sec)
6. 枚举类型和集合类型
枚举和集合可以让字段的值只能在给定范围中选择，如单选框，多选框
enum 单选 只能在给定的范围内选一个值，如性别 sex 男male/女female
set 多选 在给定的范围内可以选择一个或一个以上的值（爱好1,爱好2,爱好3...）
mysql> create table consumer( -> id int, -> name varchar(50), -> sex enum('male','female','other'), -> level enum('vip1','vip2','vip3','vip4'),#在指定范围内，多选一 -> fav set('play','music','read','study') #在指定范围内，多选多 -> ); Query OK, 0 rows affected (0.03 sec) mysql> insert into consumer values -> (1,'jack','male','vip2','read,study'), -> (2,'steve','other','vip4','play'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from consumer; +------+---------+-------+-------+------------+ | id | name | sex | level | fav | +------+---------+-------+-------+------------+ | 1 | jack | male | vip2 | read,study | | 2 | steve | other | vip4 | play | +------+---------+-------+-------+------------+ rows in set (0.00 sec)
五. 完整性约束
1. 介绍
约束条件与数据类型的宽度一样，都是可选参数
作用：用于保证数据的完整性和一致性
主要分为: PRIMARY KEY (PK) #标识该字段为该表的主键，可以唯一的标识记录 FOREIGN KEY (FK) #标识该字段为该表的外键 NOT NULL #标识该字段不能为空 UNIQUE KEY (UK) #标识该字段的值是唯一的 AUTO_INCREMENT #标识该字段的值自动增长（整数类型，而且为主键） DEFAULT #为该字段设置默认值 UNSIGNED #无符号 ZEROFILL #使用0填充
说明: #1. 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值 #2. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值 sex enum('male','female') not null default 'male' #必须为正值（无符号） 不允许为空 默认是20 age int unsigned NOT NULL default 20 # 3. 是否是key 主键 primary key 外键 foreign key 索引 (index,unique...)
2. not null 和 default
是否可空，null表示空，非字符串 not null - 不可空 null - 可空 默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值
create table tb1( nid int not null defalut 2, # 约束nid不能为空,默认值为2, 设置nid字段有默认值后，则无论id字段是null还是not null，都可以插入空，插入空默认填入default指定的默认值 num int not null # 约束num不能为空 );
3. unique
在mysql中称为单列唯一
举例说明:
创建表:
# 创建公司部门表（每个公司都有唯一的一个部门）。 mysql> create table department( -> id int, -> name char(10) -> ); Query OK, 0 rows affected (0.01 sec) mysql> insert into department values(1,'IT'),(2,'IT'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from department; +------+------+ | id | name | +------+------+ | 1 | IT | | 2 | IT | +------+------+ rows in set (0.00 sec) # 发现： 同时插入两个IT部门也是可以的，但这是不合理的，所以我们要设置name字段为unique 解决这种不合理的现象。 验证之前重复插入记录的操作是可行的，但是不符合场景
使用约束条件unique，来对公司部门的字段进行设置
#第一种创建unique的方式 #例子1： create table department( id int, name char(10) unique ); mysql> insert into department values(1,'it'),(2,'it'); ERROR 1062 (23000): Duplicate entry 'it' for key 'name' #例子2： create table department( id int unique, name char(10) unique ); insert into department values(1,'it'),(2,'sale'); #第二种创建unique的方式 create table department( id int, name char(10) , unique(id), unique(name) ); insert into department values(1,'it'),(2,'sale');
联合唯一: 只要两列记录，有一列不同，既符合联合唯一的约束
# 创建services表 mysql> create table services( -> id int, -> ip char(15), -> port int, -> unique(id), -> unique(ip,port) -> ); Query OK, 0 rows affected (0.05 sec) mysql> desc services; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | YES | UNI | NULL |
|
| ip | char(15) | YES | MUL | NULL | | | port | int(11) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ rows in set (0.01 sec) #联合唯一，只要两列记录，有一列不同，既符合联合唯一的约束 mysql> insert into services values -> (1,'192,168,11,23',80), -> (2,'192,168,11,23',81), -> (3,'192,168,11,25',80); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from services; +------+---------------+------+ | id | ip | port | +------+---------------+------+ | 1 | 192,168,11,23 | 80 | | 2 | 192,168,11,23 | 81 | | 3 | 192,168,11,25 | 80 | +------+---------------+------+ rows in set (0.00 sec) mysql> insert into services values (4,'192,168,11,23',80); ERROR 1062 (23000): Duplicate entry '192,168,11,23-80' for key 'ip'
4. primary key : 主键
一个表中可以：
单列做主键
多列做主键（复合主键）
约束：等价于 not null unique,字段的值不为空且唯一
存储引擎默认是（innodb）:对于innodb存储引擎来说，一张表必须有一个主键。
单列主键:
# 创建t14表，为id字段设置主键，唯一的不同的记录 create table t14( id int primary key, name char(16) ); insert into t14 values (1,'xiaoma'), (2,'xiaohong'); mysql> insert into t14 values(2,'wxxx'); ERROR 1062 (23000): Duplicate entry '6' for key 'PRIMARY' # not null + unique的化学反应，相当于给id设置primary key create table t15( id int not null unique, name char(16) ); mysql> create table t15( -> id int not null unique, -> name char(16) -> ); Query OK, 0 rows affected (0.01 sec) mysql> desc t15; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | char(16) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ rows in set (0.02 sec)
复合主键:
create table t16( ip char(15), port int, primary key(ip,port) ); insert into t16 values ('1.1.1.2',80), ('1.1.1.2',81);
5. auto_increment
让约束的字段为自动增长，约束的字段必须同时被key约束
# 不指定id，则自动增长 # 创建student create table student( id int primary key auto_increment, name varchar(20), sex enum('male','female') default 'male' ); mysql> desc student; +-------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | sex | enum('male','female') | YES | | male | | +-------+-----------------------+------+-----+---------+----------------+ rows in set (0.17 sec) #插入记录 mysql> insert into student(name) values ('老白'),('小白'); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from student; +----+--------+------+ | id | name | sex | +----+--------+------+ | 1 | 老白 | male | | 2 | 小白 | male | +----+--------+------+ rows in set (0.00 sec)
也可以指定id
mysql> insert into student values(4,'asb','female'); Query OK, 1 row affected (0.00 sec) mysql> insert into student values(7,'wsb','female'); Query OK, 1 row affected (0.01 sec) mysql> select * from student; +----+--------+--------+ | id | name | sex | +----+--------+--------+ | 1 | 老白 | male | | 2 | 小白 | male | | 4 | asb | female | | 7 | wsb | female | +----+--------+--------+ rows in set (0.00 sec) # 再次插入一条不指定id的记录，会在之前的最后一条记录继续增长 mysql> insert into student(name) values ('大白'); Query OK, 1 row affected (0.00 sec) mysql> select * from student; +----+--------+--------+ | id | name | sex | +----+--------+--------+ | 1 | 老白 | male | | 2 | 小白 | male | | 4 | asb | female | | 7 | wsb | female | | 8 | 大白 | male | +----+--------+--------+ rows in set (0.00 sec)
对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长
mysql> delete from student; Query OK, 5 rows affected (0.00 sec) mysql> select * from student; Empty set (0.00 sec) mysql> select * from student; Empty set (0.00 sec) mysql> insert into student(name) values('ysb'); Query OK, 1 row affected (0.01 sec) mysql> select * from student; +----+------+------+ | id | name | sex | +----+------+------+ | 9 | ysb | male | +----+------+------+ row in set (0.00 sec) #应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它 mysql> truncate student; Query OK, 0 rows affected (0.03 sec) mysql> insert into student(name) values('xiaobai'); Query OK, 1 row affected (0.00 sec) mysql> select * from student; +----+---------+------+ | id | name | sex | +----+---------+------+ | 1 | xiaobai | male | +----+---------+------+ row in set (0.00 sec) mysql>
清空表区分delete和truncate的区别：
delete from t1, 如果有自增id，新增的数据，仍然是以删除前的最后一样作为起始.
truncate table t1, 数据量大，删除速度比上一条快，且直接从零开始.
6. foreign key
一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY.
employee表
| id | name | age | dep_id |
|---|---|---|---|
| 1 | a | 19 | 2 |
| 2 | b | 23 | 1 |
| 3 | c | 27 | 2 |
| 4 | d | 24 | 3 |
department 表
| id | Address |
|---|---|
| 1 | 技术部 |
| 2 | 销售部 |
| 3 | 财务部 |
注意:
1) 先建主表(独立的表),即department表,再建被关联表(也叫从表,有外键),即employee表.
2) 在建关联表时,要加入一下sql语句:
on delete cascade 同步删除
on update cascade 同步更新
create table employee( id int primary key, name varchar(20) not null, age int not null, dep_id int, constraint fk_dep foreign key(dep_id) references dep(id) # 建立外键, fk_dep是我们起的外键名 on delete cascade # 同步删除 on update cascade # 同步更新 );
六. 单表查询
一、单表查询的语法 SELECT 字段1,字段2... FROM 表名 WHERE 条件 GROUP BY field HAVING 筛选 ORDER BY field LIMIT 限制条数 二、关键字的执行优先级（重点） 重点中的重点：关键字的执行优先级 from where group by having select distinct order by limit 1.找到表:from 2.拿着where指定的约束条件，去文件/表中取出一条条记录 3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组 4.将分组的结果进行having过滤 5.执行select 6.去重 7.将结果按条件排序：order by 8.限制结果的显示条数
公司员工表，表的字段和数据类型如下
company.employee 员工id id int 姓名 name varchar 性别 sex enum 年龄 age int 入职日期 hire_date date 岗位 post varchar 职位描述 post_comment varchar 薪水 salary double 办公室 office int 部门编号 depart_id int
sql语句建公司员工表,并插入记录
#创建表,设置字段的约束条件 create table employee( id int primary key auto_increment, name varchar(20) not null, sex enum('male','female') not null default 'male', #大部分是男的 age int(3) unsigned not null default 28, hire_date date not null, post varchar(50), post_comment varchar(100), salary double(15,2), office int,# 一个部门一个屋 depart_id int ); # 查看表结构 mysql> desc employee; +--------------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment| | emp_name | varchar(20) | NO | | NULL | | | sex | enum('male','female') | NO | | male | | | age | int(3) unsigned | NO | | 28 | | | hire_date | date | NO | | NULL | | | post | varchar(50) | YES | | NULL | | | post_comment | varchar(100) | YES | | NULL | | | salary | double(15,2) | YES | | NULL | | | office | int(11) | YES | | NULL | | | depart_id | int(11) | YES | | NULL | | +--------------+-----------------------+------+-----+---------+----------------+ rows in set (0.08 sec) #插入记录 #三个部门：教学，销售，运营 insert into employee(name ,sex,age,hire_date,post,salary,office,depart_id) values ('jack','male',18,'20170301','办事处',7300.33,401,1), #以下是教学部 ('tom','male',78,'20150302','teacher',1000000.31,401,1), ('wusir','male',81,'20130305','teacher',8300,401,1), ('ben','male',73,'20140701','teacher',3500,401,1), ('nezha','male',28,'20121101','teacher',2100,401,1), ('steve','female',18,'20110211','teacher',9000,401,1), ('jerry','male',18,'19000301','teacher',30000,401,1), ('xiaomage','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), ('a','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门 ('b','male',18,'19970312','operation',20000,403,3), ('c','female',18,'20130311','operation',19000,403,3), ('d','male',18,'20150411','operation',18000,403,3), ('e','female',18,'20140512','operation',17000,403,3) ;
(1) where 约束
where子句中可以使用 1.比较运算符：>、<、>=、<=、<>、!= 2.between 80 and 100 ：值在80到100之间 3.in(80,90,100)值是10或20或30 4.like 'xiaomagepattern': pattern可以是%或者_。%小时任意多字符，_表示一个字符 5.逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
#1 :单条件查询 mysql> select id,emp_name from employee where id > 5; +----+------------+ | id | emp_name | +----+------------+ | 6 | steve | | 7 | jerry | | 8 | xiaomage | | 9 | 歪歪 | 10 | 丫丫 | 11 | 丁丁 | 12 | 星星 | 13 | 格格 | 14 | a | 15 | b | 16 | c | 17 | d | 18 | e #2 多条件查询 mysql> select emp_name from employee where post='teacher' and salary>10000; +----------+ | emp_name | +----------+ | tom | | jerry | +----------+ #3.关键字BETWEEN AND SELECT name,salary FROM employee WHERE salary BETWEEN 10000 AND 20000; SELECT name,salary FROM employee WHERE salary NOT BETWEEN 10000 AND 20000; #注意''是空字符串，不是null SELECT name,post_comment FROM employee WHERE post_comment=''; ps： 执行 update employee set post_comment='' where id=2; 再用上条查看，就会有结果了 #5:关键字IN集合查询 mysql> SELECT name,salary FROM employee WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ; +------------+---------+ | name | salary | +------------+---------+ | ben | 3500.00 | | steve | 9000.00 | +------------+---------+ rows in set (0.00 sec) mysql> SELECT name,salary FROM employee WHERE salary IN (3000,3500,4000,9000) ; +------------+---------+ | name | salary | +------------+---------+ | ben | 3500.00 | | steve | 9000.00 | +------------+---------+ mysql> SELECT name,salary FROM employee WHERE salary NOT IN (3000,3500,4000,9000) ; +-----------+------------+ | name | salary | +-----------+------------+ | jack | 7300.33 | | tom | 1000000.31 | | wusir | 8300.00 | | nezha | 2100.00 | | jerry | 30000.00 | | xiaomage | 10000.00 | | 歪歪 | 3000.13 | | 丫丫 | 2000.35 | | 丁丁 | 1000.37 | | 星星 | 3000.29 | | 格格 | 4000.33 | | a | 10000.13 | | b | 20000.00 | | c | 19000.00 | | d | 18000.00 | | e | 17000.00 | +-----------+------------+ rows in set (0.00 sec) #6:关键字LIKE模糊查询 通配符’%’ mysql> SELECT * FROM employee WHERE name LIKE 'jin%'; +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ | 6 | steve | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 7 | jerry | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ rows in set (0.00 sec) 通配符'_' mysql> SELECT age FROM employee WHERE name LIKE 'to_'; +-----+ | age | +-----+ | 78 | +-----+ row in set (0.00 sec) 练习： 1. 查看岗位是teacher的员工姓名、年龄 2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄 3. 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资 4. 查看岗位描述不为NULL的员工信息 5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资 6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资 7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪 #对应的sql语句 select name,age from employee where post = 'teacher'; select name,age from employee where post='teacher' and age > 30; select name,age,salary from employee where post='teacher' and salary between 9000 and 10000; select * from employee where post_comment is not null; select name,age,salary from employee where post='teacher' and salary in (10000,9000,30000); select name,age,salary from employee where post='teacher' and salary not in (10000,9000,30000); select name,salary*12 from employee where post='teacher' and name like 'jin%';
(2) group by 分组查询
#1、首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的 #2、分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等 #3、为何要分组呢？ 取每个部门的最高工资 取每个部门的员工数 取男人数和女人数 小窍门：‘每’这个字后面的字段，就是我们分组的依据 #4、大前提： 可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助于聚合函数
mysql> select * from employee group by post; +----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | 14 | a | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 2 | tom | male | 78 | 2015-03-02 | teacher | | 1000000.31 | 401 | 1 | | 1 | jack | male | 18 | 2017-03-01 | 办事处 | NULL | 7300.33 | 401 | 1 | +----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ rows in set (0.00 sec) #由于没有设置ONLY_FULL_GROUP_BY,于是也可以有结果，默认都是组内的第一条记录，但其实这是没有意义的 如果想分组，则必须要设置全局的sql的模式为ONLY_FULL_GROUP_BY mysql> set global sql_mode='ONLY_FULL_GROUP_BY'; Query OK, 0 rows affected (0.00 sec) #查看MySQL 5.7默认的sql_mode如下： mysql> select @@global.sql_mode; +--------------------+ | @@global.sql_mode | +--------------------+ | ONLY_FULL_GROUP_BY | +--------------------+ row in set (0.00 sec) mysql> exit;#设置成功后，一定要退出，然后重新登录方可生效
# group by分组之后，只能查看当前字段,如果想查看组内信息 mysql> select * from emp group by post;# 报错 ERROR 1054 (42S22): Unknown column 'post' in 'group statement' mysql> select post from employee group by post; +-----------------------------------------+ | post | +-----------------------------------------+ | operation | | sale | | teacher | | 办事处 | +-----------------------------------------+ rows in set (0.00 sec)
(3) 聚合函数
group by分组之后，只能查看当前字段,如果想查看组内信息，可以借助于聚合函数
max()求最大值 min()求最小值 avg()求平均值 sum() 求和 count() 求总个数 #强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组 # 每个部门有多少个员工 select post,count(id) from employee group by post; # 每个部门的最高薪水 select post,max(salary) from employee group by post; # 每个部门的最低薪水 select post,min(salary) from employee group by post; # 每个部门的平均薪水 select post,avg(salary) from employee group by post; # 每个部门的所有薪水 select post,sum(age) from employee group by post;
(4) having 过滤
HAVING与WHERE不一样的地方在于 #！！！执行优先级从高到低：where > group by > having #1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。 #2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
mysql> select * from employee where salary>1000000; +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+ | 2 | tom | male | 78 | 2015-03-02 | teacher | | 1000000.31 | 401 | 1 | +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+ row in set (0.00 sec) mysql> select post,group_concat(name) from emp group by post having salary > 10000; ##错误，分组后无法直接取到salary字段 ERROR 1054 (42S22): Unknown column 'post' in 'field list'
(5) order by 查询顺序
按单列排序 SELECT * FROM employee ORDER BY age; SELECT * FROM employee ORDER BY age ASC; SELECT * FROM employee ORDER BY age DESC; 按多列排序:先按照age升序排序，如果年纪相同，则按照id降序 SELECT * from employee ORDER BY age ASC, id DESC;
验证多列排序： SELECT * from employee ORDER BY age ASC,id DESC; mysql> SELECT * from employee ORDER BY age ASC,id DESC; +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | 18 | d | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 | | 17 | c | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 | | 16 | b | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 | | 15 | a | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | | 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 | | 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | | 7 | jerry | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 6 | steve | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 1 | jack | male | 18 | 2017-03-01 | 办事处 | NULL | 7300.33| 401 | 1 | | 14 | a | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | | 5 | nezha | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | | 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 8 | xiaomage | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | | 4 | ben | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 2 | tom | male | 78 | 2015-03-02 | teacher | | 1000000.31 | 401 | 1 | | 3 | wusir | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ rows in set (0.01 sec) mysql>
(6) limit 限制查询的记录数
示例： SELECT * FROM employee ORDER BY salary DESC LIMIT 3; #默认初始位置为0 SELECT * FROM employee ORDER BY salary DESC LIMIT 0,5; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条 SELECT * FROM employee ORDER BY salary DESC LIMIT 5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条
# 第1页数据 mysql> select * from employee limit 0,5; +----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | 1 | jack | male | 18 | 2017-03-01 | 河办事 | NULL | 7300.33 | 401 | 1 | | 2 | tom | male | 78 | 2015-03-02 | teacher | | 1000000.31 | 401 | 1 | | 3 | wusir i | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | | 4 | ben | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 5 | nezha | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | +----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ rows in set (0.00 sec) # 第2页数据 mysql> select * from employee limit 5,5; +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ | 6 | steve | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 7 | jerry | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 8 | xiaomage | 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 | +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ rows in set (0.00 sec) # 第3页数据 mysql> select * from employee limit 10,5; +----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ | 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | | 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 | | 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | | 14 | a | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 15 | b | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | +----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ rows in set (0.00 sec)
七. 多表查询
准备两张表，部门表（department）、员工表（employee）
create table department( id int, name varchar(20) ); create table employee( id int primary key auto_increment, name varchar(20), sex enum('male','female') not null default 'male', age int, dep_id int ); #插入数据 insert into department values (200,'技术'), (201,'人力资源'), (202,'销售'), (203,'运营'); insert into employee(name,sex,age,dep_id) values ('egon','male',18,200), ('alex','female',48,201), ('wupeiqi','male',38,201), ('yuanhao','female',28,202), ('nvshen','male',18,200), ('xiaomage','female',18,204) ; # 查看表结构和数据 mysql> desc department; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ rows in set (0.19 sec) mysql> desc employee; +--------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | sex | enum('male','female') | NO | | male | | | age | int(11) | YES | | NULL | | | dep_id | int(11) | YES | | NULL | | +--------+-----------------------+------+-----+---------+----------------+ rows in set (0.01 sec) mysql> select * from department; +------+--------------+ | id | name | +------+--------------+ | 200 | 技术 | | 201 | 人力资源 | | 202 | 销售 | | 203 | 运营 | +------+--------------+ rows in set (0.02 sec) mysql> select * from employee; +----+----------+--------+------+--------+ | id | name | sex | age | dep_id | +----+----------+--------+------+--------+ | 1 | a | male | 18 | 200 | | 2 | b | female | 48 | 201 | | 3 | c | male | 38 | 201 | | 4 | d | female | 28 | 202 | | 5 | e | male | 18 | 200 | | 6 | f | female | 18 | 204 | +----+----------+--------+------+--------+ rows in set (0.00 sec)
1. 多表连接查询
外链接语法:
SELECT 字段列表 FROM 表1 INNER|LEFT|RIGHT JOIN 表2 ON 表1.字段 = 表2.字段;
(1) 交叉连接
mysql> select * from employee,department; +----+----------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +----+----------+--------+------+--------+------+--------------+ | 1 | a | male | 18 | 200 | 200 | 技术 | | 1 | a | male | 18 | 200 | 201 | 人力资源 | | 1 | a | male | 18 | 200 | 202 | 销售 | | 1 | a | male | 18 | 200 | 203 | 运营 | | 2 | b | female | 48 | 201 | 200 | 技术 | | 2 | b | female | 48 | 201 | 201 | 人力资源 | | 2 | b | female | 48 | 201 | 202 | 销售 | | 2 | b | female | 48 | 201 | 203 | 运营 | | 3 | c | male | 38 | 201 | 200 | 技术 | | 3 | c | male | 38 | 201 | 201 | 人力资源 | | 3 | c | male | 38 | 201 | 202 | 销售 | | 3 | c | male | 38 | 201 | 203 | 运营 | | 4 | d | female | 28 | 202 | 200 | 技术 | | 4 | d | female | 28 | 202 | 201 | 人力资源 | | 4 | d | female | 28 | 202 | 202 | 销售 | | 4 | d | female | 28 | 202 | 203 | 运营 | | 5 | e | male | 18 | 200 | 200 | 技术 | | 5 | e | male | 18 | 200 | 201 | 人力资源 | | 5 | e | male | 18 | 200 | 202 | 销售 | | 5 | e | male | 18 | 200 | 203 | 运营 | | 6 | f | female | 18 | 204 | 200 | 技术 | | 6 | f | female | 18 | 204 | 201 | 人力资源 | | 6 | f | female | 18 | 204 | 202 | 销售 | | 6 | f | female | 18 | 204 | 203 | 运营 |
(2) 内连接：只连接匹配的行
#找两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选出了匹配的结果 #department没有204这个部门，因而employee表中关于204这条员工信息没有匹配出来 mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id; +----+---------+------+--------+--------------+ | id | name | age | sex | name | +----+---------+------+--------+--------------+ | 1 | a | 18 | male | 技术 | | 2 | b | 48 | female | 人力资源 | | 3 | c | 38 | male | 人力资源 | | 4 | d | 28 | female | 销售 | | 5 | e | 18 | male | 技术 | +----+---------+------+--------+--------------+ rows in set (0.00 sec) #上述sql等同于 mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_id=department.id;
(3) 外链接之左连接：优先显示左表全部记录
#以左表为准，即找出所有员工信息，当然包括没有部门的员工 #本质就是：在内连接的基础上增加左边有，右边没有的结果 mysql> select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id; +----+----------+--------------+ | id | name | depart_name | +----+----------+--------------+ | 1 | a | 技术 | | 5 | e | 技术 | | 2 | b | 人力资源 | | 3 | c | 人力资源 | | 4 | d | 销售 | | 6 | f | NULL | +----+----------+--------------+ rows in set (0.00 sec)
(4) 外链接之左连接：优先显示左表全部记录
#以右表为准，即找出所有部门信息，包括没有员工的部门 #本质就是：在内连接的基础上增加右边有，左边没有的结果 mysql> select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id; +------+---------+--------------+ | id | name | depart_name | +------+---------+--------------+ | 1 | a | 技术 | | 2 | b | 人力资源 | | 3 | c | 人力资源 | | 4 | d | 销售 | | 5 | e | 技术 | | NULL | NULL | 运营 | +------+---------+--------------+ rows in set (0.00 sec)
(5) 全外连接：显示左右两个表全部记录
#外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果 #注意：mysql不支持全外连接 full JOIN #强调：mysql可以使用此种方式间接实现全外连接 语法：select * from employee left join department on employee.dep_id = department.id union all select * from employee right join department on employee.dep_id = department.id; mysql> select * from employee left join department on employee.dep_id = department.id union select * from employee right join department on employee.dep_id = department.id ; +------+----------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +------+----------+--------+------+--------+------+--------------+ | 1 | a | male | 18 | 200 | 200 | 技术 | | 5 | b | male | 18 | 200 | 200 | 技术 | | 2 | c | female | 48 | 201 | 201 | 人力资源 | | 3 | d | male | 38 | 201 | 201 | 人力资源 | | 4 | e | female | 28 | 202 | 202 | 销售 | | 6 | f | female | 18 | 204 | NULL | NULL | | NULL | NULL | NULL | NULL | NULL | 203 | 运营 | +------+----------+--------+------+--------+------+--------------+ rows in set (0.01 sec) #注意 union与union all的区别：union会去掉相同的纪录
2.子查询
#1：子查询是将一个查询语句嵌套在另一个查询语句中。 #2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。 #3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字 #4：还可以包含比较运算符：= 、 !=、> 、<等
(1) 带 in 关键字的子查询
#查询平均年龄在25岁以上的部门名 select id,name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25); # 查看技术部员工姓名 select name from employee where dep_id in (select id from department where name='技术'); #查看不足1人的部门名 select name from department where id not in (select dep_id from employee group by dep_id);
(2) 带比较运算符的子查询
#比较运算符：=、!=、>、>=、<、<=、<> #查询大于所有人平均年龄的员工名与年龄 mysql> select name,age from employee where age > (select avg(age) from employee); +---------+------+ | name | age | +---------+------+ | a | 48 | | c | 38 | +---------+------+ #查询大于部门内平均年龄的员工名、年龄 思路： （1）先对员工表(employee)中的人员分组(group by)，查询出dep_id以及平均年龄。 (2)将查出的结果作为临时表,再对根据临时表的dep_id和employee的dep_id作为筛选条件将employee表和临时表进行内连接。 (3)最后再将employee员工的年龄是大于平均年龄的员工名字和年龄筛选。 mysql> select t1.name,t1.age from employee as t1 inner join (select dep_id,avg(age) as avg_age from employee group by dep_id) as t2 on t1.dep_id = t2.dep_id where t1.age > t2.avg_age; +------+------+ | name | age | +------+------+ | b | 48 |
(3) 带EXISTS关键字的子查询
#EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。而是返回一个真假值。True或False #当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询 #department表中存在dept_id=203，Ture mysql> select * from employee where exists (select id from department where id=200); +----+----------+--------+------+--------+ | id | name | sex | age | dep_id | +----+----------+--------+------+--------+ | 1 | a | male | 18 | 200 | | 2 | b | female | 48 | 201 | | 3 | c | male | 38 | 201 | | 4 | d | female | 28 | 202 | | 5 | e | male | 18 | 200 | | 6 | f | female | 18 | 204 | +----+----------+--------+------+--------+ #department表中存在dept_id=205，False mysql> select * from employee where exists (select id from department where id=204); Empty set (0.00 sec)
八. 索引
1. 索引
数据库中专门用于帮助用户快速查找数据的一种数据结构. 类似于字典中的目录, 查找字典内容时可以根据目录查找到数据的存放位置, 然后直接获取. 索引的作用是约束和查找.
(1) 建索引的目的:
a.额外的文件保存特殊的数据结构
b.查询快，但是插入更新删除依然慢
c.创建索引之后，必须命中索引才能有效
(2) 索引的种类
hash索引和BTree索引 （1）hash类型的索引：查询单条快，范围查询慢 （2）btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）
2. 常见的索引
- 普通索引 - 唯一索引 - 主键索引 - 联合索引（多列） - 联合主键索引 - 联合唯一索引 - 联合普通索引
3. 普通索引
作用：仅有一个加速查找
创建表 create table userinfo( nid int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, index ix_name(name) # 创建普通索引 ); 创建普通索引 create index 索引的名字 on 表名(列名) 删除索引 drop index 索引的名字 on 表名 查看索引 show index from 表名
4. 唯一索引
唯一索引有两个功能：加速查找和唯一约束（可含null）
创建表+唯一索引 create table userinfo( id int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, unique index ix_name(name) );
创建唯一索引 create unique index 索引名 on 表名(列名) 删除唯一索引 drop index 索引名 on 表名;
5. 主键索引
主键索引有两个功能： 加速查找和唯一约束（不含null）
3 创建表+主键索引 create table userinfo( id int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, unique index ix_name(name) ) or create table userinfo( id int not null auto_increment, name varchar(32) not null, email varchar(64) not null, primary key(nid), unique index ix_name(name) ) 创建主键索引 alter table 表名 add primary key(列名); 删除主键索引 alter table 表名 drop primary key; alter table 表名 modify 列名 int, drop primary key;
6. 组合索引
组合索引是将n个列组合成一个索引, 其应用场景为：频繁的同时使用n列来进行查询，
创建组合索引 create index 索引名 on 表名(列名1,列名2);
7. 索引的注意事项
(1)避免使用select * (2)count(1)或count(列) 代替count(*) (3)创建表时尽量使用char代替varchar (4)表的字段顺序固定长度的字段优先 (5)组合索引代替多个单列索引（经常使用多个条件查询时） (6)尽量使用短索引 （create index ix_title on tb(title(16));特殊的数据类型 text类型） (7)使用连接（join）来代替子查询 (8)连表时注意条件类型需一致 (9)索引散列（重复少）不适用于建索引，例如：性别不合适
无限的我，现在才开始绽放，从东边的第一缕阳光到西边的尽头
