MySql常用知识归纳整理（收藏）

Orchestrator_me

已于 2023-12-06 14:23:29 修改

阅读量319

点赞数

文章标签： sql

于 2020-03-15 18:34:01 首次发布

原文链接：https://blog.csdn.net/weixin_39309402/article/details/98126899

版权

MySql常用知识归纳整理（收藏）

入门或者提升MySql，那推荐一书《Mysql必知必会》，提取链接：
链接: https://pan.baidu.com/s/1lemqy8kaFEa4ycpSFj8qGw 提取码: mgtt

一、连接与退出

1、登录连接数据库（生产或者测试，大多用客户端工具）
输入：mysql -u root -p 随后输入密码即可。PS:root为用户名，实际操作中请输入实际用户号。

2、连接数据库以后，一般操作命令：
show databases; — 显示所有数据库
use databaseName; —选择指定数据库
show tables; —显示该数据库下所有表
select database(); —查看当前自己所在数据库

3、查询当前安装的MySQL服务器的版本号
select version();

4、退出数据库
mysql> \q;
mysql> exit; — 两命令皆可

二、导入导出——推荐Navicat For Mysql

1）导出数据库 mysqldump命令(mysql的安装路径，即此命令的路径)
1、导出数据库(包含数据+表结构)
mysqldump -u root -p test > test_20160513.sql
mysqldump -t test -uroot -p > test222.sql
回车后提示输入密码

2、导出数据库(只导出表结构)— 多加了一个-d命令，注意区别
mysqldump -uroot -p -d test > test1.sql
mysqldump --opt -d test -u root -p > test_desc.sql

3、导出数据库中的某一张表
mysqldump -h localhost -u root -p test --opt T_CFN_LIMIT >T_CFN_LIMIT11.sql

4、导出数据库中某一张表的结构(不导出数据)
mysqldump -uroot -p -B test – table T_CFN_LIMIT > T_CFN_LIMIT_desc.sql

5、导出数据库中的数据和表结构
mysqldump test -uroot -p > test_20180711.sql

6、查询结果导出为Excle[连接数据库以后执行]
select * from T_ECFN_PAY WHERE PAY_FLAG NOT IN (‘A’,‘C’,‘6’) AND VOU_DATE=‘20180101’ INTO outfile ‘/home/tomcat/test2.xls’ CHARACTER SET GBK
ps: 查询语句 + INTO outfile + 路径 + 设置编码

2）导入数据库
mysql>create database abc
mysql>use abc
mysql>set names utf8
导入数据（注意sql文件的路径）
mysql>source /home/abc/abc.sql

方法2：
mysql>create database abc
mysql -u用户名 -p密码数据库名 < 数据库名.sql
mysql -uabc_f -p abc < abc.sql

PS:该部分命令比较难记，一般推荐使用Navicat For Mysql（可视化工具）

三、修改密码与增加用户权限

1、新建用户
CREATE USER name IDENTIFIED BY ‘aaa’;
如：CREATE USER user_name [ @hostname ] [ IDENTIFIED BY [ PASSWORD ] ‘pass_word’ ];
2、更改密码
mysql>SET PASSWORD FOR name=PASSWORD(‘bbb’);
3、权限管理
mysql>SHOW GRANTS FOR name; //查看name用户权限
mysql>GRANT SELECT ON db_name.* TO name;　//给name用户db_name数据库的所有权限
mysql>REVOKE SELECT ON db_name.* TO name;　　　　//GRANT的反操作，去除权限；
4、复制数据库
全部复制：>CREATE TABLE tb_name2 SELECT * FROM tb_name;
部分复制：>CREATE TABLE tb_name2 SELECT id,name FROM tb_name;

四、结构化查询语言

DDL：数据定义语言——用于建立、修改、删除数据库对象>表、视图、索引、序列
DML：数据操作语言——用于增、删、改表中的数据>DML是伴随事务(TCL)控制的
TCL：事务控制语言——用来维护数据一致性的语句[给予数据库一个后悔的机会]
DQL：数据查询语言——必要的字句有两部分组成>SELECT子句+FROM字句
DCL：用于执行权限的授予和收回操作

一、DDL：数据定义语言

作用：定义数据库或表结构
操作对象：数据库或表结构
关键字：CREATE、 ALTER、 DROP
1、创建数据库
mysql> create database 数据库名; //数据库已经存在或创建权限则执行失败。
mysql>CREATE TABLE table_name(
mysql>id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,　
mysql>name VARCHAR(60) NOT NULL
mysql>score TINYINT UNSIGNED NOT NULL DEFAULT 0,　　　　
mysql>PRIMARY KEY(id)
mysql>)ENGINE=InnoDB　　
mysql>DEFAULT charset=utf8;

第2行：id值，无符号、非空、递增——唯一性，可做主键。
第4行：设置默认列值
第6行：设置表存储引擎，常用InnoDB和MyISAM；InnoDB可靠，支持事务；MyISAM高效不支持全文检索
第7行：设置默认的编码，防止数据库中文乱码
如果有条件的创建数据表还可以使用
mysql>CREATE TABLE IF NOT EXISTS tb_name(…）

2、删除数据库
mysql> drop database 数据库名;

3、创建表结构
mysql>create table 表名(列名列数据类型,列名列数据类型…);
mysql>create table dept( //创建部门表
deptno int(11) primary key, //部门编号,int型，主键约束
deptname varchar(12) unique //部门名称，varchar类型，唯一性约束
);

附：常用约束
a)主键约束：primary key --非空且唯一，一张表就定义一个主键
b)主键自增长：auto_increment
c)非空：not null --不允许插入null值，可定义多个非空约束
d)唯一性：unique --不允许重复，但可以为空，可定义多个唯一约束
e)默认值：default 默认值 --指定缺省值，在没有添加值得情况下使用default后指定的默认值
f)外键：foreign key(列名) references 外键表名(外键列名); //注意：有外键时，创建表，先创建父表，再创建子表；插入数据，先插入父表，再插入子表；删除数据，先删除子表中的数据，再删除父表中的数据。

4、查看表结构
mysql> desc 表名; //常用
mysql> describe 表名;
mysql> show columns from 表名;

5、修改表名称
mysql> alter table 原表名 rename to 新表名;
mysql> rename table 原表名 to 新表名;
例：mysql> alter table dept rename to de; //将dept表名称改为de

6、修改表编码字符集
mysql>alter table 表名 character set 字符集;
例：mysql>alter table 表名 character set gbk; //修改表的编码字符集为gbk

7、向表中添加新的列
mysql> alter table 表名 add 列名列的数据类型 [<列的完整性约束>];
mysql>ALTER TABLE tb_name ADD COLUMN address varchar(80) NOT NULL;

8、修改表中的某一列
a)修改表中某一列的列名(同时可修改列的数据类型)
mysql>alter table 表名 change 原列名新列名列数据类型;
mysql>ALTER TABLE tb_name CHANGE name stu_name varchar(80) NOT NULL;
b)修改表中某一列的数据类型
mysql>alter table 表名 modify 列名列新的数据类型;

9、删除表中的某一列
mysql>alter table 表名 drop 列名; //column可省略

10、删除表
mysql>drop table 表名; //彻底删除表，数据库中不会保留表结构。注意与delete和truncate区别[truncate 和 delete 只删除数据不删除表的结构(定义)]。
mysql>truncate 表名; //截断表结构，删除数据

11、创建和删除索引
CREATE INDEX - 创建索引（搜索键）
DROP INDEX - 删除索引

12、全文检索——MATCH和AGAINST
1、SELECT MATCH(note_text)AGAINST(‘PICASO’) FROM tb_name;
2、InnoDB引擎不支持全文检索，MyISAM可以；

13、视图
1、创建视图
mysql>CREATE VIEW name AS SELECT * FROM tb_name WHERE ~~ ORDER BY ~~;
2、视图的特殊作用：
a、简化表之间的联结（把联结写在select中）；
b、重新格式化输出检索的数据（TRIM，CONCAT等函数）；
c、过滤不想要的数据（select部分）
d、使用视图计算字段值，如汇总这样的值。

14、触发器：触发器是指在进行某项指定操作时，触发触发器内指定的操作
1、支持触发器的语句有DELETE、INSERT、UPDATE,其他均不支持
2、创建触发器：
mysql>CREATE TRIGGER trig AFTER INSERT ON ORDERS FOR EACH ROW SELECT NEW.orser_name;
mysql>INSERT语句，触发语句，返回一个值
3、删除触发器
mysql>DROP TRIGGER trig;

二、DML: 数据操作语言

作用：操作表中的数据
关键字：INSERT、 UPDATE 、DELETE
1、insert语句需字段和值一一对应，但一般不建议直接insert

2、修改某一列的所有字段值为新的字段值
mysql>update 表名 set 列名=新字段值;
mysql>update 表名 set 列名=新字段值,列名=新字段值…; //批量修改多个列的所有字段值都新字段值。
如：update fei set name=‘秋冬’,age=27;

3、修改某一条记录的字段值
mysql>update 表名 set列名=新字段值 where 列名=字段值;
mysql>update 表名 set 列名=新字段值,列名=新字段值… where 列名=字段值; //批量修改多个字段的字段值都为该值。
如：>update fei set name=‘林拜’ where age=27;

4、删除表中的某一条记录
mysql>delete from 表名 where 列名=列值;
例：删除学生表(student)中名字(sname)为’张三’的学生的记录
mysql>delete from student where sname=‘张三’; //注意，由于学生名字有重复的可能，所以会把所有名字叫张三的学生信息都从student表中删除掉，所以删除单条记录的条件可以参照主键约束的列，或者唯一约束的列，或者在已确定没有重复的情况下自行确定where后面选的条件。

5、删除表
mysql>delete from 表名;
补充：truncate 、delete与drop
------ 相同点 ------
1.truncate和不带where子句的delete、以及drop都会删除表内的数据。
2.drop、truncate都是DDL语句(数据定义语言),执行后会自动提交。

------- 不同点 ------
1） truncate 和 delete 只删除数据不删除表的结构[删除表中的所有行，但表结构及其列、约束、索引等保持不变]
2）delete 语句会放到 rollback segement 中，事务提交之后才生效；truncate、drop是操作立即生效，原数据不放到 rollback segment 中，不能回滚。
3）执行速度: drop> truncate > delete
4）安全性：drop 和 truncate使用需注意，若无备份，一般不用。一般来说，删除部分数据，用delete带where子句.删除整张表用drop，如果
想保留表而将所有数据删除，且和事务无关，用truncate即可。
5）delete是DML语句,不会自动提交。drop/truncate都是DDL语句,执行后会自动提交。
6）对于由FOREIGN KEY约束引用的表，不能使用TRUNCATE TABLE，而应使用不带WHERE子句的DELETE语句。因为TRUNCATE TABLE不记录在日志中，所以它不能激活触发器。
7）TRUNCATE TABLE不能用于参与了索引视图的表。

三、DQL:数据查询语言[按照实际类别划分可以归为DML]

关键字：SELECT
1、查询表中所有记录
mysql> select * from 表名;

2、查询表中的某一条记录
mysql>select * from 表名 where 列名=字段值;
如：select age,grade from student where sname=‘张三’;

3、滤重[过滤表中重复数据(关键字distinct)]
mysql>select distinct 列名 from 表名; //返回该字段下的所有字段值,若有相同字段值,则只返回一个。

4、排序(关键字order by)
mysql>select * from 表名 order by 列名 asc; //默认是升序,asc一般可省略。
mysql>select * from 表名 order by 列名 desc; //desc是降序，不可省略
如：select sno,sname,grade from student order by grade desc; //依据成绩降序排列

5、分页查询(关键字limit)
mysql>select * from 表名 limit (pageNo-1)*pagesize,pagesize; // pageNo–>要查询的页数，pageSize–>每页显示的记录数
如：select * from 表名 limit 0,5; //查询第一页每一页显示5条

6、模糊查询(关键字like)
mysql>select * from 表名 where 列名 like ’ % '; //%指代任意字符
如：select * from student where sname like ‘陈%’; //查询姓陈的学生
select * from student where sname like ‘飞%’; //指代一个字符查询学生表中姓名第二个字为飞的学生信息

7、范围查询(关键字between … and … , in())
mysql>select * from 表名 where 列名 between 字段值(小) and 字段值(大); //包含两端的字段值
mysql>select * from 表名 where 列名 in (字段值1,字段值2,…);
如: mysql>select * from student where chinese between 80 and 90; //语文成绩在80~90分之间的所有学生信息(包含80和90)
mysql>select * from student where math in (60,70,80,90); //数学成绩为60分,70分,80分和90分的所有学生信息

8、别名
mysql>select 字段名别名,字段名别名,… from 表名;
如:mysql>select sname 姓名,chinese+math+english 总分 from sc; //查询学生成绩表(sc)中学生姓名(sname)及总成绩,sname字段用姓名表示,所有科目得分总和用总分表示。

9、分组查询(关键字group by)
mysql>select 列名,count(列名) from 表名 group by 列名;
如:mysql>select deptno,count(empno) from emp group by deptno; //empno–员工编号，emp–员工表查询每个部门的员工人数,根据部门编号(deptno)对员工进行分组,返回各部门员工人数。

having:对分组后的结果进行条件限制
如:mysql>select deptno,count(empno) from emp group by deptno having count(empno)>2; //查询每个部门员工人数大于2的所有记录,返回人数大于2人的部门编号及其部门人数。

10、表连接查询(同时涉及多个表的查询称为连接查询,用来连接两个表的条件称为连接条件)
内连接:表1 join 表2 on 表1.列名=表2.列名;
如：mysql> select empno,empname,emp.deptno,deptname from emp join dept on emp.deptno=dept.deptno; //查询员工表(emp)中的员工号(empno)、员工姓名(empname)、部门号(deptno)和部门名称(deptname),部门名称字段在部门表(dept)中.

左外连接:表1 left join 表2 on 表1.列名=表2.列名;
表1作为主表，主表中的所有记录都会输出，和从表匹配不上的字段用null进行补齐。
如：mysql> select empno,empname,emp.deptno,deptname from emp left join dept on emp.deptno=dept.deptno; //以员工表(emp)为主表实现上述查询

右外连接:表1 right join 表2 on 表1.列名=表2.列名;
表2为主表，主表中的所有记录都会输出，和从表匹配不上的字段用null进行补齐。
如：mysql> select empno,empname,emp.deptno,deptname from emp right join dept on emp.deptno=dept.deptno; //以部门表(dept)为主表实现上述查询

11、复制表
mysql>create table 新表表名 select * from 被复制表表名; //新表的表结构和数据与原表相同。
mysql>create table 新表表名 select 列名1,列名2,… from 被复制表表名; //可以通过在select查询中指定字段来限制出现在新表中的字段
mysql>create table 新表表名 select * from 被复制表表名 where 0=1; //通过使用select语句创建已存在表的空副本(即创建相同表结构,但不复制原表数据过来),并且返回一个空结果集。

12、聚合函数
a)返回指定列非空值的个数
mysql>select count(列名) from 表名;
如：mysql>select count(sno) from student; //查询学生表(student)中学生的总人数：根据学生学号计算学生个数,学号不为空且唯一

b)返回指定列的最值
mysql>select max(列名) from 表名; //返回指定列的最大值
如：mysql>select sname,max(sage) from student; //查询学生表(student)中年龄(sage)最大的学生姓名(sname)和年龄

mysql>select min(列名) from 表名; //返回指定列的最小值
如：mysql>select sno,min(chinese) from sc; //查询学生成绩表(sc)中语文成绩(chinese) 最低的学生的学号(sno)和语文成绩

c)返回指定列的平均值
mysql>select avg(列名) from 表名;

d)返回指定列的所有值之和
mysql>select sum(列名) from 表名;

13、UNION规则——可以执行两个语句（可以去除重复行）

[可以参考W3CSchool说明：http://www.w3school.com.cn/sql/sql_union.asp]

UNION 操作符用于合并两个或多个SELECT 语句的结果集。
PS：SELECT 语句必须拥有相同数量的列，且列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。
UNION是去除重复的列，而UNION ALL是列出所有的列

14、MySQL的正则表达式：
1、Mysql支持REGEXP的正则表达式：
mysql>SELECT * FROM tb_name WHERE name REGEXP ‘¹’ //找出以A-D 为开头的name
2、特殊字符需要转义

附：MySQL常见函数：

1、字符串链接——CONCAT()
mysql>SELECT CONCAT(name,‘=>’,score) FROM tb_name
2、数学函数：
AVG、SUM、MAX、MIN、COUNT；
3、文本处理函数：
TRIM、LOCATE、UPPER、LOWER、SUBSTRING
4、条件控制符：
=、>、<、<>、IN(1,2,3…)、BETWEEN a AND b、NOT AND 、OR Linke()用法中
% 为匹配任意、
_ 匹配一个字符（可以是汉字）
IS NULL 空值检测
5、时间函数：
DATE()、CURTIME()、DAY()、YEAR()、NOW()…

四、DCL:数据控制语言
GRANT - 授权
REVOKE - 撤销授权
DENY - 拒绝授权

五、TCL：事物控制语言
SAVEPOINT - 设置保存点
ROLLBACK - 回滚
COMMIT - 提交
SET TRANSACTION – 改变事务选项

五、数据库进程和表大小查询

查询正确执行的进程

select * from information_schema.`PROCESSLIST` where info is not NULL;

查询指定数据库操作

select * from information_schema.`PROCESSLIST` where DB LIKE '%*%';

删除日志文件

RESET MASTER;

查看所有数据库容量大小

select table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;

查看所有数据库各表容量大小

select table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc;

查看指定数据库容量大小

select table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql';

查看指定数据库各表容量大小

select table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql'
order by data_length desc, index_length desc;