My MySQL Database learning notes

First, the basic statement of the Operation database

cmd into MySQL: mysql-uroot-p
Creating database: Create library name;
Create data Table : with SQLite;
View database : Show DATABASES;
View Data Sheet : show TABLES;
Enter database : use library name;
View Library creation Statement : Show create database name;
View Table creation Statement : Show create table table name;
View table Structure : Show COLUMNS from table name, or DESC table name
Record additions and deletions are the same as SQLite. Data types are no longer mentioned.

II. constraints and modification of the database structure

2.1 constraint  
non-null constraint : not null 
PRIMARY KEY constraint : PRIMARY key (only one in a table)  
UNIQUE constraint : unique key 
default constraint : default 
Check Constraint : CHECK (less)  
foreign KEY constraint : FOREIGN key

/*外键约束,格式:FOREIGN KEY REFERENCES 关联的表名(字段名),注意如下几点：1.参考段需有索引，provinces的id字段为主键约束，自动有索引2.外键段pid不创建索引，系统也会自动添加索引3.参考段若为整型，那么整型类型，有无符号均要一样。若为字符型则无要求*/CREATE TABLE users1 (id SMALLINT PRIMARY KEY AUTO INCREMENT,pid INT FOREIGN KEY REFERENCES provinces(id))

• 1
• 2
• 3
• 4
• 5
• 6
• 7

NOTE: Foreign KEY constraints are only valid when the database engine is INNODB!!!

2.2 Modifying a database structure statement

2.2.1 table field
add field : ALTER TABLE users2 Add ID SMALLINT UNSIGNED;
Delete field : ALTER TABLE users2 DROP ID;
To bulk operations, add ", DROP col_name ..." at the end.
add field to a specified location : ALTER TABLE users2 Add ID SMALLINT UNSIGNED after column name (or first, plug to start);

2.2.2 Constraints
Add PRIMARY KEY constraint : ALTER TABLE users2 add CONSTRAINT pk_users2_id PRIMARY KEY (ID);
add Unique constraint : ALTER TABLE users2 Add unique (username);
add foreign KEY constraint : ALTER TABLE users2 add FOREIGN KEY (PID) REFERENCES provinces (ID);
Add Delete default constraint : Alter TABLE USERS2 alter age SET to default 15; Alter TABLE USERS2 alter age DROP DEFAULT;
Delete primary KEY constraint : ALTER TABLE users2 DROP PRIMARY key;
Delete Unique constraint : ALTER TABLE users2 DROP INDEX (KEY) index_name;
Delete foreign KEY constraint : ALTER TABLE users2 DROP FOREIGN KEY users2_ibfk_1;

2.2.3 Column
Modify Column Definition : ALTER TABLE tab_name MODIFY [column] Col_name column_definition [first| After Col_name];
Example: ALTER TABLE users2 MODIFY ID SMALLINT UNSIGNED not NULL first; (at this point the data type can be modified and a large range will result in some data loss)
Modify Column name : ALTER TABLE tab_name Change [column] Old_col_name new_col_name column_definition [first| After Col_name];

2.2.4 Modifying table names
(1) ALTER TABLE tab_name RENAME [as| To] New_tab_name (one-time renaming)
(2) RENAME TABLE tab_name to New_tab_name[,tab_name2 to new_tab_name2 ...];( Batch renaming)
Note: column names, table names may be referenced by other or created indexes, renaming may result in views, stored procedures do not work properly, Use caution!!!

Third, record operation

INSERT:

1. INSERT [into] tab_name [(Col_name,...)] {values| VALUE} ({expr| DEFAULT},...), (...),... (Insert a piece of data)
2. INSERT [into] tab_name SET col_name={expr| DEFAULT},... (Insert a field data)
3. INSERT [into] tab_name [(Col_name,...)] SELECT ... (Select records from other tables to add)

UPDATE

1. Single-Table update
   UPDATE [low_priority] [IGNORE] table_references SET col_name1={expr| DEFAULT} [, col_name2={expr| DEFAULT}] ... WHERE where_condition
2. Multiple table updates, see the next section

DELETE

1. Single-Table deletion
   DELETE from Tab_name [WHERE where_condition]
2. Multiple table deletion, see the following section

SELECT

• SELECT select_expr [, select_expr]
  [
  From Table_references
  [WHERE Where_condition]
  [GROUP by {col_name|position} [asc| DESC],...]
  [Having where_condition]
  [ORDER by {col_name|position|expr} [asc| DESC],...]
  [LIMIT {[offset,] row_count|row_count offset offset}]
  ]

Iv. Sub-Query and connection

4.1 Sub-query Introduction
A subquery refers to a SELECT clause that appears in other SQL statements.
For example: SELECT * from t1 where col1= (select col2 from T2), where select * from T1 is called Outer query/outer statement,select col2 from T2 called Sub-query;

• Subqueries are nested inside the query and must always exist within parentheses ()
• Subqueries can include multiple keywords or conditions, such as Distinct,group By,order by,limit, Functions, and so on.
• The outer query of a subquery can be: Select,insert,update,delete,set or do
• A subquery can return a scalar, row, column, or subquery

4.2 Using subqueries
4.2.1 using the comparator =,>,<,>=,<=,!=,<>,<=>
such as: SELECT * from Tdb_goods WHERE goods_price>= (select ROUND (AVG (Goods_price), 2) from Tdb_goods);

4.2.2 with the Any,some,all modifier to compare multiple data returned by a subquery, any and SOME are equivalent, as long as one data is satisfied, all to satisfy all data.
such as: Select Goods_name from Tdb_goods where Goods_price >= all (select Goods_price from tdb_goods where goods_cate = ' hyper-polar ');

4.2.3 using "not" in subqueries
=any and in equivalence
!=all or <>all and not in equivalence

4.2.4 Writing query results to a data table
INSERT [into] tab_name [(Col_name,...)] SELECT ...
such as: INSERT tdb_goods_cates (cate_name) SELECT goods_cate from Tdb_goods GROUPBY goods_cate;

4.3 Multi-table updates
UPDATE table-references SET col_name1={expr1| default}[,col_name2={expr2| DEFAULT}] ... [WHERE Where_condition]

4.4 Multi-table Update one step update (CREATE TABLE, query results write new table, multi-table update compositing one step)
The first step, the second step is one step:
CREATE TABLE [IF not EXISTS] tab_name[(create_definition,...)] select_statement
Such as:

CREATE TABLE tdb_goods_brands (brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,brand_name VARCHAR(40) NOT NULL)SELECT brand_name FROM tdb_goods GROUP BY brand_name;

• 1
• 2

Step Three:

UPDATE tdb_goods AS  g  INNER JOIN tdb_goods_brands AS b ON g.brand_name = b.brand_name SET g.brand_name = b.brand_id;

• 1
• 2

(because two tables have the same name segment brand_name, not as direct as before)

4.3 connection  
table_references{[inner| cross]join| {left| Right[outer]join}table_references on conditional_expr  
If the names of the two tables in the conditional_expr are the same name, you can set aliases  
(1) table_references tab_name [[as] alias]|table_subquery [as] alias 
data table can use Tab_name as Alias_name or tab_ Name Alias_name aliases, see above  
(2) Table_subquery can be used as a subquery in a form sentence, such a subquery must give   to it;
connection is divided into:  
(1) Within the connection, showing that the left and right tables meet the connection conditions of the record  
(2) left connection, showing all records in the left table and the record in the table with the connection conditions  
(3) Right connection, showing all records of the right table and the records of the left table matching the join conditions &NBSP
Multiple table connection  
such as: SELECT Goods_id,goods_name,cate_name,brand_name,goods_price from Tdb_ Goods as G INNER join Tdb_goods_cates as C on g.cate_id=c.cate_id INNER join Tdb_goods_brands as B on g.brand_id=b.brand_i d; 
A few notes about the connection:  
? A left JOIN B join_condition

• The result set of data table B depends on the data table A.
• The result set of data table A depends on all data tables (except for table B) based on the left join condition.
• Left OUTER JOIN condition determines how data table B is retrieved (without specifying a where condition)
• If a record of table a conforms to the where condition, but there is no record in data table B that matches the join condition, an additional B row is generated for all columns that are empty.

? If the records that are found with an inner join exist in the middle of the join data table and try the operation in the WHERE clause: Col_name is NULL, if col_name is defined as not null,mysql stops searching for more rows after it finds records that meet the join criteria.

4.4 Infinite Pole classification table design
(1) data table design with infinite classification
CREATE TABLE tdb_goods_types (type_id SMALLINT UNSIGNED PRIMARY KEY auto_increment,type_name VARCHAR () not NULL, Parent_ ID SMALLINT UNSIGNED not NULL DEFAULT 0);
After inserting the data:

(2) Find all categories and their parent classes

SELECT s.type_id,s.type_name,p.type_name FROM tdb_goods_types AS s LEFT JOIN tdb_goods_types AS p ON s.parent_id = p.type_id;

• 1
• 2
• 3
• 4

s.parent_id = p.type_id, the order of the left join can never change, indicating which table is displayed all, to display the full field with the left table. xx

4.5 multiple table deletions
Delete duplicate data in the ID of the larger

DELETE t1 FROM tdb_goods AS t1 LEFT JOIN (SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2 ) AS t2 ON t1.goods_name = t2.goods_name WHERE t1.goods_id > t2.goods_id;

• 1
• 2
• 3
• 4

v. Operators and Functions

As a result of paper notes, temporarily no longer introduced, can be directly Baidu  
the necessary condition for a custom function:  
1. Parameter  
2. return value  
function has return value, not necessarily parameter  
Basic statement:  
CREATE FUNCTION function_name RETURNS {string| integer| real| DECIMAL} routine_body  
routine_body--> function Body

The body of the
• function consists of a valid SQL statement
• function body can be a simple select or INSERT statement
• function Body If it is a composite structure, use begin ... The end statement

• Composite structure can include declarations, loops, control structures

  such as: CREATE FUNCTION F1 () RETURNS VARCHAR RETURN Date_format (now (), '%y year% M-month%d day%h point:%i:%s seconds ');  
  Why I directly select Date_format (...) Returned correctly, SELECT F1 () returns the result: and Chinese became? Number video F1 () Back right!!!

  Create a function with a composite structure function body (to insert data as an example)  
  error demonstration:  
  Create FUNCTION AddUser (username VARCHAR) RETURNS INT UNSIGNED RETURN INSERT Test (username) VALUES (username);  
  Because the result of insert return is not at all int type  
  is correctly demonstrated:  
  1.DELIMITER//(modify delimiter; for//, others can)  
  2. Put Last_ INSERT_ID () as the return value  
  3. Because of the INSERT statement and the last_insert_id () function at this time, a composite structure   is required;
  4. The final statement is as follows:

FUNCTION adduser(username VARCHAR(20)) RETURNS INT UNSIGNED BEGIN INSERT test(username) VALUES(username) ;RETURN LAST_INSERT_ID() ;END // **ps:注意这两个；号，缺一不可，且只能用；号**

• 1
• 2
• 3
• 4
• 5
• 6
• 7

To Delete a custom function
DROP FUNCTION [if_exists] Fun_name

vi. MySQL stored procedures

6.1 What are stored procedures and how to create

SQL command Execution Flow:
SQL instruction-->mysql Engine parsing _> syntax correct--recognition command _ execute _> execution Result _ return _> Client

The MySQL storage process is a set of SQL statements to complete a particular function, compiled and stored in a database, and when the user needs to use that group of SQL statements, it can be invoked by specifying the name of the stored procedure and given parameters to execute it, eliminating the need for parsing syntax and other steps to improve efficiency

Statement:
CREATE [definer={user| Current_User}]
PROCEDURE sp_name ([proc_parameter[,...]])
[Characteristic ...] Routine_body

where proc_parameter–>[in| Out| INOUT] Param_name Type
in, indicating that the value of the parameter must be specified when the stored procedure is called
Out , indicating that the value of the parameter can be changed by the stored procedure and can be returned
A INOUT that indicates that the value of this parameter is specified when the stored procedure is called and can be changed and returned
Process Body:

• The process body is composed of legitimate SQL statements
• Process body can be arbitrary additions and deletions, multi-table connection and other SQL statements
• Process body if it is a composite structure, use begin ... End Statement
• Process bodies can include declarations, loops, control structures

Example:
To create a stored procedure:
CREATE PROCEDURE SPL () SELECT VERSION ();
Use:
Call SPL, or call SPL () (because no parameters are specified when created, both methods are available, and if a parameter is specified, the second is required)

6.2 Creating a stored procedure with an in type parameter

//CREATE PROCEDURE removeUserById(IN id INT UNSIGNED)BEGINDELETE FROM users WHERE id=id;END//CALL removeUserById(1);

• 1
• 2
• 3
• 4
• 5
• 6

Note: Our intention is to delete the Id=1 records in the Users table, but the result is that the records are all deleted, this is because we set the name of the parameter and ID field, in the where statement, the left ID is the field names, the right ID is the number of bars, but the system will mistakenly think both sides ID is the field name!!!
Modify:
(1) DROP PROCEDURE removeuserbyid; (Delete stored procedure)
(2) Repeat the above operation, modify the parameter name is not an ID

6.3 Creating a stored procedure with the In,out type parameter
In order to use the MySQL variable, do a brief introduction:
Terminology classification for MySQL variables:

1. User variables: Start with "@", in the form "@ Variable name"

The user variable is bound to the MySQL client, and the set variable is only valid for the client used by the current user

2. Global variables: When defined, appears in the following two forms, set global variable name or SET @ @global. Variable Name

Effective for all clients. You can set global variables only if you have super permissions

3. Session variable: Valid only for connected clients.

4. Local variables: The action range is between the begin and end statement blocks. The variable that is set in the statement block

The Declare statement is specifically used to define local variables. Set statements are variables of different types, including session variables and global variables

CREATE procedure p8  () begin declare a int; DECLARE b INT; set a = 5; set B = 5; INSERT into t VALUES (a); select S1 * a from t WHERE s1 >= B; end; //             

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9

To create a stored procedure that deletes the specified number of rows and returns the remaining rows, for example:

create procedure removeuserandreturnusernums ( Span class= "Hljs-keyword" >in p_id INT unsigned,out usernum INT UNSIGNED) begindelete from users Span class= "Hljs-title" >where _id=p_id; Select count (_id) from users into usernum;end//call removeuserandreturnusernums (1,@ Nums); select @nums;            

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10

6.4 Creating a stored procedure with multiple out type parameters
Use the Row_count () function to return the number of record bars affected by the previous operation
To create a stored procedure that deletes the specified record and returns the number of deleted records and remaining records as an example

delimiter//create PROCEDURE Removeuserbynameandreturninfos (in P_name VARCHAR (Ten), out Deleteusers SMALLINT UNSIGNED, Out usercounts SMALLINT UNSIGNED) begindelete from Test WHERE username=p_name; Select Row_count () into deleteusers; SELECT COUNT (ID) from test to usercounts; End//delimiter; Call Removeuserbynameandreturninfos ( ' a ', @a,@b);  Select @a,@b;+------+------+| @a | @b |+------+------+| 1 | 7 |+--- ---+------+//table with 8 records, only 1 username=a       

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21st

6.5 The difference between a stored procedure and a custom function

• The functions of the stored procedure implementation are more complex, and the functions are more targeted
• A stored procedure can return multiple values, and a function can have only one return value
• Stored procedures are generally independent to execute, and functions can appear as part of other SQL statements

The actual development of functions with less, stored procedures with more

vii. MySQL Storage engine

MySQL can store data in files (memory) in different technologies, known as the storage engine.
Each storage engine uses different storage mechanisms, indexing techniques, locking levels, and ultimately offers a wide range of different functions.

First introduce a few nouns:

• concurrency control
  Ensure data consistency and integrity when multiple connections modify records
• Lock (similar to a lock in Java, can be locking to a table, a record)
  Shared lock (read lock): Multiple users can read the same resource during the same time period without any changes to the data during reading
  Exclusive lock (Write lock): Only one user can write to the resource at any time, while the write lock will block other read or write lock operations
• Lock particles
  Table lock, which is the least expensive lock policy, one table has only one
  Row locks, is one of the most expensive lock policies, a table can have multiple or even one lock per row
• Transaction
  Transactions are used to ensure database integrity
• Transaction characteristics
  Atomic Nature
  Consistency
  Isolation of
  Durability
• FOREIGN key
  is a strategy to ensure data consistency
• Index
  is a structure that sorts the values of one or more columns in a data table

MySQL-supported storage engines:

• MyISAM
• InnoDB
• Memory
• Csv
• Archive
  

Ways to modify the storage engine

• By modifying the MySQL configuration file implementation, more than 5.5 default InnoDB
  -default-storage-engine=engine

• Implement by creating a data Table command
  -create TABLE table_name (
  ...
  ) Engine=engine;

• Implemented by modifying the data Table command
  -alter TABLE table_name engine[=] engine_name;

viii. MySQL graphical management tool

Because the database actual combat use to navicat, the concrete operation also please own Baidu

My MySQL Database learning notes