資料庫

1.1 資料庫基礎

1.1.1 選擇

1.1.1.1 Having 子句的作用是(C )。

A.查詢結果的分組條件 B.組的篩選條件

C.限定返回的行的判斷條件 D.對結果集進行排序

1.1.1.2 最常用的一種基本數據模型是關係數據模型，它的表示採用（D）

A.樹 B.網路 C.圖 D.二維表

1.1.1.3 資料庫系統的核心是（B）

A.數據模型 B.資料庫管理系統 C.資料庫 D.資料庫管理員

1.1.1.4 關係表中每一橫行稱為（B）

A.元組 B.欄位 C.屬性 D.碼

1.1.1.5 資料庫管理系統維護中，最重要的是（B）

A程式維護 B數據維護 C編碼維護 D硬體維護

1.1.1.6 以下那些不是NoSQL(AD)

A.mssql B memcached C mongodb D postgresql E cassandra F couchdb G redis

1.1.1.7 PHP和 MYSQL的聯合使用解決了（C）

A 在proxy上處理資料庫訪問問題

B在www伺服器上處理黑客的非法訪問問題

C 在www伺服器上處理資料庫的訪問問題

D 在sendmail郵件系統是上處理資料庫的訪問問題

1.1.1.8 以下哪個埠為遠程登陸預設埠(C)

A1433 B3389 C3306

1.1.2 填空

1.1.2.1 如何通過shell登錄mysql？（mysql -u -p -P -h）

1.1.2.2 資料庫觸發器能監控的觸發事件有——監視地點（table）—監視事件（insert，update，delete）———觸發時間（after，before）—觸發事件（insert，update，delete）—

1.1.2.3 資料庫系統的組成部分有：硬體、軟體、資料庫、用戶（資料庫管理員）

1.1.2.4 實現多台交換機或集線器連接在一起只能通過級聯的方式（級聯複製）

1.1.2.5 遠程連接mysql資料庫的命令是什麼_____mysql -u -p -P -h_

1.1.2.6 主資料庫文件的擴展名是___mdf__，事務日誌文件的擴展名是_ldf_

1.1.2.7 當刪除一個資料庫後，僅能用__binlog,物理備份__數據重建以前的資料庫中的數據

1.1.2.8 SQL中定義別名的三種方法是_______列 AS 別名，列 別名 列=別名____________________

1.1.2.9 查詢病人表中的所有內容，並以住院號倒序排序SQL代碼為：Select * from pat_visit ______order by___inp_to_____desc

1.1.2.10 查詢一根病人的信息。但忘記了該病人的名字，只記得該病人姓“張”，而且名字中有一個“美”字，SQL代碼為：Select * from Pat_visit where name__like "張%"__

1.1.2.11 mysql伺服器預設的監聽埠是：3306_ ，mysql的配置文件是：____/etc/my.cnf

1.1.3 簡答

1.1.3.1 簡述mysql單機安裝過程

①官網下載二進位安裝包；

②解壓二進位包；

③修改配置文件/etc/my.cnf

④初始化配置；

⑤啟動

1.1.3.2 mysql登錄命令

mysql -u -p -P -h

1.1.3.3 在mysql客戶端查詢工具中，如何獲取當前的所有連接信息。

show full processlist

1.1.3.4 如何利用crontab定期執行mysql語句。

mysql -u -p -e "show databases;"

1.1.3.5 說明資料庫記憶體結構中SGA和PGA的組成，以及這兩個記憶體區存放信息的區別

SGA區由數據緩衝區，共用池，重做日誌緩衝區，大型池，java池構成；PGA區由排序區，私有SQL區以及堆棧構成。
SGA區是由Oracle分配的共用記憶體結構，包含一個資料庫實例共用的數據和控制信息。當多個用戶同時連接同一個實例時，SGA區數據供多個用戶共用，所以SGA區又稱為共用全局區。用戶對資料庫的各種操作主要在SGA區中進行。該記憶體區隨資料庫實例的創建而分配，隨實例的終止而釋放。PGA區是在用戶進程連接資料庫，創建一個會話時，由oracle為用戶分配的記憶體區域，保存當前用戶私有的數據和控制信息，因此該區又稱為私有全局區。每個伺服器進程只能訪問自己的PGA區，所有伺服器進程PGA區總和即為實例的PGA區的大小。

1.1.3.6 說明資料庫表空間的種類，以及不同類型表空間的作用

共用表空間，獨立表空間

共用表空間：一個數據的文件都存放在一個文件內。

獨立表空間：一個表有一個idb和frm文件。

1.1.3.7 mysql表中的中文字體亂碼，原因可能是什麼？如何修改

字元集編碼不一致問題

alter table 表名 default character set utf8 collate utf8_general_ci;

1.1.3.8 mysql忘記密碼如何操作

--skip-grand-tables #跳過 授權表

--skip-networking #跳過網路連接

1.1.3.9 請寫出目前市場上最常見的2-5種關係型資料庫廠家，並註明他們產品的最新版和你熟悉的或者解除過的版本：

廠家： 最新版本： 熟悉的版本： 接觸過的版本

mysql 5.6 5.7

1.1.3.10 如何修改mysql的root密碼

mysqladmin -uroot -p password oldboy123

set password for 用戶@localhost=password('新密碼')

mysql> alter user '用戶名'@'登錄主機' identified by '密碼(自定義)';

1.1.3.11 請舉出幾個你熟悉的關係型資料庫管理系統和非關係型資料庫管理系統，非關係型資料庫相比之下有什麼不同之處（好處和不足）？

關係型：mysql，oracle

非關係型：redis，mongodb

非關係型數據快，操作簡單，但是又丟數據風險。

1.1.3.12 存儲過程與函數的區別

存儲過程是用戶定義的一系列sql語句的集合，涉及特定表或其它對象的任務，用戶可以調用存儲過程，而函數通常是資料庫定義的方法，它接收參數返回某種類型的值並且不涉及特定用戶表。

1.1.3.13 什麼是視圖，游標是什麼？

視圖是一種虛擬的表，具有和物理表相同的功能，可以對視圖進行增改查操作，視圖通常是有一個表或者多個表的行或者列的子集。對視圖的修改不影響基本表。它使得我們獲取數據更容易，相比多表查詢。

游標：是對查詢出來的結果集作為一個單元來有效的處理。游標可以定在該單元中的特定行，從結果集的當前行檢索一行或多行。可以對結果集當前行做修改。一般不使用游標，但是需要逐條處理數據的時候，游標顯得十分重要。

1.1.3.14 你對資料庫瞭解嗎？你們公司的資料庫是什麼樣的？

高可用mysql及主從架構和讀寫分離。

1.1.3.15 你們公司資料庫最大連接數是多少？

2000

1.1.3.16 查看預設引擎

select @@default_storage_engine;

1.1.3.17 MySQL資料庫storage engine中的myisam、innodb有什麼差別

myisam:表級鎖，不支持事務，支持溫備。

innodb：行級鎖，支持事務，支持溫備。自動故障恢復，外鍵。

1.1.3.18 請列舉幾個常見的nosql db 你最熟悉那個？

redis,mongodb,memcached

1.2 SQL語句

1.2.1 選擇

1.2.1.1 DELETE FROM S WHERE 年齡>60語句的功能是（A）

A.從S表中徹底刪除年齡大於60歲的記錄

B.S表中年齡大於60歲的記錄被加上刪除標記

C.刪除S表

D.刪除S表的年齡列

1.2.1.2 使用什麼命令可以清除表中所有的內容?(CD)

A.INSERT B.UPDATE C.DELETE D.TRUNCATE

1.2.1.3 以下哪個表不用於mysql 的許可權管理( D)

A.HOST B.DB C.COLUMNS_PRIV D.MANAGER

1.2.1.4 在SOL 語法中，用於更新的命令是:( B)

A.INSERT B.UPDATE C.DELETE D.CREATE

1.2.1.5 下麵命令中不是資料庫用來DML操作是( A)

A.SELECT B.UPDATE C.INSERT D.DROUP

1.2.1.6 SQL 語言中，刪除一個表的命令是( B)

A.DELETE B. DROP C.CLEAR D.RI MORE

1.2.1.7 MySQL語句中刪除一個表（假如表名稱是t1）的命令是_______C_

Arm table t1 Bdelete table t1 Cdrop table t1 Dtruncate t1

1.2.1.8 下麵哪個語句將user表的名稱變更為userinfo？(C)rename table user to userinfo

A.alter table user rename as userinfo B .rename to userinfo from user

C.rename user to userinfo D.rename user as userinfo

1.2.1.9 sql語句中修改表結構的命令是(C)

Amodify table Bmodify structure Calter table Dalter structure

1.2.1.10 帶有（B）子句的select語句可以在表的一行或多行上防止排他鎖

A.FOR INSERT B.FOR UPDATE C.FOR DELETE D.FOR REFRESH

1.2.1.11 在SQL中，建立表用的命令是（B）

A.CREATE SCHEMA B.CREATE TABLE C.CREATE VIEW D.CREATE INDEX

1.2.1.12 SQL 語言是（C）語言

A層次資料庫 B網路資料庫 C關係資料庫 D非資料庫

1.2.1.13 SQL語言中，條件年齡BETWEEN 15 AND 35表示年齡在15至35之間，且（A）

A包括15歲和35歲 B不包括15歲和35歲

C包括15歲但不包括35歲 D包括35歲但不包括15歲

1.2.1.14 在資料庫管理系統能實現對資料庫中的數據查詢、插入、修改和刪除，這類功能稱為（C）

A數據定義功能 B數據管理功能 C數據操縱功能 D數據控制功能

1.2.1.15 下列執行數據的刪除語法在運行時不會產生錯誤信息的選項是（A）

A.Delete * from pat_visit Where inp_no = '6' B.Delete From pat_visit Where inp_no ='6'

C.Delete pat_visit Where inp_no = '6' D.Delete pat_visit Set inp_no = '6'

1.2.2 填空

1.2.2.1 在SQL中，用子句————DISTINCT——————消除重覆出現的元組。

1.2.2.2 標準的SQL語言語句類型可以分為—DML—、—DQL—、—DDL—、DCL

1.2.2.3 SQL語句中的查詢語句是select（DQL）

1.2.2.4 字元轉換日期函數___select date_format(now(),'%Y-%m-%d')_

1.2.2.5 DML包括__insert__ update delete

1.2.2.6 簡述where和having的區別：

Where 子句是用來指定 "行" 的條件的，而 Having 子句是指定 “組” 的條件的

所以 Where 子句 = 指定行所對應的條件

所以 Having 子句 = 指定組所對應的條件

當在 Where 子句和 Having 子句中都可以使用的條件，從語句的執行效率來看，最好寫在 Where 子

句中。

在使用 Count 函數等對錶中的數據進行聚合操作時，DBMS 內部會進行排序處理，而排序操作會增

加機器的負擔，減少排序的行數，可以增加處理速度。

使用 Where 子句指定條件時，由於排序之前就對數據進行了過濾，所以能夠減少排序的數據量。但

是 Having 子句是在排序之後才對數據進行分組的，因此與前者相比，需要排序的數據量就要多得

多。

第三，使用 Where 子句更具速度優勢的另一個理由是，可以對 Where 子句指定條件所對應的列創建

索引，這樣可以大幅提高處理速度。

第四，Where 子句中不能使用聚合函數，而 Having 子句中可以。

1.2.2.7 在SQL中，建立資料庫的命令是___create databases;_，建立視圖的命令是___create vim v as (select * from table1)union all (select * from table2)，建立索引的是_______alter table student add index idx_name(name)

1.2.2.8 在SQL中，用___insert___命令向表中輸入數據，用__select___命令檢查和查詢表中的內容

1.2.2.9 在SQL中，用___update___命令可以修改表中的數據，用__alter___命令可以修改表的結構

1.2.2.10 在SQL中，Alter命令可以有兩個選擇項，___alter table change/modify___子命令用於修改表的性質，__alter table add__子命令用於增加列

1.2.2.11 在SQL中，用__delete___命令可以從表中刪除行，用___drop__命令可以從資料庫中刪除表

1.2.2.12 在SQL中，控制用保留字___null___，非空值用保留字___not null__

1.2.2.13 SELECT命令中，表示條件表達式用__where__字句，分組用___group by__字句，排序用__order by字句

1.2.2.14 在ORDER BY字句的選項中，DESC代表__倒序___序輸出，若省略時，代表__正序__序輸出

1.2.2.15 合併多個查詢結果集，應使用__union___關鍵字

1.2.3 簡答

1.2.3.1 列舉SQL常用命令：

show databases;

create database;

select * from table;

update table set state

1.2.3.2 Mysql查看order表的第3條到第7條的數據。

select * from order limit 2,5

1.2.3.3 Mysql授予用戶admin通過IP為192.168.0.10/24對資料庫的全部授權。

grand all on *.* to admin@'192.168.0.%' identified '123';

1.2.3.4 對order這個表的name欄位數據進行base64編碼

select to_base64(name) from 'order';

1.2.3.5 為什麼說group by和order by操作的是代價高昂的？

沒有合理的索引條件下，需要大量的CPU時間來排序和處理。

1.2.3.6 寫分組瀏覽，分組求和，分組求最大值的sql語句，每個只能用1句話

select group_contat(欄位1) from db group by '欄位2'

select sum(欄位1) from db group by '欄位2'

select max(欄位1) from db group by "欄位2"

1.2.3.7 如何列出所有資料庫？（show databases;）

1.2.3.8 如何查看表內所有數據？（select * from table;）

1.2.3.9 如何知道表內行數？（select count(*) from table;）

1.2.3.10 用SQL實現以下統計結果

CSBH CSBH_COUNT

20 2

10 8

CSBH SUM_GJQJ

20 211.039900

10 871.317850

select CSBH,count(CSBH) as CSBH_COUNT from table group by CSBH;
select CSBH,sum(CSBH) as SUM_GJQJ from table group by CSBH;

1.2.3.11 用SQL命令實現給主鍵添加索引

alter table oldboy add primary key pri(id);

1.2.3.12 刪除如下表中年齡大於30，且住址為nj的員工

表A user_table

Name age city

Tom 20 bj

Jimmy 50 nj

Zhou 33 sh

Jing 30 nj

Nanan 28 sh

create table user_table(id int(11),name varchar(20),age int,city varchar(20)) engine=innodb;
insert into user_table(name,age,city) values(tom,20,bj),(jimmy,50,nj),(zhou,33,sh),(jing,30,nj),(nannan,28,sh);

1.2.3.13 根據上題的表，計算出每個城市的人數和平均年齡

select city,sum(name),avg(age) from user_table group by city;

1.2.3.14 查詢下表平均成績大於60分的同學的學號s_d)和平均成績(score)

表Student 學生表

S_id Sname Ssex

1 Xiaoli 男

2 xiaowang 女

3 zhanglong 男

4 zhaohu 女

create table student(s_id int(11),sname varchar(20),ssex varchar(20)) engine=innodb;
insert into student(s_id,sname,ssex) values(1,xiaoli,男)，（2，xiaowang，女），（3，zhanglong，男），（4，zhaohu，女）；

表 Course 課 程 表

C_id Cname T_id

1 語文 1

2 歷史 3

3 數學 2

4 政治 4

create table course(c_id int(11),cname varchar(20),t_id int(20)) engine=innodb;
insert into course(c_id,cname,t_id) values(1,語文，1)，（2，歷史，3），（3，數學，2），（4，政治，4）

表SC 成績表

s_id C_id score

1 2 99

3 4 88

1 3 55

2 1 77

create table sc(s_id int(11),c_id int(11),score varchar(20)) engine=innodb;
insert into sc(s_id,c_id,score) values(1,2,99),(3,4,88),(1,3,55),(2,1,77);

表Teacher 教師表

T_id Tname

1 zhangsan

2 lisi

3 wangwu

4 zhaoliu

create table teacher(t_id int(11),tname varchar(20)) engine=innodb;
insert into teacher(t_id,tname) values(1,zhangsan),(2,lisi),(3,wangwu),(4,zhouliu);

查詢上所有同學的學號姓名、選課數、總成績

①select student.s_id,avg(sc.score) from student join sc on student.s_id=sc.s_id having avg(sc.score)>60;
②select student.s_id,student.sname,count(sc.cname),sum(sc.score) from student join sc on student.s_id=sc.s_id

1.2.3.15 有兩個表A和B，表結構和欄位分別為：

A： B

ID Name ID hobby

1 tim 1 football

2 Jimmy 2 tennis

3 John 3 soccer

請寫出Select A.Name, B.Hobbt from A,B where A.id = B.id的結果

select a.name,b.hobbt from a jon b on a.id=b.id

1.2.3.16 寫出MySQL5.6新建用戶dev，授權op庫test表所有許可權，任何地址的訪問許可權SQL

grand all on op.test to dev@'%' identified '123';

1.2.3.17 寫出一條sql語句：取出表A中滿足時間2006年1月1日至2006年1月31日的記錄（時間欄位為time）

select * from table where time between '2006-01-01' and '2006-01-31';

1.2.3.18 寫出一條SQL語句：清除A表中的數據，但不刪除表

delete * from tablename;或者truncate table name;

1.2.3.19 查找在機關單位任職的人員姓名、性別、出生日期、單位名稱、並且查詢結果按照單位排序；

select name,sex,birthday,company from table order by company desc;

1.2.3.20 查找性別為女的少數民族的非中共人員姓名、民族、出生日期；

select name,mingzu,birthday from table where sex='女' and 面貌！=中共黨員；

1.2.3.21 在事業單位查找具有研究生學歷博士學位的在任人員，要求顯示姓名、性別、出生日期、政治面貌、單位名稱、職務名稱、批准任職日期，同時結果按照批准任職日期升序；

select name,sex,birthday,政治面貌，company，職務，批准日期 from table where xueli=boshi order by 批准日期；

1.2.3.22 查詢在不同單位性質類別的單位任職的人數；

select count(name) from table group by company;

1.2.3.23 請寫出下列語句的執行結果：select replace (ltrim(rtrim('abc3453436')),'34','abc')

abcabc53abc36

1.2.3.24 寫一個查詢語句，從資料庫表中取出年齡age最大的十個人

select name from table order by age desc limit 10;

1.2.3.25 編寫SQL語句。統計電腦系58班每個月過生日的男生人數，井按月進行排序

表 dept (系):

dept_id(系編號)，dept_name(系名稱)

表 class(班級)：

class_id(班編號)，dept_i(系編號)，class_name(班名)

表 setdent(學生)：

setdent_id(學生編號),class_id(班編號), sex(性別),birthday(生日)

select count(setdent.setdent_id),date_format(setdent.birthday,%m) from setdent join class on setdent.class_id=class.class_id join deptp on dept.dept_id=class.dept_id where dept.dept_name='電腦' and sex='男' group by Date_format(setdent.birthday,%m) order by date_format(setdent.birthday,%m) desc;

1.2.3.26 現有T表：（欄位：ID，NAME，ADDRESS，PHONE，LOGDATE）

查詢T表中地址中包含“北京”的SQL語句

插入一條數據到T表中

請寫出將T表中第3~5行數據列出來的SQL語句

select * from t where ADDRESS="%北京%"；

insert into t(ID,NAME,ADDRESS,PHONE,LOGDATE) values(,,,,);

select * from t limit 2,3;

1.2.3.27 Mysql創建資料庫的命令，創建表的命令，插入語句的命令？

create database

create table ;

insert into t()values()

1.2.3.28 資料庫中的兩個資料庫表，定義如下：

表名1：cardApply

欄位（欄位名/類型/長度）：

applyNo varchar 8; //申請單號（關鍵字）

applyDate date 8; //申請日期

state varchar 2; //申請狀態

表2：cardApplyDetail

欄位（欄位名/類型/長度）：

applyNo varchar 8; //申請單號（關鍵字）

name varchar 30; //申請人姓名

idCard varchar 5; //ID卡號

applyNo varchar 8; //申請單號（關鍵字）

其中，兩個表的關聯欄位為申請單號，依據上述信息完成1-5題目：

查詢ID卡號為11111的申請日期

select cardApply.applydate from cardApply join cardApplyDetail on cardApplyDetail.applyNo=cardApply.applyNo where cardApplyDetail.idCard ='11111';

查詢同一個ID卡號有兩條以上記錄的ID卡號及記錄個數

select idCard,count(idCard) from cardApplyDetail group by idCard having count(idCard )>2;

將ID卡號為11111的記錄在兩個表中的申請狀態均改為02

update cardApplyDetail set state='02' where idCard='11111';

刪除cardApplyDetall 表中所有姓李的記錄

delete * from cardApplyDetall where name='李%'；

假如某一天有10個人申請，另外一天有20個人申請，還有一天有30個人申請，查詢出申請卡最多的那一天日期

select applyDate,count(applyNo) from cardApply group by applyDate order by count(applyNo) desc limit 1;

1.2.3.29 第一題

Student S# Sname Sage Ssex 學生表

Course C# T# Cname () 課程表

SC S# C# score 成績表

Teacher T# tname 教師表

寫出學生表建表語句

create table student( s# int,not null,primary key auto_increment,sname varchar(20) not null,sage int not null,ssex enum('m','f') not null default 'm') engine=innodb charset='utf-8';

查詢姓張的學生名單

select * from student where name='張%'；

查詢所有同學的學號、姓名、選課數、總成績

select student.s#,student.sname,count(sc.c#),sum(sc.score) from student join sc on sc.s#=student.s# group by student.sname;

查詢選修“葉平”老師所授課程的學生中，成績最高的學生姓名及其成績

select student.sname,max(score),avg(score) from student join sc on student.s#=sc.s# join course on course.c#=sc.course.c# join teacher on course.t#=teacher.t# group by teacher.tname where teacher.tname='葉平'；

1.2.3.30 第二題

第一題中的教師表中哪個欄位可以建立唯一索引？寫出建立唯一索引的語句

t#建立唯一索引。

alter table teacher add unique key index(t#);

1.2.3.31 第三題

如何檢查第一題中學生表在資料庫中所占的空間大小

select table_name,(index_length+avg_row_length*table_rows)/1024/1024 from information_schema.tables where table_name='student';

如何將第一題中的4個表進行備份，資料庫用戶、密碼為：admin/admin 資料庫名為：dbschool

select concat("mysqldump -uadmin -padmin" ,talble_schema " ",table_name,">temp/",table_schema,"__",table_name,".sql") from infomation_schema.tables where table_schema not in ('infomation_schema','performance_schema','sys') into outfile 'tmp.bak.sh';

1.2.3.32 部門表：t_dept（deptid ,deptname）工資表：t_salary（id,employeeid,deptid,salary）要求：查出員工的平均工資大於3000的部門名稱

select t_depy.depyname ,avg(t_salary.salary) from t_dept join t_salary on t_dept.deptid=t_salary.deptid group by t_dept.deptid having avg(t_salary.salary)>3000;

1.2.3.33 student表（Sno，Sname，Sage，Ssex）Course表（Cno，Cname，Ccredit）SC表（Sno，Cno,grade）

建表student、course、SC

create table student(sno int,sname varchar(20),sage int,ssex varchar(10))

create table course(cno int,cname varchar(20),ccredit varchar(20))

create table course(sno int , cno int,grade varchar(20))

查詢年齡小於20的學生學號、年齡，並顯示

select sno,sname,sage from student where sage<20;

查詢年齡不在20-23（包括20,23）的學生的成績

select student.sname sc.grade from student join sc on student.sno=sc.sno where student.sage<20 and student.sage>23;

1.2.3.34 給出查詢表A（ID,Name）中存在ID重覆三次以上的記錄的SQL語句

select id,count(id) from a group by id having count(id)>3;

1.2.3.35 mysql用戶名：root，密碼：123456，socket：/opt/mysql3306/mysql3306.sock，跑預設埠，請使用一條命令更新該數據源neutron庫中的ipallocations表中的ip_address 欄位等於10.4.182.36的記錄為202.113.14.92

mysql -uroot -p123456 -S /opt/mysql3306/mysql3306.sock -e "update table neutron.ipallocations set ip_address="202.113.14.92" where ip_address='10.4.182.36';

1.2.3.36 寫出以下SQL語句（涉及到表名和欄位自定義）

查詢某一個表前20條記錄

select * from t limit 20;

某公司從下個月起，每個人工資在原有基礎上上漲10%

update table a salary=salary+salary*0.1 where date=date_format(now(),%m)+1

刪除某個表中日期為2017-01-01以前的數據

delete * from a where date<'2017-01-01';

新增一條記錄

insert into a values();

備份資料庫

mysqldump -uroot -poldboy123 -A -master-date -single-transaction -R -E -triggers >/tmp.bak.gz

1.2.3.37 有三張表分別為客戶表customers，賬戶表accounts，用戶表users

客戶表customers (cust_id,cust_name,cust_opendate())

賬戶表accounts(acc_id,acc_name,cust_id,acc_opendate(賬戶開通時間))

用戶表users(user_id,user_name,cust_id,lost_login_date(用戶最後登錄時間))

每個客戶可以開通多個帳號，每個客戶有多個用戶，cust_id,acc_id,user_id分別為客戶、賬戶、用戶的唯一標誌，cust_opendate,acc_opendate,lost_login_date類型為date按照要求用一條sql語句完成

統計每個月開通的客戶數

select count(cust_id) ,date.format(accounts.acc_opendate,%m) from customers join accounts on customers.cust_id=accounts.cust_id group by date_format(accounts.acc_opendate,%m);

列出上個月開通客戶列表，並輸出這些客戶所具有的賬戶和用戶數量

select accounts.cust.id,count(users.user.id) from accounts join users on accounts.cust_id=users.cust_id where data_format(accounts.acc_opendate,%m)=date_format(now(),%m)-1;

統計三個月內登錄過的客戶數量

select count(user_id) from users where date_format(lost_login_date,%m) in date_format(now(),%m)-1,date_format(now(),%m)-2,date_format(now(),%m)-3;

統計老客戶今年開通賬戶的數量，老客戶是指今年以前開通的賬戶

select date_format(cust_opendate,%Y),count(cust_id) from customers where date.format(cust_opendate,%Y)=date.format(now(),%Y);

1.2.3.38 SQL

表名 user

Name Tel Content Date

張三 13333663366 大專畢業 2006-10-11

張三 13612312331 本科畢業 2006-10-15

張四 021-55665566 中專畢業 2006-10-15

（a）有一新記錄（小王 13254748547 高中畢業 2007-05-06）請用SQL語句新增至表中

insert into user values(‘小王 ’，‘13254748547’， ‘高中畢業’， ‘2007-05-06’)；

（b）請用sql語句把張三的時間更新成為當前系統時間

update user set date=date_format(now(),"%Y-%m-%d") where name='張三'；

（c）請寫出刪除名為張四的全部記錄

delete from user where name='張四'；

1.2.3.39 創建一個用戶並賦予許可權

grand all on *.* to oldboy@localhost identified '123';

1.2.3.40 創建表空間neuspace,數據文件命名為neudata.dbf,存放在d:\data目錄下，文件大小為200MB，設為自動增長，增量5MB，文件最大為500MB

create tablespace neuspace datefile 'd:\data\neudata.dbf' size 200 autoextend on text 5M maxsize 500M;

1.2.3.41 以系統管理員身份登錄，創建賬號tom，設置tom的預設表空間為neuspace.為tom分配connect和resource系統角色，獲取基本的系統許可權。然後為tom分配對用戶scott的表emp的select許可權和對SALARY，MGR屬性的update許可權

create user tom identified by tom default tablespace account;

grand connect,resource to tom;

grand select on scott.emp to tom;

grand update on SALARY.MGR to tom;

1.2.3.42 按如下要求創建class

屬性 類型（長度） 預設值 約束 含義

CLASSNO 數值（2） 無 主鍵 班級編號

CNAME 變長字元（10）無 非空 班級名稱

create table class(classon int(2) not null primary key auto_increment comment '班級編號',cname varchar(10) not null comment '班級名稱')

1.2.3.43 學生表有四個欄位：學號、姓名、年級、學分，統計出按年級降序同名同姓的數據情況（寫出sql語句）

select count(姓名)，年級 from a group by name order by 年級 ;

1.2.3.44 在表class的CNAME屬性上創建索引class_sname_idx

alter table class add index class_sname_idx(cname);

1.2.3.45 sql類

學生信息表

姓名（Name） 學號（Code）

張三 001

李四 002

馬五 003

甲六 004

考試信息表

學號 學科 成績

001 數學 80

002 數學 75

001 語文 90

002 語文 80

001 英語 90

002 英語 85

003 英語 80

004 英語 70

查詢各科成績的平均分，顯示欄位為學科、平均分，sql怎麼寫

select 學科，avg（成績）from 考試 group by 學科；

查詢所有學生各科成績，顯示欄位為姓名、學號、學科、成績，並以學號與學科排序，沒有成績的學生也需要列出，sql怎麼編寫

select a.name,a.code,b.course,b.grade from a join b on a.id=b.id order by course;

查詢出單科成績最高的，顯示欄位為：姓名、學號、學科、成績，sql怎麼編寫

select a.name,a.id,b.course,max(grade) from a join b a.id=b.id group by b.course;

列出每位學生各科成績，要求輸出格式:姓名、學號、語文成績、數學成績、英語成績，sql怎麼寫

select a.name,a.id

case when b.學科=‘語文’ then b.成績 then as ‘語文成績’，

case when b.學科=‘數學’ then b.成績 then as ‘數學成績’

case when b.學科=‘英語’ then b.成績 then as ‘英語成績’

from a join b on a.id=b.id

group by a.id;

1.2.3.46 假如有A*和B兩個表，A表中包括ID，COL1，COL2，COL3等欄位，B表中包括ID，COL1，COL2，COL4，COL5等欄位，現需要把B中的CLO1，COL2內容更新到A表COL1，COL2中，ID***為關鍵欄位，要求只寫一個SQL**

update table a set (select clo1,col2 from b)

1.2.3.47 不藉助第三方工具，如何獲得SQL的執行計劃

explain

1.3 MySQL索引

1.3.1 選擇

1.3.1.1 在SQL資料庫一個班級表裡只記錄了100位同學的情況，那麼對該表建立索引文件的描述正確的是(B)

A一定要，因為索引有助於加快搜索記錄的進程

B不適宜，因為對少林記錄的表進行索引實際上會產生不利的影響

C一定要，因為索引對於任何資料庫表都是必要的

D沒有必要，因為建立索引對任何資料庫的性能都沒有影響

1.3.2 簡答

1.3.2.1 索引的好處和壞處是什麼？

好處，在有索引的條件下查詢速度快。

壞處，需要維護成本。

1.3.2.2 資料庫中，哪些列需要創建索引，哪些列不適合創建索引

主鍵索引，唯一列，聯合列；

1.3.2.3 Mysql列出資料庫___show databases;切換當前用戶連接的資料庫_-su___

1.3.2.4 請問以下一些查詢語句是否用到該索引？()對last_name和first_name做索引。

A.SELECT * FROM customer_master WHERE first_name = 'Ed';

B.SELECT * FROM CUSTOMER_MASTER WHERE last_name LIKE '%rane' AND first_name = 'Ed';

C.SELECT * FROM CUSTOMER_MASTER WHERE last_name = 'Crane' AND first_name LIKE '%Ed';

D.SELECT * FROM CUSTOMER_MASTER WHERE last_name LIKE 'Crane%' ORDER BY first_name,last_name;

E.SELECT * FROM CUSTOMER_MASTER WHERE last_name='Crane' OR first_name='ED';

1.3.2.5 說說你對索引的認識（結構、對dml的影響、為什麼提高查詢性能）

索引有 B-TREE、BIT、CLUSTER 等類型。ORACLE 使用了一個複雜的自平衡 B-tree 結構;通常來

說，在表上建立恰當的索引，查詢時會改進查詢性能。但在進行插入、刪除、修改時，同時會進行

索引的修改，在性能上有一定的影響。有索引且查詢條件能使用索引時，資料庫會先度取索引，根

據索引內容和查詢條件，查詢出 ROWID，再根據 ROWID 取出需要的數據。由於索引內容通常比全

表內容要少很多，因此通過先讀索引，能減少 I/O，提高查詢性能。

b-tree index/bitmap index/function index/patitional index(local/global)索引通常能提高 select/update/delete

的性能,會降低 insert 的速度。

1.4 MySQL優化

1.4.1 選擇

1.4.1.1 順序表（2,6,8,10,12,15,16,18,24,28,32）中，用二分法查找關鍵碼值，所需的關鍵詞比較次數為（c）

A. 3 B. 4 C．5 D. 6

1.4.1.2 從二叉樹的任一結點出發到根的路徑上，所經過的結點序列必按其關鎮字降仔指列。（c）

A二叉排序樹 B.大頂堆 C 小頂堆 D.平衡二叉樹

1.4.2 填空

1.4.2.1 在總計函數中，傳回非NULL值的欄位數目的函數名稱是____count()__

1.4.3 簡答

1.4.3.1 若果一個sql查詢語句很長，執行速度慢，你會想到哪些優化的方法？

查詢不需要的記錄，使用 limit 解決；

多表關聯返回全部列，指定 A.id, A.name, B.age；

總是取出全部列，SELECT * 會讓優化器無法完成索引覆蓋掃描的優化。

優化 count() 查詢

優化關聯查詢

優化子查詢（即嵌套查詢）

優化 GROUP BY 和 DISTINCT

優化 LIMIT 分頁

優化 UNION 查詢

1.4.3.2 資料庫里有些數據會反覆被查詢，問有什麼好方法提高效率？

建立索引

分區（如 MySQL，按時間分區）

儘量使用固定長度欄位和限制欄位長度（如 varchar(10)）

增加緩衝區

如果設計表的級聯，不同的表存儲在不同磁碟上，以增加 I/O 速度

優化 sql 語句，減少比較次數

限制返回條目數(mysql 中使用 limit）

1.4.3.3 系統中，主要的業務表。按月進行分表。7,8,9月表分別為tab_201707、tab_201708、tab_201709這三個表裡。表結構如下：

SQL> desc tab_201707;

Name Type Nullable Default Comments

ID NUMBER 自增

CID NUMBER Y 批次ID

CODE VARCHER2(20) Y 特服號

CORPORATEID NUMBER 企業ID

USERID NUMBER Y 用戶ID

請寫出SQL語句，統計7、8、9三個月，每個USERID的記錄總數

select userid,count(userid) from tab_201707;

1.4.3.4 什麼是跨站腳本攻擊，有何危害，sq|註入攻擊如何防範

XSS 跨站腳本攻擊：兩種情況。一種通過外部輸入然後直接在瀏覽器端觸發，即反射型 XSS；還有

一種則是先把利用代碼保存在資料庫或文件中，當 web 程式讀取利用代碼並輸出在頁面上時觸發漏

洞，即存儲型 XSS。DOM 型 XSS 是一種特殊的反射型 XSS。

跨站點腳本（XSS）允許攻擊者通過利用網際網路伺服器的漏洞來發送惡意代碼到其他用戶。攻擊者

利用跨站點腳本（XSS）攻擊向那些看似可信任的鏈接中註入惡意代碼。當用戶點擊了鏈接後，內

嵌的程式將被提交並且會在用戶的電腦上執行，這會使黑客獲取。

危害：前端頁面能做的事它都能做。（不僅僅盜取 cookie、修改頁面等）

漏洞防範

（1） 特殊字元 HTML 實體轉碼。最好的過濾方式是在輸出和二次調用的時候進行加 HTML 實體

一類的轉碼，防止腳本註入。

（2） 標簽事件屬性黑名單。特殊字元容易被繞過，所以還得加標簽事件得黑名單或者白

名單，這裡推薦使用白名單的方式，實現規則可以直接使用正則表達式來匹配，如果匹配到的事件

不在白名單列表，就可以直接攔截，而不是過濾為空。

1.4.3.5 在工作中，一臺線上MSSQL資料庫查詢數據突然變得異常緩慢，如果交給你排查，請寫出排查思路

首先應該從系統級別來排查。先找出到底哪裡慢，是特定操作慢呢，還是所有操作都慢了。

既然是突然變慢，那麼就是說之前還是比較快的。自從某個特殊事件發生後，慢了下來。特殊事件

一般包括：

1、程式做了修改。

2、突然有大數據量的系統訪問。

3、某些不常用功能突然啟用，或大量訪問。

4、某些硬體出了功能性問題。

對於系統整體變慢，基本上還是先查系統日誌。查最近的系統更新日誌。查看磁碟剩餘容量。硬體

是否由異常。

1.4.3.6 SQL語句調優

select v_id,v_owner,v_uesr,v_options,v_date,v_anonymous from t_vote_hash_by_id_65 where v_id='536' and v_user='yu281590760' and v_owner='xiwangkuaile222';

1.4.3.7 列出您認為sql優化可以從哪些方面進行

1、sql 語句的執行計劃是否正常。

2、減少應用和資料庫的交互次數、同一個 sql 語句的執行次數。

3、資料庫實體的碎片的整理（特別是對某些表經常進行 insert 和 delete 動作，尤其註意，索引欄位

為系列欄位、自增長欄位、時間欄位，對於業務比較頻繁的系統，最好一個月重建一次）。

4、減 少表之間的關聯，特別對於批量數據處理，儘量單表查詢數據，統一在記憶體中進行邏輯處理，減少

資料庫壓力（java 處理批量數據不可取，儘量用 c 或者 c++ 進行處理，效率大大提升）。

5、對訪問頻繁的數據，充分利用資料庫 cache 和應用的緩存。

6、數據量比較大的，在設計過程中，為了減少其他表的關聯，增加一些冗餘欄位，提高查詢性能。

1.4.3.8 某個系統sql忽然變慢，影響了系統的性能，但通過sqlplus或者PL/SQL developer運行改sql，速度卻很快，請問產生此情況的可能原因，並簡述解決辦法

listener.log 過大導致

1.5 MySQL集群

1.5.1 選擇

1.5.1.1 下麵對Linux下mysqldump備份命令及參數描述正確的是（A）

A.mysqldump -h ip -u -p DBNAME>back.sql B.mysqldump -P ip -h DBNAME>back.sql

C.mysqldump -u ip -P DBNAME>back.sql D.mysqldump -h ip -u DBNAME>back.sql

1.5.1.2 如何將MySQL資料庫中的mail這個資料庫備份成mail.sql?(B)

A、mysql -opt mail uroot-p > mail sql B、mysqldump --opt mail -uroot p > mail.sql

C、mysql -uroot -p > mail.sql D.mysql -uroot -p < mail sql

1.5.1.3 MySQL主從結構的主資料庫中不可能出現以下哪種日誌______C__

A錯誤日誌 B事務日誌 C中繼日誌 Dredo log

1.5.1.4 對於資料庫備份正確的做法是：B

A停止業務開始備份 B 停止資料庫開始備份 C不需要停止備份 D根據情況而定

1.5.2 填空

1.5.2.1 如何用mysql命令進行備份和恢復？以Lest庫為例，創建一個備份，並再用此備份進行恢復

mysql -uroot -poldboy123 -B lest >/tmp/lest.bak.gz

source /tmp/lest.bak.gz

1.5.2.2 Mysql備份工具___xtrabackup,mysqldump___

1.5.3 簡答

1.5.3.1 每天凌晨2點對mfdata資料庫進行備份，備份到/data/mysql/下，並以時間為備份名。

crontab -e

00 02 * * * /bin/sh /server-scripts/backup.sh >/dev/null 1&>2

mysqldump -uroot -poldboy123 -B mfdata >/data/mysql/backup_$(date +%F).bak.sql

1.5.3.2 mysql備份命令

xtraback

mysqldump

本地備份 mysqldump -uroot -p -S /tmp/mysql.sock

遠程備份 mysqldump -uroot -p -h 10.0.0.51 -P3306

1.5.3.3 說明一下mysql主從複製原理並描述一下部署主從複製的步驟

1.change master to 時，ip pot user password binlog position 寫入到 master.info 進行記錄

\2. start slave 時，從庫會啟動 IO 線程和 SQL 線程

3.IO_T，讀取 master.info 信息，獲取主庫信息連接主庫

\4. 主庫會生成一個準備 binlog DUMP 線程，來響應從庫

\5. IO_T 根據 master.info 記錄的 binlog 文件名和 position 號，請求主庫 DUMP 最新日誌

\6. DUMP 線程檢查主庫的 binlog 日誌，如果有新的，TP(傳送)給從從庫的 IO_T

\7. IO_T 將收到的日誌存儲到了 TCP/IP 緩存，立即返回 ACK 給主庫 ，主庫工作完成

8.IO_T 將緩存中的數據，存儲到 relay-log 日誌文件,更新 master.info 文件 binlog 文件名和 postion，

IO_T 工作完成

9.SQL_T 讀取 relay-log.info 文件，獲取到上次執行到的 relay-log 的位置，作為起點，回放 relay-log

10.SQL_T 回放完成之後，會更新 relay-log.info 文件。

1. relay-log 會有自動清理的功能。

1.5.3.4 備份Mysql的所有資料庫。

mysqldump -uroot -poldboy123 -A >/tmp/backup.bak.gz;

1.5.3.5 對mysql資料庫test進行備份和恢復

create database test;

mysqldump -uroot -poldboy123 -A >/tmp/backup.bak.gz

source /tmp/backup. bak.gz

1.5.3.6 如何備份某個庫，某個庫下的某個表，某個庫排除某個表

mysqldump -uroot -poldboy123 testdb --ignore-table test123>/tmp/backup/test.bak.gz

1.5.3.7 如何恢復備份的數據，過程，註意事項？

mysqldump -uroot -poldboy testdb </tmp/backup/test.bak.gz

還原直接複製目錄的備份 通過這種方式還原時，必須保證兩個 MySQL 資料庫的版本號是相同

的。MyISAM 類型的表有效，對於 InnoDB 類型的表不可用，InnoDB 表的表空間不能直接複製。

1.5.3.8 主從概念，如何配置，管理，監控，有確定。主從如何同步的？

1.change master to 時，ip pot user password binlog position 寫入到 master.info 進行記錄

\2. start slave 時，從庫會啟動 IO 線程和 SQL 線程

3.IO_T，讀取 master.info 信息，獲取主庫信息連接主庫

\4. 主庫會生成一個準備 binlog DUMP 線程，來響應從庫

\5. IO_T 根據 master.info 記錄的 binlog 文件名和 position 號，請求主庫 DUMP 最新日誌

\6. DUMP 線程檢查主庫的 binlog 日誌，如果有新的，TP(傳送)給從從庫的 IO_T

\7. IO_T 將收到的日誌存儲到了 TCP/IP 緩存，立即返回 ACK 給主庫 ，主庫工作完成

8.IO_T 將緩存中的數據，存儲到 relay-log 日誌文件,更新 master.info 文件 binlog 文件名和 postion，

IO_T 工作完成

9.SQL_T 讀取 relay-log.info 文件，獲取到上次執行到的 relay-log 的位置，作為起點，回放 relay-log

10.SQL_T 回放完成之後，會更新 relay-log.info 文件。

\11. relay-log 會有自動清理的功能。

監控

1）監控 MYSQL 的運行狀態；

2）Slave 機器的 IO 和 SQL 狀態都必須為 YES，缺一不可；

1.5.3.9 msyql備份命令？mysql如何給jfedu用戶對所有表授權訪問，密碼為jfedu.net

mysqldump -uroot -poldboy123 -A >/tmp/backup.bak.sql

grand all on . to jfdeu@localhost identified by 'jfedu.net';

1.5.3.10 如何判斷mysql主從是否同步? 該如何使其同步?

show slave status\G;

Slave_IO_Running

Slave_SQL_Runing

1.5.3.11 mysql的innodb如何定位鎖問題，mysql如何減少主從複製延遲

鎖監控設計到的命令:

show status like 'innodb_rows_lock%'

select * from information_schema.innodb_trx;

select * from sys.innodb_lock_waits;

select * from performance_schema.threads;

select * from performance_schema.events_statements_current;

select * from performance_schema.events_statements_history;

---------------------

1 主庫寫 binlog 不及時 *******

控制 binlog 從記憶體寫入磁碟的控制開關

每次事務提交都立即刷新 binlog 到磁碟(雙一標準中的其一)

sync_binlog=1

每次事務提交不立即寫入磁碟,靠操作系統判斷什麼時候寫入

sync_binlog=0

說明：5.6 預設是 0，5.7 以後預設是 1

2 dump 線程多導致的，系統資源壓力大，由於傳送日誌是串列的。

從庫越多,壓力越大

由於超大事務存在，由於是串列工作，會阻塞後續其他事務的傳送。

解決方案：

減少大事務

group commit （需要配合 GTID 來實現的）

3 從庫-IO 線程阻塞

大事務拆成小事務

事務量大(主庫壓力大)

group commit 可以緩解

業務的分離和分散式(Mycat,InnoDB Cluster)

4 SQL 線程慢(Classic replication 傳統) *****

原因：

從庫 預設只有一個 SQL 線程,串列回放事務。在主庫有併發事務量大，或者有超大事務時，都會導

致 SQL 延時較嚴重。

如何解決：

5.6 版本，加入了 GTID 特性，所以支持了併發 SQL 特性，基於不同庫實現並行回放事務

5.7 版本，GTID 功能進行了升級，可以通過 Logical_clock 模式，實現事務級別的多 SQL 線程的回

放。我們把這種複製模式叫做 MTS。

1.5.3.12 寫出對MySQL資料庫test進行備份和恢復的命令

mysqldump -uroot -poldboy123 test >/tmp/bakcup/test.bak.sql

create databases test;

source /tmp/backup/test.bak.sql

1.5.3.13 Mysql的binlog格式有哪些，預設的是什麼格式，格式選取的標準是什麼

binlog 有三種格式：Statement、Row 以及 Mixed。

–基於 SQL 語句的複製(statement-based replication,SBR)，

–基於行的複製(row-based replication,RBR)，

–混合模式複製(mixed-based replication,MBR)。

1、STATEMENT 模式（SBR）

每一條會修改數據的 sql 語句會記錄到 binlog 中。優點是並不需要記錄每一條 sql 語句和每一行的數

據變化，減少了 binlog 日誌量，節約 IO，提高性能。缺點是在某些情況下會導致 master-slave 中的

數據不一致(如 sleep()函數， last_insert_id()，以及 user-defined functions(udf)等會出現問題)

2、ROW 模式（RBR）

不記錄每條 sql 語句的上下文信息，僅需記錄哪條數據被修改了，修改成什麼樣了。而且不會出現

某些特定情況下的存儲過程、或 function、或 trigger 的調用和觸發無法被正確複製的問題。缺點是

會產生大量的日誌，尤其是 alter table 的時候會讓日誌暴漲。

3、 MIXED 模式（MBR）

以上兩種模式的混合使用，一般的複製使用 STATEMENT 模式保存 binlog，對於 STATEMENT 模式

無法複製的操作使用 ROW 模式保存 binlog，MySQL 會根據執行的 SQL 語句選擇日誌保存方式。

1.5.3.14 Mysql主從是否同步，要在主庫上查看還是從庫上查看，主要關註哪些參數

show slave status\G;

slave_IO_Running

slave_SQL_Running

1.5.3.15 說明一下目前線上mysql 集群 的部署方案

MHA+atlas

Mycat+MGR

InnoDB cluster

PXC

MGC

1.5.3.16 用哪個命令可以對Mysql中的資料庫進行備份？

mysqldump -uroot -poldboy123 -A >/tmp/backup.bak.sql

source /tmp/backup.bak.sql

mysqldump

mysqldump -u 用戶名 -p 密碼 --databases 資料庫 1 資料庫 2 > xxx.sql

常見選項：

-u: 用戶名

-p: 密碼

-P: 埠號，不寫預設 3306

--all-databases, -A：備份所有資料庫

--databases, -B: 用於備份多個資料庫，如果沒有該選項，mysqldump 把第一個名字參數作為資料庫

名，後面的作為表名。使用該選項，mysqldum 把每個名字都當作為資料庫名。

-d: 只導出資料庫的表結構

-t: 只導出資料庫的數據

--quick, -q：快速導出

--xml, -X：導出為 xml 文件

1.5.3.17 mysql的binlog有幾種，區別是什麼？mysql雙主複製原理是什麼？有什麼優點和缺點？mysql如何進行增量備份？

binlog 的格式也有三種：STATEMENT、ROW、MIXED 。

1、STATMENT 模式：基於 SQL 語句的複製(statement-based replication, SBR)，每一條會修改數據的

sql 語句會記錄到 binlog 中。

2、基於行的複製(row-based replication, RBR)：不記錄每一條 SQL 語句的上下文信息，僅需記錄哪

條數據被修改了，修改成了什麼樣子了。

3、混合模式複製(mixed-based replication, MBR)：以上兩種模式的混合使用，一般的複製使用

STATEMENT 模式保存 binlog，對於 STATEMENT 模式無法複製的操作使用 ROW 模式保存

binlog，MySQL 會根據執行的 SQL 語句選擇日誌保存方式。

雙主原理:

雙向的主從複製，也就是互為對方的從伺服器，每台伺服器即是對方的主伺服器，又是對方的從服

務器

資料庫複製 replication 的實現原理

1:主伺服器凡運行語句,都產生一個二進位日誌 binlog

2:從伺服器不斷讀取主伺服器的 binlog

3:從主服務讀取到的 binlog,轉換為自身可執行的 relaylog,

4:執行 relaylog

1.5.3.18 簡述資料庫中全量備份、差異備份、事務日誌備份的恢復方式