preface

Mysql > insert into mysql

  • Mysql operation 01 – Basic instructions, SQL library operations
  • Mysql > select * from ‘SQL’;
  • Mysql operation 03 – SQL data operation
  • Mysql > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL
  • Mysql Operations 05 – SQL advanced data operations

1. Basic instructions

Mysql -u root -p -- Log in to the database use < database name > -- log in to the database show databases -- Log in to the database exit/quilt/\qCopy the code

Note:

The database name consists of alphanumeric underscores (_) and cannot start with a number.

— Double hyphen + space: comment (single line comment), also can use # sign

2. SQL library operations

2.1 Adding a Database

The basic grammar

Create database Database name [library option]; Example: create database myDatabase charset UTf8;Copy the code

Library options:

Used to constrain the database with two options:

Character set: charset/character Set Character set (encoding format of data storage) : common character set GBK and UTF8

Collate set Setting: Collate Specific collate set (rules for data comparison)

Note:

  1. Database names cannot use keywords (already used characters) or reserved words (which may be used in the future)

    Create database database charset UTf8;Copy the code

    If you must use keywords or reserved words, you must use backquotes.

    Create database 'database' charset utf8;Copy the code

  2. Chinese database is ok, but there is a prerequisite: ensure that the server can recognize. (Not recommended)

    Create database 'Chinese' charset utf8; Create database Chinese charset utf8; / / an errorCopy the code

    Solution:

    Tell the server what the current Chinese character set is

    set names gbk; Create database Chinese charset utf8;Copy the code

  3. What happens when the SQL statement that created the database executes?

    • In the database system, added the corresponding database information
    • Create a folder corresponding to the database name in the folder where the data is stored, the data directory
    • There is an OPT file under each database

2.2 Viewing a Database

  1. Viewing all databases

    show databases;
Copy the code

  2. View the specified part of the database: fuzzy query

    show databases like 'pattern'; -- Pattern indicates the matching pattern. % : indicates that multiple characters are matched. _ : Indicates that single character is matchedCopy the code

    Ex. :

    _ need to be escaped show database like 'inform\_%'; show database like 'inform_%'; -- Equivalent to inform%Copy the code

  3. View the database creation statement

    Show create database Database name;Copy the code

2.3 Updating the Database

The database name cannot be changed.

Database modifications are limited to library options: character set and collation set (collation set depends on character set)

Basic format:

Alter database Alter database name [library option]; Charset /charset set[=] character set collate Collate setCopy the code

Ex. :

Alter database INFORM chartSET GBK;Copy the code

3.4 Deleting a Database

Basic format:

Drop database Specifies the database name.Copy the code

What happens after the delete database statement is executed?

  • The corresponding database cannot be seen inside the database;
  • In the folder where the database is stored: the folder corresponding to the database name is also deleted (cascading deletion: all tables in the database are deleted)

Note:

Do not delete the database at will, should be backed up after operation. (Delete irreversible)