数据库学习之七：MySQL 客户端工具及SQL入门

七、MySQL 客户端工具及SQL入门

1、课程大纲：

mysql客户端命令介绍；

mysql获取帮助的方法细讲；

DDL语句之管理数据库；

DDL语句之管理表与案例介绍；

DML语句之管理表中的数据；

SELECT 检索数据；

2、mysql接口程序使用及SQL入门

mysql客户端命令介绍：
• mysql： – 用于数据库连接管理
- 将 用户SQL 语句发送到服务器
• mysqladmin： – 命令行管理工具
• mysqldump： – 备份数据库和表的内容

- 用于管理数据库：
	命令接口自带命令
	DDL:数据定义语言（create）
	DCL：数据控制语言(grant,revoke)
	DML：数据操作语言(update,delete,insert)
mysql 接口程序：
mysql -uroot -poldboy123 -e "show variables like "%server_id%""
mysql>:
1,接口自带的功能
mysql命令：
1.h或help 或？
显示接口命令帮助命令。
2.G
将显示的内容格式输出。
3.T或者tee
日志记录，需要先：tee /tmp/test.log
所有mysql操作及输出都记录在这个文件里。
4.c 或者CTRL+c
语句后面带c，前面的命令不在执行。ctrl+c退出
5.s 或 status
查看当前数据库的基本状态。
6.. 或 source
用来执行外部的SQL脚本：二进制日志截取，备份出来的sql脚本
7. use
use 进入到某个数据库。
2，服务器端命令（SQL结构化的查询语言,mysql接口程序只负责接收SQL)
show 系列命令。

2、服务器端命令（SQL）

（1）SQL:结构化的查询语言，mysql接口程序只负责接收SQL，传送SQL.

（2）SQL种类：

​ DDL：数据库对象定义语言(create)

​ DCL：数据库控制语言（grant，revoke）

​ DML:数据行操作语言（update,delete,insert)

​ DQL:数据查询语言（show，select）

DDL操作：

​ 对象：f

​ 库：

​ 定义什么？

​ 1、库名字

​ 2、库的基本属性

​ 如何定义？

​ create database lufei;

​ create shema lf;

​ show databases;

mysql> help create database
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_specification] ...
创建属性：
create_specification:
字符集：[DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] 
  排序规则：COLLATE [=] collation_name
mysql> create database llf character set utf-8;
mysql> show create database llf;#查看建库语句
drop database llf;删除数据库

help  后面加命令，帮助不熟悉使用的命令。
修改字符集：
ALTER DATABASE [db_name] CHARACTER SET charset_name collation_name
mysql> alter database lf charset utf8mb4;
                          缩写
mysql> show create database lf;

​ 表：

​ 表数据

​ 表属性（元数据）：表明，列名字，列定义（数据类型，约束，特殊列属性）、表的索引信息。

​ 定义什么？

​ 定义表的属性？

use lufei;
create table t1(id int,name varchar(20));
mysql> use lufei;
mysql> create table t1(id int ,name varchar(20));
mysql> show tables;
mysql> show create table t1;
mysql> desc t1;
mysql> drop table t1;

修改表的定义：
		修改：
		（1）在表中添加一列
		alter table t1 add age int;
		（2）添加多列
		alter table t1 add bridate datetime, add gender enum("M","F");
		（3）在指定列后添加一列
		alter table t1 add stu_id int after id;
		（4）在表中最前添加一列
		alter table t1 add sid int first;
		（5）删除列
		 alter table t1 drop sid;
		（6）修改列名
		alter table t1 change name stu_name varchar(20);
		（7）修改列属性
		alter table t1 modify stu_id varchar(20);
		（8）修改表名
		rename table t1 to student;
		alter table student rename  as stu;

DML语句：数据库操作语言

​ insert

update

​ delete

DML语句：数据操作语言
	insert 
	use  lufei
	create table t1 (id int ,name varchar(20));
	insert into t1 values(1,"zhang3");
	select * from t1;
	insert into t1 values (2,"li4"),(3,"wang5"),(4,"ma6");
	insert into t1(name) values ("xyz");
	update
	update  t1  set name="zhang33" ;   ----会更新表中所有行的name字段，比较危险。
	update  t1  set name="zhang55" where id=1;   ----update在使用时一般都会有where条件去限制。
	delete
	delete from t1 ;  --删除表中所有行，比较危险。一行一行删除表中数据。
	delete from t1   where  id=2;
	DDL
	truncate table t1;  ---在物理上删除表数据，速度比较快。

DQL语句：（数据库查询语句）

DQL:
	select语句：
SELECT USER,PASSWORD ,HOST  FROM mysql.user;

-- select 基本查询

DESC world.city
SELECT  id ,NAME   FROM  world.city;
SELECT * FROM world.`city`;
-- select 条件查询 where
---- 1、查询中国（CHN）所有的城市信息
SELECT * FROM world.`city` WHERE countrycode="CHN";
---- 2、查询中国（CHN）安徽省所有的城市信息。
SELECT * FROM world.`city` 
WHERE countrycode="CHN"
AND
district="anhui";

---- 3、查询世界上人口数量在10w-20w城市信息
SELECT * FROM world.`city` 
WHERE 
population BETWEEN 100000 AND 200000 ;

---- 4、中国或者日本的所有城市信息
where字句中的IN
SELECT * FROM world.city
WHERE countrycode IN ("CHN","JPN");

---- 5、模糊查询
SELECT * FROM world.city
WHERE countrycode LIKE "ch%";

select 排序并限制

按照人口数量排序输出中国的城市信息（asc（默认升序），desc（降序））

-- select 排序并限制
---- 按照人口数量排序输出中国的城市信息（ASCDESC）

SELECT * FROM world.`city` WHERE countrycode="CHN" ORDER BY population ASC;
SELECT * FROM world.`city` WHERE countrycode="CHN" ORDER BY population DESC;

---- 按照多列排序人口+省排序
SELECT * FROM world.`city` WHERE countrycode="CHN" ORDER BY id DESC  ;
按照第5列进行降序排序：
SELECT * FROM city
ORDER BY 5 DESC ;

1-20
SELECT * FROM world.`city` WHERE countrycode="CHN" 
ORDER BY 5 DESC LIMIT 20;

显示11-20行
SELECT * FROM world.`city` WHERE countrycode="CHN" 
ORDER BY 5 DESC LIMIT 10,10 ;

SELECT * FROM world.`city` WHERE countrycode="CHN" 
ORDER BY 5 DESC LIMIT 10 OFFSET 10 ;

表连接查询（使用where）

传统的连接写法（使用where）
---- 中国所有城市信息+使用语言
		
SELECT NAME ,countrycode ,population FROM city 
WHERE countrycode ="CHN"	
	
SELECT countrycode ,LANGUAGE FROM countrylanguage;

SELECT ci.NAME ,ci.countrycode ,ci.population,cl.language 
FROM 
city AS ci , countrylanguage AS cl
WHERE ci.countrycode ="CHN" 
AND
ci.CountryCode=cl.CountryCode;

SELECT NAME,ci.countrycode ,cl.language ,ci.population
FROM  city ci , countrylanguage cl
WHERE 
ci.countrycode="chn" AND
ci.`CountryCode`=cl.countrycode;

SELECT NAME,countrycode ,LANGUAGE ,population
FROM  city NATURAL  JOIN  countrylanguage 
WHERE population > 10000000
ORDER BY population;

SELECT NAME,countrycode ,LANGUAGE ,population
FROM  city JOIN  countrylanguage 
USING(countrycode);

---- 查询青岛这个城市，所在的国家具体叫什么名字
DESC city
DESC country

SELECT NAME,countrycode FROM city WHERE NAME="qingdao";

SELECT NAME FROM country WHERE CODE="CHN";
--------------------------------
SELECT ci.name ,ci.countrycode,ci.population ,co.name
FROM city AS ci 
JOIN 
country AS co
ON ci.countrycode=co.code
AND
ci.name="qingdao";

group by +聚合函数 （avg(),max(),min(),sum())

group by +聚合函数（avg()、max()、min()、sum()）

SELECT countrycode ,SUM(population) FROM city
WHERE countrycode = "chn"
GROUP BY countrycode;

union 

用来替换  or 、in()

SELECT * FROM world.city
WHERE countrycode IN ("CHN","JPN");
改写为：

SELECT * FROM world.city
WHERE countrycode ="CHN"
union
SELECT * FROM world.city
WHERE countrycode ="JPN";

字符集

字符集：

charset:字符集
UTF8
UTF8mb4

gbk

collation：排序规则

a-z ,A-Z  大小写敏感

aA-zZ     小写不敏感

show charset;
show collation;

数据库：

服务器端字符集：

控制的是，存到mysql中时，字符集控制

客户端字符集

控制的是用户的输入及显示
系统字符集

控制的是系统相关的显示，和一些依赖于操作系统的应用
alter database oldboy CHARACTER SET utf8 collate utf8_general_ci;
alter table t1 CHARACTER SET latin1;

注意：更改字符集时，一定要保证由小往大改，后者必须是前者的严格超集。生产中别随便改。

数据类型及列属性：

数字类型
字符类型
时间类型
列属性
create table student(id int not null primary key  AUTO_INCREMENT)；
create table student1(id int not null primary key  AUTO_INCREMENT,name varchar(20))charset utf8;
create table teacher(id int not null ,name varchar(20) not null);
create table teacher1(id int not null ,name varchar(20) not null,beizhu varchar(20) not null default "ok");

primary key 主键：非空、唯一
unique：唯一

获取元数据：

information_schema :

数据行之外

元数据（定义数据的数据列属性，列名字等，状态）

充当数据库元数据的中央系统信息库：

• 模式和模式对象
• 服务器统计信息（状态变量，设置，连接）

采用表格式以实现灵活访问

• 使用任意select 语句

是“虚拟数据库”

• 表并非“真实”表（基表），而是“系统视图”
• 根据当前用户的特权动态填充表

mysql> use information_schema 

mysql> show tables;

mysql> desc tables;

mysql> select table_name ,table_schema,engine from world;

显示数据库world中表的列的信息：

mysql> select * from columns where table_schema="world"G;

mysql> select table_schema,table_name from information_schema.tables where table_schema="world";
+--------------+-----------------+
| table_schema | table_name      |
+--------------+-----------------+
| world        | city            |
| world        | country         |
| world        | countrylanguage |
+--------------+-----------------+
批量拼接语句：
----
mysql> select concat("hellow");
+------------------+
| concat("hellow") |
+------------------+
| hellow           |
+------------------+
1 row in set (0.01 sec)
----
实例：
mysql> select concat("mysqldump -uroot -poldboy123 ",table_schema," ",table_name," >>","/backup/",table_schema,"-",table__name,".bak.sql") from information_schema.tables where table_schema="world";
+-----------------------------------------------------------------------------------------------------------------------------+
| concat("mysqldump -uroot -poldboy123 ",table_schema," ",table_name," >>","/backup/",table_schema,"-",table_name,".bak.sql") |
+-----------------------------------------------------------------------------------------------------------------------------+
| mysqldump -uroot -poldboy123 world city >>/backup/world-city.bak.sql                                                        |
| mysqldump -uroot -poldboy123 world country >>/backup/world-country.bak.sql                                                  |
| mysqldump -uroot -poldboy123 world countrylanguage >>/backup/world-countrylanguage.bak.sql                                  |
+-----------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
实例2：
SELECT CONCAT("CREATE TABLE ", TABLE_SCHEMA, ".",
			 TABLE_NAME, "_backup LIKE ", TABLE_SCHEMA, ".",
			 TABLE_NAME, ";") FROM INFORMATION_SCHEMA.TABLES
			 WHERE TABLE_SCHEMA = ‘world’;

linux命令行使用的命令：
[root@centos6-kvm3 data]# mysqlshow -uroot -poldboy123 world
元数据一般查询语句：
show
show databases
show create database oldboy
show tables
show create table t1

SOHW databases：列出所有数据库
SHOW TABLES：列出默认数据库中的表
SHOW TABLES FROM ：列出指定数据库中的表
SHOW COLUMNS FROM 
：显示表的列结构
SHOW INDEX FROM 
：显示表中有关索引和索引列的信息
SHOW CHARACTER SET：显示可用的字符集及其默认整理
SHOW COLLATION：显示每个字符集的整理
SHOW STATUS：列出当前数据库状态
SHOW VARIABLES：列出数据库中的参数定义值