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 排序并限制
---- 按照人口数量排序输出中国的城市信息（asc\desc）

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 <database_name>：列出指定数据库中的表
show columns from <table_name>：显示表的列结构
show index from <table_name>：显示表中有关索引和索引列的信息
show character set：显示可用的字符集及其默认整理
show collation：显示每个字符集的整理
show status：列出当前数据库状态
show variables：列出数据库中的参数定义值
原文地址：https://www.cnblogs.com/cuiyongchao007/p/12852961.html