MySQL客户端工具及SQL语句

MySQL客户端工具及SQL语句

一.客户端命令介绍

mysql

1.mysql客户端命令

#MySQL接口自带的命令

\h 或 help 或？      查看帮助

\G                  格式化查看数据（key：value）

\T 或 tee            记录日志

\c（5.7可以ctrl+c）   结束命令

\s 或 status         查看状态信息

\. 或 source         导入SQL数据

\u或 use             使用数据库

\q 或 exit 或 quit   退出

help命令的使用

mysql> help

mysql> help contents

mysql> help select

mysql> help create

mysql> help create user

mysql> help status

mysql> help show

2.MySQLadmin客户端管理命令

1、命令行管理工具

2. mysqldump: 备份数据库和表的内容

3.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 database

#创建数据库

[root@db01 ~]# mysqladmin -uroot -p123 create database

#重载授权表

[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

二.接收用户的SQL语句

1.SQL是结构化的查询语句
2.SQL的种类

DDL：数据定义语言

库对象：库名字、库属性

开发规范：库名，表名小写（windows不区分大小写，linux区分大小写）

创建库：create database|schema

#查看创建数据库语句帮助

mysql> help create database

Name: 'CREATE DATABASE'

Description:

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;

#查看数据库

mysql> show databases;

#创建oldboy数据库添加属性

mysql> create database testa charset utf8;

#创建特test1库（避免数据库已存在报错，做好判断）

mysql> create database if not exists zls;

#规范创建数据库（做好判断并且设置字符集形式utf8_general_ci）

mysql> create database if not exists test1 default character set utf8 default collate utf8_general_ci;

#查看库的属性  (DQL)

mysql> show create database test1;

+----------+----------------------------------------------------------------+

| Database | Create Database                                                |

+----------+----------------------------------------------------------------+

| test1    | CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET utf8 */ |

+----------+----------------------------------------------------------------+

改库（alter）

#查询字符集类型

mysql> use information_schema;

mysql> select * from collations;

+--------------------------+--------------------+-----+------------+-------------+---------+

| COLLATION_NAME           | CHARACTER_SET_NAME | ID  | IS_DEFAULT | IS_COMPILED | SORTLEN |

+--------------------------+--------------------+-----+------------+-------------+---------+

| utf8_general_ci          | utf8               |  33 | Yes        | Yes         |       1 |

| utf8_bin                 | utf8               |  83 |            | Yes         |       1 |

+--------------------------+--------------------+-----+------------+-------------+---------+

219 rows in set (0.00 sec)

#查看当前库的字符集

mysql> select * from schemata;

+--------------+--------------------+----------------------------+------------------------+----------+

| CATALOG_NAME | SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |

+--------------+--------------------+----------------------------+------------------------+----------+

| def          | information_schema | utf8                       | utf8_general_ci        | NULL     |

| def          | liqi               | utf8                       | utf8_general_ci        | NULL     |

| def          | mysql              | utf8                       | utf8_general_ci        | NULL     |

| def          | performance_schema | utf8                       | utf8_general_ci        | NULL     |

+--------------+--------------------+----------------------------+------------------------+----------+

10 rows in set (0.01 sec)

#查看oldboy的创建语句（DQL）

mysql> show create database oldboy;

#修改字符集类型

mysql> alter database oldboy charset gbk;

Query OK, 1 row affected (0.00 sec) mysql> show create 

#修改校验规则

mysql> alter database test1 collate utf8_bin;

Query OK, 1 row affected (0.01 sec)

#查询mysql客户端和服务端字符集校验规则

mysql> show global variables like '%server';

+----------------------+-----------------+

| Variable_name        | Value           |

+----------------------+-----------------+

| character_set_server | utf8            |

| collation_server     | utf8_general_ci |

+----------------------+-----------------+

2 rows in set (0.01 sec)

mysql> show variables like '%server';

+----------------------+-----------------+

| Variable_name        | Value           |

+----------------------+-----------------+

| character_set_server | utf8            |

| collation_server     | utf8_general_ci |

+----------------------+-----------------+

2 rows in set (0.00 sec)

删库：drop database

#删除oldboy数据库

mysql> drop database oldboy;

表

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

创建表：create table （开发做）

#查看创建表语句帮助

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：字符类型（变长）

zerofill: 自动补0

char：字符类型（定长)

tinyint：整数 -128 ~ 128

enum：枚举类型

datetime：时间类型年月日时分秒

#创建表加其他属性  (大小写都可以)

create table student2(

sid int not null primary key auto_increment comment '学号',

sname varchar(10) not null comment '学生姓名',

sage tinyint unsigned comment '学生年龄',

sgender enum('m','f') not null default 'm' comment '学生性别',

cometime datetime not null default NOW() comment '入学时间');

#查看建表语句

mysql> show create table student2;

#查看表

mysql> show tables;

#查看表中列的定义信息

mysql> desc student;

数据属性

not null：非空

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

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

unique key：单独的唯一的（可以为空）

pk=uk+not null

default：默认值

unsigned：无符号，和数字结合用就是非负数

comment：注释

删除表drop

#删除表

mysql> drop table student;

修改表定义：alter table （开发做）*

#修改表名student为stu

mysql> alter table student rename stu;

#添加列和列定义

mysql> alter table stu add age int;

#添加多个列,2

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

#授权root@10.0.0.51用户所有权限（非超级管理员）

mysql> grant all on *.* to root@'10.0.0.51' identified by '123';

#授权普通用户

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

#怎么去授权一个超级管理员呢？

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（增）

#注意：所有值必须一一对应，如果没有就给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');

update（改）

#不规范

mysql> update student set sgender='f';

#规范update修改

mysql> update student set sgender='f' where sid=1;

#如果非要全表修改

mysql> update student set sgender='f' where 1=1;

delete（删）

#不规范

mysql> delete from student;

#规范删除（危险）

mysql> delete from student where sid=3;

#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 aid=1;

3）应用查询存在的数据

mysql> select * from student where status=1;

DQL：数据查询语言

select：基础用法

#常用用法

mysql> select countrycode,district from city;

#查询city表中的所有内容

mysql> select * from city;

#查询单列

mysql> select countrycode from city;

#行级查询,limit（翻页功能）

mysql> select countrycode,district from city limit 2;

mysql> select id,countrycode,district from city limit 2,2;

#条件查询

mysql> select name,population from city where countrycode='CHN';

#多条件查询> 、< 、>=、<=、<>(!=)

mysql> select * from city where countrycode='chn' and population>999999;

mysql> select name,population from city where countrycode='CHN' and district='heilongjiang';

#范围查询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 like 'H%';

mysql> select * from city where countrycode like '%H';

mysql> select * from city where countrycode like '%H%';

mysql> select name,population,countrycode from city where countrycode like '%H%' limit 10;

#排序查询（顺序）

mysql> select id,name,population,countrycode from city order by countrycode limit 10;

#排序查询（倒叙）

mysql> select id,name,population,countrycode from city order by countrycode desc limit 10;

函数用法

#group by + 聚合函数

#聚合函数种类：

#max() 最大值

#min()  最小值

#avg() 平均值

#sum() 相加

#count()  计数

#distinct() 去重

#password() 查密码

#now() 当前时间

#database() 查库

总结：

1.遇到统计想函数

**2.形容词前group by **

3.函数中央是名词

**4.列名select后添加 **

例子：

#把原密码123改为1

[root@db01 ~]# mysqladmin -uroot -p123 password '1';

#查用户密码

mysql> select user,password from mysql.user;

+------+-------------------------------------------+

| user | password                                  |

+------+-------------------------------------------+

| root | *E6CC90B878B948C35E92B003C792C46C58C4AF40 |

| rep  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

| pri1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |

+------+-------------------------------------------+

3 rows in set (0.00 sec)

#相当于命令行的pwd，查当前所在的库

mysql> select  database();

+------------+

| database() |

+------------+

| world      |

+------------+

1 row in set (0.00 sec)

#举例

#统计世界上每个国家的总人口数

select 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 人口;

#统每个国家的城市数量

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');

三.字符集定义

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；

四.字符集设置

系统层：

#C6:

vim /etc/sysconfig/i18n

LANG="en US.UTF-8 " 

#C7:

[root@db01 ~]# vim /etc/locale.conf

LANG="en_US.UTF-8"

工具 xshell:

gbk 500-60000

utf8 1-90000

gb2312 2-5000

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：在配置文件中设置字符集

#永久

#修改配置文件/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;

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> 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

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

mysql> alter database oldboy CHARACTER SET utf8 collate utf8_general_ci;

mysql> alter table t1 CHARACTER SET utf8;

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

1.多表连接查询（连表查询）联合查询效率比in和or高

集合：

A: 1 2 3

B: 2 3 4 

交集：23

并集：1234

差集：14 

id：1 2 3

name: qls haoda zhang3 

id： 1 2 3

mark：80 90 120

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

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

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

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

例如：

#世界上小于100人的人口城市是哪个国家的？

select city.name,city.countrycode,country.name

from city,country

where city.countrycode=country.code

and city.population<100;

#世界上人口数量小于100的城市在哪个国家，说的什么语言？

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

#统计除中国各省份人数

mysql> select  district,sum(population)  from city where  countrycode='chn' group by district order by sum(population) desc ;

+----------------+-----------------+

| district       | sum(population) |

+----------------+-----------------+

| Liaoning       |        15079174 |

| Shandong       |        12114416 |

...

| Heilongjiang   |        11628057 |

| Jiangsu        |         9719860 |

+----------------+-----------------+

31 rows in set (0.01 sec)

#统计除国家名字出现的次数倒叙取前十行

mysql> select countrycode,count(name) from city group by countrycode order by count(name) desc limit 10;

+-------------+-------------+

| countrycode | count(name) |

+-------------+-------------+

| CHN         |         363 |

| IND         |         341 |

+-------------+-------------+

10 rows in set (0.01 sec)

#中国的国家代码出现的次数

mysql> select countrycode,count(name) from city where countrycode='chn' group by countrycode  order by count(name) desc limit 10;

+-------------+-------------+

| countrycode | count(name) |

+-------------+-------------+

| CHN         |         363 |

+-------------+-------------+

1 row in set (0.00 sec)

1.2 NATURAL　JOIN（自连接的表要有共同的列名字）

SELECT city.name,city.countrycode ,countrylanguage.language ,city.population

FROM  city NATURAL  JOIN  countrylanguage

WHERE population > 1000000

ORDER BY population;

#世界上人口数量小于100的城市在哪个国家，说的什么语言？

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

+------------+-----------+----------+-------------+

| population | name      | name     | language    |

+------------+-----------+----------+-------------+

|         42 | Adamstown | Pitcairn | Pitcairnese |

+------------+-----------+----------+-------------+

1.3企业中多表连接查询（内连接）

select city.name,city.countrycode,country.name

from city join country on city.countrycode=country.code

where city.population<100;

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

1.4外连接

select city.name,city.countrycode,country.name

from city left join country

on city.countrycode=country.code

and city.population<100;

左连接和右连接

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;

+----------------+--------------+--------------+

| 城市名称       | 国家代码     | 国家名称     |

+----------------+--------------+--------------+

| Kabul          | NULL         | NULL         |

| Qandahar       | NULL         | NULL         |

| Utrecht        | NULL         | NULL         |

| Eindhoven      | NULL         | NULL         |

| Tilburg        | NULL         | NULL         |

+----------------+--------------+--------------+

1.5 UNION（合并查询）

#范围查询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

巴特西

MySQL05-- 客户端工具及SQL语句