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 回滚事务(撤销)
QQ：1061767621
Q群：215481318
