Mysql 知识点

最新推荐文章于 2023-06-02 12:04:40 发布
最新推荐文章于 2023-06-02 12:04:40 发布
阅读量248 收藏
点赞数
分类专栏： mysql 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/l547319765/article/details/100553270
版权
这篇博客详细介绍了MySQL的基础知识，包括数据类型、基本SQL语句、用户权限和密码管理、事务处理、索引原理及优化。还讨论了中文乱码问题和数据备份策略。重点讲解了不同类型的索引及其优缺点，并提供了SQL优化的建议。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

数据类型
<1>整数型
     类型      大小      范围（有符号）               范围（无符号unsigned）    用途
     TINYINT   1 字节    (-128，127)                (0，255)                 小整数值
     SMALLINT  2 字节    (-32768，32767)            (0，65535)               大整数值
     MEDIUMINT 3 字节    (-8388608，8388607)        (0，16777215)            大整数值
     INT       4 字节    (-2147483648，2147483647)  (0，4294967295)          大整数值
     BIGINT    8 字节     （）                       (0，2的64次方减1)        极大整数值
​
<2>浮点型
 FLOAT(m,d）  4 字节    单精度浮点型  备注：m代表总个数，d代表小数位个数
 DOUBLE(m,d） 8 字节    双精度浮点型  备注：m代表总个数，d代表小数位个数
 
 <3>定点型
 DECIMAL(m,d）    依赖于M和D的值    备注：m代表总个数，d代表小数位个数
 
 <4>字符串类型 
 类型          大小              用途
 CHAR          0-255字节         定长字符串
 VARCHAR       0-65535字节       变长字符串
 TINYTEXT      0-255字节         短文本字符串
 TEXT          0-65535字节       长文本数据
 MEDIUMTEXT    0-16777215字节    中等长度文本数据
 LONGTEXT      0-4294967295字节  极大文本数据
 
 char的优缺点：存取速度比varchar更快，但是比varchar更占用空间
 varchar的优缺点：比char省空间。但是存取速度没有char快
 
 <5>时间型
 数据类型    字节数            格式                    备注
 date        3                yyyy-MM-dd              存储日期值
 time        3                HH:mm:ss                存储时分秒
 year        1                yyyy                    存储年
 datetime    8                yyyy-MM-dd HH:mm:ss     存储日期+时间
 timestamp   4                yyyy-MM-dd HH:mm:ss     存储日期+时间，可作时间戳
基础mysql语句
  1. DML语句
    建库
    	查看当前mysql使用的字符集
	show variables like 'character%';
	直接创建数据库 db1
	create database db1;  
	查看当前在哪个库里边
	select database();
	进入库的操作
	use 库名; 
	判断是否存在，如果不存在则创建数据库 db2
	create database if not exists db2;    
	创建数据库并指定字符集为 gbk
	create database db3 default character set gbk;    
	查看某个库是什么字符集；
	show create database XD;
    建表
    CREATE TABLE 表名 (
              字段名1 字段类型1 约束条件1 说明1,
              字段名2 字段类型2 约束条件2 说明2,
              字段名3 字段类型3 约束条件3 说明3
              );
create table 新表名 as select * from 旧表名 where 1=2;(注意：建议这种创建表的方式用于日常测试，因  为可能索引什么的会复制不过来)
create table 新表名 like 旧表名;

约束条件：
comment         ----说明解释
not null        ----不为空
default         ----默认值
unsigned        ----无符号（即正数）
auto_increment  ----自增
zerofill        ----自动填充
unique key      ----唯一值

查看数据库中的所有表：show tables；
查看表结构：desc 表名;
查看创建表的sql语句：show create table 表名;
\G :有结束sql语句的作用，还有把显示的数据纵向旋转90度
\g :有结束sql语句的作用
    修改表
    修改表名
	rename table 旧表名 to 新表名;
	rename table student to user;

添加列
	给表添加一列：alter table 表名 add 列名 类型;
	alter table user add addr varchar(50);
	alter table add 列名 类型 comment '说明';
	alter table user add famliy varchar(50) comment '学生父母';
​
	给表最前面添加一列：alter table 表名 add 列名 类型 first;
	alter table user add job varchar(10) first;
​
	给表某个字段后添加一列：alter table 表名 add 列名 类型 after 字段名;
	alter table user add servnumber int(11)  after id;
​
	注意：没有给表某个字段前添加一列的说法。
修改列类型
	alter table 表名 modify 列名 新类型;
	alter table user modify servnumber varchar(20);
修改列名
	alter table 表名 change 旧列名 新列名 类型;
	alter table user change servnumber telephone varchar(20);
删除列
	alter table 表名 drop 列名;
	alter table user drop famliy;
修改字符集
	alter table 表名 character set 字符集;
	alter table user character  set GBK;
mysql表的删除
	drop table 表名；
	drop table user;
	看表是否存在，若存在则删除表：drop table if exists 表名;
	drop table  if exists teacher;
  2. DDL各种语法
    DDL插入
    普通的插入表数据
	insert into 表名（字段名） values（字段对应值）;
	insert into employee (empno,ename,job,mgr,hiredate,sal,deptnu) values ('1000','小明','经理','10001','2019-03-03','12345.23','10');
	insert into 表名 values（所有字段对应值）;
	insert into employee  values ('1001','小明','经理','10001','2019-03-03','12345.23','10');    
蠕虫复制（将一张表的数据复制到另一张表中）
	insert into 表名1 select * from 表名2;
	insert into 表名1（字段名1，字段名2） select 字段名1，字段名2 from 表名2;
	insert into emp (empno,ename) select empno,ename from employee;
建表复制
	create table 表名1 as select 字段名1，字段名2 from 表名2;
	create table emp as select empno ,ename from employee;
一次性插入多个数据
 	insert into 表名  (字段名) values (对应值1),(对应值2),(对应值3);
    DDL修改删除
    修改（更新）：
update 表名 set 字段名1=值1 where 字段名=值;
update 表名 set 字段名1=值1,字段名2=值2 where 字段名=值;

删除：
delete from 表名 where 字段名=值;

delele 会把删除的操作记录给记录起来，以便数据回退，不会释放空间，而且不会删除定义。
truncate不会记录删除操作，会把表占用的空间恢复到最初，不会删除定义
drop会删除整张表，释放表占用的空间。
    where条件查询
    简单查询
select * from employee;
select empno,ename,job as ename_job from employee;

精确条件查询
select * from employee where ename='后裔';
select * from employee where sal != 50000;
select * from employee where sal <> 50000;
select * from employee where sal > 10000;

模糊条件查询
show variables like '%aracter%'; 
select * from employee  where ename like '林%';

范围查询
select * from employee where sal between 10000 and 30000;
select * from employee where hiredate between '2011-01-01' and '2017-12-1';

离散查询
select * from employee where ename in ('猴子','林俊杰','小红','小胡'); 
 
清除重复值
select distinct(job) from employee;

统计查询（聚合函数）:
   count(code)或者count(*)
    select count(*) from employee;
    select count(ename) from employee;
    
   sum()  计算总和 
    select sum(sal) from employee;
    
   max()  计算最大值
    select * from employee where sal= (select  max(sal) from employee);
    
   avg()  计算平均值
    select avg(sal) from employee;
    
   min()  计算最低值
    select * from employee where sal= (select  min(sal) from employee);
    
   concat函数： 起到连接作用
    select concat(ename,' 是 ',job) as aaaa from employee;
    group by分组查询
    select deptnu,count(*) from employee group by deptnu;
select deptnu,job,count(*) from employee group by deptnu,job;
select job,count(*) from employee group by job;
    having条件查询
    select job,count(*) from employee group by job having job ='文员';
select  deptnu,job,count(*) from employee group by deptnu,job having count(*)>=2;
select  deptnu,job,count(*) as 总数 from employee group by deptnu,job having 总数>=2;
    order by排序查询（排序）
    select * from employee order by sal;
select * from employee order by hiredate;
select  deptnu,job,count(*) as 总数 from employee group by deptnu,job having 总数>=2 order by deptnu desc;
select  deptnu,job,count(*) as 总数 from employee group by deptnu,job having 总数>=2 order by deptnu asc;
select  deptnu,job,count(*) as 总数 from employee group by deptnu,job having 总数>=2 order by deptnu;
​
顺序：where ---- group by ----- having ------ order by
    limit限制查询（限制）
    select * from XD.employee limit n，m n:代表起始条数值，不写默认为0；m代表：取出的条数
    exists型子查询(返回一个boolean值)
    分为俩种：exists跟 not exists

select 1 from employee where 1=1;
select * from 表名 a where exists (select 1 from 表名2 where 条件);
​
eg:查询出公司有员工的部门的详细信息
select * from dept a where exists (select 1 from employee b where a.deptnu=b.deptnu);
select * from dept a where not exists (select 1 from employee b where a.deptnu=b.deptnu);
    左连接查询与右连接查询
    select a.dname,b.* from dept a  left join employee b on a.deptnu=b.deptnu;
select b.dname,a.* from employee a  right join  dept b on b.deptnu=a.deptnu;
    内连接查询与联合查询
    eg:想查出员工张飞的所在部门的地址
select a.addr  from dept a inner join employee b on a.deptnu=b.deptnu and b.ename='张飞';
select a.addr from dept a,employee b where a.deptnu=b.deptnu and b.ename='张飞';
    union查询的注意事项：
    (1)两个select语句的查询结果的“字段数”必须一致；
​
(2)通常，也应该让两个查询语句的字段类型具有一致性；
​
(3)也可以联合更多的查询结果；
​
(4)用到order by排序时，需要加上limit（加上最大条数就行），需要对子句用括号括起来
​
eg:对销售员的工资从低到高排序，而文员的工资从高到低排序
    (select * from employee a where a.job = '销售员'  order by a.sal limit 999999 ) union  (select * from employee b where b.job = '文员' order by b.sal desc limit 999999);
mysql用户权限和密码
  1. 查看root权限
    查看root用户可以在哪台机器登录
select user,host from mysql.user where user='root';
修改mysql库里边的user表
update mysql.user set host='localhost' where user='root';
刷新权限
flush privileges;
  2. 修改用户密码分三种方法：
    第一种：set password for 用户@ip = password('密码');
	set password for root@localhost = password('root');
	
第二种：mysqladmin -u用户 -p旧密码 password 新密码;
	mysqladmin -urootmysqladmin -uroot -proot password;
	
第三种：update mysql.user set authentication_string=password('密码') where user='用户' and host='ip';
	update mysql.user set authentication_string=password('root') where user='root' and host='localhost';
  3. 忘记密码 ：在mysql问题中有讲解
用户权限控制
  1. 创建用户
    创建用户的语法：create user 'username'@'host' identified by 'password';

	username：你将创建的用户名
	​
	host：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机    登陆，可以使用通配符%
	​
	password：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器
	
创建用户语法：

创建一个pig用户，并指定登录密码：123456，可以在任何一台远程主机都可以登录
create user 'pig'@'%' identified by '123456';

创建一个pig用户，并指定登录密码：为空，指定在120网段的机器登录
create user 'pig'@'120.%.%.%' identified by '';
  2. 查看权限：
       	select * from mysql.user where user='pig'\G
   	
    mysql> show grants for 'pig'@'%';
    +---------------------------------+
    | Grants for pig@%                |
    +---------------------------------+
    | GRANT USAGE ON *.* TO 'pig'@'%' |
    +---------------------------------+
    USAGE：无权限的意思
    mysql> show grants for 'root'@'localhost';
    +---------------------------------------------------------------------+
    | Grants for root@localhost                                           |
    +---------------------------------------------------------------------+
    | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
    +---------------------------------------------------------------------+
    WITH GRANT OPTION:表示这个用户拥有grant权限，即可以对其他用户授权
  3. 删除用户语法：drop user ‘username’@‘host’;
    drop user 'pig'@'%';
delete from mysql.user where user='pig';
  4. 授权语法：
    grant 权限1,权限2..... on 数据库对象 to '用户'
grant 权限1,权限2..... on 数据库对象 to '用户'@'host' identified by 'password';

all privileges:代表所有权限
. :代表所有库所有表

	对现有用户进行授权：对现有用户pig授予所有库所有表所有权限。
	grant all privileges on *.*  to 'pig';
	
	对没有的用户进行授权：创建一个新用户dog授予XD库的所有权限，登录密码123456，任何一台主机登录
	grant all privileges on XD.* to 'dog'@'%' identified by '123456';
	
	对没有的用户进行授权：创建一个新用户cat授予XD库的employee表 查与修改权限，登录密码123456，任何一台主机登录
	grant select,update on XD.employee to 'cat'@'%' identified by '123456'
	
	对没有的用户进行授权：对用户cat授予XD库的employee表insert 权限，登录密码123456，任何一台主机登录
	grant insert on XD.employee to 'cat'@'%' identified by '123456';
	
回收语法：revoke 权限1,权限2..... on 数据库对象 from '用户'@'host';
	回收pig用户的所有权限（注意：并没有回收它的登录权限）
	revoke all privileges on *.*  from 'pig' @ '%';
	flush privileges;
回收pig用户的所有权限（并回收它的登录权限）
	delete from mysql.user where user='pig';
	flush privileges;
回收cat用户对XD库的employee的查与修改权限
	revoke select,update on XD.employee from 'cat'@'%';
	flush privileges;
    视图
    创建的基本语法是：
create view <视图名称> as select 语句;
create view <视图名称> (字段) as select 语句;
create or replace view <视图名称>;
修改的语法是：
alter view <视图名称> as select 语句;
视图删除语法：
drop view <视图名称> ;
    触发器
    create trigger 触发器名称  after/before   insert/update/delete on 表名  
    for each row
    begin
    sql语句;
    end
        
after/before:可以设置为事件发生前或后
insert/update/delete:它们可以在执行insert、update或delete的过程中触发
for each row:每隔一行执行一次动作

删除触发器的语法：
drop trigger 触发器名称;
    创建存储过程语法：
    create procedure 名称 (参数....)
        begin
         	过程体;
        	过程体;
         end
参数：in|out|inout 参数名称 类型（长度）
        in：表示调用者向过程传入值（传入值可以是字面量或变量）
        out：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
        inout：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）
声明变量：declare 变量名 类型(长度) default 默认值;
给变量赋值：set @变量名=值;
调用存储命令：call 名称(@变量名);
删除存储过程命令：drop procedure 名称;
查看创建的存储过程命令：
show create procedure 名称\G;
事务实战
  1. 事务特性
    • 原子性(Atomicity)：事务必须是原子工作单元，一个事务中的所有语句，应该做到：要么全做，要么一个都不做；
    • 一致性(Consistency):让数据保持逻辑上的“合理性”，比如：小明给小红打10000块钱，既要让小明的账户减少10000，又要让小红的账户上增加10000块钱；
    • 隔离性(Isolation)：如果多个事务同时并发执行，但每个事务就像各自独立执行一样。
    • 持久性(Durability)：一个事务执行成功，则对数据来说应该是一个明确的硬盘数据更改（而不仅仅是内存中的变化）。
      你要使用事务的话，表的引擎要为innodb引擎
  2. 事务操作
    事务的开启：begin; start transaction;

事务的提交：commit;

事务的回滚：rollback;

    开启autocommit（临时生效）：set autocommit=1;
开启autocommit（永久生效）：
	修改配置文件：vi /etc/my.cnf 在[mysqld]下面加上：autocommit=1 记得重启服务才会生效
索引
  1. mysql存储介绍
    MyISAM与InnoDB的区别:
    MyISAM：支持全文索引（full text）;不支持事务;表级锁;保存表的具体行数;奔溃恢复不好

Innodb：支持事务;以前的版本是不支持全文索引，但在5.6之后的版本就开始支持这个功能了;行级锁（并非绝对，当执行sql语句时不能确定范围时，也会进行锁全表例如： update table set id=3 where name like 'a%';）;不保存表的具体行数;奔溃恢复好

MyISAM：
•    一般来说MyISAM不需要用到事务的时候
•    做很多count计算
​
InnoDB(推荐)：
•    可靠性要求高的，或者要求支持事务
•    想要用到外键约束的时候（讲外键的时候会讲）
  2. 索引：索引是一个单独的，存储在磁盘中上的数据库结构，它们包含着对数据表里的所有记录的引用指针。使用索引可以快速的找出在某列或多列中有特定值的行。
  3. 索引的优缺点
    索引的优点：
    通过创建唯一索引，来保证数据库表中的每一行数据的唯一性。
    • 可以加快数据的检索速度。
    • 可以保证表数据的完整性与准确性
    索引的缺点：
    索引需要占用物理空间。
    • 对表中的数据进行改动时，索引也需要跟着动态维护，降低了数据的维护速度。
  4. 常见索引：
    • index：普通索引：普通索引（index）顾名思义就是各类索引中最为普通的索引，主要任务就是提高查询速度。其特点是允许出现相同的索引内容，允许空（null）值
    • unique：唯一索引：（unique）顾名思义就是不可以出现相同的索引内容，但是可以为空（null）值
    • primary key：主键索引：把主键添加索引就是主键索引，它是一种特殊的唯一索引，不允许有空值，而唯一索引（unique是允许为空值的）。指定为“PRIMARY KEY”
    • foreign key：外键索引
    • fulltext: 全文索引是将存储在数据库中的文章或者句子等任意内容信息查找出来的索引，单位是词。全文索引也是目前搜索引擎使用的一种关键技术。指定为 fulltex
    • 组合索引
    主键索引
	删除主键：
	语法：
	alter table 表名 drop primary key;
	eg： 
	alter table test drop primary key;
	注意：在有自增的情况下，必须先删除自增，才可以删除主键
	删除自增：alter table test change id id int(7) unsigned zerofill not null;
全文索引
	select * from 表名 where match  (字段名) against ('检索内容');
	查看匹配度：
	select * from command where match(instruction) against ('directory');
	停止词：出现频率很高的词，将会使全文索引失效
	in boolean mode 模式：
		in boolean mode：意思是指定全文检索模式为布尔全文检索（简单可以理解为是检索方式）
		select * from 表名 where match (字段名) against ('检索内容' in boolean mode);
	注意点：使用通配符*时，只能放在词的后边，不能放前边。
	删除全文索引：
	alter table command drop index instruction;
	注意点总结：
	    1、一般情况下创建全文索引的字段数据类型为 char、varchar、text 。其它字段类型不可以
		2、全文索引不针对非常频繁的词做索引。比如is，no，not，you，me，yes这些，我们称之为停止词
	    3、对英文检索时忽略大小写
什么是外键？
	外键就是作用于两个表数据之间的链接的一列或多列，用来保证表与表之间的数据的完整性和准确性。
添加外键约束：
	语法：foreign key (字段名) references 关联的表名(关联表的字段名)
注意：主键跟外键的字段类型一定要相
create table的方法：
	CREATE TABLE `employee` (
	  `empno` int(11) NOT NULL COMMENT '雇员编号',
	  `ename` varchar(50) DEFAULT NULL COMMENT '雇员姓名',
	  `job` varchar(30) DEFAULT NULL,
	  `mgr` int(11) DEFAULT NULL COMMENT '雇员上级编号',
	  `hiredate` date DEFAULT NULL COMMENT '雇佣日期',
	  `sal` decimal(7,2) DEFAULT NULL COMMENT '薪资',
	  `deptnu` int(11) DEFAULT NULL COMMENT '部门编号',
	  PRIMARY KEY (`empno`),
	 foreign key (deptnu) references dept(deptnu)
	) ENGINE=InnoDB DEFAULT CHARSET=utf8;
alter table的方法：
	alter table employee add foreign key (deptnu) references dept(deptnu);
删除外键约束：
	注意：在干掉外键索引之前必须先把外键约束删除，才能删除索引
	mysql> alter table employee drop index deptnu;
	ERROR 1553 (HY000): Cannot drop index 'deptnu': needed in a foreign key constraint
	mysql> 
	mysql> alter table employee drop foreign key employee_ibfk_1;
	Query OK, 0 rows affected (0.01 sec)
	Records: 0  Duplicates: 0  Warnings: 0
	mysql> 
	mysql> alter table employee drop index deptnu;
	Query OK, 0 rows affected (0.01 sec)
	Records: 0  Duplicates: 0  Warnings: 0
注意点总结：
	（1）俩个表，主键跟外键的字段类型一定要相同
	（2）要使用外键约束表的引擎一定得是InnoDB引擎，MyISAM是不起作用的
	（3）在干掉外键索引之前必须先把外键约束删除，才能删除索引
详细介绍联合索引
	什么是联合索引？
		联合索引又称组合索引或者复合索引，是建立在俩列或者多列以上的索引。
	怎么来创建联合索引？
		alter table 表名 add index(字段1,字段2,字段3);
		alter table test add index(username,servnumber,password);
	怎么删除联合索引？
		alter table test drop index username;
	为什么要使用联合索引，而不使用多个单列索引？
		联合索引的效率远远高于单列索引
	联合索引的最左原则
注意点总结：
	 索引并非越多越好，过多的索引会增加数据的维护速度还有磁盘空间的浪费。
		• 当表的数据量很大的时候，可以考虑建立索引。
		• 表中经常查数据的字段，可以考虑建立索引			​
		• 想要保证表中数据的唯一性，可以考虑建立唯一索引。
		• 想要保证俩张表中的数据的完整性跟准确性，可以考虑建立外键约束			​
		• 经常对多列数据进行查询时，可以考虑建立联合索引。
  5. 慢查询
    第一步：查看是否已经开启了慢查询日志
mysql> show variables like 'slow%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_launch_time    | 2                                    |
| slow_query_log      | OFF                                  |
| slow_query_log_file | /data/mydata/xdclass-public-slow.log |
+---------------------+--------------------------------------+
第二步：开启慢查询日志
set global slow_query_log = on ;

日志路径也可以自定义：

set global slow_query_log_file = '路径';
第三步：查看慢查询的时间临界值
show variables like '%long%';
第四步：设置慢查询的时间标准
set long_query_time=0.4;
注意：重启mysql服务会让在交互界面设置的慢查询恢复到默认
永久生效的设置方法：修改配置文件 vi /etc/my.cnf
[mysqld]
slow_query_log = 1
long_query_time = 0.1
slow_query_log_file =/usr/local/mysql/mysql_slow.log

最后必须重启服务才能生效！
  6. 性能查询
    第一步：查看性能详情是否开启
mysql> show variables like '%profiling%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | OFF   |
| profiling_history_size | 15    |
+------------------------+-------+
第二步：开启性能记录功能
set profiling = on ;
第三步：查看性能的记录
mysql> show profiles;
+----------+------------+---------------------------------------------------+
| Query_ID | Duration   | Query                                             |
+----------+------------+---------------------------------------------------+
|        1 | 0.00177775 | show variables like '%profiling%'                 |
|        2 | 0.00037900 | select * from test where id='087878'              |
|        3 | 0.34618025 | select * from test where servnumber='1367008787'  |
|        4 | 0.31986825 | select * from test where servnumber='13670087879' |
+----------+------------+---------------------------------------------------+
第四步：查看语句的执行性能详情
mysql> show profile for query 4;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000100 |
| checking permissions | 0.000010 |
| Opening tables       | 0.000023 |
| init                 | 0.000045 |
| System lock          | 0.000015 |
| optimizing           | 0.000016 |
| statistics           | 0.000028 |
| preparing            | 0.000020 |
| executing            | 0.000006 |
| Sending data         | 0.319489 |
| end                  | 0.000037 |
| query end            | 0.000012 |
| closing tables       | 0.000012 |
| freeing items        | 0.000040 |
| cleaning up          | 0.000017 |
+----------------------+----------+
性能线程的详细解释官方文档链接：
https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html
sql优化
  1. explain用于查询sql语句的操作过程
  2. 为经常查询的字段建立索引
  3. 为经常分组，排序，union，distinct建立索引
  4. 数据少的字段不建立索引
  5. 尽量不用default null 而是 not null default ‘’
  6. 多用distinct少用group by
  7. 多用between少用in
  8. 避免类型转换
  9. 避免查询所有字段
  10. 避免使用or
  11. %放在前面不会进行索引使用
中文乱码问题
  1. 可以用语句查看各部分编码
    show variables like 'character%';
    结果
    mysql> show variables like 'character%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8                             |
| character_set_connection | utf8                             |
| character_set_database   | utf8                             |
| character_set_filesystem | binary                           |
| character_set_results    | utf8                             |
| character_set_server     | utf8                             |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+

    character_set_client：客户端请求数据的字符集
character_set_connection：客户端与服务器连接的字符集
character_set_database：数据库服务器中某个库使用的字符集设定，如果建库时没有指明，将默认使用配置上的字符集
character_set_results：返回给客户端的字符集(从数据库读取到的数据是什么编码的)
character_set_server：为服务器安装时指定的默认字符集设定。
character_set_system：系统字符集(修改不了的，就是utf8)
character_sets_dir：mysql字符集文件的保存路径
    修改编码
    临时：set names gbk;
永久：修改配置文件my.cnf里边的
	[client]
	default-character-set=gbk
	作用于外部的显示
	[mysqld]
	character_set_server=gbk
	作用于内部，会作用于创建库表时默认字符集
	
修改库的字符集编码
alter database xiaoxiao default character set gbk;

修改表的字符集编码
alter table employee default character set utf8;
数据备份

  1. 数据库的备份类型：

    （1）完全备份：对整个数据库的数据进行备份

（2）部分备份：对部分数据进行备份（可以是一张表也可以是多张表）

	  增量备份：是以上一次备份为基础来备份变更数据的，节约空间

	  差异备份：是以第一次完全备份的基础来备份变更备份的，浪费空间

  2. 数据库备份的方式：

    （1）逻辑备份：直接生成sql语句保存起来，在恢复数据的时候执行备份的sql语句来实现数据的恢复

（2）物理备份：直接拷贝相关的物理数据

    区别：逻辑备份效率低，恢复数据效率低，但是逻辑备份节约空间；物理备份浪费空间，但是相对逻辑备份而言效率比较高

  3. 数据库备份的场景：

    （1）热备份：备份时，数据库的读写操作不会受到影响

（2）温备份：备份时，数据库的读操作可以进行，但是写操作不能执行

（3）冷备份：备份时，不能进行任何操作

  4. 如何利用mysql自带命令mysqldump来备份单库或者多库

    mysqldump使用语法：

mysqldump -u 用户 -h host -p 密码 dbname table > 路径

远程备份单库例子：

mysqldump -uroot -pabc123456 -h120.25.93.69 zabbix | gzip  > /mysql_data_back/zabbix_users.sql.gz
远程备份单库例子并保留创建库语句：

mysqldump -uroot -pabc123456 -h120.25.93.69 --databases zabbix | gzip  > /mysql_data_back/zabbix_bak.sql.gz
远程备份单库单表的例子：

mysqldump -uroot -pabc123456 -h120.25.93.69 zabbix  users | gzip  > /mysql_data_back/zabbix_users.sql.gz
远程备份多库的例子：

mysqldump -uroot -pabc123456 -h120.25.93.69 --databases zabbix XD | gzip  > /mysql_data_back/zabbix_XD.sql.gz
远程备份全库的例子：

mysqldump -uroot -pabc123456 -h120.25.93.69 --all-databases  | gzip  > /mysql_data_back/all.sql.gz
	​

  5. mysql数据安全之mysql数据的恢复

    	简介：如何恢复数据
	
	远程恢复数据（备份的数据文件里有创建库的语句）：
	
	mysql -uroot -pabc123456 -h120.25.93.69  < zabbix_bak.sql
	​
	远程恢复数据（备份的数据文件里没有创建库的语句）：
	
	mysql -uroot -pabc123456 -h120.25.93.69  zabbix < zabbix_bak.sql

  6. mysql数据安全之物理备份

    看找数据库源文件路径（一）：

mysql> show variables like 'datadir%';
+---------------+---------------+
| Variable_name | Value         |
+---------------+---------------+
| datadir       | /data/mydata/ |
+---------------+---------------+
​
看找数据库源文件路径（二）：

 cat /etc/my.cnf
​
MyISAM表源文件：

db.opt：创建库的时候生成，主要存储着当前库的默认字符集和字符校验规则
​
.frm ：记录着表结构信息的文件
​
.MYI：记录着索引的文件
​
.MYD :记录着表的数据
​
InnoDB表源文件：InnoDB有着共享表空间跟独立表空间的概念。

db.opt：创建库的时候生成，主要存储着当前库的默认字符集和字符校验规则

.frm ：记录着表结构信息的文件

.ibd ：独立表空间，里边记录这个表的数据和索引

ibdata1：共享表空间，里边记录表的数据和索引

请求全局读锁：

flush tables with read lock;

解锁：

unlock tables;

  7. mysql数据安全之利用二进制日志mysqlbinlog备份数据

    简介：讲解如何利用二进制日志来备份数据

什么是二进制日志：

二进制日志就是记录着mysql数据库中的一些写入性操作，比如一些增删改，但是，不包括查询！

二进制日志有哪些功能：

一般情况下，二进制日志有着数据复制和数据恢复的功能

注意：

开启二进制日志会有1%的性能消耗！

查看二进制日志是否开启：

mysql> show variables like 'log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
​
 

开启二进制日志 : vi /etc/my.cnf

[mysqld]
log-bin=/data/mydata/log_bin/mysql-bin
server-id=1
​
查看所有的binlog日志列表：

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     23638 |
+------------------+-----------+
​
刷新二进制日志：

flush logs;
​
重置(清空）二进制日志文件：

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1091 |
+------------------+-----------+
​
使用mysqldump备份数据时，加上-F选项可以重新生成一个新的二进制日志文件

mysqldump -uroot -p XD user -F  > user_bak.sql

  8. mysql数据安全之利用二进制日志mysqlbinlog恢复数据

    简介：讲解如何利用二进制日志来恢复数据

查看二进制日志文件的内容报错：

[root@xdclass-public log_bin]# mysqlbinlog mysql-bin.000002
mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8'
​
解决：

第一种：在mysqlbinlog 后边加上 --no-defaults 
​
第二种：注释掉配置文件里边的default-character-set=utf8
​
把二进制日志文件导出成普通文件：

 mysqlbinlog --base64-output=DECODE-ROWS -v mysql-bin.000002 > mysqlbin.sql
​
找出要恢复的位置：

（1）找出关键字的行数：mysqlbinlog --no-defaults mysql-bin.000002 | cat -n  | grep -iw 'drop'
​
[root@xdclass-public log_bin]# mysqlbinlog --no-defaults mysql-bin.000002 | cat -n  | grep -iw 'drop'
  4180  DROP TABLE `user` /* generated by server */
​
​
（2）打印出相关内容：mysqlbinlog --no-defaults mysql-bin.000002 | cat -n | sed -n '4170,4180p'
​
[root@xdclass-public log_bin]# mysqlbinlog --no-defaults mysql-bin.000002 | cat -n | sed -n '4170,4180p'
  4170  # at 59578
  4171  #190419  0:41:48 server id 1  end_log_pos 59609 CRC32 0x36cda2b7        Xid = 6380
  4172  COMMIT/*!*/;
  4173  # at 59609
  4174  #190419  0:41:48 server id 1  end_log_pos 59674 CRC32 0x8de2f06a        Anonymous_GTID  last_committed=145      sequence_number=146
  4175  SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
  4176  # at 59674
  4177  #190419  0:41:48 server id 1  end_log_pos 59787 CRC32 0x6b2edd2b        Query   thread_id=14    exec_time=0     error_code=0
  4178  use `XD`/*!*/;
  4179  SET TIMESTAMP=1555605708/*!*/;
  4180  DROP TABLE `user` /* generated by server */
[root@xdclass-public log_bin]# 
​
恢复数据：

第一步：把备份的数据表user恢复到数据库中：mysql -uroot -p XD < /mysql_data_back/user_bak.sql

第二步：利用上面找到的删除的位置进行恢复数据

mysqlbinlog --no-defaults --set-charset=utf8  --stop-position="59674" /data/mydata/log_bin/mysql-bin.000002 | mysql -uroot -p
MySQL优化之大字段longtext、text引发的生产问题
守望希望
03-21 1万+
背景 对接多个外部接口，需要保存请求参数以及返回参数，方便消息的补偿，因为多个外部接口，多个接口字段都不统一，整体使用一个大字段（longtext）进行存储，但是当数据只有40w的时候查询速度就非常慢长达40s左右。 CREATE TABLE `risk_request_log_bak` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID', `risk_buss_no` varchar(32) DEFAULT NULL COM
查询mysql数据库中符合条件的表名并修改表内字段的类型
weixin_50899360的博客
01-21 1648
今天上班的时候领导提出来一个问题让我来修改 修改数据库中一类表中字段的类型和该字段的注释 首先肯定是要查询到时哪一类的表 SELECT table_name 表名称, table_comment 表注释, FROM information_schema.TABLES WHERE table_schema = '数据库名称' -- 表名的查询条件 AND table_name LIKE '%doc_%' ORDER BY table_name 其次是修改表字段中类型和注释
记录业务操作日志实现
zwrlj527的专栏
06-02 723
自定义操作业务日志就这么简单业务操作记录到变更字段避免不了跟业务耦合其实还有一种方法是跟每张表建一张影子表，带版本号，通过版本找历史好，就写到这里，希望可以帮到大家，uping！
MySQL数据库中常用数据类型及字段约束
ANFO的博客
08-15 646
一.数据类型 1.整型 由小到大：tinyInt、smallint、mediumint、int 2.小数 float(m,d)、double(m,d) 这里要注意，MySQL数据库中的浮点型和编程语言中的浮点型不一样，其中m表示数字长度，d表示小数位数，例如float(5,2)最大值999.99。 3.文章 数据库当然不只能存些简单的数据，还可以存储文章，text就是文章的数据类型。...
MySQL开发——【字段类型、约束】
dengwei5160的博客
04-16 270
创建数据表 基本语法： 1 create table 数据表名称( 2 字段名称 字段类型 字段约束, 3 ... 4 )[表选项]; 字段类型 整数型 1 tinyint ：占用1个字节的长度，无符号型，可以表示0-255，有符号型，可以表示-128-127 2 smallint ：占用2个字节的长度，无符号型，可以表示0-6553...
MySQL知识点思维导图PDF
03-23
MySQL知识点思维导图PDF
mysql知识点整理汇总
最新发布
01-08
汇总整理了mysql的知识点
计算机二级考试MySQL知识点 常用MYSQL命令
01-21
计算机二级考试MySQL知识点基础篇，MYSQL常用命令，具体如下 常用MYSQL命令（将计算机二级站点加入收藏夹） 　启动：net start mySql; 　进入：mysql -u root -p/mysql -h localhost -u root -p databaseName; 　...
mysql知识点
01-12
mysql的一些知识点 LAST_INSERT_ID() 最近插入表的自动增加字段的当前值 ifnull，或者coalesce 字段为空时设置默认值
MySQL知识点脑图.png
04-08
知识图示
MySQL数据库基础（数据类型，表的结构，约束）
Welcome to my blog.
04-13 678
这里操作mysql使用的是命令行和workbench 5.7版本的图形化界面的客户端。 文章目录1️⃣预备知识2️⃣数据库的基本操作1.显示当前数据库2.创建数据库3.使用数据库4.删除数据库3️⃣常用数据类型1.数值类型2.字符串类型3.日期类型 1️⃣预备知识 SQL中不区分大小写。 库名、表名、列名建议不要使用中文，表中的数据可以使用中文。 区分三种引用符： 反引号( ` ),在库名、表名、列名上使用。一般不用反引号，但是名称和关键字重复了就必须带上。 单引号( ')，双引号( " )，在数据上使用
Bootstrap wysiwyg，将富文本数据保存到mysql
沉默王二
12-24 5万+
Bootstrap提供了一个叫[wysiwyg](http://www.bootcss.com/p/bootstrap-wysiwyg/)的富文本组件，用来显示和编辑富文本数据，但如何将编辑后的数据保存到mysql数据库，却不得而知
mysql 存储 longtext 与 longblob数据类型的区别
zxjdC的博客
06-12 1万+
最近项目中同时用到了存储大文本的两种情况，做一小结。 mysql数据类型参考连接：mysql数据类型 1.将文件存储到 mysql 中，存储为二进制数据。 mysql 的表字段类型选择了LONGBLOB。在java中生成的实体类对应的字段类型为 byte[ ]; CREATE TABLE `tmp_jmeter_case` ( `ID` int(16) NOT NULL AUTO_INCREMENT COMMENT '主键', `JMETER_CASE_NAME` varchar(100) DEFA
mysql 字段类型说明
南海神尼
04-06 1715
  MySQL支持大量的列类型，它可以被分为3类：数字类型、日期和时间类型以及字符串(字符)类型。本节首先给出可用类型的一个概述，并且总结每个列类型的存储需求，然后提供每个类中的类型性质的更详细的描述。概述有意简化，更详细的说明应该考虑到有关特定列类型的附加信息，例如你能为其指定值
MYSQL的字符类型TEXT、TINYTEXT、MEDIUMTEXT、LONGTEXT选择
IT密码
07-26 375
储存不区分大小写的字符数据 TINYTEXT 最大长度是 255 (2^8 - 1) 个字符。 TEXT 最大长度是 65535 (2^16 - 1) 个字符。 MEDIUMTEXT 最大长度是 16777215 (2^24 - 1) 个字符。 LONGTEXT 最大长度是 4294967295 (2^32 - 1) 个字符 ...
如何在mysql中插入longtext类型大文本
07-21 2948
我这有一篇别人的, 你先用用吧(未经过测试,但应该没问题) // MySql2.cpp : Defines the entry point for the console application. // #include "stdafx.h" //在DBMS中线要创建数据库www，table www，file字段数据类型用LONGTEXT即可测试 //测试文件c:\test.iso，你可以找任何一
mysql数据类型介绍(含text,longtext,mediumtext说明)
热门推荐
百事乐
10-06 7万+
转载，文章原连接已经失效，百度快照找到的。 MySQL支持大量的列类型，它可以被分为3类：数字类型、日期和时间类型以及字符串(字符)类型。本节首先给出可用类型的一个概述，并且总结每个列类型的存储需求，然后提供每个类中的类型性质的更详细的描述。概述有意简化，更详细的说明应该考虑到有关特定列类型的附加信息，例如你能为其指定值的允许格式。     由MySQL支持的列类型列在下面。下列代
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值