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.

sql classification and syntax

Classification:

DDL (Data Definition Language)

Used to manipulate databases and tables

Keywords: create,drop,alter, etc
DML (Data Manipulation Language)

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

Keywords: insert,delete, update, etc
DQL (Data Query Language)

Used to query the data of tables in the database

Keywords: select, from, where, etc
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
TCL (Transaction Control Language)

Transaction operations used to control the database

Keywords: commit, rollback, etc

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 / * * /

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;

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;

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:

Values and fields must correspond, with the same number and type
The data size of the value must be within the length of the field
Except for the numeric type, the values of other field types must be enclosed in quotation marks.
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

DQL (data query language)

Query order, condition query and operation query

select....from...where..group 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

Range query, fuzzy query, sort query

Range query:

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

Fuzzy query like

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

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,

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

Execution effect:

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
count(1) includes ignoring all columns. 1 represents the code line. In the statistical results, the column value is NULL
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:

If there is a primary key, the execution efficiency of count (primary key) is the best
If the column name is the primary key, count (column name) will be faster than count(1)
If the column name is not a primary key, count(1) will be faster than count (column name)
If the table has multiple columns and a primary key or index, the count(*) system will automatically optimize the primary key or index
If the table has multiple columns and no primary key, the execution efficiency of count(1) is better than count(*)

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

Paging query

limit Number of skips,Number of queries;

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');

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

Primary key constraint: primary key

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

features:

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

Format:

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;

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

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

Multi table

Design and implementation of multi table

There are three types:

one-on-one

realization:

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

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

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;

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.

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

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

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

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

Subquery

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;

Example:

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.*,d.name from dept d,(select * from emp where join_date>'2011-01-01') l
where d.id=l.dept_id;
```

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

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.