Shulou(Shulou.com)06/02 Report--

This article mainly introduces "what are the knowledge points of the Java database". In the daily operation, I believe that many people have doubts about the knowledge points of the Java database. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the questions of "what are the knowledge points of the Java database?" Next, please follow the editor to study!

The complete execution order of the select statements of 1:SQL.

(1) from clause assembles data from different data sources

(2) the where clause filters record rows based on the specified criteria

(3) the group by clause divides the data into multiple packets

(4) use aggregation function to calculate.

(5) use the having clause to filter the grouping

(6) evaluate all expressions

(7) Fields of select

(8) use order by to sort the result set.

Aggregate function of knowledge point 2:SQL

An aggregate function is a function that evaluates a set of values and returns a single value. It is often used with the group by clause in a select statement.

(1) avg (): returns the average value in the specified group, and the null value is ignored.

(2) count (): returns the number of items in the specified group.

(3) max (): returns the maximum value in the specified data.

(4) min (): returns the minimum value in the specified data.

(5) sum (): returns the sum of the specified data, can only be used for numeric columns, and null values are ignored.

(6) group by (): group the data, aggregate the groups after group by, and calculate the values of each group. Finally, the groups that do not meet the criteria are removed with having, and every element in the having clause must appear in the select list.

Join query of knowledge points 3:SQL

External connection:

Left join (left outer join): use the left table as the benchmark to query, and all the data in the left table will be displayed. If the data in the right table matches the data in the left table, the data in the corresponding fields will be displayed. If it does not match, it will be displayed as null.

Right join (right outer join): query using the right table as the benchmark, all the data in the right table will be displayed, if the data in the left table matches the data in the right table, the data in the corresponding fields will be displayed, and if it does not match, it will be displayed as null.

Full connection: first, the left table is used for the left outer connection, and then the right table is used for the right outer connection.

Inner join: displays all rows that have a join match between tables.

Sql injection of knowledge points 4:SQL

Get a database on a Web site with a security vulnerability by entering (malicious) SQL statements into the Web form, rather than executing SQL statements as intended by the designer. For example: when the sql executed is select * from user where username = "admin" or "a" = "a", the sql statement is always true, and the parameter admin is meaningless. Ways to prevent sql injection:

(1) precompiled sentences: for example, select * from user where username =? The semantics of the sql statement will not change, and the variable in the sql statement will use? Indicates that even if the parameter is passed as "admin or'a'='a'", the whole is queried as a character creation.

(2) # in the mapper mode in the Mybatis framework can also prevent sql injection to a large extent ($cannot prevent sql injection).

5:Mysql performance optimization of knowledge points

(1) use limit 1 when only one row of data is needed.

If you know that you will get a piece of data when querying, adding limit 1 in this case will increase performance. Because the mysql database engine will stop searching after finding a result, instead of continuing to query whether the next one meets the criteria until all records have been queried.

(2) choose the correct database engine

There are two engines in Mysql, MyISAM and InnoDB, each of which has its advantages and disadvantages. MyISAM is suitable for some applications with a large number of queries, but it is not very good for applications with a large number of write functions. Even if you only need to update one field, the whole table will be locked. Other processes, even read operations, cannot continue until the current update operation is complete. In addition, MyISAM is super fast for operations like select count (*).

The trend of InnoDB will be a very complex storage engine, which will be slower than MyISAM for some small applications, but supports "row locks", so it will be better when there are a lot of writes. And it supports many advanced applications, such as things.

(3) replace not in with not exists

Not exists uses joins to play the role of indexes that have been established, and not in cannot use indexes. Not in is the slowest way to compare with each record, which is not recommended for operations with a large amount of data.

(4) to optimize the operators, try not to use operators that are not conducive to the index.

For example, a field such as in not in is null is not null is always searched and indexed: in Mysql, you can use the alter table statement to add an index to the field in the table. The syntax is: alter table indicates add index (field name).

Knowledge point 6:Mysql database architecture diagram

7:Mysql performance optimization of knowledge points

The following code creates a MySQL stored procedure called pr_add, which has two input parameters of type int "a" and "b" and returns the sum of these two parameters.

(1) drop procedure if exists pr_add; (Note: if there is a stored procedure for pr_add, delete it first)

(2) calculate the sum of two numbers (Note: realize the function of calculating the sum of two integers)

Create procedure pr_add (an int, b int) begin declare c int; if an is null then set a = 0

End if

If b is null then set b = 0; end if

Set c = a + b

Select c as sum

Perfect solution to the problem of Chinese garbled code in knowledge point 8:MySQL

The core idea of solving garbled codes is unified coding. When we use MySQL to build databases and tables, we should try to use uniform encoding. Utf8 encoding is highly recommended, because it is compatible with almost all the characters in the world.

The default encoding can be set when the database is installed, and it must be set to utf8 encoding when installing. If the database and table created after setting up do not specify the encoding, they will use utf8 encoding by default, which saves a lot of trouble. After the database software is installed, you can view the default code with the following command:

(1) the default encoding format used by the query database software

Show variables like "colla%"

Show varables like "char%"

(2) modify the default database encoding to utf8.

SET character_set_client='utf8'

SET character_set_connection='utf8'

SET character_set_results='utf8'

(3) specify utf8 encoding when creating the database

CREATE DATABASE `test`

CHARACTER SET 'utf8'

COLLATE 'utf8_general_ci'

(4) specify utf8 encoding when creating the table

CREATE TABLE `database_ user` (

`ID`varchar (40) NOT NULL default''

`UserID`UserID` varchar (40) NOT NULL default''

) ENGINE=InnoDB DEFAULT CHARSET=utf8

(5) modify the coding of a specific database or table

ALTER DATABASE `db_ name` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

ALTER TABLE `tb_ name` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

At this point, the study of "what are the knowledge points of Java database" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.