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:

1. 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

1. 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;
   

2. 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:

1. 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.
   

1. Encapsulate some cumbersome Association queries. When used again in the future, you can query the view directly
2. Simplify our query data

Simple classification

1. Simple view - the view is a query from a single table
2. 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

1. Query for view - DQL

   select * from stu_view;
   

2. 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   |
   

1. 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

1. DB,DBA,DBS,DBMS,RDBMS

2. The difference between delete and truncate and drop

   delete - DML
   truncate - DDL
   -- Empty table
   truncate Table name;
   
   drop  - ddl - Delete table
   

3. Database optimization - sql optimization

4. Pessimistic lock and optimistic lock

5. Difference between innodb and myisam

6. Index underlying principle [innodb and myisam] - b + tree

7. Clustered index and non clustered index [secondary index]

8. Isolation level of transaction

9. Dirty reading, non repeatable reading, repeatable reading * *, unreal reading**

10. DQL query statement - Project

11. Differences between stored procedures and functions

12. Advantages of view

13. Three paradigm concepts

14. SQL is classified by function [DCL,DTL,DML,DQL,DDL]