MySQL SQL Learning-the first chapter

During the learning process, please tap to spray. Write with Markdown, incredibly no format, do not know why.

# 1.distinct
SELECT distinct user_name from Cwd_user;
go to heavy, not partially use distinct, search for all rows

# 2.limit
select * from user_mapping limit 2,3;
limit 2,3:2 means that MySQL returns 3 rows starting from line 2, excluding the 2nd row itself.

# 3.order by
Select user_name from Cwd_user order by user_name;
#排序
desc
#降序
Select id,user_name,email_address from Cwd_user order by id desc;
Select id,user_name,email_address from Cwd_user order by user_name Desc,email_address;
#desc关键字只能应用到直接位于其前面的列名
ASC (Ascending)
#升序
Select id,user_name,email_address from Cwd_user order by user_name Asc,email_address;
#PS: The default is ascending, and if DESC,ASC is not specified, ASC is assumed
Select id,user_name,email_address from Cwd_user ORDER BY id desc LIMIT 1;
#取出Id列最大值
#区分大小写与排序顺序: In MySQL, for the text nature data, the default A and a are the same, if you need to sort, you must make additional settings.

# 4. where #
Select id,user_name,email_address from Cwd_user where user_name= ' liubin ';
#操作符 = <>! = < <= > >= between
#between 4 and 10 from low to high value, and keyword delimited

# 5. Null #
Select id,user_name,email_address from cwd_user where email_address is null;
#表示返回没有email_address的行, null (no value), which is not the same as a field containing 0, an empty string, or a space that contains only spaces

# 6. And, or#
Select id,user_name,email_address from Cwd_user where ID < 9666589 and user_name < ' F ';
#满足所有给定的条件的行
Select id,user_name,email_address from Cwd_user where ID < 9666589 or user_name < ' F ';
#满足任一一个条件的行
#PS: And and Or,and precedence calculations, in general, use () to explicitly group operators

# 7. In #
Select id,user_name,email_address from Cwd_user where ID in (425992,425993) Order by user_name Desc;
The keyword used in the #where clause to specify a list of matching values, with or equivalent

# 8. Not #
Select id,user_name,email_address from Cwd_user where ID not in (425992,425993) Order by user_name Desc;
#where子句中用来否定后跟条件的关键字

# 9. Wildcard characters #
# 1) Like # #
Select id,user_name,email_address from Cwd_user where user_name like ' b% ';

# # 2)% percent # # #
#表示任何字符出现的任意次数, case-sensitive, such as jaa%
#尾空格可能会影响通配符匹配, you can append a percentage at the end, such as%jaa%. or use other functions to remove trailing spaces
#NULL, unable to match

# # 3) _ Underline # #
#表示只匹配一个字符

# 4) Using wildcard tricks # #
#不要过度使用通配符
#使用通配符时, try not to use it at the beginning of the search mode, or it will be slow
#仔细注意通配符的位置, if misplaced, you will not be able to return the correct data

# 10. Regular Expressions #
regexp ' expression '
# 1) analytic regexp and like
Select Prod_name from Products where prod_name like ' n ';
Empty Set (0.00 sec)
Select Prod_name from Products where prod_name regexp ';
+--------------+
| prod_name |
+--------------+
    | JetPack |
+--------------+
1 row in Set (0.00 sec)
#Like匹配整个列. The matched value appears in the column value (for example, aaa1000), and like does not return it unless a wildcard character is used.
#regexp在列值内进行匹配. The matched value appears in the column values (for example, aaa1000), and RegExp returns.

Regular expressions
#

Select Prod_name from Products where prod_name regexp ';
# ^1000$ means beginning with 1, 0 ending
Select Prod_name from Products where Prod_name regexp '.
# = matches any character
Select Prod_name from Products where prod_name regexp binary ' JetPacK.
# binary is case-sensitive
# [[: <:]] The beginning of the word
# [[::;]] The end of the word


# 2) or # #
Search for one of two strings, you can use |
#
Select Prod_name from Products where prod_name regexp ' 1000|2000 ';
# Query for rows that contain 1000 or 2000
Select Prod_name from Products where prod_name regexp ' [123] ton ';
# [^123] matches all lines starting with
Select Prod_name from Products where Prod_name regexp ' 1|2|3 ton (not added) ';
# Match a line of one of several characters

# 3) matching range # #
Select Prod_name from Products where prod_name regexp ' [1-5] ';
# matches all rows containing 1 to 5
Select Prod_name from Products where prod_name regexp ' [1-5] ton ';
# matches all lines beginning with 1-5 lines
# 
Ps:

special character escapes, must be preceded by \ \. For example, \\-represents query-,\\. Represents a query.
\\f Page Change
\\n Line Break
\\r Enter
\\t watchmaking
\\v longitudinal watchmaking

match character class, predefined character set
[: alnum:]
[: Alpha:]
[: blank:]
[: Cntrl:]
[:d igit:]
    ... ....

# # 4) matches multiple occurrences # #
* 0 or more matches
+ 1 or more matches (equals {1,})
? 0 or more matches (equals {0,1})
{n} specified number of matches
{n,} not less than a specified number of matches
{n,m} matches the number of ranges (m, not more than 255)


# 11. calculated Field #
# # # 1) splicing Fields # # #
concat (): This function is used to stitch two columns
Select Concat (vend_name, ' {', Vend_country, '} ') from vendors order by Vend_name;
#拼接串, which is to put multiple strings

# # # 2) go to the Wardrobe line trailing space # # #
RTrim () Right
LTrim () left
Trim () both sides
Select Concat (Trim (vend_name), ' {', Vend_country, '} ') from vendors order by Vend_name;

# # # 3) alias # # #
As : An alternate name for a field or value, given as a keyword
Select Concat (Trim (vend_name), ' {', Vend_country, '} ') as Vend_title from vendors order by Vend_name;

# # # 4) perform arithmetic calculations # # #
+ - * /

    

This article is from the "Rise" blog, please be sure to keep this source http://binbinwudi8688.blog.51cto.com/3023365/1736699

MySQL SQL Learning-the first chapter