第一章資料庫單表習題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）

id	name	sex	department	score
1	張三	女	體育系	9
2	李四	男	外語系	10
3	娜娜	女	中文系	6
4	凱蒂	男	外語系	7
5	肖鹿	男	表演系	1
6	菲菲	不詳	外星系	0

由於錄入資訊的老師不細心導致資料庫好多資訊是錯誤的，運用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;