sql workbench使用 t-sql语句

本文链接：https://blog.csdn.net/weixin_44769592/article/details/102708019

点击
在这里插入图片描述
建立数据库Market

apply
在这里插入图片描述

PK：primary key 主键
NN：not null 非空
UQ：unique 唯一索引
BIN：binary 二进制数据(比text更大)
UN：unsigned 无符号（非负数）
ZF：zero fill 填充0 例如字段内容是1 int(4), 则内容显示为0001
AI：auto increment 自增

use JWGL;
create table SC
(
Sno Char(8) not null Foreign key,
Cno char(10) not null,
Grade tinyint
);

use JWGL;
create table SC
(
Sno Char(8) not null primary key,
Cno char(10) not null,
Grade tinyint
);
create table Course
(
Cno Char(10) not null,
Cname Varchar(20) not null,
Total_perior tinyint,
Week_perior Tinyint,
Credit Tinyint not null,
Pcno char(10)
);

在这里插入图片描述

use SPJ;
create table S
(
	Sno Char(8) not null,
	Cname char(10) not null,
	Statues int,
    City char(10)
);
create table P
(
	Pno Char(10),
	Pname Varchar(20),
	Color char(10),
	Weight int
);
create table J
(
	Jno char(10),
    Jname char(10),
    City char(10)
    
);
create table SPJ
(
	Sno Char(8) not null,
	Pno Char(10),
	Jno char(10),
	Qty int
);
insert into S(Sno,Sname,Status,City)
values('S1','精益',20,'天津');
insert into S(Sno,Sname,Status,City)
values('S2','精益',20,'天津');
insert into S(Sno,Sname,Status,City)
values('S3','精益',20,'天津');
insert into S(Sno,Sname,Status,City)
values('S4','精益',20,'天津');
insert into S(Sno,Sname,Status,City)
values('S5','精益',20,'天津');

insert into P(Pno,Pname,Color,Weight)
values('P1','螺母','红',12);
insert into P(Pno,Pname,Color,Weight)
values('P2','螺栓','绿',17);
insert into P(Pno,Pname,Color,Weight)
values('P3','螺丝刀','蓝',14);
insert into P(Pno,Pname,Color,Weight)
values('P4','螺丝刀','红',14);
insert into P(Pno,Pname,Color,Weight)
values('P5','凸轮','绿',40);
insert into P(Pno,Pname,Color,Weight)
values('P6','齿轮','蓝',30);

insert into SPJ(Sno,Pno,Jno,Qty)
values('S1','P1','J1',200)

运行：

在这里插入图片描述

右键点击Refresh All
刷新

在这里插入图片描述

use Market;
create table Custumers
(
	Cid int(4) primary key,
	Cname char(10),
	Adress char(50),
    City char(10),
    Phone_number int(20),
    Corporations char(20),
    Birthday int(20),
    Category char(10)
);
create table Goods
(
	Gid Char(8) not null,
	Gname char(10) not null,
	Price int,
    Gdescribe char(10),
    Mount int(10),
    Business char(10) ,
    Statu char(10)
);
create table Orders
(
	Oid Char(8) not null,
	Gid char(10) not null,
	Omount int,
    Allprice char(10),
    riqi int(10)
);

use SPJ;
insert into S
(
	Sno,Cname,Statues,City
)values("a","sheng",1,"北京");

在这里插入图片描述

use SPJ;
select * from S

在这里插入图片描述

use SPJ;
delete from S WHERE Cname='sheng';

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

use SPJ;
SET SQL_SAFE_UPDATES = 0;
delete from S WHERE Cname='sheng';

在这里插入图片描述

在使用mysql执行update的时候，如果不是用主键当where语句，会报如下错误，使用主键用于where语句中正常。
异常内容：Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Queries and reconnect.
这是因为MySql运行在safe-updates模式下，该模式会导致非主键条件下无法执行update或者delete命令，执行命令SET SQL_SAFE_UPDATES = 0;修改下数据库模式

联系：
1.varchar/varchar2用于存储可变长度的字符串
比如varchar（20），存入字符串’abc’,则数据库中该字段只占3个字节，而不是20个字节
2.size 的最大值是 4000，而最小值是 1，其值表示字节数，比如
varchar（20）表示最大可以存放20个字节的内容

学生课程数据库

INSERT INTO Student(StudentNo,LoginPwd,StudentName,Sex,GradeId,Phone,Address,BornDate,Email)
VALUES(‘002’,‘12345’,‘李四’,‘男’,1,‘1234567890123’,DEFAULT,‘2012-10-01’,‘ABC@126.COM’),
(‘003’,‘12345’,‘王五’,‘男’,1,‘1234567890123’,DEFAULT,‘2012-10-01’,‘ABC@126.COM’)

use SWGL;
create table Student
(
	Sno int(10) PRIMARY key,
    Sname char(10),
    Ssex  CHAR(2) default '男' CHECK (Ssex in('男','女')),
    Sage int(10),
    Sdept char(4)
);
create table Course
(
	Cno int(10) PRIMARY key,
    Cname char(10),
    Cpno  int(2),
    Ccredit int(10)
);
create table SC
(
	Sno int(10),
    Cno char(10),
    Grade int(4)
);
INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept)
VALUES(201215121,"李勇",'男',20,"CS"),
(201215122,"刘晨",'女',19,"CS"),
(201215123,"王敏",'女',18,"MA"),
(201215124,"张立",'男',19,"IS");
INSERT INTO Course(Cno,Cname,Cpno,Ccredit)
VALUES(1,"数据库",5,4),
(2,"数学",null,2),
(3,"信息系统",1,4),
(4,"操作系统",6,3),
(5,"数据结构",7,4),
(6,"数据处理",null,2),
(7,"PASCAL语言",6,4);
insert into SC(Sno,Cno,Grade)
VALUES(201215121,1,92),
(201215121,2,85),
(201215121,3,88),
(201215122,2,90),
(201215122,3,80)

3.16 查询全体学生的学号和姓名和所在系

use JWGL;
select Sno,Sname,Sdept
FROM Student

3.18

use JWGL;
select Sno,Sname,Ssex,Sage,Sdept
FROM Student

等价于在这里插入图片描述

select *
from Student

第三章习题五

USE SPJ;
create table J
(
	Jno char(10),
    Jname char(10),
    City char(4)
);
INSERT INTO J(Jno,Jname,City)
VALUES("J1","三建","北京"),
("J2","一汽","长春"),
("J3","弹簧厂","天津"),
("J4","造船厂","天津"),
("J5","机车厂","唐山"),
("J6","无线电厂","常州"),
("J7","半导体厂","南京")

修改列名

use SPJ;
ALTER TABLE S RENAME COLUMN Cname TO Sname;

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

use SPJ;
select Sname,City
from s

在这里插入图片描述

找出所有零件的名称，颜色，数量

SELECT Pname,Color,weight
from P

在这里插入图片描述

找出供应商s1所提供的零件的工程号码

SELECT jno from SPJ where Sno='S1'

在这里插入图片描述

找出工程项目J2 使用的各种零件的名称及数量在这里插入图片描述

select P.Pname,SPJ.Qty
from P,SPJ
where P.Pno=SPJ.Pno
and SPJ.Jno='J2';

找出上海厂商供应的所有零件的号码

select distinct Pno
from spj
where sno in
(select sno
from s
where city='上海')

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

select Jname
from J,SPJ,S
where J.Jno=SPJ.Jno
and SPJ.Sno=S.Sno
and S.City='上海'

找出没有使用天津产的零件的工程号码

select Jno
from J
WHERE NOT EXISTS
(select *
from SPJ
where SPJ.JNO=J.JNO
and SNO IN(
	select SNO
	FROM S
	WHERE CITY='天津'))

在这里插入图片描述

把全部红色零件的颜色改成蓝色

updata P
set Color='蓝'
where Color='红'

由s5供给J4的零件，P6改为由S3供应请做必要修改

update
SPJ
set Sno=‘S3’
where Sno=‘s5’
and Jno=‘J4’
and Jno=‘J6’

从供应商关系中删除S2的记录并从供应情况关系中删除相应记录
delete
from SPJ
where Sno=‘S2’;
delete
from S
where Sno=“S2”

请将（S2 ，J6，P4，200）插入供应情况关系
insert into SPJ(Sno,Pno,Jno,Qty)
values(“S2”,“P6”,“J4”,200)