<> catalogue

Part I : Basics —— Add, delete, check and modify
【 Chapter I 】 make preparation Getting Started ( duration 25 minute )
【 Chapter II 】 Retrieving data in a single table Retrieving Data From a Single Table ( duration 53 minute )
【 Chapter III 】 Retrieving data from multiple tables Retrieving Data From Multiple Tables ( duration 1 hour 2 branch )
【 Chapter IV 】 insert , Update and delete data Inserting, Updating, and Deleting Data ( duration 42 minute )

Part II : Basic advanced —— Summary , Complex query , Built in function
【 Chapter V 】 Summary data Summarizing Data ( duration 33 minute )
【 Chapter VI 】 Write complex queries Writing Complex Query ( duration 45 minute )
【 Chapter VII 】MySQL Basic function of Essential MySQL Functions ( duration 33 minute )

Part III : increase of efficiency —— view , stored procedure , function
【 Chapter VIII 】 view Views ( duration 18 minute )
【 Chapter IX 】 stored procedure Stored Procedures ( duration 48 minute )

Part IV : High level theme —— trigger , event , affair , Concurrent
【 Chapter 10 】 Triggers and events Triggers and Events ( duration 22 minute )
【 Chapter XI 】 Transaction and concurrency Transactions and Concurrency ( duration 49 minute )

Part V : talent showing itself —— data type , Design database , Indexes , protect
【 Chapter 12 】 data type Data Types ( duration 35 minute )
【 Chapter 13 】 Design database Designing Databases ( duration 1 Time 30 branch )
【 Chapter 14 】 Efficient indexing Indexing for High Performance ( duration 58 minute )
【 Chapter 15 】 Protect database Securing Databases ( duration 20 minute )

<>【 Chapter I 】 make preparation

<> What is? SQL

* A database is a collection of data stored in an accessible format
* To manage our database We use a system called database management system (DBMS, Database Management
System) Software for . We connect to a DBMS Then issue instructions to query or modify data ,DBMS Will execute our instructions and return the results
* There are relational and non relational databases , In more popular relational databases , We store data in a database that is related to each other through certain relationships
In the data sheet , Each table stores a specific type of data , This is the origin of relational database name .( as : Customer table through customer id Link to order form , The order form passes through the goods id Link to commodity list )
* SQL(Structured Query Language, Structured query language ) It's specially used to deal with ( Including query and modification ) Standard language for relational database
* Syntax of different relational database management systems (MySQL,SQL
Server,Oracle) Slightly different , But it's all based on standards SQL, This lesson uses the most popular open source relational database management system ,MySQL
<>【 Chapter II 】 Retrieving data in a single table

<> Selection clause

SELECT Yes column / Field selection statement , Selectable columns , Mathematical expression between columns , Specific value or text , available AS Keyword set column alias (AS Can be omitted ), be careful DISTINCT Use of keywords .

be careful

SQL Will completely ignore case ( Case in most cases ), Extra space ( More than one space ), Indent and wrap ,SQL Statements are completely separated by semicolons ;
division , Indent with , Line breaks are just to make the code look more beautiful and the structure clearer , These and Python Very different , it is to be noted that .

example

USE sql_store;

SELECT
DISTINCT last_name,
– The selected field is divided in the middle DISTINCT How to filter ?
first_name,
points,
(points + 70) % 100 AS discount_factor/‘discount factor’
– % Surplus ( Take mold )
FROM customers
practice

Unit price increase 10% As new unit price

SELECT
name,
unit_price,
unit_price * 1.1 ‘new price’
FROM products
As shown in the above example , When aliasing ,AS Can save , Just a space followed by an alias , Can be regarded as SQL The column variable and the first space after its mathematical operation are recognized as AS

<>WHERE clause

WHERE Is row filter criteria , It's actually line by line / One record after another to verify whether it meets the conditions , Filter

Navigation

3~9 This section is all about writing WHERE Different ways of expressing filter conditions in Clause , This section ( The first 3 section ) It mainly focuses on comparative operation , The first 4 Logical operation
AND,OR,NOT,5~9 It can be seen as talking about special comparison operations ( Whether certain conditions are met ):IN,BETWEEN,LIKE,REGEXP,IS NULL.

So in general WHERE The condition is mathematics → compare → Logical operation , The logical hierarchy and execution priority are also in the order of these three .

example

USE sql_store;

SELECT *
FROM customers
WHERE points > 3000
/WHERE state != ‘va’ – ‘VA’/'va’ equally
Comparison operator > < = >= <= !=/<> , Note that it's an equal sign, not two equal signs

You can also compare dates or text , be careful SQL The standard way of writing the date in and its need to wrap it in quotation marks

WHERE birth_date > ‘1990-01-01’

<>AND, OR, NOT operator

USE sql_store;

SELECT *
FROM customers
WHERE birth_date > ‘1990-01-01’ AND points > 1000
/WHERE birth_date > ‘1990-01-01’ OR
points > 1000 AND state = ‘VA’
AND Priority over OR, But it's better to put parentheses , Clearer

WHERE birth_date > ‘1990-01-01’ OR
(points > 1000 AND state = ‘VA’)
NOT Usage of

WHERE NOT (birth_date > ‘1990-01-01’ OR points > 1000)
De bracket equivalent conversion to

WHERE birth_date <= ‘1990-01-01’ AND points <= 1000

<>IN operator

use IN Operator combines a property with multiple values ( A series of values ) Compare
The essence is the simplification of multiple equal comparison operation conditions

case

elect ’va’,‘fl’,'ga’ Customers in three states

USE sql_store;

SELECT * FROM customers
WHERE state = ‘va’ OR state = ‘fl’ OR state = ‘ga’
No state = ‘va’ OR ‘fl’ OR ‘ga’ Because mathematical and comparative operations take precedence over logical operations , Bracketed state = (‘va’ OR ‘fl’ OR
‘ga’) Neither , Logical operators can only link Boolean values .

use IN Operators simplify the condition

WHERE state IN (‘va’, ‘fl’, ‘ga’)
coca NOT

WHERE state NOT IN (‘va’, ‘fl’, ‘ga’)
Available here NOT Reasons for : You can see it this way ,IN sentence IN (‘va’, ‘fl’, ‘ga’)
Is making a judgment on whether it meets the conditions , It can be regarded as a special comparison operation , What you get is a logical value , Therefore available NOT Reverse

practice

The inventory is just 49,38 or 72 Products

USE sql_store;

select * from products
where quantity_in_stock in (49, 38, 72)

<>BETWEEN operator

Used to express range conditions

be careful

use AND Not parentheses
Closed interval , Include both end points
Can also be used for dates , After all, dates are also numerical in nature , Dates also have sizes ( Sooner or later ), Comparable operation
with IN equally ,BETWEEN The essence is also a specific Multiple comparison operation conditions Simplification of
case

Select points in 1k reach 3k Our customers

USE sql_store;

select * from customers
where points >= 1000 and points <= 3000
Equivalent simplified as :

WHERE points BETWEEN 1000 AND 3000
Note that both ends are included Can't write BETWEEN (1000, 3000)! Don't talk to IN I'm confused about the way I write

practice

elect 90 Post Customer

SELECT * FROM customers
WHERE birth_date BETWEEN ‘1990-01-01’ AND ‘2000-01-01’

<>LIKE operator

fuzzy search , Find a record of a string with a pattern / that 's ok

be careful

Outdated usage ( But sometimes it's easier to use , Later, I found that it seems to be used more ), Regular expressions in the next lesson are more flexible and powerful
Note that like regular expressions, strings are wrapped in quotation marks
USE sql_store;
SELECT * FROM customers
WHERE last_name like ‘brush%’ / ‘b____y’
Describe the desired string pattern within quotation marks , be careful SQL( almost ) Case insensitive in all cases

Two wildcards :

% Any number ( include 0 individual ) Character ( Use more )
_ Single character
practice

Select customers who meet the following conditions :

*
Address contains ‘TRAIL’ or ‘AVENUE’

*
Phone number in 9 end

USE sql_store;

select *
from customers
where address like ‘%Trail%’ or
address like ‘%avenue%’
LIKE Execution priority after logical operator , after all IN BETWEEN LIKE
The essence can be regarded as the simplification of comparison operators , It should be at the same level as the comparison operation , mathematics → compare → logic , Always remember this order , The above one is much simpler if regular expressions are used

where phone like ‘%9’
/where phone not like ‘%9’
LIKE Your judgment is also a TRUE/FASLE Question of , Any logical value / Brin values can be prefixed NOT To take the opposite

<>REGEXP operator

regular expression , More powerful in searching strings , More complex templates can be searched

example

USE sql_store;

select * from customers
where last_name like ‘%field%’
Equivalent to :

where last_name regexp ‘field’
regexp yes regular expression( regular expression ) Abbreviation of

Regular expressions can be combined to express more complex string patterns

where last_name regexp ‘^mac|field$|rose’
where last_name regexp ‘[gi]e|e[fmq]’ – Find with ge/ie or ef/em/eq of
where last_name regexp ‘[a-h]e|e[c-j]’

Regular expression summary :

Symbol significance
^ start
$ ending
[abc] contain abc
[a-c] contain a reach c
| logical or
( Regular expression usage and more , Check it yourself )

practice

Select customers who meet the following conditions :

*
first names yes ELKA or AMBUR

*
last names with EY or ON end

*
last names with MY start Or include SE

*
last names contain BR or BU

select *
from customers
where first_name regexp ‘elka|ambur’
/where last_name regexp ‘ey ∣ o n |on ∣on’
/where last_name regexp ‘^my|se’
/where last_name regexp ‘b[ru]’/‘br|bu’

<>IS NULL operator

Find null value , Find some missing attributes

case

Find customers with missing phone numbers , Maybe send an email to remind them or something

USE sql_store;

select * from customers
where phone is null/is not null
Note that IS NULL and IS NOT NULL here NOT Not preceded by brin value , But more in line with English grammar be After verb

practice

Find orders that haven't been shipped yet ( Common query requirements of online mall administrators )

USE sql_store;

select * from orders
where shipper_id is null
review

3~9 The festival is all about WHERE Specific writing of conditions in Clauses :

The first 3 section : Comparison operation > < = >= <= !=
The first 4 section : Logical operation AND,OR,NOT
5~9 section : Special comparison operation ( Whether certain conditions are met ):IN and BETWEEN,LIKE and REGEXP,IS NULL
So in general WHERE The condition is

Mathematical operation → Comparison operation ( Including special comparison operations )→ Logical operation
The logical hierarchy and execution priority are also in the order of these three .

<>ORDER BY clause

Sort statement , and SELECT …… Very similar :

Multi column
It can be a mathematical expression between columns
Any column can be included , Include unselected columns (MySQL characteristic , other DBMS Possible error reporting ),
It can be a previously defined alias column (MySQL characteristic , It can even be a column alias set with a constant )
Any sort by column can be added after it DESC
be careful

Better not use it ORDER BY 1, 2( Express with SELECT …… Select the second column in the column 1,2 Column as sorting basis )
This implicit basis , because SELECT When the selected column changes, it is easy to make mistakes , It's better to write the column name explicitly as the sorting basis

notes :workbench The middle wrench icon opens the design mode of the table , View or modify the columns in the table ( attribute ), You can see who is the primary key . If the sorting statement is omitted, it will be sorted by primary key by default
example

USE sql_store;

select name, unit_price * 1.1 + 10 as new_price
from products
order by new_price desc, product_id
– These two are Don't list and No columns selected , It's all used MySQL characteristic
practice

order 2 The goods are listed in descending order of total price :

method 1. The ranking can be based on the mathematical expression of the total price

select * from order_items
where order_id = 2
order by quantity * unit_price desc
– Mathematical expression between columns
method 2. Or define the total price alias first , Sort by alias

select *, quantity * unit_price as total_price
from order_items
where order_id = 2
order by total_price desc
– Column alias

<>LIMIT clause

Limit the number of records that return results ,“ front N individual ” or “ skip M Front after N individual ”

example

USE sql_store;

select * from customers
limit 3 / 300 / 6, 3
6, 3 Indicates before skipping 6 individual , Take the first 7~9 individual ,6 Is the offset ,
as : Page paging each 3 A record displays one page The first 3 The record that the page should display is limit 6, 3

practice

Find out the top three diehard fans

USE sql_store;

select *
from customers
order by points desc
limit 3
review

SELECT The sentence is over , The order of the clauses inside is fixed, and you should remember it well , Wrong sequence will be reported
select from where + order by limit
Vertical selection column , Determination table , Horizontal line selection ( The writing and combination of various conditions should be clear and familiar ), Finally, sort and limit

<>【 Chapter III 】 Retrieving data from multiple tables

Technology
©2019-2020 Toolsou All rights reserved,
C++ of string of compare usage MySQL Basics Commonly used sentence ( Add / delete / modify query )C Language of a Gobang game implementation QT5.9 Use of learning notes QSqlQuery Method of Qt Getting Started tutorial 【 Basic controls 】QCalendarWidget calendar control java Polymorphic array of web Front end signature plug-in _signature_pad Plug in implements electronic signature function centos7 install RabbitMqspringboot use redis Experiment 4 Automated test tools - software test