Mysql database summary and review

Mysql database summary and review 1. enhanced mysql database Review 2. mysql transaction trigger 3. mysql index foreign key enhancement 4. zendstudio Installation and Use 5. svn version controller usage 1. mysql database review enhancement 1. Export the mysql database's ecshop to disk D. The mysqldump command is the same as the mysql command to generate an environment variable: that is, the system finds the path of the program to be run Mysqldump-u root-p Database Name> the exported file name 1. Find the mysqldump.exe program, which is used for backup 2. Execute the BACKUP command E: \ wamp \ mysql \ bin> mysqldump-u root-p ecshop> d:/ecshopbak. SQL (2) 1,1,a data table 1. Find the mysqldump.exe program. It is used to back up 2. Run the BACKUP command E: \ wamp \ mysql \ bin> mysqldump-u root-p e Cshop ecs_goods> d: goodsbak. SQL import external database file source command is a mysql delete data table drop table ecs_goods; clear data in the data table: delete from table name delete and drop difference: delete q clear data in the data table (structure) drop to delete the entire table encoding: UTF-8 is the unified encoding. It supports both Chinese and English. We recommend that you use UTF-8 when creating the table, which is conducive to the international website. gb2312 supports Chinese in mainland China. gbk includes gb2312, supports Korean, Japanese, and Hong Kong... Mysql database data type (column type) 1, numerical type (integer, floating point type, fixed point type) 2, string type (char, varchar, text, enum type )) however, due to compatibility and portability of the enum type, it is not recommended to use 3, date and time type datetime () ------ year month day hour minute second date ---------- year month day time -------- hour minute second time stamp to indicate time ----------- timestamp ------- now () int indicates the range --- 0 --- tinyint indicates the range ------ 0-255smallint -------- signed ------ positive number-128 to 127 indicates the range is also 255 length unsigned ------ no positive number 0 -255 If You Want To create an unsinged string for the entire class, set the char length (fixed length) string ----- 0-65535varchar Variable Length ---- The number of varchar (255) bytes. A maximum of 255 text data time types of date () parse this database function timestamp ------------ do not parse ----- so the speed is faster than the mysql date function. I suggest using a timestamp to represent the time exercise: create a data table for our students, fields include student id name admission time tinyint 255-128 127 0-255 unsignedvarchar (10) variable length, up to 10 bytes, actually use a few allocated to you several char () --------- fixed length 0-65535, regardless of whether the string occupies more or less 65535 length name timedate, datetime mysql built-in date function, mysql engine query must first parse the function, slow timestamp string ('2017-04-16 ') Considerations for optimizing the table: 1. Create an id ----- try to link the table prefix to the id. If the item table goods_id is classified Table cat_id 2, when creating a table --- specify storage engine ------ if the query is dominated, engine = myisam, to avoid garbled ---- specify utf8 default charset3, try to use a timestamp or string to represent the date and time 4, field (value, string) (2) query statement: obtain the use 1 of the group by group of commodity information whose id is 3 5 7 8. The cat_id field is added to the product table and associated with 2, ecs_category Category Table stores the category id of a product. Note: // query different cat_id values. The number of different products is as follows: (1) grouping by category first (2) count the number of items in the group. group by is grouped by xx. If the aggregate function is used together with group by, the statistics are the data exercise questions in the current group: under different product categories, the sum of product prices is queried. Under different product categories, the most expensive products are queried. Under different product categories, the average product prices Total number function: Max: Maximum M1in: Minimum Count (): Count the total number of records Avg: average Sum: Sum having Sum where difference: where: select goods_name from ecs_goods where goods_id = 9; having: The preceding query result is restricted, what are the conditions for this result that union queries union join union can connect multiple query statements, select 1 union select2 (select goods_name from ecs_goods where cat_id = 3 order by shop_price desc limit 3) union (select goods_name from ecs_goods where cat_id = 4 order by shop_price asc limit 3); // query For more information, see mysql> select cat_name from ecs_category where cat_id = 1 union select cat_name from ecs_category where parent_id = 1; subquery if a select query statement contains another select query statement, it is called subquery (1) select-type subquery select goods_name from ecs_goods where cat_id = (select cat_id from ecs_category where cat_name = 'gsm phone'); (2) from-type subquery note: the subquery list after from must have its own alias select goods_name from (select * from ecs_goods where cat_id in (3, 5) as tem _ Goods where goods_name like 'nokia % '; Train of Thought 1: select avg (score ), name from score where name in (select name from score where score <60 group by name having count (*)> = 2) group by name; Method 2: 1: count each group, number of homework with scores less than 60 select count (subject) as na from score group by subject having na> = 2; train of thought, count each group, number of lessons with scores less than 60 select sum (score <60) as su from score group by name having su> = 2; select avg (score), name from score where Name in (select name from score where score <60 group by name having count (*)> = 2) group by name; 2. mysql transaction trigger 1, connection query union connects multiple select statements join query: When the expected results are no longer the same table, we need to use the join query syntax: A field in table1 join table2 on table1 = a field condition in table2 is the product category id in the product table = the category id in the category table. When a query statement is used for multiple times, or when the table name is particularly long, we recommend that you add an alias for this table: mysql> select goods_name, shop_price, cat_name from ecs_goods as g join ecs_category as c on g. cat_id = C. cat_id where g. shop_price> = 1000; join classification: left join query left join: the query result is displayed as long as there are records on the left, null is not displayed on the right. right join query: right join. inner join: the query result is a data transaction that both tables have. What is a transaction? A transaction is a logical group of operations that comprise all units of this group of operations. If all the units are successful or fail, this feature is the transaction. Note: mysql DATA supports transactions, however, the innoDB Storage engine must be used to solve this problem: mysql transactions solve this problem. Because of the transaction characteristics of mysql, this group of operations is required, either all successful or all failed, this avoids the failure of an operation. How to Use Data Security: (1) Before executing SQL statements, start transaction must be enabled; (2) normal execution of our SQL statements (3) After SQL statements are executed, there are two cases: 1. All are successful. We need to submit the impact of SQL statements on the database to the database. committ 2, some SQL statements fail, when we execute rollback, we will immediately cancel the php code for database operations: after the transaction is started, as long as the commit is not executed, the SQL statement will not affect the real database only after the commit is executed, trigger: A trigger is a database program that monitors a behavior of a data table. Once this behavior of a data table occurs, execute the corresponding SQL statement trigger syntax structure immediately: create trigger name trigger event on the table name listened for each row action after the execution of the SQL statement trigger event composition :; it consists of two parts: the time when the trigger event occurs ----- It is in the monitoring The behavior of the table to be listened to is commonly used by the after trigger: when adding, deleting, modifying, and creating an order table, note that order is sorted by a keyword in mysql, to avoid errors, we can add backquotes to indicate that this is not a keyword to use the trigger: Case study: once an order is generated, the corresponding inventory table must subtract the corresponding data (1) create a commodity table and an order table (2) bind a trigger to the order table. Once an order is added to the order table, run the SQL statement immediately and subtract the corresponding data from the inventory table. Now there is a problem: after each order is placed, the inventory table is replaced by the corresponding quantity, but our requirement is: how to use the trigger data in the trigger after the user orders, the inventory table minus several inventories? What is the trigger data: that is, the number of new keywords that the user purchases in the order table represents the newly added quantity, in the order table, the old keyword indicates the old record (past record, number of canceled orders). new usage: old past record, I have bought a few items but I don't want them anymore. After I cancel the order and cancel the order, I want to increase the corresponding quantity in the inventory to update the use of new and old products. The current requirements are as follows: I have purchased five samsung mobile phones and want to buy 10 now. When I need to update the order table, the trigger needs to listen for the update action and revoke the previous order, what is another order trigger? It is used to listen to a behavior (insert, delete, update) of a data table. Once this behavior occurs, immediately execute the corresponding SQL statement to delete the trigger: drop trigger name 3. mysql index foreign key enhancement 1. What is index? Index ------ Search --- guide ------ guided me to search for the definition of a record: If there is no index, we need to query a record from the first record, knowing the records we need, if the number of records is very large, it is equivalent to a haystack, and the speed is particularly slow: create an index ----- point to the Data Location ----- reflect to the Xinhua Dictionary ----- the advantage of the number of pages in which the record is located: the query speed is high, but not enough: I have increased the workload of maintaining indexes-every time I add a record, I will add an index number in the index list to point to the position of this record for fast query, however, the speed of adding, deleting, and updating is slow. What are the indexes stored in the extended database? The category stored in the index: 1. Primary Key Index: When a table is created and the primary key is specified, the primary key is automatically set to primary key index 2. Common Index: it is to create an index on a common field. 3. Unique index: a field in the data table is unique but not repeated. ---- 4. Full-text index: create a full-text index on a field, extract the keywords in the record, and then index each keyword. sph#----- you can create a full-text index to create an index: (1) create [index type] index name on table name (on a table field) (2) alter table name add [index type] index (table field) the number of records queried before the index is created. A record is queried after the index is created. The number of records is deleted. Index: Modify the index. Only the foreign key can be deleted first. What is the foreign key: example of a student: each student corresponds to a personal information table (name, student ID) Student details information table (score, violation record, reward, home address, tuition ,) after these two tables are associated with foreign keys, if one day you graduate, you should delete your name and student ID. This student The following detailed information (score, violation record, rewards, home address, and tuition) automatically deletes the condition for using the foreign key: (1) ensure that the data table storage engine must be innoDB (2) the data type of the columns in the two tables of the foreign key relationship must be similar to that of the int tinyint int ---------- varchar foreign key Syntax: create table tem (id int, name varchar, foreign key (id) the behavior of the foreign key table monitored by references outTable (id) on delete cascade on update cascade): delete update foreign key (id) specifies which field of the current table is a foreign key references and which external table is associated with constrict ''; specifies a name (the name of the foreign key association, in order to delete this foreign key in the future, the constraint name mysql> create table xiaodi (Id int primary key auto_increment, foreign_id int, name varchar (32), constraint xiaodi_for foreign key (foreign_id) references dage (id) on delete cascade on update cascade) engine = innodb default charset = utf8; (1) Associate two tables with the foreign key field created in the current table using the foreign key ------ a field in the External table can be associated: foreign key (foreign_id) after references dage (id) is associated, It listens to the External table. If it is deleted, what should I do? If the table is updated, what should I do? on delete (3 parameters) on update (3 parameters) the current table listens to the table associated with the foreign key, there are two types of listening behaviors (delete and update). Once these actions occur, I (xiaodi) divide the operations into three (commonly used three), cascade: waterfall, indicating which table is strictly restrict associated with the foreign key, indicating that the table associated with the external key is deleted, And I strictly require myself (do not follow the changes) NO ACTION -------- NO ACTION, the deletion of foreign keys is not reflected: alter table Name drop foreign key name reset Database Password (1) Where do we need to know where the Database Password is stored? To change the password of the user table of the mysql database, first enter the mysql window, and then modify the password field of the user table. (do not close the window after the process is completed.) (3) link to the mysql server through the client. At this time, we log on to the root user (4) Hurry and change the password. Note: Use the mysql password () encryption function (5) close the process (end the process and restart the service process) in the form of skipping permission verification logon. 4. the installation and use of zendstudio is like a combination of dw notepad ~ 5. svn version controllers include servers, the client server creates a repository inventory library file in the root directory. For example, If weibo runs the svnserve-d-r create path in the cmd window, create a folder on the client and obtain the file from the relevant path. Then, use commit use update to select the corresponding version for update when it is submitted to the server for updates