Detailed explanation of SQL language foundation

Posted by jasoncable on Tue, 11 Jan 2022 16:25:43 +0100

sql language

Overview and function

**Overview: * * structured query language is abbreviated as SQL, and SQL statement is a language for database operation.

**Function: * * you can add, delete, modify and query the data in the database, tables and tables in the database management system through Sql language.

<br/ >

sql classification and syntax


  1. DDL (Data Definition Language)

    Used to manipulate databases and tables

    Keywords: create,drop,alter, etc

  2. DML (Data Manipulation Language)

    It is used to add, delete and modify the data of tables in the database

    Keywords: insert,delete, update, etc

  3. DQL (Data Query Language)

    Used to query the data of tables in the database

    Keywords: select, from, where, etc

  4. DCL (Data Control Language)

    It is used to define the access rights and security levels of the database and create users.

    Keywords: grant, revoke, etc

  5. TCL (Transaction Control Language)

    Transaction operations used to control the database

    Keywords: commit, rollback, etc

<br/ >

sql syntax:

  • SQL statements can be written in one or more lines, ending with semicolons
  • Spaces and indents can be used to enhance the readability of statements
  • The QL keyword itself is case insensitive. The names of tables, columns, etc. are case sensitive related to the database
  • Comments can be done using – or # or / * * /

<br/ >

DDL (data definition language)

Operation database

1 - create database

-- Create a database. Default: utf-8	
create database Database name;
-- Determine whether the database exists and create it. Existing: not created; Does not exist: created. Default: utf-8
create database if not exists Database name;
-- Creates the specified character set for the database. Generally not
create database Database name character set character set(gbk);

2 - query database

-- Query all databases
show databases;

-- View definition information of a database
show create database Database name;

3 - delete database

-- Delete database
drop database Database name;

4 - other database operations

-- View the database currently in use
select database();

-- Switch databases. Note: if you want to operate which library (create a table), you must first enter the database
use Database name;

<br/ >

Operation table

Note: if you want to operate which database (create a table), you must first enter the database: use database name;

1 - create table (important)

-- Delete the table if it exists
drop table IF EXISTS Table name;
-- Create database tables and define table column properties.	-- ps: There will be keywords for the table name. Solution: change the name.	
create table Table name(
	Field name 1 field type 1(length),		-- ps: Only string types need to be manually lengthened, and other types do not need to be lengthened(Default yes)
    Field name 2 field type 2(length)   

-- Create empty table
create table Table name;

-- Example: Create a student table(id name sex)
create table stu(
    id int,
    name varchar(10),
    sex varchar(2),
    price double,
    time date		    		

-- data type
java Data type for      mysql Data type of database
    int		    		int
    float		    	float
    double		    	double
    char/string	    	varchar

    date		    	datetime  YYYY-MM-DD HH:MM:SS  
                        date      yyyy-MM-dd
                        time      HH:MM:SS

2 - view table

-- View all tables in a database
show tables;

-- View table structure
desc Table name;

-- Query table creation statement
show create table Table name;

-- Quickly create a table structure. Features: the structure of the two tables is the same
create table New table name like Old table name;

3 - modify table structure

-- Add table column
alter table Table name add Column name type;
-- Delete table column
alter table Table name drop Listing;

-- Modify column name
alter table Table name change Old name new name type;
-- Modify column type
alter table Table name modify New type of column name

-- Modify table name
rename table Table name to New table name;

4 - delete table

-- Delete table directly
drop table Table name;
-- Delete the specified table name when it exists. It is often used before initializing the database and creating tables
drop table if exists Table name; 

<br/ >

DML (data manipulation language)

1 - insert record

-- Add some fields
insert into Table name (Field name 1, Field name 2, ...) values(Value 1, Value 2, ...);

-- Add all fields in the order defined by the column
insert into Table name values (Value 1, Value 2, Value 3);  

matters needing attention:

  1. Values and fields must correspond, with the same number and type

  2. The data size of the value must be within the length of the field

  3. Except for the numeric type, the values of other field types must be enclosed in quotation marks.

  4. If you want to insert a null value, you can either not write the field or manually insert null

2 - worm replication

-- Insert the data copy from table 2 into table 1. Premise: consistent table structure
insert into Table name 1 select * from Table name 2;

3 - modification record

-- Modify data according to conditions. Features: change according to conditions
update Table name set Field name 1 = Value 1, Field name 2 = Value 2 where Field name=value ;
-- Modify data without conditions. Features: all corresponding fields in the table are changed
update Table name set Field name 1 = Value 1, Field name 2 = Value 2 ;

4 - delete record

-- Delete data according to conditions. Features: delete by conditions
delete from Table name where Field name=value; 
-- Delete data without conditions. Features: delete all
delete from Table name;  
-- truncate Delete table records.
truncate table Table name;  

The difference between truncate and delete:

  • delete deletes the data in the table one by one. As like as two peas, truncate destroys the entire table and recreates a new table. The new table structure is exactly the same as the original table structure.

  • delete the deleted data can be retrieved. truncate deleted data cannot be found

<br/ >

DQL (data query language)

Query order, condition query and operation query by...having...order by...limit a,b	

Simple query

-- Query all columns
select * from Table name;

-- Query specified column
select Field name 1,Field name 2... from Table name; 

-- Alias query
select Field name 1 as alias, Field name 2 as alias... from Table name as Table alias; 

-- Clear duplicate values
select distinct Field name from Table name;

-- The query results participate in the operation. The field of parameter operation must be numeric
select Column name 1 + Fixed value from Table name;

Condition query:

select * from Table name where Conditions (various operators); 

Operation query:

>   greater than 
<   less than 
<=  Less than or equal to 
>=  Greater than or equal to 
=   be equal to 
<> , !=  Not equal to

and(&&) 	Multiple conditions are met at the same time 
or(||) 		Multiple conditions, one of which is satisfied 
not(!) 		dissatisfaction

<br/ >

Range query, fuzzy query, sort query

Range query:

between Value 1 and Value 2
in(Value 1, Value 2, Value 3, ...)

Fuzzy query like

	%  : Fuzzy multi bit wildcard
	_  : Fuzzy one bit wildcard

is null
is not null

Sort query order by field

order by Field name [desc | asc] , Field name [desc | asc] ;		-- asc(default): In ascending order, desc: Descending order

<br/ >

Aggregate function query

count() : count

sum(field) : Sum
max(field) : Find the maximum value
min(field) : Find the minimum value
avg(field) : Average

ifnull(field, Standby value) : If the first parameter is not NULL,This value is returned; if it is NULL,Returns an alternate value,

<br/ >

Differences between count(1), count(*) and count (column name)

Execution effect:

  1. count(*) includes all columns, which is equivalent to the number of rows. The column value of NULL will not be ignored in the statistical results

  2. count(1) includes ignoring all columns. 1 represents the code line. In the statistical results, the column value is NULL

  3. Count (column name) only includes the column name. In the statistical results, the count of column values that are empty (the empty here is not only an empty string or 0, but represents null) will be ignored, that is, when a field value is null, no statistics will be made.

Execution efficiency:

  1. If there is a primary key, the execution efficiency of count (primary key) is the best

  2. If the column name is the primary key, count (column name) will be faster than count(1)

  3. If the column name is not a primary key, count(1) will be faster than count (column name)

  4. If the table has multiple columns and a primary key or index, the count(*) system will automatically optimize the primary key or index

  5. If the table has multiple columns and no primary key, the execution efficiency of count(1) is better than count(*)

<br/ >

Grouping query and paging query

Grouping query

group by Grouping field [having condition]

-- Example: query people over 25 years old,Grouping by sex,Count the number of people in each group,And only the data with the total number of people greater than 2 are displayed
select sex, COUNT(*) from student where age>25 group by sex having COUNT(*)>2;

The difference between having and where

  • having is to filter the data after grouping; where is to filter data before grouping

  • Aggregate functions can be used after having; Aggregate functions cannot be used after where

<br/ >

Paging query

limit Number of skips,Number of queries;

<br/ >

DCL (data control language)

-- Create user
-- Command format:
CREATE USER 'user name'@'Host address' IDENTIFIED BY 'password';	-- By default, you can't do anything without any permission
-- Example:
CREATE USER 'tom'@'localhost' IDENTIFIED BY '123';

-- Assign permissions to users
-- Command format:
GRANT Authority 1, Authority 2... ON Database name.Table name TO 'user name'@'host name';
-- Example:
GRANT ALL ON db3.* TO 'tom'@'localhost';

-- View permissions
-- Command format:
SHOW GRANTS FOR 'user name'@'host name';
-- Example:
SHOW GRANTS FOR 'tom'@'localhost'

-- Revoke permissions
-- Command format:
REVOKE Authority 1, Authority 2... ON database.Table name FROM 'user name'@'host name';
-- Example:
REVOKE ALL ON db3.* FROM 'tom'@'localhost';

-- delete user
-- Command format:
DROP USER 'user name'@'host name';
-- Example:
DROP USER 'tom'@'localhost';

-- to root User change password
-- Features: you can operate the command directly without logging in
-- Format:
mysqladmin -uroot -p password New password

-- Change the password for ordinary users
-- Features: required root Users log in first and then use the command to change the password of ordinary users
-- Format:
set password for 'user name'@'host name' = password('New password');
-- Example:
set password for 'aaa'@'localhost' = password('root');

<br/ >

Database Constraints

Constraints: further restrictions can be made on the data in the table to ensure the uniqueness, correctness and integrity of the data

Constraint type:

  • PRIMARY KEY: PRIMARY KEY constraint. The data representing this field cannot be empty and cannot be repeated
  • NOT NULL: NOT NULL. The data representing this field cannot be empty
  • UNIQUE: UNIQUE. The data representing this field cannot be duplicate

<br/ >

Primary key constraint: primary key

Primary key constraints are required in a table to mark the uniqueness of a record


  • The primary key field value is unique and cannot be repeated
  • Primary key field values cannot contain NULL values
  • There can only be one primary key in a table, but the primary key can be multiple fields (joint primary key)

MySQL implementation

Add primary key

  • Method 1: add constraints when creating tables


    create table Table name ( 
    	Field name 1 field type primary key, 	
    	Field name 2 field type constraint 
  • Method 2: when creating a table, add constraints at the end

    Format 1: single primary key

    create table Table name (
    	Field name 1 field type, 	
    	Field name 2 field type, 	
    	primary key(Field name 1)  

    Format 2: Union primary key

    create table Table name (
    	Field name 1 field type, 	
    	Field name 2 field type, 	
    	primary key(Field name 1,Field name 2)  

Delete primary key

alter table Table name drop PRIMARY KEY;

Primary key auto increment

auto_increment		-- By default AUTO_INCREMENT The start value of is 1

-- Modify starting value
ALTER TABLE Table name AUTO_INCREMENT=Starting value;

<br/ >

Unique constraint: unique

**Features: * * the modified field is unique and cannot be repeated

Note: a table can have multiple unique modified fields, but it has no effect on null

Implementation mode

-- Add after field when creating table 
create table Table name ( 
	Field name 1 field type unique, 
	Field name 2 field type constraint 

<br/ >

Non NULL constraint: not null

**Feature: * * modified field cannot be empty

**Implementation method: * * add directly after the field

create table Table name ( 
	Field name 1 field type not null default 'Default value', 			-- default: Default value
	Field name 2 field type unique not null default 'Default value', 	-- Unique constraints and non null constraints can appear on a field at the same time
    Field name 3 field type constraint 

<br/ >

Multi table

Design and implementation of multi table

There are three types:

  • one-on-one


    1. Let the primary keys of both sides correspond one by one as foreign keys

    2. Create a field on either side as the primary key of the foreign key pointing to the other side, but the foreign key must be unique

  • One to many

    Call one party as the master table and the other party as the slave table

    Establishment principle: as long as it is one to many, create a field from the table (one of the many) as a foreign key, and then make the foreign key point to the primary key of the main table (one of the many)

  • Many to many

    Establishment principle: you need to create an intermediate table externally. The intermediate table needs at least two fields, and then the two fields are used as foreign keys to the primary keys of their respective tables

<br/ >

Multi table constraint (foreign key constraint)

Function of foreign key constraint: it can ensure the integrity and effectiveness of data

Feature: if the data of the primary table is associated with the data of the secondary table, the data of the primary table cannot be deleted, so as to ensure the integrity and effectiveness of the data

<br/ >

Performance issues with foreign keys:

  • The database needs to maintain the internal management of foreign keys;
  • Foreign key means to implement the consistency transaction of data and hand it over to the database server for completion;
  • With a foreign key, after some operations involving the addition, deletion and update of foreign key fields, you need to trigger relevant operations to check, and you have to consume resources;
  • Foreign keys are also prone to deadlock because they need to request internal locking of other tables;

<br/ >

Foreign key usage strategy:

  • In large-scale systems (low performance requirements and high security requirements), use foreign keys;

    In large-scale systems (with high performance requirements and self-control security), foreign keys are not used;

    Small system casually, generally without foreign keys.

  • The use of foreign keys should be appropriate and should not be pursued too much

  • When using programs to control data consistency and integrity without foreign keys, a layer should be written to ensure that each application accesses the database through this layer.

<br/ >

Add foreign key constraint

  • Method 1: add a foreign key constraint on an existing table

    Add a foreign key constraint to the foreign key field from the table

    alter table From table add constraint [Foreign key alias] foreign key(Foreign key field) references Main table name(Primary key field)
  • Mode 2: visual graphics mode

    In the schema designer, you can directly specify a foreign key and drag it to the primary key, which is simple, convenient and fast

Delete foreign key

  • Mode 1:

    ALTER TABLE From table drop foreign key Foreign key name;	
  • Method 2: in the schema designer, select the pointing relationship and right-click to delete it

<br/ >

multi-table query

Multi table query: obtain related data from multiple tables. For example, query classification information and all goods under classification

Syntax classification of multi table query:

  • Internal connection query: only query data related to multiple tables

  • External connection query

  • Subquery

  • Cross query: the product of two tables (Cartesian product) is queried. Both related data and unrelated data are found

<br/ >

Internal connection query

Features: you can use inner join to query data related to two tables

  • Implicit inner connection:

    -- Syntax format:
    select * from Table 1 Table 1 aliases,Table 2 where Association condition and Screening conditions; 	-- ps: You can use table aliases to simplify writing
  • Show internal connections

    -- Syntax format:
    select * from Table 1 [inner] join Table 2  on Association condition where Screening conditions and Screening conditions; 
        -- on: Followed by the association condition
        -- where: Followed by the filter criteria for the results

<br/ >

External connection query

  • Left outer connection: mainly the table on the left of the join. The queried data is the data related to the two tables and all the data in the left table.

    If there is no corresponding data in the right table that meets the conditions, null will be filled in

    -- Syntax format:
    select * from Table 1 left [outer] join Table 2 on Association condition where Screening conditions;
  • Right external connection: mainly the table on the right of the join. The data queried is the data related to the two tables and all the data in the table on the right

    If there is no corresponding data in the left table that meets the conditions, null will be filled in

    -- Syntax format:
    select * from Table 1 rigth [outer] join Table 2 on Association condition where Screening conditions;

<br/ >


Syntax format:

-- Subquery as where Conditions. Applicable to single column single value or single column multi value
select * from Table name where (Subquery results);
-- The subquery acts as a temporary table. Applicable to multiple columns and multiple values
select * from Subquery results where condition;
-- Subquery as result set field
select *,(Subquery results) from Table name where condition;


  • Sub query as an example of where condition: query who is the employee with the highest salary (the result is single column and single value)

    -- Query the highest salary first
    select MAX(salary) from emp;
    -- Then query the employee with the highest salary
    select name from emp where salary=(select MAX(salary) from emp);
  • Sub query is used as an example of where condition: query the Department ID of employees whose salary is greater than 5000 and from which department (the result is single column and multiple values)

    -- First query the employee department whose salary is greater than 5000 ID
    select dept_id from emp where salary>5000;
    -- According to Department id Get department name
    select name from dept where id in (select dept_id from emp where salary>5000);
  • As a temporary example, the sub query finds the information of employees who have been employed since January 1, 2011, including department name (the result is multi column and multi value)

    select l.*, from dept d,(select * from emp where join_date>'2011-01-01') l

<br/ >

Cross connect

select * from Table 1,Table 2;

The product data of two tables will be generated, which is called Cartesian Product data for short. The related data and unrelated data are found

<br/ >

Data backup and restore

mysqldump: MySQL database backup

The mysqldump command is a backup tool in MySQL database. It is used to export the database in MySQL server in standard sql language and save it to a file.

Syntax format:

mysqldump [option] Database name [Table name] > Script name
mysqldump [option] --Database name [Option table name] > Script name
mysqldump [option] --all-databases [option]  > Script name

# Common parameters:
    -h, --host		The server IP address
    -P. --port		Server port number
    -u, --user		MySQL user name
    -p, --pasword	MySQL password
    --databases			Specify the database to back up
    --all-databases		backups mysql All databases on the server
    --compact		Compression mode produces less output
    --comments		Add comment information
    --complete-insert	Output completed insert statement
    --lock-tables		Lock all database tables before backup
    --no-create-db/--no-create-info		Prohibit generating database creation statements
    --force			Continue the backup operation when an error occurs
    --default-character-set		Specifies the default character set
    --add-locks		Locking database tables when backing up database tables
    --ignore-table	Exclude a table when backing up data library

Reference examples:

# Export entire database:
mysqldump -u linuxcool -p smgp_apps_linuxcool > linuxcool.sql
# Export the specified database:
mysqldump -u linuxcool -p smgp_apps_linuxcool users > linuxcool_users.sql
# Back up the specified database (multiple tables are separated by spaces)
mysqldump -uroot -p  mysql db event > /backup/mysqldump/2table.sql
# Export a database structure:
mysqldump -u linuxcool -p -d --add-drop-table smgp_apps_linuxcool > linuxcool_db.sql
# Excludes some tables from backing up the specified database
mysqldump -uroot -p test --ignore-table=test.t1 --ignore-table=test.t2 > /backup/mysqldump/test2.sql

Backup restore command:

mysqladmin -uroot -p create db_name 
mysql -u root -p password db_name < /backup/mysqldump/db_name.db

# Note: before importing the backup database, DB_ If name does not exist, it needs to be created; And with db_name. Only when the database name in DB is the same can it be imported.

k8s deploy regular backup mysql database and restore command:

# Create scheduled task
crontab -e
# Scheduled task (executed at 0:00 every day):
0 0 * * * /usr/local/bin/kubectl exec `/usr/local/bin/kubectl get po | grep mysql | awk '{print $1}'` -- mysqldump -u user -p password db_name > /root/mysql-bak/`date + \%d`.sql 2>&1

# Note: 2 > & 1 means redirecting standard errors to standard output. Here the standard output has been redirected to ~ sql.  Then the standard error will also be output to ~ SQL file

# Restore command:
/usr/local/bin/kubectl exec `/usr/local/bin/kubectl get po | grep mysql | awk '{print $1}'` -- mysql -u user -p password db_name < /root/mysql-bak/`date + \%d`.sql

Topics: Database SQL