MySQL02-操作mysql
库的相关操作
\c # 废弃当前编写错的操作指令
show global variables like '%password%'; # 搜索全局变量中包含password字样的
show databases; # 显示已有的库
use 库名; # 切换库
select database(); # 显示当前所在库名
create database 库名; # 创建库
show tables; # 显示当前库中的表
drop database 库名; # 删除库
数据库命名规则 不可纯数字 区分大小写
表管理命令
desc 表名; # 查看表结构
desc 表名\G; # 纵向查看表结构
select * from 表名; # 查看全部表内容
drop table 表名; # 删除表
插入数据
// test表有id,name,age三个字段
insert into test values(1, 'bob', 20); # 插入单条数据，包含所有字段
insert into test(id, age) values(1, 30); # 插入单条数据，指定字段
insert into test values(1, 'bob', 10), (2, 'tom', 20); # 插入多条数据，包含所有字段
insert into test(id, age) values(1, 10), (2, 20); # 插入多条数据，指定字段
删除数据
仅删除符合条件的记录
delete from test where id = 1;
删除所有的表记录 delete from 表名；
delete from test;
根据表中某一字段，删除表中重复数据
delete t1 from product_info t1 INNER JOIN product_info t2 on t1.serial_number = t2.serial_number and t1.id > t2.id;
修改数据
update test set age=10;
update test set age=10 where id=1;
查找数据
对于字符类型的字段，内容要用双或单引号括起来
select * from mydatabase.test where id=1;
模糊查询
// 匹配3600-1 3600-9
select * from platform where name like '%3600-_%';
// _ 匹配单个字符
// % 匹配任意字符
查询结果排序
order by desc //降序
order by asc //升序
// 查询结果分组
group by xx
// 查询output表中各产品的数量
select product_id,count(product_id) as shuliang from output group by product_id order by product_id;
查询结果过滤
在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。
这里可以把having理解为where
// 具体查询数量>3000的数据
select product_id,count(product_id) as shuliang from output group by product_id having count(product_id) > 3000 order by product_id;
select id,name from platform where name in (select name from platform where name like '%7%' and id > 440 group by name having count(name) > 1) order by name
限制查询结果显示行数
select * from product limit 10; // 显示前10行
select * from product limit 10, 20; // 从第11行显示，共显示20行
指定查询偏移量
select * from product limit 20 offset 10; // 从第11行显示，共显示20行
修改表结构
语法结构
alter table 表名 执行动作 字段名 类型(宽度) 约束条件 after/first 字段名
执行动作：
add 增加
modify 更改字段类型
change 更改字段名称
rename 更改表名
drop 删除
添加新字段
alter table 表名 add 字段名 类型(宽度) 约束条件 after 或 first 指定添加字段的位置
删除age字段
alter table mytable drop age;
更改字段类型
alter table mytable modify 字段名 类型(宽度) 约束条件
同时修改多个字段
alter table mytable modify 字段1 类型(宽度) 约束条件,modify 字段2 类型(宽度) 约束条件;
更改字段名（可以同时修改字段的类型）
alter table 表名 change 原字段名 新字段名 类型(宽度) 约束条件
更改表名
alter table mytable rename newtable;
显示建表时的命令
show create table mytable;
查询及匹配条件
数值比较
=、>、>=、<、<=、!=
字符比较/空/非空
=、!=、is null、is not null
逻辑匹配
or、and、!、()
范围内匹配
in()、not in()、between aa and bb、distinct cc去重
正则表达式
// 匹配以大写D开头
select * from product where name regexp '^D'
//正则元字符 ^ $ . [] * |
四则运算
+ - * / %
聚集函数
avg() sum() min() max() count()
键值类型
普通索引 index
为了加快大表或复杂表的查询速度，盲目的使用索引会导致sql性能下降
特点
(1)一个表中可有多个INDEX字段
(2)字段的值允许有重复，且可以赋null值
(3)经常把做查询条件的字段作为index字段
(4)Index字段的key标志是MUL
//在已有表中设置index字段
create index 索引名 on 表名(字段名)
//删除指定表的索引字段
drop index 索引名 on 表名
//查看表的索引信息
show index from 表名
//建表时创建索引
mysql> CREATE TABLE mytable(
-> id char(6) NOT NULL,
-> name varchar(6) NOT NULL,
-> age int(3) NOT NULL,
-> gender ENUM('boy','girl') DEFAULT 'boy',
-> INDEX(id),INDEX(name)
-> );
唯一索引 unique
1.定义了unique的字段中不能包含重复值
2.可以为一个或多个字段定义unique
3.设置unique索引的字段允许为空
4.unique字段的KEY标志为UNI
用法同index
//将id、name字段建立设置UNIQUE索引，age字段设置INDEX索引：
create table mytable(
id int(10),
name varchar(10) not null,
age int(10),
unique(id), unique(name),
index(age));
primary key = unique + not null
查看表结构，可发现设置了unique字段的Key标志为UNI
由于字段name必须满足 not null 的非空约束，所以将其设置为unique后会自动变成了primary key主
键字段：
mysql> DESC tea5;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | char(6) | YES | UNI | NULL | |
| name | varchar(4) | NO | PRI | NULL | |
| age | int(3) | NO | MUL | NULL | |
+-------+------------+------+-----+---------+-------+
主键 primary key
作用：是能确定一条记录的唯一标识
1. 一个表中只能有一个primary key字段
2. 对应的字段值不允许有重复，且不允许赋null值
3. 如果有多个字段作为primary key，称为复合主键，必须一起创建
4. 主键字段的KEY标志是PRI
5. 通常与AUTO_INCREMENT连用
6. 经常把表中能够唯一标识记录字段设置为主键字段
建表时，可直接在某字段的“约束条件”部分指定PRIMARY KEY
也可在最后指定PRIMARY KEY(某个字段名)
// 直接在字段定义时约束
CREATE TABLE biao01(
id int(4) PRIMARY KEY,
name varchar(8));
// 在sql语句最后指定主键
CREATE TABLE biao01(
id int(4),
name varchar(8)，
PRIMARY KEY(id));
//在建表的时候，如果主键字段为int类型，还可以为其设置AUTO_INCREMENT自增属性
CREATE TABLE mytable(
id int(4) AUTO_INCREMENT,
name varchar(4) NOT NULL,
age int(2) NOT NULL,
PRIMARY KEY(id));
//在已有表中设置主键
alter table 表名 add primary key(字段名)
//删除现有表的主键
alter table 表名 drop primary key
//设置复合主键
alter table 表名 add primary key(字段1，字段2)
//若删除的主键字段具有AUTO_INCREMNET自增属性，必须先清除自增属性
alter table 表名 modify 有自增属性的字段 类型(宽度) 约束条件 # 删除自增属性
alter table 表名 drop primary key # 删除主键
外键 foreign key
定义：让当前表字段的值在另一个表中字段值的范围内选择,
作用：用于与另一张表的关联，是能确定另一张表记录的字段，用于保持信息的一致性
使用外键的条件
(1)表的存储引擎必须是innodb
(2)字段类型要一致
(3)被参照字段必须要是索引类型的一种（primary key）
//将mytable表的id字段设置为外键，参考newtable表的id字段
alter table mytable add foreign key(mytable_id) references newtable(newtable_id) on update cascade on delete cascade;
on update cascade 同时更新
on delete cascade 同时删除
//创建test1和test2表，2表的id字段设置外键关联1表的id字段
CREATE TABLE test1 (
id int(10) not null,
name char(25) not null
age int(10) not null);
CREATE TABLE test2 (
id int(10) not null,
name char(25) not null
age int(10) not null
foreign key (id) references test1(id) on update cascade on delete cascade);
//当向test2表中插入id为3的数据时，不可插入，因为test1表中只有id为1和2的字段
insert into test1 values (1, 'tom', 10);
insert into test2 values (1, 'bob', 20), (3, 'jerry', 30);
//修改test1表时，test2表的相应字段也会更改
update test1 set name = 'harry' where id = 1; # test2表中id为1的数据的name字段也会被修改
delete from test1 where id = 1; # test2表中id为1的数据的name字段也会被删除
//删除外键
alter table 表名 drop foreign key 约束名
索引
聚集索引（Clustered Index）
聚集索引是数据库表中的一种特殊的索引类型，它决定了表中数据的物理存储顺序。在InnoDB存储引擎中，表总是按照主键的顺序进行存储，因此InnoDB表总是有一个聚集索引，即使你没有显式地指定。
二级索引（辅助索引/非聚集索引）
二级索引的叶子节点存储的数据是主键的值，而不是整行数据。这意味着，通过二级索引，可以定位到主键的位置。
作用：通过二级索引，可以快速定位到主键的值，然后再通过主键值去获取整行数据。这通常比直接扫描全表要快。
回表
在InnoDB存储引擎下，当使用二级索引查询数据时，如果查询需要的数据不仅仅在二级索引中（即需要查询所有列的数据），MySQL会首先通过二级索引找到对应的主键值，然后再根据这个主键值到主键索引（也称为聚集索引）中去查找完整的行记录。这个过程就被称为“回表”。
explin 的 Extra字段
using index condition # 表示查询使用了索引，但是需要回表查询
using where;using index # 表示查询使用了索引，数据在索引列中都有，不需要回表查询
前缀索引
当字段为varchar或text时，索引字符串可能会很长，查询时会浪费IO，影响效率，可以截取前缀形成索引
create index idx_xxx on table_name(column(n)) # 截取前n个字符作为索引
如何确定前缀长度？
表中不重复的索引值与表中记录总数的比值，越接近1性能越高，1的性能最高
select count(distinct col_name)/count(*) from test_table
截取多次不同长度来决定前缀长度
select count(distinct substring(col_name,1,10)/count(*) from test_table
复制表
1.不能复制主键、外键
2.原表中的数据也将被复制
# 复制一张表的全部字段
create table newtest select * from test;
# 复制一张表的部分字段
create table neetable select id,name,age from test;
查询
where子查询
# 查询表中年龄最小的人的全部信息
select * from test where test.age = (select min(age) from test);
左连接查询 left join
select * from test1 left join test2 on test1.name = test2.name;
右连接查询 right join
select * from test1 right join test2 on test1.name = test2.name;
inner join 也可写作 join
select * from test1 join test2 on test1.name = test2.name;
用户授权
grant 权限列表 on 库名.表名 to 用户名@'客户端地址' identified by '密码';
grant all privileges on *.* to root@'localhost' identified by '123456' with grant option;
权限列表
all 所有权限
usage 没有权限
select,update,insert 查找,更新,插入的权限
select,update(字段1,字段2) 查找,更新字段1,字段2的权限
客户端地址(单引号不可省略)
% 匹配所有主机
192.168.1.% 匹配指定的一个网段
192.168.1.1 匹配指定IP地址的单个主机
%.example.com 匹配一个DNS区域
test.example.com 匹配制定域名的单个主机
注意：写域名的前提是数据库服务器可以解析域名和主机名
查看用户的权限
# 查看root用户本地登陆的权限
show grants for root@'localhost';
**有关授权的表**
mysql.user 存储用户的访问权限
mysql.db 存储授权用户对数据库的访问权限
mysql.tables_priv 存储授权用户对表的访问权限
mysql.columns_priv 存储授权用户对字段的访问权限
撤销权限
# 删除tom的select，insert权限
revoke select,insert on mytestdb.test from tom@'localhost';
# 删除tom用户和所有权限
drop user tom@'localhost';
# 删除用户
delete from mysql.user where user='用户名';
身份认证插件
SELECT Host, User, plugin from user;
+-----------+------------------+-----------------------+
| Host | User | plugin |
+-----------+------------------+-----------------------+
| % | root | mysql_native_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
| localhost | root | mysql_native_password |
+------------------------------------------------------+
修改密码
alter user 'root'@'%' identified by 'newpasswd'
flush priveleges;
#在命令行下设置mysql密码
mysqladmin -u root -p password '新密码' # 会要求输入旧密码
修改密码策略
# 临时修改密码策略
set global validate_password_policy=0; //只验证长度（low:0 medium:1 strong:2）
set global validate_password_length=6； //修改密码长度为6（默认8位）
# 密码策略：要求包含数字 字母 大小写 字符 字符长度
# 永久修改密码策略
vim /etc/my.cnf
[mysqld]
validate_password_policy=0
validate_password_length=10
查看密码
# 查看当前密码
select host,user,authentication_string from mysql.user where user='root';
# 查看随机生成的root管理密码
grep password /var/log/mysqld.log
查看mysql的当前设置
显示当前登陆的客户端地址和用户名
select user();
显示当前登陆用户的访问权限
show grants;
管理员及具有授权的用户修改其他授权用户连接密码
set password for 授权用户@'授权IP'=password（'新密码');
查看提交状态
show variables like 'autocommit';
set autocommit=off; # 关闭自动提交
rollback; # 数据回滚
commit; # 提交数据
命令行操作sql语句
mysql -u root -p -e "SHOW VARIABLES LIKE 'default_storage_engine';"
数据导入/导出路径设置
导入数据时检索文件的默认目录：/var/lib/mysql-files
查看默认使用目录
show variables like 'secure_file_priv'
修改默认使用目录(此路径必须存在,并且属主属组为mysql)
vim /etc/my.cnf
secure_file_priv='具体路径'
// 不能这样改
set secure_file_priv = '/tmp/mysql-files';
Variable 'secure_file_priv' is a read only variable
数据导入/导出
将系统中的数据导入数据库
注意：
1.指定导入文件的绝对路径
2.字段分隔符要与文件内容的一致（不能无中生有）
3.导入数据的表字段类型要与文件字段匹配
4.禁用selinux
// 在mysql中执行系统命令,将要导入的表复制到开始路径
mysql>system cp /etc/passwd /var/lib/mysql-files/
// 创建接收数据的表
create table passwd(
name char(20),
uid char(10),
git char(10),
comment char(100),
homedir char(100),
shell char(50));
// 将/var/lib/mysql-files/passwd中的内容导入userlist表
load data infile '/var/lib/mysql-files/passwd' \
into table userlist \
fields terminated by ':' \ # 指定默认分隔符
lines terminated by '\n'; # 指定默认换行符(默认\n，所以这行可以省略)
从MySQL数据库中导出查询结果
注意：
1.导出内容由sql查询语句决定
2.导出的是表中的记录，不包括字段名
3.导出的表会自动创建
4.禁用selinux
select * from passwd into outfile '/tmp/mysql-passwd' \
fields terminated by '#' \ # 每行的每个字段以#为间隔
lines terminated by '\n';
