Shulou(Shulou.com)06/02 Report--

Today, I would like to share with you the relevant knowledge of how to operate DQL in mysql grammar. The content is detailed and the logic is clear. I believe most people still know too much about this knowledge, so share this article for your reference. I hope you can get something after reading this article.

DQL (Data Query Language), a data query language, is mainly used to query data, which is also the most important part of SQL!

Simple query # basic query for DQL operation # create database CREATE DATABASE IF NOT EXISTS mydb2;# use database USE mydb2;# to create table CREATE TABLE IF NOT EXISTS product (pid int PRIMARY KEY auto_increment, pname varchar (20) not null, price double, category_id varchar (20)) # adding data can be abbreviated, but it is recommended to develop a good code specification INSERT INTO product (pid,pname,price,category_id) values (null,' Haier washing Machine', 5000 Haier washing Machine', null,' Haier washing Machine', 3000). 'c001'), (null,' Gree air conditioner', 5000meme c001'), (null,' Jiuyang rice cooker', 5000meme c001') # query all commodities SELECT * FROM product;SELECT pid,pname,price,category_id FROM product;# query commodity name and price SELECT pname,price FROM product;# alias query keyword is as can be omitted # table alias SELECT * FROM product as p * column alias SELECT pname as' commodity name', price as' commodity price 'FROM product # remove duplicate values # remove a column of duplicate SELECT DISTINCT price FROM product;#, remove a row of duplicate SELECT DISTINCT * FROM product;# operation query (query result is an expression): you can alias the result SELECT pname,price+10 as now_price FROM product; operator query # operator (where to look up (table) what (field) what condition (condition) # arithmetic operator SELECT pname,price+10 as now_price FROM product SELECT pname,price*1.2 as now_price FROM product;# comparison operator # logical operator SELECT * FROM product where pname = 'Haier washing machine'; SELECT * FROM product where price 3000 select * FROM product where price BETWEEN 3000 AND 5000 select * FROM product where price IN; #% can match any character _ can match a single character SELECT * FROM product where pname LIKE'% washing machine'; SELECT * FROM product where pname LIKE'_ _ washing machine%' # NULL judges SELECT * FROM product where category_id IS NOT NULL;# uses the Least function to find the minimum. If one is NULL, it will not be compared. The direct result is NULLSELECT LEAST. # use the Greatest function to find the maximum value, SELECT GREATEST. (28Query 35,521). Sorting query # sorting query when sorting multiple fields of read data in descending order, the default asc ascending order is SELECT * FROM product ORDER BY price. SELECT * FROM product ORDER BY price DESC;SELECT * FROM product ORDER BY price DESC,pname ASC;# to reorder SELECT DISTINCT price FROM product ORDER BY price Aggregate query # aggregate query aggregate function previous queries are aggregated in behavior units # common aggregate query functions are count (*) count (1) count (primary key) three same as NULL (*) aggregate (1) count (primary key) three same # general aggregate function and grouped together # query total item # pid field is not empty (recommended primary key) SELECT COUNT (pid) FROM product;# line is not empty SELECT COUNT (*) FROM product # query the total items of goods whose price is greater than 3000, first check the items greater than 3000, and then calculate the total items SELECT COUNT (pid) FROM product WHERE price > 3000 position # query the total price and SELECT SUM (price) FROM product # aggregate query deals with NULL # if it is an integer, you can set default 0 grouping query # grouping query group by (the fields after group by are put in the same group) # first, grouping according to group by, and then aggregating each group and finally select to get the results # after grouping, select can only write grouping fields and aggregate functions # count the number of classified commodities SELECT category_id Count (pid) FROM product GROUP BY category_id # conditional filtering after grouping having from- > where- > group by- > select- > having#where screening rows generated by from clause group by filtering where clause having is used to filter the results produced by group by # count the number of classified items and filter out SELECT category_id,count (pid) FROM product GROUP BY category_id HAVING count (pid) > = 3 Paging query # paging query limit is used for too many goods, so paging shows the subscript 0 SELECT * FROM product LIMIT 5 * select * FROM product LIMIT 3 FROM product LIMIT 2; one table query result is inserted into another table # insert into select imports data from one table into another existing table CREATE TABLE product2 (pname VARCHAR (20), price DOUBLE); INSERT INTO product2 (pname,price) SELECT pname,price FROM product where category_id = 'c001' CREATE TABLE product3 (category_id VARCHAR (20), product_count int); INSERT INTO product3 SELECT category_id,count (*) FROM product GROUP BY category_id; can first analyze the operation to be performed according to the table structure, what the corresponding operation order is, what to do first, then to do, and then write the corresponding SQL language after the whole analysis. Although there are many shorthand methods, it is recommended to write the foundation first, write familiar with it and then simplify it, and lay the foundation at the basic stage. SQL sentence Analysis # SQL Writing order (basically not based on human will) SELECT category_id Count (pid) as cnt FROM product WHERE price > 1000 GROUP BY category_id HAVING cnt > 3 ORDER BY cnt LIMIT 1 # SQL execution order (help analysis) from- > where- > group by- > count- > having- > order by- > limitDQL exercise 1

The foundation must be solid, the important thing is not the answer, but the process of analyzing the answer!

# DQL Operation exercise USE mydb2;CREATE TABLE IF NOT EXISTS student (id INT, name VARCHAR (20), gender VARCHAR (20), chinese INT, english INT, math INT) # do not add the last field after it is defined, and the field name and type are separated by a space INSERT INTO student (id,name,gender,chinese,english,math) VALUES (1) Zhang Ming, 'male', 89, 78, 90), (2) Li Jin 'male', '67pence53' (95), (3recorder 'Wang Wu', 'female', '87pai78' 77), (4'Li Yi', 'female', '88pence98') (5Jing'Li Cai', 'male', 82'84'), (6 'Zhang Bao', 'male', 55'85'45) (7rem 'Huang Rong','Nu', 75pr '65pr 30), (7rect' Huang Rong','Nu', 75pm '65Rong') # query the total score of each student (aggregate query is a whole column operation, but here is the addition of different columns) SELECT name, (chinese+english+math) as total_score FROM student # I wanted to add group by id again at first, but think carefully about select # is already one line, so these fields are equivalent to operations on a certain line # query students whose total score is greater than 20000 # here WHERE (chinese+english+math) > 200 cannot be written as total_score > 200because think about the SQL execution order when where does not have the previous select to execute SELECT *, (chinese+english+math) as total_score FROM student WHERE (chinese+english+math) > 200 # query the students whose math score is not 89 9091, in () means that either of these numbers can SELECT name,math FROM student WHERE math not in (89 FROM student WHERE name LIKE 90 91); SELECT name,math FROM student WHERE NOT (math in (89 90 91)); # the descending order of the total score of the students surnamed Li is to find the students surnamed Li first and then find the total score and sort SELECT * Li% 'ORDER BY (chinese+english+math) DESC in descending order # query the number of boys and girls and sort the output in descending order # when writing in order, select doesn't know what to write first * write later and then change the previous SELECT gender,count (id) FROM student GROUP BY gender ORDER BY count (id) # query the number of boys and girls and sort the output in descending order and select the output with a total number greater than 4 # here you need to understand that the total number is greater than 4 after grouping and then sorting after screening! # first analyze what to do according to the requirements, then look at the sequence of these operations, and then write SQL language step by step to check SELECT gender,count (id) FROM student GROUP BY gender HAVING count (id) > 4 ORDER BY count (id); DQL small exercise 2#DQL operation exercise USE mydb2 CREATE TABLE IF NOT EXISTS emp (empno INT, # employee number ename VARCHAR (50), # employee name job VARCHAR (50), # work name mgr INT, # superior leader number hiredate date, # entry date sal INT, # salary comm INT, # bonus deptno INT # department number) INSERT INTO emp VALUES (7369 Smithsonian pamphlet CLERKLERKGER 7902, 1980-12-17, 1980-12-17, 800, null, 20), (7499), (7499, 7499) (7521) (7521) (756) (756) (756) (756) (7521) (7521) (7521) (7521) (7521) (7521) (7521) (7521) (7521) (7521) (7521) (7521) (7521) (7521) (756) (756) (756) (756) (756) '1981-04-02), (7654), (1250, 1400, and 30) 'Null BLAKE','MANAGER',7839,'1981-06-09) # query the information of employees whose second letter is not An and whose salary is greater than 1000 according to the descending order of annual salary, the second letter is either An or not like implementation # ifnull (sal 0) 0 if sal is NULL otherwise it is the original value SELECT * FROM emp WHERE ename NOT LIKE'_ A%'& & sal > 1000 ORDER BY 12*sal+ifnull (comm,0) DESC # ask for the average salary of each department, that is, the average group query thinks of the aggregate function SELECT deptno,AVG (sal) FROM emp GROUP BY deptno;# to find the highest salary of each department SELECT deptno,max (sal) FROM emp GROUP BY deptno # ask for the maximum salary of each department, every post, each job grouping field is two, not two groups, two GROUP BYSELECT deptno,job,max (sal) FROM emp GROUP BY deptno,job # when you see the highest and lowest average, you have to think that the aggregate function (column operation) is generally a number plus the number of groups. # query the difference between the maximum and minimum wages SELECT max (sal)-min (sal) FROM emp; regular expression

Regular expressions are a set of rules that describe character matching, and MYSQL uses the REGEXP keyword to support regular expressions for string matching.

The symbolic meaning ^ matches the start position of the input string $matches the end position of the input string. Match any single character except'\ n' [...] Match any of the characters contained [^...] Match any characters not included p1 | p2 | p3 match p1 or p2 or p3 * match the previous subexpression 0 or more times + match the previous subexpression 1 or more times? Match the previous sub-expression 1 or 0 times {n} match the determined n times {n,} match at least n times {n department m} match at least n times and up to m times # regular expression query (never memorize what you need to look up) SELECT 'abc' REGEXP' ^ a regular expression select 'abc' REGEXP' centry match REGEXP expression is the regular expression that is the string format SELECT * FROM product WHERE pname REGEXP'^ sea 'that needs to be matched. Any single character'.b 'except'\ n' denotes any character + bSELECT 'abc' REGEXP' .b'; # indicates whether there is a character preceded by SELECT 'abc' REGEXP' [xaz]'; # indicates whether any character does not appear in front of SELECT 'abc' REGEXP' [^ abc]' # usually automatically matches * | + if you want more than one character, put parentheses # a* to match 0 or more a, including the empty string SELECT 'stab' REGEXP' .ta * bouncy select 'stb' REGEXP' .ta * bouncy's select 'stb' REGEXP'. Ta * bouncy'a + means that one or more a does not include the empty string SELECT 'stab' REGEXP'. Ta + bouncy select 'stb' REGEXP' .ta + bouncy'a? Indicates that 0 or 1 a matches, including the empty string SELECT 'stab' REGEXP'. Ta? breadth select 'staab' REGEXP'. Ta? breadth select'a 'REGEXP'a | bouncy selection' c 'REGEXP' ^ (a | b)'; SELECT 'auuuuc' REGEXP' au {4} cantilever select 'auuuuc' REGEXP' au {4,} cantilever select 'auuuuc' REGEXP' au {3} c' This is the end of the basic query, starting tomorrow multi-table query, because I wrote in navicat, and then to write the csdn, so most of my notes in the study have also been written, focusing on the analysis process! These are all the contents of this article entitled "what is the operation of DQL in mysql grammar?" Thank you for reading! I believe you will gain a lot after reading this article. The editor will update different knowledge for you every day. If you want to learn more knowledge, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.