MySQL database

Catalog

MySQL database

One 、 summary ：

DDL（Data Definition Language): Data definition statement , For libraries and The creation of a table 、 modify 、 Delete .

DDL Used to define the structure of the database , Such as creating 、 Modify or delete Database objects , Includes the following SQL sentence ：

Two 、 Library management

3、 ... and 、 The management of the table

Four 、 General database building table writing method

5、 ... and 、 Replication of tables

summary ： The creation and modification of the basic library is relatively simple , Focus on the syntax and keywords of the modified part and the keywords when creating the table , Later, when other parts are published, they will write one by one , The content is not very comprehensive , After simple study, go deep .

One 、 summary ：

Database installation ： Database installation tutorial

Use as much as possible MySQL5.7 And other commonly used versions , There are many inconsistencies between the new version and the old version , For the convenience of practice, please choose the common version .

DDL（Data Definition Language): Data definition statement , For libraries and The creation of a table 、 modify 、 Delete .

DDL Used to define the structure of the database , Such as creating 、 Modify or delete Database objects , Includes the following SQL sentence ：

1. CREATE TABLE： Create database tables

2. ALTER TABLE： Change table structure 、 add to 、 Delete 、 Modify column length

3. DROP TABLE： Delete table

4. IF EXISTS It is only used for deleting and creating libraries and tables

Two 、 Library management

1、 Database creation

     grammar ：
        CREATE DATABASE Library name ;

Create a database using fault tolerance judgment （ Determine whether the database exists , If any, create after deletion , If there is no direct creation ）：

mysql> create database if not exists mysqld;
Query OK, 1 row affected, 1 warning (0.24 sec)

Change the library name (8.0 Later versions do not support the use of )
        RENAME DATABASE book TO The name of the new library ;

2、 View the system default character set

mysql> show variables like "%char%";
+--------------------------+-----------------------------------+
| Variable_name            | Value                             |
+--------------------------+-----------------------------------+
| character_set_client     | utf8                              |
| character_set_connection | utf8                              |
| character_set_database   | utf8                              |
| character_set_filesystem | binary                            |
| character_set_results    | utf8                              |
| character_set_server     | utf8                              |
| character_set_system     | utf8                              |
| character_sets_dir       | F:\Tools\mysqin\share\charsets\ |
+--------------------------+-----------------------------------+
8 rows in set, 1 warning (0.19 sec)

3、 Modify database character set

mysql> alter database mysqld character set gbk;
Query OK, 1 row affected (0.20 sec)

4、 Delete database （ If it exists ）

mysql> DROP DATABASE IF EXISTS mysqld;
Query OK, 0 rows affected (0.25 sec)

3、 ... and 、 The management of the table

1、 The creation of a table

grammar ：

      CREATE TABLE Table name (
        Field 1 Field type 【( length ) constraint 】,
         Field 2 Field type 【( length ) constraint 】,
        Field 3 Field type 【( length ) constraint 】,
        Field name Field type 【( length ) constraint 】
     );

Fault tolerant creation ：

# Case study 1： Create table book

CREATE TABLE IF EXISTS book(
        id int(5),# Book number
        bName varchar(20),# Title
        price double(2),# Price
        authorId int(5),# author id
        publishDate datetime # Publication date
    );

# Case study 2： Create table author
    CREATE TABLE 【IF EXISTS】 book(
        id int(5),
        au_Name varchar(20),
        nation varchar(10)
    );

2、 The modification of table

grammar ：
    ALTER TABLE Table name Change column names | Modify column type | Add columns | Delete column | Modify the name of the table 【COLUMN】 Name 【 constraint 、 type 】;
    ALTER TABLE Table name CHANGE|MODIFY|ADD|DROP|RENAME 【COLUMN】 Name 【 constraint 、 type 】;

modify ：
    ① Name
        ALTER TABLE Table name CHANGE 【COLUMN】 Old column names New column names New field type ;
    ② Column types and constraints
        ALTER TABLE Table name MODIFY 【COLUMN】 Old column names New field type ;
    ③ Add new column
        ALTER TABLE Table name ADD 【COLUMN】 New column names New field type ;
    ④ Delete column
        ALTER TABLE Table name DROP 【COLUMN】 Name ;
    ⑤ Modify the name of the table
        ALTER TABLE Table name RENAME TO 【COLUMN】 The new name of the table ;

3、 The deletion of the table
    DROP TABLE IF EXISTS Table name ;
    SHOW TABLES; # View all tables in the current database

Four 、 General database building table writing method

    DROP DATABASE IF EXISTS Old library name ;
    CREATE　DATABASE　 The name of the new library ;
    
    DROP TABLE IF EXISTS The old name of the table ;
    CREATE　TABLE　 The new name of the table ();

5、 ... and 、 Replication of tables

# Just copy the structure of the table
    CREATE TABLE copy LIKE  book;

# You can copy the structure and data content of the table
    CREATE TABLE copy2 
    SELECT * FROM book;
# Copy only part of the data
    CREATE TABLE copy3
    SELECT id,name 
    FROM book
    WHERE name=" Chunshang village tree ";
# Copy only part of the table structure
    CREATE TABLE copy4
    SELECT id,name 
    FROM book
    WHERE 0;

summary ： The creation and modification of the basic library is relatively simple , Focus on the syntax and keywords of the modified part and the keywords when creating the table , Later, when other parts are published, they will write one by one , The content is not very comprehensive , After simple study, go deep .