Mysql的使用

第一个实验

实验内容

用SQL语句建立图中的4个表；针对建立的4个表用SQL完成查询。

相关命令：

1. 启动MySQL：
   net start mysql80;
2. 登录：
   mysql -h localhost -u root -p
3. 创建数据库：
   create database 数据库名称;
create database spj;
4. 建表之前先进入数据库：
   use 数据库名称;
use spj;
5. 建表：

create table 表名
      (属性值 (字段参数)，
      属性值 (字段参数)....);

create table spj
      (sno char(4),
      sname varchar(20),
      status int,
      city varchar(20));

6. 查看表：
   show tables;
7. 在表中插入数据：

insert into 表名
      (属性列，属性列,.....)
      Values
      (‘字符串’，20，’字符串’,....）; 

insert into s
      (sno,sname,status,city)
      values
      ('s1','精益'，20，'天津');

插入多条记录：(记录间用逗号隔开)

 insert into p
    -> values
    -> ('p1','螺母','红',12),
    -> ('p2','栓母','绿',17),
    -> ('p3','螺丝刀','蓝',14),
    -> ('p4','螺丝刀','红',14);

8. 查看表中内容

select *
from 表明；

select *
from s;

9. 更新表中某一元组的数据

update 表名
set 属性值 = 修改后的数据，属性值 = 修改后的数据...
where 条件

update s
set city = '北京'
where sno = 's1';

10. 取消重复行，使用distinct
11. 删除表中某一个元组的值

delete
    -> from s
    -> where sno = 's2';

实现命令

1. 求供应工程j1零件的供应商号码sno；

select distinct sno
    -> from spj
    -> where jno = 'j1';

2. 求供应工程j1零件p1的供应商号码sno；

select distinct sno
    -> from spj
    -> where
    -> (jno = 'j1'and
    -> pno = 'p1');

3. 求供应工程j1零件为红色的供应商号码sno；
   嵌套

• 嵌套内的where子句不加;
• where子句不加括号

select sno
    -> from spj
    -> where jno = 'j1'and pno in
    -> (select pno
    -> from p
    -> where color = '红');

或者
连接运算

select sno
-> from p,spj
-> where p.pno = spj.pno and
-> jno = 'j1' and color = '红';

4. 求没有使用天津供应商生产的红色零件的工程号jno；
   分析：此题的主干是工程号，工程没有使用。。。，所以是jno not in(),括号里面的内容是使用天津供应商生产的红色零件.错误原因是没有搞清主干，比较运算查询了使用红色零件并且不是天津供应商的工程号，但查询到的此工程可能有其他的供应情况，使用了红色零件或者是天津供应商。嵌套查询使用了把两个条件分开查询，

select distinct jno
    -> from spj
    -> where jno not in
    -> (select jno
    -> from s,p,spj
    -> where city = '天津'and
    -> color = '红' and
    -> s.sno = spj.sno and p.pno = spj.pno);
+------+
| jno  |
+------+
| j2   |
| j5   |
+------+
2 rows in set (0.00 sec)

错误代码：
比较运算：!=或<>

select jno
    -> from s,p,spj
    -> where p.pno = spj.pno and
    -> s.sno = spj.sno and
    -> color = '红' and
    -> city != '天津';
+------+
| jno  |
+------+
| j1   |
| j2   |
| j4   |
+------+

或者

select jno
    -> from s,p,spj
    -> where p.pno = spj.pno and
    -> s.sno = spj.sno and
    -> color = '红' and
    -> city <> '天津';
+------+
| jno  |
+------+
| j1   |
| j2   |
| j4   |
+------+

再或者
嵌套

select jno
    -> from spj
    -> where sno not in
    -> (select sno
    -> from s
    -> where city = '天津') and                                                                                                                 
    -> pno in
    -> (select pno
    -> from p
    -> where color = '红');
+------+
| jno  |
+------+
| j1   |
| j2   |
| j4   |
+------+

5. 求至少用了供应商s1所供应的全部零件的工程号jno；

select jno
    -> from spj
    -> where sno = 's1'

第二个实验

实验内容

针对第一个实验的4个表用SQL完成以下各项操作

1. 找出所有供应商的姓名和所在城市

mysql> select sname,city
    -> from s;

2. 找出所有零件的名称、颜色、重量

select pname,color,weight
    -> from p;

3. 找出所有供应商s1所供应的零件的工程号码

select jno
    -> from spj
    -> where sno = 's1'

4. 找出工程项目j2使用的各种零件的名称及其数量

select pname,qty
    -> from p,spj
    -> where p.pno = spj.pno and jno = 'j2';

5. 找出上海厂商供应的所有零件号码
   嵌套

select distinct pno
    -> from spj
    -> where sno in
    -> (select sno
    -> from s
    -> where city = '上海');

连接

select distinct pno
    -> from s,spj
    -> where s.sno = spj.sno and city = '上海';

6. 找出使用上海产的零件的工程名称

select jname
    -> from s,j,spj
    -> where s.city = '上海'and s.sno =spj.sno and j.jno = spj.jno;
+--------+
| jname  |
+--------+
| 三建   |
| 一汽   |
| 造船厂 |
| 造船厂 |
+--------+

7. 找出所有没有使用天津产的零件的工程号码(同第一个实验的4题相似)

select distinct jno
    -> from spj
    -> where jno not in
    -> (select jno
    -> from s,spj
    -> where sname = '天津'and s.sno = spj.sno);
+------+
| jno  |
+------+
| j1   |
| j3   |
| j4   |
| j2   |
| j5   |
+------+

8. 把全部红色零件的颜色改为蓝色

update p
    -> set color = '蓝'
    -> where color = '红';

9. 由s5供给j4的零件p6改为由s3供应，请做必要的修改

update spj
    -> set sno = 's3'
    -> where sno = 's5' and pno = 'p6' and jno = 'j4';

10. 从供应商关系中删除s2的记录，并从供应情况关系中删除相应的记录

//第一步
delete
    -> from s
    -> where sno = 's2';
//第二步
delete
    -> from spj
    -> where sno = 's2';

11. 请将(s2,j6,p4,200)插入供应情况关系

insert spj
    -> values
    -> ('s2','j6','p4',200);

实验总结

还不明白的

• 求没有使用天津供应商生产的红色零件的工程号jno；
  分析：此题的主干是工程号，工程没有使用。。。，所以是jno not in(),括号里面的内容是使用天津供应商生产的红色零件.错误原因是没有搞清主干，比较运算查询了使用红色零件并且不是天津供应商的工程号，但查询到的此工程可能有其他的供应情况，使用了红色零件或者是天津供应商。嵌套查询使用了把两个条件分开查询，
• 找出所有供应商s1所供应的零件的工程号码

select jno
    -> from spj
    -> where sno = 's1'

• 求至少用了供应商s1所供应的全部零件的工程号jno；

select jno
    -> from spj
    -> where sno = 's1'

第三个实验

实验内容

请为三建工程项目建立一个供应情况的视图，包括供应商代码（sno），零件代码（pno）,供应数量（qty）。针对该视图完成下列查询：

1. 找出三件工程项目使用的各种零件代码及其数量。
   创建视图：

create view vj
    -> as
    -> select sno,pno,qty
    -> from j,spj
    -> where j.jno = spj.jno and jname = '三建';

查询各种零件代码及其数量

select distinct pno,qty
    -> from vj;
+------+------+
| pno  | qty  |
+------+------+
| p1   |  200 |
| p3   |  400 |
| p5   |  400 |
| p3   |  200 |
| p5   |  100 |
+------+------+

2. 找出供应商s1的供应情况。

select *
    -> from vj
    -> where sno = 's1';
+------+------+------+
| sno  | pno  | qty  |
+------+------+------+
| s1   | p1   |  200 |
+------+------+------+