MySQL notes, hidden favorites

This is the 9th day of my participation in the November Gwen Challenge. Check out the event details: The last Gwen Challenge 2021

1 definition

Table in a database: A row is called a record. Each column is called a property, or a field. Primary key: a special field in a table that has a unique value and can be used to uniquely identify a record. Foreign key: The value of a field in a table is the value of a field in another table or itself.

2 Data type of MySQL

2.1 numerical

1. TINYINT small integers
2. INT/INTEGER to INTEGER
3. BIGINT long integer
4. FLOAT the decimal
5. DOUBLIE double decimal

2.2 the date

1. DATE YYYY-MM-DD
2. TIME HH:MM:SS
3. YEAT YYYY
4. DATETIME hybrid
5. TIMESTAMP accuracy

2.3 the string

1. CHAR A fixed-length character string
2. VARCHAR u
3. BLOB binary files
4. LONGBLOB long binary file
5. TEXT Long TEXT data
6. LONGTEXT LONGTEXT data

Note: This is a summary of common types, not all types

3 the SQL statement

3.1 Creating a Database

CREATE DATABASE DATABASE name [parameter]Copy the code

For example, create a database named test and set the character set to UTF-8

CREATE DATABASE test CHARACTER SET utf8;
Copy the code

If the following information is displayed, the creation is successful.

Query OK, 0 rows affected (0.00 sec)
Copy the code

You can view the newly created database using the following statement

SHOW DATABASES;
Copy the code

Select the database you want to operate on

USE test;
Copy the code

Database Changed appears after executing the above statement, and all your actions will be performed in the test Database.

3.2 create a table

CREATE TABLE name (column name data attribute [constraint], column name data attribute... ...). ;Copy the code

For example, create a users table whose ID is primary key and grows automatically. PRIMARY KEY: indicates the PRIMARY KEY constraint. AUTO_INCREMENT: indicates the autogrowth constraint

CREATE TABLE users(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
account VARCHAR(50),
pwd VARCHAR(50),
);
Copy the code

3.3 Inserting Data

INSERT INTO VALUES (1, 2,... , NULL,...). ;Copy the code

This insertion method must write all values, or NULL if empty. Values and columns must correspond one to one with primary keys and write NULL if set to increment

You can also specify the column to which you want to insert data, as follows.

INSERT INTO table name (1, 2,...) VALUES (Value 1, value 2,....) ;Copy the code

Press Enter to appear

Query Ok, 1 row affected (0.05sec)Copy the code

Success. Otherwise, check the SQL statement.

For example, insert a piece of data into the Users table you just created

INSERT INTO users VALUES(NULL,'admin','admin');
Copy the code

The first column is id, with the primary key increment and no insertion required.

3.4 Querying Data

SELECT * FROM table_name [SELECT * FROM table_name];Copy the code

For example, query the Account column of all data in the Users table.

SELECT account FROM users;
Copy the code

3.4.1 track WHERE the query

You can give the table after FROM an alias. Such as:

SELECT tn.id,tn.account FROM tablename AS tn
WHERE tn.id = 1;
Copy the code

3.4.2 Conditional statement after WHERE

You can write a variety of conditional statements after WHERE. 1. Relational statements: =,>,<,! =,>=,<= 2. Logic statement: AND,OR,NOT 3. IS NULL 4. The ID is between 11 and 15. 5. IN: IN.. WHERE u.ccount IN (value 1, value 2...) ; Query account in (value 1, value 2...) 5. Fuzzy matching WHERE u.id LIKE 'wildcard '; Wildcard '%': replaces zero or more characters '_': replaces one character WHERE U.account LIKE '%A%'; Query data with 'A' in account field. [charList]: any single character in the character column WHERE u.account LIKE '[ASD]%'; Query data starting with A, S, and D. [! Charlist]: any single character that is not in the character column WHERE u.account LIKE '[!ASD]%'; Query data that does not start with A, S and DCopy the code

3.4.3 Aggregation function

SELECT AVG FROM table nameCopy the code

1. AVG(column name): Returns the average value of a column
2. COUNT(column name): Returns the number of rows matching the specified condition
3. FIRST(column name): Returns the value of the FIRST record in the specified field.
4. LAST(column name): Returns the value of the LAST record in the specified field.
5. MAX(column name): Returns the maximum value in a column. NULL values are not included in the calculation.
6. MIN(column name): Returns the minimum value in a column. NULL values are not included in the calculation.
7. SUM(column name): Returns the total number of numeric columns.
8. UCASE(column name): Converts the value of a field to uppercase.
9. LCASE(column name): Converts the field value to lowercase.
10. MID(column name, start position, cut length): Used to extract characters from text fields.

SELECT MID(account,1,3) as uname FROM users;
Copy the code

11. LEN(column name): Returns the length of the value in the text field.
12. ROUND(column name, decimal number): Used to ROUND a numeric field to the specified decimal number.
13. CURDATE(): returns the current date. YYYY – MM – DD.
14. CURTIME(): returns the current time. HH: MM: SS.
15. NOW(): Returns the current time. MM – DD YYYY – HH: MM: SS.
16. YEAR(d), MONTH(d),DAY(d): Returns the YEAR, MONTH, and DAY of the parameters.
17. ADDDATE(d,n): adds n days to the time of d.
18. SUBDATE(d,n): subtracts n days from d.

19. DATE_FORMAT(d,f): format time d. %a abbreviated name of the week % B abbreviated name of the month % C month, value %D Days of the month with English prefix %D days of the month, value (00-31) % E days of the month, Value (0-31) %f microseconds %H hours (00-23) %H hours (01-12) %I(uppercase I) hours (01-12) %I minutes, Values (00-59) %j year days (001-366) % K hours (0-23) % L (lowercase L) hours (1-12) %M Month name %M month, values (00-12) %p AM or PM %r time, 12- hour (hh:mm: SS AM or PM) %S S (00-59) %S S (00-59) %T time, 24 hours (hh:mm: SS) %U Week (00-53) Sunday is the first day of the week %U Week (00-53) Monday is the first day of the week %V Week (01-53) Sunday is the first day of the week, With %X use %v week (01-53) Monday is the first day of the week, with %X use %W week name %W week day (0 = Sunday, 6= Saturday) %X year, where Sunday is the first day of the week, 4 bits, with %v use %X year, where Monday is the first day of the week, 4 bits, using %Y years with %v, 4 bits %Y years, 2 bitsCopy the code

3.4.4 Group Query

The GROUP BY statement is used to combine aggregate functions to GROUP result sets BY one or more columns.

SELECT * FROM table_name WHERE GROUP BY table_nameCopy the code

For example, count the number of users in each group based on the role attribute in the user table

SELECT role, COUNT(id)
FROM users
GROUP BY role;
Copy the code

3.4.5 Group Filtering

The reason for adding the HAVING clause in SQL is that the WHERE keyword cannot be used with the aggregate function. For example, count the number of users in each group based on the roles attribute in the user table and search for roles whose number is greater than 10

SELECT role, COUNT(id)
FROM users
GROUP BY role
HAVING COUNT(id)>10;
Copy the code

3.4.6 Sorting of results

The ORDER BY statement is used to sort the result set BY the specified column. The default is ascending, and the descending order uses the keyword DESC. For example, the number of users in each group is counted based on the role attribute in the user table, and the number of users is ranked in descending order

SELECT role, COUNT(id) count
FROM users
GROUP BY role
ORDER BY count DESC;
Copy the code

3.5 Updating Data

UPDATE table name SET column name = new value WHERE conditionCopy the code

WHERE conditions are optional. If not, modify all data in the table

For example, change the password of user admin to root by modifying the data in the Users table

UPDATE users SET pwd = 'root' WHERE account = 'admin';
Copy the code

3.6 Deleting Data

DELETE FROM table name WHERE conditionCopy the code

Select * from ‘WHERE’ WHERE (select * from ‘WHERE’)

4 Modification of table attributes

4.1 Table Renaming

ALTER TABLE table_name RENAME new TABLE name;Copy the code

4.2 Adding a New column

ALTER TABLE table_name ADD table_name [AFTER insert position]Copy the code

For example, add a role field of type INTEGER after the PWD field in the Users table

ALTER TABLE users ADD role INTEGER AFTER pwd;
Copy the code

4.3 modify the column

ALTER TABLE table_name CHANGE column name new column name new datatype;Copy the code

For example, change the PWD field in the Users table to password with type varchar(20).

ALTER TABLE users CHANGE pwd password varchar(20);
Copy the code