MySQL Learning notes

1. SQL statements are divided into data manipulation language (DML) and data definition language (DDL)

Data Manipulation Language section:

• Select Get Data
• Update updates data
• Delete Deletes data
• INSERT INTO Inserts data

Data Definition Language section:

• Create DATABASE creates new databases
• ALTER DATABASE modifies databases
• CREATE TABLE creates new tables
• ALTER TABLE to modify the data table
• drop TABLE Delete Tables
• CREATE index
• Drop INDEX to delete indexes

2. Grammar
(1) SELECT statement
SELECT column name from table name
The column name can be * and all columns will be queried
(2) SELECT DISTINCT statement
SELECT DISTINCT column name from table name
Only rows with different values will be queried
(3) WHERE clause
SELECT column name from table name WHERE column operator condition value
To add a condition to another statement, the operator has =,! =,>,<,<=,>=,between,like
If the condition value is a string, enclose it in ".
The use syntax for like is select Column name from table name where column name like match condition
The match condition is ' a% ', which is to filter out all rows starting with a in this column, and% is a wildcard character
And and OR, used to separate conditions, and is with, or is, or
(4) Order BY statement
SELECT column name from table name order BY column name
is ascending according to the column
SELECT column name from table name order BY column name Desc
Descending according to the column
(5) INSERT INTO statement
Insert into table name (column 1, column 2) VALUES (value 1, value 2)
Insert into table name values (value 1, value 2)
(6) UPDATE statement
Update a column
Update table name set column name = value 2 where column name = value 1
Update a few columns
Update table name set column 1= value 1, column 2= value 2 where column 1= value 3
(7) DELETE statement
Delete a row
Delete from table name where column 1= value 1
Delete all rows, keep table structure
Delete from table name
Delete * FROM table name
(8) TOP clause and top percent clause
Select top 2 Records
Select Column name from table name limit 2
MySQL does not support the TOP clause, nor does the top percent clause support
(9) in clause
Match multiple values
Select Column name from table name where column name in (value 1, value 2)
(TEN) between clause
MySQL between query results will include the value 1 and the value 2, the database is different this result may be different
Select Column name from table name where column name between value 1 and value 2
If you add a not before between, you will find the opposite result.
Select Column name from table name where column name not between value 1 and value 2

3.
(1) wildcard characters
Using wildcard characters is typically paired with a LIKE clause
% stands for any number of characters
_ Represents any one character
[^ASD]/[!ASD] any character except ASD (in MySQL!). It's useless, you have to use ^)
[Any character in asd]asd (like is not matched with [] in MySQL, [] must be paired with RegExp)
When using RegExp, the match field is not% and _, but ^ (non), * (matches the preceding subexpression 0 or more times), + (matches the preceding subexpression one or more times), {n} (for example a{2} matches 2 a), {n,m} (for example a{2,3} matches a minimum of 2 up to 3)
(2) Alias aliases
Alias for table name
Select Column name from table name as table alias
Alias for column
Select column name as column alias from table name
(3) 2 tables associated
Can be associated by a foreign key
Select Table 1. Column 1 table 2. Column 1 from table 1, table 2 where table 1 primary Key name = Table 2 column name
You can also join by:
INNER JOIN: There is at least one match in the table that returns the row
Left join: Returns all rows from the table, even if the right table does not match
Right join: Returns all rows from the correct table, even if the left table does not match
Full join: All rows are returned, even if the left and right tables do not match
(4) Merging multiple result sets
Union statement
Merge non-repeating values
SELECT statement 1 Union SELECT statement 2
Merge non-repeating values
SELECT statement 1 UNION ALL SELECT statement 2
(5) Inserting columns into another table
Select Table name into new table name [database name] from old table name
(6) SQL constraints
Not NULL if you do not add a value to the field, you cannot update the data or insert the data
Unique, but you can add a unique key (column name) to the last row when you create a table for null
Revoke a UNIQUE constraint ALTER TABLE name DROP INDEX column name
Primary KEY Primary Key
Undo primary key using ALTER TABLE name drop PRIMARY key
Foreign key foreign key foreign key (this table column name) references other table name (other table primary key)
Undo FOREIGN KEY constraint ALTER TABLE name drop FOREIGN key foreign key name
A check constraint is used in a constraint column to have values greater than 0 in the value range check (class name >0) class
Undo Check Constraint ALTER TABLE name drop CONSTRAINT check Constraint column name
Defuault Default
Add a default value to a column with the defaults constraint
Undo default constraint ALTER TABLE name ALTER COLUMN name drop default
(7) Index
The CREATE INDEX statement is used for creating indexes, all of which can use duplicate values, index values cannot be numbers
Create index index name on table name (column name)
Create a unique index: 2 rows cannot have the same index value
Create unique index index name on table name (column name)
Delete index ALTER TABLE name DROP INDEX index name
(8) Change table structure operation
Delete all data in table: Drop table name
Delete all data in table, keep table structure: TRUNCATE TABLE name
Add column: ALTER TABLE name add column name data type
Delete column: ALTER TABLE table name drop column name
Change column data type: ALTER TABLE name ALTER COLUMN name new data type
(9) Auto-increment self-increment, only the primary key to use this constraint, each update plus 1, starting from 1
Change start value: ALTER TABLE table name auto-increment= value 1
(10) Time function
Now () Current date time
Curdate () Current date
Curtime () Current time
Date_format () format time
(11) function
Select Function name (column name) from table name
AVG () Draw value
Count () rows
Max () highest value
MIN () Minimum value
SUM () summation
The GROUP BY statement is used to group result sets, typically for aggregate functions, merging rows of the same name
Select Column name sum (column name) from table name Group BY column name
HAVING clause: The aggregate function cannot increase the where condition to judge, to use the HAVING clause
Select column name, function name (column name) from table name having function name (column name) <200
UCase () to uppercase
Select UCase (column name) from table name
LCase () to lowercase
Mid () extracts characters from the field
Select Mid (column name, start position, extract length) from table name
The extract Length property is optional
Len () returns the field length
Round () Formatting decimals
Select round (column name, number of decimal digits) from table name

MySQL Learning notes