Introducing a simple database Database tutorial (4)

SQL basic operations

Basic operations: CURD, that is, add, delete, modify, and 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

1 Add a new database

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

Among them, the library option is used To constrain the database, it is optional (with 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+ specific collation set, indicating the rules of data comparison, which depends on the character set.

Example: create database TBL_ERROR_CODE charset utf8;

The name of the database cannot use keywords (already occupied characters, such as update and insert, etc.) or reserved words (which may be used in the future) , such as access and 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 also want to use Chinese as the name of the database, Then you must ensure that the database can recognize Chinese (it is strongly recommended not to name the database in Chinese), for example:

-- method of setting Chinese names, where gbk is the default character set of the current database set names gbk;create database fructose charset utf8;123123

2 Query database

View all–> Basic syntax: show databases;

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

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

%: means 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 underscores to escape.

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, because the database SQL will be optimized before executing the SQL statement, and the system saves the optimized results.

3 Update the 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[=] proofing 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.

The above is the detailed content of Introducing a simple database Database tutorial (4). For more information, please follow other related articles on the PHP Chinese website!