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擁有大量的可以用於各種業務場景的單元處理器可供使用者挑選和使用,這些單元處理器主要提供例如系統之間數據的傳輸,數據的路由,數據的轉換、處理、... ...
一周排行
  • 背景 習慣使用markdown的人應該都知道Typora這個神器,它非常簡潔高效。雖然博客園的線上markdown編輯器也不錯,但畢竟是網頁版,每次寫東西需要登錄系統-進後臺-找到文章-編輯-保存草稿。。。非常難受。。。 但是使用Typora來寫的話,文章圖片又是個問題,本地寫完粘貼到網站上,圖片全 ...
  • 案例:修改預設線程個數 1.NameValueCollection System.Collections.Specialized.NameValueCollection collection = new System.Collections.Specialized.NameValueCollecti ...
  • // from https://stackoverflow.com/questions/35381238/how-to-use-custom-fonts-in-emgucv string text = "塗聚文(Geovin Du)"; // 下麵定義一個矩形區域 int rectWidth = t ...
  • 場景 ASP.NET中新建Web網站並部署到IIS上(詳細圖文教程): https://blog.csdn.net/BADAO_LIUMANG_QIZHI/article/details/107199747 前面講過將ASP.NET的項目部署到本機的IIS上。 但是如果將其部署到伺服器上Window ...
  • 在不同的區域中使用Convert.ToDouble可能會產生問題。 string str = "20.0"; double val = Convert.ToDouble(str); 比如在某些區域語言中得到的結果是200,如: Thread.CurrentThread.CurrentCulture ...
  • 1、前言 ​ 不知道你是否對.NET裡面的定時器產生過一些疑問,以下是武小棧個人的一些總結。 2、官方介紹 在.NET的框架之內定時器有四種,先看一下微軟官方對他們各自特點介紹: System.Timers.Timer,它將觸發事件,並定期在一個或多個事件接收器中執行代碼。 類旨在用作多線程環境中基 ...
  • 筆試考試系統需求分析 1. 引言 1.1編寫目的 項目需求分析目的是使用戶和軟體開發者雙方對項目開發目標有一個共同的理解,便於對軟體開發各個過程的控制與管理,通過對項目開發目標的描述,使開發人員能夠正確理解用戶需求,明確該系統應具有的功能。性能與界面要求。 需求分析作為項目開放的基礎和依據,其預期讀 ...
  • 使用Topshelf部署.net core windows服務 首先新建一個.net core的模板worker程式 過程 略 打開Program.cs namespace TopshelfDemo { public class Program { public static void Main(s ...
  • xaml裡面使用很簡單 xmlns:i="http://schemas.microsoft.com/xaml/behaviors" <i:Interaction.Behaviors> <i:MouseDragElementBehavior/> </i:Interaction.Behaviors> 後 ...
  • Application Insignhts是微軟開發的一套監控程式。他可以對線上的應用程式進行全方位的監控,比如監控每秒的請求數,失敗的請求,追蹤異常,對每個請求進行監控,從http的耗時,到SQL查詢的耗時,完完整整的被記錄下來。當對程式進行優化跟排錯時非常好使。它原來是visualstudio ...