SQL语句DQL
1.DQL语句
SELECT
1.使用DQL查询
1.使用mysql官方提供的world.sql
mysql> SHOW TABLES;
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
| stu |
+-----------------+
DESC数据表
mysql> DESC city;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | MUL | | |
| District | char(20) | NO | | | |
| Population | int(11) | NO | | 0 | |
+-------------+----------+------+-----+---------+----------------+
id ---->自增长的序号列，主键
NAME ---->城市名字
countrycode ---->国家代号，三个字母组成,比如中国（CHN），美国(USA)
district ---->省或直辖市
population ---->人口数量
2.使用DQL
1.查看表所有的内容
SELECT * FROM city;
2.查看部分列数据，查询所有城市的人口数量（awk）
SELECT NAME,population FROM city;
2.DQL过滤查询
1 中国所有城市的信息（grep）
SELECT * FROM city WHERE countrycode='CHN';2 中国超过500w人口的城市信息
SELECT * FROM city WHERE countrycode='CHN' AND population>5000000;3 中国人口数量从大到小，前三的城市信息
SELECT * FROM city WHERE countrycode='CHN' ORDER BY population DESC LIMIT 3 ;4 浙江省人口排前三的城市
SELECT * FROM city WHERE district='zhejiang' ORDER BY population DESC LIMIT 3;5 查询中国省名字"shan"开头的城市信息
SELECT * FROM city WHERE District LIKE 'shan%';6 查询世界上人口数量大于500w 或者小于100人的城市
SELECT * FROM city WHERE population>5000000 OR population<100; 或者 SELECT * FROM city WHERE population>5000000 UNION SELECT * FROM city WHERE population<100;一般遇到
or,in的语句，我们建议修改成union的写法。
3.排序分组和聚合操作
排序、分组(常用函数)、聚合操作
DESC city1
order by条件列 ，一般情况是数字列1.查询美国城市人口情况，并由小到大排序显示
SELECT * FROM city WHERE countrycode='USA' ORDER BY population; SELECT * FROM city WHERE countrycode='USA' ORDER BY population DESC;2.查询美国城市人口情况，并由大到小排序显示前十名
SELECT * FROM city WHERE countrycode='USA' ORDER BY population DESC LIMIT 10;3.查询美国城市人口情况，并由大到小排序显示第十一名到第十三名
SELECT * FROM city WHERE countrycode='USA' ORDER BY population DESC LIMIT 10,3;或者
SELECT * FROM city WHERE countrycode='USA' ORDER BY population DESC LIMIT 3 OFFSET 10; DESC city;2.
GROUP BY分组查询SELECT * FROM city WHERE countrycode='CHN';1.统计中国的人口总和
CHN 1300000000 SELECT countrycode,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY countrycode2.统计所有国家的人口总和
SELECT countrycode,SUM(population) FROM city GROUP BY countrycode;3.统计中国每个省的人口总和,并按总人口数量进行从大到小排序
SELECT countrycode, District,SUM(population) AS sp FROM city WHERE countrycode='chn' GROUP BY District ORDER BY sp DESC LIMIT 3;
4.常见常见SQL操作
1、在oss库下创建以下表,并插入相应数据。
id(学号) NAME(姓名) age(年龄) gender(性别) xueke(学科) telnum(手机) gongzi(工资) 1 guosiyu 10 f linux 110 10000 2 yuhuafei 20 m linux 120 10010 3 zhangjianfeng 19 m linux 119 10020 4 lijiayang 18 m linux 123 10030 5 changxueyuan 17 m linux 132 10040 6 sunwukong 200 m python 211 1000 7 zhubajie 201 f python 122 2000 8 bailongma 123 f java 345 3000 9 zhangfei 40 m java 456 5000 10 guanyu 50 m bdata 789 6000 ----------------------------------------------------------------------------------- CREATE TABLE opsxdev( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, NAME VARCHAR(30) NOT NULL , age TINYINT NOT NULL , gender ENUM('m','f') NOT NULL DEFAULT 'm', xueke VARCHAR(20) NOT NULL , telnum CHAR(11) NOT NULL UNIQUE, gongzi INT NOT NULL ); DESC opsxdev ; INSERT INTO opsxdev(id,NAME,age,gender,xueke,telnum,gongzi) VALUES (1,'guosiyu',10,'f','linux','110',10000), (2,'yuhuafei',20,'m','linux','120',10010), (3,'zhangjianfeng',19,'m','linux','119',10020), (4,'lijiayang',18,'m','linux','123',10030), (5,'changxueyuan',17,'m','linux','132',10040), (6,'sunwukong',200,'m','python','211',1000), (7,'zhubajie',201,'f','python','122',2000), (8,'bailongma',123,'f','java','345',3000), (9,'zhangfei',40,'m','java','456',5000), (10,'guanyu',50,'m','bdata','789',6000); SELECT * FROM opsxdev; 2、UPDATE+WHERE ```sql UPDATE opsxdev SET gongzi=gongzi-1000 WHERE NAME='guosiyu';3、UPDATE+SET+WHERE
SELECT * FROM opsxdev WHERE NAME='yuhuafei'; UPDATE opsxdev SET NAME='yudaye' WHERE id=2;4、ALTER
ALTER TABLE opsxdev ADD state ENUM('1','0') NOT NULL DEFAULT '1';5、SELECT+FROM
SELECT NAME,gongzi FROM opsxdev;6、WHERE +
>SELECT * FROM opsxdev WHERE age>100;7、WHERE +
<SELECT * FROM opsxdev WHERE gongzi<5000;8、模糊查询
SELECT * FROM opsxdev WHERE NAME LIKE 'zhang%';9、ORDER BY
SELECT * FROM opsxdev WHERE xueke='linux' ORDER BY gongzi;10、统计不同学科的总工资
SELECT xueke,SUM(gongzi) FROM opsxdev GROUP BY xueke;11、统计不同学科的平均工资
SELECT xueke,AVG(gongzi) FROM opsxdev GROUP BY xueke;12、统计不同学科的最高工资
SELECT xueke,MAX(gongzi) FROM opsxdev GROUP BY xueke;13、统计不同学科的最低工资的员工
SELECT xueke, NAME,MIN(gongzi) FROM opsxdev GROUP BY xueke;14、统计男、女性别的员工数量(COUNT())
SELECT gender, COUNT(gender) FROM opsxdev GROUP BY gender;15、统计各个学科的员工数量(COUNT())
SELECT xueke , COUNT(xueke) FROM opsxdev GROUP BY xueke16、查询linux学科低于平均工资的员工名字
求linux学科平均工资
SELECT AVG(gongzi) FROM opsxdev WHERE xueke='linux' GROUP BY xueke;查找linux部门的，低于9620元的员工姓名
SELECT NAME,gongzi FROM opsxdev WHERE xueke='linux' AND gongzi<9620; SELECT NAME,gongzi FROM opsxdev WHERE xueke='linux' AND gongzi<(SELECT AVG(gongzi) FROM opsxdev WHERE xueke='linux' GROUP BY xueke);17、linux部门最低工资员工工资情况，与其他学科对比的排名情况
SELECT xueke,NAME ,MIN(gongzi) AS gongzi FROM opsxdev WHERE xueke='linux' GROUP BY xueke UNION SELECT xueke ,NAME ,gongzi FROM opsxdev WHERE xueke !='linux' ORDER BY gongzi DESC ;