Database Specialization Summary end (third day)

CREATE TABLE Student (

ID int (3) AUTO_INCREMEMT primary key NOT NULL,

Name varchar (TEN) default NULL,

Sex char (1) default NULL,

Cardid varchar (default) NULL,

Birthday datatime default NULL,

Email varchar (+) default NULL,

Class varchar (default) NULL,

Entertime datetime default NULL

) Engine=innodb default Charset=utf8;

CREATE TABLE Tsubject (

Subjectid nvarchar (10),

SubName nvarchar (30),

Bookename nvarchar (30),

Publiser nvarchar () 20

) Engine=innodb default Charset=utf8;

CREATE TABLE Score (

StudentID nvarchar (15),

Subjectid nvarchar (10),

Make decimal

) Engine=innodb default Charset=utf8;

SQL query Statements

1. Querying all fields

SELECT * FROM table;

2. Querying a specified field

Select field from table;

3. Querying a specified record

Select field from table where qualified content;

4. Query with in keyword

SELECT * FROM table where left (sname,1) in (' King ', ' Lee ');

5. Range query with Betweed and

SELECT * FROM table where convert (studentid,singned) between and 150;

6. String matching query with like

Shirane wildcard '% ' matches any length of character, even including 0 characters

Underline wildcard ' _ ' can only match any one character at a time

SELECT * FROM table where sname like ' _ zhi _ ';

SELECT * FROM table where sname like '% log% ';

7. Querying for null values

SELECT * FROM table where field is null;

8. Multi-criteria query with and

9. Multi-condition query with OR

10. Query results are not duplicated

Select distinct column from table;

11. Limit query rows with limit

Add the limit number directly after the command;

You can add two numbers to find out how many lines to start with.

12. Merging the results of the query

Using the Union keyword

Select column from table where condition Union select column from table where condition

(The columns are the same)

1. Alias the table

Select Table name as table alias;

2. Alias a field

Select column name as column alias;

Multi-Table Connection query

1. Internal connection Query

Select a.*,b.* from table 1 A join table 2 B on a. Column id=b. Column ID;

2. External connection

Left JOIN Connect Right

Right Join LEFT Join

3. Self-connect

Select a.*,b.* from table 1 a full join table 2 B on a. Column id=b. Column ID;

Sort the results of a query

ORDER BY

ASC from small to large

Desc from big to small

Group queries

is to group data by one or more fields

Aggregate function Query

Count () times

Select Class,count (*) from table group by class;

SUM () and

Select SUM (mark) from table;

AVG () Average

Max () max

Min () min

Sub-query

subquery for where type

The subquery of the where type is the condition of the inner query as the result of the outer query.

The given table is as follows:

CREATE TABLE article (

article_id Int (3),

Article_title varchar (50),

Article_content text,

article_comments Int (3),

articlecategory_id Int (3)

);

INSERT into article values (1, "fff1", "contteee", 55, 1);

INSERT into article values (2, "fff2", "conttffffffeee", 15,2);

INSERT into article values (3, "FFF3", "conttdgfdfdsfeee", 515, 1);

INSERT into article values (4, "Fff4", "Conttesdfsdfsee", 505, 1);

INSERT into article values (5, "Fff5", "Conttesdfsdfee", 545,2);

INSERT into article values (6, "Fff6", "Conttesdfsee", 575,2);

INSERT into article values (7, "Fff7", "Conttesdfsdee", 5, 1);

INSERT into article values (8, "Fff8", "Conttesdfsdfee", 77, 1);

Example: Select Article_id,article_title,article_content from article where article_comments in (select Max (article_comments) From article group by articlecategory_id);

From sub-query

The from subquery is the result of a query (a table in memory) that is treated as a temporary table and then processed.

From subquery solves the problem above

such as: Select Tmp.article_id,tmp.article_content,article_comments from (SELECT * from article ORDER by articlecategory_id, Article_comments desc) as TMP GROUP by TMP.ARTICLECATEGORY_ID;

Exists sub-query

Definition: Exists sub-query is the outer layer of the table to cycle, and then internal query internal table. Similar to In (), but they are still different. The main point is to see the extent of the two table size difference.

If the subquery table is larger with exists (inner index), the Sub-query table is small with in (outer index);

Pattern Description

^ matches the starting position of the input string. If the Multiline property of the RegExp object is set, ^ also matches the position after ' \ n ' or ' \ R '.

$ matches the end position of the input string. If the Multiline property of the RegExp object is set, $ also matches the position before ' \ n ' or ' \ R '.

. matches any single character except "\ n". To match any character including ' \ n ', use a pattern like ' [. \ n] '.

[...] The character set is combined. Matches any one of the characters contained. For example, ' [ABC] ' can match ' a ' in ' plain '.

[^...] negative Character set. Matches any character that is not contained. For example, ' [^ABC] ' can match ' P ' in ' plain '.

P1|p2|p3 matches p1 or P2 or p3. For example, ' Z|food ' can match "z" or "food". ' (z|f) Ood ' matches "Zood" or "food".

* matches the preceding subexpression 0 or more times. For example, zo* can match "z" and "Zoo". * Equivalent to {0,}.

+ matches the preceding subexpression one or more times. For example, ' zo+ ' can match "Zo" and "Zoo", but not "Z". + equivalent to {1,}.

N N is a non-negative integer. Matches the determined n times. For example, ' o{2} ' cannot match ' o ' in ' Bob ', but can match two o in ' food '.

{N,m} both M and n are non-negative integers, where n <= m. Matches at least n times and matches up to M times.

Instance

Knowing the regular requirements above, we can write SQL statements with regular expressions more of our own needs. Below we will list a few small instances (table name: PERSON_TBL) to deepen our understanding:

Look for all the data in the Name field that begins with ' St ':

mysql> SELECT name from person_tbl WHERE name REGEXP ' ^st ';

Look for all the data that ends with ' OK ' in the Name field:

mysql> SELECT name from person_tbl WHERE name REGEXP ' ok$ ';

Find all data that contains the ' Mar ' string in the Name field:

mysql> SELECT name from person_tbl WHERE name REGEXP ' Mar ';

Find all data in the Name field that begins with a vowel character or ends with an ' OK ' string:

mysql> SELECT name from person_tbl WHERE name REGEXP ' ^[aeiou]|ok$ ';

This article from "Big Plum" blog, declined reprint!

Database Specialization Summary end (third day)