1. Create a table of exercises
create table goods (
goods_id mediumint(8) unsigned primary key auto_increment,
goods_name varchar(120) not null default ' ',
cat_id smallint(5) unsigned not null default '0',
brand_id smallint(5) unsigned not null default '0',
goods_sn char(15) not null default ' ',
goods_number smallint(5) unsigned not null default '0',
shop_price decimal(10,2) unsigned not null default '0.00',
market_price decimal(10,2) unsigned not null default '0.00',
click_count int(10) unsigned not null default '0'
) engine=myisam default charset=utf8Copy the code
Insert data
insert into `goods` values (1,'kd876', 4, 8,'ecs000000', 1138, 8.00, 1665.60, 9), (4,Nokia N85 Charger, 8, 1,'ecs000004', 17,58.00, 69.60, 0), (3,'Nokia 5800 Headphones', 8, 1,'ecs000002', 24,68.00, 81.60, 3), (5,SONY Ericsson Original M2 Card Reader, 11, 7,'ecs000005', 8,20.00, 24.00, 3), (6,'Suntek Kingmax Memory Card', 11, 0,'ecs000006', 50.40, 15,42.00, 0), (7,'Nokia N85 Stereo Headset HS-82', 8, 1,'ecs000007', 120.00, 20100.00, 0), (8.'Philips 9@9v', 3, 4,'ecs000008', 1399.00, 478.79, 10), (9,'Nokia E66', 3, 1.'ecs000009', 4229, 8.00, 2757.60, 20), (10,'SONY Ericsson c702c', 3, 7,'ecs000010', 7132, 8.00, 1593.60, 11), (11,'SONY Ericsson c702c', 3, 7,'ecs000011', 1130, 0.00, 0.00, 0), (12,'MOTOROLA A810', 3, 2,'ecs000012', 8983.00, 1179.60, 13), (13,Nokia 5320 Xpressmusic, 3, 1.'ecs000013', 8131, 1.00, 1573.20, 13), (14,'nokia 5800xm', 4, 1,'ecs000014', 1262, 5.00, 3150.00, 6), (15,'MOTOROLA A810', 3, 2,'ecs000015', 945.60, 3788.00, 8), (16,'Henderson Wai Yip G101', 2, 11,'ecs000016', 0823 to 1, 988.00, 3), (17,'amoi n7'3, 3, 5,'ecs000017', 1230, 0.00, 2760.00, 2), (18,'amoi t5', 4, 5,'ecs000018', 1287, 8.00, 3453.60, 0), (19,'samsung SGH - f258', 3, 6,'ecs000019', 12858.00, 1029.60, 7), (20.'samsung bc01', 3, 6,'ecs000020', 12280.00, 336.00, 14), (21,'gionee a30', 3, 10,'ecs000021', 40200, 0.00, 2400.00, 4), (22,'Dopod Touch HD', 3, 3,'ecs000022', 1599, 9.00, 7198.80, 16), (23,'Nokia N96', 5, 1,'ecs000023', 8370, 0.00, 4440.00, 17), (24,'p806', 3, 9,'ecs000024', 100200, 0.00, 2400.00, 35), (25,'PHS/Fixed Phone $50 Top-up Card'And 13, 0,'ecs000025',48.00, 2, 57.59, 0), (26,'PHS/Fixed Phone $20 Top-up Card'And 13, 0,'ecs000026',19.00, 2, 22.80, 0), (27,'Unicom 100 Yuan Recharge Card', 15, 0,'ecs000027',95.00, 2, 100.00, 0), (28,'Unicom 50 Yuan Recharge Card', 15, 0,'ecs000028',45.00, 0, 50.00, 0), (29,'Move 100 yuan top-up card', 14, 0,'ecs000029',90.00, 0, 0.00, 0), (30,'Move 20 yuan top-up card', 14, 0,'ecs000030', 9,18.00, 21.00, 1), (31,'MOTOROLA E8', 3, 2,'ecs000031', 1133, 7.00, 1604.39, 5), (32,'Nokia N85', 3, 1.'ecs000032', 4301, 0.00, 3612.00, 9);Copy the code
Select --from-- WHERE --group by--having--order by select--from--where--group by--having--order byCopy the code2, select * from ‘Where’
(1) the where expression
Usage: If expression is true, this line is taken out
Using the occasion
Various conditions of inquiry occasions, such as by student number check students, by price check goods, by release time check news, etc
② Select 5 where operator
③ Select 5 seed sentences where match
Like fuzzy match % Wildcard Any character _ Wildcard single character
2.1 Take out the goods table whose price is less than or equal to 100 yuan
select goods_id,cat_id,goods_name,shop_price from goods where shop_price <= 100;Copy the code
2.2 Take out the goods in column 4 and column 11 (OR is not allowed)
select goods_id,cat_id,goods_name,shop_price from goods where cat_id in (4, 11);Copy the code
2.3 Remove 100<= price <=500 (do not use and)
select goods_id,cat_id,goods_name,shop_price from goods where shop_price between 100 and 500;Copy the code
2.3 Take out the item whose name begins with “Nokia”
select goods_id,cat_id,goods_name,shop_price from goods where goods_name like 'Nokia %';Copy the code
2.4 Take out the mobile phone named “Nokia Nxx”
select goods_id,cat_id,goods_name,shop_price from goods where goods_name like 'Nokia N__';
Copy the code
2.5 Find out the goods whose price is more than 200 yuan less than the market price
select goods_id, goods_name,abs(market_price-shop_price) as discount from goods
where (market_price-shop_price)>200;Copy the codeThe query is going wrong here because the market_price and shop_price fields are unsigned for decimal (10,2)
I don’t know why mysql doesn’t work…
Change decimal(10,2) unsigned to decimal(10,2)
Alter table goods modify column shop_price decimal(10,2); Alter table goods modify column market_price decimal(10,2);Copy the code3, Group by group
① Select 5 subsentence group and statistical function
Max: find the maximum min: find the minimum sum avg: find the average count: find the total number of rows
② Select 5 subsentence group introduction
group by
Action: Groups rows by field
Syntax: Group by col1,col2… colN
Using the occasion
Common in statistical occasions, such as column calculation of the number of posts, statistics of the average score of each person, etc..
3.1 Group and statistical function
- Max: Find the maximum
- Min: Calculates the minimum value
- Sum: summing
- Avg: average
- Count: number of rows (count does not recognize null)
3.2 Calculate the sum of inventory quantity of each commodity
select goods_name,sum(goods_number) from goods group by goods_name;Copy the code
4. Having a filter
① Select 5 分 sentences having introduction
Having C. having D. having
Having is similar to where, where can filter data
Where plays a role in querying data for columns in a table
Having plays a role in filtering data for columns in the query result
4.1 Query the backlog of goods payment under each column, and screen out the column with the backlog > 20,000
select cat_id,sum(goods_number*shop_price) as sumMoney from goods group by cat_id
having sumMoney > 20000Copy the code
4.2 Where -having-group comprehensive exercises
# building table
create table result (
name varchar(20),
subject varchar(20),
score tinyint
)engine myisam charset utf8;Copy the code
# insert data
insert into result
values
('Joe'.'mathematics', 90), ('Joe'.'Chinese', 50), ('Joe'.'geographic', (40),'bill'.'Chinese', 55), ('bill'.'political', 45),'Cathy'.'political'30),Copy the code
Select * from students who fail 2 or more courses
select name,sum(score < 60) as gk ,avg(score) as pj from result group by name having gk >=2;Copy the code
5, Order by order
① Select the order of 5 subsentences
Order by sort function
Sort query results by one or more fields
Usage: Order by col1,col2,col3
Description of the application of knowledge points
Various sorting occasions, such as news sorted by click, commodity sorted by price, etc
Default sort: ascending sort
5.1 Arrange the commodities in ascending order by column. The commodities in the same column shall be arranged in descending order by price
# ASc stands for ascending and desc stands for descending
select goods_id,cat_id,goods_name,shop_price
from goods
order by cat_id asc, shop_price descCopy the code
6, limit the number of results
① Select 5 subsentence limit introduction
Limit Indicates the number of limits
Limit [offset,] N, the limit result is N
Usage: limit [offset,], fetch entry
Description of the application of knowledge points
Paging applications are most typical, such as 1-20 items on page 1 and 21-40 items on page 2.
6.1 Take out the first 10 items of data in descending order under column 3
select goods_id, cat_id, goods_name, shop_price
from goods
where cat_id = 3
order by shop_price asc
limit 10;Copy the code
6.2 Query the goods from the third to the fifth in the order of the highest price in the store
select goods_id, goods_name, shop_price
from goods
order by shop_price
desc
limit 2, 3;
# limit 2 = offset 1 = offset 2 = offset 2 = offset 3Copy the code
7, where subquery
7.1 Querying the item with the maximum goods_id under each column
Select Max (goods_id),cat_id from goods group by cat_id where goods_id = goods_id select Max (goods_id),cat_id from goods group by cat_idwhereSelect goods_id, goods_name from goodswhere goods_id in
(select max(goods_id) from goods group by cat_id);Copy the code
Select * from subquery
8.1 Query the item with the maximum goods_id under each column
Select goods_id, goods_name from goodsorder by cat_id, Select * from (select goods_id,cat_id, goods_name from (select goods_id,cat_id, goods_name from goods order by cat_id asc, goods_id desc) as tmp group by cat_id;Copy the code
9. Exists sub query
Create a new table and practice using the goods table above
create table category(
cat_id int auto_increment primary key,
cat_name varchar(20) not null default ' '
)engine myisam charset utf8;Copy the code# insert data
insert into category
(cat_name)
values
('Phone Type'),
('the CDMA mobile phone'),
('the GSM mobile phone'),
('3 g mobile phone'),
('Dual mode phone'),
('Mobile Phone Accessories'),
('Charger'),
('headphone'),
('battery'),
('Card reader and Memory'),
('Top up card'),
('PHS/Landline Top-up Card'),
('Mobile Phone Top-up Card'),
('Unicom Mobile Prepaid Card');
Copy the code
9.1 Take out the item column with items under the column (not every cat_ID has items)
select cat_id,cat_name from category where exists
(select * from goods where goods.cat_id = category.cat_id);Copy the code
SQL > create table not null default
Let's create a table
create table test9(
sname varchar(20)
)engine myisam charset utf8;
# insert data
insert into test9
values
('lisi'),
('wangwu'),
(null);Copy the code
Please compare the two pictures below
select sname from test9 wheresname ! = null;Copy the code
select sname from test9 where sname = null;Copy the code
That’s weird! =null gets an empty set. =null is also an empty set.
The reason is that null must be represented by special expressions, including is NULL and is not NULL, for example
select sname from test9 where sname is not null;Copy the code