MYSQL Basic statement + advanced query

mysql command to execute SQL script file:

Enter MySQL command line mode from cmd:

Mysql>-uroot–prootpassword–ddatabasename

If it is my local database, it will be modified as follows:

Mysql>-uroot–p111111–dmy_database < Xxx.sql

Execute SQL script file:

Mysql> source E:\program Files\sql\xxxxxx.sql.

As shown in the following:

2. mysql Basics: 2.1 Connect to MySQL Database

Hostname (computer name)--if connected to a local MySQL server, localhost;

Port--The default port is 3306.

2.2 Database Operations

Suppose the name of the database is: myDatabase;

(1) Select database:

Input: UsemyDatabase;

Output: Database changed

(2) All databases are displayed:

Input:SHOW DATABASES;

(3) Show all tables under this database:

Input:SHOW TABLES;

(4) Display all the columns in a table, assuming the table name is customers:

Input: SHOW COLUMNS from customers; Or the DESCRIBE customers;

2.3 Retrieving data (SELECT)

Input:SELECT product_name from Products;

Meaning: A column named Product_Name is retrieved from the Products table.

Note: The retrieved data is not sorted, and the returned data may be the order in which the data is added to the table.

Input:SELECT product_id, Product_Name, Product_price from products;

Input:SELECT * from Products;

Input:SELECT DISTINCT vend_id from Products;

Input:SELECT product_name from the Products LIMIT 5;// less than 5 lines.

Input:SELECT product_name from the products LIMIT 5,5;// retrieves 5 lines after the 6th line .

Input:SELECT product.product_name from Products;// use fully qualified table names

Input:SELECT products.product_name from mydatabase.products;// fully qualified database name and table name

2.4 Sort retrieved data (order by)

Input:SELECT product_name from the products order by product_name;// Sort by a column.

Input:SELECT product_id, Product_price, product_name from Products ORDER by Product_price, product_name; Sort by more than one column.

Specify the sort direction (if you do not specify a sort direction, the default is ascending ASC):

Input:SELECT product_id,product_price,product_name from the products ORDER by Product_price Desc/asc;//desc is descending, ASD is ascending.

Input:SELECT product_id,product_price,product_name from Products ORDER by Product_price DESC, Product_Name; First Follow Product_price descending row, and then the result by name sort.

Input:SELECT Product_price from Products ORDER by Product_price DESC LIMIT 1;

Note : Keyword order select->from>order by>limit, if the order is incorrect, the result will be an error.

2.5 filtering data (WHERE)

Filter Condition: =,<>,!=,<,<=,>,>=,between

operator	=	<>	! =	<	<=	;	>=	between
Description	Equals	Not equal to	Not equal to	Less than	Less than or equal	Greater than	Greater than or equal	Between

Input:SELECT Product_name,product_price from Products WHERE product_price=2.50;

Input:SELECT Product_name,product_price from Products WHERE Product_price between 5 and ten;

Input:SELECT product_name from Products WHERE Product_price is NULL;// null value check.

2.6 Data filtering (combination WHERE clause, and/or/in/not)

Input:SELECT product_id,product_price,product_name from products WHERE vend_id = 1003 and Product_price <=10;

Input:SELECT Product_name,product_price from Products WHERE vend_id=1002 OR vend_id=1003;

Input:SELECT Product_name,product_price from Products WHERE (vend_id=1002 OR vend_id=1003) and Product_price >=10;

Note : Whenever you use a WHERE clause with and and OR operators, you should use parentheses to explicitly group the operators instead of relying too heavily on the default calculation order.

Input:SELECT Product_name,product_price from Products WHERE vend_id in (1002,1003) ORDER by Product_Name;

Input:SELECT Product_name,product_price from Products WHERE vend_id=1002 OR vend_id=1003 ORDER by Product_Name;

Note : in Where clause to apply in and OR function is the same, but in the advantages of the operator are as follows:

(1) When using a long list of legitimate options, the in operator's syntax is clearer and more intuitive;

(2) The order of calculation is easier to manage;

(3) In operators are generally faster than the OR operator list;

(4) The greatest advantage of in is that it can contain other SELECT statements, allowing for a more dynamic creation of where clauses.

Input:SELECT Product_name,product_price from Products WHERE vend_id not in (1002,1003) ORDER by Product_Name;

2.7 Filtering with wildcard characters (like-%)

wildcard character (wildcard ): A special character used to match a portion of a value.

Like indicates that the search pattern followed by MySQL is compared using wildcard matching instead of direct equality matching.

% : Indicates any number of occurrences of any character.

1) Find all products that start with the word jet:

Input :SELECT product_id,product_name from products WHERE product_name like ' jet% ';

Note : Wildcards can be used anywhere in search mode, and multiple wildcard characters can be used.

Input :SELECT product_id,product_name from products WHERE product_name like '%anvil% ';

_ : Use with% , but the underscore matches only a single character instead of multiple characters.

Note : Wildcard searches typically take longer to process than other searches discussed earlier. Tips for using wildcard characters:

(1) Do not overuse wildcard characters, that is, if other operators can achieve the same purpose, you should use other operators;

(2) If you do need to use wildcards, do not use them at the beginning of the search pattern unless absolutely necessary. the wildcard character is placed at the beginning of the search mode, and the search is the slowest;

(3) Pay close attention to the position of the wildcard character. If misplaced, you may not return the data you want.

2.8 Searching with regular expressions

Regular Expressions: used to match Special strings (character sets) in text. Regular expressions are supported by all kinds of programming languages, text editors, and operating systems.

Note: MySQL only a small subset of the implementations of most regular expressions is supported.

2.8.1 Basic character Matching

Input:SELECT product_name from Products WHERE product_name REGEXP ' "ORDER by Product_Name;

meaning: It tells Mysql,regexp Followed by something as a regular expression (with the text body matches a regular expression) to be processed.

Note: like and RegExp The difference:

(1) Like matches the entire column. If the matched text appears in the column value, like will not find it, and the corresponding row will not be returned (unless wildcard characters are used);

(2) RegExp matches within the column values, and if the matched text appears in the column values, RegExp will find it and the corresponding row will be returned.

2.8.2 for or Match

Input:SELECT prod_name from Products WHERE prod_name REGEXP ' 1000|2000 ' ORDER by Prod_name;

Input:SELECT prod_name from Products WHERE prod_name REGEXP ' [123] Ton ' ORDER by Prod_name;

Meaning: [123] Defines a set of characters, meaning match 1 or 2 or 3, equivalent to 1|2|3.

2.9 Creating a calculated field

Requirement: because the data stored in the table is not directly required by the application. Therefore, the calculation needs to be performed simultaneously during the retrieval process.

2.9.1 stitching Fields (concatenate)

Input: SELECT Concat (Vend_name, ' (', Vend_country, ') ') from vendors ORDER by Vend_name;

meaning: A new post-splicing column name will be formed for concat (Vend_name, ' (', ' vend_country, ') ').

2.9.2 using aliases (alias)

Input: SELECT Concat (RTrim (Vend_name), ' (', Vend_country, ') ') as Vend_title from vendors ORDER by Vend_name;

Meaning: represents the concatenation and creates a new calculated field name Vend_title is used to store the calculated results.

2.9.3 Perform arithmetic calculations

Input: SELECT prod_id, Quantity, Item_price, Quantity*item_price as Expanded_price from OrderItems WHERE order_num = 2000;

2.10 Using data processing functions

SQL supports the following types of functions:

(1) Handling string functions;

(2) arithmetic operation on the numerical data;

(3) Process date and time values and extract specific components from these values (for example, return the difference of two dates, check the validity of dates, etc.).

2.10.1 Text Processing Functions

Input: SELECT vend_name, Upper (vend_name) as vend_name_upcase from vendors ORDER by Vend_name;

Text functions	Left/right ()	Length ()	Locate ()	Lower/upper	Ltrim/rtrim	Substring ()
Description	Returns the left/right character of a string	String length	string of substrings	lowercase/Uppercase	Remove left/Right spaces	Characters that return characters

2.10.2 date and time processing functions

In the database, dates and times are stored in the appropriate data types and in special formats, so that they can be sorted or filtered quickly and efficiently, and save physical storage space.

Table of processing functions for common date and time

Adddate/addtime ()	Add a date/time.
Curdate/curtime ()	Returns the current date/time.
Date ()	Returns the date part of the date time.
DateDiff ()	Calculates the difference of two dates.
Date_add ()	A highly flexible date arithmetic function.
Date_sub ()	Subtracts a specified time interval from a date.
Date_format ()	Returns a formatted date or time string.
Year/month/day ()	Returns the year/month/days portion of a date.
DayOfWeek ()	For a date, returns the corresponding day of the week.
Now ()	Returns the current date and time.
Hour/minute/second	Returns the hour/minute/second of a time.

In MySQL, the date must be formatted YYYY-MM-DD whether the table value is inserted or updated, or if it is filtered with a WHERE clause.

input: SELECT cust_id, order_num from orders WHERE order_date = ' 2005-09-01 ' ;

Note: If the Order_date field value of the database is stored for a time other than the date, the above retrieval will fail. Therefore, the more insured search statement is:

Input: SELECT cust_id, order_num from Orders WHERE Date (order_date) = ' 2005-09-01 ';

Input: SELECT cust_id, order_num from Orders WHERE Date (order_date) between ' 2005-09-01 ' and ' 2005-09-30 ';

Input: SELECT cust_id, order_num from Orders WHERE year (order_date) = 2005 and Month (order_date) = 9;

2.10.3 Numeric processing functions

numeric functions	Abs ()	Cos ()	EXP ()	Pi ()	Rand ()	SQRT ()
Description	Absolute	Cosine	Index	Pi	Random number	Square root

MYSQL Basic statement + advanced query