MySQL database operations (6) query

Preparatory work

Create and enter the database:

CREATE DATABASE ' info ';  use ' info ';

#创建学院表:

CREATE TABLE  INTPRIMARYKEYVARCHAR (not NULL );

#创建学生表:

CREATE TABLE  INTPRIMARYKEYVARCHAR (not NULL   INT, FOREIGN KEY REFERENCES '   Department ' (' ID '));

#创建学生的详细信息表:

CREATE TABLE  intPRIMARYKEYintCHAR( 1  ),FOREIGNKEYREFERENCES  ' student ' (' s_id '));

#创建课程表:

CREATE TABLE  INTPRIMARYKEYVARCHAR (not NULL );

#创建中间表:

CREATE TABLE 'selectintint,PRIMARYKEY (' s_id ', ' c_id '), FOREIGN KEY REFERENCES ' student ' (' s_id '), FOREIGN KEY REFERENCES ' Course ' (' id '));

#查看当前存在的表:

SHOW TABLES;

#往学院表中添加数据:

INSERT  into ' Department ' (' name ') VALUES ('中文版'), ('Art'), ('computer '), ('Alchemy');

#往学生表中添加数据:

INSERT  into' student ' (' name ', ' dept_id ')VALUES('David',1),('Lucy',3),('Lily',3),('John',4),('Jack',2),('Alfred',3);

#往学生详细信息表中添加数据:

INSERT  intostu_detailsVALUES(1, -,'M'),(4, -,'M'),(3, -,'F'),(2, +,'F');

#往课程表中添加数据:

INSERT  into ' Course ' (' name ') VALUES ('Math'), ('Python'), ('Test '), ('Music');

#往中间表中添加数据:

INSERT  into`Select`VALUES(1,2),(1,4),(2,1),(2,4),(4,1),(4,2),(4,4);

Inquire

Query all records:
SELECT * from table name;
Cases:

SELECT *  from ' department '; SELECT *  from ' student '; SELECT *  from ' stu_details '; SELECT *  from ' Course '; SELECT *  from 'select';

Query the specified column records:

SELECT column name 1, column name 2 from table name;
Cases:

SELECT  from ' stu_details ';

Query for records under specified conditions:

SELECT column name from table name WHERE condition;
Cases:

SELECT  from WHERE Age<;

To alias a column after a query:

SELECT column name [as] alias from table name;
Aliases are only displayed on the table header
Cases:

SELECT  as  as  from WHERE s_id>=2; SELECT  from WHERE s_id>=2;

Fuzzy query:

SELECT column name from table name WHERE condition column like match string;
% matches 0~n characters
_ Match one character
Cases:

SELECT *  from WHERE  like ' l% ' ; SELECT *  from WHERE  like ' __fr% ';

Logical operator OR and:

Cases:

SELECT  from WHERE Age< Sex='M'; SELECT  from WHERE Age>=OR Sex='F' ;

Judged null, not =, can only be used is

Cases:

SELECT *  from WHERE  is NULL ; SELECT *  from WHERE  is  not NULL;

In, not in

SELECT *  from WHERE  not inch (1,3); SELECT *  from WHERE inch (1,3);

Sort ' ORDER BY ':

' ASC ' ascending (default) ' DESC ' descending
Cases:
Output by student number Ascending

SELECT *  from 'selectORDER by ' s_id ';

Output by Course ID Descending

SELECT *  from 'selectORDERbyDESC;

Limit the number of displayed data ' limit ':

LIMIT N starts at No. 0 and takes n in the back face
LIMIT M,n starts with section M and takes n in the back face
Cases:
Top 4 data in ascending order by student number

SELECT *  from 'selectORDERby4

4 Data starting from 2nd (data index starting from 0) in ascending order of students ' learning number

SELECT *  from 'selectORDERby2,4;

Packet query ' GROUP BY ':

GROUP by can only be used with aggregate functions, generally
The values within a grouping may be different columns, not used as a SELECT, for example: Select s_id, age, and sex from the stu_details group by sex; it is illegal because the elements in each sex group, s_id and age, are different.
Cases:

SELECT MAX  from GROUP  by sex;

Each element within a group is spelled into a string and delimited by the specified delimiter.

SELECT ' , '  from GROUP  by ' dept_id ';

Aggregation functions:

----------------------------------------------------
| AVG (column) | Returns the average of a column |
| COUNT (column) | Returns the number of rows in a column (not including NULL values) |
| COUNT (*) | Returns the number of rows selected |
| First (column) | Returns the value of the first record in a specified field |
| Last (column) | Returns the value of the last record in the specified field |
| MAX (column) | Returns the highest value of a column |
| MIN (column) | Returns the lowest value of a column |
| SUM (column) | Returns the sum of a column |
----------------------------------------------------
Having group conditions:
After having the field must be a select after, and you can use the alias
Where: Take the condition to the table and delete the selection.
Having:select out the result, and then another one to delete the election.
Cases:

SELECT dept_id,countas fromGROUP by has n=  1;

Sub-query
The select sentence that appears in other SQL statements.
1) nested inside the query
2) must always appear within parentheses
3) can contain multiple keywords or conditions
Take the result of another SQL as data and use it directly.
Cases:
# Find out the average age of students

SELECT AVG  from ' stu_details ';

#查找出大于平均年龄的数据

SELECT *  from WHERE ' age '>18.25;

#将平均数的SQL语句作为子查询放入上一条语句中

SELECT *  from WHERE ' age '>(SELECTAVG from ' stu_details ');

#选出学院为Computer和English的学生, select the Academy ID, and then find the student ID that matches.

SELECT *  from WHERE inch (SELECTfromWHERE in ('computer',' 中文版 ') );

MySQL database operations (6) query