MySQL Tutorial: Basic Operations of SQL Library

This article mainly introduces you to the basic operations and library operations of SQL. I hope it will be helpful to friends in need!

Recommended reference tutorial: "SQL Tutorial"

SQL basic operationsTutorial: Basic Operations of SQL Library>

Basic operations: CURD, that isadd, delete, modify, query .

According to different operation objects, we can divide the basic operations of SQL into three categories: library operations, table (field) operations and data operations.

Library operation

MySQL Tutorial: Basic Operations of SQL Library Add a new database

Basic syntax: create database database name [library option];

Among them, the library option is used to constrain the database and is optional (with a default value). There are two types, namely:

• Character set setting: charset/ character set Specific character set used to represent the encoding format of data storage. Commonly used character sets include GBK and UTF8, etc.

• Collation set setting: collate The specific collation set represents the rules of data comparison, which depends on the character set.

Example: create database TBL_ERROR_CODE charset utf8;

Among them, the name of the database cannot use keywords (characters that have already been occupied, such as update and insert, etc.) or reserved words (which may be used in the future, such as access, cast, etc.).

If you must use database keywords or reserved words as the database name, you must enclose it in backticks, for example:

create databaseaccesscharset utf8;

If you still want to use Chinese as the name of the database, you must ensure that the database can recognize Chinese (it is strongly recommended not to name the database in Chinese), for example:

-- 设置中文名称的方法，其中 gbk 为当前数据库的默认字符集set names gbk;create database 北京 charset gbk;

2 Query the database

View all–> Basic syntax: show databases;

View Part (fuzzy query) –> Basic syntax: show databases like 'pattern';

Among them, pattern is the matching pattern, there are two types, namely:

• %: Indicates matching multiple characters;

• _: Indicates matching a single character .

In addition, when matching database names containing underscores _, you need to add a backslash \_ in front of the underscore for conversion meaning operation.

Example: show databases like 'TBL%'; means matching all databases starting with TBL.

View the creation statement of the database –> Basic syntax: show create database database name;

Here, the results viewed may be different from the SQL statement we wrote , this is because the database will optimize the SQL before executing the SQL statement, and the system saves the optimized results.

3 Update database

Here, please note: the name of the database cannot be modified.

The modification of the database is limited to library options, that is, character set and collation set (the collation set depends on the character set).

Basic syntax: alter database database name [library option];

• ##charset/character set[=] character set;

• collate[=] Collate set;

Example:

alter database TBL_ERROR_CODE charset gbk; means modifying the character set of this database to gbk.

4 Delete database

Basic syntax:

drop database database name ;

Here, please note: Before deleting the database, you should perform a backup operation first. Because deletion is an irreversible operation, do not delete the database at will.

Warm reminder: The contents enclosed by the symbols [] represent optional options; the symbols represent the meaning of connection.

The above is the detailed content of MySQL Tutorial: Basic Operations of SQL Library. For more information, please follow other related articles on the PHP Chinese website!