06-服务器端命令（SQL）

SQL定义：结构化的查询语言，mysql接口程序只负责接收SQL，传送给SQL层

SQL种类

• DDL:数据库（对象）定义语言
• DCL:数据库控制语言（grant revoke）
• DML：数据（行）操作语言（update delete insert）
• DQL: 数据查询语言（show、select）

一、DDL:数据库（对象）定义语言：

DDL

对库的操作

create database lufei; 
create schema  lf;
show databases;
create database llf CHARACTER SET utf8 ;
show create database llf； # 查询表的创建属性
drop database llf; 
help  create database;
字符集：  CHARACTER SET [=] charset_name
排序规则：COLLATE [=] collation_name
		
改库的字符集:
ALTER DATABASE [db_name] CHARACTER SET  charset_name COLLATE collation_name
mysql> alter database lf charset utf8mb4;
mysql> show create database lf;

对表的操作

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

创建：

create table t1 (id int ,name varchar(20));

查询：

show tables;
show create table t1; #查询创建语句
desc t1 # 查看列属性

show charset; #查看支持的字符集

show collation; # 查看支持的校对规则

删除

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  to stu;

• 修改字符集

alter database oldboy CHARACTER SET utf8 collate utf8_general_ci;
alter table t1 CHARACTER SET latin1;

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

二、DML：数据（行）操作语言（update delete insert）

1. 创建一个表

create table t1 (id int ,name varchar(20));

2. 在表中插入数据

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'); # 只给某一列插入数据这里是name列

3. 更新表中数据

update
update  t1  set name='zhang33' ;   ----会更新表中所有行的name字段，比较危险。
update  t1  set name='zhang55' where id=1;   ----update在使用时一般都会有where条件去限制。

4. 删除数据

delete
delete from t1 ;  --删除表中所有行，比较危险。一行一行删除表中数据。
delete from t1   where  id=2;

truncate table t1; ---DDL 操作在物理上删除表数据，速度比较快。

三、DQL: 数据查询语言（show、select）

select 基本查询

1. 语法

SELECT * FROM world.`city`;
       列        库.表

2. 查询中国（CHN）所有的城市信息

SELECT * FROM world.`city` WHERE countrycode='CHN';

3. 查询中国（CHN）安徽省所有的城市信息。AND

SELECT * FROM world.`city` WHERE countrycode='CHN' AND District='anhui';

4. 查询世界上人口数量在10w-20w城市信息 BETWEEN

SELECT * FROM world.`city` WHERE population BETWEEN 100000 AND 200000 ;

5. 中国或者日本的所有城市信息 IN

SELECT * FROM world.city WHERE countrycode IN ('CHN','JPN');

6. 模糊查询 LIKE

SELECT * FROM world.`city` WHERE countrycode LIKE 'CH%';

7.按照人口数量排序输出中国的城市信息（ASC\DESC）

SELECT * FROM world.`city` WHERE countrycode='CHN' ORDER BY population ;
SELECT * FROM world.`city` WHERE countrycode='CHN' ORDER BY population DESC; #从大到小

8. LIMIT

SELECT * FROM world.`city` WHERE countrycode='CHN' ORDER BY population DESC LIMIT 10; # 显示前10行

SELECT * FROM world.`city` WHERE countrycode='CHN' ORDER BY population DESC LIMIT 12,10; # 从12行开始往后显示10行

SELECT * FROM world.`city` WHERE countrycode='CHN' ORDER BY population DESC LIMIT 10 OFFSET 12;  # 从12行开始往后显示10行

表连接查询

1. 传统的连接写法使用where 中国所有城市信息+使用语言 （需要两个名字一样的列）

SELECT ci.id,ci.name,ci.countrycode,ci.population,cl.language  # 需要查询的信息
FROM
city AS ci , countrylanguage AS cl # 在那个表中查询 AS设置别名
WHERE 
ci.countrycode='CHN' #查询个条件
AND
ci.`CountryCode` = cl.`CountryCode` # 两个表具有相同值的列

2. 查询青岛这个城市，所在的国家具体叫什么名字 JOIN ON

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()）

1. 查询中国的总人口数量

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

2. union 用来替换 or 、in()

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

SELECT * FROM world.city WHERE countrycode='chn'
UNION  # 连接两个sql 查询的列需要一样
SELECT * FROM world.city WHERE countrycode='jpn'