MySQL select query command under Linux

MySQL select query command under Linux

1. Select query Format:
   SELECT field from table WHERE condition;

For example: SELECT * from table name; #查询表所有内容

SELECT Field 1, Field 2 ...       from table name; #投影 to display only the content in the specified field

SELECT [DISTINCT] * from table name WHERE condition; #选择, show only the contents of all fields that match the criteria section of the row

SELECT * from students;

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M00/83/A5/wKiom1d5wHejv2LkAADke385RQs373.jpg-wh_500x0-wm_3 -wmp_4-s_89572525.jpg "title=" 1.jpg "alt=" Wkiom1d5whejv2lkaadke385rqs373.jpg-wh_50 "/>

SELECT name,age from students;

SELECT * FROM students WHERE age>=20;

SELECT name,age from students WHERE age>=20;

SELECT name,age from students WHERE gender= ' F ';

SELECT DISTINCT Gender from students; #DISTINCT仅显示Gender字段中的相同值只显示一次

2. Query statement type: Simple query, multi-table query, sub-query

FROM clause: Relational table, multiple tables, other SELECT statements to query

WHERE clause: boolean-relational expression,

2.1. The comparison operators are as follows: =, >, >=, <=, <

SELECT name,age from students WHERE age+1>20;

2.2. Logical relationship: and OR not

SELECT Name,age,gender from students WHERE age>20 and gender= ' F ';

SELECT Name,age,gender from students WHERE age>20 OR gender= ' F ';

SELECT Name,age,gender from students WHERE not age>20 ';

SELECT Name,age,gender from students WHERE not age>20 and not gender= ' F ';

SELECT Name,age,gender from students WHERE not (age>20 OR gender= ' F ');

2.3.BETWEEN ... And ... #在AND两值之间

SELECT name,age from students WHERE age>=20 and age<=25;

SELECT Name,age from students WHERE age between and 25;

2.4.LIKE "

%: Any character of any length

_: Any single character

SELECT name from students WHERE Name is like ' Y% ';

SELECT name from students WHERE Name is like ' y____ ';

SELECT name from students WHERE Name is like '%willow% ';

2.5.REGEXP, rlike (regular expression)

SELECT name from students WHERE Name rlike ' ^[lny].*$ ';

2.6.IN (value 1, value 2,...)

SELECT Name,age from Students WHERE age in (20,30,40);

2.7.IS NULL

SELECT Name from students WHERE CID2 is NULL;

2.8.IS not NULL

SELECT Name from students WHERE CID2 are not NULL;

2.9.ORDER by field name {asc| DESC} #排序, ascending by default

SELECT Name from students WHERE CID2 is NULL for ORDER by Name;

SELECT Name from students WHERE CID2 are NULL ORDER by Name DESC;

2.10.AS, referencing field aliases or table aliases

SELECT Name as student_name from students;

2.11.LIMIT clause: LIMIT [offset row,] display row

SELECT Name as Student_name from students LIMIT 2;

SELECT Name as Student_name from students LIMIT 2, 3;

2.12. Aggregation: SUM (), MIN (), MAX (), AVG (), COUNT ()

SELECT AVG (age) from students;

SELECT MAX (age) from students;

SELECT MIN (age) from students;

SELECT SUM (age) from students;

SELECT COUNT (age) from students;

2.13.GROUP by: Grouping

SELECT AVG (age) from students WHERE gender= ' M ';

SELECT AVG (age) from students WHERE GROUP by Gender;

SELECT COUNT (CID1) as persons,cid1 from students GROUP by CID1;

2.14.HAVING conditions (must be used with GROUP by)

SELECT COUNT (CID1) as persons,cid1 from students GROUP by CID1 have persons>=2;

3. Multi-Table query:

3.1. Cross-linking: Cartesian product

SELECT * from Students,courses;

3.2. Natural connection:

SELECT * from students,courses WHERE students. CID1 = courses. CID;

SELECT students. Name,courses. Cname from students,courses WHERE students. CID1 = courses. CID;

SELECT S.name,c.cname from students as s,courses as C WHERE s.cid1 = C.cid;

3.3. Left Outer connection: ... Left JOIN ... On ... #以左表为基准

SELECT S.name,c.cname from students as s left JOIN courses as C on s.cid1 = C.cid;

3.4. Right outer connection: ... Right JOIN ... On ... #以右表为基准

SELECT S.name,c.cname from students as s right joins courses as C on s.cid1 = C.cid;

3.5. Self-Connection: self-connection in the same table

SELECT C.name as student,s.name as teacher from students as c,students as S WHERE c.tid=s.sid;

4. Sub-query:

4.1. Using subqueries in comparison operations: Subqueries can only return a single value;

Select Name from Students WHERE > (SELECT AVG (age) from students);

4.2.IN (): Use sub-query;

Select Name from Students WHERE-age-in (SELECT-age from tutors);

4.3. Using sub-queries in from;

Select Name,age from (select Name,age from students) as S WHERE s.age >= 20;

4.4. Joint query: union

(SELECT name,age from students) UNION (SELECT tname,age from tutors);

5. Comprehensive complex query case:

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M02/83/A7/wKioL1d50y6xGCSdAAEOJCGQWC8728.jpg-wh_500x0-wm_3 -wmp_4-s_2314017051.jpg "title=" 2.jpg "alt=" wkiol1d50y6xgcsdaaeojcgqwc8728.jpg-wh_50 "/>650) this.width=650;" Src= "http://s5.51cto.com/wyfs02/M00/83/A7/wKioL1d50z2i9JjUAADke385RQs675.jpg-wh_500x0-wm_3-wmp_4-s_1965359739. JPG "title=" 1.jpg "alt=" Wkiol1d50z2i9jjuaadke385rqs675.jpg-wh_50 "/>

5.1. Find out the course name of the course in the courses table that is not students selected CID2;

Select Cname from Courses the where CID not in (the Select DISTINCT CID2 from students where CID2 are not NULL);

5.2. Find a teacher who does not teach any courses

Select Tname from Tutors WHERE TID is not in (SELECT DISTINCT TID from courses);

Find out the course name of the same course that CID1 has two (including) students in the students table

Select Cname from Courses WHERE CID in (SELECT CID1 from students GROUP by CID1 have COUNT (CID1) >= 2);

5.3. Identify each teacher and the courses he or she teaches; a course that is not taught is kept null

SELECT T.tname,c.cname from tutors as T left joins courses as C on T.tid=c.tid;

5.4. Identify each of the courses and their associated teachers, no courses taught by the teacher to show their teachers null

SELECT T.tname,c.cname from tutors as T right joins courses as C on T.tid=c.tid;

5.5. Identify the course name of each student's CID1 course and the name of the teacher who taught the relevant course

SELECT name,cname,tname from Students,courses,tutors WHERE students. Cid1=courses. CID;

6. View: Is the storage of the SELECT statement, that is, based on the base table query results;

6.1. Create a view:

CREATE View name as SELECT statement;

CREATE VIEW Sct_viewname as SELECT name,cname,tname from Students,courses,tutors WHERE students. Cid1=courses. CID;

SELECT * from Sct_viewname;

Show TABLES; You can view this view as a table

SHOW TABLE Status\g

6.2. Delete the view:

DROP View Name

6.3. View the CREATE VIEW command

SHOW CREATE viwe view name;

6.4. View the CREATE TABLE command

SHOW CREATE table table name;

This article is from the "Xavier Willow" blog, please be sure to keep this source http://willow.blog.51cto.com/6574604/1795548

MySQL select query command under Linux