Addition, deletion, modification and query of the master table of learning notes for Python introductory development

The point of this section

• Master the addition, deletion, modification and query of tables

The duration of this section needs to be controlled 15 Within minutes

One 、 Table introduction

A table is equivalent to a file , A record in a table is equivalent to a line in a file , The difference is , A record in the table has a corresponding title , Fields called tables

id,name,qq,age Called fields , The rest , A line is called a record

Two 、 Create table

grammar

create table Table name (
Field name 1 type [( Width ) constraint condition ],
Field name 2 type [( Width ) constraint condition ],
Field name 3 type [( Width ) constraint condition ]
);
# Be careful ：
1\. In the same table , Field names cannot be the same
2\. Width and constraints are optional
3\. Field name and type are required

demonstration

MariaDB [(none)]> create database db1 charset utf8;
MariaDB [(none)]> use db1;
MariaDB [db1]> create table t1(
-> id int,
-> name varchar(50),
-> sex enum('male','female'),
-> age int(3)
-> );
MariaDB [db1]> show tables; # see db1 All table names under the library
MariaDB [db1]> 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 | |
+-------+-----------------------+------+-----+---------+-------+
MariaDB [db1]> select id,name,sex,age from t1;
Empty set (0.00 sec)
MariaDB [db1]> select * from t1;
Empty set (0.00 sec)
MariaDB [db1]> select id,name from t1;
Empty set (0.00 sec)

Insert data into a table

MariaDB [db1]> insert into t1 values
-> (1,'egon',18,'male'),
-> (2,'alex',81,'female')
-> ;
MariaDB [db1]> select * from t1;
+------+------+------+--------+
| id | name | age | sex |
+------+------+------+--------+
| 1 | egon | 18 | male |
| 2 | alex | 81 | female |
+------+------+------+--------+
MariaDB [db1]> insert into t1(id) values
-> (3),
-> (4);
MariaDB [db1]> select * from t1;
+------+------+------+--------+
| id | name | age | sex |
+------+------+------+--------+
| 1 | egon | 18 | male |
| 2 | alex | 81 | female |
| 3 | NULL | NULL | NULL |
| 4 | NULL | NULL | NULL |
+------+------+------+--------+

Pay attention to pay attention to ： Don't add a comma to the last field in the table

3、 ... and 、 View table structure

MariaDB [db1]> describe t1; # View table structure , It can be 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 | |
+-------+-----------------------+------+-----+---------+-------+
MariaDB [db1]> show create table t1\G; # Look at the detailed structure of the table , coca \G

Four 、 Modify table structure

 grammar ：
1\. Modify the name of the table
ALTER TABLE Table name
RENAME The new name of the table ;
2\. Add fields
ALTER TABLE Table name
ADD Field name data type [ Integrity constraints …],
ADD Field name data type [ Integrity constraints …];
ALTER TABLE Table name
ADD Field name data type [ Integrity constraints …] FIRST;
ALTER TABLE Table name
ADD Field name data type [ Integrity constraints …] AFTER Field name ;
3\. Delete field
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 types [ Integrity constraints …];
ALTER TABLE Table name
CHANGE Old field name new field name New data types [ Integrity constraints …];

demonstration

 Example ：
1\. Modify the 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; // add to name After the fields
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; // Change to primary key
5\. Add constraints （ Add... To the existing primary key 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\. Add a compound primary key to an existing table
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\. Add primary keys and grow automatically
mysql> alter table student1
-> modify id int not null primary key auto_increment;
9\. Delete primary key
a. Delete autoincrement constraint
mysql> alter table student10 modify id int(11) not null;
b. Delete primary key
mysql> alter table student10
-> drop primary key;

5、 ... and 、 Copy table

 Replicated table structure ＋ Record （key Will not copy : Primary key 、 Foreign keys and indexes ）
mysql> create table new_service select * from service;
Copy only table structure
mysql> select * from service where 1=2; // The condition is false , No records can be found
Empty set (0.00 sec)
mysql> create table new1_service select * from service where 1=2;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create table t4 like employees;

6、 ... and 、 Delete table

DROP TABLE Table name ;