Database foundation

database

Database refers to an organized and shareable data set stored in the computer for a long time. In short, a database is a place to store data. However, its storage method has specific rules. This makes it easy to process data. Database operations include creating and deleting databases. These operations are the basis of database management.

Create and delete databases

Creating a database refers to dividing a space in the database system to store the corresponding data. This is the basis of table operation and database management. In MySQL, creating a database is realized through the SQL statement create database. Its grammatical form is as follows:

create database Database name;

Example:

create database student;

Indicates that the creation is successful, 1 line is affected, and the processing time is 0.01 seconds; " "Query Ok" indicates that the creation, modification and deletion are successful. Before creating a database, you can use the show databases statement to display the existing databases. The syntax format is as follows:

show databases;

After executing the statement, the following information is displayed:

Indicates that there are 5 rows of information in the collection, 5 databases exist, and the processing time is 0.00 seconds,

0.00 seconds does not mean no time is spent, but the time is very short, less than 0.01 seconds.

Delete database: delete an existing database in the database system. After deleting the database, the originally allocated space will be reclaimed. It is worth noting that deleting a database will delete all tables and all data in the database. Therefore, special care should be taken.

In MySQL, deleting a database is realized through the SQL statement DROP DATABASE. Its grammatical form is as follows:

  DROP DATABASE Database name ;

Database name: indicates the name of the database to be deleted;

After the delete statement is executed, you can use the show databases statement to display the existing databases;

Create, modify, and delete tables

**Table: * * is the basic unit of data stored in the database. A table contains several fields or records. Table operations include creating new tables, modifying tables, and deleting tables. These operations are the most basic and important operations in database management.

**Create table: * * refers to creating a new table in an existing database. This is the most important step in building a database and the basis for other table operations.

The syntax format for creating a table is as follows:

 create table Table name (property name data type)[Integrity constraints],
				............
               Property name data type[Integrity constraints] );

**Table name: * * name of the table to be created;

Attribute name: the name of the field in the table;

**Data type: * * specifies the data type of the field;

**Integrity constraints: * * some special constraints for the specified field.

The table name cannot be a keyword of SQL language, such as create, update, order, etc. a table can have one or more attributes. When defining, the letters can be case sensitive, and the attributes are separated by commas. There is no need to add commas after the last attribute.

==Note: = = when using the create table statement to create a table, first use the use statement to select the database. The basic format of the database statement is: use database name. If the database is not selected, there will be a problem when creating the table: [the external chain picture transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-KVcJ0E8h-1637919892337)(C:\Users156\AppData\Roaming\Typora\typora-user-images\image-20211126103926931.png)]

Example:

use s   create table Student
  (Sno   char(5) ,Sname char (20),Ssex   char (1) ,Sage   INT, Sdept  char (15)); 

**Note: * * how to execute the selected statement in navicat: select the statement to be executed, right-click - select "run selected" to execute the selected statement.

Integrity constraints:

Primary key: indicates that the attribute is the primary key of the table;

Foreign key: indicates that the attribute is the foreign key of the table and the primary key of the associated table;

Not null: the identity property cannot be empty;

Unique: the value identifying this attribute is unique;

Auto_increment: indicates that the value of this attribute will increase automatically;

Default: set the default value for this attribute;

* primary key: * * is a special field of the table. This field can uniquely identify every item in the table. The relationship between the primary key and the record is similar to the relationship between the identity card and the person. The primary key is used to identify each record, and the primary key values of each record are different. The ID card is used to identify the identity of the person, and everyone has the unique ID number. The setting of the primary key of the table is set up when creating the table. A field of a table is the primary key of the table.

The main purpose of the primary key is to help MySQL find a piece of information in the table as quickly as possible. The conditions that the primary key must meet: the primary key must be unique, the values of the primary key fields of any two records in the table cannot be the same, and the value of the primary key is a non empty value. The primary key can be a single field or a combination of multiple fields.

1. Single field PK

2. Multi field PK

Set the primary key of the table:

(1) Single field as primary key: add the primary key directly after the field;

Example: set Sno as the primary key in the Student table

 CREATE TABLE Student(
 	Sno    CHAR(5) primary key,
 	Sname CHAR(20), 
 	Ssex   CHAR(1),
    Sage   INT,  
    Sdept  CHAR(15))

(2) Single field as primary key: [constraint < constraint name >] primary key [field name]
Example: set Sno as the primary key in the Student table

 CREATE TABLE Student
               (Sno       CHAR(5),
                Sname  CHAR(20),          
                Ssex      CHAR(1),
                Sage      INT,
                Sdept    CHAR(15),
                primary key(Sno))

(3) Multiple fields are used as primary keys: the primary keys are set uniformly after the attributes are defined. The syntax rules are as follows:
primary key (attribute name 1, attribute name 2,..., attribute name n)
Example: set sno and cno as primary keys in sc

 create table sc(sno char(10) ,
                             cno char(10),
                             grade int,
                             primary key (sno ,cno));

Set foreign keys for tables

A foreign key is a special field of a table. If the field sno is an attribute of table a and depends on the primary key of table B, then table B is called the parent table, table a is the child table, and sno is the foreign key of table a. the parent table B and child table a are associated through the sno field. Setting a foreign key of a table refers to setting a field as a foreign key when creating a table. This section mainly explains the principles of foreign key setting and the role of foreign keys . method of setting foreign keys.

**Principles for setting foreign keys: * * must depend on the primary key of the existing parent table in the database; foreign keys can be null.

**The function of foreign key: * * is to establish the association relationship between the table and its parent table. When deleting a piece of information from the parent table, the corresponding information in the child table must also be changed accordingly. For example, stu_id is the primary key of the student table, and stu_id is the foreign key of the grade table. When the student whose stu_id is' 123 'drops out of school, the student's information needs to be deleted from the student table. Then, the stu_id in the grade table is‘ All information of 123 'should also be deleted at the same time.

The syntax rules for setting foreign key constraints are as follows:

constraint Foreign key name foreign key (Attribute 1, attribute 2,..., attribute n)
                   references Table name (attribute 1, attribute 2.,attribute n) on  delete  cascade  on update cascade;

Example:

create table Student
        (Sno      char(5) , 
         Sname    char(20),          
         Ssex     char(1) ,
         Sage     int,
         Sdept    char(15),
         primary key(sno)); /* Set primary key*/

Create table course(cno  char(10) primary key,
                                cname char(10),
                                credit int);   
 create table sc(Sno char(10),
                             cno char(10),
                             grade int,
                             primary key (Sno ,cno),
                             constraint   s_fk  foreign  key (sno) references  student(sno),
                             constraint  c_fk  foreign  key(cno)  references   course(cno));      

You can also create external constraints using the following statement:

alter table Table name add constraint fk_Table name foreign key(field) references Table name

External constraint: the foreign key must be the primary key of another table.

Set table uniqueness constraints

Uniqueness means that the value of this field cannot be repeated in all records. Setting the uniqueness constraint of a table means adding UNIQUE constraints to some special fields of the table when creating the table. The uniqueness constraint ensures that the value of this field cannot be repeated in all records. For example, a uniqueness constraint is added to the id field, so the same value cannot appear on the id field in the record. For example, if you add a uniqueness constraint to the id field of the table, the id value of each record is UNIQUE and cannot be repeated. If the id of one record is' 0001 ', the id of another record cannot appear in the table.

The basic syntax rules for setting uniqueness constraints are as follows:

Property name data type  UNIQUE

Example:

create table   sc(sno char(10) unique,
                            cno char(10),
                            grade int,
                            primary key (sno ,cno),
                            constraint   s_fk  foreign  key (sno) references  student(sno),
                            constraint  c_fk  foreign  key(cno)  references   course(cno));


Set the attribute value of the table to increase automatically

AUTO_INCREMENT is a special constraint in MySQL database. It is mainly used to automatically generate unique ID s for new records inserted in the table. A table can only have one field using AUTO_INCREMENT constraint, and the field must be part of the primary key. AUTO_ The field of the increment constraint can be any integer type (TINYINT, SMALLINT, INT, BIGINT, etc.). By default, the value of this field is incremented from 1.

The basic syntax rules for setting attribute value fields are as follows:

 Property name data type  auto_increment

Example:

create table Student(Sno int   primary  key  auto_increment , 
         Sname      char(20),          
         Ssex       char(1) ,
         Sage       int,
         Sdept      char(15));

==Note: = = if a field is auto_increment constraint, the field must be set as the primary key, and the data type should be numeric.

Set the default values for the properties of the table

When you create a table, you can specify DEFAULT values for fields in the table. If no value is assigned to this field when inserting a new record, the database system will automatically insert the DEFAULT value for this field. The DEFAULT value is set through the DEFAULT keyword. The basic syntax rules for setting DEFAULT values are as follows:

Property name data type  default Default value

Example:

create table  sc(sno   char(10) unique,
            cno   char(10)  ,
            grade  int default 0 ,
            primary key (sno ,cno)

View table structure

Viewing table structure refers to viewing the definition of tables that already exist in the database. The statements to view the table structure include the describe statement and the show create table statement. Through these two statements, you can view the field name, data type, integrity constraints, etc. of the table.

(1) In MySQL, the describe statement can view the basic definition of a table. This includes field name, field data type, whether it is a primary key, default value, etc. The syntax of the describe statement is as follows:

describe Table name;

(2) In MySQL, the show create table statement can view the detailed definition of a table. This statement can view the field name, data type, integrity constraints and other information of the table. In addition, you can view the table's default storage engine and character encoding. The syntax of the show create table statement is as follows:

show create table Table name;

Modify table

Modifying a table refers to modifying the definition of an existing table in the database. Modifying a table is simpler than redefining a table. There is no need to reload data and will not affect ongoing services. In MySQL, you can modify tables by using the alter table statement. Modifying a table includes modifying the table name, modifying the field data type, modifying the field name, adding fields, deleting fields, modifying the arrangement position of fields, changing the default storage engine and deleting the foreign key constraints of the table.

Modify table name

• a table whose name can be uniquely determined in a database. The database system distinguishes different tables by table names. For example, there is a student table in the school database. Then, the student table is unique. Another table named "student" cannot exist in the database school. In MySQL, modifying the table name is realized through the SQL statement ALTER TABLE. Its grammatical form is as follows:

alter table Old table name rename [to] New table name ;

Example:

 alter table student rename to stu;

Modify the data type of the field

The data types of fields include integer type, floating point type, string type, binary type, date and time type, etc. The data type determines the storage format, constraints and effective range of data. Each field in the table has a data type. In MySQL, the alter table statement can also modify the data type of a field. The basic syntax is as follows:

 alter table  Table name  modify  Property name data type ;

Note: data type refers to the modified data type
Example: change the data type of stu table to char type.

 alter table  stu  modify  sno  char(10);

Modify field name

Field name can uniquely determine a field in a table. The database system distinguishes different fields in the table by field names. For example, the student table contains an id field. Then, the id field is unique in the student table. Another field named "id" cannot exist in the student table. In MySQL, the ALTER TABLE statement can also modify the field name of the table. The basic syntax is as follows:

ALTER  TABLE  Table name  CHANGE  Old property name new property name new data type ;

The "old attribute name" parameter refers to the field name before modification; The "new attribute name" parameter refers to the modified field name; The modified data type of the new data type parameter. If it does not need to be modified, set the new data type to be the same as the original.
1. Only modify the field name
2. Modify field name and field data type

Example: change the sno column name of the stu table to id and the data type to char(5)

   alter  table  stu  change  sno   id char(5); 

Note: both modify and change can change the data type of the field. Change can change the field name while changing the data type. If you use change to modify the field data type, change must be followed by two same field names.

Example: use change to change the data type of the sno column in the stu table to char(10)

 alter table stu change  sno sno char(10);

Add field

When you create a table, the fields in the table are already defined. If you want to add a new field, you can add it through the ALTER TABLE statement. In MySQL, the basic syntax of adding fields in ALTER TABLE statement is as follows:

ALTER  TABLE  Table name  ADD  Property name 1 data type  [Integrity constraints]  [FIRST |  AFTER  Property name 2] ;

Attribute name 1: Specifies the name of the field to be added;

Data type: refers to the data type of the newly added field;

Integrity constraints: used to set integrity constraints for new fields;

First: add the new field to the field indicated by attribute name 2;

Note: if the FIRST | AFTER attribute name 2 parameter does not specify the location of the new field in the SQL statement, the new field is the last field of the table by default.

Add field:

1. Add fields without integrity constraints

Example: add a field parents for the stu table with the type char(10)

  alter  table stu  add  parents  char(10)

2. Add fields with integrity constraints

Example: add a non empty field P to the stu table_ Phone, type char(10);

    alter table stu  add  p_phone  char(10)  not null ;

3. Add a field to the first position of the table

Example: add char type field in the first position of stu table;

   alter  table stu add  p_phone char(10) first;

4. Add field at specified location

If "alter attribute name 2" is added to the SQL statement, the newly added field is inserted after "attribute name 2"

Example: add a column P after the id column of the stu table_ phone.

  alter  table stu add p_phone char(10) after id;

Delete field

Deleting a field refers to deleting a field in a defined table. After the table is created, if a field is found, it needs to be deleted. You can delete the entire table and then recreate a table. This can achieve the goal, but it will inevitably affect the data in the table. Moreover, the operation is troublesome. In MySQL, the ALTER TABLE statement can also delete fields in a table. The basic syntax is as follows:

  ALTER TABLE  Table name  DROP  Attribute name ;

Example: delete the id column in the stu table

  alter  table   stu   drop  id 

Modify the arrangement position of fields

When creating a table, the arrangement position of fields in the table has been determined. If you want to change the arrangement position of fields in the table, you need to use the ALTER TABLE statement. In MySQL, the basic syntax of ALTER TABLE statement for modifying the arrangement position of fields is as follows:

ALTER TABLE Table name MODIFY Property name 1 data type FIRST | AFTER Property name 2;

The "attribute name 1" parameter refers to the name of the field where the location needs to be modified; The "data type" parameter refers to the data type of "attribute name 1"; The "FIRST" parameter specifies the position as the FIRST position of the table; The AFTER attribute name 2 parameter specifies that attribute name 1 is inserted AFTER attribute name 2.

1. Modify the field to the first position

2. Modify the field to the specified position

Example:

1. Field is modified to the first position

The first parameter specifies that the field is the first field in the table

Example: change the id field in the stu table to the first field in the table

alter  table stu  modify  id   char(10)   first ;

2. Modify the field to the specified location

Example: change the Ssex column in the stu table to the back of the id column

 alter  table  stu  modify  Ssex  char(1)  after  id ;

Delete foreign key constraint for table

A foreign key is a special field that associates a table with its parent table. When the table is created, the foreign key constraint has been set. Due to special needs, the association relationship with the parent table needs to be removed, and the foreign key constraint needs to be deleted. In MySQL, the ALTER TABLE statement can also delete the foreign key constraint of the table. The basic syntax is as follows:

ALTER TABLE Table name DROP FOREIGN KEY Foreign key ;

(1) Add the format of foreign key constraint:

 alter table  Table name  add constraint  Constraint name  foreign key(Field name) references   Referenced table name (referenced field name)

Example:

alter  table  sc  add  constraint  c_k  foreign key(sno) references stu(id)

(2) Format for deleting foreign key constraints:

alter  table  Table name  drop  foreign  key  Foreign key name

You can use the show create table table table name to query the foreign key name of the table. After executing this statement, copy the contents of the create table column. Query the foreign key name of the sc table to get:

CREATE TABLE `sc` ( `sno` char(10) NOT NULL, `cno` char(10) NOT NULL, `grade` int(11) DEFAULT '0', PRIMARY KEY (`sno`,`cno`), 

UNIQUE KEY `sno` (`sno`), 

CONSTRAINT `c_k` FOREIGN KEY (`sno`) REFERENCES `stu` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

You can see that the foreign key constraint is named c_k

Example: delete the foreign key constraint of the sc table

  alter table  sc drop foreign key c_k

Delete normal tables that are not associated

In MySQL, you can directly use the DROP TABLE statement to delete ordinary tables that are not associated with other tables. The basic syntax is as follows:

DROP TABLE Table name ;

The table name parameter is the name of the table to be deleted.

Example: the example5 table will be deleted. The SQL code is as follows:

DROP TABLE example5 ;

If the code runs successfully, the example5 table is deleted from the database. Before executing the code, use the DESC statement to check whether the example5 table exists for comparison with the deleted table.

Delete parent tables associated with other tables

Some tables become parent tables, which are associated with their child tables. To delete these parent tables, the situation is not as simple as the previous section.

Example: delete the stu table, which is associated with sc

 drop  table  stu  ;

After the code is executed, the result is displayed as:

Mysql > drop table stu ;

[Err] 1217 - Cannot delete or update a parent row: a foreign key constraint fails

At this time, you should first delete the foreign key constraint and then delete the table. The code is as follows:

alter table  sc  drop  foreign  key  c_k;

drop table stu 

Indexes

Index is a special database structure, which can be used to quickly query specific records in database tables. Index is an important way to improve database performance. In mysql, all data types can be indexed. MySQL indexes include general index, unique index, full-text index, single column index, multi column index and spatial index.

An index is created on a table. It is a structure that sorts the values of one or more columns in a database table. Indexing can speed up queries. This section will explain the meaning, function, advantages and disadvantages of the index in detail. Index has obvious advantages and inevitable disadvantages.

Advantages of index: it can improve the query speed. For the joint query between dependent child tables and parent tables, it can improve the query speed; Queries using grouping and sorting clauses can also improve speed.

Disadvantages of index: creating and maintaining index Xu Yinghao takes time, and the amount of time increases with the increase of data volume; Indexes need to occupy physical space, and each index needs to occupy a certain physical space; When adding, deleting and modifying data, the index should be maintained dynamically, resulting in the reduction of data maintenance speed.

Design principles of index

1 select a unique index

Uniqueness: the value of the index is unique, and a record can be determined more quickly through the index. For example, the student's student number is unique. Establishing a unique index in this field can quickly determine the information of a student.

2 index the fields that often need sorting, grouping and union operations

3 index fields that are often used as query criteria

4 limit the number of indexes

The number of indexes is not the more, the better. Each index takes up disk space

5 try to use indexes with less data

The longer the index value, the slower the query speed

6 try to use prefix to index

If the value of a field is very long, it is best to use the prefix of the value to index. Such as text and blog fields, full-text retrieval will waste time. If only the first few fields of the field are retrieved, the retrieval speed can be improved.

7 delete indexes that are no longer used or rarely used

Index on table creation

MySQL indexes include general index, unique index, full-text index, single column index, multi column index and spatial index. Create index when creating table:

create table Table name (property name data type)[Integrity constraints],
                   Property name data type[Integrity constraints],
                    ...
                   Property name data type 
             [unique| fulltext | spatial] index |key 
           [alias](Property name 1  [(length)][ASC|DESC])  );

Unique: unique index

Fulltext: full text index

Spatial: spatial index

**index |key: * * used to specify the field to create the index. You can select one of them for the same purpose

**Alias: * * used to give a new name to the created index

**Attribute 1: * * specifies the name of the field corresponding to the index, which must be the previously defined field;

**Length: * * the length of the index. It must be a string type before it can be used;

**ASC: * * sort in ascending order

**DESC: * * descending order

1 create a normal index when creating a table

 create  table  s(sno int ,
                       sname char(10),
                       sex char(10),
                       index(sno)); 

2 create a unique index
Creating a unique index requires a unique constraint
Example: create a unique index named index_id on the sno of the s table and arrange it in ascending order.

     create  table  s(sno int ,
                       sname char(10),
                       sex char(10),
                       unique index2_id(sno  ASC)); 

3 Create full-text index
Full text indexing can only be built on char,varchar or text type fields. Only MyISAM storage engine supports full-text indexing.

create  table  s(sno int ,
                       sname char(10),
                       sex char(10),
              FULLTEXT  INDEX  index3(sno))ENGINE=MyISAM; 

4 create a single column index
A single column index is an index built on a single field of a table
Example: create a single column index named s_index on the sno of table s

  create  table  s(sno char(15),
                       sname char(10),
                       sex char(10),
                       index  index_sno(sno(8)); 

Note: sno field length is 15, while index_ The length of sno index is only 8, which can improve the query speed.

5 create multi column index
Create an index on multiple fields. The index will work only if the first field of these fields is used in the query criteria

 create  table  s(sno char(15),
                       sname char(10),
                       sex char(10),
                       index  index_sname(sname,sex)); 

Use the show create table statement to view the table structure. You can see that there is a multi column index on the table_ sname.

CREATE TABLE `s` (
  `sno` char(15) DEFAULT NULL,
  `sname` char(10) DEFAULT NULL,
  `sex` char(10) DEFAULT NULL,
  KEY `index_sname` (`sname`,`sex`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

5 create multi column index

Use the explain statement to view the usage of the index. Only sname is used as the query condition. The display results are as follows:

 explain  select  *  from  s where sname ='ABC'

You can see the key and possible from the query results_ Keys are used.

If only sex is used as query criteria, the display results are as follows:

You can see the key and possible from the query results_ Key is null.

6 create spatial index

To create a spatial index, the storage engine of the table must be of type myisam, and the index fields must have non empty constraints.

**Example: * * create an index on sname of s table_ Spatial index of SP

create table  s(sno char(10),
          sname geometry not null,
          sex char(10),
		  spatial index index_sp(sname))ENGINE=MyISAM;

Note: only non empty spatial data type fields can create spatial indexes. Common spatial types include geometry, point, linestring and polygon

Index on an existing table

On an existing table, you can directly create an index for one or more fields on the table. The basic form is as follows:

CREATE [ UNIQUE | FULLTEXT | SPATIAL ] INDEX  Index name ON Table name (Attribute name [ (length) ] [ ASC | DESC] );

Unique: unique index

Fulltext: full text index

Spatial: spatial index

index: used to specify the field to be indexed

Index name: used to give a new name to the created index

Attribute name: Specifies the name of the field corresponding to the index, which must be the previously defined field;

**Length: * * the length of the index. It must be a string type before it can be used;

**ASC: * * sort in ascending order

**DESC: * * descending order

1 create a normal index

Example: create an index in the sno field of table s_ Index of ID

  create  index  index_id on s(sno)

2 unique index

**Example: * * create an index in the sno field of table s_ Unique index of ID

  create unique  index index_id on s(sno)

3 Create full-text index

Example: create an index in the sno field of table s_ Full text index of ID

create  fulltext   index  index_id  on  s(sno)

The fields to create a full-text index must be of types such as char, varchar, and text

4 create a single column index

Example: create an index in the sno field of table s_ Index of 10

create   index  index_10  on s(sno) 

5 create multi column index

**Example: * * create multi column indexes on sname and sex of table s

create  index  index_na  on  s(sno,sex)

6 create spatial index

**Example: * * create an index on the sno of table s_ Spatial index of Na

 create table s(sno char(10),
           sname geometry  not null,
            sex char(10))
create  spatial  index index_sp on s(sname)

Index with alter table

On an existing table, you can directly create an index for one or more fields on the table through the ALTER TABLE statement. The basic form is as follows:

ALTER TABLE Table name ADD  [ UNIQUE | FULLTEXT | SPATIAL ]  INDEX Index name (property name) [ (length) ] [ ASC | DESC]);

The parameters are the same as those in the previous two methods.

1. Establish general index

Example: create a common index on the sno of the s table

alter  table  s  add  index  index_id(sno) 

2 create a unique index

Example: create a unique index on the sno of the s table

alter table s add unique index index_id(sno)

3 Create full-text index
Example: create a full-text index on the sno of the s table

alter    table   s  add   fulltext  index  index_s(sno)  

Note: full text indexing can only be built on char,varchar or text fields, and only myisam storage engine supports full-text indexing.

4 create a single column index
Example: create a single column index on the sno of the s table

alter   table    s    add   index   index_id(sno)

Use the show create tables statement to query the index

5 create multi column index
Example: create a multi column index on sname and sex of table s

alter   table  s  add  index  index_id(sno,sex) 

6 create spatial index
Example: create a spatial index on the sname column of the s table

create  table  s(sno  char(10),
               sname  geometry  not null,
               sex char(10))
        	   alter table s add spatial index _sp(sname)

Index deletion

Deleting an index can be done by a drop statement

The basic form is as follows:

drop  index  Index name  on  Table name 

DML: data operation language (insert, delete, update)

The database changes the records in the table by inserting, updating and deleting. Inserting data is to INSERT new records into the table, which is realized by INSERT statement. Updating data is to change the existing data in the table, which is realized by UPDATE statement. Deleting data is to DELETE the data that is no longer used in the table, which is realized by DELETE statement.

insert

Inserting data is to insert a new record into the table. In this way, you can add new data to the table.

In MySQL, you can INSERT new data through the INSERT statement. You can INSERT data for all fields of the table at the same time, or for the specified fields of the table. The INSERT statement can INSERT multiple records at the same time, and you can also INSERT the data queried from one table into another table.

1 insert data for all fields of the table

Usually, the new record to be inserted contains all the fields of the table. The INSERT statement can INSERT data for all the fields of the table at the same time in two ways. The first way is not to specify a specific field name. The second way is to list all the fields of the table.

Note: the self growing data in insert may not be written

Format:

insert    into  <Table name> [(<Attribute column 1>[,<Attribute column 2 >...)]
             values(<Constant 1> [,<Constant 2>]    ...   )

Or:

insert  into <Table name> values(Constant 1, constant 2,constant n)

The number and data type of constants must match the number and data type of the given attributes

Example: insert a record in the sc table

  insert  into  sc  values('4', 'c', 75) 

Example: insert a record in the sc table

 insert  into sc(sno, cno, grade)  values('5', 'd', 80)

2 insert data for the specified field of the table

If the INSERT statement described in the previous section only specifies some fields, you can INSERT data for some fields in the table. The basic statement form is as follows:

 INSERT  INTO  Table name(Attribute 1, Attribute 2, ... , attribute m)
	VALUES(Value 1, Value 2, ..., value m);

Example: insert data in the sno,ssex,sname,sage columns of the stu table

 insert into stu(sno,ssex,sname,sage) values('4','male','d',19)

3 insert multiple records at the same time

Inserting multiple records at the same time refers to inserting multiple records with one INSERT statement. When you need to INSERT several records, you can use the methods in the above two sections to INSERT records one by one. However, you have to write a new INSERT statement every time. This is troublesome. In MySQL, one INSERT statement can INSERT multiple records at the same time. Its basic syntax is as follows:

 INSERT  INTO Table name [ (Attribute list) ] 
	VALUES(Value list 1),(Value list 2)
	... ,
	(Value list n) ;

**Example: * * insert 3 records into the sc table at the same time

 insert into sc values('7', 'e', 90),
	('8', 'f' , 80),
 	('9', 'g', 85)  

Example: insert 3 records into SnO and CNO of sc table at the same time

 insert into sc(sno,cno) values('10', 'e'),
	('11', 'f'),
	('12', 'g')

4 insert the query results into the table

INSERT statement can INSERT the data queried from one table into another table. In this way, it can facilitate data exchange between different tables. Its basic syntax is as follows:

INSERT  INTO Table name 1 (Attribute list 1) 
	SELECT  Attribute list 2  FROM  Table name 2  WHERE Conditional expression;

Note: the data types of attribute list 2 and attribute list 1 are the same. If the data types are different, the database system will report an error.

Example: insert the student number of stu table into sc table

 insert into  sc(sno) 
	select sno from stu 

Update

UPDATE can be used to UPDATE a single row or multiple rows, which is left to the WHERE clause

Note: if the WHERE clause is not added, each row and column in the table mentioned in the SET clause will be modified to a new value. Use the UPDATE statement to replace the combination of DELETE and INSERT.

Syntax format of UPDATE statement:

UPDATE    table_name 
	SET 
	column_name1 = expr1,
	column_name2 = expr2,
   ...
WHERE
   condition;  

In the UPDATE statement above:

First, specify the table name of the data to be updated after the UPDATE keyword.

Second, the SET clause specifies the column to modify and the new value. To update multiple columns, use a comma separated list. Provide the value to be SET in the assignment of each column in the form of a literal, expression, or subquery.

Third, use the conditions in the WHERE clause to specify the rows to UPDATE. The WHERE clause is optional. If the WHERE clause is omitted, the UPDATE statement updates all rows in the table.

Example: change the grade of course a to 0.

update sc set  grade=0  where cno='a'

Example: modify all course selection scores to 0

 update sc set  grade=0  

Delete

Format:

  delete  from  Table name  where   Conditional expression 

Example: delete the student information of mathematics department in stu table

  delete  from  stu  where sdept='MA'

Example: delete all records in sc table

   delete  from sc

DQL: Data Query Language (select)

Query data refers to obtaining the required data from the database. Querying data is the most common and important operation in database operation. Users can use different query methods according to their data needs. Different data can be obtained through different query methods. MySQL uses the SELECT statement to query data.

Querying data is the most commonly used operation in database operation. By querying the database, users can obtain the required data from the database. The database may contain countless tables and countless records. Therefore, it is not easy to obtain the required data. In MySQL, you can use the SELECT statement to query data. According to different query conditions, the database system will find different data. The required information can be easily obtained through the SELECT statement.

In MySQL, the basic syntax of SELECT is as follows:

SELECT Attribute list 
FROM  Table name and view list
[ WHERE Conditional expression 1 ]
[ GROUP BY Property name 1 [ HAVING Conditional expression 2 ] ]
[ ORDER BY Property name 2 [ ASC | DESC ] ]
Single table query

Single table query refers to querying the required data from a table. When querying data, you can query data from one table or multiple tables at the same time. There are some differences in query methods between the two. Because single table query only operates on one table, the query is relatively simple. This section will explain how to query all fields, query specified fields, query specified rows, multi condition query, no duplicate query results, sort query results, group query and LIMIT the number of query results on a single table.

1. List all fields in the table

Format:

select  Field name 1,Field name 2,...,Field name n  from Table name

Example: query students' personal information

select  sno , sname , sex , sage from s 

2. Use "*" to query all fields

Format:

select  *  from  Table name

Example: query students' personal information

select  *  from  s 

3 query specified fields

Format:

select Field name 1, field name 2,...,Field name s   from Table name

Example: query student number and name

 select  sno , sname  from  s 

4 query with conditions
Format:

select  Target list expression  from Table name  where  Conditional expression  (Field nameθ{Field name/constant}) 

(1) θ Can be: =, <, >, > =, < =,! =, < >
Example: query records with student number "2015144101"

select   *   from    s   where  sno ='2018123112'

Example: query the student name of CS department

 select  sname  from  student  where  sdept='cs';

Example: query the name and age of students younger than 20

select    sname ,  sage   from  student   Where   sage<20  ;

(2) Specified range: field name [not]between {field name / constant} and {field name / constant}
Example: query the name, department and age of students aged 20-23

Select  sname,sdept,sage  from  student  where sage  between   20 and  23;

(3) specified set: in, not in
Example: query the name and gender of students in computer science department, mathematics department and information department

 select   sname,  ssex   From   stu    where   sdept   in('CS','MA','IS');
         select  sname,  ssex  From stu   where sdept not in('CS','MA','IS')

(4) matching strings like and not like
%: any length string
-: any single character
Example: query the name, student number and gender of all students surnamed Liu

select  sname,sno,sex  from  s  Where  sname  like  'Liu%'

Example: query the name of a student whose last name is "Ouyang" and whose full name is 3 Chinese characters

select   sname,sno,sex  from  s  where   sname  like   'Ouyang_'

(5) is null, is not null
Example: query the student number and course number whose course selection score is empty

 select   sno ,cno  from  sc  where  grade   is    not  null;

Example: some students did not take the exam after taking elective courses, so they have course selection records, but they have no test results. Query the student number and corresponding course number of students with missing grades.

SELECT  sno ,cno   FROM   SC    WHERE   Grade   IS NULL;

(6) multiple query criteria and, or
Example: query the names of students under the age of 20 in the computer department.

 SELECT  Sname  FROM  Student  WHERE  Sdept= 'CS'  AND  Sage<20;

Example: query the names and genders of students in the Department of information (IS), Department of Mathematics (MA) and Department of Computer Science (CS)

 Select  Sname,   Ssex    from    stu
   Where  Sdept= ' IS '  or   Sdept= ' MA' or  Sdept= ' CS '

(7) No duplicate query results

----Use the distinct keyword to remove duplicate values

Example: query the student number of the selected course

select  distinct  sno   from  sc

(8) Sort query results

In order to make the order of query results meet the user's requirements, you can use the order by keyword to sort records. The syntax rules are as follows:

 select Target list expression from Table name where Conditional expression order by Attribute name ASC | DESC  

Attribute name: sort by this field; ASC parameter indicates sorting by ascending order; The DESC parameter indicates that sorting is performed in descending order. By default, sorting is performed in ASC.

Example: query student information in ascending order of student age

 select   *   from   stu   order  by   sage  ASC

Example: query the student's student number, name and department in descending order

select  p_phone, sname, sdept from stu  order by  p_phone DESC 

When sorting in ascending order, if the value of the sorting field is null, the record will be ranked first. It can be understood that null value is the minimum value of the field. When sorting in descending order, the record with null value of the sorting field will be displayed last. In MySQL, you can specify to sort by multiple fields A1 and A2. In the sorting process, first sort by A1 field, and then sort by A2 field. If A1 fields are equal, sort by A2 field.
(9) Grouping query
GROUP BY keyword can group query results by one or more fields. Fields with equal values are a group. The syntax rules are as follows:

GROUP  BY  Attribute name  [ HAVING Conditional expression ] [ WITH  ROLLUP ]

Attribute name: refers to grouping according to the value of the field.

having conditional expression: used to restrict the display after grouping. The results that meet the conditional expression will be displayed.

with rollup: a record will be added at the end of all records, which is the sum of all records above.

(1) Using the group by keyword alone, the query result displays only one record of a group

Example: group query according to the sex field of stu table

select   * ,group_concat(sname)  from   stu  group  by   ssex 

(2) group by keyword and group_ Use with concat() function

group by keyword and group_ When used with the concat () function, the specified field values in each group are displayed.

p_phoneidSsexSnameSageSdeptgroup_concat(sname)
null2femaleb17MAb,c
1501malea18CSa

The records with the value of "female" in the sex field are a group, and the records with the value of "male" are a group, and the names of everyone in each group are queried.

(3) The group by keyword is used with the set function to calculate the total record, maximum value and minimum value in a group

Example: query the number of male and female students in the student table

 select  ssex , count(ssex)   from stu group  by  ssex 

The query results are as follows:

ssexcount(ssex)
female2
male1

count(ssex) calculates the number of records in different groups of the sex field. There is only one record in the first group and two records in the second group.

(4) group by is used with having
Adding "having conditional expression" can limit the output results. Only the results that meet the conditional expression will be displayed.

Example: query the course number with more than 3 students

 select  cno ,count(sno)  from  sc group by cno  having count(sno)>3

"having condition expression": used for grouping records. It is used to select groups that meet the conditions.

"where condition expression": it acts on tables or views. It is the query condition of tables and views

(5) Group by multiple fields

Group according to the A1 field first. When the A1 field values are equal, group according to the A2 field.

Example: query the sc table and group by SnO and sex

 select   *    from  sty    group  by  sno ,  sex 

(6) group by is used with rollup

Example: query the number of selected courses for each course

 select   cno , count(sno) from sc group by  cno with rollup

When querying data, many records may be queried. The records required by users may be only a few. In this way, you need to LIMIT the number of query results. LIMIT is a special keyword in MySQL. It can be used to specify which record the query results are displayed from. You can also specify how many records are displayed in total. The LIMIT keyword can be used in two ways. The two methods are not specifying the initial position and specifying the initial position.

1. Do not specify the initial position

When the limit keyword does not specify the initial position, the record is displayed from the first record. The conditions for displaying records are specified by the limit keyword. The syntax format is as follows:

limit records

Number of records: indicates the number of records displayed. If the value of "number of records" is less than the total number of records in the query result, the specified number of records will be displayed starting from the first record. If the value of "number of records" is greater than the total number of records in the query results, the database system will directly display all the queried records.

Example: query all records in the sc table, but only the first two are displayed

 select *  from sc limit 2 

2. Specify the initial position

limit keyword can specify which record to display from and how many records to display. Its syntax rules are as follows:

limit initial position, number of records

**Initial position: * * the parameter specifies which record to display from

**Number of records: * * parameter indicates the number of records displayed

The position of the first record is 0 and the position of the second record is 1

**Example: * * query all records in sc table and display the first two records

 select  * from sc limit 0 , 2
Query using set functions

Set functions include COUNT(), SUM(), AVG(), MAX() and MIN(). Among them, COUNT() is used to count the number of records; SUM() is used to calculate the sum of field values; AVG () is used to calculate the average value of the value of the field; MAX() is used to query the maximum value of a field; MIN() is used to query the minimum value of the field. When you need to sum, average, query maximum value, query minimum value and other operations on the records in the table, you can use set functions. For example, to calculate the average grade in the student transcript, you can use the AVG() function. The GROUP BY keyword usually needs to be used with a collection function. Various set functions are explained in detail in this section.

COUNT() function: used to count the number of records. If you want to count the number of records in the employee table, you can use the count () function. If you want to count the number of people in different departments in the employee table, you can also use the COUNT() function.

Example: use the COUNT() function to count the number of records in the student table.

 select   count(*)  from   stu ;

**SUM() function: * * is a summation function. The SUM() function can be used to find the sum of the values of a field in the table.

Example: query the student grade of student No. 001.

select sno, sum(grade)  from  sc where  sno='001'

Example:

 select   d_id,  count(*)   from   examplee  group by d_id; 

**AVG() function: * * is the function of averaging. AVG() function can be used to find the average value of a field in the table.

Example: query the average age of students

  select   avg(sage)  from  stu

Example: query the average grade of each student's course selection.

  select  sno, avg(grade) from  sc group by sno

MAX() function: it is a function to find the maximum value. Use the MAX() function to find the maximum value of a field in the table. The MAX() function can calculate the maximum value of characters and strings. The MAX() function uses the ASCII code corresponding to the characters for calculation.

Example: query the maximum age of students

  select max(sage) from stu

Example: the highest scores of different subjects in the query sc table

select  cno ,max(grade) from sc  group  by cno 

• * * MIN() function: * * is the function to find the minimum value. Use the MIN() function to find the minimum value of a field in the table.

Example: query the minimum age of students

select  min(sage)  from stu

Example: query the lowest score of each subject in the course selection table.

  select cno , min(grade) from sc group by cno 
join query

Join query is to join two or more tables according to a certain condition to select the required data. Join queries are used when querying two or more tables at the same time. When there are fields representing the same meaning in different tables, these tables can be connected through this field. For example, there is course in the student table_ The ID field represents the course number of the course studied, and the num field in the course table represents the course number. Then, you can use course in the student table_ The ID field is connected with the num field in the curriculum table for query. Connection query includes inner connection query and outer connection query.

Internal connection query

Inner join query is the most commonly used join query. Inner join query can query two or more tables. For better understanding, we will only explain the join query of two tables for the time being. When a field representing the same meaning exists in two tables, the two tables can be connected through this field. When the values of the field are equal, the record is queried.

Example: query each student and their elective courses.

 select   Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
            from   Student,SC
            where  Student.Sno = SC.Sno;

Example: query the course information and course selection information of each student's selected course

select   *    from   c ,   sc    where   c.cno=sc.cno

External connection query

External join query can query two or more tables. External connection query also needs to be connected by specifying fields. When the field values are equal, the record can be queried. Moreover, records with unequal values in this field can also be queried. External connection query includes left connection query and right connection query. The basic syntax is as follows:

SELECT Attribute name list  
FROMb  Table name 1   bLEFT | RIGHT JOINb Table name 2
ON  Table name 1.Property name 1=Table name 2.Property name 2 ;

INNER JOIN (INNER JOIN or equivalent join): obtain the records of the field matching relationship in two tables.

LEFT JOIN: obtain all records in the left table, even if there is no corresponding matching record in the right table.

RIGHT JOIN: Contrary to LEFT JOIN, it is used to obtain all records in the right table, even if there is no corresponding matching record in the left table.

1 left connection

By left join query, you can query all records in the table referred to by "table name 1", and only matching records can be queried in the table referred to by "table name 2".

Example: query course selection information and personal information of course selection students

select *  from sc  left join stu on sc.sno=stu.sno

2 right connection

Example: please use the external link to query each student's personal information and course selection information

select *  from sc  right join stu on sc.sno=stu.sno
Compound conditional join query

Other restrictions can also be added when connecting queries. Through the compound query of multiple conditions, the query results can be more accurate. For example, when querying the employee table and the department table, you can restrict the value of the age field to be greater than 24. In this way, the information of employees older than 24 can be queried more accurately.

**Example: * * query the personal and course selection information of students with make-up exams.

 select  student.* ,  cno, grade from  student, sc where  grade<60  and  student.sno=sc.sno 

**Example: * * query the student numbers and names of all students who take the No. 2 course and score above 90

select  Stu.id,   stu.Sname
 from   Stu,  SC
 where  Stu.id = SC.sno  AND  
SC.Cno= '2'   AND  SC.Grade > 90
Subquery

Subquery is to nest one query statement IN another query statement. The query results of inner query statements can provide query conditions for outer query statements. Because IN a particular case, the conditions of one query statement need to be obtained by another query statement. For example, now you need to query the grades of computer students from the student transcript. Then, first of ALL, we must know which courses are elective for computer students. Therefore, we must first query the elective courses of computer students, and then query the scores of computer students according to these courses. Query between multiple tables can be realized through sub query. The subquery may include keywords such as IN, NOT IN, ANY, ALL, EXISTS, NOT EXISTS, etc. The subquery may also contain comparison operators, such as "=", "! =", ">" and "<".

1. Subquery with IN keyword

The condition of one query statement may fall IN the query result of another SELECT statement. This can be judged by the IN keyword. For example, it is necessary to query which students have chosen the courses offered by the computer department. First, we must find out what courses are offered IN the computer department from the curriculum. Then query from the student table. If a student's elective course is IN the previously queried course, the student's information will be queried. This can be achieved with a subquery with the IN keyword.

**Example: * * query the names of the students taking the No. 2 course

 select   Sname  from Stu
	where  id  in
	( select  sno  from  sc where cno= '2' )

Example: query the information of students who have taken No. 1 course

select  *  from stu where  stu.id  in (select sno from sc where cno='1')

**Example: * * query students studying in the same department as "Liu Chen"

select id, Sname, Sdept
	from  Stu
	where Sdept  in
	(select Sdept from  Stu where  Sname='Liu Chen')

Example: query the student number and name of the elective course named "information system"

 select  id ,  Sname   from   Stu         
 	WHERE sno IN
		(SELECT  Sno   
         	FROM  SC       
			 	WHERE  Cno IN
			 (SELECT Cno   FROM c WHERE Cname= 'information system') )

2. Subquery with ANY keyword

ANY keyword indicates that ANY of these conditions are met. When ANY keyword is used, as long as ANY one of the results returned by the inner query statement is satisfied, the outer query statement can be executed through this condition. For example, you need to find out which students can get scholarships. Then, first of all, you must find out the minimum scores of various scholarship requirements from the scholarship table. As long as a student's score is higher than ANY of the lowest scores of different scholarships, the student can get a scholarship. ANY keyword is usually used with comparison operators.

**Example: * * query the name and age of students in other departments who are older than any (one) student in the information department

SELECT   Sname,Sage  FROM  Student
        WHERE  Sage < ANY (SELECT  Sage
             FROM    Student WHERE  Sdept= ' IS ')
                   AND Sdept <> ' IS ' ;  

Example: query the names and ages of students in other departments who are older than any (one) student in the information department

select  Sname,  Sage  from   Stu
    Where  Sage < (select  max(Sage) from   Stu 
        			 Where  Sdept= 'IS')  and Sdept <> 'IS'

3. Subquery with ALL keyword

The ALL keyword indicates that ALL conditions are met. When using the ALL keyword, the outer query statement can be executed only if ALL the results returned by the inner query statement are met. For example, you need to find out which students can get the first-class scholarship. First of ALL, the minimum scores of various scholarship requirements must be queried from the scholarship table. Because first-class scholarships require the highest scores. Only when a student's score is higher than the minimum score of ALL scholarships can he get a first-class scholarship. The ALL keyword is also often used with comparison operators.

**Example: * * query the names and ages of students in other departments who are younger than all students in the information department.

Method 1: use ALL predicate

SELECT  Sname,Sage
   FROM Student
      WHERE Sage < ALL
              (SELECT   Sage
                    FROM    Student
                      WHERE    Sdept= ' IS ')
                         AND Sdept <> ' IS ';

Example: query the names and ages of students younger than all students in the information department in other departments.

Method 2: set function

SELECT Sname, Sage
	FROM Stu
		WHERE Sage < 
 			(SELECT MIN(Sage)
 				FROM  Stu
 					WHERE  Sdept= 'IS')
						AND Sdept <>'IS';

Merge query results

Merging query results is to merge the query results of multiple SELECT statements together. Because in some cases, the results of several SELECT statements need to be merged and displayed. For example, you need to query the information of all employees of company a and company B. This requires querying the information of all employees from company a, and then querying the information of all employees from company B. Then merge the results of the two queries together. The UNION and UNION ALL keywords are used for the merge operation. When using the UNION keyword, the database system will merge all query results together and remove the same records. The UNION ALL keyword is simply merged together. The syntax rules are as follows:

SELECT Statement 1
     UNION | UNION ALL
SELECT Statement 2 
     UNION | UNION ALL ....
SELECT sentence n ;

Example: query the students in the Department of computer science and students no older than 19 years old.

Method 1:

SELECT *  FROM Student
WHERE Sdept = 'CS' 
 	UNION 
 	SELECT *  FROM Student WHERE Sage<=19

Method 2:

SELECT DISTINCT  *  FROM Student
   WHERE Sdept = 'CS' OR Sage<=19

Example: query all student numbers in the course selection table and student table

 select sno from stu 
   union all 
 	select sno from sc 

If union all is used, duplicate student numbers will not be deleted; if union is used, duplicate records will be deleted

 select  sno  from  stu 
    union   
select  sno  from  sc 

Alias tables and fields

When querying, you can take an alias for tables and fields. This alias can replace its specified tables and fields.

Format: table name table alias

**Example: * * query student number and name

select sno , sname  from  stu  s 

**Note: * * give the table stu an alias s

Example: query prerequisite courses of prerequisite courses

  select  c2.cpno  from c c1,  c c2 where c1.cpno=c2.cno

When querying data, MySQL displays the nouns of each output column. By default, the column name displayed is the column name defined by the created table. Sometimes a more intuitive name is needed to represent this column in order to display the results more intuitively.

The basic form of aliasing fields in MySQL is as follows:

Property name [AS] alias

**Example: * * query the student's student number and name, and the column names are "student number" and "name" respectively

 select sno as Student number,  sname full name from stu

**Note: * * the alias of the table cannot have the same name as other tables in the database, and the alias of the field cannot have the same name as other fields in the table. Field aliases cannot be used in conditional expressions, otherwise an error message such as Unknown column will appear.

Using regular expression queries

Regular expressions are A way to match A class of strings with A pattern. For example, A regular expression can be used to query A string containing any of the letters A, B, C. The query ability of regular expressions is more powerful and flexible than that of wildcards. Regular expressions can be applied to very complex queries.

Pattern character of regular expressionmeaning
.Match any single character
^Matches the beginning of the string
$Matches the end of the string
[...]To match any character in square brackets, you can use '-' to represent the range, such as [a-z],[0-9], and you can mix [a-dXYZ] to match any one of a,b,c, D, x, y and Z; (note that using parentheses and '|' can also achieve the same effect, such as (a|b|c) matching any one of a, B and C)
[^ ...]In addition, '' can be used to indicate negation, for example, [a-z] means that it does not contain any character in the middle of a-z;
*Represents matching 0 or more characters before it. For example, X * represents 0 or more x characters, and. * represents any character that matches any number
+Indicates that it matches one or more characters that precede it. For example, a + represents one or more a characters.
String {N}The string appears N times, a{5} indicates a total of 5 a matches, and a{2,8} indicates 2 ~ 8 A matches.
String {M,N}The string appears at least M times and at most N times

Basic form:

Attribute name regexp 'matching method'

**^: * * matches the beginning of the character

**Example: * * query records starting with a from sname field of stu table

 select  *  from stu  where  sname  regexp '^a' 

Example: query records whose names begin with "aaa"

  select  * from stu  where sname regexp  '^aaa' 

$: matches the end of the character

Example: query the record ending with c from the name field of info table

 select * from info where name regexp 'c$'; 

Example: the query name starts with 'L' and ends with 'Y', with any one character record in the middle

  select * from stu where sname REGEXP  '^L.Y$'

[character set]: matches any character in the character set

Example: query the name field of info table for records containing any one of c, e and o

select  *  from  info  where  name  regexp  '[ceo]' 

[^ character set]: matches any character except the character set

Example: query records containing characters other than a-w letters and numbers from sname field of stu table

   select * from stu  where name regexp '[^a-w0-9]'; 

Example: query the record containing 'ic' from the name field of info table

 select * from stu  where name regexp 'ic'; 

Example: query the name field of info table for records containing any one of ic, uc and ab strings

  select *  from stu  where sname regexp 'ic|uc|ab'; 

*: represents multiple characters before this character, including 0 or 1

Example: query the name field of info table for the record of a before c

 select  *  from  stu  where sname regexp  'a*c'; 

+: represents multiple characters before this character, including 1

Example: query the sname field of stu table for the record of a before c

   select * from info where name regexp 'a+c';/*(Pay attention to the comparison results!) */

String {N}: string appears N times

Example: query the sname field in the info table for records that have 'a' 3 times

select * from stu where name regexp 'a{3}'; 

String {M, n}: the string appears at least M times and at most N times

Example: query the sname field of stu table for records with "ab" appearing at least once and at most three times

  select  * from  stu  where  sname  regexp  'ab{1,3}'; 

view

A view is a table derived from one or more tables. It is a virtual table. The view is like a window through which you can see the data provided by the system. In this way, users can not see the data in the whole database table, but only care about the data that is useful to themselves. View can make user's operation more convenient, and can ensure the security of database system.

A view is a virtual table exported from one or more tables in a database. Its function is to facilitate users' operation of data.

A view is a virtual table. View a table that is exported from one or more tables in the database. Views can also be defined from existing views. The database only stores the definition of the view, not the data in the view. These data are stored in the original table. When using the view to query data, the database system will retrieve the corresponding data from the original table. Therefore, the data in the view depends on the data in the original table. Once the data in the table changes, the data displayed in the view will also change.

View is a virtual table redefined on the basis of the original table or view, which can select useful information for users from the original table. Information that is useless to users or that users do not have permission to know can be directly blocked. This not only simplifies the application, but also ensures the security of the system. Views act like filters.

In MySQL, view creation is realized through SQL statement CREATE VIEW. Its grammatical form is as follows:

CREATE [ ALGORITHM = { UNDEFINED | MERGE | TEMPTABLE } ]
   VIEW View name [ ( Attribute list ) ]
AS SELECT sentence
  [ WITH [ CASCADED | LOCAL ] CHECK OPTION ] ;

ALGORITHM: represents the view selection ALGORITHM

UNDEFINED: mysql will automatically select the algorithm to be used

MERGE: MERGE the statement using the view with the view definition so that a part of the view definition replaces the corresponding part of the statement

TEMPTABLE: store the results of the view in a temporary table, and then use the temporary table to execute the statement

CASCADED: all related view and table conditions must be met when updating the view. This parameter is the default value;

LOCAL: when updating a view, you need to meet the definition conditions of the view itself

1 create a view on a single table

In MySQL, you can create views on a single table in the following format:

create  view  View name
	 as  select sentence
	[with [cascaded| local] check option]

cascaded: indicates that the conditions of all related views and tables should be met when updating the view. This parameter is the default value;

local** 😗* Indicates that when updating a view, the defined conditions of the view itself should be met;

Note: when using the create view statement to create a view, it is best to add the with check option parameter and the cascaded parameter. After a new view derived from the view is updated, the constraints of its parent view need to be considered, so as to ensure the security of the data.

Example: establish a view of information department students.

CREATE VIEW IS_Student 
 AS  SELECT Sno , Sname , Sage FROM Student 
	WHERE Sdept = 'IS' ; 

Note: the column name of the view table is consistent with the column name queried by the select statement. There is no need to give the column name of the view chart

**Example: * * create a view of information department students.

 CREATE VIEW  IS_Student1 (Student number,full name,Age)
     AS 
       SELECT  Sno,   Sname,  Sage  FROM    Stu
            WHERE  Sdept= 'IS';

Note: when the column name of the view is inconsistent with the column name queried by the select statement, the column name in the view chart must be given

**WITH CHECK OPTION: * * when adding, deleting or modifying a view, the predicate condition in the view definition (that is, the condition expression in the subquery) must not be destroyed
Example: create a view of information department students and require that the update operation through this view only involves information department students.

 create  view   is_Student
        as 
      select    Sno,Sname,Sage
            from   Student
                where   Sdept= 'IS'
            with  check   option  

2 create views on multiple tables

**Example: * * create a view sc_student

create  view  sc_student (sno,sname ,cno,grade)
  as  select  sno, sname, cno, grade from stu, sc where stu.sno =sc.sno

Viewing a view is to view the definition of a view that already exists in the database. You must have show view permission to view the view. This information is saved in the user table under the mysql database. The methods to view views include describe statement, show table status statement, show create view statement and query information_ Views table under schema database, etc.

1. Use the describe statement to view the basic information of the view

Format: describe view name

View name: the name of the view to view

Example: view IS_Student information

  describe  IS_Student

2. Use the show table status statement to view the basic information of the view

Format:

show  table  status  like'View name'

like: indicates that the string is matched later

**View name: * * refers to the name of the view to be viewed, which should be enclosed in single quotation marks

Although show table sattatus can view the basic information of the view, it is rarely used because when using this statement to query the view information, the values displayed by each attribute are null, and only the value displayed by the comment attribute is view

Example: query IS_Student view information

show  status  like  'IS_Student'

3 show create view statementview the view information

Format:

 show  create view  View name 

Example:

show  create  view  IS_Student 

4 view the view details in the views table

 select  * from  information_schema.VIEWS

Note: the show create view statement can view the details of the view. If you want to know the details, you can use this statement. All view definitions are stored in information_ In the views table under the schema database, you can also view the view definition in this table.

5 modify view

Use the create or replace view statement to modify the view. The syntax is as follows:

In MySQL, the CREATE OR REPLACE VIEW statement can be used to modify the view. The use of this statement is very flexible. Modify the view when it already exists; When a view does not exist, you can create a view. The syntax of the CREATE OR REPLACE VIEW statement is as follows:

create or replace  [ alogorithm= {undefined|  merge |temptable} ]
view   View name [ ( Attribute list ) ]
 	as  select sentence
[ with [cascaded| local] check option] ;

5.1 use the create or replace view statement to modify the view

Example: modify v_stu view

(1) Create view v_stu

create  algorithm=temptable
	view   v_stu1( Student number, full name, Age)
	as  select  sno,  sname,  sage  from  stuent

(2) Modify view

create or replace  algorithm=temptable
	view   v_stu( sno,  sname ,sage)
	as   select  sno,  sname,  sage  from  stuent

The Create or replace view statement can not only modify an existing view, but also create a new view.

5.2 modify the view with alter statement

In MySQL, the ALTER statement can modify the definition of a table and create an index. Not only that, the ALTER statement can also be used to modify the view. ALTER statement modifies the syntax format of the view as follows:

 ALTER [ ALGORITHM = { UNDEFINED | MERGE |  TEMPTABLE } ]
	VIEW View name [ ( Attribute list ) ]
	AS SELECT sentence
[ WITH [ CASCADED | LOCAL ] CHECK OPTION ] ;

**Example: * * modify V with alter statement_ Stu view

alter view v_stu 
 	as select stu.sno , sname , ssex ,  grade
 from stu,sc where stu.sno = sc.sno 

Non updatable view

Updating a view means inserting, updating, and deleting data in a table through a view. Because the view is a virtual table with no data in it. When the view is updated, it is converted to the basic table to UPDATE. When updating a view, you can only UPDATE the data within your permission. If it is out of range, it cannot be updated.

The view cannot be updated when:

(1) The view contains functions such as sum(),count(),max() and min()

Example: create a view to store the number of students of each gender in the student table

create  view  stu_view(sex,total)
	as  select  ssex,  count(ssex) from student

(2) The view contains keywords such as union, Uinon, all, distinct, group by and having

Example: create a view to store the selected student number of each course

 create view sc_view1(sno,cno)
	as select sno, cno from sc group by cno 

(3) Constant view

Example:

 create view view_sc
	 as select 'aric' as name ;

Because the name field of the view has a string bright "Aric", the view cannot be updated. When updating with update, a system error will be reported.

(4) The select in the view contains subqueries

create viewstu_view ( sname)
   as select sname from student where sno in(select sno  from sc where cno ='1') 

(5) Views exported from non updatable views

Example:

create view worker_view
	 as select * from view_sc

Update view worker_view

  update  worker_view  set name='abc' 

report errors:

[Err] 1288 - The target table worker_view of the UPDATE is not updatable

(6) There is a column without default value in the table corresponding to the view, and the column is not included in the view. For example, the name field in the table has no default value, but the field is not included in the view. This view cannot be updated, because when updating the view, the record without default value will have no value insertion and no null value insertion. The database system will not allow this to happen, and it will organize the update of this view.

When a view is not needed, you can delete it. When deleting a view, you can only delete the definition of the view, not the data. Delete one or more views. Use the DROP VIEW statement. The syntax format is as follows:

DROP VIEW [IF EXISTS] 
     View_name [,view_name1]...... 
          [RESTRICT | CASCADE]

explain:

View_name: indicates the name of the view to be deleted. Multiple view names can be added and separated by commas

IF EXISTS: determines whether the view exists. If it exists, it will be executed; if it does not exist, it will not be executed;
Note: you must have DROP permission to delete a view.

Database integrity constraints (mysql tutorial)

1 primary key constraint

2 uniqueness constraint

Example:

create table s(sno varchar(10),
 	sname char(10),
 	unique(sno))

3 reference integrity constraints

Example:

create table sc ( sno char(10),
	cno char(10),
	Grade int ,
	constraint f_c foreign key ( sno ) references s(sno)
                 on delete  restrict
                 on update restrict )

The student number in the Sc table must exist in the s table

To delete a foreign key constraint:

 alter  table sc   drop  foreign  key f_c

3 reference integrity constraints

Example * *:

Create table sc(sno char(10),
	cno char(10),
	grade int ,
	primary  key(sno,cno),
	foreign  key(sno)  references s(sno) on update  cascade )

Example: when updating the s12 table, the student number in the sc table also changes

create table s12(sno char(10) primary key)
	insert into s12 values('2015142101')
Create table sc1(sno  char(10),
	cno char(10),
	grade  int,
	primary  key(sno,cno),
	foreign  key(sno)  references s12(sno) on update cascade)

insert into sc1 values('2015142101','1',80)
update s12 set sno ='2015142102' where  sno ='2015142101'

4 check integrity constraints

check can be defined as a column integrity constraint or a table integrity constraint.

The syntax format is as follows:

Check (expression)

Example: create a student table with two columns: student number and gender. Gender can only be male or female.

create table student
	(sno char(10) not null,
	sex char(10) not null
	check(sex in('male','female')) )

4 check integrity constraint (invalid)

Example: create a student table containing the student number and date of birth of the student. The date of birth must be greater than January 1, 1990

create table student (sno char(10),
	birthday date not null 
	check(birthday>'1990-01-01'))
insert  into  student1 values('2012142101','1990-01-05')

Example: create a student table student2, which only contains student number and gender, and all values in the gender column are derived from the gender column of the student table.

Create table student3 (sno char(10) not null , 
                       sex char(10) not null ,
                      check(sex in(select sex from student)))

Example: create a student table with student number, best score and average score. It is required that the best score must be greater than the average score.

Create table student (sno char(10),
		max_grade int ,
        avg_grade int ,
        check (max_grade>avg_grade))
 

5 delete integrity constraint

If a drop table statement is used to delete a table, all integrity constraints are automatically deleted. All foreign keys of the referenced table are also deleted. Using the alter table statement, the integrity can be deleted independently without deleting the table itself. The syntax for deleting integrity constraints is the same as that for deleting indexes.

create table sc ( sno char(10),
	cno char(10),
	Grade int ,
	constraint f_c foreign key (sno) references s(sno)
	on delete    restrict
	on update   restrict )

The student number in the Sc table must exist in the s table

Delete the foreign key of the created table sc:

    alter  table  sc   drop  foreign  key  f_c 

Tags: Database MySQL

Posted on Fri, 26 Nov 2021 17:56:21 -0500 by BizBoy