MySQL basic syntax

Concept of SQL

What is SQL: Structured Query Language

SQL function: through SQL statements, we can easily operate the database, tables and data in the database. SQL is a standard that all database management systems need to follow. Different database manufacturers support SQL statements, but they all have unique contents.

Classification of SQL language:

DDL statements operate database and table create, drop, alter, etc
DML statement inserts, deletes and updates table data
DQL statement performs various dimension select queries on table data
DCL data control language is used to define the access rights and security levels of the database and create users. Keywords grant, revoke, etc

The constraints of MySQL database ensure the correctness, validity and integrity of data, including primary key constraints, unique constraints and non empty constraints

DDL syntax

Operation database

Show all databases:
```
show databases;
```
Display database:
```
show database <database_name>;
```
Display the created database information:
```
show create database <database_name>;
```
Create database:
```
create database <database_name>;
```
Determine whether the database exists and create the database:
```
create database if not exists <database_name>;
```

Create a database and specify a character set:

create database <database_name> character set <utf8>;

Usage data:
```
use <database_name>;
```
View the database used:
```
select database();
```

Modify database character set:

alter database <database_name> default character set <utf8>;

Delete database:
```
drop database <database_name>;
```

Operation data sheet

View all tables:
```
show tables;
```

Create table:

create table <table_name> (<name1> <type1>, <name2> <type2>);

View table structure:
```
desc <table_name>;
```
To view a table creation statement:
```
show create table <table_name>;
```

Create a table with the same table structure:

create table <new_table_name> like <old_table_name>;

Delete table:
```
drop table <table_name>;
```
Judge whether the table exists and delete it:
```
drop table if exists <table_name>;
```

Add table columns:

alter table <table_name> add <col_name> <type>;

Modify table column type:

alter table <table_name> modify <col_name> <type>;

Modify column name:

alter table <table_name> change <old_col_name> <new_col_name> <type>;

Delete column:

alter table <table_name> drop <col_name>;

Modify table name:

rename table <old_table_name> to <new_table_name>;

Modify table character set:

alter table <table_name> character set <utf8>;

DML statement

Insert all data:
- The value must correspond to the field, with the same number and type
- The data size of the value must be within the length of the field
- Except for numeric types, values of other field types must use quotation marks (single quotation marks)
- If you want to insert a null value, you can not write the field or insert null
```
insert into <table_name>(name1, name2, ...) values(vaule1, value2, ...);
--Equivalent to
insert into values(vaule1, value2, ...);
```
Worm replication:
- If you only want to copy the user in the student table_ The data of name and age fields are transferred to the student2 table using the following format
```
insert into student2(user_name, age) select user_name, age from student;
```
```
insert into student2() select * from student;
```

Update table records

Modify data without conditions:
```
update <table_name> set <name>=<value>;
```

Modify data with conditions:

update <table_name> set <name>=<value> where <name>=<value>;

Delete table record
- Delete data without conditions:
```
delete from <table_name>;
```
- Delete data with conditions:
```
delete from <table_name> where <name>=<value>;
```
- Delete data
  - Delete is to delete the data in the table one by one
  - Truncate destroys the entire table and creates a new table. The new table structure is the same as the original table structure
  - Primary key auto increment, delete Auto_ Increment does not reset, truncate Auto_ Reset increment to 1
```
truncate table <table_name>;
```

DQL statement

Query does not modify the data in the database, but is a way to display the data.

Query value:
```
select * from student;
```

Alias query: PS: as can be omitted without writing.

select <old_col_name> as <new_col_name> from student;

No duplicate name appears in the query result of name and age:
```
select distinct name, age from student;
```

Query results involved in the operation: PS: those involved in the operation must be numeric types.

select <col_ Name > + fixed value from < table_ name>;
select <col1_name> + <col2_name> from <table_name>;

Query data with ID 1, 3 and 5:

select * from <table_name> where id = 1 or id = 3 or id = 5;

--Equals

select * from <table_name> where id in (1, 3, 5);

Query data whose ID is not 1, 3 or 5:

select * from <table_name> where id not in (1, 3, 5);

Query data with ID between 3 and 7: (closed interval)
```
select * from <table_name> where id between 3 and 7;
```
Fuzzy query:
- %: indicates 0 or more characters (any character)
- \_: Represents a character
```
select * from <table_ Name > where < name > like < 'wildcard string' >;
```

Sort:

ASC: ascending (default)

Desc: descending order

select * from <table_name> order by age desc, id asc;

Aggregate function
- Count: count the number of records in the specified column. If the record is null, it will not be counted. PS: use * to calculate all non null columns
- Sum: calculate the numerical sum of the specified column. If it is not a data type, the calculation result is 0
- Max: calculates the maximum value of the specified column
- Min: calculates the minimum value of the specified column
- AVG: calculate the average value of the specified column. If it is not a numeric type, the calculation result is 0
```
select count(<col_name>) from <table_name>;
```
Grouping query, PS: generally, two names are the same
```
select count(*),<name> from <table_name> group by <name>;
```
Filter after grouping query
The difference between having and where
- Having is to filter the data after grouping, and where is to filter the data before grouping
- The where function can not be used later
```
select count(*),<name> from <table_name> group by <name> having count(*) > 2;
```
Restrictions:limit offset length
- Offset: offset, which can be considered as the number of skips. The default is 0
- Length: several pieces of data need to be displayed.
```
select * from <table_name> limit 3,6;
```

Order:

Select * | field name [as alias] from table name [where clause] [group by clause] [having clause] [order by clause] [limit clause];

Database Constraints

Ensure the correctness, effectiveness and integrity of data.

Constraint type:

Primary key: primary key
Unique: unique
Not null: not null
Default: default
Foreign key: foreign key

Primary key

Function: used to uniquely identify a record. Each table should have a primary key, and each table can only have one primary key.

By not using the business field as the primary key, an ID field is designed for each table separately, and the ID is used as the primary key. Primary keys are for databases and programmers, not for end customers. It doesn’t matter whether the primary key has meaning or not, as long as it doesn’t duplicate and is not empty.

create table <table_name> (
   id int primary key,
   name varchar(20)
);

Delete primary key

alter table <table_name> drop primary key;

Primary key auto increment

create table <table_name> (
    id int primary key auto_increment,
    name varchar(20)
);

--Equivalent to

create table <table_name> (
    id int auto_increment,
    name varchar(20),
    primary key(name) USING BTREE
);

Modify default value of primary key auto increment

alter table <table_name> auto_increment = 100;

Unique constraint

You cannot insert the same name, but you can insert twonull

Index category:

unique index: unique index
normal index: general index

Index method:

BTREE
HASH

create table <table_name> (
   id int,
   name varchar(20) unique index
);

--Equivalent to

create table <table_name> (
    id int,
    name varchar(20),
    UNIQUE INDEX name(name) USING BTREE
);

Non NULL constraint

create table <table_name> (
   id int,
   name varchar(20) not null,
   gender char(2)
);

Default settings

create table <table_name> (
   id int,
   name varchar(20),
   Location varchar (50) default "shooter"
);

More references:https://github.com/astak16/bl…