資料庫

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 簡述資料庫中全量備份、差異備份、事務日志備份的恢復方式

mysql -hhostname -uusername -ppassword databasename < backupfile.sql

mysqlbinlog -d test mysql-bin.000003 >003bin.sql

1.5.3.19 如果需要可以回滾的修改以下sql陳述句，如何實作?

<v:shapetype id="_x0000_t75" coordsize="21600,21600" o:spt="75" o:preferrelative="t" path="m@4@5l@4@11@9@11@9@5xe" filled="f" stroked="f"><v:stroke joinstyle="miter"><v:formulas></v:formulas><v:path o:extrusionok="f" gradientshapeok="t" o:connecttype="rect"></v:path></v:stroke></v:shapetype><v:shape id="圖片_x0020_10" o:spid="_x0000_i1025" type="#_x0000_t75" style="width:6in;height:195pt;visibility:visible;mso-wrap-style:square"><v:imagedata src=https://www.cnblogs.com/cuiyongchao007/p/"file:///C:/Users/ADMINI~1/AppData/Local/Temp/msohtmlclip1/01/clip_image001.png" o:title="">

1.5.3.20 SAN方式高可用，你對這方面有了解嗎？

SAN(Storage Area Network)簡單點說就是可以實作網路中不同服務器的資料共享，

共享存盤能夠為資料庫服務器和存盤解耦，使用共享存盤時，服務器能夠正常掛載檔案系統并操

作， 如果服務器掛了，備用服務器可以掛載相同的檔案系統，執行需要的恢復操作，然后啟動 MySQL

1.5.3.21 mysqldump實作不鎖表的引數

mysqldump 備份不鎖表：加上--lock-tables=false 引數，

如果是 innodb，則加上--single-transcation 比較好，

1.5.3.22 MySQL資料庫的備份命令

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

mysqldump -uroot -poldboy123 -B test >/temp/backup.bak.sql

1.5.3.23 MySQL的同步、半同步、一步同步主從復制有什么區別

異步復制

異步復制，主庫將事務 Binlog 事件寫入到 Binlog 檔案中，此時主庫只會通知一下 Dump 執行緒發

送這些新的 Binlog，然后主庫就會繼續處理提交操作，而此時不會保證這些 Binlog 傳到任何一個

從庫節點上，

全同步復制

全同步復制，當主庫提交事務之后，所有的從庫節點必須收到、APPLY 并且提交這些事務，然后主

庫執行緒才能繼續做后續操作，但缺點是，主庫完成一個事務的時間會被拉長，性能降低，

半同步復制

半同步復制，是介于全同步復制與全異步復制之間的一種，主庫只需要等待至少一個從庫節點收到

并且 Flush Binlog 到 Relay Log 檔案即可，主庫不需要等待所有從庫給主庫反饋，同時，這里只是

一個收到的反饋，而不是已經完全完成并且提交的反饋，如此，節省了很多時間，

1.5.3.24 對于一個資料量較多的系統（資料庫10T，每天歸檔500g），您如何設計備份策略

每周 Full，每天 inc，或使用 DG、OGG

1.5.3.25 對于exp/imp和impdb/expdp邏輯備份的時候，有什么方法提高效率

expdp/impdp 是服務端程式，影響它速度的只有磁盤 IO，

exp/imp 可以在服務端，也可以在客戶端，所以，它受限于網路和磁盤

采用 direct path 可以提高匯出速度， 所以，在使用 exp 時，就可以采用直接路徑模式， 這種模式有 2 個相關的引數：

DIRECT 和 RECORDLENGTH 引數，

DIRECT 引數定義了匯出是使用直接路徑方式(DIRECT=Y)，還是常規路徑方式(DIRECT=N)，常規路徑匯出使用 SQL SELECT

陳述句從表中抽取資料，直接路徑匯出則是將資料直接從磁盤讀到 PGA 再原樣寫入匯出檔案，從而避免了 SQL 命令處理層的數

據轉換程序，大大提高了匯出效率，在資料量大的情況下，直接路徑匯出的效率優勢更為明顯，可比常規方法速度提高三倍之

多，

和 DIRECT=Y 配合使用的是 RECORDLENGTH 引數，它定義了 Export I/O 緩沖的大小，作用類似于常規路徑匯出使用的

BUFFER 引數，建議設定 RECORDLENGTH 引數為最大 I/O 緩沖，即 65535(64kb)，其用法如下：

如：exp userid=system/manager full=y direct=y recordlength=65535 file=exp_full.dmp log=exp_full.log

1.6 Oracle

1.6.1 選擇

1.6.1.1 如果一個服務器行程非正常終止，Orade系統將使用下列哪一個行程來釋放它所占用的資源?(D)

A.DBWR B.LGWR C.SMON D.PMON

1.6.1.2 下例選項中，哪一部分不是Orade 實體的組成部分?(B )

A.SGA系統全域區 B.控制檔案 C.PMON 后臺行程 D.Dnnn 調度行程

1.6.1.3 Oracle 本地決議依賴那個檔案（B）

A.sqlnet.ora B.listener.ora C.cman.ora D.tnsnames.ora

1.6.1.4 在Oracle中，當控制一個顯示游標時，以下哪個命令包含into子句？（C）

A.Open B.close C.fetch Dcursor

1.6.1.5 Oracle中要生成資料庫表，下列哪個選項是無效表生產的陳述句？(D)

A.create table cats(c_name varchar2(10),c_weighe number,c_owner varcjar2(10))

B.create table cats(c_name varchar2(10),c_weighe number,c_owner varcjar2(10))

C.create global temporary table temp_cats(c_name varchar2(10),c_weighe number,c_owner varcjar2(10))

D.create table 51cats as select c_name,c_weight from cats where c_weigth >5

1.6.1.6 在oracle中，當控制一個顯示游標時，一下哪個命令包含into子句？C

A.Open B.close C.fetch D.cursor

1.6.1.7 表中的資料可以存為一下那種檔案格式（B）

A.PDF B.EXE C.TXT D.DMP

1.6.1.8 在全域存盤區SGA中，那個部分記憶體區域是回圈使用的（B）

A資料緩沖區 B日志緩沖區 C共享池 D大池

1.6.1.9 如果要查詢資料庫中所有表的資訊，應當使用下列哪種資料字典視圖? (A)O

A.DBA 視圖 B.ALL 視圖 C.USER 視圖 D.動態性能視圖

1.6.1.10 下列哪一項是Oracle資料庫中最小的存盤分配單元?( D)

A.表空間 B.段 C.盤區 D.資料塊

1.6.1.11 下面的各選項中哪一一個正確描述了Oracle資料庫的邏輯存盤結構? (A)O

A.表空間由段組成，段由盤區組成，盤區由資料塊組成

B.段由表空間組成，表空間由盤區組成，盤區由資料塊組成

C.盤區由資料塊組成，資料塊由段組成，段由表空間組成

D.資料塊由段組成，段由盤區組成，盤區由表空間組成

1.6.1.12 下列哪一個行程用于將修改過的資料從記憶體保存到磁盤資料檔案? (A

A.DBWR B.LGWR C.RECO D.ARCH

1.6.1.13 解忙后的SOL陳述句在SGA的哪個區域中進行快取?(C)

A資料緩沖區 B日志緩沖區 C共享池 D大池

1.6.1.14 當資料庫運行在歸檔模式下時，如果發生目志切換，為了保證不覆寫舊的日志資訊系統將啟動如下哪個行程？D

A.DBWR B.LGWR C.SMON D.ARCH

1.6.1.15 PL/SQL代碼段中注釋 符號是A

A// B\ C- D,

1.6.1.16 （C）模式存盤資料庫中資料字典的表和視圖

A 、DBA B、SCOTT C、SYSTEM D、SYS

1.6.2 填空

1.6.2.1 從存盤結構的角度來說，Oracle資料庫可分為——、——區塊段

1.6.2.2 要更新游標結果集中的當前行，應使用__FOR MODIFY 子句

1.6.2.3 Oracle查看表空間情況使用哪個資料字典_______ dba_tablespces_v$tablespace _______

1.6.2.4 Oracle 備份工具（包括匯入匯出）_____ _____ _____ （exp___ _imp__expdp impdp rman ）

1.6.2.5 Oracle下取得當前系統時間的代碼為______select sysdate from dual;

1.6.2.6 PL/SQL塊中不能直接使用的SQL命令是____DDL trong

1.6.3 簡答

1.6.3.1 在ORACLE 中用SQL 陳述句備份表A,備份表名為B，

1.6.3.2 往oracle 插入資料超時，root 用戶下如何排查故障節點，描述重新啟動oracle 服務程序

1、startup nomount

這個階段，Oracle 行程讀取初始引數檔案，查看 Oracle 資料庫的引數是如何配置的，比如記憶體

大小配置引數等，讀取引數檔案后，與資料庫相關的記憶體區域被建立起來，同時，后臺行程也已經

開始啟動，我們把這些記憶體和行程叫做 Oracle instance，一旦 Oracle Instance 啟動成功，資料庫就進

入了 NoMount 階段，

2、Mount

啟動到這個階段時，會打開和讀取控制檔案，控制檔案是二進制檔案，記錄著 Oracle 的重要

資訊，例如，資料檔案的路徑等，

這個階段，Oracle 確定資料檔案的位置，但是還沒有打開這些檔案，一旦資料檔案的路徑被定

位后，資料庫開始進入下一階段，

3、Open這個階段，Oracle 會讀取所有的資料檔案，并且確定這些資料檔案是一致的（consistent），

1.6.3.3 Oracle表空間，單個資料檔案最大支持存盤多少G資料，索引需要重建嗎，什么時候需要重建索引

32G

1.6.3.4 請寫出unix系統中oracle資料庫啟、停命令

啟動：startup

停止：shutdown immediate

1.6.3.5 請描述oracle資料庫備份方式

Oracle 備份包括邏輯備份和物理備份，

1．邏輯備份 資料庫的邏輯備份包含讀一個資料庫記錄集和將記錄集寫入檔案，

（1）輸出（Export）輸出可以是整個資料庫、指定用戶或指定表，

（2）輸入（Import）輸入將輸出建立的二進制轉儲檔案讀入并執行其命令，

2．物理備份 物理備份包含拷貝構成資料庫的檔案而不管其邏輯內容，

Oracle 支持兩種不同型別的物理檔案備份：脫機備份（offline backup）和聯機備份（online

backup），

（1） 脫機備份 脫機備份用在當資料庫已正常關閉，資料庫處于"offline"時，要備份下列檔案

所有資料檔案 所有控制檔案

1.6.3.6 Oracle如何實作資料庫的優化

1、調整資料結構的設計，這一部分在開發資訊系統之前完成，程式員需要考慮是否使用 ORACLE

資料庫的磁區功能，對于經常訪問的資料庫表是否需要建立索引等，

2、調整應用程式結構設計，這一部分也是在開發資訊系統之前完成，程式員在這一步需要考慮應用

程式使用什么樣的體系結構，是使用傳統的 Client/Server 兩層體系結構，還是使用

Browser/Web/Database 的三層體系結構，不同的應用程式體系結構要求的資料庫資源是不同的，

3、調整資料庫 SQL 陳述句，應用程式的執行最終將歸結為資料庫中的 SQL 陳述句執行，因此 SQL 語

句的執行效率最終決定了 ORACLE 資料庫的性能，ORACLE 公司推薦使用 ORACLE 陳述句優化器

（Oracle Optimizer）和行鎖管理器（row-level manager）來調整優化 SQL 陳述句，

1.6.3.7 備份如何分類

邏輯備份與物理備份，或者冷備份與熱備份，

1.6.3.8 你最擅長的是oracle那部分

性能優化、故障修復， RAC，DG，OGG，遷移

1.6.3.9 喜歡oracle嗎？喜歡上論壇嗎？或者偏好oracle的那一部分

官方檔案，MOS 等；性能優化、故障修復， RAC，DG，OGG，遷移

1.6.3.10 隨意說說你覺得oracle最有意思的部分或者最困難的部分

SQL 調優

1.6.3.11 Oracle的回滾段的作用是什么

保存資料的前像，保證資料讀取的時間點一致性，Oracle 里資料的多版本特性就是通過回滾段來實

現的，正因為此，Oracle 資料庫實作了讀寫不競爭的性能優勢！

1.6.3.12 oracle的日只有哪幾種，作用是什么

Alert log files--警報日志，

Trace files--跟蹤日志(用戶和行程)，

redo log 重做日志（記錄資料庫的更改）

歸檔 保存 redo

1.6.3.13 oracle行程主要有哪些，作用是什么

pmon smon dbwr lgwr arch lisner

1.6.3.14 請簡單描述下您對oracle鎖機制的認識

ORACLE 里鎖有以下幾種模式:

0：none

1：null 空

2：Row-S 行共享(RS)：共享表鎖

3：Row-X 行專用(RX)：用于行的修改

4：Share 共享鎖(S)：阻止其他 DML 操作

5：S/Row-X 共享行專用(SRX)：阻止其他事務操作

6：exclusive 專用(X)：獨立訪問使用

數字越大鎖級別越高, 影響的操作越多，

一般的查詢陳述句如 select ... from ... ;是小于 2 的鎖, 有時會在 v$locked_object 出現，

select ... from ... for update; 是 2 的鎖，

當對話使用 for update 子串打開一個游標時，

所有回傳集中的資料行都將處于行級(Row-X)獨占式鎖定，

其他物件只能查詢這些資料行，不能進行 update、delete 或 select...for update 操作，

insert / update / delete ... ; 是 3 的鎖，

沒有 commit 之前插入同樣的一條記錄會沒有反應,

因為后一個 3 的鎖會一直等待上一個 3 的鎖, 我們必須釋放掉上一個才能繼續作業，

創建索引的時候也會產生 3,4 級別的鎖，

locked_mode 為 2,3,4 不影響 DML(insert,delete,update,select)操作,

但 DDL(alter,drop 等)操作會提示 ora-00054 錯誤，

有主外鍵約束時 update / delete ... ; 可能會產生 4,5 的鎖，

DDL 陳述句時是 6 的鎖，

以 DBA 角色, 查看當前資料庫里鎖的情況可以用如下 SQL 陳述句：

select object_id,session_id,locked_mode from v$locked_object;

select t2.username,t2.sid,t2.serial#,t2.logon_time

老男孩教育-Linux 學院-微信:lidao996(加上備注)37

from vlockedobjectt1,vsession t2

where t1.session_id=t2.sid order by t2.logon_time;

如果有長期出現的一列，可能是沒有釋放的鎖，

我們可以用下面 SQL 陳述句殺掉長期沒有釋放非正常的鎖：

alter system kill session 'sid,serial#';

如果出現了鎖的問題, 某個 DML 操作可能等待很久沒有反應，

當你采用的是直接連接資料庫的方式，

也不要用 OS 系統命令 killprocessnum 或者 kill -9 process_num 來終止用戶連接，

因為一個用戶行程可能產生一個以上的鎖, 殺 OS 行程并不能徹底清除鎖的問題，

記得在資料庫級別用 alter system kill session 'sid,serial#';殺掉不正常的鎖，

1.6.3.15 oracle的Dataguard有哪幾種模式，各有什么差別

DataGuard 有三種模式：

1.最大性能

這是 Data Guard 默認的保護模式，primay 上的事務 commit 前不需要從 standby 上收到反饋資訊，該

模式在 primary 故障時可能丟失資料，但 standby 對 primary 的性能影響最小，

2.最大可用

在正常情況下，最大可用模式和最大保護模式一樣；在 standby 不可用時，最大可用模式會自動降

低成最大性能模式，所以 standby 故障不會導致 primay 不可用，只要至少有一個 standby 可用的情況

下，即使 primary down 機，也能保證不丟失資料，

3.最大保護

最高級別的保護模式，primay 上的事務在 commit 前必須確認 redo 已經傳遞到至少一個 standby 上，

如果所有 standby 不可用，則 primary 會掛起，該模式能保證零資料丟失, 一般為保證主庫在某個從

庫故障后仍能正常運行,最好配置兩個及以上的從庫，

1.6.3.16 oracle冷備與熱備的區別

熱備份針對歸檔模式的資料庫，在資料庫仍舊處于作業狀態時進行備份，

而冷備份指在資料庫關閉后，進行備份，適用于所有模式的資料庫，

熱備份的優點在于當備份時，資料庫仍舊可以被使用并且可以將資料庫恢復到任意一個時間點，

冷備份的優點在于它的備份和恢復操作相當簡單，并且由于冷備份的資料庫可以作業在非歸檔模式

下,

資料庫性能會比歸檔模式稍好，（因為不必將 archive log 寫入硬碟）

1.7 Redis

1.7.1 簡答

1.7.1.1 簡述redis與DB的互動機制

1.7.1.2 redis服務器的連接命令是什么？

1.7.1.3 Redis默認跑什么埠？使用命令列連接redis的默認命令是什么？

1.7.1.4 redis實作主從使用什么命令？

1.7.1.5 是否維護過redis集群，使用什么技術實作的集群？

1.7.1.6 查看redis存活狀態及key個數的命令，redis cluster寫操作步驟

1.8 Mongodo

1.8.1 簡答

1.8.1.1 Mongodb忘記admin密碼的解決方法（或mysql忘記root密碼）

1.9 SQL sever

1.9.1 選擇

1.9.1.1 在SQL Server資料庫中，你想得到在products表中最貴的產品的產品名稱（Productname）和產品價格（Price）,應該使用的SQL查詢陳述句是____AC______

SELECT TOP 1 Productname,Price FROM Products ORDER BY Price desc

SELECT Productname,MAX(price) FROM Products

SELECT Productname

1.9.1.2 SQL Server是一種（C）軟體

A作業系統 B語言處理 C資料庫管理系統 D服務性程式

1.9.1.3 SQL Server資料庫的主資料檔案的擴展名為（BD）

A.sql B.mdf C.mdb D.ldf

1.9.1.4 關于sql server 常用的資料型別，以下（C）說法是錯誤的

A.Image 資料型別可以用來存盤影像 B.使用字符資料型別時，可以改變長度資訊

C.使用數字資料型別時，可以改變長度資訊 D.Bit資料型別為1位長度，可以存盤表示是/否的資料

1.9.1.5 使用T-SQL中的（D）陳述句可以洗掉資料表格或者視圖中的一個或多個記錄

A.DEL B.PRUGE C.DELETE D.DROP

1.9.1.6 使用T-SQL對資料的修改是通過（D）陳述句實作的

A.MODIFY B.EDIT C.REMAKE D.UPDATE

1.9.1.7 下列關于SQL Server資料庫日志的說話錯誤是（D）

日志檔案是維護資料庫完整性的重要工具 有的對SQL資料庫的操作都需要寫日志

當日志檔案的空間占滿時，將無法寫日志 當修改資料庫時，必先寫日志

1.9.2 填空

1.9.2.1 sqlserver資料庫，如何限制不同人員的訪問權限

根據需要授予角色權限

主資料庫檔案的擴展名是__mdf___，事務日志檔案的擴展名是__ldf__

1.9.2.2 SQL Server下取得當前系統時間的代碼為_____select GETDATE()__ ___

1.9.2.3 將SQL Server資料復制到其他的資料庫中，包括Access、Oracle、DB2等，采用__ODBC 作為連接機制

1.9.2.4 SQL Server能夠識別兩種登錄認證機制--Windows認證和_____SQL SERVER 認證

1.9.2.5 SQL Server默認的資料庫系統管理員的用戶名是2005 以前默認是：sa，2008 以后是安裝時

設定的，默認為本機的名字___

1.9.2.6 在SQL Server中定義字符型，不論char（n）格式還是varchar（n）格式，其資料長度均不能超過__8000位元組

1.9.2.7 在SQL Server表中，一個表只能有一個___主鍵__，且其值必須唯一

1.9.2.8 在SQL Server中，一根資料庫至少包含___資料庫主__檔案和___日志__檔案

1.9.2.9 SQL Server提供了四種資料庫備份方式：資料庫備份、日志備份、__差異___備份以及檔案或檔案組備份

1.9.2.10 在檢索資訊時可以通過WHERE字句指定檢索的條件，而且SQL Server還提供了NOT、OR和__and三種運算子

1.9.3 簡答

1.9.3.1 SQL-SERVER2008有幾種認證方式？這幾種認證方式有什么區別？

1、Windows 身份驗證和 SQL 身份驗證都是資料庫身份驗證的一種，身份驗證是用以識別資料的操

作者身份，不管使用哪種身份驗證，只要具有資料庫或表的相關權限，那么均可以對資料庫及表進

行相關的權限范圍之內的增刪查改的操作，所操作后的資料都是相互影響；

2、Windows 身份驗證和 SQL 身份驗證的區別不是權限，因為不管是 Windows 用戶（包括服務器本

地用戶及活動目錄用戶）還是 SQL 用戶，都需要在 SQL 管理器中進行授予權限后，才能在權限范

圍之內操作，樓上所說的 windows 用戶登錄默認 windows 管理員為資料庫的管理員，這種說法是錯

誤的，SQL2008 在安裝時，會讓你添加至少一個 SQL 管理員，一般來說都會添加當前的 Windows

用戶為默認的資料庫管理員，當然也可以添加其它任何 Windows 用戶作為資料庫管理員（并不是

Windows 管理員就會是資料庫管理員），在此也可以決定是否啟用 SQL 身份驗證，如果啟用，那么

則需要為 SQL 用戶 SA 設定一個密碼，

那么區別在于，一個使用 SQL 單獨用戶，一個使用 Windows 用戶，使用 Windows 用戶可以極大的

方便管理員的管理，統一用戶身份驗證（一般使用活動目錄用戶，在實踐環境中，一般企業都會有

自己的活動目錄，如果使用 SQL 用戶的話，管理員則需要記憶和維護兩套用戶名及密碼，在這種情

況下就會使用 Windows 用戶，SQL 可以直接呼叫 Windows 用戶并授予資料庫及表相關權限，那么

在有權限的情況下，管理員可以直接使用自己的域用戶來登陸連接 SQL 資料庫）

老男孩教育-Linux 學院-微信:lidao996(加上備注)40

老男孩教育-Linux 學院-微信:lidao996(加上備注)

3、只要有相關的權限，所登陸到的資料當然是一樣，前面已經說過，身份驗證是用以識別資料的操

作者身份，那么不管以何種身份登陸，只要具有相關權限，那么均可以對資料庫及表進行相關的權

限范圍之內的增刪查改的操作，

1.9.3.2 SQL-SERVER2008中，使用一個有標的具體步驟有哪些？

1.用下面的陳述句選出所的表名：

select name from DBName.Sysobjects where xtype='U'

將上面的 DBName 替換成你的資料庫名，

2.通過 Excel 及 UltraEdit 等 工具，把按下面的陳述句替換 表名 ，生成一系列的修改陳述句，

增加一個 IDKEY 欄位

ALTER TABLE 表名 ADD idkey bigint identity(1,1);

alter table 表名 add constraint pk_表名 primary key (idkey) ;

3.執行這些陳述句

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/40039.html

標籤：MySQL

上一篇：MySQL的Connection-Control介紹

下一篇：MySQL企業級備份