mysql的增删改查及例题

CREATE TABLE student(
	id INT,
	NAME VARCHAR(20),
	chinese FLOAT,
	english FLOAT,
	math FLOAT
);

--1. 请添加2列信息，出生日期，籍贯。

ALTER TABLE student ADD COLUMN birthday DATE;

ALTER TABLE student ADD COLUMN native_place VARCHAR(255);

--2. 请修改语文成绩的数据类型为int型

ALTER TABLE student MODIFY COLUMN chinese INT;

3. 请在里面插入10名学生数据
提示：首先修改name这一列的字符集，然后再插入，否则默认的字符集为Latin1，插入中文会报错

INSERT INTO student
VALUES (1, '张三', 85, 52, 88, '2000-01-01', '北京'),
       (2, '李四', 88, 89, 56, '2000-02-01', '上海'),
       (3, '王五', 80, 78, 82, '2000-03-01', '广州'),
       (4, '赵涛', 78, 92, 90, '2000-04-01', '深圳'),
       (5, '钱七', 92, 88, 55, '2000-05-01', '武汉'),
       (6, '孙力', 84, 85, 88, '2000-06-01', '天津'),
       (7, '杨九', 45, 84, 86, '2000-07-01', '南京'),
       (8, '吴十', 90, 89, 84, '2000-08-01', '杭州'),
       (9, '周杰', 89, 83, 80, '2000-09-01', '成都'),
       (10, '郑好', 66, 82, 85, '2000-10-01', '西安');
4. 假设10名同学中有同姓的，如王，请找出姓王同学的信息


       
SELECT * FROM student WHERE NAME LIKE '王%';
5. 请找出各科不及格学生的信息


SELECT * FROM student WHERE chinese<60;
SELECT * FROM student WHERE math<60;
SELECT * FROM student WHERE english<60

6. 请找出有任何一科不及格学生的名称


SELECT * FROM student WHERE chinese<60 OR math <60 OR english<60;

7. 请找出两科成绩在90分以上的学生名称


SELECT *FROM student 
WHERE 
   ( chinese > 90 AND math >90) OR
   (chinese >90 AND english >90) OR
   (math >90 AND english >90);
   
   -- 解法二
   SELECT NAME 
FROM student 
WHERE 
    (CASE WHEN chinese > 90 THEN 1 ELSE 0 END +
     CASE WHEN english > 90 THEN 1 ELSE 0 END +
     CASE WHEN math > 90 THEN 1 ELSE 0 END) >= 2;


8. 请找出没有一科挂科的学生名称

SELECT * FROM student WHERE chinese>=60 AND math >=60 AND english>=60;








CREATE TABLE s (
    id INT PRIMARY KEY,   
    NAME VARCHAR(50),     
    gender VARCHAR(10) CHECK (gender IN ('male', 'female')),  
    age INT,             
    dept VARCHAR(50),
    dept_id INT   
);



-- 1.查询全体学生的学号与姓名。

SELECT id,NAME FROM s



-- 3.查询全体学生的详细记录。
SELECT * FROM s

-- 4.查询全体学生的姓名、出生年份和所属部门 使用列别名改变查询结果的列标题

SELECT NAME '姓名', YEAR(CURDATE())-age '出生年份',dept '所属部门' FROM s;


-- 7.查询所有年龄在20岁以下的学生姓名及其年龄。

SELECT NAME,age FROM s WHERE age<20


-- 8.查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。
SELECT NAME, dept,age FROM s WHERE age BETWEEN 20 AND 23;


-- 9.查询年龄不在20~23岁之间的学生姓名、系别和年龄。
SELECT NAME, dept,age FROM s WHERE age NOT BETWEEN 20 AND 23;

-- 10.查询信息系、数学系和计算机系学生的姓名和性别。

SELECT dept,GROUP_CONCAT(NAME,gender) FROM s GROUP BY dept;
-- 11.查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。
SELECT NAME,gender FROM s WHERE dept NOT IN('信息系','数学系','计算机科学系')

-- 12.查询学号为200518的学生的详细情况。
SELECT * FROM s WHERE id = 200518;

-- 13.查询所有姓刘学生的姓名、学号和性别。
SELECT NAME,id,gender FROM s WHERE NAME LIKE '刘%';

-- 14.查询姓“李”且全名为两个汉字的学生的姓名。

SELECT NAME FROM s WHERE NAME LIKE '李_';

-- 15.查询名字中第2个字为“立"字的学生的姓名和学号。
SELECT NAME,id FROM s WHERE NAME LIKE '_立_';


-- 16.查询所有不姓刘的学生姓名。
SELECT NAME FROM s WHERE NAME NOT LIKE '刘%';


-- 17.查询学号在201801~201809之间的学生姓名。
SELECT NAME FROM s WHERE id BETWEEN 201801 AND 201809


-- 18.查询不同院系学生的人数。

SELECT dept, COUNT(*) FROM s GROUP BY dept

-- 19.查询不同院系学生的平均年龄，并以降序排序。

SELECT dept,AVG(age) FROM s GROUP BY dept ORDER BY AVG(age) DESC;

-- 20.查询计算机系年龄在20岁以下的学生姓名。
SELECT NAME FROM s WHERE dept = '计算机科学系' AND age <20;


-- 22.查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。

SELECT * FROM s ORDER BY dept_id,age DESC;





DROP TABLE city;


CREATE TABLE city (
    id INT ,
    NAME VARCHAR(50),
    country_name VARCHAR(50),
    population INT,
    province VARCHAR(50)
);
INSERT INTO city 
VALUES 
(1, '北京', '中国', 599, '北京市'),
(2, '上海', '中国', 698, '上海市'),
(3, '纽约', '美国', 819, '纽约州'),
(4, '洛杉矶', '美国', 379, '加利福尼亚州'),
(5, '巴黎', '法国', 214, 'Île-de-France'),
(6, '东京', '日本', 137, '关东地方'),
(7, '悉尼', '澳大利亚', 474, '新南威尔士州'),
(8, '莫斯科', '俄罗斯', 125, '中央联邦区'),
(9, '小镇', '中国', 99, '西藏'),
(10, '芝加哥', '美国', 269, '伊利诺伊州'),
(11, '休斯顿', '美国', 214, '得克萨斯州'),
(12, '凤凰城', '美国', 72, '亚利桑那州'),
(13, '费城', '美国', 158, '宾夕法尼亚州'),
(14, '圣安东尼奥', '美国', 147, '得克萨斯州'),
(15, '圣彼得堡', '俄罗斯', 195, '圣彼得堡联邦主体'),
(16, '诺夫哥罗德', '俄罗斯', 217, '诺夫哥罗德州'),
(17, '耶卡捷琳堡', '俄罗斯', 96, '斯瓦德洛夫斯克州'),
(18, '喀山', '俄罗斯', 500, '鞑靼斯坦共和国'),
(19, '索契', '俄罗斯',334, '克拉斯诺达尔边疆区'),
(20, '海淀', '中国', 599, '北京市'),
(21, '朝阳', '中国', 599, '北京市'),
(22, '虹桥', '中国', 599, '上海市');

INSERT INTO city (id, NAME, country_name, population, province)
VALUES 
(23, '大阪', '日本', 269, '大阪府'),
(24, '京都', '日本', 145, '京都府'),
(25, '福冈', '日本', 150, '福冈县'),
(26, '仙台', '日本', 108, '宫城县'),
(27, '神户', '日本', 152, '兵库县');




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

SELECT  NAME ,population FROM city;

-- 2. 查询city表中，所有中国的城市信息

SELECT * FROM city WHERE country_name = '中国';

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

SELECT * FROM city WHERE population<100;

-- 4. 查询中国,人口数超过500的所有城市信息
SELECT * FROM city WHERE country_name ='中国' AND population >500;

-- 5. 查询中国或美国的城市信息
SELECT * FROM city WHERE country_name IN('中国','美国');

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


SELECT * FROM city WHERE population BETWEEN 100 AND 200;

-- 7. 查询中国或美国，人口数大于500的城市

SELECT * FROM city WHERE country_name IN('中国','美国') AND population >500;

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

SELECT * FROM city WHERE NAME LIKE 'qing%';

-- 9. 统计city表的行数
SELECT COUNT(*) FROM city;

-- 10.统计各国城市的个数
SELECT country_name,COUNT(NAME) FROM city GROUP BY country_name;

-- 11.统计每个国家的城市总人口数
SELECT country_name, SUM(population) FROM city GROUP BY country_name;

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

SELECT province ,COUNT(NAME),GROUP_CONCAT(NAME) FROM city WHERE country_name = '中国' GROUP BY province;
        
-- 13.统计每个国家的城市个数,并且只显示超过5个城市的国家

SELECT country_name,COUNT(NAME) FROM city  GROUP BY country_name HAVING  COUNT(NAME) >5;


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

SELECT country_name,COUNT(NAME) FROM city  GROUP BY country_name HAVING  COUNT(NAME) >5 ORDER BY COUNT(NAME) DESC;

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

SELECT country_name,COUNT(NAME) FROM city  GROUP BY country_name HAVING  COUNT(NAME) >5 ORDER BY COUNT(NAME)DESC LIMIT 3;