mysql database technology 1 - Basic addition, deletion and alteration sql statements

Keywords: MySQL Database SQL mysqldump

1. Classification of database languages

DDL: Database Definition Language

Used to create, modify, and delete data structures within a database, such as:

1: Create and delete databases (CREATE DATABASE || DROP DATABASE);

2: Create, modify, rename, delete tables (CREATE TABLE | ALTER TABLE | RENAME TABLE | DROP TABLE, TRUNCATE TABLE);

3: Create and delete indexes (CREATEINDEX || DROP INDEX)

DML: data Manipulation language

Modify data in the database, including insert (INSERT), update (UPDATE), and delete (DELETE)

DCL: data Control language (Manage User Rights)

Used to access the database, mainly including creating users, authorizing users, revoking authorization for users, querying user authorization and deleting users, etc.

Such as: 1. create user 2: grant access rights to users (GRANT); 3: cancel user access rights (REMOKE); 4 delete users (drop user).

DQL: data Query language

Query data from one or more tables in a database (SELECT)

Here are four types of languages

2. DDL: database definition language data Definition language

2.1. Create a database and create it with the utf8 character set

-- If not, create a database,And with utf Character set creation for
CREATE DATABASE IF NOT EXISTS westos DEFAULT CHARACTER SET = 'utf8'

2.2. Delete the database if it exists

-- Delete the database if it exists
DROP DATABASE IF EXISTS westos

2.3. Using databases

-- Use database
USE westos

2.4. View all databases

-- View all databases
SHOW DATABASES

2.5. View all tables

-- View all tables
SHOW TABLES

2.6. Creating tables

-- Create Student Table,If it does not exist
CREATE TABLE IF NOT EXISTS student (
    `id` INT NOT NULL AUTO_INCREMENT COMMENT 'Student table id',
    `name` VARCHAR(30) NOT NULL DEFAULT 'anonymous' COMMENT 'Full name',
    `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT 'Password',
    `sex` VARCHAR(2) NOT NULL DEFAULT 'male' COMMENT 'Gender',
    `birthday` DATETIME DEFAULT NULL COMMENT 'Date of birth',
    `address` VARCHAR(100) DEFAULT NULL COMMENT 'Home Address',
    `email` VARCHAR(50) DEFAULT NULL COMMENT 'mailbox',
    PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET = 'utf8'

2.7. View statements that create databases and data tables

-- View statement to create database
SHOW CREATE DATABASE westos
-- View the statement that created the data table
SHOW CREATE TABLE student

2.8. View table structure

-- View the specific structure of the table
DESC student

3. DQL: data Query language

3.1. Modify table name

-- Modify Table Name
ALTER TABLE student RENAME AS teacher

3.2. Add fields to the table

-- Add Field to Table
ALTER TABLE teacher ADD age INT

3.3. Modify the fields of the table

--Modify the fields of the table (rename, modify constraints)
ALTER TABLE teacher MODIFY age VARCHAR(11) - Modify constraints and cannot change field names
 ALTER TABLE teacher CHANGE age is_del INT -- Field rename

3.4. Delete fields from tables

-- Delete field from table
ALTER TABLE teacher DROP is_del

3.5, Delete Table

 -- Delete Table
 DROP TABLE IF EXISTS student

3.6. Insert statement

 -- Insert statement (add)
 INSERT INTO teacher (`name`) VALUES ('xgp')

3.7. Modify statement

-- Modify statement
UPDATE teacher SET `name`='xgp',sex='female' WHERE id=1

3.8. Time to set up database

-- Setting the time of the database
UPDATE `teacher` SET `birthday` = CURRENT_TIME WHERE id=1

3.9, Delete specified data

-- Delete specified data
DELETE FROM teacher WHERE id=1

3.10. Empty a data table

-- Clear a database table completely, without changing its structure and index constraints,Self-increasing returns to zero
TRUNCATE TABLE teacher

4. DQL: data Query language

4.1. Query by Alias

--Query (using aliases)
SELECT `name` name, pwd password FROM teacher table

4.2, Split String

--Function stitching string Concat(a,b)
SELECT CONCAT('Name:', `name`) New name FROM teacher

4.3. Weight removal

-- Duplicate removal
SELECT DISTINCT `name` FROM teacher

4.4. View System Version

-- View System Version
SELECT VERSION()

4.5. Calculation results

--Calculation results
 SELECT 100*3-23 calculation results
 SELECT pwd+'1'password plus 1 FROM teacher

4.6. Fuzzy Query

-- Fuzzy Query
-- like Combination %(Represents 0 to any character) _(Represents a character)
SELECT `name`,pwd FROM teacher
WHERE `name` LIKE '_g_'

-- in
SELECT id,`name`,pwd FROM teacher
WHERE id IN (4,7,10)

4.7, Sort Query

-- Sort: Ascending ASC Descending order DESC
-- order by Which field to sort and how to arrange
SELECT * FROM teacher ORDER BY id DESC 
SELECT * FROM teacher ORDER BY id ASC

4.8, Paging Query

-- Paging:
SELECT * FROM teacher ORDER BY id DESC LIMIT 2,10

5. Common Functions

5.1. Mathematical Functions

--Common Functions
 SELECT ABS(-78) --Absolute value
 SELECT CEILING(9.4) - Rounding up
 SELECT FLOOR(9.4) - Rounding down
 SELECT SIGN(-9) --Symbol for judging a number

5.2, String Functions

--String function
 SELECT CHAR_LENGTH('Haha') --String Length
 SELECT CONCAT('xgp','aaa') - Split String
 SELECT INSERT('asa',1,2,'ss') - Replace string
 SELECT LOWER ('AAAAAA') - To Lower Case
 SELECT UPPER ('aaaaaa') - Uppercase
 SELECT INSTR ('a a a','a') - Returns the index of the first occurrence of a substring
 SELECT REPLACE ('a a a a','a','x') - Replace string
 SELECT SUBSTR('asasasxsssas',4,6) - intercept string, intercept position, intercept length
 SELECT REVERSE('aasss') --String inversion

5.3, Time and Date Functions

--Time and date function (get current date)
SELECT CURRENT_DATE(); --Get the current date
 SELECT CURDATE(); --Get the current date
 SELECT NOW(); --Get the current time
 SELECT LOCALTIME(); --Get local time
 SELECT SYSDATE() -- Get system time

5.4. System Functions

-- system
SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()

5.5. Aggregation function

-- Aggregate function
SELECT COUNT(*) FROM teacher
SELECT COUNT(field) FROM teacher   -- Ignore null value
SELECT COUNT(1) FROM teacher
SELECT COUNT(Primary key) FROM teacher -- Faster

SELECT SUM(pwd) Summation FROM teacher
SELECT AVG(pwd) Average FROM teacher
SELECT MAX(pwd) Highest FROM teacher
SELECT MIN(pwd) minimum FROM teacher

6. DCL: data Control Language (Manage User Rights) data Control

6.1. Creating Users

-- Create User
CREATE USER xgp123 IDENTIFIED BY '123456'

6.2. Modify Password

-- Modify password (change when user password)
SET PASSWORD = PASSWORD('111111')
-- Change password (change the password of a custom user)
SET PASSWORD FOR xgp123 = PASSWORD('123456')

6.3. Rename users

-- Rename User
RENAME USER xgp123 TO xgp

6.4. User Authorization

-- User Authorization(Grant all permissions, libraries, tables)
GRANT ALL PRIVILEGES ON *.* TO xgp

6.5. View specified user rights

-- View specified user permissions
SHOW GRANTS FOR xgp

6.6. View Administrator Permissions

-- View Administrator Permissions
SHOW GRANTS FOR root@localhost

6.7. Revoke Rights

-- Revoke Permission(Revoke all permissions)
REVOKE ALL PRIVILEGES ON *_* FROM xgp

7. Transactions

7.1. Autocommit of open/close transactions, mysql is on by default

--Transaction (Test Transfer) mysql is open by default transaction autocommit
 SET autocommit = 0; --Turn off transaction commit first
 SET autocommit = 1; --On

7.2. Processing transactions manually

-- Transaction Open
START TRANSACTION -- Mark the start of a transaction

-- Submit
COMMIT

-- RollBACK
ROLLBACK

-- End of Transaction

-- understand
SAVEPOINT -- Set a save point for a transaction
ROLLBACK TO SAVEPOINT Save point name, roll to save point

7.3. Testing transactions manually through transfer cases

-- Write transfer cases
CREATE TABLE account (
id INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`money` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (id)
)ENGINE=INNODB DEFAULT CHARSET = 'utf8'

INSERT INTO account(`name`,money) VALUES ('Zhang San',2000.00),('Li Si',10000.00)

-- Simulated transfer
SET autocommit = 0; -- Turn off automatic submission
START TRANSACTION; -- Open Transaction
UPDATE account SET money = money - 500 WHERE `name` = 'Zhang San';
UPDATE account SET money = money + 500 WHERE `name` = 'Li Si';

COMMIT; -- Submit Transaction
ROLLBACK; -- RollBACK
SET autocommit = 1; -- Restore Defaults

8. Other Technologies

8.1. Back up the database

Command line executes export command mysqldump
mysqldump -hlocalhost -uroot -p123456 westos > D:/a.sql

8.2. Importing database

--Import
 source sql file path

8.3. Encrypt the database with md5

-- Database level MD5 encryption
UPDATE teacher SET pwd = MD5(pwd)
-- Encrypt on insert
INSERT INTO teacher(`name`,pwd) VALUES ('aaa',MD5('123456'))
-- How to Verify
SELECT * FROM teacher WHERE `name`='aaa' AND pwd = MD5('123456')

9. Detailed grammar format document for mysql basic grammar

Common commands for mysql products

1, start and stop of mysql service

Mode 1: Computer - Right-click Management - Service
 Mode 2: Run as Administrator
    net start service name (start service)
    net stop service name (stop service)

2, login and exit of mysql service

Mode 1: Through the client that comes with mysql
    root user only
 Mode 2: via cmd command line
    Land:
        mysql [-h hostname-P port number]-u username-p[password]
    Sign out:
        exit or ctrl+c

3. Basic use of sql statements

1. View all current databases
    show databases;
2. Open the specified library
    user library name
 3. View all tables in the current library
    show tables;
4. View all tables from other libraries
    show tables from library name;
5. Create tables
    create table table table name (
        Column name column type,
        Column name column type,
        ...
    );
6. View table structure
    desc table name;
7. View the server version
    Mode 1: Log on to mysql server
        select version();
    Mode 2: No login to mysql server
        mysql --version or mysql-V
 8. Comments
    Single line: #Comment text
        --Comment Text
    Multiline: /* Comment Text*/

DQL Language Learning

Phase 1:1, Basic Query

Grammar:
    select query list from table name;
Query list:
    Fields, Constant Values, Expressions, Functions in Tables
 1. Single field in query table
    select last_name from employees;
2. Query multiple fields in a table
    select last_name,salary,email from employees;
3. Query all fields in the table
    select * from employees;

2, Other Base Queries

1.Query Constant Value
    select 100;
    select 'join';
2.Query expression
    select 100*98;
    select 100%98;
3.Query function
    select version();

3, alias the field

1. Mode 1: (use as)
    select 100%98 as result;
    select last_name as last name from employees;
2. Mode 2: (Use spaces)
    select last_name from employees;

4, distinct

1.Case: Query the number of all departments in the employee table
    select distinct
        department_id
    from
        employees;

Function of the + sign

1. Case: Query employee's name and name are joined into a field and displayed as name
    Wrong practices:
        select 
            last_name + first_name as name
        from
            employees;
    Correct practice: (use concat() function)
        select 
            concat(last_name,first_name) as name
        from 
            employees;

6, use of ifnull() function

Format:
    Ifnull (a parameter that determines whether it is empty, if it is an empty return value)
Give an example:
    select ifnull(commission_pct,0) as bonus rate;

Stage 2: 1, Conditional Query

Grammar:
    select 
        Query List 
    from 
        Table Name
    where
        Filter conditions;
Filter criteria:
    1. Filter by conditional expression
        Conditional operator:
            > < = ....
        Case:
            select 
                * 
            from 
                employees 
            where 
                salary > 12000;
    2. Brush Selection by Logical Operators
        Logical operators:
            && || !
        mysql recommends:
            and or not
        Case:
            select 
                * 
            from 
                employees 
            where 
                salary > 12000 and salary < 20000;
    3. Fuzzy Query
        1. Keyword:like
        Characteristic:
            General and Wildcard Use
                %Any number of characters, including 0 characters
                _Any number of characters
        Case: Query employee information with character a in employee name
            select 
                *
            from
                employees
            where
                last_name like '%a%';
        Special case (escape is required): query employee information whose second character is _in employee name
            select 
                *
            from
                employees
            where
                last_name like '_$_%' escape '$';
        2. Keyword: between and
        Characteristic:
            Contains a critical value
        Case:
            select 
                * 
            from 
                employees 
            where 
                salary between 12000 and 20000;
        3. Keyword:in
        Case:
            select 
                * 
            from 
                employees 
            where 
                job_id in ('IT_PROT','AD_VP','AD_PRES');
        4. Keyword: is null
        Note: The = sign does not determine the null value
        Case:
            select
                *
            from 
                employees
            where
                commission_pct is null;

2, Introduction of two special symbols (<>, <=>)

1. <>Equivalent!=, but mysql recommends overrides
 2. <=>Safe equals, you can judge null value

Stage 3: 1, Sort Query

Grammar:
    select 
        query criteria
    from 
        surface
    [where filter]
    order by
        Sort List [asc|desc]
Be careful:
    asc: ascending order
    desc: descending order
    Do not write: default ascending order
 Cases (from high to low desc):
    select
        *
    from
        employee
    order by
        salary desc;
Cases (from low to high asc):
    select
        *
    from
        employee
    order by
        salary asc;
Special Column: Show employee's name and salary by byte length of name (last_name by function)
    select 
        length(last_name) byte length, last_name,salary
    from 
        employees
    order by 
        length(last_name) desc;

2, Multiple Field Sorting

1.Case: Querying employee information requires sorting by salary and then by employee number [sorted by multiple fields]
    select 
        *
    from
        employees
    order by
        salary asc,employee_id desc;

Stage 4: Common functions

1. Call: select function name (argument list) [from table];
2. Classification: One-line function, aggregate function

1, Character function

1.length(character) Number of bytes to get parameter values
    select length('join');
    select length('Ha');
2.concat(Parameter 1,Parameter 2,...) Split String
    select concat(last_name,'_',first_name) Full name from employees;
3.upper(character) lower(character)
    select upper('dadada');
    select lower('HJJJI');
4.substr(character,Position 1,Position 2)，substring(character,Position 1,Position 2) Intercept Characters
    //Note: Index in mysql starts from 1
        select substr('hduxshdfkjsf',2,6);
5.instr(Character, the string to find) Play back the index of the first occurrence of the substring, if no playback 0 is found
    select instr('dsfsfd','fs');
6.trim(character) Remove Front and Back Spaces
    select length(trim(' Long memory ')) as out_put
//Other uses: Remove specified characters before and after
    trim(character from Character string);
    select trim('a' from 'aaaaaaaaa Perhaps first aaaaaacxhddjkhaaaaaaaaa') as out put;
7.lpad(character,Number,Fill Character) Fills the specified length left with the specified character
    select lpad('xgp',2,'*') as out_put;
8.rpad(character,Number,Fill Character) Fills the specified length right with the specified character
    select rpad('xgp',12,'*') as out_put;
9.replace(Original string,To replace characters, replace characters) replace
    select replace('xgp123','xgp','123') as oup_put;

2. Mathematical functions

1.round(numerical value,Keep decimal places) Rounding
    select round(1.65);
    select round(1.675,2);
2.ceil(numerical value) ceil
    select ceil(1.02);
3.floor(numerical value) Rounding Down
    select floor(-9.99);
4.truncate(Number, keep several decimal places) truncation
    select truncate(1.66,1);
5.mod(Remainder taken, Remainder taken) Remaining
    select mod(-10,-3);

3, Date function

1.now() Date put back to current system+time
    select now();
2.curdate() Play back the current system date, excluding time
    select curdate();
3.curtime() Play back the current system time, excluding the date
    select curtime();
4.You can get the specified part, year, month, day, hour, minute, second
    select year(now()) year;
5.str_to_date(Format, Character Type) Converts a character in date format to a date in a specified format
    select str_to_date('1998-3-2','%Y-%c-%d');
6.date_format(Format, Character Type); Convert Date to Character
    select date_format(now(),'%y year%m month%d day');

4, other functions

1.select version(); view mysql version
 2.select database(); view the current database
 3.select user(); view current user

5. Process Control Functions

1. Effect of if (judgment condition, if result, else result) function if else
    Select if (10>5,'big','small');
2. Use of the case() function
    The field or expression to be determined by the case
    when constant 1 The value 1 or statement 1 to be displayed
    when constant 2 The value 2 or statement 2 to be displayed
    when constant 3 The value 3 or statement 3 to be displayed
    ......
    Value n or statement n to be displayed by else
    end

Case: Query employee salary, request
    Department number = 30, showing 1.1 times salary
    Department number = 40, showing 1.2 times salary
    Department number = 50, showing 1.3 times salary
    Other departments, shown as original wages

    SELECT 
        Salary original salary, department_id,
    CASE 
        department_id = 30 
    WHEN 30 THEN salary*1.1
    WHEN 40 THEN salary*1.2
    WHEN 50 THEN salary*1.3
    ELSE 
        salary
    END 
        AS New Wages
    FROM 
        employees;
3. Use of case() function 2
    case 
    when condition 1 The value 1 or statement 1 to be displayed
    when condition 2 The value 2 or statement 2 to be displayed
    ......
    Value n or statement n to be displayed by else
    end

Case: Query the salary of an employee
    If the salary is > 20000, A
    If the salary is >15000, B
    If salary > 10000, C
    Otherwise D

    SELECT salary,
    CASE 
    WHEN salary > 20000 THEN 'A'
    WHEN salary > 15000 THEN 'B'
    WHEN salary > 10000 THEN 'C'
    ELSE 'D'
    END AS Wage Level
    FROM employees;

6. Aggregate function

1.sum (field) summation avg() mean min() minimum max() maximum count() count
    select sum(salary) from employees;
2. Features:
    1.sum(),avg() supports numeric types
        min(),max(),count() supports any type
    2. The above aggregation functions exclude null values
 3. Use with distinct
    select count(distinct salary),count(salary) from employees;
4. Detailed description of count() function
    select count(*) from employees;
    select count(1) from employees;
    Efficiency:
        High count(*) efficiency with MYISAM storage engine
        Under the INNODB storage engine, count(*) and count(1) are nearly as efficient, and are slightly more efficient than count (field)
5. Normally no fields will be used with aggregate functions

Phase 5:1, Group Query

1. Syntax:
    select grouping function, column (required after group by)
    from table
    [where filter]
    List of group by groupings
    [order by clause]
2. Note:
    Query lists must be special, requiring fields to appear after the grouping function and group by
 3. Case 1: Query the maximum wage per job
    select max(salary),job_id 
    from employees
    group by job_id;

2, Filter before adding groups

Case 1: Query mailbox for inclusion a Character, average wage per department
    select 
        avg(salary),department_id 
    from 
        employees
    where
        email like '%a%' 
    group by
        department_id;
//Case 2: Query the maximum salary of each supervisor's employees with a bonus
    select 
        max(salary),manager_id
    from 
        employees
    where 
        commission_pct is not null 
    group by
        manager_id;

3, filter after adding grouping

1.Case 1: Query which department has the number of employees>2
    1.Query the number of employees per department
        select 
            count(*),department_id 
        from
            employees
        group by 
            deparment_id;
    2.Based on the results of 1, query which department has the number of employees>2
        select 
            count(*),department_id 
        from
            employees
        group by 
            deparment_id;
        having 
            count(*)>2;
2.Case 2: Query the maximum salary of employees with bonuses for each type of work>12000 Type number and maximum wage
    select
        max(salary),job_id
    from 
        employees
    where
        commission_pct is not null
    group by 
        job_id
    having
        max(salary) > 12000;

4, grouped by multiple fields

1.Case: Query the average salary of employees per job in each department
    select 
        avg(salary),department_id,job_id 
    from
        employees
    group by
        deparentment_id,job_id;

5, Add Sort

1.Case: Query the average salary of employees for each type of work in each department and show it by average salary
    select 
        avg(salary) a,department_id,job_id
    from 
        employees
    where 
        department_id is not null
    group by
        job_id,department_id
    having
        avg(salary) > 10000
    order by 
        avg(salary) desc;

Stage 6:1, Join Query (Multi-Table Query)

Cartesian product phenomenon: no valid connection condition

How to avoid: adding valid connection conditions

Give an example:
    SELECT 
        NAME,boyName 
    FROM 
        boys,beauty
    WHERE 
        beauty.boyfriend_id = boys.id;

Classification:

By age:
    sql92 standard: only internal connections are supported
    sql99 Standard [Recommended]: Supports internal connection + external connection (left and right outer) + cross connection
 By function:
    Internal connection:
        Equivalent Connection
        Non-Equijoin
        Self-connection
    External connection:
        Left Outer Connection
        Right Outer Connection
        External connection
    Cross Connection

2, sql92 standard

1, equivalence connection

1.Case 1: Query employee name and corresponding department name
    SELECT 
        last_name,department_name
    FROM
        employees,departments
    WHERE
        employees.department_id = departments.department_id
2.Case 2: Query the type name and number of employees for each type of work, in ascending order by number of employees
    SELECT 
        job_title,COUNT(*)
    FROM 
        employees e,jobs j
    WHERE 
        e.job_id = j.job_id
    GROUP BY
        job_title
    ORDER BY
        COUNT(*) DESC;
3.Case 3: Query employee name, department name and city (three table query)
    SELECT
        last_name,department_name,city
    FROM 
        employees e,departments d,locations l
    WHERE
        e.department_id = d.department_id
    AND
        d.location_id = l.location_id
    AND
        city LIKE 's%'
    ORDER BY
        department_name DESC;

2, non-equivalent connection
Case 1: Query the salary and salary level of an employee

    SELECT
        salary,grade_level
    FROM
        employees e,job_grades g
    WHERE
        salary BETWEEN g.lowest_sal AND g.highest_sal;

3, self-connection
1. Case: Query employee name and Supervisor Name

    SELECT 
        e.employee_id,e.last_name,m.employee_id,m.last_name,m.department_id
    FROM
        employees e,employees m
    WHERE 
        e.manager_id = m.employee_id;

3, sql99 standard

1. Syntax:
    select
        Query List
    from 
        Table 1 Alias connection type
    join
        Table 2 Alias
    on
        Connection Conditions
    [where
        Filter Criteria]
    [group by
        Grouping]
    [having
        Filter Criteria]
    [order by
        Sort List]

2. Connection type
    Inner Connection (*):inner
    External connection:
        Outside Left (*):left [outside]
        Outside Right (*):right [outside]
        External: full [outer]
    cross join

1, internal connection

1. Syntax:
    select
        Query List
    from
        Table 1 Alias
    inner join
        Table 2 Alias
    on 
        Connection Conditions