MySQL series: (3) MySQL Enhanced

1. SQL Statement Classification

DDL data Definition Language: Create/drop/alter

DML data manipulation statement: Insert/delete/update/truncate

DQL data Query Language: select/show

2. Data constraint

2.1. What are data constraints

To constrain column value data in a table

2.2. Default value

Role: The default value is used when the user does not insert a value into a field that uses the default value.

Note: You can insert null for the default value field.

CREATE TABLE t_persons (Id int,name NVARCHAR, Gender NVARCHAR (2) DEFAULT ' Male ')

2.3, non-empty

Role: The limit field must be assigned a value

Note: 1) non-null characters must be assigned, 2) non-null characters cannot be assigned null.

CREATE TABLE t_persons (Id INT not Null,name NVARCHAR (), Gender NVARCHAR (2))

2.4, the only

Function: The value of a field cannot be duplicated

Note: 1) Unique field can be inserted null;2) Unique field can insert multiple null

CREATE TABLE t_persons (Id INT unique,name NVARCHAR), Gender NVARCHAR (2))

2.5. Primary key

function: non-null + unique

Attention:

1) Typically, each table will have a primary key field set. Used to mark the uniqueness of each record in a table.

2) It is recommended that you do not select a field that contains business meaning for the table as the primary key, and it is recommended that you design a non-business-meaning ID field independently for each table.

CREATE TABLE t_persons (Id INT PRIMARY key,name NVARCHAR), Gender NVARCHAR (2))

2.6. Self-growth

Function: Auto Increment

CREATE TABLE t_persons (Id INT auto_increment,name NVARCHAR), Gender NVARCHAR (2))---------------------------------- --------------------------CREATE TABLE t_persons (Id INT (4) zerofill PRIMARY KEY auto_increment,--Self-growing, starting from 0 Zerofill 0 filling N AME NVARCHAR (+), Gender NVARCHAR (2)) DELETE from T_persons; --cannot affect self-growth constraints truncate TABLE t_persons;--can affect self-growth constraints

2.7. Foreign key

Role: Constrain data for both tables

Syntax: CONSTRAINT foreign Key name FOREIGN key (foreign key field for secondary table) REFERENCES primary table (primary key of primary table)

--Department table (main Table) CREATE TABLE t_department (Id INT PRIMARY KEY auto_increment,name VARCHAR (20))--Employee table (sub-table/from table) CREATE TABLE T_employ EE (Id INT PRIMARY key auto_increment,name VARCHAR), DepartmentID int,constraint EMPLOYEE_DEPARTMENT_FK FOREIGN Key ( DepartmentID) REFERENCES t_department (Id))

Attention:

1) The constrained table is called the secondary table, the table that constrains others is called the main table, and the foreign key is set on the secondary table!!!

2) The Reference field of the main table is usually the primary key!

3) Add data: First add the Main table, then add the secondary table

4) Modify the data: Modify the secondary table first, then modify the main table

5) Delete the data: Delete the secondary table before deleting the main table

2.8. Cascade operation

Cascading modifications: on UPDATE CASCADE

Cascade Delete: ON delete CASCADE

Note: cascading operations must be used on a foreign key basis

CREATE TABLE t_employee (Id INT PRIMARY KEY auto_increment,name VARCHAR), DepartmentID int,constraint employee_ DEPARTMENT_FK FOREIGN KEY (departmentid) REFERENCES t_department (Id) on UPDATE CASCADE on DELETE CASCADE)

3. Three major paradigms of database and MySQL variables

3.1. Three major paradigms

Design principles: The proposed design of the table as far as possible to abide by the three paradigms.

First paradigm: Each field of a requirement table must be an indivisible independent unit .

If the "Li Donghua | li yan moth" is stored in the Name field in the T_persons table, the first paradigm is violated.

If you store "Li Donghua" in the Name field in the T_persons table, storing "Li Yan Moth" in the Oldname field will conform to the first normal form.

The second paradigm: on the basis of the first paradigm, each table is required to express only one meaning. Each field of the table is dependent on the primary key of the table.

The third paradigm: on the basis of the second paradigm, all fields other than the primary key of each table are required to directly determine the dependency relationship with the primary key.

According to my own understanding:

The first paradigm is to constrain "columns", and each field is an indivisible independent unit;

The second paradigm is to constrain the "table", and each table only expresses one meaning;

The third paradigm is to constrain the "table-to-table relationship", which can only contain the primary key of the main table.

3.2. mysql variable

MySQL variables include: global variables, session variables, local variables

Global variables (built-in variables): Variables built into the MySQL database (all connections work)

View all global variables: show variables

View a global variable: select @@ 变量 Name

Modify global variables: Set Variable name = new value

Two values in a global variable: character_set_client and Character_set_results. Can query show VARIABLES like ' character_% ';

Encoding of received data for the CHARACTER_SET_CLIENT:MYSQL server

CHARACTER_SET_RESULTS:MYSQL encoding of server output data

Session Variables : Only one connection is present between the current client and the database server side. If the connection is broken, then all session variables are lost!

Defining Session Variables: SET @ variable = value

View Session Variables: SELECT @ variable

Local Variables : Variables that are used in stored procedures are called local variables. Local variables are lost as long as the stored procedure is executed!!

4. Stored Procedures

4.1. What is a stored procedure

Stored procedure, which is a logical SQL statement .

The previous SQL has no conditional judgment, no loops, and the stored procedure takes the Process Control statement (if while).

4.2. Characteristics of Stored Procedure

1) execution efficiency very fast! The stored procedure is executed on the server side of the database!!!

2) Very poor transplant! Stored procedures for different databases are not portable.

4.3. Stored procedure Syntax

Grammar:

Delete stored procedure: Drop PROCEDURE stored procedure name;

Parameters:

In: Indicates input parameters, can carry data with stored procedure

Out: Represents an output parameter that can be returned from a stored procedure

INOUT: Indicates input and output parameters, can either input function or output function

4.3.1, non-parametric stored procedure

--Create stored procedure delimiter $--Terminator Create PROCEDURE Sp_findall () beginselect * from t_persons declaration stored procedure; END $--Execute Stored procedure call Sp_findall (); --Call stored procedure name (parameter);

4.3.2, stored procedure with input parameters

--Create stored procedure delimiter $CREATE PROCEDURE Sp_findbyid (in PID INT) Beginselect * from T_persons WHERE id=pid; END $--Execute Stored procedure call Sp_findbyid (2);

4.3.3, stored procedure with output parameters

--Create a stored procedure delimiter $CREATE PROCEDURE sp_findcount (out str VARCHAR (a), out num INT) beginset str = ' Hello world '; SELECT COUNT (Id) into Num from t_persons; End $--Execute Stored procedure call Sp_findcount (@str, @num);--View the result of the out type of the stored procedure select @str, @num;

4.3.4, stored procedure with input and output parameters

--Create stored procedure delimiter $CREATE PROCEDURE sp_testinout (INOUT n INT) beginselect N; SET n = 500; END $--Execution Stored procedure set @n = 10; Call Sp_testinout (@n); SELECT @n;

4.3.5, a stored procedure with conditional judgment

--Create stored procedure delimiter $CREATE PROCEDURE sp_testif (in num int,out str VARCHAR) beginif num = 1 thenset str = ' Monday '; --Note the assignment with set ElseIf num = 2 thenset str = ' Tuesday '; ELSEIF num = 3 thenset str = ' Wednesday '; Elseset str = ' input error! ‘; End if;--Note here there is a semicolon ending end $--executing the stored procedure call Sp_testif (4, @str); SELECT @str;

4.3.6, cycle-capable stored procedures

--Create stored procedure delimiter $CREATE PROCEDURE sp_testwhile (in num int,out result INT) begindeclare i INT DEFAULT 1;declare iresult I NT DEFAULT 0; While I <= num doset iresult = iresult + i; SET i = i + 1; END while; SET result = Iresult; END $--Execute Stored procedure call Sp_testwhile (@result); SELECT @result;

4.3.7, assigning a value to a variable using the query's result (into)

--Create stored procedure delimiter $CREATE PROCEDURE sp_testinto (in PID int,out vname VARCHAR) beginselect NAME to VName from T_perso NS WHERE Id=pid; END $--Execute Stored procedure call Sp_testinto (2, @vname); SELECT @vname;

5. Trigger

When you manipulate a table, you want to trigger some action/behavior at the same time, you can use the trigger to complete!!

Grammar:

DELIMITER $CREATE The name of the TRIGGER trigger after the Insert/update/delete on table name for each rowbegin--SQL statement end $

Example:

CREATE TABLE t_logs (Id INT PRIMARY KEY auto_increment,content VARCHAR) DELIMITER $CREATE TRIGGER Tri_add after INSERT On T_persons-Rowbegininsert into T_logs (Content) VALUES (' Insert a piece of data '); END $

6. mysql User rights

All users of the MySQL database are stored in the Mysql.user table

Where the root user has all the privileges (can do anything);

A privileged account that only has partial permissions (curd) For example, you can only manipulate a table of a database

1) How do I change the password of a MySQL user?

UPDATE USER SET password=password (' root ') WHERE user= ' root ';

In MySQL, password password is encrypted by MD5 (one-way encryption)

Use MySQL; SELECT * from USER; SELECT PASSWORD (' root '); --*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B

2) Assigning rights to accounts

GRANT permissions on the database name. Table name to ' user name ' @ ' IP address ' identified by ' password ';

If you use "%" at the IP address, it means both native and remote.

GRANT SELECT on TestDB. T_persons to ' rk ' @ ' localhost ' identified by ' 123456 '; GRANT DELETE on TestDB. T_persons to ' rk ' @ ' localhost ' identified by ' 123456 ';

7. Backup and Recovery

7.1. Backup

Mysqldump-u root-p testdb > C:/bak.sql

7.2. Recovery

Mysql-u root-p TestDB < C:/bak.sql

Note: No login required

MySQL series: (3) MySQL Enhanced