mysql-Database and data table basic operation

1. Database operations

To Create a database :

CREATE {database| SCHEMA} [IF not EXISTS] Db_name

If CREATE DATABASE t1

[If not EXISTS]: If the library already exists, it will not return an error

Display the database encoding method :

SHOW CREATE DATABASE T1

Specify the encoding method :

CREATE DATABASE IF not EXIST T2 CHARACTER SET=GBK

To Modify a database :

ALTER {database| SCHEMA} db_name

such as alter DATABASE T2 CHARACTER Set=utf8

to view a list of databases under the current server :

SHOW {database| SCHEMAS}

such as SHOW DATABASES

View the currently open database

SELECT DATABASE ();

To Delete a database :

DROP {database| SCHEMA} [IF EXISTS] Db_name

2. Data Sheet

Use database name

To create a data table :

CREATE TABLE [IF not EXISTS] table_name (column_name data_type,...)

Such as:

CREATE TABLE tb1 (

Username VARCHAR (20),

Age TINYINT UNSIGNED,

Salary FLOAT (8,2) UNSIGNED

);

To view a list of databases :

SHOW TABLES [From Db_name]

To view the data table structure :

SHOW COLUMNS from Tb_name

Insert Record :

INSERT [Into] tb_name [(Col_name,...)] VALUES (Val,...)

Such as:

INSERT tb1 VALUES (' Tom ', 25,7863.25);//must match

INSERT tb1 (username,salary) VALUES (' Tom ', 7863.25);

Record Lookup :

SELECT Expr,... From Tb_name

Such as:

SELECT *from tb1;//* for field filtering

Null and non-null for null:

Such as:

CREATE TABLE TB2 (

Username VARCHAR () not NULL,

Age TINYINT UNSIGNED NULL

);

Auto Number :

Auto_increment: Must be used in combination with primary key, by default the starting value is 1, each increment is 1

PRIMARY KEY :

PRIMARY KEY: Automatic for not NULL does not need to use values with auto_increment unique not to repeat

CREATE TABLE Tb3 (

ID SMALLINT UNSIGNED auto_increment PRIMARY KEY,

Username VARCHAR () not NULL

);

CREATE TABLE TB4 (

ID SMALLINT UNSIGNED PRIMARY KEY,

Username VARCHAR () not NULL

);

UNIQUE constraint :

Unique KEY: The uniqueness of the record can be guaranteed, and the field can be null. Multiple unique constraints can exist for each data table

CREATE TABLE Tb5 (

ID SMALLINT UNSIGNED auto_increment PRIMARY KEY,

Username VARCHAR () not NULL UNIQUE KEY,

Age TINYINT UNSIGNED

);

Default Constraints :

Default: Automatically assigns defaults when inserting a record without explicitly assigning a value to it

CREATE TABLE Tb5 (

ID SMALLINT UNSIGNED auto_increment PRIMARY KEY,

Username VARCHAR () not NULL UNIQUE KEY,

Sex ENUM (' 1 ', ' 2 ', ' 3 ') DEFAULT ' 3 '

);

FOREIGN KEY constraints :

FOREIGN KEY P: Maintain data consistency and integrity for one-to-one or one-to-many relationships

Requirements:

Note: The child table is a table with a foreign key column, and the table referenced by the child table is called the parent table

to edit the default storage engine (configuration) for a data table:

Default-set-engine=innodb

CREATE table Provinces (//parent table

ID SMALLINT UNSIGNED auto_increment PRIMARY KEY,

PName VARCHAR () not NULL

);

CREATE TABLE Users (//Sub-band

ID SMALLINT UNSIGNED auto_increment PRIMARY KEY,

Username VARCHAR () not NULL,

pid SMALLINT UNSIGNED,//Note data types must be consistent

FOREIGN KEY (PID) REFERENCES provinces (ID)

);

Such as:

• FOREIGN KEY (PID) PREFERENCES provinces (id) on the DELETE CASCADE,

INSERT provinces (pname) VALUES (' A ');

INSERT provinces (pname) VALUES (' B ');

INSERT provinces (pname) VALUES (' C ');

INSERT users1 (username,pid) VALUES (' Tom ', 1);

INSERT users1 (username,pid) VALUES (' Jerry ', 2);

INSERT users1 (username,pid) VALUES (' Lucy ', 3);

When id=3 data c is deleted from the provinces, Lucy in the corresponding Users1 is also deleted, as is the case with the update;

In practical development, we seldom use physical foreign key constraints, but logical constraints, and physical foreign key constraints are supported only by InnoDB, which is not supported by MyISAM, which does not support physical foreign key constraints. Conversely, when we use the engine as MyISAM, we can only use logical foreign keys (that is, the logical relationship of the design of two tables).

In the actual development, with column-level constraints are more, table-level constraints are seldom used, in addition, in all constraints, it is not said that each constraint has a table-level or column-level constraints, where NOT NULL non-null constraints, the default constraint of the two constraints there is no table-level constraints, they have column-level constraints, For the other three kinds, such as primary key, unique, foreign key, they all can exist table-level and column-level constraints.

3. Modify the Data sheet

Add a single column :

ALTER TABLE tb_name ADD [column] Col_name column_definition [first| After Col_name];

Add multiple columns :

ALTER TABLE tb_name ADD (col_name data_type,...);

To delete a single column :

ALTER TABLE tb_name DROP col_name;

To Delete multiple columns :

ALTER TABLE tb_name DROP col_name,drop ...;

compound increment and decrement column :

ALTER TABLE tb_name ADD col_name data_type [first| After Col_name],drop Col_name;

ALTER TABLE tb_name ADD (col_name data_type,...), DROP col_name,drop ...;

Add a PRIMARY KEY constraint

ALTER TABLE tb_name ADD [CONSTRAINT [symbol]] PRIMARY KEY (index_col_name);

Add a UNIQUE Constraint

ALTER TABLE tb_name ADD [CONSTRAINT [symbol]] UNIQUE [index| KEY] (index_col_name,...);

Add a FOREIGN KEY constraint

ALTER TABLE tb_name ADD [CONSTRAINT [symbol]] FOREIGN KEY (index_col_name,...) REFERENCES db_name (col_name);

Add or remove a DEFAULT constraint

ALTER TABLE tb_name ADD col_name {SET DEFAULT literal | DROP DEFAULT};

Delete a PRIMARY KEY constraint

ALTER TABLE tb_name DROP PRIMARY KEY;

Remove UNIQUE Constraint

ALTER TABLE tb_name DROP {INDEX | KEY} index;;/ /with command show INDEXES from tb_name (table name) \g; View the column name of the unique constraint. In addition, the index can also be used to delete indexes directly

Delete a FOREIGN KEY constraint

ALTER table Tb_name DROP FOREIGN key fk_name;//Use show CREATE TABLE users2; View the name of the foreign key constraint, defined by constraint [symbol]

Modifying a column definition

ALTER TABLE Tb_name MODIFY ID SMALLINT UNSIGNED not NULL first;//modify the data type of the ID field and where it is located in table Users2

Modify column names

Alter TABLE tb_name change PID (old field name) p_id (new field name) TINYINT UNSIGNED not null;//modify PID field to p_id field

Data table renaming

ALTER table tb_name1 RENAME tb_name2;//Change the name users2 to the new USERS3

mysql-Database and data table basic operation