Summary of MySQL additions, deletions and modifications

Operation of summary table

Summarize according to Master Kang's video tutorial, which is convenient for individuals to review, query and use

Create and manage tables

#Create database
CREATE DATABASE IF NOT EXISTS XXXXXX;
#
CREATE DATABASE XXXXXX CHARACTER SET 'UTF8';
#Use database
USE XXXXXX;
#modify the database
ALTER DATABASE xxxxxx CHARACTER SET 'utf8'
#Delete database
DROP DATABASE IF EXISTS xxxxxx

#Create table [syntax format]
CREATE TABLE [IF NOT EXISTS] Table name(
            Field 1, data type [constraint condition] [Default value],
            Field 2, data type [constraint condition] [Default value],
            Field 3, data type [constraint condition] [Default value],
            ......
            [Table constraints]
);

example:
-- Create table
CREATE TABLE emp (
            -- int Type, self increasing
            emp_id INT  AUTO_INCREMENT,
            -- Save up to 20 Chinese and English characters
            emp_name VARCHAR(20),
            -- The total number of digits shall not exceed 15
            salary DOUBLE,
            -- Date type
            birthday DATE
);

#rename table 
ALTER table dept RENAME detail_dept; 

#Delete table [syntax format]
DROP TABLE [IF EXISTS] Data sheet 1 [, Data sheet 2, ..., data sheet n];

example:
DROP TABLE dept80;

#Note: the DROP TABLE statement cannot be rolled back

#Empty table
TRUNCATE TABLE detail_dept;




#Append a column [syntax format]
ALTER TABLE Table name ADD [COLUMN] Field name field type[ FIRST|AFTER [field name];

example:
ALTER TABLE dept80 ADD job_id varchar(15);


#Modify a column [syntax format]
ALTER TABLE Table name MODIFY [COLUMN] Field name 1 field type[ DEFAULT [default][ FIRST|AFTER [field name 2];

example:
ALTER TABLE dept80 MODIFY last_name VARCHAR(30); 

#Delete a column [syntax format]
ALTER TABLE Table name DROP [COLUMN]Field name

example:
ALTER TABLE dept
DROP COLUMN job_id;

Addition, deletion and modification

#Inserts data for the specified field of the table
INSERT INTO Table name(column1 [, column2, ..., column n])
VALUES (value1 [,value2, ..., value n]);

example:
INSERT INTO departments(department_id, department_name)
VALUES (80, 'IT');


#Insert multiple records at the same time
INSERT INTO table_name(column1 [, column2, ..., column n])
VALUES
(value1 [,value2, ..., value n]),
(value1 [,value2, ..., value n]),
......
(value1 [,value2, ..., value n]);


#Insert query results into the table
INSERT INTO Target table name
(tar_column1 [, tar_column2, ..., tar_columnn])
SELECT
(src_column1 [, src_column2, ..., src_columnn])
FROM Source table name
[WHERE condition]

example:
INSERT INTO emp2
SELECT *
FROM employees
WHERE department_id = 90;


#Update data
UPDATE table_name
SET column1=value1, column2=value2, ... , colum n=value n
[WHERE condition]

example:
UPDATE employees
SET department_id = 70
WHERE employee_id = 113;


#Delete data
DELETE FROM table_name [WHERE <condition>];

example:
DELETE FROM departments
WHERE department_name = 'Finance';


#Calculation column
CREATE TABLE tb1(
        id INT,
        a INT,
        b INT,
        c INT GENERATED ALWAYS AS (a + b) VIRTUAL
);

constraint

Classification of constraints

  • According to the constraints of constraint data columns, constraints can be divided into:

    • Single column constraint: each constraint constrains only one column
    • Multi column constraint: each constraint can constrain multiple columns of data

  • According to the scope of action of constraints, constraints can be divided into:

    • Column level constraint: it can only act on one column, following the definition of the column
    • Table level constraint: it can act on multiple columns, not together with the columns, but defined separately

Specific:

-NOT NULL is a non NULL constraint, which specifies that a field cannot be empty

- By default, all types of values can be NULL，include INT,FLOAT Equal data type
- Non null constraints can only appear on the columns of table objects. Non null constraints can only be restricted to a column, and non null constraints cannot be combined
- A table can have many columns, which are limited to non empty
- Empty string''Not equal to NULL，0 It doesn't mean NULL

CREATE TABLE Table name(
        Field name data type,
        Field name data type NOT NULL,
        Field name data type NOT NULL
);

#Delete non empty constraints
alter table Table name modify Field name data type NULL;

-UNIQUE constraint specifies that a field is UNIQUE in the whole table

  • The same table can have multiple unique constraints.
  • The unique constraint can be the unique value of a column or the unique value of a combination of multiple columns.
  • The uniqueness constraint allows column values to be empty.
  • When creating a unique constraint, if the unique constraint is not named, it will be the same as the column name by default.
  • MySQL will create a unique index on the column with unique constraint by default.
create table Table name(
            Field name data type,
            Field name data type unique,
            Field name data type unique key,
            Field name data type
);
create table Table name(
            Field name data type,
            Field name data type,
            Field name data type,
            [constraint Constraint name] unique key(Field name)
);

example:
create table student(
            sid int,
            sname varchar(20),
            tel char(11) unique,
            cardid char(18) unique key
);

CREATE TABLE USER(
        id INT NOT NULL,
        NAME VARCHAR(25),
        PASSWORD VARCHAR(16),
        -- Using table level constraint syntax
        CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD)
        #Indicates that the combination of user name and password cannot be repeated
);

#Specify a unique key constraint after creating a table
alter table Table name add unique key(Field list);
#If it is a field in the field list, it means that the value of this column is unique. If there are two or more fields, the compound is unique, that is, the combination of multiple fields is unique

#Delete unique constraint

-PRIMARY KEY (non empty and unique) constraint

  • The primary key constraint is equivalent to the combination of unique constraint and non empty constraint. The primary key constraint column cannot be duplicated or null value is not allowed.
  • A table can only have one primary key constraint at most. A primary key constraint can be created at the column level or at the table level.
  • The primary key constraint corresponds to one or more columns in the table (composite primary key)
  • If it is a composite primary key constraint of multi column combination, these columns are not allowed to be null, and the combined values are not allowed to be repeated.
  • The PRIMARY key name of MySQL is always PRIMARY. Even if you name the PRIMARY key constraint name, it's useless.
  • When creating a primary key constraint, the system will establish a corresponding primary key index on the column or column combination by default (which can be queried according to the primary key)
    , query according to the primary key, which is more efficient). If the primary key constraint is deleted, the index corresponding to the primary key constraint will be deleted automatically.
  • One thing to note is that do not modify the value of the primary key field. Because the primary key is the unique identification of the data record, if the primary key is modified
    Value, it may destroy the integrity of the data.
create table Table name(
        Field name data type primary key, #Column level mode
        Field name data type,
        Field name data type
);
CREATE TABLE emp4(
        id INT PRIMARY KEY AUTO_INCREMENT ,
        NAME VARCHAR(20)
);


create table Table name(
        Field name data type,
        Field name data type,
        Field name data type,
        [constraint Constraint name] primary key(Field name) #Table level mode
);
CREATE TABLE emp5(
        id INT NOT NULL AUTO_INCREMENT,
        NAME VARCHAR(20),
        pwd VARCHAR(15),
        CONSTRAINT emp5_id_pk PRIMARY KEY(id)
);

#Add primary key constraint after creating table
ALTER TABLE Table name ADD PRIMARY KEY(Field list); #The field list can be one field or multiple fields. If there are multiple fields, it is a composite primary key
ALTER TABLE emp5 ADD PRIMARY KEY(NAME,pwd);

#Delete primary key constraint
alter table Table name drop primary key;

#To delete a primary key constraint, you do not need to specify a primary key name, because a table has only one primary key. After deleting a primary key constraint, non empty primary keys still exist.

-FOREIGN KEY constraint [generally not used (not allowed)]

  • Function: limit the referential integrity of a field in a table.
  • (1) The foreign key column of the slave table must reference / refer to the primary key of the master table or the column with unique constraints
    Why? Because the dependent / referenced value must be unique
  • (2) When creating a foreign key constraint, if you do not name the foreign key constraint, the default name is not the column name, but a foreign key name is automatically generated. You can also specify the foreign key constraint name.
  • (3) If foreign key constraints are specified when creating a table, create the master table first and then the slave table
  • (4) When deleting a table, delete the slave table first (or delete the foreign key constraint first), and then delete the primary table
  • (5) When the records of the master table are referenced from the slave table, the records of the master table cannot be deleted. If you want to delete data, you need to delete the dependent records in the slave table first
    The data of the record can be deleted before the data of the main table can be deleted
  • (6) Specify foreign key constraints in from table, and a table can establish multiple foreign key constraints
  • (7) The name of the foreign key column of the slave table and the referenced column of the master table can be different, but the data type must be the same and the logical meaning must be the same. If class
    If the type is different, an error will occur when creating a sub table
  • (8) When creating a foreign key constraint, the system will establish a corresponding ordinary index on the column by default. But the index name is a foreign key constraint
    Name. (high efficiency of query based on foreign keys)
  • (9) After you delete a foreign key constraint, you must manually delete the corresponding index
  • The binding relationship is for both parties
    • After the foreign key constraint is added, the modification and deletion data of the main table are constrained
    • After adding a foreign key constraint, the data added and modified from the table is constrained
    • To create a foreign key on the slave table, the master table must exist
    • When deleting a primary table, you need to delete the secondary table first, or delete the relationship that references the primary table from the foreign key in the table first

Constraint level

  • Cascade method: when updating / deleting records on the parent table, update/delete the matching records of the child table synchronously
  • Set null method: when updating / deleting records on the parent table, set the columns of matching records on the child table to null, but pay attention to the child table
    The foreign key column of the table cannot be not null
  • No action method: if there are matching records in the child table, the update/delete operation on the candidate key corresponding to the parent table is not allowed
  • Restrict mode: the same as no action, which is to check the foreign key constraints immediately
  • Set default mode (blank may be displayed in the visualizer SQLyog): when the parent table changes, the child table will set the foreign key column
    As a default value, but Innodb cannot recognize it

For foreign key constraints, it is best to use the method of ON UPDATE CASCADE ON DELETE RESTRICT.

#Add foreign key constraint
create table Main table name(
        Field 1 data type primary key,
        Field 2 data type
);
create table From table name(
        Field 1 data type primary key,
        Field 2 data type,
        [CONSTRAINT <Foreign key constraint name>] FOREIGN KEY(From a field in the table) references Main table name(Referenced field)
);
#The data type of (a field of the slave table) must be consistent with the data type of the main table name (referenced field), and the logical meaning is the same
#The field name of (a field in the slave table) can be the same as or different from that of the main table name (referenced field)
-- FOREIGN KEY: Specify the columns in the child table at the table level
-- REFERENCES: Columns marked in the parent table

example:
create table dept(   #Main table
    did int primary key, #Department number
    dname varchar(50) #Department name
);

create table emp(    #From table
    eid int primary key, #Employee number
    ename varchar(5), #Employee name
    deptid int, #Employee's Department
    foreign key (deptid) references dept(did)  on update cascade on delete set null
    #Specify foreign key constraints in the from table
    #The data types of deptid in emp table and did in dept table are the same, and the meaning is to represent the department number
    #Set the modification operation to cascade modification level and the deletion operation to set null level
);
--explain:
--(1)Main table dept You must create it successfully before you can create it emp Table, specifying foreign key succeeded.
--(2)When deleting a table, delete the slave table first emp，Then delete the main table dept

Delete foreign key constraint:
(1)The first step is to view the constraint name and delete the foreign key constraint
SELECT * FROM information_schema.table_constraints WHERE table_name = 'Table name';#View the constraint name of a table
ALTER TABLE From table name DROP FOREIGN KEY Foreign key constraint name;

(2)The second step is to view the index name and delete the index. (note that it can only be deleted manually)
SHOW INDEX FROM Table name; #View the index name of a table

ALTER TABLE From table name DROP INDEX Index name;

-CHECK check constraint [equivalent to java enumeration class]

  • Check whether the value of a field is required by symbol xx, which generally refers to the range of values
create table employee(
        eid int primary key,
        ename varchar(5),
        gender char check ('male' or 'female')
);

-DEFAULT default value constraint

  • Assign a default value to a field / column. Once the default value is set, when inserting data, if the field has no explicit assignment, the assignment is default
    Value recognition.
create table Table name(
        Field name data type primary key,
        Field name data type unique key not null,
        Field name data type unique key,
        Field name data type not null default Default value,
);
create table Table name(
        Field name data type default Default value ,
        Field name data type not null default Default value,
        Field name data type not null default Default value,
        primary key(Field name),
        unique key(Field name)
);
#Note: the default value constraint is generally not added to the unique key and primary key column
alter table Table name modify Field name data type default Default value;
alter table Table name modify Field name data type default Default value not null;

#After table building


#Delete default constraint
alter table Table name modify Field name data type ;

Auto increment

#Specify self increasing constraints
#When creating tables
create table Table name(
        Field name data type primary key auto_increment,
        Field name data type unique key not null,
        Field name data type unique key,
        Field name data type not null default Default value,
);
create table Table name(
        Field name data type default Default value ,
        Field name data type unique key auto_increment,
        Field name data type not null default Default value,
        primary key(Field name)     
);

#After table building
alter table Table name modify Field name data type auto_increment;


#Delete autoincrement constraint
alter table Table name modify Field name data type;

-2022/1/30

Keywords: Database MySQL SQL

Added by Pavlos1316 on Tue, 01 Feb 2022 04:25:23 +0200

Popular Keywords

©2024 Programming VIP