系统运维-23-1-MariaDB的SQL语句基础

最新推荐文章于 2021-07-17 10:27:40 发布

ligan1115

最新推荐文章于 2021-07-17 10:27:40 发布

阅读量337

点赞数

分类专栏： DEVOPS 文章标签： sql mariadb mysql database table

本文链接：https://blog.csdn.net/ligan1115/article/details/89928380

版权

DEVOPS 专栏收录该内容

42 篇文章 0 订阅

订阅专栏

数据基础应用

MariaDB [testdb]> CREATE TABLE students (id int UNSIGNED NOT NULL PRIMARY KEY, name VARCHAR(20) NOT NULL, age tinyint UNSIGNED);
Query OK, 0 rows affected (0.00 sec)

获取帮助

   MariaDB [testdb]> HELP CREATE TABLE;
   Name: 'CREATE TABLE'
   Description:
   Syntax:
   CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
   (create_definition,...)
   [table_options]
   [partition_options]

Or:

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

Or:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }

   create_definition:
   col_name column_definition
   | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
   [index_option] ...
   | {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
   [index_option] ...
   | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
   [index_name] [index_type] (index_col_name,...)
   [index_option] ...
   | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...)
   [index_option] ...
   | [CONSTRAINT [symbol]] FOREIGN KEY
   [index_name] (index_col_name,...) reference_definition
   | CHECK (expr)

   column_definition:
   data_type [NOT NULL | NULL] [DEFAULT default_value]
   [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
   [COMMENT 'string']
   [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
   [STORAGE {DISK|MEMORY|DEFAULT}]
   [reference_definition]

index_col_name:
col_name [(length)] [ASC | DESC]

index_type:
USING {BTREE | HASH}

   index_option:
   KEY_BLOCK_SIZE [=] value
   | index_type
   | WITH PARSER parser_name
   | COMMENT 'string'

   reference_definition:
   REFERENCES tbl_name (index_col_name,...)
   [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
   [ON DELETE reference_option]
   [ON UPDATE reference_option]

reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION

table_options:
table_option [[,] table_option] ...

   table_option:
   ENGINE [=] engine_name
   | AUTO_INCREMENT [=] value
   | AVG_ROW_LENGTH [=] value
   | [DEFAULT] CHARACTER SET [=] charset_name
   | CHECKSUM [=] {0 | 1}
   | [DEFAULT] COLLATE [=] collation_name
   | COMMENT [=] 'string'
   | CONNECTION [=] 'connect_string'
   | DATA DIRECTORY [=] 'absolute path to directory'
   | DELAY_KEY_WRITE [=] {0 | 1}
   | INDEX DIRECTORY [=] 'absolute path to directory'
   | INSERT_METHOD [=] { NO | FIRST | LAST }
   | KEY_BLOCK_SIZE [=] value
   | MAX_ROWS [=] value
   | MIN_ROWS [=] value
   | PACK_KEYS [=] {0 | 1 | DEFAULT}
   | PASSWORD [=] 'string'
   | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
   | TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]
   | UNION [=] (tbl_name[,tbl_name]...)

   partition_options:
   PARTITION BY
   { [LINEAR] HASH(expr)
   | [LINEAR] KEY(column_list)
   | RANGE{(expr) | COLUMNS(column_list)}
   | LIST{(expr) | COLUMNS(column_list)} }
   [PARTITIONS num]
   [SUBPARTITION BY
   { [LINEAR] HASH(expr)
   | [LINEAR] KEY(column_list) }
   [SUBPARTITIONS num]
   ]
   [(partition_definition [, partition_definition] ...)]

   partition_definition:
   PARTITION partition_name
   [VALUES
   {LESS THAN {(expr | value_list) | MAXVALUE}
   |
   IN (value_list)}]
   [[STORAGE] ENGINE [=] engine_name]
   [COMMENT [=] 'comment_text' ]
   [DATA DIRECTORY [=] 'data_dir']
   [INDEX DIRECTORY [=] 'index_dir']
   [MAX_ROWS [=] max_number_of_rows]
   [MIN_ROWS [=] min_number_of_rows]
   [TABLESPACE [=] tablespace_name]
   [NODEGROUP [=] node_group_id]
   [(subpartition_definition [, subpartition_definition] ...)]

   subpartition_definition:
   SUBPARTITION logical_name
   [[STORAGE] ENGINE [=] engine_name]
   [COMMENT [=] 'comment_text' ]
   [DATA DIRECTORY [=] 'data_dir']
   [INDEX DIRECTORY [=] 'index_dir']
   [MAX_ROWS [=] max_number_of_rows]
   [MIN_ROWS [=] min_number_of_rows]
   [TABLESPACE [=] tablespace_name]
   [NODEGROUP [=] node_group_id]

select_statement:
[IGNORE | REPLACE] [AS] SELECT ... (Some valid select statement)

CREATE TABLE creates a table with the given name. You must have the
CREATE privilege for the table.

   Rules for permissible table names are given in
   http://dev.mysql.com/doc/refman/5.5/en/identifiers.html. By default,
   the table is created in the default database, using the InnoDB storage
   engine. An error occurs if the table exists, if there is no default
   database, or if the database does not exist.

URL: http://dev.mysql.com/doc/refman/5.5/en/create-table.html

查看创建表时使用的命令

   MariaDB [testdb]> SHOW CREATE TABLE students;
   +----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Table | Create Table |
   +----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | students | CREATE TABLE `students` (
   `id` int(10) unsigned NOT NULL,
   `name` varchar(20) NOT NULL,
   `age` tinyint(3) unsigned DEFAULT NULL,
   PRIMARY KEY (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
   +----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   1 row in set (0.00 sec)

查看表的状态

   MariaDB [testdb]> SHOW TABLE STATUS LIKE 'students'\G;
   *************************** 1. row ***************************
   Name: students
   Engine: InnoDB
   Version: 10
   Row_format: Compact
   Rows: 0
   Avg_row_length: 0
   Data_length: 16384
   Max_data_length: 0
   Index_length: 0
   Data_free: 6291456
   Auto_increment: NULL
   Create_time: 2019-05-07 09:13:17
   Update_time: NULL
   Check_time: NULL
   Collation: latin1_swedish_ci
   Checksum: NULL
   Create_options:
   Comment:
   1 row in set (0.00 sec)

查看修改帮助
   字段：
       添加字段 add
       删除字段 drop
       修改字段 alter change modify
   索引：
       添加索引 add
       删除索引 drop
   表选项：
       修改

   MariaDB [testdb]> HELP ALTER TABLE;
   Name: 'ALTER TABLE'
   Description:
   Syntax:
   ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
   [alter_specification [, alter_specification] ...]
   [partition_options]

   alter_specification:
   table_options
   | ADD [COLUMN] col_name column_definition
   [FIRST | AFTER col_name ]
   | ADD [COLUMN] (col_name column_definition,...)
   | ADD {INDEX|KEY} [index_name]
   [index_type] (index_col_name,...) [index_option] ...
   | ADD [CONSTRAINT [symbol]] PRIMARY KEY
   [index_type] (index_col_name,...) [index_option] ...
   | ADD [CONSTRAINT [symbol]]
   UNIQUE [INDEX|KEY] [index_name]
   [index_type] (index_col_name,...) [index_option] ...
   | ADD FULLTEXT [INDEX|KEY] [index_name]
   (index_col_name,...) [index_option] ...
   | ADD SPATIAL [INDEX|KEY] [index_name]
   (index_col_name,...) [index_option] ...
   | ADD [CONSTRAINT [symbol]]
   FOREIGN KEY [index_name] (index_col_name,...)
   reference_definition
   | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
   | CHANGE [COLUMN] old_col_name new_col_name column_definition
   [FIRST|AFTER col_name]
   | MODIFY [COLUMN] col_name column_definition
   [FIRST | AFTER col_name]
   | DROP [COLUMN] col_name
   | DROP PRIMARY KEY
   | DROP {INDEX|KEY} index_name
   | DROP FOREIGN KEY fk_symbol
   | MAX_ROWS = rows
   | DISABLE KEYS
   | ENABLE KEYS
   | RENAME [TO|AS] new_tbl_name
   | ORDER BY col_name [, col_name] ...
   | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
   | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
   | DISCARD TABLESPACE
   | IMPORT TABLESPACE
   | FORCE
   | ADD PARTITION (partition_definition)
   | DROP PARTITION partition_names
   | TRUNCATE PARTITION {partition_names | ALL}
   | COALESCE PARTITION number
   | REORGANIZE PARTITION [partition_names INTO (partition_definitions)]
   | ANALYZE PARTITION {partition_names | ALL}
   | CHECK PARTITION {partition_names | ALL}
   | OPTIMIZE PARTITION {partition_names | ALL}
   | REBUILD PARTITION {partition_names | ALL}
   | REPAIR PARTITION {partition_names | ALL}
   | PARTITION BY partitioning_expression
   | REMOVE PARTITIONING

index_col_name:
col_name [(length)] [ASC | DESC]

index_type:
USING {BTREE | HASH}

   index_option:
   KEY_BLOCK_SIZE [=] value
   | index_type
   | WITH PARSER parser_name
   | COMMENT 'string'

table_options:
table_option [[,] table_option] ... (see CREATE TABLE options)

partition_options:
(see CREATE TABLE options)

   ALTER TABLE changes the structure of a table. For example, you can add
   or delete columns, create or destroy indexes, change the type of
   existing columns, or rename columns or the table itself. You can also
   change characteristics such as the storage engine used for the table or
   the table comment.

   Partitioning-related clauses for ALTER TABLE can be used with
   partitioned tables for repartitioning, for adding, dropping, merging,
   and splitting partitions, and for performing partitioning maintenance.
   For more information, see
   http://dev.mysql.com/doc/refman/5.5/en/alter-table-partition-operations
   .html.

Following the table name, specify the alterations to be made. If none
are given, ALTER TABLE does nothing.

URL: http://dev.mysql.com/doc/refman/5.5/en/alter-table.html

修改具体使用实例：
   MariaDB [testdb]> ALTER TABLE students ADD gender ENUM('m','f');
   Query OK, 0 rows affected (0.01 sec)
   Records: 0 Duplicates: 0 Warnings: 0

   MariaDB [testdb]> ALTER TABLE students CHANGE id sid int UNSIGNED NOT NULL;
   Query OK, 0 rows affected (0.00 sec)
   Records: 0 Duplicates: 0 Warnings: 0

删除的实例：
   MariaDB [testdb]> ALTER TABLE students DROP age;
   Query OK, 0 rows affected (0.00 sec)
   Records: 0 Duplicates: 0 Warnings: 0

索引
   索引是一种特殊的数据结构，定义在查找时作为查找条件的字段
   索引可以有索引名称
   一般不会修改索引，主要是添加和删除，不用的索引一般删除，否则会影响性能
   多个索引一般要同时使用

   MariaDB [testdb]> HELP CREATE INDEX;
   Name: 'CREATE INDEX'
   Description:
   Syntax:
   CREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
   [index_type]
   ON tbl_name (index_col_name,...)
   [index_option] ...

index_col_name:
col_name [(length)] [ASC | DESC]

index_type:
USING {BTREE | HASH}

   index_option:
   KEY_BLOCK_SIZE [=] value
   | index_type
   | WITH PARSER parser_name
   | COMMENT 'string'

   CREATE INDEX is mapped to an ALTER TABLE statement to create indexes.
   See [HELP ALTER TABLE]. CREATE INDEX cannot be used to create a PRIMARY
   KEY; use ALTER TABLE instead. For more information about indexes, see
   http://dev.mysql.com/doc/refman/5.5/en/mysql-indexes.html.

URL: http://dev.mysql.com/doc/refman/5.5/en/create-index.html

删除索引实例：

   MariaDB [testdb]> DROP INDEX name ON students;
   Query OK, 0 rows affected (0.00 sec)
   Records: 0 Duplicates: 0 Warnings: 0

DML命令
INSERT DELETE SELECT UPDATE

   MariaDB [testdb]> HELP INSERT;
   Name: 'INSERT'
   Description:
   Syntax:
   INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
   [INTO] tbl_name [(col_name,...)]
   {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
   [ ON DUPLICATE KEY UPDATE
   col_name=expr
   [, col_name=expr] ... ]

Or:

   INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
   [INTO] tbl_name
   SET col_name={expr | DEFAULT}, ...
   [ ON DUPLICATE KEY UPDATE
   col_name=expr
   [, col_name=expr] ... ]

Or:

   INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
   [INTO] tbl_name [(col_name,...)]
   SELECT ...
   [ ON DUPLICATE KEY UPDATE
   col_name=expr
   [, col_name=expr] ... ]

   INSERT inserts new rows into an existing table. The INSERT ... VALUES
   and INSERT ... SET forms of the statement insert rows based on
   explicitly specified values. The INSERT ... SELECT form inserts rows
   selected from another table or tables. INSERT ... SELECT is discussed
   further in [HELP INSERT SELECT].

URL: http://dev.mysql.com/doc/refman/5.5/en/insert.html

插入语句实例

   MariaDB [testdb]> INSERT INTO students VALUES (1,'Tom','m'),(2,'Jerry','f');
   Query OK, 2 rows affected (0.01 sec)
   Records: 2 Duplicates: 0 Warnings: 0

   MariaDB [testdb]> SELECT * FROM students;
   +-----+-------+--------+
   | sid | name | gender |
   +-----+-------+--------+
   | 1 | Tom | m |
   | 2 | Jerry | f |
   +-----+-------+--------+
   2 rows in set (0.00 sec)

   MariaDB [testdb]> INSERT INTO students (sid,name) VALUES (3,'Andy'),(4,'Jason');
   Query OK, 2 rows affected (0.00 sec)
   Records: 2 Duplicates: 0 Warnings: 0

   MariaDB [testdb]> SELECT * FROM students;
   +-----+-------+--------+
   | sid | name | gender |
   +-----+-------+--------+
   | 1 | Tom | m |
   | 2 | Jerry | f |
   | 3 | Andy | NULL |
   | 4 | Jason | NULL |
   +-----+-------+--------+
   4 rows in set (0.00 sec)

查询的用法
   字段表示法
       *：所有字段
       as：字段别名

   MariaDB [testdb]> HELP SELECT;
   Name: 'SELECT'
   Description:
   Syntax:
   SELECT
   [ALL | DISTINCT | DISTINCTROW ]
   [HIGH_PRIORITY]
   [STRAIGHT_JOIN]
   [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
   [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
   select_expr [, select_expr ...]
   [FROM table_references
   [WHERE where_condition]
   [GROUP BY {col_name | expr | position}
   [ASC | DESC], ... [WITH ROLLUP]]
   [HAVING where_condition]
   [ORDER BY {col_name | expr | position}
   [ASC | DESC], ...]
   [LIMIT {[offset,] row_count | row_count OFFSET offset}]
   [PROCEDURE procedure_name(argument_list)]
   [INTO OUTFILE 'file_name'
   [CHARACTER SET charset_name]
   export_options
   | INTO DUMPFILE 'file_name'
   | INTO var_name [, var_name]]
   [FOR UPDATE | LOCK IN SHARE MODE]]

   SELECT is used to retrieve rows selected from one or more tables, and
   can include UNION statements and subqueries. See [HELP UNION], and
   http://dev.mysql.com/doc/refman/5.5/en/subqueries.html.

The most commonly used clauses of SELECT statements are these:

o Each select_expr indicates a column that you want to retrieve. There
must be at least one select_expr.

o table_references indicates the table or tables from which to retrieve
rows. Its syntax is described in [HELP JOIN].

   o The WHERE clause, if given, indicates the condition or conditions
   that rows must satisfy to be selected. where_condition is an
   expression that evaluates to true for each row to be selected. The
   statement selects all rows if there is no WHERE clause.

   In the WHERE expression, you can use any of the functions and
   operators that MySQL supports, except for aggregate (summary)
   functions. See
   http://dev.mysql.com/doc/refman/5.5/en/expressions.html, and
   http://dev.mysql.com/doc/refman/5.5/en/functions.html.

SELECT can also be used to retrieve rows computed without reference to
any table.

URL: http://dev.mysql.com/doc/refman/5.5/en/select.html

查找实例：
   条件逻辑
       and
       or
       not
   BETWEEN ... AND ...
   LIKE 模糊匹配，字符串比较
       %：任意长度的任意字符
       _：任意单个字符
   RLIKE 基于正则表达式匹配，字符串比较

   MariaDB [testdb]> SELECT * FROM students WHERE sid<3;
   +-----+-------+--------+
   | sid | name | gender |
   +-----+-------+--------+
   | 1 | Tom | m |
   | 2 | Jerry | f |
   +-----+-------+--------+
   2 rows in set (0.00 sec)

   MariaDB [testdb]> SELECT * FROM students WHERE gender='m';
   +-----+------+--------+
   | sid | name | gender |
   +-----+------+--------+
   | 1 | Tom | m |
   +-----+------+--------+
   1 row in set (0.00 sec)

MariaDB [testdb]> SELECT * FROM students WHERE gender='';
Empty set (0.00 sec)

   MariaDB [testdb]> SELECT * FROM students WHERE gender IS NOT NULL;
   +-----+-------+--------+
   | sid | name | gender |
   +-----+-------+--------+
   | 1 | Tom | m |
   | 2 | Jerry | f |
   +-----+-------+--------+
   2 rows in set (0.00 sec)

   MariaDB [testdb]> SELECT * FROM students ORDER BY name;
   +-----+-------+--------+
   | sid | name | gender |
   +-----+-------+--------+
   | 3 | Andy | NULL |
   | 4 | Jason | NULL |
   | 2 | Jerry | f |
   | 1 | Tom | m |
   +-----+-------+--------+
   4 rows in set (0.00 sec)

   MariaDB [testdb]> SELECT * FROM students ORDER BY name DESC;
   +-----+-------+--------+
   | sid | name | gender |
   +-----+-------+--------+
   | 1 | Tom | m |
   | 2 | Jerry | f |
   | 4 | Jason | NULL |
   | 3 | Andy | NULL |
   +-----+-------+--------+
   4 rows in set (0.01 sec)

   MariaDB [testdb]> SELECT * FROM students ORDER BY name DESC LIMIT 2;
   +-----+-------+--------+
   | sid | name | gender |
   +-----+-------+--------+
   | 1 | Tom | m |
   | 2 | Jerry | f |
   +-----+-------+--------+
   2 rows in set (0.00 sec)

   MariaDB [testdb]> SELECT * FROM students WHERE name LIKE 'J%';
   +-----+-------+--------+
   | sid | name | gender |
   +-----+-------+--------+
   | 2 | Jerry | f |
   | 4 | Jason | NULL |
   +-----+-------+--------+
   2 rows in set (0.00 sec)

   MariaDB [testdb]> SELECT * FROM students WHERE name RLIKE '.*e.*';
   +-----+-------+--------+
   | sid | name | gender |
   +-----+-------+--------+
   | 2 | Jerry | f |
   +-----+-------+--------+
   1 row in set (0.00 sec)

使用别名实例

   MariaDB [testdb]> SELECT sid as stuid, name as stuname FROM students;
   +-------+---------+
   | stuid | stuname |
   +-------+---------+
   | 1 | Tom |
   | 2 | Jerry |
   | 3 | Andy |
   | 4 | Jason |
   +-------+---------+
   4 rows in set (0.00 sec)

删除数据实例：

MariaDB [testdb]> DELETE FROM students WHERE sid=3;
Query OK, 1 row affected (0.00 sec)

   MariaDB [testdb]> SELECT * FROM students;
   +-----+-------+--------+
   | sid | name | gender |
   +-----+-------+--------+
   | 1 | Tom | m |
   | 2 | Jerry | f |
   | 4 | Jason | NULL |
   +-----+-------+--------+
   3 rows in set (0.00 sec)

更新数据实例：

   MariaDB [testdb]> UPDATE students SET gender='f' WHERE sid=4;
   Query OK, 1 row affected (0.01 sec)
   Rows matched: 1 Changed: 1 Warnings: 0

   MariaDB [testdb]> SELECT * FROM students;
   +-----+-------+--------+
   | sid | name | gender |
   +-----+-------+--------+
   | 1 | Tom | m |
   | 2 | Jerry | f |
   | 4 | Jason | f |
   +-----+-------+--------+
   3 rows in set (0.00 sec)

ligan1115

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
系统运维-23-1-MariaDB的SQL语句基础

数据基础应用 MariaDB [testdb]> CREATE TABLE students (id int UNSIGNED NOT NULL PRIMARY KEY, name VARCHAR(20) NOT NULL, age tinyint UNSIGNED); Query OK, 0 rows affected (0.00 sec) MariaDB [...
复制链接

扫一扫