Notes on learning MySQL grammar

osc_ 7ac8o6u9 2021-01-21 10:15:28
notes learning mysql grammar


MySQL Grammar study notes

The way to learn , Those who don't do their best can't enter ! I'm haw and haw , Welcome to my notes , If you have any questions, please discuss them .


SQL It's a structured query language , Standard language for querying relational databases , Includes a number of keywords and consistent Syntax , Convenient for database components ( surface 、 Indexes 、 Field etc. ) The establishment and manipulation of .



1.MySQL Building database

1.1 Build library statement

create database emp # Building database be known as emp
default character set utf-8 # Set the default encoding format of the library to utf-8
collate utf8_general_ci; # Set database proofing rules . Case insensitive 

Example utf8_bin Store every character in the string in binary data , Case sensitive .utf8_genera_ci Case insensitive ,ci by case insensitive Abbreviation , That is, the case is not sensitive .utf8_general_cs Case sensitive ,cs by case sensitive Abbreviation , That is, case sensitivity .

1.2 Delete Library

drop database emp;

2. Build table

2.1 Form template

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;

2.2 Primary key (PRIMARY KEY)

The primary key value must be unique : Each row in a table must have a unique primary key value .
If the primary key USES a single column , Its value must be unique .
What we've seen so far CREATE TABLE The examples all use a single column as the primary key .

PRIMARY KEY (cust_id)

If you use multiple columns , Then the combined values of these columns must be unique .
Create a primary key with multiple columns , Column names should be given in a comma separated list .

PRIMARY KEY (order_num,order_item)

Can only be used in primary key, not allowed NULL Columns of values , allow NULL The column of the value cannot be used as a unique identity .

2.3AUTO_INCREMENT

AUTO_INCREMENT Define properties that are listed as self incrementing , Generally used for primary key , One at a time INSERT In operation , The value will be added automatically 1
The simplest number to use is the next number , The next number is the number greater than the current maximum number . for example , If cust_id The maximum number of is 10005, Then insert the next customer in the table , It can be equal to 10006 Of cust_id .

2.4 The default value is

If no value is given when inserting a row ,MySQL Allows you to specify the default value to use at this time . The default value is use CREATE TABLE In the column definition of the statement DEFAULT Keyword assignment

2.5 Storage engine (ENGINE=InnoDB)

And others DBMS equally ,MySQL There is an internal engine that specifically manages and processes data .
In your use CREATE TABLE When the sentence is , The engine specifically creates tables .
And when you use SELECT sentence Or other database processing , The engine processes your request internally .
Most of the time , This engine All hidden in DBMS Inside , There's no need to pay too much attention to it .


Here are a few engines to know :

  • InnoDB Is a reliable transaction engine , It doesn't support full-text search ;

  • MEMORY In function equivalent to MyISAM, But because the data is stored in memory ( Not disk ) in , fast ( Especially suitable for temporary tables );

  • MyISAM It's a very high performance engine , It supports full-text search but not transaction processing .

2.6 ALTER TABLE sentence

ALTER TABLE Statement is used to add... To an existing table 、 Delete or modify Columns .

-- Add columns to the table 
ALTER TABLE customers
ADD cust_phone char(10)
-- Delete the columns in the table ( Please note that , Some database systems do not allow this way of deleting columns in database tables )
ALTER TABLE customers
DROP COLUMN cust_phone
-- Change the data type of the column in the table 
-- The default value of the modified quantity field is 80
ALTER TABLE customers
MODIFY COLUMN quantity int(11) null default 80 after prod_id

2.7 Delete table

DROP TABLE customers

3.SQL The basic method of processing data

3.1 data retrieval (select sentence )

Select Sentence syntax
Select Retrieve all columns
Select Retrieve a single column
Select Retrieve different lines
Select The results of the test limit



# Description table 
DESC products;
# select sentence Retrieve all columns 
SELECT * FROM products;
# select sentence Retrieve multiple columns specified 
SELECT prod_id,prod_name,prod_price FROM products;
# select sentence Retrieve a specified column 
SELECT vend_id FROM products;
# duplicate removal ,DISTINCT Keywords are used to return unique different values 
SELECT distinct vend_id FROM products;
# The first 1 OK, let's start , Take before 3 That's ok 
SELECT * FROM products limit 3;
# The first 4 OK, let's start , take 3 That's ok 
SELECT * FROM products limit 3,3;

3.2 Data sorting

ORDER BY Keywords are used to sort the result set by one or more columns .
ORDER BY By default, the keyword sorts the records in ascending order . If you need to sort records in descending order , have access to DESC keyword .

# Let's take a look at this time products surface 
SELECT * from products;
# Sorting of individual fields Ascending Descending 
SELECT prod_id,prod_price from products
ORDER BY prod_price;
# Default (ASC) It's in ascending order ,desc It's in descending order , Default can not write 
SELECT prod_id,prod_price from products
ORDER BY prod_price desc;
# Default sort a-z , The reverse order is z-a 
SELECT prod_id,prod_price from products
ORDER BY prod_name desc;
# Multi field sorting 
SELECT prod_name,prod_price from products
ORDER BY prod_price,prod_name;
# Find the cheapest product 
SELECT prod_name,prod_price from products
ORDER BY prod_price LIMIT 1;
# Find the most expensive products 
SELECT * from products
ORDER BY prod_price DESC LIMIT 1; 

3.3 Data filtering

Database tables generally contain a large amount of data , It's rarely necessary to retrieve all the rows in a table .
Usually only a subset of table data is extracted according to the needs of a specific operation or report .
To retrieve only the required data, you need to specify search criteria (search criteria)
Search criteria are also called filter criteria (filter condition).
stay SELECT In the sentence , The data is based on WHERE Filter the search criteria specified in clause .
WHERE Clause in table name (FROM Clause ) Then give .




3.3.1 Use Where Clause

Check individual values
Mismatch check

# The screening price is 2.5 Products 
select * from products where prod_price = 2.5;
# The screening price is 2.5 Products , And display only specific fields 
select prod_name,prod_price from products
where prod_price = 2.5;
# WHERE Conditional operators :=、>、<、>=、<=、<>/!=、between * and *( Include the beginning and the end )
select prod_name,prod_price from products
where prod_price >= 2.5 and prod_price <10
order by prod_price;
select prod_name,prod_price from products
where prod_price BETWEEN 2.5 and 10
order by prod_price;

practice :
Find out if the price is lower than 10 Yuan's products
Find out the price is not the supplier 1003 Manufactured products
Find the supplier 1001,1003 Manufactured products


select prod_name,prod_price from products
where prod_price < 10
order by prod_price;
select vend_id,prod_name,prod_price from products
where vend_id != 1003
order by prod_price;
select vend_id,prod_name,prod_price from products
where vend_id = 1001 or vend_id = 1003;
order by prod_price;

3.3.2 Null check

select * from products
where prod_desc is null

3.3.3 And、Or、In、Not Operations and their order of calculation

  • AND & OR Filter the record based on more than one condition .
  • If the first condition and the second condition hold , be AND Operator to display a record .
  • If only one of the first and second conditions holds , be OR Operator to display a record .
  • First and Execute after statement or sentence
# where One of the combined clauses and operation ( intersection )
select * from products where vend_id=1003 and prod_price <=10
ORDER BY prod_price;
# where One of the combined clauses or operation ( Combine )
select * from products where vend_id=1003 or vend_id =1002;
# where One of the combined clauses AND and OR Combined use of ( Use parentheses to make complex expressions )
SELECT * FROM products
WHERE prod_price <=10
AND (vend_id=1003 or vend_id =1002);
  • IN The operator is allowed in WHERE clause Specify multiple values
  • ‘=’ Set a value
# where One of the combined clauses in operation 
select * from products where
vend_id in (1001,1005,1002);
# where One of the combined clauses in And = Transformation 
select * from products where
vend_id = 1001 or vend_id = 1005 or vend_id = 1002;
# where One of the combined clauses not operation 
select * from products where
vend_id not in (1002);
  • not operation
# where One of the combined clauses not operation 
select * from products where
vend_id not in (1002);

3.3.4 wildcard ( Fuzzy matching )

  • Wildcards can be used to replace any other character in a string .
  • % replace 0 Characters or more
  • “_” replace 1 Characters .
  • LIKE The operator is used in WHERE Search for the specified pattern in the column
# Find out jet The first product 
select prod_id ,prod_name from products
where prod_name like 'jet%'

 Insert picture description here

select prod_id ,prod_name from products
where prod_name like '%anvil%'
select prod_id ,prod_name from products
where prod_name like 's%e'
select prod_id ,prod_name from products
where prod_name like '_ ton anvil'
select prod_id ,prod_name from products
where prod_name like '__ ton anvil'

Be careful :

  • Don't overuse wildcards , If other operators do the same thing , Priority should be given to other operators .
  • When not necessary , Don't use wildcards at the beginning of a search pattern , Because this is the slowest way to search .
  • Pay close attention to the location of wildcards , If you put it in the wrong place, you may not return the data you want .

4.SQL Advanced methods of processing data

4.1 Regular expressions

Regular expressions Compare a special set of characters to a text string , Filter and retrieve the desired data

  • Regular expressions are special strings used to match text ( Character set )
  • If you want to extract a phone number from a text file , You can use regular expressions .
  • If you need to look up all the files with numbers in the middle of their names , You can use one Regular expressions .
  • If you want to find all the repeated words in a block of text , You can use a A regular expression .
  • If you want to replace all in a page URL For these URL Reality HTML link , You can also use a regular expression ( For the last example , Or two regular expressions ).

4.1.1 Basic string matching

# Use like Keywords and wildcards % 
select prod_name from products
where prod_name like '%1000';
# Using regular expressions REGEXP
select prod_name from products
where prod_name REGEXP '1000';
# You can retrieve prod_name All of them contain ‘1000’ The line of 

 Insert picture description here

4.1.2 Special characters “ .” Use

“ .” In a regular expression, it means to match any One character

# Use like Keywords and wildcards _
select prod_name from products
where prod_name like 'JetPack _000';
# Using regular expressions REGEXP And special characters “.”
select prod_name from products
where prod_name REGEXP '.000';

 Insert picture description here

  • Would anyone be surprised here ,“.” It means to match any character , But it turns out that JetPack 1000、JetPack
    2000,000 There's more than one character in front of , How can it match like this ?
  • Understand this sentence well “ Regular expressions are special strings used to match text ”
    You'll find that with regular expressions, we can match all the lines in the corresponding fields that contain the text that needs to be matched . What we need to match in the example is ‘.000’,.
    Can represent any character , So we put JetPack 1000、JetPack 2000
    It's a match ,1000,2000 It's the text we want to match . No matter whether there are other characters before and after them, they will be queried .


  • contrast keyword like The combination with wildcards can't achieve this effect .

4.1.3 Use or Match ( Matching conditions )

this or It's not true or , Instead, use vertical lines “|” Represent search One of two matching text strings

# retrieval prod_name All of them contain ‘1000’ perhaps ‘2000’ The line of 
select prod_name from products
where prod_name REGEXP '1000|2000';

 Insert picture description here
Multiple... Can be used in regular expressions or Conditions

select prod_name from products
where prod_name REGEXP '1000|2000|anvil';

 Insert picture description here

4.1.4 [] matching

”[]“ Representatives need to match [] Any character contained in

4.1.4.1 Match one of several characters

select prod_name from products
where prod_name REGEXP '[12] ton';

Here we use regular expressions [12] ton . [12] Defines a set of characters 1,2; Can match 1 ton perhaps 2 ton.
Actually ,[] It's another form of or sentence

4.1.4.2 Match range

[] matching [] Any character contained in
[0-9] matching 0 To 9 Any numeric character of
[1-3] matching 1 To 3 Any numeric character of
[6-9] matching 6 To 9 Any numeric character of
[a-z] matching a To z Any alphabetic character of



select prod_name from products
where prod_name REGEXP '[1-5] ton';
# matching 1 To 5 Any number , So it goes back 1 ton、2 ton、5 ton

 Insert picture description here

4.1.4.3 Exclusive [^]

  • [^] The match is not included in [] Any character in . namely , Will match anything but the specified character .
  • Place a... At the beginning of the collection ^
  • for example ,[^12], It will match and divide 1,2 Anything outside
select prod_name from products
where prod_name REGEXP '[^345] ton'
# There will be no 3 ton、4 ton、5 ton

 Insert picture description here

4.1.5 Special character matching (. [] | -)

Match special character (. [] | -) when , Need to use Escape character ( Two backslashes \\)
\\- Expression lookup -,\\. Expression lookup .

select prod_name from products
where prod_name REGEXP '\\.';
# lookup .

 Insert picture description here
\\ Also used to refer to metacharacters ( A character with a special meaning )

Metacharacters explain
\\f Change the page
\\n Line break
\\r enter
\\t Tabulation
\\v Vertical tabulation

4.1.6 Match character class

[:digit:] Arbitrary number ( Same as [0-9])
[:alnum:] Any letter and number ( Same as [a-zA-Z0-9])
[:alpha:] Any letter ( Same as [a-zA-Z])
[:lower:] Any lowercase letter ( Same as [a-z])
[:upper:] Any capital letter ( Same as [A-Z])



select prod_name from products
where prod_name REGEXP '[[:digit:]]{4}'

4.1.7 Match multiple instances , Repeat metacharacter

Metacharacters explain
* 0 One or more matches
+ 1 One or more matches ( Same as {1,})
? 0 Or 1 A match ( Same as {0,1})
{n} A specified number of matches
{n,} No less than the specified number of matches
{n,m} The range of the number of matches (m No more than 255)
# Match any connected 4 Rows of digits 
select prod_name from products
here prod_name REGEXP '[[:digit:]]{4}';
select prod_name from products
where prod_name REGEXP '[0-9][0-9][0-9][0-9]';

 Insert picture description here

select prod_name from products
where prod_name REGEXP '\\([0-9] sticks?\\)';
# \\( 、\\) Use escape characters ,[0-9] matching 1 To 9 Arbitrary number 
#s? in Use ? Express s Can appear 0 Time or 1 Time , So there are results stick、sticks

 Insert picture description here

  • ? Match any character before it 0 Time or 1 time , let me put it another way ,? The preceding characters have (1 individual ) Or not (0 individual ) Can be matched

4.1.8 Locator , Metacharacters

Metacharacters explain
^ Start of text
$ End of text
[[:<:]] The beginning of words
[[:>:]] Ending of words
select prod_name from products
where prod_name REGEXP '^[\\.]'

 Insert picture description here
ad locum , We can review the exclusivity [^ ]
To sum up ^ Use of :
1. In the assembly [^], Represents the characters contained in the mismatched set
2. Matches the start of the input string .



4.2 Calculated field

4.3 MySQL Data processing functions

4.4 Data aggregation

4.5 The data packet

4.6 Subquery

4.7 Join table

4.8 Advanced join table

5.SQL Advanced data query

5.1 Combination query

5.2 Full text search

5.2 insert data

5.3 Update and delete data

5.4 View

5.5 stored procedure

5.6 The cursor

5.7 trigger

版权声明
本文为[osc_ 7ac8o6u9]所创,转载请带上原文链接,感谢
https://javamana.com/2021/01/20210121100046646N.html

  1. 【计算机网络 12(1),尚学堂马士兵Java视频教程
  2. 【程序猿历程,史上最全的Java面试题集锦在这里
  3. 【程序猿历程(1),Javaweb视频教程百度云
  4. Notes on MySQL 45 lectures (1-7)
  5. [computer network 12 (1), Shang Xuetang Ma soldier java video tutorial
  6. The most complete collection of Java interview questions in history is here
  7. [process of program ape (1), JavaWeb video tutorial, baidu cloud
  8. Notes on MySQL 45 lectures (1-7)
  9. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  10. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  11. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  12. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  13. 【递归,Java传智播客笔记
  14. [recursion, Java intelligence podcast notes
  15. [adhere to painting for 386 days] the beginning of spring of 24 solar terms
  16. K8S系列第八篇(Service、EndPoints以及高可用kubeadm部署)
  17. K8s Series Part 8 (service, endpoints and high availability kubeadm deployment)
  18. 【重识 HTML (3),350道Java面试真题分享
  19. 【重识 HTML (2),Java并发编程必会的多线程你竟然还不会
  20. 【重识 HTML (1),二本Java小菜鸟4面字节跳动被秒成渣渣
  21. [re recognize HTML (3) and share 350 real Java interview questions
  22. [re recognize HTML (2). Multithreading is a must for Java Concurrent Programming. How dare you not
  23. [re recognize HTML (1), two Java rookies' 4-sided bytes beat and become slag in seconds
  24. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  25. RPC 1: how to develop RPC framework from scratch
  26. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  27. RPC 1: how to develop RPC framework from scratch
  28. 一次性捋清楚吧,对乱糟糟的,Spring事务扩展机制
  29. 一文彻底弄懂如何选择抽象类还是接口,连续四年百度Java岗必问面试题
  30. Redis常用命令
  31. 一双拖鞋引发的血案,狂神说Java系列笔记
  32. 一、mysql基础安装
  33. 一位程序员的独白:尽管我一生坎坷,Java框架面试基础
  34. Clear it all at once. For the messy, spring transaction extension mechanism
  35. A thorough understanding of how to choose abstract classes or interfaces, baidu Java post must ask interview questions for four consecutive years
  36. Redis common commands
  37. A pair of slippers triggered the murder, crazy God said java series notes
  38. 1、 MySQL basic installation
  39. Monologue of a programmer: despite my ups and downs in my life, Java framework is the foundation of interview
  40. 【大厂面试】三面三问Spring循环依赖,请一定要把这篇看完(建议收藏)
  41. 一线互联网企业中,springboot入门项目
  42. 一篇文带你入门SSM框架Spring开发,帮你快速拿Offer
  43. 【面试资料】Java全集、微服务、大数据、数据结构与算法、机器学习知识最全总结,283页pdf
  44. 【leetcode刷题】24.数组中重复的数字——Java版
  45. 【leetcode刷题】23.对称二叉树——Java版
  46. 【leetcode刷题】22.二叉树的中序遍历——Java版
  47. 【leetcode刷题】21.三数之和——Java版
  48. 【leetcode刷题】20.最长回文子串——Java版
  49. 【leetcode刷题】19.回文链表——Java版
  50. 【leetcode刷题】18.反转链表——Java版
  51. 【leetcode刷题】17.相交链表——Java&python版
  52. 【leetcode刷题】16.环形链表——Java版
  53. 【leetcode刷题】15.汉明距离——Java版
  54. 【leetcode刷题】14.找到所有数组中消失的数字——Java版
  55. 【leetcode刷题】13.比特位计数——Java版
  56. oracle控制用户权限命令
  57. 三年Java开发,继阿里,鲁班二期Java架构师
  58. Oracle必须要启动的服务
  59. 万字长文!深入剖析HashMap,Java基础笔试题大全带答案
  60. 一问Kafka就心慌?我却凭着这份,图灵学院vip课程百度云