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

The following is followed by the author to briefly understand the basic sentence of MySQL. I believe you will benefit a lot after reading it. The text is not much in essence. I hope the basic sentence of MySQL is what you want.

1. Create a database

The official standard syntax for creating a database is:

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification]... create_specification: [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name

Among them, the content in {} is more than one, and the content in [] can be taken or not, and it is the same meaning if there are such symbols later.

In general work, you often create a database like this:

CREATE DATABASE IF NOT EXISTS `test_ db` DEFAULT CHARACTER SET utf8

To view the creation statement for the database, you can view it like this:

Mysql > show create database test_db +-+-+ | Database | Create Database | +- -+ | test_db | CREATE DATABASE `test_ db` / *! 40100 DEFAULT CHARACTER SET utf8 * / | + -+ 2. Modify the database

In general, it is rare to modify the database. The official standard syntax is:

ALTER {DATABASE | SCHEMA} [db_name] alter_specification... alter_specification: [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name

In special cases, we may modify the character set of the database, when we can write like this:

ALTER DATABASE `test_ db` DEFAULT CHARACTER SET utf8mb4;3. Delete database

Be careful when deleting the database. Don't delete the library and run away. This kind of demand is generally very few, but we also have to know it. Wouldn't it be humiliating not to delete the library ~ ~ or take a look at the official document grammar:

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

Deletions are much simpler and ruder than creating and changing. We should be extra careful when we operate, and it is recommended to make a backup before deleting it. For example, if we want to delete the test_db library, we can write:

DROP DATABASE IF EXISTS `test_ db`

After the deletion, we execute show database and we won't see the test_db library.

4. Create a tabl

Creating a data table is a statement we often encounter. The official reference syntax is relatively long. Here we list it first. In order not to take up too much space, we use code pictures instead.

The common options for our study and work are summarized as follows:

CREATE TABLE ([table definition options]) [table options] [partition options]; the format of [table definition options] is: [, …]

For temporary table creation and partition table creation options, there are not many options used in daily study and work. Here is a basic statement to create a table:

CREATE TABLE `username`varchar (45) CREATE TABLE `username` varchar', `email`varchar (30) NOT NULL COMMENT 'user mailbox', `nickname`varchar (45) NOT NULL COMMENT 'nickname', `birthday day`date NOT NULL COMMENT', `sex`tinyint (4) DEFAULT'0' COMMENT 'gender' Introduce yourself in the sentence `short_ introduce`varchar (150) DEFAULT NULL COMMENT' Up to 50 Chinese characters', 'create_ time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT' creation time', 'update_ time`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT' modification time', PRIMARY KEY (`id`), UNIQUE KEY `timetimeid` (`user_ id`), KEY `idx_ username` (`username`) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=' user information table'5. Modify the table

There are also many options for modifying tables. Refer to the above options for creating tables, and they are not listed here.

Alter table is used to change the structure of a table, for example, you can add or delete columns, create or delete indexes, change the type of existing columns, or rename columns or the table itself. You can also change the storage engine or table comments for the table. Here are a few examples of commonly used table modifications:

Modify table options

# modify the storage engine of the table ALTER TABLE T1 ENGINE = InnoDB;# modify the table's self-added ALTER TABLE T1 AUTO_INCREMENT = 13 COMMENT # modify the table's character set ALTER TABLE T1 CHARACTER SET = utf8;# add (or change) the table note: ALTER TABLE T1 COMMENT = 'New table comment';# modify table name ALTER TABLE T1 RENAME T2

Field (column) operation

# add field # ALTER TABLE ADD COLUMN [constraint] [FIRST | existing field name of AFTER] ALTER TABLE T1 ADD COLUMN col1 INT FIRST;# delete field ALTER TABLE T1 DROP COLUMN col1;# modify field type ALTER TABLE T1 MODIFY col1 VARCHAR (30); # change field name ALTER TABLE T1 CHANGE col1 col2 VARCHAR (30)

Index operation

# add index alter table T1 add index index_name (column_list); alter table T1 add unique (column_list); alter table T1 add primary key (column_list); # delete index alter table T1 drop index index_name; alter table T1 drop primary key; 6. Truncation table

A truncated table is truncate table, which can also be understood as emptying a table. Logically, TRUNCATE TABLE is similar to DELETE all rows of a table, but it bypasses the DML method of deleting data, so it cannot be rolled back. The truncate syntax is simple. Examples of official documents:

TRUNCATE [TABLE] tbl_name7. Delete tabl

The reference syntax officially given by the delete table is:

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name]... [RESTRICT | CASCADE]

If we do not need this table, we can consider using this syntax, but it will delete the table definition and all table data, which is a non-rollback operation, so be careful.

After reading this article on the basic sentences of MySQL, many readers will certainly want to know more about it. If you need more industry information, you can follow our industry information section.

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.