MySQL的结构化查询

DML（Database Modification Language）

###### 表 ######
## 增   --- insert
1. 查询表字段的约束
desc bname
insert into mysql.user() values()
insert mysql.user(字段1，字段2，。。) values(值1，值2.。)
insert mysql.user values(全部值)

## 删   ---- delete
delete from mysql.user; # 不要出现很危险  删除表中的所有的数据
delete from mysql.user where 1=1;
delete from mysql.user where user=root and host='loalhost';
delete from mysql.user where user=root or user=kjt;

## 改   ---- update
update mysql.user set user=kjt where user=root and host='localhost'

利用update代替delete

# 添加一个状态列  enum类型的状态列
alter table zls.stu30 add status enum('0','1') not null default '0' comment '0代表no 1代表yes';

# 全表status列 设置为1
update zls.stu30 set status='1' where 1=1;

# 使用 update修改其中一列  的status
update zls.stu30 set status='0' where id=1;

# 查询语句中加上判断
select * from zls.stu30 where status='1';

DCL（Database Control Language）

# --- 授权  grant
grant all on *.* to root@'%' identified by '123';

# max_queries_per_hour：一个用户每小时可发出的查询数量  默认登录会查询一次数据库
grant all on *.* to 111@'%' identified by '123' with max_queries_per_hour 2;   

# max_updates_per_hour：一个用户每小时可发出的更新数量update
grant all on *.* to 111@'%' identified by '123' with max_queries_per_hour 2 max_updates_per_hour 2;  

# max_connections_per_hour：一个用户每小时可连接到服务器的次数
grant all on *.* to 111@'%' identified by '123' with max_connections_per_hour 1;

# max_user_connections：允许同时连接数量
grant all on *.* to 111@'%' identified by '123' with max_user_connections 2;

# --- 回收权限 revoke
revoke select on *.* from 111@'%';

DQL（Database Query Language）

### show
show databases;
show tables;
show tables from zls;
show create database zls;
show create table zls.stu30;
# 查询用户
show grants for test111@'%';
# 查询内置变量
show variables like '%char%';
# 查询存储引擎
show engines;
# 查询MySQL支持的字符集
show charset;
# 查询MySQL支持的校验规则
show collation;
# 查询 MySQL后台的语句
show processlist;
show full processlist;

### desc 查询表结构
desc mysql.user

### select

## 全盘扫描
select * from mysql.user;
## 单条件查询
select * from mysql.user where user=root;
## 多条件查询
select * from mysql.user where user=root or user=kjt;
## 成员查询
select * from mysql.user where user in ('root','kjt')
## 联合查询---union all
select * from mysql.user where user=root union all select * from mysql.user where user=kjt;
## 范围查询 （ < 、 > 、 >= 、 <= 、 <> 、 !=）
select * from city where id<10;
## 模糊查询----like
select * from mysql.user where user like '%oo%';
select * from mysql.user where user like 'roo%';
select * from mysql.user where user like '%oot';

## 排序查询------order by
# 正序 
select * from city where countrycode like 'H%' order by population;
# 倒序
select * from city where countrycode like 'H%' order by population desc;

## 分页---- limit
select * from mysql.user limit 5;
select * from mysql.user limit 10,5;

#### MySQL 自带的函数
password()
now()


## 去重复
distinct()

### 分组查询 group by
# max()      -- 最大
# min()      -- 最小
# avg()      -- 平均
# sum()      -- 求和
# count()    -- 统计

1.遇到统计想函数
2.形容词前group by
3.函数中央是名词
4.列名select后添加

root@localhost:world 02:52:25>select * from city limit 5;
+----+----------------+-------------+---------------+------------+
| ID | Name           | CountryCode | District      | Population |
+----+----------------+-------------+---------------+------------+
|  1 | Kabul          | AFG         | Kabol         |    1780000 |
|  2 | Qandahar       | AFG         | Qandahar      |     237500 |
|  3 | Herat          | AFG         | Herat         |     186800 |
|  4 | Mazar-e-Sharif | AFG         | Balkh         |     127800 |
|  5 | Amsterdam      | NLD         | Noord-Holland |     731200 |
+----+----------------+-------------+---------------+------------+
5 rows in set (0.00 sec)


## 统计世界每个国家的总人数
1.遇到统计想函数 sum()
2.形容词前groupby group by countrycode
3.函数中央是名词 sum(population)
4.列名select后添加 select 国家,总人口数

select countrycode,sum(population) from city group by countrycode;


# 统计中国各个省的人口数量
1.遇到统计想函数 sum()
2.形容词前groupby groupby district
3.函数中央是名词 sum(population)
4.列名select后添加 select countrycode,district,sum(population)

select District,sum(Population) from city group by District;


# 统计每个国家的城市数量
1.遇到统计想函数 count()
2.形容词前groupby group by countrycode
3.函数中央是名词 count(name)
4.列名select后添加 select countrycode,count(name)

select CountryCode,count(Name) from city group by CountryCode;

# 统每个国家的每个省的城市数量(练习)
select District,count(Name) from city group by District;
select CountryCode,District,count(Name) from city group by District,CountryCode;

# 统中国的每个省的城市数量(练习)
select District,count(Name) from city where CountryCode='CHN' group by District;

查询student表中的所有记录的sname、ssex和class列
select sname,ssex,class from student;

查询教师所有的单位即不重复的depart列
select depart from teacher group by depart;

查询student表的所有记录
SELECT * FROM student;

查询score表中成绩在60到80之间的所有记录。
select * from score where mark>'60' and mark<'80';

查询score表中成绩为85，86或88的记录。
select * from score where mark='85' or mark='86' or mark='88';

查询student表中1班或性别为“女”的同学记录。
select * from student where ssex='0';

以class降序查询Student表的所有记录.
select * from Student order by class desc;

以cno升序、mark降序查询Score表的所有记录
select * from score order by cno;

查询2班的学生人数
select count(class) from  where class='2';

查询”曾志高翔“教师任课的学生成绩
select student.sanme,score.mark from student,score where student.sno=score.sno and con='1';

查询语文课程所有男生的成绩并且查出对应课程的教师名，职称，及所在部门。
select teacher.tname,score.make from student,score,teacher where student.sno=score.sno and con='2' and ssex='1';

把11题查出的成绩按照降序排序。




select country.name,city.name,city.Population,countrylanguage.Language
from city,country,countrylanguage
where country.Code=countrylanguage.CountryCode 
and city.CountryCode=country.Code 
and country.Population<100;

select country.name,city.name,city.Population
from city join country
where city.Population<100


select country.name,city.name,city.Population,countrylanguage.Language
from city join country
on city.CountryCode=country.Code
join countrylanguage
on country.Code=countrylanguage.CountryCode 
where city.Population<100;