mysql

榕树下的蜗牛
271 阅读29分钟

基本操作

mysql引擎

查看默认引擎

show variables like 'default_storage_engine%';

查看引擎类型

show engines;

Support 列的值表示某种引擎是否能使用，YES表示可以使用，NO表示不能使用，DEFAULT表示该引擎为当前默认的存储引擎。

设置存储引擎

SET default_storage_engine=<引擎名称>;

修改数据表的存储引擎

alter tabel <表名> engine=<存储引擎名>;
存储引擎描述
ARCHIVE用于数据存档的引擎，数据被插入后就不能在修改了，且不支持索引。
CSV在存储数据时，会以逗号作为数据项之间的分隔符。
BLACKHOLE会丢弃写操作，该操作会返回空内容。
FEDERATED将数据存储在远程数据库中，用来访问远程表的存储引擎。
InnoDB具备外键支持功能的事务处理引擎
MEMORY置于内存的表
MERGE用来管理由多个 MyISAM 表构成的表集合
MyISAM主要的非事务处理存储引擎
NDBMySQL 集群专用存储引擎

MySQL存储引擎特性汇总和对比

特性MyISAMInnoDBMEMORY
存储限制有支持有
事务安全不支持支持不支持
锁机制表锁行锁表锁
B树索引支持支持支持
哈希索引不支持不支持支持
全文索引支持不支持不支持
集群索引不支持支持不支持
数据缓存支持支持
索引缓存支持支持支持
数据可压缩支持不支持不支持
空间使用低高N/A
内存使用低高中等
批量插入速度高低高
支持外键不支持支持不支持
  • .frm：表结构定义文件，存放着该数据表的结构定义。
  • .MYD：MY Data 的缩写，数据文件，存放着该数据表中各个行的数据。
  • .MYI： MY Index 的缩写，索引文件，存放着该数据表的全部索引信息。

MyISAM存储引擎

MyISAM存储引擎的表在数据库中，每一个表都被存放为三个以表名命名的物理文件。首先肯定会有任何存储引擎都不可缺少的存放表结构定义信息的.frm文件，另外还有.MYD和.MYI文件，分别存放了表的数据(.MYD)和索引数据(.MYI)。每个表都有且仅有这样三个文件做为MyISAM存储类型的表的存储，也就是说不管这个表有多少个索引，都是放在同一个.MYI文件中。

MyISAM支持以下三种类型的索引：

一、B-Tree索引

B-Tree索引，顾名思义，就是所有的索引节点都按照balancetree的数据结构来存储，所有的索引数据节点都在叶节点。

二、R-Tree索引

R-Tree索引的存储方式和B-Tree索引有一些区别，主要设计用于为存储空间和多维数据的字段做索引，所以目前的MySQL版本来说，也仅支持geometry类型的字段作索引

三、Full-text索引

Full-text索引就是我们常说的全文索引，他的存储结构也是b-tree。主要是为了解决在我们需要用like查询的低效问题。

特点：

1.MyIASM是MySQL默认的引擎，但是它没有提供对数据库事务的支持，也不支持行级锁和外键
2.当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些
3.MyISAM中存储了表的行数，于是select count(*) from table 时只需要直接读取已经保存好的值而不需要进行全表扫描
4.如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyIASM也是很好的选择
5.MyIASM支持全文类型索引

适合查询多于写入类业务

Innodb存储引擎

对于 InnoDB 存储引擎的数据表，一个表对应两个文件，一个是 *.frm，存储表结构信息；一个是 *.ibd，存储表中数据。

1.支持事务安装
2.数据多版本读取
3.行级锁、实现外键约束
4.没有保存表的行数，当select count(*) from table 时需要扫描全表
5.MySQL运行时Innodb会在内存中建立缓冲池，用于缓冲数据和索引
6.Innodb不支持全文索引

适合写入多于查询类业务

Memory

Memory 存储引擎的数据表是创建在内存中的数据表。因为 MySQL 服务器把 Memory 数据表的数据和索引都存放在了内存中而不是硬盘上，所以除了相应的 .frm 文件外，Memory 引擎表在文件系统里没有其它相应的代表文件。

Archive

Archive 存储引擎的数据表除了拥有 .frm 表结构定义文件外，还有一个扩展名为 .arz 的数据文件，用来存储历史归档数据。执行优化操作时可能还会出现一个扩展名为 .arn 的文件。

Csv

与其它类型的存储引擎相同，CSV 引擎表也会包含一个 .frm 表结构定义文件，此外还会创建一个扩展名为 .CSV 的数据文件。这个文件是 CSV 格式的文本文件，用来保存表中的实际数据。

.CSV 文件可以直接在 Excel 中打开，或者是使用其它文件编辑工具查看。另外，还有一个同名的元信息文件，文件扩展名为 .CSM，用来保存表的状态及表中保存的数据量。

由于 CSV 文件可被直接编辑，如果操作得当，可以不通过 SQL 语句直接修改 CSV 文件中的内容。

CSV 存储引擎基于 CSV 格式文件存储数据，由于自身文件格式的原因，所有列必须强制指定 NOT NULL。

Blackhole

由于在 BLACKHOLE 存储引擎的数据表中写入任何数据都会消失，所以除了 .frm 文件，BLACKHOLE 引擎表没有其他相应的代表文件。

转存失败，建议直接上传图片文件

linux安装

--1.下载mysql源安装包
$ wget http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm

--2.安装mysql源
$ yum localinstall mysql57-community-release-el7-8.noarch.rpm 

--3.检查mysql源是否安装成功
$ yum repolist enabled | grep "mysql.*-community.*"

--4.修改 vim /etc/yum.repos.d/mysql-community.repo源 ，改变默认安装的mysql版本。比如要安装5.6版本，将5.7源的enabled=1改成enabled=0。然后再将5.6源的enabled=0改成enabled=1即可。

--5.安装MySQL
$ yum install mysql-community-server

--6.添加MySQL服务到系统中
$ systemctl start mysqld.service

--7.开机启动
$ systemctl enable mysqld
$ systemctl daemon-reload

--启动
$ service mysqld start
--停止
$ service mysqld stop
--重启
$ service mysqld restart

mac双系统win10安装

[mysql]  
# 设置mysql客户端默认字符集  
default-character-set=utf8  
[mysqld]  
#设置3306端口  
port = 3306  
# 设置mysql的安装目录  
basedir=C:\\software\\mysql\\mysql-5.7.21-winx64
# 设置mysql数据库的数据的存放目录  
datadir=C:\\software\\mysql\\mysql-5.7.21-winx64\\data
# 允许最大连接数  
max_connections=200  
# 服务端使用的字符集默认为8比特编码的latin1字符集  
character-set-server=utf8  
# 创建新表时将使用的默认存储引擎  
default-storage-engine=INNODB

innodb_data_file_path = ibdata1:1024M;ibdata2:512M:autoextend
innodb_flush_method            = normal

卸载mysql

$ rpm -qa |grep mysql //查看数据库的下载情况
$ yum remove mysql-*  //卸载mysql
$ find / -name mysql  //查看路径下的mysql
$ rm -rf 路径 //删除残余路径


//win10
mysqld --remove  mysql //服务名进行服务删除

mysqld --install //服务名进行服务安装

net stop 服务名 //停止服务

net start 服务名 //开启服务

配置文件

vi /etc/my.cnf

[mysql]  
#设置mysql客户端默认字符集  
default-character-set=utf8 

[mysqld]  
#设置3306端口  
port = 3306  

#设置mysql的安装目录  
basedir=D:\mysql\mysql-5.7.21-winx64  

#设置mysql数据库的数据的存放目录  
datadir=D:\mysql\mysql-5.7.21-winx64\data  

#允许最大连接数  
max_connections=200  

#服务端使用的字符集默认为8比特编码的latin1字符集  
character-set-server=utf8  

#创建新表时将使用的默认存储引擎  
default-storage-engine=INNODB 

#优化远程连接
skip-name-resolve

启动mysql

net start mysql;-----启动mysql服务
net stop mysql;-----停止mysql服务

yum 安装的mysql

systemctl status mysqld -----查看状态
systemctl stop mysqld -----停止服务
systemctl start mysqld -----启动服务
systemctl restart mysqld -----重启服务

修改默认密码

//查看默认密码
$ grep "password" /var/log/mysqld.log
//登录mysql
$ mysql -u root -p
//修改密码的规则参数
mysql> set global validate_password_policy=0;
//修改密码的长度
mysql> set global validate_password_length=1;
//修改密码
mysql> alter user 'root'@'localhost' identified by '123456';
//设置远程权限
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;

登录数据库

mysql -u root -p      //需要输入密码
mysql -uroot -proot   //账号密码一步操作
mysql -h127.0.0.1 -uroot -proot //远程登录mysql
exit / quit 退出命令行

数据库的备份和还原

命令行

--备份
$ mysqldump -u用户名 -p密码 数据库名称 > 保存的路径 

--还原
步骤：
	1.登录数据库
	2.创建数据库
	3.使用数据库
	4.执行文件
mysql> source 文件路径名

图形化工具

--备份
1.右键数据库名——>备份/导出——>备份数据库，转储到sql
2.选择保存路径——>点击导出——>完成

--还原
1.菜单数据库——>导入——>执行sql脚本

默认数据库

  • information_schema：主要存储了系统中的一些数据库对象信息，比如用户表信息、列信息、权限信息、字符集信息和分区信息等。
  • mysql：MySQL 的核心数据库，类似于 SQL Server 中的 master 表，主要负责存储数据库用户、用户访问权限等 MySQL 自己需要使用的控制和管理信息。常用的比如在 mysql 数据库的 user 表中修改 root 用户密码。
  • performance_schema：主要用于收集数据库服务器性能参数。
  • sakila：MySQL 提供的样例数据库，该数据库共有 16 张表，这些数据表都是比较常见的，在设计数据库时，可以参照这些样例数据表来快速完成所需的数据表。
  • sys：MySQL 5.7 安装完成后会多一个 sys 数据库。sys 数据库主要提供了一些视图，数据都来自于 performation_schema，主要是让开发者和使用者更方便地查看性能问题。
  • world：world 数据库是 MySQL 自动创建的数据库，该数据库中只包括 3 张数据表，分别保存城市，国家和国家使用的语言等内容。

help帮助

  • help_category：关于帮助主题类别的信息
  • help_keyword：与帮助主题相关的关键字信息
  • help_relation：帮助关键字信息和主题信息之间的映射
  • help_topic：帮助主题的详细内容

查询帮助文档目录列表

help 'contents';

查看具体内容

help '文档目录列表项';

数据类型

分类类型名称说明
整数类型tinyint微整形：很小的整数(占 8 位二进制，1字节)
snallint小整型:小的整数(占 16 位二进制，2字节)
mediumint中整型:中等长度的整数(占 24 位二进制，3字节)
int(integer)整型:整数类型(占 32 位二进制，4字节)
bigint大整数：(占64位二进制，8字节)
浮点数float单精度浮点数，占 4 个字节
double双精度浮点数，占 8 个字节
decimal(m,d)压缩严格的定点数
日期和时间yearYEAR 类型是一个单字节类型，用于表示年 yyyy，1字节
time表示时间类型 HH:MM:SS 3字节
date表示日期类型 yyyy-MM-dd 3字节
datetime同时可以表示日期和时间类型 yyyy-MM-dd HH:mm:ss 8字节
timestamp时间戳类型 yyyy-MM-dd HH:mm:ss 默认系统时间 4字节
字符串char(m)固定长度的字符串，无论使用几个字符都占满全部，M 为 0~255 之间的整数
varchar(m)可变长度的字符串，使用几个字符就占用几个，M 为 0~65535 之间的整数
大二进制tinyblob Big Large Object允许长度 0~255 字节
blob允许长度 0~65535 字节
mediumblob允许长度 0~167772150 字节
longblob允许长度 0~4294967295 字节
大文本tinytext允许长度 0~255 字节
text允许长度 0~65535 字节
mediumtext允许长度 0~167772150 字节
longtext允许长度 0~4294967295 字节

常见的错误代码

服务器端

错误代码说 明
1004无法创建文件
1005无法创建数据表、创建表失败
1006无法创建数据库、创建数据库失败
1007无法创建数据库，数据库己存在
1008无法删除数据库，数据库不存在
1009不能删除数据库文件导致删除数据库失败
1010不能删除数据目录导致删除数据库失败
1011删除数据库文件时出错
1012无法读取系统表中的记录
1013无法获取的状态
1014无法获得工作目录
1015无法锁定文件
1016无法打开文件
1017无法找到文件
1018无法读取的目录
1019无法为更改目录
1020记录已被其它用户修改
1021硬盘剩余空间不足，请加大硬盘可用空间
1022关键词重读，更改记录失败
1023关闭时发生错误
1025更改名字时发生错误
1032记录不存在
1036数据表是只读的，不能对它进行修改
1037系统内存不足，请重启数据库或重启服务器
1042无效的主机名
1044当前用户没有访问数据库的权限
1045不能连接数据库，用户名或密码错误

客户端

错误代码说 明
2000未知 MySQL 错误
2001不能创建 UNIX 套接字（%d）
2002不能通过套接字“ %s”（%d）连接到本地 MySQL 服务器， self 服务未启动
2003不能连接到 %s ”（%d ）上的 MySQL 服务器，未启动 mysql 服务
2004不能创建 TCP/IP 接字（%d）
2005未知的 MySQL 服务器主机“ %s”（%d）
2007协议不匹配，服务器版本=%d，客户端版本=%d
2008MySQL 客户端内存溢出
2009错误的主机信息
2010通过 UNIX 套接字连接的本地主机
2012服务器握手过程中出错
2013查询过程中丢失了与 SQL 服务器的连接
2014命令不同步，现在不能运行该命令
2024连接到从服务器时出错
2025连接到主服务器时出错
2026SSL 连接错误

一、SQL：结构查询语言

Structure Query Language

定义了操作所有关系型数据库的规则

二、DCL： 数据控制语言

Data Control Language

定义访问权限，取消访问权限，安全设置 grant

管理用户

创建用户

三种方式

1.使用 CREATE USER 语句创建用户

--修改密码的规则参数
mysql> set global validate_password_policy=0;
--修改密码的长度
mysql> set global validate_password_length=1;

create user '<用户名>'@'<host地址>' identified by '<密码>';

--查看用户密码
select password('<用户名>');

2.在 mysql.user 表中添加用户

insert into mysql.user(host,user,authentication_string,ssl_cipher,x509_issuer,x509_subject) values('<ip>','<用户名>',password('<密码>'),'','','');

--使新用户生效
flush privileges;

3.使用 GRANT 语句创建用户

grant select on*.* to '<用户名>'@<ip> identified by '<密码>';

--“*.*” 表示所有数据库下的所有表，都有查询（SELECT）权限

修改用户

rename user '<旧用户>'@'<ip>' to '<新用户>'@'<ip>';
--密码不变

删除用户

DROP USER '用户名'@'主机名';
或者
delete from mysql.user where host='<ip>' and user='<用户名>'

查看用户权限

select * from mysql.user;
或者
show grants for '<用户名>'@'<ip>';

修改用户密码

--方式一
UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi';

--方式二
UPDATE MySQL.user SET authentication_string = PASSWORD("newpwd") WHERE User = "username" AND Host = "hostname";

--方式三
SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');

--方式四
GRANT USAGE ON *.* TO 'user'@’hostname’ IDENTIFIED BY 'newpwd';

--修改root账户密码
mysqladmin -u root [-h <ip>] -p password "<newpwd>";

忘记root用户密码

--1.停止mysql服务,需要管理员运行该cmd
$ net stop mysql 
--2.使用无验证方式启动mysql服务
$ mysqld --skip-grant-tables
--3.打开新的cmd窗口,直接输入mysql命令，敲回车。就可以登录成功
--4.使用mysql数据库
$ use mysql;
--5.修改密码
$ update user set password = password('你的新密码') where user = 'root';
--6. 关闭两个窗口
--7. 打开任务管理器，手动结束mysqld.exe 的进程
--8. 启动mysql服务
--9. 使用新密码登录。

查询用户

-- 1. 切换到mysql数据库
USE myql;
-- 2. 查询user表
SELECT * FROM USER;
			
* 通配符： % 表示可以在任意主机使用用户登录数据库

授权

查询权限

SHOW GRANTS FOR '用户名'@'主机名';
SHOW GRANTS FOR 'lisi'@'%';

授予权限

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

-- 给张三用户授予所有权限，在任意数据库任意表上
GRANT ALL ON *.* TO 'zhangsan'@'localhost';

grant <权限列表> on <数据库名>.<表名> to '[新]用户名'@'主机名' [identified by '<密码>'] [with <grant option>];

撤销权限

revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';

用户授权

GRANT priv_type [(column_list)] ON database.table
TO user [IDENTIFIED BY [PASSWORD] 'password']
[, user[IDENTIFIED BY [PASSWORD] 'password']] ...
[WITH with_option [with_option]...]

其中：

  • priv_type 参数表示权限类型；
  • columns_list 参数表示权限作用于哪些列上，省略该参数时，表示作用于整个表；
  • database.table 用于指定权限的级别；
  • user 参数表示用户账户，由用户名和主机名构成，格式是“'username'@'hostname'”；
  • IDENTIFIED BY 参数用来为用户设置密码；
  • password 参数是用户的新密码。

WITH 关键字后面带有一个或多个 with_option 参数。这个参数有 5 个选项，详细介绍如下：

  • GRANT OPTION：被授权的用户可以将这些权限赋予给别的用户；
  • MAX_QUERIES_PER_HOUR count：设置每个小时可以允许执行 count 次查询；
  • MAX_UPDATES_PER_HOUR count：设置每个小时可以允许执行 count 次更新；
  • MAX_CONNECTIONS_PER_HOUR count：设置每小时可以建立 count 个连接;
  • MAX_USER_CONNECTIONS count：设置单个用户可以同时具有的 count 个连接。

MySQL 中可以授予的权限有如下几组：

  • 列权限，和表中的一个具体列相关。例如，可以使用 UPDATE 语句更新表 students 中 name 列的值的权限。
  • 表权限，和一个具体表中的所有数据相关。例如，可以使用 SELECT 语句查询表 students 的所有数据的权限。
  • 数据库权限，和一个具体的数据库中的所有表相关。例如，可以在已有的数据库 mytest 中创建新表的权限。
  • 用户权限，和 MySQL 中所有的数据库相关。例如，可以删除已有的数据库或者创建一个新的数据库的权限。

对应地，在 GRANT 语句中可用于指定权限级别的值有以下几类格式：

  • *：表示当前数据库中的所有表。
  • .：表示所有数据库中的所有表。
  • db_name.*：表示某个数据库中的所有表，db_name 指定数据库名。
  • db_name.tbl_name：表示某个数据库中的某个表或视图，db_name 指定数据库名，tbl_name 指定表名或视图名。
  • db_name.routine_name：表示某个数据库中的某个存储过程或函数，routine_name 指定存储过程名或函数名。
  • TO 子句：如果权限被授予给一个不存在的用户，MySQL 会自动执行一条 CREATE USER 语句来创建这个用户，但同时必须为该用户设置密码。

权限类型说明

1）授予数据库权限时，<权限类型>可以指定为以下值：

权限名称对应user表中的字段说明
SELECTSelect_priv表示授予用户可以使用 SELECT 语句访问特定数据库中所有表和视图的权限。
INSERTInsert_priv表示授予用户可以使用 INSERT 语句向特定数据库中所有表添加数据行的权限。
DELETEDelete_priv表示授予用户可以使用 DELETE 语句删除特定数据库中所有表的数据行的权限。
UPDATEUpdate_priv表示授予用户可以使用 UPDATE 语句更新特定数据库中所有数据表的值的权限。
REFERENCESReferences_priv表示授予用户可以创建指向特定的数据库中的表外键的权限。
CREATECreate_priv表示授权用户可以使用 CREATE TABLE 语句在特定数据库中创建新表的权限。
ALTERAlter_priv表示授予用户可以使用 ALTER TABLE 语句修改特定数据库中所有数据表的权限。
SHOW VIEWShow_view_priv表示授予用户可以查看特定数据库中已有视图的视图定义的权限。
CREATE ROUTINECreate_routine_priv表示授予用户可以为特定的数据库创建存储过程和存储函数的权限。
ALTER ROUTINEAlter_routine_priv表示授予用户可以更新和删除数据库中已有的存储过程和存储函数的权限。
INDEXIndex_priv表示授予用户可以在特定数据库中的所有数据表上定义和删除索引的权限。
DROPDrop_priv表示授予用户可以删除特定数据库中所有表和视图的权限。
CREATE TEMPORARY TABLESCreate_tmp_table_priv表示授予用户可以在特定数据库中创建临时表的权限。
CREATE VIEWCreate_view_priv表示授予用户可以在特定数据库中创建新的视图的权限。
EXECUTE ROUTINEExecute_priv表示授予用户可以调用特定数据库的存储过程和存储函数的权限。
LOCK TABLESLock_tables_priv表示授予用户可以锁定特定数据库的已有数据表的权限。
ALL 或 ALL PRIVILEGES 或 SUPERSuper_priv表示以上所有权限/超级权限
  1. 授予表权限时，<权限类型>可以指定为以下值：
权限名称对应user表中的字段说明
SELECTSelect_priv授予用户可以使用 SELECT 语句进行访问特定表的权限
INSERTInsert_priv授予用户可以使用 INSERT 语句向一个特定表中添加数据行的权限
DELETEDelete_priv授予用户可以使用 DELETE 语句从一个特定表中删除数据行的权限
DROPDrop_priv授予用户可以删除数据表的权限
UPDATEUpdate_priv授予用户可以使用 UPDATE 语句更新特定数据表的权限
ALTERAlter_priv授予用户可以使用 ALTER TABLE 语句修改数据表的权限
REFERENCESReferences_priv授予用户可以创建一个外键来参照特定数据表的权限
CREATECreate_priv授予用户可以使用特定的名字创建一个数据表的权限
INDEXIndex_priv授予用户可以在表上定义索引的权限
ALL 或 ALL PRIVILEGES 或 SUPERSuper_priv所有的权限名

  1. 授予列权限时，<权限类型>的值只能指定为 SELECT、INSERT 和 UPDATE，同时权限的后面需要加上列名列表 column-list。

  2. 最有效率的权限是用户权限。

授予用户权限时，<权限类型>除了可以指定为授予数据库权限时的所有值之外，还可以是下面这些值：

  • CREATE USER：表示授予用户可以创建和删除新用户的权限。
  • SHOW DATABASES：表示授予用户可以使用 SHOW DATABASES 语句查看所有已有的数据库的定义的权限。

三、DDL： 数据定义语言

Data Definition Language

操作数据库、表

create  --创建

drop  --删除

alter  --修改

数据库的(增删改查操作DDL)

CRUD create read update delete

1.创建数据库

语法：

create database [if not exists] <数据库名> --在创建数据库之前进行判断，只有该数据库目前尚不存在时才能执行操作。此选项可以用来避免数据库已经存在而重复创建的错误。

[[default] character set <字符集名>]  --指定数据库的字符集。指定字符集的目的是为了避免在数据库中存储的数据出现乱码的情况。如果在创建数据库时不指定字符集，那么就使用系统的默认字符集。

[[default] collate <校对规则名>]; --指定字符集的默认校对规则

实例：基本方式创建数据库

create database test_db;

实例：if not exists从句创建数据库

create database if not exists test_db; --创建数据库之前判断test_db数据库是否存在，存在就不创建

实例：创建数据库时添加字符集

create database if not exists test_db_char default character set utf8 default collate utf8_general_ci; --default关键字可以省略

综合语句创建

create database if not exists blog character set utf8/gbk;

2.查看数据库

查看所有数据库

show databases;  --查看所有数据库

使用LIKE从句搜索数据库

show databases [LIKE '数据库名'];

show databases like 'test_db'; --查看全匹配的数据库
show databases like '%test%';  --查看名字中包含 test 的数据库
show databases like 'db%';     --查看名字以 db 开头的数据库
show databases like '%db';     --查看名字以 db 结尾的数据库

查看创建的数据库信息

show create database 数据库名字;
show create database blog;  --查看创建的数据库信息，字符集

3.修改数据库

语法：

alter database [数据库名] { 
	[ default ] character set <字符集名> |
	[ DEFAULT ] collate <校对规则名>
}

修改数据库的字符集

alter database 数据库的名字  character set 字符集 collate 校对规则;
alter database test_db_char character set gbk collate gbk_chinese_ci;

4.使用数据库

语法：

use <数据库名>

实例：

use 数据库名字; 
use blog;

5.查看当前使用的数据库

select database();

6.删除数据库

语法：

drop database [if exists] <数据库名称>;

drop database 数据库名字;
drop database student;

--判断存在就删除
drop database if exists 数据库名称;

数据表(增删改查操作DDL)

1.创建表

语法：

create table <表名> ([表定义选项])[表选项][分区选项];

create table 表名(列名 列的类型(长度) 列的约束，...)

create table student(sid int primary key auto_increment,sname varchar(31),sex int,age int);

列的类型

int char varchar float double boolean date time datetime timestamp text blob

列的约束

--主键约束  primary key
--id自增长  auto_increment
--唯一约束  unique
--非空约束  not null

2.查看表

  • 查看所有表
show tables;
  • 查看创建表的过程
show create table <表名>;
show create table student;
  • 查看表结构
desc <表名>;
desc student;
  • Null：表示该列是否可以存储 NULL 值。
  • Key：表示该列是否已编制索引。PRI 表示该列是表主键的一部分，UNI 表示该列是 UNIQUE 索引的一部分，MUL 表示在列中某个给定值允许出现多次。
  • Default：表示该列是否有默认值，如果有，值是多少。
  • Extra：表示可以获取的与给定列有关的附加信息，如 AUTO_INCREMENT 等。

3.修改表

语法：

alter table <表名> [修改选项]

修改选项语法：

{ 
	ADD COLUMN <列名> <类型>
	| CHANGE COLUMN <旧列名> <新列名> <新列类型>
	| ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }
	| MODIFY COLUMN <列名> <类型>
	| DROP COLUMN <列名>
	| RENAME TO <新表名>
	| CHARACTER SET <字符集名>
	| COLLATE <校对规则名> 
}
  • 在末尾添加字段(add)
alter table <表名> add <新字段名> <字段类型> [约束条件]
alter table student add age int not null;
  • 在开头添加字段(add)
alter table <表名> add <新字段名> <字段类型> [字段约束] first;
  • 在中间位置添加字段(add)
alter table <表名> add <新字段名> <字段类型> [字段约束] after <存在的字段>;
  • 修改字段数据类型(modify)
alter table <表名> modify <字段名> <新数据类型>;
alter table student modify sex varchar(2);
  • 修改字段名称(change)
alter table <表名> change <旧字段名> <新字段名> <新字段的类型>;
alter table student change chengji count int;
  • 删除字段(drop)
alter table <表名> drop <字段名>;
alter table student drop count;
  • 修改表名(rename)
--第一种方式
rename table 旧表名 to 新表名;
rename table student to heima;

--第二种方式
alter table 旧表名 rename to 新表名；
  • 修改表的字符集(character set)
alter table 表名 character set 字符集 collate <校队规则名>;
alter table users character set gbk collate gbk_chinese_ci;
--修改列的编码
alter table score change score score varchar(50) character utf8;

4.删除表

语法：

drop table [if exists] 表名1 [表名2,表名3,...]

drop table 表名；
--判断是否存在
drop table if exists 表名;

解除外键约束

alter table <子表名> drop foreign key <外键约束名>;
alter table tb_emp2 drop foreign key fk_emp1_emp2;

--注：解除外键约束后再删除表

5.复制表

create table 表名 like 被复制的表名;

6.复制相同表数据

insert into <有数据的表名>(<所有字段名>) select <所有字段名> from <空表名>;

insert into temp2(id,name,age) select id,name,age from temp1;

字段约束

对表中的数据进行限定，保证数据的正确性，有效性和完整性。

主键约束(primary key)

默认就是不能为空，唯一

外键都是指向另外一张表的主键

主键一张表只能有一个

--创建表时添加
字段名 字段类型 primary key,
id int primary key,

--给字段添加主键约束
alter table 表名 modify 字段名 字段类型 primary key;

--删除主键约束
alter table 表名 drop primary key;

--设置联合主键
primary key [字段1，字段2...]

--修改表时添加主键约束
alter table <数据表名> add primary key(<字段名>);

自动增长(auto_increment)

  • 默认情况下，auto_increment 的初始值是 1，每新增一条记录，字段值自动加 1。
  • 一个表中只能有一个字段使用 auto_increment约束，且该字段必须有唯一索引，以避免序号重复（即为主键或主键的一部分）。
  • auto_increment约束的字段必须具备 NOT NULL 属性。
  • auto_increment约束的字段只能是整数类型（tinyint、smallint、int、bigint 等）。
  • auto_increment约束字段的最大值受该字段的数据类型约束，如果达到上限，auto_increment就会失效
--创建表时添加
字段名 字段类型 auto_increment,
id int auto_increment,

--和主键连用
id int primary key auto_increment,

--给字段添加自动增长
alter table 表名 modify 字段名 字段类型 auto_increment;

--指定自增字段初始值
create table <表名>(字段...) auto_increment = <初始值>

--删除自动增长
alter table 表名 modify 字段名 字段类型;
alter table 表名 modify id int;

非空约束(not null)

--给字段添加非空
alter table 表名 modify 字段名 字段类型 not null;

--在创建表时设置非空约束
字段名 字段类型 not null,

--在修改表时添加非空约束
alter table <表名> change column <字段名> <字段名> <数据类型> not null;

--删除非空字段约束
alter table 表名 modify 字段名 字段类型;
--or
alter table <表名> change column <字段名> <字段名> <数据类型> null;

唯一约束(unique)

唯一约束不可以作为其他表的外键

可以有多个唯一约束

列的内容，必须是唯一，不能出现重复情况，可以为null

--在修改表时添加唯一约束
alter table <数据表名> add constraint <唯一约束名> unique(<列名>);

--给字段添加非空
alter table 表名 modify 字段名 字段类型 unique;

--在创建表时设置为一约束
字段名 字段类型 unique,

--删除唯一约束
alter table <表名> drop index <唯一约束名>;

外键约束(foreign key)

外键可以为null，不能为不存在的值

--给字段添加外键约束
alter table 表名 add constraint 外键名称 foreign key(外键) references 表名(主键);
alter table product add constraint pro_fk foreign key(cno) references category(cid);

--创建表时添加外键约束
create table emp(
	id int primary key auto_increment,
  cname varchar(20),
  age int,
  dep_id int,
 	constraint emp_dept_fk foreign key(dep_id) references department(id)
)
--语法：constraint 外键名称 foreign key(外键列名称) references 主表名称(主键名称)

--删除外键约束
alter table 含有外键的表名 drop foreign key 外键名称;

检查约束(check)

--选取设置检查约束的字段
check <表达式>

--在创建表时设置检查约束
check(<检查约束>)
check(age > 0 and age < 150 ),

--在修改表时添加检查约束
alter table <表名> add constraint <检查约束名> check(<检查约束>)
alter table tb_emp7 add constraint check_id check(id>0);

--删除检查约束
alter table <表名> drop constraint <检查约束名>;

默认值约束(default)

--在创建表时设置默认值约束
<字段名> <数据类型> default <默认值>;

--在修改表时添加默认值约束
alter table <表名> change column <字段名> <数据类型> default <默认值>;

--删除默认值约束
alter table <表名> change column <字段名> <字段名> <数据类型> default null;

级联操作(慎用)

--级联更新 on update cascade
--级联删除 on delete cascade


--添加级联更新和级联删除
alter table 表名 add constraint 外键名称 foreign key(外键) references 表名(主键) on update cascade on delete cascade;

alter table product add constraint pro_fk foreign key(cno) references category(cid) on update cascade on delete cascade;

CREATE TABLE sys_user_role(
userId INT,
roleId INT,
PRIMARY KEY(userId,roleId), 
FOREIGN KEY (userId) REFERENCES sys_USER(id),
FOREIGN KEY (roleId) REFERENCES sys_role(id)
)

运算符

算术运算符

运算符作用使用方法
+加法运算用于获得一个或多个值的和
-减法运算用于从一个值中减去另一个值
*乘法运算使数字相乘，得到两个或多个值的乘积
/除法运算，返回商用一个值除以另一个值得到商
%，MOD求余运算，返回余数用一个值除以另一个值得到余数

逻辑运算符

运算符作用
NOT 或者 !逻辑非
AND 或者 &&逻辑与
OR 和 ||逻辑或
XOR逻辑异或

XOR 表示逻辑异或，具体语法规则为：

  • 当任意一个操作数为 NULL 时，返回值为 NULL；
  • 对于非 NULL 的操作数，如果两个操作数都是非 0 值或者都是 0 值，则返回值为 0；
  • 如果一个为0值，另一个为非 0 值，返回值为 1。

比较运算符

运算符作用
=等于
<=>安全的等于
<> 或者 !=不等于
<=小于等于
>=大于等于
大于
IS NULL 或者 ISNULL判断一个值是否为空
IS NOT NULL判断一个值是否不为空
BETWEEN AND判断一个值是否落在两个值之间，expr BETWEEN min AND max

<=> 操作符和 = 操作符类似，不过 <=> 可以用来判断 NULL 值，具体语法规则为：

  • 当两个操作数均为 NULL 时，其返回值为 1 而不为 NULL；
  • 而当一个操作数为 NULL 时，其返回值为 0 而不为 NULL。

位运算符

运算符说明使用形式举例
|位或a | b5 | 8
&位与a & b5 & 8
位异或a ^ b5 ^ 8
~位取反~a~5
<<位左移a << b5 << 2，表示整数 5 按位左移 2 位
>>位右移a >> b5 >> 2，表示整数 5 按位右移 2 位

运算符优先级

优先级由低到高排列运算符
1=(赋值运算）、:=
2II、OR
3XOR
4&&、AND
5NOT
6BETWEEN、CASE、WHEN、THEN、ELSE
7=(比较运算）、<=>、>=、>、<=、<、<>、!=、 IS、LIKE、REGEXP、IN
8|
9&
10<<、>>
11-(减号）、+
12*、/、%
13
14-(负号）、〜（位反转）
15!

四、DML： 数据操作语言

Data Manipulation Language

主要是用来增、删、改表中的数据

insert  --插入

update  --修改

delete  --删除

增删改表中的数据(DML)

1.插入数据

插入某一些字段

insert into 表名(列1，列2，列3) values(值1，值2，值3);
insert into users(sid,sname,sex,age)values(1000001,"张三","男",14);
--注：如果主键是自增长，则不写主键字段，或者写主键字段值为null。

简写插入字段全部插入

insert into 表名 values(值1，值2，值3);
insert into users values(1000002,'wangsi',1,13);
--注：如果主键是自增长，则对应的值写成null。

批量插入

insert into 表名 values(值1，值2，值3),(值1，值2，值3),(值1，值2，值3);

insert...set语句

insert into 表名 set 字段名=值,字段名=值,...

2.删除记录

删除符合条件的记录

delete from 表名 [where 条件]
delete from users where sname='李四';

删除全部记录

delete from 表名;       
--一条一条删除记录(效率低),
--配合事件回滚可以找回数据,
--系统不会重新设置自增字段的计数器
--可以通过 WHERE 子句指定条件来删除部分数据
--会返回删除数据的行数

truncate 表名；         
-- 删除全部记录后重新建表,
--不支持事务的回滚，数据删除后无法找回
--系统会重新设置自增字段的计数器
--不支持 WHERE 子句，只能删除整体
--只会返回 0，没有任何意义

3.更新表记录

update 表名 set 列名=列值，列名2=列值2 [where 条件]
update users set sname='小燕' where sid=2;

五、DQL： 数据查询语言

Data Query Language

查询表中的数据

select  --查询

from  --子句

where  --子句

查询表中的数据(DQL)

1.基本查询

(*)号查询
select * from 表名; --不建议用消耗性能
多个字段查询
select 字段1,字段2,... from 表名;
去掉重复值(distinct)
select distinct <字段1,字段2,...> from <表名>;
select distinct address from student;
判断值为null的替换成0(ifnull)
--判断值为null的替换成0
select cname,math,english,math + ifnull(english,0) from student;
字段别名(as)
select 列 as 列别名，列2 as 列别名2 from 表名;
select pname as 商品名称,price as 商品价格 from product;
select cname,math,english,math + ifnull(english,0) 总分 from student;

2.条件查询

单条件查询(where)
select * from 表名 where 条件
多条件查询(where...and)
select * from <表名> where <条件1> and <条件2> and <条件3> ...;
多条件查询(where...or)
select * from <表名> where <条件1> or <条件2>;
多条件查询(where...xor)

记录满足其中一个条件，并且不满足另一个条件时，才会被查询出来。

select * from <表名> where <条件1> xor <条件2>;
in(集合)在某个范围中获得值
--两个是一样的效果，查询age是22，18，25的。
select * from student where age = 22 or age = 18 or age = 25;
select * from student where age in (22,18,25);

select * from 表名 where 列名 in (条件);
select * from product where cno in (1,4);

3.模糊查询(like)

语法：

select * from <表名> where <字段名> [not] like '字符串';

--NOT LIKE 表示字符串不匹配时满足条件。

_ --代表的是一个任意字符
% --代表的是多个任意字符

--默认情况下，LIKE 关键字匹配字符的时候是不区分大小写的。如果需要区分大小写，可以加入 BINARY 关键字
select * from <表名> where <字段名> [not] like [binary] '字符串';
带有“%”通配符的查询
--以字符串开头的
select * from <表名> where <字段名> like '字符串%';

--以字符串结尾的
select * from <表名> where <字段名> like '%字符串';

--包含字符串的
select * from <表名> where <字段名> like '%字符串%';
带有“_”通配符的查询
--查询第二个字是云的
select * from student where cname like '_云%';

--查询姓名是三个字的,三个_
select * from student where cname like '___';

--查询姓名中包含云的字
select * from student where cname like '%云%';

4.范围查询(Between…and)

语法：

select * from <表名> where <字段> [not] between 值1 and 值2;

NOT：可选参数，表示指定范围之外的值。如果字段值不满足指定范围内的值，则这些记录被返回。 取值1：表示范围的起始值。 取值2：表示范围的终止值。

--年龄在20于30之间
select * from student where age between 20 and 30;

5.空值查询(is null)

is [not] null

--判断null值,查询字段值为null的值
select * from <表名> where <字段名> is null;

--判断null值,查询字段值不为null的值
select * from <表名> where <字段名> is not null;

6.排序查询(order by)

使用 ORDER BY 关键字应该注意以下几个方面：

  • ORDER BY 关键字后可以跟子查询（关于子查询后面教程会详细讲解，这里了解即可）。
  • 当排序的字段中存在空值时，ORDER BY 会将该空值作为最小值来对待。
  • ORDER BY 指定多个字段进行排序时，MySQL 会按照字段的顺序从左到右依次进行排序。
asc:ascend     --升序(默认排序方式)
desc:descend   --降序(大的在前面)

select * from 表名 order by <字段名> [排序类型];
select * from product order by price desc;

select * from product where pname like '%小%' order by price asc;

--按照数学成绩排名，如果数学成绩一样，则按英语成绩排名。当第一排序相等才会执行第二排序。
select * from student order by math asc,english desc;

7.分页查询(limit)

select * from 表 limit 起始索引,查询条数;
select * from product limit 0,3;

--公式： 起始索引 = (当前的页码 - 1) * 每页显示的条数 


select * from 表 limit 查询条数 offset 初始索引;

8.分组查询(group by)

语法：

select * from <表名> group by <字段1,字段2,...>
group_concat()

函数会把每个分组的字段值都显示出来

select sex,group_concat(name) from temp group by sex;

+------+----------------------------+
| sex  | GROUP_CONCAT(name)         |
+------+----------------------------+
| 女   | Henry,Jim,John,Thomas,Tom  |
| 男   | Dany,Green,Jane,Lily,Susan |
+------+----------------------------+
with rollup

WITH POLLUP 关键字用来在所有记录的最后加上一条记录，这条记录是上面所有记录的总和，即统计记录数量。

 select sex,group_concat(name) from temp group by sex with rollup;
 
+------+------------------------------------------------------+
| sex  | GROUP_CONCAT(name)                                   |
+------+------------------------------------------------------+
| 女   | Henry,Jim,John,Thomas,Tom                            |
| 男   | Dany,Green,Jane,Lily,Susan                           |
| NULL | Henry,Jim,John,Thomas,Tom,Dany,Green,Jane,Lily,Susan |
+------+------------------------------------------------------+

--1.根据cno字段分组，分组后统计商品的个数
select 列名，count(*) from 表名 group by 列名;
select cno,count(*) from product group by cno;


--按照性别分组，分别查询男、女同学的平均分，人数。
select sex,svg(math),count(id) from student group by sex;
--分数大于70分以上的按照性别分组，分别查询男、女同学的平均分，人数,。
select sex,svg(math),count(id) from student where math > 70 group by sex;
--分数大于70分以上的按照性别分组，分别查询男、女同学的平均分，人数。并且查询出人数在两人以上的数据。
select sex,svg(math),count(id) as cp from student 
	where math > 70 group by sex having cp > 2;


--2.根据cno分组，分组统计每组商品的平均价格，并且商品平均价格 > 60
select 列名，avg(列名) from 表名 group by 列名 having 条件;
select cno,avg(price) from product group by cno having avg(price) > 60;

 having --关键字 可以接聚合函数的，出现在分组之后，如果条件不满足，则不会被查询出来。
 where  --关键字 是不可以接聚合函数，出现在分组之前,如果不满足条件，则不参与分组。

9.过滤分组(having)

select * from <表名> having <查询条件>;
having和where的区别
  • 一般情况下，WHERE 用于过滤数据行，而 HAVING 用于过滤分组。
  • WHERE 查询条件中不可以使用聚合函数，而 HAVING 查询条件中可以使用聚合函数。
  • WHERE 在数据分组前进行过滤，而 HAVING 在数据分组后进行过滤 。
  • WHERE 针对数据库文件进行过滤，而 HAVING 针对查询结果进行过滤。也就是说，WHERE 根据数据表中的字段直接进行过滤，而 HAVING 是根据前面已经查询出的字段进行过滤。
  • WHERE 查询条件中不可以使用字段别名，而 HAVING 查询条件中可以使用字段别名。

10.正则表达式查询

选项说明例子匹配值示例
^匹配文本的开始字符'^b' 匹配以字母 b 开头的字符串book、big、banana、bike
$匹配文本的结束字符'st$' 匹配以 st 结尾的字符串test、resist、persist
.匹配任何单个字符'b.t' 匹配任何 b 和 t 之间有一个字符bit、bat、but、bite
*匹配零个或多个在它前面的字符'f*n' 匹配字符 n 前面有任意个字符 ffn、fan、faan、abcn
+匹配前面的字符 1 次或多次'ba+' 匹配以 b 开头，后面至少紧跟一个 aba、bay、bare、battle
<字符串>匹配包含指定字符的文本'fa' 匹配包含‘fa’的文本fan、afa、faad
[字符集合]匹配字符集合中的任何一个字符'[xz]' 匹配 x 或者 zdizzy、zebra、x-ray、extra
[^]匹配不在括号中的任何字符'[^abc]' 匹配任何不包含 a、b 或 c 的字符串desk、fox、f8ke
字符串{n,}匹配前面的字符串至少 n 次'b{2}' 匹配 2 个或更多的 bbbb、bbbb、bbbbbbb
字符串 {n,m}匹配前面的字符串至少 n 次， 至多 m 次'b{2,4}' 匹配最少 2 个，最多 4 个 bbbb、bbbb

语法：

select * from <表名> where <字段> regexp '正则';
查询以字符串开头的记录
select * from <表名> where <字段> regexp '^字符串';
查询以字符串结尾的记录
select * from <表名> where <字段> regexp '字符串$';
替代字符串中的任意一个字符

字符.用来替代字符串中的任意一个字符

select * from <表名> where <字段> regexp 'a.y';

+----+------+------+------+--------+-----------+
| id | name | age  | sex  | height | course_id |
+----+------+------+------+--------+-----------+
|  1 | Dany |   25 | 男   |    160 |         1 |
+----+------+------+------+--------+-----------+
匹配多个字符

字符*和+都可以匹配多个该符号之前的字符。不同的是，+表示至少一个字符，而*可以表示 0 个字符。

select * from <表名> where <字段> regexp '^字符串*';
select * from <表名> where <字段> regexp '^字符串+';
匹配指定字符串
select * from <表名> where <字段> regexp '字符串';
select * from <表名> where <字段> regexp '字符串|字符串';
匹配指定字符串中的任意一个
select * from <表名> where <字段> regexp '[字符串]';
匹配指定字符以外的字符
select * from <表名> where <字段> regexp '[^字符集合]';
使用{n,}或者{n,m}来指定字符串连续出现的次数
--表示字符串连续出现 n 次
select * from <表名> where <字段> regexp '字符串{n,}';

--表示字符串连续出现至少 n 次，最多 m 次
select * from <表名> where <字段> regexp '字符串{n,m}';

常用函数

1.数值型函数

abs():     --求绝对值
sqrt():	   --求二次方根
mod():     --求余数
ceil()：    --返回不小于参数的最小整数，即向上取整
ceiling(): --返回不小于参数的最小整数，即向上取整
floor():   --向下取整，返回值转化为一个bigint
rand():    --生成一个0~1之间的随机数，传入整数参数是，用来产生重复序列
round():   --对所传参数进行四舍五入
sign():    --返回参数的符号
pow():     --所传参数的次方的结果值
power():   --所传参数的次方的结果值
sin():     --求正弦值
asin():    --求反正弦值，与函数 SIN 互为反函数
cos():     --求余弦值
acos():    --求反余弦值，与函数 COS 互为反函数
tan():     --求正切值
atan():    --求反正切值，与函数 TAN 互为反函数
cot():     --求余切值

2.字符串函数

length():   --计算字符串长度函数，返回字符串的字节长度
concat():   --合并字符串函数，返回结果为连接参数产生的字符串，参数可以使一个或多个
insert():   --替换字符串函数
lower():    --将字符串中的字母转换为小写
upper():    --将字符串中的字母转换为大写
left():     --从左侧字截取符串，返回字符串左边的若干个字符
right():    --从右侧字截取符串，返回字符串右边的若干个字符
trim():     --删除字符串左右两侧的空格
replace():  --字符串替换函数，返回替换后的新字符串
substring(): --截取字符串，返回从指定位置开始的指定长度的字符换
reverse():  --字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串

3.日期和时间函数

unix_timestamp():  --获取unix时间戳函数，返回一个以 unix 时间戳为基础的无符号整数
from_unixtime():   --将 unix 时间戳转换为时间格式，与unix_timestamp互为反函数
month():	       --获取指定日期中的月份
monthname():	   --获取指定日期中的月份英文名称
dayname():	       --获取指定曰期对应的星期几的英文名称
dayofweek():	   --获取指定日期对应的一周的索引位置值
week():	           --获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1〜53
dayofyear():	   --获取指定曰期是一年中的第几天，返回值范围是1~366
dayofmonth():	   --获取指定日期是一个月中是第几天，返回值范围是1~31
year():	           --获取年份，返回值范围是 1970〜2069
time_to_sec():	   --将时间参数转换为秒数
sec_to_time():	   --将秒数转换为时间，与time_to_sec 互为反函数
addtime():         --时间加法运算，在原始时间上添加指定的时间
subtime():	       --时间减法运算，在原始时间上减去指定的时间
datediff():	       --获取两个日期之间间隔，返回参数 1 减去参数 2 的值
date_format():	   --格式化指定的日期，根据参数返回指定格式的值
weekday():	       --获取指定日期在一周内的对应的工作日索引
curdate() 和 current_date()	--两个函数作用相同，返回当前系统的日期值
curtime() 和 current_time()	--两个函数作用相同，返回当前系统的时间值
date_add() 和 adddate()	    --两个函数功能相同，都是向日期添加指定的时间间隔
date_sub() 和 subdate()	    --两个函数功能相同，都是向日期减去指定的时间间隔
now() 和 sysdate()	        --两个函数作用相同，返回当前系统的日期和时间值

4.聚合函数

--将一列数据作为一个整体，进行纵向的计算，聚合函数的计算是排除了null值的。

sum():   --求和，返回指定列的总和
avg():   --求平均值，返回指定列数据的平均值
count(): --统计查询结果的行数
max():   --查询指定列的最大值
min():   --查询指定列的最小值

--1.获得所有商品价格的总和
select sum(列名) from 表名;
select sum(price) from product;

--2.获取所有商品的平均价格
select avg(列名) from 表名;
select avg(price) from product;

--3.获得所有商品的个数
select count(*) from 表名;
select count(*) from product;

--注意：where 条件后面不能接聚合函数

5.流程控制函数

if():	   --判断，流程控制
ifnull():  --判断是否为空
case():	   --搜索语句

编写顺序

S..F..W..G..H..O
select .. from .. where .. group by .. having .. order by

执行顺序

F..W..G..H..S..O
from .. where .. group by .. having .. select .. order by
--查找表 .. 条件判断 .. 分组 .. 条件筛选 .. 控制显示 .. 排序

六、数据库的设计

1.多表之间的关系

分类
  • 一对一：班级和班长，公民和身份证，国家和国旗
    • 建表原则：将一对一的情况，当作是一对多情况处理，在任意一张表添加一个外键，并且这个外键要唯一指向另外一张表
    • 直接将两张表合并成一张表
    • 将两张表的主键建立起连接，让两张表里主键相等
  • 一对多：商品和分类
    • 建表原则：在多的一方添加一个外键，指向一的一方的主键
  • 多对多：老师和学生、学生和课程
    • 建表原则：多建一张中间表，将多对多的关系拆成一对多关系，中间表至少要有两个外键，这两个外键分别指向原来的那张表

2.数据库设计的范式

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。 ​ 目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

第一范式（1NF）

每一列都是不可分割的原子数据项

第一范式表

学号姓名系名系主任课程名称分数
10010张无忌经济系张三丰高等数学95
10010张无忌经济系张三丰大学英语87
10010张无忌经济系张三丰计算机基础65
10011令狐冲法律系任我行法理学77
10011令狐冲法律系任我行大学英语87
10011令狐冲法律系任我行法律社会学65
10012杨过法律系任我行法律社会学98
10012杨过法律系任我行法理学76
10012杨过法律系任我行大学英语99

第二范式（2NF）

在1NF的基础上，非码属性必须完全依赖于码（在1NF基础上消除非主属性对主码的部分函数依赖）

  1. 函数依赖：A-->B,如果通过A属性(属性组)的值，可以确定唯一B属性的值。则称B依赖于A ​ 例如：学号-->姓名。 （学号，课程名称） --> 分数
  2. 完全函数依赖：A-->B， 如果A是一个属性组，则B属性值得确定需要依赖于A属性组中所有的属性值。 例如：（学号，课程名称） --> 分数
  3. 部分函数依赖：A-->B， 如果A是一个属性组，则B属性值得确定只需要依赖于A属性组中某一些值即可。 例如：（学号，课程名称） -- > 姓名
  4. 传递函数依赖：A-->B, B -- >C . 如果通过A属性(属性组)的值，可以确定唯一B属性的值，在通过B属性（属性组）的值可以确定唯一C属性的值，则称 C 传递函数依赖于A 例如：学号-->系名，系名-->系主任
  5. 码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性组)为该表的码 例如：该表中码为：（学号，课程名称） * 主属性：码属性组中的所有属性 * 非主属性：除过码属性组的属性

第二范式表

选课表

学号课程名称分数
10010高等数学95
10010大学英语87
10010计算机基础65
10011法理学77
10011大学英语87
10011法律社会学65
10012法律社会学98
10012法理学76
10012大学英语99

学生表

学号姓名系名系主任
10010张无忌经济系张三丰
10011令狐冲法律系任我行
10012杨过法律系任我行

第三范式（3NF）

在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

第三范式表

选课表

学号课程名称分数
10010高等数学95
10010大学英语87
10010计算机基础65
10011法理学77
10011大学英语87
10011法律社会学65
10012法律社会学98
10012法理学76
10012大学英语99

学生表

学号姓名系名
10010张无忌经济系
10011令狐冲法律系
10012杨过法律系

系表

系名系主任
经济系张三丰
法律系任我行
法律系任我行

七、多表查询

笛卡尔积：

有两个集合A,B .取这两个集合的所有组成情况。

要完成多表查询，需要消除无用的数据

1.交叉连接查询

语法：

select <字段名> from <表1> cross join <表2> [where子句]
或
select * from <表1>,<表2> [where子句];

--笛卡尔积,查出来是两张表的乘积
select * from 表1,表2 where 条件;
select * from product,category where cno=cid;

2.内连接查询

隐式内连接

select * from 表1,表2 where 表1.字段名 = 表2.字段名;
select * from product as p,category as c where p.cno=c.cid;

显示内连接

select * from 表1 别名1 [inner] join 表2 别名2 on 条件;
select * from product p join category c on p.cno=c.cid;

3.外连接

左外连接

查询的是左表所有数据以及其交集部分。

select 字段列表 from 表1 left [outer] join 表2 on 条件;

--左外连接，会将左表中的所有数据都查询出来，如果右表中没有对应的数据，用null代替
select * from product p left [outer] join category c on p.cno=c.cid;
--左连接做子查询
select p.* from product p left join category c on p.cno=c.cid where c.cname='手机数码';

右外连接

查询的是右表所有数据以及其交集部分。

select 字段列表 from 表1 right [outer] join 表2 on 条件;

--右外连接，会将右表中的所有数据都查询出来，如果左表中没有对应的数据，用null代替
select * from product p right [outer] join category c on p.cno=c.cid;

5.子查询

查询中嵌套查询，称嵌套查询为子查询。

语法：

select <字段> from <表名> where <表达式> <操作符> (子查询)

select (子查询) from 表名;

select * from (子查询) as 表的别名;

查询结果为单行单列

select * from 外键表 where 外键=(select 主键 from 主键表 where 条件);
select * from product where cno = (select cid from category where cname='手机数码');


--子查询可以作为条件，使用运算符去判断。 运算符： > >= < <= =
SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);

查询的结果是多行单列

--子查询可以作为条件，使用运算符in来判断

SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部';
SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;

SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');

查询的结果是多行多列

--子查询可以作为一张虚拟表参与查询
SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2
WHERE t1.id = t2.dept_id;

八、事务

事务的基本介绍

如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。

操作

  • 开启事务： start transaction 或者 begin;
  • 回滚：rollback;
  • 提交：commit;
-- 0. 开启事务
START TRANSACTION;
-- 1. 张三账户 -500
		
UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';
-- 2. 李四账户 +500
-- 出错了...
UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';
		
-- 发现执行没有问题，提交事务
COMMIT;
		
-- 发现出问题了，回滚事务
ROLLBACK;

MySQL数据库中事务默认自动提交

事务提交的两种方式

  • 自动提交： * mysql就是自动提交的
    • 一条DML(增删改)语句会自动提交一次事务。
  • 手动提交：
    • Oracle 数据库默认是手动提交事务
    • 需要先开启事务，再提交

修改事务的默认提交方式：

  • 查看事务的默认提交方式：SELECT @@autocommit; 1 代表自动提交(开启事务提交) 0 代表手动提交(关闭事务提交)
  • 修改默认提交方式： set @@autocommit = 0;

事务的四大特征(熟练)

  1. 原子性(Atomicity)：是不可分割的最小操作单位，要么同时成功，要么同时失败。
  2. 持久性(Durability)：当事务提交或回滚后，数据库会持久化的保存数据。
  3. 隔离性(Isolation)：多个事务之间。相互独立。
  4. 一致性(Consistency)：事务操作前后，数据总量不变

事务的隔离级别

多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。

存在问题

1. 脏读：一个事务，读取到另一个事务中没有提交的数据
2. 不可重复读(虚读)：在同一个事务中，两次读取到的数据不一样。
3. 幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。

隔离级别

  1. read uncommitted：读未提交(读到未提交的内容)
    • 产生的问题：脏读、不可重复读、幻读
  2. read committed：读已提交 （Oracle）(只能读到已经提交了的内容)
    • 产生的问题：不可重复读、幻读
  3. repeatable read：可重复读 （MySQL默认）(专门针对不可重复读这种情况而制定的隔离级别，可以有效的避免不可重复读)
    • 产生的问题：幻读
  4. serializable：串行化
    • 可以解决所有的问题

注意：隔离级别从小到大安全性越来越高，但是效率越来越低

数据库查询隔离级别

select @@tx_isolation;
或
show variables like '%tx_isolation%';

--全局事务隔离级别
SELECT @@global.tx_isolation;

--会话事务隔离级别
SELECT @@session.tx_isolation;

数据库设置隔离级别

set global transaction isolation level  级别字符串;

set [session | global] transaction isolation level {read uncommitted | read committed | repeatable read | serializable}

九、视图

1.创建视图

  • <视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。
  • <SELECT语句>：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。

创建单表视图

create view <视图名>(<字段名>) as select <字段名> from <表名>;

创建多表视图

create view <视图名>(<字段名>) as select <字段名> from <表1>,<表2> [where<条件>];

create view v_class as select student.s_id,student.name,stu_info.class  from student,stu_info where student.s_id=stu_info.s_id;

2.查看视图

select * from <视图名>; --查询视图数据

describe <视图名>; --查看视图字段信息
desc <视图名>;

show create view <视图名> --查看视图的详细信息

3.修改视图

alter view <视图名>(<字段名>) as select <字段名> from <表名>;

4.删除视图

drop view [if exists] <视图名...>

十、索引

索引是存放在模式(schema)中的一个数据库对象，创建索引的唯一作用就是加速对表的查询，索引通过使用快速路径访问方法来快速定位数据，从而减少了磁盘的I/O.

索引的优缺点

优点：

  • 通过创建唯一索引可以保证数据库表中每一行数据的唯一性。
  • 可以给所有的 MySQL 列类型设置索引。
  • 可以大大加快数据的查询速度，这是使用索引最主要的原因。
  • 在实现数据的参考完整性方面可以加速表与表之间的连接。
  • 在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间

缺点：

  • 创建和维护索引组要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
  • 索引需要占磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间。如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
  • 当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度。

创建索引的三种方式

1.create index

可以使用专门用于创建索引的 CREATE INDEX 语句在一个已有的表上创建索引，但该语句不能创建主键。

create <索引名> on <表名> (<列名> [<长度>] [ asc | desc])

2.create table

--创建新表的同时创建该表的主键
constraint primary key [索引类型] (<列名>,…)

--创建新表的同时创建该表的索引
key | index [<索引名>] [<索引类型>] (<列名>,…)
create table <表名>(<字段>,key|index(字段));

--创建新表的同时创建该表的唯一性索引
unique [ index | key] [<索引名>] [<索引类型>] (<列名>,…)
create table <表名>(<字段>,unique index(字段));

--创建新表的同时创建该表的外键
foreign key <索引名> <列名>

--创建普通索引
create table <表名>(<字段>,index(字段));

3.alter table

--修改表的同时为该表添加索引
ADD INDEX [<索引名>] [<索引类型>] (<列名>,…)

--修改表的同时为该表添加主键。
ADD PRIMARY KEY [<索引类型>] (<列名>,…)

--修改表的同时为该表添加唯一性索引
ADD UNIQUE [ INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,…)

--修改表的同时为该表添加外键
ADD FOREIGN KEY [<索引名>] (<列名>,…)

查看索引

show index from <表名> [from <数据库名>]

删除索引语法

drop index 索引名 on 表名

例：
drop index emp_last_name_idx2 on employees

十一、存储过程

创建存储过程

语法：

create procedure <过程名> ( [过程参数[,…] ] ) <过程体> [过程参数[,…] ] 格式 [ in | out | inout ] <参数名> <类型>

修改结束符

delimiter $$

查看存储过程的状态

show procedure status like <存储过程名称>;

查看存储过程的定义

show create procedure <存储过程名称>;

修改存储过程

特征指定了存储过程的特性，可能的取值有：

  • CONTAINS SQL 表示子程序包含 SQL 语句，但不包含读或写数据的语句。
  • NO SQL 表示子程序中不包含 SQL 语句。
  • READS SQL DATA 表示子程序中包含读数据的语句。
  • MODIFIES SQL DATA 表示子程序中包含写数据的语句。
  • SQL SECURITY { DEFINER |INVOKER } 指明谁有权限来执行。
  • DEFINER 表示只有定义者自己才能够执行。
  • INVOKER 表示调用者可以执行。
  • COMMENT 'string' 表示注释信息。
alter procedure <存储过程名称> [特征...]

删除存储过程

drop procedure [if exists] <过程名称>;

十二、触发器

创建触发器

create <触发器名> <before|after> <insert|update|delete> on <表名> for each row<触发器主体>;

查看触发器

show triggers;

修改触发器

删除触发器

drop trigger [if exists] [数据库名] <触发器名>;

十三、事件

也可称为事件调度器（Event Scheduler），是用来执行定时任务的一组 SQL 集合，可以通俗理解成 MySQL 中的定时器。一个事件可调用一次，也可周期性的启动。

查看事件是否开启

show variables like 'event_scheduler';

开启事件

1.通过设置全局参数修改

set global event_scheduler = ON;
--注意：此方式 MySQL 重启服务后事件又会回到原来的状态，如果想要始终开启或关闭事件修改配置文件。

2.更改配置文件

--在 MySQL 配置文件中找到 [mysqld] 选项，然后在下面添加以下代码开启事件。重启 MySQL 服务才能生效。
event_scheduler = ON

创建事件

create event [if not exists] event_name on schedule schedule [on completion [not] preserve] [enable | disable | disable on slave] [comment 'comment'] do event_body;

查看事件

1. 查看 mysql.event
select * from mysql.event

2. 查看 information_schema.events
select * from information_schema.events limit 1;

3. 切换到相应的数据库后执行 SHOW EVENTS;

修改事件

alter event [if not exists] event_name on schedule schedule [on completion [not] preserve] [enable | disable | disable on slave] [comment 'comment'] do event_body;

删除事件

drop event [if exists] event_name;

十四、字符集

查看当前使用的字符集

show variables like 'character%';

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
名称说明
character_set_clientMySQL 客户端使用的字符集
character_set_connection连接数据库时使用的字符集
character_set_database创建数据库使用的字符集
character_set_filesystemMySQL 服务器文件系统使用的字符集，默认值为 binary，不做任何转换
character_set_results数据库给客户端返回数据时使用的字符集
character_set_serverMySQL 服务器使用的字符集，建议由系统自己管理，不要人为定义
character_set_system数据库系统使用的字符集，默认值为 utf8，不需要设置
character_sets_dir字符集的安装目录

乱码时，不需要关心 character_set_filesystem、character_set_system 和 character_sets_dir 这 3 个系统变量，它们不会影响乱码 。

查看当前使用的校对规则

show variables like 'collation\_%';

+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | gbk_chinese_ci    |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+

对上述运行结果说明如下：

  • collation_connection：连接数据库时使用的校对规则
  • collation_database：创建数据库时使用的校对规则
  • collation_server：MySQL 服务器使用的校对规则

校对规则命令约定如下：

  • 以校对规则所对应的字符集名开头
  • 以国家名居中（或以 general 居中）
  • 以 ci、cs 或 bin 结尾，ci 表示大小写不敏感，cs 表示大小写敏感，bin 表示按二进制编码值比较。

服务器字符集和校对规则

1.可以在my.ini中配置

[mysqld]
character-set-server=字符集名称

2.连接 MySQL 服务器时指定字符集

mysql --default-character-set=字符集名称 -h 主机IP地址 -u 用户名 -p 密码

数据库字符集和校对规则

--查看数据库字符集
show variables like 'character_set_database';
或者
show variables like 'collation_database';

--数据库操作中设置或修改字符集

表字符集和校对规则

--查看表字符集
show create table <表名>;

--表操作中设置或修改字符集

列字符集和校对规则

--列字符集和校对规则的定义可以在创建表时指定，或者在修改表时调整
ALTER TABLE 表名 MODIFY 列名  数据类型 CHARACTER SET 字符集名;

连接字符集和校对规则

1.在 my.ini 配置

[mysql]
default-character-set=gbk

2.可以通过以下命令来设置连接的字符集和校对规则，这个命令可以同时修改以上 3 个参数（character_set_client、character_set_connection 和 character_set_results）的值。

set names <字符集>;

十五、数据库备份

备份类型

热备（Hot Backup）

热备份可以在数据库运行中直接备份，对正在运行的数据库操作没有任何的影响，数据库的读写操作可以正常执行。这种方式在 MySQL 官方手册中称为 Online Backup（在线备份）

  • 逻辑备份
  • 裸文件备份

冷备（Cold Backup）

冷备份必须在数据库停止的情况下进行备份，数据库的读写操作不能执行。这种备份最为简单，一般只需要复制相关的数据库物理文件即可。这种方式在 MySQL 官方手册中称为 Offline Backup（离线备份）。

温备（Warm Backup）

温备份同样是在数据库运行中进行的，但是会对当前数据库的操作有所影响，备份时仅支持读操作，不支持写操作。

备份的数据种类

  • 表数据
  • 二进制日志、InnoDB 事务日志
  • 代码（存储过程、存储函数、触发器、事件调度器）
  • 服务器配置文件

备份工具

  • mysqldump：逻辑备份工具，适用于所有的存储引擎，支持温备、完全备份、部分备份、对于 InnoDB 存储引擎支持热备。
  • cp、tar 等归档复制工具：物理备份工具，适用于所有的存储引擎、冷备、完全备份、部分备份。
  • lvm2 snapshot：借助文件系统管理工具进行备份。
  • mysqlhotcopy：名不副实的一个工具，仅支持 MyISAM 存储引擎。
  • xtrabackup：一款由 percona 提供的非常强大的 InnoDB/XtraDB 热备工具，支持完全备份、增量备份。

冷备份及恢复步骤

1.为了保证所备份数据的完整性，在停止 MySQL 数据库服务器之前，需要先执行 FLUSH TABLES 语句将所有数据写入到数据文件的文本文件里

flush tables;

2.停掉 MySQL 服务，命令（2种方式）

mysqladmin -uroot shutdown;
net start mysql;

3.备份过程就是复制整个数据目录到远程备份机或者本地磁盘上，Linux 和 Windows 命令如下

scp -r /data/mysql/ root@远程备份机ip:/新的目录

copy -r /data/mysql/ 本地新目录

4.恢复过程就更简单了，仅仅需要把已备份的数据目录替换原有的目录就可以了，最后重启 MySQL 服务。

逻辑备份

1.mysqldump

mysqldump 是 MySQL 自带的逻辑备份工具。它的备份原理是通过协议连接到 MySQL 数据库，将需要备份的数据查询出来，然后将查询出的数据转换成对应的 INSERT 语句。当我们需要还原恢复这些数据时，只要执行这些 INSERT 语句，就能将对应的数据还原。所以有的资料也将这种备份方式称为 INSERT 备份。

2.SELECT INTO…OUTFILE

SELECT INTO…OUTFILE 语句可以把表数据导出到一个文本文件中，且能将数据库中的表数据以特定分隔符进行分隔后记录在文本文件中，以达到逻辑备份的效果。

3.mydumper

mydumper 是针对 MySQL 数据库备份的一个轻量级第三方的开源工具，备份方式为逻辑备份。它支持多线程，备份速度远高于原生态的 mysqldump 以及其它众多优异特性。与其配套的相应恢复数据为 myloader 工具。DBA 称 mydumper 和 myloader 为备份界的“小钢炮”

裸文件备份

裸文件备份主要在底层复制数据文件，所以备份速度要比逻辑备份更快。

avatar
文章
阅读
粉丝