MySQL的SQL种类

SQL种类主要分为：

DDL:数据库（对象）定义语言。（Data Definition Languag）
DCL:数据库控制语言（grant revoke）（Data Control Language）
DML：数据（行）操作语言（update delete insert）（Data Manipulation Language）
DQL: 数据查询语言（show、select）（Data Query Language）

DDL操作

对象：包括库和表

库

定义什么？
1、库名字
2、库的基本属性

如何定义？
create database lufei;  创建数据库lufei
create schema  lf;      创建数据库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;

表

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

use  lufei;
创建：
create table t1 (id int ,name varchar(20));

查询：
show tables;
show create table t1;
desc 

删除
drop table t1;

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

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: 数据查询语言

select基本查询

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

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 `Population`,`District` ASC

--- 按照列的位置排序（按照第5列排序，这里的第五列为Population）
SELECT * FROM city ORDER BY 5 DESC ;

limit子句

limit语句的使用（一般建议配合排序使用）

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

-- 11-20  跳过10行，然后再显示10行，所以显示的是11-20行的信息
SELECT * FROM world.`city` WHERE countrycode='CHN' 
ORDER BY 5 DESC LIMIT 10,10 ; 

--11-20  limit 10显示10行, OFFSET跳过10行。所以显示的是11-20行，跟上面一条SQL执行结果一样。
SELECT * FROM world.`city` WHERE countrycode='CHN' 
ORDER BY 5 DESC LIMIT 10 OFFSET 10 ;

注意：在MySQL中，把NULL值当作一列值中最小值对待。因此，生序排序时，它出现在最前面。

连表查询

DESC city
DESC countrylanguage

传统的连接写法（使用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;


# NATURAL  JOIN 使用的不多
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';


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

# 查询中国对应城市的总人口数

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


union（一般用来替换像 or 、in()，union的性能要优于它们）

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

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