The content of this article is a summary of the main knowledge of the book “MySql Must Know must Know”, covering the concepts and knowledge points of each chapter; Mysql is an introductory article for students who want to quickly grasp the terminology and knowledge of mysql. I have to say that as an introductory book, MySql Will Know will Know is very reliable and practical, suitable for all ages.

Database terminology

  1. Database A container that holds organized data (usually a document or set of files).
  2. Table A structured list of data of a particular type.
  3. Column A field in a table. All tables are composed of one or more columns.
  4. Datatype (datatype) the type of data allowed. Each table column has a corresponding data type that limits (or allows) the data stored in that column.
  5. A record in a row table. The data in a table is stored in rows, with each record saved stored in its own row. If you think of a table as a grid, the columns in the grid are vertical and the rows are horizontal.
  6. Primary key A column (or group of columns) whose value uniquely distinguishes each row in a table. Each row in a table should have a column (or set of columns) that uniquely identifies itself.
  7. SQL(pronounced as the letter S-q-L or sequel) is an abbreviation of Structured Query Language. SQL is a language specifically designed to communicate with databases.

MAC Database Installation

  1. MySQL Community Server; Portal: MySQL download
  2. After the installation is complete, you can modify the configuration file for global convenience. ZSHRC if the terminal uses ZSH, and.bash_profile if you use bash. ZSHRC is used as an examplevim ~/.zshrc, added at the endexport PATH=$PATH:/usr/local/mysql/bin, save the Settings and exitsource ~/.zshrcAnd then typemysql -u root -h localhost -p, the terminal will ask you to enter the password, in this case, enter the password you set during the mysql installation to successfully log in.

Using mysql

  1. Terminal using mysql
  2. Vscode use mysql
  3. Professional visualization tools such as workbench

Here I mainly use the first two ways, the terminal is very convenient to use mysql, the only deficiency is the operation results are not too friendly display; If you are using vscode, I also recommend using vscode to operate mysql as follows:

  • Start by searching for SQLTools in the vscode plug-in store and installing it

  • You will then see a data graph icon on the left-hand menu bar of vscode

  • You can then create a database connection, create a new.sql file, enter the command, and click run on Active Connection at the top of the file to execute the SQL statement

  • Execute the commandcreate database demo;Create a new database, called Demo, and perform all of the following exercises in this database.

Create table

Here we use the sample table definition directly from the MySql Must Know must Know book. Create a customer information table as an example:

########################
# Create customers table
########################
DROP TABLE IF EXISTS customers;
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;
Copy the code

Definitions for other sample tables are also given in this book. The full definition is as follows:

######################################## # MySQL Crash Course # http://www.forta.com/books/0672327120/ # Example table creation scripts ######################################## ######################## # Create customers table ######################## DROP TABLE IF EXISTS customers; 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 ######################### DROP TABLE IF EXISTS orderitems; 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 ##################### DROP TABLE IF EXISTS orders; 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 ####################### DROP TABLE IF EXISTS products; 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 ###################### DROP TABLE IF EXISTS vendors; 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 ########################### DROP TABLE IF EXISTS productnotes; 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

After creating the sample table above, you can see all the current tables in vscode:

Insert data

The SQL statement for inserting data is

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]');
Copy the code

Insert multiple data You can repeat multiple INSERT into statements, or values can be followed by multiple data values, separated by commas.

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]' ), ( 10001, 'Coyote Inc.', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'Y Lee', '[email protected]' );Copy the code

Populate the table with sample data using the populate. SQL script.

Retrieve the data

Most of the chapters in the book are about data retrieval, and the main work is also data retrieval. The following will introduce data retrieval in detail.

Based retrieval

  • Select prod_name from products;

  • Retrieve multiple columns

    select prod_id, prod_name from products;

  • Retrieve all columns

    select * from products;

  • To retrieve different values, or to de-duplicate data, use the keyword distinct; Distinct does not only apply to the column that follows, but to all columns.

    Select select * from products; Select prod_id, prod_name from products;Copy the code

  • LIMIT the search results — LIMIT, OFFSET

    select prod_name from products limit 5; Select prod_name from products limit 5 offset 5; //limit specifies the number of rows to return, and offset specifies where to startCopy the code

  • Sort retrieve data — ORDER BY

  • Column sorting

    select cust_id from customers order by cust_id; Select CUST_id, cust_name from customers order by CUST_id, CUST_name;Copy the code

  • Column position sort

    Select cust_id, cust_name from customers order by 1,2;Copy the code

  • Matters needing attention

    • The ORDER by clause must be the last clause in the SELECT statement
    • The order BY clause can be followed by a non-retrieved column

  • Specify sort

    • Ascending ASC
    • Descending DESC
    Select CUST_id, CUST_name from customers order by CUST_id desc, CUST_name ASC;Copy the code

Filter data — WHERE

Select * from products where prod_price > 3; select * from products where prod_price > 3; #select * from products where prod_name = 'saFe'; #select * from products where prod_name = 'safe' and prod_price > 9; #select * from products where prod_price between 10 and 50 and prod_name > 'j'; #select * from products where prod_price in (10, 20); #select * from products where prod_price not in (10, 20, 55, 50);Copy the code
  • The operator
symbol meaning
= Is equal to the
! = Is not equal to
<> Is not equal to
< Less than
< = Less than or equal to
! < Not less than
> Is greater than
> = Greater than or equal to
! > No greater than
between and Range between
is null Is empty

Advanced filtering – Combines WHERE clause conditions

  • AND with the
  • The OR OR
  • IN the scope of
  • NOT the

Fuzzy retrieval

  1. Wildcard match

Fuzzy retrieval for wildcard matching contains the following elements: – Wildcard – search pattern – predicate

Common wildcards are: % and _

  • (percent sign) % : Matches any number of characters {0,}
  • (Underscore) _ : Matches a single character. Matches two characters with __ and two underscores
# use wildcard for fuzzy matching, with predicate like use #% wildcard to match any character any number of times #_ Match any character once # Place wildcard at the beginning of the search pattern, which is the slowest to search. #select * from products where prod_name like '%anv%'; #select * from products where prod_name like '_ ton anv%';Copy the code
  • Tips and precautions for using wildcards
    • Wildcard searches take more processing time than normal searches
    • Don’t overuse wildcards
    • Try not to use wildcards at the beginning of the search pattern; this is the slowest
    • Pay careful attention to the placement of wildcards, which return unexpected data if misplaced
  1. Regular match search
#select * from products where prod_name regexp '\\([0-9] sticks{0,1}\\)';Copy the code
  1. Full-text search
Select match(column name) against(search string); Rank #select note_text from productNotes where match(note_text) against('rabbit');Copy the code

Computed field

Computed fields do not actually exist in the database table, but are generated when the SELECT statement is run. You usually need to alias the computed field, using the AS keyword, to use the computed field in the return result.

  • Several methods of concatenating fields

    • Column 1 + ‘custom’ + Column 2 + ‘Custom’

    • Column 1 | | ‘custom’ | | column 2 | | ‘custom’

    • Concat(column 1, ‘xx’, column 2, ‘custom’)

  • Perform arithmetic calculations

    • Column 1 * Column 2 as alias

  • Field alias (AS) keyword

    • Computed fields must be aliased before they can be returned to the client for use

  • A few examples

select  prod_name, quantity,item_price,quantity*item_price AS expanded_price from OrderItems where order_num = 20008;

select Concat(RTRIM(vend_name, '(', RTRIM(vend_country), ')') as vend_title from Vendors order by vend_name;
Copy the code

  • Use functions to process data

    • Advantages and disadvantages of functions

      • Advantages: Handling complex calculations
      • Disadvantages: Each DBMS supports different function names for the same computing power, and there is a risk that functions cannot be used across platforms

    • The function type

      • The system function
      • String handling function
      • Date handler function
      • Numerical processing function

  • Summary data

    • Aggregate function – a function that runs on certain lines and evaluates and returns a value

    • Common aggregation functions

      • AVG: Returns the average value of a column

      • COUNT: Returns the number of rows in a column

      • MAX: Returns the maximum value of a column

      • MIN: Returns the minimum value of a column

      • SUM: Returns the SUM of the values in a column

Use the grouping — GROUP BY clause

  • The GROUP BY clause

    • Create groups, instruct the DBMS to group the data, and then aggregate each group rather than the entire result set
    • The columns listed in the group by clause must be retrieved columns or valid expressions for retrieval.

  • HAVING clause

    • Used for filtering packets
    • Having vs. where
      • Where filter rows, HAVING filter groups
      • Having group by filters groups. Without the group by clause, it does the same thing as where
      • Where and HAVING can exist together in a single search statement, usually for group searches

  • example

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 having count(*) >= 2;
Copy the code
  • Considerations for using Group by
    • The Group by clause can contain any number of columns, making it possible to nest groups and group data more subtly
    • If groups are nested in the group by clause, the data is summarized on the last group specified.
    • Each column listed in the group by clause must be a retrieval column or a valid expression and cannot be aliased
    • Most SQL implementations do not allow group by columns to have variable-length data types
    • Every column in a SELECT statement must be given in the group by clause, except for the aggregate calculation statement
    • If the grouping column contains rows with null values, NULL is returned as a grouping.
    • The group by clause must appear after the WHERE clause and before the ORDER by clause.

Retrieves the sentence order of a sentence

The order of the individual clauses of the retrieval statement is:

  • select
  • from
  • where
  • group by
  • having
  • order by

The subquery

  • Nested retrieval statements are called subqueries in which another retrieval statement is nested
  • Subqueries are used when a retrieval statement depends on data in other database tables. That is, subqueries are used as query criteria.
select cust_id from Orders
where order_num in (select order_num from OrderItems where prod_id = 'RGANO1');
Copy the code
  • Subqueries are used as computed fields
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

Link – the JOIN table

  • Table1 inner join table2 on condition

  • Table1 outer join table2 on

  • Table1 left outer join table2 on

  • Table1 right outer join table2 on condition

  • What is a table JOIN

    • The operation of jointly querying a collection of data from multiple relational tables

  • Why use table joins

    • Table joins are a great solution when you want to retrieve data from multiple relational tables in a SINGLE SELECT statement

  • How do I join a table

    • From and where clauses

      • The FROM clause specifies multiple tables
      • The WHERE condition specifies the condition of the join

  • example

select vend_name, prod_name, prod_price from Vendors, Products where Vendors.vend_id = Products.vend_id; select vend_name, prod_name, prod_price from Vendors inner join Products on vendors.vend_id = Products.vend_id; Left outer join left outer join right outer join Left selects all rows from the left table, Select Customers. Cust_id,orders.order_num from customers left outer join Orders on Orders.cust_id = customers.cust_id; select customers.cust_id,orders.order_num from customers right outer join orders on orders.cust_id = customers.cust_id;Copy the code
  • Related terms
    • Equivalent coupling
    • In the connection
    • Outer joins

Combined query — UNION

  • Usage scenarios for UNION

    • Retrieve data from multiple tables
    • Retrieves data from a table with different conditional constraints

  • The operator

    • UNION

      • duplicate removal

    • UINION ALL

      • Not to heavy

  • example

    select cust_name from Customers where cust_state in ('IL', 'IN', 'MI') union select cust_name from Customers where cust_name = 'Fun4All'
Copy the code

  • Matters needing attention

    • It must consist of more than two SELECT statements
    • Each query must contain the same column, expression, or aggregate function
    • Column data types must be compatible: the types do not have to be identical, but they must be types that can be implicitly converted.

Using the view

The concept of a view: A view is a virtual table that, unlike a table that contains data, contains only queries that dynamically retrieve data when used. The view itself contains no data. The advantage of using views is that you can reuse SQL and simplify complex SQL operations.

  • Views are created using the CREATE VIEW statement.
  • Use SHOW CREATE VIEW viewname; To view the statement that creates the view.
  • DROP VIEW viewName; DROP VIEW viewname; .
  • When updating a VIEW, you can use DROP before CREATE, OR CREATE OR REPLACE VIEW directly. If the view to be updated does not exist, the second update statement creates a view; If the view to be updated exists, the second update statement replaces the original view.

Simplify complex joins with views

Storing complex join processes in a view simplifies subsequent retrieval of the same type of data.

Create view productCustomers as select CUST_NAME, CUST_contact,prod_id 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 ';Copy the code

Use view and computed fields

The relevant computational logic is stored in view, which facilitates subsequent data retrieval

create view orderitemexpanded as
select order_num, prod_id, quantity, item_price, quantity * item_price as expanded_price
from orderitems;
Copy the code

Using stored procedures

CREATE PROCEDURE processor() BEGIN select * from products end;Copy the code

Using triggers

# TRIGGER TRIGGER CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT new. order_num; ERROR 1415 (0A000): Not allowed to return a result set from a trigger MYSQL5 CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW select new. order_num INTO @order_num; insert into orders(order_date,cust_id) values(now(),10001); select @order_num; / / back to 20011Copy the code

Using transactions

Not all engines support transactions, MyISAM does not support transactions, while InnoDB does. Transaction processing is a mechanism for managing mysql operations that must be executed in batches to ensure that the database does not contain incomplete operation results. Transaction, rollback, Commit commit, savePointCopy the code

Performance and Safety

Todo…