SQL statement overview and quick query

Posted by guanche on Wed, 22 Dec 2021 08:43:14 +0100

1, SQL overview

SQL (structured Query Language) is a structured Query Language used to access, query, update data and manage relational database systems

1.1 SQL development

SQL was introduced by IBM in 1981. Once it was applied in the database, it became the general specification of the database
The specification of SQL is determined by ANSI organization
Different databases comply with the general specification of SQL in their products, but they also have special instructions for SQL, which has improved their own production

1.2. SQL classification

The database operation completed according to the SQL instruction can be divided into four categories

DDL Data Definition Language
- Used to manage (create, delete, modify) database objects (database, data table, view, index, etc.)
DML Data Manipulation Language
- It is used to add, delete and modify the data in the data table
DQL Data Query Language
- It is used to query the data in the data table
DCL Data Control Language
- Used to complete control operations such as transaction management

2, SQL basic syntax

In command line Client or navicat of MYSQL

SQL is not case sensitive
Each SQL instruction ends with; ending
SQL instructions are separated by spaces
There is no restriction on line breaks between SQL

3, DDL

query data base

# Displays a list of all databases in mysql
show databases;

# Displays the SQL instruction to create the database with the specified name
show create database db_test;

Create database

# Create a database named db_test database
create database db_test;

# Create a database named db_test database if the database does not exist
create database if not exists bd_test;

# Specify the character set of the database while creating the database (character set: the encoding format utf8 gbk in which the data is stored in the database)
create database <dbName> character set utf8;

Delete database

# Deleting a database will delete all data tables and all data in the database
drop database <dbName>;

# Delete a database if it exists
drop database if exists <dbName>;

modify the database

# Modify the character set of the database
alter database <dbName> character set gbk;

Switch database

# Switch to the desired database to create the data table
use <dbName>;

Create data table

A data table is actually a two-dimensional table. A table is composed of multiple columns

create table students(
    stu_num char(8) not null unique,
    stu_name varchar(20) not null,
    stu_gender char(2) not null,
    stu_age int not null,
    stu_tel char(11) not null unique,
    stu_qq varchar(11) unique
);

Query data table

# Displays a list of all tables in the current database
show tables;

# Displays the SQL instruction to create a table with the specified name
show create database <tableName>;

Query table structure
```
desc <tableName>;
```
Delete data table
```
drop table <tableName>;
```

Modify data table

# Modify table name
alter table <tableName> rename to <newTableName>;

# The data table also has a character set, and the default character set is consistent with the data table
alter table <tableNmae> character set utf8;

# Add field
alter table <tableName> add <columnName> varchar(10)

# Modify the field name and type of the field
alter table <tableName> change <oldColumnName> <newColumnNam> <type>;

# Modify only field types
alter table <tableName> modify <ColumnName> <type>

# Delete field
alter table <tableName> drop <ColumnName>

# Delete primary key constraint in table
alter table <tableName> drop primary key

4, MYSQL data type

Data type refers to the data type supported in the data table

value type

type	storage space	Range	explain
tinyint	1byte	Signed: - 128 ~ 127 Unsigned: 0-255	Extra small integer (eg: age)
smallint	2byte	Signed: - 32768 ~ 32767 Unsigned: 0-65535	Small integer
mediumint	3byte	Signed: - 223 ~ 223-1 Unsigned: 0-2 ^ 24-1	Medium integer
int/integer	4byte	Signed: - 231 ~ 231-1 Unsigned: 0-2 ^ 32-1	integer
bigint	8byte	Signed: - 263 ~ 263-1 Unsigned: 0-2 ^ 64-1	Large integer
float	4byte		Single precision
double	8byte		Double precision
decimal	First parameter + 2		decima(10,2) has 10 people in total Decimal places occupy 2 places

Character type

type	Length range of character sequence	explain
char	0-255 bytes	Fixed length string, which can store up to 255 characters; When we specify a data table character The segment is char(n), and the maximum length of data in this column is n characters. If the added data If it is less than N, add '\ u0000' to the length of n
varchar	0-65535 bytes	Variable length string with a maximum length of 65535 characters
tinyblob	0-255 bytes	Store binary strings
blob	0-65535 bytes	Store binary strings
mediumblob	0-1677215 bytes	Store binary strings
longblob	0-4294967295 bytes	Store binary strings
tinytext	0-255 bytes	Text data (string)
text	0-65536 bytes	Text data (string)
mediumtext	0-1677215 bytes	Text data (string)
longtext	0-4294967295 bytes	Text data (string)

Date type

type	format	explain
data	2021-09-13	Date, only the date is stored
time	11:12:13	Event, only hours, minutes and seconds are stored
year	2021	particular year
datatime	2021-09-13 11:12:13	Date time storage year month day hour minute second
timestamp	20210913 111213	Date time (timestamp)

5, Field constraints

When creating a data table, specify the data restriction requirements for the columns of the data table (restrict the data of the columns in the table)

Why add constraints to ensure the validity, integrity and correctness of data

Non NULL constraint. The value of the restriction column must be provided. It cannot be null. The default value is
unique constraint: multiple pieces of data in the table, and the value of this column cannot be repeated
The primary key constraint is non empty + unique and can uniquely identify a piece of data in the data table
- A primary key is the unique identifier of a record in a data table. There can be at most one primary key in a table
```
# Define primary keys when creating tables
create table book(
	book_isdn char(4) primary key,
    book_name varchar(10) not null,
    book_author varchar(10)
);

# Or define a primary key separately
create table book(
	book_isdn char(4),
    book_name varchar(10) not null,
    book_author varchar(10),
    primary key(book_isdn)
);

# Or delete the primary key constraint after creating the table
alter table <tableName> drop primary key;

# Or add a primary key constraint after creating the table
alter table <tableName> modify <columnName> <type> primary key;
```
- When we create a data table, If there is a column in the data table that can be used as the primary key (for example, the student number of the student table), we can directly set the table as the primary key. When there is no suitable column in some data tables as the primary key, we can define an additional column (ID) as the primary key of the data table, the data in this column has no specific meaning. It is mainly used to mark a record. In MYSQL, we can define this column as int type and set it to automatic growth. When we add a record to the data table, there is no need to provide the value of ID column, and it will be generated automatically.
  
  Automatic growth starts from 1. Every time a record is added, the automatically growing column will be + 1. We delete a piece of data and then add data. The automatically growing data will not be generated repeatedly (automatic growth only ensures uniqueness, not continuity).
```
# Define automatic growth of primary key
create table books(
    book_id int primary key auto_increment,
	book_isdn char(4),
    book_name varchar(10) not null,
    book_author varchar(10)
);
```
- A primary key can be a field or a combination of multiple fields
  
  In the database design of an actual enterprise project, the use frequency of the federated primary key is not high, and an ID can be defined directly
```
create table book(
	stu_num char(8) not null,
    course_id int,
    score int not null,
    primary key(stu_num,course_id)
);

## Note that cannot be defined at creation time
## The following is wrong
create table book(
	stu_num char(8) not null primary key,
    course_id int primary key,
    score int not null
);
```
The foreign key constraint is used to establish the association relationship between different tables
- Explanation of multi table Association

6, DML

It is the same as completing the operation on the data in the data table

insert data

# Add data to the specified column in the data table. Columns that are not allowed to be empty must be added
insert into <tableName>(columnName,columnName......) values(valuse1,value2......);

# When you want to add data to all columns in the table, you can delete the field list
insert into <tableName> values(valuse1,value2......);

# for instance
insert into students(stu_num,stu_name,stu_age,stu_gender,stu_tel,stu_qq)
values('20202021','zhangsan',21,'m','13091370333','123456');

# In the actual project development, even if you want to add data to all fields, do not omit the good of the field list, so as to enhance the compatibility and stability of SQL

Delete data

Delete data that meets specific conditions from the data table

# Delete records that meet the conditions
delete from <tableName> where age=20

# Delete all data in the current table
delete from <tableName>

Modify data

update <tableName> set <columnName> = value [where...] 

# eg modification example
update students set stu_name='tom' where stu_qq=456789;

# eg modify multiple instances, separated by commas
update students set stu_tel=1231231321,stu_qq=456456 where stu_num=20210103;

7, DQL

Intercept records that meet specific conditions from the data table

Single table query
Multi table joint query

7.1 basic syntax of query

# Query the data of this field from this table
select <columnName> from <tableName>

# eg query a column
select stu_num from students;
# eg query multiple columns
select stu_num,stu_name from students;
# eg query all columns
select * from students;

7.2,where

After deleting the modified and queried statements, you can add a where clause. The condition is used to filter the data that meets the specific conditions, and is used to delete, modify and query operations

Operator	explain	eg
=	Equal to, exact match	where stu_num=20188888
!=	Not equal to	where stu_num!=20188888
<>	Not equal to	where stu_num<>20188888
>	greater than	where stu_num>20188888
<	less than	where stu_num<20188888
>=	Greater than or equal to	where stu_num>=20188888
<=	Less than or equal to	where stu_num>=20188888
between...and...	Interval query	where stu_num between 20188888 and 20187878

7.3. Multi criteria query

# Multi condition satisfaction
select * from students where stu_num=20188888 and stu_age=22;

# Satisfy any condition
select * from students where stu_num=20188888 or stu_age=22;

# not negative
select * from students where stu_age not between 21 and 22;

7.4. Fuzzy query

You can use the like keyword in the condition of the while clause

# grammar
# %Represents any number of characters, '% o%' contains o
# _ Represents any character, '_ O% ', that is, the second character is o
select * from <tableName> where <columnName> like '%o%';

# eg filters out those whose names contain o
select * from students where stu_name like '%o%';

7.5. Processing of query results

Set query column

Declares the specified column that displays the query results

# grammar
select <columnName1>,<columnName1>... from <tableName> where <direction>;

Calculation column

The column of the record queried from the data table is displayed after certain calculation
```
# eg
select 2021-stu_age from students where stu_name like '%o%';
```
field alias

We can choose a more semantic name for the column name of the query result
```
# eg
select 2021-stu_age as birth from students where stu_name like '%o%';
```
eliminate duplicate records

From the query results, duplicate records are eliminated
```
select distinct stu_age from students;
```

sort

Sort the queried values in ascending or descending order

# If eg is not declared, it is in ascending order, and asc is the default
select * from students where stu_age>20 order by stu_gender;

# Can be declared with desc
select * from students where stu_age>20 order by stu_gender desc;

# If there are multiple fields, the first sorting rule is satisfied first, and then the second sorting rule is satisfied
select * from students where stu_age>20 order by stu_gender desc,stu_age asc;

7.6 aggregation function

SQL provides some functions that can calculate the columns of queried records

count()

Count the number of specified fields that meet the conditions

# eg
select count(stu_num) from students;
# eg
select count(stu_num) from students where stu_gender='m'

max()

Query the maximum value of the specified column in the records that meet the criteria

# eg
select max(stu_num) from students;
# eg
select max(stu_num) from students where stu_gender='m';

min()

Query the minimum value of the specified column in the records that meet the criteria

# eg
select min(stu_num) from students;
# eg
select min(stu_num) from students where stu_gender='m';

sum()

Calculate and query the sum of the specified columns in the records that meet the conditions

# eg
select sum(stu_num) from students;
# eg
select sum(stu_num) from students where stu_gender='m';

avg()

Calculate and query the average value of the specified column in the records that meet the criteria
```
# eg
select avg(stu_num) from students;
# eg
select avg(stu_num) from students where stu_gender='m';
```

7.7 date function and string function

When we add data to a date type column, we can assign a value through a string (the format of the string is fixed)

To get the current time, we can use now(),sysdate()

# Query current time
select now();

# Query current system time
select sysdate();

Processing strings through sql instructions

# String splicing
select concat('df','edu');
select concat(stu_name,'-',stu_gender) from students;

# Convert to uppercase, convert the value of the field to uppercase, and Chinese will not be changed
select upper(stu_name) from students;

# Convert to lowercase, convert the value of the field to uppercase, and Chinese is not moved
select lower(stu_name) from students;

# Intercept the string and intercept 4 bits from 7
select stu_name,substring(stu_tel,7,4) from students;

7.8 group query

Grouping - is to group the records in the data table according to the specified columns

# grammar
select Grouping field/Aggregate function from Table name [where directions] group by Group column names;

# eg grouped by gender 
select * from students group by stu_gender;

After select, use * to group the query results, and only the first record will be obtained. Such results often have little meaning

After select, the grouped fields and aggregation functions are usually displayed to make statistics on the grouped data

# eg statistics by sex
select stu_gender,count(stu_gender) from students group by stu_gender;

Remember the SQL mantra: select from where group by having order by

7.9 paging query

When there are many records in the data table, if all the records are displayed to the user at one time, the readability of the user is not good, so we can display them in pages

# The students in the data table are displayed in pages. The first parameter of limit represents where to start, and the second parameter represents several data on each page
select * from students limit 0,3;

directions] group by group column name;

eg grouped by gender

select * from students group by stu_gender;

`select`Post use`*`Grouping the query results will only get the first record, which is often meaningless

`select`The grouped fields and aggregate functions are usually displayed after grouping to make statistics on the grouped data

```sql
# eg statistics by sex
select stu_gender,count(stu_gender) from students group by stu_gender;

Remember the SQL mantra: select from where group by having order by

7.9 paging query

When there are many records in the data table, if all the records are displayed to the user at one time, the readability of the user is not good, so we can display them in pages

# The students in the data table are displayed in pages. The first parameter of limit represents where to start, and the second parameter represents several data on each page
select * from students limit 0,3;

Topics: Database MySQL SQL

Programmer Think

SQL statement overview and quick query

1, SQL overview

1.1 SQL development

1.2. SQL classification

2, SQL basic syntax

3, DDL

4, MYSQL data type

5, Field constraints

6, DML

7, DQL

7.1 basic syntax of query

7.2,where

7.3. Multi criteria query

7.4. Fuzzy query

7.5. Processing of query results

7.6 aggregation function

7.7 date function and string function

7.8 group query

7.9 paging query

eg grouped by gender

7.9 paging query

Hot Topics