Shulou(Shulou.com)05/31 Report--

This article mainly introduces the mysql DML advanced, paging search, SQL constraints and multi-table operation related knowledge, detailed and easy to understand, simple and fast operation, has a certain reference value, I believe that after reading this mysql DML advanced, paging search, SQL constraints and multi-table operation method article will have a harvest, let's take a look at it.

one。 What is DML, and the basic operation of DML, the column and row of the table are followed by new operations to modify the column # first, simply create an student table to prepare for the subsequent operation use test;create table student (id int, name varchar (8), age tinyint) engine = innodb default charset = utf8mb4;desc student

Add a new column, format: alter table table name add new column name data type (length)

Alter table student add addr varchar (20); # add a new addr list

Modify the data type (length) of the column, format: alter table table name modify column name modified data type (length)

Alter table student modify addr varchar (15); # modify the data type of the addr column in the student table (length modification) alter table student modify addr char (20); # modify the data type of the addr column in the student table (type changed to char (20))

Modify the name of the column, format: alter table table name change column name new column name data type (length)

Alter table student change addr stu_addr varchar (20); # change is more powerful than modify to modify column names. # modify cannot modify column names

. Delete the specified column, format: alter table table name drop column name

Alter table student drop stu_addr;# deletes the stu_ addr column from the student table

In view of the above, it is not recommended to modify the column structure of the table, because the databases of many companies are particularly large, and it is not a small thing to modify a column of data. If the modification is not good, the data will be lost.

For the rows of the table, the operations of the table record (addition, deletion, modification and query) are inserted into the table record

Method 1: insert the specified field in the format: insert into table name (field 1, field 2,...) Values (value 1, value 2,...); I call this the insertion operation of the specified field, where the field and the assignment correspond one to one.

Insert into student (id, name, age) values (1000, 'Zhang San', 18); # insert a record insert into student (id, name, age) values (1001,'Li Si', 20), (1002, 'Wang Wu', 22), (1003, 'fat man', 25) into the student table with an id of 1000 name and an age of Zhang San and age 18; # can also support values to follow multiple records # separate each record

Method 2, insert values into all fields in the format: insert into table name values (value 1, value 2,...)

That is to say, by default, all fields are inserted sequentially, so you don't have to write them down.

Insert into studentvalues (1004,'Li Si his father', 47), (1005, 'Wang Wu his mother', 40), (1006, 'fat man is old and special', 20); # No fields can be specified in this way, and all fields can be inserted in the default order

Summary insertion operation notes:

Values and fields must correspond to the same number and type

The data size of the value must be within the specified length of the field

Except for integer / decimal types, values of other field types must enclose quotation marks (single quotation marks are recommended)

If you want to insert a null value, you can leave the field unwritten, or insert null

Update table records

Syntax format: update table name set field 1 = value, field 2 = value. Where condition

Update student set name = 'fat man's father' where id = 1005 th # and the record id = 1005 in the new student table is the fat man's father

Update student set name ='I am your father', age = 1005 where id = 1005 id # and id = 1005 in the new student table, the record name is I am your father, and the age is 100

Summary and new record operation notes:

The type of the column name should be the same as the modified value

The value cannot be modified beyond the length range of the field

Except for integer / decimal types, values of other field types must be enclosed in quotation marks

Delete table record

Syntax format: delete from table name where condition

Delete from student where id=1005; # Delete the record id=1005 from the student table delete from student where name= Fatty # Delete the record of name= Fatty from the student table

It is necessary to practice the operation of adding, deleting, changing and checking records, because it is quite common to modify, delete, add and so on specific records in the database.

Solve the problem of paging lookup

What is this up there? I believe that you like to search records or shopping is no stranger, this way to display records is paged to display records (paged search)

Paging query format: select * from table name limit startRow, pageSize

# simulate such a scenario with 5 pieces of data per page select * from student limit 0,5; # first page select * from student limit 5,5; # second page select * from student limit 10,5; # third page

Throw the question, at this time we all know the number of pieces of data on each page, the page size is fixed, the question is if we need to query the number of pages to determine startRow?

-- the page number and number of pages (page size) are calculated at the background.-- the results of the relevant data required for paging are analyzed as follows.-- Note: the following pseudo code is not used to execute int curPage = 2;-- current number of pages int pageSize = 5;-- number of pages displayed int startRow = (curPage-1) * pageSize;-- current page, the position (number of rows) at the beginning of the record is calculated.

In fact, we just need to know the current number of pages (- 1) * pageSize; to know startRow

three。 Definition of SQL constraint detailed solution constraint

Unexpectedly, we need to learn about constraints. first of all, let's figure out what a constraint is. In fact, it's quite simple. A constraint is a kind of constraint, so that you can't go beyond this scope of control.

The constraint in the database means that the data in the table cannot be filled in randomly and must be filled in in accordance with the requirements. To ensure the integrity and security of the data.

Primary key constraint PRIMARY KEY constraint

What is a primary key constraint: the only constraint that is not empty. The primary key constraint is not NULL and uniquely identifies a record. Almost every table must have such a constraint.

Add primary key constraint

Method 1: when creating a table, at the field description, declare the specified field as the primary key:

Format: field name data type [length] primary key

Create table user_table (id int primary key, # add primary key constraint name varchar (10), age tinyint) engine = innodb charset = utf8mb4

The primary key constraint uniquely identifies the record and cannot be empty

Insert into user_tablevalues (1001, 'Cuihua', 18); # insert the first record Cuihua is no problem insert into user_tablevalues (1001, 'Wang Wu', 20); # insert this record should be an error, repeatedly insert the primary key # [Err] 1062-Duplicate entry '1001' for key' PRIMARY'# repeatedly add 1001 as the primary key

Primary key constraint cannot be empty (distinguish unique primary key)

Insert into user_tablevalues (null, 'Big Fat', 30); # distinguishing unique constraints, primary key constraints cannot be null# [Err] 1048-Column 'id' cannot be null

Method 2: when creating a table, declare the specified field as the primary key in the constraint constraint area

Syntax form: [constraint name] primary key (field list)

What is the need to appear? The need for this approach is to add a federated primary key. The use of the federated primary key will be applied in the middle table below. Here we first learn how to create the

Create table persons (pid int, lastname varchar (255), firstname varchar (255), address varchar (255), constraint persons_pk primary key (lastname, firstname) # adds a joint primary key through constraint) engine = innodb default charset = utf8mb4

Consider why there is a need for the constraint of a joint primary key. The primary key must identify different records. Sometimes there is a situation where we need to use name and gender to identify different objects. (unfortunately, there is a situation where both boys and girls are called Wang Yujie, and only the name cannot be distinguished. At this time, you can combine other fields to form a primary key to constrain identification.)

Method 3: after creating the table, declare the specified field as the primary key by modifying the table structure:

Format: altertable table name add [constraint name] primary key (field list)

Alter table user_table add constraint name_id_pk primary key (name, id); # add a joint primary key of name + id to the user_table table to delete the primary key constraint

Format: alter table table name drop primary key

Alter table user_table drop primary key;# removes the primary key constraint auto-growing column from the user_ table table (describes how the primary key constraint can be separated from it)

We usually want the database to automatically generate the value of the field each time a new record is inserted

Especially for the primary key field, if it is only recorded as a mark, there is no need for us to set the value.

We can use the auto_increment keyword in the table, the auto-grow column type must be shaped, and the auto-grow column must be a key (usually for the primary key)

Format: field name integer type [length] [constraint] auto_increment

Create table test (id int primary key auto_increment, # add a primary key constraint to set automatic growth. The default growth is 1 age tinyint, name varchar (20) engine = innodb default charset = utf8mb4;insert into test values (null, 18, 'small snore'); # We set the primary key increment automatically so that there is no need to pass in the primary key field # or the primary key passes in null. He will automatically set the default increment of 1 starting from 1.

Or when insert into does not pass anything, OK, null can not be passed in.

However, if we want to use it like the above, what we must pay attention to is to specify the field insertion, otherwise all three values are given by default. When we specify to insert the field, we do not need to specify the id, which is a bit like the default value.

Non-empty constraint

NOT NULL constraint: columns do not accept NULL values. Requires that the field always contain a value. This means that you cannot insert a new record or update a record without adding a value to the field.

Add a non-empty constraint

Format: field name data type [length] NOT NULL

Drop table test;create table test (id int primary key auto_increment, name varchar (10) not null,# sets non-null insert data cannot be passed into null age tinyint) engine = innodb auto_increment = 10 default charset = utf8mb4;# We can also specify the auto_increment value insert test values (null, null, 28) # error occurs. Not null constraint is added to the second field. # empty will report an error [Err] 1048-Column 'name' cannot be null delete non-null constraint

Format: alter table table name modify field name data type [length]

Alter table test modify name varchar (10); # A very simple way to change the data type directly without null constraints to desc test

Unique constraint

Unique constraint: the value of the specified column cannot be repeated.

Note:

Both UNIQUE and PRIMARY KEY constraints provide a guarantee of uniqueness for columns. PRIMARY KEY is an automatically defined UNIQUE constraint.

Each table can have multiple UNIQUE constraints, but each table can only have two PRIMARY KEY constraints.

UNIQUE does not limit the number of times null values appear

Add only constraint

In the same way as the primary key, there are 3 kinds. Let me give you a few examples here.

Drop table test;create table test (id int unique, # add a unique constraint name varchar (20) not null, age tinyint) engine = innodb default charset = utf8mb4;desc test;insert into test values (null, 'Zhang San', 19); # allow incoming null to distinguish primary keyinsert into test values (1,'Li Si', 30), (1, 'Wang Wu', 38) # error report [Err] 1062-Duplicate entry'1' for key 'id'

Format 2: [constraint name] UNIQUE (field) corresponds to primary key mode 2

Format 3: ALTER TABLE table name ADD [CONSTRAINT name] UNIQUE (field) comparison method 3

Delete a unique constraint in the same way that you just deleted the primary key constraint default constraint

Default constraint: used to specify field default values. When a record is inserted into the table, if the field is not explicitly assigned, the default value is automatically assigned

Add default constraint, add format when creating table: field name data type [length] DEFAULT default

CREATE TABLE persons (pid INT, lastname VARCHAR, firstname VARCHAR, address VARCHAR) DEFAULT 'Hong Kong'-add default constraint) engine = innodb default charset = utf8mb4;# input null, then insert into persons (pid, lastname, firstname) values (2, 'Dehua', 'Liu') is assigned according to default; # or specify field, default field can not be passed val

Summary

About the column operation of the table (add, delete, change and query) the beginning of the alter keyword followed by the add modify change drop

Alter table table name add column name type (length) add a column

Alter table table name modify column name oldtype newtype for a column only type modification

Alter table table name change old column name new column name oldtype newtype for a column can make type + list modification

Alter table table name drop column name; delete for one column

About the record operation of the table (add, delete, change and check)

Insert into table name (specified field) values (specified value), (specified value); specify insert field value (insert record)

Insert into table name values (all fields write values in order); insert field values in the order in which the table fields are created

Update table name set field = value where condition specifies the record change record

Delete from table name where condition specifies that the record deletes the record that meets the condition from the specified table

Learning about various constraints

A constraint is a restriction.

Primary key constraints (equivalent to a combination of unique constraints and non-null constraints) to uniquely identify records in a table

Unique constraint, which is also non-repeatable, column field values are unique, but allowed to be null

Non-null constraint. It is not allowed to input null as a parameter for null.

Default constraint. If null is passed in, the default field value is the initial default.

This is the end of the article on "DML advance, paging lookup, SQL constraints and multi-table operation of mysql". Thank you for reading! I believe you all have a certain understanding of "mysql's DML advance, paging search, SQL constraints and multi-table operation methods". If you want to learn more, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.