mysql basic learning notes

install

MacOS

Windows 10

Centos 7

SQL specification

  1. It is not case sensitive, but upper case keywords, lower case table names and column names are recommended
  2. End of semicolon for each SQL recommendation
  3. Line feed indent per SQL as required
  4. Note:
    Single line: \--
    Multiline: / **/

type

Value:
    Integer:
    Decimal:
        Fixed point number
        Floating point number
 Characters:
    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        2020

Common SQL

use test; -- Selected database
show tables; -- All tables of the currently selected library

show tables from mysql; # Query tables under mysql

SHOW INDEX FROM stuinfo; # Show current index

select database(); # View current library

/* create table table1(
    id int,
    name varchar(24)
); */

desc table1; -- View table structure

select * from table1;

insert into table1 (id,name) values(1,'test'); -- insert

update table1 set name='holy crap' where name='ces'; -- modify

update table1 set id=0 where name='holy crap'; -- modify

delete from table1 where name='holy crap'; -- delete

Common functions

Single-Row Functions

handle

Character function

SELECT LENGTH('Who am I'); -- Get the current byte length from the current character set

SELECT CONCAT('me','yes','Who'); -- Splice string

SELECT UPPER('Abc'); -- Convert to uppercase characters

SELECT LOWER('Abc'); -- Convert to lowercase

SELECT SUBSTR('abc123 one two three',4,3); -- Intercept 3 indexes containing 4 starting from 4 starting from 1

SELECT SUBSTRING('abc123 one two three',4,3); -- Intercept 3 indexes containing 4 starting from 4 starting from 1

SELECT INSTR('01234556','234'); -- Find the position where the string appears. If it is not found, it is 0

SELECT TRIM(' A B C D '); -- Remove front and back spaces

SELECT TRIM('a' FROM 'aaaaA B  CaaaDaaaa' ); -- Before and after removal a

SELECT LPAD('abc123 one two three',20,'*'); -- padding-left /Keep left
SELECT RPAD('abc123 one two three',20,'*'); -- Right fill/Keep left

Mathematical function

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

SELECT CEIL(0.2); -- Round up
SELECT FLOOR(0.9); -- Round down

SELECT RAND(); -- random number

SELECT TRUNCATE(0.2345,3); -- How many decimal places are reserved for processing

SELECT MOD(10,3); -- Residual

Date function

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

SELECT CURDATE(); -- Returns the current date

SELECT CURTIME(); -- Return to current time

SELECT YEAR(NOW()) as `year`, MONTH(NOW()) as `month`, DAY(NOW()) as date as `day`; -- year/month/day

SELECT STR_TO_DATE('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'); -- Format time

Other functions

SELECT VERSION(); -- View version number

SELECT DATABASE(); -- View current library
 
SELECT USER(); -- Current user

Process control function

SELECT IF(10<5,'large','small'); -- if

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

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

Statistical function

statistics

SELECT COUNT(*) FROM `employees`; -- Quantity statistics
SELECT SUM(`salary`) FROM `employees`; -- Additive sum
SELECT AVG(`salary`) FROM `employees`; -- average value
SELECT MAX(`salary`) FROM `employees`; -- Maximum
SELECT MIN(`salary`) FROM `employees`; -- minimum value

SELECT COUNT(*) AS `count`, SUM(`salary`) AS `sum`, AVG(`salary`) AS `avg`, MAX(`salary`) as `max`, MIN(`salary`) as `min`
FROM `employees`;

# take care
/*

*/

Common constraints

A restriction used to restrict the data in a table to ensure the accuracy and reliability of the data in the table

Classification:
    Six constraints:
        NOT NULL: Not empty,Used to ensure that the value of this field cannot be empty
        DEFAULT: Default
        PRIMARY KEY: Primary key, used to ensure that the field is unique (not empty)
        UNIQUE: Unique (nullable)
        CHECK: Check( mysql Not supported)
        FOREIGN KEY: Foreign key, used to restrict the relationship between two tables, and to ensure that the field must come from the primary key of the associated table
    When to add constraints:
        1. Create table
        2. Modify table
    Add category of constraints:
        Column level constraints:
            The six constraints are syntactically supported, and foreign key constraints are invalid
        Table level constraints:
            Supports all but non empty and default
            
Primary key and unique difference:
    Primary key: unique, non empty, and only one
    Unique: unique, nullable, multiple

Foreign key:
    1. Set foreign key relationships from tables
    2. The types of master and slave tables are consistent/compatible
    3. The primary table association key is generally primary key or unique
    4. The data in the primary table must be corresponding. Delete the secondary table before deleting the primary table

DQL data query language

Constants, expressions, functions

SELECT 1; -- constant value
SELECT 10*20; -- expression
SELECT VERSION(); -- function

alias

SELECT 1+2 as number;

Weight removal

SELECT DISTINCT 
    `name`
FROM 
    `table`

+No

SELECT 1+2; -- Number addition
SELECT 1+'123'; -- String will be forcibly converted to number and non number to 0
SELECT 1 + Null; -- And Null return Null

String concat

SELECT CONCAT('a','b','c'); -- String splicing
SELECT CONCAT(`first_name`,`last_name`) as `name` FROM `employees`; -- Splice field

Condition query

Conditional expression

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

# equal to
SELECT 
    CONCAT(`first_name`,`last_name`) as `name` 
FROM `employees` 
WHERE 
    `first_name`='Bruce'; 

# Security equals checkable Null

SELECT 
    CONCAT(`first_name`,`last_name`) as `name` 
FROM `employees` 
WHERE 
    `first_name`<=>'Bruce'; 

# greater than
SELECT 
    *
FROM `employees` 
WHERE 
    `department_id` > 60;

# less than
SELECT 
    *
FROM `employees` 
WHERE 
    `department_id` <= 60;
    
# Not equal to 
# Hey= Not recommended
SELECT 
    *
FROM `employees` 
WHERE 
    `department_id` <> 60;
Logical expression

&& || !
AND OR NOT

# And 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';
    
# wrong
SELECT 
    CONCAT(`first_name`,`last_name`) as `name` 
FROM `employees` 
WHERE 
    NOT
    `first_name`='Bruce' 
Fuzzy query
like fuzzy 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 and scope

Between what and what, including before and after

# Find data between 100 and 200
SELECT * FROM `employees` 
WHERE 
    `employee_id` BETWEEN 100 AND 200;
in belongs to

Query whether it belongs to one of some lists

# Query whether it belongs to one of some lists
SELECT * FROM `employees` 
WHERE 
    `job_id` IN ('SH_CLERK','AD_ASST','AD_VP');
is null or is not null
# Query field is empty
SELECT * FROM `employees`
WHERE
    `commission_pct` IS NULL;

# Query is not empty
SELECT * FROM `employees`
WHERE
    NOT `commission_pct` IS NULL;

SELECT * FROM `employees`
WHERE
    `commission_pct` IS NOT NULL;
< = > Security equals

Both NULL and numeric values can be judged

SELECT * FROM `employees`
WHERE
    `commission_pct` <=> NULL;
order by

ASC ascending DESC descending default ASC

SELECT * FROM `employees` ORDER BY `salary` ASC; -- Ascending order
SELECT * FROM `employees` ORDER BY `salary` DESC; -- Descending order

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

SELECT LENGTH(`last_name`) as len FROM `employees` ORDER BY len; -- By function

SELECT * FROM `employees` ORDER BY `salary` DESC, `employee_id` ASC; -- Multiple sort criteria

Group query

# Total wages per type of work
SELECT SUM(`salary`) AS `money`,`job_id` FROM `employees` GROUP BY `job_id` ORDER BY `money`;

# Maximum wage per type of work
SELECT MAX(`salary`) as `max`,`job_id` FROM `employees` GROUP BY `job_id` ORDER BY `max`;

# Query the maximum salary of the type of work with a in the mailbox
SELECT MAX(`salary`) as `max`, `job_id`
FROM `employees`
WHERE email LIKE '%a%' 
GROUP BY `job_id` 
ORDER BY `max`;

# Query for HAVING with the highest salary of more than 10000 for the type of work with a in the mailbox
SELECT MAX(`salary`) as `max`, `job_id` 
FROM `employees`
WHERE email LIKE '%a%' 
GROUP BY `job_id`
HAVING `max` > 10000
ORDER BY `max`;

# Query data of more than 5 persons with the same name length
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`

join query

classification:
    By age:
        sql92 Standard: only internal connection is supported
        sql99 Standard [recommended]: support internal connection+External connection (left outer, right outer)+Cross connect
    By function:
        Internal connection:
            Equivalent connection
            Non equivalent connection
            Self connection
        External connection:
            Left outer connection
            Right outer connection
            All external connection
        Cross connect

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

# Self 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

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('*') 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 connection
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;

Subquery

Classification:
    Press where it appears:
        select Back:
            Only scalar subqueries are supported
        form behind
            Table subquery
        where and having behind *
            scalar subquery   *
            Column subquery    *
            Row subquery
        exists behind
            Table subquery
        
    Different rows and columns by result set:
        scalar subquery (The result set has only one row and one column)
        Column subquery(The result set has only one column and multiple rows)
        Row subquery(The result set has only one row with multiple columns)
        Table subquery(The result set is generally multi row and multi column)

Behind where and having

Features: 1 Put in parentheses 2 Place to the right of the condition 3 Scalar subquery: cooperate with single line operator. Column subquery: cooperate with multi row operator.

# Check out people who earn more than Abel
SELECT * FROM employees 
WHERE salary > (
    SELECT salary FROM employees 
    WHERE last_name = 'Abel'
);

# Job_ Employees whose ID 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
)

# Query the minimum wage of the Department higher than the minimum wage of department No. 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;

Paging query

SELECT *
FROM `employees` 
LIMIT (page-1)*size,size;

SELECT *
FROM `employees` 
LIMIT 10,10;

Joint query

# nothing

DML data manipulation language

insert

INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES('p_a','Garbage collection 1',200,6000);

INSERT INTO jobs 
VALUES('p_a1','Garbage collection 1',200,6000),
('p_a2','Garbage collection 2',200,6000);

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

INSERT INTO jobs SELECT 'ces1','444',200,6000

modify

# Simple modification
UPDATE jobs
SET job_title = '2222'
WHERE job_id = 'ces1'

# Modify multiple tables
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'

delete

# Single table deletion
DELETE FROM jobs
WHERE job_id = 'ces'

# Empty entire table
TRUNCATE TABLE ttt;

# Multi table deletion
DELETE employees
FROM employees
INNER JOIN jobs
ON jobs.job_id = employees.job_id
WHERE jobs.job_id = 'SA_MAN'

DDL data definition language

1. Library management
    establish\modify\delete
2. Table management
    establish\modify\delete

establish: create
 modify: alter
 delete: drop

Library management

# Create IF NOT EXISTS for fault tolerant CHARACTER SET
CREATE DATABASE IF NOT EXISTS books CHARACTER SET utf8;

# Can't modify
RENAME DATABASE books TO newbook;

# Change character set
ALTER DATABASE books CHARACTER SET gbk;

# Library deletion
DROP DATABASE IF EXISTS books;

Table management

Create table

USE books;
# Table creation
/*
CREATE TABLE 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;

Table modification

/*
Modify column:
ALTER TABLE Table name [CHANGE|MODIFY|ADD|DROP] COLUMN column name type constraint;
Modify table name:
ALTER TABLE Table name RENAME TO new table name;
*/
# Modify column name
ALTER TABLE book CHANGE COLUMN publish_date publishDate DATETIME;
# Modifying column type constraints
ALTER TABLE book MODIFY COLUMN publishDate TIMESTAMP;
# Add new column
ALTER TABLE author ADD COLUMN annual DOUBLE;
# delete
ALTER TABLE author DROP COLUMN annual;
# Modify table name
ALTER TABLE author RENAME TO authers;

Deletion of tables

DROP TABLE IF EXISTS authers;

Replication of tables

# Copy table structure
CREATE TABLE copy LIKE author;

# Copy entire table
CREATE TABLE copy2 
SELECT * FROM author;

constraint

Add constraints when adding tables

# Column level constraint
CREATE TABLE stuinfo(
    id INT PRIMARY KEY, # Primary key
    stu_name VARCHAR(20) NOT NULL, # Not empty
    gender CHAR(1) DEFAULT 'male', # default
    seat INT UNIQUE # only
);

# Table level constraint
CREATE TABLE stuinfo(
    id INT, # Primary key
    stu_name VARCHAR(20), # Not empty
    gender CHAR(1) , # default
    seat INT, # only
    majorid INT,
    
    CONSTRAINT pk PRIMARY KEY(id),# Primary key
    CONSTRAINT uq UNIQUE(seat),# only
    CONSTRAINT fk_stuiinfo_major FOREIGN KEY(majorid) REFERENCES major(id)# Foreign key
);

# General recommendation constraints
DROP TABLE  IF EXISTS stuinfo;
CREATE TABLE stuinfo(
    id INT PRIMARY KEY, # Primary key
    stu_name VARCHAR(20) NOT NULL, # Not empty
    gender CHAR(1) DEFAULT 'male', # default
    seat INT UNIQUE, # only
    majorid INT,
    -- Table level constraint
    CONSTRAINT fk_stuiinfo_major FOREIGN KEY(majorid) REFERENCES major(id)# Foreign key
);

Constraints when modifying tables

# Modify 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;

Identity column (self growing column)

AUTO_INCREMENT

CREATE TABLE stuinfo(
    id INT PRIMARY KEY AUTO_INCREMENT, # Primary key
    stu_name VARCHAR(20) NOT NULL, # Not empty
    gender CHAR(1) DEFAULT 'male', # default
    seat INT UNIQUE, # only
    majorid INT,
    -- Table level constraint
    CONSTRAINT fk_stuiinfo_major FOREIGN KEY(majorid) REFERENCES major(id)# Foreign key
);

TCL transaction control language

One or a group of sql statements form an execution unit, which either executes all or fails all.

Acid: a: atomicity C: consistency I: isolation D: persistence

# Commit means completion, rollback means 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 transaction
ROLLBACK TO a; -- Rollback transaction to specified node
COMMIT;    -- Commit transaction 

Tags: MySQL

Posted by kbaker on Tue, 31 May 2022 12:48:45 +0530