- SQL statement classification
- SQL statement execution order
- Common data types
- Group query?
- Grouping function
- Result set deduplication
- Cartesian product phenomenon
- Classification of join queries
- Internal connection
- Outer join
- Paging query
- Isolation level
- Three paradigms of data design
SQL statement classification
- DQL: Query
- DML: database operations
- DDL: database definition, table structure modification
- TCL: Transaction
- DCL: database management
SQL statement execution order
Common data types
int 整数型(java中的int) bigint 长整型(java中的long) float 浮点型(java中的float double) char 定长字符串(String) varchar 可变长字符串(StringBuffer/StringBuilder) date 日期类型 （对应Java中的java.sql.Date类型） BLOB 二进制大对象（存储图片、视频等流媒体信息） Binary Large OBject （对应java中的Object） CLOB 字符大对象（存储较大文本，比如，可以存储4G的字符串。） Character Large OBject（对应java中的Object） ...... char和varchar怎么选择？ 在实际的开发中，当某个字段中的数据长度不发生改变的时候，是定长的，例如：性别、生日等都是采用char。
When the data length of a field is uncertain, for example: introduction, name, etc. are all used varchar
For example, if a student table needs to query the results of girls and boys separately, then divide the men and women into a group and then calculate.
select SUM(grade),sex from student group by sex
- count count
- min minimum
- avg average
ps: Aggregate function: perform calculations on a set of values and return a single value.
Result set deduplication
select count(distinct age) from student
Cartesian product phenomenon
A table and b table query, a record in table a matches all records in table b
- It is the principle of database connection query. There is no condition when the query is connected. The final result is the product of the two tables, which can be avoided by using inner joins.
- Note: However, even if it is avoided, the matching records will not be reduced, only valid records are displayed.
Classification of join queries
Difference: sql92 does not support outer joins, and sql99 conditional links are separated, which is easy to read.
- Equivalent connection: The condition is an equivalence relationship.
- Non-equivalent connection: Conditional non-equivalent relationship.
- Self-join: A table is regarded as two tables, and you can query yourself.
Features: There is no distinction between primary and secondary tables.
Left and right outer joins: distinguish the primary and secondary tables. The primary table is unconditionally queried. If the secondary table data does not match the primary table, null will be used instead
- limt When the first entry starts, how many will be displayed
A transaction is a complete business logic unit and cannot be divided.
- Atomicity: A transaction is the smallest unit of work and cannot be divided.
- Consistency: The transaction guarantees that multiple DML statements succeed or fail at the same time.
- Isolation: Transactions are isolated in a concurrent environment, and each transaction has its own completed data space.
- Persistence: After the transaction is completed, the modification of the data is permanent, and it will not be lost even if the system fails.
- What you read is
未提交the data of the other party's affairs (
- Read the data submitted by the other party's transaction, and it exists
不可重复度现象(a transaction reads the same row of data twice, but it gets different results)
- The data seen at the start of the transaction is the same as the data seen during the execution and will not be changed. However, it is possible to read data added by other transactions, which may cause it
Serialized read/serial read
- Need to queue up, low efficiency, and solve all problems.
The higher the transaction isolation level, the fewer problems will be generated under concurrency, but the greater the performance consumption will be at the same time.
Three paradigms of data design
According to this design database will not be redundant.
- Each table must have a primary key, and the atomicity of each field cannot be divided (
For example, if the contact information includes phone number and address, it is not inseparable! !
- The primary key of a non-primary key field
完全依赖cannot be transitively dependent.
For example, the fields of a table depend on different primary keys.
- Each column of data is directly related to the primary key, but not indirectly. Can not rely on indirectly.
Product information in the customer table is indirectly related