SQL syntax: DDL, DQL, DCL

Time:2021-8-8

SQL syntax:

General syntax:

  1. SQL statements can be written in one or more lines, ending with semicolons.
  2. Spaces and indents can be used to enhance the readability of statements.
  3. The SQL statements of MySQL database are not case sensitive.
  4. Three notes

    • Single line comment: – comment content or # comment content (unique to MySQL)
    • Multiline comment:/notes/

SQL classification

  1. DDL (data definition language) data definition language is used to define database objects: database, table, column, etc. Keywords: create, drop, alter, etc
  2. DML (data manipulation language)

It is used to add, delete and modify the data of tables in the database. Keywords: insert, delete, update, etc

  1. DQL (data query language)

Used to query the records (data) of tables in the database. Keywords: select, where, etc

  1. DCL (data control language)

It is used to define the access rights and security levels of the database and create users. Keywords: Grant, revoke, etc

DDL: operation database and table

1. Operation database: CRUD
    1. C (create): create
        *Create database:
            *Create database database name;
        *Create a database, judge that it does not exist, and then create:
            *Create database if not exists;
        *Create a database and specify a character set
            *Create database database name character set character set name;

        *Exercise: create a DB4 database, determine whether it exists, and set the character set to GBK
            * create database if not exists db4 character set gbk;
    2. R (retrieve): query
        *Query the names of all databases:
            * show databases;
        *Query the character set of a database: query the creation statement of a database
            *Show create database database name;
    3. U (update): modify
        *Modify the character set of the database
            *Alter database database name character set character set name;
    4. D (delete): delete
        *Delete database
            *Drop database database name;
        *Judge whether the database exists, and then delete it
            *Drop database if exists database name;
    5. Use database
        *Query the name of the database currently in use
            * select database();
        *Use database
            *Use database name;


2. Operation table
    1. C (create): create
        1. Syntax:
            Create table table name(
                Column name 1, data type 1,
                Column name 2, data type 2,
                ....
                Column name n data type n
            );
            *Note: comma (,) is not required for the last column
            *Database type:
                1. Int: integer type
                    * age int,
                2. Double: decimal type
                    * score double(5,2)
                3. Date: date, only including yyyy mm DD
                4. Datetime: date, including yyyy MM DD HH: mm: SS
                5. Timestamp: time error type, including yyyy MM DD HH: mm: SS    
                    *If this field is not assigned in the future, or the assignment is null, the current system time will be used by default to automatically assign the value

                6. Varchar: String
                    *Name varchar (20): the maximum length of the name is 20 characters
                    *Zhangsan has eight characters and two characters in three
            

        *Create table
            create table student(
                id int,
                name varchar(32),
                age int ,
                score double(4,1),
                birthday date,
                insert_time timestamp
            );
        *Copy table:
            *Create table name like the name of the copied table;          
    2. R (retrieve): query
        *Query all table names in a database
            * show tables;
        *Query table structure
            *Desc table name;
    3. U (update): modify
        1. Modify table name
            Alter table name rename to new table name;
        2. Modify the character set of the table
            Alter table name character set character set name;
        3. Add a column
            Alter table name add column name data type;
        4. Modify column name type
            Alter table name change column name new column type new data type;
            Alter table name modify column name new data type;
        5. Delete column
            Alter table name DROP column name;
    4. D (delete): delete
        *Drop table name;
        *Drop table if exists table name;

DML: add, delete and modify data in the table

1. Add data:
    *Syntax:
        *Insert into table name (column name 1, column name 2,... Column name n) values (value 1, value 2,... Value n);
    *Note:
        1. The column name and value should correspond one by one.
        2. If the column name is not defined after the table name, values will be added to all columns by default
            Insert into table name values (value 1, value 2,... Value n);
        3. In addition to the number type, other types need to be enclosed in quotation marks (either single or double)
2. Delete data:
    *Syntax:
        *Delete from table name [where condition]
    *Note:
        1. If no conditions are added, all records in the table will be deleted.
        2. If you want to delete all records
            1. Delete from table name-- Not recommended. How many records will be deleted
            2. Truncate table name-- Recommended. It is more efficient. First delete the table, and then create the same table.
3. Modify data:
    *Syntax:
        *Update table name set column name 1 = value 1, column name 2 = value 2,... [where condition];

    *Note:
        1. If no conditions are added, all records in the table will be modified.

DQL: querying records in a table

Select * from table name;

1. Syntax:
    select
        Field list
    from
        Table name list
    where
        Condition list
    group by
        Grouping field
    having
        Conditions after grouping
    order by
        sort
    limit
        Paging limit


2. Basic query
    1. Query of multiple fields
        Select field name 1, field name 2... From table name;
        *Note:
            * 如果查询所有字段,则可以使用*来替代Field list。
    2. Remove duplicates:
        * distinct
    3. Calculation column
        *Generally, four operations can be used to calculate the values of some columns( Generally, only numerical calculation will be carried out)
        *Ifnull (expression 1, expression 2): the calculation results of the operations involved in null are null
            *Expression 1: which field needs to be judged whether it is null
            *The replacement value if the field is null.
    4. Alias:
        *As: as can also be omitted
        

3. Condition query
    1. Where clause followed by condition
    2. Operator
        * > 、< 、<= 、>= 、= 、<>
        * BETWEEN...AND  
        *In (set) 
        *Like: fuzzy query
            *Placeholder:
                * _: Single arbitrary character
                *%: multiple arbitrary characters
        * IS NULL  
        *And or&&
        *Or or|| 
        *Not or!
        
            --Query age greater than 20 years old

            SELECT * FROM student WHERE age > 20;
            
            SELECT * FROM student WHERE age >= 20;
            
            --The query age is equal to 20 years old
            SELECT * FROM student WHERE age = 20;
            
            --Query age is not equal to 20 years old
            SELECT * FROM student WHERE age != 20;
            SELECT * FROM student WHERE age <> 20;
            
            --Query age is greater than or equal to 20 and less than or equal to 30
            
            SELECT * FROM student WHERE age >= 20 &&  age <=30;
            SELECT * FROM student WHERE age >= 20 AND  age <=30;
            SELECT * FROM student WHERE age BETWEEN 20 AND 30;
            
            --Query the information of age 22, 18 and 25
            SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25
            SELECT * FROM student WHERE age IN (22,18,25);
            
            --Query English score is null
            SELECT * FROM student WHERE english = NULL; --  No. Null value cannot use = (! =) judge
            
            SELECT * FROM student WHERE english IS NULL;
            
            --Query English score is not null
            SELECT * FROM student WHERE english  IS NOT NULL;



            --What are the names of horses? like
            Select * from student where name like 'horse%';
            --The second word of the query name is the humanized person
            
            SELECT * FROM student WHERE NAME LIKE "_ % ";
            
            --A person whose name is three words
            SELECT * FROM student WHERE NAME LIKE '___';
            
            
            --Query the person whose name contains De
            Select * from student where name like '% de%';

Recommended Today

Supervisor

Supervisor [note] Supervisor – H view supervisor command help Supervisorctl – H view supervisorctl command help Supervisorctl help view the action command of supervisorctl Supervisorctl help any action to view the use of this action 1. Introduction Supervisor is a process control system. Generally speaking, it can monitor your process. If the process exits abnormally, […]