问题一:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| 1xk2 |
| abc |
| bjsxt |
| information_schema |
| mysql |
| performance_schema |
| spjj |
| sys |
| testdb |
+--------------------+
9 rows in set (0.05 sec)
mysql> use SPJJ
Database changed
mysql> create table S
-> (
-> sno varchar(20),
-> sname varchar(10),
-> status smallint,
-> city varchar(10)
-> );
Query OK, 0 rows affected (0.07 sec)
mysql> insert into S values('S1','精益',20,'天津');
Query OK, 1 row affected (0.01 sec)
mysql> --插入多行数据
-> insert into S
-> values
-> ('S2','盛锡',10,'北京'),
-> ('S3','东方红',30,'北京'),
-> ('S2','丰泰盛',20,'天津'),
-> ('S2','丰泰盛',20,'天津'),--啊这里输入错误,重新复制一下
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '--插入多行数据
insert into S
values
('S2','盛锡',10,'北京'),
('S3','\00E4\00B8' at line 1
mysql> insert into S
-> values
-> ('S2','盛锡',10,'北京'),
-> ('S3','东方红',30,'北京'),
-> ('S4','丰泰盛',20,'天津'),
-> ('S5','为民',30,'上海');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from S;
+------+--------+--------+------+
| sno | sname | status | city |
+------+--------+--------+------+
| S1 | 精益 | 20 | 天津 |
| S2 | 盛锡 | 10 | 北京 |
| S3 | 东方红 | 30 | 北京 |
| S4 | 丰泰盛 | 20 | 天津 |
| S5 | 为民 | 30 | 上海 |
+------+--------+--------+------+
5 rows in set (0.00 sec)
mysql> --创建P表
-> create table P
-> (
-> pno varchar(10),
-> pname varchar(10),
-> color varchar(10),
-> weight smallint
-> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '--创建P表
create table P
(
pno varchar(10),
pname varchar(10),
color varchar(' at line 1
mysql> create table P
-> (
-> pno varchar(10),
-> pname varchar(10),
-> color varchar(10),
-> weight smallint
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> insert into P
-> values
-> ('p1','螺母','红',12),
-> ('p2','螺铨','绿',17),
-> ('p3','螺丝刀','蓝',14),
-> ('p4','螺丝刀','红',14),
-> ('p5','凸轮','蓝',40),
-> ('p6','齿轮','红',30);
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from P;
+------+--------+-------+--------+
| pno | pname | color | weight |
+------+--------+-------+--------+
| p1 | 螺母 | 红 | 12 |
| p2 | 螺铨 | 绿 | 17 |
| p3 | 螺丝刀 | 蓝 | 14 |
| p4 | 螺丝刀 | 红 | 14 |
| p5 | 凸轮 | 蓝 | 40 |
| p6 | 齿轮 | 红 | 30 |
+------+--------+-------+--------+
6 rows in set (0.00 sec)
mysql> create table J
-> (
-> jno varchar(10),
-> jname varchar(20),
-> city varchar(10)
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> insert into J
-> values
-> ('J1','三建','北京'),
-> ('J2','一汽','长春'),
-> ('J3','弹簧厂','天津'),
-> ('J4','造船厂','天津'),
-> ('J1','三建','北京'),^C
mysql> select * from J;
Empty set (0.01 sec)
mysql> insert into J
-> values
-> ('J1','三建','北京'),
-> ('J2','一汽','长春'),
-> ('J3','弹簧厂','天津'),
-> ('J4','造船厂','天津'),
-> ('J5','机车厂','唐山'),
-> ('J6','无线电厂','常州'),
-> ('J7','半导体厂','南京');
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> select * from J
-> ;
+------+----------+------+
| jno | jname | city |
+------+----------+------+
| J1 | 三建 | 北京 |
| J2 | 一汽 | 长春 |
| J3 | 弹簧厂 | 天津 |
| J4 | 造船厂 | 天津 |
| J5 | 机车厂 | 唐山 |
| J6 | 无线电厂 | 常州 |
| J7 | 半导体厂 | 南京 |
+------+----------+------+
7 rows in set (0.00 sec)
mysql> create table SPJ
-> (
-> sno varchar(20),
-> pno varchar(10),
-> jno varchar(10),
-> qty smallint
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> insert into SPJ values('S1','P1','J1',200);
Query OK, 1 row affected (0.01 sec)
mysql> insert into SPJ
-> values
-> ('S1','P1','J3',100),
-> ('S1','P1','J4',700),
-> ('S1','P2','J2',100),
-> ('S2','P3','J1',400),
-> ('S2','P3','J2',200),
-> ('S2','P3','J4',500),
-> ('S2','P3','J5',400),
-> ('S1','P5','J1',400),
-> \p
--------------
insert into SPJ
values
('S1','P1','J3',100),
('S1','P1','J4',700),
('S1','P2','J2',100),
('S2','P3','J1',400),
('S2','P3','J2',200),
('S2','P3','J4',500),
('S2','P3','J5',400),
('S1','P5','J1',400),
--------------
-> --输入失误
-> \c
mysql> insert into SPJ
-> values
-> ('S1','P1','J3',100),
-> ('S1','P1','J4',700),
-> ('S1','P2','J2',100),
-> ('S2','P3','J1',400),
-> ('S2','P3','J2',200),
-> ('S2','P3','J4',500),
-> ('S2','P3','J5',400),
-> ('S2','P5','J1',400),
-> insert into SPJ
-> values
-> ('S1','P1','J3',100),
-> ('S1','P1','J4',700),
-> ('S1','P2','J2',100),
-> ('S2','P3','J1',400),
-> ('S2','P3','J2',200),
-> ('S2','P3','J4',500),
-> ('S2','P3','J5',400),
->
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'insert into SPJ
values
('S1','P1','J3',100),
('S1','P1','J4',700),
('S1','P2','J' at line 11
mysql> insert into SPJ
-> values
-> ('S1','P1','J3',100),
-> ('S1','P1','J4',700),
-> ('S1','P2','J2',100),
-> ('S2','P3','J1',400),
-> ('S2','P3','J2',200),
-> ('S2','P3','J4',500),
-> ('S2','P3','J5',400),
-> ('S2','P5','J1',400),
-> ('S2','P5','J2',100),
-> ('S3','P1','J1',200),
-> ('S3','P3','J1',200),
-> ('S4','P5','J1',100),
-> ('S4','P6','J3',300),
-> ('S4','P6','J4',200),
-> ('S5','P2','J4',100),
-> ('S5','P3','J1',200),
-> ('S5','P6','J2',200),
-> ('S5','P6','J4',500);
Query OK, 18 rows affected (0.01 sec)
Records: 18 Duplicates: 0 Warnings: 0
mysql> select * from SPJ;
+------+------+------+------+
| sno | pno | jno | qty |
+------+------+------+------+
| S1 | P1 | J1 | 200 |
| S1 | P1 | J3 | 100 |
| S1 | P1 | J4 | 700 |
| S1 | P2 | J2 | 100 |
| S2 | P3 | J1 | 400 |
| S2 | P3 | J2 | 200 |
| S2 | P3 | J4 | 500 |
| S2 | P3 | J5 | 400 |
| S2 | P5 | J1 | 400 |
| S2 | P5 | J2 | 100 |
| S3 | P1 | J1 | 200 |
| S3 | P3 | J1 | 200 |
| S4 | P5 | J1 | 100 |
| S4 | P6 | J3 | 300 |
| S4 | P6 | J4 | 200 |
| S5 | P2 | J4 | 100 |
| S5 | P3 | J1 | 200 |
| S5 | P6 | J2 | 200 |
| S5 | P6 | J4 | 500 |
+------+------+------+------+
19 rows in set (0.00 sec)
mysql> --求供应工程J1零件的供应号码sno
-> \c
mysql> select sno
-> from J,SPJ
-> where J.jno=SPJ.jno
-> and j.jno='J1';
+------+
| sno |
+------+
| S1 |
| S2 |
| S2 |
| S3 |
| S3 |
| S4 |
| S5 |
+------+
7 rows in set (0.00 sec)
mysql> --问题二
-> select sno
-> from J,SPJ
-> where J.jno=SPJ.jno
-> and j.jno='J1'
-> and SPJ.pno='P1';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '--问题二
select sno
from J,SPJ
where J.jno=SPJ.jno
and j.jno='J1'
and SPJ.pno' at line 1
mysql> select sno
-> from J,SPJ
-> where J.jno=SPJ.jno
-> and j.jno='J1'
-> and SPJ.pno='P1';
+------+
| sno |
+------+
| S1 |
| S3 |
+------+
2 rows in set (0.00 sec)
mysql> --问题三
-> \c
mysql> select sno
-> from P,J,SPJ
-> where J.jno = SPJ.jno
-> and SPJ.pno = P.pno
-> and J.jno='J1'
-> and P.color='红';
+------+
| sno |
+------+
| S1 |
| S3 |
+------+
2 rows in set (0.00 sec)
mysql> --问题四
-> \c
mysql> select * from SPJ where SNO IN(select SNO from S where CITY='天津')
-> AND PNO IN(select PNO from P where COLOR='红')
-> ;
+------+------+------+------+
| sno | pno | jno | qty |
+------+------+------+------+
| S1 | P1 | J1 | 200 |
| S1 | P1 | J3 | 100 |
| S1 | P1 | J4 | 700 |
| S4 | P6 | J3 | 300 |
| S4 | P6 | J4 | 200 |
+------+------+------+------+
5 rows in set (0.01 sec)
mysql> select * from SPJ where exists(select * from SPJ where SNO='S1')
-> ;
+------+------+------+------+
| sno | pno | jno | qty |
+------+------+------+------+
| S1 | P1 | J1 | 200 |
| S1 | P1 | J3 | 100 |
| S1 | P1 | J4 | 700 |
| S1 | P2 | J2 | 100 |
| S2 | P3 | J1 | 400 |
| S2 | P3 | J2 | 200 |
| S2 | P3 | J4 | 500 |
| S2 | P3 | J5 | 400 |
| S2 | P5 | J1 | 400 |
| S2 | P5 | J2 | 100 |
| S3 | P1 | J1 | 200 |
| S3 | P3 | J1 | 200 |
| S4 | P5 | J1 | 100 |
| S4 | P6 | J3 | 300 |
| S4 | P6 | J4 | 200 |
| S5 | P2 | J4 | 100 |
| S5 | P3 | J1 | 200 |
| S5 | P6 | J2 | 200 |
| S5 | P6 | J4 | 500 |
+------+------+------+------+
19 rows in set (0.01 sec)
题目二:
在某仓库管理系统中,有两个表:KC表和CKMX表,其结构和部分数据如下:
其中,KC表保存当前仓库库存材料的信息,CKMX表存放库存材料的出库名细。
例如,KC表第一行表示该仓库中现有计算机10台。CKMX表第二行表示2009年11月20日从仓库中出库3台计算机。
(1) 试写出创建KC表和CKMX表的SQL语句,要求定义实体和参照完整性。
(2) 写出在KC表上按材料名称建立唯一索引的SQL语句。
(3) 创建一个视图,用于查询计算机的出库明细。
(4) 写出将表中数据插入到相应表中的语句。
(5) 从表CKMX中统计出各种材料的出库次数和出库总数量,试写出SQL语句。
(6) 写出由KC表和CKMX表产生如下结果集的查询语句:
mysql> use changku;
Database changed
mysql> --创建表
-> \c
mysql> create table KC
-> (
-> 材料代码 varchar(2) primary key,'
'> \p
'> '
-> \p
create table KC
(
材料代码 varchar(2) primary key,'
\p
--------------
-> \c
mysql> create table KC
-> (
-> 材料代码 varchar(2) primary key,
-> 材料名称 varchar(20) ,
-> 单位 varchar(20),
-> 单价 double(6,2),
-> 库存数量 int
-> );
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> create table CKMX
-> (
-> 编号 varchar(4)primary key,
-> 材料代码 varchar(2) references KC(材料代码),
-> 出库日期 date,
-> 出库数量 int
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> --创建索引
->
-> create unique index index1 on KC(材料名称);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '--创建索引
create unique index index1 on KC(材料名称)' at line 1
mysql> --创建索引
-> \c
mysql> create unique index index1 on KC(材料名称);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> --创建视图
-> \c
mysql> create view viewComputer
-> as
-> select * from CKCK where 材料代码='01';
ERROR 1146 (42S02): Table 'changku.ckck' doesn't exist
mysql> create view viewComputer
-> as
-> select * from CKMK where 材料代码='01';
ERROR 1146 (42S02): Table 'changku.ckmk' doesn't exist
mysql> create view viewComputer
-> as\
-> \c
mysql> create view viewComputer
-> as
-> select * from CKMX where 材料代码='01';
Query OK, 0 rows affected (0.01 sec)
mysql> --插入数据
-> \c
mysql> insert into KC values('01','计算机','台',5000.00,10);
Query OK, 1 row affected (0.01 sec)
mysql> insert into KC values('02','电视机','台',2000.00,20);
Query OK, 1 row affected (0.01 sec)
mysql> insert into CKMK values('1','01','2009-10-10',4);
ERROR 1146 (42S02): Table 'changku.ckmk' doesn't exist
mysql> insert into CKMX values('1','01','2009-10-10',4);
Query OK, 1 row affected (0.01 sec)
mysql> insert into CKMX values('2','01','2009-10-20',3);
Query OK, 1 row affected (0.01 sec)
mysql> insert into CKMX values('3','02','2009-10-20',5);
Query OK, 1 row affected (0.01 sec)
mysql> select * from CKMX;
+------+----------+------------+----------+
| 编号 | 材料代码 | 出库日期 | 出库数量 |
+------+----------+------------+----------+
| 1 | 01 | 2009-10-10 | 4 |
| 2 | 01 | 2009-10-20 | 3 |
| 3 | 02 | 2009-10-20 | 5 |
+------+----------+------------+----------+
3 rows in set (0.00 sec)
mysql> select * from CK;
ERROR 1146 (42S02): Table 'changku.ck' doesn't exist
mysql> select * from KC;
+----------+----------+------+---------+----------+
| 材料代码 | 材料名称 | 单位 | 单价 | 库存数量 |
+----------+----------+------+---------+----------+
| 01 | 计算机 | 台 | 5000.00 | 10 |
| 02 | 电视机 | 台 | 2000.00 | 20 |
+----------+----------+------+---------+----------+
2 rows in set (0.00 sec)
mysql> --统计
-> \c
mysql> select 材料代码.count(*) as 次数.sum(出库数量) as 出库数量
-> from CKMX
-> group by 材料代码;
-> ;
-> \c
mysql> --查询
-> select CKMX.编号,出库日期,材料名称,单价,出库数量,出库数量*单价 as 金额
-> from CK,CKMX
-> where KC.材料代码 = CKMX.材料代码;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '--查询
select CKMX.编号,出库日期,材料名称,单价,出库数量,出库' at line 1
mysql> select CKMX.编号,出库日期,材料名称,单价,出库数量,出库数量*单价 as 金额
-> from CK,CKMX
-> where KC.材料代码 = CKMX.材料代码;
ERROR 1146 (42S02): Table 'changku.ck' doesn't exist
mysql> select CKMX.编号,出库日期,材料名称,单价,出库数量,出库数量*单价 as 金额
-> from KC,CKMX
-> where KC.材料代码 = CKMX.材料代码;
+------+------------+----------+---------+----------+----------+
| 编号 | 出库日期 | 材料名称 | 单价 | 出库数量 | 金额 |
+------+------------+----------+---------+----------+----------+
| 1 | 2009-10-10 | 计算机 | 5000.00 | 4 | 20000.00 |
| 2 | 2009-10-20 | 计算机 | 5000.00 | 3 | 15000.00 |
| 3 | 2009-10-20 | 电视机 | 2000.00 | 5 | 10000.00 |
+------+------------+----------+---------+----------+----------+
3 rows in set (0.01 sec)
mysql>
问题三:
学校有多名学生,财务处每年要收一次学费。财务处现用两个表记录相关信息,其结构和部分数据如下表:
其中,XS表是在校学生名册和基本信息登记表,JFQK表是学生交学费情况记录。
(1) 试写出创建XS表和JFQK表的SQL语句,要求定义实体和参照完整性。
(2) 写出在XS表上按学生姓名建立唯一索引的SQL语句。
(3) 创建一个视图,用于查询计算机系同学的基本信息。
(4) 写出将表中数据插入到相应表中的语句。
(5) 将计算机系同学的年龄增加1岁,并将计算机系同学的学费减1000元。
(6) 从表JFQK中统计出每个同学的交费次数和交费总金额,试写出SQL语句。
(7) 写出由XS表和JFQK表能产生类似如下结果集的查询语句:
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200428222637639.png)
mysql> use school;
Database changed
mysql> create table XS
-> (
-> 学号 varchar(10) primary key,
-> 姓名 varchar(10) not null,
-> 性别 varchar(10) null,
-> 年龄 smallint,
-> 所在系 varchar(20)
-> );
Query OK, 0 rows affected (0.13 sec)
mysql> create table JFQK
-> (
-> 序号 int primary key,
-> 学号 varchar(10) references XS(学号),
-> 交费日期 date,
-> 书费 double(6,2),
-> 学费 int
-> );
Query OK, 0 rows affected, 1 warning (0.08 sec)
mysql> --创建索引
-> \c
mysql> create unique index index1 on XS(姓名);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> --创建视图
-> create view viewComputer1
-> as
-> select *
-> from XS
-> where 所在系='计算机';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '--创建视图
create view viewComputer1
as
select *
from XS
where 所在系='è\00AE' at line 1
mysql> create view viewComputer1
-> as
-> select *
-> from XS
-> where 所在系='计算机';
Query OK, 0 rows affected (0.02 sec)
mysql> --插入数据
-> \c
mysql> insert into XS values('200901','张明','男',18,'计算机');
Query OK, 1 row affected (0.01 sec)
mysql> insert into XS values('200902','王晓萌',null,21,'信息');
Query OK, 1 row affected (0.01 sec)
mysql> insert into XS values('200903','李刚','男',20,'计算机');
Query OK, 1 row affected (0.01 sec)
mysql> insert into JKQK values(1,'200901','2009-09-10',500.50,5000);
ERROR 1146 (42S02): Table 'school.jkqk' doesn't exist
mysql> insert into JFQK values(1,'200901','2009-09-10',500.50,5000);
Query OK, 1 row affected (0.01 sec)
mysql> insert into JFQK values(2,'200902','2009-09-10',400.00,5000);
Query OK, 1 row affected (0.01 sec)
mysql> insert into JFQK values(3,'200901','2010-10-10',300.20,4000);
Query OK, 1 row affected (0.01 sec)
mysql> select * from JFQK;
+------+--------+------------+--------+------+
| 序号 | 学号 | 交费日期 | 书费 | 学费 |
+------+--------+------------+--------+------+
| 1 | 200901 | 2009-09-10 | 500.50 | 5000 |
| 2 | 200902 | 2009-09-10 | 400.00 | 5000 |
| 3 | 200901 | 2010-10-10 | 300.20 | 4000 |
+------+--------+------------+--------+------+
3 rows in set (0.00 sec)
mysql> select * from XS;
+--------+--------+------+------+--------+
| 学号 | 姓名 | 性别 | 年龄 | 所在系 |
+--------+--------+------+------+--------+
| 200901 | 张明 | 男 | 18 | 计算机 |
| 200902 | 王晓萌 | NULL | 21 | 信息 |
| 200903 | 李刚 | 男 | 20 | 计算机 |
+--------+--------+------+------+--------+
3 rows in set (0.00 sec)
mysql> --更新数据
-> \c
mysql> update XS set 年龄 = 年龄 + 1
-> where 所在系 = ‘计算机';
'> \c
'> '
-> \c
mysql> update XS set 年龄 = 年龄 + 1
-> where 所在系 = '计算机';
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from XS;
+--------+--------+------+------+--------+
| 学号 | 姓名 | 性别 | 年龄 | 所在系 |
+--------+--------+------+------+--------+
| 200901 | 张明 | 男 | 19 | 计算机 |
| 200902 | 王晓萌 | NULL | 21 | 信息 |
| 200903 | 李刚 | 男 | 21 | 计算机 |
+--------+--------+------+------+--------+
3 rows in set (0.00 sec)
mysql> --统计
-> \c
mysql> select 学号.count(*) as 交费次数.sum(书费+学费) as 总金额
-> from JFQK
-> group by 学号;