Mysql (iii) constraints

First, the View

A view is a virtual data table that does not store the data itself, but rather provides a logical presentation of the data.

1. Create a View

Create View Stu_view asselect s1.id, S1.name, S2.room, s2.stay_timefrom student s1, stay s2where s1.id = s2.id;

After you create a view, you can query the view like a data table. The fields of the view are the fields that we query from the data table.

Select ID from Stu_view desc stu_view
2. Modify the View

Alter VIEW Stu_view Asselect s1.id, S1.name, S2.room, s2.stay_timefrom student S1 left outer join stay s2on s1.id = s2.id; SELECT * FROM Stu_view

3. Delete View

Drop View Stu_view;

4, when modifying the view, we can not use alter, but or replace. Creates a view when the view does not exist, and replaces the view when the view is present.

Create or replace view Stu_view as
Select S1.id, S1.name, S2.room, S2.stay_time
From student S1 left outer join stay s2
on s1.id = s2.id;

5. The view is just a pre-stored query statement. (query statement after as) when we query the view, the query statement for the view expands. (that is, query from the query statement (result set) stored in the view.)

SELECT * FROM Stu_viewselect * FROM (select S1.id, S1.name, S2.room, s2.stay_timefrom student S1 left outer join stay S2on S1.id = s2.id) x;

Features of the View:

1 views as pre-stored query statements, do not improve performance.
The 2 view simplifies our input operations.
3 views can provide different logical presentations of the same data table.

Two

The view is named by the field we queried by default.
When #当视图的查询字段出现同名时 (naming conflicts), we can:
#1 use aliases when aliases are used, the view fields are named with aliases.
#2 the name of the Custom view field

1. Aliases

Create or Replace view Stu_view asselect s1.id stu_id, S2.id stay_id, S1.name, S2.room, s2.stay_timefrom student S1 left O Uter Join stay S2on s1.id = S2.id;desc  stu_view;

2, the name of the Custom view field, the number of fields specified must be the same as the number of fields queried.

Create or Replace view Stu_view (field1, Field2) asselect s1.id, s2.stay_timefrom student S1 left outer join stay S2on s1.i D = S2.id;desc  Stu_view;

Third, index

An index is a special object in a data table.

Pros: Indexes can speed up the logging of queries.

Disadvantages:
The #1 index requires additional hard disk space.
#2 the index needs to be re-changed (re-maintained) when records in the datasheet change (add, modify, etc.).

Where the index is used:

1 large amount of data (more records in table)
2 more query Operations
3 increase, delete and so on less operation.

1. Create an index

Create INDEX Stu_index on student (ID);

Create index name on table name ( field 1, field 2, ...).     field N); You can specify one or more columns

2. Delete Index

Drop INDEX index name on table name

Drop INDEX Stu_index on student

Index This is similar to the catalog of books, if we want to query some content, we can start from the first page, search by page, but this is bound to spend a lot of time to find. However, if we query through the directory, we can quickly locate the relevant page number.

Iv. database Constraints

Constraints
Constraint, is a limitation, it can guarantee the correctness and integrity of the data.
Constraints can be grouped into the following categories:
1 Uniqueness constraints
2 Non-null constraints
3 PRIMARY KEY constraints
4 FOREIGN KEY constraints
5 Checking constraints

Constraints have field-level syntax and table-level syntax.

1, the uniqueness of the constraints

Uniqueness constraints guarantee that the values of constrained columns (one or more columns) cannot be duplicated. Uniqueness constraints allow the addition of multiple null values. Because in MySQL, NULL is not equal to any value, including itself. Therefore, multiple null values are not equal to each other.

Field-level syntax:

CREATE table if not present if not exists t (ID int primary KEY, age    int unique);

If the table exists, delete.
drop table if exists t;

　

INSERT into T (ID, age) VALUES (1, 1); #错误, the illegality of the unique constraint. #insert into T (ID, age) VALUES (2, 1); INSERT into T (ID, age) VALUES (3, NULL); #允许插入多个null值. INSERT into T (ID, age) VALUES (4, NULL), and select * from T;

Table-level syntax:

Uniqueness constraints can also be used for multiple fields. When used with multiple fields, it is considered to be non-repeating as long as the values of multiple fields are not all equal.

CREATE TABLE t (ID int primary KEY, age    int,    name varchar (a),    unique key (age, name));

Unlike the table-level syntax unique key (age, name), for table-level syntax, Age,name can have only one field value, and for field-level syntax, age and name two field values are not allowed to be duplicated.

Adding uniqueness constraints after a table is in progress:

For constraints added after the table is built, it is important to ensure that the data in the current table does not break the new constraint, otherwise the constraint cannot be joined successfully.

ALTER TABLE T add unique key (age, name);
ALTER TABLE T modify age int unique;

Remove Uniqueness Constraint
ALTER TABLE T DROP INDEX age;

2, non-null constraints

A non-null constraint indicates that a field is not allowed to be a null value, which has only a field-level syntax and no table-level syntax.

CREATE TABLE T (
ID int PRIMARY KEY,
Age int NOT NULL
);

Specify a non-empty constraint after a table is built

CREATE TABLE t (    ID int primary KEY, age    int.); ALTER TABLE T modify age int not null;　　

Delete (cancel) a non-empty constraint.
ALTER TABLE T modify age int null;

3. PRIMARY KEY constraint

The primary key field cannot be null, nor can it be duplicated. A PRIMARY KEY constraint is a uniqueness constraint + a non-null constraint.

Field-level syntax:

CREATE TABLE T (
ID int PRIMARY Key
);

Table-level syntax:

CREATE TABLE t (id int,    name varchar,    primary key (ID, name)    #我们可以给主键命名, but is only syntactically supported and functionally unsupported.    #不管我们如何命名, the MySQL primary key name is primary.    #primary key PK (ID, name));

When we use multiple fields to act as primary keys (federated primary key), multiple fields as primary keys are considered non-repeating as long as they are not identical, but each field is not allowed to be null.

Add a primary key after the table is built:

CREATE TABLE T (
ID int
);
ALTER TABLE T add primary key (ID);
ALTER TABLE t modify ID int primary key;

To delete a primary key:

ALTER TABLE t drop PRIMARY key;

4, FOREIGN KEY constraints:

If the B1 field in table B refers to the A1 field in table A, we call B table from table, table A as the main table. The value of the B1 field is either null or must be a value that exists in the A1 field. The A1 field must be a PRIMARY key constraint, or a uniqueness constraint.

Book List
CREATE TABLE book (
ID int PRIMARY KEY,
Name varchar (30),
Author varchar (30)
);

Borrowing table
#字段级语法, MySQL only supports syntax and does not support functionality.
CREATE TABLE Borrow (
ID int PRIMARY KEY,
book_id int references book (ID),
Borrow_person varchar (30)
);

#表级语法, MySQL support
drop table if exists borrow;
CREATE TABLE Borrow (
ID int PRIMARY KEY,
book_id int,
Borrow_person varchar (30),
Foreign KEY (book_id) references book (ID)
#也可以自定义外键的名字.
#constraint FK foreign Key (book_id) references book (ID)
);

INSERT into book (ID, name, author) values (1, ' Java ', ' abc '): INSERT into book (ID, name, author) values (2, ' C + + ', ' def '); ins ert into book (ID, name, author) values (3, ' C # ', ' Zhang San '); INSERT into book (ID, name, author) values (4, ' Hadoop ', ' Xiao Li '); select * FROM Book;insert to borrow (ID, book_id, Borrow_person) VALUES (1001, 3, ' Student A '), #错误, violates foreign key constraints. #insert into borrow (ID, book_id, Borrow_person) #values (1002, 5, ' student B '); INSERT into borrow (ID, book_id, Borrow_person) Val UES (1002, NULL, ' student B '); Update book set id=10 where Id=3delete from book where id=3;

Add foreign key after table:

CREATE TABLE Borrow (
ID int PRIMARY KEY,
book_id int,
Borrow_person varchar (30)
);
ALTER TABLE Borrow Add foreign key (book_id) references book (ID);

Specify a foreign key name
ALTER TABLE borrow add constraint FK foreign key (book_id) references book (ID);
Delete foreign key

ALTER TABLE borrow drop foreign key FK;

When a record of a primary table is referenced from a table, when the main table record is modified or deleted,
How to behave from a table (behavior):
1restrict when the primary table record is modified or deleted, the execution is rejected.
2cascade when the main table record is modified or deleted, it is also modified or deleted from the table.
3set NULL The table record is set to a null value when the primary table record is modified or deleted.
4no action equivalent to restrict
The default behavior is: Restrict

5, check the constraint (MySQL only supports syntax, does not support features)

CREATE TABLE t (    ID int primary KEY, age    int.,    check (age > 0)), insert into T (ID, age) VALUES (1,-2); select * FROM T;

　　

The table-level syntax for the constraint and the field-level syntax.

1 With respect to table-level syntax, field-level syntax is simpler.
2 field-level syntax can only be used for a single field, and table-level syntax may be used for multiple fields. For example, a federated primary key.
3 field-level syntax cannot be named for constraints, and table-level syntax can be named for constraints.

V. Union

Union is used to merge multiple result sets.

The field type that requires multiple result sets is consistent with the number of fields.

Union distinct removes duplicate records from the result set.

Union ALL does not remove duplicate records from the result set.

The default is union distinct

It is preferable to use union ALL (better performance).

SELECT * FROM Studentselect * from Stay;select IDs from student union select IDs from Stay;select IDs from student union ALL Select ID from stay;

Six, sub-query

Subqueries are queries (nested queries)
Depending on where the subquery appears, you can divide the subquery into two categories:
1 appears after the from, as a temporary data table.
2 appears after the Where (having), as a filter condition.

Sub-queries need to be enclosed in ().

A subquery can be divided into a subquery based on the number of records in the query result:
1 single-line subquery returns a record
2 Multiline subqueries return multiple (one) records.

Mysql (iii) constraints