第四章·DBA-MySQL客户端工具及SQL讲解

客户端命令介绍

mysql(用于数据库的连接管理)

# 查看命令帮助
? \? help \h

# 查看状态
status \s

# 退出
exit quit \q

# 结束当前的SQL语句
\c
Ctrl + c : 5.6中退出MySQL 5.7中结束当前SQL语句

# 在MySQL中执行系统命令
system \!

# 临时将操作记录到指定文件中
tee \T

# 切换数据库
user \u

# 导入数据
source \.

# 格式化(key:value)方式,显示数据
\G

# 客户端配置,显示当前所在数据库及登录用户
[client]
prompt="\u@\h:\d>"

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 DATABASE
#创建数据库
[root@db01 ~]# mysqladmin -uroot -p123 create DATABASE
#重载授权表
[root@db01 ~]# mysqladmin -uroot -p123 reload
#刷新日志
[root@db01 ~]# mysqladmin -uroot -p123 flush-log
#刷新缓存主机
[root@db01 ~]# mysqladmin -uroot -p123 reload
#修改口令
[root@db01 ~]# mysqladmin -uroot -p123 password

mysqldump(备份数据库和表的内容)

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

接收用户的SQL语句

什么是SQL语句

结构化的查询语句

SQL的种类

DDL：数据定义语言

库对象：库名字、库属性
开发规范：库名小写

Database Definition Language
数据      定义      语言

# 开发规范：
(01) 表名不能大写,数字开头,16个字符串
(02) 表名和业务有关
(03) drop 语句禁止
(04) 选择合适的数据类型
(05) 必须要有主键
(06) 列尽量非空约束
(07) 减少外键约束
(08) 必须设置存储引擎和字符集
(09) 列必须要有注释
(10) 对于非负数设置数据类型约束--无符号

创建库：create database|schema

语法：
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_option] ...

create_option: [DEFAULT] {
    CHARACTER SET [=] charset_name
  | COLLATE [=] collation_name
}

#创建rowey数据库
mysql> create database rowey;
mysql> create schema rowey;
#创建ROWEY数据库
mysql> create database ROWEY;
# 如果库存在则不创建
create database if not exists rowey charset 
#查看数据库
mysql> show databases;
#查看oldboy的创建语句（DQL）
mysql> show create database rowey;
#查看创建数据库语句帮助
mysql> help create database
#创建oldboy数据库添加属性
mysql> create database testa charset utf8;

删库：drop database

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

修改定义库：alter database

#修改rowey数据库属性
mysql> alter database rowey charset gbk;
#查看rowey的创建语句（DQL）
mysql> show create database rowey;

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

创建表：create table （开发做）

#查看创建表语句帮助
mysql> help create table
#创建表
mysql> create table student(
sid INT,                #学生id,整型
sname VARCHAR(20),       #学生姓名,字符串
sage TINYINT,            #学生年龄,整数
sgender ENUM('m','f'),    #学生性别,枚举男女
cometime DATETIME);       #入学时间,时间类型

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

#创建表加其他属性
mysql> create table student(
sid INT NOT NULL 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 ‘入学时间’)chatset utf8 engine innodb;
#查看建表语句
mysql> show create table student;
#查看表
mysql> show tables;
#查看表中列的定义信息
mysql> desc student;

数据类型
not null： 非空
primary key： 主键（唯一且非空的）
auto_increment： 自增（此列必须是：primary key或者unique key）
unique key： 单独的唯一的
default： 默认值
unsigned： 非负数
comment： 注释

删除表

#删除表
mysql> drop table student;

# 删用户
drop user 'lw@%'

修改表定义：alter table (开发做)

# 修改表名:
alter table 表名 rename 新表名;
alter table student rename stu;

# 添加字段(将字段添加在表的最后一列)
alter table stu add stu5 varchar(11) not null;
alter table stu add stu6 char(11) default 'sb'; ## 带默认值添加字段

# 添加多个字段
alter table stu add stu7 varchar(11) not null,add stu8 char(11)

# 将字段添加在最前面一列
alter table stu add stu3 char(11) first;

# 按指定位置添加字段
alter table stu add stu4 char(11) after stu3;

# 删除字段
alter table stu drop stu3

# 修改数据类型和属性
alter table stu modify gender enum('f','m')default 'f';

# 修改字段名 数据类型 属性
alter table stu change stu5 stu55 varchar(10)

DML：数据操作语言

操作表的数据行信息

insert

Data Manipulation Language
数据 操作         语言
所有的DML都是操作表内容的

# 插入单条数据
insert into stu(name,gender,age,date,phone,bir,id) value('stu1','f',255,'2022-08-
10','133',NOW(),1);

# 插入多条数据
insert into stu(name,gender,age,date,phone,bir,id)
value('stu2','f',255,'2022-08-10','136',NOW(),4),
('stu3','m',38,'2022-09-10','137',NOW(),5);

# 默认字段不加
insert into stu(name,bir,phone) value('stu3',NOW(),'138');

# 不规范写法
insert stu value(7,'stu4',18,'m',NOW(),'139',NOW());

delete

使用delete一定要加条件
delete from stu;(不要这么删)

# 加条件
delete from stu where name='stu1';

# 想全部删除也要加
delete from stu where 10=10; #循环,10肯定是等于10
delete from stu where 1=1;

注意: 一般在删除数据时, 我们会根据唯一性的字段进行删除
delete from mysql.user where user='lw' and host='%'

update

使用update一定要加条件
update student2 set id=3 where name='stu2';

#如果非要全表修改
mysql> update student set sgender='f' where 1=1;

# 使用update替代delete删除数据
1.给表中，加一个状态列
alter table student2 add status enum('0','1') default '1';

2.使用update删除数据
update student2 set status='0' where name='lw';

3.查询的时候，使用where条件查询
select * from student2 where status='1';

注意：update修改数据库用户的密码，是要flush privileges;修改其他数据，不需要。

DCL：数据控制语言

针对权限进行控制

grant

Database Control Language
数据     控制     语言

### 赋予权限
5.6和5.7区别：5.7老版本，grant赋予权限，如果该用户不存在，则无法创建，5.6和5.7新版本可以直接创建用户

# 授权其他用户
grant all on *.* to test@'%' identified by '123';
grant 权限1,权限 on 库.表 to 用户@'主机域' identified by '密码';

# 授权超级管理员
grant all on *.* to test@'%' identified by '123' with grant option;

#其他参数（扩展）
max_queries_per_hour：一个用户每小时可发出的查询数量
max_updates_per_hour：一个用户每小时可发出的更新数量
max_connetions_per_hour：一个用户每小时可连接到服务器的次数
max_user_connetions：允许同时连接数量

revoke

#收回select权限
revoke 权限 on 库.表 from 用户@'主机域';
mysql> revoke select on *.* from root@'10.0.0.51';

#查看权限
mysql> show grants for root@'10.0.0.51';

DQL: 数据查询语言

show

Database Query Language
数据     查询   语言

# 查看数据库
show databases;
# 查看表
show tables;
# 查看指定数据库下的表,不用切换数据库
show tables from lw;
# 查看建库语句
show create database 库名;
# 查看建表语句,也是为了查看注释
show create table 表名;
# 查看建表语句不用切换库,也是为了查看注释
show create table 库名.表名;
# 查看用户授权语句,也是为了查看该用户的权限
show grants for 用户名@'主机域';
# 查看所有的内置变量(默认配置)
show variables;
# 模糊查询(过滤)
show variables like '%server'
show variables like '%server%'
show variables like 'server%'
# 查看后台执行的sql语句
show processlist;
# 查看完整后台执行的sql语句
show full processlist;
# 查看所有校验规则
show collation;
# 查看所有字符集以及该字符集的默认校验规则
show charset;

desc

# 查看表结构
desc 库.表 

explain

# 查看执行计算 (索引)

select

示例库world

root@localhost:(none)> source /root/world.sql

root@localhost:(none)> show databases;
+--------------------+
| Database |
+--------------------+
| world |
+--------------------+
root@localhost:(none)> use world
root@localhost:world> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
+-----------------+

# select 常用简单查询
## 查看该表中所有的字段的记录（所有数据）
root@localhost:world> select * from city;
## 查看指定字段的所有数据
root@localhost:world> select id,name,countrycode from city; 

# select 行级查询（翻页功能）
## 查看前十行内容
root@localhost:world> select id,name,countrycode from city limit 10;
## 翻页60行
root@localhost:world> select id,name,countrycode from city limit 60;
root@localhost:world> select id,name,countrycode from city limit 120,60; ## 翻页功能，从121开始查 60个
root@localhost:world> select id,name,countrycode from city limit 180,60; ## 翻页功能，从181开始查 60个

# select 条件查询
select * from city where countrycode='CHN';
select * from city where countrycode='CHN' or countrycode='USA';
select * from city where countrycode='CHN' and district='heilongjiang';

# 条件查询结合行级查询
select * from city where countrycode='CHN' and district='heilongjiang' limit 10;

# select 模糊查询
select * from city where countrycode like '%HN' or countrycode='JPN';
select * from city where countrycode like '%HN%' or countrycode='JPN';
select * from city where countrycode like 'HN%' or countrycode='JPN';

# select 排序查询 order by
select * from city where countrycode='CHN' order by population; ## 顺序排序
select * from city where countrycode='CHN' order by population desc; ## 倒序排序
select * from city order by population; ## 不加条件 顺序排序
select * from city order by population desc; ## 不加条件 倒序排
序

# select 范围查询
mysql[world]> select * from city where population > 10000000;
> < >= <= <> !=

# in
select * from city where countrycode in ('CHN','USA');
# or
select * from city where countrycode='CHN' or countrycode='USA';

# union all 联合查询
select * from city where countrycode='CHN' union all select * from city where
countrycode='USA';

# select 分组查询 group by
group by + 聚合函数
聚合函数:
max()：最大值
min()：最小值
sum()：求和
avg()：求平均值`
count()：统计

1.遇到统计想函数
2.形容词前groupby
3.函数中央是名词
4.列名select后添加

# 统计世界上每个国家的总人口数
遇到统计想函数：sum()
形容词前groupby：group by countrycode
函数中央是名词：sum(population)
列名select后添加：国家 人口数 countycode,population

mysql[world]> select countrycode,sum(population) from city group by countrycode;

# 统计中国各个省的人口数量(练习)
遇到统计想函数：sum()
形容词前groupby：group by district
函数中央是名词：sum(population)
名select后添加：省 人口数 district,population

select district,sum(population) from city where countrycode='CHN' group by district;

# 统每个国家的城市数量(练习)
遇到统计想函数：count()
形容词前groupby：group by countrycode
函数中央是名词：count(name)
名select后添加：国家，城市数量 countrycode,count(name)

select countrycode,count(name) from city group by countrycode

# 让字段显示自己想要的字段 as
select countrycode as 国家,count(name) as 城市数量 from city group by countrycode;

字符集定义

1.什么是字符集

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

2.MySQL数据库的字符集

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

3.MySQL中常见的字符集

1）UTF8
2）LATIN1
3）GBK

4.常见校对规则

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

字符集设置

1.操作系统级别

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

2.操作系统客户端级别(SSH)

3.MySQL实例级别

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

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

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

[mysqld]
character-set-server=utf8

4.建库级别

mysql> create database oldboy charset utf8 default collate = utf8_general_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;

select的高级用法

1.多表连接查询(连表查询)

创建表格

[zhang3,li4,wang5]
[50,70,80]

t1:
sid 001 002 003
sname zhang3 li4 wang5

t2:
sid 001 002 003
mark 50 70 80

# 建表
create table student(
sid int(3) zerofill,
sname varchar(10)
);

create table score(
sid int(3) zerofill,
smark float(3,1)
);

# 插数据
insert into student(sid,sname) value(1,'zhang3'),(2,'li4'),(3,'wang5');

insert into score value(1,50),(2,70),(3,80);

# 查看
mysql[zls1]> select * from student;
+------+--------+
| sid | sname |
+------+--------+
| 001 | zhang3 |
| 002 | li4 |
| 003 | wang5 |
+------+--------+

mysql[zls1]> show tables;
+----------------+
| Tables_in_zls1 |
+----------------+
| score |
| student |
+----------------+

传统连接

# 语法:
select tb1.field,tb2.field,tb3.field
from tb1,tb2,tb3
where 等价条件1
and 等价条件2
and 需求条件
注意：两张表，要有等价的条件列

# 查询出wang5的成绩
select student.sname,score.smark from student,score where student.sid=score.sid and
student.sname='wang5';

### 连表查询的前提：两张表，必须有关联的字段

## 基于上面world.sql库
# 世界上小于100人的人口城市是哪个国家的？
select country.name as 国家, city.name as 城市,city.population as 城市人口
from city,country
where city.countrycode=country.code
and city.population < 100;

+----------+-----------+-----------------+
| 国家 | 城市 | 城市人口数               |
+----------+-----------+-----------------+
| Pitcairn | Adamstown | 42             |
+----------+-----------+-----------------+

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

自连接(NATURAL JOIN)

# 语法:
natural join
select tb1.field1,tb2.field,tb1.field2
from tb1 natural join tb2
where 需求条件
#自动找到等价条件，前提：两张表的等价条件字段名，必须一样
city 国家代码：countrycode
country 国家代码： code

countrylanguage 国家代码： countrycode
SELECT city.name,city.countrycode ,countrylanguage.language ,city.population
FROM city NATURAL JOIN countrylanguage
WHERE population > 1000000;

# 说english的城市有哪些，他们的国家代码是什么？
select city.countrycode,city.name,countrylanguage.language
from city natural join countrylanguage
where countrylanguage.language='english';

# 每个国家有几个城市说英语,他们的国家代码是什么
select city.countrycode as 国家代码,count(city.name) as 城市数量,countrylanguage.language as 语言
from city natural join countrylanguage
where countrylanguage.language='english'
group by countrycode;

# 每个国家有几个城市说英语，他们的国家代码是什么？ 按城市数量排序
select city.countrycode as 国家代码,count(city.name) as 城市数量,countrylanguage.language as 语言
from city natural join countrylanguage
where countrylanguage.language='english'
group by countrycode order by 城市数量;

内连接(join on)

inner join on
# 语法:
### 两表
select tb1.field1,tb2.field,tb1.field2
from tb1 join tb2
on 等价条件
where 需求条件

### 多表
select tb1.field,tb2.field,tb3.field
from tb1
join tb2
on 等价条件1
join tb3
on 等价条件2
where 需求条件

# 每个国家有几个城市说英语，他们的国家代码是什么？
select city.countrycode as 国家代码,count(city.name) as 城市数量,countrylanguage.language as 语言
from city join countrylanguage
on city.countrycode=countrylanguage.countrycode
where countrylanguage.language='english'
group by city.countrycode;

# 世界上大于10000000人口数量的城市在哪个国家，说什么语言？
1 jion 2 on A

表1 jion 表2 on 等价条件A join 表3 on 等价条件B

**** 传统连接 ****
select country.name,city.name,city.population,countrylanguage.language
from country,city,countrylanguage
where country.code=city.countrycode
and country.code=countrylanguage.countrycode
and city.population > 10000000;

**** 内连接 三表连查 ****
select country.name,city.name,city.population,countrylanguage.language
from country
join city
on country.code=city.countrycode
join countrylanguage
on country.code=countrylanguage.countrycode
where city.population > 10000000;
join on 注意：大表在前，小表在后

外连接

# 左外连接 left join
- 左外连接
left join
select tb1.field1,tb2.field,tb1.field2
from tb1 left join tb2
on 等价条件
and 需求条件
显示左边表的内容，tb1，右边表结果显示NULL

select city.name,city.countrycode,country.name from city left join country on
city.countrycode=country.code and city.population<100;

# 右外连接 right join
- 右外连接
right join
select tb1.field1,tb2.field,tb1.field2
from tb1 right join tb2
on 等价条件
显示右边表的内容，tb2，左边表结果显示NULL

select city.name,city.countrycode,country.name from city right join country on city.countrycode=country.code and city.population<100;

联合查询

# 范围查询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' limit 10;

视图：view
触发器：trigger

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