Database Notes (i)--Database basics

First, the grammar requirements

1. SQL statements can be written single-line or multiline, ending with a semicolon.
2. You can use spaces and indents to enhance the readability of your statement.
3. Keywords are not case-sensitive.

Ii. Classification of sentences

1. DDL (data definition Language): A definition language used to define database objects: libraries, tables, columns, indexes, and so on. The commonly used statement keywords include create, drop, alter and so on.
2. DML (Data Manipulation Language): An operational language that defines database records (data). The commonly used statement keywords include insert, delete, udpate and so on.
3. DCL (Data Control Language): An information-controlled language used to define access rights and security levels. The main statement keywords include GRANT, revoke, and so on.
4. DQL (data query Language): The Language for Querying records (data). The main statement keywords include select and so on.

Third, DDL statements

1. Basic Library operation

1. CREATE DATABASE [IF not EXISTS] Db_liu; Create a new database Db_liu,
2. SHOW DATABASES; View all databases
3. Use Db_liu; Switch to Database Db_liu
4. ALTER DATABASE db_liu CHARACTER SET UTF8; Modify Database Encoding
5. DROP DATABASE [IF EXISTS] Db_liu; Deleting a database

2. Basic table Operation

1. CREATE TABLE table name (Field Name field type, field Name field type ...); Create a new table (not NULL, primary key, Auto_increment)
   Example: CREATE TABLE User (id int,username varchar (), password varchar (20));
2. SHOW TABLES; View all Tables
3. DESC table name; View table Structure
4. SHOW CREATE table name \g; View Comprehensive Table definition information
5. Modify Table
   1. ALTER table name MODIFY [column] column definition [First | After column name]; modifying column types
      Example: ALTER TABLE user modify username varchar (18)
      After Password;first is to change the column to the first column, after password is changed to the back of the password column
   2. ALTER table name ADD [column] column definition [First | After column name]; Adding columns
      Example: ALTER TABLE user add age int;
   3. ALTER table name DROP [column] column name; Delete Column
   4. ALTER table name change [column] old column name new column definition; Modify column names
      Example: ALTER TABLE user change username name varchar (20);
   5. ALTER table name RENAME [to] new table name; Modify Table Name
   6. drop table name; Delete a table
      Note: The change, first, and after columns are extensions of MySQL, and other databases may not have

3. DML statements

1. Inserting data
   INSERT into table name (column name 1, column Name 2, ...) Values (value 1, value 2,...);
   INSERT into table name values (value 1, value 2,...)
2. Update data
   UPDATE table name SET field1=value1,field2=value2,... [Conditional judgment];
   Example: Update user set username= "ABCD" where id=1;
3. Delete record delete from table name [conditional judgment];

4. DQL statement

2. Basic use
   Select Name,age from user where id=1; (Use the * representative to query all records)
4. Use distinct to remove duplicate records
   Select distinct name from user;
6. Conditional query
   Use the and (and), or (or), >, <, >=, <=,! =, =, and so on operators
8. Sort
   SELECT * from user where age>12 order by ID ASC; (desc is descending, default ascending)
10. Paging Query
    Limit start line, query number of rows; (start line starting from 0)
    SELECT * from user limit 0, 5;
12. Aggregate function (data that excludes null values)
    SUM (sum), count (number of records), AVG (average), Max (max), min (min)
    Select Age,count (*) from the user where Id>5 group by age have count (*) >=2; (after having the statement is to filter the aggregated results)
14. Table Connection
    Select Student.name,course.name from Student,course where Student.sno=course.sno;
    Outer joins: Left OUTER join (contains all records on the left table regardless of the right table has no matching record), right outer join (contains all records of the right table regardless of the left table has no matching record)
    Select Student.name,course.age from student left join course on Student.sno=course.sno;
16. Sub-query
    The condition that the query needs is the result of another SELECT statement, keyword in, not in, =,! =, exists, not exists, and so on
    SELECT * from student where ID in (select Sno from course where name= ' math ');
18. Record Union
    Union (remove duplicates), union ALL (do not remove weight)
    Select ID from student Union select Sno from Course;
20. Fuzzy query
    % replaces any character, _ instead of one character
    SELECT * from user where name is like "Zhang _";

Database Notes (i)--Database basics