1.SQL protocol

1. Statute of Forms

Required three fields:

create table `demo` (
  `id` bigint(32) not null auto_increment comment 'Primary key',
  `gmt_create` timestamp not null default current_timestamp comment 'Active Creation Time',
  `gmt_modified` timestamp not null default current_timestamp on update current_timestamp comment 'Passive Update Time',
  primary key (`id`)
) 

Use decimal (8,2) type for price field

2. Index Protocol

2.SQL optimization

1. Reasonable use of connections

1. Inner Connection and Left Connection

//Query student Information + Scores, Implicit Internal Link, student Information Table, student Courrce,
//Return only records with the same number in two tables
select * from student s, studentcource sc where s.id = sc.sid;

//Left join, with left table as the main table, returns all rows in the left table and joins with the right table, even if the right table does not have row data corresponding to the left table, it needs to return null
//Change the right connection to studentcource sc left student s
//Full join equals all rows of left join plus all rows of right join
select * from student s left join studentcource sc on s.id = sc.sid;

//Add a where condition to the left join, specifying the name of the query
select * from student s left join studentcource sc on s.id = sc.sid where name = 'Tom';

2. The cost of paradigms and connections

An understanding of the three paradigms may refer to the article http://blog.csdn.net/wangqyoho/article/details/52900585 Generally speaking, a database only needs to satisfy the third paradigm (3NF):

Three Paradigms
 1NF: Field is inseparable; 
2NF: There are primary keys, non-primary key fields depend on primary keys; 
3NF: Non-primary key fields cannot be interdependent; 

Explanation: 
1NF: Atomic fields are no longer separable, otherwise they are not relational databases; 
2NF: Uniqueness A table says only one thing; 
3NF: Each column has a direct relationship with the primary key, and there is no transfer dependency; 

Now that tables are designed according to the three-paradigm principle, considering the cost of joining tables is something you have to do, so you have to weigh the cost of joining between the three-paradigm and the three-paradigm.

3. Tables need to be balanced against data redundancy and connection costs

At design time, if you know that the amount of data in your system will not be too large (the cost of association will not be too high), then the three-paradigm principle is necessary, since it can avoid the need for updating the insertion of the same fields in multiple tables due to data redundancy.

If the data is large, then redundant data is required, such as the mail address of the user table and the commodity name of the commodity table, which is also included in the order flow table.Redundant data comes at a cost, such as modifying the field in both the user table and the order flowchart once the user updates the e-mail address.

Not only do we need to master technologies such as the paradigm of connectivity, but we should also weigh the cost of building tables from a business perspective and make business-specific analyses.

2. Some commonly used SQL

//Query the top 10 data in the student table, limit is the keyword of paging query
select * from student limit 0,10

//uselikeMake a Fuzzy Query
select * from Table Name where name like '%java%'

//Function: Query records where the first letter of name is J
select * from Table Name where substr(name)='J'

insert into Table Name 

//Display company names alphabetically
select company, order_number from orders order by company

3. Some neglected SQL s

1.group by and have

//Query the total number of each group
 //Community Group Record Table contains fields: grade, number, organization group name
 //Return data format: grade, total number
 Selectect Community Group Name, sum as Total Number from Community Group Record Table group by Community Group Name

//Filter data after grouping: which group has fewer than 10 people
 Selectect Community Group Name, sum as Total Number from Community Group Record Form group by Community Group Name has sum <10

//having View duplicate records, duplicate problems can be avoided by establishing primary key constraints
 //Student table fields: id, name
 select id from student table group by having count (*)>1

2.select Formula

Common select statements include a combination of subqueries, in, group by, and having.

/*Build four tables: 
Student field of student table: number, name, age
 course field: course number, course name, Teacher number
 sc field of report sheet: number, course number, result
 teacher field: teacher number, teacher name*/

//Query for information about students who have passed all their courses
select sid, sname, sage from student where sid not in(select sid from sc where score<60)

//Query the number and name of a student whose language performance is worse than that of math
select sid sname from
    (select student.'sid', student.sname, score,
        (select score from sc sc_2 where student.'sid'=sc_2.'sid' and sc_2.'cname'='Chinese')
        score2 from student, sc where sc.'sid'=student.'sid' and sc.'cname'='Mathematics')
    s_2 where score2<score

//Query has been learned2Number of the student for the course that Class No.
//usecount(*)To ensure the number of courses these students have learned and2Class No. 1 has the same number of courses
select 'sid' from sc where 'cid' in
    (select 'cid' from sc where 'sid'=2)
    group by 'sid' having count(*)=(select count(*) from sc where 'sid'=2)

4. Solve SQL slow query problem

1. order by Solution for Slow Sorting SQL

Order by sorting in projects takes a lot of time, and the difference between adding and not adding order by is about 80 times.Look up the data from three tables (left join) and sort by one of the fields (the sorted fields are indexed).In this case, consider finding the data and then sorting it in Java:

//Object Sort One
Collections.sort(returnList, new Comparator<CostStatisticsD0>() {
    @Override
    public int compare(CostStatisticsD0 o1, CostStatisticsD0 o2) {
        return o1.getConsume() < o2.getConsume() ? 1 : -1;
    }
});

//Object Sort Two
Collections.sort(returnList, new Comparator<CostStatisticsD0>() {
    @Override
    public int compare(CostStatisticsD0 o1, CostStatisticsD0 o2) {
        return o1.getAdvertEntName().compareTo(o2.getAdvertEntName());
    }
});

5. Use and cost of index

Indexes are essential tools for database optimization.Index Related Content Reference Articles http://blog.csdn.net/xluren/article/details/32746183
Purpose of index: High query efficiency.
Cost: Space (index takes up hard disk space); Time (rebuild index once new data is inserted).

Indexing requires a trade-off between pros and cons (indexing can be done to improve query efficiency).Indexing requires business needs. For example, in a commodity table, we often query by name. If there is no index, queries will be slow, and then we need to index.However, in project development, if you do not often query based on the commodity number, then there is no need to index the number.

Note: Indexing comes at a cost. Don't build anything, and don't build too many indexes on a table.

6.JDBC operation optimization

1.try-catch-finally optimization

1. Exceptions cannot be used directly to receive all exceptions. Professional exception handling classes, such as SQL Exception, should be used first to receive exceptions, and then Exception to do the final defense.
2. In the catch clause, some operable statements should be returned to prompt the user what to do when they encounter an exception.
3. try-catch should be as narrow as possible, including only the necessary code.

2. Pre-processing and batch processing

Preprocessing with placeholder (?) improves efficiency and avoids SQL injection, thus ensuring system security.
Bulk operations can be efficient, typically without 500 - 1000 statements, but keep in mind that don't put all insert statements in one go, because too much SQL can explode the cache and cause errors.

MyBatis Bulk Operations MySQL Reference Articles http://blog.csdn.net/hardworking0323/article/details/51105218