Shulou(Shulou.com)06/01 Report--

The following content mainly brings you some common knowledge points about the MySQL series, which are different from books, are summed up by professional and technical personnel in the process of contact with users, and have a certain value of experience sharing, hoping to bring help to the majority of readers.

1. Classification of SQL statements

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

Constrain the column value data in the table

2.2, default value

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

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

CREATE TABLE T_Persons (Id INT, NAME NVARCHAR (20), Gender NVARCHAR (2) DEFAULT 'male') 2.3, not empty

Function: limit fields must be assigned values

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

CREATE TABLE T_Persons (Id INT NOT NULL, NAME NVARCHAR (20), Gender NVARCHAR (2)) 2.4, unique

Function: the value of the field cannot be repeated

Note: 1) unique fields can be inserted into null;2) unique fields can be inserted into multiple null

CREATE TABLE T_Persons (Id INT UNIQUE, NAME NVARCHAR (20), Gender NVARCHAR (2)) 2.5, primary key

Function: non-empty + unique

Note:

1) typically, a primary key field is set for each table. Used to mark the uniqueness of each record in the table.

2) it is not recommended to select the fields with business meaning of the table as the primary key. It is recommended to design an independent id field with non-business meaning for each table.

CREATE TABLE T_Persons (Id INT PRIMARY KEY, NAME NVARCHAR (20), Gender NVARCHAR (2)) 2.6, self-growth

Function: auto increment

CREATE TABLE T_Persons (Id INT AUTO_INCREMENT, NAME NVARCHAR (20), Gender NVARCHAR (2))-CREATE TABLE T_Persons (Id INT (4) ZEROFILL PRIMARY KEY AUTO_INCREMENT,-- self-growth Starting from 0, ZEROFILL zero fills NAME NVARCHAR (20), Gender NVARCHAR (2) DELETE FROM T_Persons -- can not affect the self-growth constraint TRUNCATE TABLE-- can affect the self-growth constraint 2.7, foreign keys.

Function: constrain the data of two tables

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

-- Department table (master table) CREATE TABLE T_Department (Id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR (20))-- employee table (secondary table / slave table) CREATE TABLE T_Employee (Id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR (10), DepartmentId INT, CONSTRAINT employee_department_fk FOREIGN KEY (DepartmentId) REFERENCES T_Department (Id))

Note:

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

2) the reference field of the primary 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, and then modify the main table

5) Delete data: delete the secondary table first, then the primary table

2.8, cascade operation

Cascade modification: ON UPDATE CASCADE

Cascading deletion: ON DELETE CASCADE

Note: cascading operations must be based on foreign keys

CREATE TABLE T_Employee (Id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR (10), DepartmentId INT, CONSTRAINT employee_department_fk FOREIGN KEY (DepartmentId) REFERENCES T_Department (Id) ON UPDATE CASCADE ON DELETE CASCADE) 3, database three paradigms and MySQL variables 3.1,three paradigms

Design principles: it is recommended that the designed table should follow the three paradigms as far as possible.

The first paradigm: requires that each field of the table must be an indivisible independent unit.

If you store "Li Donghua | Plum Leaf Moth" in the name field in the TPersons table, you will violate the first paradigm.

If you store "Li Donghua" in the name field in the T_ persons table and "plum leaf moth" in the oldname field, you will conform to the first paradigm.

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

The third paradigm: on the basis of the second paradigm, it is required that fields other than the primary key of each table can only be directly dependent on the primary key.

According to my personal understanding:

The first paradigm is to constrain the "column". Each field is an inseparable independent unit.

The second paradigm is to constrain the "table". Each table expresses only one meaning.

The third paradigm is to constrain the "relationship between tables". Secondary tables can only contain the primary key of the primary table.

3.2Variable MySQL

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 @ @ variable name

Modify global variable: set variable name = new value

Two values in the global variable: character_set_client and character_set_results. You can query SHOW VARIABLES LIKE 'character_%'

Character_set_client: the encoding of the received data of the mysql server

Encoding of output data from character_set_results:mysql server

Session variable: only exists in the current connection between the client and the database server. If the connection is disconnected, then all session variables are lost!

Define session variable: set @ variable = value

View the session variable: select @ variable

Local variables: variables used in stored procedures are called local variables. As long as the stored procedure is finished, the local variable will be lost!

4. Stored procedure

4.1. What is a stored procedure

Stored procedures are sql statements with logic.

The previous sql had no conditional judgment and no loop; the stored procedure had a process control statement (if while).

4.2. Characteristics of stored procedures

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

2) poor portability! Stored procedures in different databases are not portable.

4.3. Stored procedure syntax

Syntax:

Delete stored procedure: DROP PROCEDURE stored procedure name

Parameters:

IN: indicates input parameters, which can be carried with data in stored procedures

OUT: represents the output parameter, and the result can be returned from the stored procedure

INOUT: represents input and output parameters, which can both input and output functions

4.3.1. Stored procedure with no parameters-- create stored procedure DELIMITER $--declare the Terminator of the stored procedure CREATE PROCEDURE sp_findAll () BEGIN SELECT * FROM characters alternate end $--execute the 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) BEGIN SELECT * FROM T_Persons WHERE Id=pid;END $--execute stored procedure CALL sp_findById (2); 4.3.3. Stored procedure with output parameters-- create stored procedure DELIMITER $CREATE PROCEDURE sp_findCount (OUT str VARCHAR (20), OUT num INT) BEGIN SET str = 'hello world' SELECT COUNT (Id) INTO num FROM stored Personality end $--execute stored procedure CALL sp_findCount (@ str,@num);-- View the result of the OUT type of 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) BEGIN SELECT n; SET n = 500 END $--execute the stored procedure SET @ n = 10 * * call sp_testInOut (@ n); SELECT @ n * 4.3.5, stored procedure with conditional judgment-create the stored procedure DELIMITER $CREATE PROCEDURE sp_testIf (IN num INT,OUT str VARCHAR (20)) BEGIN IF num = 1 THEN SET str = 'Monday' -- pay attention to using SET for assignment ELSEIF num = 2 THEN SET str = 'Tuesday'; ELSEIF num = 3 THEN SET str = 'Wednesday'; ELSE SET str = 'typed error!' ; END IF;-- notice that there is a semicolon ending with END $--execute the stored procedure CALL sp_testIf; SELECT @ str;4.3.6, the stored procedure with loop function-create the stored procedure DELIMITER $CREATE PROCEDURE sp_testWhile (IN num INT,OUT result INT) BEGIN DECLARE i INT DEFAULT 1; DECLARE iResult INT DEFAULT 0 WHILE I c:/bak.sql7.2, restore mysql-u root-p testdb < c:/bak.sql

Note: no login required

For some of the common knowledge points about the MySQL series above, if you have more information, you can continue to pay attention to the innovation of our industry. If you need professional answers, you can contact the pre-sale and after-sale ones on the official website. I hope this article can bring you some knowledge updates.

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.