Mysql Basics Learning Notes

The installation

“MacOS“

“Windows 10“

“Centos 7“

The SQL standard

1. Case insensitive, but uppercase is recommendedkeywordsAnd lower caseThe name of the table,The column name
2. Each SQL suggestion ends with a semicolon
3. Each SQL line is indented as needed
4. Note: Single line:# -Multiple lines:/ * * /

type

Value: integer: decimal: fixed point number Floating point number character: short character: char varcahr Long text: Text Blob Date: Date 2020-02-03 datetime 2020-02-02 02:02:02 timesiamp 1594279093389 time 02:02:02 Year 2020Copy the code

Commonly used SQL

use test; Select database
show tables; -- Reality all tables of the currently selected library

show tables from mysql; Mysql > alter table table

SHOW INDEX FROM stuinfo; Display the current index  select database(a);View the current library  /* create table table1(  id int,  name varchar(24) ); * /  desc table1; Look at the table structure  select * from table1;  insert into table1 (id.name) values(1.'test'); Insert -  update table1 set name='I' where name='ces'; - to modify  update table1 set id=0 where name='I'; - to modify  delete from table1 where name='I'; - delete   Copy the code

Common function

A single function

❝

To deal with

❞

Character function

SELECT LENGTH('Who am I?'); -- Gets the current byte length based on the current character set

SELECT CONCAT('我'.'is'.'who's that'); -- Concatenate string

SELECT UPPER('Abc'); -- Converts to uppercase characters
 SELECT LOWER('Abc'); -- Convert to lowercase  SELECT SUBSTR('ABC123 one two three'.4.3); Select 3 indexes with 4 starting from 1  SELECT SUBSTRING('ABC123 one two three'.4.3); Select 3 indexes with 4 starting from 1  SELECT INSTR('01234556'.'234'); -- Search for the position where the string appears  SELECT TRIM(' A B C D '); -- Remove the space before and after  SELECT TRIM('a' FROM 'aaaaA B CaaaDaaaa' ); -- before and after removing a  SELECT LPAD('ABC123 one two three'.20.The '*'); -- Left fill/keep left SELECT RPAD('ABC123 one two three'.20.The '*'); -- Fill right/keep left  Copy the code

Mathematical function

SELECT ROUND(0.4); -- Round
SELECT ROUND(0.5);  -- Round
SELECT ROUND(0.4);  -- Round
SELECT ROUND(0.5);  -- Round

SELECT CEIL(0.2); -- round up SELECT FLOOR(0.9); -- round down  SELECT RAND(a);- the random number  SELECT TRUNCATE(0.2345.3); -- How many decimal places to leave unprocessed  SELECT MOD(10.3); Take over -  Copy the code

Date function

SELECT NOW(a);-- Returns the current date and time

SELECT CURDATE(a);-- Returns the current date

SELECT CURTIME(a);-- Returns the current time
 SELECT YEAR(NOW()) as `year`.MONTH(NOW()) as `month`.DAY(NOW()) as date as `day`; -- Year/month/day  SELECT STR_TO_DATE('the 2020-03-23 22:32:12'.'%Y-%m-%d %H:%i:%s'); Parse string to time  SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s'); -- Formatting time Copy the code

Other functions

SELECT VERSION(a);-- Check the version number

SELECT DATABASE(a);View the current library
 
SELECT USER(a);-- Current user
Copy the code

Flow control function

SELECT IF(10<5.'big'.'small'); -- if

SELECT `last_name`.IF(`commission_pct` IS NULL.TRUE.FALSE) AS isPct from `employees` ORDER BY `isPct` DESC; Example - if

# case 
SELECT `salary`.`department_id`.CASE department_id  WHEN 80 THEN  salary * 1.2  WHEN 40 THEN  salary * 1.9  ELSE  salary * 0 END AS newMoney FROM `employees` ORDER BY department_id DESC;  Copy the code

Statistical function

❝

statistical

❞

SELECT COUNT(*) FROM `employees`; -- Quantity statistics
SELECT SUM(`salary`) FROM `employees`; - add and
SELECT AVG(`salary`) FROM `employees`; The average -
SELECT MAX(`salary`) FROM `employees`; A maximum -
SELECT MIN(`salary`) FROM `employees`; - the minimum
 SELECT COUNT(*) AS `count`.SUM(`salary`) AS `sum`.AVG(`salary`) AS `avg`.MAX(`salary`) as `max`.MIN(`salary`) as `min` FROM `employees`;  # note / * * / Copy the code

Common constraints

❝

A restriction that limits the data in a table to ensure accuracy and reliability of the data in the table

❞

Category: six constraints: NOT NULL: indicates that the field value cannot be NULL. DEFAULT: indicates the DEFAULT value. PRIMARY KEY: indicates the PRIMARY KEY, which indicates that the field is UNIQUE (NOT NULL). Check (mysql not supported) FOREIGN KEY: used to restrict the relationship between two tables, used to ensure that the field must be from the primary KEY of the associated table add constraint time: 1. Create table 2. Alter table constraint add category: column level constraint: all columns are supported; foreign key constraint is invalid; table level constraint: all columns are supported
The differences between a primary key and a unique key are as follows: primary key: unique, non-empty, only one unique: unique, nullable, multiple
Copy the code
Foreign key: 1. Set the foreign key relationship for the secondary table. 2. The associated key of a primary table is usually the primary key or unique key. 4. The primary table must correspond to data in the primary table

DQL Data query language

Constants, expressions, functions

SELECT 1; - constant values
SELECT 10*20; Expression -
SELECT VERSION(a);- function
Copy the code

The alias

SELECT 1+2 as number;
Copy the code

duplicate removal

SELECT DISTINCT 
    `name`
FROM 
    `table`
Copy the code

+.

SELECT 1+2; -- Add numbers
SELECT 1+'123'; -- The string is strongly converted to a number
SELECT 1 + Null; -- returns Null with Null
Copy the code

String conjunctionconcat

SELECT CONCAT('a'.'b'.'c'); -- String concatenation
SELECT CONCAT(`first_name`.`last_name`) as `name` FROM `employees`; -- Concatenation field
Copy the code

Conditions of the query

Conditional expression

❝

< > >= <=! = < > < = >

❞

# is equal to the
SELECT 
    CONCAT(`first_name`.`last_name`) as `name` 
FROM `employees` 
WHERE 
 `first_name`='Bruce';  # secure = searchable Null  SELECT  CONCAT(`first_name`.`last_name`) as `name` FROM `employees` WHERE  `first_name`< = >'Bruce';  # is greater than the SELECT  * FROM `employees` WHERE  `department_id` > 60;  # is less than SELECT  * FROM `employees` WHERE  `department_id`< =60;  # is not equal to #! = it is not recommended SELECT  * FROM `employees` WHERE  `department_id` <> 60; Copy the code

Logical expression

❝

&& || !

AND OR NOT

❞

# and the query
# Not recommended &&
SELECT 
CONCAT(`first_name`.`last_name`) as `name`
FROM `employees` 
WHERE  `first_name`='Bruce'  AND  `last_name`='Ernst';  # or SELECT  CONCAT(`first_name`.`last_name`) as `name` FROM `employees` WHERE  `first_name`='Bruce'  OR  `last_name`='K_ing';  # the SELECT  CONCAT(`first_name`.`last_name`) as `name` FROM `employees` WHERE  NOT  `first_name`='Bruce' Copy the code

Fuzzy query

likeFuzzy query

% : any number of characters _: Any single character \: Escape

# contains a
SELECT * FROM `employees` 
WHERE 
    `first_name` like '%a%'; 

# Define escape characters SELECT * FROM `employees` WHERE  `last_name` like '% $_ %' ESCAPE ' 
between andThe scope of
 
              

               ❝ 
               
Between what and what, before and after
 
               ❞
              
 
Find data between 100 and 200
SELECT * FROM `employees` 
WHERE 
    `employee_id` BETWEEN 100 AND 200;
Copy the code
inBelong to

               ❝ 
Query whether it belongs to a certain list
               ❞
              
# query whether you belong to a list
SELECT * FROM `employees` 
WHERE 
    `job_id` IN ('SH_CLERK'.'AD_ASST'.'AD_VP');
Copy the code
is null or is not nullWhether to Null
The query field is null
SELECT * FROM `employees`
WHERE
    `commission_pct` IS NULL;

# query is not null SELECT * FROM `employees` WHERE  NOT `commission_pct` IS NULL;  SELECT * FROM `employees` WHERE  `commission_pct` IS NOT NULL; Copy the code
< = >Security is equal to the

               ❝ 
You can judge both NULL and numeric values
               ❞
              
SELECT * FROM `employees`
WHERE
    `commission_pct`< = >NULL;
Copy the code
order byThe sorting
ASC ascending DESC Descending order Default ASC
SELECT * FROM `employees` ORDER BY `salary` ASC; - ascending
SELECT * FROM `employees` ORDER BY `salary` DESC; - in descending order

SELECT `salary` * IFNULL(`commission_pct`.0) + IFNULL(manager_id,0) as money,`salary`  FROM `employees` ORDER BY `money`; -- Expression aliases in descending order

SELECT LENGTH(`last_name`) as len FROM `employees` ORDER BY len; - according to the function  SELECT * FROM `employees` ORDER BY `salary` DESC.`employee_id` ASC; -- Multiple sorting conditions Copy the code
Grouping query

The total salary for each job
SELECT SUM(`salary`) AS `money`.`job_id` FROM `employees` GROUP BY `job_id` ORDER BY `money`;

# Maximum salary per job
SELECT MAX(`salary`) as `max`.`job_id` FROM `employees` GROUP BY `job_id` ORDER BY `max`;  Select * from 'a' where 'a' = 'a' SELECT MAX(`salary`) as `max`.`job_id` FROM `employees` WHERE email LIKE '%a%' GROUP BY `job_id` ORDER BY `max`;  Select * from a where the maximum salary of a job is greater than 10,000 SELECT MAX(`salary`) as `max`.`job_id` FROM `employees` WHERE email LIKE '%a%' GROUP BY `job_id` HAVING `max` > 10000 ORDER BY `max`;  Select * from (select * from (select * from (select * from (select * from (select * from))) SELECT LENGTH(`first_name`) AS `name`.COUNT(1) AS `count` FROM `employees` GROUP BY `name` HAVING `count` > 5;  # Multi-field grouping SELECT AVG(`salary`) AS `avg`.`department_id`.`job_id` FROM `employees` GROUP BY `department_id`.`job_id` ORDER BY `department_id`.`job_id` Copy the code
Join queries
Category: divided by years: SQL92 standard: only internal connection SQL99 standard [recommended] : Internal connection + external connection (left and right) + cross connection Divided by functions: Internal connection: equivalent connection Non-equivalent connection Self-connection External connection: Left and external connection right external connection Full external connection Cross connectionCopy the code
Sql92 standard
# Equivalent connection
SELECT e.`first_name`,j.`job_title`.`j`.`job_id`
FROM `employees` as e,`jobs` as j
WHERE `e`.`job_id` = `j`.`job_id`;

# non-equivalent connection  # the connection SELECT e.last_name,m.employee_id,e.manager_id,m.last_name FROM `employees` AS e, `employees` AS m WHERE `e`.employee_id = `m`.manager_id Copy the code
Sql99 standard
# Equivalent connection
SELECT `last_name`.`department_name`
FROM employees
INNER JOIN departments
ON employees.`department_id` = departments.`department_id`
 # Complex equivalent connection SELECT department_name,COUNT(The '*') AS count.MAX(`salary`) AS max.min(`salary`) AS min FROM employees INNER JOIN departments ON employees.department_id = departments.department_id WHERE last_name LIKE '%o%' GROUP BY department_name HAVING `count` BETWEEN 2 AND 10 ORDER BY `count`  # multi-table equivalent join SELECT last_name,department_name,job_title FROM employees INNER JOIN departments ON departments.department_id =employees.department_id INNER JOIN jobs ON employees.job_id = jobs.job_id;  Copy the code
The subquery
Category: by location: SELECT: only standard quantum query form table subquery WHERE and HAVING standard quantum query column subquery exists table subquery
According to the number of rows and columns of the result set: standard quantum query (the result set has only one row and one column) column subquery (the result set has only one column and one row) row subquery (the result set has only one row and one row) table subquery (the result set generally has many rows and many columns)Copy the code
Copy the code


Where and having
Features: 1. Placed in parentheses 2. Placed on the right side of the condition 3. Column subqueries: With multi-line operators.

Select * from Abel whose salary is higher than Abel's
SELECT * FROM employees 
WHERE salary > (
 SELECT salary FROM employees 
 WHERE last_name = 'Abel' );  # job_id Employee whose salary is the same as employee 141 and whose salary is greater than employee 143 SELECT last_name,salary,employee_id FROM employees WHERE job_id = (  SELECT job_id FROM employees WHERE employee_id = 141 ) AND salary > (  SELECT salary FROM employees WHERE employee_id = 143 )  Select * from department where minimum wage is higher than minimum wage in department 50 SELECT department_id, COUNT(*),MIN(salary) AS minsalary FROM employees GROUP BY department_id HAVING minsalary > (SELECT MIN(salary) AS minsalary FROM employees WHERE department_id = 50) ORDER BY minsalary DESC; Copy the code
Paging query
SELECT *
FROM `employees` 
LIMIT (page- 1) *size.size;

SELECT *
FROM `employees` LIMIT 10.10; Copy the code
The joint query
# no
Copy the code
DML data manipulation language
insert
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES('p_a'.'Pick up litter 1'.200.6000);

INSERT INTO jobs 
VALUES('p_a1'.'Pick up litter 1'.200.6000),
('p_a2'.'Pick up litter 2'.200.6000);
 INSERT INTO jobs SET job_id = 'ces',job_title="123"  INSERT INTO jobs SELECT 'ces1'.'444'.200.6000 Copy the code
Modify the
# Simple changes
UPDATE jobs
SET job_title = '2222'
WHERE job_id = 'ces1'

Alter table multiple UPDATE jobs INNER JOIN employees ON employees.job_id = jobs.job_id SET jobs.job_title = CONCAT(jobs.job_title,'22222'),employees.job_id = 'ces1' WHERE jobs.job_title = 'Public Accountant12322222' Copy the code
delete
# delete single table
DELETE FROM jobs
WHERE job_id = 'ces'

Clear the entire table
TRUNCATE TABLE ttt;  Drop multiple tables DELETE employees FROM employees INNER JOIN jobs ON jobs.job_id = employees.job_id WHERE jobs.job_id = 'SA_MAN' Copy the code
DDL data definition language
Database management create, modify, delete 2. Table management create, modify, deleteCopy the code
Create: create modify: alter drop: drop

Library management
# create IF NOT EXISTS CHARACTER SET
CREATE DATABASE IF NOT EXISTS books CHARACTER SET utf8;

# Change cannot be done
RENAME DATABASE books TO newbook;
 # change character set ALTER DATABASE books CHARACTER SET gbk;  # delete library DROP DATABASE IF EXISTS books;  Copy the code
The management of the table
Create a table
USE books;
Create table
/ *CREATE TABLE name (Column name column type ([length]) [constraint],Column name column type ([length]) [constraint],.) * / CREATE TABLE book (  id INT. b_name VARCHAR(20),  price DOUBLE. author_id INT. publish_date DATETIME );  DESC book;  CREATE TABLE author(  id INT. au_name VARCHAR(20),  nation VARCHAR(10) );  DESC author; Copy the code
The modification of table
/ *Modify the column:The ALTER TABLE TABLE name [CHANGE | the MODIFY | ADD | DROP] COLUMN COLUMN type | constraints;Alter table name:ALTER TABLE name RENAME TO new TABLE name;* / Change the column name ALTER TABLE book CHANGE COLUMN publish_date publishDate DATETIME; Change the column type constraint ALTER TABLE book MODIFY COLUMN publishDate TIMESTAMP; Add a new column ALTER TABLE author ADD COLUMN annual DOUBLE; # remove ALTER TABLE author DROP COLUMN annual; Alter table name ALTER TABLE author RENAME TO authers; Copy the code
The deletion of the table
DROP TABLE IF EXISTS authers;
Copy the code
The replication of table
Duplicate the table structure
CREATE TABLE copy LIKE author;

Select * from table_name
CREATE TABLE copy2 
SELECT * FROM author; Copy the code
The constraint
Add constraints when adding tables
# column level constraint
CREATE TABLE stuinfo(
 id INT PRIMARY KEY.# key
 stu_name VARCHAR(20) NOT NULL.# is not empty
 gender CHAR(1) DEFAULT 'male'.# the default
 seat INT UNIQUE # only );  Table level constraint CREATE TABLE stuinfo(  id INT.# key  stu_name VARCHAR(20), # is not empty  gender CHAR(1),# the default  seat INT.# only  majorid INT.  CONSTRAINT pk PRIMARY KEY(id),# key  CONSTRAINT uq UNIQUE(seat),# only  CONSTRAINT fk_stuiinfo_major FOREIGN KEY(majorid) REFERENCES major(id)Outside the # key );  # Generic suggestion constraints DROP TABLE IF EXISTS stuinfo; CREATE TABLE stuinfo(  id INT PRIMARY KEY.# key  stu_name VARCHAR(20) NOT NULL.# is not empty  gender CHAR(1) DEFAULT 'male'.# the default  seat INT UNIQUE.# only  majorid INT. Table level constraints  CONSTRAINT fk_stuiinfo_major FOREIGN KEY(majorid) REFERENCES major(id)Outside the # key );   Copy the code
Constraints when modifying tables
Alter column constraints
ALTER TABLE stuinfo MODIFY COLUMN stu_name VARCHAR(20) NOT NULL; 
Table level constraint
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
ALTER TABLE stuinfo ADD FOREIGN KEY(majorid) REFERENCES major(id);
# delete foreign key # ALTER TABLE stuinfo DROP PRIMARY KEY; Copy the code
Identity column (self-growing column)

               ❝ 
AUTO_INCREMENT
               ❞
              
CREATE TABLE stuinfo(
 id INT PRIMARY KEY AUTO_INCREMENT, # key
 stu_name VARCHAR(20) NOT NULL.# is not empty
 gender CHAR(1) DEFAULT 'male'.# the default
 seat INT UNIQUE.# only
 majorid INT. Table level constraints  CONSTRAINT fk_stuiinfo_major FOREIGN KEY(majorid) REFERENCES major(id)Outside the # key ); Copy the code
TCL Transaction control language

               ❝ 
One or a group of SQL statements constitute a unit of execution, and either all execute or all fail.
               ❞
              

               ❝ 
A. atomicity B. consistency C. isolation D. persistence
               ❞
              
Commit indicates completion, rollback indicates exception
set autocommit = 0; -- Turn off automatic transactions
START TRANSACTION; -- Start transaction
UPDATE stuinfo SET stu_name = '12232' WHERE id = 3;
SAVEPOINT a; -- Save node
UPDATE stuinfo SET stu_name = '12332' WHERE id = 5; ROLLBACK; -- Rollback the transaction ROLLBACK TO a; -- Roll back transactions to specified nodes COMMIT; Commit transaction Copy the code
This article is formatted using MDNICE
Copy the code

   ;   
between andThe scope of

    ❝ 
Between what and what, before and after
    ❞
   
Find data between 100 and 200

SELECT * FROM `employees` 

WHERE 

    `employee_id` BETWEEN 100 AND 200;

Copy the code
inBelong to

    ❝ 
Query whether it belongs to a certain list
    ❞
   
# query whether you belong to a list

SELECT * FROM `employees` 

WHERE 

    `job_id` IN ('SH_CLERK'.'AD_ASST'.'AD_VP');

Copy the code
is null or is not nullWhether to Null
The query field is null

SELECT * FROM `employees`

WHERE

    `commission_pct` IS NULL;



# query is not null

SELECT * FROM `employees`

WHERE

    NOT `commission_pct` IS NULL;



SELECT * FROM `employees`

WHERE

    `commission_pct` IS NOT NULL;

Copy the code
< = >Security is equal to the

    ❝ 
You can judge both NULL and numeric values
    ❞
   
SELECT * FROM `employees`

WHERE

    `commission_pct`< = >NULL;

Copy the code
order byThe sorting
ASC ascending DESC Descending order Default ASC
SELECT * FROM `employees` ORDER BY `salary` ASC; - ascending

SELECT * FROM `employees` ORDER BY `salary` DESC; - in descending order



SELECT `salary` * IFNULL(`commission_pct`.0) + IFNULL(manager_id,0) as money,`salary`  FROM `employees` ORDER BY `money`; -- Expression aliases in descending order



SELECT LENGTH(`last_name`) as len FROM `employees` ORDER BY len; - according to the function



SELECT * FROM `employees` ORDER BY `salary` DESC.`employee_id` ASC; -- Multiple sorting conditions

Copy the code
Grouping query


The total salary for each job

SELECT SUM(`salary`) AS `money`.`job_id` FROM `employees` GROUP BY `job_id` ORDER BY `money`;



# Maximum salary per job

SELECT MAX(`salary`) as `max`.`job_id` FROM `employees` GROUP BY `job_id` ORDER BY `max`;



Select * from 'a' where 'a' = 'a'

SELECT MAX(`salary`) as `max`.`job_id`

FROM `employees`

WHERE email LIKE '%a%' 

GROUP BY `job_id` 

ORDER BY `max`;



Select * from a where the maximum salary of a job is greater than 10,000

SELECT MAX(`salary`) as `max`.`job_id` 

FROM `employees`

WHERE email LIKE '%a%' 

GROUP BY `job_id`

HAVING `max` > 10000

ORDER BY `max`;



Select * from (select * from (select * from (select * from (select * from (select * from)))

SELECT LENGTH(`first_name`) AS `name`.COUNT(1) AS `count`

FROM `employees`

GROUP BY `name`

HAVING `count` > 5;



# Multi-field grouping

SELECT AVG(`salary`) AS `avg`.`department_id`.`job_id`

FROM `employees`

GROUP BY `department_id`.`job_id`

ORDER BY `department_id`.`job_id`

Copy the code
Join queries
Category: divided by years: SQL92 standard: only internal connection SQL99 standard [recommended] : Internal connection + external connection (left and right) + cross connection Divided by functions: Internal connection: equivalent connection Non-equivalent connection Self-connection External connection: Left and external connection right external connection Full external connection Cross connectionCopy the code
Sql92 standard
# Equivalent connection

SELECT e.`first_name`,j.`job_title`.`j`.`job_id`

FROM `employees` as e,`jobs` as j

WHERE `e`.`job_id` = `j`.`job_id`;



# non-equivalent connection



# the connection

SELECT e.last_name,m.employee_id,e.manager_id,m.last_name 

FROM `employees` AS e, `employees` AS m

WHERE `e`.employee_id = `m`.manager_id

Copy the code
Sql99 standard
# Equivalent connection

SELECT `last_name`.`department_name`

FROM employees

INNER JOIN departments

ON employees.`department_id` = departments.`department_id`



# Complex equivalent connection

SELECT department_name,COUNT(The '*') AS count.MAX(`salary`) AS max.min(`salary`) AS min

FROM employees

INNER JOIN departments

ON employees.department_id = departments.department_id

WHERE last_name LIKE '%o%'

GROUP BY department_name

HAVING `count` BETWEEN 2 AND 10

ORDER BY `count`



# multi-table equivalent join

SELECT last_name,department_name,job_title

FROM employees

INNER JOIN departments

ON departments.department_id =employees.department_id

INNER JOIN jobs

ON employees.job_id = jobs.job_id;



Copy the code
The subquery
Category: by location: SELECT: only standard quantum query form table subquery WHERE and HAVING standard quantum query column subquery exists table subquery
According to the number of rows and columns of the result set: standard quantum query (the result set has only one row and one column) column subquery (the result set has only one column and one row) row subquery (the result set has only one row and one row) table subquery (the result set generally has many rows and many columns)Copy the code
Copy the code


Where and having
Features: 1. Placed in parentheses 2. Placed on the right side of the condition 3. Column subqueries: With multi-line operators.


Select * from Abel whose salary is higher than Abel's

SELECT * FROM employees 

WHERE salary > (

 SELECT salary FROM employees 

 WHERE last_name = 'Abel'

);



# job_id Employee whose salary is the same as employee 141 and whose salary is greater than employee 143

SELECT last_name,salary,employee_id FROM employees

WHERE job_id = (

 SELECT job_id FROM employees WHERE employee_id = 141

) AND salary > (

 SELECT salary FROM employees WHERE employee_id = 143

)



Select * from department where minimum wage is higher than minimum wage in department 50

SELECT department_id, COUNT(*),MIN(salary) AS minsalary FROM employees 

GROUP BY department_id

HAVING minsalary > (SELECT MIN(salary) AS minsalary FROM employees WHERE department_id = 50)

ORDER BY minsalary DESC;

Copy the code
Paging query
SELECT *

FROM `employees` 

LIMIT (page- 1) *size.size;



SELECT *

FROM `employees` 

LIMIT 10.10;

Copy the code
The joint query
# no

Copy the code
DML data manipulation language
insert
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES('p_a'.'Pick up litter 1'.200.6000);



INSERT INTO jobs 

VALUES('p_a1'.'Pick up litter 1'.200.6000),

('p_a2'.'Pick up litter 2'.200.6000);



INSERT INTO jobs SET job_id = 'ces',job_title="123"



INSERT INTO jobs SELECT 'ces1'.'444'.200.6000

Copy the code
Modify the
# Simple changes

UPDATE jobs

SET job_title = '2222'

WHERE job_id = 'ces1'



Alter table multiple

UPDATE jobs

INNER JOIN employees

ON employees.job_id = jobs.job_id

SET jobs.job_title = CONCAT(jobs.job_title,'22222'),employees.job_id = 'ces1'

WHERE jobs.job_title = 'Public Accountant12322222'

Copy the code
delete
# delete single table

DELETE FROM jobs

WHERE job_id = 'ces'



Clear the entire table

TRUNCATE TABLE ttt;



Drop multiple tables

DELETE employees

FROM employees

INNER JOIN jobs

ON jobs.job_id = employees.job_id

WHERE jobs.job_id = 'SA_MAN'

Copy the code
DDL data definition language
Database management create, modify, delete 2. Table management create, modify, deleteCopy the code
Create: create modify: alter drop: drop

Library management
# create IF NOT EXISTS CHARACTER SET

CREATE DATABASE IF NOT EXISTS books CHARACTER SET utf8;



# Change cannot be done

RENAME DATABASE books TO newbook;



# change character set

ALTER DATABASE books CHARACTER SET gbk;



# delete library

DROP DATABASE IF EXISTS books;



Copy the code
The management of the table
Create a table
USE books;

Create table

/ *

CREATE TABLE name (

Column name column type ([length]) [constraint],

Column name column type ([length]) [constraint],

.

)

* /

CREATE TABLE book (

 id INT.

 b_name VARCHAR(20),

 price DOUBLE.

 author_id INT.

 publish_date DATETIME

);



DESC book;



CREATE TABLE author(

 id INT.

 au_name VARCHAR(20),

 nation VARCHAR(10)

);



DESC author;

Copy the code
The modification of table
/ *

Modify the column:

The ALTER TABLE TABLE name [CHANGE | the MODIFY | ADD | DROP] COLUMN COLUMN type | constraints;

Alter table name:

ALTER TABLE name RENAME TO new TABLE name;

* /

Change the column name

ALTER TABLE book CHANGE COLUMN publish_date publishDate DATETIME;

Change the column type constraint

ALTER TABLE book MODIFY COLUMN publishDate TIMESTAMP;

Add a new column

ALTER TABLE author ADD COLUMN annual DOUBLE;

# remove

ALTER TABLE author DROP COLUMN annual;

Alter table name

ALTER TABLE author RENAME TO authers;

Copy the code
The deletion of the table
DROP TABLE IF EXISTS authers;

Copy the code
The replication of table
Duplicate the table structure

CREATE TABLE copy LIKE author;



Select * from table_name

CREATE TABLE copy2 

SELECT * FROM author;

Copy the code
The constraint
Add constraints when adding tables
# column level constraint

CREATE TABLE stuinfo(

 id INT PRIMARY KEY.# key

 stu_name VARCHAR(20) NOT NULL.# is not empty

 gender CHAR(1) DEFAULT 'male'.# the default

 seat INT UNIQUE # only

);



Table level constraint

CREATE TABLE stuinfo(

 id INT.# key

 stu_name VARCHAR(20), # is not empty

 gender CHAR(1),# the default

 seat INT.# only

 majorid INT.

 

 CONSTRAINT pk PRIMARY KEY(id),# key

 CONSTRAINT uq UNIQUE(seat),# only

 CONSTRAINT fk_stuiinfo_major FOREIGN KEY(majorid) REFERENCES major(id)Outside the # key

);



# Generic suggestion constraints

DROP TABLE  IF EXISTS stuinfo;

CREATE TABLE stuinfo(

 id INT PRIMARY KEY.# key

 stu_name VARCHAR(20) NOT NULL.# is not empty

 gender CHAR(1) DEFAULT 'male'.# the default

 seat INT UNIQUE.# only

 majorid INT.

 Table level constraints

 CONSTRAINT fk_stuiinfo_major FOREIGN KEY(majorid) REFERENCES major(id)Outside the # key

);





Copy the code
Constraints when modifying tables
Alter column constraints

ALTER TABLE stuinfo MODIFY COLUMN stu_name VARCHAR(20) NOT NULL; 

Table level constraint

ALTER TABLE stuinfo ADD PRIMARY KEY(id);

ALTER TABLE stuinfo ADD FOREIGN KEY(majorid) REFERENCES major(id);

# delete foreign key

# ALTER TABLE stuinfo DROP PRIMARY KEY;

Copy the code
Identity column (self-growing column)

    ❝ 
AUTO_INCREMENT
    ❞
   
CREATE TABLE stuinfo(

 id INT PRIMARY KEY AUTO_INCREMENT, # key

 stu_name VARCHAR(20) NOT NULL.# is not empty

 gender CHAR(1) DEFAULT 'male'.# the default

 seat INT UNIQUE.# only

 majorid INT.

 Table level constraints

 CONSTRAINT fk_stuiinfo_major FOREIGN KEY(majorid) REFERENCES major(id)Outside the # key

);

Copy the code
TCL Transaction control language

    ❝ 
One or a group of SQL statements constitute a unit of execution, and either all execute or all fail.
    ❞
   

    ❝ 
A. atomicity B. consistency C. isolation D. persistence
    ❞
   
Commit indicates completion, rollback indicates exception

set autocommit = 0; -- Turn off automatic transactions

START TRANSACTION; -- Start transaction

UPDATE stuinfo SET stu_name = '12232' WHERE id = 3;

SAVEPOINT a; -- Save node

UPDATE stuinfo SET stu_name = '12332' WHERE id = 5;

ROLLBACK; -- Rollback the transaction

ROLLBACK TO a; -- Roll back transactions to specified nodes

COMMIT; Commit transaction

Copy the code
This article is formatted using MDNICE