Shulou(Shulou.com)06/01 Report--

SQL statement-DDL statement

=

Overview:

=

MySQL server SQL statement

-server command: SQL statement, sent to the server to run and retrieve the result; an explicit statement Terminator is required

★ DDL: data definition language

☉ function:

Mainly used for database components, such as databases, tables, indexes, views, triggers, event schedulers, stored procedures, stored functions

Common ☉ commands:

CREATE (create), ALTER (modify), DROP (delete) (? Followed by a command for help)

★ DML: data manipulation language

☉ function:

CRUD (add, delete, change and query) operation, which is mainly used to manipulate the data in the table; query before each operation.

☉ command

INSERT,DELETE,UPDATE,SELECT

★ DCL: data control language

☉ function:

Authorized users, login host address permissions and recycling rights

☉ command

GRANT (authorization), REVOKE (recall rights)

SQL MODE: define the response behavior of mysqld to constraints and other violations and other settings

MODE commonly used in ★:

TRADITIONAL: traditional schema, anything that violates the data definition is not allowed

STRICT_TRANS_TABLES: only strictly limit the event table

STRICT_ALL_TABLES: strictly restrict all tables

★ modification method:

Mysql > SET GLOBAL sql_mode='MODE'

Mysql > SET @ @ global.sql_mode='MODE'

Note:

The default is empty mode. If the data definition is violated, an alert will be issued and the data will be subtracted to the maximum range allowed.

Sql mode is a required parameter. To take effect permanently, you need to write to the configuration file.

Demo:

1. When the sql mode schema is empty (default), inserting data into the table can be successfully inserted, but if the data definition is violated, the data will be reduced to the maximum allowable range, as follows:

MariaDB [(none)] > SELECT @ @ session.sql_mode;+-+ | @ @ session.sql_mode | +-+ | | +-+ 1 row in set (0.00 sec) MariaDB [(none)] > CREATE DATABASE testdb Query OK, 1 row affected (0.00 sec) MariaDB [(none)] > use testdb;Database changedMariaDB [testdb] > create table tbl1 (id tinyint unsigned,name CHAR (5)); Query OK, 0 rows affected (0.03 sec) MariaDB [testdb] > insert into tbl1 (id) values (16), (256); # default maximum is 255Query OK, 2 rows affected, 1 warning (0.00 sec) # error Records: 2 Duplicates: 0 Warnings: 1MariaDB [testdb] > select * from tbl1 +-+-+ | id | name | +-+-+ | 16 | NULL | 25 5 | NULL | # you can find that the 256 we inserted is not successful, only to the maximum range of +-+-+ 2 rows in set (0.00 sec) MariaDB [testdb] > insert into tbl1 (name) values ('jerry'), (' taotaoxiuxiu'). Query OK, 2 rows affected, 1 warning (0.00 sec) Records: 2 Duplicates: 0 Warnings: 1MariaDB [testdb] > show Warnings +-+ | Level | Code | Message | +- -- + | Warning | 1265 | Data truncated for column 'name' at row 2 | +-- + 1 row in set (0.00 sec) MariaDB [testdb] > select * from tbl1 +-+-+ | id | name | +-+-+ | 16 | NULL | 255 | NULL | | NULL | jerry | | NULL | taota | # the maximum number of characters we define is limited to 5 characters, and the excess will be subtracted by +-+-+ 4 rows in set (0.00 sec).

two。 Now we define the sql mode schema as TRADITIONAL (traditional mode), that is, we strictly limit the data and do not allow inserts that violate the data requirements, as follows:

MariaDB [testdb] > SET @ @ session.sql_mode='TRADITIONAL'; # sets the current session to traditional mode Query OK, 0 rows affected (0.00 sec) MariaDB [testdb] > SELECT @ @ session.sql_mode +- -- + | @ @ session.sql_mode | +- - -+ | STRICT_TRANS_TABLES STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER NO_ENGINE_SUBSTITUTION | +- -- + 1 row in set (0.00 sec) MariaDB [testdb] > insert into tbl1 (name) values ('jerry') ('taotaoxiuxiu') ERROR 1406 (22001): Data too long for column 'name' at row 2 # insert error again, insertion is not allowed

DDL statement of SQL statement

1. Get help

Mysql > help KEYWORD

Mysql > help contents

Demo:

MariaDB [(none)] > help contentsYou asked for help about help category: "Contents" For more information, type 'help', where is one of the followingcategories: Account Management Administration Compound Statements Data Definition Data Manipulation Data Types Functions Functions and Modifiers for Use with GROUP BY Geographic Features Help Metadata Language Structure Plugins Procedures Table Maintenance Transactions User-Defined Functions UtilityMariaDB [(none)] > help Data Types # get data type You asked for help about help category: "Data Types" For more information, type 'help' Where is one of the followingtopics: AUTO_INCREMENT BIGINT BINARY BIT BLOB BLOB DATA TYPE BOOLEAN CHAR CHAR BYTE DATE DATETIME DEC DECIMAL DOUBLE DOUBLE PRECISION ENUM FLOAT INT INTEGER LONGBLOB LONGTEXT MEDIUMBLOB MEDIUMINT MEDIUMTEXT SET DATA TYPE SMALLINT TEXT TIME TIMESTAMP TINYBLOB TINYINT TINYTEXT VARBINARY VARCHAR YEAR DATA TYPEMariaDB [(none)] > help INTName: 'INT'Description:INT [(M)] [UNSIGNED] [ZEROFILL] A normal-size integer. The signed range is-2147483648 to 2147483647.The unsigned range is 0 to 4294967295.URL: http://dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html

two。 Database management

★ creates the database:

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name

Character character set. SHOW CHARACTER SET can view supported character sets.

★ modifies database

ALTER {DATABASE | SCHEMA} [db_name] CHARACTER SET [=] charset_name

★ deletes the database

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

★ related commands:

SHOW CHARACTER SET / / View character set

SHOW COLLATION / / View collation

SHOW CREATE DATABASE db_name / / View the statements used to create the database

Command demonstration:

MariaDB [(none)] > show create database mydb # View the statement +-+-+ when creating a database mydb | Database | Create Database | | +-+ | mydb | CREATE DATABASE `mydb` / *! 40100 DEFAULT CHARACTER SET latin1 * / | +-- | -+ 1 row in set (0.00 sec) MariaDB [(none)] > alter database mydb character set 'utf8' # modify character set Query OK, 1 row affected (0.00 sec) MariaDB [(none)] > show create database mydb # View Library creation +-+-- + | Database | Create Database | +- -- + | mydb | CREATE DATABASE `mydb` / *! 40100 DEFAULT CHARACTER SET utf8 * / | + -- + 1 row in set (0.00 sec)

3. Table management

1) Table creation

★ syntax:

CREATE TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options]

☉ create_definition: a comma-separated list

◆ field definition:

Column_name column_defination Field name + Field definition related Information

◆ constraint definition:

PRIMARY KEY (col1 [, col2,....])

UNIQUE KEY

FOREIGN KEY

CHECK (expr)

◆ index definition:

{INDEX | KEY} ordinary index creation

{FULLTEXT | SPATIAL} full-text index, spatial index

Note: column_definition:

Data_type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string']

☉ table_option:

ENGINE [=] engine_name storage engine

★ to view the types of storage engines supported by the database:

Mysql > SHOW ENGINES

★ looks at the storage engine of the specified table:

Mysql > SHOW TABLE STATUS LIKE clause

★ looks at the table structure definition:

DESC tbl_name

★ to view table status attribute information:

SHOW TABLE STATUS [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]

Demo:

1. Table creation:

[root@centos7] # mysql-p134296Welcome to the MariaDB monitor. Commands end with; or\ g.Your MariaDB connection id is 28Server version: 5.5.44-MariaDB MariaDB ServerCopyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.MariaDB [(none)] > SHOW DATABASES +-+ | Database | +-+ | information_schema | | mydb | | mysql | | performance_schema | | test | | ultrax | +-+ 6 rows in set (0.00 sec) MariaDB [(none)] > USE mydb Database changedMariaDB [mydb] > CREATE TABLE tbl1 (id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,name CHAR (30) NOT NULL,age TINYINT UNSIGNED,gender ENUM) DEFAULT 'KEY (name,gender), INDEX (name)); Query OK, 0 rows affected (0.04 sec) MariaDB [mydb] > DESC tbl1 +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | id | smallint (5) unsigned | NO | PRI | NULL | auto_increment | | name | char (30) | NO | MUL | NULL | | age | tinyint (3) unsigned | YES | | NULL | | | gender | enum ('F') | 'M') | YES | | M | | +-+-- + 4 rows in set (0.00 sec)

two。 View the storage engine type:

MariaDB [(none)] > show engines +- -- + | Engine | Support | Comment | Transactions | XA | Savepoints | +- -+ | InnoDB | DEFAULT | Percona-XtraDB Supports transactions, row-level locking And foreign keys | YES | | CSV | YES | CSV storage engine | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | | NO | NO | | BLACKHOLE | YES | / dev/null storage engine (anything you write to it disappears) | NO | | MEMORY | YES | Hash based | Stored in memory Useful for temporary tables | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | | ARCHIVE | YES | Archive storage engine | | NO | | MyISAM | YES | MyISAM storage engine | NO | | FEDERATED | YES | FederatedX pluggable storage engine | YES | NO | | | YES | | Aria | YES | Crash-safe tables with MyISAM heritage | NO | +- -+ 10 rows in set (0.00 sec)

3. View table status information:

MariaDB [mydb] > show table status\ gateway * 1. Row * * Name: tbl1 Engine: InnoDB Version: 10 Row_format: Compact Rows: 0 Avg_row_length: 0 Data_length: 16384Max_data_length : 0 Index_length: 32768 Data_free: 0 Auto_increment: 1 Create_time: 2016-10-16 17:54:32 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: # if there are multiple tables You can use where name or like to match the related table MariaDB [(none)] > use mysql Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedMariaDB [mysql] > show tables +-- + | Tables_in_mysql | +-+ | columns_priv | | db | | event | | func | | general_log | | help_category | | help_keyword | | help_relation | | help_topic | | host | | ndb_binlog_index | | plugin | | proc | | procs_priv | | proxies_priv | | servers | | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +-+ 24 rows in set ( Sec) MariaDB [mysql] > show table status like 'proc%'\ G # matches proc-related tables * * 1. Row * * Name: proc Engine: MyISAM Version: 10 Row_format: Dynamic Rows : 0 Avg_row_length: 0 Data_length: 292Max_data_length: 281474976710655 Index_length: 4096 Data_free: 292 Auto_increment: NULL Create_time: 2016-10-12 20:06:15 Update_time: 2016-10-12 20:06:15 Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: Stored Procedures* * 2. Row * * Name: procs_priv Engine: MyISAM Version: 10 Row_format: Fixed Rows: 0 Avg_row_length: 0 Data_length: 0Max_data_length: 239253730204057599 Index_length: 4096 Data_free: 0 Auto_increment: NULL Create_time: 2016-10-12 20:06:15 Update_time: 2016-10-12 20:06:15 Check_time: NULL Collation: utf8_bin Checksum: NULL Create_options: Comment: Procedure privileges2 rows in set (2016 sec)

- -

2) Table modification

★ syntax:

ALTER TABLE tbl_name [alter_specification [, alter_specification]...]

☉ alter_specification

◆ options

ENGINE=engine_name

...

◆ table definition

Field

ADD: increase

DRO: deleting

CHANGE: big change

MODIFY: small changes in local scope

Keys and indexes

ADD {PRIMARY | UNIQUE | FOREIGN} key (col1, col2,...)

ADD INDEX (col1, col2,...)

DROP {PRIMARY | UNIQUE | FOREIGN} KEY key_name

DROP INDEX index_name

★ views the index information on the table:

SHOW INDEXES FROM tbl_name

Command demonstration:

MariaDB [mydb] > use mydbMariaDB [mydb] > show index from tbl1 +- -+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +- -- + | tbl1 | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | tbl1 | 0 | name | 1 | name | A | 0 | NULL | NULL | | BTREE | | tbl1 | 0 | name | 2 | gender | A | 0 | NULL | NULL | YES | BTREE | | tbl1 | 1 | name_2 | 1 | name | A | 0 | NULL | NULL | | BTREE | | | +-- -+ 4 rows in set (0.00 sec) MariaDB [mydb] > alter table tbl1 drop index name_2 # delete index name_2Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0MariaDB [mydb] > show index from tbl1 +- -+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +- -- + | tbl1 | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | tbl1 | 0 | name | 1 | name | A | 0 | NULL | NULL | | BTREE | | tbl1 | 0 | name | 2 | gender | A | 0 | NULL | NULL | YES | BTREE | +-+- -+-+ 3 rows in set (0.00 sec) MariaDB [mydb] > desc tbl1 +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | id | smallint (5) unsigned | NO | PRI | NULL | auto_increment | | name | char (30) | NO | MUL | NULL | | age | tinyint (3) unsigned | YES | | NULL | | | gender | enum ('F') | 'M') | YES | | M | | +-+-+ 4 rows in set [mydb] > alter table tbl1 add ClassID TINYINT UNSIGNED NOT NULL # add a new field Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0MariaDB [mydb] > desc tbl1 +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | id | smallint (5) unsigned | NO | PRI | NULL | auto_increment | | name | char (30) | NO | MUL | NULL | | age | tinyint (3) unsigned | YES | | NULL | gender | enum ('F') | 'M') | YES | | M | | ClassID | tinyint (3) unsigned | NO | | NULL | | +-+-- -+ 5 rows in set (0.00 sec) # use modify local modification to put MariaDB [mydb] > alter table tbl1 modify ClassID TINYINT UNSIGNED NOT NULL after age after the line of age Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0MariaDB [mydb] > desc tbl1 +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | id | smallint (5) unsigned | NO | PRI | NULL | auto_increment | | name | char (30) | NO | MUL | NULL | | age | tinyint (3) unsigned | YES | | NULL | ClassID | tinyint (3) unsigned | NO | | NULL | | gender | enum ('F') | 'M') | YES | | M | | +-+-- + 5 rows in set (0.00 sec)

3) Table deletion and view table creation

★ table deletion

DROP TABLE [IF EXISTS] tbl_name [, tbl_name]...

You can delete multiple tables at a time

★ view table creation statement:

SHOW CREATE TABLE tbl_name

4. Index management

The role of ★ in introducing indexes:

The establishment of MySQL index is very important for the efficient operation of MySQL, and the index can greatly improve the retrieval speed of MySQL.

In fact, an index is also a table that holds the primary key and index fields and points to the records of the entity table.

★ index type:

Clustered index, nonclustered index: whether the index exists with the data

Primary key index, secondary index

Dense index, sparse index: whether each data item is indexed

BTREE (B+), HASH, R Tree, FULLTEXT

BTREE: left prefix

★ creation

☉ syntax:

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name ON tbl_name (index_col_name,...) Index field name

Index_col_name:

Col_name [(length)] [ASC | DESC]

{INDEX | KEY}: general index creation

{FULLTEXT | SPATIAL}: full-text index, spatial index

★ deletion:

DROP INDEX index_name ON tbl_name

★ View:

SHOW {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name [{FROM | IN} db_name] [WHERE expr]

★ uses the ALTER command to add and delete indexes

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):

This statement adds a primary key, which means that the index value must be unique and cannot be NULL.

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list):

The value that this statement creates the index must be unique (NULL may appear multiple times except for NULL).

ALTER TABLE tbl_name ADD INDEX index_name (column_list):

Add a normal index, and the index value can appear multiple times.

ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):

This statement specifies that the index is FULLTEXT for full-text indexing.

Command demonstration:

MariaDB [mydb] > show index from tbl1 # View Index +- -+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +- -+ | tbl1 | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | tbl1 | 0 | name | 1 | name | A | 0 | NULL | NULL | | BTREE | | | tbl1 | 0 | name | 2 | gender | A | 0 | NULL | NULL | YES | BTREE | +-| -+ 3 rows in set (0.03 sec) MariaDB [mydb] > drop index name on tbl1 # delete index Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0MariaDB [mydb] > show index from tbl1 +- -+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +- -- + | tbl1 | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | +-+- -+-+ 1 row in set (0.00 sec) MariaDB [mydb] > create index name_and_gender on tbl1 (name (5) Gender) # create index Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0MariaDB [mydb] > show index from tbl1 # View the following +- -+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +- -+- -+ | tbl1 | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | tbl1 | 1 | name_and_gender | 1 | name | A | 0 | | 5 | NULL | | BTREE | | tbl1 | 1 | name_and_gender | 2 | gender | A | 0 | NULL | NULL | YES | BTREE | +- -+- -+ 3 rows in set (0.00 sec) MariaDB [mydb] > show index from tbl1 where Key_name like 'name%' # View the specified index + -- + | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-- -+ -+ | tbl1 | 1 | name_and_gender | 1 | name | A | 0 | 5 | NULL | | BTREE | tbl1 | 1 | name_and_gender | 2 | gender | A | 0 | | NULL | NULL | YES | BTREE | +-| +-+-+ 2 rows in set (0.04 sec)

5.VIEW view

★ virtual table: stored SELECT statements

☉ creation:

CREATE VIEW view_name [(column_list)] AS select_statement

☉ modification:

ALTER VIEW view_name [(column_list)] AS select_statement

☉ deletion:

DROP VIEW [IF EXISTS] view_name [, view_name]...

Demo:

MariaDB [testdb] > create table tbl2 (id INT UNSIGNED,name VARCHAR (50), age TINYINT UNSIGNED); Query OK, 0 rows affected (0.04 sec) MariaDB [testdb] > insert into tbl2 VALUES (1 Query OK, 0 Warnings: 0MariaDB [testdb] > select * from tbl2 +-+ | id | name | age | 1 | tom | 21 | 2 | tao | 15 | 3 | jing | 22 | +-+ 3 rows in set (0.00 sec) MariaDB [testdb] > CREATE VIEW testview AS SELECT id,name FROM tbl2 # create VIEWQuery OK, 0 rows affected (0.02 sec) MariaDB [testdb] > SHOW TABLES; # check and find that view is also used as a table +-+ | Tables_in_testdb | +-+ | tbl1 | | tbl2 | | testview | +-+ 3 rows in set (0.00 sec) MariaDB [testdb] > DESC testview # but the data only has id and name segments Different from the original table tbl2, +-+ | Field | Type | Null | Key | Default | Extra | +-- -- + | id | int (10) unsigned | YES | | NULL | | name | varchar (50) | YES | | NULL | | +-+- -+ 2 rows in set (0.01sec) MariaDB [testdb] > select * from testview +-+-+ | id | name | +-+-+ | 1 | tom | | 2 | tao | | 3 | jing | +-+-+ 3 rows in set (0.00 sec) MariaDB [testdb] > show table status\ G # View the type of table You can see that the third table is of view type * * 1. Row * * Name: tbl1 Engine: InnoDB Version: 10 Row_format: Compact Rows: 0 Avg_row_length: 0 Data_length: 16384Max _ Data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 2016-11-24 15:41:24 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: * * 2. Row * * Name: tbl2 Engine: InnoDB Version: 10 Row_format: Compact Rows: 3 Avg_row_length: 5461 Data_length: 16384Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 2016-11-24 16:43:04 Update_time: NULL Check_time: NULL Collation: latin1 _ swedish_ci Checksum: NULL Create_options: Comment: * * 3. Row * * Name: testview Engine: NULL Version: NULL Row_format: NULL Rows: NULL Avg_row_length: NULL Data_length: NULLMax_data_length: NULL Index_length: NULL Data_free: NULL Auto_increment: NULL Create_time: NULL Update_time: NULL Check_time: NULL Collation: NULL Checksum: NULL Create_options: NULL Comment: VIEW3 rows in set (0.00 sec) MariaDB [testdb] > DROP VIEW testview # Delete viewQuery OK, 0 rows affected (0.00 sec) MariaDB [testdb] > show tables;+-+ | Tables_in_testdb | +-+ | tbl1 | | tbl2 | +-+ 2 rows in set (0.00 sec)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.