MySQL Basics Summary

1. Create a table

CREATE TABLE Customers (  cust_id      INT not       null auto_increment,  cust_name    CHAR ()  is not NULL,  cust_address CHAR (+)  NULL,  cust_city    char (a)  null,  cust_state   char (5)   null,  cust_zip     char (10 )  null,  cust_country char (a)  null,  cust_contact char ($)  null,  cust_email   CHAR (255) NULL,  PRIMARY KEY (cust_id)) Engine=innodb;

Where auto_increment indicates that the column is autogrow, Char holds a fixed-length string, and char (50) indicates that the stored character length of 50,primary key (cust_id) represents the cust_id setting as the primary key. Engine=innodb, which indicates that the storage engine is set to InnoDB.

About InnoDB:

InnoDB is the first data storage engine on MySQL to provide foreign key constraints, in addition to providing transactional processing, InnoDB also supports row locks, provides consistent, non-lock reads like Oracle, increases the number of concurrent read users and improves performance without increasing the number of locks. InnoDB's design goal is to maximize performance when dealing with large volumes of data, and its CPU utilization is the most efficient of all other disk-based relational database engines.

InnoDB is a complete database system placed in the background of MySQL, InnoDB has its own buffer pool, can buffer the data and index, InnoDB also holds the data and index in the table space, may contain several files, this and MyISAM table is completely different, in MyISAM, The table is stored in a separate file, and the size of the InnoDB table is limited only by the size of the operating system file, typically 2GB.

Similarly, you can create a OrderItems table (which holds the actual items in each order), the Orders table (to hold the customer's order), the Products table (store the product catalog, each line represents a product), and the Vendors table (the supplier that holds the product)

create TABLE orderitems (order_num int not NULL, Order_item int. not NUL L, prod_id CHAR (TEN) is not NULL, quantity INT is not null, Item_price DECIMAL (8,2) is not NULL, PRIMARY K EY (Order_num, Order_item)) Engine=innodb; CREATE TABLE orders (order_num int not null auto_increment, order_date DATETIME not NULL, cust_id INT NO T NULL, PRIMARY KEY (order_num)) Engine=innodb;     CREATE TABLE Products (prod_id char (TEN) NOT NULL, VEND_ID int. NOT NULL, Prod_name char (255) Not NULL, Prod_price DECIMAL (8,2) is not NULL, Prod_desc TEXT NULL, PRIMARY KEY (prod_id)) Engine=innodb; CREATE TABLE Vendors (vend_id INT not NULL auto_increment, Vend_name char (a) not NULL, vend_address Char () NULL, Vend_city char (a) null, Vend_state char (5) NULL, Vend_zip char (TEN) NULL, Vend_country char (5 0) NULL, PRIMARY KEY (vend_id)) Engine=innodb; 

2. Add data to the Customers table, where column = value is used, so that inserting data is not affected when the order of the columns in the table changes.

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 ', ' 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 (c ust_id, Cust_name, cust_address, cust_city, Cust_state, Cust_zip, Cust_country, Cust_contact, Cust_email) VALUES (10003, ' Wascals ', ' 1 Sunny ', ' Muncie ', ' in ', ' 42222 ', ' USA ', ' Jim Jones ', ' [email protected] '); INSERT into Customers (c ust_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 C Ustomers (cust_id, Cust_name, cust_addrESS, Cust_city, Cust_state, Cust_zip, Cust_country, Cust_contact) VALUES (10005, ' E fudd ', ' 4545 53rd Street ', ' Chicago ', ' I L ', ' 54545 ', ' USA ', ' E Fudd ');

Similarly, you can insert data into the OrderItems table, Orders table, Products table, vendors table.

3. Show all databases

SHOW DATABASES;

4. Search

(1) Retrieving a single column

If a column named Prod_name is retrieved from the Products table.

SELECT Prod_name from Products;

Output:

(2) Retrieving multiple columns

SELECT Prod_id,prod_name,prod_price from Products;

Output

(3) Retrieving all columns

SELECT * FROM Products;

(4) Sort Search

SELECT Prod_name from the products ORDER by Prod_name;

Precautions:

A. When using the ORDER BY clause, ensure that it is the last sentence of the SELECT statement

B. You can sort by a column that is not selected, such as

SELECT prod_id from the products by Prod_name;

PROD_ID is the selected column, Prod_name is a non-selectable column.

C. You can sort by more than one column, such as

Selectprod_id,prod_price,prod_name from the products ORDER by Prod_price,prod_name;

D. You can sort by the relative position of the column

Selectprod_id,prod_price,prod_name from the Products ORDER by 2, 3;

At this point, sorting can only be sorted by the selected column, and the possibility of sorting by wrong column names is added.

E. The default sort is ascending, and descending sorting requires specifying the DESC keyword, which is placed behind the selected column.

SELECT prod_name from Productsorder by Prod_name DESC;

If you need to sort in descending order on more than one column, you must specify DESC for each column.

(5) Filter Search

A. Single-condition filtering

Where the data is filtered by specific search criteria to filter out specific rows.

SELECT Prod_name,prod_price from Products WHERE Prod_price < 3.49;

WHERE clause support =, <>! =, <, <=,;, >=,between,is null action

Note that when you use a string in the WHERE clause to filter, you use single quotation marks to qualify the string. Such as:

SELECT Vend_id,prod_name from Products WHERE prod_id! = ' FB '; SELECT vend_id,prod_name,prod_id from Products WHERE prod_id! = ' FB ';

B. Multi-conditional filtering (combined filtering)

Retrieves rows that satisfy all conditions, each condition connected with and, equivalent to &&

SELECT prod_id,prod_price,prod_name,vend_id from products WHERE vend_id = 1001 and Prod_price < 10;

Output:

Retrieves a row that satisfies either condition, with or to connect between conditions, equivalent to | |

SELECT prod_id,prod_price,prod_name,vend_id from products WHERE vend_id = 1001 OR Prod_price < 10;

Note: When using both and and or statements, parentheses should be used to specify the order of calculation, because the default and precedence is higher than or, and SQL processes and statements first. All products produced by manufacturer 1001 or 1002, if the price is below $10, are retrieved.

SELECT prod_id,prod_price,prod_name,vend_id from Products

WHERE vend_id = 1001 OR vend_id = 1002 and Prod_price < 10;

Output:

In this result, a product with a price of more than $10 was present because SQL processed and caused it first without parentheses. Change to

SELECT prod_id,prod_price,prod_name,vend_id from Products

WHERE (vend_id = 1001 OR vend_id = 1002) and Prod_price < 10;

To get the right results

Another multi-conditional filter-operator in (functionally equivalent to or) is queried for data that conforms to any of the criteria in.

SELECT prod_id,prod_price,prod_name,vend_id from Products WHERE vend_id in (1001,1002);

Advantages:

The in operator is generally faster than or executed.

In can contain other SELECT statements, enabling more dynamic creation of where words.

Negative Query-not

Not can be placed in front of the column name, or it can be placed after the column name. As follows:

SELECT prod_id,prod_price,prod_name,vend_id from the products WHERE vend_id isn't in (1001,1002); SELECT prod_id,prod_price,prod_name,vend_id from Products WHERE not vend_id in (1001,1002);

Use like for fuzzy queries

"%" stands for any character, and "_" represents a single character.

SELECT prod_id,prod_price,prod_name,vend_id from Products WHERE prod_id like ' f% ';

SELECT prod_id,prod_price,prod_name,vend_id from Products WHERE prod_id like ' f_ ';

Wildcard search processing time is long, so you can use other operators to achieve the same purpose, it is not recommended to use wildcard characters.

To repeat the query distinct

SELECT DISTINCT order_num from OrderItems;

(7) Calculation field query

1. Stitching: Connecting multiple values together to form a single value

such as the vend_name and vend_country stitching.

SELECT CONCAT (Vend_name, ' (', Vend_country, ') ') from the vendors ORDER by Vend_name;

Use alias as

SELECT CONCAT (Vend_name, ' (', Vend_country, ') ') as Vend_title from vendors ORDER by Vend_name;

Benefits of using aliases: 1) assigning an alias to a calculated field makes it easy for the client to use

2) make the meaning of the column easier to understand

(8) Querying using functions

Text-handling functions:

Left (Str,len);

STR is a string, and Len means to intercept from the left and intercept the length of the string.

Right (Str,len)

Same as Left (Str,len), just starting from the right to intercept.

SUBSTRING (Str,pos);

is equivalent to substring (str from POS), str is a string, POS is the position from which to start interception, until the end, if the POS is negative, it means to intercept starting from the countdown.

SUBSTRING (Str,pos,len);

The equivalent of substring (str from POS for Len) is intercepted from the POS, and the Intercept length is Len.

Substring_index (Str,delim,count);

Delim is a literal character, and if Count is a positive number, the string that precedes the count identifier is intercepted, and if count is a negative number, the string that follows the count of counting characters is truncated.

Length (); Calculate string Lengths

Lower (); Uppercase to lowercase

SOUNDEX (); Returns the string in which the string is pronounced more like.

As input:

SELECT Vend_name,upper (vend_name) as vend_name_upcase from vendors ORDER by Vend_name;

Aggregation functions:

AVG (), COUNT (), MAX (), MIN (), SUM ()

such as calculating the average price of all products:

SELECT AVG (Prod_price) as avg_price from products;

(9) Packet Query GROUP by

After a query is grouped by a field, the number of groups, such as

SELECT Vend_id,count (*) as num_prods from the products GROUP by vend_id;

The number of products produced by each manufacturer in the product is queried.

Output:

Used with the WITH rollup

SELECT Vend_id,count (*) as Num_prods from Products GROUP by vend_id with rollup;--last add a sum row

Use with having, filter groups

Where filters the rows, having a filter grouping, such as a customer who queries the number of orders greater than or equal to 2.

SELECT Cust_id,count (*) as ' order number ' from Orders GROUP by cust_id have COUNT (*) >= 2;

Note: 1) Group by words can contain any number of columns

2) If nested groupings are used in group by, the data is aggregated on the last set of groupings

3) The column listed in the Group by clause must be a column in select or a valid expression, but not a clustered function.

4) Each column in the SELECT statement must be given in the GROUP BY clause

5) If there is a null value in the grouping column, NULL is returned as a group, and if there are multiple rows of null values in the column, they will be grouped.

6) The Group by clause must appear after the where sentence, before the ORDER BY clause

MySQL Basics Summary