MySQL database basic operations (II)

table structure operation (ALTER table)

Add a single column: ALTER TABLE tb1_name Add [colunm] col_namecolumn_definition [first| After Col-name]

　　

CREATE TABLE ' tb1 ' (    ' id ' int,      ' name ' varchar); example: MySQL> ALTER table ' tb1 '     ADD ' age ' INT     , Query OK, 0 rows affected (0.03 sec) records:0  duplicates:0  warnings:0mysql> ALTER tabl E ' tb1 '    , ADD ' number    ' INTfirst ; Query OK, 0 rows affected (0.03 sec) records:0  duplicates:0  warnings:0mysql> ALTER TABLE ' Tb1 ' ADD ' aaa ' INT after ' id ';

Add multiple columns: ALTER TABLE tbl_name Add [column] (col_name column_definition,...)

　　

example: MySQL> ALTER TABLE '    tb1' , add '  aa ' int,     add  ' bb ' int,      Add  ' cc '    INT , Query OK, 0 rows affected (0.02 sec) records:0  duplicates:0  warnings:0mysql>

Delete columns in the Data table ALTER TABLE Tbl_name DROP [column] col_name;

　　

# Example:mysql> ALTER TABLE '    tb1', DROP ' AA '     , Query OK, 0 rows affected (0.03 sec) records:0  duplicates:0  warnings:0mysql> ALTER TABLE ' Tb1 '    BB ', drop ' cc    ' ,     Query OK, 0 rows affected (0.03 sec) records:0  duplicates:0  warnings:0

Table-Structured operations supplement:

ALTER TABLE ' Tbname ' added: Add Delete: Drop modify: MODIFY  #改列的数据类型 (attribute)  change  #改列名和数据类型  RENAME  # Change the table name to modify the column name mysql> ALTER TABLE TB1 change ' name ' sex ' varchar (20); Modify data type mysql> ALTER TABLE TB1 modify ' age ' varchar (20); Modified table name mysql> ALTER table ' tb1 ' RENAME to ' students '; Query OK, 0 rows affected (0.40 sec)

non-null constraint

NULLThe field value can be empty

NOT NULLField value cannot be empty

Example:mysql> CREATE TABLE tb1 (     ID INT,,    name VARCHAR (), not NULL    ); Query OK, 0 rows affected (0.01 sec) # When there is a non-null constraint, specify INSERT, you must add name. mysql> INSERT into TB1 (ID) value (1);   # error mysql> INSERT into TB1 (id,name) value (1, ' canon '); Query OK, 1 row affected (0.01 sec) # # Note   inside MySQL, ' Not equal to null# manual, add non-null constraint (Must this field, no null value) mysql> ALTER TABLE TB1    -Modify ID int not NULL; Query OK, 0 rows affected (0.02 sec) records:0  duplicates:0  warnings:0# cancel non-null constraint mysql> ALTER TABLE TB1    -> ; Modify ID int;

UNIQUE Constraint

Make sure that the value in the field is uniqueunique key

Example:mysql> CREATE TABLE TB2 (-    ID int not null unique key,      name varchar (a) not null    );    mysql> INSERT into TB2 value (1, ' Zhang San '); Query OK, 1 row Affected (0.00 sec) mysql> INSERT into TB2 value (1, ' Zhang San ');  # error, violation of UNIQUE constraint    #添加唯一约束mysql > ALTER TABLE ' tb2 '    ADD unique key (' name '),    #删除唯一约束mysql > Desc TB2 ;mysql> ALTER TABLE TB2    , drop key name; #联合唯一mysql > ALTER TABLE TB2    , add AA int,    add bb Int;mysql> ALTER TABLE TB2,    add unique key (AA,BB);mysql> insert INTO TB2 value (4, ' Canon ', $);mysql> Inse RT into TB2 value (5, ' haha ', up); ERROR 1062 (23000): Duplicate entry ' 1-2 ' for key ' AA ' # Remove federated unique  (show create table TB2;) ALTER TABLE TB2 drop key AA;

PRIMARY KEY constraint

The primary key guarantees the uniqueness of the record, uniquely identifying each data primary key automatically for NOT NULL only one primary key NOT NULL + UNIQUE KEY per data table

When one is another UNIQUE KEY NOT NULL , then it is treated as a PRIMARY KEY primary Key when there is no primary key in a table, the first non-null and unique column that appears is considered to have a primary key.

#A primary key is a data table that can be uniquely identified. It's like an ID card. Mysql>CREATE TABLE Tb3 (-ID int primary KEY,-Name varchar (20) notNULL- ); MySQL>desc Tb3;mysql> INSERT into TB3 value (1,'Zhang San'); Query OK,1 row affected (0.27sec) MySQL> INSERT into TB3 value (1,'Zhang San'); ERROR1062 (23000): Duplicate entry'1'  forKey'PRIMARY'#Delete a primary KEY constraintMysql>ALTER TABLE TB3--drop primary key;#inside a table, there is only one primary keyMysql>desc tb3;#Add a PRIMARY KEY constraintMysql>ALTER TABLE TB3-Add primary key (ID); #Federated Primary KeyMysql>CREATE TABLE TB4 (-id_a int,-id_b int,Content varchar (20),    -primary KEY (Id_a,id_b)-); MySQL>desc TB4;#Delete a primary KEY constraintMysql>ALTER TABLE TB4-drop primary key;#To add a federated primary keyMysql>ALTER TABLE TB4Add primary key (Id_a,id_b);

self- AUTO_INCREMENT growth

AUTO_INCREMENTAuto-numbering, usually combined with primary key. There's only one self-increment in a table . By default, the starting value is 1, and the increment is 1 each time. When you insert a record, if you AUTO_INCREMENT explicitly specify a value for the data column, there are two cases.

Case one, if the inserted value repeats with an existing number, an error message occurs because the value of the Auto_increment data column must be unique;

In case two, if the inserted value is greater than the numbered value, it is inserted into the data column and the next number is incremented from the new value. In other words, you can skip some numbers. If the maximum value of the self-increment sequence is deleted, the value is reused when a new record is inserted. (Can be adjusted large, can not be reduced)

Example: MySQL>CREATE TABLE Tb5 (-ID int primary key auto_increment,Name varchar (20)    ) auto_increment = 100;#If you do not write, the default starting from 1Mysql>desc Tb5;mysql> INSERT into TB5 (name) VALUES ('Zhang San'),('John Doe'); MySQL> select * fromtb5;#auto_increment value, can be adjusted to largeInsert into TB5 (id,name) VALUES (110,'Harry'); MySQL> select * fromtb5;#cannot be adjusted to smallInsert into TB5 (id,name) VALUES (108,'Bastard'INSERT into TB5 (name) VALUES ('Tianqi'); MySQL> select * fromtb5;#Delete auto-GrowMysql>ALTER TABLE TB5-modify ID int;#Increase auto-growth auto_incrementMysql>ALTER TABLE TB5-Modify ID int auto_increment;

Default Constraints DEFAULT

DEFAULT(default constraint) initial value setting, when inserting a record, automatically assigns a default value if no value is explicitly assigned to the field.

Add/| DROP DEFAULT}

#Example:Mysql>CREATE TABLE Tb6 (-ID int primary key auto_increment,-Name varchar (20) notNULL,-Age int notNull default); MySQL>desc Tb6;mysql> INSERT into TB6 (name) VALUES ('Zhang San'),('John Doe'),('Harry'); MySQL> select * fromTb6;#Delete DefaultMysql>ALTER TABLE TB6-Modify Age int notNull;mysql>desc TB6;#(2)Mysql>ALTER TABLE TB6-alter age drop default;#Add defaultMysql>ALTER TABLE TB6-Modify Age int default 20; MySQL>desc TB6;#(2)Mysql>ALTER TABLE TB6, alter age set default 21;

Homework

A student form for Jebsen (
Study number PRIMARY key
The name is not empty unique
Gender
Age is not empty
）

1. Remove the unique name
2. Add gender, not empty, default to Male
3. Set age defaults to 18

MySQL database basic operations (II)