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

This article mainly gives you a brief introduction to the detailed analysis of commonly used MySQL commands. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here. Let's go straight to the topic of detailed analysis of commonly used MySQL commands. I hope we can bring you some practical help.

SQL (Structure Query Language structured query language)

1. Data definition language (Data Definition Language, DDL)

The database management system provides data definition language to define all kinds of objects involved in the database, and to define data integrity constraints, confidentiality restrictions and other constraints.

2. Data manipulation language (Data Manipulation Language, DML)

Retrieve (query) and update (insert, delete, update)

3. Data control language (Data Control Language, DCL)

Data integrity control, data security control and database recovery and so on.

Various versions of MySQL:

Community Edition (Community) and Enterprise Edition (Enterprise)

GA (General Availability): officially recommended as a widely used version

RC (Release Candidate): candidate version, the version closest to the official version

Both Alpha and Bean belong to the test version, where Alpha refers to the internal test version and Bean refers to the public test version

System database:

Information_schema: mainly stores some database object information in the system, such as user table information, column information, permission information, character set information and partition information, etc.

Performance_schema: mainly stores database server performance parameters

Mysql: user rights information for the primary storage system

Test: this database is a test database created automatically by the MySQL database management system. Any user can use it.

CREATE DATABASE zt001

SHOW DATABASES

USE zt001

DROP DATABASE zt001

SHOW ENGINES\ G

Storage engines are: FEDERATED, MRG_MYISAM, MyISAM, BLACKHOLE,CSV,MEMORY,ARCHIVE, InnoDB, PERFORMANCE_SCHEMA. The Enginge parameter indicates the name of the storage engine; the Support parameter indicates whether the MySQL database management system supports the storage engine, DEFAULT indicates that the storage engine is the default storage engine of the database management system, and the Comment parameter indicates comments about the storage engine. The Transactions parameter indicates whether the storage engine supports transactions, the XA parameter indicates whether the distribution supported by the storage engine conforms to the XA specification, and the Savepoints parameter indicates whether the storage engine supports Savepoints in transactions.

SHOW VARIABLES LIKE 'have%'; / / View supported storage engines

SHOW VARIABLES LIKE 'storage_engine%'; / / query default storage engine

MyISAM storage engine: because the storage engine does not support transactions and foreign keys, the access speed is relatively fast. Therefore, access-based applications that have no requirements for transaction integrity are suitable to use the storage engine.

InnoDB storage engine: because this storage engine has the transactional advantage of supporting transaction installations with commit, rollback, and crash resilience, it takes up more disk space than the MyISAM storage engine. Therefore, it is necessary to update and delete frequently, at the same time, the integrity of the transaction is relatively high, and concurrency control is needed. It is suitable to use this storage engine at this time.

MEMORY storage engine: this storage engine uses memory to store data, so the data access speed of the storage engine is fast, but there is no security guarantee. If the data involved in the application is relatively small and needs to be accessed quickly, it is suitable to use this storage engine.

CREATE DATABASE company

USE company

CREATE TABLE t_dept (

Deptno INT

Dnmae VARCHAR (20)

Loc VARCHAR (40)

);

DESCRIBE table_name

SHOW CREATE TABLE table_name

SHOW CREATE TABLE t_dept\ G

DROP TABLE table_name

ALTER TABLE old_table_name RENAME new_table_name; / / modify the table name

Add fields to the last location of the table:

ALTER TABLE table_name ADD attribute name attribute type

ALTER TABLE t_dept ADD descri VARCHAR (20)

Add a field in the first position of the table:

ALTER TABLE table_name ADD attribute name attribute type FIRST

ALTER TABLE t_dept ADD descri VARCHAR (20) FIRST

Add a field after the specified field of the table

ALTER TABLE table_name ADD attribute name attribute type AFTER attribute name

ALTER TABLE t_dept ADD descri VARCHAR (20) AFTER deptno

Delete a field:

ALTER TABLE table_name DROP attribute name

ALTER TABLE t_dept DROP deptno

Modify the data type of the field:

ALTER TABLE table_name MODIFY attribute name data type

ALTER TABLE t_dept MODIFY deptno VARCHAR (20)

Modify the name of the field

ALTER TABLE table_name CHANGE old attribute name new attribute name old data type

ALTER TABLE t_dept CHANGE loc location VARCHAR (40)

Modify the name and properties of the field at the same time

ALTER TABLE table_name CHANGE old attribute name new attribute name new data type

ALTER TABLE t_dept CHANGE loc location VARCHAR (20)

Modify the order of fields

ALTER TABLE table_name MODIFY attribute name 1 data type FIRST | AFTER attribute name 2

ALTER TABLE t_dept MODIFY loc VARCHAR (40) FIRST

ALTER TABLE t_dept MODIFY deptno INT (11) AFTER dname

Commonly used MySQL command detailed analysis will first tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.