Query for Data

First, query all columns:

SELECT * from student;

Second, the query specified column

SELECT Id,name,gender from student;

Third, the query specified alias (AS)
Note: table aliases are frequently used in multiple table queries, student as s (note only characters, not strings).

SELECT ID as ' number ', name as ' name ' from student;

Add constant columns when querying
Requirements: Add a constant column (class column) when querying the student table for the Java jobs class.

SELECT id,name,gender,age, ' Java Employment class ' as ' Grade ' from  student;

Merge columns when querying
Requirements: Query the total score of each student's servlet and JSP

SELECT Id,name, (servlet+jsp) as ' total score ' from student;

Note: merge columns can only merge fields of numeric types, and non-numeric types cannot be merged

SELECT ID, (name+servlet) from student;

Vi. removing duplicate records when querying (DISTINCT)
Needs: Inquiring about gender of students

SELECT DISTINCT gender from student;

Another syntax distinct () represents a function

SELECT DISTINCT (gender) from student;

Vii. conditional query (where)
1. Logical conditions: and (with) or (or)
Requirements: The student with the name Dick 2 with the query ID

--Intersection
SELECT * from student WHERE id=2 and name= ' Dick ';

Requirements: Query ID 2, or student with name John

--and set

2. Comparison conditions: > < >= <= = <> (not equal to) between and (equivalent to >= and <=)
Requirements: Query servlet students with more than 70 score

SELECT * FROM student WHERE servlet>70;

Requirements: Query JSP students with a score greater than or equal to 75, and less than 90 points

SELECT * FROM student WHERE jsp>=75 and jsp<=90;

Another syntax

--(after package)

Requirements: Inquiring about gender not boys ' students

SELECT * FROM student WHERE gender<> ' male ';

3. Null null string: Is null/is not NULL/= "(equal to NULL)/<>" (not equal to null)
Requirements: Students with empty query addresses (including null and empty strings)

--(including null and empty strings)

difference between null and empty string: null: Indicates no value, empty string: Value
Judge Null (=null is not correct, must be null)

SELECT * FROM Student WHERE the is NULL;

To judge an empty string

SELECT * FROM student WHERE address= ';

Requirements: Query for students with addresses (excluding null and empty strings)

SELECT * FROM student WHERE, not NULL and address<> ';

4, fuzzy conditions: Like
The following substitution marks are usually used:
1)%: represents any character
2) _: denotes a character
Demand: The student whose surname ' Zhang ' is queried

SELECT * FROM student WHERE NAME like ' Zhang% ';

Requirement: The student surnamed ' Li ' with a name of only two words

SELECT * FROM student WHERE NAME like ' li _ ';

VIII. aggregate queries (queries using aggregate functions)
Common aggregate functions: SUM () avg () max () min () count ()
Requirements: Query the student's Servlet's total score (sum (): Sum function)

The total score of the SELECT SUM (servlet) as ' servlet ' from student;

Requirements: Check the average score of the student's servlet

The average score of the SELECT avg (servlet) as ' servlet ' from student;

Requirements: Querying the current servlet highest score

SELECT MAX (servlet) as ' highest score ' from student;

Requirements: Minimum points for enquiries

SELECT MIN (servlet) as ' min ' from student;

Requirements: Statistics How many students are currently (count (field))

SELECT COUNT (*) from student;

Requirements: Count the number of students by ID
SELECT COUNT (ID) from student;
Note: The count () function counts the number of data that does not contain NULL, but contains an empty string so the number of records using the Count table is used to use segments that do not contain null values.

Nine, paging query (limit start line, query a few lines)
1, starting line starting from 0
Paging: How many pages are displayed on the current page per page
2, paging query the current page of the data sql:
SELECT * FROM Student LIMIT (current page-1) * How many shows per page, how many per page;

3, Requirements: Query 1th, 2 records (1th page of data)

SELECT * FROM student LIMIT 0, 2;

4, Requirements: Query 3rd, 4 Records (2nd page of data)

SELECT * FROM Student LIMIT 2, 2;

5, Requirements: Query 5th, 6 Records (3rd page of data)

SELECT * FROM Student LIMIT 4, 2;

6, Requirements: Query 7th, 8 Records (no record does not show)

SELECT * FROM student LIMIT 6, 2;

Ten, query sort (order by)
1. Syntax: ORDER BY Field Asc/desc
2, ASC: order, positive sequence. Value: Ascending, Letter: Natural order (A-Z)
3, desc: reverse, reverse sequence. Numeric value: Descending, Letter: Natural reverse sequence (z-a)
4, by default, sorted by the order of insertion records.

5, requirements: sorted by ID Order

SELECT * FROM student the order by ID ASC;
SELECT * FROM student the order by ID; --Default positive sequence

6, requirements: sorted by ID reverse order

SELECT * FROM student ORDER by ID desc;--reverse sequence

7. Note: Multiple sorting criteria
Requirements: In accordance with the servlet positive sequence, according to the reverse JSP
SELECT * FROM Student ORDER by servlet asc,jsp DESC;
Results: The student table is sorted according to the positive sequence of the servlet, and then the rows of the same servlet are arranged in reverse order of the JSP.

Xi. grouped Queries (group by)
Needs: Enquiries about the number of men and women
Steps:
1 The students are grouped by sex (group by gender)
2 count the number of persons per group (*)

SELECT Gender,count (*) from student GROUP by gender;

12. Filter after group query
Needs: Enquiries are more than 2 of the total number of sex
Steps:
1. Check the number of men and women
2) screening of records with a population greater than 2 (having)
Note: Before grouping conditions use the WHERE keyword, before grouping conditions using the HAVING keyword

SELECT Gender,count (*) from student WHERE GROUP by gender has COUNT (*) >2;

XI. Classification of SQL statements:
1, DDL: Data definition language, such as Create/drop/alter
2, DML: Data manipulation statements, such as Insert/delete/update/truncate
3, DQL: Data query language, such as Select/show