Basic usage of Mysql: use of native SQL statements: Table creation, deletion, and modification (I), mysqlsql

Basic usage of Mysql: use of native SQL statements: Table creation, deletion, and modification (I), mysqlsql

In the previous article, I mainly talked about the installation of MariaDB, a Mysql branch, on Linux. Now I can try the charm of SQL.

Red indicates the command. Blue indicates the custom name.

View Database

 

MariaDB [(none)]> show databases; # view Database list + ---------------------- + | Database | + -------------------- + | information_schema | # Database Name example: there are four local databases (the table data table is saved in the database) | human | mysql | performance_schema | + ------------------ + 4 rows in set (0.00 sec)

 

First, create a database.

 

Create a database

　　

MariaDB [(none)]> create database data_name; # create database data_name as the name of the created custom database
(Note: The created database does not contain the s suffix. Do not confuse databases used to view database commands.) Query OK, 1 row affected (0.00 sec) # indicates that the SQL statement is successfully executed.

 

Then we enter the database we just created

　　

MariaDB [(none)]> use data_name; # entering the Database data_name indicates the name of the Database to enter changed # the Database to enter has been changed

 

The first thing we need to talk about here is simple creation.

Create a data table in the database

　　

MariaDB [data_name]> create table table_name (id int not null, name varchar (20 )); # creating a simple data table table_name indicates that the name id of the created data table indicates that the column name int in the first column of the data table indicates that the inserted data in the column is an integer, not null, and the constraint name cannot be null. name of the second column varchar (20) for the character type 20, the data character length of this column can only be 20 Query OK, 0 rows affected (0.06 sec)

 

Check the table created in our database.

　

MariaDB [data_name]> show tables; # view all data tables in the current database (including visual charts) + --------------------- + | Tables_in_data_name | + --------------------- + | table_name | # table data table name + --------------------- + 1 row in set (0.00 sec)

 

　

Two ways to view the structure of a data table

Method 1 (suitable for viewing the column names and constraints of this table)

Describe can be replaced by desc

　

MariaDB [data_name]> describe table_name; # method 1 for viewing the structure of a table data table (you can intuitively see the column and bound constraints of a data table) + ------- + ------------- + ------ + ----- + --------- + ------- + | Field | Type | Null | Key | Default | Extra | # field: column name type: insert data Type null: if the NO condition is not empty or not, it cannot be blank (if the inserted data is empty, the SQL statement will fail to be inserted) + ------- + ------------- + ------ + ----- + --------- + ------- + # KEY: primary key constraint or foreign key constraint (null indicates no constraint) default: indicates that when the inserted data is null, It is inserted by default (null indicates w is null) | id | int (11) | NO | NULL | # Extra) | YES | NULL | + ------- + ------------- + ------ + ----- + --------- + ------- + 2 rows in set (0.00 sec)

 

　

Method 2 (a good way to view detailed query constraint names)

 

MariaDB [data_name]> show create table table_name; # view the SQL statement created for the table data table (you can see that the SQL statement used for the creation of the table at that time is convenient to view other details of the constraint name and settings) + ------------ + tables + | Table | Create Table | + ------------ + tables + | table_name | create table 'table _ name' (# ENGINE: indicates the table type, currently, MyISAM and InnoDB are the most commonly used table types in Mysql. Each table has its own advantages. Click here | 'id' int (11) not null, # CHARSET: it indicates that the character encoding of this table is Latin1 (Chinese characters are not supported). You can set mainstream gb2312 or utf8 encoding methods. Click here | 'name' varchar (20) default null # Here are the solutions for setting Chinese character encoding once and for all |) ENGINE = InnoDB default charset = latin1 | + ------------ + bytes + 1 row in set (0.00 sec)

 

Does the above table feel uncomfortable and uncomfortable? For patients with obsessive-compulsive disorder, myslq provides a way to treat obsessive-compulsive disorder;

　　

MariaDB [data_name]> show create table table_name \ G # Here; changed to \ G and found that the display style is different, more eye-catching (the gospel of obsessive-compulsive disorder) \ G is not just here,
All displayed SQL statements can be replaced by \ G; * *************************** 1. row *************************** Table: table_nameCreate Table: create table 'table _ name' ('id' int (11) not null, 'name' varchar (20) default null) ENGINE = InnoDB default charset = latin11 row in set (0.00 sec)

 

* ************************************ I am dynamic light wave ************************************** **

 

Modify data table fields

Have a cup of tea and have a rest

 

One day, the project manager says he forgot to tell you that this table lacks an important field. The friendship boat says "flip", but don't worry about redo, SQL provides the opportunity to repent and add or delete columns at will.

 

Add data table fields

　　

MariaDB [data_name]> alter table table_name add phone varchar (11, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0

 

Delete data table fields

 

MariaDB [data_name]> alter table table_name drop phone; # Delete the phone column in The table_name table.
(This is much simpler than adding. Actually, destruction is much easier than creating. But aren't all creations created after destruction ?) Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0

 

Is it difficult to delete and modify a column if it is set incorrectly? There is a way to delete and add a dual-in-One overlord and modify the machine.

Modify data table fields

MariaDB [data_name]> alter table table_name change name username varchar (20); # change the name field name of table_name in the data table to username and change the maximum length of varchar to 20 Query OK, 0 rows affected (0.05 sec) can be modified to add the constraint Records: 0 Duplicates: 0 Warnings: 0

 

 

There are other ways to change the type but not the field name.

Alter table table_name modify name char (20 );

This method can achieve the same effect using the same old and new names of change, that is, change is a small omnipotent

 

The last lazy artifact --Template tabulation

 

1. A good helper for expanding tables

　

MariaDB [data_name]> create table copy1_table_name like table_name; # copy table structure table_name create copy1_table_name table (this method does not copy internal data of this table) Query OK, 0 rows affected (0.06 sec)

 

2. Good assistant for backing up tables

　　

MariaDB [data_name]> create table copy2_table_name as select * from table_name; # copy the table structure table_name to create the copy1_table_name table (this method copies the internal data of this table) Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0

 

This is the case. If you still have something missing or make a mistake, please point it out!