mysql flash (update from time to time)

At the interview stage, you will basically ask some questions about mysql, and the specific advanced theory will be gradually supplemented later, but it is inevitable to brush the questions, and we will directly deliver the goods below

Create / drop table and index series

  • Create table
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 'Update time',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  • Delete table
drop table if exists person; 
  • Clear table (delete does not reset auto increment key, truncate resets, truncate does not write log faster)
delete from person;
truncate table person;
truncate person;
  • Add index
#How to add alter table

1.Add to PRIMARY KEY(Primary key index) 

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

 

2.Add to UNIQUE(unique index) 
ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 

 

3.Add to INDEX(General index) 

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

 

4.Add to FULLTEXT(Full-text index) 

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

 

5.Add multi column index 
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )


#create mode can only add these two indexes;
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

  • Delete index
drop index index_name on table_name ;

alter table table_name drop index index_name ;

alter table table_name drop primary key ;

Account related / authority assignment

  • View existing users
SELECT USER,HOST FROM MYSQL.USER;
  • Create mysql user

Format: 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';

Analysis:
USERNAME user name 
HOST host
 PASSWORD
 localhost can only log in locally
 %Local login, remote login
 126.96.10.26 designated login ip
  • Delete mysql user:

Format: DROP USER 'USERNAME'@'HOST';

DROP USER 'vinter'@'localhost';
  • User authorization:
    Format: GRANT CRUD ON DATABASE.TABLES TO 'USERNAME'@'HOST';
GRANT ALL ON *.* TO 'vinter'@'%';
GRANT select ON blog.article TO 'vinter'@'%';
  • Modify Host to log in remotely
SET SQL_SAFE_UPDATES = 0
update MYSQL.user set host = '%' where user = 'root'
  • Change Password

set password for 'USERNAME'@'HOST' = password('New password ');

set password for root@localhost = password('123'); 

Or update the table directly:

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

Data checking

  • Query duplicate data
Write a SQL query to find all duplicate emails in the table named Person.
Example:
+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+
Based on the above input, your query should return the following results:

+---------+
| Email   |
+---------+
| a@b.com |
+---------+

Answer and analysis:

#The number of repetition is more than one (mainly considering the use of group by living, but the title does not refer to group by name)
SELECT
	Email 
FROM
	Person 
GROUP BY
	Email 
HAVING
	Count( * ) >1
  • Delete duplicate data
Write a SQL query to delete all duplicate e-mails in the Person table, and only keep the message with the smallest (or largest) Id in the duplicate message.

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+
Id is the primary key of this table
 For example, after running the query, the Person table above should display the following lines:

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+

Answer and analysis:

#The use of group by is still considered here, but the topic does not refer to name grouping.)
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;
	
	
#Here's an explanation of why we need to cover double layers. It can't be written directly
DELETE 
FROM
	person 
WHERE
	id NOT IN ( SELECT Min( id ) AS id FROM person st GROUP BY email );
//The following error will be prompted:	
You can't specify target table 'person' for update in FROM clause

//This is because mysql does not allow to delete and query a table at the same time. Here we use a temporary table, temp, to avoid this problem.

Logical judgement

  • Update data by condition
Given a payroll, as shown below, M = men and f = women. Exchange all f and m values
 For example, change all f values to m and vice versa. An update query is required and there is no intermediate temporary table.

| id | name | sex | salary |
|----|------|-----|--------|
| 1  | A    | m   | 2500   |
| 2  | B    | f   | 1500   |
| 3  | C    | m   | 5500   |
| 4  | D    | f   | 500    |
After running the query you wrote, you will get the following table:

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

if usage:
If (field = value, former condition is true, former condition is false)

Positive solution:

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

when case usage

Xiaomei is an information technology teacher in a m id dle school. She has a seat table, which is used to store students' names and their corresponding seat IDs. The IDS in the columns are increasing continuously, and Xiaomei wants to change the seats of the two adjacent students. Can you help her write a SQL query to output the results Xiaomei wants?
Example:
+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Abbot   |
|    2    | Doris   |
|    3    | Emerson |
|    4    | Green   |
|    5    | Jeames  |
+---------+---------+
If the data input is from the above table, the output results are as follows:
+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Doris   |
|    2    | Abbot   |
|    3    | Green   |
|    4    | Emerson |
|    5    | Jeames  |
+---------+---------+
Note: if the number of students is odd, there is no need to change the seat of the last student.

Positive solution:

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. Common function types

  • mod()
A new cinema opened in a city, attracting many people to come to see the movie. The movie theater pays special attention to the user experience, and has a special LED display board for movie recommendation, which publishes movie reviews and related movie descriptions.

As the director of the Information Department of the movie theater, you need to write an SQL query to find out all the movies described as non boring (not boring) and with an odd id. please rate the results by grade.

 

For example, the following table, 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     |
+---------+-----------+--------------+-----------+
For the above example, the correct output is:

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

Positive solution:

SELECT
	id,
	movie,
	description,
	rating 
FROM
	cinema 
WHERE
	description != 'boring' 
	AND MOD ( id, 2 ) = 1 
ORDER BY
	rating DESC
  • To days function (timestamps that convert dates 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.
//Given a weather table, write a statement to find the id of the entry with a higher temperature than the previous day
+---------+------------+------------------+
| 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 |
+----+

Positive solution:

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

//Resolution: when you select * from TABLE1,TABLE2... The Cartesian product of two tables will be displayed
(That is, every record found TABLE1 All entries of TABLE2 All entries for)

5 other

  • Cartesian product
Suppose a web site contains two tables, the Customers table and the Orders table. Write an SQL statement to find all Customers who never order anything.

Table name: Customers.

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

+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+
Take the above table as an example to return the following:

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

Positive solution:

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

  • join usage of a connection
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      |
+----------+

Positive solution:

#Method 1:
SELECT
	e.NAME
FROM
	employee e
	JOIN employee m ON e.ManagerId = m.Id 
	AND e.Salary > m.Salary;
	
#Method 2:
SELECT
	e.NAME 
FROM
	employee e,
	employee m 
WHERE
	e.ManagerId = m.Id 
	AND e.Salary > m.Salary;
//Analysis: one is display connection and the other is implicit connection

Tags: MySQL SQL Database

Posted on Tue, 24 Mar 2020 03:51:47 -0700 by H4mm3r3r