SQL应用
1.client
help:mysql的内置命令
? 打印帮助
\c 结束上一条命令的运行
\G 让每行的数据以列的形式显示 ，针对列很多的数据
exit 退出
/q 退出
ctl d 退出
source 导入sql脚本，类似于 <
例子 ：source /root/a.sql
system:可以调用linux的命令
2.server
Linux中一切皆命令，Linux中一切皆文件。
2.1DDL 数据定义语言
(1)库定义 ：
创建数据库：
create database oldguo charset utf8mb4;
规范：1.库名，小写，业务有关，不要数字开头，不要太长，不能用保留关键字
查看数据库：show databases;
查看建库语句:show create database 库名;
修改数据库：alter database 库名 charset utf8mb4;一般修改字符集是往大了改。不然不兼容.
删除数据库：不要操作。drop database 库名.
(2)表定义 ：
创建表
create table user ( id int not null auto_increment comment '用户序号', name varchar(64) not null comment '用户名', age tinyint unsigned not null default 18 comment '年龄', gender char(1) not null default 'F' comment '性别', cometime datetime not null comment '注册时间', shengfen enum('成都','南充','广安','小香港','重庆') default '成都' not null comment '省份', primary key (id)) engine=innodb charset=utf8mb4;
建表规范:
1.表名:
小写字母 原因：windows操作系统不区分大小写
不能数字开头，
表名和业务有关，
不能使用关键字，
名字不要太长，不超过15个字符.
2.必须设置存储引擎和字符集
3.数据类型：合适，简短，足够
4.必须要有主键
5.每个列尽量设置not null,不知道怎么填，设定默认值
6.每个列要有注释
7.列名也不要太长
查询表
show tables;
desc user;
show create table user;
修改表
例子：
添加字段:alter table user add column phone_no bigint not null unique key comment '手机号';
修改列属性:alter table user modify phone_no char(11) not null unique key comment '手机号';
删除列:alter table user drop phone_no;
删除表:drop table user;
清空表:truncate table user ;
2.2DCL 数据库控制语言
grant 赋权
revoke 回收权限
2.3DML 数据库操作语言
insert
insert into user (字段1,字段2,...) values (值1，值2,...);
插入多行:
insert into user (字段1,字段2,...) values (值1，值2,...),(值1,值2,....);
update
update user set name='乔碧萝' where id=3;
delete
delete from user where name ='苍井空';
扩展:
1.伪删除
--修改表结构，添加一个state状态列
alter table user add column state tinyint not null default 1 comment '状态';
update user set state = 0 where name ='张飞';
select * from user where state = 1 ;
2.区别
delete dml语言，逻辑上，逐行删除，数据多，操作慢，
并没有真正删除，只是在存储层面打标记，磁盘空间不会立即释放，HMW高水位线不会降低，
会产生大量的碎片。
drop ddl语言,将表结构(元数据)和数据行物理层次删除。
truncate 物理层次删除表中所有数据，磁盘空间立即释放，HMW高水位线立即降低.
2.4DQL 数据库查询语言
select :实际属于dml语言，这里抽离出来。体现重要性
功能:获取表中的数据行
1)select 单独使用(mysql独家)
select 配合函数使用。
\mysql> select now() ;
| now() |
| 2020-03-17 21:30:08 |
mysql> select concat('good');
| concat('good') |
| good |
select concat(user,'@',host) from mysql.user;
2)计算
select 100/3 ;
3)查询数据库的参数
select @@sql_mode;select @@datadir;select @@socket;
4)select 标准用法 （配合其他子句使用）
--单表
前提默认执行顺序
select
from 表1，表2
where 条件1，条件2
group by 条1，条2
select_list 列名列表
having 过滤条件1，过滤条件2
order by 条件列1 ，条件列2
limit 限制
mysql示例数据库地址：
shttp://dev.mysql.com/doc/index-other.html
导入示例库 :
mysql- uluobiao -p < wolrd.sql
模糊查询: like like只适用于 字符串
select * from city where `CountryCode` like 'CHN%';
下面这种查询方式不会走索引，需要注意:
select * from city where `CountryCode` like '%CHN%'
where 配合逻辑连接符 and or
bwetween and 类似<= and >=
group by 配合聚合函数使用
max() ：最大值
min() 最小值
avg() 平均值
count(0) 统计个数
sum() 求和
group_concat() 列转行
求出中国省份的人数，和城市数量，以及城市列表
select countrycode,District,count(0),sum(Population) ,group_concat(name,'@') from city
where countrycode='CHN'
group by District
扩展：delete drop truncate 操作，怎么恢复数据
1.通过备份+日志，恢复数据。三种。
2.delete 可以通过翻转日志（binlog恢复），只针对delete
3.也可以通过 延时从库进行恢复，三种
having 只显示总人口数500万的省
select countrycode,District,sum(Population) from city
where countrycode='CHN'
group by District
having sum(Population) >= 5000000 ;
order by 只显示总人口数500万的省,且降序排序
select countrycode,District,sum(Population) from city
where countrycode='CHN'
group by District
having sum(Population) >= 5000000
order by sum(Population) desc ;
order by 只显示总人口数500万的省,且降序排序,只看前5名
select countrycode,District,sum(Population) from city
where countrycode='CHN'
group by District
having sum(Population) >= 5000000
order by sum(Population) desc
limit 5 ;
limit 3 offset 2 等价于 limit 2,3 ;
意思是显示3行，跳过2行。
---多表连接
1.内连接 select t.* ,c.* from teacher t , course c where t.`tno` = c.`tno`
select t.* ,c.* from teacher t inner join course c on t.`tno` = c.`tno` (标准写法);
2.外连接:
left join on right join on 实际上是重新生成一张表 在做where group by 等操作
例子：张三学习了那些课程
select t.`sno`,t.sname,group_concat(c.cname) from student t
join sc s
on t.`sno` = s.sno and t.sname='zhang3'
join course c on s.`cno` = c.`cno`
group by t.`sno`,t.sname;
驱动表的概念:
next_loop 循环
内连接的驱动表是由优化器决定。
外连接，是连接的那一边的表作为驱动表(左连接是左表，右连接是右表)。驱动表是作为外层循环去连接内层循环。
所以建议：使用小表作为驱动表.降低next_loop的次数.
列别名: as ‘别名’ 或者 '别名':
作用：1.方便查看 2.可以在having order by limit 以及group by 后面的子句使用别名。
表别名:全局使用
distinct(字段名) 去重复字段函数.
例子：select distinct(countrycode) from city ;
union 与union all的区别
union 会去除重复的数据而union all 不会。
show 语句
show databases; 查询所有的库
show tables;查询use到库下的所有的表
show tables from 库名;查询某个库下的表
show processlist; 查询所有的用户连接。
show full processlist; 会显示所有详细信息.
show charset; 查看字符集
show collation;查看校对规则
show engines; 查看支持的引擎信息
show privileges; 查看支持的权限信息
show grants for 查看某用户的权限
show create database 库名 查看建库语句
show create table 查看建表语句
show index from 索引名称 查看表的索引信息
show engine innodb status 查询innodb引擎状态
show status 查看数据库状态信息
show status like '%状态名%' 模糊查询数据库的状态
show variables 查看数据库的参数
show variables like '%%' 模糊查询部分参数
show binary logs 查询所有二进制日志文件信息
show binlog events in 查询二进制日志事件
show master status 查询二进制日志的位置点信息
show slave status 查询从库的状态信息
show relaylog events in 查询中继日志事件
show variables like '%mode' \G
元数据 use information_schema 库
每次数据库启动，会自动在内存中生成i_s,生成查询mysql部分元素据信息视图
i_s中的视图就是保存的元信息
is.tables
作用保存了所有表的数据字典信息
字段信息：
table_schema 表所在的库
table_name 表名
engine 表引擎
table_rows 表的数据行（不是实时更新）
avg_row_length 平均行长度
data_length 表的使用存储空间大小（不是实时更新）
index_length 表中索引占用空间的大小
data_free 表中是否有碎片
i_s.tables 企业应用案例
--例子1:数据资产统计--数据库资产统计-统计每个库，所有的表个数和表名
select table_schema,count(0),group_concat(table_name) from tables group by table_schema;
--例子2:统计所有的库占用的存储空间大小 (默认单位bytes)
方法一.select table_schema,sum(data_length + index_length) from tables group by table_schema;
方法二(推荐).select table_schema,sum(table_rows * avg_row_length + index_length) from tables group by table_schema;
例子3：查询业务库中，非innodb表的
select table_schema,table_name from tables where engine != 'InnoDB' and table_schema not in
('sys','mysql','information_schema','performance_schema')
例子4，将所有非innodb的业务表转换为innodbi 表
select concat('alter table ',table_schema,'.',table_name,' engine = innodb;') from tables where engine != 'InnoDB' and table_schema not in
('sys','mysql','information_schema','performance_schema') into outfile '/tmp/test.sql';
将上面的sql输出到本地
1.需要将输出的目录作为安全目录保存到mysql配置文件,
vi /etc/my.cnf 写入服务器端 : secure-file-priv=/tmp ，并重启数据库
执行导入命令: mysql -uroot -p </tmp/test.sql;
