Skip to content
Search or jump to…

Pull requests
Issues
Marketplace
Explore

@zhangzixuan1997
Learn Git and GitHub without any code!
Using the Hello World guide, you’ll start a branch, write comments, and open a pull request.

1
00zhangzixuan1997/MySQL-Knowledge-Points
Code Issues 0 Pull requests 0 Projects 0 Wiki Security Insights Settings
MySQL-Knowledge-Points
/
Name your file…

100
SELECT p1.productVendor, productCode, productName, quantityInStock, MaxInventory
101
FROM Products AS p1 JOIN
102
(SELECT productVendor, MAX(QuantityInStock) AS MaxInventory
103
FROM Products GROUP BY productVendor )AS p2
104
ON p1.productVendor = p2.productVendor
105
WHERE p1.quantityInStock = p2.MaxInventory
106

IF we need distinct productVendor

107

As 要放在整个语句之后

108
select c1/c2 as turn, productVendor
109
from (
110
(select sum(quantityOrdered) as c1, productVendor
111
from orderdetails left join products using (productCode) group by productVendor) as a
112
join
113
(select sum(quantityInStock) as c2, productVendor from products group by productVendor) as b
114
using(productVendor) );
115
#Variable is not allowed in the exam
116
SET @var1:=
117
(select avg(quantityInStock) from products);
118
select productCode, quantityInStock from products
119
where quantityInStock < @var1
120
ORDER BY quantityInStock DESC;
121
#Create A Database
122
create database temp; use temp; create table bankcustomer
123
(contactID INT(11) primary key not null auto_increment, Name varchar(30)
124
not null, networth double)
125
insert into bankcustomer(contact......)
126
values ('','','',null) must include null value
127
CONCAT: set address = concat('name'-'bank')
128
#Patterns
129
select* from ** where names like 's%'
130
where names like 'S__' where names like '%m%'
131
ALL ANY 可以灵活运用在 where 和 having 语句
132
EXISTS 语句的应用
133
find customers who has not placed any orders
134
select customerNum, customerName from Customers
135
where not exists
136
(select * from orders where orders.customerNum=customers.customerNum)
137
LEFT JOIN 也可以解决类似问题

@zhangzixuan1997
Commit new file
Commit summary
Create new file
Optional extended description
Add an optional extended description…
Commit directly to the master branch.
Create a new branch for this commit and start a pull request. Learn more about pull requests.

© 2019 GitHub, Inc.
Terms
Privacy
Security
Status
Help
Contact GitHub
Pricing
API
Training
Blog
About