-DQL operation table record - query [ key ]

7.1 basic query syntax

select Field name to query from Table name [where condition] 

7.2 simple query

7.2.1 query records of all rows and columns

• grammar

select * form surface

• Query all the columns in the commodity table

select * from product;

7.2.2 query records of specific columns in a table

• grammar

select Listing,Listing,Listing... from surface

• Query product name and price

select pname, price from product;

7.2.3 de duplication query distinct

• grammar

SELECT DISTINCT Field name FROM Table name;   //The data can be as like as two peas before being weighed.

• Go to re query the name of the product

SELECT DISTINCT pname,price FROM product

Note: for a column, the column name cannot appear before distinct

7.2.4 alias query

• grammar

select Listing as alias ,Listing  from surface   //The column name as may not be written
select alias.* from surface as alias      //Table alias (multi table query, which will be discussed in detail tomorrow)

• Query product information and use alias

SELECT pid ,pname AS 'Trade name',price AS 'commodity price',num AS 'Commodity inventory' FROM product

7.2.5 operation query (+,, *, /,% etc.)

• Query the commodity name and commodity price + 10: we can not only add a fixed value to a certain field, but also calculate and query multiple fields

select pname ,price+10 as price from product;

select name,chinese+math+english as total from student

be careful

• Operation query fields. Fields can be
• String and other types can be used for operation query, but the result is meaningless

7.3 query criteria (very important)

7.3.1 grammar

select ... from surface where condition 
//Take out each data in the table, and the records that meet the conditions will be returned, and the records that do not meet the conditions will not be returned

7.3.2 operators

1. Comparison operator

Greater than:>
Less than:<
Greater than or equal to:>=
Less than or equal to:<=
be equal to:=   Cannot be used for null judge
 Not equal to:!=  or <>
Safety equals: <=>  Can be used for null Value judgment

2. Logical operators (words are recommended for readability)

Logic and:&& or and
 Logical or:|| or or
 Logical non:! or not
 Logical XOR:^ or xor

3. Scope

Interval range: between x  and  y
      not between x  and  y
 Set range: in (x,x,x) 
      not  in (x,x,x)

4. Fuzzy query and regular matching (only for string type and date type)

like 'xxx'  Fuzzy query is to perform partial matching when processing strings
 If you want to represent 0~n Characters, in%
If you want to represent a certain character, use_

regexp 'regular'

5. Special null value handling

#(1) Judgment time
xx is null
xx is not null
xx <=> null

7.3.3 practice

• Query products with commodity price > 3000

select * from product where price > 3000;

• Query goods with pid=1

select * from product where pid = 1;

• Query products with PID < > 1

select * from product where pid <> 1;

• Inquire about goods with prices between 3000 and 6000

select * from product where price between 3000 and 6000;

• Query products with pid in the range of 1, 5, 7 and 15

select * from product where id = 1;
select * from product where id = 5;
select * from product where id = 7;
select * from product where id = 15;

select * from product where id in (1,5,7,15);

• Query products whose product name starts with iPho (iPhone Series)

select * from product where pname like 'iPho%';

• Query commodities with commodity price greater than 3000 and quantity greater than 20 (condition and...)

select * from product where price > 3000 and num > 20;

• Query products with id=1 or price less than 3000

select * from product where pid = 1 or price < 3000;

7.4 Sorting Query

Sorting is written after the query, which means sorting after querying the data

7.4.1 environmental preparation

# Create a student table (with Sid, student name, student gender, student age and score columns, where sid is the primary key and grows automatically)
CREATE TABLE student(
  sid INT PRIMARY KEY auto_increment,
  sname VARCHAR(40),
  sex VARCHAR(10),
  age INT,
    score DOUBLE
);

INSERT INTO student VALUES(null,'zs','male',18,98.5);
INSERT INTO student VALUES(null,'ls','female',18,96.5);
INSERT INTO student VALUES(null,'ww','male',15,50.5);
INSERT INTO student VALUES(null,'zl','female',20,98.5);
INSERT INTO student VALUES(null,'tq','male',18,60.5);
INSERT INTO student VALUES(null,'wb','male',38,98.5);
INSERT INTO student VALUES(null,'Xiao Li','male',18,100);
INSERT INTO student VALUES(null,'Xiao Hong','female',28,28);
INSERT INTO student VALUES(null,'cockroach','male',21,95);

7.4.2 single column sorting

1. Syntax: sort by only one field, single column

SELECT Field name FROM Table name [WHERE condition] ORDER BY Field name [ASC|DESC];  //ASC: ascending, default value; DESC: descending order

1. Exercise: query all students in descending order of scores

SELECT * FROM student ORDER BY score DESC

7.4.3 combined sorting

1. Syntax: sort multiple fields at the same time. If the first field is equal, sort by the second field, and so on

SELECT Field name FROM Table name WHERE field=value ORDER BY Field name 1 [ASC|DESC], Field name 2 [ASC|DESC];

1. Exercise: query all students in descending order of scores. If the scores are consistent, then in descending order of age

SELECT * FROM student ORDER BY score DESC, age DESC

7.5 aggregate function

Aggregation functions are usually used together with grouping queries to count the data of each group

7.5.1 aggregate function list

Aggregate function	effect
Max (column name)	Find the maximum value of this column
Min (column name)	Find the minimum value of this column
AVG (column name)	Find the average of this column
Count (column name)	Count the number of records in this column
Sum (column name)	Sum this column

1. grammar

SELECT Aggregate function(Listing) FROM Table name [where condition];

1. practice

-- Find the highest score in the student table
SELECT MAX(score) FROM student
-- Find the lowest score in the student table
SELECT MIN(score) FROM student
-- Find the sum of the scores in the student table(ignore null value)
SELECT SUM(score) FROM student
-- Find the average score in the student table
SELECT AVG(score) FROM student
-- Count the total number of students (ignore null) 
SELECT COUNT(sid) FROM student
SELECT COUNT(*) FROM student

Note: the aggregate function ignores NULL values

We found that null records will not be counted. It is recommended not to use columns that may be null if the number of records is counted, but what if NULL needs to be counted? We can solve this problem through ifnull (column name, default value) function If the column is not empty, return the value of this column. If NULL, the default value is returned.

SELECT AVG(IFNULL(score,0)) FROM student;

7.6 group query

GROUP BY takes the same contents in the grouping field results as a group and returns the first data of each group, so grouping alone is useless. The purpose of grouping is for statistics. Generally, grouping will be used together with aggregation function

7.6.1 grouping

1. grammar

SELECT Field 1,Field 2... FROM Table name  [where condition] GROUP BY column [HAVING condition];

1. Exercise: count the total number of students in each group according to gender

-- Grouped by gender, Count the total number of students in each group
SELECT sex 'Gender',COUNT(sid) 'Total number' FROM student GROUP BY sex

-- According to gender grouping, the average scores of students in each group were counted
SELECT sex 'Gender',AVG(score) 'average' FROM student GROUP BY sex

-- According to gender grouping, the total scores of students in each group were counted
SELECT sex 'Gender',SUM(score) 'Total score' FROM student GROUP BY sex

7.6.2 screening after grouping

• Practice grouping according to gender and count the total number of students in each group > 5 (screening after grouping)

SELECT sex, count(*) FROM student GROUP BY sex HAVING count(*) > 5

• Practice grouping according to gender. Only those aged 18 or older are counted, and the number of people in the group is required to be greater than 4

SELECT sex 'Gender',COUNT(sid) 'Total number' FROM student WHERE age >= 18 GROUP BY sex HAVING COUNT(sid) > 4

7.6.3 difference between where and having [interview]

Sub name	effect
where clause	1) Before grouping the query results, remove the rows that do not meet the where criteria, that is, filter the data before grouping, that is, filter before grouping. 2) Aggregate functions cannot be used after where
having sentence	1) The function of having clause is to filter the groups that meet the conditions, that is, filter the data after grouping, that is, group first and then filter. 2) Aggregate functions can be used after having

7.7 paging query

7.7.1 grammar

select ... from .... limit a ,b

LIMIT a,b;
a indicates the number of data pieces skipped
b indicates the number of data to be queried

7.7.2 practice

-- Paging query
-- limit Keywords are used at the back of the query. If there is sorting, they are used at the back of the sorting
-- limit Grammar of: limit offset,length  among offset Indicates how many pieces of data are skipped, length Indicates how many pieces of data to query
SELECT * FROM product LIMIT 0,3
-- query product The first three data in the table(0 It means to skip 0 items, and 3 means to query 3 items)

SELECT * FROM product LIMIT 3,3
-- query product The fourth to sixth data in the table(3 It means skipping 3 items, and 3 means querying 3 items)
-- When paging, I will only tell you which page of data I need and how many pieces of data there are on each page
-- If you need 3 pieces of data per page, I want the first page of data: limit 0,3
-- If,Each page needs 3 pieces of data. I want the second page of data: limit 3,3
-- If you need 3 pieces of data per page, I want the data on the third page: limit 6,3
 conclusion: length = Number of data pieces per page, offset = (Current number of pages - 1)*Number of data pieces per page
 limit (Current number of pages - 1)*Number of data pieces per page, Number of data pieces per page

7.8 syntax summary of query

select...from...where...group by...order by...limit

select...from...where...
select...from...where...order by...
select...from...where...limit...
select...from...where...order by...imit

Import and export data (understand)

8.1 single database backup

mysql5.5

C:\Windows\System32> mysqldump -h Host address -P Port number -u user name -p password --database  Database name > File path/file name.sql

For example:

C:\Windows\System32>mysqldump -hlocalhost -P3306 -uroot -p123456 --database  test > d:/test.sql

mysql5. Version 7

C:\Windows\System32> mysqldump -h Host address -P Port number -u user name -p Password data name > File path/file name.sql

Stop writing – database

8.2 import sql script for backup

Log in to mysql first, and then execute the following command:

mysql> source  sql Script pathname.sql

For example:

mysql>source d:/test.sql;