MySQL Basic Syntax

SQL Concepts

What is SQL: Structured Query Language Structured Query Language

SQL function: We can easily operate the database, table and data in the database through the SQL statement. SQL is a specification that database management systems all need to follow. Different database manufacturers support SQL statements, but all have unique content.

Classification of SQL language:

• DDL statements operate database and table create, drop, alter, etc.
• DML statements insert, delete, update table data
• DQL statements perform various dimension select queries on table data
• DCL Data Control Language, used to define database access rights and security levels, and create users. Keywords grant, revoke, etc.

The constraints of the MySQL database ensure the correctness, validity and integrity of the data, including: primary key constraints, unique constraints, not-null constraints

DDL syntax

Operating the database

1. Show all databases:

   show databases;

2. Show database:

   show database <database_name>;

3. Display the created database information:

   show create database <database_name>;

4. Create the database:

   create database <database_name>;

5. Determine whether the database exists, and create the database:

   create database if not exists <database_name>;

6. Create the database and specify the character set:

   create database <database_name> character set <utf8>;

7. Use data:

   use <database_name>;

8. Check out using the database:

   select database();

9. Modify the database character set:

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

10. Delete the database:

    drop database <database_name>;

Operating Data Sheet

1. View all tables:

   show tables;

2. Create the table:

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

3. View the table structure:

   desc <table_name>;

4. View the table creation statement:

   show create table <table_name>;

5. Create a table with the same table structure:

   create table <new_table_name> like <old_table_name>;

6. Drop table:

   drop table <table_name>;

7. Determine the existence of the table and delete the table:

   drop table if exists <table_name>;

8. Add table column:

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

9. Modify the table column type:

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

10. Modify column names:

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

11. Remove columns:

    alter table <table_name> drop <col_name>;

12. Modify the table name:

    rename table <old_table_name> to <new_table_name>;

13. Modify the table character set:

    alter table <table_name> character set <utf8>;

DML statement

1. Insert all data:

   • Values and fields must correspond to 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 be quoted (single quotes)

   • If you want to insert a null value, you can leave the field blank, or insert null

     insert into <table_name>(name1, name2, ...) values(vaule1, value2, ...);
     -- 等价于
     insert into values(vaule1, value2, ...);

2. Worm replication:

   • If you only want to copy the user_name, age field data in the student table to the student2 table, use the following format

     insert into student2(user_name, age) select user_name, age from student;

     insert into student2() select * from student;

3. update table record

   • Modify data without conditions:

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

   • Modify data with conditions:

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

4. delete table record

   • Delete data without conditions:

   delete from <table_name>;

   • Conditionally delete data:

   delete from <table_name> where <name>=<value>;

   • delete data

     • delete is to delete the data in the table one by one
     • truncate is to destroy the entire table and recreate a new table, the new table structure is exactly the same as the original table structure
     • The primary key is incremented automatically, delete auto_increment is not reset, and truncate auto_increment is reset to 1

   truncate table <table_name>;

DQL statement

A query doesn't modify the data in the database, it's just a way to display the data.

1. Query value:

   select * from student;

2. Alias query: ps: as can be omitted or not written.

   select <old_col_name> as <new_col_name> from student;

3. Query name, age results do not have duplicate names:

   select distinct name, age from student;

4. The query result participates in the operation: ps: The part involved in the operation must be a numeric type.

   select <col_name> + 固定值 from <table_name>;
   select <col1_name> + <col2_name> from <table_name>;

5. Query data with id 1, 3, 5:

   select * from <table_name> where id = 1 or id = 3 or id = 5;
   
   -- 等于
   
   select * from <table_name> where id in (1, 3, 5);

6. Query data whose id is not 1, 3, or 5:

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

7. Query data with id between 3 and 7: (closed interval)

   select * from <table_name> where id between 3 and 7;

8. Fuzzy query:

   • %: Represents 0 or more characters (any character)

   • \_: represents a character

     select * from <table_name> where <name> like <'通配符字符串'>;

9. Sort:

   • asc: ascending order (default)

   • desc: descending order

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

10. Aggregate function

    • count: Count the number of records in the specified column, and those records that are NULL will not be counted. ps: Use * to calculate all columns that are not NULL
    • sum: Calculate the numerical sum of the specified column. If it is not a data type, the calculation result is 0
    • max: Calculate the maximum value of the specified column
    • min: Calculate 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>;

11. Group query, ps: generally two names are the same

    select count(*),<name> from <table_name> group by <name>;

    After grouping the query, after filtering

    The difference between having and where

    • having is to filter data after grouping, where is to filter data before grouping
    • Aggregate functions can be used after having, and aggregate functions cannot be used after where

    select count(*),<name> from <table_name> group by <name> having count(*) > 2;

12. Restrictions: limit offset length

    • offset: offset, which can be considered as the number of skips, default 0

    • length: Several pieces of data need to be displayed.

      select * from <table_name> limit 3,6;

order:

select *|字段名 [as 别名] from 表名 [where 子句] [group by 子句] [having 子句] [order by 子句] [limit 子句];

database constraints

Ensure the correctness, validity and integrity of the data.

Constraint type:

• primary key: primary key
• unique: unique
• not null: not empty
• default: default
• foreign key: foreign key

primary key

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

By not using the business field as the primary key, design an id field for each table separately, and use the id as the primary key. The primary key is for the database and the programmer, not the end customer. It doesn't matter whether the primary key has meaning or not, as long as it is not repeated and 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)
);

-- 等价于

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

Modify the auto-increment default value of the primary key

alter table <table_name> auto_increment = 100;

unique constraint

Cannot insert the same name, but can insert two null

Index category:

• unique index : Unique index
• normal index : normal index

Index method:

• BTREE
• HASH

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

-- 等价于

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

Not Null Constraint

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

Default setting

create table <table_name> (
   id int,
   name varchar(20),
   location varchar(50) default "射手"
);

More references: https://github.com/astak16/blog-mysql