MySQL basic focus

Article Directory

SQL statement classification

  • DQL: Query
  • DML: database operations
  • DDL: database definition, table structure modification
  • TCL: Transaction
  • DCL: database management

SQL statement execution order

Insert picture description here

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

Group query?

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
Insert picture description here

Grouping function

  • count count
  • max
  • min minimum
  • sum
  • 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

  1. sql92
  2. sql99

Difference: sql92 does not support outer joins, and sql99 conditional links are separated, which is easy to read.

Internal connection

  • 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.

Outer join

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

Paging query

  • limt When the first entry starts, how many will be displayed

Calculation formula:

limit xx

Affairs

A transaction is a complete business logic unit and cannot be divided.

ACID

  • 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.

Isolation level

Read uncommitted

  • What you read is 未提交the data of the other party's affairs ( 脏读)

Read submitted

  • 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)

Repeatable

  • 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.

  1. 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! !
  2. 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.
  3. 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

MVCC