Detailed description of SQL statements in MySQL, detailed description of mysqlsql statements

Detailed description of SQL statements in MySQL, detailed description of mysqlsql statements

1: Data Definition Language (DDL)
 
Used to CREATE, modify, and delete data structures in a DATABASE, such as: 1. create database | drop database); 2: create table | alter table | rename table | drop table); 3: CREATE and delete indexes (CREATEINDEX | drop index)
 
2: Data Query Language (DQL)
 
Query data from one or more tables in the database (SELECT)
 
3: data operation language (DML)
 
Modify data in the database, including INSERT, UPDATE, and DELETE)
 
4: Data Control Language (DCL)
 
Used to access the database, such as: 1: GRANT the user access permission (GRANT); 2: cancel the user access permission (REMOKE)
 
Q: When to use DROP? When to use DELETE?
 
A: It can be seen that for Schema deletion, such as database deletion, table deletion, and index deletion, DROP is used, while for data deletion, DELETE is used.
 
Manage databases and tables
 
1: CREATE a DATABASE ----- create database Name
 
Example: CREATEDATABASE Instant
 
CREATE a TABLE ----- create table name (column name, column data type <column constraints> );
 
Example: create table Student (
 
Id int primart key;
 
Name VARCHAR (50 ));
 
Note: If you create a local temporary table (only visible in the connection where the temporary table is created), add # before the table name. If you create a global temporary table (visible to all connections ), add ##;
 
Create index ----- create index name ON table name (column name ...);
 
Example: (non-unique INDEX) create index NameIndex ON Friend (Nmae );
 
(Unique index) create unique index NameIndex ON Friend (Name );
 
2: connect to the database --- USE Database Name
 
Example: USEInstant;
 
3: delete DATABASE ----- drop database Name
 
Example: DROPDATABASE Instant;
 
Delete TABLE ----- drop table Name
 
Example: drop table Student;
 
Delete An index --- DROP INDEXFriend. PhoneNoIndex; (you must specify the table name and index name)
 
4: copy a table ----- SELECT * FROM My_Friends FROM Friends; (this copy cannot copy the table conventions)
 
Copy table structure without copying data: SELECT * FROM My_FriendsFROM Friends WHERE 1 = 0;
 
5: Modify Table -----

1: Add a new column .. ALTERTABLE Friends ADD Address VARCHAR (50 );
 
2: change the definition... ALTERTABLE Friends MODIFY Phone DEFAULT ('author's here ');
 
3: delete a column .. ALTERTABLE Friends drop cloumn PhoneNo;
 
Ensure data integrity
 
I. Category:
 
1: entity integrity;
 
2: domain integrity;
 
3: Application integrity;
 
4: User-Defined integrity;
 
II. Implementation:
 
1: create a non-NULL constraint ------ NOT NULL
 
2: Set the primary key constraint ------ PRIMARY KEY
 
3: Set the UNIQUE constraint ------- UNIQUE
 
4: Specify the DEFAULT constraint ------- DEFAULT
 
5: Set CHECK constraints ------- CHECK
 
6: automatic number column ---------- IDENTITY
 
7: foreign key constraint -------- FOREIGN KEY
 
Use DML statements to change data
 
1: INSERT data: (single row) value of the VALUES column in the insert into table name column;
 
Example: INSERT into student (id, name) VALUES (1, 'zhang san ');
 
(Multiple rows) insert into table name column SELECT (statement)
 
Example: INSERT into student (id, name) SELECT id + 2, name FROM Students;
 
2: table replication: SELECT column name INTO new table name FROM table name;
 
Example: SELECT * explain student2 FROM Student;
 
3: UPDATE data: UPDATE table name SET column n = new value .. WHERE (filter condition );
 
Example: UPDATE Student SET id = 2, age = 20 WHERE name = 'zhangsan'

UPDATE Student age = NULLWHERE name = 'zhangsan'

4: DELETE data: delete from table name WHERE (filter bar

Example: DELETE FROMStudent WHERE name = 'zhangsan

Note: You can delete all TABLE data without filtering conditions. You can also use the truncate table name.

Simple Data Query
 
1: Query: SELECT column name FROM table name;
 
Example: SELECT id FROM Student;

Query full table data: SELECT * FROM Student;
 
2: Table name prefix: SELECT Student. id FROM Student;
 
3: List alias: select t. Column a as a, T. Column B as B, T. Column C AS C FROM table AS T;
 
4: calculated column: SELECT id, mark * 2 AS MarksFROM Student;
 
SELECT FirstName + ''+ LastName AS FullName FROM Student;
 
5: exclude duplicate data: select distinct column a from table name ;\
 
Example: select distinct name FROM Student;
 
6: query by number of rows: select top rowCount ColumA, ColumB FROM Table;
 
Example: SELECT TOP5 id, name FROM Student;
 
7. Conditional query WHERE
 
8: Range Query ()
 
9: Define the set relationship IN
 
10: fuzzy query LIKE (single character _ multiple characters %)
 
11: NULL data control: SELECT column A, column B FROM table name WHERE column C IS NOT NULL;
 
SELECT column A, column B FROM table name WHERE column C IS NULL;
 
12: Sorting: ascending .. ASC
 
Descending Order... DESC
 
Aggregate functions and Groups
 
1: select count (standard) FROM table name;
 
A: COUNT
 
B: SUM
 
C: Average AVG
 
D: MAX value
 
F: MIN
 
2: group ..
 
3: result set processing: SELECT StudentID, AVG (Mark) AS AverageMark FROM StudentExam GROUP BYStudentID having avg (Mark) <50 or avg (Mark)> 70;
 
4: EXISTS;
 
5: ALL;
 
6: ANY;
 
7: UNION;
 
8: retained duplicate rows: union all;
 
9: intersection and difference: INTERSECT EXCEPT T
 
Join
 
1: inner JOIN: JOIN
 
2: outer join: 1: left outer join or LEFT OUTER JOIN
 
2: RIGHT outer join: RIGHTJOIN or RIGHT OUTERJOIN
 
3: full outer join or FULL OUTER JOIN

The preceding section describes all the content of SQL statement classification in MySQL. I hope you can provide more support for this article ~