MySQL中district,mysql SQL的应用

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