MySQL Statement advanced

1.null

Mysql> CREATE TABLE worker (ID int not null,name varchar (8) Not null,pass varchar (a) not null);

Mysql> INSERT into worker values (1, ' HA ', ' 123456 ');

Mysql> INSERT into worker values (1, ' LB ', null);

ERROR 1048 (23000): Column ' pass ' cannot be NULL cannot be null

Mysql> INSERT INTO worker values (2, ' HPC ', ');

Null in MySQL is space-consuming. is a numeric value. Null and ' are different.

Default value for 2.default set field

Mysql> CREATE TABLE Test2 (name varchar (8) Not null,dept varchar (+) Default ' SOS ');

mysql> INSERT into TEST2 (name) VALUES (' Kko ');

If the Time field, the default is the current time, insert 0 o'clock, the default is the current time.

If it is an enum type, the default is the first element.

3.auto_increment field constraints

for the primary key, this is very useful. You can create a unique identifier for each record (maximum value plus 1)

Mysql> CREATE table items (ID int not NULL auto_increment primary key, label varchar (a) not null);

Mysql> INSERT into items values (' Aaba ');

Mysql> INSERT INTO items values (9, ' Aaba ');

How many more IDs will be inserted

Mysql> INSERT into items values (' abc ');

Delete cannot clear auto_increment

Mysql> Delete from items;

Mysql> INSERT INTO items values ("AAAA");

Method Two: Delete all records in the table, clear the Auto_increment value.

mysql> TRUNCATE TABLE items;

Mysql> INSERT into items values (' ABV ');

4. Index

To speed up search, reduce query time.

1 The index is stored as a file. If the index is too large, it takes up disk space. And he affects: Insert, UPDATE, delete execution time.

2 The data in the index must be synchronized with the data table data: If there are too many indexes, the indexes will be updated synchronously when the data in the table is updated, which reduces the efficiency.

Type of index

1. General Index

2. Uniqueness Index

3. Primary key index (primary index)

4. Composite Index

Normal index

The most basic index, not uniqueness, is to speed up the query speed

Note: You can use Key , you can also use Index . the index name (field) , the index name, can be added or not, and the field name is not used as the indexed name.

Mysql> CREATE TABLE demo (ID int (4), name varchar (), PWD varchar, index (PWD));

Note: Index and the Key is the same

Mysql> CREATE TABLE demo1 (ID int (4), name varchar (), PWD varchar, key (PWD));

Mysql> CREATE TABLE Demo2 (ID int (4), name varchar, pwd varchar, key index_pwd (PWD)); #加上索引名称

View Index

Note: if Key is a MUL, then the value of the column can be repeated , The column is a leading column of a non-unique index ( first column ) or a component of a unique index but can contain null values NULL . is to indicate that it is a normal index.

Let's delete the index first

Mysql> ALTER TABLE demo drop key pwd; Note that the PWD here refers to the name of the index, not the field of the PWD in the table.

Then use ALTER to add

Mysql> ALTER TABLE demo add key (PWD);

Unique index

Basically the same as a normal index, but with one difference: all the values of an indexed column can only occur once, that is, must be unique to constrain the content, and the field value can only occur once. A unique index should be added. Uniqueness allows null values < allow NULL >.

Mysql> CREATE TABLE Demo3 (ID int (4) Auto_increment primary key, uName varchar, upwd varchar, unique index (UN AME));

Method Two: Add a unique index when modifying a table

ALTER TABLE Table name Add unique Index name ( field );

mysql> ALTER TABLE DEMO3 drop key uName;

Mysql> ALTER TABLE DEMO3 add unique (uName);

Primary key Index

Querying the database, by primary key query is the fastest, each table can have only one primary key column, you can have multiple ordinary index columns. The primary key column requires that all the contents of the column must be unique, and the indexed column does not require that the content be unique, not allowed to be empty

Mysql> CREATE TABLE Demo4 (ID int (4) NOT NULL Auto_increment primary key,name varchar (4) is not NULL);

method Two: Add a table after creating < Not recommended >

Remove the cause of this condition being auto_increment

mysql> ALTER TABLE DEMO4 change ID ID int (4) is not null;

mysql> ALTER TABLE Demo4 drop PRIMARY key;

Add again

mysql> ALTER TABLE DEMO4 change ID ID int (4) is not null primary key auto_increment;

Summary: Primary key index, uniqueness Index difference: Primary key index cannot have NULL , a unique index can have a null value

Composite Index

An index can consist of one, two, or more columns. An index on two or more columns is called a composite index

Example: Create a table to hold the IP and port that the server allows or deny, to record the IP and port to be unique.

Mysql> CREATE table Firewall (a host varchar () not NULL, port smallint (4) is not NULL, Access enum (' Deny ', ' allow ') is not Nu ll, primary key (Host,port));

Mysql> INSERT into firewall values (' 10.96.52.46 ', ' deny ');

Mysql> INSERT into firewall values (' 10.96.52.46 ', +, ' allow ');

Mysql> INSERT into firewall values (' 10.96.52.46 ', +, ' allow ');

ERROR 1062 (23000): Duplicate entry ' 10.96.52.46-21 ' for key ' PRIMARY '

5. Foreign key

FOREIGN Key: the integrity of the data is checked each time it is inserted or updated.

Note: The following 4 conditions must be met for a successful creation:

1. Ensure that the referenced tables and fields exist.

2. The fields that make up the foreign key are indexed.

3. The storage engine must be specified with type: InnoDB.

4. Foreign key fields and associated fields must be of the same data type.

Example: we create a database that contains user information tables and order tables

Mysql> CREATE DATABASE market;

Mysql> CREATE table ' user ' (id int (one) not NULL auto_increment, name varchar (+) NOT null default ' ', sex int (1) not nul L Default ' 0 ', primary key (ID)) Engine=innodb;

#创建时, if the table name is a SQL keyword, use the anti-quote '

Mysql> CREATE TABLE ' order ' (o_id int (one) auto_increment, u_id Int (one) default ' 0 ', username varchar (+), Money Int (11), Primary KEY (O_ID), index (U_ID), foreign key Order_f_key (u_id) references user (ID) on the DELETE cascade on UPDATE cascade) EN Gine=innodb;

Note:

1:on DELETE CASCADE on UPDATE CASCADE add cascade deletions and updates:

2:: Ensure that the ID field exists in the referenced table user. The field u_id that makes up the foreign key is indexed. The storage engine must be specified with type: InnoDB.

Foreign key fields and associated fields must be of the same data type.

Inserting test data

mysql> INSERT INTO User (Name,sex) VALUES (' HA ', 1), (' LB ', 2), (' HPC ', 1);

mysql> insert INTO ' order ' (U_id,username,money) VALUES (1, ' HA ', 234), (2, ' LB ', 146), (3, ' HPC ', 256);

Test Cascade Delete:

mysql> Delete from user where id=1; To delete data with ID 1 in the user table

Test cascading updates:

mysql> Update user set id=6 where id=2;

Test data integrity

FOREIGN KEY constraint, the order table is constrained by the user table

Insert a data in order u_id for 5 users, there is no in the user table, so insert does not go in

mysql> INSERT INTO user values (5, ' Find ', 1);

mysql> insert INTO ' order ' (U_id,username,money) VALUES (5, ' Find ', 346);

Method Two: Create a foreign key and cascade update via ALTER TABLE, CASCADE Delete

Mysql> CREATE TABLE Order1 (o_id int (one) auto_increment, u_id Int (one) default ' 0 ', username varchar (+), Money Int (11), Primary KEY (O_ID), index (u_id)) Type=innodb;

Mysql> ALTER TABLE Order1 add foreign key (u_id) references user (ID) on the DELETE cascade on update Cascade,type=innodb;

Mysql> ALTER TABLE ORDER1 add constraint ' BK ' foreign Key (u_id) references user (ID) on DELETE cascade on UPDATE cascade,  Type=innodb; Specify a foreign key name

Mysql> Show CREATE TABLE order1;

To delete a foreign key:

Grammar

ALTER TABLE data table name drop FOREIGN KEY constraint (foreign key) name

mysql> ALTER TABLE order1 drop foreign key order1_ibfk_1;

Mysql> Show CREATE TABLE order1;

6. View

Create a View

Syntax: CREATE view name (that is, virtual table name) as SELECT statement.

mysql> CREATE view BC as select B.bname, B.price, c.btypename from books as B left join category as C on B.btypeid=c. Btypeid;

can be accessed according to the normal table.

In addition, the data in the chart is synchronized with the data in the original data table.

Mysql> show CREATE View BC \G

Update or modify a view

Alter VIEW view name (that is, virtual table name) as SELECT statement.

Update view name (that is, virtual table name) set

mysql> ALTER view BC AS SELECT B.bname, b.publishing, C.btypeid from books as B left join category as C on b.btypeid=c . Btypeid;

mysql> update BC set bname= ' HA ' where price=34;

Delete a view

Drop view name.

mysql> drop View BC;

MySQL Statement advanced