mysql< three >

--########## 01, query sorting ##########--requirements: The class of all boys of the age of the order-thinking:-thinking 1, the first sort of all the data, and then to filter-thinking 2, the entire data line screening, and then sorted--Obviously, Train of thought 2 this form of efficiency is high, semantic and implementation are in line with the requirements, because the more data sorted, the more time consuming, so first by filtering to reduce the amount of data to be sorted and then sorted-order: Ascending (sequential) ASC and descending (reverse order) desc--features:-- A: Instead of specifying which field to use as the sort order, the default display order is sorted by the field of the table's primary key (ascending)-B: In ascending order, the null value precedes the non-null values-C: Does not explicitly indicate the order, the default is ascending (sequential) Ascdesc student; SELECT * FROM student;--1, single field sort select * from student order by phone ASC; SELECT * FROM student ORDER by phone DESC; SELECT * FROM Student ORDER by phone;--2, multiple fields of the sort insert into student VALUES (null, ' Guo Jia ', ' Male ', ' 666 '), (null, ' Guo Jia ', ' Male ', ' 11 1 ');--demand: Press Studentname ascending, while pressing phone in descending order select * FROM Student ORDER by Studentname ASC, phone desc;insert into student VALUES (N ULL, ' Guo Jia ', ' Male ', ' 222 ');-note: The following sentence also involves the ordering of multiple fields: first by Studentname Ascending, then by the primary key StudentID ascending-that is, first consider the sort order explicitly indicated in the ORDER BY clause, and secondly consider the implicit primary key ascending    SELECT * FROM student ORDER by Studentname asc;--########## 02, column-based Logic ##########--commodity table CREATE TABLE product (--Product number ProductID INT auto_increment PRIMARY KEY,--Product name ProductName VARCHAR (20),--Classification code Categorycode ENUM (' f ', ' C ')), INSERT into product VALUES (null, ' Apple ', ' F '), (null, ' pear ', ' f '), (NULL, ' Banana ', ' F '), (null, ' Nike ', ' C '), (null, ' Kappa ', ' C '); SELECT * FROM product;--Requirements: Classification code categorycode display F, C is not clear enough, in the query results want to be able to see the clear meaning-analysis: This is the logic for the column, the content of the column if f is shown as fruit,  If C is displayed as a dress--using the combination of the keyword case, when, then, ELSE, end--the format of the case expression 1:--the select---a case field or expression-when the value is 1 then the result is a value of 2 Then result 2--...--else default result--Endselect ProductID as product number, ProductName as product name, Categorycode as classification code (unclear), CA SE Categorycode when the ' F ' then ' fruit ' when ' C ' then ' clothes ' else ' other ' END as categorical encoding (clear) from the product;--Case expression format 2:-- select--case--When condition 1 then result when condition 2 then result 2--...--else default result--Endselect ProductID as product number, ProductName A    S product name, Categorycode as classification code (not clear), case when categorycode = ' F ' Then ' fruit ' when categorycode = ' C ' Then ' clothes ' Else ' other ' END as categorical encoding (clear) from product;--########## 03, line-based logic ######### #SELECT * from STUDENT;DESC Student;--1. Use the WHERE clause to conditionally filter the collection select * FROM student WHERE gender = ' female '; SELECT * FROM student WHERE gender = ' male ';--error code: 1064 You have a error in your SQL syntax; Check the manual-corresponds to your MySQL server version for the right syntax-use near ' LIMIT 0, ' 1S Elect * from student where gender = default;--2, using and in the WHERE clause, representing the relationship of logic (filtering out the results that all conditions in the WHERE clause satisfy) SELECT * FROM student WHERE gender = ' female ' and phone = ' 123 '; SELECT * FROM student WHERE gender = ' female ' and phone = ' 114 ';--3, using or in the WHERE clause, representing a logical or relationship (the WHERE clause is filtered as long as the result of the condition is satisfied) SELECT * FR OM student WHERE gender = ' female ' OR phone = ' 123 '; SELECT * FROM student WHERE gender = ' female ' OR phone = ' 114 ';  SELECT * FROM student where gender = ' female ' or phone = ' 119 ';--4, use! = in the WHERE clause, or<>that represents a relationship that does not equal select * FROM student WHERE gender! = ' woman '; SELECT * FROM student WHERE gender<>' female ';--error code: 1064 You has an error in your SQL syntax; check the manual, corresponds to your MySQL server Versio n for the right syntax-use-near ' ><' Female ' LIMIT0, + ' at line 1SELECT * from student WHERE gender><' woman ';--5. * Wildcard: Asterisk wildcard, used instead of all field names select * from student;--6, _ Wildcard: Underline wildcard characters (placeholder wildcard), combined with the LIKE keyword use-note: _ Wildcard placeholder for a position, this position can be any character, But there must be a character insert into student VALUES (null, ' Cao Pi ', ' Male ', ' a '), (null, ' Cao Zhi ', ' Male ', ' "') ', (null, ' Cao Mengde ', ' Male ', '" ') ', (null, ' Cao ', ' Male ', ' 140 ');-demand: Query begins with ' Cao ', followed by a record of any character select * from student WHERE studentname like ' Cao _ ';--demand: Query begins with ' Cao ', followed by two arbitrary characters Record SELECT * FROM student where studentname like ' Cao __ ';--demand: The query begins with an arbitrary character, with a ' Meng ' word in the middle, followed by a record of any character select * from student WHERE St Udentname like ' _ meng _ ';-demand: The query begins with an arbitrary character, with a ' Cao ' in the middle, followed by a record of any character select * from student WHERE studentname like ' _ Cao _ ';--note: experience accounted for The meaning of the bit, the next sentence insert space + Cao Shu, will be detected insert into student VALUES (NULL, ' Cao Shu ', ' Male ', 150); SELECT * FROM student WHERE studentname like ' _ Cao _ ';--7,% wildcard: a percent-semicolon wildcard (any wildcard character), with the LIKE keyword used-the meaning of any match: whether it matches one character or multiple characters, There are even no characters that match the select * from student WHERE studentname like ' Cao% ';--The following sentence of the ' Cao ' character has two percent% percent and the effect is consistent with a percent percent semicolon, all in the ' Cao ' character after any match select * FROM student where studentname like ' Cao-percent ';--The following sentence indicates any match between the ' Cao ' characters select * from student where StUdentname like '% Cao ';--match all records (non-Empty Records) SELECT * FROM student WHERE studentname like '% ';--match all contact phone records starting with 1 SELECT * from stud ENT WHERE phone like ' 1% ';--match all contact phone non-empty records select * FROM student WHERE phone like '% ';--8. When using like for fuzzy query, the inverse operation is not the same as the Nu ll values match the select * from student WHERE The phone is not a like ' 1% ';--9. Use the In keyword in the WHERE clause to describe a collection that is in a small range-- The values in the corresponding small range collection are the same in the table: the record of the matching value is taken out select * from student WHERE phone in (' 110 ', ' 120 ', ' 666 ');--  The values in the corresponding small-range collection are partially present in the table: Records of the matching values are taken out of select * from Student WHERE phone in (' 110 ', ' 120 ', ' 777 ');--note: The use of the above in is understood as: SELECT * from  Student WHERE Phone= ' a 'OR Phone= ' + 'OR Phone= ' 777 ';--10. Use the not in keyword in the WHERE clause to describe a collection that is not in a small range select * FROM student WHERE phone is not in (' 110 ', ' 120 ', ' 666 '); SELECT * FROM student WHERE phone isn't in (' 110 ', ' 120 ', ' 777 ');-note: In a certain small range + not in a small range is not equal to the full range, because null values are ignored- -11, use is null to filter the contents of the field to a record of NULL SELECT * FROM student WHERE The phone is null;--12, the contents of the filter field using is not null the content of the non-null record select * FROM St Udent WHERE Phone is not null;--Note: is NULL filtered records + is not null filtered records equal to all records-13, use greater than, less than, greater than, less than equals symbol for range filtering (same does not package Records with null values) SELECT * FROM student WHERE phone>' 150 '; --2 Records SELECT * FROM student WHERE phone<''; --7 Records SELECT * FROM student WHERE phone>= ' 150 '; --3 Records SELECT * FROM student WHERE phone<= ''; --8 Records--14, using between ... And ... Do scope filtering (consider whether the boundary value is included?) A: Two boundary values are included) SELECT * from student where StudentID between 3 and 8;--the result of the next sentence query: No record is met for the condition select * FROM student WHERE student ID between 8 and 3;--Note: Between value 1 and value 2 equals greater than or equal to 1 and is less than or equal to the value 2--198 rows can be understood as the following sentence: SELECT * from student WHERE StudentID 
    >= 3 and StudentID<= 8;--201 lines can be understood as the following sentence: SELECT * from student WHERE StudentID>= 8 and StudentID<= 3;--########## 04, summarizing data ##########--1, using DISTINCT keyword to remove duplicate values-demand: Get the student's gender select gender from student;--The sentence is syntactically correct, but from the semantic point of view, Will take out a lot of duplicate data, and these duplicate data is meaningless select DISTINCT gender from student;--*****************************************************- -Aggregate function: SUM (), AVG (), MAX (), MIN (), COUNT () SELECT * FROM student;--2, use aggregate function alone--count (*) Statistics records for non-empty fields statistics SELECT COUNT (*) as Number of records from student;--a field that does not have a null value in the contents of the field (primary key field) count (), the result is consistent with the number of records for COUNT (*), select COUNT (studentid) as record number from student;-- Count () for a field that does not have a null value in the contents of the field (non-empty field), and the number of records for COUNT (*) is consistent with the record count of select COUNT (studentname) as record number from student;-- Count () for a field with a null value in the contents of the field (nullable field), and the number of records for the Count (*) differs by a null value of the number of select COUNT (phone) as non-null record bars from student;-- Note: null values in the field contents are not counted by the count () of this aggregate function-the * in the count (*) function does not match all fields, but those non-empty fields select MAX (StudentID) as the most university from student; SELECT MIN (StudentID) as the primary school number from student; SELECT Max (phone) as maximum contact number from student; SELECT min (phone) as minimum contact number from student;--Note: null values in the field contents are not counted by the Max (), MIN () aggregate functions, alter TABLE student ADD score INT After PHONE;DESC student;        SELECT * from student;--this time, the Score score field (nullable) is added, the default value is null, SUM () and AVG () are null values for select SUM (Score) as the total score from student;    --Nullselect AVG (score) as average from student; --null--update random scores, test select ROUND (RAND () * 100); SELECT SUBSTRING (RAND () * 100, 1, 2);  --this form may result in a single digit plus a decimal point number--update the data in the table student SET score= ROUND (RAND ()* 100) WHERE StudentID in (1, 2, 8); SELECT SUM (score) as total score from student; SELECT avg (score) as average from student;--note: null values in field contents are not counted by the sum (), AVG ()-AVG (a field)= SUM (a field)/Count (a field) SELECT SUM (Score)/count (score) from student;--aggregate function Summary: Aggregate functions ignore null values

mysql< three >