MySQL

2024-07-16 05:58| 来源: 网络整理| 查看: 265

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

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

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

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

select * from city where Population>=1000000 AND Population5000000;

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;

【本文地址】

公司简介

联系我们