SQL statement instance description _ MySQL

SQL statement instance description bitsCN.com

I did a test in the MySQL database. different databases have some differences.

In MySQL, the mode is database.

Show databases;

Show databases;
List all database names

CREATE DATABASE <数据库名>

Create database TEST;
Create a database named TEST

DROP DATABASE <数据库名>

Drop database TEST;
Delete a database named TEST

USE <数据库名>

Use TEST;
Use a database named TEST

SHOW TABLES

Show tables;
Display All tables of the current database

SHOW [FULL] COLUMNS FROM <表名>

Show [full] columns from <表名>
Displays the table structure in the MySQL database. if full is added, the table structure is more detailed.

DESC <表名>

Desc TEST;
View the structure of the TEST table, which is the same as show columns from TEST

SHOW CREATE TABLE <表名>

Show create table TEST;

View all information about the table TEST, including the table creation statement.

Create a basic table:
CREATE TABLE <表名>
( <列名> <数据类型> [Constraints on column-level integrity],
<列名> <数据类型> [Constraints on column-level integrity],
..................
);

Create table STUDENT
(
StudentId int (30) primary key,
Name varchar (255 ),
Address varchar (1, 255)
);
Create a table named STUDENT with the attributes studentId, name, and address. studentId is the primary key.

Create table TEACHER
(
TeacherId int (30 ),
Name varchar (255 ),
Age int (20 ),
StudentId int (30 ),
Primary key (teacherId ),
Foreign key (studentId) references STUDENT (studentId)
);
Create a table named TEACHER, where teacherId is the primary key, studentId is the foreign key, and reference the primary key studentId of the STUDENT table

Modify basic table:

ALTER TABLE <表名> RENAME <修改后表名>

Alter table TEACHER rename S;
Change the table name of the TEACHER table to S.

ALTER TABLE <表名> ADD COLUMN <列名> <属性类型>

Alter table TEACHER add column ADDRESS varchar (255 );
Add the ADDRESS column to the table named TEACHER.

ALTER TABLE <表名> CHANGE COLUMN <列名> <修改后列名> <属性类型>

Alter table TEACHER change column ADDRESS varchar (230 );
Modify the columns in the TEACHER table and change the name of the ADDRESS column to address.

ALTER TABLE <表名> DROP [COLUMN] <列名>

Alter table TEACHER drop [column] address;
Delete The address column. the column can be unavailable.

Delete basic table:

DROP TABLE <表名> [RESTRICT | CASCADE]
Delete table

Drop table STUDENT restrict;
Delete the STUDENT table. Restricted deletion: the basic table to be deleted cannot be referenced by other table constraints (such as check and foreign key constraints ),
No view, no trigger, no stored procedure or function.

Drop table STUDENT cascade;

If you select cascade, there is no restriction on the deletion of the table. When deleting a basic table, related dependent objects, such as views, will be deleted together.

However, during the MySQL test, the error message "Cannot delete or update a parent row: a foreign key constraint fails" is displayed. the error message is not deleted. I do not know why.

Complete limitations:

References:SQL definition of integrity constraints

ALTER TABLE <表名> ADD CONSTRAINT <约束名> <约束条件>

Alter table teacher add constraint pk_teacher_id primary key teacher (id );

Add a primary key constraint named pk_teacher_id to the teacher table.

ALTER TABLE <表名> DROP <约束条件>

Alter table teacher drop primary key;

Delete the primary key constraint of the teacher table.

Alter table student add constraint fk_student_teacher foreign key student (teacherId) references teacher (id );

Add a constraint named fk_student_teacher to the student table. The constraint is a foreign key constraint.

Index creation and deletion:

Index creation:

CREATE [UNIQUE] | [CLUSTER] INDEX <索引名> ON <表名> ( <列名> [Order] [, <列名> [Order]...);

UNIQUE indicates that each index value of this index only corresponds to a UNIQUE data record.

CLUSTER indicates that the index to be created is a clustered index.

Create unique index id_index on teacher (id asc );
Create a unique index for the id column of the teacher table. The index name is id_index.

Delete an index:

DROP INDEX <索引名> ON <表名>

Drop index id_index on teacher;
Delete the index in the teacher table. The index name is id_index.

Other methods:

Create an index:

ALTER TABLE <表名> ADD [UNIQUE] | [CLUSTER] INDEX [ <索引名> ] ( <列名> [ <次序> ], [ <列名> [ <次序> ]……)

Alter table teacher add unique index id_index (id asc );
Create a unique index for the id column in ascending order in the teacher table. The index name is id_index.

Delete an index:

ALTER TABLE <表名> DROP INDEX <索引名>

Alter table teacher drop index id_index;
Delete the index named id_index in the teacher table

The establishment of database indexes has the following advantages and disadvantages:

Functions and advantages and disadvantages of database indexes (I) functions and advantages and disadvantages of database indexes (II) Principles of Database indexing

Data query:

SELECT [ALL | DISTINCT] <目标列表达式> [, <目标列表达式> ]……

FROM <表名或视图名> [ <表名或视图名> ]……

[WHERE <条件表达式> ]

[GROUP <列名1> [HAVING <条件表达式> ]

[ORDER <列名2> [ASC | DESC] [, <列名3> [ASC | DESC]…];

Query calculated values:

Select teacherId as id, salary-100 as S from teacher;

The calculated value is queried. the teacherId field is queried from the teacher table. the alias is id and the salary field minus 100 is queried. the alias is S.

Use functions and strings:

Select teacherid as id, 'birth', salary-20 as SA, lower (name) from teacher;

<目标表达式> It can be string constants and functions, 'birth' is a string constant, and lower (name) is a function. the name field is output in lowercase letters.

Remove rows with duplicate values:

Select distinct name from teacher;

If the DISTINCT keyword is not specified, the default value is ALL.

Query the qualified tuples:

Common query conditions of the WHERE clause:

Query conditions	Predicate
Comparison	=,>, <, >=, <= ,! =, <>,!>, ! <
Confirm range	Between and, NOT BETWEEN AND
Determine set	IN, NOT IN
Character matching	LIKE, NOT LIKE
Null value	Is null, IS NOT NULL
Multiple conditions (logical operations)	AND, OR, NOT

(1) compare size:

Select * from teacher where name = 'test ';

Select * from teacher where salary> 500;

Select * from teacher where salary <> 500;

(2) scope:

Select * from teacher where salary between 300 and 1000;

Select * from teacher where salary not between 500 & 1000

(3) determine the set

Select * from teacher where name in ('test', 'test2 ');

Select * from teacher where name not in ('test', 'test2 ');

(4) character matching:

[NOT] LIKE' <匹配串> '[ESCAPE' <换码字符> ']
<匹配串> It can be a complete string or contain wildcards % and _
% Represents any length (the length can be 0) characters. For example, a % B indicates a string of any length starting with a and ending with B. Such as acb, addgb, AB

_ Represents any single character. For example, a_ B indicates any string starting with a and ending with B with a length of 3. Such as acb and afb all match strings.

Select * from teacher where name like '% 100 ';

Select * from teacher where name like '_ e % d ';

Note that a Chinese character occupies two characters.

(5) query involving null values:

Select * from teacher where name is null;

Select * from teacher where name is not null;

Note that the "is" cannot be replaced by the symbol (=.

(6) multi-condition query:

Select * from teacher where name = 'test' and salary between 400 & 800;

Select * from teacher where name like '% s %' or salary = 500;

Order by clause:

The order by clause orders the query results in ascending or descending ORDER of one or more attribute columns. the default value is (ASC)

Select salary from teacher order by salary asc;

Select * from teacher order by name desc, salary asc;

Aggregate functions ):

COUNT ([DISTINCT | ALL] *) COUNT the number of tuples
COUNT ([DISTINCT | ALL] <列名> ) Count the number of values in a column
SUM ([DISTINCT | ALL] <列名> ) Calculates the sum of values in a column.
AVG ([DISTINCT | ALL] <列名> ) Calculate the average value of a column.
MAX ([DISTINCT | ALL] <列名> ) Returns the maximum value in a column.
MIN ([DISTINCT | ALL] <列名> ) Returns the minimum value of a column.

The default value is ALL.

Select count (distinct name) from teacher;
Query the number of duplicate names

Select count (*) from teacher;
Query the total number of records in the teacher table

Select sum (salary) from teacher;
Queries the total salary fields of the teacher table.

Select avg (salary) from teacher;
Query the average value of the salary field in the teacher table

Select max (salary) from teacher;
Queries the maximum value of the salary field in the teacher table.

Select min (salary) from teacher;
Queries the minimum value of the salary field in the teacher table.

Group by clause:

The group by clause groups query results BY values of one or more columns. if the values are the same.
The purpose of grouping query results is to refine the target objects of clustering functions. After grouping, the aggregate function acts on each group, that is, each group has a function value.

Select cno, count (*) from teacher group by cno;
Group the teacher table by cno and calculate the number of elements in each group.

If you want to filter these groups according to certain conditions after grouping, and only the groups that meet the specified conditions are output, you can use the HAVING Statement to specify the filtering conditions.

Select cno, count (*) from teacher group by cno having count (*)> = 4;
Group the teacher table by cno and calculate the number of elements in each group. The number of elements is greater than or equal to 4.

Connection query:

Connection query is the most important query in relational databases, including equi-join queries, natural connection queries, non-equi-join queries, self-connection queries, external connection queries, and composite condition connection queries.

Equi and non-equi join queries:

The conditions used to connect two tables in the WHERE clause of the join query are called join conditions or join predicates. the format is:

[ <表名1> .] <列名> <比较运算符> [ <表名2> .] <列名2>

The comparison operators include: =, >,<,>=, <= ,! = (Or <>)

Select s. *, t. * from student as s, teacher as t where s. teacherid = t. teacherid;
Equi join query: connects the student table to the teacher. the connection condition is s. teacherid = t. teacherid.

Self-connection:

A table is connected to itself.

Select teacher. name, student. name from people as teacher, people as student where teacher. name = student. teacher;
For self-join query, there are two roles in the people Table. one is the instructor and the other is the student. by using the self-join query, the tuples with the same name field and teacher field are obtained.

External connection:

The left outer join lists all tuples in the left link, and the right outer join lists all tuples in the right link.

Left outer join: SELECT <目标列表达式> [, <目标列表达式> ]…… FROM <表名1> LEFT [OUTER] JOIN <表名2> ON <连接条件>
Right outer join: SELECT <目标列表达式> [, <目标列表达式> ]…… FROM <表名1> RIGHT [OUTER] JOIN <表名2> ON <连接条件>

Select s. sno, s. name, c. cno, c. name from student as s left outer join class as c on (s. cno = c. cno );
The student table and the class table are connected to the left outer table. the connection condition is s. cno = c. cno.

Select c. cno, c. name, s. sno, s. name from student as s right outer join class as c on (s. cno = c. cno );
The student table and the class table are connected to the outer right. the connection condition is s. cno = c. cno.

Student table data:

+ ----- + ------ +
| Sno | cno | name |
+ ----- + ------ +
| 1 | 1 | |
| 2 | 2 | Hua Xiong |
| 3 | 1 | Xiao Ci |
| 4 | 3 | required |
+ ----- + ------ +

Class table data:

+ ----- + ------ +
| Cid | cno | name |
+ ----- + ------ +
| 1 | 1 | chemistry |
| 2 | 2 | physical |
| 3 | 3 | politics |
+ ----- + ------ +

Left outer join effect:

+ ----- + ------ +
| Sno | name | cno | name |
+ ----- + ------ +
| 1 | | 1 | chemistry |
| 2 | Hua Xiong | 2 | physical |
| 3 | Xiao Ci | 1 | chemistry |
| 4 | required | 3 | politics |
+ ----- + ------ +

Right outer join effect:

+ ----- + ------ +
| Cno | name | sno | name |
+ ----- + ------ +
| 1 | chemistry | 1 | |
| 1 | chemistry | 3 | Xiao Ci |
| 2 | physical | 2 | Hua Xiong |
| 3 | politics | 4 | required |
+ ----- + ------ +

MySQL does not support all external connections!

Composite condition connection:

The WHERE clause can have multiple join conditions, which are called composite join conditions.

Select s. sno, s. name, c. name, s. score from student s, class c where s. cno = c. cno and s. score <60;
Composite condition join query: queries student information and course information, and records with scores less than 60

Nested query:

A select-FROM-WHERE statement is called a query block. A query block is nested in the WHERE clause or HAVING phrase of another query block.

The SELECT statement of a subquery cannot use the order by clause. The order by clause can only sort the final query results.

Subqueries with IN Predicates:

Select sno, name from student
Where cno in
(
Select cno from student
Where name = 'axion'
);

Query the student ID and name of all students that select the same course as "Hua Xiong.

The query condition of a subquery does not depend on the parent query.Unrelated subqueries.

If the subquery condition depends on the parent query, this type of subquery is calledRelated subqueries,The entire query statement is called a nested query statement.

Subqueries with comparison operators:

Select name, cno from student s1
Where score>
(
Select avg (score) from student s2
Where s2.name = s1.name
);

Query the subjects whose scores are higher than the average scores of each subject.

The above are related subqueries.

Subqueries with ANY (SOME) or ALL predicates

When a subquery returns a single value, it can use a comparison operator, but when returning a multi-value, it must be modified using ANY (SOME systems use SOME) or ALL predicates. When using the ALL or ALL predicates, you must use the comparison operator.

> ANY is greater than a value in the subquery result.
> ALL is greater than ALL values in the subquery results.
> = ANY is greater than or equal to a value of the subquery result.
> = ALL is greater than or equal to ALL values of the subquery results.
<= ANY is less than or equal to a value of the subquery result.
<= ALL is less than or equal to ALL values in the subquery results
= ANY is equal to a value in the subquery result.
= ALL is equal to ALL values in the subquery results (usually meaningless)
! = (Or <>) ANY is not equal to a value in the subquery result.
! = (Or <>) ALL is not equal to any value of the subquery result.

Select name, score from student where score <= all (select score from student );
Query the name and score of the student with the minimum score

Set query:

The query result of a SELECT statement is a set of tuples, so the results of multiple SELECT statements can be set. Set operations mainly include UNION, INTERSECT, and difference t ).
The number of columns of the query results that participate in the set operation must be the same; the data types of the corresponding items must also be the same.
The MySQL database does not support INTERSECT and ipvt operations!

Select * from student where cno = 1
Union
Select * from student where cno = 2;
Query information of all students in class 1 and Class 2

Data Update:

Insert data:

Insert tuples:

INSERT
INTO <表名> [( <属性列1> )[, <属性列2> ]……]
VALUES ( <常量1> [, <常量2> ]……);

Example:

Insert into student (cno, name, score) values (2, 'Crosswalk ', 85 );

Insert the subquery result:

INSERT
INTO <表名> [( <属性1> [, <属性2> ]……)]
Subquery;

Example:

Insert into studentcopy select * from student;
Copy all the information of the student table to the studentcopy table.

Modify data:

UPDATE <表名>
SET <列名> = <表达式> [, <列名> = <表达式> ]……
[WHERE <条件> ]

Modify the value of a mona1 group:

Update studentcopy set score = 80 where sno = 1;

Modify the values of multiple tuples:

Update studentcopy set score = score-20;

Delete data:

DELETE
FROM <表名>
[WHERE <条件> ];

Delete a mona1 group:

Delete from studentcopy where sno = 1;

Delete multiple tuples:

Delete from studentcopy;

The following statement is used to delete a query:

Delete from studentcopy where cno in (select cno from student as s where s. cno = 2 );

View:

For more information about the role and advantages of a view, see database view.

Create a view:

CREATE VIEW <视图名> [( <列名> [, <列名> ]……)]
AS <子查询>
[With check option]

Subqueries can be any complex SELECT statement, but generally do not contain the order by clause and DISTINCT statement.

With check option indicates that the UPDATA, INSERT, and DELETE operations on the view must be performed to ensure that the updated, inserted, or deleted rows meet the predicate conditions in the view definition.

The attribute column name that makes up the view is omitted or all specified. there is no third option. If the attribute column names of the view are omitted, the view is implicitly composed of fields in the target column of the SELECT clause in the subquery.
However, in the following three cases, you must specify all the column names that make up the view:
(1) a target column is not a simple attribute name, but a clustering function or column expression.
(2) several fields with the same column as the view are selected for multi-table join.
(3) you need to enable a new and more appropriate name for a column in the view.

Create view part_student
As
Select * from student
Where cno = 2;
Create a view of physical students

Create view student_class (sno, student_name, class_name, score)
As
Select s. sno, s. name, c. name, s. score
From student as s, class as c
Where s. cno = c. cno;
Create a view based on the student table and course selection table

If the structure of the basic table is modified later, the ing between the basic table and the view is damaged, and the view cannot work correctly. To avoid such problems, it is best to delete the View exported from the basic table after modifying the basic table and then recreate the view.

Delete View:

DROP VIEW <视图名> [CASCADE];

If other views are exported to the view, use the CASCADE delete statement to delete the view and all exported views.

Query view:

The query view is similar to the query basic table.

Update view:

Updating a view is similar to updating a basic table, but it has some limitations.

BitsCN.com