Detailed operation of MySQL library table

Keywords: MySQL Database Python less

One library operation

1. Creating a database

1.1 Grammar

CREATE DATABASE database name charset utf8;

1.2 Database Naming Rules

By letters, numbers, underscores, bars, bars, and dollars

Case-sensitive

Uniqueness

Keyword cannot be used such as create select

Numbers cannot be used alone

Maximum 128 bits

Basically the same naming rules as python or js

2. Database-related operations

It's also some basic operations, similar to what we said before.

1 view the database
show databases;
show create database db1;
select database();

2 Select the database
USE Database name

3 Delete the database
DROP DATABASE Database name;

4 modify the database
alter database db1 charset utf8;

Let's talk about the contents of the library. Haha, it's a little bit less. It's not our focus. Let's look at the table operation below.~~~

Two-table operation

1. Storage Engine

Storage engine is table type, mysql will have different processing mechanism according to different table type. For the introduction of storage engine, see my blog: https://www.cnblogs.com/changxin7/p/11525457.html

2. Table Introduction

A table is equivalent to a document, and a record in the table is equivalent to a row of contents of the document. A record in the table has a corresponding title, which is called the field of the table.

    

The id, name2, age of the first line are fields, and the rest are called a record.

3. Create tables

3.1 tabular grammar

Grammar:
create table table table name(
Field name 1 type [(width) constraint],
Field name 2 type [(width) constraint],
Field Name Type 3 [(Width) Constraints]
);

Note:
1. In the same table, field names can't be the same
 2. Width and constraints are optional and unnecessary. Width refers to field length constraints, such as: 10 in char(10).
3. Field names and types are required
mysql> create database db1 charset utf8;

mysql> use db1;

mysql> create table t1(  
    -> id int, 
    -> name varchar(50),
    -> sex enum('male','female'),
    -> age int(3)
    -> );

mysql> show tables; #View all table names under the db1 Library

mysql> desc t1;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | varchar(50)           | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+

mysql> select id,name,sex,age from t1;
Empty set (0.00 sec)

mysql> select * from t1;
Empty set (0.00 sec)

mysql> select id,name from t1;
Empty set (0.00 sec)
mysql> insert into t1 values
    -> (1,'chao',18,'male'),
    -> (2,'sb',81,'female')
    -> ;
mysql> select * from t1;
+------+------+------+--------+
| id   | name | age  | sex    |
+------+------+------+--------+
|    1 | chao |   18 | male   |
|    2 | sb |   81 | female |
+------+------+------+--------+



mysql> insert into t1(id) values 
    -> (3),
    -> (4);
mysql> select * from t1;
+------+------+------+--------+
| id   | name | age  | sex    |
+------+------+------+--------+
|    1 | chao |   18 | male   |
|    2 | sb |   81 | female |
|    3 | NULL | NULL | NULL   |
|    4 | NULL | NULL | NULL   |
+------+------+------+--------+

4. View Table Structure

mysql> describe t1; #View the table structure, abbreviated as: desc table name
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | varchar(50)           | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+


mysql> show create table t1\G; #Look at the detailed structure of the table and add \G

5. Basic data types of MySQL

For data types, see my blog: https://www.cnblogs.com/changxin7/p/11528101.html

6. Table Integrity Constraints

For table integrity constraints, see my blog: https://www.cnblogs.com/changxin7/p/11535976.html

    

7. Modify table alter table

The following content will not take you to demonstrate, simply show you, are fixed grammar format, write according to this, there is no logic, so do not do too many demonstrations, you go back to practice for yourself:

    

Syntax:
1. Modify the table name
      ALTER TABLE Table Name 
                          RENAME new table name;

2. Adding fields
      ALTER TABLE Table Name
                          ADD Field Name Data Type [Integrity Constraints... ] Note that you can split it here by commas and add multiple constraints
                          ADD Field Name Data Type [Integrity Constraints... ];
      ALTER TABLE Table Name
                          ADD Field Name Data Type [Integrity Constraints... ] FIRST;  When you add this field, put it in the first field position.
      ALTER TABLE Table Name
                          ADD Field Name Data Type [Integrity Constraints... ] AFTER field name; # after is placed after this field, we can put the newly added field into any field position of the table through a first and a after.
                            
3. Delete fields
      ALTER TABLE Table Name 
                          DROP field name;

4. Modify fields
      ALTER TABLE Table Name 
                          MODIFY Field Name Data Type [Integrity Constraints... ];
      ALTER TABLE Table Name 
                          CHANGE Old Field Name New Field Name Old Data Type [Integrity Constraints... ] # change has one more renaming function than modify, which means that only one field name has been renamed in the sentence __________ change.
      ALTER TABLE Table Name 
                          CHANGE Old Field Name New Field Name New Data Type [Integrity Constraints... ] In addition to changing the field name, the sentence changed the data type, integrity constraints, and so on.

Statement that adds foreign key attributes to a field: alter table table table 2 adds foreign key (a field in table 2) references Table 1 (a field in table 1);

Note that in mysql, table names are case-insensitive. If you rename a table name T1 (lowercase t1) to a T1 (uppercase T1), it is completely useless, because table names are lowercase in the database.

Example:
1. Modify Storage Engine
mysql> alter table service 
    -> engine=innodb;

2. Add fields
mysql> alter table student10
    -> add name varchar(20) not null,
    -> add age int(3) not null default 22;
    
mysql> alter table student10
    -> add stu_num varchar(10) not null after name;                //After adding the name field

mysql> alter table student10                        
    -> add sex enum('male','female') default 'male' first;          //Add to the front

3. Delete field
mysql> alter table student10
    -> drop sex;

mysql> alter table service
    -> drop mac;

4. Modify field type modify
mysql> alter table student10
    -> modify age int(3);
mysql> alter table student10
    -> modify id int(11) not null primary key auto_increment;    //Modification is the primary key

5. Increase constraints (for existing primary keys) auto_increment)
mysql> alter table student10 modify id int(11) not null primary key auto_increment;
ERROR 1068 (42000): Multiple primary key defined

mysql> alter table student10 modify id int(11) not null auto_increment;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

6. Adding composite primary keys to existing tables
mysql> alter table service2
    -> add primary key(host_ip,port);        

7. Add primary key
mysql> alter table student1
    -> modify name varchar(10) not null primary key;

8. Increasing primary keys and automatic growth
mysql> alter table student1
    -> modify id int not null primary key auto_increment;

9. Delete primary key
a. Delete self-increasing constraints
mysql> alter table student10 modify id int(11) not null; 

b. Delete primary key
mysql> alter table student10                                 
    -> drop primary key;

    

8. Duplicate tables

    

When we re-create a table like it, we need to write the table structure by ourselves before using create, and then insert those data into the new table, which is very troublesome, so we can use mysql directly. The function of replicating tables is provided: (replicating tables is rarely expensive, just understand it)

Grammar: Copy table structure + record (key does not copy: primary key, foreign key and index)
Mysql > create table new_service select * from service; This sentence means that the data you query from the service table should not be printed on the screen, but you should give me the new_service directly.

Let's write an example ourselves:

      

Although we can't copy key, we can add it back to him: alter table xxx

      

Copy table structure only, not data
Mysql > select * from service where 1 = 2; // condition false, no records can be found, so we can only copy the table structure through it, see the following sentence
    Empty set (0.00 sec)
Mysql > create table new1_service select * from service where 1 = 2; If the condition of filtering data is false, then only the structure is obtained, and no data is queried, so only the table structure is copied.
    Query OK, 0 rows affected (0.00 sec)
    Records: 0 Duplicates: 0 Warnings: 0

MySQL > create table T4 like employees; get like to achieve this effect

Posted by globetrottingmike on Thu, 19 Sep 2019 00:57:29 -0700