029 SQL basic query and data update

– workbench: Software

– query data: select
//Query the name of fruit with a price of 10.2 yuan from the fruits table

select f_name(,f_price Better look) from fruits where f_price=10.2;

– query with in keyword: discrete value
//Query s from the fruits table_ Records with IDS 101 and 102

select * from fruits where s_id in (101,102);

//Not for

select * from fruits where s_id not in (101,102);

– queries with between... and: continuous values
//Query the records with prices between 2.00 and 12.00 from the fruits table

select * from fruits where f_price between 2.00 and 12.00;

//No

select * from fruits where f_price not between 2.00 and 12.00;

030 SQL query in workbench

– query with like: character matching (fuzzy query)
//Wildcards that can be used with like in sql statements:% and_
//% wildcard: matches any character length, including 0 characters
//Query fruits starting with 'b'

select * from fruits where f_name like 'b%';

//Fruit with name 'g'

select * from fruits where f_name like '%g%';

//_ Wildcards: match any single character
//Query the fruit that ends with 'y' and has only 4 letters before 'y'

select * from fruits where f_name like '____y';

– null value comparison: is null or is not null
//null is not equivalent to an empty string. null values generally indicate unknown data, inapplicability, etc

select * from suppliers where s_city is null;

– multi criteria query: and or
//and has higher priority

– no duplicate query results: distinct
//Which cities does the supplier come from

select distinct s_city from suppliers;

– query result statistics: count()
//How many cities are suppliers distributed

select count(distinct s_city) as 'Number of cities' from suppliers;

031 SQL single table query

– sort query results: order by xxx,xxx (desc descending)

select * from fruits order by f_price;
select * from fruits order by s_id desc,f_price desc;

– group query: group by
//Statistical difference s_ Total number of IDS: count()

select s_id,count(*) as 'Commodity category'
from fruits
group by s_id;

//Find out the type and name of fruit provided by each supplier: group_concat()

select s_id,count(*) as 'Commodity category',group_concat(f_name)
from fruits
group by s_id;

//Reorder grouped query results

select s_id,count(*) as 'Commodity category',group_concat(f_name)
from fruits
group by s_id
order by Commodity category;

//Filtering: group by... having
//Query the supplier information of more than one type of goods supplied

select s_id,count(*) 'Commodity category' from fruits
group by s_id
having Commodity category>1;

//Statistics record: group by... with rollup
//According to s_id groups fruits and displays the number of records

select s_id,count(*) from fruits
group by s_id with rollup;

– limit the number of query results: limit
//Start with the second item and display 4 fruit information

select * from fruits limit 1,4;

//Using limit to realize top n problem
//Find the four most expensive items

select * from fruits
order by f_price desc
limit 4;

//Paging query with limit

select * from fruits limit 0,5;
select * from fruits limit 5,5;
......
select * from fruits limit 5*(n-1),5;

032 SQL case statement and index

– case when statement: calculate the condition list and return the result expression
//If the price of fruit is more than 10 yuan, it will be "expensive"< 5 yuan, displayed as "cheap"; Other "normal"

select f_name,
case
	when f_price>10 then 'expensive'
	when f_price>10 then 'cheap'
	else 'normal'
end as 'Price description'
from fruits;

//Processing null values

select s_id,s_name,
case
	when s_city is null then 'Unknown'
    else s_city
end as 'City'
from suppliers;

select s_id,s_name,ifnull(s_city,'Unknown')
from suppliers;

– aggregate function query
//Keep two decimal places for avg

select round(avg(f_price),2)
from fruits;

– index: speed up query (index commonly used fields after where)
//For S_ Index the name field: create index... on

create index index_sname on suppliers(s_name);

//View the index on the specified data table: show index from

show index from suppliers;

//drop index... on

drop index index_sname on suppliers;

– multi table query: internal connection and external link
– internal connection
//Query the supplier information of 'apple' products. / / use alias

select f_name,f_price,f.s_id,s_name,s_city
from fruits f,suppliers s
where f_name='apple' and f.s_id=s.s_id;

//ANSI, inner can be omitted

select f_name,f_price,f.s_id,s_name,s_city
from fruits f inner join suppliers s
on f.s_id=s.s_id
where f_name='apple';

– external connection: internal connection + information that cannot be connected
//Left outer join (let the table on the left of the join fully participate): left outer join
//right outer join

//Query the information of all goods and their suppliers, including the information of unserved goods

select f_name,f_price,s.s_id,s_name,s_city
from fruits f right outer join suppliers s
on f.s_id=s.s_id;

033 SQL multi table query

– subquery
//Query the information of all goods whose price is higher than the average price

select * from fruits
where f_price>(select avg(f_price) from fruits);

//Query the commodity information of all suppliers from Beijing and Shanghai

select * from fruits
where s_id in (select s_id from suppliers where s_city in ('Beijing','Shanghai'));

– merge query results: Union (will eliminate duplicates), union all

select s_name,s_city from suppliers
union
select f_name,f_price from fruits;

– regular expression query regexp: match a format
Match start:^
Match end:$
Match: [a-z0-9]
The number of times the preceding character is repeated 0 or more times:*
The number of times the preceding character is repeated 1 or more times:+
Any character:

//Names that begin with a

select * from fruits
where f_name regexp '^a';

//Names ending with a

select * from fruits
where f_name regexp 'a$';

//With x in the name

select * from fruits
where f_name regexp 'x';

//The name starts with x and ends with y, with multiple arbitrary characters in the middle

select * from fruits where f_name regexp '^x.*y$';

//The name starts with x and ends with y, with 4 arbitrary characters in the middle

select * from fruits where f_name regexp '^x.{4}y$';

//Starts with x and ends with y, with at least one ba in the middle

select * from fruits where f_name regexp '^x(ba)+y$';

//2 ba

where f_name regexp '(ba){2}';

034 SQL data table connection and data entry

– import external data
//Connect CMD

mysql -u root -p --local-infile=1
SHOW VARIABLES LIKE '%local%'; 
SET GLOBAL local_infile=1;

//After using database, import data

load data local infile 'E:/'
into table xxx
fields terminated by ','
ignore 1 lines;

Note: only "multi side" can provide value; If you use "less side" to provide a value, the final result will be doubled.