"MySQL must know must know" study notes

"MySQL must know must know" study notes

0 database script

####################################### MySQL Crash Course # http://www .forta.com/books/0672327120/ # Example table creation scripts ################################# #### ####################### # Create customers table ##################### ## CREATE TABLE customers ( cust_id int NOT NULL AUTO_INCREMENT, cust_name char ( 50 ) NOT NULL , cust_address char ( 50 ) NULL , cust_city char ( 50 ) NULL , cust_state char ( 5 ) NULL , cust_zip char ( 10 ) NULL , cust_country char ( 50 ) NULL , cust_contact char ( 50 ) NULL , cust_email char ( 255 ) NULL , PRIMARY KEY (cust_id) ) ENGINE = InnoDB ; ######################### Create orderitems table ##################### #### CREATE TABLE orderitems ( order_num int NOT NULL , order_item int NOT NULL , prod_id char ( 10 ) NOT NULL , quantity int NOT NULL , item_price decimal ( 8 , 2 ) NOT NULL , PRIMARY KEY (order_num, order_item) ) ENGINE = InnoDB ; ##################### # Create orders table ##################### CREATE TABLE orders ( order_num int NOT NULL AUTO_INCREMENT, order_date datetime NOT NULL , cust_id int NOT NULL , PRIMARY KEY (order_num) ) ENGINE = InnoDB ; ####################### # Create products table ###################### CREATE TABLE products ( prod_id char ( 10 ) NOT NULL , vend_id int NOT NULL , prod_name char ( 255 ) NOT NULL , prod_price decimal ( 8 , 2 ) NOT NULL , prod_desc text NULL , PRIMARY KEY (prod_id) ) ENGINE = InnoDB ; ###################### # Create vendors table ###################### CREATE TABLE vendors ( vend_id int NOT NULL AUTO_INCREMENT, vend_name char ( 50 ) NOT NULL , vend_address char ( 50 ) NULL , vend_city char ( 50 ) NULL , vend_state char ( 5 ) NULL , vend_zip char ( 10 ) NULL , vend_country char ( 50 ) NULL , PRIMARY KEY (vend_id) ) ENGINE = InnoDB ; ########################### # Create productnotes table ################### ######## CREATE TABLE productnotes ( note_id int NOT NULL AUTO_INCREMENT, prod_id char ( 10 ) NOT NULL , note_date datetime NOT NULL , note_text text NULL , PRIMARY KEY (note_id), FULLTEXT(note_text) ) ENGINE =MyISAM; ##################### # Define foreign keys ##################### ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders a FOREIGN KEY (order_num) the REFERENCES Orders (order_num); the ALTER TABLE OrderItems the ADD CONSTRAINT fk_orderitems_products a FOREIGN KEY (the prod_id) the REFERENCES Products (the prod_id); the ALTER TABLE Orders the ADD CONSTRAINT fk_orders_customers a FOREIGN KEY (the cust_id) the REFERENCES the Customers (the cust_id); The ALTER TABLE Products the ADD CONSTRAINT fk_products_vendors a FOREIGN KEY (vend_id) the REFERENCES vendors (vend_id); duplicated code
####################################### MySQL Crash Course # http://www .forta.com/books/0672327120/ # Example table population scripts ################################# #### ######################### # Populate customers table #################### ###### INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) VALUES ( 10001 , 'Coyote Inc.' , '200 Maple Lane' , 'Detroit' , 'MI ' , '44444' , 'USA' , 'Y Lee' , 'ylee@coyote.com' ); INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact) VALUES ( 10002, 'Mouse House' , '333 Fromage Lane' , 'Columbus' , 'OH' , '43333' , 'USA' , 'Jerry Mouse' ); INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) VALUES ( 10003 , 'Wascals' , '1 Sunny Place' , 'Muncie' , 'IN' , '42222' , 'USA' , 'Jim Jones' , 'rabbit@wascally.com' ); INSERT INTOcustomers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) VALUES ( 10004 , 'Yosemite Place' , '829 Riverside Drive' , 'Phoenix' , 'AZ' , '88888' , 'USA' , 'Y Sam' , 'sam@yosemite.com' ); INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact) VALUES ( 10005 , 'E Fudd' , '4545 53rd Street' ,'Chicago' ,'IL' , '54545' , 'USA' , 'E Fudd' ); ######################## # Populate vendors table ###################### ## INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country) VALUES ( 1001 , 'Anvils R Us' , '123 Main Street' , 'Southfield' , 'MI' , '48075' , 'USA ' ); INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country) VALUES ( 1002 , 'LT Supplies' , '500 Park Street' , 'Anytown' ,'OH' , '44333' , 'USA' ); INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country) VALUES ( 1003 , 'ACME' , '555 High Street' , 'Los Angeles' , 'CA' , '90046' , 'USA' ); INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country) VALUES ( 1004 , 'Furball Inc.' , '1000 5th Avenue' ,'New York' ,'NY' , '11111' , 'USA' ); INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country) VALUES ( 1005 , 'Jet Set' , '42 Galaxy Road' , 'London' , NULL , 'N16 6PS' , 'England' ); INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country) VALUES ( 1006 , 'Jouets Et Ours' , '1 Rue Amusement' ,'Paris' ,NULL , '45678' , 'France' ); ######################### Populate products table ##################### #### INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES ( 'ANV01' , 1001 , '.5 ton anvil' , 5.99 , '.5 ton anvil, black, complete with handy hook' ); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES ( 'ANV02' , 1001 , '1 ton anvil' , 9.99 , '1 ton anvil, black, complete with handy hook and carrying case' ); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES ( 'ANV03' , 1001 , '2 ton anvil' , 14.99 , '2 ton anvil, black, complete with handy hook and carrying case' ); INSERT INTO products (prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES ( 'OL1' , 1002 , 'Oil can' , 8.99 , 'Oil can, red' ); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES ('FU1' , 1002 , 'Fuses' , 3.42 , '1 dozen, extra long' ); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES ( 'SLING' , 1003 , 'Sling' , 4.49 , ' Sling, one size fits all' ); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES ( 'TNT1' , 1003 , 'TNT (1 stick)' , 2.50 , 'TNT, red, single stick'); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES ( 'TNT2' , 1003 , 'TNT (5 sticks)' , 10 , 'TNT, red, pack of 10 sticks' ); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES ( 'FB' , 1003 , 'Bird seed' , 10 , 'Large bag (suitable for road runners)' ); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES ( 'FC', 1003 , 'Carrots' , 2.50 , 'Carrots (rabbit hunting season only)' ); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES ( 'SAFE' , 1003 , 'Safe' , 50 , 'Safe with combination lock' ); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES ( 'DTNTR' , 1003 , 'Detonator' , 13 , 'Detonator (plunger powered),fuses not included'); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES ( 'JP1000' , 1005 , 'JetPack 1000' , 35 , 'JetPack 1000, intended for single use' ); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES ( 'JP2000' , 1005 , 'JetPack 2000' , 55 , 'JetPack 2000, multi-use' ); ####################### # Populate orders table ####################### INSERT INTO orders(order_num, order_date, cust_id) VALUES ( 20005 , '2005-09-01' , 10001 ); INSERT INTO orders(order_num, order_date, cust_id) VALUES ( 20006 , '2005-09-12' , 10003 ); INSERT INTO orders(order_num, order_date, cust_id) VALUES ( 20007 , '2005-09-30' , 10004 ); INSERT INTOorders(order_num, order_date, cust_id) VALUES ( 20008 , '2005-10-03' , 10005 ); INSERT INTO orders(order_num, order_date, cust_id) VALUES ( 20009 , '2005-10-08' , 10001 ); ########################### # Populate orderitems table ################## ######## INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price) VALUES ( 20005 , 1 , 'ANV01' , 10 , 5.99 ); INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price ) VALUES ( 20005 , 2 , 'ANV02' , 3 , 9.99 ); INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price) VALUES ( 20005 , 3 , 'TNT2' , 5 , 10 ); INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price) VALUES ( 20005 , 4 , 'FB' , 1 , 10 ); INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price) VALUES ( 20006 , 1 , 'JP2000' , 1 , 55 ); INSERT INTOorderitems(order_num, order_item, prod_id, quantity, item_price) VALUES ( 20007 , 1 , 'TNT2' , 100 , 10 ); INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price) VALUES ( 20008 , 1 , 'FC' , 50 , 2.50 ); INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price) VALUES ( 20009 , 1 , 'FB' , 1 ,10 ); INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price) VALUES ( 20009 , 2 , 'OL1' , 1 , 8.99 ); INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price) VALUES ( 20009 , 3 , 'SLING' , 1 , 4.49 ); INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price) VALUES ( 20009 , 4 ,'ANV03' , 1 , 14.99 ); ############################ # Populate productnotes table ################ ############ INSERT INTO productnotes(note_id, prod_id, note_date, note_text) VALUES ( 101 , 'TNT2' , '2005-08-17' , 'Customer complaint: Sticks not individually wrapped, too easy to mistakenly detonate all at once. Recommend individual wrapping.' ); INSERT INTO productnotes(note_id, prod_id, note_date, note_text) VALUES ( 102 , 'OL1' , '2005-08-18' , 'Can shipped full, refills not available. Need to order new can if refill needed.' ); INSERT INTO productnotes(note_id, prod_id, note_date, note_text) VALUES ( 103 , 'SAFE' , '2005-08-18' , 'Safe is combination locked, combination not provided with safe. This is rarely a problem as safes are typically blown up or dropped by customers.' ); INSERT INTO productnotes(note_id, prod_id, note_date, note_text) VALUES ( 104 , 'FC' , '2005-08-19' , 'Quantity varies, sold by the sack load. All guaranteed to be bright and orange, and suitable for use as rabbit bait.' ); INSERT INTO productnotes(note_id, prod_id, note_date, note_text) VALUES ( 105 , 'TNT2' , '2005-08-20' , 'Included fuses are short and have been known to detonate too quickly for some customers. Longer fuses are available (item FU1) and should be recommended.' ); INSERT INTO productnotes(note_id, prod_id, note_date, note_text) VALUES ( 106 , 'TNT2' , '2005-08-22' , 'Matches not included, recommend purchase of matches or detonator (item DTNTR).' ); INSERT INTO productnotes(note_id, prod_id, note_date, note_text) VALUES ( 107 , 'SAFE' , '2005-08-23' , 'Please note that no returns will be accepted if safe opened using explosives.' ); INSERT INTO productnotes(note_id, prod_id, note_date, note_text) VALUES ( 108 , 'ANV01' , '2005-08-25' , 'Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.' ); INSERT INTO productnotes(note_id, prod_id, note_date, note_text) VALUES ( 109 , 'ANV03' , '2005-09-01' , 'Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.' ); INSERT INTO productnotes(note_id, prod_id, note_date, note_text) VALUES ( 110 , 'FC' , '2005-09-01' , 'Customer complaint: rabbit has been able to detect trap, food apparently less effective now.' ); INSERT INTO productnotes(note_id, prod_id, note_date, note_text) VALUES ( 111 , 'SLING' , '2005-09-02' , 'Shipped unassembled, requires common tools (including oversized hammer).' ); INSERT INTO productnotes(note_id, prod_id, note_date, note_text) VALUES ( 112 , 'SAFE' , '2005-09-02' , 'Customer complaint: Circular hole in safe floor can apparently be easily cut with handsaw.' ); INSERT INTO productnotes(note_id, prod_id, note_date, note_text) VALUES ( 113 , 'ANV01' , '2005-09-05' , 'Customer complaint: Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.' ); INSERT INTO productnotes(note_id, prod_id, note_date, note_text) VALUES ( 114 , 'SAFE' , '2005-09-07' , 'Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added. Comment forwarded to vendor.' ); Copy code

1 Understanding SQL

Database: a container for storing organized data (usually a file or a group of files)

Database software (DBMS): People usually use database software, and the database is a container created and manipulated by the DBMS

Table: A structured list of a specific type of data

Schema: information about the layout and characteristics of databases and tables

Column: A field in a table. All tables are composed of one or more columns.

Row: a record in the table

Primary key: Each row in the table should have a column that can uniquely identify itself, and its value can uniquely distinguish each row in the table. Without a primary key, it is difficult to update or delete specific rows in a table because there is no safe way to ensure that only related rows are involved.

Mysql primary key rules:

  • No two rows have the same primary key value

  • Each row must have a primary key value, and the primary key value is not allowed to be null

2 Introduction to mysql

  • A brief introduction to Mysql, you can query related information by yourself

3 Use MySQL

# Create a database learn_mysql, connect to the database use learn_mysql; # Show tables in a database show databases ; # Show all the columns in a table design information: describe table_name equivalence Show the Columns from the Customers; # above equivalent DESCRIBE the Customers; Copy the code

4 Retrieve data

# The displayed data is unordered: the sql statement returns raw, unformatted data select prod_name from products; # De-duplicate search select vend_id from products; select distinct vend_id from products; # The number of database rows starts from 0; rowIndex = (front-end page number -1)*The number of displays per page select prod_name from products limit 0 , 1 ; Copy code

5 Sort and retrieve data

  • order by: must be after the from clause, if combined with limit, limit must be after order by
# Single column arrangement: order by, retrieval data usually needs to be sorted to make sense, the default is ascending order select prod_name from products order by prod_name; # Multiple column arrangement: multiple columns to sort, the first one is the same and the next one is sorted. If the previous one is different, the latter sort condition will never be established select prod_id,prod_price,prod_name from products order by prod_price,prod_name; # Sort in ascending order select prod_id,prod_price,prod_name from products order by prod_price desc ; # The previous ascending order, the next descending order select prod_id,prod_price,prod_name from products order by prod_price desc ,prod_name; # Find the highest price select prod_price from products order by prod_price desc limit 1 ; Copy code

6 Filter data

OperatorDescription
=equal
<>not equal to
!=not equal to
<Less than
<=Less than or equal to
>more than the
>=greater or equal to
betweenBetween the two
# where: case insensitive select prod_name,prod_price from products where prod_name = 'fuses' ; select prod_name,prod_price from products where prod_name = 'FUses' ; # select vend_id,prod_name from products where vend_id <> 1003; select vend_id,prod_name from products where vend_id != 1003; # between: Query within the range select prod_name, prod_price from products where prod_price between 5 and 10 ; # Null value query: is null select cust_id from customers where cust_email is null ;

7

Advantages of the in operator:

  1. The syntax is clearer and more intuitive
  2. Calculation sequence is easier to manage
  3. in is faster than or
  4. The biggest advantage of in is that it can contain other select statements
# where+and: Both must match select prod_id, prod_price, prod_name from products where vend_id = 1003 and prod_price <= 10 ; # where+or: match any one can select prod_name, prod_price from products where vend_id = 1002 or vend_id = 1003 ; # and+or:and has a higher priority than or, vend_id=1003 and prod_price >=10 execute first select prod_name,prod_price from products where vend_id= 1002 or vend_id= 1003 and prod_price >= 10 ; # or words need to be executed first , You need to add parentheses select prod_name,prod_price from products where (vend_id= 1002 or vend_id= 1003 ) and prod_price >= 10 ; # in: in (a,b) is equivalent to a or b select prod_name,prod_price from products where vend_id in ( 1002 , 1003 ) order by prod_name; select prod_name,prod_price from products where vend_id = 1002 or vend_id = 1003 order by prod_name; # not + in: MySQL supports select prod_name, prod_price from products where vend_id not in ( 1002 , 1003 ) order by prod_name; copy code

8 Tips for using wildcards

Pay attention to trailing spaces: %anvil cannot match the last one or more spaces. The solution is to remove the leading and trailing spaces in %anvil% or the function in Chapter 11

Note Null: Although% can match anything, it cannot match a null value

# Wildcard%: default is not case sensitive SELECT the prod_id, PROD_NAME from Products WHERE PROD_NAME like 'the Jet%' ; SELECT the prod_id, PROD_NAME from Products WHERE PROD_NAME like 'Jet%' ; # Wildcard% is used before and after: select prod_id,prod_name from products where prod_name like '%anvil%' ; Wildcard _ #: matching a single character, .5 ton anvil out on matching, job% SELECT the prod_id, PROD_NAME from Products WHERE PROD_NAME like '_ TON Anvil' ; SELECT the prod_id, PROD_NAME from Products WHERE PROD_NAME like '% TON Anvil' ; Copy code

9 Search with regular expressions

Blank metacharacterDescription
\\fPage change
\\nWrap
\\rCarriage return
\\ttabulation
\\vVertical tabulation
Character classDescription
[:alnum:]Any letter or number (same as [a-zA-Z0-9])
[:alpha:]Any character (same as [a-zA-Z])
[:blank:]Space and tab (same as [\\t])
[:cntrl:]ASCII control characters (ASCII 0 to 32 and 127)
[:digit:]Any number (same as [0-9])
[:graph:]Same as [:print:] but without spaces
[:lower:]Any lowercase letter (same as [az])
[:prinit:]Any printable character
[:punct:]Any character that is neither in [:alnum:] nor in [:cntrl:]
[:space:]Any blank characters including spaces (same as [\\f\\n\\r\\t\\v])
[:upper:]Any uppercase character (same as [AZ])
[:xdigit:]Any hexadecimal number (same as [a-fA-F0-9])
Repeating metacharactersDescription
*0 or more matches
+1 or more matches (equal to {1,})
?0 or 1 match (equal to {0,1})
{n}Specified number of matches
{n,}Not less than the specified number of matches
{n,m}Range of matching number (m does not exceed 255)
Anchor metacharacterDescription
^Start of text
$End of text
[[:<:]]The beginning of the word
[[:>:]]End of word
# regexp: means using regular expressions, '1000' is equivalent to %1000%, which is equivalent to fuzzy matching select prod_name from products where prod_name regexp '1000' ; select prod_name from products where prod_name regexp'.000 ' ; # The difference between like and regexp select prod_name from products where prod_name like '1000' ; # like matches the entire column, the matched text appears in the column value, and the corresponding row will not be returned select prod_name from products where prod_name regexp '1000' ; # regexo matches the entire column, the matched text appears in the column value, and the corresponding row will be returned # Regexp: Default is not case sensitive, if case sensitive, binary regexp SELECT PROD_NAME from Products WHERE PROD_NAME regexp binary 'the JetPack .000' ; # regexp+|: function similar to or select prod_name from products where prod_name regexp '1000|2000' order by prod_name; # []:Indicates matching a certain character # [123] ton = strictly in accordance with [1|2|3] ton select prod_name from products where prod_name regexp '[123] ton' order by prod_name; # ^: Invert select prod_name from products where prod_name regexp '[^123] ton' order by prod_name; # 1|2|3 ton: the column contains 1 or 2 or 3 ton, which is different from the meaning of [123 ton] select prod_name from products where prod_name regexp '1|2|3 ton' order by prod_name; # [1-5]: indicates the range select prod_namefrom products where prod_name regexp '[1-5] ton' order by prod_name; #. Means to match any character, select vend_name from vendors where vend_name regexp '.' order by vend_name; # Match special characters plus//,//. is the match. Character select vend_name from vendors where vend_name regexp '\\.' order by vend_name; # Match (digital stick) or (digital sticks) select prod_name from products where prod_name regexp '\\([0-9] sticks?\\)' order by prod_name; select prod_name from products where prod_name regexp '\\([: digit:] sticks?\\)' order by prod_name; # Match contains 4 digits select prod_name from products where prod_name regexp '[:digit:]{4}' order by prod_name; Locator #: ^ represents the regular expression from the start of the text matches all or products to a decimal number starting # SELECT PROD_NAME from Products WHERE PROD_NAME regexp '^ [0-9\\.]' Order by PROD_NAME; duplicated code

10 Create a calculated field

Field: basically the same meaning as a column, but the column name is the name of the column actually stored in the database, and the field is usually used in the connection of the calculated field

Concatenate: concatenate values together to form a single value

# CONCAT uses splicing, mysql splicing uses concat, and other SQL will be different. select CONCAT (vend_name, '(' ,vend_country, ')' ) from vendors order by vend_name; # Rtrim: remove the space on the right select CONCAT ( Rtrim (vend_name), '(' , Rtrim (vend_country), ')' ) from vendors order by vend_name; # take alias select CONCAT ( Rtrim (vend_name), ' (' , Rtrim (vend_country), ')' ) asvend_title from vendors order by vend_name; # Perform calculations, MySQL arithmetic operators support +, -, *,/four select prod_id, quantity, item_price from orderitems where order_num = 20005 ; select prod_id, quantity, item_price, quantity*item_price as expanded_price from orderitems where order_num = 20005 ; # Mysql select the words may be omitted from simply facilitates access to and process the data select . 3 * 2 ; select TRIM ( 'ABC' ); select Now (); # Returns the current date and time to copy the code

11 Use data processing functions

Commonly used text processing functionsDescription
Left()Returns the character to the left of the string
Length()Returns the length of the string
Locate()Find a substring of the string
Lower()Convert string to lowercase
LTrim()Remove the left space of the string
Right()Returns the character on the right side of the string
Rtrim()Remove the space on the right side of the string
Soundex()Return the SoundDex value of the string
SubString()Returns the character of the substring
UpperConvert string to uppercase

Soundex(): An algorithm that converts any text string into an alphanumeric mode that describes its language representation, the following is an example

# SOUNDEX (): text matches similar pronunciation SELECT CUST_NAME, cust_contact from the Customers WHERE cust_contact = 'Lie Y.' ; SELECT CUST_NAME, cust_contact from the Customers WHERE SOUNDEX (cust_contact) = SOUNDEX ( 'Lie Y.' ); duplicated code
Common date and time processing functionsDescription
AddDate()Add a date (day, week, etc.)
AddTime()Add a date (hour, minute, etc.)
CurDate()Return current date
CurTime()Return current time
Date()Returns the date part of the date and time
DateDiff()Calculate the difference between two dates
Date_Add()Highly flexible date calculation function
Date_Format()Return a formatted date or time string
Day()Returns the days part of a date
DayOfWeek()For a date, return the corresponding day of the week
Hour()Back to the hour part
Minute()Back to minutes
Month()Return to the month part
Now()Returns the current date and time
Second()Back to seconds
Time()Back to the time section
Year()Back to the year part
Commonly used numerical processing functionsDescription
Abs()Absolute value
Cos()Cosine
Exp()Index value
Mod()Remainder of division
Pi()PI
Rand()random number
Sin()Sine
Sqart()Square root
Tan()Tangent
# Text processing function: Upper select vend_name, Upper (vend_name) as vend_name_upcase from vendors order by vend_name; # Date yyyy-mm-dd, time HH:MM:SS # So the date is not rigorous, only the date with the default time of 0 can be found, such as 2005-09-01 00:00:00 # But 2005-09- 01 10:20:30 cannot find out the desired result select cust_id, order_num from orders where order_date = '2005-09-01' ; # Use Date() and Time() functions select cust_id, order_num from orders where Date ( order_date) = '2005-09-01' ; select cust_id,order_num from orders where Time (order_date) = '01:00:00' ; # Search for orders within September 2005 # Method 1: Need to know the specific days of the month select cust_id, order_num from orders where Date (order_date) between '2005-09-01' and '2005-09-30' ; # Method 2 : You don't need to know the specific number of days, it is most convenient to select cust_id,order_num from orders where Year (order_date) = '2005' and Month (order_date) = '9' ; copy the code

12 Summary data

Aggregate functionDescription
AVG()Average value, ignoring null values
COUNT()Rows
MAX()Maximum value
MIN()Minimum
SUM()Sum
# avg: Calculate the average value select avg (prod_price) as avg_price from products; # Calculate the average value of a specific column select avg (prod_price) as avg_price from products where vend_id = 1003 ; # Calculate the average value of a specific column after deduplication select avg ( distinct prod_price) as avg_price from products where vend_id = 1003 ; # count(*) and (1): count the number of rows select count (*) as num_cust from customers; select count ( 1 ) as num_cust from customers; select count (cust_email) as num_cust from customers; # max select max (prod_price) as max_price from products; # min select min (prod_price) as max_price from products; # sum select sum (quantity) as times_ordered from orderitems where order_num = 20005 ; # The sum value after mathematical operation select sum (item_price*quantity) as total_price from orderitems where order_num = 20005 ; # Combination aggregate function SELECT COUNT (*) AS NUM_ITEMS, min (prod_price) AS MIN_PRICE, max (prod_price) AS of max_price, AVG (prod_price) AS the avg_price from Products; duplicated code

13 packet data

Precautions for using group by:

  • The group by clause can contain any number of columns. This makes it possible to nest groups while data grouping provides finer control
  • If groups are nested in the group by clause, the data will be summarized on the last specified group. In other words, when creating a grouping, all the specified columns are calculated together
  • Each column listed in the group by clause must be a retrieval column or a valid expression (but not an aggregate function). If you use an expression in select, you must specify the same expression in the group by clause
  • In addition to the aggregate calculation statement, each column in the select statement must be given in the group by clause
  • If the grouping column has a null value, then null will be returned as a grouping. If there are multiple rows of null values in the column, they will be grouped together
  • The phrase group by must appear after where and before order by

The difference between having and where:

  • where filters before grouping, and having filters after array grouping
order bygroup by
Sorted outputGroup rows. But the output may not be in the order of grouping
Any column can be usedIt is only possible to use select columns or expression columns, and each select column expression must be used
Not necessarily requiredIf you use columns (or expressions) with aggregate functions, you must use
Execution order of words and sentencesDescriptionDo you have to use
selectThe column or expression to returnYes
fromThe table from which to retrieve dataOnly used when selecting data from the table
whereRow-level filteringno
group byGroup descriptionOnly used when calculating focus by group
havingGroup level filteringno
order byOutput sort orderno
limitThe number of rows to retrieveno
# What if you want to return the number of products provided by each supplier? select count ( 1 ) from products where vend_id = 1003 ; # group by: The array is grouped and aggregated for each group instead of each result set # Using group by to output may not be the original ordering of the grouping, so a good habit is to use group by + order by together select vend_id, count ( *) as num_prods from products group by vend_id order by vend_id; # group by + rollup: sum up the total (level) for each group select vend_id, count (*) as num_prods from products group by vend_id with rollup order by vend_id; # gruop by is grouping, +having is filtering grouping select cust_id, count (*) as orders from orders group by cust_id having count (*) >= 2 ; # where+group by combination use: where filter the data first, and then group select vend_id, count (*) as num_prods from products where prod_price >= 10 group by vend_id having count (*)>= 2 order by vend_id; select vend_id, count (*) as num_prods from products group by vend_id having count (*)>= 2 order by vend_id; # Retrieve the order number with the total order price >=50 and the summary order price, use the origin of the good habit of group by + order by select order_num, sum (quantity * item_price) as ordertotal from orderitems group by order_num having sum (quantity * item_price)> = 50 ; select order_num, sum (quantity * item_price) as ordertotal from orderitems group by order_num having sum (quantity * item_price) >= 50 order by ordertotal; copy code

14 Use subqueries

The most common use of subqueries is in the in operator of the where clause + and used to fill calculated columns.

# List all customers of the order item TNT2 # 1. Retrieve the number of all orders containing the item TNT2 # 2. Retrieve all customer IDs with the order number listed in the previous step # 3. Retrieve all customers with the customer ID returned in the previous step Information SELECT cust_name, cust_contact FROM customers The WHERE the cust_id the IN ( the SELECT the cust_id the FROM Orders the WHERE order_num the IN ( the SELECT order_num the FROM OrderItems the WHERE the prod_id = 'TNT2' )); # The total number of orders for each customer in the customers table, the order and the ID of the corresponding customer are stored in the orders table # The subquery is executed once for each customer retrieved, and the subquery is executed 5 times, so 5 are retrieved Customer select cust_name, cust_state, ( select count (*) from orders where orders.cust_id = customers.cust_id ) as orders from customers order by cust_name; copy code

15 Use connections

Cartesian Product:

  • The structure returned by the table relationship without the join condition is the Cartesian product. The number of rows retrieved will be the number of rows in the first table multiplied by the number of rows in the second table
  • Note: When using multi-table query, please use where to match, otherwise there will be a lot of false data
# Two tables join query the SELECT vend_name, prod_name, prod_price from vendors, Products's where vendors.vend_id = products.vend_id the Order by vend_name, prod_name; # when multi-table join query, use the match where, otherwise there will be a lot of useless data the SELECT vend_name ,prod_name,prod_price from vendors,products order by vend_name,prod_name; # Inner join query: same as the first query result select vend_name,prod_name,prod_price from vendors inner join products on vendors.vend_id = products.vend_id order by vend_name,prod_name ; # Join multiple tables SELECT prod_name, vend_name, prod_price, quantity FROM orderitems, products, vendors WHERE products.vend_id = vendors.vend_id AND orderitems.prod_id = products.prod_id AND order_num = 20005 ; #14 The customer list of the ordered product TNT2 is changed to a junction query # 14 Sub-query SELECT cust_name, cust_contact FROM customers The WHERE the cust_id the IN ( the SELECT the cust_id the FROM Orders the WHERE order_num the IN ( the SELECT order_num the FROM OrderItems the WHERE the prod_id = 'TNT2' )); # Multi-table join query SELECT cust_name, cust_contact FROM customers, orders, orderitems WHERE customers.cust_id = orders.cust_id AND orderitems.order_num = orders.order_num AND prod_id = 'TNT2' ; copy code

16 Advanced Connection

Use connection and connection conditions:

  • Pay attention to the type of connection used. Generally we use internal connections, but it is also effective to use external connections.
  • Ensure that the correct connection type is used, otherwise incorrect data will be returned
  • Connection conditions should always be provided, otherwise Cartesian products will appear
  • A connection can contain multiple tables, and even different connection types can be used for each connection. But do a good job of testing to facilitate investigation and writing
# Column alias select CONCAT ( Rtrim (vend_name), '(' , Rtrim (vend_country), ')' ) as vend_title from vendors order by vend_name; # Table alias: not only applies to where clauses , select lists, order by clauses and statements Other parts of SELECT cust_name, cust_contact FROM customers as c, orders as o, orderitems as oi WHERE c.cust_id = o.cust_id AND oi.order_num = o.order_num AND prod_id = 'TNT2' ; # Query supplier other products that produce DTNTR # Self-connection select prod_id,prod_name from products where vend_id = ( select vend_id from products where prod_id = 'DTNTR' ); select p1.prod_id,p1.prod_name from products as p1,products as p2 where p1.vend_id = p2.vend_id and p2.prod_id = 'DTNTR' ; # Natural connection: wildcards are only used for the first table, all other columns are clearly listed, so no duplicate columns are retrieved SELECT c.*, o.order_num,o.order_date, oi.prod_id,oi.quantity,oi.item_price FROM customers as c, orders as o, orderitems as oi WHERE c.cust_id = o.cust_id AND oi.order_num = o.order_num AND prod_id = 'FB' ; # Inner join: retrieve all customers and orders, no null value will be generated, because which table is not the main table select customers.cust_id, orders.order_num from customers inner join orders on customers.cust_id = orders.cust_id; select customers .cust_id ,orders.order_num from customers left join orders on customers.cust_id = orders.cust_id; # External join: Including rows that are not associated are also displayed, so they must be matched with left or right select customers.cust_id ,orders.order_num from customers right outer join orders on customers.cust_id = orders.cust_id order by customers.cust_id; select customers. cust_id ,orders.order_num from customers left outer join orders on customers.cust_id = orders.cust_id order by customers.cust_id; # Retrieve all customers and the number of orders placed by each customer # Use aggregate function internal connection SELECT customers.cust_name, customers.cust_id, count (orders.order_num) AS num_ord FROM customers INNER JOIN orders ON customers.cust_id = orders.cust_id GROUP BY customers.cust_id; # Use aggregate function external join SELECT customers.cust_name, customers.cust_id, count (orders.order_num) AS num_ord FROM customers left outer join orders ON customers.cust_id = orders.cust_id GROUP BY customers.cust_id; Copy code

17 Combination query

Union rules:

  • The union must consist of 2 or more select statements, separated by the keyword union
  • Each query in the union must contain the same columns, expressions, or aggregate functions (but the columns do not need to be listed in the same order)
  • Column data types must be compatible: the types need not be exactly the same, but must be a type that can be implicitly converted by the DBMS
# union: multiple queries can be aggregated into the same result set select vend_id, prod_id, prod_price from products where prod_price <= 5 union select vend_id, prod_id, prod_price from products where vend_id in ( 1001 , 1002 ); # union and where more The query conditions are the same select vend_id, prod_id, prod_price from products where prod_price <= 5 or vend_id in ( 1001 , 1002 ); # union will cancel duplicate rows by default, use union all to return all rows select vend_id,prod_id,prod_price from products where prod_price <= 5 union all select vend_id,prod_id,prod_price from products where vend_id in ( 1001 , 1002 ); # The sorting of union uses order by, and it is used for all tables, because union is combined and must use all tables select vend_id,prod_id,prod_price from products where prod_price <= 5 union select vend_id,prod_id,prod_price from products where vend_id in ( 1001 , 1002 ) order by vend_id,prod_price; copy code

18 Full text search

Full-text boolean operatorsDescription
+Contains, word must exist
-Exclude, word must not exist
>Include, but increase the level value
<Include, lower the level value
()Group words into sub-expressions, allowing these sub-expressions to be included, sorted, arranged, etc. as a group
~Cancel the rank value of a word
*Suffix wildcard
""Define a phrase (unlike a list of single words, it matches the entire phrase in order to include or exclude the phrase)

Instructions for full text search:

  • When searching for full-text data, short words are ignored and excluded from the index. Short words are defined as those words with 3 or less characters
  • Mysql comes with a built-in list of stopwords, which are always ignored when indexing full-text data.
  • Many words appear frequently, and searching for them is useless (returns too many results). Therefore, MySQL stipulates a 50% rule. If it appears in more than 50% of the rows at a time, it will be regarded as a non-use word ignore. The 50% rule is not used in in boolean mode
  • If the number of rows in the table is less than 3 rows, the full text search does not return results (because each time either does not appear, or at least appears in 50% of the rows)
  • Ignore single quotes in words.
  • Languages that do not have word separators cannot properly return full-text search results
  • Only support full text search in MyISAM database engine
# Full-text search only supports MyISAM engine select note_text from productnotes where match (note_text) against( 'rabbit' ); # Although like fuzzy query can also be implemented, the sorting is random, full-text matching can be sorted according to the degree of text matching select note_text from productnotes where note_text like '%rabbit%' ; # Query expansion: not only the full text match except the text you want, but also the lines that may be related to it select note_text from productnotes where match (note_text) against( 'anvils' ); select note_text from productnotes where match (note_text) against( 'anvils' with query expansion); # Boolean Operator# Boolean operator is not used select note_text from productnotes where match (note_text) against( 'heavy' in boolean mode ); # Use Boolean operator: -exclude , *truncate (= wildcard) select note_text from productnotes where match (note_text) against( 'heavy -rope*' in boolean mode ); # +Must include: description also includes rabbit bait select note_text from productnotes where match (note_text) against( '+rabbit +bait' in boolean mode); # No +, indicating that at least one of the rabbit bait will work select note_text from productnotes where match (note_text) against( 'rabbit bait' in boolean mode ); # "rabbit bait" is the matching phrase rabbit bait select note_text from productnotes where match (note_text) against( '"rabbit bait"' in boolean mode ); # >: increase the level value, <: decrease the level value select note_text from productnotes where match (note_text) against( '>rabbit <bait"' in boolean mode ); # (); Group words into sub-expressions, allowing these sub-expressions to be included, excluded, arranged, etc. as a group select note_text from productnotes where match (note_text) against( '+safe +(<combination)"' in boolean mode ); Copy code

19 Insert data

# Insert an entire row unsafely, because the order of the table columns may change insert into customers values ( null , 'Pep E. LaPew' , '100 Main Street' , 'Los Angeles' , 'CA' , '90046' , ' USA' , null , null ); # Safely insert a whole line: insert into a specified list # Good practice: always use the name of the list to operate insert into customers ( cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email ) values ( 'Pep E. LaPew' , '100 Main Street' , 'Los Angeles' , 'CA' , '90046' , 'USA' , null , null ); Copy code
# A single piece of data is gradually inserted, and the writing is cumbersome. The advantage is that the performance is faster than inserting multiple pieces of data at one time. insert into customers ( cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country ) values ( 'Pep E. LaPew' , '100 Main Street' , 'Los Angeles' , 'CA' , '90046' , 'USA' ); insert into customers ( cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country ) values ( 'M. Martin' , '100 Main Street' , 'Los Angeles' , 'CA' , '90046' , 'USA' ); # Write multiple pieces of data at one time, the advantage is that it is simple to write, but the disadvantage is that the performance is not as fast as a single piece of data is inserted row by row. insert into customers ( cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country ) values ( 'Pep E. LaPew' , '100 Main Street' , 'Los Angeles' , 'CA' , '90046' , 'USA' ),( 'M. Martin' , '100 Main Street' , 'Los Angeles' , 'CA' , '90046' , 'USA' ); Copy code
# Create custnew table to simulate insert select operation CREATE TABLE custnew ( cust_id int NOT NULL AUTO_INCREMENT, cust_name char ( 50 ) NOT NULL , cust_address char ( 50 ) NULL , cust_city char ( 50 ) NULL , cust_state char ( 5 ) NULL , cust_zip char ( 10 ) NULL , cust_country char ( 50 ) NULL , cust_contact char ( 50 ) NULL , cust_email char ( 255 ) NULL , PRIMARY KEY (cust_id) ) ENGINE = InnoDB ; # Copy a row of data in the customers table to custnew, pay attention to modify the primary key cust_id to the primary key value that does not appear in customers INSERT INTO `learn_mysql` . `custnew` ( `cust_id` , `cust_name` , `cust_address` , `cust_city` , `cust_state` , `cust_zip` , `cust_country` , `cust_contact` , `cust_email` ) VALUES ( 20001 , 'Coyote Inc.' , '200 Maple Lane' , 'Detroit' , 'MI' , '44444' , ' USA' , 'Y Lee' ,'ylee@coyote.com' ); # insert select operation, insert the result of a query statement into another table, note that all query data is inserted into insert into the Customers ( cust_id, cust_contact, cust_email, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country ) select cust_id, cust_contact, cust_email, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country from custnew; copy code

20 Update and delete data

Guidelines for updates and deletions

  • Unless you really intend to delete, update and delete every row, never use update and delete statements without the where clause
  • Ensure that each table has a primary key, use it like a where clause as much as possible
  • When using where for update or delete statements, it is best to use select to ensure the correctness of where filtering
  • Using a database that enforces referential integrity, mysql will not allow you to delete rows of data associated with other tables
# The update statement must be used with where, otherwise the data of the entire column will be updated UPDATE customers SET cust_email ='elmer@fudd.com' WHERE cust_id = 10005 ; # Update multiple columns UPDATE customers SET cust_name = 'The Fudds' ,cust_email ='elmer@fudd.com' WHERE cust_id = 10005 ; # delete must also be used with where, otherwise it will also delete all data # If you want to delete all rows in the table, use delete; delete the original table and create one again, use truncate table delete from customers where cust_id = 10006 ; copy the code

21 Create and manipulate database tables

Use auto_increment:

  • Only one auto_increment column is allowed per table, and its tourmaline is indexed (for example, let it be the primary key)
  • select last_insert_id()
    Query the value of the last auto_increment

Engine type:

  • InnoDB is a reliable transaction processing engine, but does not support full-text search
  • Memery is functionally equivalent to MyISAM, but because the data is stored in memory, the speed is very fast (applicable to temporary tables)
  • MyISAM is a very high-performance engine, it supports full-text search, but does not support transactions
  • Foreign keys cannot be used across engines, so it is particularly important to define tables
# Create table: create table table CREATE TABLE customers ( # AUTO_INCREMENT Each table can only have one, and must be indexed cust_id int NOT NULL AUTO_INCREMENT, cust_name char ( 50 ) NOT NULL , cust_address char ( 50 ) NULL , cust_city char ( 50 ) NULL , cust_state char ( 5 ) NULL , cust_zip char ( 10 ) NULL , cust_country char ( 50 ) NULL , cust_contact char ( 50 ) NULL , cust_email char ( 255 ) NULL , PRIMARY KEY (cust_id) ) ENGINE = InnoDB ; # select last_insert_id() Query the value of the last auto_increment select last_insert_id (); CREATE TABLE orderitems ( order_num int NOT NULL , order_item int NOT NULL , prod_id char ( 10 ) NOT NULL , quantity int NOT NULL default 1 , # If not specified, the default is 1 item_price decimal ( 8 , 2 ) NOT NULL , PRIMARY KEY (order_num, order_item) # two primary keys ) ENGINE = InnoDB ; CREATE TABLE productnotes ( note_id int NOT NULL AUTO_INCREMENT, prod_id char ( 10 ) NOT NULL , note_date datetime NOT NULL , note_text text NULL , PRIMARY KEY (note_id), FULLTEXT(note_text) ) ENGINE = MyISAM; # MyISAM engines designated copy the code
# Update table alter table xx # Add a column of alter table vendors add vend_phone char ( 20 ); # Delete a column of alter table drop column vend_phone; # Alter create foreign keys the ALTER TABLE OrderItems the ADD CONSTRAINT fk_orderitems_orders a FOREIGN KEY (order_num) the REFERENCES the Orders (order_num); the ALTER TABLE OrderItems the ADD CONSTRAINT fk_orderitems_products a FOREIGN KEY (prod_id) the REFERENCES Products's (prod_id); the ALTER TABLE the Orders the ADD CONSTRAINT fk_orders_customers a FOREIGN KEY (cust_id) REFERENCES customers (cust_id); ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id); # Delete the entire table drop table custnew; # Rename a table the rename Table the Customers to Customers1; the rename Table Customers1 to the Customers; duplicated code

22 Using the view

view

  • It is a virtual table, a mysql encapsulation of the select statement, which can be used to simplify data processing and reformat basic data or protect basic data

  • The view itself does not contain data, so the data they return is retrieved from other tables. When adding and changing the data in these tables, the view will return the changed data

  • Crate view to create a view, show craete view viewname to view the created view

  • drop view delete view

  • When updating the view, you can absorb the drop and then crate, or create or replace the view

  • If there are the following operations in the view definition, the view cannot be updated and you need to manually delete the view and create it again. Generally, the view is only used for query!

    • Grouping (group by and having)
    • coupling
    • Subquery
    • and
    • Aggregate function
# Chapter 15 of the multi-table join query, you can use the view to encapsulate it SELECT cust_name, cust_contact FROM customers, orders, orderitems WHERE customers.cust_id = orders.cust_id AND orderitems.order_num = orders.order_num AND prod_id = 'TNT2' ; # Create a view, you can execute the original complex sql like simple sql CREATE VIEW productcustomers AS SELECT cust_name, cust_contact, prod_id #Need to add the column name FROM that needs to be queried later customers, orders, orderitems WHERE customers.cust_id = orders.cust_id AND orderitems.order_num = orders.order_num; select cust_name, cust_contact from productcustomers where prod_id = 'TNT2' ; # Create a view to facilitate reformatting of the previous data create view vendorlocations as SELECT CONCAT ( Rtrim (vend_name ), '(' , Rtrim (vend_country ), ')' ) AS vend_title FROM vendors ORDER BY vend_name; select * from vendorlocations; # Create a view to filter unwanted data create view customeremaillist as select cust_id,cust_name,cust_email from customers where cust_email is not null ; select * from customeremaillist; # Create a view, save the calculation results, easy to use where create view orderitemsexpanded as SELECT order_num, prod_id, quantity, item_price, quantity * item_price AS expanded_price FROM orderitems; select * from orderitemsexpanded where order_num = 20005 ; Copy code

23 Using stored procedures

# Create a stored procedure and use it, pay attention to the use of comma and semicolon create procedure productpricing() begin select avg (prod_price) from products; end ; # Use a stored procedure call productpricing(); # Delete the stored procedure, no need () drop procedure productpricing; # Functional stored procedure: mysql only supports three types of function parameters in, out, and inout # 3.out create procedure productpricing( out pl decimal ( 8 , 2 ), out ph decimal ( 8 , 2 ), out pa decimal ( 8 , 2 ) ) begin select min (prod_price) into pl from products; select max (prod_price) into ph from products; select avg (prod_price) into pa from products; end ; # mysql variables all start with @, which is a piece of temporarily stored data call productpricing( @pricelow, @pricehigh, @pricehigh ); select @pricelow,@pricehigh,@pricehigh # in and out use create procedure ordertotal( in onumber int , out ototal decimal ( 8 , 2 ) ) begin select sum (item_price * quantity) from orderitems where order_num = onumber into ototal; end ; # Query the total price of order 20005 call ordertotal( 20005 ,@totoal); select @totoal; copy the code

Establish an intelligent storage process: need to add sales tax to the total order

  • Get the total (same as before)
  • Conditionally add business tax to the total
  • Return total
create procedure ordertotal( in onumber int , in taxable boolean , out ototal decimal ( 8 , 2 ) ) comment'Selectively calculate the total order price with/without tax' begin # Define the temporary variable total declare total decimal ( 8 , 2 ); # The tax rate is 6 points declare taxrate int default 6 ; # Query the order when there is no tax the total price of the SELECT SUM (the Quantity * ITEM_PRICE) from OrderItems the WHERE order_num = onumber INTO Total; # whether there is a tax if taxable then select total+(total/100 * taxrate) into total; end if ; # The temporary variable result is returned to the function return parameter select total into ototal; end ; call ordertotal( 20005 , 0 ,@total); select @total; call ordertotal( 20005 , 1 ,@total); select @total; copy code
# View detailed information such as stored procedures show procedure status ; copy the code

24 Using the cursor

Use cursor:

  • Before the cursor can be used, it must be declared. This process does not actually retrieve data, it just defines the select statement to be used
  • Once declared, the cursor must be opened for use. This process uses the previously defined select statement to actually retrieve the cursor
  • For the cursor filled with data, take out each row as needed
  • At the end of the use of the cursor, the cursor must be closed
create procedure processorders( out outnumber int ) begin # Temporary variable declare o int ; # Create cursor declare ordernumbers cursor for select order_num from orders; # Open cursor open ordernumbers; # Use cursor fetch ordernumbers into o; # Return data to function parameter select o into outnumber; # Close the cursor close ordernumbers; end ; call processorders(@outnumber); select @outnumber; copy the code

25 trigger

After mysql5 does not support triggers to return results, manually add a variable @variable name by yourself, return the results to see the trigger results; triggers are only useful for insert, delete, and update

# mysql5 does not support triggers to return results in the future, add a variable @variable name, and return the result # For the first use triggers: each time you add a line to the products, return the "Product added" the Create the Trigger the NewProduct the After INSERT ON products for the each Row the SELECT 'Product added' INTO @ee; insert into products(prod_id,vend_id,prod_name,prod_price,prod_desc) VALUES ( "ANV011" , "1001" , "3 ton anvil" , 2.33 , "good" ); select @ee; Copy code

insert trigger:

  • In the insert trigger code, you can use a virtual table named new to access the inserted row
  • In the before insert trigger, the value in new can also be updated (allowing to change the inserted value)
  • For the auto_increment column, new contains 0 before insert is executed, and contains the new automatically generated value after insert
# insert trigger: auto_increment column, new contains 0 before insert is executed, and automatically generated value after insert is executed create trigger neworder after insert on orders for each row select NEW.order_num into @neworderRes; insert into orders(order_date,cust_id) values ( Now (), 10001 ); select @neworderRes; drop trigger neworder; copy code

delete trigger:

  • In the delete trigger code, you can refer to a virtual table named old to access the deleted row
  • The values in old are all read-only and cannot be updated
# delete trigger: before any order is deleted, old will save the result back to the achive_orders table create trigger deleteorder before delete on orders for each row begin insert into achive_orders(order_num,order_date,cust_id) values (old.order_num,old.order_date) ,old.cust_id); end ; copy the code

update trigger:

  • In the update trigger code, you can refer to a virtual table named old before accessing (update statement value), refer to a virtual table named new to access the newly updated value
  • In the before update trigger, the value in new may also be updated (allowing to change the value that will be used in the update statement)
  • The values in old are all read-only and cannot be updated
# Update triggers: new and old can be used Create Trigger updatevender before Update ON vendors from each Row SET new.vend_state = Upper (new.vend_state) INTO @updatevenderRes; duplicated code

26 Management Transaction Processing

Transaction (transaction): refers to a set of sql statements

Rollback (rollback): refers to the process of revoking the specified SQL

Submit (commit): the value of the unstored sql statement results are written to the database table

Savepoint: refers to the temporary placeholder in the transaction processing settings (place-holder, you can issue a rollback to it)

# Start transaction: learn to roll back start TRANSACTION ; select * from orderitems; delete from orderitems; select * from orderitems; # Roll back the transaction, all statements after TRANSACTION do not execute ROLLBACK ; select * from orderitems; # Open transaction: learn commit start transaction ; the Delete from OrderItems the WHERE order_num = 20010 ; the Delete from the Orders the WHERE order_num = 20010 ; in the # transaction block, commit to a section of sql, the premise is not being given the commit ; # Start the transaction: set the retention point start TRANSACTION ; # Set the retention point of a transaction () savepoint deletel; # If there is a problem, go back to the set retention point rollback to deletel; # Set mysql not to automatically submit set autocommit = 0 ; set autocommit = 1 ; copy the code

27 Globalization and localization

# Display all available character sets and their default school team show character set ; # Display all available school teams and their default character set show collation ; # Display all character sets and the school team name Show the Variables like 'Character%' ; Show the Variables like 'collation%' ; # Create table specified character set and school team method create table mytable( column1 int , column2 varchar ( 10 ) ) engine = Innodb default character set hebrew collate hebrew_general_ci; # Column specified character set and school team method create table mytable1( column1 int , column2 varchar ( 10 ), column3 varchar ( 100 ) character set latin1 collate latin1_general_ci ) engine = Innodb default character set hebrew collate hebrew_general_ci; When # query order by designated school team the way the SELECT * from the Customers the Order by CUST_NAME COLLATE utf8_general_ci; Copy the code

28 Security Management

# mysql user permissions are placed in the user table (the default one) in the mysql library (the default library) use mysql; select * from user ; # Create a database user: ben # account: ben # Password: password the Create the User ben IDENTIFIED by 'password' ; # but users created in this way have no authority inquiries will know the SELECT * from the User ; # rename the database user name rename user ben to ben1; rename user ben1 to ben; # View the permissions of the ben user show grants for ben; # ben learn_mysql grant select on learn_mysql.* to ben; # ben revoke select on learn_mysql.* to ben; # set password for ben = PASSWORD('password1'); # set password = PASSWORD('password1');

29

# analyze: analyze table orders; # check: check table orders,orderitems;

30

  • show variables;show status;

  • show processlist (very useful to view ip on the server)

  • explain to view the select statement performance, and then optimize

  • Never retrieve more data than required, that is, select *reduce use

  • Select or use too much when retrieving data, you can use union to splice multiple select statements, the performance is much better

  • like is very slow, it is better to use fulltext instead of like