catalogue
1, Data Definition Language DDL
(3) Use database (enter database)
③ Modifying columns in a table
2, DML data manipulation language
② Insert partial attribute values
③ Insert multiple pieces of data
③ Multi condition modification
1, Constraint correlation concept
3. Implementation of data integrity
4.1 classification of constraints:
4.2 various constraints and their descriptions are as follows:
2. Modification of table constraints
① Add unique constraints when creating tables:
② To add a unique constraint to a specified column:
3.1. Compound unique constraint
① Add a compound unique constraint when creating a table
② Add a compound unique constraint after the table is created
③ The compound unique constraint is deleted (according to the constraint name)
① Set single column primary key when creating a table
② Set multiple attributes as federated primary keys
③ Delete primary key constraint
① Add autoincrement column when creating table
③ Delete auto increment column
① Set default constraints when creating tables
② Add / remove default value constraints
① Establish foreign key constraints when creating tables
③ Delete foreign key constraint
3, DDL and DML
1, Data Definition Language DDL
1. Database creation
(1) Create database
① View database
show databases;
② Create database
create database <Database name>;
Example:
create database db_1; -- Create a database named db_1
③ Create a database if the database name does not exist:
create database [if not exists] <Database name>; -- If the database name does not exist, the database is created; otherwise, it is not created
(2) Delete database
drop database <Database name>; -- Delete name is<Database name>Database of
Example:
drop database DB_1; -- Delete database DB_1
(3) Use database (enter database)
use Database name; -- Enter the database. Only after entering the database can you create a data table in the database
Example:
use DB_1; -- Use database DB_1
2. Creation of data table
(1) Create data table
create table [if not exists] <Table name> ( Property name 1 Attribute type [constraint...], Property name 2 Attribute type [constraint...], Attribute name n Attribute type [constraint ] )[engine innodb] [charset=utf8]; # Create a table and set its property name, data type, storage engine, and character set
For example:
create table students( -- Create a student data table s_id int, -- Student number s_name varchar(10), -- full name s_sex varchar(4), -- Gender s_age int, -- Age s_cid int -- Class );
(2) View table structure
desc Table name; -- View the attributes, data types, constraints, etc. of the data table
Or:
show columns from Table name;
For example:
desc students; show columns from students;
(3) Modify table structure
① Add column in table
alter table <Table name> add <Attribute name> <data type>; -- Add an attribute column to the data table
For example:
alter table students add s_score float; -- To data sheet students Add an attribute column to the s_score
② Delete columns in table
alter table <Table name> drop column <Attribute name>; -- Delete an attribute column in the data table
For example:
alter table students drop column s_cid; -- Delete data table students in s_cid attribute
③ Modifying columns in a table
alter table <Table name> modify column <Attribute name> <data type>; -- Modify the data type or constraint of an attribute column in the data table
For example:
alter table students modify column s_name varchar(30); -- Attribute s_name The data type of is modified to varchar(30) alter table students modify column s_score double; -- Attribute s_score The data type of is modified to double
④ Modify column name
alter table <Table name> change column <Old column name> <New column name> <data type>; -- Modify the name of an attribute in the data table
For example:
alter table students change column s_id s_num int; -- Attribute name s_id Change to s_num
(4) Delete data table
drop table <Table name>; -- Delete data table
For example:
drop table students; -- Delete data table students
(5) View data sheet
show tables; -- View the data tables that exist in the database you are using
For example:
(6) Modify table name
rename table <Original table name> to <New table name>; -- Modify the name of a data table
2, DML data manipulation language
1. Data insertion
① Insert all attribute values
insert into <Table name> [value | values](Attribute value 1,Attribute value 2,......,Attribute value n);
For example:
insert into students_tb value(1,'Xiao Ming','male',16,2); -- towards students_tb Insert a piece of data into the table
② Insert partial attribute values
insert into <Table name>(Property name 1,Property name 2,......,Attribute name n) [value | values](Attribute value 1,Attribute value 2,......,Attribute value n);
For example:
insert into students_tb(s_id,s_name) value(2,'floret');
insert into students_tb(s_name,s_id,s_age,s_sex,s_cid) value('Xiao Wang',3,18,'male',1);③ Insert multiple pieces of data
insert into students_tb values(4,'Little fat','male',17,3),(5,'Bob','male',18,2),(6,'C','female',15,1);-- Use commas between multiple pieces of data','Separate
2. Modify data (update data)
① Modify all data
update <Table name> set <Attribute name> = <Attribute value>; -- Modify the value of an attribute in the data table
For example:
update students_tb set s_sex = 'female'; -- modify students_tb In the table s_sex The value of the attribute is'female'
② Condition modification
update <Table name> set <Attribute name> = <Attribute value> [where <Conditional expression>]; -- Modify a property value that meets the condition in the data table
For example:
update students_tb set s_sex = 'male' where s_name = 'Xiao Ming'; -- modify'Xiao Ming'Your gender is'male'
③ Multi condition modification
update students_tb set s_sex = 'male' where s_name = 'Little fat' || s_name = 'Xiao Wang' or s_name = 'bob';-- Multiple conditions satisfy any one'||','or','OR'(Logical or) update students_tb set s_age = 16,s_cid = 1 where s_name = 'floret'&& s_id = 2; -- Multiple conditions are met at the same time'&&','and','AND'(Logic and)Multiple conditions are satisfied at the same time(&&，and,AND)， Meet any one of them or(||，or，OR) where The following conditional statements can use comparison operators:=,>,<,>=,<=,!=(<>)
3. Delete data
① Delete all data
delete from <Table name>; -- Delete all data in the data table
For example:
delete from students_tb;
② Condition deletion
delete from <Table name> where <Conditional expression>; -- Delete the data that meets the conditions in the data table
For example:
delete from students_tb where s_name = 'floret';
③ Multi condition deletion
delete from students_tb where s_name = 'Little fat' || s_id = 5;
--Note: they are also deleted. The difference between delete and drop
Delete is used when deleting data in a data table. It is used to delete data relative to data.
drop is used when deleting database objects. It is used to delete database objects (such as database, data table, function, view, etc.) relative to the database.
#The difference between alter and update is similar to that between delete and drop
4, Database integrity
1, Constraint correlation concept
1. What is data integrity
Data integrity means that the data stored in the database should correctly reflect the actual situation, and the input data must not be invalid value, wrong value or garbled code.
2. Type of data integrity
① Entity integrity: the integrity of an identifier or primary key to make its value unique.
② Domain integrity: limit type, format, value range, etc.
③ Referential integrity: keep the data consistency between the original table and the reference table.
④ Custom integrity: user defined business rules
3. Implementation of data integrity
① Entity integrity: primary key constraint, unique value constraint
② Domain integrity: check constraints, default constraints
③ Referential integrity: foreign key constraints
④ User defined integrity: free combination of the above constraints
4. Restraint
Data integrity constraints, that is, constraints when creating a table, are used to restrict attributes or data in the table. You can also modify constraints through alter table.
4.1 classification of constraints:
Constraints are divided into row level constraints (the scope of constraints is only for rows) and table level constraints (affecting the constraints between tables). Row level constraints and table level constraints are essentially the same, but the effectiveness and scope of constraints are different.
Non empty constraints, default value constraints and primary key constraints are generally row level constraints;
Foreign key constraints (Federated primary keys) are table level constraints;
Check constraints can be either row level constraints or table level constraints
4.2 various constraints and their descriptions are as follows:
2, Integrity constraints
1. Non NULL constraint
Ensure that the property of the current constraint is not null. Non null constraints can only appear in the column of the table object. They are row level constraints. Indicates that the value of this attribute cannot be empty. If the inserted data is empty, it will be blocked by the system. (if no attribute value is given in the database, the attribute value is empty by default)
Non empty constraint keyword: not null
Syntax format for creating non empty constraints:
create table <table_name>( no int not null, name varchar(20) not null ); #Create a table so that the number and name cannot be empty
2. Modification of table constraints
# Modify the general format of row level attribute constraints in the table:
Example:
alter table <Table name> modify column <Attribute name> <data type> [constraint];
Example:
alter table teachar modify column t_name varchar(20) not null; # Modify the attribute constraint in the table as a non empty constraint:
#Remove non empty attribute constraints by modifying the table:
alter table teachar modify column t_no varchar(20);
Note: when modifying table attributes, the data in the original table will be checked. If there is a value conflict, the table constraint cannot be modified.
3. Unique constraint
The values of a specified column or columns of data cannot be repeated.
Unique constraint keyword: unique
① Add unique constraints when creating tables:
create table teacher_tb(
t_no int not null unique,
t_name varchar(20)
);② To add a unique constraint to a specified column:
alter table <Table name> add Constraint type(Attribute name); #Example: you can alias constraints through constraint alter table <Table name> add constraint <Constraint name> unique(Attribute name);
③ Delete unique constraint:
alter table <Table name> drop index <Attribute name/Constraint name>;
3.1. Compound unique constraint
Create a compound unique constraint (table level constraint), which can uniquely constrain multiple columns. More than two fields (attributes) cannot be consistent at the same time.
① Add a compound unique constraint when creating a table
constraint <Constraint name> unique(<Attribute 1>[,Attribute 2][,...attribute n])
② Add a compound unique constraint after the table is created
alter table <Table name> add constraint <Constraint name> unique(<Attribute 1>[,Attribute 2][,...attribute n]);
③ The compound unique constraint is deleted (according to the constraint name)
alter table <Table name> drop index <Constraint name> # Here, in constraint attribute 1, attribute 2 to attribute n, attributes with the same value can be allowed, but they can't be the same at the same time, and there must be at least one difference.
4. Primary key constraint
# If a data table in the database has no primary key constraint, the first non empty and unique attribute is the primary attribute (primary key) by default
1. The primary key constraint is a non empty constraint + unique constraint, which is used to specify entity integrity.
2. And each table can only have one primary key constraint (table level). In the case of multi column joint primary key constraints, the values of joint primary keys cannot be repeated at the same time.
Primary key constraint keyword: primary key.
① Set single column primary key when creating a table
create table <Table name>( Property name data type primary key, # Set primary key constraint property name (data type not null)
② Set multiple attributes as federated primary keys
alter table <Table name> add [constraint <Constraint name>] primary key(Attribute 1[,Attribute 2,...,attribute n]); -- If the field to which the primary key is added in the table already has null or duplicate values, it is not allowed to add the primary key.
③ Delete primary key constraint
alter table <Table name> drop primary key; # Lose the primary key directly
5. Auto increment
Auto increment column means that when inserting data, if the value of the attribute is not specified, its value will be automatically increased by 1 according to the value of the previous attribute.
Auto increment keyword: auto_increment (row level constraint)
① Add autoincrement column when creating table
create table <Table name>( id int primary key auto_increment, # Set id to auto increment column);
② Add auto increment column
alter table <Table name> modify column <Attribute name> <data type> auto_increment = 100; # You can set the initial value of auto increment column (the default value is 1)
③ Delete auto increment column
alter table <Table name> modify column <Attribute name> <data type>;
6. Default value constraint
When adding data to an attribute with a default value constraint, if no attribute value is given, a default value is automatically added.
Default value constraint keyword: default
① Set default constraints when creating tables
create table <Table name>( name varchar(20) not null,sex varchar(2) default 'male'); # Gender defaults to 'male'
② Add / remove default value constraints
alter table <Table name> modify column <Attribute name> <Attribute type> [default <Default value>];
7. Check constraints
Check constraint is to check the value range of attributes in the column and judge whether the value of the field is the specified value. If not, data insertion or modification is not allowed.
Check constraint keyword: check
For example:
age int check(age>=0 and age=<150), # The value range of constraint age is 0 ~ 150 sex varchar(2)check(sex='male' or sex='female') # The value range of constraint gender is' male 'or' female '
#Note: MySQL does not support checking constraints, which needs to be judged externally
8. Foreign key constraint
Foreign key constraint refers to that the attribute value in one table refers to the value of the primary attribute in another table, that is, the foreign key value refers to the primary key value. Because the foreign key constraint is a table level constraint, the referenced table must be created before creating the reference table.
① Establish foreign key constraints when creating tables
constraint <Constraint name> foreign key(<Attribute name>) references Referenced table(<Referenced attribute name>)
② Add foreign key constraint
alter table <Table name> add constraint <Constraint name> foreign key(<Attribute name>) references Referenced table(<Referenced attribute name>);
③ Delete foreign key constraint
alter table <Table name> drop constraint <Constraint name>; # Delete constraint based on constraint name
9. Cascade update / delete
New / delete refers to the processing methods of tuple update or deletion in the parent table on the child table. There are three processing methods:
#In update / delete: (on update/on delete)
--① cascade: tuples in the parent table are updated or deleted, followed by updates or deletes in the child table
#After the foreign key constraint, add: on update cascade/on delete cascade
--② No action: no action. If there are matching records in the child table, it is not allowed to update / delete the data of the parent table
#Add: on update no action/on delete no action after the foreign key
--③ set null: set to null. If there are matching records in the child table, update / delete the data in the parent table, and set the corresponding data in the child table to null
#Add: on update set null/on delete set null after the foreign key constraint
You can set the corresponding operations for update and deletion at the same time
--After the foreign key constraint, add: on update cascade on delete no action
#When the parent table updates data, if there are matching records in the child table, the child table updates the matching data synchronously, and if there are matching records in the child table, the parent table data cannot be deleted
Example:
create table student1( s_id int primary key, s_name varchar(20) not null, s_cid int not null, s_age int,s_sex varchar(2), constraint for_scid foreign key(s_cid) references class(class_id) on update cascade on delete set null ); # Class number in student table s_cid refers to the class number in the class table_ id