MySQL内置功能
连接数据库
-u : 用户名
-p : 密码
-S : socket文件
-h : 主机host
-P : 端口号
-e : 免交互执行SQL语句
< : 恢复数据
# 示例
[root@localhost]# mysql -uroot -p -S /tmp/mysql.sock
[root@localhost]# mysql -uroot -p -h192.168.1.1 -P3306
[root@localhost]# mysql -uroot -p -e "show databasees"
[root@localhost]# mysql -uroot -p123 < /root/test.mysql
内置命令
- help ：打印MySQL帮助
- ^c : 取消本条命令的执行
- ^d : 退出mysql
- \G : 格式化输出
- source : 恢复备份文件
SQL基础应用
SQL种类
- DDL: 数据定义语言
- DCL：数据控制语言
- DML：数据操作语言
- DQL：数据查询语言
字符集（charset）
-
查看字符集
mysql> show charset； -
常用字符集
utf8：3个字节
utf8mb4（建议）：4个字节，支持emoji
校对规则（collation）
-
查看校对规则（对于英文字符串大小写敏感）
mysql> show collation； 其中：带ci说明对大小写敏感，不带ci明对大小写不敏感
数据类型
- 数字类型
- 整数
tinyint：极小数数据类型（0 ~ 255）
int：常规整数（-231 ~ 232-1） - 浮点数
- 整数
- 字符串类型
- char：定长字符串类型
不管字符串长度多长，都一次性分配指定存储空间，未使用空间使用空格填充 - varchar：变长字符串类型
每次存储前都要先判断以下长度，然后按需分配存储空间
会单独申请一个字符长度的空间存储字符长度（少于255，超出255，则需要申请两个字符长度的空间，以此类推）- 如何选择两个数据类型
少于255个字符串长度，定长的列值，选择chart
多余255个字符串长度，变长的字符串，选择varchar
- 如何选择两个数据类型
- enum：枚举数据类型（对常用字符类型数据进行预定义，不建议数字类型的数据使用该类型）
- char：定长字符串类型
- 时间类型
- datetime：
格式：YYYY-MM-DD hh:mm:ss[.uuuuuu]
范围：1000-01-01 00:00:00.000000 到 9999-12-31 23:59:59.999999
不会自动根据时区调整时间 - timestamp：
格式：YYYY-MM-DD hh:mm:ss[.uuuuuu]
范围：1970-01-01 00:00:00.000000 到 2038-01-19 03:14:07.999999
会自动根据时区调整时间
- datetime：
- 二进制类型
DDL数据定义语言应用
数据库操作
-
库定义的规范
- 库名使用小写字符
- 库名不能以数字开头
- 库名不能是数据库关键字
- 必须设置字符集
-
创建库
-
语法
CREATE DATABASE [数据库名称] CHARSET [字符集] COLLATE [校对规则] -
示例
CREATE DATABASE test_db CHARSET utf8mb4 COLLATE utf8mb4_bin;
-
-
删除库
-
语法
DROP DATABASE [数据库名称] -
示例
DROP DATABASE test_db;
-
-
修改库
说明：修改数据库一般修改数据库的字符集、校对规则等。在修改字符集时，目标字符集一定要是原字符集的超集，否则会出现乱码-
语法：
ALTER DATABASE [数据库名称] CHARSET [字符集] -
示例：
ALTER DATABASE test8 CHARSET utf8mb4;
-
表的操作
-
建表规范
- 表名小写
- 不能以数字开头，不能使用保留字符
- 表名要见其名知其义
- 选择合适的数据类型及长度
- 每个列设置NOT NULL、DEFAULT属性，对于数字类数据默认使用“0”填充，对于字符类数据使用有效字符串填充
- 每个列设置注释
- 表必须设置存储引擎和字符集
- 主键列尽量是无关数字列，最好是自增长
- enum类型不要保存数字，只能是字符串类型
-
建表
-
列属性
PRIMARY KEY ：主键约束，表中只能有一个，非空且唯一
NOT NULL ：非空约束，不允许空值
UNIQUE ：唯一键约束，不允许重复值
DEFAULT ：默认值，一般配合NOT NULL使用
UNSIGNED ：无符号，一般配合数字列，非负数
AUTO_INCREMEN ：自增
COMMENT ：注释 -
语法
CREATE TABLE [[数据库.]表名] (
[列名] [列属性1 列属性2 ...],
[列名] [列属性1 列属性2 ...],
...
[列名] [列属性1 列属性2 ...]
)ENGINE [存储引擎] CHARSET [字符集] -
示例
CREATE TABLE stu( id INT PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT "学号", s_name VARCHAR(255) NOT NULL COMMENT "姓名", age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT "年龄", sex ENUM('男','女','保密') NOT NULL DEFAULT '保密' COMMENT '性别', intime DATETIME NOT NULL DEFAULT NOW()) COMMENT '进入时间' )ENGINE INNODB CHARSET utf8mb4; -
创建一个结构与现有表相同的表
- 语法
CREATE TABLE [新表] LIKE [源表]
-
示例
CREATE TABLE stu_copy LIKE stu;
-
-
删表
-
语法
drop TABLE [表名] -
示例
drop TABLE stu_copy;
-
-
改表
注意：在线修改表列时，会造成锁表-
在所有列后面添加列
-
语法
ALTER TABLE [表名] ADD [列名] [列属性 列属性1 ...]
-
示例
ALTER TABLE stu ADD ph_num VARCHAR(15) NOT NULL COMMENT '手机号码';
-
-
在指定列后面添加列
-
语法
ALTER TABLE [表名] ADD [列名] [列属性 列属性1 ...] AFTER [列名]
-
示例
ALTER TABLE stu ADD address VARCHAR(255) NOT NULL COMMENT '住址' AFTER s_name;
-
-
在列最前面添加列
-
语法
ALTER TABLE [表名] ADD [列名] [列属性 列属性1 ...] FIRST
-
示例
ALTER TABLE stu ADD num VARCHAR(10) NOT NULL COMMENT '序号' FIRST;
-
-
删除指定列
注意：删除数据是直接物理删除整列数据，不可恢复-
语法
ALTER TABLE [表名] DROP [列名]
-
示例
ALTER TABLE stu DROP num;
-
-
修改指定列的数据属性（不修改列名）
注意：修改列属性时，修改后的属性为最终属性，因此在修改属性时需要输入所有列属性-
语法
ALTER TABLE [表名] MODIFY [列名] [列属性 列属性1 ...]
-
示例
ALTER TABLE stu MODIFY ph_num INT NOT NULL COMMENT '手机号码';
-
-
同时修改列名与列属性
-
语法
ALTER TABLE [表名] CHANGE [原列名] [新列名] [列属性 列属性1 ...]
-
示例
ALTER TABLE stu CHANGE sex gender CHAR(6) NOT NULL DEFAULT '保密' COMMENT '性别';
-
-
-
查看表结构
-
语法
DESC [表名]
-
示例
DESC student;
-
DML数据操作语言应用
insert（插入数据）
说明：有默认值的列可以不录入
-
作用：
插入数据 -
语法
INSERT INTO [表名] (列1,列2,...)
VALUES (值1，值2,...) -
示例
INSERT INTO stu(id,s_name,age,sex,intime) VALUES (1,'张三',20,'男',NOW());
update（修改数据）
-
作用
修改表中数据 -
语法
UPDATE [表名] SET 字段1=值 1 [,字段2=值2… ] [WHERE 子句 ] [ORDER BY 子句] [LIMIT 子句] -
示例
UPDATE stu SET s_name = 'zhang3' WHERE id = 1;
delete（删除数据）
-
作用
删除一行或多行数据 -
按条件删除
-
语法
DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句] -
示例
DELETE FROM stu WHERE id = 4;
-
-
删除全部数据
-
语法
DELETE FROM [表名]
truncate table [表名] -
示例
DELETE FROM stu truncate table stu; # 二者间的区别: delete: 属于DML操作, 是逻辑性质删除,逐行进行删除,速度慢. truncate: 属于DDL操作,对与表段中的数据页进行清空,速度快.
-
-
伪删除
说明：用update来替代delete，最终保证业务中查不到（select）即可-
操作流程
# 添加状态列 ALTER TABLE stu ADD state TINYINT NOT NULL DEFAULT 1 ; # UPDATE 替代 DELETE UPDATE stu SET state=0 WHERE id=6; # 业务语句查询 SELECT * FROM stu WHERE state=1;
-
DQL数据查询语言应用
select（查询语句）
-
select单独使用
# 查看当前数据库 SELECT database(); # 查看数据库端口 SELECT @@port; # 查看数据库软件安装目录 SELECT @@basedir; # 查看数据库目录 SELECT @@datadir; # 查看错误日志路径 SELECT @@log_error; # 查看数据库版本 SELECT @@version; # 查看server_id SELECT @@server_id; # 查看默认密码过期策略 SELECT @@default_password_lifetime; -
单表操作
-
总体语法
select [列] from [表] where [条件] group by [条件] having [条件] order by [条件] limit [条件] -
select配合
from子句使用-
语法
select [列1],[列1],... from [表] -
示例
# 查询表中所有信息 SELECT * FROM city;
-
-
select配合
where子句使用-
语法
select [列1],[列1],... from [表] where [过滤条件] -
示例
# 等值查询 SELECT Name,Population from city WHERE CountryCode='CHN'; # 不等值查询 SELECT Name,Population from city WHERE Population<10000; # 配合逻辑运算符（and、or） SELECT Name,Population from city WHERE CountryCode='CHN' AND Population>1000000; SELECT Name,Population from city WHERE Population>1000000 OR Population<900000; # 配合like子句实现模糊查询 SELECT * FROM city WHERE Name LIKE 'Sh%';
-
-
select配合
group by与统计函数应用-
常用聚合函数介绍
最大值：MAX()
最小值：MIN()
平均值：AVG()
计数 ：COUNT()
求和 ：SUM()
列转行：GROUP_CONCAT()
列数据拼接：CONCAT() -
group by
-
group by作用
将某列中有共同条件的数据行分成一组，然后再进行聚合函数操作，使用 DISTINCT 参数实现去重复 -
示例
# 统计每个国家的总人口数 SELECT countrycode,SUM(Population) FROM city GROUP BY CountryCode; # 统计每个国家的城市数量 SELECT countrycode,COUNT(id) FROM city GROUP BY CountryCode; # 统计每个国家省的数量 SELECT countrycode,COUNT(DISTINCT District) FROM city GROUP BY CountryCode; # 统计中国每个省的总人口数 SELECT District,SUM(Population) FROM city WHERE Countrycode='CHN' GROUP BY District; # 统计中国每个省的城市总数 SELECT District,COUNT(id) FROM city WHERE Countrycode='CHN' GROUP BY District; # 统计中国每个省的城市名称列表 SELECT District,GROUP_CONCAT(Name) FROM city WHERE Countrycode='CHN' GROUP BY District;
-
-
-
select配合
having子句应用-
作用
having主要用于二次过滤，功能与where相同 -
说明
having属于全表操作，不使用索引，因此在性能上有一定的影响 -
示例
# 统计所有国家人口总数并显示总人口大于1亿的国家 SELECT countrycode,SUM(Population) FROM city GROUP BY CountryCode HAVING SUM(Population) > 100000000;
-
-
select配合
order by子句-
作用
用于排序，倒序排列使用 DESC 参数 -
示例
# 统计所有国家人口总数并显示总人口大于5KW的国家 ，并按照从大到小排列 SELECT countrycode,SUM(Population) FROM city GROUP BY CountryCode HAVING SUM(Population)>50000000 ORDER BY SUM(Population) DESC;
-
-
select配合
limit子语句-
作用
主要用于分页显示 -
用法
LIMIT m,n说明：跳过 m 行显示 n 行
-
示例
# 统计所有国家人口总数并显示总人口大于5KW的国家 ，并按照从大到小排列，且只显示前三名 SELECT countrycode,SUM(Population) FROM city GROUP BY CountryCode HAVING SUM(Population)>50000000 ORDER BY SUM(Population) DESC LIMIT 3;
-
-
union和union all子句-
作用
多个结果集合并查询，性能优于逻辑操作 -
union和union all的区别
union会执行去重操作，union all不执行去重操作 -
示例
# 查询中国和美国的城市信息 SELECT * FROM city WHERE Countrycode='CHN' UNION ALL SELECT * FROM city WHERE Countrycode='USA';
-
-
-
多表链接查询（内连接）
-
作用
单表数据不能满足查询需求时，需使用多表连接查询 -
语法要求
- 多表操作的关键点是找到多张表之间的关联条件列
- 列书写时，必须是：表名.列
- 所有涉及到的查询列都放到SELECT后
- 表间的关联条件写在ON后面
- 将所有的过滤、分组、排序的条件按顺序写在ON后面
-
单表连接语法
SELECT [表1.列],[表2.列],... FROM [表1] JOIN [表2] ON [表1.列] = [表2.列] WHERE [过滤条件] -
多表连接语法
SELECT [表1.列],[表2.列],... FROM [表1] JOIN [表2] ON [表1.列] = [表2.列] JOIN [表3] ON [表2.列] = [表3.列] WHERE [过滤条件] -
示例
# 查询世界上人口小于1000人的城市，并列出其所在的国家、国土面积、城市名、人口数量 SELECT city.CountryCode,city.Name,city.Population,country.Name,country.SurfaceArea FROM city JOIN country ON city.countryCode = country.Code WHERE city.population < 1000; -
别名应用
- 表别名
在表名后面添加as [别名]，作用范围是全局- 语法
[表名] as [别名]
- 语法
- 列别名
在列名后面添加as [别名]，列别名可以在having和order by中调用- 语法
[列名] as [别名]
- 语法
- 表别名
-
information_schema基础应用
information_schema数据库是 MySQL自带的信息数据库，information_schema用于存储数据库元数据，例如：数据库名、表名、存储引擎等信息
-
information_schema.tables表的常用字段介绍
- TABLE_SCHEMA：表所在的库名
- TABLE_NAME：表名
- ENGINE：存储引擎
- TABLE_ROWS：数据行
- AVG_ROW_LENGTH：平均行长度
- INDEX_LENGTH：索引长度
-
基础使用示例
# 显示所有的库和表的信息 SELECT table_schema,table_name FROM information_schema.tables; # 以下列方式显示所有的库和表的信息 ## world city,country,countrylanguage SELECT table_schema,GROUP_CONCAT(table_name) FROM information_schema.tables GROUP BY table_schema; # 查询所有innodb引擎的表 SELECT table_schema,table_name ,ENGINE FROM information_schema.tables WHERE ENGINE='innodb'; # 统计world下的city表占用空间大小 ## 表的数据量=平均行长度*行数+索引长度 ## AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH SELECT table_name,(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024 FROM information_schema.TABLES WHERE table_schema='world' AND table_name='city'; # 统计world库数据量总大小 SELECT table_schema,SUM((AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH))/1024 FROM information_schema.TABLES WHERE table_schema='world'; # 统计每个库的数据量大小，并按数据量从大到小排序 SELECT table_schema,SUM((AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH))/1024 AS total_KB FROM information_schema.TABLES GROUP BY table_schema ORDER BY total_KB DESC ; -
配合concat()函数拼接语句或命令示例
# 模仿以下语句,进行数据库的分库分表备份。 mysqldump -uroot -p123 world city >/bak/world_city.sql SELECT CONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name ," >/bak/",table_schema,"_",table_name,".sql") FROM information_schema.tables; # 模仿以下语句,进行批量生成对world库下所有表进行操作 ALTER TABLE world.city DISCARD TABLESPACE; SELECT CONCAT("ALTER TABLE ",table_schema,".",table_name," DISCARD TABLESPACE;") FROM information_schema.tables WHERE table_schema='world';
show命令介绍
- 查看数据库列表
show databasees - 查看建库使用的命令
show create database [数据库名称] - 查看表信息
show tables - 查看建表信息
show create table [表名称] - 查看所有用户连接情况
show processlist - 查看支持的字符集
show charset - 查看所有支持的校对规则
show collation - 查看用户的权限信息
show grants for [用户名] - 查看参数信息
show variables
show variables like [变量名] - 查看所有支持的存储引擎类型
show engines; - 查看表的索引信息
show index from [表名] - 查看innoDB引擎详细状态信息
show engine innodb status\G - 查看二进制日志的列表信息
show binary logs - 查看二进制日志的事件信息
show binlog events in [日志文件] - 查看mysql当前使用二进制日志信息
show master status - 查看从库状态信息
show slave status\G - 查看中继日志的事件信息
show relaylog events in [日志文件] - 查看数据库整体状态信息
show status like [数据库名]