MySQL约束，多表查询-优快云博客

本文链接：https://blog.youkuaiyun.com/2201_75643669/article/details/146455325

一、添加约束

在之前，我们创建了一个数据库db_ck,同时创建了一个表t_hero

可以看到只设置一个主键约束，现在我们为这个表添加几个其他约束。

修改列类型
   ALTER TABLE 表名 MODIFY 列名列类型; -- 注意存在值的情况，类型不一定能成功
增加列
   ALTER TABLE 表名 ADD 列名列类型;
删除列
   ALTER TABLE 表名 DROP 列名;
列改名
   ALTER TABLE 表名 CHANGE 旧列名新列名列类型;
更改表名
   ALTER TABLE 表名 RENAME 新表名;
   RENAME TABLE 表名 TO 新表名;

1.给name设置唯一约束

alter table t_hero modify name varchar(50) unique；

2.给novel设置非空约束

alter table t_hero modify novel varchar(100) not null;

二、练习

1.利用count(*)统计共有多少行数据，同时用别名as

select count(*) as 总 from t_hero;

2.使用WHERE子句限定返回的记录

（1）

select * from t_hero where novel = "红楼梦";

（2）

select * from t_hero where id <=3;

（3）

select * from t_hero where id between 10 and 15;

（4）

select * from t_hero where id in (1,3,5,7,11);

（5）

select * from t_hero where name like "_悟%";

(6)

select * from t_hero where alias is null;

（7）

select * from t_hero where id in (15, 16, 18) and novel = "红楼梦";

（8）

select * from t_hero where id <= 10 or novel = "红楼梦";

（9）

select * from t_hero where novel not in ("三国演义","红楼梦","西游记");

3.分组查询

（1）

select novel, count(*) from t_hero group by novel;

（2）

select novel, count(*) from t_hero group by novel having novel in ("水浒传","西游记");

4.排序

我先给t_hero表添加一个age列表示年龄，同时插入数据。

alter table t_hero add age int;

UPDATE t_hero SET age = 47 WHERE id = 1;
UPDATE t_hero SET age = 42 WHERE id = 2;
UPDATE t_hero SET age = 38 WHERE id = 3;
UPDATE t_hero SET age = 27 WHERE id = 4;
UPDATE t_hero SET age = 53 WHERE id = 5;
UPDATE t_hero SET age = 35 WHERE id = 6;
UPDATE t_hero SET age = 30 WHERE id = 7;
UPDATE t_hero SET age = 35 WHERE id = 8;
UPDATE t_hero SET age = 28 WHERE id = 9;
UPDATE t_hero SET age = 25 WHERE id = 10;
UPDATE t_hero SET age = 500 WHERE id = 11;
UPDATE t_hero SET age = 25 WHERE id = 12;
UPDATE t_hero SET age = 1000 WHERE id = 13;
UPDATE t_hero SET age = 1000 WHERE id = 14;
UPDATE t_hero SET age = 300 WHERE id = 15;
UPDATE t_hero SET age = 15 WHERE id = 16;
UPDATE t_hero SET age = 13 WHERE id = 17;
UPDATE t_hero SET age = 16 WHERE id = 18;
UPDATE t_hero SET age = 25 WHERE id = 19;
UPDATE t_hero SET age = 14 WHERE id = 20;

（1）

select * from t_hero order by age, id desc;

5.分页

从第5条记录开始查4条数据

select * from t_hero limit 5,4;

三、题目

1.单表查询

素材

CREATE TABLE worker (
    `部门号` int(11) NOT NULL,
    `职工号` int(11) NOT NULL,
    `工作时间` date NOT NULL,
    `工资` float(8,2) NOT NULL,
    `政治面貌` varchar(10) NOT NULL DEFAULT '群众',
    `姓名` varchar(20) NOT NULL,
    `出生日期` date NOT NULL,
    PRIMARY KEY (`职工号`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) 
VALUES (101, 1001, '2015-5-4', 3500.00, '群众', '张三', '1990-7-1');

INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) 
VALUES (101, 1002, '2017-2-6', 3200.00, '团员', '李四', '1997-2-8');

INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) 
VALUES (102, 1003, '2011-1-4', 8500.00, '党员', '王亮', '1983-6-8');

INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) 
VALUES (102, 1004, '2016-10-10', 5500.00, '群众', '赵六', '1994-9-5');

INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) 
VALUES (102, 1005, '2014-4-1', 4800.00, '党员', '钱七', '1992-12-30');

INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) 
VALUES (102, 1006, '2017-5-5', 4500.00, '党员', '孙八', '1996-9-2');

1、显示所有职工的基本信息。

select * from worker;

2、查询所有职工所属部门的部门号，不显示重复的部门号。

select distinct `部门号` from worker;

3、求出所有职工的人数。

select count(*) as '总人数' from worker;

4、列出最高工和最低工资。

select max(工资),min(工资) from worker;

5、列出职工的平均工资和总工资。

select avg(工资),sum(工资) from worker;

6、创建一个只有职工号、姓名和参加工作的新表，名为工作日期表。

create table worke_day select 职工号,姓名,工作时间 from worker;

7、显示李四出生日期

select 出生日期 from worker where 姓名='李四';

8、列出所有姓孙的职工的职工号、姓名和出生日期。

select 职工号,姓名,出生日期 from worker where 姓名 like '孙%';

9、列出1990年以前出生的职工的姓名、参加工作日期。

select 姓名,工作时间 from worker where 出生日期 < '1990-01-01';

10、列出工资在3000－5000之间的所有职工姓名。

select 姓名 from worker where 工资 betweent 3000 and 5000;

11、列出所有王姓和赵姓的职工姓名。

select 姓名 from worker where 姓名 like '王%' or 姓名 like '赵%';

12、列出所有部门号为102和103的职工号、姓名、政治面貌。

select 职工号,姓名,政治面貌 from worker where 部门号 in (102,103);

13、将职工表worker中的职工按出生的先后顺序排序。

select * from worker order by 出生日期;

14、显示工资最高的前3名职工的职工号和姓名。

select 职工号,姓名 from worker order by 工资 desc limit 3;

15、求出各部门党员的人数。

select 部门号,count(*) as 人数 from worker where 政治面貌='党员' group by 部门号;

16、统计各部门的总工资和平均工资

select 部门号,sum(工资),avg(工资) from worker group by 部门号;

17、列出总人数大于等于4的部门号和总人数。

select 部门号,count(*) as 总人数 from worker group by 部门号 having 总人数 >= 4;

2.多表查询

素材

创建student表

CREATE TABLE student (
    id INT(10) NOT NULL UNIQUE PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    sex VARCHAR(4),
    birth YEAR,
    department VARCHAR(20),
    address VARCHAR(50)
);

创建score表

CREATE TABLE score (
    id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
    stu_id INT(10) NOT NULL,
    c_name VARCHAR(20),
    grade INT(10)
);

插入学生数据

INSERT INTO student VALUES (901, '张老大', '男', 1985, '计算机系', '北京市海淀区');
INSERT INTO student VALUES (902, '张老二', '男', 1986, '中文系', '北京市昌平区');
INSERT INTO student VALUES (903, '张三', '女', 1990, '中文系', '湖南省永州市');
INSERT INTO student VALUES (904, '李四', '男', 1990, '英语系', '辽宁省阜新市');
INSERT INTO student VALUES (905, '王五', '女', 1991, '英语系', '福建省厦门市');
INSERT INTO student VALUES (906, '王六', '男', 1988, '计算机系', '湖南省衡阳市');

插入成绩

INSERT INTO score VALUES (NULL, 901, '计算机', 98);
INSERT INTO score VALUES (NULL, 901, '英语', 80);
INSERT INTO score VALUES (NULL, 902, '计算机', 65);
INSERT INTO score VALUES (NULL, 902, '中文', 88);
INSERT INTO score VALUES (NULL, 903, '中文', 95);
INSERT INTO score VALUES (NULL, 904, '计算机', 70);
INSERT INTO score VALUES (NULL, 904, '英语', 92);
INSERT INTO score VALUES (NULL, 905, '英语', 94);
INSERT INTO score VALUES (NULL, 906, '计算机', 90);
INSERT INTO score VALUES (NULL, 906, '英语', 85);

1.查询student表的所有记录

select * from student;

2.查询student表的第2条到4条记录

select * from student limit 1,3;

3.从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息

select id,name,department from student;

4.从student表中查询计算机系和英语系的学生的信息

select * from student where department in ("计算机系","英语系");

5.从student表中查询年龄35~40岁的学生信息

select * from student where year(curdate()) - birth between 35 and 40;

6.从student表中查询每个院系有多少人

select department,count(*) as 总人数 from student group by department;

7.从score表中查询每个科目的最高分

select c_name,max(grade) from score group by c_name;

8.查询李四的考试科目（c_name）和考试成绩（grade）

select c_name,grade from student,score where student.id = score.stu_id and name = "李四";

9.用连接的方式查询所有学生的信息和考试信息

select * from student,score where score.stu_id = student.id;

10.计算每个学生的总成绩

select stu_id,sum(grade) from score group by stu_id;

11.计算每个考试科目的平均成绩

select c_name,avg(grade) from score group by c_name;

12.查询计算机成绩低于95的学生信息

select * from student,score where student.id=score.stu_id and c_name="计算机" and grade <95;

13.查询同时参加计算机和英语考试的学生的信息

 select * from student where id in  (select stu_id from score where c_name in ("计算机","英语") group by stu_id having count( distinct c_name) = 2);

14.将计算机考试成绩按从高到低进行排序

select grade from score where c_name="计算机" order by grade desc;

15.从student表和score表中查询出学生的学号，然后合并查询结果

(select id ID from student) union (select stu_id ID from score);

16.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩

select name,department,c_name,grade from student inner join score on student.id=score.stu_id where name like "王%" or name like "张%";

17.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩

select name,year(curdate())-birth,department,c_name,grade from student inner join score on student.id = score.stu_id where address like "%湖南%";