MySQL

笔痕墨影

已于 2023-01-29 10:46:52 修改

阅读量1.2k

点赞数

分类专栏： MySQL 文章标签： mysql 数据库

于 2019-07-16 15:58:21 首次发布

版权归原作者所有

本文链接：https://blog.csdn.net/qq_43919294/article/details/96139898

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

MySQL语句优化

	之所以将这个放在第一位，是因为我相信大多数来看MySQL相关知识的读者，
	肯定是有一定SQL基础的，对于基本SQL语句的编写是没有任何问题的。
	更多的难点则在于对SQL语句的优化，包括我自己在内，对我来说，也比较困难！！！
	废话不说了，直接来看……

对查询进行优化，避免全表查询
尽量避免在where字句中对字段进行null值的判断（备注：否则将导致引擎放弃使用索引而进行全表查询）
尽量避免在where字句中使用！= <>操作（等同于2的备注）
尽量避免在where字句中使用or连接条件，若一个有索引，另一个没有索引（等同于2的备注）
优化：select id from t where 字段=10 union all select id from t where name =“?”;
in 和 not in也要慎用，能用between就不用in；
select from a where exists(select i from between n=a.name and m=a.age)
模糊查询会导致全表扫描
可考虑全文检索：全文数据库是全文检索系统的主要构成部分。所有全文数据库不是海量信息数据库
避免在where子句中进行表达式操作（等同于2的备注）
select id from t where num=100*2；优化：num=200
使用索引字段作为条件时，若该索引是复合索引，必须使用到该索引中的第一个字段作为条件，才能保证系统使用该索引。并尽可能让字段顺序与索引顺序相一致。
update语句，需要修改什么字段就改什么字段，否则消耗性能，同时带来大量日志。
对于多张大数据量的表关联，先分页在join，否则性能相当差。
索引并非越多越好，一张表最多不要超过6个
尽可能使用varchar代替char
避免频繁创建和删除临时表。减少系统表资源的消耗
在新建临时表时，如果一次性插入数据量很大，可使用select into代替 create table，减少日志，提高效率
避免使用游标，因为效率太差。游标：用于PL/SQL和存储过程
尽量避免大事物操作，提高系统并发能力；避免向客户端返回大量数据，若过大，则重新设计。
拆分大的delete或者insert语句，批量提交sql语句要非常小心避免操作导致网站崩溃。所以，有大量的处理，一定要拆分，使用limit，rownum会更合适

数据备份

备份：

mysqldump -uroot -p database_name>backPath/backName
windows:mysqldump -uroot -p ttms>c:/ttms/sql
linux:mysqldump -uroot -p db2>/backPath/db_copy.sql

导入(还原)：
source backPath
1.创建一个数据库，create database 库名;
2.使用库，use 库名;
3.还原数据，source 备份的文件名

数据库操作

数据库相关查操作：

show variables like ‘%char%’;用来查看数据库所有的字符集

创建数据库：create database 数据库名；
显示（查询）所有数据库：show databases；
查看数据库详情：show create database 数据库名；
创建数据库指定字符集：creata database 数据库名 character set gbk or utf8;
删除数据库：drop database 数据库名；
创建数据之后，再去指定字符集命令：
1.先输入：use 数据库名；（use是使用数据库）
2.再输入：alter database db3 character set gbk;

表相关操作：

注意：对table进行操作之前，一定要先使用对应的数据库，如：use db1;
显示数据库里所有的表：show tables；
创建表：
create table 表名（字段1名字段1的类型，字段2名字段2的类型，…）；
create table person(name varchar(4),age int);
varchar:表示字符串，varchar(4)：表示限制长度最大为4

查看表详情：show create table 表名;

创建表时指定表的引擎和字符集：
create table 表名(name varchar(4),age int) engine=myisam charset=gbk;
注意：engine引擎有常用两种：innodb/myisam；charset的值按需求赋值。

查看表字段：desc 表名；

删除表：drop table 表名；

修改表相关：

1.修改表名称：rename table 旧表名 to 新表名;
如：rename table student to person;

2.修改表的引擎和字符集：
alter table 表名 engine=myisam/innodb charset=utf8/gbk;
如：alter table student engine=myisam charset=utf8;

3.添加字段：
alter table 表名 add 字段名字段类型；（最后面添加）
alter table 表名 add 字段名字段类型 first；（前面添加）
alter table 表名 add 字段名字段类型 after xxx；（在xxx的后面添加新字段）

4.删除表字段：
alter table 表名 drop 字段名；

5.修改表字段的名字和类型：
alter table 表名 change 旧字段名新字段名新字段类型；

6.修改表字段的类型和位置：
alter table 表名 modify 字段名类型位置；
如：alter table 表名 modify 字段名类型 first/(after xxx)；

数据相关
1.全表插入数据
insert into 表名 values（字段1的值，字段2的值，字段3的值，……）；
2.指定字段插入数据：
insert into 表名（字段1，字段2）values（字段1的值，字段2的值）；
insert into 表名（字段1，字段2）values（字段值为中文）；
中文字符问题 set names gbk;

备注：字符串需用但引号括起来

3.批量插入数据
insert into emp(表名) values(1002 ‘刘备’,200,8000),(1003,‘关羽’,300,6000),(1003,‘张飞’,300,9000);

insert into emp (name,age) values('悟空'，500),('八戒',400),('杀生',300);

4.查询全部数据的全部字段信息
select*from 表名;

5.查询所有员工的姓名和年龄
select name,age from 表名；

6.查询年龄小于25的员工信息
select*from emp where age<25;

7.查询工资3000块的员工信息，姓名，年龄，工资
select name,age,sal from emp where sal=3000;

修改数据
1.修改tom的工资为3333
update 表名 set sal(要修改的字段)=3333 where name(要修改的人)=‘tom’;

2.修改30岁以下的工资为666
update emp set sal=666 where age<30;

3.修改id等于1005的名字为吕布年龄为55 工资为20000
update emp set name=‘吕布’,age=55,sal=20000 where id=1005;

4.修改工资为null的工资为800；
update emp set sal=800 where sal is null;

删除数据
1.删除id=1的员工
delete from emp(表名) where id=1;alter table 表名 drop 字段名；

2.删除全部数据
delete from 表名；

主键约束

主键：用于表示数据唯一性的字段称为主键
约束：是给字段添加的限制条件
主键约束：限制主键字段不能重复且非空
	create table 表名(id int primary key,name varchar(10));
		create table t1(id int primary key,name varchar(10));
		insert into t1 values(1,'jack');

设置字段值自动增加
create table 表名(id int primary key auto_increment,name varchar(10));
	create table t2(id int primary key auto_increment,name varchar(10));

	insert into t2 values(null,'aaa')//1,aaa
	insert into t2 values(2,'aaa')//2,aaa
	insert into t2 values(10,'aaa')//10,aaa
	insert into t2 values(null,'aaa')//11,aaa
总结：1.当字段值为null的时候会自己增长
	 2.自增字段值也可以手动赋值
	 3.增长规则：从曾经出现的值大值基础上+1
	 4.自增数值只增不减（delete清空表，自增数值并不会清零）

注释 comment
comment：创建表声明字段的时候给字段添加的介绍
create table t3 (id int primary key auto_increment comment ‘这是一个主键’,name varchar(10) comment ‘这是员工的姓名’);
create table 表名(id int primary key auto_increment comment ‘这是一个主键’,name varchar(10) comment ‘这是员工的姓名’);

注意：
	赋号“ ` ”和“ ' ”,一个用于修饰表名和字段名可以省略，另一个用于修饰字符串

数据冗余

如果表设计不够合理，保存大量数据的同时，可能会出现大量重复的数据，
这些重复数据的现象就称为数据冗余，通过拆表可以在一定程度上解决冗余的问题

emp
create table marketB(id int primary key auto_increment,name varchar(10),salary int,sex varchar(4),age int,deptID int);

dept
create table dept(id int primary key auto_increment,name varchar(10),parentID int);

insert into dept values(null,‘A’,null);
insert into dept values(null,‘B’,1);
insert into dept values(null,‘B1’,2);
insert into emp values(null,‘liuBei’,8000,‘nan’,25,3(对应B1));

insert into dept values(null,‘C’,1);
insert into dept values(null,‘C1’,4);
insert into emp values(null,‘Hope’,8000,‘nan’,15,5);

1.保存男装分类（category）下西服分类下的商品皮尔卡丹西服，价格为9800，库存98件

2.保存家用电器分类下，电视机分类下的小米电视，价格2500，库存108件

create table shopInfo(id int primary key auto_increment,name varchar(10),money int,numbers int,categoryID int);	

create table categoryInfo(id int primary key auto_increment,name varchar(10),parentID int);

insert into categoryInfo values(null,‘’,null);
insert into categoryInfo values(null,‘西服’,null);
insert into categoryInfo values(null,‘电视机’,null);
insert into shopInfo values(null,‘皮尔卡丹’,9800,98,1);
insert into shopInfo values(null,‘小米电视’,2500,108,2);

查看自动提交状态：show variables like ‘%autocommit%’;
测试转账：
create table person(id int,name varchar(10),money int);
insert into person values(1,‘超人’,500),(2,‘钢铁侠’,10000);
关掉自动提交：
set autocommit=0;
1.先让超人+2000
update person set money=2500 where id=1;
2.开启另一个终端验证此时的数据库文件中的数据并没有该掉，数值还是保存在内存中

3.让钢铁侠-2000
update person set money=8000 where id=2;
4.执行提交
commit;

回滚：

将内存中的修改回滚到上次提交（commit）的点(比如上面我转了帐之后不commit，也就是取消转账，用rollback,当然确保自动提交是关闭状态)
update person set money=100 where id=1;
rollback;

保存回滚点
update person set money=100 where id=1;
savepoint s1(回滚点名);
update person set money=2500 where id=1;
savepoint s2(回滚点名);
rollback to s1(回滚点名)

SQL的分类

DDL：data definition language
	数据定义语言：create,alter,drop,truncate,不支持事物
DML：data manipulation language
	数据操作语言：insert,delete,update,select(DQL),支持事物
DQL：data query language
	数据查询语言：只包括select，和事物没有关系，因为并没有修改数据
TCL：transaction control language
	事物控制语言：commit,rollback,savepoint,rollback to
DCL:data control language
	数据控制语言：用于处理分配用户权限相关的操作。

truncate

truncate table 表名;

删除表并且创建一个新表
truncate、drop和delete的区别：
delete用于删除数据，使用delete清空表时自增数值不清零执行效率最低
drop 用于删除表执行效率最高
truncate 用于删除表并创建新的空表，执行效率比delete要高，而且自增数值会清零

数据库的数据类型

整数
常用整数有 int(m)和bigint(m)，m代表显示长度必须和zerofill结合使用

create table t_int(num int(10) zerofill);

insert into t_int values(123);

select * from t_int;

浮点数

常用浮点数double(m,d) m代表总长度 d代表小数长度 23.346 m=5 d=3
decimal超高精度浮点数，应用场景：涉及超高精度运算时使用
create table t_double(num double(5,3));
insert into t_double values(23.5678); 值为23.568
insert into t_double values(23.5); 值为23.500
字符串
char(m): 固定长度 m=10 abc 占10，效率高，最大255
varchar(m):可变长度 m=10 abc 占3，节省空间，最大65535，如果超过255建议使用text
text(m):可变长度最大65535

日期

date:只能保存年月日
time:只能保存时分秒
datetime:保存年月日时分秒，默认值为null，最大值9999-12-31
timestamp(时间戳距离19700101 08：00：00):保存年月日时分秒，默认值为当前系统时间,最大值2038-01-19 
create table t_time(t1 date,t2 time,t3 datetime,t4 timestamp);
insert into t_time values('2018-10-16',null,null,null);

insert into t_time values(‘2018-10-16’,null,now(),null);
insert into t_time values(null,‘20:06:32’,‘2008-11-22 18:22:11’,null);

配置URL：
jdbc:mysql://localhost:3306/db2?useUnicode=true&characterEncoding=UTF-8，这个是用来配置消灭了文件的，特别要注意如果是MySQL8.0以上版本的话，需要额外增加很多其它的参数