SQL statement

1. What is SQL?

SQL:Structured Query Language, or Structured Query Language.

That is, it defines the rules for operating all relational databases. Each database operates in a different way, called a dialect.

Note: If you do not have a MySQL database installed, you can refer to the article MySQL(1) | Detailed installation and uninstallation of the database (with software) , which describes in detail the installation of MySQL and its simple use.

2. General syntax of SQL

1. SQL statements can be written on one or more lines, ending with a semicolon.
2. Spaces and indentation can be used to enhance the readability of statements.
3. SQL statements in MySQL databases are case insensitive, and uppercase is recommended for keywords.
4. Three ways of commenting
   • Single line comment: - comment content or #comment content (MySQL specific)
   • Multiline comment: /* comment*/

3. SQL Classification

1. DDL(Data DefINition Language): Data Definition Language

   Used to define database objects: databases, tables, columns, etc.

   Keyword: CREATE, DROP,ALTER, etc.

2. DML(Data Manipulation Language): Data Operation Language

   Used to add or delete data from tables in a database.

   Keyword: INSERT, DELETE, UPDATE, etc.

3. DQL(Data Query Language): Data Query Language

   Used to query records (data) of tables in a database.

   Keyword: SELECT, WHERE, etc.

4. DCL(Data Control Language): Data Control Language

   Used to define access and security levels for the database and to create users.

   Keyword: GRANT, REVOKE, etc.

4. DDL - Data Definition Language

4.1. Operational Database (CRUD)

1. C(CREATE): Create

1. Create a database
CREATE DATABASE `Database Name`;

2. Create a database to determine if it exists in the creation
CREATE DATABASE IF NOT EXISTS `Database Name`;

3. Create a database and specify a character set
CREATE DATABASE `Database Name` CHARACTER SET `Database Name`


#Example: Create a db4 database, determine if it exists, and set the character set to gbk
CREATE DATABASE IF NOT EXISTS `db4` CHARACTER SET `GBK`;

1. R(RETRIEVE): Query

1. Query the names of all databases
show DATABASES;

1. U(UPDATE): Modify

1. Modify the character set of the database
ALTER DATABASE `Database Name` CHARACTER SET `Character Set Name`;

1. D(DELETE): Delete

1. Delete database
DROP DATABASE `Database Name`;

2. Judge that the database exists and delete it
DROP DATABASE IF EXISTS `Database Name`;

1. Use database

1. Query the name of the database currently in use
SELECT DATABASE();

2. Use database
USE `Database Name`;

4.2, Action Table (CRUD)

1. C(CREATE): Create

1. Create Table
CREATE TABLE `Table Name`(
    Column Name 1 Data Type 1,
    List 2 Data Type 2,
    ......
    Column Name n  data type n
);
#Note: For the last column, there is no need to add a comma "."

2. Copy Table
CREATE TABLE `Table Name` LIKE `Copied table name`;

#Data type:
1. INT: Integer type
    For example, age INt

2. DOUBLE: Decimal Type
    For example, score double(5, 2)

3. DATE: date(Only year, month, day)
    Format: yyyy-MM-dd

4. DATETIME: date(Contains years, months, days, hours, minutes, seconds)
    Format: yyyy-MM-dd HH:mm:ss

5. TIMESTAMP: timestamp(Contains years, months, days, hours, minutes, seconds)
    Format: yyyy-MM-dd HH:mm:ss
    If you do not assign or assign to this field NULL，Then assign using the current system time by default

6. VARCHAR: Character string
    name VARCHAR(20) : Maximum 20 characters
    For example,``zhangsan``8 characters,``Zhang San``2 characters

Supplement: In a MySQL database, field or column comments are added using the attribute COMMENT.

For example, in a script that creates a new table, you can add a COMMENT attribute to the field definition script to add a comment, followed by an example of creating a table.

CREATE TABLE student(
    id INt COMMENT 'Student id',
    name VARCHAR(32) COMMENT 'Full name',
    age INt COMMENT 'Age',
    scORe double(4,1) COMMENT 'achievement',
    birthday date COMMENT 'Birthday',
    INSERT_time timestamp COMMENT 'Inserted Time'
);

1. R(RETRIEVE): Query

1. Query all table names in a database
show TABLES;

2. Query table structure
DESC `Table Name`;	#Equivalent to SHOW COLUMNS FROM `table name'; DESCRIBE `Table Name`;
#Note 1: DESC RIBE is abbreviated here.
#Note that 2:DESC (abbreviation for DESCENT) means descending arrangement in sorting.

1. U(UPDATE): Modify

1. Modify Table Name
ALTER TABLE `Table Name` RENAME TO `New table name`;

2. Modify Character Set of Table
ALTER TABLE `Table Name` CHARACTER SET `Character Set Name`;

3. Add a column
ALTER TABLE `Table Name` ADD `Column Name` data type;

4. Modify column names and data types
ALTER TABLE `Table Name` CHANGE `Column Name` `New Column Name` New Data Type;

5. Modify Data Type
ALTER TABLE `Table Name` MODIFY `Column Name` New Data Type;

6. Delete Columns
ALTER TABLE `Table Name` DROP `Column Name`;

1. D(DELETE): Delete

1. Delete Table
DROP TABLE `Table Name`;

2. If the table exists,Then delete the table
DROP TABLE IF EXISTS `Table Name`;

5. DML - Data Manipulation Language

DML (Data Manipulation Language) is mainly used to add, delete and change data in ** "Table"**.

1. Add data
INSERT INTO `Table Name` (`Column Name 1`,`Column Name 2`,...`Column Name n`)
VALUES (Value 1,Value 2,...value n);

#Be careful:
	1. One-to-one correspondence between column names and values
	2. If column name is not defined after table name,Add values to all columns by default
		INSERT INTO `Table Name`
		VALUES (Value 1,...value n);
	3. In addition to numeric types, quotation marks are required for other types(single|double)Cause

2. Delete data
DELETE FROM `Table Name` [WHERE condition]

#Be careful:
	1. If no condition is added,All records in the table will be deleted
	2. If you want to delete all records in the table
		-- Not recommended, how many records will delete how many times
		1. DELETE FROM `Table Name`; 
		
		-- Recommended for more efficient use; principle
		-- The principle is to delete the table before creating the same one
		2. truncate TABLE `Table Name`;

3. Modify data
UPDATE `Table Name` 
SET 
	Column Name 1 = Value 1,
	Column Name 2 = Value 2,
	...
	[WHERE condition];

#Note: If no conditions are added, all records in the table will be modified

6. DQL - Data Query Language

DQL (Data Query Language) is mainly used to query data in Tables.

1. SELECT * FROM `Table Name`;

2. grammar
SELECT 
	`Field List`
FROM
	`Table Name`
WHERE
	List of conditions
GROUP BY
	Grouping Fields
HAVING
	Grouping Conditions
ORDER BY
	sort
LIMIT
	Paging restriction;

6.1. Basic Query

1. Queries for Multiple Fields
SELECT `Field 1`, `Field 2`, ...`field n` 
FROM `Table Name`;

#Note: If you query all fields, you can use'*'instead of a list of fields

2. Remove duplication
DISTINCT

3. Computed Columns
	1. Generally, four operations can be used to calculate the values of some columns.
	-- Generally only numeric calculations are performed
	
	2.IFNULL(Expression 1, Expression 2)
    

4. Alias
AS `alias` -- AS Or ignore it

6.2. Conditional Query

1. WHERE Conditions Following Clauses

2. operator
	1. >, <, >=, <=, =, <> -- <>Is not equal to
	2. BETWEEN...AND -- Contains Boundary
	3. IN (aggregate)
	4. LIKE: Fuzzy Query
		Placeholder:
			1. _: Single placeholder
			2. %: Multiple arbitrary symbols
    5. IS NULL
    6. AND perhaps &&
    7. OR perhaps ||
    8. NOT perhaps !

Next, let's execute a few query statements using the student table created above as an example.

-- Query Age Over 20
SELECT * FROM student WHERE age > 20;

-- Query age equal to 20 years
SELECT * FROM student WHERE age = 20;

-- Query age is not equal to 20
SELECT * FROM student WHERE age != 20;
SELECT * FROM student WHERE age <> 20;

-- Query age greater than or equal to 20 less than or equal to 30
SELECT * FROM student WHERE age >= 20 &&  age <=30;
SELECT * FROM student WHERE age >= 20 AND  age <=30;
SELECT * FROM student WHERE age BETWEEN 20 AND 30;

-- Query information about age 22, 18, 25
SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25
SELECT * FROM student WHERE age IN (22,18,25);

-- Query English for NULL
SELECT * FROM student WHERE englISh = NULL; -- Wrong writing. NULL Value cannot be used = And (!=) judge

SELECT * FROM student WHERE englISh IS NULL; -- Correct Writing

-- Query English for failures NULL
SELECT * FROM student WHERE englISh  IS NOT NULL;

-- What is the last name of a horse? like
SELECT * FROM student WHERE name LIKE 'Horse%';

-- Query names for people whose second word is changed
SELECT * FROM student WHERE name LIKE "_turn%";

-- Query for a person whose name is 3 words
SELECT * FROM student WHERE name LIKE '___';

-- Query for people whose names contain virtue
SELECT * FROM student WHERE NAME LIKE '%Virtue%';

7. DCL - Data Control Language

DCL (Data Control Language) is mainly used by DBA to manage object privileges in the system. It is rarely used by ordinary developers. DCL is used to grant or reclaim certain privileges to access the database, control the time and effect of database manipulation transactions, monitor the database, etc.

DBA: Data Manager

1. Manage Users
    1. Add User
    	CREATE USER 'User name'@'host name' IDENTIFIED BY 'Password';
    	
    2. delete user
    	DROP USER 'User name'@'Host Number';
    	
    3. Modify user password
    	UPDATE USER 
    	SET PASSWORD = PASSWORD('New password') 
    	WHERE USER = 'User name';
    	
    	-- For example, the lisi Change user's password to abc
    	UPDATE USER SET 
    	PASSWORD = PASSWORD('abc') 
    	WHERE USER = 'lisi';
    	
    4. Query Users
    	-- 1. switch to MySQL data base
		USE mysql;
		-- 2. query user surface
		SELECT * FROM USER;
		Wildcards:% Indicates that a user can log in to the database on any host
		
		

#Add: What if you forget the root user password in MySQL?
    1. Run as Administrator cmd -- > net stop mysql Stop it mysql service
    2. Start without authentication mysql Services: mysqld --skip-grant-tables
    3. Open New cmd window,Direct Input mysql Command, knock back. You can log in successfully
    4. USE mysql;
    5. UPDATE USER 
       SET PASSWORD = PASSWORD('Your new password') 
       WHERE USER = 'root';
    6. Close both windows
    7. Open Task Manager and end manually mysqld.exe Process
    8. start-up mysql service
    9. Log in with a new password.

2. Rights Management
	1. Permission Query
		-- Query permissions
		SHOW GRANTS FOR 'User name'@'host name';
		
		-- For example, query the permissions of Lisi
		SHOW GRANTS FOR 'Li Si'@'%';

    2. Grant privileges
    	-- Grant privileges
    	GRANT Authorization List ON `Database Name`.`Table Name` TO 'User name'@'host name';
    	
    	-- For example, grant all permissions to Zhang San on any table in any database
    	GRANT ALL ON *.* TO 'Zhang San'@'localhost';

	3. Revoke Permission
		-- Revoke Permission
		REVOKE Permission List ON `Database Name`.`Table Name` FROM 'User name'@'host name';
		
		-- For example, revoke Four Users of Li in db3 data base account Modify permissions in tables
		REVOKE UPDATE ON `db3`.`account` FROM 'Li Si'@'%';

Note: This article is a personal learning note. If there are any errors, please correct them.