DDL operates databases and tables

1. Operation database: CRUD

   1. C: (Create) to Create

      • Create database:
        • Create database Specifies the database name.
      • Create a database and check whether it does not exist.
        • Create database if not exists Database name;

      • Create the database and specify the character set

        • Create database Database name character set name;

      • Exercise: create db4 database, determine whether exists, and specify character set GBK

        • create database if not exists db4 character set gbk;
   2. R (Retrieve) : query
      • Query all database names:
        • show databases;
      • Query the character set of a database: Query the creation statement of a database
        • Show create database Database name;
   3. U (Update) : change
      • Modify the character set of the database
        • Alter DATABASE alter database name character set name;
   4. D (Delete) : Delete
      • Deleting a Database
        • Drop database Specifies the database name.
      • Check whether the database exists and then delete it
        • Drop database if EXISTS Indicates the database name.
   5. Using a database
      • Query the name of the database currently in use
        • select database();
      • Using a database
        • Use Database name;

2. The operating table

   1. C: (Create) to 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: The last column does not need a comma (,).

         • Database type:

           1. Int: indicates an integer
              • age int,
           2. Double: indicates the decimal type
              • Score double (5, 2)
           3. Date: indicates the date, including year, month and day, YYYY-MM-DD
           4. Datetime: indicates the date, including year month day hour minute second YYYY-MM-DD HH: MM :ss

           5. Timestamp: Time error type Includes year month day minute second YYYY-MM-DD HH: MM :ss

              • If this field is not assigned in the future, or is assigned to null, the current system time is used by default for automatic assignment

           6. Varchar: string

              • Name vARCHar (20): The name contains a maximum of 20 characters
              • A zhangsan contains 8 characters. A zhangsan contains 2 characters

      2. Create table student(id int, name varchar(32), age int, score double(4,1), birthday date, insert_time timestamp);

      3. The replication table:
         • Create table name like the name of the table to be copied;
   2. R (Retrieve) : query
      • Query all table names in a database
        • show tables;
      • Query the table structure
        • Desc table name;
   3. U (Update) : change
      1. Alter table rename to rename a table;
      2. Alter table table name character set character set name;
      3. Alter table table_name add column name datatype;
      4. Alter table table name change column name New column type new data type; Alter table table name modify column name New data type;
      5. Alter table table_name drop table_name;
   4. D (Delete) : Delete
      • Drop table table name;
      • Drop table if exists Specifies the name of the table.

DML: Add, delete, or modify data in a table

1. Add data:
   • Grammar:
     • Insert into table name (1, 2,… N) values(1, 2… The value of n);
   • Note:
     1. Column names and values should correspond one to one.
     2. Insert into values(values 1, 2… The value of n);
     3. Except for numeric types, all types need to be quoted (odd or even)
2. Delete data:
   • Grammar:
     • Delete from table name [where condition]
   • Note:
     1. If no condition is added, all records in the table are deleted.
     2. If you want to delete all records
        1. Delete from table name; — Not recommended. Delete operations are performed as many times as there are records
        2. TRUNCATE TABLE name; Drop the table and create the same table.

3. Modify data:

   • Grammar:

     • Update table_name set table_name 1 = 1, table_name 2 = 2,… [] the where condition;

   • Note:

     1. If no conditions are added, all records in the table are modified.

DQL: queries records in a table

• Select * from table_name;

• Select list of columns from table names WHERE list of conditions group by having conditions after a group ORDER by sort LIMIT Paging limit

• Based on the query

  1. Multiple fields of query 1, the select field name field name 2… From the name of the table;
     • Note:
       • If all fields are queried, you can use * instead of the list of fields.
  2. To remove repetition:
     • distinct
  3. Computed columns
     • In general, you can use four operations to compute the values of some columns. (Generally only numerical calculations are performed)
     • Ifnull (expression 1, expression 2) : the calculation that null participates in is null
       • Expression 1: Which field needs to be checked for null
       • If the field is the replacement value after null.
  4. Names:
     • As: As can also be omitted

• Conditions of the query

  1. The WHERE clause is followed by a condition

  2. The operator

     • > 、< 、<= 、>= 、= 、<>
     • BETWEEN… AND
     • IN (set)
     • LIKE: fuzzy query
       • Placeholder:
         • _: a single arbitrary character
         • % : multiple arbitrary characters
     • IS NULL
     • And or &&
     • The or or | |

     • Or not!

       Query age > 20

       SELECT * FROM student WHERE age > 20;

       SELECT * FROM student WHERE age >= 20;

       SELECT * FROM student WHERE age = 20;

       SELECT * FROM student WHERE age = 20 SELECT * FROM student WHERE age = 20 = 20; SELECT * FROM student WHERE age <> 20;

       The 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;

       Select * from user where age 22, age 18, SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25 SELECT * FROM student WHERE age = 22 OR age = 25;

       SELECT * FROM student WHERE English = null; — No. Null values cannot be used with = (! =) judgment

       SELECT * FROM student WHERE english IS NULL;

       SELECT * FROM student WHERE English IS NOT null;

       Select * from horse; Like SELECT * FROM student WHERE NAME like ‘%’; Select * from person where the second word is personified

       SELECT * FROM student WHERE NAME LIKE “_化%”;

       SELECT * FROM student WHERE NAME = ‘___’;

       SELECT * FROM student WHERE NAME LIKE ‘% de %’;