MySQL – Basic syntax

SQL

Structured Query Language (Structured Query Language) defines rules for manipulating all relational databases

SQL General syntax

1. SQL statements can be written on single or multiple lines, ending with a semicolon
2. Whitespace and indentation can be used to enhance the readability of statements
3. The SQL statements of the MySQL database are case insensitive. You are advised to uppercase the keyword
4. Note:
   1. Single line comment: — Comment content or # comment content (MySQL only)
   2. Multi-line comments: /* Comments the content */

Classification of SQL

1. Date Definition Language (DDL) Database Definition Language: Defines database objects such as databases, tables, and columns. Key words:CREATE,DROP,ALTER 等
2. Date Manipulation Language (DML) Database operation Language: Adds, deletes, and modifies data in database tables. Key words:INSERT,DELETE,UPDATE 等
3. Date Query Language (DQL) Database Query Language: used to Query data in database tables. Key words:SELECT,WHERE 等
4. Date Control Language (DCL) database Control Language: specifies the access permission and security level of the database. Key words:CRANT,REVOKE 等

DDL

Operating database

C (Create) – Create

• Create database:CREATE DATABASE Specifies the DATABASE name.
• Create database – no more create:CREATE DATABASE IF NOT EXISTS DATABASE name;
• Create database – specify character set:CREATE DATABASE DATABASE name CHARACTER SET name;

R (Retrieve) – queries

• Query all database names:SHOW DATABASE;
• SQL > select * from character set; SQL > select * from character set;SHOW CREATE DATABASE DATABASE name;

U (Update) – Modify

• Alter database character setALTER DATABASE ALTER DATABASE name CHARACTER SET name;

D (Delete) – Delete

• Delete database:DROP DATABASE Indicates the DATABASE name
• Mysql > delete database;DROP DATABASE IF EXISTS Indicates the DATABASE name

Using a database

• Query the name of the database currently in use:SELECT DATABASE();
• Using a database:USE Database name;

The operating table

C (Create) – Create

CREAT TABLETable name (column name1The data type1And the column name2The data type2. Column name n Data type n);Copy the code

Common table types:

• int: age INT,
• double: Score DOUBLE(5,2), # up to 5 digits, save 2 decimal places 99.12
• Date: indicates the date, including year, month and day, YYYY-MM-DD
• Datetime: indicates the date, including year, month, day, hour, minute, second, YYYY-MM-DD HH: MM :ss
• Timestamp: indicates a time timestamp, including year, month, day, hour, minute, second, YYYY-MM-DD HH: MM :ss. If no value is assigned or the value is null, the current system time is automatically assigned by default
• Varchar: string,Name VARCHAR(20) # Maximum 20 character name

CREATE TABLE new name LIKE old name;

R (Retrieve) – queries

• Query all table names in a database:SHOW TABLES;
  • Query table structure:DESC table name;
• Query construction sentences:SHOW CREATE TABLE name;

U (Update) – Modify

• Alter table name:ALTER TABLE RENAME TO ALTER TABLE RENAME;
• Alter table character set;ALTER TABLE table_name CHARACTER SET CHARACTER SET name;
• Add a column:ALTER TABLE name ADD column name data type;
• Modify column names and data types:
  • Change the data type as well as the name:ALTER TABLE table_name CHANGE column name new column name new data type;
  • Modify only the data type:ALTER TABLE TABLE name MODIFY column name New data type;
• Delete the columns:ALTER TABLE table_name DROP table_name;

D (Delete) – Delete

• Delete table:DROP TABLE TABLE name;
• Delete from table where table existsDROP TABLE IF EXISTS Specifies the name of the TABLE.

DML

Add data

INSTER INTO table name (1, 2,… N) VALUES(1, 2… And the value of n); Note: 1. Column names and values should correspond one by one. INSERT INTO VALUES(value 1, value 2… And the value of n)). 3. Use quotation marks (single or double) for all types except numeric ones

Delete the data

DELETE FROM table name [WHERE condition] If no condition is added (DELETE FORM name;) Delete all records from the table. If you do not add WHERE, all data in the entire table will be deleted (n deletes will be performed). 2. To delete all data in a TABLE, use TRUNCATE TABLE name. Drop the table and create an empty table with the same name.

Modify the data

UPDATE table_name SET table_name 1= 1, table_name 2= 2,… [] the WHERE condition; UPDATE table_name SET table_name 1= 1, table_name 2= 2… , all records in the table are modified.

DQL

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 field queries
   1. SELECT * from (SELECT * from (SELECT * from (SELECT * from (SELECT * from (SELECT * from (SELECT * from)))) FROM the name of the table;
   2. Note: If all fields are queried, you can use * instead of the list of fields
2. Remove duplicate
   • DISTINCT
     • eg: SELECT DISTINCT address FROM student;
3. Computed columns
   1. In general, you can use four operations to compute the values of some columns. (Generally only numerical calculations are performed)
      • eg: SELECT name,math,english,math + english FROM student;SQL > select * from test1 where test1 = test2 where test1 = test1
   2. Ifnull (expression 1, expression 2) : the calculation that null participates in is null
      1. Expression 1: Which field needs to be checked for null
      2. Expression 2: The new value to replace if field 1 is null
      3. eg: SELECT name,math,english,math + IFNULL(english, 0) FROM student;
4. names
   1. As: As can be omitted
   2. eg: SELECT name,math AS math, English AS English,math + IFNULL(English, 0) FROM student;

Conditions of the query

1. WHEREREClause followed by condition
2. The operator
   1. >、 <、 <=、 >=、 =、 <>
   2. BETWEEN... AND
   3. Student: IN
   4. LIKE
   5. IS NULL
   6. AND or &&
   7. The OR OR | |
   8. Or NOT!
   9. LIKE
      1. A placeholder
         1. _: a single arbitrary character
         2. %: multiple arbitrary characters

eg:

Query age > 20
SELECT * FROM student WHERE age > 20;

-- Query age greater than or equal to 20 years old
SELECT * FROM student WHERE age > = 20;
	
Query age = 20
SELECT * FROM student WHERE age = 20;
	
-- Query age not equal to 20
SELECT * FROM student WHERE age ! = 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 age 22, age 18, age 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 values cannot be used with = (! =) judgment
SELECT * FROM student WHERE english IS NULL;
	
-- Query English score not null
SELECT * FROM student WHERE english  IS NOT NULL;

Select * from horse; like
SELECT * FROM student WHERE NAME LIKE 'ma %';

Select * from person where the second word is personified
SELECT * FROM student WHERE NAME LIKE "_化%";
	
Select * from person whose name is 3 characters
SELECT * FROM student WHERE NAME LIKE '_';
				
-- Query for people whose names contain des
SELECT * FROM student WHERE NAME LIKE '% % DE';
Copy the code

Sorting query

• Grammar:The ORDER BY clause
  • ORDER BY 排序字段1 排序方式1, 排序字段2 排序方式2...;
• Sorting methods:
  • ASC: Ascending (default)
  • DESC: descending
• Note:
  • If there are multiple sorting conditions, the second condition will be judged only when the value of the front condition is consistent.

Aggregation function

A column of data as a whole is computed vertically. Note: Aggregate functions exclude null values by default

• COUNT: Count the number
  • You typically choose a non-empty column: the primary key
  • COUNT(*)
• MAXThe maximum value of:
• MIN: the minimum
• SUMAnd:
• AVGAverage:

Grouping query

• Grammar:GROUP BY;
• Note:
  • The query fields after grouping can only be grouped fields and aggregate functions
  • WHERE 和 HAVINGThe difference between?
    • WHEREQualify before grouping. If the conditions are not met, you will not participate in grouping.HAVINGAfter grouping, if the result is not satisfied, it will not be queried.
    • WHEREYou can’t follow the aggregate function,HAVINGAggregation function can be judged.

eg:

-- By gender. Query the average scores of male and female students respectively
SELECT sex , AVG(math) FROM student GROUP BY sex;
	
-- By gender. Query the average score and number of male and female students respectively
SELECT sex , AVG(math),COUNT(id) FROM student GROUP BY sex;
	
-- By gender. Query the average score of male and female students respectively, the number of requirements: the score is less than 70, not to participate in the group
SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex;
	
-- By gender. Query the average score of male and female students respectively, the number of requirements: scores below 70, not to participate in the group, after the group. There are more than two people
SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;

SELECT sex , AVG(math),COUNTThe number (id)FROM student WHERE math > 70 GROUP BY sex HAVINGThe number of> 2;
Copy the code

Paging query

• Grammar:LIMITStart index, number of queries per page;
• Formula: Start index = (current page number -1) * number of pages to display per page
• LIMITIs a mysql-specific keyword

DCL

Manage users and authorize them.

Manage users

• Adding a user:CREATE USER 'username '@' hostname' INDENTIFIED' BY 'password ';
• Delete a user:DROP USER 'username '@' username'
• Changing a user password:UPDATE USER SET PASSWORD = PASSWORD(' new PASSWORD ') WHERE USER = 'username '; 或 SET PASSWORD FOR 'username '@' username' = PASSWORD(' new PASSWORD ');

Rights management

• Query permission:SHOW GRANTS on 'userid '@' host name ';
  • SHOW GRANTS FOR 'lisi'@'%';
• Grant permissions:GRANT permission list ON database name. Alter table name TO 'username '@' hostname ';
  • GRANT ALL ON *.* TO 'zhangsan'@'localhost'; Grant root permission to John
• Revoking permission:REVOKE Permission list ON database name. Select * FROM 'username '@' hostname ';
  • REVOKE UPDATE ON db3.accountFROM 'lisi'@'%'; Alter TABLE db3 alter table DB3 alter table DB3 alter table DB3 alter table DB3 alter table DB3 alter table DB3

eg:

-- Displays 3 records per page
SELECT * FROM student LIMIT 0.3; - page 1
SELECT * FROM student LIMIT 3.3; - page 2
SELECT * FROM student LIMIT 6.3; - page 3
Copy the code

The constraint

• Concept: To qualify data in a table to ensure correctness, validity, and integrity of the data.
• Classification:
  • Primary key:PRIMARY KEY
  • Is not empty:NOT NULL
  • The only:UNIQUE
  • Foreign keys:FOREIGN KEY

Is not empty:NOT NULLThe value of a column cannot be null

• Add a non-null constraint when creating a table

  CREAT TABLE student (
      id INT,
      NAME VARCHAR(20) NOT NULL -- Name is not empty
  );
  Copy the code

• ALTER TABLE student MODIFY NAME VARCHAR(20) NOT NULL; ALTER TABLE student MODIFY NAME VARCHAR(20) NOT NULL;

• ALTER TABLE student MODIFY NAME VARCHAR(20); ALTER TABLE student MODIFY NAME VARCHAR(20);

Unique constraint:UNIQUEThe values of a column cannot be repeated

• Note: Unique constraints can have NULL values, but only one record can be NULL

• Add unique constraints when creating tables

  CREAT TABLE student (
  id INT,
  phoneNumber VARCHAR(20) UNIQUE -- The mobile phone number cannot be repeated
  );
  Copy the code

• ALTER TABLE student MODIFY phoneNumber VARCHAR(20) UNIQUE;

• ALTER TABLE student DROP INDEX phoneNumber;

Primary key constraint:PRIMARY KEY

• Note:

  • Meaning: Not empty and unique
  • A table can have only one field primary key
  • The primary key is the unique identification of the records in the table

• When you create a table, add a primary key constraint

  CREAT TABLE student(
      id INT PRIMARY KEY, Add primary key constraint to id
      name VARCHAR(20));Copy the code

• ALTER TABLE student DROP PRIMARY KEY;

• ALTER TABLE student MODIFY ID INT PRIMARY KEY;

• Autoincrement: If a column is of numeric type, use AUTO_INCREMENT to increment the value automatically.

  • When the table is created, the primary key constraint is added and primary key auto-growth is completed

    CREATE TABLE student (
        id INT PRIMARY KEY AUTO_INCREMENT, Add primary key constraint to id
        name VARCHAR(20));Copy the code

  • ALTER TABLE student MODIFY id INT ALTER TABLE student MODIFY id INT

  • Add automatic growth

  ALTER TABLE student MODIFY id INT AUTO_INCREMENT

Foreign key constraints:FOREIGN KEY

• When you create a table, you can add foreign keys

  • Note: A foreign key can be null, but not a nonexistent value

  • Grammar:

    CREATE TABLEThe name of the table (... A foreign key columnCONSTRAINTName of the foreign keyFOREIGNKEY (foreign KEY column name)REFERENCESPrimary table name (primary table column name);Copy the code

• Remove the foreign key

ALTER TABLE TABLE name DROP FOREIGN KEY name;

• After the table is created, add the foreign key

ALTER TABLE name ADD CONSTRAINT FOREIGN KEY name FOREIGN KEY name REFERENCES primary TABLE name;

• Cascade operation
  1. Add a cascading operation
     1. Classification:
        1. Cascading UPDATE: ON UPDATE CASCADE
        2. CASCADE deletion: ON DELETE CASCADE
     2. Grammar:

     ALTER TABLEThe name of the tableADD CONSTRAINTName of the foreign keyFOREIGNKEY (foreign KEY field name)REFERENCESPrimary table name (primary table column name)ON UPDATE CASCADE / ON DELETE CASCADE;
     Copy the code

Database design

Relationships between multiple tables

• One to one: person and id card
• One to many: eg: Department and staff
  • Implementation: create a foreign key on the “many” side, pointing to the “one” side of the primary key
• Many-to-many: students and courses
  • Implementation: With the third intermediate table. The intermediate table contains at least two fields that serve as the foreign key of the third table and point to the primary key of each table

case

Create table tab_category
-- CID Primary key for travel route classification, automatic growth
-- cname Tourist route category name is a non-empty and unique character string of 100
CREATE TABLE tab_category (
    cid INT PRIMARY KEY AUTO_INCREMENT,
    cname VARCHAR(100) NOT NULL UNIQUE
);
	
Create table tab_route
100 price price rdate date date cid foreign key, category */
CREATE TABLE tab_route(
    rid INT PRIMARY KEY AUTO_INCREMENT,
    rname VARCHAR(100) NOT NULL UNIQUE,
    price DOUBLE,
    rdate DATE,
    cid INT.FOREIGN KEY (cid) REFERENCES tab_category(cid)
);
	
/* create a user table tab_user uid primary key (uid) username length 100 (unique) password length 30 (non-empty) name Name length 100 birthday birthday sex Sex The value is a fixed-length character string. 1 telephone Mobile phone number the value is a string of 11 Email email address. The value is a string of 100 */
CREATE TABLE tab_user (
    uid INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(100) UNIQUE NOT NULL,
    PASSWORD VARCHAR(30) NOT NULL,
    NAME VARCHAR(100),
    birthday DATE,
    sex CHAR(1) DEFAULT 'male',
    telephone VARCHAR(11),
    email VARCHAR(100));/* Create favorite table tab_favorite RID travel route ID, foreign key date Favorite time uid User ID, foreign key RID and uid cannot be the same, set compound primary key, same user cannot favorites the same route two times */
CREATE TABLE tab_favorite (
    rid INT.- the line id
    DATE DATETIME,
    uid INT.- user id
    Create a compound primary key
    PRIMARY KEY(rid,uid), -- Federated primary key
    FOREIGN KEY (rid) REFERENCES tab_route(rid),
    FOREIGN KEY(uid) REFERENCES tab_user(uid)
);

Copy the code

The paradigm of database design

• Concept: Some specifications to follow when designing a database. To comply with the following paradigm requirements, you must first comply with all the previous paradigm requirements

  When designing relational database, follow different standard requirements and design reasonable relational database. These different standard requirements are called different paradigms. Various paradigms present sub-norms, and the higher the paradigms, the less redundancy of database. At present, there are six paradigms for relational databases: first paradigms (1NF), second paradigms (2NF), third paradigms (3NF), Bas-Coad paradigms (BCNF), fourth paradigms (4NF) and fifth paradigms (5NF, also known as perfect paradigms).Copy the code

• Classification:

  • First Normal Form (1NF) : Each column is an indivisible atomic data item
  • Second normal Form (2NF) : On the basis of the first normal form, non-code attributes must depend entirely on the code. (On the basis of 1NF eliminate the partial function dependence of the main attribute on the code)
    • Function dependence: A->B, if the value of the unique B attribute can be determined by the value of the A attribute (attribute group), then B is said to be dependent on A. Eg: Student id -> name, (student ID, course name) -> mark
      • Full function dependence: A->B, if A is an attribute group, then the value of the B attribute needs to depend on the values of all elements in the A attribute group. Eg :(student number, course name) -> mark
      • Partial function dependence: A->B, if A is an attribute group, then B attribute values need only depend on some values in A attribute group. Eg :(student id, course name) -> name
      • Transfer function dependence: A->B, B->C, if the value of A attribute (attribute group) can determine the value of the unique B attribute, and then the value of B attribute (attribute group) can determine the value of the unique C attribute, then C transfer depends on A.
    • Code: If an attribute (attribute group) in a table is completely dependent on all other attributes, that attribute (attribute group) is called the code of that table. (Student ID, course name)
      • Master attribute: All attributes in a code attribute (group)
      • Non-primary attributes: Attributes other than code attributes (groups)
  • Third normal Form (3NF) : On the basis of 2NF, any non-primary attribute is not dependent on other non-primary attributes. (Eliminate transitive dependencies based on 2NF)

Database backup and restoration

• The command line:
  • Backup syntax:Mysqldump -u username -p password Database name > Save location
  • Restore mode:
    • Logging In to the Database
    • Creating a database
    • Using a database
    • Execute file (previously saved file path)

Multi-table query

• Cartesian product: There are two sets A, B, take all the composition of the two sets
• To complete a multi-table query, you need to eliminate unwanted data
• Grammar:

SELECTThe column name listFROMThe table listWHEREconditionsCopy the code

Practice:

Create a department tableCREATE TABLE dept(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20));INSERT INTO dept (NAME) VALUES ('Development Department'), ('Marketing Department'), ('Finance Department'); Create employee tableCREATE TABLE emp (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10),
	gender CHAR(1), - gender
	salary DOUBLE.Wages,
	join_date DATE.-- Date of entry
	dept_id INT.FOREIGN KEY (dept_id) REFERENCES dept(id) -- Foreign key, associated department table (primary key of department table)
);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES(Sun Wukong.'male'.7200.'2013-02-24'.1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('Pig Eight Quit'.'male'.3600.'2010-12-02'.2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES("Tang's monk.'male'.9000.'2008-08-08'.2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('Ghost of Bones'.'woman'.5000.'2015-10-07'.3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES(Spider spirit.'woman'.4500.'2011-03-14'.1);
Copy the code

Classification of multi-table queries

Inner join query

1. Query data from which tables 2. What are the conditions 3Copy the code

Implicit inner join

- Query information about all employees and corresponding departments
SELECT * FROM emp,dept WHERE emp.dept_id = dept.id;
    
Select * from employee table where name, gender The name of the department table
Select * from employee table where name, gender The name of the department table
SELECT
	t1.`NAME`,
	t1.gender,
	t2.`NAME`
FROM
	emp t1,		- the employee table
	dept t2		- department of table
WHERE
	t1.dept_id = t2.id
Copy the code

Explicit inner join

• Grammar:SELECT * FROM 表名1 [INNER] JOIN 表名2 ON;
  • For example,SELECT * FROM emp JOIN dept ON emp.dept_id = dept.id;

External join query

The left outer join

• Grammar:SELECT * FROM 表1 LEFT [OUTER] JOIN 表2 ON;
• SQL > select * from left table where all data has intersection with existing table
• Example:

If the employee has a department, the department name is displayed. If the employee has no department, the department name is not displayed
SELECT
	t1.*,
	t2.`NAME`
FROM
	emp t1
LEFT JOIN
	dept t2
ON
	t1.dept_id = t2.id
Copy the code

The subquery

• Concept: nested query in a query, into a nested query as a sub-query.
• Example:

-- Query information about the highest paid employee
SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp);

Select 'finance' and 'marketing' from all employees
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = 'Finance Department' OR NAME = 'Marketing Department');

Query employee information and department information after 2011-11-11
SELECT 
    * 
FROM 
    dept t1 ,
    (SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2
WHERE 
    t1.id = t2.dept_id;
Copy the code

The transaction

• Concept: If a multi-step business operation is transaction-managed, the operations will either succeed or fail at the same time.
• Operation:
  • Start transaction:START TRANSACTION;
  • Roll back:ROLLBACK
  • Commit:COMMIT
• Transactions are committed automatically by default in MySQL. (Running a DML statement automatically commits a transaction)
• The Oracle database manually commits a transaction by default. You must enable the transaction before committing the transaction.

Four characteristics of transactions:

1. Atomicity: The smallest indivisible unit of operation that either succeeds or fails at the same time.
2. Persistence: The database persists data after a transaction is committed or rolled back.
3. Isolation: Between multiple transactions. Independent of each other.
4. Consistency: The total amount of data remains the same before and after a transaction