MySQL Statement overview

Database: A collection of data stored in an organized way

Tables table: A structured list of data of a particular type

Schema schema: Information about the layout and characteristics of databases and tables

Column columns: A field in a table

Data type datatype: The type of data allowed

Row row: A record in the table

Primary key PRIMARY key: A column (or set of columns) whose value uniquely distinguishes each row in a table

Keyword key word: reserved word as part of MySQL language

Wildcard wildcard: A special character used to match part of a value, a wildcard search is used for a long time, not at the beginning of the search pattern

Search pattern: Search criteria consisting of a literal value, a wildcard character, or a combination of both

Character class character class: Pre-defined character set

Fields field: Basically the same as the columns (column)

Stitching concatenate: Connecting values together to form a single value

Aliases alias: is the alternate name for a field or value, sometimes referred to as an export column derived

Aggregate function aggregate function: Functions that evaluate and return a single value on a row group

command input, or \g end, enter quit or exit Exit command line utility

1. Using CrashCourse Database

use CrashCourse;

2. Returns a list of available databases

SHOW DATABASES;

3. Get a list of tables within a database

SHOW TABLES;

4. Display table columns

SHOW COLUMNS from customers; (there is a quick way to DESCRIBE customers;)

5. Display a wide range of server status information

SHOW STATUS;

6. Retrieving data

SELECT prod_name from Products;

7, row weight, search not peer

SELECT DISTINCT vend_id from Products;

8. Limit Results

SELECT Prod_name from Products LIMIT 5;

9. Sorting Data

SELECT Prod_name from the products ORDER by Prod_name;

10, specify the sort direction, the default ascending order, descending order with DESC

SELECT Prod_id,prod_name,prod_price from Products ORDER by Prod_price DESC, Prod_name;

11. Filter data,WHERE clause,<> not equals,!= Not Equal,between, match default case-insensitive, single quote to qualify string

SELECT Prod_id,prod_name,prod_price from Products WHERE prod_price between 5 and 10; (Range values include start Value 5 and end value 10)

12. Null value Check

SELECT Prod_id,prod_name,prod_price from Products WHERE Prod_price is NULL;

13, and add multiple filter conditions OR match any condition using the parentheses grouping in operator to specify a range of conditions, each condition in the range can be matched, with a comma-delimited list of legal values, all enclosed in parentheses. function with or, execute faster than or, can contain other SELECT statements

SELECT Prod_id,prod_name,prod_price from Products WHERE vend_id in (1002,1003) ORDER by Prod_name;

14, not the negation of any conditions, can be in, between, exists clauses to negate

SELECT Prod_id,prod_name,prod_price from Products WHERE vend_id not in (1002,1003) ORDER by Prod_name;

15. like is a predicate that indicates that the search pattern followed by MySQL uses wildcard matching instead of a direct equality match to compare % to indicate any number of occurrences of any character, including 0 times, and does not match null. _ underscores match only a single character. Trailing spaces may interfere with wildcard matching. Like matches the entire column, and if the matched text appears in the column values, like will not find it

SELECT Prod_id,prod_name,prod_price from Products WHERE prod_name like 'jet% '; (Search for any word that begins with Jet)

16, regular expression, is used to match the text of the special string (character set). . (dot) is a regular expression language that matches any one character, and the following 1000 and 2000 match. RegExp matches within the column values, and if the matched text appears in the column values, RegExp will find it. Matching is case-insensitive, and the binary keyword can be used. See "Regular expressions must know"

SELECT Prod_name from Products WHERE prod_name REGEXP BINARY 'JetPack . ' ORDER by Prod_name;

17,| is the OR operator of the regular expression, which represents one of the matches. [] is another form of an or statement, the regular expression [123] ton is an abbreviation for [1|2|3] Ton

SELECT Prod_name from Products WHERE prod_name REGEXP '| | " ORDER by Prod_name;

18. Negate a character set and place a ^ at the beginning of the collection. [123] matches the character 1, 2, or 3, but [^123] matches anything except these characters. Available - to define a range [0-9] functionally equivalent to [0123456789],[a-z] matches any alphabetic character. In order to match a special character, you must use \ \ as the leading, \\-to find-the process is called escape (escaping), and \ \ is used to refer to metacharacters. \\f ,\\n ,\\r Enter,\\t tabulation,\\v longitudinal tabulation

SELECT Prod_name from Products WHERE prod_name REGEXP ' \ \ ([0-9] sticks?       \ \) ' ORDER by Prod_name; Sticks? Match stick and sticks (s)? Make S optional, because? Matches any character in front of it 0 or 1 times, no? , matching stick and sticks can be very difficult)

19,[:d igit:] matches any number, with [0-9], thus it is a set of numbers. {n} specifies a number of matches, and {4} exactly requires that the preceding characters (any number) appear 4 times. [[:d Igit:]] {4} matches any 4-bit number that is joined together. You can also write ' [0-9][0-9][0-9][0-9] ' as follows

SELECT Prod_name from Products WHERE prod_name REGEXP ' [[:d igit:]'{4}' ORDER by Prod_name;

20. To match text at a specific location, you need to use a locator. ^ The beginning of the text, the end of the$ text,[[: <:]] The beginning of the word[[:] [:]] The end of the word. Find all products that start with a number (including the number starting with the decimal point), using the locator ^

SELECT Prod_name from Products WHERE prod_name REGEXP '^[0-9\\.] ORDER by Prod_name;

21, Concat () function stitching two columns

SELECT Concat ( vend_name, ' (', Vend_country, ') ' ) from the Vendors ORDER by Vend_name;

22, RTrim () function to delete the data to the right of the extra space, LTrim () function to delete the data left extra space, and trim () remove the left

SELECT Concat ( RTrim (vend_name), ' (', RTrim (vend_country), ') ') from vendors ORDER by Vend_name;

23, alias with as keyword given

SELECT Concat (RTrim (Vend_name), ' (', RTrim (vend_country), ') ') as Vend_title from vendors ORDER by Vend_name;

24. The now () function returns the current date and time

SELECT now ();

25. Upper () convert text to uppercase

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

26. Soundex is an algorithm that converts any text string into an alphanumeric pattern describing its voice representation. Soundex considered similar pronounced characters and syllables, making it possible to compare strings in pronunciation rather than letters. The Soundex () function matches a Soundex value that sounds similar.

SELECT cust_name,cust_contact from Customers WHERE Soundex (cust_contact) = Soundex ( ' Y Lie ' c3>);

27, date must be format YYYY-MM-DD, data type is DateTime. Date () returns the datetime portion of the DateTime, Time (), and year () is a function that returns the years from a date (or datetime). Like, month () returns the month from the date

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

28. The AVG () function can be used to return the average of all columns or to return the average of a particular column or row, and the column name must be given as a function parameter. To get the average of multiple columns, you must use more than one AVG () function. The AVG () function ignores rows where the column value is null. The count () function is counted. Max () returns the maximum value in the specified column. When used with text data, Max () returns the last row if the data is sorted by the appropriate column. The min () function returns the minimum value for the specified column. When used with text data, the first row is returned. SUM () is used to return the sum (grand total) of the specified column value, or it can be used to aggregate the calculated value, which is to use +-*/to calculate the value between columns, contain only different values, specify the distinct parameter, distinct must use the column name, and cannot be used for calculations or expressions.

SELECT AVG (DISTINCT prod_price) as Avg_price from the products WHERE vend_id=1003;

29. Grouping data. The GROUP BY clause can contain any number of columns (each column must be a retrieval column or a valid expression, but not a clustered function, not an alias), which makes it possible to nest the groupings, and the data will be summarized on the last set of groupings. Each column in the SELECT statement must be given in the GROUP BY clause. With the WITH rollup keyword, you can get the values for each grouping and for each grouping summary level (for each grouping)

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

30, having a filter group, just like where filter rows. Having support for all where operators

SELECT Cust_id,count (*) as orders from orders GROUP by cust_id have COUNT (*) >= 2;

-----"MySQL must know"

MySQL Statement overview