Database operations

  • CREATE DATABASE Specifies the name of the DATABASECreating a database
  • USE database nameSelect database
  • show tablesDisplay all tables
  • Drop Database Specifies the database nameDeleting a Database

Table operation

  • CREATE TABLE name (column type, column type)Create a table
  • Show create table Specifies the name of the tableDisplays table creation statements and details
  • Desc table nameShows the structure of the table
  • DROP TABLE TABLE nameDelete table
  • Truncate table table nameClear the table
  • Alter table table name add field typeAdd a table field
  • Alter table name add type after table nameAdds a table field and inserts it after the specified field location
  • Alter table name add type firstAdd the table field and insert the front
  • Alter table Table name modify New type of the fieldChanging the field Type
  • Alter table name alter table name alter table name alter table name alter table nameChange the field name and change the type
  • Alter table table_name drop column;Drop table field
  • Alter table Old table name rename New table nameModify the name of the table

Data manipulation

  • Conditions of the querywhere
SELECT * from t_doctor WHERE doctor_name = doctor_name;Copy the code

  • Order by ascending (ASC), descending (high to low)desc

  • GROUP BY GROUP

Grouping may not be easy to understand, but LET me give you a separate example:

If there is a nurse table, and the task list, the task of the nurse little sister is to follow up their responsible patients, a nurse little sister is responsible for many patients, all the nurses in the task list little sister, all the tasks performed. Now, let’s see how many missions each nurse has done

<!Select nurse_id from nurse_id
SELECT nurse_id,count(*) as tatal FROM t_visit_task GROUP BY nurse_id ORDER BY tatal desc;
Copy the code
  • with ROLLUPIndicates whether the results of classification aggregation are resummarized
<! -- In addition to knowing how many missions each nurse performed, SELECT nurse_id,count(*) as tatal FROM t_visit_task GROUP BY nurse_id WITH ROLLUP;Copy the code
  • Fuzzy querylike
<!SQL > select * from doctor_name;
SELECT * from t_doctor WHERE doctor_name LIKE "%张%";
Copy the code
  • inThe query
SELECT * FROM t_visit_task WHERE nurse_id in ("1126cf7f-d31e-43f1-a2dd-05b37873f99b","8ce40555-4b62-498b-8e39-af4abb18abfd"); <! SELECT * FROM t_visit_task where nurse_id = "1126CF7f-d31E-43F1-a2DD-05b37873f99b" OR nurse_id = "1126CF7f-d31E-43F1-a2DD-05b37873f99b "8ce40555-4b62-498b-8e39-af4abb18abfd"Copy the code

  • Insert into values(1, 2),(1, 2) insert into values(1, 2

  • Select * from table_name where table_name =’ XXX ‘and table_name =’ XXX’ where table_name =’ XXX ‘and table_name =’ XXX’ and table_name =’ XXX

  • Delete from table_name where table_name =”xx”; Delete data, must add conditions, or delete all

  • Union and union all combine the data results from multiple select queries (removing duplicate parts), and union all does not repeat

    • A Union must consist of two or more SELECT statements with Union links between them

    • Each query in a Union must contain the same columns, expressions, or aggregate functions, and they may appear in a different order.

    • Column data types must be compatible, which means that the database can implicitly convert their types

<!-- The first query 2020-10-13 after the entry of the nurse sister -->
<!-- The second query name contains Li's little sister, regardless of when she started -->
<!After execution, two SQL results may contain each other's existing data, union is repeated to merge display -->
SELECT * FROM t_nurse WHERE created_at > "2020-10-13"
UNION
SELECT * FROM t_nurse WHERE nurse_name LIKE "% %" li;
Copy the code
  • Multi-table LianZha
    • inner joinExample Query the data that meets the association conditions on multiple tables
    SELECT * FROM t_nurse INNER JOIN t_visit_task ON t_nurse.nurse_id = t_visit_task.nurse_id; <! SELECT * FROM t_nurse,t_visit_task WHERE t_nurse. Nurse_id = t_visit_task.nurse_id;Copy the code
    • left joinThe left table is the main table, and the other tables are matched with the data given the associated conditions. If there is one, it will be displayed, and if there is no one, it will be empty
    SELECT * FROM t_nurse LEFT JOIN t_visit_task ON t_nurse.nurse_id = t_visit_task.nurse_id;
Copy the code
    • right joinThe right table as the main table, other tables with the associated conditions of the data, there is a display, no empty
    SELECT * FROM t_nurse RIGHT JOIN t_visit_task ON t_nurse.nurse_id = t_visit_task.nurse_id;
Copy the code

The data type

Numeric types

Date time type

String type