mysql刷題(不定時更新)

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

面試階段大家基本都會問一些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 root@localhost = 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;
解析:一種是顯示連接一種是隱式連接


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

-Advertisement-
Play Games
更多相關文章
  • 公司伺服器已經安裝了 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擁有大量的可以用於各種業務場景的單元處理器可供使用者挑選和使用,這些單元處理器主要提供例如系統之間數據的傳輸,數據的路由,數據的轉換、處理、... ...
一周排行
    -Advertisement-
    Play Games
  • 概述:這個WPF項目通過XAML繪製汽車動態速度表盤,實現了0-300的速度刻度,包括數字、指針,並通過定時器模擬速度變化,展示了動態效果。詳細實現包括界面設計、刻度繪製、指針角度計算等,通過C#代碼與XAML文件結合完成。 新建 WPF 項目: 在 Visual Studio 中創建一個新的 WP ...
  • 概述:在WPF中使用`WpfAnimatedGif`庫展示GIF動畫,首先確保全裝了該庫。通過XAML設置Image控制項,指定GIF路徑,然後在代碼中使用庫提供的方法實現動畫控制。這簡化了在WPF應用中處理GIF圖的過程,提供了方便的介面來管理動畫播放和暫停。 當使用 WpfAnimatedGif  ...
  • 您是否曾經訪問過一個網站,它需要很長時間載入,最終你敲擊 F5 重新載入頁面。 即使用戶刷新了瀏覽器取消了原始請求,而對於伺服器來說,API也不會知道它正在計算的值將在結束時被丟棄,刷新五次,伺服器將觸發 5 個請求。 為瞭解決這個問題,ASP.NET Core 為 Web 伺服器提供了一種機制,就 ...
  • 本章將和大家分享如何通過 Elasticsearch 實現自動補全查詢功能。 一、自動補全-安裝拼音分詞器 1、自動補全需求說明 當用戶在搜索框輸入字元時,我們應該提示出與該字元有關的搜索項,如圖: 2、使用拼音分詞 要實現根據字母做補全,就必須對文檔按照拼音分詞。在 GitHub 上恰好有 Ela ...
  • using System; using System.Collections.Generic; using System.IO; using System.Linq; using System.Text; using System.Windows.Forms; namespace OOP { pub ...
  • 概述:以上內容詳細介紹了在C#中如何從另一個線程更新GUI,包括基礎功能和高級功能。對於WinForms,使用`Control.Invoke`;對於WPF,使用`Dispatcher.Invoke`。高級功能使用`SynchronizationContext`實現線程間通信,確保清晰、可讀性高的代碼 ...
  • Nuget包 Microsoft.Extensions.Telemetry.Abstractions 包含的新的日誌記錄source generator,它支持使用[LogProperties]將整個對象作為State與日誌一起記錄。 我將展示一種方法來控制如何使用[LogProperties]對象 ...
  • 支持.Net/.Net Core/.Net Framework,可以部署在Docker, Windows, Linux, Mac。 常見的ORM技術(比如:Entity Framework,Dapper,SqlSugar,NHibernate,等…),它們不是在做Sql語句的程式化變種,就是在做Sq ...
  • 一、引言 在現代應用程式開發中,尤其是在涉及I/O操作(如網路請求、文件讀寫等)時,非同步編程成為了提高性能和用戶體驗的關鍵技術。C#作為.NET框架下的主流開發語言,提供了強大的非同步編程支持,通過async/await關鍵字,可以讓開發者以同步的方式編寫非同步代碼,極大地簡化了非同步編程的複雜性。本文將 ...
  • 一、引言 在.NET開發中,操作Office文檔(特別是Excel和Word)是一項常見的需求。然而,在伺服器端或無Microsoft Office環境的場景下,直接使用Office Interop可能會面臨挑戰。為瞭解決這個問題,開源庫NPOI應運而生,它提供了無需安裝Office即可創建、讀取和 ...