MySQL-SQL语句-增删改查-单表操作-练习题

MySQL-SQL语句-增删改查-单表操作-练习题

库定义

1.创建库并指定字符集

CREATE DATABASE ZHANG3 CHARSET utf8mb4;

表定义

1.复制表

ceate table stu like student;

2.查看表结构

use mysql;
desc student;

3.最后增加字段

ALTER TABLE `student`
ADD COLUMN TELNUM CHAR(11) NOT NULL UNIQUE KEY DEFAULT '0'
COMMENT '手机号';

4.指定字后增加字段

ALTER TABLE `student`
ADD COLUMN TELNUM CHAR(11) NOT NULL UNIQUE KEY DEFAULT '0'
COMMENT '手机号'
after name;

5.第一列前增加字段

ALTER TABLE `student` 
ADD COLUMN b CHAR(11) NOT NULL UNIQUE KEY DEFAULT '0' COMMENT '手机号' 
first;

6.删除字段

ALTER TABLE `student` 
  drop a;
 
ALTER TABLE `student` 
  drop b;

行定义

1.插入所有字段数据

insert into
city
values('4080','Rafah','CHN','Rafah','333');

2.插入部分字段的数据

insert into
city(ID,Name)
values('4081','Rafah');

3.查看所有数据

select * from city;

查询语句联系

1.查看指定的字段内容

select Name,Population from city;
desc city;
select Name,Population from city limit 10;

2.查询所有城市名及人口信息

select name,population from city;

3.查询city表中,所有中国的城市信息

select * from city where CountryCode='CHN';

4.查询人口数小于100人城市信息

select * from city where Population<100;

5.查询中国,人口数超过500w的所有城市信息

select * from city where CountryCode='CHN' AND Population>5000000;

6.查询中国或美国的城市信息

select * from city where CountryCode='CHN' OR CountryCode='USA';

7.查询人口数为100w-200w(包括两头)城市信息

select * from city where Population>=1000000 AND Population<=2000000;
select * from city where Population between 1000000 AND 2000000;

8.查询中国或美国,人口数大于500w的城市

select * from city where CountryCode in ('CHN','USA') AND Population>5000000;

9.查询城市名为qing开头的城市信息

select * from city where Name like 'qing%';

10.统计city表的行数

select count(*) from city;

11.统计中国城市的个数

select count(*) from city where Countrycode='CHN';

12.统计中国的总人口数

select sum(Population) from city where CountryCode='CHN';

13.统计每个国家的城市个数

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

14.统计每个国家的总人口数

select CountryCode,SUM(Population) from city group by CountryCode;

16.统计中国每个省的城市个数及城市名列表

SELECT district, COUNT(NAME),GROUP_CONCAT(NAME)
FROM city
WHERE countrycode='CHN' 
GROUP BY district;

17.统计每个国家的城市个数,并且只显示超过100个城市的国家

SELECT countryCode,COUNT(NAME) 
FROM city 
GROUP BY countryCode 
having count(countryCode)>100;

18.统计每个国家的城市个数,并且只显示超过100个城市的国家并按照从大到小排序

SELECT countryCode,COUNT(NAME) 
FROM city 
GROUP BY countryCode 
having count(countryCode)>100
order by count(Name) desc;

19.统计每个国家的城市个数,并且只显示超过100个城市的国家并按照从大到小排序,并且只显示排名前三

SELECT countryCode,COUNT(NAME) 
FROM city 
GROUP BY countryCode 
having count(countryCode)>100
order by count(Name) desc
limit 3;
©️2020 CSDN 皮肤主题: 数字20 设计师:CSDN官方博客 返回首页