Review of SQL Sever basic command

Time:2021-5-8

catalogue

preface

Because the professional teachers of database principles have to take an exam after the May Day holiday, they try to write a note for review and sharing.

Operating environment: Hyper-V virtual machine runs windows sever 2012 R2 + SQL Sever express 2012 SP2
Textbook: Principles and applications of database

Installation of virtual machine

If you are a pure theorist, it’s not impossible not to pretend. You can jump toNext section

Express is a compact version of SQL Sever, which can be used for free.

SQL Server Express 2005 (hereinafter referred to as sqlexpress) is a reduced version of SQL Server 2005 (hereinafter referred to as SQL2005) developed by Microsoft. This version is free. It inherits most of the functions and features of SQL Server 2005, such as security settings, custom functions and procedures, transact SQL, SQL, CLR, etc, It also provides its supporting management software SQL Server Management Studio Express for free.

Sqlexpress has several major limitations:

  1. Only local connections are allowed.
  2. The maximum size of database file is 4GB. This limit only applies to data file (suffix is MDF), but not to log file (suffix is LDF).
  3. Using only one CPU can not make full use of the performance of multi CPU server.
  4. The maximum size of memory available is 1GB.
  5. There is no SQL agent, if you want to do the scheduling service, you must write it yourself.

Therefore, it is a low-end product in the SQL Server product series. It is for desktop applications or small internal network applications.

I choose to install hereMicrosoft® SQL Server® 2012 Service Pack 2 (SP2) Express64 bit with tools (package name: sqlexrwt)_ x64_ CHS. Exe). Even if you install all of express, it won’t be too big. Unlike the original sql sever, it has a 4GB package size… As an exercise, it’s enough. Click the text above to jump to the download link.

The only difficulty encountered by the author is that windows sever 2012 R2 does not have its own. Net 3.5 environment, so it needs to be added in the “add roles and functions” interface, and then SQL Sever express can be successfully installed.
Although there are only commands in the test, I still recommend not to cancel the installation of GUI (SQL Server Management Studio, or SSMS) to prevent error correction or database reconstruction when inputting wrong commands.

There are many installation tutorials, please search by yourself, no more details.

Commands and operations

Basic command syntax

If you really don’t understand it, or you don’t think it’s necessary, just look at it casually. I don’t know when to come back.

When describing the command line parameters, there are some common ways to write their formats, and there are also differences between different systems. The general format is as follows:

Command < required parameter 1 | required parameter 2 > [- option {required parameter 1 | required parameter 2 | required parameter 3}] [optional parameter...] {(default parameter) | parameter | parameter}

The meanings of several symbols commonly used in command format are as follows:

  • Angle brackets < >: required parameters, which should be replaced with the required parameters in actual use.
  • Brace {}: required parameter, internal use, including the parameters allowed here.
  • Square bracket []: optional parameter, which can be selected or rejected as required in the command.
  • Parentheses (): indicates the default value of the parameter, only used in {}.
  • Vertical bar |: used to separate multiple mutually exclusive parameters, meaning “or”. Only one parameter can be selected when using.
  • Ellipsis…: any number of parameters.

The difference between braces and angle brackets is that only the required parameters (or one of them) listed can be selected in braces, but the required parameters need to be replaced according to the actual situation in angle brackets

Example 1:

  • Command syntax:git help <name>
  • Actual use:Git help config or git help branchOr… (name is replaced with the actual content)

Example 2:

  • Command syntax:git stash {apply | pop}
  • Actual use:git stash applyorgit stash pop(only one of the required parameters, apply or pop, can be selected)

Case sensitive

This passage is mainly for you to write more freely.

SQL keyword and function name

SQL keywords and function names are not case sensitive. For example, the following statements are equivalent:

SELECT NOW();
select now();
sElEcT nOw();

Column and Index Names

In mysql, column names and index names are not case sensitive. For example, the following statements are equivalent:

SELECT name FROM student;
SELECT NAME FROM student;
SELECT nAmE FROM student;

alias

By default, the alias of a table is case sensitive. You can use any case (uppercase, lowercase or mixed case) to specify an alias in an SQL statement. If you need to use the same alias multiple times in the same statement, you must keep their case consistent.

Case sensitive in Linux:

project Linux
Database name yes
Table name yes
Table alias yes
Listing no
Column alias no
Variable name yes

In windows, they are not case sensitive.

Too long to see?

The names of all kinds of things should be case sensitive (database name, column name, table name, etc.); SQL keywords and functions need not be distinguished (create, select, from, etc.). Under windows, it all depends on your mood.

Space

The SQL statement ignores extra spaces. Therefore, you have enough free space to organize your code structure and style.

Database connection

Connection of SSMS

When using GUI (SSMS), you may encounter the problem that the server name is empty and cannot be linked. At this time, you can click “view more” under the drop-down box to try to find, or run services. MSC to find all the display names, such asSQL Server (database name)Our services.

No sever name dialog

For example, on my machine, there is a display namedSQL Server (SQLEXPRESS)The actual service name isMSSQL$SQLEXPRESSThen the name of the SQL server instance isSQLEXPRESS, which can be entered in the server name box./SQLEXPRESSTo log in.
Then you should see the database you just logged in to in object explorer.
Click “new query” to input, edit, debug and run commands under SSMS.

Connection of command line

Enter in CMD or PowerShellsqlcmd -S localhost -U sa -P 123456To connect the command line.
The following is the command syntax of sqlcmd tool, for reference only.

PS C:\Users\Administrator> sqlcmd -?
Microsoft (R) SQL server command line tool
Version 11.0.2100.60 NT x64
Copyright (c) 2012 Microsoft. All rights reserved.

Usage: sqlcmd [- U login ID] [- P password]
[- s server] [- H host name] [- e trusted connection]
[- N encrypted connection] [- C trusted server certificate]
[- D use database name] [- L login timeout value] [- t query timeout value]
[- H title] [- s column separator] [- W screen width]
[- a packet size] [- e echo input] [- I allow quoted identifiers]
[- C end of command] [- L [C] list servers [clear output]]
[- Q "command line query"] [- Q "command line query" and exit]
[- M error level] [- V severity level] [- W remove trailing spaces]
[- U Unicode output] [- R [0|1] message sent to stderr]
[- I input file] [- O output file] [- Z new password]
[- f < code page > | I: < code page > [, O: < code page >]]] [- Z create new password and exit]
[- K [1|2] delete [replace] control character]
[- y variable length type display width]
[- y fixed length type display width]
[- P [1] print statistics [colon format]]
[- R use client locale]
[- K application intention]
[- M multi subnet fail over]
[- B abort batch processing on error]
[- V variable = "value"...] [- a private management connection]
[- x [1] disable commands, start scripts, environment variables [and exit]]
[- x disable variable substitution]
[-?  Show syntax summary]

It can be seen that the – S parameter is used to specify the connection to the localhost local server, the SA user login, and the password is 123456. This is a built-in administrator account.
If the command line window appears on the left1> If the connection is successful, you can start to input the command. Note that the Convention of ending a statement with a semicolon has no effect, and you need to use a semicolongoThe command is executed.

identifier effect
GO All statements entered after the last go command is executed
RESET Clear all statements that have been entered
ED Call editor
!! Command Execute operating system commands
Quit or exit Exit sqlcmd directly
CTRL+C End query without quitting sqlcmd

Only if the go (default), reset, ED,!, and Exit, quit, and Ctrl + C are recognized only when they appear at the beginning of a line (immediately following the prompt). Sqlcmd ignores anything entered after these keywords in the same line.

After that, I’ll assume that we operate in a command-line interactive way. It is difficult to edit complex commands directly under the interactive window. You can copy it directly after editing elsewhere, or choose the above debug instructions in SSMS.

Database operation

establish

Use the create database statement to create a database.
Grammar:

Create database < database name >;

In the command line interaction window, what you enter should be as follows:

1> create database my_db;
2> go

The command line window then reappears1> , operation completed successfully.
Other parameters may not be so important. Please refer to p77 for details.

Then, enterUse database nameEnter database operation.

delete

Use the drop database statement.

Drop database < database name >;

Don’t say you should know – use it carefully.

constraint

The prior knowledge of constraints is the creation of data tables. If you are not familiar with the rules of table creation, it is recommended to skip toCreate table sectionAnd then come back and continue.

SQL constraints are used to specify the rules for the data in the table.
Constraints can be divided into column level and table level. Column level constraints act on a single column, while table level constraints act on the entire data table.

integrity constraint

Integrity constraints are used to ensure the accuracy and consistency of data in relational databases. For relational databases, data integrity is guaranteed by reference integrity (RI).
There are many kinds of constraints that can play the role of referential integrity, including primary key constraint, foreign key constraint, unique constraint and other constraints mentioned below.

Don’t worry too much about this concept.

Common constraints

The following constraints are commonly used in SQL:

Constraint name explain
Not NULL constraint Ensure that the data in the column cannot have null values
Default constraint Provides the default value used when the column data is not specified
Unique constraint Ensure that all data in the column are different
Primary key constraint Uniquely identifies rows / records in a data table
Foreign key constraints Uniquely identifies a row / record in another table
Check constraint This constraint guarantees that all values in the column satisfy a certain condition
Indexes Used to quickly create or retrieve data in a database

If you want to specify the constraint name, you can add theConstraint < constraint name >To replace the original constraint name. This is optional. One of the advantages of adding a constraint name is that it may be more convenient to modify or delete the constraint in the future.

NOT NULL

By default, the columns of the table accept null values, and the not NULL constraint forces the columns not to accept null values. This constraint makes the field always contain a value. This means that you cannot insert a new record or update a record without adding a value to the field.

UNIQUE

The unique constraint uniquely identifies each record in the database table. Both the unique and primary key constraints guarantee the uniqueness of columns or column sets. The primary key constraint has automatically defined unique constraints.

Note that each table can have multiple unique constraints, but each table can only have one primary key constraint.

PRIMARY KEY

The primary key constraint uniquely identifies each record in the database table. The primary key must contain a unique non null value.

Each table should have a primary key, and each table can only have one primary key.

FOREIGN KEY

Foreign key in one table points to primary key in another table.
We agree that the primary key table is the master table and the foreign key table is the slave table.

Let’s explain the foreign key with an example. Here are two tables:

“Persons” table:

P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

“Orders” table:

O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 2
4 24562 1

“P” in the “orders” table_ The “Id” column points to the “P” in the “persons” table_ “Id” column.
“P” in “people” table_ The “Id” column is the primary key in the “persons” table.
“P” in the “orders” table_ The “Id” column is the foreign key in the “orders” table.

Foreign key constraint is used to prevent the behavior of breaking the connection between tables. Foreign key constraint can also prevent illegal data from being inserted into the foreign key column, because it must be one of the values in the table it points to.

Grammar:

//Definition of XX table column // foreign key references < primary table name > [(primary table primary key name)]

The column names of the main table can be omitted because the main table can only have one primary key.
The primary table primary key name is the name of the primary table primary key column, not the primary key constraint name.

example:

CREATE TABLE Orders (
    O_id int AUTO_INCREMENT,
    OrderNo int,
    P_id int FOREIGN KEY REFERENCES Persons
)

DEFAULT

The default constraint is used to insert default values into a column. If no other value is specified, the default value is added to all new records. When defining, write the default value after default.

CHECK

Check constraint is used to limit the range of values in the column, which can improve the efficiency of the programRobustness。

If you define a check constraint on a single column, only specific values are allowed for that column.
If you define a check constraint on a table, the constraint restricts the values in a particular column based on the values of other columns in the row.

example:

  • Column constraint:
CREATE TABLE Persons
(
    P_Id int NOT NULL CHECK (P_Id>0),
    Name varchar(255) NOT NULL,
    City varchar(255)
)
  • Table constraints
CREATE TABLE Persons
(
    P_Id int NOT NULL,
    Name varchar(255) NOT NULL,
    City varchar(255),
    CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)

Table operation

data type

Most commonly used data types:

data type describe
integer(size)
int(size)
smallint(size)
tinyint(size)
Holds only integers. Specify the maximum number of digits in brackets.
decimal(size,d)
numeric(size,d)
Holds numbers with decimals.
“Size” specifies the maximum number of digits“ D “specifies the maximum number of digits to the right of the decimal point.
char(size) Holds fixed length strings (letters, numbers, and special characters).
Specify the length of the string in parentheses.
varchar(size) Holds variable length strings (letters, numbers, and special characters).
Specify the maximum length of the string in parentheses.
date(yyyymmdd) Date of accommodation.

Text classes (char and text), preceded by N, are the corresponding data types for storing Unicode characters.
You don’t need to remember all of them, just know them. Click here for reference
All data types

Create table create table

Use the CREATE TABLE statement to create a table.
Grammar:

Create table name
(
    < column name 1 > < data type > [default] [column constraint}],
    < column name 2 > < data type > [default] [column constraint}],
    ...,
    [table constraint]
);

example:

CREATE TABLE courses (
    course_id   INT         AUTO_INCREMENT,
    course_name VARCHAR(50) NOT NULL,
    start_date  DATE,
    PRIMARY KEY (employee_id , course_id)
);

For more information about constraints, you can go up toConstraint sectionLook back.
Table and column constraints are different. Even in setting the primary key, the actual meaning is not clearSlightly differentHowever, if we don’t go deep into it, sometimes the functions of the two are similar. How to realize it depends on the reality and your needs without too much entanglement.

Drop table delete table

Use the drop table statement. Grammar:

Drop table name

Modifying columns in a table

Modify the columns in the table and use the ALTER TABLE statement.

To add columns to a table, use the following syntax:

ALTER TABLE table_name         
ADD column_name datatype

To delete a column from a table, use the following syntax:

ALTER TABLE table_name
DROP COLUMN column_name

To change the data type of a column in a table, use the following syntax:

ALTER TABLE table_name
ALTER COLUMN column_name datatype

As a DDL language, the syntax structure of SQL command is very direct.
The general process is as follows:

  1. First, determine the table to modify,Alter table the name of the data table to change
  2. Then select three possible column operations:
    • If you want to add columns, useAdd column name data typeKeywords (you can enter multiple lines separated by commas)
    • To delete a column, useDrop column the name of the data table to be deletedkeyword
    • If you change the table, enterAlter column name to change new name new typeOrders.

query

Suppose there is this table:

u_id u_name u_age u_sex
1 Wang Yi 18 male
2 Runer 19 female
3 Zhang San 21 female
4 Li Si 21 male
5 Zhao Wu 22 female
6 Lei Liuliu 19 male
7 Period four one 20 male
s_id amount u_id
0 7300 2
1 7100 1
2 6400 4
3 1700 3
4 5000 5
5 6000 5
6 2900 1

Here are two tables of company employees. The above is the detailed information, and the following is the record of salary payment.

These two tables will serve as syntax andconnectExample of.

Basic grammar

Select < column name >
From < table name >
[where < query condition >]

You can use it*Represents all columns.

AS

Use as to alias query results (or omit as)

SELECT
u_name as "myName",
u_age as "myAge"
FROM staff;

Note: if you need lowercase letters or the alias contains spaces, you need to add single quotation marks, otherwise it will be resolved to uppercase letters.

result:

1> SELECT
2> u_name as 'myName',
3> u_age as 'myAge'
4> FROM staff;
5> go
myName myAge
------ -----------
Wang yi18
Runer19
Zhang San 21
Li Si 21
Zhao Wu 22
Lei 66 19
Phase I 20

(7 lines affected)

DISTINCT

Output only differentiated elements. Example:

SELECT DISTINCT u_age
FROM staff;

IS NULL

Is null is the only way to judge null in where clauseXX=NULLThe search condition of the form.

GROUP BY

Now, we want to find the total amount (total salary) paid to each employee. We can use the group by statement to combine the employee ID

SELECT u_id, SUM(amount) FROM salary
GROUP BY u_id;

result:

1> SELECT u_id, SUM(amount) FROM salary
2> GROUP BY u_id;
3> go
u_id
----------- -----------
          1        7100
          2        7300
          3        1700
          4        6400
          5       11000
          9        2900
         11        6300

(7 lines affected)

As you can see, wages are sorted by group and output. Incidentally, if the column is not named, the name of the column will be blank in the query result.
But without group by?

1> SELECT u_id, SUM(amount) FROM salary;
2> go
Message 8120, level 16, status 1, server sqlexpress, line 1
Select the column 'salary. U' in the list_ ID 'is not valid because the column is not contained in an aggregate function or a group by clause.

Well, it’s a mistake. If there is no error, the output result should be similar to this:

u_id
0 36400
1 36400
2 36400
3 36400
4 36400
5 36400
6 36400

This is definitely not the result we want.

HAVING

The “having” clause enables you to specify filter conditions to control which groups in the query result can appear in the final result.
The where clause imposes conditions on the selected column, while the having clause imposes conditions on the group generated by the group by clause.

There is no having clause in the book, so I won’t introduce it in detail here.

ORDER BY

The order by statement is used to sort the result set. ASC ascending or desc descending can be specified later. Default ascending order. Example:

SELECT * FROM staff ORDER BY u_age ASC

Common functions

Function name function
AVG Averaging
SUM Sum up
MAX Find the maximum
MIN Minimum value
COUNT Number of Statistics

After select, it can be used with as. Example:

SELECT * FROM staff
WHERE u_age = (
    SELECT MAX(u_age) FROM staff
);

Use Max function and nested query to get the oldest record.

WHERE

Operator overview

The following operators can be used in the where clause:

Operator describe
=、<>、!=、>、<、>=、<= Compare size
AND、OR、NOT Multiple conditions
BETWEEN … AND … Setting range
IN Set set
LIKE Fuzzy character search
IS NULL It is empty

!= And < >

In some versions of SQL Sever, there are=!Operators, and<>The same means that two values are not equal.

BETWEEN … AND …

The operator between… And selects a range of data between two values. These values can be numeric, text, or date.

SELECT * FROM staff
WHERE u_name
Between 'runer' and 'Zhaowu';

result:

1> SELECT * FROM staff
2> WHERE u_name
3> Between 'runer' and 'Zhaowu';
4> go
u_id        u_name u_age       u_sex
----------- ------ ----------- -----
          Wang yi18, male
          2 runer 19 female
          Three three 21 women
          5 Zhao Wu 22 female

(4 lines affected)

It can be seen that when the operators are input into the text, they are arranged in phonetic order, and the corresponding results are returned.

be careful: in some DBMS database management systems, this operator does not contain the equal sign or only contains the initial equal sign. In SQL Sever, this operator contains the equal signs at both ends.

IN

Grammar:

IN (value1,value2,...)

example:

1> SELECT u_name, u_age FROM staff
2> WHERE u_age IN (18, 21);
3> go
u_name u_age
------ -----------
Wang yi18
Zhang San 21
Li Si 21

(3 lines affected)

LIKE

The like operator is used to search for the specified pattern in the column in the where clause.

wildcard

SQL wildcards can replace one or more characters when searching for data in a database.
SQL wildcards must be used with the like operator.

In SQL, you can use the following wildcards:

wildcard describe
% Replaces one or more characters
_ Replace only one character
[charlist] Any single character in a character column
[^ charlist] or [! charlist] Any single character that is not in the character column
Matching pattern

Easy to get, inu_nameField,
as%OneIt can find the “Wangyi” and “qisiyi” entries in the table.
as_ oneIt can find the “Wang Yi” entry in the table.

connect

We often use four connection types:

  • (inner) join: returns a row if there is at least one match in the table
  • Left join: returns all rows from the left table even if there is no match in the right table
  • Right join: returns all rows from the right table even if there is no match in the left table
  • Full join: returns rows as long as there is a match in one of the tables

We will continue to use itThe two tables mentioned aboveAs an example.

Inner join

Inner join keyword returns a row when there is at least one match in the table.

This is the most common way to connect. give an example:

SELECT u.u_id, u.u_name, s.amount 
FROM staff as u
INNER JOIN salary as s
ON u.u_id = s.u_id
ORDER BY u.u_id;

notes: u is user, s is salary.INNER JOINandJOINThe keywords are the same.
asKeyword to specify the alias,ONClause specifies the join condition, and the final result uses theORDER BYSort.

result:

1> SELECT u.u_id, u.u_name, s.amount
2> FROM staff as u
3> INNER JOIN salary as s
4> ON u.u_id = s.u_id
5> ORDER BY u.u_id;
6> go
u_id        u_name amount
----------- ------ -----------
          1 Wang Yi 7100
          2 runer 7300
          Three three 1700
          4 Li Si 6400
          5 Zhao Wu 5000
          5 Zhaowu 6000

(6 lines affected)

notesINNER JOINKeyword returns a row when there is at least one match in the table. If the rows in the “staff” table do not match in “salary”, they are not listed.

Sometimes, we can omitINNER JOINConnector. The following statement is equivalent to the above.

SELECT u.u_id, u.u_name, s.amount 
FROM staff as u, salary as s
WHERE u.u_id = s.u_id
ORDER BY u.u_id;

By specifying multiple tables in the from clause, SQL automatically joins them through the conditions entered (where in this case). After omitting the connector, you cannot use the on clause.

Left join

notes: in some databases, left join is called left outer join.

The left join keyword returns all rows from the left table (table1), even if there is no match in the right table (table2). If there is no match in the right table, the result is null.

In the following example, we use salary as the left table and staff as the right table to return the salary and name of all employees.

SELECT s.u_id, s.amount, u.u_name
FROM salary as s
LEFT JOIN staff as u
ON u.u_id = s.u_id
ORDER BY s.u_id;

result:

1> SELECT s.u_id, s.amount, u.u_name
2> FROM salary as s
3> LEFT JOIN staff as u
4> ON u.u_id = s.u_id
5> ORDER BY s.u_id;
6> go
u_id        amount      u_name
----------- ----------- ------
          1 7100 Wang Yi
          27300 runer
          3 1700 sheets
          4 6400 Li Si
          5 5000 Zhao Wu
          5 6000 Zhao Wu
          9        2900 NULL
         11        6300 NULL

(8 lines affected)

It can be seen that the left connection matches. Even if there is data in the left table but not in the right table, the records in the left table will be returned, and the right table will be empty.

Right join

The right join keyword returns all rows from the right table (table2), even if there is no match in the left table (table1). If there is no match in the left table, the result is null.
Right link is the reverse operation of left link query.

In the following example, we use salary as the left table and staff as the right table to return the salary and name of all employees.

SELECT s.u_id, s.amount, u.u_name
FROM salary as s
RIGHT JOIN staff as u
ON u.u_id = s.u_id
ORDER BY s.u_id;

result:

1> SELECT s.u_id, s.amount, u.u_name
2> FROM salary as s
3> RIGHT JOIN staff as u
4> ON u.u_id = s.u_id
5> ORDER BY s.u_id;
6> go
u_id        amount      u_name
----------- ----------- ------
       Lei Liuliu
       Null period one
          1 7100 Wang Yi
          27300 runer
          3 1700 sheets
          4 6400 Li Si
          5 5000 Zhao Wu
          5 6000 Zhao Wu

(8 lines affected)

Full outer join

I don’t seem to see that outer can be omitted. If you don’t want to remember, you can get rid of itINNER JOINWrite out in the middle of the connector.

The same example:

SELECT s.u_id, s.amount, u.u_name
FROM salary as s
FULL OUTER JOIN staff as u
ON u.u_id = s.u_id
ORDER BY s.u_id;

result:

1> SELECT s.u_id, s.amount, u.u_name
2> FROM salary as s
3> FULL OUTER JOIN staff as u
4> ON u.u_id = s.u_id
5> ORDER BY s.u_id;
6> go
u_id        amount      u_name
----------- ----------- ------
       Lei Liuliu
       Null period one
          1 7100 Wang Yi
          27300 runer
          3 1700 sheets
          4 6400 Li Si
          5 5000 Zhao Wu
          5 6000 Zhao Wu
          9        2900 NULL
         11        6300 NULL

(10 lines affected)

stick out a mile.

Summary

Internal connectionINNER JOINMore inclined to natural connection, can be connected to connect the place, can not be connected to throw away.
soLEFT JOINandRIGHT JOINIt’s just a change of the connector. From the code point of view, nothing else has changed. From is followed by the left table, XX join is followed by the right table, and the connection between the left table and the right table is only for which table’s data is to be fully displayed and which table’s data is not to be filled in the blank.
If the left table is connected, the left table will be displayed completely, and if the right table is connected, the right table will be displayed completely.
FULL OUTER JOINIt is equivalent to a left connection + right connection, which can display all the data of the left table and the right table.

Subquery

It’s nested queries.aboveI’ve shown you a few examples of nested queries.
If you have the idea of recursion, you will soon be familiar with the content of this section, so the introduction will be brief.

General subquery

Instead of introducing nested subqueries that return a value, we will start with subqueries that return a set of data.

ANY

Any means any one of a set of data.

Query the names of people earning more than 3000 words, for example:

SELECT DISTINCT u_name
FROM staff
WHERE u_id = ANY (
    SELECT u_id
    FROM salary
    WHERE amount > 3000
);

result:

1> SELECT DISTINCT u_name
2> FROM staff
3> WHERE u_id = ANY (
4>     SELECT u_id
5>     FROM salary
6>     WHERE amount > 3000
7> );
8> go
u_name
------
Li Si
Runer
Wang Yi
Zhao Wu

(4 lines affected)

The operation table of any is as follows:

condition Express meaning
x = ANY (…) The value in column x must match one or more values in the collection.
x != ANY (…) The value in column x cannot match one or more values in the collection.
x > ANY (…) The value in column x must be greater than the minimum value in the set.
x < ANY (…) The value in column x must be less than the maximum value in the collection.
x >= ANY (…) The value in column x must be greater than or equal to the minimum value in the set.
x <= ANY (…) The value in column x must be less than or equal to the maximum value in the collection.

ALL

All and any are operators of the same class.

condition describe
c > ALL(…) The value in column C must be greater than the maximum value in the set.
c >= ALL(…) The value in column C must be greater than or equal to the maximum value in the set.
c < ALL(…) The value in column C must be less than the minimum value in the set.
c <= ALL(…) The value in column C must be less than the minimum value in the set.
c <> ALL(…) The value in column C must not be equal to any value in the set.
c = ALL(…) The value in column C must be equal to any value in the collection.

Roughly understand it and use it according to the actual situation.ANYJudging by the lowest standard,ALLJudge by the highest standards.

IN

INand= ANYEquivalence.
NOT INand<> ALLEquivalence.

Related subquery

The query that refers to the parent query information in the child query is called related child query. Don’t worry too much about this concept. Just mark the name of the table clearly when using it.

Other types of queries

Union set operation query

UNION

The union operator is used to merge the result sets of two or more select statements.

Note that the select statement inside a union must have the same number of columns. Columns must also have similar data types. At the same time, the order of the columns in each select statement must be the same.

For demonstration, we create a new staff2 table, which is the staff list of the second department.

u_id u_name u_age u_sex
1 Liu Wusi 18 male
2 Zhang Jianguo 19 female
3 Edison Chan 21 female
4 Zhang San 21 male

List all the different employee names in the two departments:

SELECT u_name FROM staff
UNION
SELECT u_name FROM staff2;

result:

1> SELECT u_name FROM staff
2> UNION
3> SELECT u_name FROM staff2;
4> GO
u_name
------
Edison Chan
Lei Liuliu
Li Si
Liu Wusi
Period four one
Runer
Wang Yi
Zhang Jianguo
Zhang San
Zhao Wu

(10 lines affected)

notes: this command cannot list all employees. In the above example, we have two employees with the same name, and only one of them is listed. The union command selects only different values.

UNION ALL

The union all command is almost equivalent to the union command, but the union all command lists all the values.

It’s the same example above, but this time we use itUNION ALL

SELECT u_name FROM staff
UNION ALL
SELECT u_name FROM staff2

result:

1> SELECT u_name FROM staff
2> UNION ALL
3> SELECT u_name FROM staff2
4> GO
u_name
------
Wang Yi
Runer
Zhang San
Li Si
Zhao Wu
Lei Liuliu
Period four one
Liu Wusi
Zhang Jianguo
Edison Chan
Zhang San

(11 lines affected)

Summary

distinguishJoin connectionandUnion setImagine that join is to connect two tables horizontally in some way and then operate, while union is to simply “connect” the two tables horizontally, which is a simple addition / union operation.

Select into result table

The select into statement is the same as the ordinary select statement, except that an into clause is added, and the return result of the command is not displayed in the interactive window, but a new table is created to store the data returned by the query.

According to this feature, we can use this command to copy / back up the table. example:

SELECT *
INTO staff_backup
FROM staff

Similarly, being able toSELECTClauses used in statements can also be used, such asWHEREJOINAnd so on, but I will not repeat it here.

Data manipulation

Insert into add data

The insert into statement is used to insert a new row into a data table.

Grammar:

Insert into table name values (value 1, value 2,....)

To specify the column in which you want to insert data:

Insert into table name (column 1, column 2,...) values (value 1, value 2,....)

This allows us to insert data into specific columns without having to provide data for all columns.
But if you don’t specify a column, thenVALUESIt should correspond to the fields in the table one by one. There should be no lack of data items or input type mismatch with the corresponding fields. Remember to put single quotation marks on character data.

example:

INSERT INTO staff (u_ id, u_ Name) values (8, 'temporary worker');

As above, a new one will be created in the staff tableu_idandu_nameRecord whose field is not empty.

Update modify data

The update statement is used to modify the data in the table.

Grammar:

Update table name set column name = new value [, column name 2 = new value,...]
Where column name = a value

It’s a little abstract, right? Let’s take an example. If we find that runer is a big man in women’s clothes, and his gender is not female but male, we need to update his entry in the data table

UPDATE staff SET u_ Sex ='male 'where u_ Name ='runer '

First, enter the table name of the record to be updated, then the column name of the record, and then use the where clause to find the record, and successfully modify the value.

Delete delete data

The delete statement is used to delete rows in a table.

Grammar:

Delete from table name where column name = value

useFROMDetermination table,WHEREDetermine the records to delete.

Run two students were found to be women’s wear boss, even began to be the company’s oldest Zhao five discrimination, bullying. How can there be such disharmony! In order to maintain the love and peace of the world, Zhao Wu was dismissed by the company. For this reason, we need to delete Zhao Wu’s record. As follows:

DELETE FROM staff WHERE u_ Name ='zhao Wu ';

In the future, if more people discriminate against runer, you can also change itWHERETo match more records and delete them at the same time.

How good are you! As a DBA, you once again maintain the love and peace of a small place in the universe! Please continue to work hard!

view

In SQL, a view is a visual table based on the result set of SQL statements.

The view contains rows and columns, just like a real table. Fields in a view are fields from real tables in one or more databases. We can add SQL functions to the viewWHEREas well asJOINStatement, we can also submit data, just like these from a single table.

notes: the design and structure of the database are not affected by the functions in the viewwhereorjoinThe influence of sentence.

In my personal understanding, a view is a virtual table composed entirely of elements of other real tables.

The meaning of view

  • It simplifies the operation, defines the frequently used data as views, and encapsulates complex SQL query statements.

    For example, in practical work, different people only pay attention to the data related to them, and the data that has nothing to do with them is meaningless to them. According to this situation, you can create a view specifically for it, customize user data, and focus on specific data. After that, when he queries the data, he just needs toselect * from view_name;That’s it.

  • Security, users can only query and modify the data they can see.

    The view is virtual and doesn’t exist physically. It is just a collection of stored data. We can not send the important field information in the base table to the user through the view to ensure the security of the data. It is convenient for permission management and allows users to have permissions on the view instead of the underlying table, which further enhances the security.

  • The independence of logic shields the influence of the structure of real table.

    The existence of view: mainly to provide external data support (external system); Hide the base table field (Privacy); Ensure the data security of the database (protect the internal data structure of the database); Flexible control of external data: ensure that each interface has a separate data support, enhance the user friendliness.

Create view create view

Basic grammar:

Create view view name (column name 1, column name 2,...) as
Select column 1, column 2
From table name
Where condition;

To create a view, you first need to select which column you want to extract from which table and which records are used as the elements of the view. The column name of the view is optional. In essence, it’s inASA query statement is constructed later. You’re still free to use the normal oneSELECTStatement, and don’t forget thatASoh

The complete view creation command also includes some commands such asWITH CHECK OPTIONAnd so on, but let’s not study it so deeply first, just understand its basic use.

You see, a simple drawing of all the staff’s names and ages only needs to be written as follows:

CREATE VIEW staff_all(name, age) AS
(
    SELECT u_name, u_age FROM staff
    UNION
    SELECT u_name, u_age FROM staff2
)

The new view has two columns namedname, a column namedage. The other data – the view provider is designed not to worry about or manipulate other data items.

Alter view modify view

Grammar:

Alter view < view name > [(view list)] as
Subquery

There’s not much in the book, but it’s basically a level of rebuilding a view. Consider the alter view command as the process of replacing an existing view with a new view, and the subsequent commands are used to define the new view.

Drop view delete view

you’ll see.

Drop view < view name >

Query view

Write the view after from as a normal table. In essence, a view is the result of a series of queries, and the query to a view is a nested query automatically generated by the system through the view.

Manipulate / update view

Use the same insert, update and delete statements to operate the view as a table.

In actual operation, it should be noted that because views may be connected by multiple tables with certain rules, some team views can not be operated successfully because their original tables are restricted by rules and are not allowed to add a single data item.

Indexes

Index is a special query table, which can be used by database search engines to speed up data retrieval. Generally, B + tree and hash index are used to build data structure, but here, we only need to know the concept of an index table.

Classification of index

The following is an introduction to concepts. If you only consider SQL statements, you can skim, roughly understand or skip them. View index, full-text index and XML index will not be found.

Clustered index

Clustered index. In a clustered index, the physical order of rows in a table is the same as the logical (index) order of key values.

Nonclustered index

Non clustered index. If it is not a clustered index, the physical order of the rows in the table does not match the logical order of the key values. Clustered index has faster data access speed than non clustered index.

The difference between aggregation and non aggregation

If the two concepts are indistinguishable, we can consider itReference below

(1) Understand index structure in simple terms

In fact, you can think of an index as a special kind of directory. Microsoft SQL server provides two kinds of indexes: clustered index and nonclustered index. Next, let’s illustrate the difference between clustered index and nonclustered index with an example

In fact, the text of our Chinese Dictionary itself is a clustered index. For example, if we want to look up the word “an”, we will naturally turn to the first few pages of the dictionary, because the Pinyin of “an” is “an”, while the dictionary of Chinese characters in pinyin order starts with the English letter “a” and ends with “Z”, so the word “an” naturally ranks at the front of the dictionary. If you can’t find the word “a” after turning through all the parts beginning with “a”, it means that you don’t have the word in your dictionary; Similarly, if you look up the word “Zhang”, you will also turn your dictionary to the last part, because the Pinyin of “Zhang” is “Zhang”. In other words, the body of the dictionary itself is a directory, you do not need to look up other directories to find what you need to find.

We call this kind of text content itself a kind of directory arranged according to certain rules as “clustered index”.

If you know a word, you can quickly find it from the automatic search. But you may also encounter a word you don’t know, and you don’t know its pronunciation. At this time, you can’t find the word you want to look up according to the method just now. Instead, you need to look up the word you want according to the radical, and then turn to a page directly to find the word you want according to the page number after the word. But the sorting of the words you find by combining the “radical catalog” with the “check list” is not the real sorting method of the text. For example, when you look up the word “Zhang”, we can see that the page number of “Zhang” in the check list after you look up the radical is 672, the top of “Zhang” in the check list is “Chi”, but the page number is 63, the bottom of “Zhang” is “Nu”, and the page number is 390. Obviously, these words are not really located at the top and bottom of the word “Zhang”. The three consecutive words “Chi”, “Zhang” and “Nu” you see now are actually their sort in the nonclustered index, which is the mapping of the words in the dictionary body in the nonclustered index. We can find the word you need in this way, but it takes two steps: first find the result in the directory, and then turn to the page you need.

We call this sort of directory purely directory and text purely text “nonclustered index”.

Through the above examples, we can understand what “clustered index” and “nonclustered index” are.

Further extension, we can easily understand: each table can only have one clustered index, because the directory can only be sorted in one way.

unique index

Unique index does not allow two rows to have the same index value.

If there are duplicate key values in the existing data, most databases do not allow the newly created unique index to be saved with the table. The database also refuses to accept the new data when it will duplicate the key values in the table.

View index

As the name suggests, an index to a view. As a virtual table that refers to a real table, views are operated dynamically every time, which costs a lot. Therefore, it is necessary to reduce the cost of performance and time for indexing views.

However, if the basic table data is updated frequently, the overhead of repeatedly and automatically rebuilding the view index may be greater than the performance benefit of the view index. Please use it carefully.

Full text index

Full text index can improve the speed of data search. In SQL Sever 2012, full-text index is allowed to be created under the condition that there is at most one table per table and only for columns of text, binary and XML data types.

XML index

As the name suggests, XML is indexed.

Operation of index

Come and learn English by the way

cluster
English[ ˈ kl ʌ st ə( r) ] beauty[ ˈ kl ʌ st ə r]
n. Group; Cluster; Plexus; strand
Vi. clustering; Clumps
Vt. cause to gather; vt; Gather around sb
n. (cluster) name( Craster, UK

Create index

There seems to be some discrepancy between the materials and textbooks

Create [unique] [clustered | nonclustered] index name
On data table name (column name,...);

If specified(NON) CLUSTEREDThen a (non) clustered index is created. If specifiedUNIQUE, a unique index is created.

example:

CREATE UNIQUE CLUSTERED INDEX index_staff
ON staff(u_id, u_name);

This command creates a unique clustered index foru_idandu_nameComposite index of two columns.

Alter index modify index

The index is usually maintained automatically by the DBMS. If there is no special purpose, I really don’t know why I want to change it.

Alter index < index name or all > index
On table name
{ REBUILD | DISABLE | REORGANIZE | SET }
  1. REBUILD: rebuild index.
  2. DISABLE: disable indexing.
  3. SET: set some flags for the index.
  4. I can’t understand the rest. It’s not mentioned in the book. I haven’t given any examples.
  5. I don’t have an example. I probably won’t take the exam.

Drop index drop index

There are two syntax for deleting an index:

Drop index table name. Index name
Drop index name on table name

It’s easy, isn’t it?

Summary

What can you do with alter?

  1. Modify table column
  2. Modify view
  3. Modify index

It can be seen that modifying table columns is most useful.

other

In fact, I’m not sure what to summarize. Let’s put it here first.

Postscript

Thank you for looking at the fruits of my labor that I have made with a few days’ holiday.
I wish you all a happy may day and a smooth exam.

Blogging is really time consuming
Of course, it may be that I’m too busy to handle. Welcome to the great gods.

It’s gone.

reference resources

[1]Commands and actions: reprinted fromhttps://www.cnblogs.com/uakora/p/11809501.html
[2]Constraints: most reprinted fromhttps://www.w3cschool.cn/sql/vgh71oyq.html
[3]The meaning of view: reprinted fromhttps://zhuanlan.zhihu.com/p/80183774