Operations such as subqueries in MySQL

1.delete and truncate difference?
1.1 Delete slow, truncate fast

1.20delete logging, so slow truncate does not log

1.31 when all records are deleted, the primary key value Delete is not numbered from 1, and truncate is numbered from 1 onwards

1.43 Truncate cannot delete a primary table with a foreign key

1.5 Delete can add where to delete partial records

2. Two kinds of engines
InnoDB: Support Transactions
MyISAM:

3. Add insert into grade (field name) values (corresponding)
Modify Update table name set column name = ' new value ' where primary key =xxxx
Remove delete from table name where primary key =yyy

4.5 Types of Constraints
CHECK constraints
PRIMARY KEY constraint
FOREIGN KEY constraints
Non-null constraint
Default value Constraint
Unique constraint

1. Two scenarios for one-time entry of N records into a table
Programme one:
Requirement: The target table does not exist
CREATE table New Table (SELECT * from student)

Scenario Two:
Requirement: The target table must exist
Insert into new table name select * FROM original table
2. Simple query about * will cause a full table scan problem

3. Find the name "Cold Rain" information
SELECT * FROM Student
WHERE studentname= ' slightly cold rain '

4. Precautions
--01.sql keyword is case insensitive

--02. String and date types are required [[[[[[[Single quotes]]]

--03. Do not get up too well when starting a table name, for example, do not use user as the table name.
--If the table name and the keyword are duplicated, we can remove the escape by '.
SELECT * from ' User '

--04. Be careful about the Chinese spaces in the new query window.
SELECT * FROM Message
5. Aliases
Alias scheme as
6. Constant columns
SELECT *, ' Han ' as National from student
7. Explanation of NULL
Must understand

8. Aggregation functions
Count: Count the total number of data in a single table
Select SUM (studentresult) as total score from Result
Select AVG (studentresult) as average split from Result
Select MAX (Studentresult) as highest score from Result
Select min (studentresult) as highest score from Result
9. Common functions
9.1 String Functions
9.2 Date-time functions
9.3 Mathematical Functions
10.Order
11. Sort Limit

Code

# #检索所有邮箱为NULL的学生
SELECT * FROM student WHERE Email
# #如果说有一条记录真实值 ' xxx ' How do you lock
# # #统计成绩表中有几条记录?
SELECT SUM (studentresult) from result
# #字符串函数
SELECT UPPER (' abcd Tomorrow is Friday, pay attention to the presentation of the speech ')
# # #截取
SELECT SUBSTRING (' T14 is explaining MySQL, after the end there is an exam ', 8,5)
# # #字符串拼接
SELECT CONCAT (' str1 ', ' str2 ', ' STR3 ')
# # #替换函数
SELECT INSERT (' This is MySQL database ', 3,5, ' Oracle ')
# # #在mYSQL获取默认的时间
SELECT Now ()
# # # #只获取系统的日期, don't get time
SELECT Curdate ()
# # # #只获取系统时间
SELECT Curtime ()
# # #获取当前日期是该年中的第几周?
SELECT WEEK (now ())
# # #查看强哥已经活了多少天
SELECT DATEDIFF (now (), ' 1992-06-01 ')
# # #SQL标准 SQL 99
SELECT Adddate (now (),-3)

# # # #数学函数 ceil () The ceiling is rounded up floor () to fetch the entire rand () random number

SELECT ceil (2.0001)

# # # #Order by Sort by field
SELECT * FROM student ORDER by Sex,borndate DESC

# # #分页 Limit parameter One (the page data starting from the first, subscript starting from 0), Parameter 2 (page size/page shows the number of records)
SELECT * FROM student LIMIT 2,2
# # #练习
# # #1. Check the number of students and their scores for the first 5 exams on February 17, 2016
SELECT Studentno,studentresult
From result
WHERE examdate>= ' 2017-07-13 ' and examdate< ' 2017-07-14 '
ORDER by Studentresult DESC LIMIT 5
SELECT * from result
# # #2. Sort all female students by age from the largest to the youngest, starting with the 2nd record showing the name, age, date of birth, cell phone number of 6 female students
SELECT Studentname,ceil (DATEDIFF (now (), borndate)/365) as age, Borndate,phone
From student
WHERE sex= ' man '
ORDER by age DESC
LIMIT 1,6

# # #3. Check the highest score, lowest score, average score for all participants who participated in the February 17, 2016 exam
SELECT MAX (Studentresult) as highest score, min (studentresult) as minimum, AVG (Studentresult) as average
From result
WHERE examdate>= ' 2017-7-13 ' and examdate< ' 2017-7-14 '

SELECT * from result

# # #子查询 must know what is a subquery select * FROM (subquery)
# # #年龄比彪哥小的学生信息
SELECT * FROM student where borndate< (select Borndate from student where studentname= ' Puma ')

# #查询参加 the highest and lowest score of the most recent Logic Java exam score
SELECT MAX (Studentresult) as highest score, min (studentresult) as Min.
from result< Br>where examdate= (select MAX (examdate) from the result where subjectid= (select Subjectid from SUBJECT WHERE subjectname= ' language ' )
and subjectid= (SELECT subjectid from SUBJECT WHERE subjectname= ' languages ')

# # #查询 The name of the student with a 60 javalogic test score
SELECT studentname from student
WHERE Studentno in
(
SELECT Studentno from Result
where studentresult=77
and subjectid=
(
SELECT subjectid from SUBJECT
where subjectname= ' languages '
)
)
Select * from Result
# # # #查询 The most recent list of students who have not taken the language exam (studentname)
SELECT studentname from student
Wher E Studentno not in
(
Select Studentno from result
WHERE subjectid=
(
SELECT subjectid from SUBJECT
Where subjectname= ' language '
) and examdate=
(
SELECT MAX (examdate) from result
WHERE subjectid=
(
SEL ECT Subjectid from SUBJECT
WHERE subjectname= ' languages '
)
)
)
and gradeid=
(
SELECT Gradeid from S Ubject
WHERE subjectname= ' language '
)

# #检查 "Logic Java" course last exam results
# #如果有 more than 80 points, showing the number of students ranked in the top 5 and the score
SELECT Studentno,studentresult from result
WHERE EXISTS
(
SELECT * from result
WHERE subjectid=
(
SELECT Subjectid from SUBJECT
WHERE subjectname= ' language '
)
and examdate=
(
SELECT MAX (examdate) from result
WHERE subjectid=
(
SELECT Subjectid from SUBJECT
WHERE subjectname= ' language '
)
)
and studentresult>80

)
and subjectid=
(
SELECT Subjectid from SUBJECT
WHERE subjectname= ' language '
)
and examdate=
(
SELECT MAX (examdate) from result
WHERE subjectid=
(
SELECT Subjectid from SUBJECT
WHERE subjectname= ' language '
)
)
and studentresult>60
ORDER by Studentresult DESC
LIMIT 5

SELECT * FROM Student
WHERE EXISTS
(
SELECT * from result WHERE studentresult>100
)

Operations such as subqueries in MySQL