SQL CRUD additions and deletions to review summary
1. Create a database
CREATE database name
Deleting a database
DROP database name
2. Create a table
CREATE TABLE table name
(
column name Type (length) from the growth primary key is not empty,
)
Self-growth: auto_increment
PRIMARY key: Primary key
non-empty: NOT NULL
FOREIGN key: foreign key Column name references table name (column name)
Delete a table
drop table name
3.CRUD Operation
insert into table name values (value)
insert into table name (column name) values (value)
Delete from table name where condition
Update table name set column name = value WHERE Condition
select * FROM table name
Select column name from table name
select * FROM table name where condition
select * FROM table name where Condition 1 or condition 2
select * FROM table name where column name like '% value% '
select * FROM table name where column name between A and B
select * FROM table name where column name in (value)
select * FROM table name limit n,m
select * FROM table name order BY column name Desc
select * FROM table name Group BY column name having condition
Select COUNT (*) from table name
Select sum (column name) from table name
Select AVG (column name) from table name
select max (column name) from table name
Select min (column name) from table name
select DISTINCT column name from table name
Advanced query:
1. Connection Query
SELECT * FROM table 1, table 2 where connection conditions
SELECT * FROM table 1 join table 2 on join condition
2. Joint Queries
Select column name from table 1
Union
Select column name from table 2
3. Sub-query
unrelated subqueries
Subqueries are not related to parent queries, and subqueries can be executed separately
select * FROM table where column = (select column from table)
related sub-query
Subqueries and parent queries are related to each other, and subqueries need to use the contents of the parent query
Database additions and deletions revise review