MySQL Common commands, simple queries, cursors, stored procedures, and triggers

I. Basic Common commands for MySQL Databases
1. Start and Stop a service
Net stop MySQL
Net start MySQL

2. log on to MySQL
Mysql-uroot-P press enter to enter the password
Note: to connect to another machine, you need to add the parameter-H Machine ip110.110.110.110, the username is root, and the password is abcd123. Enter the following command:
Mysql-h110.110.110.110-uroot-pabcd123
 

Mysql-u root-P/MySQL-H localhost-u root-P databasename;

3. display the Database List
Show databases; by default, there are two databases: MySQL and test. MySQL inventory contains the MySQL system and user permission information. We change the password and add users, in fact, this database is actually operated

4. Select a database and display a data table
Use MySQL;
Show tables;

5. display table structure
Desc tablename

6. Create and delete Databases
Create Database dbname;
Drop database dbname;

7. Basic a table creation, B insert information, C add field, d Modify Field name, e Modify Field Data Type, F update data
A. Create Table EMP (ID int not null auto_increment primary key, name varchar (50 ));
B. insert into tablename values ("hyq", "M ");
C. alter table tablename add columnname type;
D. alter table tablename1 change tablename2 char (10) Not NULL;
E. alter table tablename modify columnname type
F. Update tablename set age = "25" where id = "";

8. Clear table records and Delete tables
Delete from tablename
Drop table tablename;

9. Export the imported data
Export data and data structure: input: mysqldump-U [database username]-P [name of the database to be backed up]> [storage path of the backup file]
Example: mysqldump-u root-P test> E: \ TT. SQL
Import: mysql-u root-P <[backup file storage path]
Restore the backup file:
Go to MySQL command line Client
First create database: Create Database test Note: test is the name of the created database
Switch to the current database: Use test
Enter \. D:/test. SQL or Souce E: \ TT. SQL.

10. Exit MySQL exit or press Ctrl + C;

Ii. MySQL Query

1. Several common functions
A. Count (); Statistics
B. sum (); sum
C. AVG () calculate the average
D. Max, min max and min

2. Several advanced query operations
A. The Union operator derives a result table by combining two other result tables (such as Table1 and table2) and removing any duplicate rows in the table. When all is used together with Union (that is, Union all), duplicate rows are not eliminated.
B. the distinct t operator derives a result table by including all rows in Table 1 but not in table 2 and eliminating all repeated rows. When all is used with distinct T (distinct t all), duplicate rows are not eliminated.
C. The Intersect operator derives a result table by only including the rows in Table1 and Table2 and eliminating all repeated rows. When all is used with intersect (intersect all), no duplicates are eliminated.
(The query results of the preceding operators must be consistent)

3. Several Common connection queries
A. left Outer Join: left Outer Join (left join): the result set contains the matching rows of the connected table and all rows of the left join table. SQL: select. a,. b,. c, B. c, B. d, B. f from a left out join B on. A = B. c;
B. Right Outer Join (right join): the result set includes both matched join rows in the connection table and all rows in the right join table.
C. Full outer join: includes not only matching rows in the symbolic join table, but also all records in the two join tables.
D. Inner join: equivalent join only returns rows with the same join fields in two tables.

Iii. MySQL View

1. concept: it is a virtual table. Instead of storing data, you can only use the SELECT statement to reference the data of an existing table (one or more;

2. Role: Make the query clearer, store the data we need in the view, and simplify the operation; make the data safer. The data in the view does not exist in the view, but is still in the basic table, through the view relationship, we can effectively protect our important data

3. MySQL view type: MySQL view has three types: merge, temptable, and undefined. Without the algorithm clause, the default algorithm is undefined (undefined ). The algorithm affects the way MySQL processes the view.
A. Merge combines the text of the statements that reference the view with the view definition, so that a part of the view definition replaces the corresponding part of the statement.
B. temptable. The view result is placed in a temporary table and then used to execute the statement.
C. undefined. MySQL selects the algorithm to use. If possible, it tends to be merge rather than temptable, because merge is generally more effective, and if a temporary table is used, the view cannot be updated.

4. Basic creation: A. Create view db_name.view_name as select * from t; (tables and views share the same namespace in the database. The view name in the same database cannot be the same as that in the table .)
B. Create [or replace] [algorithm = {undefined | merge | temptable}]
View view_name [(column_list)]
As select_statement
[With [cascaded | Local] Check option]

Iv. MySQL Indexes
A. index is a double-edged sword. Explain select * From tablename and explain are used to describe how MySQL performs the query operation and the number of rows to be executed when MySQL returns the result set successfully.

5. Stored Procedure (stored procedure is supported only after mysql5)
1. Concept: a set of SQL statements to complete special functions, which are compiled and stored in the database and called and executed by the process name;
2. Advantages: A. enhanced SQL flexibility and functions to complete complicated judgment and computation;
B. Fast execution speed, effectively saving traffic (only calling statements are passed );
C. Data security plays a major role.
3. A simple example
Delimiter //
Create procedure pro_test (out s int)
Begin
Select count (*) Total into S from user;
End
//
Delimiter;
Explanation: The preceding stored procedure has an output parameter S, which assigns the number of records to S;
A. delimiter, which indicates the delimiter. MySQL uses the separator ";" by default. If we do not declare the delimiter, the compiler treats the stored procedure as an SQL statement,
An error is reported during the compilation process of the stored procedure. Therefore, use the delimiter keyword to declare the delimiter of the current segment.
B. Three parameter types: In input parameter: indicates that the value of this parameter must be specified when the stored procedure is called. Modifying the value of this parameter in the stored procedure cannot be returned, which is the default value;
Out output parameter: The value can be changed within the stored procedure and can be returned;
Inout input and output parameters: they are specified during the call and can be changed and returned;
C. Variable Declaration: declare age int default 18;
D. Variable assignment: Set variable name = expression value
E. user variable: select "Hello Kitty" into @ X. After declaration, the result of select @ X is "Hello Kitty" User variable name. Generally, misuse of user variables starting with @ will make the program hard to understand and manage.
4. MySQL stored procedure call
Call stored procedure name ();
5. query stored procedures
Select name frommysql. Proc where DB = 'dbname ';
Show procedure status where DB = 'dbname ';
Show create procedure

6. Modify and delete stored procedures
Alter procedure;
Drop procedure;

6. triggers
1. Concept: When performing Delete, update, or insert operations, you can use triggers to trigger certain operations.
2. Create: Create trigger trigger_name trigger_time trigger_event on tbl_name for each row trigger_stmt
3. Explanation: trigger_name indicates the trigger name,
Trigger_time: before, after
Trigger_event: insert, update, delete
Tbl_name: name of the associated table
Note: In addition to the insert operation, the insert operation can also activate the load data event. For the same trigger_event, there cannot be two triggers with the same trigger_time.
Trigger_stmt: The statement executed when the trigger is activated. You can use either a single statement or a composite statement such as begin-end.

4. Example: mysql> Create Table account (acct_num int, amount decimal (10, 2 ));
 
Mysql> Create trigger ins_sum before insert on account
 
-> For each row set @ sum = @ sum + new. amount;
 
Mysql> set @ sum = 0;
Mysql> insert into account values (5, 12.5 );
Mysql> select @ num;
 
In this example, the keyword new. col_name is referenced in the insert trigger program;
Another keyword, old. col_name, can be used in Delete.
Both new and old can be used in the update trigger program.
The column of the old command is read-only and the column named new. If you have the select permission, you can reference it. If you have the update permission in the before program, you can use set new. col_name = value method, change the value before insertion

VII. cursor
1. Definition: declare cursor_name cursor for select_statement;
2. Open cursor: Open cursor_name;
3. Fetch: get the record of the current pointer of the cursor and pass it to the specified Variable list. Note that the number of variables must be consistent with the number of fields returned by the MySQL cursor. To obtain multiple rows of data, execute fetch using cyclic statements
Fetch cursor_name into Variable list;
4. Close the cursor: Close cursor_name; the MySQL cursor is read-only, that is, you can only read the result set from the beginning in sequence, not from the back to the front, you cannot directly jump to the intermediate record.
5. Example:
-- Define local variables
Declare o varchar (128 );

-- Define a cursor
Declare ordernumbers cursor
For
Select callee_name from account_tbl where acct_timedurl = 10800;
Declare continue handler for not found set no_more_attributes = 1;
Set no_more_departments = 0;

-- Open the cursor
Open ordernumbers;

-- Loop all rows
Repeat
-- Get Order Number
Fetch ordernumbers into O;
Update account set allmoney = allmoney + 72, lastmonthconsume = lastMonthConsume-72 where numtg = @ O;


-- Loop ends
Until no_more_administrative ments
End repeat;
-- Close the cursor
Close ordernumbers;