SQL classification summary, SQL introduction and classification

Standard answer: select ,,sum(case when use status=.)'In use'then 1 else 0 end) in use, sum(case when use status='Unused'then 1 else 0 end) is not used.

from a,b where

group by ,

select Table A*, Table BIn use, Table BUnused

from table a left join

select dc_id,sum( case dc_name when 'In use'then 1 else 0 end ) as in use, sum( case dc name when'Unused'then 1 else 0 end ) as unused.

from Table B

group by dc_id

as table bon table = table.

-> Test data: @a

declare @a table (dc_id varchar(2),dc_name varchar(3))

insert into @a

select 'a1','aaa' union all

select 'a2','aab'

Test data: @b

declare @b table (dp_id varchar(2),dc_id varchar(2),use_status varchar(6))

insert into @b

select 'b1','a1','In use' union all

select 'b2','a1','Unused' union all

select 'b3','a2','In use'

select, unused = sum(case when ='Unused' then 1 else 0 end)

Use = sum(case when ='In use' then 1 else 0 end)

from @a a

inner join @b b on =

group by , dc id dc name is not used.

a1 aaa 1 1

a2 aab 0 1

The number of rows affected is 2).

SQL (Structured Query Language) is a standard computer language used to access and process databases. A programming language that operates relational databases, and defines a uniform standard for operating all relational databases.

Universal grammar

SQL statements can be written on a single line or multiple linesEnds with a semicolon

It's not case-sensitive, but uppercase is recommended for keywords.

Notes:Single-line comments: - Comment content (-- must be followed by a space) (You can also use comment content under MySQL).

Multi-line comments: Comments *

SQL classification

DDL (Data Definition Language): A data definition language used to define database objects, such as databases, tables, and columns.

Data Manipulation Language (DML) is a data manipulation language used to add, delete, and modify data in tables in a database.

Data Query Language (DQL) is used to query the records (data) of tables in a database

Data Control Language (DCL) is used to define the access and security levels of databases, and to create users.

Query all databases

Create a database

Delete the database

Use databases

After the database is created, to create a table in the database, you must first specify which database to operate in, and then you need to use the database.

View the database currently in use

Manipulating a table is to add, create, retrieve, update, and delete a table.

Query the names of all tables in the current database

Query the table structure

Create a table

MySQL supports several types and can be divided into three categories:

Delete the table

Determine whether a table exists when you delete a table

Change the table name

Add a column

Modify the data type

Modify the column name and data type

Delete columns

Add, delete, or update data is performed.

Add data to the specified column

Add data to all columns

Add data in bulk

Modify table data

Deletion of data