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

This article mainly gives you a brief account of the common skills in Mysql. You can look up the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article on what common skills in Mysql can bring you some practical help.

1 、 DML 、 DDL 、 DCL

DML (Dada Manipulation Language) data manipulation language (CRUD) A). Add a). Insert insert into An in a single line (a <...] values / b). Multi-line insertion of insert into A (a recital bpeng c) values (a 1 recital b1 penny C1), (a2 meme b2 menc c 2); B). Update a) .set single field update A set a = 1 where c = 3; b) .set multifield update A set a = 1, b = 2 where c = 2; C). Query a). Pay attention to the where condition select a dint bpm c from A; D). Delete a). Note the where condition delete from A where c = 3 + 2) .DDL (Dada Definition Language) database definition language A) .CREATE a). Create the table create table A (an int (10), b tinyint (4), c tinyint (4), d char (10),...); B) .ALERT a). Add field alter table An add tag int; b). Modify field alter table A modify COLUMN tag char (20); c). Delete the field alter table A drop COLUMN tag; C) .DROP a). Delete table drop table A; b). Delete library drop database Demo; 3) .DCL (Dada Control Language) database control language A) .grant authorization a) .grant permissions on database objects to user B) .deny refuses to authorize DENY permissions TO user C) .revoke revokes authorization a) .revoke permissions on database objects from user 4). Other A). View the table structure a) .desc A; b) .describe A; c) .show columns from A; B). Clear table data a) .truncate table A

2. Analysis of SQL sentences.

1). EXPLAIN, DESC statements-key information interpretation A) .Type (system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL) B. Possible_keys (NULL, there is no related index. In this case, you can improve your query performance by checking the WHERE clause to see if it references certain columns or columns suitable for the index) C). Key (key actually decides to use the key (index) D). Key_len (number of bytes used in the index, shorter length without loss of accuracy) E). Ref (join matching condition) That is, which columns or constants are used to find values on index columns) F) .rows (MySQL estimates the number of rows to read to find the required records based on table statistics and index selection) G) .Extra (details of MySQL solving queries) 2) .SHOW PROCESSLIST analysis

3. Mysql executes stored procedures through job task scheduling (event)

1)。 Event (EVENT) calls the function (f (x)) (stored procedure) a). Event Call proc_detail (); b). Stored procedure CREATE PROCEDURE proc_detail () BEGIN DECLARE id1 bigint (20); DECLARE openid1 varchar (100); DECLARE unionid1 varchar (100);-- traversal data end flag DECLARE done INT DEFAULT FALSE;-- Vernier DECLARE cur_account CURSOR FOR select id,openid,unionid from m_users where phone_bind = 1 -- bind the closing flag to the cursor DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;-- Open the cursor OPEN cur_account;-- traverse read_loop: LOOP-- take the value of multiple fields FETCH NEXT from cur_account INTO id1,openid1,unionid1 IF done THEN LEAVE read_loop; END IF;-- insert into m_users_details (uid,openid,unionid,style) VALUES (id1,openid1,unionid1,1) you want to do; END LOOP; CLOSE cur_account; END

What are the common skills in Mysql to tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.