MySQL>>笔记2-1-1

Posted on 2022-02-12 23:20 凡是过去，皆为序曲阅读(192) 评论(0) 编辑收藏举报

mysql> create database bookonline;
Query OK, 1 row affected

mysql> use bookonline;
Database changed

#主键
mysql> create table user(
-> uid char(4) primary key,
-> uname varchar(20),
-> email varchar(20),
-> tnum varchar(15),
-> score int
-> );
Database changed

#非空
mysql> create table book(
-> bid int primary key,
-> bname varchar(50) not null,
-> author char(8),
-> price float,
-> pbulisher varchar(50),
-> discount float,
-> cid int
-> );
Query OK, 0 rows affected

mysql> create table category(
-> cid int primary key,
-> cname varchar(16)
-> );
Query OK, 0 rows affected

#默认，复合主键
mysql> create table order0(
-> bid int,
-> uid char(4),
-> ordernum int default 1,
-> orderdate datetime,
-> deliverydate datetime,
-> primary key(bid,uid)
-> );
Query OK, 0 rows affected

#唯一
mysql> alter table user
-> modify tnum varchar(15) unique;
Database changed
Records: 0 Duplicates: 0 Warnings: 0
mysql>

#外键
mysql> alter table book
-> add constraint c_b_cid foreign key(cid)
-> references category(cid);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table order0
-> add constraint u_o_uid foreign key(uid)
-> references user(uid);
Database changed
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table order0
-> add constraint b_o_bid foreign key(bid)
-> references book(bid);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc order0;
+------------+---------+------+------+--------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+------+--------+-------+
| bid | int(11) | NO | PRI | NULL | |
| uid | char(4) | NO | PRI | NULL | |
| ordernum | int(11) | YES | | 1 | |
| orderdate | datetime | YES | | NULL | |
| deliverydate | datetime | YES | | NULL | |
+------------+---------+------+------+--------+-------+
5 rows in set
===================================================
#删除默认
alter table 表名 modify 字段类型；

mysql> alter table order0
-> modify ordernum int;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc order0;
+------------+---------+------+------+--------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+------+--------+-------+
| bid | int(11) | NO | PRI | NULL | |
| uid | char(4) | NO | PRI | NULL | |
| ordernum | int(11) | YES | | NULL | |
| orderdate | datetime | YES | | NULL | |
| deliverydate | datetime | YES | | NULL | |
+------------+---------+------+------+--------+-------+
5 rows in set

#删除非空
alter table 表名 modify 字段类型；

mysql> alter table book
-> modify bname varchar(50);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

#删除唯一
alter table 表名 drop index 字段;

#删除主键
alter table 表名 drop primary key;

mysql> alter table order0
-> drop primary key;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

#删除外键
alter table 表名 drop foreign key 外键名；

mysql> alter table book
-> drop foreign key c_b_cid;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

====================================================================

/* 动手实践 */

# 创建用户表
CREATE TABLE mydb.user (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '用户id',
username VARCHAR(20) UNIQUE NOT NULL COMMENT '用户名',
mobile CHAR(11) NOT NULL COMMENT '手机号码',
gender ENUM('男', '女', '保密') NOT NULL COMMENT '性别',
reg_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
level TINYINT UNSIGNED NOT NULL COMMENT '会员等级'
) DEFAULT CHARSET=utf8;

# 添加测试记录
INSERT INTO mydb.user VALUES
( NULL, '小明','12311111111', '男','2018-01-01 11:11:11', 1);

查看用户表中的记录
SELECT * FROM mydb.user;

==========================================================================================================================================
/* 动手实践 */

# 创建用户表
create table mydb.user (
id int unsigned primary key auto_increment comment '用户id',
username varchar(20) unique not null comment '用户名',
mobile char(11) not null comment '手机号码',
gender enum('男', '女', '保密') not null comment '性别',
reg_time timestamp default current_timestamp comment '注册时间',
level tinyint unsigned not null comment '会员等级'
) default charset=utf8;

# 添加测试记录
insert into mydb.user values
( null, '小明','12311111111', '男','2018-01-01 11:11:11', 1);

-> (null,'小明','12311111111','男','2018-01-01 11:11:11', 1),
-> (null,'小赵','12311111112','男','2018-01-01 11:11:12', 2),
-> (null,'小钱','12311111113','女','2018-01-01 11:11:13', 3),
-> (null,'小孙','12311111114','女','2018-01-01 11:11:11', 1),
-> (null,'小李','12311111115','男','2018-01-01 11:11:14', 5),
-> (null,'小周','12311111116','保密','2018-01-01 11:11:11', 1),
-> (null,'小吴','12311111121','男','2018-01-02 11:11:11', 10),
-> (null,'小郑','12311111131','女','2018-01-02 11:12:11', 2),
-> (null,'小王','12311111141','男','2018-01-03 12:11:11', 7),
-> (null,'小许','12311111151','男','2018-02-01 12:10:11', 3),
-> (null,'小魏','12311111211','男','2018-01-04 11:15:11', 6);
查看用户表中的记录
select * from mydb.user;

======================================================

(1) 使用MySQL创建会员表(如表3-9所示）图书表(如表3-10所示)的表结构
(2）创建图书类别表
(3）创建订购表

mysql> create database bookonline;
Query OK, 1 row affected

mysql> use bookonline;
Database changed
mysql> create table user(
-> uid char(4) primary key comment '会员编号',
-> uname varchar(20) comment '会员昵称',
-> email varchar(20) comment 'E-mail',
-> tnum varchar(15) comment '联系电话',
-> score int comment '积分'
-> );
Database changed
mysql>

mysql> create table book(
-> bid int primary key comment '图书编号',
-> bname varchar(50) not null comment '图书名称',
-> author char(8) comment '作者',
-> price float comment '价格',
-> pbulisher varchar(50) comment '出版社',
-> discount float comment '折扣',
-> cid int comment '图书类别'
-> );
Query OK, 0 rows affected

mysql>

mysql> create table book1(
-> cid int primary key comment '类别编号',
-> cname varchar(16) comment '类别名称'
-> );
Query OK, 0 rows affected

mysql> create table bookorder(
-> bid int comment '图书编号',
-> uid char(4) comment '会员编号',
-> ordernum int default 1 comment '订购量',
-> orderdate datetime comment '订购日期',
-> deliverydate datetime comment '发货日期'
-> );
Query OK, 0 rows affected

mysql> alter table bookorder
-> add primary key(bid,uid);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table book
-> add constraint b1_b_cid foreign key(cid)
-> references book1(cid);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table bookorder
-> add constraint b_bo_bid foreign key(bid)
-> references book(bid);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table bookorder
-> add constraint u_bo_uid foreign key(uid)
-> references user(uid);
Database changed
Records: 0 Duplicates: 0 Warnings: 0

mysql>

mysql>
mysql> desc bookorder;
+------------+---------+------+------+--------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+------+--------+-------+
| bid | int(11) | NO | PRI | NULL | |
| uid | char(4) | NO | PRI | NULL | |
| ordernum | int(11) | YES | | 1 | |
| orderdate | datetime | YES | | NULL | |
| deliverydate | datetime | YES | | NULL | |
+------------+---------+------+------+--------+-------+
5 rows in set

(5）用户表中添加字段“联系地址”：更改“联系地址”为“联系方式”：删除添加的字段
mysql> alter table user
-> add 联系地址 varchar(20);
Database changed
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table user change 联系地址联系方式 varchar(20);
Database changed
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table user drop 联系方式;
Database changed
Records: 0 Duplicates: 0 Warnings: 0
mysql>
==========================================================================================================================================================

mysql> insert into user values
-> ('1001','何仙姑','Hxg18@163.com','13320101991',20),
-> ('1002','平平人生','Lp011@126.com','13545158219',300),
-> ('1003','四十不惑','12345@qq.com','18688168818',1000),
-> ('1004','桃花岛主','810124@qq.com','13068011234',600),
-> ('1005','水灵','zs123@371.cn','15838182503',150),
-> ('1006','感动心灵','gandong@tom.com','13641151234',500);
Database changed
Records: 6 Duplicates: 0 Warnings: 0

mysql> insert into book1 values
-> (1,'历史'),
-> (2,'家教'),
-> (3,'文化'),
-> (4,'小说');
Query OK, 4 rows affected
Records: 4 Duplicates: 0 Warnings: 0

mysql> insert into book values
-> (1,'中国时代','师永刚',39.0,'作家出版社',27.8,1),
-> (2,'中国历史的屈辱','王重旭',26.0,'华夏出版社',18.2,2),
-> (3,'择业要趁早','海文',28.0,'海天出版社',19.3,3),
-> (4,'房间','爱玛',37.6,'人民文学出版社',26.3,4),
-> (5,'平凡的世界','路遥',75,'北京出版社',63.75,4),
-> (6,'心灵鸡汤','关然',27.0,'大豫出版社',20.0,3),
-> (7,'蜕','赵婷',32.0,'上海出版社',28.5,3);
Query OK, 7 rows affected
Records: 7 Duplicates: 0 Warnings: 0

mysql> alter table bookorder drop primary key;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

mysql> insert into bookorder(uid,bid,ordernum,orderdate) values
-> ('1001',1,2,'2016-03-12'),
-> ('1001',3,1,'2016-04-15'),
-> ('1001',1,1,'2016-09-15'),
-> ('1003',7,1,'2015-12-14'),
-> ('1003',3,1,'2016-10-10'),
-> ('1005',5,1,'2015-08-17'),
-> ('1005',7,3,'2016-11-12'),
-> ('1006',5,1,'2016-09-18'),
-> ('1006',1,2,'2016-10-21'),
-> ('1006',7,2,'2015-11-21');
Query OK, 10 rows affected
Records: 10 Duplicates: 0 Warnings: 0

mysql> alter table bookorder
-> add primary key(bid,uid,orderdate);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

mysql> update user set uname='何大姑' where uid='1001';
Database changed
Rows matched: 1 Changed: 1 Warnings: 0

mysql> update bookorder set ordernum=10,orderdate='2016-10-01'
-> where uid='1003' and bid=3;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0

#删除2015年的订单信息
mysql> delete from bookorder
-> where orderdate>='2015-01-01' and orderdate<='2015-12-31';
Query OK, 3 rows affected

#复制book表为book1，清空book1表数据,注意做删除操作时，要先删除表的主外键约束
mysql> alter table book_copy drop foreign key book_copy_ibfk_1;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table book_copy drop primary key;
Query OK, 7 rows affected
Records: 7 Duplicates: 0 Warnings: 0

mysql> delete from book_copy;
Query OK, 7 rows affected

==========================================================

填空题
37、未添加数据的字段系统会自动为该字段添加默认值 null
38、无符号整数类型需要使用 unsigned 关键字修饰。
39、有符号整数-256的显示宽度为 4
40、BLOB 和 TEXT 数据类型不支持默认约束。
41、not null 用于限定字段的值不能为NULL。
42、关键字 default 用于为数据表中的字段指定默认值。
43、MySQL提供 delete 语句用于删除表中的数据。
44、整数类型 TINYINT 的取值范围在0~255之间。
45、在创建数据表时可以利用CHARACTER SET或 CHARSET 设置字符集。
46、语句 rename table 可同时修改多个数据表名。

简答题
47、请简述ENUM和SET数据类型的区别。

ENUM类型又称为枚举类型,列表最多可以有65535个值,数据只能从枚举列表中取,并且只能取一个。
SET类型用于保存字符串对象,列表中最多可以有64个值,可以从列表中选择一个或多个值来保存,多个值之间用逗号“,”分隔。

48、在mydb数据库中创建一张电子杂志订阅表（subscribe）。电子杂志订阅表中要包含4个字段，分别为编号（id）、
订阅邮件的邮箱地址（email）、
用户是否确认订阅（status，使用数字表示，1表示已确认，0表示未确认）、
邮箱确认的验证码（code）。
一、创建数据库和表结构
二、数据操作
1、为电子杂志订阅表添加5条测试数据，如表所示。(见文件48.二.1--图.jpg)
tom123@163.com TRBXPO
lucy123@163.com LOICPE
lily123@163.com JIXDAMI
jimmy123@163.com QKOLPH
joy123@163.com JSMWNL
2、查看已经通过邮箱确认的电子杂志订阅信息。
3、将编号等于4的订阅确认状态设置为“已确认”。
4、删除编号等于5的电子杂志订阅信息。

mysql> create database mydb;
Query OK, 1 row affected
mysql> use mydb;
Database changed
mysql> create table subscribe(
-> id int primary key comment '编号',
-> email varchar(20) comment '邮箱地址',
-> status int(1) default 0 comment '确认订阅',
-> code varchar(10) comment '验证码'
-> );
Query OK, 0 rows affected

mysql> insert into subscribe values
-> (1,'tom123@163.com',1,'TRBXPO'),
-> (2,'lucy123@163.com',1,'LOICPE'),
-> (3,'lily123@163.com',0,'JIXDAMI'),
-> (4,'jimmy123@163.com',0,'QKOLPH'),
-> (5,'joy123@163.com',1,'JSMWNL');
Query OK, 5 rows affected
Records: 5 Duplicates: 0 Warnings: 0

mysql> select *
-> from subscribe where status = 1;
+----+-----------------+-------+----------+
| id | email | status | code |
+----+-----------------+-------+----------+
| 1 | tom123@163.com | 1 | TRBXPO |
| 2 | lucy123@163.com | 1 | LOICPE |
| 5 | joy123@163.com | 1 | JSMWNL |
+----+-----------------+-------+----------+
3 rows in set

mysql> update subscribe
-> set status = 1
-> where id = 4;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0

mysql> delete from subscribe
-> where id = 5;
Query OK, 1 row affected

49、根据以下的要求完成学生表（student）SQL语句的创建。
student(id,name,tel,gender,hobby,time)
学生姓名name：可以使用中文，不允许重复，长度在2~20个字符之间。
手机号码tel：长度为11个字符。
性别gender：有男、女、保密3种选择。
爱好hobby：指定用户可以多选的项，“运动、唱歌、跳舞、戏剧、手工、其他”。
入学时间time：注册时的日期和时间。

create table student (
id int unsigned primary key auto_increment comment '用户id',
name varchar(20) unique not null comment '学生姓名',
tel char(11) not null comment '手机号码',
gender enum('男', '女', '保密') not null comment '性别',
hobby set('运动', '唱歌', '跳舞','戏剧','手工','其他') not null comment '爱好',
time timestamp default current_timestamp comment '入学时间'
);

mysql> create table student (
-> id int primary key comment '用户id',
-> name varchar(20) unique not null comment '学生姓名',
-> tel int(11) not null comment '手机号码',
-> gender enum('男','女','保密') not null comment '性别',
-> hobby set('运动','唱歌','跳舞','戏剧','手工','其他') not null comment '爱好',
-> time timestamp comment '入学时间'
-> );
Query OK, 0 rows affected

50、根据电子杂志订阅表subscribe(id,email,status,code)进行操作。
（1）将code字段的位置调整到status字段的前面。
（2）添加一个标签（label）字段，用于保存用户的偏好。
（3）删除标签字段。

(1)alter table subscribe modify status int after code;
(2)alter table subscribe add label varchar(255);
(3)alter table subscribe drop label;

mysql> alter table subscribe
-> modify status int(1) after code;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table subscribe
-> add label varchar(50);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table subscribe drop label;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

================================================================

mysql> use stuchoose;
Database changed

【例5-1】查询“学生选课”数据库的studentinfo表，输出所有学生的详细信息

mysql> select * from studentinfo;
+----------+--------+---------+------------+-------------+
| sno | sname | sgender | sbirth | sclass |
+----------+--------+---------+------------+-------------+
| 10101001 | 张永峰 | 男 | 1993-08-01 | 电子商务101 |
| 10101002 | 赵迪 | 男 | 1994-09-12 | 电子商务101 |
| 10101003 | 周四 | 女 | 1993-01-01 | 电子商务101 |
| 10101004 | 王二 | 女 | 1994-09-12 | 电子商务101 |
| 10101005 | 李强 | 男 | 1993-06-28 | 电子商务101 |
| 10101006 | 韩梅梅 | 女 | 1994-10-12 | 电子商务101 |
| 10101007 | 李勇 | 男 | 1993-08-01 | 电子商务102 |
| 10101008 | 路海空 | 男 | 1994-09-12 | 电子商务102 |
| 10101009 | 潘小小 | 女 | 1993-01-01 | 电子商务102 |
| 10101010 | 薛莉莉 | 女 | 1994-09-12 | 电子商务102 |
| 10101011 | 陈大海 | 男 | 1993-06-28 | 电子商务102 |
| 10101012 | 王天力 | 女 | 1994-10-12 | 电子商务103 |
| 10101013 | 安装包 | 男 | 1993-08-01 | 电子商务103 |
| 10101014 | 刘琪琪 | 男 | 1994-09-12 | 电子商务103 |
| 10101015 | 张博 | 女 | 1993-01-01 | 电子商务103 |
| 10101016 | 高兴 | 女 | 1994-09-12 | 电子商务103 |
| 10101017 | 方鸿渐 | 男 | 1993-06-28 | 电子商务103 |
| 10101018 | 侯花花 | 女 | 1994-10-12 | 电子商务103 |
+----------+--------+---------+------------+-------------+
18 rows in set

【例5-2】查询“学生选课”数据库的studentinfo表，输出所有学生的学号和姓名

mysql> select sno as 学号,sname as 姓名 from studentinfo;
+----------+--------+
| 学号 | 姓名 |
+----------+--------+
| 10101001 | 张永峰 |
| 10101002 | 赵迪 |
| 10101003 | 周四 |
| 10101004 | 王二 |
| 10101005 | 李强 |
| 10101006 | 韩梅梅 |
| 10101007 | 李勇 |
| 10101008 | 路海空 |
| 10101009 | 潘小小 |
| 10101010 | 薛莉莉 |
| 10101011 | 陈大海 |
| 10101012 | 王天力 |
| 10101013 | 安装包 |
| 10101014 | 刘琪琪 |
| 10101015 | 张博 |
| 10101016 | 高兴 |
| 10101017 | 方鸿渐 |
| 10101018 | 侯花花 |
+----------+--------+
18 rows in set

【例5-3】查询“学生选课”数据库的studentinfo表，输出所有学生的学号，姓名，以及此次查询的日期和时间

mysql> select sno as 学号,sname as 姓名,now() as 当前时间 from studentinfo;
+----------+--------+-------------------+
| 学号 | 姓名 | 当前时间 |
+----------+--------+-------------------+
| 10101001 | 张永峰 | 2020-11-17 14:17:01 |
| 10101002 | 赵迪 | 2020-11-17 14:17:01 |
| 10101003 | 周四 | 2020-11-17 14:17:01 |
| 10101004 | 王二 | 2020-11-17 14:17:01 |
| 10101005 | 李强 | 2020-11-17 14:17:01 |
| 10101006 | 韩梅梅 | 2020-11-17 14:17:01 |
| 10101007 | 李勇 | 2020-11-17 14:17:01 |
| 10101008 | 路海空 | 2020-11-17 14:17:01 |
| 10101009 | 潘小小 | 2020-11-17 14:17:01 |
| 10101010 | 薛莉莉 | 2020-11-17 14:17:01 |
| 10101011 | 陈大海 | 2020-11-17 14:17:01 |
| 10101012 | 王天力 | 2020-11-17 14:17:01 |
| 10101013 | 安装包 | 2020-11-17 14:17:01 |
| 10101014 | 刘琪琪 | 2020-11-17 14:17:01 |
| 10101015 | 张博 | 2020-11-17 14:17:01 |
| 10101016 | 高兴 | 2020-11-17 14:17:01 |
| 10101017 | 方鸿渐 | 2020-11-17 14:17:01 |
| 10101018 | 侯花花 | 2020-11-17 14:17:01 |
+----------+--------+-------------------+
18 rows in set

【例 5-4】查询“学生选课”数据库的studentinfo表，输出所有学生的学号，姓名，以及此次查询的日期和时间，并分别使用“学生学号”“学生姓名"”查询日期“作为别名

mysql> select sno as 学生学号,sname as 学生姓名,now() as 查询日期 from studentinfo;
+----------+----------+-------------------+
| 学生学号 | 学生姓名 | 查询日期 |
+----------+----------+-------------------+
| 10101001 | 张永峰 | 2020-11-17 14:18:14 |
| 10101002 | 赵迪 | 2020-11-17 14:18:14 |
| 10101003 | 周四 | 2020-11-17 14:18:14 |
| 10101004 | 王二 | 2020-11-17 14:18:14 |
| 10101005 | 李强 | 2020-11-17 14:18:14 |
| 10101006 | 韩梅梅 | 2020-11-17 14:18:14 |
| 10101007 | 李勇 | 2020-11-17 14:18:14 |
| 10101008 | 路海空 | 2020-11-17 14:18:14 |
| 10101009 | 潘小小 | 2020-11-17 14:18:14 |
| 10101010 | 薛莉莉 | 2020-11-17 14:18:14 |
| 10101011 | 陈大海 | 2020-11-17 14:18:14 |
| 10101012 | 王天力 | 2020-11-17 14:18:14 |
| 10101013 | 安装包 | 2020-11-17 14:18:14 |
| 10101014 | 刘琪琪 | 2020-11-17 14:18:14 |
| 10101015 | 张博 | 2020-11-17 14:18:14 |
| 10101016 | 高兴 | 2020-11-17 14:18:14 |
| 10101017 | 方鸿渐 | 2020-11-17 14:18:14 |
| 10101018 | 侯花花 | 2020-11-17 14:18:14 |
+----------+----------+-------------------+
18 rows in set

【例5-5】查询“学生选课”数据库的studentinfo表，输出学生所在的班级，每个班级只输出一次

【例5-6】查询“学生选课”数据库的studentinfo表，输出”网络技术101“班学生的详细信息

mysql> select * from studentinfo where sclass="网络技术101";
Empty set

【例5-7】查询“学生选课”数据库的studentinfo表，输出1992年出生的学生的详细信息。

mysql> select * from studentinfo where sbirth like "1992%";
Empty set

或者 where sbirth between "1992-01-01" and "1992-12-31";

【例5-8】查询“学生选课”数据库的studentinfo表，输出学号为10101001、10102001、11101001的学生的详细信息

mysql> select * from studentinfo where sno in ("10101001","10102001","11101001");

或者 sno = "10101001" or sno = "10102001" or sno = "11101001"

+----------+--------+---------+------------+-------------+
| sno | sname | sgender | sbirth | sclass |
+----------+--------+---------+------------+-------------+
| 10101001 | 张永峰 | 男 | 1993-08-01 | 电子商务101 |
+----------+--------+---------+------------+-------------+
1 row in set

【例5-9】查询“学生选课”数据库的studentinfo表，输出姓”张“的学生详细信息。

mysql> select * from studentinfo where sname like "张%";
+----------+--------+---------+------------+-------------+
| sno | sname | sgender | sbirth | sclass |
+----------+--------+---------+------------+-------------+
| 10101001 | 张永峰 | 男 | 1993-08-01 | 电子商务101 |
| 10101015 | 张博 | 女 | 1993-01-01 | 电子商务103 |
+----------+--------+---------+------------+-------------+
2 rows in set

【例5-10】查询“学生选课”数据库的elective表，输出没有成绩的学生信息。

mysql> select * from elective where score is null;
+----------+------+-------+
| sno | cno | score |
+----------+------+-------+
| 10101004 | 1004 | NULL |
| 10101006 | 1008 | NULL |
| 10101013 | 1008 | NULL |
+----------+------+-------+
3 rows in set

【例5-11】查询“学生选课”数据库的studentinfo表，输出姓"王"且是“电子商务111”班的学生信息。

mysql> select * from studentinfo where sname like "王%" and sclass="电子商务111";
Empty set

【例5-12】查询“学生选课”数据库的studentinfo表，输出姓"王"或者“电子商务111”班的学生信息。

mysql> select * from studentinfo where sname like "王%" or sclass="电子商务111";
+----------+--------+---------+------------+-------------+
| sno | sname | sgender | sbirth | sclass |
+----------+--------+---------+------------+-------------+
| 10101004 | 王二 | 女 | 1994-09-12 | 电子商务101 |
| 10101012 | 王天力 | 女 | 1994-10-12 | 电子商务103 |
+----------+--------+---------+------------+-------------+
2 rows in set

【例5-13】查询“学生选课”数据库的studentinfo表，输出不是1992年出生的学生信息

mysql> select * from studentinfo where sbirth not like "1992%";
+----------+--------+---------+------------+-------------+
| sno | sname | sgender | sbirth | sclass |
+----------+--------+---------+------------+-------------+
| 10101001 | 张永峰 | 男 | 1993-08-01 | 电子商务101 |
| 10101002 | 赵迪 | 男 | 1994-09-12 | 电子商务101 |
| 10101003 | 周四 | 女 | 1993-01-01 | 电子商务101 |
| 10101004 | 王二 | 女 | 1994-09-12 | 电子商务101 |
| 10101005 | 李强 | 男 | 1993-06-28 | 电子商务101 |
| 10101006 | 韩梅梅 | 女 | 1994-10-12 | 电子商务101 |
| 10101007 | 李勇 | 男 | 1993-08-01 | 电子商务102 |
| 10101008 | 路海空 | 男 | 1994-09-12 | 电子商务102 |
| 10101009 | 潘小小 | 女 | 1993-01-01 | 电子商务102 |
| 10101010 | 薛莉莉 | 女 | 1994-09-12 | 电子商务102 |
| 10101011 | 陈大海 | 男 | 1993-06-28 | 电子商务102 |
| 10101012 | 王天力 | 女 | 1994-10-12 | 电子商务103 |
| 10101013 | 安装包 | 男 | 1993-08-01 | 电子商务103 |
| 10101014 | 刘琪琪 | 男 | 1994-09-12 | 电子商务103 |
| 10101015 | 张博 | 女 | 1993-01-01 | 电子商务103 |
| 10101016 | 高兴 | 女 | 1994-09-12 | 电子商务103 |
| 10101017 | 方鸿渐 | 男 | 1993-06-28 | 电子商务103 |
| 10101018 | 侯花花 | 女 | 1994-10-12 | 电子商务103 |
+----------+--------+---------+------------+-------------+
18 rows in set

【例5-14】查询“学生选课”数据库的elective表，输出选修了c001号课程的学生信息，并将查询结果按成绩的降序排序。

mysql> select * from elective where cno="c001" order by score desc;
Empty set

【例5-15】查询“学生选课”数据库的studentinfo表，输出前三条学生记录的信息。

mysql> select * from studentinfo limit 3;
+----------+--------+---------+------------+-------------+
| sno | sname | sgender | sbirth | sclass |
+----------+--------+---------+------------+-------------+
| 10101001 | 张永峰 | 男 | 1993-08-01 | 电子商务101 |
| 10101002 | 赵迪 | 男 | 1994-09-12 | 电子商务101 |
| 10101003 | 周四 | 女 | 1993-01-01 | 电子商务101 |
+----------+--------+---------+------------+-------------+
3 rows in set

【例5-16】查询“学生选课”数据库的studentinfo表，输出表中第五行学生记录的信息

mysql> select * from studentinfo limit 4,1;
+----------+--------+---------+------------+-------------+
| sno | sname | sgender | sbirth | sclass |
+----------+--------+---------+------------+-------------+
| 10101005 | 李强 | 男 | 1993-06-28 | 电子商务101 |
+----------+--------+---------+------------+-------------+
1 row in set

【例5-17】查询“学生选课”数据库的studentinfo表，统计学生总人数

mysql> select count(*) as 总人数 from studentinfo;
+--------+
| 总人数 |
+--------+
| 18 |
+--------+
1 row in set

【例5-18】查询“学生选课”数据库的elective表，统计选修了c003号课程的学生人数、总成绩、平均分、最高分和最低分。

mysql> select count(*) as 学生人数,sum(score) as 总成绩,avg(score) as 平均分,max(score) as 最高分,min(score) as 最低分 from elective where cno="c003";

或者 select count(sno) as 学生人数,sum(score) as 总成绩,avg(score) as 平均分,max(score) as 最高分,min(score) as 最低分 from elective group by cno having cno="c003";
+----------+--------+--------+--------+--------+
| 学生人数 | 总成绩 | 平均分 | 最高分 | 最低分 |
+----------+--------+--------+--------+--------+
| 0 | NULL | NULL | NULL | NULL |
+----------+--------+--------+--------+--------+
1 row in set

【例5-19】查询“学生选课”数据库的studentinfo表，分别统计男生女人的人数

mysql> select sgender as 性别,count(*) as 人数 from studentinfo group by sgender;
+------+------+
| 性别 | 人数 |
+------+------+
| 男 | 9 |
| 女 | 9 |
+------+------+
2 rows in set

【例5-20】查询“学生选课”数据库的elective表，统计并输出每个学生所选课程数及平均分。

mysql> select sno 学号,count(*) 选课数,avg(score) 平均分 from elective group by sno;
+----------+--------+---------+
| 学号 | 选课数 | 平均分 |
+----------+--------+---------+
| 10101001 | 4 | 80.0000 |
| 10101002 | 5 | 72.4000 |
| 10101003 | 3 | 80.6667 |
| 10101004 | 4 | 74.6667 |
| 10101005 | 2 | 67.5000 |
| 10101006 | 3 | 58.5000 |
| 10101007 | 1 | 60.0000 |
| 10101008 | 2 | 77.5000 |
| 10101009 | 4 | 67.2500 |
| 10101010 | 1 | 91.0000 |
| 10101011 | 1 | 82.0000 |
| 10101012 | 1 | 73.0000 |
| 10101013 | 1 | NULL |
+----------+--------+---------+
13 rows in set

【例5-21】查询“学生选课”数据库的elective表，统计并输出每门课程所选学生人数及最高分

mysql> select cno 课号,count(*) 选课人数,max(score) 最高分 from elective group by cno;
+------+----------+--------+
| 课号 | 选课人数 | 最高分 |
+------+----------+--------+
| 1001 | 4 | 85 |
| 1002 | 4 | 91 |
| 1003 | 2 | 77 |
| 1004 | 4 | 82 |
| 1005 | 4 | 82 |
| 1006 | 6 | 95 |
| 1008 | 4 | 94 |
| 1009 | 2 | 50 |
| 1010 | 2 | 70 |
+------+----------+--------+
9 rows in set

【例5-22】查询elective表中每门课程成绩都在70-90分的学生的学号

错误：mysql> select cno 课号,sno 学号,score 成绩 from elective group by cno having 70<=score and score<=90;
+------+----------+------+
| 课号 | 学号 | 成绩 |
+------+----------+------+
| 1001 | 10101001 | 85 |
| 1003 | 10101002 | 77 |
| 1004 | 10101001 | 76 |
| 1010 | 10101003 | 70 |
+------+----------+------+
4 rows in set

正确： mysql> select sno 学号 from elective group by sno having min(score)>=70 and max(score)<=90;
+----------+
| 学号 |
+----------+
| 10101003 |
| 10101008 |
| 10101011 |
| 10101012 |
+----------+
4 rows in set

【例5-23】查询至少选修了三门课程的学生的学号

mysql> select sno as 学号,count(*) as 选课数 from elective group by sno having count(*)>=3;
+----------+--------+
| 学号 | 选课数 |
+----------+--------+
| 10101001 | 4 |
| 10101002 | 5 |
| 10101003 | 3 |
| 10101004 | 4 |
| 10101006 | 3 |
| 10101009 | 4 |
+----------+--------+
6 rows in set

========================================================================

DDL 数据定义语言
@#@视图创建

create view 视图名(字段别名,字段别名) as select 字段 from 表名;

create nopass_view(学号,姓名,课程名,成绩) as select sno,sname,cname,score from ...;
===========================================================
查看视图

视图结构
describe 视图名;
@#@简写：
desc 视图名;

视图定义信息（状态信息）
show table status like "视图名"\G

@#@视图创建信息
show create view 视图名;

===========================================================
修改视图
@#@(1) create or replace view 视图名 as select ...from;

@#@(2) alter view 视图名 as select from;
===========================================================
@#@删除视图
drop view if exists 视图名;
===========================================================
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
===========================================================
DQL 查询
DML 修改

插入数据
insert into 视图名 values ('s','s',1,'s');

update 视图名 set 字段名 = XX where ...;

delete from 视图名 where ...;

select 字段 from 视图名 where ...;
===========================================================

select s.学号,姓名,分数 from student_info s left join grade g on s.学号=g.学号;

select s.学号,姓名,sum(分数) from student_info s left join grade g on s.学号=g.学号 group by s.学号;
=======================================================
select 学号,avg(分数) from grade group by 学号;
=======================================================

select s.学号,姓名,分数 from student_info s left join grade g on s.学号=g.学号 where 分数 between 80 and 90;
=======================================================

select 学号,姓名,出生日期 from student_info;
=======================================================

mysql> create or replace view stu_view as select 姓名,家族住址 from student_info;
Query OK, 0 rows affected

mysql> select * from stu_view;
+--------+---------------------+
| 姓名 | 家族住址 |
+--------+---------------------+
| 张青平 | 衡阳市东风路77号 |
| 刘东阳 | 东阳市八一北路33号 |
| 马晓夏 | 长岭市五一路763号 |
| 钱忠理 | 滨海市洞庭大道279号 |
| 孙海洋 | 长岛市解放路27号 |
| 郭小斌 | 南山市红旗路113号 |
| 肖月玲 | 东方市南京路11号 |
| 张玲珑 | 滨江市新建路97号 |
+--------+---------------------+
8 rows in set
=======================================================

select s.学号,姓名,max(分数) from student_info s join grade g on s.学号=g.学号 group by s.学号;

=======================================================

select c.课程编号,课程名称,count(学号) from grade g right join curriculum c on g.课程编号=c.课程编号 group by c.课程编号;

===========================================================

刷新页面返回顶部