mysql刷題(不定時更新)

来源:https://www.cnblogs.com/vinter/archive/2020/03/24/12560427.html

面試階段大家基本都會問一些mysql的題,具體的高深理論以後再慢慢補充,但是刷題是不可避免的,下麵直接上貨 創建/刪除表和索引系列 創建表 sql CREATE TABLE if not exists ( int(11) NOT NULL AUTO_INCREMENT, date DEFAULT N ...


面試階段大家基本都會問一些mysql的題,具體的高深理論以後再慢慢補充,但是刷題是不可避免的,下麵直接上貨

創建/刪除表和索引系列

  • 創建表

CREATE TABLE if not exists `test_date` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` date DEFAULT NULL,
  `temp` int(11) NOT NULL,
  `updateTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  • 刪除表
drop table if exists person; 
  • 清空表(delete不重置自增鍵,truncate重置,truncate不寫日誌速度更快)
delete from person;
truncate table person;
truncate person;
  • 增加索引
#alter table添加方式

1.添加PRIMARY KEY(主鍵索引) 

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

 

2.添加UNIQUE(唯一索引) 
ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 

 

3.添加INDEX(普通索引) 

ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 

 

4.添加FULLTEXT(全文索引) 

ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 

 

5.添加多列索引 
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )


#create方式只能添加這兩種索引;
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

  • 刪除索引
drop index index_name on table_name ;

alter table table_name drop index index_name ;

alter table table_name drop primary key ;

賬戶相關/許可權分配

  • 查看已經存在的用戶
SELECT USER,HOST FROM MYSQL.USER;
  • 創建mysql 用戶

格式:CREATE USER 'USERNAME'@'HOST' IDENTIFIED BY 'PASSWORD';


CREATE USER 'vinter'@'%' IDENTIFIED BY '123456';
CREATE USER 'jerry'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'Tom'@'126.96.10.26' IDENTIFIED BY '123456';

解析:
USERNAME                用戶名 
HOST                    主機
PASSWORD                密碼
localhost               只可以本地登陸
%                       本地登陸,遠程登陸
126.96.10.26            指定登陸的ip
  • 刪除mysql 用戶:

格式:DROP USER 'USERNAME'@'HOST';

DROP USER 'vinter'@'localhost';
  • 用戶授權:
    格式:GRANT CRUD ON DATABASE.TABLES TO 'USERNAME'@'HOST';
GRANT ALL ON *.* TO 'vinter'@'%';
GRANT select ON blog.article TO 'vinter'@'%';
  • 修改Host 可以遠程登陸
SET SQL_SAFE_UPDATES = 0
update MYSQL.user set host = '%' where user = 'root'
  • 修改密碼

set password for 'USERNAME'@'HOST' = password('新密碼');

set password for [email protected] = password('123'); 

或者直接更新表:

 use mysql;
 
 update user set password=password('123') where user='root' and host='localhost';
 
 flush privileges; 

數據查重

  • 查詢重覆數據
編寫一個 SQL查詢 來查找名為 Person 的表中的所有重覆電子郵件。
示例:
+----+---------+
| Id | Email   |
+----+---------+
| 1  | [email protected] |
| 2  | [email protected] |
| 3  | [email protected] |
+----+---------+
根據以上輸入,您的查詢應返回以下結果:

+---------+
| Email   |
+---------+
| [email protected] |
+---------+

答案及解析:

#重覆的也就是數量大於一的(主要考慮group by having的用法,但是題目卻不指名分組)
SELECT
	Email 
FROM
	Person 
GROUP BY
	Email 
HAVING
	Count( * ) >1
  • 刪除重覆數據
編寫一個SQL查詢來刪除Person表中所有重覆的電子郵件,在重覆的郵件中只保留Id最小(或最大)的郵件。

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | [email protected] |
| 2  | [email protected]  |
| 3  | [email protected] |
+----+------------------+
Id是這個表的主鍵.
例如,在運行查詢之後,上面的 Person 表應顯示以下幾行:

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | [email protected] |
| 2  | [email protected]  |
+----+------------------+

答案及解析:

#這裡還是考慮group by 的用法,但是題目卻不指名分組)
DELETE 
FROM
	person 
WHERE
	id NOT IN ( SELECT id FROM ( SELECT Min( id ) AS id FROM person st GROUP BY email ) temp );
SELECT
	* 
FROM
	person;
	
	
#這裡解釋一下為什麼要套雙層,不能直接寫成
DELETE 
FROM
	person 
WHERE
	id NOT IN ( SELECT Min( id ) AS id FROM person st GROUP BY email );
會提示如下錯誤:	
You can't specify target table 'person' for update in FROM clause

這是因為mysql不允許同時刪除和查詢一個表,這裡我們是用一個臨時表temp來避免這種問題。

邏輯判斷

  • 按條件更新數據
給定一個工資表,如下所示,m=男性 和 f=女性 。交換所有的 f 和 m 值
例如,將所有 f 值更改為 m,反之亦然。要求使用一個更新查詢,並且沒有中間臨時表。

| id | name | sex | salary |
|----|------|-----|--------|
| 1  | A    | m   | 2500   |
| 2  | B    | f   | 1500   |
| 3  | C    | m   | 5500   |
| 4  | D    | f   | 500    |
運行你所編寫的查詢語句之後,將會得到以下表:

| id | name | sex | salary |
|----|------|-----|--------|
| 1  | A    | f   | 2500   |
| 2  | B    | m   | 1500   |
| 3  | C    | f   | 5500   |
| 4  | D    | m   | 500    |

if的用法:
if(欄位=值,前麵條件為真值,前麵條件為假的值)

正解:

update salary set sex = if(sex='m', 'f', 'm')

when case用法

小美是一所中學的信息科技老師,她有一張 seat 座位表,平時用來儲存學生名字和與他們相對應的座位 id。其中縱列的 id 是連續遞增的,小美想改變相鄰倆學生的座位。你能不能幫她寫一個 SQL query 來輸出小美想要的結果呢?
示例:
+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Abbot   |
|    2    | Doris   |
|    3    | Emerson |
|    4    | Green   |
|    5    | Jeames  |
+---------+---------+
假如數據輸入的是上表,則輸出結果如下:
+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Doris   |
|    2    | Abbot   |
|    3    | Green   |
|    4    | Emerson |
|    5    | Jeames  |
+---------+---------+
註意:如果學生人數是奇數,則不需要改變最後一個同學的座位。

正解:

SELECT
CASE
		
	WHEN MOD
		( id, 2 ) = 1 
		AND id != ( SELECT max( id ) FROM person ) THEN
			id + 1 
			WHEN MOD ( id, 2 ) = 0 THEN
			id - 1 ELSE id 
		END id,
	email 
FROM
	person 
ORDER BY
	id

4.常用函數類型

  • 取餘函數 mod()
某城市開了一家新的電影院,吸引了很多人過來看電影。該電影院特別註意用戶體驗,專門有個 LED顯示板做電影推薦,上面公佈著影評和相關電影描述。

作為該電影院的信息部主管,您需要編寫一個 SQL查詢,找出所有影片描述為非 boring (不無聊) 的並且 id 為奇數 的影片,結果請按等級 rating 排列。

 

例如,下表 cinema:

+---------+-----------+--------------+-----------+
|   id    | movie     |  description |  rating   |
+---------+-----------+--------------+-----------+
|   1     | War       |   great 3D   |   8.9     |
|   2     | Science   |   fiction    |   8.5     |
|   3     | irish     |   boring     |   6.2     |
|   4     | Ice song  |   Fantacy    |   8.6     |
|   5     | House card|   Interesting|   9.1     |
+---------+-----------+--------------+-----------+
對於上面的例子,則正確的輸出是為:

+---------+-----------+--------------+-----------+
|   id    | movie     |  description |  rating   |
+---------+-----------+--------------+-----------+
|   5     | House card|   Interesting|   9.1     |
|   1     | War       |   great 3D   |   8.9     |
+---------+-----------+--------------+-----------+

正解:

SELECT
	id,
	movie,
	description,
	rating 
FROM
	cinema 
WHERE
	description != 'boring' 
	AND MOD ( id, 2 ) = 1 
ORDER BY
	rating DESC
  • TO_DAYS函數(將日期轉換成天數的時間戳)
Given a Weather table, write a SQL query to find all dates' Ids with higher temperature compared to its previous (yesterday's) dates.
翻譯:給定一個天氣表,寫一個語句用來找出比前一天氣溫高的條目的id
+---------+------------+------------------+
| Id(INT) | Date(DATE) | Temperature(INT) |
+---------+------------+------------------+
|       1 | 2015-01-01 |               10 |
|       2 | 2015-01-02 |               25 |
|       3 | 2015-01-03 |               20 |
|       4 | 2015-01-04 |               30 |
+---------+------------+------------------+
For example, return the following Ids for the above Weather table:
+----+
| Id |
+----+
|  2 |
|  4 |
+----+

正解:


SELECT
	w1.id 
FROM
	weather w1,
	weather w2 
WHERE
	TO_DAYS( w1.date ) = TO_DAYS( w2.date ) + 1 
	AND w1.temperature > w2.temperature

解析:當你select * from TABLE1,TABLE2 ...的時候會顯示出兩個表的笛卡爾積
(即查出的記錄中每一個TABLE1的條目都對應TABLE2的所有條目)

5 其他

  • 笛卡爾積
假設一個網站包含兩個表,Customers 表和 Orders 表。編寫一個SQL語句找出所有從不訂購任何東西的客戶。

表名: Customers。

+----+-------+
| Id | Name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+
Table: Orders.

+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+
以上述表格為例,返回以下內容:

+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

正解:

SELECT name 
FROM
	customers 
WHERE
	customers.id NOT IN (SELECT
	customerid 
FROM
	orders)

  • 連接的join用法
The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.

+----+-------+--------+-----------+
| Id | Name  | Salary | ManagerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | NULL      |
| 4  | Max   | 90000  | NULL      |
+----+-------+--------+-----------+
Given the Employee table, write a SQL query that finds out employees who earn more than their managers. For the above table, Joe is the only employee who earns more than his manager.

+----------+
| Employee |
+----------+
| Joe      |
+----------+

正解:

#方法1:
SELECT
	e.NAME
FROM
	employee e
	JOIN employee m ON e.ManagerId = m.Id 
	AND e.Salary > m.Salary;
	
#方法2:
SELECT
	e.NAME 
FROM
	employee e,
	employee m 
WHERE
	e.ManagerId = m.Id 
	AND e.Salary > m.Salary;
解析:一種是顯示連接一種是隱式連接


您的分享是我們最大的動力!

更多相關文章
  • 公司伺服器已經安裝了 Docker 環境,但沒有安裝 Docker Compose,使用起來十分不便。由於服務無法連接外網,下麵演示如何離線安裝 Docker Compose。 (1)首先訪問 docker compose 的 GitHub 版本發佈頁面: https://github.com/do ...
  • Linux下離線安裝Docker 一、基礎環境 1、操作系統:CentOS 7.3 2、Docker版本:18.06.1 官方下載地址(打不開可能很慢) 3、百度雲Docker 18.06.1地址:https://pan.baidu.com/s/1YdN9z72QutPkHBfLq06H1A 密碼: ...
  • 一 Flannel組件 1.1 flannel介紹 Kubernetes的網路模型假定了所有Pod都在一個可以直接連通的扁平網路空間中。若需要實現這個網路假設,需要實現不同節點上的Docker容器之間的互相訪問,然後運行Kubernetes。目前已經有多個開源組件支持容器網路模型。如Flannel、 ...
  • 一 CNM網路模型 1.1 網路模型 生產環境中,跨主機容器間的網路互通已經成為基本要求,更高的要求包括容器固定IP地址、一個容器多個IP地址、多個子網隔離、ACL控制策略、與SDN集成等。目前主流的容器網路模型主要有Docker公司提出的Container Network Model(CNM)模型 ...
  • 不知道各位第一次接觸linux系統是什麼時候,我是高中的時候在一篇技術推文中接觸到linux系統的,不同於windows系統的顯示界面和命令操作讓我很是感興趣。於是在當時還未熟悉電腦操作的情況下,手忙腳亂地亂鼓弄了一番。當時記得是準備在筆記本上裝雙系統的,結果不知道為什麼(可能是當時分區的時候不懂, ...
  • MySQL整理 這幾天整理了MySQL的知識點,還有一部分需要在梳理一下,圖上先寫這麼多吧。 未完待續…… ...
  • 從監控工具DPA中發現一個資料庫(SQL Server 2008 R2)的等待事件突然彪增,下鑽分析發現資料庫執行存儲過程sp_MailItemResultSets時,引起了非常嚴重的等待(High Wait),而主要的等待事件為PREEMPTIVE_OS_WAITFORSINGLEOBJEC。 如... ...
  • 2.3 NiFi Processor應用介紹對於NiFi的使用者來說,如果想要創建一個高效的數據流,那麼就需要瞭解什麼樣的單元處理器才最適合這個數據流。NiFi擁有大量的可以用於各種業務場景的單元處理器可供使用者挑選和使用,這些單元處理器主要提供例如系統之間數據的傳輸,數據的路由,數據的轉換、處理、... ...
一周排行
  • 在上篇文章中我們已經知道了多線程是什麼了,那麼它到底可以幹嘛呢?這裡特別聲明一個前面的委托沒看的同學可以到上上上篇博文查看,因為多線程要經常使用到委托。源碼 一、非同步、同步 1.同步(在計算的理解總是要你措不及防,同步當線程做完一件事情之後,才會執行後續動作),同步方法慢,只有一個線程執行,非同步方法 ...
  • 本文主要是講解stopwatch對程式運行時間的準確測量 僅僅介紹裡面的StartNew()方法,Restart()方法和ElapsedMilliseconds { get;}屬性 public void StartNew():作用是對新的 System.Diagnostics.Stopwatch ...
  • 一、引言 RabbitMQ是Rabbit Message Queue的簡寫,但不能僅僅理解其為消息隊列,消息代理更合適。RabbitMQ是一個由 Erlang 語言開發的AMQP(高級消息隊列協議)的開源實現,其內部結構如下: RabbitMQ作為一個消息代理,主要和消息打交道,負責接收並轉發消息。 ...
  • TerminalMACS(Terminal Manager And Check System) 遠程終端管理和檢測系統 本文同步更新地址:https://dotnet9.com/11429.html 一、本系統可監控多種終端資源: 移動端 Android iOS PC端 Windows Linux ...
  • 首先,好消息是Goole將於2020年2月份發佈Chrome 80版本。本次發佈將推進Google的“漸進改良Cookie”策略,打造一個更為安全和保障用戶隱私的網路環境。 壞消息是,本次更新可能導致瀏覽器無法向服務端發送Cookie。如果你有多個不同功能變數名稱的應用,部分用戶很有可能出現會話時常被打斷的 ...
  • 在偶然一次調試某程式時,遇到提示: 無法載入程式集*****.XmlSerializers.dll,文件找不到(Could not load file or assembly ****.XmlSerializers.dll , FileNotFoundException...)。於是嘗試在項目屬性中 ...
  • 在上一篇abp(net core)+easyui+efcore實現倉儲管理系統——入庫管理之五(四十一) 文章中實現了入庫管理的列表頁面,並實現了控制器的代碼。在今天我們學習如何在前端實現新增入庫單信息界面。 ...
  • 面向對象 面向對象是一個抽象的概念,其本質就是對事物以抽象的方式建立對應的模型。 簡單來講,比如我有一隻鋼筆,那麼我就可以通過分析,可以得到 這隻鋼筆的材第是塑料,品牌是個雜牌 ,裡面裝的墨是黑色的,可以用。這時候就能建立一個鋼筆的模型,它在這裡應該有這些屬性: 圖是一個不正確的UML類圖,但是可以 ...
  • 在ASP.NET MVC中有四種過濾器類型 Action 1、在ASP.NET MVC項目中,新建文件夾Filter,然後新建類MyCustormFilter,繼承自ActionFilterAttribute類,我們來看下ActionFilterAttribute類有如下四個方法,從命名我應該就可以 ...
  • 你需要瞭解的 HTTP Status Code Intro 現在前後端分離的開發模式越來越流行,後端負責開發對應的 API,前端只需要 關註前端頁面的數據展示和前端邏輯即可。 對於前後端分離這種開發模式,我個人還是比較喜歡的,因為這樣可以讓更專業的人做更專業的事情,後端專註於做 API 的開發設計, ...
x