MySQL fragmented knowledge points (learned today)

1. List the number of employees and department numbers in each department with wages higher than the department's average salary, sorted by department number

Select A.deptid, COUNT (*) from employee921 as a INNER JOIN

(Select DeptID, AVG (Salary) as Avgsar from employee921 Group by DeptID) as B

where A.deptid = B.deptid and a.salary > B.avgsar Group by A.deptid ORDER by A.deptid

2. What is a stored procedure? Using what to invoke?

A stored procedure is a precompiled SQL statement, and the advantage is that it allows for a modular design, meaning that it is created once and can be called more than once in the program. If an operation needs to execute multiple times of SQL, using a stored procedure is faster than simply SQL statement execution. You can invoke a stored procedure with a command object.

Mysql> Delimiter |

Mysql> CREATE PROCEDURE Insertarticle_procedure (ptitle varchar (), pbid int,outpid int)

Begin

Insert into Article1value (null,ptitle,pbid);

Select Max (ID) into the pId fromarticle1;

-End;

|

Query OK, 0 rows affected (0.05SEC)

Mysql> callinsertarticle_procedure (' Southern Airlines ', 1, @pid);

|

Query OK, 0 rows affected (0.00SEC)

Mysql> delimiter;

3. Delimit command in MySQL

This command doesn't have anything to do with stored procedures .

In fact, just tell the MySQL interpreter If the command is over and MySQL can execute it.

4. Triggers in the database

　　The definition of a trigger is that when a condition is established, the statements defined in your trigger are automatically executed. Therefore, the trigger does not need to be called artificially, nor can it be called. There can be up to 12 triggers on a table, but only one for the same time, the same event, and the same type of trigger. It is also important to note that there is no contradiction between the various triggers. The more triggers on a table, the greater the performance impact on the DML operations on the table.

　　triggers are useful or a lot of, such as Xiaonei, happy, Facebook, you send a log, automatically notify friends, in fact, when the log is added to do a post-trigger, and then write entries to the notification table. Because of the high efficiency of the trigger.

CREATE TABLE Board1 (ID intprimary key auto_increment,name varchar (), articlecount int);

CREATE TABLE Article1 (ID intprimary key auto_increment,title varchar (), bid int referencesboard1 (ID));

Delimiter |

Create Triggerinsertarticle_trigger after insert on article1 for each row begin

-Update board1 setarticlecount=articlecount+1 where id= new.bid;

-End;

|

delimiter;

INSERT into Board1 value (null, ' test ', 0);

INSERT INTO Article1value (null, ' Test ', 1);

5. Create a federated primary key

CREATE TABLE Product (
Pro_name varchar (20),
Pro_type varchar (20),
Primary KEY (Pro_name,pro_type)
);

6. What is a primary key ? What is a foreign key ?

A primary key is a field (or fields) in a table that defines only the rows in the table, and the values in the primary key are always unique. A foreign key is a constraint that is used to establish a relationship between two tables. This relationship typically involves a series of connected fields in a table where the primary key field is associated with another table (although it may be the same table). Then these connected fields are foreign keys.

7. What does NULL mean ?

Null this value represents unknown (unknown): It does not represent "" (an empty string).

8. What is a transaction? What is a lock?

A transaction is a grouping of SQL statements that are bound together as a logical unit of work, and if any one statement fails, the entire operation is failed, and later the operation is rolled back to the pre-operation state, or there is a node on it. A transaction can be used to ensure that it is either executed or not executed. To consider a set of statements as transactions, you need to pass ACID testing, that is, atomicity, consistency, isolation, and persistence.

lock : In so the DBMS, the lock is the key to implement the transaction, the lock can guarantee the integrity and concurrency of the transaction. Like a real-life lock, it enables certain data owners to be unable to use certain data or structures for a certain period of time. Of course, locks are also divided into levels.

9. What do you call a view? What is a cursor?

A view is a virtual table that has the same functionality as a physical table. You can add, change, check, manipulate, and attempt a view that is usually a subset of rows or columns that have a table or multiple tables. Changes to the view do not affect the base table. It makes it easier for us to get data, compared to multiple table queries.

A cursor is actually a mechanism that extracts one record at a time from a result set that includes multiple data records. Cursors act as pointers. Although the cursor can traverse all the rows in the result, he points to only one row at a time.
In summary, aSQL cursor is a temporary database object, a copy of a row of data that can be used to hold in a database table, or a pointer to a row of data stored in a database. Cursors provide a way to manipulate data in a table on a line-by-row basis.
A common use of cursors is to save query results for later use. The result set of a cursor is generated by a SELECT statement, and if the process requires a record set to be reused, it is much faster to create a cursor and reuse it several times than to query the database repeatedly.

10. What is the role of the index? And what are the pros and cons of it?

Index is a special query table, the database search engine can use it to speed up the retrieval of data. It is similar to the catalogue of real-life books, and you can find the data you want without having to query the entire contents of the book. Indexes can be unique, and creating an index allows you to specify a single column or multiple columns. The disadvantage is that it slows down data entry and also increases the size of the database.

The difference between Union and union all

Select *from Studentwhere ID < 4

Union

Select *from Studentwhere ID > 2 and ID < 6

The result will be

1 Aaron 78

2 Bill 76

3 Cindy 89

4 Damon 90

5 Ella 73

If you switch to Union all to connect two result sets, the result is:

1 Aaron 78

2 Bill 76

3 Cindy 89

3 Cindy 89

4 Damon 90

5 Ella 73

As you can see, one of the differences between Union and union all is the processing of duplicate results.

The UNION will filter out duplicate records after the table link is made, so the resulting set of results will be sorted after the table is connected, the duplicate records are deleted and the results returned. The actual majority of applications do not produce duplicate records, and UNION all simply merges two results and returns. Thus, if there are duplicate data in the two result sets returned, the returned result set will contain duplicate data.

In terms of efficiency, union All is much faster than union, so if you can confirm the combined two result sets

Does not contain duplicate data, then the UNION all is used.

12. Paging statements

MySQL scenario: SELECT * from T ORDER by Idlimit 30,10

Oracle Scenario: SELECT * FROM (select RowNum r,* from T where r<=40) wherer>30

What is the role of Class.forName? Why do you use it?

Searches for and loads the class name in the string as specified in the parameter, and returns the class instance object that represents the bytecode if the class bytecode has already been loaded, otherwise the class loader's delegate mechanism is searched and loaded, and if all of the ClassLoader cannot be loaded into the class, throw ClassNotFoundException. After loading the class bytecode, you can then use the class bytecode Newinstance method to create an instance object of the class. Sometimes, all of the specific class names used in our program cannot be determined at design time (i.e. development time), but only when the program is run, it is necessary to use Class.forName to dynamically load the class, which is typically configured in a configuration file, for example, the spring IOC The specific class for each dependency injection is configured in this way, and the JDBC driver class name is usually configured through a configuration file so that the driver class name can be replaced without modifying the source program after the product is delivered.

14. What is the working mechanism of the data connection pool?

The Java EE server starts with a certain number of pooled connections and maintains not less than this number of pooled connections. Client

When a program requires a connection, the pool driver returns an unused pool connection and memento it as busy. If there is currently no idle connection, the pool driver creates a new number of connections, and the number of new connections is determined by the configuration parameters. When a pooled connection call is completed, the pool driver memento the connection to idle, and other calls can use the connection.

Implementation, the returned Connection is the agent of the original Connection, and the Close method of the proxy Connection

Instead of actually closing the connection, the Connection object that it proxies is returned to the connection pool.

PreparedStatement in JDBC compared to Statement

(a). PreparedStatement is pre-compiled and can greatly improve efficiency for batch processing. Especially if the same SQL statement with different parameters is executed multiple times.

(b) SQL injection can be prevented.

16. Three main paradigms of database design

In order to build a database with small redundancy and reasonable structure, we must follow certain rules when designing database. In a relational database, this rule is called a paradigm. A paradigm is a summary of a design requirement. In order to design a relational database with reasonable structure, a certain paradigm must be met.

The most common design paradigms in real-world development are three:

1 . First Paradigm ( ensuring that each column remains atomic )

The first paradigm is the most basic paradigm. If all the field values in a database table are non-exploded atomic values, the database table satisfies the first paradigm.

The rational follow-up of the first paradigm needs to be determined according to the actual needs of the system. For example, some database systems need to use the "address" attribute, the "address" attribute should be directly designed as a database table field. However, if the system often accesses the "city" part of the "address" attribute, then it is not to be the "address" attribute to be re-split into provinces, cities, detailed address and other parts of storage, so that in the address of a part of the operation will be very convenient. This design only satisfies the first paradigm of the database.

The user information shown in the table above follows the requirements of the first paradigm, which makes it very convenient to classify users using cities, and also improves the performance of the database.

2 . Second Paradigm ( ensure that each column in the table is related to the primary key )

The second paradigm is based on the first paradigm in a more advanced layer. The second paradigm needs to ensure that each column in a database table is related to the primary key, not just one part of the primary key (primarily for the Federated primary key). In other words, in a database table, only one data can be saved in a table, and multiple data cannot be saved in the same database table.

For example, to design an order information table, because there may be more than one item in the order, the order number and the product number are used as the federated primary key of the database table.

This creates a problem: The table is the Union primary key with the order number and the product number. In this table, the product name, unit, commodity price and other information is not related to the table's primary key, but only related to the product number. So this violates the design principle of the second paradigm.

And if the Order Information table is split, the product information is separated into another table, the Order Item table is also separated into another table, it is perfect.

This design, to a large extent, reduces the redundancy of the database. If you want to get the product information for an order, use the item number to inquire in the product information sheet.

3 . Third Paradigm ( ensure that each column is directly related to the primary key column , not indirectly )

The third paradigm needs to ensure that each column of data in a data table is directly related to the primary key, not indirectly.

For example, when designing an order data table, the customer number can be used as a foreign key and order table to establish the corresponding relationship. Instead of adding fields to the order form about other customer information (such as name, company, etc.). The design shown in the following two tables is a database table that satisfies the third paradigm.

In this way, when the order information is queried, the customer number can be used to refer to the records in the Customer information table, and the data redundancy is reduced without having to enter the contents of the customer information multiple times in the Order Information table.

MySQL fragmented knowledge points (learned today)