DML of SQL statements

Dml:database Manipulate languagedml Include:select,delete,insert into,updateaka = =also known as

• Select
  

Execution sequence Start-to- [From--[where]--[GROUP by]--[having]--[ORDER by] ]- Select--[limit]--endselect select_list from Tb_name where Condition;select query type 1) simple query, single table query 2) Multi-table Query 3) subquery, nested query
AllSELECT * from Tb_name;SelectSELECT * from tb_name where condition;projectionSELECT [DISTINCT] field1,filed2,... from Tb_name; [DISTINCT] Remove duplicates option

Single-Table Query

• FROM clause, which represents the relationship to query, tables, multiple tables, other SELECT statements
  
• WHERE clause
  

Logical relationship Operators

and	&&
Or	||
Not	!

Conditional Judgment operator>,<,=,<=>,between,in,like,rkike aka Regexp,is null,is not NULL<=> null safe euqal to;

Examplemysql> Select Name,age,gender from students where not age>20 and not gender= ' M ';
Mysql> Select Name,age,gender from students where age between and 25; Continuous valuemysql> Select Name,age from students where age in (19,25); discrete valuesMysql> select name from students where name like ' l% ';mysql> select name from students where name is like ' y____ '; MySQL > select Name,age,gender from students where name Rlike ' ^[yxn].* ';

• order by clause, sort
  

Order BY Field_name [asc| Desc]mysql> Select Name,age from students order by age DESC;

• Limit [Offset,]count clause, limiting the number of rows displayed

Mysql> select name as student_name,age as Age_number from students order BY age DESC limit 2, 4;

• As field alias
  

Mysql> select name as student_name,age as Age_number from students order by age DESC;

Aggregation: Sum (), Min (), Max (), AVG (), COUNT () has been used to filter the results of GROUP by again mysql> select Cid1 as Course,count (CID1) as Total from Students group by CID1;
Mysql> Select Cid1 as Course,count (CID1) as total from students group by CID1 havingtotal>=2;
Multi-Table QueryConnection:

• Cross-linking: Cartesian product, not suitable for large data volumes
  
• Natural connection
• External connection
  

  left outer connection: ... Left JOIN ... On ...  right outer connection: ... Right JOIN ... On ...

• Self-linking, a table becomes two tables
  

Natural connection mysql> Select Name,cname from students,courses whereCOURSES.CID=STUDENTS.CID1;
Mysql> Select Students.name,courses.cname from students,courses where courses.cid=students.cid1; standard notation
Mysql> Select S.name,c.cname from students as s,courses as C where c.cid=s.cid1; Alias of Table

External connection
Left outer connection mysql> select S.name,c.cname from students as S ieft join courses as C onC.CID=S.CID1;
Right outer connection mysql> select S.name,c.cname from students as s starboard join courses as C onC.CID=S.CID1;

Self-connected mysql> select S.name as student,t.name as teacher from students as s,students as T where T.sid=s.tid;
Sub-query1) using subqueries in comparison operations: Subqueries can only return a single value;mysql> select Name,age from students where age> (select AVG (age) from students);
2) in (): Use sub-query;mysql> select name from students where the age of (select age from Tutors);
3) using subqueries in from, can be rewritten as multi-conditional Query union query Union, do not Judge mysql> (select Name,age from students) union (select Tname,age from Tutors) ;
Mysql> (select Name,age from students) union (select Tname,gender from tutors);

Exercise 1, pick out the course name of a course that is not students by CID2 in the Courses table;mysql> Select CNAME from courses where CID not in (select distinct Cid2 fro M students where CID2 is not null);
Add: Select a teacher who does not teach any course, and the correspondence between each teacher and his/her teaching course in the Courses table;mysql> select Tname from tutors where Tutors.tid not in (select DISTINCT Courses.tid from courses);
        Find out the course name of the same course that two or two students have studied in the students table CID1;Mysql> Select CNAME from courses where courses.cid in (select Cid1 from students GROUP by CID1 have Count(CID1) >=2);
2. Show each teacher and the course he teaches; Keep the course without professors for null;mysql> select Tname,cname from Tutors Left joinCourses onTutors.tid=courses.tid;
3. Show each course and its related teacher, no teacher teaches the course to show his teacher as empty;mysql> select Tname,cname from Tutors Right JoinCourses onTutors.tid=courses.tid;
4. Display the course name of each student's CID1 course and the name of the teacher who taught the relevant course;mysql> select Name,cname,tname from students,courses,tutors where students.cid1 =courses.cid and Courses.tid=tutors.tid;
View View Virtual table      A SELECT query statement that is stored based on a base table
Creating views Create View View_name As select_statementMysql> CREATE View information as select Name,cname,tname from Students,courses,tutors where students.cid1= Courses.cid and Courses.tid=tutors.tid;

Delete Views Drop view View_name
Note: Because the SELECT statement is executed every time the view is viewed, performance may not be much improved because the view is not indexed, but the materialized view (which is not supported by MySQL) is fast, and the view can be used in situations where only the user wants to see certain fields and increase security.
Insert INSERT[Low_priority | DELAYED | High_priority] [IGNORE][ into] tbl_name [ (Col_name,...)]{VALUES | VALUE} ({expr | DEFAULT},...), (...),...[on DUPLICATE KEY UPDATEcol_name=expr[, col_name=expr] ...]
Or: Inserts a row of data for a specified field at a time INSERT[Low_priority | DELAYED | High_priority] [IGNORE][ into] tbl_nameSET col_name={expr | DEFAULT}, ...[on DUPLICATE KEY UPDATEcol_name=expr[, col_name=expr] ...]Examplemysql> INSERT INTO students set Name= ' Agan ', age=26,gender= ' M ', Cid1=8,cid2 =7,tid=6,createtime= ' 2016-05-26 18:01:30 ';

Or: Add the queried data to the table insert [low_priority | High_priority] [IGNORE][into] tbl_name [(col_name,...)]SELECT ...[on DUPLICATE KEY UPDATEcol_name=expr[, col_name=expr] ...]Mysql> INSERT INTO Tutors (tname,gender,age) Select Name,gender,age from students where age > 10;

Show only the last inserted dataMysql> SELECT * FROM student ORDER BY SIDs DESC LIMIT 1,2;mysql> SELECT * FROM student ORDER BY Sid DESC Limit 0, 2;
Display Counter mysql> select last_insert_id ();
Problems: Using the CREATE table tb_name1 like Tb_name2, with the auto_increment option, use the same counter and use between two tables Auto_ The count of the increment field will accumulate the replace usage and insert similar

• Delete

1) When using Delete, where is not used, you can suppress this operation, 2) The counter is unchanged, continue counting on the original basis mysql> delete from the student order by Sid DESC Limit 10;

• TRUNCATE TABLE

TRUNCATE [table] tbl_name clears the table and empties the auto_increment counter

• Update
  

UPDATE tb_name SET col1= ..., col2= ... WHERE




From for notes (Wiz)

DML of SQL statements