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

Introduction to the theoretical basis of Mysql database

Developed by MySQL AB, it is the most popular open source SQL database management system with the following main features:

1. It is a database management system.

2. It is an associated database management system.

3. It is a kind of open source software, and there are a lot of shared MySQL software available

4. MySQL database server is fast, reliable and easy to use.

5. MySQL server works in client / server mode, or in embedded system.

The InnoDB storage engine saves InnoDB tables in a tablespace that can be created by several files. In this way, the size of the table can exceed the maximum capacity of individual files. Tablespaces can include raw disk partitions, making large tables possible. The maximum capacity of the tablespace is 64TB.

2. Related concepts of MySQL: MySQL is a single process and multiple threads to receive requests from applications.

2.1. SQL/MySQL

1. Transaction, isolation, concurrency control, lock

two。 Users and permissions

3. Monitor and control

STATUS

4. Index type: query

VARIABLES

5. Backup and recovery

6. Copy function

7. Cluster

2.2. DML: data manipulation language

INSERT: insertin

DELETE: deletin

SELECT: pick, select, query

UPDATE: update, modify

2.3. DDL: data definition language

CREATE: creatin

DROP: deletin

ALTER: modifyin

2.4. DCL: data control language

GRANT: authorization

REVOKE: revoking permissions

2.5. MySQL plug-in storage engine:

1.MyISAM: does not support transaction processing, and is used in data warehouses with more queries but fewer modifications

In this engine, there are three files for each table:

.frm: table structure file

.MYD: table data file

.MYI: table index file

2.InnoDB: supports transaction processing, query is not so fast, and can be applied to frequently modified ones, such as forum data storage

The engine so tables share a tablespace file

Recommendation: a separate tablespace file for each table

.frm: table structure file

.ibd: tablespace (contains table data and table index files)

Set up a separate tablespace file for each table that opens innodb for mysql:

Edit the configuration file / etc/my.cnf and restart the mysql service after adding a line:

# vim / etc/my.cnf

....

Innoda_file_per_table = 1 # 1 is enabled, 0 is disabled

2.6. MYSQL database functions:

1. Database creation and deletion

2. Create tables, delete tables, modify tables

3. Creation and deletion of index

4. Users and permissions

5. Add, delete and change data

6. Query

2.7. MySQL binary program: the database storage path of the mysql database is in the / var/lib/mysql directory

2.8. Client command: mysql

-u USERNAME # which user connects to the mysql (the default user is not specified as root)

-p # specifies to enter the user password (does not specify an empty password by default)

-h MYSQL_SERVER # specifies the mysql server host (default is localhost native if not specified)

-D DATA # specify the default database when connecting to mysql

Eg1: mysql-D mydb-uroot-p-h 10.109.134.249-D mydb

-uroot specified user-p: enter user password-h: specify which mysql server to connect to (ip address)

-D: specify the default database to log in to mysql (equivalent to entering the USE mydb; command in the client)

MySQL client:

Interactive mode: step by step manual input execution

Batch mode: execute mysql script, batch execution

MySQL client tools:

Mysql

Mysqldump: a common mysql backup tool

Mysqladmin:mysql management tools

Mysqladmin extended-status display status variables

Mysqladmin status

-- sleep N display frequency

-- count N displays multiple statu

Mysqladmin variables displays server variables

Mysqladmin flush-privileges mysqld reread the authorization table, which is equivalent to reload

Mysqladmin flush-status resets most server state variables

Mysqladmin flush-logs binary and relay log scrolling

Mysqladmin flush-hosts refreshes the list of hosts

Mysqladmin refresh refreshes the log and host list, which is equivalent to executing flush-hosts and flush-logs at the same time

Mysqladmin shutdown shuts down the mysql server process

Mysqladmin version server version and current status information

Mysqladmin start-slave initiates replication and starts replication thread from the server

Mysqladmin stop-slave turns off replication and shuts down the replication thread from the server.

Format: mysqladmin [option] command [arg] [command [arg]].

Eg: mysqladmin-uroot-p password 'NEW_PASS'

[root@lamp ~] # mysqladmin create hellodb

[root@lamp ~] # mysql

.

Mysql > SHOW DATABASES

+-+

| | Database |

+-+

| | information_schema |

| | hellodb | # you can directly manage mysql through mysqladmin |

...

+-+

7 rows in set (0.00 sec)

Mysqlcheck: inspection tool

Mysqlimport: interface tool

2.9. MySQL non-client tools:

Myisamchk

Myisampack

2.10. Command categories in interactive mode:

Client commands:

? (?) Synonym for `help'. Get help

Quit (\ Q) Quit mysql. Exit the mysql client

Clear (\ c) Clear the current input statement.

\ C: the statement execution is terminated early, but only before the Terminator, otherwise the instruction is still executed.

Go (\ g) Send command to mysql server.

\ g: no matter what the statement Terminator is, send this statement directly to the server for execution

Ego (\ G) Send command to mysql server, display result vertically.

\ G: no matter what the statement Terminator is, send this statement directly to the server for execution, and the results are displayed vertically

System (\!) Execute a system shell command.

\! COMMAND: execute shell command

Warnings (\ W) Show warnings after every statement.

\ W: a warning message is displayed after the execution of the statement

Server-side commands:

Must use statement Terminator, default Terminator is semicolon; SHOW DATABASES; # View database

Delimiter (\ d) Set statement delimiter.

\ d: define the statement Terminator (the default statement Terminator is a semicolon and can be set to something else)

Help KEYWORD: get help for keyword commands

Eg: mysql > help SELECT; # get help on the SELECT command

In the mysql client, the name completion command: rehash

# vim / etc/my.cnf # modify the configuration document to make command completion permanent

[mysql]

# no-auto-rehash

Auto-rehash = 1

# Remove the next comment character if you are not familiar with SQL

# safe-updates

Mysql > rehash # if you do not change the configuration, use the command rehash to complete the command with the tab key, but reopening mysql will fail

Mysql > u # Press the tab key and there are many commands that start with U

Unlock tables user.Execute_priv user.Shutdown_priv

Updates user.File_priv user.Super_priv

...

3. MySQL relational database objects:

Libraries, tables, indexes, views, constraints, stored procedures, stored functions, triggers, cursors, users, permissions, transactions.

The most basic object of the database-- > table: row (row), column (field,column)

Server naming: mysqld

Common Mysql commands are not case-sensitive, but need to be consistent (all uppercase or lowercase):

3.1. DDL: define data objects (Data Definition Language)

3.1.1 keywords: CREATE (create), ALTER (modify), DROP (delete)

Scope of main functions: databases, tables, fields of tables

Create a user:

CREATE USER 'USERNAME'@'HOST' IDENTIFIED BY' PASSWORD'; # can not specify a password

USERNAME: user name HOST: host IDENTIFIED: specify password BY' password'

Delete a user:

DROP USER 'USERNAME'@'HOST'; delete user (you need to specify which user to delete)

Set the password for the user: (1 and 3 are recommended)

* 1. Mysql > SET PASSWORD FOR 'USERNAME'@'HOST'=PASSWORD (' PASSWORD')

Eg. SET PASSWORD FOR 'root'@'localhost'=PASSWORD (' 123456') # set the root@localhost username password to 123456

2. Enter it directly under linux without entering the mysql client (it is easy to change the password of a different host from user):

# mysqladmin-uUSERNAME-hHOST-p password' password'

Eg: [root@johntest] # mysqladmin-uroot-h20.109.13.9-p password '123456'

* 3. Modify the contents of the table directly: mysql > UPDATE user SET password=PASSWORD ('password') WHERE USER='root' AND Host='10.109.13.9' # AND and relation (need to be satisfied at the same time) OR or relationship (both can satisfy one) NOT non-relation (reverse)

3.1.2. Formats that can be used by HOST:

IP, HOSTNAME (hostname), NETWORK (network address), wildcard characters (wildcard quotation marks are required)

Wildcards contain:

_: match any single character, 172.16.0._

%: match any character

3.1.3. Create a new database:

Create a database: create a CREATE DATABASE IF NOT EXISTS db_name; database with the same name when it does not exist

Create table: CREATE TABLE tb_name (col1,col2,...); create tb_name table col to represent fields

View tables in the library: SHOW TABLES FROM db_name view tables in db_name database

View the structure of the table: DESC tb_name view the structure of the tb_ name table

Delete table: DROP TABLE IF EXISTS tb_name delete the tb_ name table if the table exists

3.1.4. Modify table: ALTER TABLE tb_name MODIFY: modify a field (change attributes, permissions)

CHANGE: change a field (change the field name)

ADD: add a field

DROP: delete a field

Eg: ALTER TABLE students ADD course VARCHAR (100); # added space length is 100course field

3.2. DML: data manipulation language (Data Manipulation Language)

Keywords: INSERT (insert), DELETE (delete), UPDATE (update, modify)

Main scope of action: operate on the values of the fields in the table

Insert data: col represents field INSERT INTO tb_name (col1,col2,...) VALUES | VALUE ('STRING',NUM,...), (' STRING',NUM,...)

Update and modify data: WHERE means to modify that specific data, not to change the value of the entire field of column to value. UPDATE tb_name SET column=value WHERE.

Eg: UPDATE students SET Course='Jiuyinzhenjing' WHERE Name='LHC'; # means that the value of the Course field in table students whose name value is LHC is changed to 'Jiuyinzhenjing'

3.3. DCL: database Control language (Data Control Language)

Keywords: GRANT (allow, authorize), REVOKE (revoke permission)

ALL PRIVILEGES: indicates all permissions

3.3.1 GRANT: authorize users

Eg: GRANT pri1,pri2,... ON DB_NAME.TB_NAME TO 'USERNAME'@'HOST' IDENTIFIED BY' PASSWORD'

# in the table TB _ NAME in the database DB_NAME, the user USERNAME@'HOST' grants pri1,pri2 and other permissions, or you can set the password IDENTIFIED BY 'PASSWORD',. If the user does not exist, create the user and authorize it.

3.3.2 REVOKE: revoke user permissions

Eg: REVOKE pri1,pri2,... ON DB_NAME.TB_NAME FROM 'USERNAME'@'HOST'

# cancel the pri1,pri2 and other permissions of user 'USERNAME'@'HOST' in table TB _ NAME in database DB_NAME

3.3.3 View the user's authorization: SHOW GRANTS FOR 'USERNAME'@'HOST'

3.3.4 Refresh user rights list: FLUSH PRIVILEGES

3.4. DQL: database query language (Data Query Language)

Keyword: SELECT (pick, select)

SELECT field FROM tb_name WHERE CONDITION

*: indicates all fields

WHERE: there is no condition to show all lines

Eg: SELECT Name,Course FROM students WHERE Gender='M'

# indicates that only the Name and Course fields in the table students whose Gender field value is M are selected

-end

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.