期末上机
和表相关的操作
创建表
create table S
(SNO char(3) PRIMARY KEY,
SNAME varchar(20),
STATUS enum('10','20','30'),
CITY varchar(20)
);
插入数据
insert into S values('S1','精益','20','天津')
给固定的某个码插入
INSERT
INTO S(SNO,SNAME,STATUS,CITY)
VALUES('S6','兴盛',null,'南昌')
用查询的值插入
INSERT
INTO course_a(cno,avggrade,maxgrade,mingrad)
SELECT Cno,AVG(Grade),MAX(Grade),MIN(Grade) FROM sc GROUP BY Cno
删除数据
DELETE FROM sc WHERE Cno IN(SELECT Cno FROM course WHERE Cname='大学英语')
DELETE FROM course_a WHERE Cno IN(SELECT Cno FROM course WHERE Cname='大学英语')
DELETE FROM course WHERE Cname='大学英语'
更新数据
update sc
set grade=grade+2
where cno='011';
查找数据
select语句的一般格式:
select 目标列
from 哪个表
where 条件表达式
group by 列名1 having 条件表达式
order by 列名2 desc / asc
- 单表简单查询
查询指定列、全部列、经过计算的值 - 选择表中的若干元组
distinc、all、比较大小、确定范围、确定集合、字符匹配、排序
DESC 升序
ASC 降序
- 聚集函数
- count(distinct|all 列名)
- sum(distinct|all 列名)
- avg(distinct|all 列名)
- max(distinct|all 列名)
- min(distinct|all 列名)
注意:聚集函数不能作为where的条件子句,只能用来作为group by中having 的子句
- GROUP BY子句
分组后聚集函数将作用于每一个组
和数据库有关的操作
a) 创建stu_database数据库,并指定字符集为utf8;
CREATE DATABASE stu_database CHARACTER SET UTF8
b) 查看所有数据库;
show DATABASES;
c) 查看stu_database数据库;
SHOW CREATE DATABASE stu_database
d) 修改stu_database的字符集为gbk 排序规则为 gbk_bin;
alter database stu_database character set gbk collate gbk_bin;
e) 删除stu_database。
视图
增删改查跟表的操作基本类似
create view name(属性1,属性2,属性3)
as
select语句
);
上机代码:
create database company;
use company;
/*员工人事表*/
CREATE TABLE employee(
emp_no char(5)not null primary key,
emp_name char(10) not null,
sex char(2) not null,
dept char(4) not null,
title char(6) not null,
date_hired datetime not null,
birthday datetime null,
salary int not null,
telephone varchar(20) null,
addr char(50) null
);
/*客户表*/
create table customer(
cust_id char(5) not null primary key,
cust_name char(20) not null,
addr char(40) not null,
tel_no char(20) not null,
zip char(6) null
);
/*销售主表*/
create table sales(
order_no int not null primary key,
cust_id char(5) not null,
sale_id char(5) not null,
tot_amt numeric(9,2) not null,
order_date datetime not null,
foreign key (cust_id) references customer(cust_id),
foreign key (sale_id) references employee(emp_no)
);
/*产品名称表*/
create table product(
prod_id char(5) not null primary key,
prod_name varchar(200) not null,
price NUMERIC(8,2),
prod_type varchar(50)
);
/*销货明细表*/
create table sale_item(
order_no int not null,
prod_id char(5) not null,
qty int not null,
unit_price numeric(8,2) not null,
constraint pk_sale_item primary key
(order_no,prod_id ),
foreign key (order_no) references sales(order_no),
foreign key (prod_id) references product(prod_id)
);
/*人事表(employee)数据:*/
insert employee values('E0001','王大华','男','业务','经理','2009-10-13','1983-08-01',80000,'13107912134','上海市');
insert employee values('E0002','陈自强','男','会计','科长','2013-09-15','1990-06-09',48000,'13307913451','南京市');
insert employee values('E0003','李小梅','女','业务','职员','2020-03-01','1997-03-28',32000,'13579607879','上海市');
insert employee values('E0004','陈建国','男','管理','科长','2010-04-15','1985-09-01',45000,'13879787765','天津市');
insert employee values('E0005','林光华','男','业务','职员','2015-10-13','1989-08-17',30000,'13979765654','上海市');
insert employee values('E0006','李珠珠','女','管理','经理','1998-01-01','1971-07-12',60000,'13679787667','北京市');
insert employee values('E0007','李庄敬','男','人事','科长','2010-09-15','1978-05-13',80000,'13979962335','广州市');
insert employee values('E0008','王成功','男','业务','职员','2020-02-15','1999-04-15',45000,'13723456789','北京市');
/* 客户(customer)表数据:*/
insert customer values('C0001','客户甲','上海市','13879765667','500100');
insert customer values('C0002','客户甲乙','天津市','13178788980','100300');
insert customer values('C0003','客户丙','北京市','13345566789','100100');
insert customer values('C0004','客户甲丁','上海市','13134455566','500210');
insert customer values('C0005','客户戊','北京市','13677889890','100120');
insert customer values('C0006','客户甲己','上海市','13767678899','500230');
insert customer values('C0007','客户庚','上海市','13522334567','500300');
/*销售(sales)主表数据:*/
insert sales values (10001,'C0001','E0008',60000.00,'2020-09-10');
insert sales values (10002,'C0002','E0003',22700.00,'2020-11-10');
insert sales values(10003,'C0003','E0005',13960.00,'2021-10-15');
insert sales values(10004,'C0003','E0005',33000.00,'2021-11-18');
insert sales values (10005,'C0007','E0008',20000.00,'2020-11-22');
/*产品名称(product)表数据:*/
insert product values ('P0001','西部数据(WD)蓝盘 500G机械硬盘电脑主机 品牌台式机 DIY主机硬盘 瀚达彩',159.00,'硬盘');
insert product values ('P0002','三星/SAMSUNG 850 EVO SATA3 2.5英寸笔记本台式机一体机服务器固态硬盘SSD 1TB(MZ-75E1T0B)',1199.00,'笔记本');
insert product values ('P0003','三星/SAMSUNG 笔记本台式机一体机电脑服务器SSD固态硬盘加装/替换升级提速盒装全国联保 SATA3 游戏作图设计主力款 1TB 5年联保',999.00,'笔记本');
insert product values ('P0004','戴尔DELL成就3400 14英寸轻薄性能商务全面屏笔记本电脑',4899.00,'笔记本');
insert product values ('P0005','华为笔记本电脑MateBook 14 2021款 14.0英寸11代酷睿i5 16G 512G 锐炬显卡/2K触控轻薄本 /多屏协同 深空灰',5799.00,'笔记本');
insert product values ('P0006','戴尔(DELL)S2421HSX 23.8英寸 IPS 旋转升降 低蓝光 FreeSync技术 三边微边框 可壁挂 家用办公 电脑显示器',1199.00,'显示器');
insert product values ('P0007','Redmi 1A 23.8英寸 IPS技术 三微边设计 低蓝光 HDMI接口 电脑办公显示器 显示屏 小米 红米',779.00,'显示器');
insert product values ('P0008','华为HUAWEI显示器S24 23.8英寸 IPS 1920×1080全高清 全面屏 75Hz 莱茵低蓝光无频闪 俯仰调节 快拆底座 灰',779.00,'显示器');
insert product values ('P0009','Redmi Note 9 Pro 5G 一亿像素 骁龙750G 33W快充 120Hz刷新率 湖光秋色 8GB+128GB 智能手机 小米 红米',1299.00,'手机');
insert product values ('P0010','荣耀Play5T Pro 6400万双摄 6.6英寸全视屏 22.5W超级快充 4000mAh大电池 全网通版8GB+128GB 钛空银',1289.00,'手机');
insert product values ('P0011','荣耀50 1亿像素超清影像 5G 6.57英寸超曲屏 66W超级快充 3200万像素高清自拍 全网通版8GB+128GB 初雪水晶',2499.00,'手机');
insert product values('P0012','Apple iPhone 12 (A2404) 128GB 白色 支持移动联通电信5G 双卡双待手机',4899.00,'手机');
insert product values('P0013','微星(MSI)MAG B560M MORTAR 迫击炮电脑主板 CPU 10400F/11400F/11600KF/10700/11700(INTEL B560/LGA 1200)',799.00,'主板');
insert product values('P0014','华硕(ASUS)TUF GAMING Z690-PLUS WIFI D4主板 支持内存DDR4 CPU 12700/12700KF(Intel Z690/LGA 1700)',2199,'主板');
/*销售明细表(sale_item)数据:*/
insert sale_item values (10001,'P0001',5 ,2500.00);
insert sale_item values (10001,'P0002',3 , 6500.00);
insert sale_item values (10001,'P0003',2 , 5300.00);
insert sale_item values (10002,'P0004',2 , 1600.00);
insert sale_item values (10002,'P0001',3 , 2600.00);
insert sale_item values (10002,'P0003',1 , 5300.00);
insert sale_item values (10003,'P0008',2 , 4800.00);
insert sale_item values (10003,'P0001',4 , 2700.00);
insert sale_item values (10004,'P0004',2 , 1580.00);
insert sale_item values (10004,'P0007',4 , 2700.00);
insert sale_item values (10005,'P0014',2 , 1580.00);
insert sale_item values (10005,'P0012',1 , 4500.00);
insert sale_item values (10005,'P0009',1 , 1180.00);
select * from sales;
select * from sale_item;
select * from product;
select * from customer;
select * from employee;
第一题:
create table book_bar_code
(id tinyint(3) PRIMARY KEY AUTO_INCREMENT,
bar_code varchar(30),
isbn varchar(20),
book_state char(8)
);
insert INTO book_bar_code(bar_code,isbn,book_state) values('9787040406641001','9787040406641','在馆');
insert INTO book_bar_code(bar_code,isbn,book_state) values('9787040406641002','9787040406641','在馆');
insert INTO book_bar_code(bar_code,isbn,book_state) values('9787111657088001','9787111657088','借出');
insert INTO book_bar_code(bar_code,isbn,book_state) values('9787111657088002','9787111657088','借出');
第二题:
select * from customer where addr='天津市' and cust_name like '%客户甲%'
第三题:
select cust_id,sum(tot_amt) as tot_amt,
(
case
when tot_amt>=30000 then '消费高'
when tot_amt<30000 then '消费一般'
end
) as counsume_status
from sales GROUP BY cust_id
第四题:
CREATE VIEW v_sale
as
SELECT employee.emp_no,employee.emp_name,employee.dept,sales.order_no,order_date,sales.tot_amt from employee LEFT JOIN sales on
employee.emp_no=sales.sale_id
SELECT * from v_sale where tot_amt>=2000
第五题:
DELETE from sale_item where order_no='1002' and prod_id='P001'
update sale_item
set qty=6
where prod_id='P0003'
update sales
set tot_mat=