四、 MySQL客户端工具及SQL讲解

一.客户端命令介绍

mysql客户端命令

​ 1、用于数据库的连接管理

1） 连接（略）

2） 管理：

3）接收用户的SQL语句

#MySQL接口自带的命令
h 或 help 或？      查看帮助,查看mysql的管理命令
G                  格式化查看数据（结果以key：value形式展示）
T 或 tee            记录日志
c（5.7可以ctrl+c）   结束当前的sql语句
s 或 status         查看mysql状态信息
. 或 source         导入SQL数据
u或 use             切换、使用数据库
!或system			在数据库中使用系统命令
q 或 exit 或 quit   退出

​ 2、将用户的SQL语句发送到服务器

mysqladmin客户端管理命令

​ 1、命令行管理工具

mysqldump客户端备份命令

​ 1、备份数据库和表的内容

help命令的使用

mysql> help
mysql> help contents
mysql> help select
mysql> help create
mysql> help create user
mysql> help status
mysql> help show

source命令的使用

#在MySQL中处理输入文件：
#如果这些文件包含SQL语句则称为：
#1.脚本文件
#2.批处理文件
mysql> source /data/mysql/world.sql
#或者使用非交互式
mysql</data/mysql/world.sql

mysqladmin命令的使用

01）“强制回应 (Ping)”服务器。
02）关闭服务器。
03）创建和删除数据库。
04）显示服务器和版本信息。
05）显示或重置服务器状态变量。
06）设置口令。
07）重新刷新授权表。
08）刷新日志文件和高速缓存。
09）启动和停止复制。
10）显示客户机信息。

#查看MySQL存活状态
[root@db01 ~]# mysqladmin -uroot -p123 ping
#查看MySQL状态信息
[root@db01 ~]# mysqladmin -uroot -p123 status
#关闭MySQL进程
[root@db01 ~]# mysqladmin -uroot -p123 shutdown
#查看MySQL当前参数
[root@db01 ~]# mysqladmin -uroot -p123 variables
#库外删除数据库
[root@db01 ~]# mysqladmin -uroot -p123 drop aaa
#库外创建数据库
[root@db01 ~]# mysqladmin -uroot -p123 create aaa
#重载授权表
[root@db01 ~]# mysqladmin -uroot -p123 reload
#刷新binlog日志
[root@db01 ~]# mysqladmin -uroot -p123 flush-log
#刷新缓存主机
[root@db01 ~]# mysqladmin -uroot -p123 reload
#修改密码
[root@db01 ~]# mysqladmin -uroot -p123 password '1'

二.接收用户的SQL语句

1.什么是SQL

结构化的查询语句

2.SQL的种类

DDL：数据定义语言

1)库对象：库名字、库属性

开发规范：库名小写

创建库：create database|schema

Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_specification] ...

create_specification:
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name

#创建oldboy数据库
mysql> create database oldboy;
#创建OLDBOY数据库
mysql> create database OLDBOY;
#查看数据库
mysql> show databases;
#查看oldboy的创建语句（DQL）
mysql> show create database oldboy;
#查看创建数据库语句帮助
mysql> help create database
#创建oldboy数据库添加属性
mysql> create database testa charset utf8;
#避免库已存在 报错
mysql> create database if not exists zls;
#规范创建数据库
mysql> create database if not exists test1 default character set utf8 default collate
utf8_general_ci;
mysql> create database if not exists test1 charset utf8 collate utf8_general_ci;

删库：drop database

#删除oldboy数据库
mysql> drop database oldboy;

修改定义库：alter database

mysql> show create database zls1;
+----------+---------------------------------------------------------------+
| Database | Create Database 											   |
+----------+---------------------------------------------------------------+
| zls1     | CREATE DATABASE `zls1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
#修改字符集
mysql> alter database zls1 charset gbk;
Query OK, 1 row affected (0.00 sec)
mysql> show create database zls1;
+----------+--------------------------------------------------------------+
| Database | Create Database 											  |
+----------+--------------------------------------------------------------+
| zls1	   | CREATE DATABASE `zls1` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+--------------------------------------------------------------+

#修改校验规则
mysql> alter database test1 collate utf8_bin;
#修改oldboy数据库属性
mysql> alter database oldboy charset gbk;
#查看oldboy的创建语句（DQL）
mysql> show create database oldboy;

2)表对象:列名、列属性、约束

创建表：create（开发做）

#查看创建表语句帮助
mysql> help create table
#创建表
mysql> create table student(
sid int,
sname varchar(20),
sage tinyint,
sgender enum('m','f'),
cometime datetime);

数据类型
int： 整数 -2^31 ~ 2^31 -1
varchar：字符类型 （变长）
char： 字符类型 （定长）
tinyint： 整数 -128 ~ 128
enum： 枚举类型
datetime： 时间类型 年月日时分秒

int(3) zerofill：显示长度3位，前面显示0自动补全（001 002...）
tinyint unsigned：为整数，正数，0-255

#创建表加其他属性
mysql> create table student(
sid int primary key auto_increment comment '学号',
sname varchar(20) not null comment '学生姓名',
sage tinyint unsigned comment '学生年龄',
sgender enum('m','f')  not null default 'm' comment '学生性别',
cometime datetime not null comment '入学时间');
#查看建表语句
mysql> show create table student;
#查看表
mysql> show tables;
#查看表中列的定义信息
mysql> desc student;

数据属性
not null： 非空

---

primary key： 主键（唯一且非空的）

unique key： 单独的唯一的

pk = uk + not null

---

auto_increment： 自增（此列必须是：primary key或者unique key）

default： 默认值

unsigned： 非负数(无符号，和数字结合用)

comment： 注释

删除表 ：drop

#删除表
mysql> drop table student;

修改表定义：alter 开发做）

#修改表名
mysql> alter table tlbb rename student;
#增加字段
mysql> alter table stu add gsb varchar(10);
#将字段插入到最前面
mysql> alter table stu add youfeng int first;
#将字段插入到某个字段的后面
mysql> alter table stu add xmg int after ljk;
#删除某个字段
mysql> alter table stu drop ljk;
#修改字段的属性
mysql> alter table stu modify qls char(10);
#修改字段名和属性
mysql> alter table stu change qls haoda int;

#修改表名
mysql> alter table student rename stu;
#添加列和列定义
mysql> alter table stu add age int;
#添加多个列
mysql> alter table stu add test varchar(20),add qq int;
#指定位置进行添加列（表首）
mysql> alter table stu add classid varchar(20) first;
#指定位置进行添加列（指定列）
mysql> alter table stu add phone int after age;
#删除指定的列及定义
mysql> alter table stu drop qq;
#修改列及定义（列属性）
mysql> alter table stu modify sid varchar(20);
#修改列及定义（列名及属性）
mysql> alter table stu change phone telphone char(20);

DCL：数据控制语言

针对权限进行控制

grant

grant all on *.* to root@'%' identified by '1';
grant all privileges on *.* to pri2@'%' identified by '1';
grant all on *.* to root@'%' identified by '1' with max_user_connections 1;

#授权root@10.0.0.51用户所有权限（非超级管理员）
mysql> grant all on *.* to root@'10.0.0.51' identified by 'oldboy123';
#怎么去授权一个超级管理员呢？
mysql> grant all on *.* to root@'10.0.0.51' identified by 'oldboy123' with grant option;
#其他参数（扩展）
max_queries_per_hour：一个用户每小时可发出的查询数量
max_updates_per_hour：一个用户每小时可发出的更新数量
max_connetions_per_hour：一个用户每小时可连接到服务器的次数
max_user_connetions：允许同时连接数量

revoke

#收回select权限
mysql> revoke select on *.* from root@'10.0.0.51';
#查看权限
mysql> show grants for root@'10.0.0.51';

DML：数据操作语言

操作表的数据行信息

增 ：insert into

#注意：所有值必须一一对应，如果没有就给null
mysql> insert into student2 values(null,'qls',18,'m',now());

#注意：只需要给前面的key添加value，前面key值的顺序可以随意，后面value必须对应
mysql> insert into student2(sname,sage,sgender) values('zls',18,'m');
mysql> insert into student2(sage,sname,sgender) values(18,'zls','m');
#插入多条数据
mysql> insert into student2(sname,sage,sgender) values('zls',18,'m'),('qls',18,'f');

#基础用法，插入数据
mysql> insert into stu values('linux01',1,NOW(),'zhangsan',20,'m',NOW(),110,123456);
#规范用法，插入数据
mysql> insert into stu(classid,birth.sname,sage,sgender,comtime,telnum,qq) values('linux01',1,NOW(),'zhangsan',20,'m',NOW(),110,123456);
#插入多条数据
mysql> insert into stu(classid,birth.sname,sage,sgender,comtime,telnum,qq) values('linux01',1,NOW(),'zhangsan',20,'m',NOW(),110,123456),
('linux02',2,NOW(),'zhangsi',21,'f',NOW(),111,1234567);

改 ：update

#不规范
mysql> update student2 set sgender='f';
#规范用法 必须接where条件
mysql> update student2 set sgender='f' where sid=1;
#如果非要全表修改
mysql> update student2 set sage=20 where 1=1;

删 ：delete

#不规范
mysql> delete from student;
#规范删除（危险）
mysql> delete from student where sid=3;
mysql> delete from student where sid>3 and sid<9;
#DDL删除表（清空表内容）
mysql> truncate table student;

使用update代替delete做伪删除

1）额外添加一个状态列

mysql> alter table student add status enum('1','0') default 1;

2）使用update

mysql> update student set status='0' where sid=1;

3）应用查询存在的数据

mysql> select * from student where status=1;

DQL：数据查询语言

select：基础用法

#查询city表中的所有内容
mysql> select * from city;
#查询指定列的内容
mysql> select name,countrycode from city;
#指定条件查询
mysql> select * from city where name='afuhan';
#limit（翻页功能）
mysql> select * from city limit 10;
mysql> select * from city limit 10,10;
#多条件查询> 、< 、>=、<=、<>(!=)
mysql> select * from city where countrycode='chn' and population>999999;
#模糊查询
mysql> select * from city where countrycode like 'H%';
mysql> select * from city where countrycode like '%H';
mysql> select * from city where countrycode like '%H%';
#排序（顺序）
mysql> select id,name,population,countrycode from city order by population  limit 0,60;
#排序（倒叙）
mysql> select id,name,population,countrycode from city order by population  desc limit 0,60;

#group by + 聚合函数
#聚合函数种类：
#max()最大
#min()最小
#avg()平均
#sum()求和
#count()统计
#distinct()差异

#此时此刻，我想吟诗一首
1.遇到统计想函数
2.形容词前group by
3.函数中央是名词
4.列名select后添加

#统计世界上每个国家的总人口数
mysql> sselect countrycode,sum(population) 
from city group by countrycode;
#统计中国各个省的人口数量(练习)
不加别名：
mysql> select District,sum(population) 
from city 
where countrycode='CHN' 
group by District order by sum(population);
别名：
mysql> select District as 省,sum(population) as 人口 
from city 
where countrycode='CHN' 
group by 省 order by 人口;
#统每个国家的城市数量(练习)
mysql> select countrycode,count(name) 
from city 
group by countrycode order by count(name);
#统计中国的城市数量
mysql> select countrycode,count(name) 
from city 
where countrycode='chn' 
group by countrycode order by count(name);

#and（多条件同时满足）
mysql> select * from city 
where countrycode='CHN' and id>500;
#or（或者）
mysql> select * from city 
where countrycode='CHN' or countrycode='USA';
#in（或者）
mysql> select * from city 
where countrycode in ('CHN','USA');
####### 联合查询 效率比in和or高

三.字符集定义

1.什么是字符集（Charset）

字符集：是一个系统支持的所有抽象字符的集合。字符是各种文字和符号的总称，包括各国家文字、标点符号、图形符号、数字等。

2.MySQL数据库的字符集

1）字符集（CHARACTER）
2）校对规则（COLLATION）

3.MySQL中常见的字符集

1）UTF8
2）LATIN1
3）GBK

4.常见校对规则

1）ci：大小写不敏感
2）cs或bin：大小写敏感

5.我们可以使用以下命令查看

mysql> show charset;
mysql> show collation;

四.字符集设置

1.操作系统级别

[root@db01 ~]# source /etc/sysconfig/i18n
[root@db01 ~]# echo $LANG
zh_CN.UTF-8

#C6:
vim /etc/sysconfig/i18n
LANG="en US.UTF-8 "

#C7:
[root@db01 ~]# vim /etc/locale.conf 
LANG="en_US.UTF-8"

2.操作系统客户端级别（SSH）

3.MySQL实例级别

方法1：在编译安装时候就指定如下服务器端字符集。

cmake . 
-DDEFAULT_CHARSET=utf8 
-DDEFAULT_COLLATION=utf8_general_ci 
-DWITH_EXTRA_CHARSETS=all 

方法2：在配置文件中设置字符集

#永久
#修改配置文件/etc/my.cnf
[mysqld]
character-set-server=utf8

#临时
mysql> set character_set_server=utf8;

4.建库级别

mysql> create database oldboy charset utf8 default collate=utf8_general_ci;

#建库
mysql> create database db_name charset utf8 collate utf8_xxx_ci;

5.建表级别

mysql>  create table test (
id int(4) not null auto_increment,
name char(20) not null,
primary key (id)
) engine=InnoDB auto_increment=13 default charset=utf8;

#建表
mysql> create table tb_name(id int) charset utf8 collate utf8_xxx_ci;

思考问题：如果在生产环境中，字符集不够用或者字符集不合适该怎么处理？

gbk 500-60000

utf8 1-90000

gb2312 2-5000

修改数据库的字符集

mysql> alter database zls charset utf8;

修改表的字符集

mysql> alter table zls charset gbk;

企业中修改某个库中的所有表字符集：

# mysqldump -uroot -p123 -B xx > /tmp/xx.sql
# vim /tmp/xx.sql
# :%s#gbk#utf8#g
# mysql -uroot -p123 < /tmp/xx.sql

update t_char set moneyyb=9999999 where aid=150;

生产环境更改数据库（含数据）字符集的方法

mysql> alter database oldboy character set utf8 collate utf8_general_ci;
mysql> alter table t1 character set utf8;

五.select的高级用法（扩展）

多表连接查询（连表查询）

集合：
[zhang3,li4,wang5]

[50,70,80]

t1:
sid 1 2 3
sname zhang3 li4 wang5

t2:
sid 1 2 3
mark 50 70 80

范式： 减少数据冗余，防止产生一致性问题，把一个表作为一个原子，把一张表拆到不能再拆为止。（开发阶段设计规范）

例：根据两张表的内容查出张三的成绩

select t1.sname,t2.mark from t1,t2 where t1.sid=t2.sid and t1.sname=’zhang3’;

1.传统连接（只能内连接，只能取交集）

#查世界上人口数量小于100的城市在哪个国家，城市和国家人口数量分别是多少？
城市名   		国家名   		城市人口数量   		国家人口数量
city.name  country.name		city.population		country.population

select city.name,country.name,city.population,country.population 
from city,country 
where city.countrycode=country.code 
and city.population<100;

#世界上人口数量小于100的城市在哪个国家，说的什么语言？
select city.population,city.name,country.name,countrylanguage.language
from city,country,countrylanguage
where city.countrycode=country.code 
and countrylanguage.countrycode=country.code
and city.population < 100;

2 .natural　join（自连接的表要有共同的列名字）

# 人口数量大于1000000的城市所在的国家，他们都说什么语言？ (自连接)
select city.population,city.name,city.countrycode,countrylanguage.language
from city natural join countrylanguage
where city.population > 1000000
order by population;

#前提条件：一定要有相同的列名字，并且列中的数据一致

3.企业中多表连接查询 join on（内连接）

#查世界上人口数量小于100的城市在哪个国家，城市和国家人口数量分别是多少？
select city.name,city.population,country.name,country.population
from city join country
on city.countrycode=country.code
where city.population<100;

#世界上人口数量小于100的城市在哪个国家，说的什么语言？
·A join B on 1 join C on 2 join D on 3·

select city.population,city.name,country.name,countrylanguage.language
from city join country 
on city.countrycode=country.code
join countrylanguage
on countrylanguage.countrycode=country.code
where city.population < 100;

建议：使用join语句时，小表在前，大表在后。

4.外连接（左外连接，右外连接）

#左外连接
mysql> select city.name as 城市名称,country.code as 国家代码,country.name as 国家名称  
from city left join country  
on city.countrycodde=country.code  
and  city.population<100 limit 10;

#右外连接
mysql> select city.name as 城市名称,country.code as 国家代码,country.name as 国家名称  
from city right join country  
on city.countrycodde=country.code  
and city.population<100 limit 10;

5. UNION（合并查询）

####### 联合查询 效率比in和or高
#范围查询OR语句
mysql> select * from city where countrycode='CHN' or countrycode='USA';
#范围查询IN语句
mysql> select * from city where countrycode in ('CHN','USA');
#替换为：
mysql> select * from city where countrycode='CHN' 
union  all
select * from city where countrycode='USA';

union：去重复合并
union all ：不去重复
使用情况：union<union all