limit statement
The application scenario is - the data in the web page [must be in mysql] must be paged
-- Get the top in the table m Row data. -- limit m mysql> select * from s_emp limit 3;
-- limit m,n -- m Represents a row subscript,Start from 0 -- n Represents the number of items displayed -- Start on line 3,Display 5 mysql> select * from s_emp limit 2,5;
Pagination formula
Scenario:
-
For users, how many pages to click, such as selecting 1 page, is considered the first page
pageNow - current page. pageSize - number of items displayed per page
limit (pageNow-1)*pageSize,pageSize
Part of sql optimization
limit m,n
m - offset n - number of bars displayed
If the offset is large, the query performance of limit statement will be low
Two common scenarios for limit optimization
-
Suppose that the name column in the stu table does not have an index added, but the name should be unique from a professional point of view
select * from stu where name='tom'; -- A full table scan will certainly be performed. - Data volume - Resulting in inefficient query explain select * from stu where name='tom'; -- Confirmed name Column values must be unique - Avoid full table scanning select * from stu where name='tom' limit 1;
-
The precondition is that the id primary key column is self increasing and continuous. Since the limit m,n increases with the m [offset], the query efficiency is low
select * from s_emp limit 10000,2; optimization select * from s_emp where id>10000 limit 2;
view
View - virtual table is similar to the "mirror" of the original table. How did you operate the table and view before
Advantages:
-
Ensure data security
For example, there are a lot of hidden data in a table, and you don't want the programmer to see your original table, such as emp(id,name,salary,birthday)
Secret data, such as salary and birthday. In order to ensure the security of data, db [Database Administrator] can create views for emp
create view emp_view as select id,name from emp; Next, the programmer's operation,Directly operate on the view[select,insert,update,delete] - > It will also have an impact on the original table. But delete the view,The original table will not be deleted.But delete the original table,The view must not exist.
- Encapsulate some cumbersome Association queries. When used again in the future, you can query the view directly
- Simplify our query data
Simple classification
- Simple view - the view is a query from a single table
- Complex view - view from associated query [multiple tables]
Create view
create view View name as select sentence;
by student Table to create a simple view create view stu_view as select * from student;
Delete view
drop view View name;
Operation view
-
Query for view - DQL
select * from stu_view;
-
Modify the original table to see if it affects the view - yes
update student set sname='admin' where id=1; select * from stu_view; | 1 | admin | 1990-01-01 00:00:00 | male |
- Can I perform dml operations on simple views? Yes
dml operations on the view will also affect the original table
update stu_view set sname='success' where id=1;
Complex view
Complex view represents the source of the whole view, which is the query of associated table - which is used to be queried
-- Query the area name and the name of the employees in this area first_name drop view region_view; create view region_view as select r.name,e.first_name from s_region r join s_dept d on d.region_id = r.id join s_emp e on e.dept_id = d.id;
select * from region_view;
One difference between it and simple view is that complex view cannot perform DML operations
delete from region_view where name = 'North America'; ERROR 1395 (HY000): Can not delete from join view 'j03s.region_view'
with check option
drop view stu_view; create view stu_view as select id,sname from student where id=13; select * from stu_view; -- update the view - The updated one is the one from the view source sql of where Condition columns in update stu_view set id=100 where id=13; -- The view is gone - It is no longer in the original table - Updates to the view are synchronized to the original table[It has an impact on the original table] mysql> select * from stu_view; Empty set (0.00 sec) -- The view is not allowed to update the one from its own source sql Medium where Condition column. drop view stu_view; create view stu_view as select id,sname from student where id=11 with check option; mysql> select * from stu_view; +------+--------+ | id | sname | +------+--------+ | 11 | Li Si | +------+--------+ -- testing with check option Effect of - When updating the view,Yes, updates are not allowed id column update stu_view set id=900 where id=11; ERROR 1369 (HY000): CHECK OPTION failed 'j03s.stu_view'
Summarize interview questions
-
DB,DBA,DBS,DBMS,RDBMS
-
The difference between delete and truncate and drop
delete - DML truncate - DDL -- Empty table truncate Table name; drop - ddl - Delete table
-
Database optimization - sql optimization
-
Pessimistic lock and optimistic lock
-
Difference between innodb and myisam
-
Index underlying principle [innodb and myisam] - b + tree
-
Clustered index and non clustered index [secondary index]
-
Isolation level of transaction
-
Dirty reading, non repeatable reading, repeatable reading * *, unreal reading**
-
DQL query statement - Project
-
Differences between stored procedures and functions
-
Advantages of view
-
Three paradigm concepts
-
SQL is classified by function [DCL,DTL,DML,DQL,DDL]