mysql命令练习

表结构

学生表

CREATE TABLE xsb (
xh INT(3) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
xm VARCHAR(20) NOT NULL,
xb CHAR(5),
jg VARCHAR(50),
nl TINYINT(3) UNSIGNED,
zcrq DATETIME DEFAULT NOW(),
bj VARCHAR(20),
sfzh CHAR(28) UNIQUE KEY
)CHARSET UTF8;

课程表

CREATE TABLE kcb (
kcm VARCHAR(20) NOT NULL,
kch INT(3) UNSIGNED ZEROFILL AUTO_INCREMENT PRIMARY KEY
)CHARSET UTF8;

成绩表

CREATE TABLE cjb(
xh INT(3) UNSIGNED,
kch INT(3) UNSIGNED ZEROFILL,
cj INT UNSIGNED,
FOREIGN KEY (xh) REFERENCES xsb (xh),
FOREIGN KEY (kch) REFERENCES kcb (kch)
)CHARSET UTF8;

表数据

学生表数据

INSERT xsb (xm,xb,jg,nl,zcrq,bj,sfzh) VALUES
(“张三”,“男”,“北京”,26,“1996-9-1 0:00”,“1班”,“110108198509012000”),
(“李四”,“男”,“河北”,20,“1999-9-1 0:00”,“2班”,“223108199012012000”),
(“王三”,“女”,“山东”,28,“2000-9-1 0:00”,“1班”,“36102319851014720X”),
(“张强”,“男”,“北京”,26,“2002-9-1 0:00”,“3班”,“150623198904260000”),
(“刘一志”,“男”,“上海”,23,“1999-9-1 0:00”,“1班”,“23070319860614070X”),
(“马力强”,“男”,“新疆”,20,“1994-9-1 0:00”,“3班”,“610502199201237000”),
(“赵辉”,“男”,“广西”,18,“2009-9-1 0:00”,“2班”,“621100198502217000”),
(“王静”,“女”,“北京”,20,“1991-9-1 0:00”,“2班”,“152528198805132000”),
(“张慧”,“女”,“湖北”,19,“2012-9-1 0:00”,“1班”,“520327198505224000”),
(“刘霞”,“女”,“河南”,28,“1989-9-1 0:00”,“3班”,“370602197608308000”),
(“王虎”,“男”,“新疆”,27,“11985-9-1 0:00”,“1班”,“652923197506026000”),
(“张三四”,“男”,“北京”,29,“2000-9-1 0:00”,“3班”,“371422199407106000”),
(“郑茜”,“女”,“上海”,22,“2014-9-1 0:00”,“2班”,“360281197504272000”),
(“刘丽”,“女”,“河北”,19,“1996-9-1 0:00”,“1班”,“522601198001260000”),
(“郑志强”,“男”,“山东”,21,“1996-9-1 0:00”,“1班”,“440511199308168000”),
(“何野”,“女”,“河北”,25,“1997-9-1 0:00”,“2班”,“51070319860323170X”),
(“贾强业”,“男”,“河南”,18,“2002-9-1 0:00”,“3班”,“43081119820706786X”),
(“刘贤”,“女”,“山西”,20,“2009-9-1 0:00”,“1班”,“410203198311308000”),
(“刘艳丽”,“女”,“北京”,24,“1996-9-1 0:00”,“3班”,“110105199901012000”),
(“李华强”,“男”,“山西”,22,“1994-9-1 0:00”,“1班”,“510923197712016000”);

课程表数据

INSERT kcb VALUES
(“高等数学”,1),
(“数据库技术”,2),
(“测试管理”,3),
(“Linux基础”,4),
(“自动化简介”,5)

成绩表数据

INSERT cjb VALUES
(1,001,90),
(1,002,60),
(1,003,77),
(1,004,87),
(1,005,50),
(2,001,22),
(2,002,65),
(2,003,100),
(2,004,70),
(2,005,60),
(3,001,32),
(3,002,55),
(3,003,40),
(3,004,89),
(3,005,45),
(4,002,70),
(4,003,96),
(4,004,55),
(4,005,70),
(5,001,NULL),
(5,002,20),
(5,003,77),
(5,004,66),
(5,005,80),
(6,001,40),
(6,002,91),
(6,003,70),
(6,004,66),
(6,005,85),
(7,001,60),
(7,002,71),
(7,003,30),
(7,004,96),
(7,005,40),
(8,001,20),
(8,002,70),
(8,004,100),
(8,005,68),
(9,001,33),
(9,002,74),
(9,003,29),
(9,004,69),
(9,005,80),
(10,001,58),
(10,002,60),
(10,003,59),
(10,004,99),
(10,005,93),
(11,001,70),
(11,003,72),
(11,004,66),
(11,005,25),
(12,001,11),
(12,002,50),
(12,003,30),
(12,004,47),
(12,005,NULL),
(13,001,30),
(13,002,60),
(13,003,46),
(13,004,84),
(13,005,90),
(14,002,99),
(14,003,100),
(14,004,58),
(14,005,85),
(15,001,40),
(15,002,96),
(15,003,49),
(15,004,95),
(15,005,100),
(16,001,68),
(16,002,79),
(16,003,NULL),
(16,004,73),
(16,005,30),
(17,001,60),
(17,002,20),
(17,003,85),
(17,004,96),
(17,005,40),
(18,001,91),
(18,002,74),
(18,003,48),
(18,004,96),
(18,005,74),
(19,001,84),
(19,003,NULL),
(19,004,48),
(19,005,84),
(20,001,93),
(20,002,63),
(20,003,85),
(20,004,100),
(20,005,86);

命令练习

单表查询

SELECT * FROM xsb
#查询学生信息表 查询学生张三的全部基本信息
SELECT * FROM xsb WHERE xm=‘张三’;
#查询学生张三或李四的基本信息
SELECT * FROM xsb WHERE xm=‘张三’ OR xm=‘李四’;
#查询姓张的学生姓名、班级、年龄
SELECT xm,bj,nl FROM xsb WHERE xm LIKE ‘张%’;
SELECT xm,bj,nl FROM xsb WHERE xm LIKE ‘张_’;
SELECT xm,bj,nl FROM xsb WHERE xm REGEXP ‘[张李王]三’;
#查询学生信息表，姓名中含有四的学生基本信息
SELECT*FROM xsb WHERE xm LIKE ‘%四%’;
#查询学生信息表，姓名长度为三个字，姓李，且最后一个字是 强 字的全部学生学号，姓名，年龄，班级，身份证号码
SELECT xh,xm,nl,bj,sfzh FROM xsb WHERE xm LIKE ‘李_强’;
#查询姓张或者姓李的学生基本信息
SELECT *FROM xsb WHERE xm REGEXP ‘[张李]’;
SELECT * from xsb WHERE xm LIKE ‘张%’ OR xm LIKE ‘李%’;
#查询姓张且在北京的学生信息
SELECT * FROM xsb WHERE xm LIKE ‘张%’ AND jg = ‘北京’;
#查询所属省份是北京 新疆 山东 或者上海的学生信息
SELECT * FROM xsb WHERE jg=‘北京’ OR jg=‘新疆’ OR jg=‘山东’ OR jg=‘上海’;
SELECT * FROM xsb WHERE jg in(‘北京’,‘新疆’,‘山东’,‘上海’);
#查询姓张，但是所属省份不是北京的学生信息
SELECT * FROM xsb WHERE xm LIKE’张%’ AND jg!=‘北京’;
SELECT * FROM xsb WHERE xm LIKE’张%’ AND jg not in (‘北京’);
#查询不是姓张的，且所属省份是河北的学生信息
SELECT * FROM xsb WHERE xm NOT LIKE ‘张%’ AND jg=‘河北’;
#查询所属省份不是北京 新疆 山东 上海的学生信息
SELECT * FROM xsb WHERE jg NOT IN (‘北京’,‘新疆’,‘山东’,‘上海’);
#查询现有的学生都来自于哪些不同的省份
SELECT xm,jg FROM xsb ORDER BY jg ;
SELECT xm,jg FROM xsb GROUP BY xm;
#统计学生信息表 ，共有多少个学生
SELECT COUNT(distinct xm) FROM xsb;
#统计年龄大于20岁的学生共有多少个
SELECT COUNT(nl) FROM xsb WHERE nl > 20;
#注册日期在1998到2000年的学生人数
SELECT COUNT(*) FROM xsb WHERE zcrq > ‘1998-01-01 00:00:00’ AND zcrq < ‘2000-12-31 23:59:59’;
#统计学号为001的学生的平均成绩
SELECT * FROM cjb;
SELECT xh,AVG(cj) FROM cjb WHERE xh=1;
#学号为001的学生总成绩
SELECT SUM(cj) FROM cjb WHERE kch = 001;
#查询课程号为001的课程最高成绩
SELECT MAX(cj) FROM cjb;
#查询所有学生 中最大年龄是多少
SELECT MAX(nl) FROM xsb;
#查询全部学生信息，并按照性别排序
SELECT * FROM xsb ORDER BY xb;
#查询没有填写成绩的学生学号，课程号和成绩，并按照学号升序排序
SELECT * FROM cjb WHERE cj IS NULL ORDER BY xh;
#查询填写成绩的选修信息，按照成绩从高到低排序
SELECT * FROM cjb WHERE cj IS NOT NULL ORDER BY cj DESC;
#统计每个课程的选修人数
SELECT kch,COUNT(kch) FROM cjb GROUP BY kch;
#统计每个同学的总成绩和学号
SELECT xh,SUM(cj) FROM cjb GROUP BY xh;
#统计每个班级每种性别的学生人数，按照班级排序
SELECT SUM(CASE WHEN xb=‘男’ THEN 1 ELSE 0 END ) AS 男,sum(CASE WHEN xb=‘女’ THEN 1 ELSE 0 END ) AS 女,bj FROM xsb GROUP BY bj;
#统计每个课程的平均成绩，按照平均成绩降序排序
SELECT kch,AVG(cj) FROM cjb GROUP BY kch ORDER BY AVG(cj) DESC;
#显示有两门以上课程不及格的学生学号
SELECT COUNT(xh) as qqq,xh FROM cjb WHERE cj<60 GROUP BY xh HAVING qqq>=2;
#统计每个班级中最大年龄是多少
SELECT bj,MAX(nl) FROM xsb GROUP BY bj;

多表查询

– 一、嵌套查询练习
– 1、用子查询实现，查询选修“高等数学”课的全部学生的总成绩
SELECT SUM(cj) FROM cjb WHERE kch=(SELECT kch FROM kcb WHERE kch=1);

– 2、用子查询实现，统计<学生选修信息表>，显示学号为"1"的学生在其各科成绩中，最高分成绩所对应的课程
SELECT kcm FROM kcb WHERE kch=(SELECT kch FROM cjb WHERE xh = 1 HAVING MAX(cj));

– 3、用子查询实现，查询2班选修"数据库技术"课的所有学生的成绩之和
SELECT kch FROM kcb WHERE kcm=“数据库技术”;
SELECT xh FROM xsb WHERE bj=1;
SELECT SUM(cj) FROM cjb WHERE kch = (SELECT kch FROM kcb WHERE kcm=“数据库技术”) AND xh in (SELECT xh FROM xsb WHERE bj=‘2班’);

– 4、用子查询实现，查询1班"张三"同学的"测试管理"成绩
SELECT kch FROM kcb WHERE kcm=“测试管理”;
SELECT xh FROM xsb WHERE xm=‘张三’ AND bj=‘1班’;
SELECT xh,cj FROM cjb WHERE kch=(SELECT kch FROM kcb WHERE kcm=“测试管理”) AND xh=(SELECT xh FROM xsb WHERE xm=‘张三’ AND bj=‘1班’);

– 二、等值连接查询练习
– 等值连接（连接条件中的运算符为“=”）
– SELECT * FROM Student,SC where (Student.Sno=SC.Sno);

– 1、查询"张三"的各科考试成绩，要求显示姓名、课程号和成绩

SELECT xsb.xm,cjb.cj,cjb.kch FROM xsb,cjb WHERE (xsb.xh=cjb.xh) AND xsb.xm=‘张三’;
– 2、查询"张三"的各科考试成绩中，哪科没有记录考试成绩，要求显示姓名、课程号和成绩
SELECT xsb.xm,cjb.cj,cjb.kch FROM xsb,cjb WHERE (xsb.xh=cjb.xh) AND xsb.xm=‘刘一志’ AND cjb.cj IS NULL;
– 3、查询不及格科数在两门以上的同学姓名以及对应的不及格的科目数量
SELECT COUNT(xh),xh FROM cjb WHERE cj<60 GROUP BY xh HAVING COUNT(xh) >=2;
SELECT xm FROM xsb WHERE xh in (SELECT xh FROM cjb WHERE cj<60 GROUP BY xh HAVING COUNT(xh) >=2);

SELECT COUNT(xh),xh FROM cjb WHERE cj<60 GROUP BY xh HAVING COUNT(xh) >=2;
SELECT xh FROM cjb WHERE cj<60 GROUP BY xh HAVING COUNT(xh) >=2;
SELECT xsb.xm,COUNT(xsb.xm) FROM xsb,cjb WHERE xsb.xh in (SELECT xh FROM cjb WHERE cj<60 GROUP BY xh HAVING COUNT(xh) >=2) AND xsb.xh=cjb.xh AND cjb.cj<60 GROUP BY xsb.xm;

– 1、等值连接
– 查询所有学生的课程成绩，并显示学号、姓名、课程号和成绩。
SELECT xsb.xh,xsb.xm,cjb.kch,cjb.cj FROM xsb,cjb WHERE xsb.xh=cjb.xh;

– 2、内连接
– 查询“张三”的课程成绩，并显示学号、姓名、课程号和成绩。
SELECT xsb.xh,xsb.xm,cjb.kch,cjb.cj FROM xsb INNER JOIN cjb ON xsb.xh=cjb.xh WHERE xsb.xm=‘张三’;

– 3、使用左联接
– 查询所有学生的课程成绩，并显示学号、姓名、课程号和成绩，包括没有选择课程的学生。
SELECT xsb.xm,xsb.xh,cjb.kch,cjb.cj FROM cjb LEFT JOIN xsb ON xsb.xh=cjb.xh;

– 4、使用右联接
– 查询所有学生的课程成绩，并显示学号、姓名、课程号和成绩，包括没有选择课程的学生。

SELECT xsb.xm,xsb.xh,cjb.kch,cjb.cj FROM cjb RIGHT JOIN xsb ON xsb.xh=cjb.xh;
– 查询每个同学的总成绩，要求显示学生姓名以及对应的总成绩
SELECT xsb.xm,SUM(cjb.cj) FROM xsb RIGHT JOIN cjb ON xsb.xh=cjb.xh GROUP BY xsb.xm;
– 查询平均分高于70的同学信息
SELECT * FROM xsb RIGHT JOIN cjb ON xsb.xh=cjb.xh GROUP BY xm HAVING AVG(cj)>70;