数据库相关操作

#创建表
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(50),
sex VARCHAR(10),
hometown VARCHAR(50),
age VARCHAR(10),
class VARCHAR(50)
)

#添加数据
INSERT INTO student VALUES
(1,'王昭君','女','北京',20,'1班'),
(2,'妲己','女','广东',26,'2班'),
(3,'李白','男','河南',21,'1班'),
(4,'刘备','男','四川',32,'2班'),
(5,'诸葛亮','男','上海',18,'2班'),
(6,'张飞','男','南京',24,'3班'),
(7,'白起','男','北京',22,'4班'),
(8,'大乔','女','天津',19,'3班'),
(9,'孙尚香','女','河北',18,'1班'),
(10,'百里玄策','男','山西',20,'2班'),
(11,'百里守约','男','上海',21,'1班'),
(12,'小乔','女','',15,'3班')

 

 

#查询所有学生信息
SELECT * FROM student

#查询名字是李白的学生
SELECT * FROM student WHERE NAME='李白'

#查询1班是上海的
SELECT * FROM student WHERE class='1班' AND hometown='上海'

#查询家乡是北京或上海的
SELECT * FROM student WHERE hometown='北京' OR hometown='上海'

#查询小乔的年龄
SELECT NAME,age FROM student WHERE NAME='小乔'

#查询家乡不在北京的学生
SELECT * FROM student WHERE hometown NOT IN('北京')

#查询年龄小于20的女同学
SELECT * FROM student WHERE sex='女' AND age<20

 

#查询年龄为18至20的学生
SELECT * FROM student WHERE age>=18 AND age<=20

#查询北京学生的年龄总和
SELECT SUM(age) FROM student WHERE hometown='北京'

#查询女生的平均年龄
SELECT AVG(age) FROM student WHERE sex='女'

#查询姓名包含白的学生
SELECT * FROM student WHERE NAME LIKE '%白%'

#查询所有学生信息，俺年龄从大到小排序，年龄相同时，再按学号从小到大排序
SELECT * FROM student ORDER BY age DESC,id

#查询女生的最大年龄
SELECT NAME,MAX(age) FROM student WHERE sex='女'

#查询北京学生的年龄总和
SELECT SUM(age) FROM student WHERE hometown='北京'

#查询各种性别的人数
SELECT sex,COUNT(sex) FROM student GROUP BY sex

#查询男生总人数
SELECT sex,COUNT(sex) FROM student WHERE sex='男'