第一章資料庫單表習題A

• • 第一題：分析以下需求，并用代碼實作

已知“我愛你家”10月份銷售人員業績存放在selldb資料庫sell表里

工號 姓名 銷售房源 銷售額 工資

1. 寫出創建selldb資料庫，及按照以上格式創建sell表的sql陳述句

CREATE DATABASE mybase1 CHARACTER SET UTF8;

CREATE TABLE selldb(

ID INT(11) PRIMARY KEY AUTO_INCREMENT,

NAME VARCHAR(20),

sellnumbers varchar(20),

sellmoney INT(20),

money INT(20)

);

2.寫出sql陳述句完成以上表格中資料的插入

INSERT INTO selldb VALUES

(1,'郭風芝',3,900000,8000),

(2,'李清風',1,250000,5000),

(3,'楊曉初',0,0,4000),

(4,'霍幣',5,1000000,6000),

(5,'宋明',6,1200000,5000),

(6,'楊洋',2,502000,7000);

3.寫出統計10月份“我愛你家”一共銷售了多少套房源以及總銷售額（sql陳述句）

SELECT SUM(sellnumbers),SUM(sellmoney) FROM selldb;

4.寫出計算低于平均銷售額的員工姓名輸出到控制臺上（sql陳述句）

SELECT name FROM selldb WHERE sellmoney < (SELECT AVG(sellmoney) FROM selldb);

5.寫出按照銷售額的降序進行排列將銷售額前三名的工資分別上漲1000（sql陳述句）

SELECT * FROM selldb ORDER BY sellmoney DESC;

UPDATE selldb SET sellmoney = sellmoney + 1000 WHERE id IN(5,4,1);

6.寫出洗掉0銷售額的員工（sql陳述句）

SELECT id FROM selldb WHERE sellmoney<=0;

DELETE FROM selldb WHERE id IN (3);

• • 第二題：分析以下需求，并用代碼實作

模擬資料備份，已知有如下學生名單，內容如下:

姓名-年齡-性別-班級-分數

張三豐-103-男-iOS就業班-100

李峰-23-男-iOS基礎班-90

張飛-36-男-javaEE基礎班-40

田甜-23-女-UI基礎班-80

李根-40-男-javaEE就業班-9

朱迪-18-女-javaEE基礎班-100

將該檔案中的內容寫入到studb庫stutb表中，該表結構是

連接資料庫完成以下操作

1. 寫出創建studb資料庫，及按照以上格式創建stutb表的sql陳述句
2. CREATE TABLE stutb(
3. NAME VARCHAR(20),
4. age INT(11),
5. sex VARCHAR(20),
6. department VARCHAR(20),
7. score VARCHAR(20)
8. );

1. 寫出sql陳述句完成以上表格中資料的插入
2. INSERT INTO stutb VALUES
3. ('張三豐',103,'男','iOS就業班',100),
4. ('李峰',23,'男','iOS基礎班',90),
5. ('張飛',36,'男','javaEE基礎班',40),
6. ('田甜',23,'女','UI基礎班',80),
7. ('李根',40,'男','javaEE就業班',9),
8. ('朱迪',18,'女','javaEE基礎班',100);

3.寫出完成基礎班在讀的男學員的所有資訊按成績的降序輸出到控制臺上（sql陳述句）

SELECT * FROM stutb WHERE department IN ('iOS基礎班','javaEE基礎班','UI基礎班') ORDER BY score DESC ;

4.寫出將李根的年齡改為20，班級改為javaEE基礎班（sql陳述句）

UPDATE stutb SET age = 20 ,department = 'javaEE基礎班' WHERE NAME ='李根';

5.寫出洗掉低于javaEE基礎班平均分的javaEE基礎班學生（sql陳述句）

SELECT NAME FROM stutb WHERE score < (SELECT AVG(score) from stutb);

DELETE FROM stutb WHERE NAME IN('張飛','李根') ;

• • 第三題：分析以下需求，并用代碼實作

假設院校有一個資料庫schooldb中有一張student表，內容如下：（可以用自己的sql來創建資料庫以及表結構）（javaEE）

由于錄入資訊的老師不細心導致資料庫好多資訊是錯誤的，運用sql將資訊修改好，并且幫助老師統計一些資料

1.寫出創建schooldb資料庫，及按照以上格式創建student表的sql陳述句

CREATE TABLE student(

id INT(11) PRIMARY KEY AUTO_INCREMENT,

NAME VARCHAR(20),

sex VARCHAR(20),

department VARCHAR(20),

score INT(11)

);

2.寫出sql陳述句完成以上表格中資料的插入

INSERT INTO student VALUES

(1,'張三' ,'女','體育系',9),

(2 ,'李四','男','外語系',10),

(3, '娜娜', '女','中文系', 6),

(4,'凱蒂','男','外語系',7),

(5,'肖鹿','男','表演系',1),

(6,'菲菲','不詳','外星系',0);

3.菲菲不是本校學生，洗掉該記錄（sql陳述句）

DELETE FROM student WHERE id = 6;

4.缺失一名學生記錄，學生資訊自己定義（sql陳述句）

INSERT INTO student VALUES(7,'劉夢真','女','外星系',4);

5.按照系分組并統計每個系各有多少人（sql陳述句）count差點忘了

SELECT department,COUNT(department) FROM student GROUP BY department;

6.按照學分給表中所有的學員進行排序，按降序將學員姓名輸出到控制臺上（sql陳述句）

SELECT * FROM student ORDER BY score DESC ;

• • 第四題：分析以下需求，并用sql實作

CREATE TABLE zhangwu (

id INT PRIMARY KEY AUTO_INCREMENT, -- 賬務ID

zname VARCHAR(200), -- 賬務名稱

zmoney DOUBLE -- 金額

);

INSERT INTO zhangwu(id,zname,zmoney) VALUES (1,'吃飯支出',247);

INSERT INTO zhangwu(id,zname,zmoney) VALUES (2,'工資收入',12345);

INSERT INTO zhangwu(id,zname,zmoney) VALUES (3,'服裝支出',1000);

INSERT INTO zhangwu(id,zname,zmoney) VALUES (4,'吃飯支出',325);

INSERT INTO zhangwu(id,zname,zmoney) VALUES (5,'股票收入',8000);

INSERT INTO zhangwu(id,zname,zmoney) VALUES (6,'打麻將支出',8000);

INSERT INTO zhangwu(id,zname,zmoney) VALUES (7,NULL,5000);

1 查詢zname,zmoney的資料

格式:

select 列名1,列名2 from 表名

SELECT zname,zmoney FROM zhangwu;

2 查詢所有列的資料

格式:

select * from 表名

SELECT * FROM zhangwu;

3 查詢去掉重復記錄的zname

DISTINCT 關鍵字 跟隨列名

SELECT DISTINCT zname FROM zhangwu;

4 查詢重新命名列,為zname重命名為name

as 關鍵字

SELECT zname AS NAME FROM zhangwu;

5 查詢資料中,直接進行數學計算,查詢zmoney并+1000

列對數字進行計算

SELECT zmoney + 1000 FROM zhangwu;

6 查詢所有的吃飯支出

SELECT SUM(zmoney) FROM zhangwu WHERE zname = '吃飯支出';

7 查詢金額大于1000 的資料

SELECT * FROM zhangwu WHERE zmoney > 1000;

8 查詢金額在2000到5000之間 的資料

SELECT * FROM zhangwu WHERE zmoney >2000 && zmoney <=5000;

9查詢金額是 1000,3500,5000 其中一個的資料、

SELECT * FROM zhangwu WHERE zmoney IN (1000,3500,5000);

10 查詢所有的支出，使用模糊查詢

-- like 模糊查詢 配合通配符

SELECT * FROM zhangwu WHERE zname LIKE '%%%%%';

11查詢賬務名字,五個字符的、

SELECT * FROM zhangwu WHERE CHAR_LENGTH(zname)=5;

12查詢賬務名,不為空的

SELECT *FROM zhangwu WHERE zname IS NOT NULL;