Mysql笔记整理

MySQL数据库(增-删-改-查)

Mysql中的默认数据库介绍
information_schema：主要存储了系统中的一些数据库对象信息。比如用户表信息、列信息、权限信息、字符集信息、分区信息等。
cluster：存储了系统的集群信息。
mysql：存储了系统的用户权限信息。
test：系统自动创建的测试数据库，任何用户都可以使用。

DDL

对于库的操作：
create database dbname;     创建一个叫dbname的数据库
show databases;             查看所有数据库
use dbname;	            进入dbname数据库
show tables;                查看当前所在数据库中的表
drop database dbname;	    删除一个叫dbname的数据库

对于表的操作:
create tabale tablename (字段1 数据类型（长度），字段2 数据类型（长度）...）;
举例：
create table emp(ename varchar(10),hiredate date,sal decimal(10,2),deptno int(2));
创建一个叫emp的表

desc tablename;                       查看tablename表的结构
show create table tablename G;       查看tablename表的结构，同时可以看到存储的引擎和字符编码
drop table tablename;		      删除tablename这个表

修改表结构的操作：
1.改字段类型
alter table tablename modify 字段 字段类型 [first | alter 字段名]
举例
alter table tablename modify ename varchar (20);    修改tablename表中ename字段的定义

2.增加表字段的操作：
alter table tablename add 字段 字段定义 [first | after 字段名]
first   添加字段为第一个字段
after   添加字段到指定字段后
默认追加
举例：
alter table emp add age int(2) after sal;           添加age字段到sal后

3.删除表字段：
alter table tablename drop 字段;
举例：
alter table emp drop age                            在表emp中删除age字段

4.修改字段名：
alter table tablename change 旧字段 新字段 字段类型 [first | after 字段名]
举例：
alter table emp change age age_new int(2) first;     将emp中的age字段改为age_new字段，同时修改了字段类型

5.修改表名：
alter table tablename rename 新表名;
alter table emp rename emp1;         将表名emp改为emp1

DML

对数据进行操作
增	insert（插入数据）
删	delete（删除数据）
改	update（更新数据）

DQL

查	select（查询数据）

1.插入数据 insert

insert into 表名（字段1,字段2,...） values（'值1','值2',...）
举例：
insert into emp (ename,hiredate,sal,deptno) values('zhangshan','2020-02-07','3000','1');     向emp表中插入一行数据
insert into emp values('lisi','2020-02-07','10000','2');                                     向emp表中插入一行数据
insert into emp (ename,sal) values('wangwu','100');                                          向emp表中插入一行数据
显示结果如下：
MariaDB [dbname]> select * from emp;
+-----------+------------+----------+--------+
| ename     | hiredate   | sal      | deptno |
+-----------+------------+----------+--------+
| zhangshan | 2020-02-07 |  3000.00 |      1 |
| lisi      | 2020-02-07 | 10000.00 |      2 |
| wangwu    | NULL       |   100.00 |   NULL |
+-----------+------------+----------+--------+
3 rows in set (0.00 sec)

举例（一次录入多个数据）：
insert into dept values(1,'zhangsan'),(2,'lisi'),(3,'wangwu');
显示结果如下：
MariaDB [dbname]> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
|      1 | zhangsan |
|      2 | lisi     |
|      3 | wangwu   |
+--------+----------+
3 rows in set (0.00 sec)

2.修改数据 update

update 表名 set 字段=值1,字段2=值2,...[where 字段=值]
举例：
之前数据如下：
MariaDB [dbname]> select * from emp;
+-----------+------------+----------+--------+
| ename     | hiredate   | sal      | deptno |
+-----------+------------+----------+--------+
| zhangshan | 2020-02-07 |  3000.00 |      1 |
| lisi      | 2020-02-07 | 10000.00 |      2 |
| wangwu    | NULL       |   100.00 |   NULL |
+-----------+------------+----------+--------+
3 rows in set (0.00 sec)
用update修改数据后如下：
update emp set sal='1000' where ename='wangwu';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [dbname]> select * from emp;
+-----------+------------+----------+--------+
| ename     | hiredate   | sal      | deptno |
+-----------+------------+----------+--------+
| zhangshan | 2020-02-07 |  3000.00 |      1 |
| lisi      | 2020-02-07 | 10000.00 |      2 |
| wangwu    | NULL       |  1000.00 |   NULL |
+-----------+------------+----------+--------+
3 rows in set (0.00 sec)

为wangwu更改入职日期和编号
MariaDB [dbname]> update emp set hiredate='2020-02-07',deptno='3'  where ename='wangwu';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [dbname]> select * from emp;
+-----------+------------+----------+--------+
| ename     | hiredate   | sal      | deptno |
+-----------+------------+----------+--------+
| zhangshan | 2020-02-07 |  3000.00 |      1 |
| lisi      | 2020-02-07 | 10000.00 |      2 |
| wangwu    | 2020-02-07 |  1000.00 |      3 |
+-----------+------------+----------+--------+
3 rows in set (0.00 sec)

两个表联合修改数据：
update emp a,dept b set a.sal=a.sal*b.deptno,a.ename =b.deptname where a.deptno=b.deptno;
delete a,b from emp a,dept b where a.deptno=b.deptno and a.deptno=2;

3.删除数据 delete

delete from 表名 [where 字段=值];
delete from dept where depton=3;

4.查询数据 select

select * from 表名 [where 字段=值];
select 字段1,字段2,... from 表名 [where 字段=值];

(1)查询不重复的数据
select distinct 字段 from emp; 打印时去掉重复的字段

(2)多条件查询
可以使用逻辑关系符 and or not ;= > < >= >= !=
select * from emp where hiredate='2019-02-07' and sal<='3000';

(3)排序 order by
select * from 表名 [where 字段=值] [order by 字段 [desc (降序)/asc(升序)]]
select * from emp order by sal desc;
select * from emp order by sal,deptno desc;

(4)分页 limit
select * from 表名 [where 字段=值] [order by 字段 [desc (降序)/asc(升序)]]
[limit [起始位置],行数];
select * from emp order by sal desc limit 1;

(5)聚合
sum()求和   count(*)数据条数   max最大值   min 最小值
select [字段1,字段2,...] [聚合函数] from 表名 [where 字段=值] [group by 字段1,字段2,] [with rollup] having条件;

聚合函数：表示要做聚合操作
group by：分组，表示要进行分组聚合的字段
with rollup：选用表示对聚合分类后的数据在汇总
having：表示对分类后的结果再次进行过滤

where和having区别,where用于聚合前,having用于聚合后
select sal,count(1) from emp group by sal having count(1)>1;

按名字分组然后最小分数大于80的列出来
select name from report group by name having min(score)>80;
select name from report group by name having min(score)>80 and count(subject)=3;
select distinct name from report where name not in (select name from report where score<=80);

子查询
查询时，需要的条件是另一个select语句的结果。
子查询用到的关键字主要包括in, not in ,=,!=等

多表联合查询：
多表连接查询就是指数据同时从多张表中获取，查询语句涉及到多张表。
内连接：inner join
外连接：
左外连接：left outer join
右外连接：right outer join
全连接：union
交叉连接：cross join

内连接查询：选用两张表中互相匹配的记录(两张表同时符合某一个指定条件的数据记录组合)
select ename,deptname,sal from emp,dept where emp.deptno=dept.deptno;

DCL

用户权限管理

grant all privilege on discuz.* to 'rundiscuz'@'%' identified by '123123';

all privilege  所有权限
select
insert
updeat
delete

主机地址：
%  所有的主机
192.168.1._   代表网段
192.168.1.10
域名或主机名

flush privileges;  #刷新数据库权限/授权刷新
show grants for admin@localhost;   #查询指定用户的所有权限
grant select on dbname.* to 'admin'@'localhost' identified by '123123';        #只给admin用户select查询dbname.*的权限
grant all privileges on dbname.* to admin@localhost identified by '123123';    #给admin用户dbname.*数据库下的所有权限
revoke all on dbname.* from 'admin'@'localhost';                               #撤销指定用户所有权限
revoke select on dbname.* from 'admin'@'localhost';                            #撤销admin用户对dbname的select查询dbname.*的权限

创建mysql用户

1.首先获取密文密码
mysql> select password('123123');
+-------------------------------------------+
| password('123123')                        |
+-------------------------------------------+
| *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
+-------------------------------------------+
1 row in set (0.00 sec)
2.创建成功
mysql> create user 'name'@'host' identified by password '*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1';
Query OK, 0 rows affected (0.00 sec)

查看mysql用户列表
select user,host,password from mysql.user;

删除mysql用户
drop user 'name'@'host';

重命名mysql用户名/host也可以修改
rename user 'name'@'host' to 'new_name'@'host'

给当前用户设置密码
set password = password ('new_password');
给其他用户设置密码
set password for 'user'@'host' = password('new_password')

mysql权限列表
all				#设置所有除grant option之外的所有权限
alter				#允许使用alter table
creater				#允许使用creater	table
creater user		        #用户管理权限
delete				#允许使用delete
drop				#允许使用drop table
insert				#允许使用insert
replication slave	        #从主服务器读取二进制日志事件
select				#允许使用select
show databases		        #显示所有数据库
update				#允许使用update

MySQL数据库的三种安装方式:

• yum (rpm)
• 源码包安装
• 二进制包安装

介绍二进制安装方式

解包:tar xf mysql-5.6.30-linux-glibc2.5-x86_64.tar.gz
mv mysql-5.6.30-linux-glibc2.5-x86_64 /usr/local/mysql 
useradd -M -s /sbin/nologin mysql
yum -y install autoconf
cd /usrl/local/myslq/scripts/
初始化数据库:
./mysql_install_db --user=mysql --datadir=/usr/local/mysql/data/ --basedir=/usr/local/mysql/
准备mysql配置文件
cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf
准备启动脚本
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

echo 'export PATH="$PATH:/usr/local/mysql/bin"' >> /etc/profile
source /etc/profile

mysql数据库忘记root密码怎么解决?

1.关闭数据库(或kill)
/etc/init.d/mysqld stop 
2.将数据库跳过授权表以安全模式在后台运行
mysqld_safe --skip-grant-table &
3.无密码登录数据库后，使用update语句修改密码;
update mysql.user set password=Password('123456') where user='root';
4.关闭安全模式的数据库，重启数据库
kill 对应的pid （或者关闭数据库）
启动数据库后，使用新密码登录

mysql 命令(mysql的客户端)

-u 指定用户 
-p 指定密码
-P 指定端口，默认3306 
-h 指定远程主机地址 
-e 执行SQL

mysql支持中文字符集；

中文字符集  GBK  GB2312 utf-8

create database dbname character set utf8;  创建数据库时指定字符集；
create table dbname.test (name varchar(20),...) character set utf8;

在创建表时指定字符集
alter database dbname character set utf8;修改数据库字符集
alter table dbname.test character set utf8;修改表的字符集(重启数据库生效)

show character set;  mysql中可以使用的字符集
show collation like 'utf%';    支持的校验字符规则
show variables like 'character_set_database';   默认使用的字符集
show variables like 'collation_data';  默认使用的字符校验规则

永久修改字符编码;

在mysql配置文件中/etc/my.cnf添加:
[mysqld]
character-set-server=utf8 

[mysql] 
default-character-set=utf8                                                 
[client]
default-character-set=utf8

show variables like 'char%';

show create table dbname.test;  可以查看表使用的字符集

索引与事务

数据库索引：是对表中的一列或多列数据的值进行排序的一种数据结构，它是一列或多列的值得集合；

mysql
举例：目前10w条数据
没有索引：select * from tablename where a=1;全表扫描
如果有索引：比如b列创建索引，只扫描b这列;

索引的作用

• 设置合适的索引,数据库能够加快查询速度;
• 可以降低数据库的IO成本,并且索引还可以降低排序的成本;
• 通过唯一性索引保证表数据的唯一性,可以加快表与表直接的连接查询;
• 在使用分组和排序时,可以减少分组和排序的时间;

索引也是表的一部分,如果索引创建的过多,会占用更多的空间,也会影响update或者是insert语句的执行;

索引的分类

• 普通索引(index)：最基本的索引,没有什么限制;
• 唯一性索引(unique)：索引列的所有值只能出现一次,也就是说必须为唯一,可以为空;
• 主键(primary key)：特殊的唯一性索引,不能为空;
• 全文索引(fulltext)：用于查找关键字,通常字段类型为text;
• 多列索引(组合索引,复和索引)

普通索引

创建普通索引：
创建表时：creat table 表名 (字段1 字段类型,字段2 字段类型[NOT NULL]...,index 索引名(字段1(length)));
创建表后：
create index 索引名 on 表名 (字段[(length)]);
alter table 表名 add index 索引名 (字段[(length)]);

create index idx_sal on sal(薪资);

查看索引
show index/keys from tablename G;

删除索引：
drop index 索引名称 on 表名;
alter table 表名 drop index 索引名称;

唯一性索引

创建表时：creat table 表名 (字段1 字段类型,字段2 字段类型[NOT NULL]...,unique index 索引名(字段1(length)));
创建表后：
create unique index 索引名 on 表名 (字段[(length)]);
alter table 表名 add unique index 索引名 (字段[(length)]);

查看索引
show index/keys from tablename G;

删除索引：
drop index 索引名称 on 表名;
alter table 表名 drop index 索引名称;

主键 （创建主键的值不能有空值，每个表中只有一个主键）

创建表时：creat table 表名 (字段1 字段类型,字段2 字段类型[NOT NULL]...,primary key 索引名(字段1(length)));
创建表后：
alter table 表名 add primary key 索引名 (字段[(length)]);

查看索引
show index/keys from tablename G;

删除主键：
alter table 表名 drop primary key;

补充说明：
length的作用：
有时需要对文本或者长字符串的字段建立索引，如果直接对该字段创建索引，会增加索引的存储空间，并且降低索引的利用率。
length的作用是指定该字段对应值的前几个字符的创建索引;使用了length的索引称为前缀索引

组合索引：
假设a,b两个字段都有索引，查询条件是a=1，b=2，查询时先找出a=1的结果，在该结果中再找b=1，mysql执行的时候，只会用到一个索引来查询，此时可以对a,b做组合索引。

索引失效：
1.条件中有or    a=1 or b=2
2.在like中查询关键字前有%    (%a%)
3.索引列是表达式或函数的一部分    where a>10
4.目测全表扫描比索引快
5.字段的数据类型是字符串，条件中的数据一定要加'',不加不使用索引;

创建索引的原则：
1.超过300的行应该有索引;
2.经常出现在where语句中的字段,应该建立索引;
3.索引尽量建立在小字段上或者使用前缀索引;
4.经常多表连接查询的表，应该在连接字段上建立索引;
5.唯一性太差的字段不适合建立索引;
6.表更新速度过快,不适合建立索引;

事务(transation)

事务是一种机制,一个操作序列,包含了一组数据的操作语句,并且把所有语句作为整体进行操作;
主要用于处理操作量比较大,复杂度高的数据;

• mysql中使用Innodb存储引擎的表或库才可以支持事务;
• 事务处理可以用来维护数据库的完整性
• 事务用来管理 insert update delete

事务的ACID特性：

• 原子性(atomicity)：事务是一个完整的整体操作，事务中的各个操作是不可分的，所以事务是以一个整体提交或回滚;

• 一致性(consistency)：当事务完成以后,数据必须处于一致的状态;
  开始之前：数据一致
  开始之后：数据有可能不一致
  完成之后：数据一致

• 隔离性(lsolation):数据对所有并发事务是彼此隔离的,以防多个事务并发执行时,由于交叉执行而导致数据不一致;

• 持久性(durability)：不管系统发生了什么样的故障,事务的处理结果都是永久的;

事务的操作

在mysql中默认开启自动提交, 当执行SQL语句时事务便自动提交;

show variables like 'autocommit'; 查看当前是否是自动提交事务
自动提交	set autocommit=1;
手动提交	set autocommit=0;

永久修改事务提交:在mysql配置文件中的[musqld]下添加autocommit=1或0

1.事务处理命令控制事务
begin		开始事务	undo log insert into -->delete update -->update
commit		提交事务
rollback	回滚事务(撤销)