0 Database Scripts

Mysql Must Know must Know Reading Notes
This book database script: mysql must know will be the official website

# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
# 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 FOREIGN KEY (order_num) REFERENCES orders (order_num);
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);
ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);
Copy the 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'.'[email protected]');
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'.'[email protected]');
INSERT INTO customers(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'.'[email protected]');
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 INTO orders(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 INTO orderitems(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 the code

1 know SQL

Database: A container that holds organized data (usually a file or group of files)

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

Table: A structured list of data of a particular type

Schema: Information about the layout and features of databases and tables

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

Row: A record in a table

Primary key: Each row in the table should have a column that uniquely identifies itself and whose value uniquely distinguishes each row in the table. Without a primary key, updating or deleting a particular row in a table is difficult because there is no secure way to ensure that only the relevant rows are involved.

Mysql > alter table primary key;

No two rows have the same primary key value
Each row must have a primary key value, which cannot be null

2 the mysql profile

A brief introduction to Mysql, you can query the relevant information

3 use MySQL

Create database learn_mysql, connect to database
use learn_mysql;

Display a table in a database
show databases;

Describe table_name equivalent
show columns from customers;
# equivalent to the above
describe customers;
Copy the code

4 Retrieving Data

SQL statements return raw, unformatted data
select prod_name from products;

# deretrieve
select vend_id from products;
select distinct vend_id from products;

# database rows start at 0; RowIndex = (number of front pages -1)* Number of pages displayed per page
select prod_name  from products limit 0.1;
Copy the code

5 Sort and retrieve data

Order by: must come after from, if combined with limit, limit must come after order by

Retrieving data usually requires sorting to make sense. The default is ascending order
select prod_name from products order by prod_name;

If the first column is the same, the second one will be sorted. If the first one is different, the last one will never be sorted
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 one is in ascending order, the next one is in 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 the code

6 Filtering Data

The operator	instructions
=	Is equal to the
<>	Is not equal to
! =	Is not equal to
<	Less than
< =	Less than or equal to
>	Is greater than
> =	Greater than or equal to
between	Between 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';

Two ways to write theta not equal to theta
select vend_id,prod_name from products where vend_id <> 1003;
select vend_id,prod_name from products wherevend_id ! =1003;

# between: query within range
select prod_name,prod_price from products where prod_price between 5 and 10;

Is null
select cust_id from customers where cust_email is null;
Copy the code

7 Data Filtering

Advantages of the IN operator:

The syntax is clearer and more intuitive
The order of calculations is easier to manage
In computes faster than OR
The biggest advantage of in is that it can contain other SELECT statements

# where+and: match both
select prod_id,prod_price,prod_name from products where vend_id = 1003 and prod_price <=10;

# where+or: match any of them
select prod_name,prod_price from products where vend_id = 1002 or vend_id =1003;

Vend_id =1003 and prod_price >=10
select prod_name,prod_price from products where vend_id=1002 or vend_id=1003 and prod_price >=10;
If the # or sentence needs to be executed first, parentheses are required
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 in, between, exists sentences
select  prod_name,prod_price from products where vend_id not in (1002.1003) order by prod_name ;
Copy the code

8. Using wildcard techniques

Note trailing whitespace: %anvil does not match the last one with a single space or more Spaces. The solution is to remove leading and trailing whitespace in %anvil% or in chapter 11

Note Null: Although % can match anything, it cannot match Null values

# wildcard % : The default is case insensitive
select prod_id,prod_name from products where prod_name like 'Jet%';
select prod_id,prod_name from products where prod_name like 'jet%';

# wildcard % before and after:
select prod_id,prod_name from products  where prod_name like '%anvil%';

# wildcard _: matches a single character.5 ton anvil does not match a single character
select prod_id,prod_name from products  where prod_name like '_ ton anvil';
select prod_id,prod_name from products  where prod_name like '%ton anvil';
Copy the code

9 Search using regular expressions

Blank metacharacter	instructions
\\f	Change the page
\\n	A newline
\\r	enter
\\t	TAB
\\v	Vertical TAB

Character classes	instructions
[:alnum:]	Any letters or numbers (same as [A-zA-Z0-9])
[:alpha:]	Any character (same as [a-za-z])
[:blank:]	Spaces and tabs (same as [\\t])
[:cntrl:]	ASCII control characters (ASCII0 through 32 and 127)
[:digit:]	Any number (same as [0-9])
[:graph:]	Same as [:print:], but without Spaces
[:lower:]	Any lowercase letter (same as [a-z])
[:prinit:]	Any printable character
[:punct:]	Any character that is neither in [:alnum:] nor [: CNTRL :]
[:space:]	Any whitespace character including space (same as [\\f\\n\\r\ t\ v])
[:upper:]	Arbitrary uppercase characters (same as [a-z])
[:xdigit:]	Any hexadecimal number (same as [A-FA-f0-9])

Repeated metacharacter	instructions
*	Zero or more matches
+	1 or more matches (equal to {1,})
?	0 or 1 match (equal to {0,1})
{n}	Specifies the number of matches
{n,}	Not less than a specified number of matches
{n,m}	Range of matches (m does not exceed 255)

Positioning metacharacter	instructions
^	The beginning of a text
$	End of text
[[: < :]]	The beginning of the term
[[: > :]]	At the end of the term

# regexp: indicates the use of 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';

# 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 is not 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 is returned

# regexp: The default is not case sensitive. If case sensitive is required, regexp binary is used
select prod_name from products where prod_name regexp binary 'JetPack .000';

# the regexp + | : similar to the or function
select prod_name from products where prod_name regexp '1000 | 2000' order by prod_name;

# []: matches a specific character
# [123] ton = | 2 | 3 [1] in strict accordance with the ton
select prod_name from products where prod_name regexp '[123] ton' order by prod_name;
# ^ : take the inverse
select prod_name from products where prod_name regexp '[^123] ton' order by prod_name;
# 1 | 2 | 3 ton: column contains 1 or 2 or 3 ton and 123 ton [] separate meanings
select prod_name from products where prod_name regexp '1|2|3 ton' order by prod_name;
# [1-5] : indicates a range
select prod_name from products where prod_name regexp '[1-5] ton' order by prod_name;

#. Matches any character,
select vend_name from vendors where vend_name regexp '. ' order by vend_name;
# match special characters followed by //, //. character
select vend_name from vendors where vend_name regexp '\ \. order by vend_name;

# Match (number stick) or (number 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;

The # match contains four digits
select prod_name from products where prod_name regexp '[:digit:]{4}' order by prod_name;

# localizer: ^ In regular expression representation from the beginning of the text
Matches all products starting with a number or decimal point
select prod_name from products where prod_name regexp '^ [0-9 \ \]' order by prod_name;
Copy the code

10 Create a calculation field

Field: Basically the same as a column, but the column name is the name of the column actually stored in the database, and the field is usually used to compute the join of the field

Concatenate: Concatenate values together to form a single value

Mysql concatenates mysql concatenates mysql concatenates mysql concatenates mysql
select CONCAT(vend_name,'(',vend_country,') ')  from vendors order by vend_name;
# Rtrim: Remove the Spaces 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),') ')  as vend_title from vendors order by vend_name;

Mysql supports +,-,*,/
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 can omit the from clause for easy access and processing of data
select 3*2;
select trim(' abc ');
select Now(a);Return the current date and time
Copy the code

11 Use data processing functions

Commonly used text processing functions	instructions
Left()	Returns the character to the left of the string
Length()	Returns the length of the string
Locate()	Find a substring of a string
Lower()	Converts the string to lowercase
LTrim()	Remove the left margin of the string
Right()	Returns the character to the right of the string
Rtrim()	Remove the Spaces to the right of the string
Soundex()	Returns the SoundDex value of the string
SubString()	Returns the character of a substring
Upper	Converts the string to uppercase

Soundex () : An algorithm that converts any string of text into an alphanumeric pattern describing its linguistic representation, as shown below

# SOUNDEX(): Matches similar-sounding text
select cust_name,cust_contact from customers where cust_contact = 'Y. Lie';
select cust_name,cust_contact from customers where soundex(cust_contact) = soundex('Y. Lie');
Copy the code

Common date and time handlers	instructions
AddDate()	Add a date (day, week, etc.)
AddTime()	Add a date (hour, grade, etc.)
CurDate()	Return current date
CurTime()	Return current time
Date()	Returns the date portion of the date time
DateDiff()	Calculate the difference between the two dates
Date_Add()	Highly flexible date manipulation function
Date_Format()	Returns a formatted date or time string
Day()	Returns the number of days of a date
DayOfWeek()	For a date, return the day of the week
Hour()	Return hour
Minute()	Return to minutes
Month()	Return to month
Now()	Returns the current date and time
Second()	Return second part
Time()	Return time part
Year()	Return year part

Commonly used numeric manipulation functions	instructions
Abs()	The absolute value
Cos()	cosine
Exp()	Index value
Mod()	The remainder of the division operation
Pi()	PI
Rand()	The random number
Sin()	sine
Sqart()	The 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
Date = 2005-09-01 00:00:00 = 2005-09-01 00:00:00 = 2005-09-01 00:00:00
# But I can't find out the desired result
select cust_id,order_num from orders where order_date = '2005-09-01';
# Use the 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: You 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: Do not need to know the specific days, the most convenient
select cust_id,order_num from orders where Year(order_date) = '2005' and Month(order_date)='9';
Copy the code

12 Summary Data

Aggregation function	instructions
AVG()	Average value, ignoring null value
COUNT()	The number of rows
MAX()	The maximum
MIN()	The minimum value
SUM()	The sum of the

# AVG: Calculate the average
select avg(prod_price) as avg_price from products;
Calculate the average of a particular column
select avg(prod_price) as avg_price from products where vend_id=1003;
Calculate the average value of a particular column after de-repetitions
select avg( distinct prod_price) as avg_price from products where vend_id=1003;

# count(*) and (1): count lines
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;
# Sum after math operation
select sum(item_price*quantity) as total_price from orderitems where order_num =20005;

# combine aggregate functions
select 
	count(*) as num_items,
	min(prod_price) as min_price,
	max(prod_price) as max_price,
	avg(prod_price) as avg_price
from products;
Copy the code

13 Grouped Data

Considerations for using Group by:

The group by sentence can contain any number of columns. This enables grouping to be nested and data grouping to provide finer control
If groups are nested in the group by clause, the data is summarized at the last specified group. In other words, when a group is created, all columns specified are evaluated together
Each column listed in the group by sentence must be a retrieval column or a valid expression (but not an aggregation function). If you use an expression in select, you must specify the same expression in the group by clause
Every column in a SELECT statement must be given in the group by clause, except for the aggregate calculation statement
If there is a NULL value in the grouping column, null is returned as a grouping. If there are multiple rows of NULL values in a column, they are grouped together
Group by must appear after WHERE and before ORDER by

Having vs. where:

Where is filtered before grouping and HAVING after grouping

order by	group by
The output produced by sorting	Grouping. But the output may not be the order of the grouping
Any column can be used	Only select or expression columns can be used, and each select column expression must be used
Not necessarily	Must be used if columns (or expressions) are used with aggregate functions

Sentence execution order	instructions	Is it mandatory to use
select	The column or expression to return	is
from	A table from which data is retrieved	Used only when selecting data from a table
where	Row-level filter	no
group by	Group show	Use only when calculating focus by group
having	Group level filter	no
order by	Output sort order	no
limit	Number of rows to retrieve	no

What if I want to return the number of products provided by each vendor?
select count(1) from products  where vend_id =1003;

# group by: Groups the array, aggregating each group instead of each result set
# Using group by output may not be the original sorting of groups, so it is a good habit 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: Total totals for each group (level)
select vend_id,count(*) as num_prods from products group by vend_id with rollup order by vend_id ;  

# gruop by +having
select cust_id,count(*) as orders from orders group by cust_id having count(*) > =2;

Where filters the data and then groups it
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; 

Select * from group by + order by; select * from 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 the code

14 Use subquery

The most common use of subqueries is + in the WHERE clause’s IN operator and to populate computed columns.

# List all customers who ordered item TNT2
# 1. Retrieve all order numbers containing item TNT2
# 2. Retrieve all customer ids with the order numbers listed in the previous step
# 3. Retrieve customer information for all customer ids returned in the previous step
SELECT
	cust_name,
	cust_contact 
FROM
	customers 
WHERE
	cust_id IN ( SELECT cust_id FROM orders WHERE order_num IN ( SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'));The total number of orders for each customer in the # Customers table, and the orders and corresponding customer ids are stored in the Orders table
The subquery executes once for each customer retrieved. The subquery executes five times, so five customers retrieved
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 the code

15 Using Connections

Cartesian product:

The structure returned by table relationships without join conditions 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 collocation, otherwise there will be a lot of false data

Select * from table_name
select vend_name,prod_name,prod_price from vendors,products where vendors.vend_id = products.vend_id order by vend_name,prod_name;
SQL > select * from 'where'
select vend_name,prod_name,prod_price from vendors,products order by vend_name,prod_name;
SQL > select * from inner join
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; 
	
The customer list for ordering product TNT2 in Chapter # 14 was changed to join query
Chapter 14 subquery
SELECT
	cust_name,
	cust_contact 
FROM
	customers 
WHERE
	cust_id IN ( SELECT cust_id FROM orders WHERE order_num IN ( SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'));Select * from table_name
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 the code

16 Advanced Connection

Use joins and join conditions:

Note the type of join used. We usually use inner joins, but it is also valid to use outer joins.
Ensure that the correct join type is used, otherwise incorrect data will be returned
Join conditions should always be provided, otherwise cartesian products occur
You can have multiple tables in a join, and you can even use different join types for each join. But do a good test, easy to check and write

# column alias
select CONCAT(Rtrim(vend_name),'('.Rtrim(vend_country),') ')  as vend_title from vendors order by vend_name;
Table aliases: not only apply to WHERE clauses, SELECT lists, ORDER by clauses, and other parts of statements
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 DTNTR supplier for other products
# the coupling
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 join: Wildcards are only used for the first table, all other columns are explicitly 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 is generated, because there is no primary 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 unassociated rows are also displayed, so it must be 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 the inner join of the aggregate function
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 external joins of aggregate functions
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 the code

17 Combination Query

The union rules:

A union must consist of two or more SELECT statements separated by the keyword union
Each query in the union must contain the same column, expression, or aggregate function (although the columns need not be listed in the same order)
The column data types must be compatible: the types do not have to be identical, but they must be types that the DBMS can implicitly convert

# union: Multiple queries can be pooled 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 is the same as where multi-condition query
select vend_id,prod_id,prod_price from products where prod_price <=5  or vend_id in(1001.1002);# union cancels duplicate lines by default. Use union all to return all lines
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 sort of union uses order by, and works with all tables, because the union 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 the code

18 Full-text search

Full-text Boolean operator	instructions
+	Contain, the word must exist
–	To exclude, the word must not exist
>	Contains, but increases the level value
<	Contains, lower the level value
(a)	Grouping words into subexpressions, allowing these subexpressions to be included, sorted, arranged, etc., as a group
~	Cancels the rank value of a word
*	Terminating wildcard
“”	Define a phrase (unlike a list of individual words, which matches the entire phrase to include or exclude it)

Full text search instructions:

When searching full-text data, short words are ignored and excluded from the index. Short words are defined as those with three or fewer characters
Mysql comes with a built-in list of non-stopwords that are always ignored when indexing full-text data.
Many words appear so frequently that it is not useful to search for them (returning too many results), so mysql has a 50% rule that ignores it as a non-word if it appears in more than 50% of the lines at one time. The 50% rule does not apply to in Boolean mode
If the number of rows in the table is less than 3, the full-text search returns no results (because each time either does not occur, or at least 50% of the rows appear)
Ignore single quotation marks in words.
Languages that do not have word delimiters cannot properly return full-text search results
Full-text search is supported only in the 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 be implemented, but the sort 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 extension: Not only match the text you want, but also search for lines that might 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
The Boolean operator is not used
select note_text from productnotes where match(note_text) against('heavy' in boolean mode);
# Use Boolean operators: -exclude, * truncate (= wildcard)
select note_text from productnotes where match(note_text) against('heavy -rope*' in boolean mode);
# + Must contain: specifies that rabbit bait is also included
select note_text from productnotes where match(note_text) against('+rabbit +bait' in boolean mode);
# No + means 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" matches the phrase rabbit bait
select note_text from productnotes where match(note_text) against('"rabbit bait"' in boolean mode);
# >: increases level value, <: decreases level value
select note_text from productnotes where match(note_text) against('>rabbit <bait"' in boolean mode);
# (); Grouping words into subexpression, allowing these subexpression 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 the code

19 Inserting Data

It is not safe to insert an entire row because the table column order may change
insert into customers 
values(
	null.'Pep E. LaPew'.'100 Main Street'.'Los Angeles'.'CA'.'90046'.'USA'.null.null
);

Safely insert an entire row: specifies the list of inserts
Good habit: Always use list names
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 the code

# Single data gradually inserted, tedious writing, the advantage is that the performance is faster than multiple data inserted at a 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 data at a time. The advantage is simple writing, but the disadvantage is not as fast as inserting single data line by line
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 the 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 the row from the CUSTOMERS table into CUSTNew, noting that primary key CUST_ID is changed to the primary key value not present 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'.'[email protected]');

Select * from table_name; select * from table_name
insert into 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 the code

20 Update and delete data

Update and delete guidelines

Never use UPDATE or DELETE statements without where statements unless you really intend to delete updates and delete every row
Make sure that each table has a primary key, and use it as much as possible as the WHERE clause
When using WHERE with UPDATE or DELETE statements, it is best to use SELECT first to ensure that where filtering is correct
With databases that enforce referential integrity, mysql will not allow the deletion of rows that have associations with other tables

The # update statement should be used with where, otherwise the entire column will be updated
UPDATE customers 
SET cust_email = '[email protected]' 
WHERE
	cust_id = 10005;

Update multiple columns
UPDATE customers 
SET cust_name = 'The Fudds',cust_email = '[email protected]' 
WHERE
	cust_id = 10005;
	
Select * from 'where' where 'delete'
If you want to delete all rows from the table, use delete. Delete the original table and create a new one, using TRUNCate TABLE
delete from customers where cust_id = 10006;
Copy the code

21 Create and operate database tables

Use the auto_increment:

Only one AUTO_increment column is allowed per table and its tourmaline is indexed (e.g., make it a primary key)
select last_insert_id()Query the last auto_increment value

Engine type:

InnoDB is a reliable transaction engine, but does not support full-text search
Memery is equivalent to MyISAM in function, but fast because the data is stored in memory (for temporary tables)
MyISAM is a very high performance engine that supports full-text search but does not support transactions
Foreign keys cannot be used across engines, so defining tables is especially important

Create table table
CREATE TABLE customers
(
	# AUTO_INCREMENT Each table can have only 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() to query the value of the last auto_increment
select last_insert_id(a);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.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;# specify engine as MyISAM
Copy the code

Alter table xx alter table xx
# add a column
alter table vendors add vend_phone char(20);

Drop a column
alter table drop column vend_phone;

# alter creates a foreign key
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);
ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);

Drop the entire table
drop table custnew;

Rename a table
rename table  customers to customers1;
rename table  customers1 to customers;
Copy the code

22 Using Views

view

A virtual table is a mysql wrapper around select statements that can be used to simplify data processing and reformat or protect basic data
The views themselves contain no data, so the data they return is retrieved from other tables. As you add and change data in these tables, the view returns the changed data
Crate view Creates a view, and show craete view viewname displays the created view
Drop View Deletes a view
When updating a view, you can absorb the DROP crate or create or replace View
If the following operations are performed in the view definition, the view cannot be updated. You need to manually delete the view and then create it. Generally, the view is only used for query.
- Group by and having
- coupling
- The subquery
- and
- Aggregation function

The multi-table join query from Chapter 15 can be encapsulated using views
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 that can execute complex SQL as well as simple SQL
CREATE VIEW productcustomers AS SELECT
	cust_name,
	cust_contact, 
	prod_id Add column names that need to be queried where later
FROM
	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 that makes it easy to reformat 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 to save the result for where to use
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 the code

23 Use stored procedures

Create and use the stored procedure. Note the use of a semicolon
create procedure productpricing()
begin
	select avg(prod_price) from products;
end;
# Use stored procedures
call productpricing();

# delete stored procedure without ()
drop procedure productpricing;

Mysql supports only three types of function parameters: in, out, inout
# 三个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 start with @ and are temporary data stores
call productpricing(
	@pricelow,
	@pricehigh,
	@pricehigh
);

select @pricelow,@pricehigh,@pricehigh

# in and out
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 total price of order 20005
call ordertotal(20005,@totoal);

select @totoal;
Copy the code

Build smart stored procedures: add business tax to order totals

Get totals (as before)
Add the sales tax conditionally to the total
Total return

create procedure ordertotal(
	in onumber int.in taxable boolean.out ototal decimal(8.2))comment'Optional calculation of 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 total order price without tax
	select sum(quantity * item_price) from orderitems where order_num = onumber into total; 
	# Whether there is a tax
	if taxable then 
		select total+(total/100 * taxrate) into total;
	end if;
	The result of a temporary variable is returned as an argument to the function
	select total into ototal;
	
end;

call ordertotal(20005.0,@total);
select @total;

call ordertotal(20005.1,@total);
select @total;
Copy the code

View details such as stored procedures
show procedure status;
Copy the code

24 Use a cursor

Using a cursor:

A cursor must be declared before it can be used. This procedure doesn’t actually retrieve the data, it just defines the SELECT statement to use
Once declared, the cursor must be opened for use. This procedure actually retrieves the cursor using the select statement defined earlier
For cursors filled with data, extract rows as needed
You must close the cursor when you end its use

 create procedure processorders(
   out outnumber int
 )
 begin
	# temporary variables
	declare o int;
	Create a cursor
	declare ordernumbers cursor
	for 
	select order_num from orders;
	# open the cursor
	open ordernumbers;
	Use a cursor
	fetch ordernumbers into o;
	
	Return data to function arguments
	select o into outnumber;
	
	Close the cursor
	close ordernumbers;
end;

call processorders(@outnumber);

select @outnumber;
Copy the code

25 the trigger

Mysql > alter table mysql5 alter table mysql5 alter table mysql5 alter table mysql5 alter table mysql5 alter table mysql5 alter table mysql5 alter table mysql5 alter table mysql5 Triggers are only useful for INSERTS, deletes, and updates

# mysql5 does not support the return of the trigger result, add a variable @variable name, return the result is ok

# first use of trigger: return "Product Added" each time a row is added to products
create trigger newproduct after insert on products 
for each row 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 the code

Insert trigger:

Within the INSERT trigger code, a virtual table named new can be used to access the inserted row
In the before INSERT trigger, values in new can also be updated (allowing changes to inserted values)
For auto_INCREMENT columns, new contains 0 before the insert and a new auto-generated value after the insert

# insert trigger: auto_increment column, new contains 0 before INSERT, new contains auto-generated value after insert
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 the code

Delete trigger:

Within the DELETE trigger code, you can reference a virtual table named old to access the deleted row
Values in old are all read-only and cannot be updated

# delete trigger: Before any order is deleted,old stores the result back into 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 (the UPDATE statement value) and a virtual table named new before accessing the newly updated value
In the before UPDATE trigger, the value in new may also be updated (allowing you to change the value to be used in the UPDATE statement)
Values in old are all read-only and cannot be updated

# update trigger: Both 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; 
Copy the code

Manage transaction processing

Transaction: A group of SQL statements

Rollback: Indicates the process of rolling back specified SQL

Commit: the value writes the result of an unstored SQL statement to a database table

Savepoint: a temporary placeholder in a transaction setting (place-holder, to which a rollback can be published)

# Start transaction: Learn rollback
start TRANSACTION;

	select * from orderitems;
	delete from orderitems;
	select * from orderitems;
	None of the statements after the TRANSACTION are executed
ROLLBACK;

select * from orderitems;

# Start transactions: Learn commit
start transaction;

	delete from orderitems where order_num = 20010;
	delete from orders where order_num = 20010;
Commit commits a block of SQL, provided no errors are reported
commit;

Set the retention point
start TRANSACTION;
	Set a transaction retention point ()
	savepoint deletel;
	If there is a problem, fall back to the set retention point
rollback to deletel;

Mysql does not commit automatically
set autocommit = 0;
set autocommit = 1;
Copy the code

Globalization and localization

Display all available character sets, along with their default school team
show character set;
# display all available school teams and their default character set
show collation;


# display all character sets and school team names
show variables like 'character%'; 
show variables like 'collation%';


Create table specify character set and school team mode
create table mytable(
	column1 int,
	column2 varchar(10))engine = Innodb default character set hebrew collate hebrew_general_ci; 

The # column specifies the character set and varsity mode
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; 

Select * from order by
select * from customers order by cust_name collate utf8_general_ci;
Copy the code

28 Security Management

Mysql > select * from user where user = 'user';
use mysql;
select * from user;

Create a database user: Ben
# account: Ben
# password: password
create user ben identified by 'password';
# create a user with no privileges
select * from user;
SQL > alter database username
rename user ben to  ben1;
rename user ben1 to  ben;

# check the permissions of user Ben
show grants for ben;

Mysql > grant learn_mysql to user Ben
grant select on learn_mysql.* to ben;

# revoke user Ben's query permission
revoke select on learn_mysql.* to ben;

# change user password, but only with permission
set password for ben = PASSWORD('password1');
# change the password of the login user by default
set password = PASSWORD('password1');
Copy the code

29 Database Maintenance

# analyze: Analyze whether there is a problem with the table construction
analyze table orders;

# check: Check a table
check table orders,orderitems;
Copy the code

30 Improved performance

To view the current configuration: show variables; show status;
Show all processes: show processList (very useful to see IP on the server)
Explain to view select statement performance, and then optimize
Never retrieve more data than you need, i.e. Select * reduce usage
When select retrieves data, or is used too much. You can use union to splice multiple SELECT statements, which has much better performance
Like is slow, so it’s best to use fulltext instead of like

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

Mysql Must Know must Know Study Notes

0 Database Scripts

1 know SQL

2 the mysql profile

3 use MySQL

4 Retrieving Data

5 Sort and retrieve data

6 Filtering Data

7 Data Filtering

8. Using wildcard techniques

9 Search using regular expressions

10 Create a calculation field

11 Use data processing functions

12 Summary Data

13 Grouped Data

14 Use subquery

15 Using Connections

16 Advanced Connection

17 Combination Query

18 Full-text search

19 Inserting Data

20 Update and delete data

21 Create and operate database tables

22 Using Views

23 Use stored procedures

24 Use a cursor

25 the trigger

Manage transaction processing

Globalization and localization

28 Security Management

29 Database Maintenance

30 Improved performance

Mysql Must Know must Know Study Notes

0 Database Scripts

1 know SQL

2 the mysql profile

3 use MySQL

4 Retrieving Data

5 Sort and retrieve data

6 Filtering Data

7 Data Filtering

8. Using wildcard techniques

9 Search using regular expressions

10 Create a calculation field

11 Use data processing functions

12 Summary Data

13 Grouped Data

14 Use subquery

15 Using Connections

16 Advanced Connection

17 Combination Query

18 Full-text search

19 Inserting Data

20 Update and delete data

21 Create and operate database tables

22 Using Views

23 Use stored procedures

24 Use a cursor

25 the trigger

Manage transaction processing

Globalization and localization

28 Security Management

29 Database Maintenance

30 Improved performance

Related Posts

Use Scala and Akka-Stream to handle AD bidding

Netty Architecture Overview & Core Components

No secrets under the source code – do the best Netty source analysis tutorial