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

Content:

1. Database introduction and mysql/mariadb background introduction.

2. Some nouns in the database

3. The service structure of mysql

4. The use of mysql client

5. Data type

6. Introduction of SQL statement

7. The transaction mechanism of mysql

First, the database introduction and mysql/mariadb background introduction.

Data can be stored in a variety of locations, such as ordinary files, specialized databases, but what is the difference between the two, and why choose database storage? We know that if the data exists in an ordinary file, when we want to find one of the data, we should load the whole file into memory and then retrieve it, not to mention slow speed. Once the file is larger, the memory will burst directly, and the database query can only load content that meets the conditions.

There are three common models for databases:

Reticular model

Hierarchical model

Relation model

The relational model is a two-dimensional relationship: table

Line: row

Column: column

Index: a data structure that assists in data lookup

Mysql is a widely used relational database:

1. MySQL is developed by the Swedish company MySQL AB and currently belongs to Oracle (acquired)

2. MySQL is the most popular relational database management system at present. In the aspect of WEB application, MySQL is one of the best RDBMS (Relational DatabaseManagement System) application software.

3. MySQL is an associated database management system, which stores data in different tables instead of all data in a large warehouse, which increases speed and flexibility.

4. The SQL language used by MySQL is the most commonly used standardized language for accessing databases.

5. MySQL software adopts the dual licensing policy: it is divided into community version and commercial version. Because of its small size, high speed and low total cost of ownership, especially open source, the development of small and medium-sized websites generally choose MySQL as the website database. Because of the excellent performance of its community version, it can form a good development environment with PHP and Apache (nginx).

The relationship between mysql and MariaDB:

(1) because mysql was acquired by sun, and sun was acquired by oracle, and oracle is a commercial database company, the founder worried about the closed source risk of MySQL, so he created MariaDB with the source code of mysql.

(2) the name MariaDB comes from the founder's third daughter, while MysDB comes from the founder's eldest daughter.

(3) currently, MariaDB has replaced the default database type called mysql in the base source of centos7.

2. Some nouns in the database:

Noun:

Database: database

Table: table

Index: index

View: view

Line: row

Column: column

Primary key: primary key

Foreign key: foreign key

Unique key: unique key

Character set: character

Sorting: collate

3. The service structure of mysql

MySQL is a single-process, multithreaded way of working.

Mysql is the architecture of Cramp S:

C:client

Mysql:CLI interactive client program

Mysqldump: backup tool

Mysqladmin: management tool

S:server

You can listen for three types of socket addresses:

Ipv4

Ipv6

Unix sock: only listens for native client responses

The structure of the server can be simply divided into three levels:

The first layer, the top layer, they all serve or what these programs need: accepting user requests, authentication, security, and so on.

The second layer, this is the core part of MySQL. It is usually called SQL Layer. Before the MySQL database system processes the underlying data, all work is done at this layer, including permission determination, sql parsing, line plan optimization, query cache processing and all built-in functions (such as date, time, math, encryption) and so on. The functions provided by each storage engine are concentrated in this layer, such as stored procedures, triggers, views and so on.

The third layer includes the storage engine. It is often called StorEngine Layer, which is the implementation part of the underlying data access operation and is composed of multiple storage engines. They are responsible for storing and fetching all the data stored in the MySQL. Just like Linux's many file systems. Each storage engine has its own advantages and disadvantages. The server interacts with them through the storage engine API. This interface hides the differences between each storage engine. Be as transparent as possible to the query layer. This API contains a lot of low-level operations. Such as starting a thing, or taking out a line with a specific primary key. The storage engine cannot parse the SQL and cannot communicate with each other. Simply respond to the server's request.

Fourth, the use of mysql client:

Command line interactive client program: mysql

Connect to the database: mysql [OPTIONS] [database]

Common options:

-uUSERNAME

-hHOST

-p [PASSWORD]

-Ddb_name: connect and use the specified database as the default database

-- socket=path,-S path: specify socket method

-- port=port_num,-P port_num: designated port

-- execute=statement,-e statement: do not need to log in to mysql, but return some results of execution

Command:

Client command

Mysql > help

\ u db_name: set the default database

\ Q: exit the client

\ d CHAR: custom statement Terminator, default is semicolon

\ g: tag the end of the statement and send the command to the server to run

\ G: statement closing tag, sending commands to the server to run, row data displayed vertically

\! SHELL_COMMAND: you can execute shell commands

\ s: connection status and server running status

\. / path/to/some_sql_script: run the SQL script

...

Server-side commands: SQL statements sent to the server for execution

(1) statement Terminator, which defaults to semicolon (;)

(2) establish an effective communication connection with a server

5. Data type:

Table: consists of rows and columns. When defining a column, you need to select the appropriate data type and meet the requirements of the normal form design.

Character type:

Define character types: CHAR (#), BINARY (#)

Variable length characters: VARCHAR (#), VARBINARY (#)

Object storage: TEXT,BLOB

Built-in: ENUM,SET

Note: all character data should be in quotation marks

Numerical type:

Exact value: INT

Approximate value: FLOAT,DOUBLE

Note: quotation marks cannot be used

Date and time type:

DATE,TIME,DATETIME,TIMESTAMP,YEAR

6. SQL statement:

DDL: data definition language, mainly used to manage library components, such as databases, tables, indexes, views, users, stored procedures, stored functions, triggers,...

CREATE,ALTER,DROP

DML: data manipulation language, mainly used to manage data in tables and realize data CRUD operations.

DCL: manage authorization

Get help:

Mysql > help KEYWORD

Database Management:

Create:

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name

Modify:

ALTER DATABASE

Delete:

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

Table Management:

Table creation:

CREATE TABLE [IF NOT EXISTS] tbl_name (create_defination) [table options]

Create_defination: a comma-separated list

Field definition:

Colume_name COLUMN DEFINATION

Constraints:

PRIMARY KEY

UNIQUE KEY

FOREIGN KEY

CHECK (expr)

Index:

{INDEX | KEY}

{FULLTEXT | SPATIAL}

Column_definition:

Data_type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]

[table options]

ENGINE [=] engine_name

……

View all storage engine types supported by the database:

Mysql > SHOW ENGINES

View the property information of the table:

SHOW TABLE STATUS [WHERE Name='tbl_name'] [LIKE PATTERN]

Modify:

ALTER TABLE tbl_name [alter_specification [, alter_specification]...]

Alter_specification:

(1) Table options

Table_options

(2) Table composition

Field:

ADD col_name DATA_TYPE [FIRST | AFTER col_name]

DORP [COLUMN] col_name

CHANGE

MODIFY

Index:

ADD INDEX (col1, col2,...)

DROP INDEX index_name

Key:

ADD {PRIMARY | UNIQUE | FOREIGN} key (col1, col2,...)

DROP {PRIMARY | UNIQUE | FOREIGN} KEY key_name

Delete:

DROP TABLE [IF EXISTS] tbl_name [, tbl_name]

Index Management:

Create:

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name ON tbl_name (index_col_name,...)

Index_col_name:

Col_name [(length)] [ASC | DESC]

Delete:

DROP INDEX index_name ON tbl_name

View:

SHOW {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name [{FROM | IN} db_name] [WHERE expr]

DML:INSERT, DELETE, UPDATE,SELECT

INSERT INTO:

INSERT [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...), (...),...

SELECT:

(1) SELECT * FROM tbl_name

(2) SELECT col1, col2, FROM tbl_name

Field: column_name [AS Alias]

(3) SELECT col1, col2,... FROM tbl_name WHERE CLUASE

WHERE CLAUSE: filter condition

Col_name operator value | col_name

Operator:

>, =, SHOW DATABASES # View all databases (specified permissions are required) +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | test | +-+ 4 rows in set (0. 01 sec) MariaDB [(none)] > CREATE DATABASE nihao Query OK, 1 row affected (0.00 sec) MariaDB [(none)] > SHOW DATABASES +-+ | Database | +-+ | information_schema | | mysql | | nihao | | performance_schema | | test | +-+ 5 rows in set (0.00 sec)

1.2. Create a table

MariaDB [(none)] > SELECT DATABASE (); # built-in function to view the current database +-+ | DATABASE () | +-+ | NULL | +-+ 1 row in set (0.00 sec) MariaDB [(none)] > USE nihao # switch to the default database Database changedMariaDB [nihao] > CREATE TABLE users (id INT UNSIGNED NOT NULL PRIMARY KEY,name CHAR (50) NOT NULL,gender ENUM ('Flying journal M')); Query OK, 0 rows affected (0.31 sec) MariaDB [nihao] > SHOW TABLES +-+ | Tables_in_nihao | +-+ | users | +-+ 1 row in set (0.00 sec) MariaDB [nihao] > DESC users # View table details +-+-+ | Field | Type | Null | Key | Default | Extra | + -- + | id | int (10) unsigned | NO | PRI | NULL | | name | char (50) | NO | | NULL | | gender | enum ('F') 'M') | YES | | NULL | | +-+-+ 3 rows in set (0.00 sec) MariaDB [nihao] > SHOW TABLE STATUS FROM nihao\ G # View database details * * 1. Row * * Name: users Engine: InnoDB Version: 10 Row_format: Compact Rows: 0 Avg_row_length: 0 Data_length: 16384Max_data_length: 0 Index_length: 0 Data_free: 10485760 Auto_increment: NULL Create_time: 0-10-14 11:22:12 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set (2016 sec)

2. ALTER modifies the table

MariaDB [nihao] > ALTER TABLE users RENAME user;Query OK, 0 rows affected (0.29 sec) MariaDB [nihao] > SHOW TABLES;+-+ | Tables_in_nihao | +-+ | user | +-+ 1 row in set (0.01 sec)

3 、 DROP

3.1 Delete the table:

MariaDB [nihao] > SHOW TABLES;+-+ | Tables_in_nihao | +-+ | user | +-+ 1 row in set (0.01 sec) MariaDB [nihao] > SELECT DATABASE () +-+ | DATABASE () | +-+ | nihao | +-+ 1 row in set (0.00 sec) MariaDB [nihao] > SHOW TABLES +-+ | Tables_in_nihao | +-+ | user | +-+ 1 row in set (0.00 sec) MariaDB [nihao] > DROP TABLE user; # Delete table Query OK, 0 rows affected (0.01 sec)

3.2. Delete the database:

MariaDB [nihao] > SHOW DATABASES;+-+ | Database | +-+ | information_schema | | mysql | | nihao | | performance_schema | | test | +-+ 5 rows in set (0.00 sec) MariaDB [nihao] > DROP DATABASE nihao Query OK, 0 rows affected (0.00 sec) MariaDB [(none)] > SHOW DATABASES;+-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | test | +-+ 4 rows in set (0.00 sec)

B, DML manipulation language commands are as follows

1 、 INSERT

2 、 DELETE

3 、 SELECT

4 、 UPDATE

1. INSERT inserts data

MariaDB [nihao] > INSERT INTO users (id,name,gender) VALUES, (2) Query OK, 2 rows affected (0. 01 sec) Records: 2 Duplicates: 0 Warnings: 0MariaDB [nihao] > SELECT * FROM users +-+ | id | name | gender | +-+ | 1 | tom | M | 2 | hill | M | +-+ 2 rows in set (0.00 sec)

2. DELETE deletes data

MariaDB [nihao] > DELETE FROM users WHERE name='tom';Query OK, 1 row affected (0.00 sec) MariaDB [nihao] > SELECT * FROM users +-+ | id | name | gender | +-+ | 2 | hill | M | +-+ 1 row in set (0.00 sec) MariaDB [nihao] > INSERT INTO users (id,name,gender) VALUES Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0MariaDB [nihao] > SELECT * FROM users +-2 | hill | M | 3 | nihao | M | 4 | herry | M | +-+ 3 rows in set (0.00 sec) MariaDB [nihao] > DELETE FROM users Query OK, 3 rows affected (0.00 sec) MariaDB [nihao] > SELECT * FROM users;Empty set (0.00 sec)

3. SELECT to view data

MariaDB [nihao] > SELECT * FROM users # No condition specified +-+ | id | name | gender | +-- + | 1 | nihao | M | 2 | herry | M | 3 | hill | M | 4 | tom | M | +-+ 4 rows in set (0. 00 sec) MariaDB [nihao] > SELECT * FROM users WHERE id = 2 # specify query conditions-> +-+ | id | name | gender | +-+ | 2 | herry | M | +-+ 1 row in set (0.00 sec) MariaDB [nihao] > SELECT * FROM users WHERE id > 2-> +-+ | id | name | gender | +-+ | 3 | hill | M | 4 | tom | M | +-+ 2 rows in set (0.00 sec) MariaDB [nihao] > SELECT * FROM users WHERE name LIKE'% l' +-+ | id | name | gender | +-+ | 3 | hill | M | +-+ 1 row in set (0.00 sec)

4. UPDATE changes the data

MariaDB [nihao] > UPDATE users SET id = 10 WHERE name = 'hill';Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0MariaDB [nihao] > SELECT * FROM users +-+ | id | name | gender | +-+ | 1 | nihao | M | 2 | herry | M | 4 | tom | M | 10 | hill | M | +-+ 4 rows in set (0.00 sec)

C, DCL control language commands are as follows

1. GRANT; automatically creates a user when the user does not exist. After creating the user, you need to refresh the authorization table.

2 、 REVOKE:

1 、 GRANT

MariaDB [nihao] > GRANT ALL ON *. * TO 'hill'@'localhost' IDENTIFIED BY' 123456 question question OK, 0 rows affected (0.29 sec) MariaDB [nihao] > FLUSH PRIVILEGES;Query OK, 0 rows affected (0.00 sec) MariaDB [nihao] > GRANT ALL ON *. * TO 'hill'@'127.0.0.1' IDENTIFIED BY' 123456 coach query OK, 0 rows affected (0.00 sec) MariaDB [nihao] > FLUSH PRIVILEGES Query OK, 0 rows affected (0.00 sec) MariaDB [nihao] > EXITBye [root@localhost ~] # mysql-uhill-pEnter password: Welcome to the MariaDB monitor. Commands end with; or\ g.Your MariaDB connection id is 19Server version: 5.5.46-MariaDB-log MariaDB ServerCopyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.MariaDB [(none)] > MariaDB [nihao] > INSERT INTO users (id,name,gender) VALUES; Query OK, 1 row affected [nihao] > SELECT * FROM users # authorized users can log in and insert data +-+ | id | name | gender | +-+ | 1 | nihao | M | 2 | herry | M | 4 | tom | M | 5 | alex | M | 10 | hill | M | + +-+ 5 rows in set (0.00 sec)

2 、 REVOKE

MariaDB [(none)] > REVOKE INSERT ON *. * FROM 'hill'@'127.0.0.1'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)] > REVOKE INSERT ON *. * FROM' hill'@'localhost'; Query OK, 0 rows affected (0 sec) MariaDB [(none)] > FLUSH PRIVILEGES-> Query OK, 0 rows affected (0.00 sec) MariaDB [(none)] > EXITBye [root@localhost ~] # mysql-uhill-pEnter password: Welcome to the MariaDB monitor. Commands end with; or\ g.Your MariaDB connection id is 21Server version: 5.5.46-MariaDB-log MariaDB ServerCopyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.MariaDB [(none)] > USE NIHAOERROR 1049 (42000): Unknown database 'NIHAO'MariaDB [(none)] > USE nihaoDatabase changedMariaDB [nihao] > INSERT INTO users (id,name,gender) VALUES

Summary (from the network)

VII. The transaction mechanism of mysql

1. What is a transaction:

Transaction: transaction

A transaction is a continuous set of database operations as if it were a single unit of work. In other words, it will never be a complete transaction unless each individual operation within the group is successful. If any operation in the transaction fails, the entire transaction will fail.

2. Why do you need the transaction function:

Take a classic example of the necessity of a transaction:

Suppose a bank's database has two tables: checks (checking) and deposits (savings). Now if you want to transfer $200 from user Jane's checking account to her savings account.

Then at least the following three steps are required:

(1) check whether the balance in the checking account is greater than $200.

(2) if true, subtract $200 from the checking account.

(III) add $200 to the savings account balance. Obviously, these three steps must be packaged in a transaction, and if any of the steps fail, they must all be rolled back. Suppose, what happens when the server crashes when you get to step 3? Users are likely to lose $200. Or what happens when another process deletes all balances in the checking account between steps 2 and 3? The bank is likely to give users 200 dollars for nothing.

3. A transaction must support four features of ACID at the same time

ACID says: atomicity, consistency, Isolation, durability

A: atomicity (atomicity) all operations in the entire transaction are either performed successfully or all failed and rolled back

C: consistency database always transitions from one consistency state to another

I: Isolation) actions made by one transaction cannot be seen by others until they are submitted; isolation has multiple isolation levels

D: durability once a transaction commits, its changes are permanently saved in the database

4. Mysql transaction:

MySQL's transaction support is not tied to the MySQL server itself, but is related to the storage engine

MyISAM: does not support transactions and is used by read-only programs to improve performance

InnoDB: supports ACID transactions, row-level locks, and concurrency

Berkeley DB: supporting transactions

In MySQL, transactions start and end with COMMIT or ROLLBACK statements. A large number of transactions are formed between the SQL commands of the start and end statements.

COMMIT & ROLLBACK:

These two keywords commit and rollback are mainly used for MySQL transactions.

When a successful transaction is completed, issuing the COMMIT command should make the changes to all participating tables take effect.

If a failure occurs, you should issue a ROLLBACK command to return each table referenced in the transaction to its previous state.

The transaction behavior that can be controlled is called the AUTOCOMMIT setting session variable. If AUTOCOMMIT is set to 1 (the default), then each SQL statement (in transaction or not) is considered a complete transaction and promises by default when it is completed. When AUTOCOMMIT is set to 0, the command SET AUTOCOMMIT = 0 is issued, and the subsequent series of statements act like a transaction until there is no active commit when there is an explicit COMMIT statement.

You can execute these SQL commands in PHP by using the mysql_query () function.

OK, please follow my blog for more 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.