MYSQL复习

2019-08-22 本文已影响0人 Eren_Jaeger

mysql数据库学习

-- mysql里面的数据类型
-- 数值
-- 字符串
-- 日期

-- 创建数据库？
create database 数据库名称;

mysql> create database study;
Query OK, 1 row affected (0.00 sec)

-- 创建表？
create table 表名(
字段数据类型,
字段数据类型,
字段数据类型,
);

mysql> create table class(
-> name VARCHAR(20),
-> sex CHAR(10),
-> age CHAR(10),
-> date VARCHAR(20))
-> ;
Query OK, 0 rows affected (0.45 sec)

mysql> show tables;
+-----------------+
| Tables_in_study |
+-----------------+
| class |
+-----------------+
1 row in set (0.00 sec)

-- 如何向数据表中添加数据？
mysql> insert into class VALUES ('liuning','men','25','2019-06-25');
Query OK, 1 row affected (0.40 sec)
mysql> select * from class;
+---------+------+------+------------+
| name | sex | age | date |
+---------+------+------+------------+
| liuning | men | 25 | 2019-06-25 |
+---------+------+------+------------+
1 row in set (0.00 sec)

-- 如何修改表中的数据？
update 表名 set date='2019-07-08' where date='2014'

mysql> update class set date='2016-12-12' where date='2014';
Query OK, 1 row affected (0.44 sec)
Rows matched: 1 Changed: 1 Warnings: 0

-- 如何删除表中的数据？
delete from 表名 where date='1992';

mysql> delete from class where date='1992';
Query OK, 1 row affected (0.42 sec)

mysql> select * from class;
+----------+-------+------+------------+
| name | sex | age | date |
+----------+-------+------+------------+
| liuning | men | 25 | 2019-06-25 |
| zhangsan | women | 18 | 2019-01-31 |
| lisi | men | 10 | 2019-02-02 |
| wangwu | men | 30 | 2016-12-12 |
+----------+-------+------+------------+
4 rows in set (0.00 sec)

-- 如何在表中增加一个字段？
alter table 表名 add column 字段名数据类型;

mysql> alter table class add column hobby varchar(20);
Query OK, 0 rows affected (0.50 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from class;
+----------+-------+------+------------+-------+
| name | sex | age | date | hobby |
+----------+-------+------+------------+-------+
| liuning | men | 25 | 2019-06-25 | NULL |
| zhangsan | women | 18 | 2019-01-31 | NULL |
| lisi | men | 10 | 2019-02-02 | NULL |
| wangwu | men | 30 | 2016-12-12 | NULL |
+----------+-------+------+------------+-------+
4 rows in set (0.00 sec)

-- 如何在表中更改字段？
alter table 表名 change 旧字段名新字段名新数据类型；

mysql> alter table class change hobby myfavorite varchar(20);
Query OK, 0 rows affected (0.47 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from class;
+----------+-------+------+------------+------------+
| name | sex | age | date | myfavorite |
+----------+-------+------+------------+------------+
| liuning | men | 25 | 2019-06-25 | NULL |
| zhangsan | women | 18 | 2019-01-31 | NULL |
| lisi | men | 10 | 2019-02-02 | NULL |
| wangwu | men | 30 | 2016-12-12 | NULL |
+----------+-------+------+------------+------------+
4 rows in set (0.00 sec)

-- modify与change的区别
相同点：
虽然两者都用来更改表结构
不同点：
modify可以修改表中的字段类型，但无法更改字段名字
change既可以修改表中字段名，又可以更改字段的数据类型

-- 如何修改表中的字段类型？
alter table 表名 modify column 字段名数据类型;

mysql> alter table class modify column myfavorite char(30);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0

-- mysql建表约束？
-- 主键约束唯一并且不能为NULL, primary key
-- 联合主键由多个主键构成，要求多个主键加起来的值唯一，并且任何一个主键不为NULL
-- 自增约束可以实现自增长，一般是在主键的基础上自增长，auto_increment
-- 外键约束分为主表和附表，附表中使用的数据必须在主表中存在，并且主表中的数据在附表中使用时无法删除
-- 唯一约束约束该字段的值不可以重复
-- 非空约束修饰的字段不可以为NULL
-- 默认约束该字段不填写，会有一个默认值

-- 如何创建主键约束？
create table 表名 (
字段名数据类型 primary key,
字段名数据类型,
字段名数据类型,
);

mysql> create table company (
-> id int primary key,
-> name varchar(20),
-> age int,
-> sex varchar(10),
-> job varchar(30)
-> );
Query OK, 0 rows affected (0.50 sec)

mysql> describe company;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| sex | varchar(10) | YES | | NULL | |
| job | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

-- 如何创建联合主键？
create table 表名 (
字段名称数据类型,
字段名称数据类型，
字段名称数据类型，
primary key(字段名称，字段名称)
);

mysql> create table grade (
-> id int,
-> name varchar(20),
-> grade int,
-> primary key (id, name)
-> );
Query OK, 0 rows affected (0.43 sec)

mysql> describe grade;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | NO | PRI | NULL | |
| grade | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

-- 如何创建自增主键？
create table 表名 (
字段数据类型 primary key auto_increment,
字段数据类型,
字段数据类型,
字段数据类型
);

mysql> create table student (
-> id int primary key auto_increment,
-> name varchar(20),
-> age int,
-> hobby varchar(20)
-> );
Query OK, 0 rows affected (0.47 sec)

mysql> describe student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| hobby | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> insert into student values (1,'liuning', 25, 'LOL');
Query OK, 1 row affected (0.43 sec)

mysql> insert into student (name, age, hobby) values ('lisi', 16, 'playgames');
Query OK, 1 row affected (0.48 sec)

mysql> select * from student;
+----+---------+------+-----------+
| id | name | age | hobby |
+----+---------+------+-----------+
| 1 | liuning | 25 | LOL |
| 2 | lisi | 16 | playgames |
+----+---------+------+-----------+
2 rows in set (0.00 sec)

-- 创建表的时候忘记添加主键约束？修改表结构，添加主键。
alter table 表名 add primary key (字段名);

mysql> create table student1 (
-> id int,
-> name varchar(20),
-> sex varchar(10),
-> grade int(10)
-> );
Query OK, 0 rows affected (0.48 sec)

mysql> alter table student1 add primary key (id);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> describe student1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| sex | varchar(10) | YES | | NULL | |
| grade | int(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

-- 通过修改表结构字段添加约束？
alter table 表名 modify 字段名数据类型 primary key;

mysql> alter table student1 modify id int primary key auto_increment;
Query OK, 0 rows affected (0.43 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> describe student1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| sex | varchar(10) | YES | | NULL | |
| grade | int(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+

-- 修改表结构，删除主键约束？
alter table 表名 drop primary key;

mysql> alter table student1 drop primary key;
Query OK, 0 rows affected (0.50 sec)
Records: 0 Duplicates: 0 Warnings: 0

-- 唯一约束
create table 表名 (
id int primary key auto_increment,
number int unique,
name varchar(20),
age int
);

alter table 表名 add unique (字段名);

alter table 表名 modify 字段数据类型 unique;

mysql> create table student2 (
-> id int primary key auto_increment,
-> study_number int unique,
-> name varchar(20),
-> grade int,
-> hobby varchar(20)
-> );
Query OK, 0 rows affected (0.43 sec)

mysql> describe student2;
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| study_number | int(11) | YES | UNI | NULL | |
| name | varchar(20) | YES | | NULL | |
| grade | int(11) | YES | | NULL | |
| hobby | varchar(20) | YES | | NULL | |
+--------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> alter table student2 drop index study_number;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table student2 add unique (study_number);
Query OK, 0 rows affected (0.42 sec)
Records: 0 Duplicates: 0 Warnings: 0

-- 删除唯一约束？
alter table 表名 drop index 字段名;

mysql> alter table student2 drop index study_number;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

-- 非空约束
-- 添加非空约束
create table 表名 (
id int primary key auto_increment,
name varchar(20) not null,
sex varchar(10)
);

alter table 表名 modify 字段数据类型 not null;

alter table 表名 add not null (字段名);

mysql> create table student3 (
-> id int primary key auto_increment,
-> name varchar(20) not null,
-> sex varchar(10)
-> );
Query OK, 0 rows affected (0.47 sec)

mysql> insert into student3 values (1, NULL, 'men');
ERROR 1048 (23000): Column 'name' cannot be null

-- 删除非空约束
alter table 表名 modify 字段名数据类型;

mysql> alter table student3 modify name varchar(20);
Query OK, 0 rows affected (0.42 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table student3 change name name varchar(20) not null;
Query OK, 0 rows affected (0.48 sec)
Records: 0 Duplicates: 0 Warnings: 0

-- 创建默认约束
create table 表名 (
id int primary key auto_increment,
name varchar(10),
grade int default 100
);

mysql> create table student4 (
-> id int primary key auto_increment,
-> name varchar(10),
-> grade int default 100
-> );
Query OK, 0 rows affected (0.45 sec)

mysql> describe student4;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | YES | | NULL | |
| grade | int(11) | YES | | 100 | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> insert into student4 (id, name) values (3, 'liuning');
Query OK, 1 row affected (0.43 sec)

mysql> insert into student4 values (2, 'xiaoming', 88);
Query OK, 1 row affected (0.43 sec)

mysql> select * from student4;
+----+----------+-------+
| id | name | grade |
+----+----------+-------+
| 2 | xiaoming | 88 |
| 3 | liuning | 100 |
+----+----------+-------+
2 rows in set (0.00 sec)

-- 外键约束
-- 涉及到主表和副表
-- 主表中没有的数据，在附表中是不可以使用的
-- 如果主表中有附表中在使用的数据，那么主表中的该列数据不可删除

-- 外键约束的创建
create table 表名 (
id int primary key auto_increment,
classname varchar(10)
);

create table 表名 (
study_number int primary key,
name varchar(20),
sex varchar(10),
grade int,
class_number int,
foreign key (class_number) references class(id)
);

mysql> insert into class values (1, '物联网应用技术');
Query OK, 1 row affected (0.00 sec)

mysql> insert into class (id, classname) values (2, '通信技术');
Query OK, 1 row affected (0.00 sec)

mysql> insert into class (id, classname) values (3, '英雄联盟竞技班');
Query OK, 1 row affected (0.42 sec)

mysql> insert into class (id, classname) values (4, '事业有成混混班');
Query OK, 1 row affected (0.43 sec)

mysql> select * from class;
+----+-----------------------+
| id | classname |
+----+-----------------------+
| 1 | 物联网应用技术 |
| 2 | 通信技术 |
| 3 | 英雄联盟竞技班 |
| 4 | 事业有成混混班 |
+----+-----------------------+
4 rows in set (0.00 sec)

mysql> insert into student5 values (013013, 'liuning', 'men', 100, 1);
Query OK, 1 row affected (0.42 sec)

mysql> insert into student5 values (013014, 'jingjing', 'women', 96, 3);
Query OK, 1 row affected (0.42 sec)

mysql> insert into student5 values (013015, 'xiaowang', 'men', 69, 4);
Query OK, 1 row affected (0.44 sec)

mysql> insert into student5 values (013020, 'xiaoli', 'men', 66, 5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (study.student5, CONSTRAINT student5_ibfk_1 FOREIGN KEY (class_number) REFERENCES class (id))

mysql> describe student5;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| study_number | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| sex | varchar(10) | YES | | NULL | |
| grade | int(11) | YES | | NULL | |
| class_number | int(11) | YES | MUL | NULL | |
+--------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> select * from student5;
+--------------+----------+-------+-------+--------------+
| study_number | name | sex | grade | class_number |
+--------------+----------+-------+-------+--------------+
| 13013 | liuning | men | 100 | 1 |
| 13014 | jingjing | women | 96 | 3 |
| 13015 | xiaowang | men | 69 | 4 |
+--------------+----------+-------+-------+--------------+
3 rows in set (0.00 sec)

-- 数据表设计
-- 1.字段值还可以再拆分的就不属于第一范式。这样可以有利于以后的查表
create table studnet (
id int primary key auto_increment,
name varchar(20),
country varchar(10),
previce varchar(10),
city varchar(10),
address varchar(10)
);
-- 2.前提是在满足第一范式的基础上，除了主键以外的其他字段要完全依赖于主键才可以，如果不满足则需要将表拆分。最好不要出现联合主键

-- 错误：
create table myorder (
myorder_id int,
product_id int,
customer_id int,
product_name varchar(10),
customer_name varchar(10)
primary key (myorder_id, product_id)
);
-- 正确：
create table myorder (
myorder_id int primary key,
product_id int,
customer_id int
);

create table product (
product_id int primary key,
product_name varchar(10)
);

create table customer (
customer_id int primary key,
customer_name varchar(10)
);

-- 3.在满足第二范式的基础上，除了主键以外的字段之间不可以有传递依赖关系
-- 错误：
create table myorder (
myorder_id int primary key,
product_id int,
customer_id int,
customer_phone
);
从这里看到customer_phone和customer_id还有传递依赖关系，所以不可以这样设计，应该拆分开来

-- 正确：
create table myorder (
myorder_id int primary key,
product_id int,
customer_id int
);

create table cutomer (
customer_id int primary key,
customer_name varchar(10),
customer_phone int
):

-- mysql数据表准备

-- 学生表
student
学号
姓名
性别
生日
所在班级

create table student (
sno varchar(20) primary key,
sname varchar(20) not null,
ssex varchar(10) not null,
sbirthday datetime,
class varchar(20)
);

-- 课程表
course
课程号
课程名称
教师编号

create table course (
cno varchar(20) primary key,
cname varchar(20) not null,
tno varchar(20) not null,
foreign key(tno) references teacher(tno)
);

-- 成绩表
score
学号
课程号
成绩

create table score (
sno varchar(20) not null,
cno varchar(20) not null,
degree decimal,
foreign key(sno) references student(sno),
foreign key(cno) references course(cno),
primary key(sno,cno)
);

-- 教师表
teacher
教师编号
教师名字
教师性别
生日
职称
所在部门

create table teacher (
tno varchar(20) primary key,
tname varchar(20) not null,
tsex varchar(10) not null,
tbrithday datetime,
prof varchar(20) not null,
depart varchar(20) not null
);

-- 查询练习
-- 1.查询student表里面的所有记录

mysql> select * from student;
+-----+-----------+------+---------------------+-------+
| sno | sname | ssex | sbirthday | class |
+-----+-----------+------+---------------------+-------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
+-----+-----------+------+---------------------+-------+

-- 2.查询student表里的所有记录的sname、ssex、class列

mysql> select sname,ssex,class from student;
+-----------+------+-------+
| sname | ssex | class |
+-----------+------+-------+
| 曾华 | 男 | 95033 |
| 匡明 | 男 | 95031 |
| 王丽 | 女 | 95033 |
| 李军 | 男 | 95033 |
| 王芳 | 女 | 95031 |
| 陆君 | 男 | 95031 |
| 王尼玛 | 男 | 95033 |
| 张全蛋 | 男 | 95031 |
| 赵铁柱 | 男 | 95031 |
+-----------+------+-------+
9 rows in set (0.00 sec)

-- 3.查询教师所有的单位即不重复的depart列
-- distinct 排重

-- 4.查询score表中成绩在60 - 80之间的所有记录

-- 方法1：
使用 where between 60 and 80;

mysql> select * from score;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+-----+-------+--------+
9 rows in set (0.00 sec)

mysql> select * from score where degree between 60 and 80;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
+-----+-------+--------+
4 rows in set (0.00 sec)

-- 方法2：
mysql> select * from score where degree > 60 and degree < 80;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
+-----+-------+--------+
4 rows in set (0.00 sec)

-- 5.查询score表中成绩为85，86或者88的记录
-- 表示同一字段的或者关系查询用in()
mysql> select * from score where degree in(85,86,88);
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
+-----+-------+--------+
3 rows in set (0.00 sec)

-- 6.查询student表中'95031'班或性别为'女的同学记录
-- 不同字段之间的或者关系用or

mysql> select * from student where class = 95031 or ssex = '女';
+-----+-----------+------+---------------------+-------+
| sno | sname | ssex | sbirthday | class |
+-----+-----------+------+---------------------+-------+
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
+-----+-----------+------+---------------------+-------+
6 rows in set (0.00 sec)

-- 7.以class降序查询student表的所有记录
-- 这里升序降序就是条件，所以不需要再跟where
-- 升序 order by 。。。 asc;(默认升序)
-- 降序 order by 。。。 desc；

mysql> select * from student order by class desc;
+-----+-----------+------+---------------------+-------+
| sno | sname | ssex | sbirthday | class |
+-----+-----------+------+---------------------+-------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
+-----+-----------+------+---------------------+-------+
9 rows in set (0.00 sec)

-- 8.以cno升序、degree降序查询score表的所有记录

mysql> select * from score order by cno asc, degree desc;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-105 | 92 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
| 103 | 3-245 | 86 |
| 105 | 3-245 | 75 |
| 109 | 3-245 | 68 |
| 103 | 6-166 | 85 |
| 109 | 6-166 | 81 |
| 105 | 6-166 | 79 |
+-----+-------+--------+
9 rows in set (0.00 sec)

-- 9.查询95031班的学生人数
-- 统计使用count()

mysql> select count() from student where class = 95031;
+----------+
| count() |
+----------+
| 5 |
+----------+
1 row in set (0.00 sec)

-- 10.查询score表中最高分的学生号和课程号

-- 方法1：
-- 此查询可以分解为以下：
1.查询degree中的最大值
2.根据where条件语句寻找最大值的学生号和课程号

mysql> select sno,cno from score where degree=(select max(degree) from score);
+-----+-------+
| sno | cno |
+-----+-------+
| 103 | 3-105 |
+-----+-------+
1 row in set (0.00 sec)

-- 方法2：
-- 先使用order by排序，然后选取第一条数据
-- limit 0,1 第一个数值代表从哪开始，第二个数字代表选取几条数据

mysql> select sno,cno from score order by degree desc limit 0,1;
+-----+-------+
| sno | cno |
+-----+-------+
| 103 | 3-105 |
+-----+-------+
1 row in set (0.00 sec)

-- 11.查询每门课的平均成绩
-- avg()可以计算某字段的平均值

mysql> select avg(degree) from score where cno = '3-105';
+-------------+
| avg(degree) |
+-------------+
| 85.3333 |
+-------------+
1 row in set (0.00 sec)

mysql> select * from score where cno = '3-105';
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-105 | 92 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
+-----+-------+--------+
3 rows in set (0.00 sec)

-- 如果要查询多门课的平均成绩就要依次去写，以下例子：
mysql> select * from score where cno = '3-105';
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-105 | 92 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
+-----+-------+--------+
3 rows in set (0.00 sec)

mysql> select * from score where cno = '3-245';
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-245 | 86 |
| 105 | 3-245 | 75 |
| 109 | 3-245 | 68 |
+-----+-------+--------+
3 rows in set (0.00 sec)

mysql> select * from score where cno = '6-166';
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 6-166 | 85 |
| 105 | 6-166 | 79 |
| 109 | 6-166 | 81 |
+-----+-------+--------+
3 rows in set (0.00 sec)

mysql> select * from score where cno = '9-888';
Empty set (0.00 sec)

-- 是否可以在一个sql语句中计算出多个课程的平均成绩呢，答案是可以的，以下例子：
-- 这里用到了group by 字段名,根据字段名分组查询
mysql> select cno,avg(degree) from score group by cno;
+-------+-------------+
| cno | avg(degree) |
+-------+-------------+
| 3-105 | 85.3333 |
| 3-245 | 76.3333 |
| 6-166 | 81.6667 |
+-------+-------------+
3 rows in set (0.00 sec)

-- 12.查询score表中至少有2名学生选修的并以3开头的课程的平均分数
-- having字句可以让我们筛选成组后的各种数据
-- count()统计数值
-- and 多个条件同时成立
-- like配合%进行模糊匹配

-- 这里先使用group by分组处理
-- 再使用having count(cno) >= 2来挑选2名学生以上选修的课程,
-- 最后使用like进行模糊匹配

mysql> select cno from score group by cno having count(cno) >= 2 and cno like '3%';
+-------+
| cno |
+-------+
| 3-105 |
| 3-245 |
+-------+
2 rows in set (0.00 sec)

-- 13.查询分数大于70小于90的sno列

-- 方法1：
-- 使用between and语句
mysql> select sno,degree from score where degree between 70 and 90;
+-----+--------+
| sno | degree |
+-----+--------+
| 103 | 86 |
| 103 | 85 |
| 105 | 88 |
| 105 | 75 |
| 105 | 79 |
| 109 | 76 |
| 109 | 81 |
+-----+--------+
7 rows in set (0.00 sec)

-- 方法2：
-- 使用 and进行条件整合
mysql> select sno,degree from score where degree >70 and degree < 90;
+-----+--------+
| sno | degree |
+-----+--------+
| 103 | 86 |
| 103 | 85 |
| 105 | 88 |
| 105 | 75 |
| 105 | 79 |
| 109 | 76 |
| 109 | 81 |
+-----+--------+
7 rows in set (0.00 sec)

-- 14.多表查询-查询所有学生的sname、cno、degree列
-- 多表查询的方式是在where语句后面跟上两表之间共有的字段使其相等
mysql> select sname,cno,degree from student,score where student.sno=score.sno;
+-----------+-------+--------+
| sname | cno | degree |
+-----------+-------+--------+
| 王丽 | 3-105 | 92 |
| 王丽 | 3-245 | 86 |
| 王丽 | 6-166 | 85 |
| 王芳 | 3-105 | 88 |
| 王芳 | 3-245 | 75 |
| 王芳 | 6-166 | 79 |
| 赵铁柱 | 3-105 | 76 |
| 赵铁柱 | 3-245 | 68 |
| 赵铁柱 | 6-166 | 81 |
+-----------+-------+--------+
9 rows in set (0.00 sec)

-- 15.查询所有学生的sno、cname、degree列

mysql> select * from course;
+-------+-----------------+-----+
| cno | cname | tno |
+-------+-----------------+-----+
| 3-105 | 计算机导论 | 825 |
| 3-245 | 操作系统 | 804 |
| 6-166 | 数字电路 | 856 |
| 9-888 | 高等数学 | 831 |
+-------+-----------------+-----+
4 rows in set (0.00 sec)

mysql>
mysql> select * from score;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+-----+-------+--------+
9 rows in set (0.00 sec)

mysql> select sno, cname, degree from course,score where course.cno=score.cno;
+-----+-----------------+--------+
| sno | cname | degree |
+-----+-----------------+--------+
| 103 | 计算机导论 | 92 |
| 103 | 操作系统 | 86 |
| 103 | 数字电路 | 85 |
| 105 | 计算机导论 | 88 |
| 105 | 操作系统 | 75 |
| 105 | 数字电路 | 79 |
| 109 | 计算机导论 | 76 |
| 109 | 操作系统 | 68 |
| 109 | 数字电路 | 81 |
+-----+-----------------+--------+
9 rows in set (0.00 sec)

-- 16.查询所有学生的sname、cname、degree列 -- 3表查询
-- 三表查询有时候会需要用到两个条件，只需要用and条件合并起来即可

mysql> select sname,cname,degree from student,course,score where student.sno=score.sno and course.cno=score.cno;
+-----------+-----------------+--------+
| sname | cname | degree |
+-----------+-----------------+--------+
| 王丽 | 计算机导论 | 92 |
| 王丽 | 操作系统 | 86 |
| 王丽 | 数字电路 | 85 |
| 王芳 | 计算机导论 | 88 |
| 王芳 | 操作系统 | 75 |
| 王芳 | 数字电路 | 79 |
| 赵铁柱 | 计算机导论 | 76 |
| 赵铁柱 | 操作系统 | 68 |
| 赵铁柱 | 数字电路 | 81 |
+-----------+-----------------+--------+
9 rows in set (0.00 sec)

更多字段的查询：
mysql> select student.sno,sname,course.cno,cname,degree from student,course,score where student.sno=score.sno and course.cno=score.cno;
+-----+-----------+-------+-----------------+--------+
| sno | sname | cno | cname | degree |
+-----+-----------+-------+-----------------+--------+
| 103 | 王丽 | 3-105 | 计算机导论 | 92 |
| 103 | 王丽 | 3-245 | 操作系统 | 86 |
| 103 | 王丽 | 6-166 | 数字电路 | 85 |
| 105 | 王芳 | 3-105 | 计算机导论 | 88 |
| 105 | 王芳 | 3-245 | 操作系统 | 75 |
| 105 | 王芳 | 6-166 | 数字电路 | 79 |
| 109 | 赵铁柱 | 3-105 | 计算机导论 | 76 |
| 109 | 赵铁柱 | 3-245 | 操作系统 | 68 |
| 109 | 赵铁柱 | 6-166 | 数字电路 | 81 |
+-----+-----------+-------+-----------------+--------+
9 rows in set (0.00 sec)

-- 17.查询95031班学生每门课的平均成绩
-- 思路：
-- 先查询95031班的学生，提取其sno
-- 针对sno在score表中进行分组
-- 分组完成后再进行计算平均分

mysql> select cno from score where sno in(select sno from student where class=95031) group by cno;
+-------+
| cno |
+-------+
| 3-105 |
| 3-245 |
| 6-166 |
+-------+
3 rows in set (0.00 sec)

mysql>
mysql>
mysql> select cno,avg(degree) from score where sno in(select sno from student where class=95031) group by cno;
+-------+-------------+
| cno | avg(degree) |
+-------+-------------+
| 3-105 | 82.0000 |
| 3-245 | 71.5000 |
| 6-166 | 80.0000 |
+-------+-------------+
3 rows in set (0.00 sec)
-- group by用在where语句后面

-- 18.查询选修3-105成绩高于109号同学3-105成绩的所有同学的记录--子查询
-- 思路：
-- 先去查询到109号同学3-105科目的成绩
-- 再去设置degree条件大于其数值的条件去查表
-- 再去增加选修3-105课程的条件，使用and即可

mysql> select sno,degree from score where degree > (select degree from score where sno=109 and cno='3-105');
+-----+--------+
| sno | degree |
+-----+--------+
| 103 | 92 |
| 103 | 86 |
| 103 | 85 |
| 105 | 88 |
| 105 | 79 |
| 109 | 81 |
+-----+--------+
6 rows in set (0.00 sec)

mysql> select sno,degree from score where degree > (select degree from score where sno=109 and cno='3-105') and cno = '3-105';
+-----+--------+
| sno | degree |
+-----+--------+
| 103 | 92 |
| 105 | 88 |
+-----+--------+
2 rows in set (0.00 sec)

-- 19.查询成绩高于学号为109、课程号为3-105的成绩的所有记录
-- 思路：
-- 先抓取学号为109，课程号为3-105的成绩
-- 设置条件degree > 其数值

mysql> select degree from score where sno=109 and cno='3-105';
+--------+
| degree |
+--------+
| 76 |
+--------+
1 row in set (0.00 sec)

mysql> select * from score where degree > (select degree from score where sno=109 and cno='3-105');
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
| 105 | 6-166 | 79 |
| 109 | 6-166 | 81 |
+-----+-------+--------+
6 rows in set (0.00 sec)

-- 20.查询和学号为108、101的同学出生的所有学生的sno、sanme和sbirthday列
-- 首先查询出学号为108、101的同学出生的年并提取出来
-- 再去根据提取出来的年份作为where条件去查询其他所有同学的记录
-- 提取年份使用year()函数

mysql> select year(sbirthday) from student where sno in (108,101);
+-----------------+
| year(sbirthday) |
+-----------------+
| 1977 |
| 1975 |
+-----------------+
2 rows in set (0.00 sec)

mysql> select * from student where year(sbirthday) in (select year(sbirthday) from student where sno in (108,101));
+-----+-----------+------+---------------------+-------+
| sno | sname | ssex | sbirthday | class |
+-----+-----------+------+---------------------+-------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
+-----+-----------+------+---------------------+-------+
4 rows in set (0.00 sec)

-- 21.查询张旭教师任课的学生成绩
-- 首先要查询出张旭教师所教课程的cno
-- 再根据cno条件使用where语句去查询

mysql> select cno from teacher,course where teacher.tno=course.tno and teacher.tname='张旭';
+-------+
| cno |
+-------+
| 6-166 |
+-------+
1 row in set (0.00 sec)

mysql> select * from score where cno=(select cno from teacher,course where teacher.tno=course.tno and teacher.tname='张旭');
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 6-166 | 85 |
| 105 | 6-166 | 79 |
| 109 | 6-166 | 81 |
+-----+-------+--------+
3 rows in set (0.00 sec)

-- 22.查询选修某课程的同学人数多于5人的教师姓名
-- 首先使用group by对cno进行分组处理
-- 分组处理后跟上条件筛选having进行count(*) > 5的处理,并筛选出cno
-- 通过得出的cno筛选出教师姓名

mysql> select cno from score group by cno having count(*) > 5;
+-------+
| cno |
+-------+
| 3-105 |
+-------+
1 row in set (0.00 sec)

mysql> select tno from course where cno=(select cno from score group by cno having count(*) > 5);
+-----+
| tno |
+-----+
| 825 |
+-----+
1 row in set (0.00 sec)

mysql> select tname from teacher where tno=(select tno from course where cno=(select cno from score group by cno having count(*) > 5));
+--------+
| tname |
+--------+
| 王萍 |
+--------+
1 row in set (0.00 sec)

-- 23.查询95033班和95031班全体学生的记录
-- 这里使用where语句配合in

mysql> select * from student where class in (95031,95033);
+-----+-----------+------+---------------------+-------+
| sno | sname | ssex | sbirthday | class |
+-----+-----------+------+---------------------+-------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
+-----+-----------+------+---------------------+-------+
9 rows in set (0.00 sec)

-- 24.查询存在85分以上的成绩课程

mysql> select cno from score where degree > 85;
+-------+
| cno |
+-------+
| 3-105 |
| 3-105 |
| 3-105 |
| 3-245 |
| 3-105 |
| 3-105 |
+-------+
6 rows in set (0.00 sec)

-- 25.查询出计算机系教师的所教课程的成绩表
-- 先去查询teacher表中计算机系教师的tno
-- 再去course表中查询tno对应的cno
-- 最后再去score表中查询cno对应的行

mysql> select * from score where cno in(select cno from course where tno in(select tno from teacher where depart='计算机系'));
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-245 | 86 |
| 105 | 3-245 | 75 |
| 109 | 3-245 | 68 |
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 103 | 3-105 | 92 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
+-----+-------+--------+
9 rows in set (0.00 sec)

MYSQL复习

mysql数据库学习

猜你喜欢

热点阅读