运维DBA3月份学习笔记

什么是SQL(菜鸡的入门学习，有些大学学过就跳过没写了，如果有什么错误，欢迎再评论区进行指正)

	sql，中文意思是结构化查询语言，它是一种对关系型数据库中的数据进行定义和操作的语言方法。
结构化查询语言是一种数据库查询和程序设计语言用于存取数据以及查询、更新和管理关系型数据库系统：同时也是数据库脚本文件的拓展名

sql的分类
		1.数据查询语言（DQL）
			DDL称为“数据检索语句”，作用是从表中获得数据，以确定数据怎样在应用程序给出  select
		2.数据操作语言（DML）
			其语句包括动词 insert，update和delete 
		3.事务处理语言（TPL）
			他的语句能确保被DML语句影响的表的所有行及时得以更新。包括 begin，transaction，commit和rollback。
		4.数据控制语言（DCL）
			它的语句通过GRANT和REVOKE获得许可。
		5.数据定义语言（DDL）
			create和drop
		6.指针控制语言（CCL）
MYSQL数据库常见管理应用
1.创建数据库
	create database <数据库名> （数据库名不能以数字开头）默认会创建格式latin1
	示例：create database if not exists test default character set utf8;
	安装时未指定mysql的字符集，创建数据库的时候就需要设置字符集，
	中文乱码时通常都是字符集不适应中文
	GBK: create database 数据库名 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
	UTF8: CREATE DATABASE 数据库名 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci；
	查看数据库字符集：show variables like '%char%';
2.数据库
	查看所有数据库  
	show databases；
	显示当前的数据库：
	eelect database();
	删除数据库：
	drop database <数据库名>;
	查看数据库版本
	mysql> select version();
	+-----------+
	| version() |
	+-----------+
	| 5.6.47    |
	+-----------+
	1 row in set (0.00 sec)
	查看当前数据库用户
	mysql> select user();
	+----------------+
	| user()         |
	+----------------+
	| root@localhost |
	+----------------+
	1 row in set (0.01 sec)
	查看数据库时间
	mysql> select now();
	+---------------------+
	| now()               |
	+---------------------+
	| 2020-03-24 02:03:49 |
	+---------------------+
	1 row in set (0.00 sec)
	删除MySQL数据库的多余用户
	drop user “user”@“定义域”；
MySQL用户创建及授权
	1.代码
		grant all on *.* to "test"@"%" identified by "test";
		创建用户的同时并授权等价于下面两行代码
		create user “test”@"%" identified by "test";
		grant all on *.* to "test"@"%";
		取消用户插入授权
		revoke insert on *.* from "xu"@"%";（所对应的权限和库以及表名都要对上，用户以及用户域）
以默认字符集建库
	MySQL会因为版本不同，造成MySQL引擎不一样
	5.1及以前为MYISam
	5.5.5及以后为innoDB
	1.代码：
		create database test;
	2.创建表
		create table user(name char(16) keyprimary,age int default "0",address char(16) not null);
		 show create table user;
			+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
			| Table | Create Table                                                                                                                                                |
			+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
			| user  | CREATE TABLE `user` (
			  `name` char(16) DEFAULT NULL,
			  `age` int(11) DEFAULT '0',
			  `address` char(16) DEFAULT NULL
			) ENGINE=InnoDB DEFAULT CHARSET=latin1 |   默认字符集与数据库一致
			+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
	3.查看数据表结构
		desc 表名
			mysql> desc user;
			+---------+----------+------+-----+---------+-------+
			| Field   | Type     | Null | Key | Default | Extra |
			+---------+----------+------+-----+---------+-------+
			| name    | char(16) | YES  |     | NULL    |       |
			| age     | int(11)  | YES  |     | 0       |       |
			| address | char(16) | NO   |     | NULL    |       |

		show columns from 表名;
			mysql> show columns from user;
			+---------+----------+------+-----+---------+-------+
			| Field   | Type     | Null | Key | Default | Extra |
			+---------+----------+------+-----+---------+-------+
			| name    | char(16) | YES  |     | NULL    |       |
			| age     | int(11)  | YES  |     | 0       |       |
			| address | char(16) | NO   |     | NULL    |       |
			+---------+----------+------+-----+---------+-------+
			3 rows in set (0.00 sec)
		表已经存在的情况下修改为主键
		mysql> desc user;
		+---------+----------+------+-----+---------+-------+
		| Field   | Type     | Null | Key | Default | Extra |
		+---------+----------+------+-----+---------+-------+
		| name    | char(16) | YES  |     | NULL    |       |
		| age     | int(11)  | YES  |     | 0       |       |
		| address | char(16) | NO   |     | NULL    |       |
		+---------+----------+------+-----+---------+-------+
		3 rows in set (0.00 sec)
		alter table user change name name int primary key auto_increment;
		mysql> desc user;
		+---------+----------+------+-----+---------+----------------+
		| Field   | Type     | Null | Key | Default | Extra          |
		+---------+----------+------+-----+---------+----------------+
		| name    | int(11)  | NO   | PRI | NULL    | auto_increment |
		| age     | int(11)  | YES  |     | 0       |                |
		| address | char(16) | NO   |     | NULL    |                |
		+---------+----------+------+-----+---------+----------------+
		3 rows in set (0.00 sec)
		key index_name(age)
		删除主键索引
			alter table user drop primary key;
		添加索引：
		alter table user add index_name(列名)；
		删除索引：
		alter table user drop index index_name; 
		查看索引：
		show index from user\G
		mysql> show index from user\G
	索引具有前缀生效特性：
		index(a,b,c) a，ab，abc具有索引效果 bc，ac不具有索引效果。
	创建一个非空索引：
		create unique index index_name on user(列名);
	索引创建条件：
		创建在查询语句的条件字段上，尤其是创建在唯一值比较多的表里
DML语句
		SQL插入语句
		insert into 表名(列名，列名...) values("值","值"....),("值","值"....),("值","值"....).....；
		MySQL备份语句：
			mysqldump -uroot -p密码 -B 数据库名 > /保存位置/mysqlback.sql
		表数据更新：
			update 表名 set 字段名=数值 where 字段名=值;
		防止误操作：
			mysql -U 在update时需要加入条件（where）
DQL语句	
		使用explain查看sql语句执行情况
		explain select * from test  where name="dd"\G
		
		select * from test;和truncate table test区别
			truncate速度更快。相当于清空物理文件，delete逻辑清除按行处理。
修改表结构
		alter table 表名 add 字段名 字段类型 after column；	只能使用 after和first		
修改数据库表名
		rename table 原表名 to 新表名；
		rename table test to xu;
MySQL数据库乱码
	思想：
		Linux，客户端，服务端，库，表，程序字符集统一
		查看Linux系统的字符集
		export |grep LANG
		declare -x LANG="en_US.UTF-8"
解决方式
	set table_names latinl；(临时性 )
	使编码格式为拉丁，确保插入的数据不乱吗
	mysql数据库文件导入时设置编码格式
	mysql -uroot -p --default-character-set=latinl database_name < /opt/data.sql
	在配置文件里设置客户端及服务端相关参数：
		更改my.cnf客户端模块的参数 可以实现set names latinl的效果，并且永久生效
		[client]
		default-character-set=latinl
		提示:无需重启服务，退出重新登录就生效。相当于set names latinl;
		更改my.cnf参数
		[mysqld]
		default-charcater-set=latinl适合5.1及以前的版本
		character-set-server=latinl适合5.5
	查看参数：
		show variables;
		show global status；
		mysql -e 可以在命令行执行mysql语句