The review and advanced of MySQL

--The review and advanced of MySQL
--Create a fully structured table
CREATE TABLE Tab1 (
ID int (5) NOT NULL Auto_increment primary key comment "This is id",
Name varchar () NOT NULL default "ran",
Age Int (2) is not NULL
);
--The ID cannot be added to the default




--Copy table
CREATE TABLE TAB2 select * from Tab1;


--Copy only the structure, not the inner wing
CREATE table tab2 like tab1;


--Add Field
ALTER TABLE TAB1 add sex varchar (2) not null;


--delete Field
ALTER TABLE TAB1 drop sex;


--Modify Fields
ALTER TABLE TAB1 change name myname varchar (a) not null;


--Increase the primary key
ALTER TABLE TAB1 add primary key (ID);


--modifications indicate
ALTER TABLE TAB1 Rename to Tab11;


--Inserting data
INSERT into TAB1 values (null, "Zhangran", "23");


--Querying data
--Limit
SELECT * from TAB1 limit 1;


--from the beginning of the first display, showing a
SELECT * from TAB1 limit 1, 1;


--Sort
--order by Asc|desc
SELECT * from TAB1 order by name ASC;


--Update data
Update tab1 set name= "Zhangranran", age= "111" where name= "Zhangran" [Order By][limit]


--Delete data
Delete from tab1 where name= "Zhangran";
Note here that truncated can also be used when delete, and faster than delete, is a data definition language, but not the security of the data
-The self-growth after truncated is a start again.


--Connection
--Cross Connect
SELECT * from TAB1,TAB2;
--all of them can be found out, redundant parts are particularly much


--Inner connection
Select Tab1.name,tab2.myname from TAB1 join TAB2 on tab1.id=tab2.id;
--Add an alias select * from Tab1 a join tab2 b where tab1.id=tab2.id;




--Left connection and right link here.


--Self-connected
--Need to add an alias
Select A.name,b.name from tab1 a,tab1 b where a.id=b.id;


--Sub-query
SELECT * from Tab1 where name= (select name from TaB2 where name= "Zhangran");


--sub-query makes derived table
SELECT * E.name from (select name from TAB2) as E;


-in application in the query
SELECT * from TAB1 where name in (select name from TAB2);


--The use of exists, at this time the subquery as a condition, if there is a return value description is true, no return value is False
SELECT * from TAB1 where exists (select name from Tab1 where id=1);


--Transaction processing
--Put several statements as a whole
--Every successful execution is the success of a transaction.
--otherwise return to the original state
Start transaction
INSERT into TAB1 values (null, "Zhangdayi", 22);
Update TAB1 set age=11 where name= "Zhangdayi";
Commit




--Want to undo this transaction
Rollback




--MySQL Management
--Data backup
--User rights
--access control
--Performance optimization




--Data backup
--Backing up a table for a database
Mysqldump-u root-p111111 Wangluo tab1 >d:/mysql.sql


--Back up all the tables in a database
Mysqldump-u root-p111111 Wangluo >d:/mysql.sql


--Backup multiple databases at once
Mysqldump-u root-p111111--database db1 DB2 >d:/mysql1.sql
Special attention should be paid to the fact that database is singular and that it is preceded by double bars.


--Back up all the tables in the database

Mysqldump-u root-p111111--all--database >d:/mysql2.sql

--Import the SQL file into the database

SOURCE D:/aaa/bbb/ccc.sql;

--User's permission and access control
--A simple example of two
Grant all privileges on *. * to ' newuser ' identified by ' 111111 ' with GRANT option;



Grant Update,select on *. * to ' newuser ' identified by ' 111111 ';


--Performance optimization
--Add index to optimize during query operation
--Optimizing Database services
Mysql-verbose-help
--remember, this instruction is not entered in the MySQL environment under CMD input


MySQL basic knowledge is probably so much, the key is can practice, more pondering, remember often back to see!!!!!!!!!!!!!!!!!!!!

--MySQL's stored procedure
--Declare Life local variables
Delimiter//
CREATE PROCEDURE Pro (in myID INT);
BEGIN
DECLARE name VARCHAR (20);
Select ID into myID from tab1 WHERE name= "Zhangran";
SELECT myID;
END
//


Call Pro (1);
//




--Assign a value with the SET command
Delimiter//
CREATE PROCEDURE Pro (in Num1 int,in num2 INT);
DECLARE num INT;
SET num=num1+num2;
SELECT num;
END
//


--Flow control statement IF
Delimiter//
CREATE PROCEDURE Pro (in Type VARCHAR (20));
IF type= "A" then select "A Grade";
ELSEIF type= "B" then select "B Grade";
ELSE Select "C-level";
END IF;
END
//



--Process Control statement case
Delimiter//
CREATE PROCEDURE Pro (in Num INT);
BEGIN
Case num
When 1 then select "1 Rank";
When 2 then select "2 Rank";
When 3 then select "3 Rank";
END case;
END
//



Loop of circular statement
Delimiter//
CREATE PROCEDURE Pro (in Num INT);
BEGIN
Lable:loop
SET num=num+1;
IF num>10 then LEAVE lable;
END IF;
END LOOP;
SET @a=num;
END
//


Call Pro (2);
//
SELECT @a;
//


--The while of the loop
Delimiter//
CREATE PROCEDURE Pro ();
BEGIN
DECLARE num INT DEFAULT 0;
While NUM<10 does;
SET num=num+1;
END while;
SELECT i;
END
//




--The repeat of circulation
--similar to a Do While loop, regardless of whether it is a first step
Delimiter//
CREATE PROCEDURE Pro ();
BEGIN
SET @num = 0;
REPEAT @[email protected]+1 UNTIL @num >10;
END REPEAT;
END
//


--View information about stored procedures
--View Creation syntax information
Show CREATE PROCEDURE Pro\g


--View the characteristics of a stored procedure
Show procedure status;




--Modify the stored procedure with ALTER, this part of the master is not quite comprehensive!!!!!!!!!!!!!
Alter PROCEDURE Mypro
SQL Security Invoker


--Delete stored procedure with drop
DROP PROCEDURE IF EXISTS Pro;


--Trigger-------------------
--MySQL5 began to count on trigger technology
--Trigger without parameters
--the same action cannot be triggered two times
Delimiter//
CREATE TRIGGER Myter
Before INSERT
On TAB1
For each ROW
BEGIN
INSERT into TaB2 SET name=new.name;
UPDATE tab3 SET name= "newname" WHERE name=new.name;
END
//
--The above trigger has been verified without any errors!!!


--View triggers
Show Triggers\g


--Delete Trigger
Drop trigger Myter;


--the trigger cannot be modified at this moment, so it can only be deleted by the Rebuild method to update




--Cursor--------------
--the cursor part is more complex, and is not commonly used, and then gradually add it






--View---
--views are made up of existing tables, filtered by certain filters, so that you can selectively let users see or modify part of the data so that the data is not completely exposed.
--Demo Instance
CREATE table user (id int not null,name varchar (a) not null,address varchar (a) not null,age int not null,tel varchar (11) Not null);
--The table's personal information, name, address can be made public, but age and phone not willing to open, you can create a view
CREATE View MyView as select name,address from user;
SELECT * from MyView;
--That's how it ends.


--View the creation syntax of the view
Show CREATE View Myview\g


--Delete View
Drop View MyView;


--almost all of it, will be slowly added later, or remember to have time to look back to see