Features of MySQL
- relational database, free to use,
- Plug-in storage engine,
- High Performance,
Basic additions and deletions to the search
DDL statements, data definition statements
1
2
3
4
5
6
7
8
9
10
11
12
|
CreateDatabase test1;
DropDatabase test1;
Use test1;
CreateTable EMP (enamevarchar), HireDateDate,salDecimal10,2), Deptnoint2));
DropTable emp;
AlterTable empModify Enamevarchar20);
AlterTable empAddColumn Ageint3);
AlterTable empDropcolumn age;
alter table emp change age age1 int (4);
alter table emp add birth date after ename;
alter table emp modify age int (3) first;
alter table emp rename emp1;
/span> |
DML statements, data manipulation statements
1
2
3
4
5
6
7
8
9
10
11
12
|
Insertinto EMP (ENAME,HIREDATE,SAL,DEPTNO)Values' Zzx1 ',' 2000-10-11 ',2000,1);
InsertInto EMPValues' Lisa ',' 2004-05-09 ',3000,2);
InsertInto DeptValues5,' Dept5 '), (6,' Dept6 ');
Update empSet sal=4000where ename=' Lisa ';
Update EMP A,dept bSet A.sal=a.sal*b.deptno,b.deptname=a.enamewhere A.deptno=b.deptno;
DeleteFrom EMPwhere ename=' Dony ';
Delete A, bFrom EMP a,dept bwhere A.deptno=b.deptnoand a.deptno=3;
SELECT *From EMPwhere ename=' Lisa ';
select distinct deptno span class= "keyword" >from emp;
select * from emp order by sal (desc);
select * from emp order by sal limit 5;
select * from emp order by sal limit 1, 5;ss
|
DCL statement, Data Control statement
SQL optimization
- Use Preparestatement (Java) as much as possible to take advantage of preprocessing capabilities.
- When making multiple records additions, modifications, and deletions, it is recommended to use the batch processing function, the number of batches to the whole
The SQL statement does not exceed the SQL statement size limit for the corresponding database.
- It is recommended that the number of elements in the in of each SQL statement be below 200, and if the number is more than one, split into multiple
SQL statement. It is forbidden to use XX in ("," ...) or xx in (', ', '). ★
- Prohibit the use of or more than 200, such as XX = ' 123 ' or xx= ' 456 '. ★
- Try not to use outer joins.
- The not exist is not recommended for use in statements. ★
- Prohibit the use of Union and, if there is a business need, split into two queries. ★
- Prohibit the use of more than 3 layers of nested queries in an SQL statement, and if so, consider using temporary tables or
Intermediate result set.
- Try to avoid taking the same number of >= from 4 tables in one SQL statement, for only the filter condition,
But does not involve the number of tables, not participating in the calculation of the number of tables
- Any action on a column in the query condition will result in a table scan, so you should try to make the database function, computation expression
Write to the right of the logical operator.
- When comparing char types, it is recommended that you do not use the RTrim () function, which should be insufficient in the program
Qi.
- Replace the EXISTS clause with a multi-table join.
- If there is a multi-table connection, there should be a master-slave point, and try to take the number from a table, such as select A.col1, A.col2
From a join B on a.col3=b.col4 where b.col5 = ' a '.
- When using like, it is recommended that one side of like is a string, and the table column appears on the side.
- The condition of the WHERE clause is not allowed to be placed in the having.
- Drop the update operation to the last execution of the transaction. Such as
- When a transaction needs to update multiple objects, it is necessary to ensure that the order of the updates is consistent to prevent deadlocks from occurring. If always first
Update the child table and update the main table, according to the Inventory file batch update the existing quantity, the incoming inventory file PK into
Sorting, and then update processing.
- It is forbidden to use temporal tables arbitrarily, and temporary tables are forbidden when the temporary data does not exceed 200 rows.
- It is forbidden to use distinct arbitrarily and avoid unnecessary sorting.
Index optimization
Create an index, delete an index
1
2
|
On the City (city (10));
On the city;
|
The index column of the search is best in the where sentence or the join clause
- Using a unique index
- Use a short index, for longer fields, to index with its prefix
- Do not over-use indexes, which cause additional performance overhead and maintenance
Base of additions and deletions, database optimization, index
