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

This article mainly explains the "overview and basic operation of mysql database", the content of the explanation is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "the overview and basic operation of mysql database" bar!

One concept 1 Database birth and Development History

Database: a warehouse that organizes, stores, and manages data according to the data structure

Born

Computers are invented to do scientific computing, which requires a lot of data input and output.

In the early days, the input and output of data were represented by punching machines and light bulbs.

Later, the data can be stored on magnetic tape, read and written sequentially.

IBM invented the disk drive in 1956, which supports random access instead of sequential access.

With the advent of the information age, with the development of hardware storage technology, there are a large number of data to be stored and managed, and the database management system DBMS was born.

No matter what storage medium is used, the data model of the database is its core and foundation.

2 early database classification

Classified according to data model: mesh database, hierarchical database, relational database

1 hierarchical database

The association between entities is represented by a tree structure. The relationship only supports one-to-many, and the tree cannot be crossed, which means that the database has IMS of IBM.

2 mesh database

General Electric first developed the mesh database IDS in 1964, which can only run on GE's own host.

Nodes describe data, and the connection of nodes is the relationship between data.

It can directly describe the objective world and can represent a variety of complex relationships of entity keys, which can not be done in a hierarchical database. For example, a node can have multiple parent nodes, and many-to-many relationships can be supported between nodes.

3 Relational database

A two-dimensional table composed of rows and columns is used to organize data and relationships, and the rows (records) in the table can describe the data entity. The relationship between entity keys can also be described.

The relational model is simpler than the mesh model and the hierarchical model, does not need the physical details of relational data storage, and focuses on the logical construction of the data, while the relational model is supported by the strict mathematical theory of the paper.

In 1970, IBM E.F.Codd published a paper called "A Relational Model of Data for large Shared Data Banks", which put forward the concept of relational model and laid the theoretical foundation of relational model. Relational model has strict data foundation, high level of abstraction, simplicity and clarity, and is easy to understand and use.

The database based on the relational model is called RDBMS (Relational DataBase System).

3 Development history of relational database

1 Oracle development

Larry Ellison developed the general and commercial database Orcale on the basis of IBM's relational database thesis. In 1992, Oracle7 gradually stabilized and achieved great success. The 9i version in 2001 was widely used.

On April 20, 2009, Oracle announced that it would acquire sun (computer Systems) for a total of $7.4 billion per share, which was successfully acquired in January 2010.

In 2013, Oracle overtook IBM to become the second largest software company after Microsoft

2 MySQL development

In 1985, several Swedes designed software for large retailer projects to access databases sequentially using indexes, which was the predecessor of MyISAMd in 1996. MySQL1.0 released, followed by 3.11.1, and began to migrate to other platforms. In 2000, MySQL was open source using the GPL protocol, and MySQL4.0 began to support the MyISAM,InnoDB storage engine. In October 2005, MySQL 5.0 was called a milestone version.

Acquired by sun Company in January 2008

In January 2009, before Oracle acquired MySQL, Monty Wideniusa worried about the acquisition, so it started an existing GPL branch from MySQL Server 5.5, which was named MariaDB.

The engine of MySQL is plug-in and can support many engines.

MyISAM, does not support transactions, insert, query fast

InnoDB, support for things, row-level locks, default engine from MySQL 5.5 onwards

3 NoSQL

NoSQL is a general term for non-SQL, non-traditional relational databases.

The word NoSQL was born in 1998. In 2009, the term was proposed again to refer to non-relational, distributed, database design patterns that do not provide ACID.

With the arrival of the Internet era, the explosive growth of data and the rapid development of database technology, in order to adapt to the new business needs, with the arrival of the mobile Internet and the Internet of things, NoSQL is equally important in big data's technology.

Overview of MySQL database and basic operation of SQL 1 MySQL database overview

MySQL database is a relational database management software that supports network access. The default port is 3306.

MySQL communication uses mysql protocol.

The underlying layer of MySQL is based on TCP protocol. Because UDP will lose packets, and the data in the database must not be lost.

2 SQL

1 Overview

SQL is a structured query language Structured Query Language, which was standardized by ISO in 1987. All mainstream relational databases support SQL,NoSQL and a large part of them support SQL.

The SQL statement is divided into:

DDL: data definition language, responsible for database definition, database object definition. Consists of three grammars: CREATE,ALTER and DROP

DML: data manipulation language, responsible for the operation of database objects, CRUD addition, deletion, modification and query

DCL: database control language, responsible for database access control, consisting of GRANT and REVOKE instructions

TCL: transaction control language that handles ACID transactions and supports commit,rollback instructions

SQL statements are not sensitive to uppercase and should end with semicolons at the end of SQL statements.

2 database basic terminology 1 PRIMARY KEY primary key

One or more columns in the table form a unique key,. That is, through this one or more columns can uniquely identify a record, the primary key column can not be empty, and can not have NULL, the primary key is often set to integer type, long integer type, and self-increasing auto _ INCREMENT, there can be no primary key in the table, however, the general table design will include the primary key.

The primary key is used without looking back, and even if the value of the current primary key is deleted, the next insert statement will be stored from the next number.

2 Index index

Can be regarded as a large dictionary directory, in order to quickly retrieve the use of space for time, significantly improve the efficiency of the query, you can set an index on one or more columns.

Primary key index, the primary key will automatically establish the primary key index, the primary key itself is to quickly locate the unique record.

Unique index, the index composed of index columns in the table must be unique, but it can be null, non-null values must be unique, and null values can have multiple values.

An ordinary index, which does not require uniqueness, is to build a dictionary directory.

Indexes also have side effects, which can significantly improve query efficiency, but can lead to inefficient insertions and deletions.

3 constrained Constraint

UNIQUE constraint (unique key constraint)

When a unique index is defined, a unique key constraint is defined.

PRIMATY KEY constraint

When the primary key is defined, the primary key constraint is defined.

Foreign key constraint Foreign Key

Foreign key: the column in table B, the primary key in table A, and the column in table B is the foreign key.

If you insert a piece of data in table B, the foreign key in table B inserts a value, which must be the value of the primary key that exists in table A, and it is the same to modify the value of the foreign key in table B. the corresponding value of the foreign key must exist in the primary key of table A.

If Table A wants to delete a record. Then it is tantamount to deleting a primary key. If the primary key is referenced in table B, the record referencing the primary key in table B must be deleted before the record in table A can be deleted, otherwise the deletion will fail.

To modify the primary key of table A, because of the uniqueness of the primary key, the modified primary key is equivalent to inserting a new primary key, then Table B refers to this primary key and will organize Table A to modify the primary key. The relevant records of Table B must be deleted before the primary key of Table A can be modified.

Foreign key constraints, in order to ensure data integrity, consistency, put an end to data redundancy, data corruption.

4 View

The view, which is also a virtual table, looks like a table. It is generated by query statements and can be used for CRUD operations.

The role of views: simplify operations, define complex query SQL statements as views, and simplify queries

Data security, the view can show only part of the columns of the real table, or the calculated results, hide the data of the real table.

The data type in 3 MySQL means tinyint1 bytes, signed range is from-128to 127, unsigned range is from 0 to 255 smaillint2 or boolenan, that is, tinyint,0 means false, non-zero represents true smaillint2 bytes, signed return is-32768 to 32767, unsigned is 0 to 65535int integer type, 4 bytes, same as integer, signed range is-2147483648 to 2147483647, unsigned range is 0 to 4294967295bigint long integer type, 8 bytes The signed range is-9223372036854775808 to 9223372036854775807, and unsigned numbers are 0 to 18446744073709551615float single-precision floating-point numbers accurate to about 7 decimal places dobule double-precision floating-point numbers accurate to about 15 decimal DATE dates. The supported range is 1000-01-01 to 9999-12-31DATETIME. The supported range is 1000-01-01 00:00:00 9999-12-31 23:59:59TIMESTAMP timestamp. The range is' 1970-01-01 00 char (M) fixed length to 2037. The right space has reached the length requirement. M is the length and returns 0-255. the M value is the character number varchar (M) variable length string, M represents the maximum column length, M ranges from 0 to 65535, but cannot break through the line maximum number of bytes 65535text large text, the maximum length is 65535 (2 ^ 16-1) characters BLOB large byte BLOB column with a maximum length of 65535 (2 ^ 16-1) bytes

The LENGTH function returns the number of bytes, while the M defined by char and varchar is the character limit

Char can change the string into equal length, space for time, slightly more efficient, longer varchar, saving space.

4 basic language for MySQL operation 1 DCL

GRANT authorizes REVOKE to revoke

GRANT ALL ON *. * TO admin@localhost IDENTIFIED BY 'Admin@Roo123'

Parameter description:

ALL, which means to grant all permissions, of course, SELECT,DELETE,UPDATE and other permissions can be granted separately.

The *. * after ON represents libraries and tables. For example, test.*, represents all tables in test libraries, and test.test represents test tables in test libraries.

The admin after TO indicates the user name @ and the localhost after @ indicates local authorization to log in. Of course, you can use'%'to indicate that all can be logged in.

The IDENTIFIED BY is followed by a password.

Use this command to log in successfully

Mysql-uadmin-pAdmin@Roo123

REVOKE revokes permissions

REVOKE ALL ON *. * FROM admin@localhost

Parameter description:

The ALL here also has all permissions, and you can also revoke some permissions, such as SELECT,DELETE, etc.

The following ON is the same as above

The FROM followed by the user name and the scope of the login allowed

Login query

Mysql > show databases;+-+ | Database | +-+ | information_schema | +-+ 1 row in set (0.00 sec)

It can only manage this open library.

2 DDL1 Delete user DROP USER admin@localhost

Login will not be possible at this time

[root@python ~] # mysql-uadmin-pAdmin@Roo123mysql: [Warning] Using a password on the command line interface can be insecure.ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES) 2 create a database

A library is a collection of data, and all data is organized in a database according to the data model.

CREATE DATABASE IF NOT EXISTS test CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci

CHARACTER SET is the specified character set, and utf8mb4 is an extension of utf8. It supports 4 bytes of utf8mb4 and needs to be greater than MySQL 5.5.3 +.

COLLATE specifies the proofreading rules of the character set, which are used to compare strings.

Delete database

DROP DATABASE IF EXISTS test

Create a datasheet

The table is divided into rows and columns, MySQL is the row storage data, the data is stored row by row, the column must be fixed number of columns.

Line Row, also become record Record, meta-ancestor

Column Column, also known as field Field

CREATE TABLE `employees` (`emp_ no` int (11) NOT NULL COMMENT 'primary key', `birth_ date` date NOT NULL COMMENT 'birthday', `first_ name` varchar (14) NOT NULL COMMENT 'user-last name', `last_ name`varchar (16) NOT NULL COMMENT 'user-first name', `gender`enum ('gender') NOT NULL COMMENT 'gender', `hire_ date`date NOT NULL COMMENT 'entry time', PRIMARY KEY (`emp_ no`) ENGINE=InnoDB DEFAULT CHARSET=utf8

The name of a backquotation mark, which is considered to be a non-keyword

Different data types take up different bytes.

The range of numbers determines the storage mode, and it is more appropriate for small-range integers to use strings and for large-range integers to use numeric types.

NOT NULL cannot be empty

Define a constraint after the definition field is completed. The PRIMARY KEY (emp_no) mentioned above is called a primary key constraint.

3 DESC query column information mysql > DESC employees +-+ | Field | Type | Null | Key | Default | Extra | +- +-+ | emp_no | int (11) | NO | PRI | NULL | birth_date | date | NO | | NULL | | first_name | varchar (14) | NO | | NULL | | last_name | varchar (16) | NO | | NULL | | gender | enum ('M') | 'F') | NO | | NULL | hire_date | date | NO | | NULL | | +-+-+ 6 rows in set (0.00 sec) mysql > DESC employees'% name' +-+ | Field | Type | Null | Key | Default | Extra | + -+-+ | first_name | varchar (14) | NO | | NULL | | last_name | varchar (16) | NO | | NULL | | +-+-+ 2 rows in set ( 0.00 sec) 4 basic design table principles for Navicat premium

Design a landing form

1 basically must have a primary key and must be an unsigned self-increasing sequence

2 loginname must not be empty. If it is empty, there will be a problem, and password cannot be empty.

3 there must be redundant fields, because if the online business is running, adding fields may lead to the interruption of online business or problems related to user experience.

4 the index must be set to speed up the select query

View automatically incrementing numbers

View the generated SQL

CREATE TABLE `login` (`id` int (10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key', `loginname` varchar (48) NOT NULL COMMENT 'login name', `username` varchar (48) DEFAULT NULL COMMENT 'user name', `password` varchar (255) NOT NULL COMMENT 'user password', `passed1` varchar (255) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `ln` (`loginname`) USING BTREE COMMENT 'login name index') ENGINE=InnoDB DEFAULT CHARSET=utf8;5 SQL import

1 Select the table, right-click to run the SQL file

Tools to create views

Select views and create tools

Pull the view you need to create into the box, and select the corresponding column to complete the creation of the view

Save

Preview

3 DML1 relational operation

Relationships: in relational databases, relationships are two-dimensional tables

A relational operation is an operation on a table

Select: also known as restriction, is the selection of meta-ancestors from a relationship that meet a given condition

Select * from test where id=1

Projection (projection): projection on a relationship is to select several attribute columns to form a new relationship.

Select name,password from test where id=1;# is equivalent to projection here, and related operations are carried out by field and column.

Join: links two different relationships into one relationship

2 insert statement

INSERT INTO table_name (key1,key2...) VALUES (vaule1,value2...)

Insert a row of data into the table, self-increasing fields, default fields, but empty fields can not be written

INSERT INTO table_name SELECT...

Insert the results of the SELECT query into the table

INSERT INTO table_name (key1,key2,...) VALUES (value1,value2,...) ON DUPLICATE KEY UPDATE key1=value1...

If there is a primary key conflict, the only key conflict executes the setting after update, which means that the primary key is not adding a record, but the primary key is updating some fields.

INSERT IGNORE INTO table_name (key1,key2,...) VALUES (value1,value2,...)

If the primary key conflicts, the only key conflict ignores the error, returns a warning, and rolls back.

3 update statement

UPDATE [IGNORE] table_name SET col_name1=expr1 [, col_name2=expr2...] [WHERE where_defintion] IGNORE (meaning here is the same as above)

UPDATE reg SET name='1234' WHERE id=5

If UPDATE does not add where, it will lead to all modifications, which may lead to serious problems in some cases.

4 delete statement

DELETE [IGNORE] FROM table_name [WHERE where_defintion]

Delete eligible records

4 select statement

SELECT [DISTINCT] select_expr,... [FROM tanle_references] [WHERE where_defintion] [GROUP BY {col_name | expr | position} [ASC | DESC],... [WITH ROLLUP]] [HAVING where_definition] [ORDER BU {col_name | expr | position} [ASC | DESC],...] [LIMIT {[offset,] row_count | row_count OFFSET offset}] [FOR UPDATE | LOCK IN SHARE MODE]

Optional representation in square brackets

The last ones are exclusive locks and shared locks.

FOR UPDATE will write the lock, which is an exclusive lock

1 simple query select 10 hand 5; # arithmetic select now (); # query current time select * from employees; # query all data in this table 2 string merge query

CONCAT (str1,str2,str3)

Select emp_no,last_name from employees; # shows only two columns of select emp_no,CONCAT (last_name, ", last_name) from employees; # using CONCAT () to merge multiple strings. 3 AS aliases define the number of select emp_no as no,CONCAT (first_name,", last_name) as name from employees as emp;4 LIMIT to limit the number of entries returned, often used for paging select * from employees LIMIT 2 # display the first two rows of data select * from employees LIMIT 2 offset 4; # start from the fourth row, display the following two rows of data, default is from line 0 or yes, select * from employees LIMIT 2 offset 4; # here indicates an offset of 2, showing 4 rows of 5 where sentence operator description = equal to not equal >, =, 10025th 9 grouping query

Use the GROUP BY clause and, if conditional, use the lifting clause to filter the grouped, aggregated results. Only the first one is displayed after the default aggregation.

-- simple grouping query SELECT COUNT (*), sum (salary) FROM test.salaries GROUP BY emp_no;-- aggregate grouping query, showing two identical parts SELECT emp_no,sum (salary), count (*), max (salary), min (salary) from test.salaries GROUP BY emp_no,from_date;SELECT emp_no,sum (salary), count (*), max (salary), min (salary) from test.salaries GROUP BY emp_no HAVING emp_no > 10007

The results are as follows

Execution order: first the content after from, followed by where filtering, followed by group by grouping, followed by having filtering, and finally order by sorting

10 subqueries

Subqueries: query statements can be nested, and internal queries are subqueries

The subquery must be in a set of parentheses

Order by cannot be used in subqueries

SELECT * FROM employees WHERE emp_no in (SELECT emp_no FROM employees WHERE emp_no > 10020) ORDER BY emp_no;SELECT emp.emp_no,emp.first_name FROM (SELECT * FROM employees WHERE emp_no > 10010) AS emp WHERE emp.emp_no

< 10015 ORDER BY emp_no DESC;-- IN后面跟单列是比较合适的，子查询是有意义的，内层选择只需要对使用的字段进行显示即可SELECT * FROM employees WHERE emp_no IN (SELECT emp_no FROM employees WHERE emp_no >

10025) ORDER BY emp_no DESC-the best way to use subqueries: SELECT * FROM (SELECT emp_no,first_name,gender FROM employees WHERE emp_no > 10025) AS emp WHERE emp.emp_no

< 10030 ORDER BY emp_no Desc;11 连接 join 连接 join 交叉连接 cross join 笛卡尔乘积，全部交叉 在MySQL中，CROSS JOIN 从语法上和INNER JOIN 等同 1 交叉连接 CROSS JOIN ，及笛卡尔乘积，全部交叉SELECT * FROM employees CROSS JOIN dept_manager 每一个结果分别和另一个结果集的所有数据相乘得到的乘机。 此种连接在生产环境中基本上是被废弃的，不用的 2 内连接 inner join ，省略为 join 等值连接，只选择某些filed相等的元组(行)，使用ON 限定关联的结果 自然连接，特殊的等值连接，会过滤掉重复的行，用的少 等值连接 -- 此种方式由于没有配置ON，因此其值和交叉连接结果相同SELECT * FROM employees INNER JOIN dept_manager;-- 此处会选择两个表中相同字段相同部分呈现出来，并将两张表中相同的字段重复显示出来 SELECT * FROM employees JOIN salaries ON employees.emp_no=salaries.emp_no 其只显示等值的链接，对于在任何一张表中不存在的都不会显示 自然连接 -- 此处会选择两个表中相同字段相同部分呈现出来，并去除两张表中的相同字段的列，但某种情况下两张表中的含义相同的字段并非名称相同，因此不一定会使用到SELECT * FROM employees NATURAL JOIN salaries; 3 外连接 outer join 左外连接 左链接： 左表的所有行都得有，左表和右表对不上的行也是需要显示的，右表中的数据有没有无所谓。 SELECT * from dept_emp LEFT JOIN departments on departments.dept_no=dept_emp.dept_no; 左表中加入了31号员工，未加入到任何部门，但在部门表中加入了管理部，此处未显示。 默认是以左表为主的，右表中的数据全不全无关 右外连接 右表中的40行在左表中全部都能找到，所以显示的是全部，视角在右表 SELECT * from employees RIGHT JOIN salaries on employees.emp_no=salaries.emp_no; 此处是等值关系，和具体的行没有关系 此处未加入部门的31号员工也未出现在此表中。而管理部出现在词表中 12 自连接 表自己和自己链接 SELECT * FROM employees as manager,employees as worker WHERE manager.emp_no=worker.emp_no and worker.emp_no=10010;SELECT * FROM employees manager INNER JOIN employees worker ON manager.emp_no=worker.emp_no WHERE worker.emp_no=10010; 第一种方式写着简单，可以使用第一种方式进行处理，若需要特别的左链接或右链接时建议使用第二种方式 通过查询创建工具方式创建 三 MySQL事务1 概述和属性1 概述 事务Transaction InnoDB 引擎，支持事务 事务，由若干条语句组成，指的是要做的一系列操作。 2 属性 关系型数据库中支持事务，必须支持其四种属性(ACID) 特性描述原子性(atomicity)一个事务是不可分割的工作单位，事务中包括所有操作要么全部做完，要么什么都不做一致性(consistency) 事务必须是使数据库从一个一致性状态变换到另一个一致性状态，一致性与原子性是紧密相关的 隔离性(isolation)一个事务的执行不能被其他事务干扰，及一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰持久性(durability)持久性也成为永久性(permanence)，指一个事务一旦提交，它对数据库中的数据改变也就应该是永久性的，接下来的其他操作或者故障不应该对其有任何影响3 具体理解 原子性： 要求事务中所有的操作，不可分割，不能做了一部分操作，剩下一部分操作。 一致性：多个事务并行执行的结果，应该和事务排队执行的结果一致，如果事务的并发执行和多线程读写共享资源一样不可预期，就不能保证一致性 隔离性:多个事务访问共享的数据了，应该互相不干扰，隔离性，指的是究竟在一个事务处理期间，其他事务能不能访问的问题 持久性：较好的理解，就是事务提交后，数据不能丢失。 2 事务的隔离级别1 隔离性不好带来的问题1 更新丢失 lost update 事务A 和事务B，更新同一个数据，都读取了初始值100，A要减10，B要加100，A减去10之后是90，B加100后是200，若A先写入，则B读取到的是100，而实际的结果已经是90了，而B使用100计算的结果是200，则此时便覆盖了A的90，将原本的190变成了200. 2 脏读 (读取到了中间状态) 事务A和事务B，事务B读取到事务A未提交的数据(这个数据可能是一个中间值，也可能是事物A回滚的值)，只要读取到了这个被修改的数据就是脏读，只要没有提交，就是没有落地，就是脏数据，若中间出现了状态，回滚，则这个中间状态的数据也是脏数据。 3 不可重复读 (unrepeatable read) 同一条记录的修改 事务A 在同一个事务中执行了相同两次的查询语句，得到了不同的结果，不能保证同一条查询语句重复读相同的结果就是不可重复读。 例如： 事务A查询了一次后，事务B修改了数据，事务A又查询了一次，发现数据不一致了。 注意： 脏读是可以读取到相同的数据，但是读取到的是一个未提交的数据，不是提交的最终结果 4 幻读(phantom read) （增加了数据的不可重复读） 事务A中同一个查询要进行多次，事务B插入的数据，导致A返回不同的结果集，如同幻觉，就是幻读 数据集有记录增加了，可以看做是增加了记录的不可重复度 2 事务的隔离级别 针对上述问题，提出了4中事务的隔离级别，如下 隔离级别描述READ UNCOMMITTED读取到未提交的数据READ COMMITTED读已经提交的数据REPEATABLE READ可重复读，MySQL的默认隔离级别SERIALIZABLE可串行化，事务键完全隔离，事务不能并发，只能串行化执行 隔离级别越高，串行化越高，数据库执行效率越低，隔离级别越低，并行度越高，性能越高 隔离级别越高，当前事物处理的中间结果对其他事务不可见程度越高， SERIALIZABLE：串行化能解决所有问题，但带来的确是效率极其低下。 REPEATABLE READ：事务A中同一条查询语句返回同样的结果，就是可以重复复读取数据了，解决的方式有： 1 对select的数据加锁，不允许其他事务删除，修改操作 2 第一次select的时候，对最后一次确切提交的事务的结果进行快照 但上述方式不能解决幻读 READ COMMITTED：在事务中，每次select可以读取到别的事务刚提交成功的数据，因为读到的是提交后的数据，解决了脏读，但是不能解决不可重复读的问题 READ UNCOMMITTED：能读取到别的事务还没有提交的数据，完全没有隔离性可言，出现了脏读 3 设置会话或者全局的隔离级别-- 设置全局或会话级别的隔离级别SET [SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE } -- 查询隔离级别 SELECT @@global.tx_isolation;SELECT @@tx_isolation;4 事务语法 START TRANSACTION 或 BEGIN 开始一个事务，START TRANSACTION 是标准的SQL的语法 使用COMMIT提交事务后，变更成永久变更。 ROLLBACK 可以在提交事务之前，回滚变更，事务中的操作就如同没有发生过一样 SET AUTOCOMMIT 语句可以禁用或启用默认的autocommit模式，用于当前链接，SET AUTOCOMMIT=0 表示禁用自动提交事务，如果开启自动提交，如果有一个修改表的语句执行后，会立即把更新存储到磁盘上。 开发过程中自动提交一般都是关闭的，一般的是一次提交多次修改的值。 测试重复读 新建一张表，如下 CREATE TABLE `t` ( `id` int(11) DEFAULT NULL, `username` varchar(255) DEFAULT NULL, `password` varchar(255) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;mysql>

Desc t +-+ | Field | Type | Null | Key | Default | Extra | +- -+-+ | id | int (11) | YES | | NULL | | username | varchar | YES | | NULL | | password | varchar | YES | | NULL | | +- -+-+ 3 rows in set (0.00 sec)

View the isolation level of the transaction

Mysql > select @ @ tx_isolation;+-+ | @ @ tx_isolation | +-+ | REPEATABLE-READ | +-+ 1 row in set (0.00 sec)

Turn off autocommit in window 1

Mysql > set autocommit=0;Query OK, 0 rows affected (0.00 sec)

Query the contents of the t table in window 1

Mysql > select * from twitt empty set (0.00 sec)

Insert directly in window 2 and view

Mysql > insert into t value; Query OK, 1 row affected (0.00 sec) mysql > select * from t +-+ | id | username | password | +-+ | 1 | admin | admin | +-+ 1 row in set (0.00 sec)

When querying again in window 1, there is no result, and after the query is submitted again, the result is produced, which indicates that the problem of repeat reading has been solved.

Mysql > select * from tactile empty set (0.00 sec) mysql > commit;Query OK, 0 rows affected (0.00 sec) mysql > select * from t +-+ | id | username | password | +-+ | 1 | admin | admin | +-+ 1 row in set (0.00 sec) read submitted test

Modify window 1 at the level of read commit, and submit the modification

Mysql > set session transaction isolation level read committed;Query OK, 0 rows affected (0.00 sec) mysql > select @ @ tx_isolation;+-+ | @ @ tx_isolation | +-+ | READ-COMMITTED | +-+ 1 row in set (0.00 sec) mysql > commit; Query OK, 0 rows affected (0.00 sec)

Insert in window 2

Mysql > insert into t value; Query OK, 1 row affected (0.00 sec) mysql > select * from t +-+ | id | username | password | +-+ | 1 | admin | admin | | 2 | root | root | +-+ 2 rows in set (0.00 sec)

If you view it in window 1, you can see the modified submitted data, which indicates that the modification was read and submitted successfully.

Mysql > select * from t +-+ | id | username | password | +-+ | 1 | admin | admin | | 2 | root | root | +-+ 2 rows in set (0) .00 sec) 3 other attributes 1 differences between a data warehouse and a database

In essence, there is no difference, they are all rooms for storing data, but the database is concerned with data persistence, data relationships, system support and transaction support for business. data warehouse stores data is a table structure designed for analysis or mining, which can store a large number of data.

Database stores online transaction data OLTP; data warehouse stores historical data for analyzing OLAP.

The database supports online business and requires frequent additions, deletions, modifications and queries. Data warehouses generally stock historical data to support SQL for analysis, and deletion and modification are generally not recommended.

2 Vernier

A method of manipulating the result set of a query

You can think of a cursor as a pointer to a row in the result set.

3 stored procedures, triggers

Stored procedure (Stored Procedure), in a database system, a SQL statement that completes a specific function, written in a function-like way, can be passed parameters and called, and supports process control statements.

Triggers (Tirgger), special stored procedures triggered by events, such as data triggers when insert

Although triggers are powerful, they are rarely used.

Thank you for your reading, the above is the content of "Overview and basic operation of mysql database". After the study of this article, I believe you have a deeper understanding of the overview and basic operation of mysql database, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.