MySQL must know the v1cn learning Summary

MySQL must know the v1cn learning Summary
----
Blog, http://blog.csdn.net/shunqiziranhao007/article/details/8730477
Date, January 1, March 28, 2013
----
Official website,
Http://forta.com/books/0672327120/
----
 
This book is suitable for getting started with MySQL. It will not take long for you to finish reading.
 
----
Chapter 2 SQL
----
1.1 database Basics
-- Database, table, column, data type, row, primary key, foreign key
1.2 What is SQL
-- SQL, Structured Query Language. SQL is case insensitive.
1.3 hands-on practice
Conclusion 1.4
 
----
Chapter 2 Introduction to MySQL
----
2.1 What is MySQL
2.2mysql Tool
-- MySQL command line utility, MySQL adminis-trator, MySQL query browser.
Conclusion 2.3
 
----
Chapter 4 use MySQL
----
3.1 connection
3.2 select database
Create Database crashcourse;
Use crashcourse;
Execute the SQL script file. Note that the path must be consistent.
\. Create. SQL
\. Populate. SQL
3.3 understand databases and tables
Show databases;
Show tables;
Show columns from MERs MERS;
Show status;
Show grants;
Show errors;
Show warnings;
Help
Help show
Help create
Conclusion 3.4
 
----
Chapter 4 Data Retrieval
----
4.1select statement
4.2 retrieve a single column
Select prod_name from products;
4.3 retrieve multiple columns
Select prod_id, prod_name, prod_price from products;
4.4 retrieve all columns
Select * from products;
4.5 retrieve different rows
Select vend_id from products;
Select distinct vend_id from products;
Distinct applies to all columns.
Select distinct vend_id, prod_price from products;
4.6 restricted results
Select prod_name from products;
Select prod_name from products limit 5;
Select prod_name from products limit 0, 1;
Select prod_name from products limit 1, 1;
Select prod_name from products limit 0, 2;
4.7 use a fully qualified table name
Select prod_name from products;
Select products. prod_name from products;
Select products. prod_name from crashcourse. Products;
Conclusion 4.8
 
----
Chapter 2 sorting and retrieval data
----
5.1 sort data
Select prod_name from products;
Sorting is non-descending by default.
Select prod_name from products order by prod_name;
5.2 sort by multiple columns
Select prod_id, prod_price, prod_name from products order by prod_price, prod_name;
5.3 specify the sorting direction
Select prod_id, prod_price, prod_name from products order by prod_price;
Select prod_id, prod_price, prod_name from products order by prod_price DESC;
Select prod_id, prod_price, prod_name from products order by prod_price DESC, prod_name;
Select prod_price from products order by prod_price DESC limit 1;
Conclusion 5.4
 
----
Chapter 4 Data Filtering
----
6.1 use the WHERE clause
Select prod_name, prod_price from products where prod_price = 2.50;
6.2where clause Operator
6.2.1 check a single value
Select prod_name, prod_price from products where prod_name = 'fuses ';
Select prod_name, prod_price from products where prod_price <10;
Select prod_name, prod_price from products where prod_price <= 10;
6.2.2 mismatch check
Select vend_id, prod_name from products where vend_id <> 1003;
Select vend_id, prod_name from products where vend_id! = 1003;
6.2.3 range value check
Select prod_name, prod_price from products where prod_price between 5 and 10;
6.2.4 null check
Select prod_name, prod_price from products where prod_price is null;
Select cust_id from customers where cust_email is null;
Conclusion 6.3
 
----
Chapter 4 Data Filtering
----
7.1 combination where clause
7.1.1and Operator
Select prod_id, prod_name, prod_price from products where vend_id = 1003 and prod_price <= 10;
7.1.2or Operator
Select prod_name, prod_price from products where vend_id = 1002 or vend_id = 1003;
7.1.3 calculation order
Select prod_name, prod_price from products where vend_id = 1002 or 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;
7.2in Operator
Select prod_name, prod_price from products where vend_id in (1002,100 3) Order by prod_name;
Select prod_name, prod_price from products where vend_id = 1002 or vend_id = 1003 order by prod_name;
7.3not Operator
Select prod_name, prod_price from products where vend_id not in (1002,100 3) Order by prod_name;
Conclusion 7.4
 
----
Chapter 2 filter with wildcards
----
8.1like Operator
8.1.1 percent (%) wildcard -- any
Select prod_id, prod_name from products where prod_name like 'Jet % ';
Select prod_id, prod_name from products where prod_name like '% andevil % ';
Select prod_name from products where prod_name like's % E ';
8.1.2 underline (_) wildcard -- single
Select prod_id, prod_name from products where prod_name like '_ ton
Select prod_id, prod_name from products where prod_name like '% ton anvil ';
8.2 tips for using wildcards
Conclusion 8.3
 
----
Chapter 2 search using regular expressions
----
9.1 Introduction to Regular Expressions
9.2 use MySQL Regular Expression
Help Regexp
9.2.1 basic character matching
Select prod_name from products where prod_name Regexp '000000' order by prod_name;
Select prod_name from products where prod_name Regexp '. 000' order by prod_name;
Select prod_name from products where prod_name Regexp binary 'jet' order by prod_name;
9.2.2 or matching
Select prod_name from products where prod_name Regexp '1000 | 2000 'order by prod_name;
9.2.3 match one of several characters
Select prod_name from products where prod_name Regexp '[123] ton 'order by prod_name;
Select prod_name from products where prod_name Regexp '1 | 2 | 3 ton 'order by prod_name;
9.2.4 matching range
Select prod_name from products where prod_name Regexp '[1-5] ton 'order by prod_name;
9.2.5 match special characters
Select vend_name from vendors where vend_name Regexp '. 'order by vend_name;
Select vend_name from vendors where vend_name Regexp '\. 'order by vend_name;
9.2.6 matching character class
9.2.7 Matching Multiple instances
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:] {4} 'order by prod_name;
Select prod_name from products where prod_name Regexp '[0-9] [0-9] [0-9] [0-9] 'order by prod_name;
9.2.8 positioning character
Select prod_name from products where prod_name Regexp '^ [0-9 \.] 'order by prod_name;
Select 'Hello 'regexp '[0-9]';
Conclusion 9.3
 
----
Chapter 4 Create a calculated Field
----
10.1 calculated fields
10.2 splice Fields
Select Concat (vend_name, '(', vend_country, ') from vendors order by vend_name;
Select Concat (rtrim (vend_name), '(', rtrim (vend_country), ') from vendors order by vend_name;
Select Concat (rtrim (vend_name), '(', rtrim (vend_country), ') as vend_title from vendors order by vend_name;
10.3 perform arithmetic calculation
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;
Select 2*3;
Select trim ('a B C ');
Select now ();
Conclusion 10.4
 
----
Chapter 4 using data processing functions
----
11.1 Functions
11.2 use functions
11.2.1 text processing functions -- left, length, locate, lower, ltrim, right, rtrim, soundex, substring, upper.
Help upper;
Select vend_name, upper (vend_name) as vend_name_upcase from vendors order by vend_name;
Select cust_name, cust_contact from MERs where soundex (cust_contact) = soundex ('y lie ');
11.2.2 date and time processing functions
Select cust_id, order_num from orders where order_date = '2017-09-01 ';
Select cust_id, order_num from orders where date (order_date) = '2017-09-01 ';
Select cust_id, order_num from orders where date (order_date) between '2017-09-01 'and '2017-09-30 ';
Select cust_id, order_num from orders where year (order_date) = 2005 and month (order_date) = 9;
11.2.3 numeric processing functions
Conclusion 11.3
 
----
Chapter 4 Summary Data
----
12.1 Aggregate functions
12.1.1avg () function
Select AVG (prod_price) as avg_price from products;
Select AVG (prod_price) as avg_price from products where vendpoints = 1003;
12.1.2count () function
Select count (*) as num_cust from MERs MERS;
Select count (cust_email) as num_cust from MERs MERS;
12.1.3max () function
Select max (prod_price) as max_price from products;
12.1.4min () function
Select min (prod_price) as min_price from products;
12.1.5sum () function
Select sum (Quantity) as items_ordered from orderitems where order_num = 20005;
Select sum (item_price * quantity) as total_price from orderitems where order_num = 20005;
12.2 aggregate different values
Select AVG (distinct prod_price) as avg_price from products where vendpoints = 1003;
12.3 Aggregate functions
Select count (*) as num_items, min (prod_price) as price_min, max (prod_price) as price_max, AVG (prod_price) as price_avg from products;
Conclusion 12.4
 
----
Chapter 4 grouped data
----
13.1 Data grouping
Select count (*) as num_prods from products where vendpoints = 1003;
13.2 create a group
Select vend_id, count (*) as num_prods from products group by vend_id;
Select vend_id, count (*) as num_prods from products group by vend_id with rollup;
13.3 filter groups
Select cust_id, count (*) as orders from orders group by cust_id having count (*)> = 2;
Select vend_id, count (*) as num_prods from products where prod_price> = 10 group by vend_id having count (*)> = 2;
Select vend_id, count (*) as num_prods from products group by vend_id having count (*)> = 2;
13.4 grouping and sorting
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;
13.5select clause order
Conclusion 13.6
 
----
Chapter 1 subquery
----
14.1 subquery
14.2 Filter Using subqueries
Select order_num from orderitems where prod_id = 'tnt2 ';
Select cust_id from orders where order_num in (20005,200 07 );
Select cust_id from orders where order_num in (select order_num from orderitems where prod_id = 'tnt2 ');
Select cust_name, cust_contact from MERs where cust_id in (select cust_id from orders where order_num in (select order_num from orderitems where prod_id = 'tnt2 '));
14.3 use subquery as a calculated Field
Select count (*) as orders from orders where cust_id = 10001;
Select cust_name, cust_state, (select count (*) from orders where orders. cust_id = MERs. cust_id) as orders from customers order by cust_name;
Select cust_name, cust_state, (select count (*) from orders where cust_id = cust_id) as orders from customers order by cust_name;
Conclusion 14.4
 
----
Chapter 4 join table
----
15.1 connections
15.1.1 relational table
15.1.2 why use join
15.2 create a connection
Select vend_name, prod_name, prod_price from vendors, products where vendors. vend_id = products. vend_id order by vend_name, prod_name;
15.2.1importance of where clause
Select vend_name, prod_name, prod_price from vendors, products order by vend_name, prod_name;
15.2.2 internal connections
Select vend_name, prod_name, prod_price from vendors inner join products on vendors. vend_id = products. vend_id;
15.2.3 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;
Conclusion 15.3
 
----
Chapter 4 Create advanced connections
----
16.1 use table alias
16.2 use different types of connections
16.2.1 auto join
16.2.2 natural connections
16.2.3 external connections
16.3 use a join with aggregate Functions
16.4 use join and join conditions
Conclusion 16.5
 
----
Chapter 4 Combined Query
----
17.1 Combined Query
17.2 create a Combined Query
17.2.1 use Union
17.2.2union rules
17.2.3 contains or cancels duplicate rows
17.2.4 sort the combined query results
Conclusion 17.3
 
----
18th full text search
----
18.1 understand full text search
18.2 use full text search
18.2.1 enable full text search
18.2.2 perform full text search
18.2.3 use query Extension
18.2.4 Boolean text search
18.2.5 instructions for full text search
Conclusion 18.3
 
----
Chapter 4 insert data
----
19.1 data insertion
19.2 Insert the complete row
Insert into MERs values (null, 'pep E. lapew ', '2014 Main Street', 'Los Angeles ', 'CA', '000000', 'USA', null, null );
Insert into MERs (cust_name, cust_contact, cust_email, cust_address, cust_city, cust_state, cust_zip, cust_country) values ('pep E. lapew ', null, null, '1970 Main Street', 'Los Angeles ', 'CA', '123', 'usa ');
19.3 insert multiple rows
19.4 Insert the Retrieved Data
Conclusion 19.5
 
----
Chapter 4 Update and delete data
----
20.1 update data
Update MERs set cust_email = 'elmer @ fudd.com 'Where cust_id = 10005;
Update MERs set cust_name = 'the fudds ', cust_email = 'elmer @ fudd.com' Where cust_id = 10005;
Update MERs set cust_email = NULL where cust_id = 10005;
20.2 delete data
Delete from MERs where cust_id = 10006;
20.3 guidelines for update and Deletion
Conclusion 20.4
 
----
Chapter 4 Create and manipulate a table
----
21.1 create a table
21.1.1 table creation Basics
21.1.2 use Null Value
21.1.3 primary key Reintroduction
21.1.4 use auto_increment
21.1.5 specify the default value
21.1.6 engine type
21.2 update a table
Alter table vendors add vend_phone char (20 );
Alter table vendors drop column vend_phone;
21.3 delete a table
Drop table customers2;
21.4 rename a table
Rename table customer2 to mers MERS;
Conclusion 21.5
----
See create. SQL
 
----
Chapter 4 usage View
----
22.1 View
22.1.1 why View
22.1.2 view rules and restrictions
22.2 use View
Create view XX, show create view XX, drop view xx;
22.2.1 simplify complex connections using views
22.2.2 reformat the retrieved data with the view
22.2.3 use views to filter unwanted data
22.2.4 use view and calculated fields
22.2.5 update View
Conclusion 22.3
 
----
Chapter 4 use stored procedures
----
23.1 stored procedures
23.2 why use stored procedures
23.3 use stored procedures
23.3.1 execute the Stored Procedure
Call productpricing (@ pricelow, @ pricehigh, @ priceaverage );
23.3.2 create a stored procedure
Create procedure productpricing ()
Begin
Select AVG (prod_price) as priceaverage from products;
End
23.3.3 delete a stored procedure
Drop procedure productpricing;
23.3.4 use parameters
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;
Call productpricing (@ pricelow, @ pricehigh, @ priceaverage );
Select @ pricehigh, @ pricelow, @ priceaverage;
Create procedure ordertotal (in onumer int, out ototal decimal (8, 2 ))
Begin
Select sum (item_price * quantity) from orderitems where order_num = onumber into ototal;
End
Call ordertotal (20005, @ total)
23.3.5 create a smart storage process
23.3.6 check the Stored Procedure
Show create procedure ordertotal;
Conclusion 23.4
 
----
Chapter 4 Use cursor
----
24.1 cursor
24.2 use a cursor
24.2.1 create a cursor
Create procedure processorders ()
Begin
Declare ordernumbers cursor for select order_num from orders;
End
24.2.2 open and close a cursor
Open ordernumbers;
Close ordernumbers;
24.2.3 Use cursor data
Conclusion 24.3
 
----
Chapter 4 Use Triggers
----
25.1 trigger
25.2 create a trigger
Create trigger newproduct after insert on products fro each row select 'product added ';
25.3 delete a trigger
Drop trigger newproduct;
25.4 use a trigger
Create trigger neworder after insert on orders for each row select new. order_num;
25.4.1insert trigger
25.4.2delete trigger
25.4.3update trigger
25.4.4 further introduction to triggers
Conclusion 25.5
 
----
Chapter 4 Management Transaction Processing
----
26.1 Transaction Processing
26.2 control transaction processing
Start transaction;
26.2.1 use rollback
Select * From ordertotals;
Start transaction;
Delete from ordertotals;
Select * From ordertotals;
Rollback;
Select * From ordertotals;
26.2.2 use commit
Start transaction;
Delete from orderitems where order_num = 20010;
Delete from orders where order_num = 20010;
Commit;
26.2.3 retention point
Savepoint delete1;
Rollback to delete1;
26.2.4 change default submission Behavior
Set autocommit = 0;
Conclusion 26.3
 
----
Chapter 2 globalization and Localization
----
27.1 Character Set and collation
27.2 use character sets and collation
Show Character Set;
Conclusion 27.3
 
----
Chapter 4 Security Management
----
28.1 Access Control
28.2 Manage Users
Use MySQL;
Select User from user;
28.2.1 create a user account
Create user Ben identified by 'p @ $ word ';
Rename user Ben to bforta;
28.2.2 delete a user account
Drop user bforta;
28.2.3 set Access Permissions
Show grants for bforta;
Grant select on crashcourse. * To bforta;
Show grants for bforta;
28.2.4 Change Password
Set password for bforta = PASSWORD ('n3w p $ word ');
Conclusion 28.3
 
----
Chapter 4 Database Maintenance
----
29.1 back up data
29.2 perform database maintenance
29.3 diagnose startup Problems
29.4 view log files
Conclusion 29.5
 
----
Chapter 2 Performance Improvement
----
30.1 improve performance
Conclusion 30.2
 
Appendix A MySQL entry
Appendix B Sample Table
Appendix C MySQL statement syntax
Appendix D Mysql Data Type
Appendix E reserved MySQL words
Index