Mariadb/MySQL数据库单表查询基本操作及DML语句
作者：尹正杰
版权声明：原创作品，谢绝转载！否则将追究法律责任。
一数据库及表相关概述
1>.数据库操作
创建数据库：
CREATE DATABASE|SCHEMA [IF NOT EXISTS] 'DB_NAME';
CHARACTER SET 'character set name’COLLATE 'collate name' 修改数据库：
ALTER DATABASE DB_NAME character set utf8; 删除数据库
DROP DATABASE|SCHEMA [IF EXISTS] 'DB_NAME'; 查看支持所有字符集：
SHOW CHARACTER SET; 查看支持所有排序规则：
SHOW COLLATION; 获取命令使用帮助：
HELP KEYWORD; 查看数据库列表：
SHOW DATABASES;
2>.创建表
创建表：CREATE TABLE
() 直接创建
CREATE TABLE [IF NOT EXISTS] ‘tbl_name’ (col1 type1 修饰符, col2 type2 修饰符, ...)
() 通过查询现存表创建；新表会被直接插入查询而来的数据
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [partition_options] select_statement
() 通过复制现存的表的表结构创建，但不复制数据
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name) }
注意：
Storage Engine是指表类型，也即在表创建时指明其使用的存储引擎，同一库中不同表可以使用不同的存储引擎
同一个库中表建议要使用同一种存储引擎类型
字段信息
col type1
PRIMARY KEY(col1,...)
INDEX(col1, ...)
UNIQUE KEY(col1, ...) 表选项：
ENGINE [=] engine_name
SHOW ENGINES;查看支持的engine类型
ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT} 获取帮助：
mysql> HELP CREATE TABLE;
3>.表操作
表：
二维关系 设计表：
遵循规范 定义：字段，索引
字段：字段名，字段数据类型，修饰符
约束，索引：应该创建在经常用作查询条件的字段上 查看所有的引擎：
SHOW ENGINES 查看表：
SHOW TABLES [FROM db_name] 查看表结构：
DESC [db_name.]tb_name
SHOW COLUMNS FROM [db_name.]tb_name 删除表：
DROP TABLE [IF EXISTS] tb_name ALTER TABLE 'tbl_name'
字段：
添加字段：add
ADD col1 data_type [FIRST|AFTER col_name]
删除字段：drop
修改字段：
alter（默认值）, change（字段名）, modify（字段属性）
索引:
添加索引：add index
删除索引：drop index
表选项
修改: 查看表上的索引：
SHOW INDEXES FROM [db_name.]tbl_name; 查看帮助：
Help ALTER TABLE
查看表创建命令：
SHOW CREATE TABLE tbl_name 查看表状态：
SHOW TABLE STATUS LIKE 'tbl_name’ 查看库中所有表状态：
SHOW TABLE STATUS FROM db_name
二.数据类型
数据类型：
数据长什么样
数据需要多少空间来存放
系统内置数据类型和用户定义数据类型
MySql支持多种列类型：
数值类型
日期/时间类型
字符串(字符)类型
更多数据类型说明参考:https://dev.mysql.com/doc/refman/5.5/en/data-types.html 选择正确的数据类型对于获得高性能至关重要，三大原则：
更小的通常更好，尽量使用可正确存储数据的最小数据类型
简单就好，简单数据类型的操作通常需要更少的CPU周期
尽量避免NULL，包含为NULL的列，对MySQL更难优化
1>.整型
tinyint(m)
1个字节 范围(-~) smallint(m)
2个字节 范围(-~) mediumint(m)
3个字节 范围(-~) int(m)
4个字节 范围(-~)
int(m)里的m是表示SELECT查询结果集中的显示宽度，并不影响实际的取值范围，规定了MySQL的一些交互工具（例如MySQL命令行客户端）用来显示字符的个数。对于存储和计算来说，Int()和Int()是相同的 bigint(m)
8个字节 范围(+-9.22*10的18次方) 加了unsigned(最高位不为符号位)，则最大值翻倍，
如：tinyint unsigned的取值范围为(~)
2>.布尔型
BOOL，BOOLEAN：布尔型，
是TINYINT()的同义词。zero值被视为假，非zero值视为真
3>.浮点型(float和double)，近似值
float(m,d)
单精度浮点型 8位精度(4字节) m总个数，d小数位 double(m,d)
双精度浮点型16位精度(8字节) m总个数，d小数位 设一个字段定义为float(,)，如果插入一个数123.,实际数据库里存的是123.，但总个数还以实际为准，即6位
4>.定点数
在数据库中存放的是精确值,存为十进制 decimal(m,d) 参数m< 是总个数，d<30且 d<m 是小数位
MySQL5.0和更高版本将数字打包保存到一个二进制字符串中（每4个字节存9个数字）。例如，decimal(,)小数点两边将各存储9个数字，一共使用9个字节：小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身占1个字节
浮点类型在存储同样范围的值时，通常比decimal使用更少的空间。float使用4个字节存储。double占用8个字节
因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用decimal——例如存储财务数据。但在数据量比较大的时候，可以考虑使用bigint代替decimal
5>.字符串
char(n)
固定长度，最多255个字符 varchar(n)
可变长度，最多65535个字符 tinytext
可变长度，最多255个字符 text
可变长度，最多65535个字符 mediumtext
可变长度，最多2的24次方-1个字符 longtext
可变长度，最多2的32次方-1个字符 BINARY(M)
固定长度，可存二进制或字符，长度为0-M字节 VARBINARY(M)
可变长度，可存二进制或字符，允许长度为0-M字节 char和varchar：
1.char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉，所以char类型存储的字符串末尾不能有空格，varchar不限于此
2.char(n) 固定长度，char()不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n< n>)，所以varchar(),存入3个字符将占用4个字节
3.char类型的字符串检索速度要比varchar类型的快 varchar和text：
1.varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n< n>)，text是实际字符数+2个字节。
2.text类型不能有默认值
3.varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text
6>.内建类型
ENUM枚举, SET集合
7>.二进制数据：BLOB
BLOB和text存储方式不同，TEXT以文本方式存储，英文存储区分大小写，而Blob是以二进制方式存储，不分大小写
BLOB存储的数据只能整体读出
TEXT可以指定字符集，BLOB不用指定字符集
8>.日期时间类型
date
日期 '2008-12-2'
time
时间 '12:25:36'
datetime
日期时间 '2008-12-2 22:06:44'
timestamp
自动存储记录修改时间
YEAR(), YEAR()：
年份
timestamp
字段里的时间数据会随其他字段修改的时候自动刷新，这个数据类型的字段可以存放这条记录最后被修改的时间
9>.修饰符
所有类型：
NULL
数据列可包含NULL值
NOT NULL
数据列不允许包含NULL值
DEFAULT
默认值
PRIMARY KEY
主键
UNIQUE KEY
唯一键
CHARACTER SET name
指定一个字符集 数值型
AUTO_INCREMENT
自动递增，适用于整数类型
UNSIGNED
无符号
三.增
MariaDB [(none)]> help CREATE #查看CREATE命令的帮助信息
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
CREATE DATABASE
CREATE EVENT
CREATE FUNCTION
CREATE FUNCTION UDF
CREATE INDEX
CREATE PROCEDURE
CREATE SERVER
CREATE TABLE
CREATE TABLESPACE
CREATE TRIGGER
CREATE USER
CREATE VIEW
SHOW
SHOW CREATE DATABASE
SHOW CREATE EVENT
SHOW CREATE FUNCTION
SHOW CREATE PROCEDURE
SHOW CREATE TABLE
SPATIAL MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> help CREATE #查看CREATE命令的帮助信息
MariaDB [(none)]> HELP CREATE DATABASE #查看创建数据库的命令
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_specification] ... create_specification:
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name CREATE DATABASE creates a database with the given name. To use this
statement, you need the CREATE privilege for the database. CREATE
SCHEMA is a synonym for CREATE DATABASE. URL: https://mariadb.com/kb/en/create-database/ MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> HELP CREATE DATABASE #查看创建数据库的命令帮助信息
MariaDB [(none)]> 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] data_type:
BIT[(length)]
| TINYINT[(length)] [UNSIGNED] [ZEROFILL]
| SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
| MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
| INT[(length)] [UNSIGNED] [ZEROFILL]
| INTEGER[(length)] [UNSIGNED] [ZEROFILL]
| BIGINT[(length)] [UNSIGNED] [ZEROFILL]
| REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
| DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
| FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
| DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL]
| NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL]
| DATE
| TIME
| TIMESTAMP
| DATETIME
| YEAR
| CHAR[(length)]
[CHARACTER SET charset_name] [COLLATE collation_name]
| VARCHAR(length)
[CHARACTER SET charset_name] [COLLATE collation_name]
| BINARY[(length)]
| VARBINARY(length)
| TINYBLOB
| BLOB
| MEDIUMBLOB
| LONGBLOB
| TINYTEXT [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| TEXT [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| MEDIUMTEXT [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| LONGTEXT [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| ENUM(value1,value2,value3,...)
[CHARACTER SET charset_name] [COLLATE collation_name]
| SET(value1,value2,value3,...)
[CHARACTER SET charset_name] [COLLATE collation_name]
| spatial_type 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
https://mariadb.com/kb/en/identifier-names/. 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: https://mariadb.com/kb/en/create-table/ MariaDB [(none)]>
MariaDB [(none)]> HELP CREATE TABLE #查看创建表的命令帮助信息
MariaDB [yinzhengjie]> 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: https://mariadb.com/kb/en/insert/ MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> HELP INSERT #查看往表中插入数据的命令帮助信息
1>.创建一个名称为"yinzhengjie"的数据库
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
rows in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> CREATE DATABASE yinzhengjie; #创建一个名称为"yinzhengjie"的数据库
Query OK, row affected (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| yinzhengjie |
+--------------------+
rows in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS yinzhengjie; #创建数据库时若不存在同名数据库则创建，已存在则不创建。
Query OK, rows affected, warning (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| yinzhengjie |
+--------------------+
rows in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS yinzhengjie; #创建数据库时若不存在同名数据库则创建，已存在则不创建。
[[email protected] ~]# cat /mysql//data/yinzhengjie/db.opt #创建数据库成功后，在数据库对应的系统目录会自动生成该文件
default-character-set=latin1 #默认的字符集
default-collation=latin1_swedish_ci #默认的字符集排序规则
[[email protected] ~]#
[[email protected] ~]# cat /mysql/3306/data/yinzhengjie/db.opt #创建数据库成功后，在数据库对应的系统目录会自动生成该文件
2>.创建数据库时指定字符集
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| yinzhengjie |
+--------------------+
rows in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> CREATE SCHEMA IF NOT EXISTS yinzhengjie2019 DEFAULT CHARACTER SET = utf8mb4; #utf8mb4支持比utf8更多的字符，包括表情包之类的数据。
Query OK, 1 row affected (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| yinzhengjie |
| yinzhengjie2019 |
+--------------------+
rows in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> CREATE SCHEMA IF NOT EXISTS yinzhengjie2019 DEFAULT CHARACTER SET = utf8mb4; #utf8mb4支持比utf8更多的字符。
[[email protected] ~]# cat /mysql//data/yinzhengjie2019/db.opt
default-character-set=utf8mb4
default-collation=utf8mb4_general_ci
[[email protected] ~]#
[[email protected] ~]# cat /mysql/3306/data/yinzhengjie2019/db.opt
3>.在"yinzhengjie"数据库中创建一张"students"表
MariaDB [(none)]> use yinzhengjie
Database changed
MariaDB [yinzhengjie]> show tables; #当前数据库中还没有任何表
Empty set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> CREATE TABLE students(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(30) NOT NULL,sex ENUM('boy','girl') DEFAULT
'boy',age TINYINT UNSIGNED,mobile CHAR(11),address VARCHAR(50));
Query OK, 0 rows affected (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW TABLES; #表创建成功后，我们就会在当前数据库看到对应的students表啦
+-----------------------+
| Tables_in_yinzhengjie |
+-----------------------+
| students |
+-----------------------+
1 row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> DESC students; #查看表结构
+---------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | | NULL | |
| sex | enum('boy','girl') | YES | | boy | |
| age | tinyint(3) unsigned | YES | | NULL | |
| mobile | char(11) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
+---------+---------------------+------+-----+---------+----------------+
6 rows in set (0.01 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW COLUMNS FROM students; #和上面的命令等效，也可以查看表结构
+---------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | | NULL | |
| sex | enum('boy','girl') | YES | | boy | |
| age | tinyint(3) unsigned | YES | | NULL | |
| mobile | char(11) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
+---------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> CREATE TABLE students(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(30) NOT NULL,sex ENUM('boy','girl') DEFAULT 'boy',age TINYINT UNSIGNED,mobile CHAR(11),address VARCHAR(50));
4>.往"students"表中插入数据
MariaDB [yinzhengjie]> SELECT COUNT(*) FROM students;
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> DESC students;
+---------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | | NULL | |
| sex | enum('boy','girl') | YES | | boy | |
| age | tinyint(3) unsigned | YES | | NULL | |
| mobile | char(11) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
+---------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> INSERT INTO students (name,age,mobile,address) VALUES ('Jason Yin',18,10000,'beijing'),('Jay','',10086,'Taiwan');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0 MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+-----------+------+------+--------+---------+
| id | name | sex | age | mobile | address |
+----+-----------+------+------+--------+---------+
| 1 | Jason Yin | boy | 18 | 10000 | beijing |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
+----+-----------+------+------+--------+---------+
2 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT COUNT(*) FROM students;
+----------+
| COUNT(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> INSERT INTO students (name,age,mobile,address) VALUES ('Jason Yin',18,10000,'beijing'),('Jay','40',10086,'Taiwan');
MariaDB [yinzhengjie]> DESC students;
+---------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | | NULL | |
| sex | enum('boy','girl') | YES | | boy | |
| age | tinyint(3) unsigned | YES | | NULL | |
| mobile | char(11) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
+---------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> INSERT INTO students SET name='yinzhengjie',age=27,address='shanxi';
Query OK, 1 row affected (0.01 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+-------------+------+------+--------+---------+
| id | name | sex | age | mobile | address |
+----+-------------+------+------+--------+---------+
| 1 | Jason Yin | boy | 18 | 10000 | beijing |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
| 3 | yinzhengjie | boy | 27 | NULL | shanxi |
+----+-------------+------+------+--------+---------+
3 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> INSERT INTO students SET name='yinzhengjie',age=27,address='shanxi';
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+-------------+------+------+--------+---------+
| id | name | sex | age | mobile | address |
+----+-------------+------+------+--------+---------+
| 1 | Jason Yin | boy | 18 | 10000 | beijing |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
| 3 | yinzhengjie | boy | 27 | NULL | shanxi |
+----+-------------+------+------+--------+---------+
3 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> INSERT students (age,sex,name,mobile,address) VALUES (28,'girl','Gloria Tang Tsz-Kei',null,'Hong Kong');
Query OK, 1 row affected (0.01 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+--------+-----------+
| id | name | sex | age | mobile | address |
+----+---------------------+------+------+--------+-----------+
| 1 | Jason Yin | boy | 18 | 10000 | beijing |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
| 3 | yinzhengjie | boy | 27 | NULL | shanxi |
| 4 | Gloria Tang Tsz-Kei | girl | 28 | NULL | Hong Kong |
+----+---------------------+------+------+--------+-----------+
4 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> INSERT students (age,sex,name,mobile,address) VALUES (28,'girl','Gloria Tang Tsz-Kei',null,'Hong Kong');
MariaDB [yinzhengjie]> DESC students;
+---------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | | NULL | |
| sex | enum('boy','girl') | YES | | boy | |
| age | tinyint(3) unsigned | YES | | NULL | |
| mobile | char(11) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
+---------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> DESC custom;
+---------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | | NULL | |
| sex | enum('boy','girl') | YES | | boy | |
| age | tinyint(3) unsigned | YES | | NULL | |
| mobile | char(11) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
+---------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+--------+-----------+
| id | name | sex | age | mobile | address |
+----+---------------------+------+------+--------+-----------+
| 1 | Jason Yin | boy | 18 | 10000 | beijing |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
| 3 | yinzhengjie | boy | 27 | NULL | shanxi |
| 4 | Gloria Tang Tsz-Kei | girl | 28 | NULL | Hong Kong |
+----+---------------------+------+------+--------+-----------+
4 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM custom;
Empty set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> INSERT custom SELECT * FROM students; #将一张表的查询结果插入到另外一张表，前提是两张表结构要一致!
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0 MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM custom;
+----+---------------------+------+------+--------+-----------+
| id | name | sex | age | mobile | address |
+----+---------------------+------+------+--------+-----------+
| 1 | Jason Yin | boy | 18 | 10000 | beijing |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
| 3 | yinzhengjie | boy | 27 | NULL | shanxi |
| 4 | Gloria Tang Tsz-Kei | girl | 28 | NULL | Hong Kong |
+----+---------------------+------+------+--------+-----------+
4 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> INSERT custom SELECT * FROM students; #将一张表的查询结果插入到另外一张表，前提是两张表结构要一致!
5>.用已经存在的表创建出一张新表(复制内容，表结构属性不完全复制)
MariaDB [yinzhengjie]> DESC students; #注意该表是有主键的
+---------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | | NULL | |
| sex | enum('boy','girl') | YES | | boy | |
| age | tinyint(3) unsigned | YES | | NULL | |
| mobile | char(11) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
+---------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+-----------+------+------+--------+---------+
| id | name | sex | age | mobile | address |
+----+-----------+------+------+--------+---------+
| 1 | Jason Yin | boy | 18 | 10000 | beijing |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
+----+-----------+------+------+--------+---------+
2 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> CREATE TABLE employee SELECT * FROM students; #将students的内容克隆到employee表中。
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0 MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> DESC employee; #克隆出来的表是没有主键的。
+---------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | | 0 | |
| name | varchar(30) | NO | | NULL | |
| sex | enum('boy','girl') | YES | | boy | |
| age | tinyint(3) unsigned | YES | | NULL | |
| mobile | char(11) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
+---------+---------------------+------+-----+---------+-------+
6 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM employee;
+----+-----------+------+------+--------+---------+
| id | name | sex | age | mobile | address |
+----+-----------+------+------+--------+---------+
| 1 | Jason Yin | boy | 18 | 10000 | beijing |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
+----+-----------+------+------+--------+---------+
2 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> CREATE TABLE employee SELECT * FROM students; #将students的内容克隆到employee表中。但不克隆主键
6>.用已经存在的表创建出一张新表(仅仅复制表结构，不复制内容)
MariaDB [yinzhengjie]> DESC students;
+---------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | | NULL | |
| sex | enum('boy','girl') | YES | | boy | |
| age | tinyint(3) unsigned | YES | | NULL | |
| mobile | char(11) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
+---------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> CREATE TABLE custom LIKE students; #仅仅将students表结构复制给custom表
Query OK, 0 rows affected (0.01 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> DESC custom;
+---------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | | NULL | |
| sex | enum('boy','girl') | YES | | boy | |
| age | tinyint(3) unsigned | YES | | NULL | |
| mobile | char(11) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
+---------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+-----------+------+------+--------+---------+
| id | name | sex | age | mobile | address |
+----+-----------+------+------+--------+---------+
| 1 | Jason Yin | boy | 18 | 10000 | beijing |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
+----+-----------+------+------+--------+---------+
2 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM custom;
Empty set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> CREATE TABLE custom LIKE students; #仅仅将students表结构复制给custom表
7>.
四.删
MariaDB [(none)]> help DROP #查看DROP命令的帮助信息
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
ALTER TABLE
ALTER TABLESPACE
DEALLOCATE PREPARE
DROP DATABASE
DROP EVENT
DROP FUNCTION
DROP FUNCTION UDF
DROP INDEX
DROP PROCEDURE
DROP SERVER
DROP TABLE
DROP TABLESPACE
DROP TRIGGER
DROP USER
DROP VIEW MariaDB [(none)]>
MariaDB [(none)]> help DROP #查看DROP命令的帮助信息
MariaDB [(none)]> help DELETE #查看DELETE命令的帮助信息
Name: 'DELETE'
Description:
Syntax:
Single-table syntax: DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count] Multiple-table syntax: DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
tbl_name[.*] [, tbl_name[.*]] ...
FROM table_references
[WHERE where_condition] Or: DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
FROM tbl_name[.*] [, tbl_name[.*]] ...
USING table_references
[WHERE where_condition] For the single-table syntax, the DELETE statement deletes rows from
tbl_name and returns a count of the number of deleted rows. This count
can be obtained by calling the ROW_COUNT() function (see
https://mariadb.com/kb/en/information-functions-row_count/). The
WHERE clause, if given, specifies the conditions that identify which
rows to delete. With no WHERE clause, all rows are deleted. If the
ORDER BY clause is specified, the rows are deleted in the order that is
specified. The LIMIT clause places a limit on the number of rows that
can be deleted. For the multiple-table syntax, DELETE deletes from each tbl_name the
rows that satisfy the conditions. In this case, ORDER BY and LIMIT
cannot be used. where_condition is an expression that evaluates to true for each row to
be deleted. It is specified as described in
https://mariadb.com/kb/en/select/. Currently, you cannot delete from a table and select from the same
table in a subquery. You need the DELETE privilege on a table to delete rows from it. You
need only the SELECT privilege for any columns that are only read, such
as those named in the WHERE clause. As stated, a DELETE statement with no WHERE clause deletes all rows. A
faster way to do this, when you do not need to know the number of
deleted rows, is to use TRUNCATE TABLE. However, within a transaction
or if you have a lock on the table, TRUNCATE TABLE cannot be used
whereas DELETE can. See [HELP TRUNCATE TABLE], and [HELP LOCK]. URL: https://mariadb.com/kb/en/delete/ MariaDB [(none)]>
MariaDB [(none)]> help DELETE #查看DELETE命令的帮助信息
1>.删除指定数据库
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| yinzhengjie |
| yinzhengjie2019 |
+--------------------+
5 rows in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> DROP DATABASE IF EXISTS yinzhengjie2019;
Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| yinzhengjie |
+--------------------+
4 rows in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> DROP DATABASE IF EXISTS yinzhengjie2019;
2>.删除表
MariaDB [yinzhengjie]> SHOW TABLES;
+-----------------------+
| Tables_in_yinzhengjie |
+-----------------------+
| students |
+-----------------------+
row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> DROP TABLE IF EXISTS students;
Query OK, 0 rows affected (0.01 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW TABLES;
Empty set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> DROP TABLE IF EXISTS students;
3>.删除表中的记录
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+--------+-----------+
| id | name | sex | age | mobile | address |
+----+---------------------+------+------+--------+-----------+
| 1 | Jason Yin | boy | 18 | 10000 | beijing |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
| 3 | yinzhengjie | boy | 27 | NULL | shanxi |
| 4 | Gloria Tang Tsz-Kei | girl | 28 | NULL | Hong Kong |
+----+---------------------+------+------+--------+-----------+
4 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> DELETE FROM students WHERE id >= 3; #删除id列大于3的行，切记要用where语句过滤匹配的行！
Query OK, 2 rows affected (0.01 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+-----------+------+------+--------+---------+
| id | name | sex | age | mobile | address |
+----+-----------+------+------+--------+---------+
| 1 | Jason Yin | boy | 18 | 10000 | beijing |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
+----+-----------+------+------+--------+---------+
2 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> DELETE FROM students WHERE id >= 3; #删除id列大于3的行，切记要用where语句过滤匹配的行！
4>. 使用TRUNCATE命令快速清空一张表
MariaDB [yinzhengjie]> SELECT * FROM custom;
+----+---------------------+------+------+--------+-----------+
| id | name | sex | age | mobile | address |
+----+---------------------+------+------+--------+-----------+
| 1 | Jason Yin | boy | 18 | 10000 | beijing |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
| 3 | yinzhengjie | boy | 27 | NULL | shanxi |
| 4 | Gloria Tang Tsz-Kei | girl | 28 | NULL | Hong Kong |
+----+---------------------+------+------+--------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> TRUNCATE TABLE custom; #快速清空一张表的数据，该操作将无法恢复！快速删除大表数据的确快但请慎用!
Query OK, 0 rows affected (0.01 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM custom;
Empty set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> TRUNCATE TABLE custom; #快速清空一张表的数据，该操作将无法恢复！快速删除大表数据的确快但请慎用!
五.改
MariaDB [yinzhengjie]> 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: https://mariadb.com/kb/en/alter-table/ MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> HELP ALTER TABLE; #查看修改表结构命令的帮助信息
MariaDB [(none)]> help UPDATE #查看UPDATE命令的帮助信息
Name: 'UPDATE'
Description:
Syntax:
Single-table syntax: UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count] Multiple-table syntax: UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition] For the single-table syntax, the UPDATE statement updates columns of
existing rows in the named table with new values. The SET clause
indicates which columns to modify and the values they should be given.
Each value can be given as an expression, or the keyword DEFAULT to set
a column explicitly to its default value. The WHERE clause, if given,
specifies the conditions that identify which rows to update. With no
WHERE clause, all rows are updated. If the ORDER BY clause is
specified, the rows are updated in the order that is specified. The
LIMIT clause places a limit on the number of rows that can be updated. For the multiple-table syntax, UPDATE updates rows in each table named
in table_references that satisfy the conditions. In this case, ORDER BY
and LIMIT cannot be used. where_condition is an expression that evaluates to true for each row to
be updated. For expression syntax, see
http://dev.mysql.com/doc/refman/5.5/en/expressions.html. table_references and where_condition are is specified as described in
https://mariadb.com/kb/en/select/. You need the UPDATE privilege only for columns referenced in an UPDATE
that are actually updated. You need only the SELECT privilege for any
columns that are read but not modified. The UPDATE statement supports the following modifiers: o With the LOW_PRIORITY keyword, execution of the UPDATE is delayed
until no other clients are reading from the table. This affects only
storage engines that use only table-level locking (such as MyISAM,
MEMORY, and MERGE). o With the IGNORE keyword, the update statement does not abort even if
errors occur during the update. Rows for which duplicate-key
conflicts occur are not updated. Rows for which columns are updated
to values that would cause data conversion errors are updated to the
closest valid values instead. URL: https://mariadb.com/kb/en/update/ MariaDB [(none)]>
MariaDB [(none)]> help UPDATE #查看UPDATE命令的帮助信息
1>.修改表的字符集
MariaDB [yinzhengjie]> SHOW CREATE TABLE students;
+----------+-------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table
|+----------+-------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| students | CREATE TABLE `students` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`sex` enum('boy','girl') DEFAULT 'boy',
`age` tinyint(3) unsigned DEFAULT NULL,
`mobile` char(11) DEFAULT NULL,
`address` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 |
+----------+-------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> ALTER TABLE students CHARACTER SET = utf8mb4; #推荐使用utf8mb4字符集，它可以比utf8支持更多的字符
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0 MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW CREATE TABLE students\G
*************************** 1. row ***************************
Table: students
Create Table: CREATE TABLE `students` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(30) CHARACTER SET latin1 NOT NULL,
`sex` enum('boy','girl') CHARACTER SET latin1 DEFAULT 'boy',
`age` tinyint(3) unsigned DEFAULT NULL,
`mobile` char(11) CHARACTER SET latin1 DEFAULT NULL,
`address` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> ALTER TABLE students CHARACTER SET = utf8mb4; #推荐使用utf8mb4字符集，它可以比utf8支持更多的字符
2>.修改表中某个字段的值
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+-----------+------+------+--------+---------+
| id | name | sex | age | mobile | address |
+----+-----------+------+------+--------+---------+
| 1 | Jason Yin | boy | 18 | 10000 | beijing |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
+----+-----------+------+------+--------+---------+
2 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> UPDATE students SET age=27 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+-----------+------+------+--------+---------+
| id | name | sex | age | mobile | address |
+----+-----------+------+------+--------+---------+
| 1 | Jason Yin | boy | 27 | 10000 | beijing |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
+----+-----------+------+------+--------+---------+
2 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> UPDATE students SET age=27 WHERE id = 1;
3>.数据库字符集更改
MariaDB [yinzhengjie]> SHOW CREATE DATABASE yinzhengjie;
+-------------+------------------------------------------------------------------------+
| Database | Create Database |
+-------------+------------------------------------------------------------------------+
| yinzhengjie | CREATE DATABASE `yinzhengjie` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+-------------+------------------------------------------------------------------------+
1 row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> ALTER DATABASE yinzhengjie CHARACTER SET utf8mb4;
Query OK, 1 row affected (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW CREATE DATABASE yinzhengjie;
+-------------+-------------------------------------------------------------------------+
| Database | Create Database |
+-------------+-------------------------------------------------------------------------+
| yinzhengjie | CREATE DATABASE `yinzhengjie` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+-------------+-------------------------------------------------------------------------+
1 row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> ALTER DATABASE yinzhengjie CHARACTER SET utf8mb4;
4>.表的字符集更改（只影响新添加字段，已添加字段在字符集不变）
MariaDB [yinzhengjie]> SHOW CREATE TABLE students\G
*************************** 1. row ***************************
Table: students
Create Table: CREATE TABLE `students` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(30) CHARACTER SET latin1 NOT NULL,
`sex` enum('boy','girl') CHARACTER SET latin1 DEFAULT 'boy',
`age` tinyint(3) unsigned DEFAULT NULL,
`mobile` char(11) CHARACTER SET latin1 DEFAULT NULL,
`address` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> ALTER TABLE students CHARACTER SET utf8;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0 MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW CREATE TABLE students\G
*************************** 1. row ***************************
Table: students
Create Table: CREATE TABLE `students` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(30) CHARACTER SET latin1 NOT NULL,
`sex` enum('boy','girl') CHARACTER SET latin1 DEFAULT 'boy',
`age` tinyint(3) unsigned DEFAULT NULL,
`mobile` char(11) CHARACTER SET latin1 DEFAULT NULL,
`address` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
1 row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> ALTER TABLE students CHARACTER SET utf8;
5>.表中某个字段的字符集更改
MariaDB [yinzhengjie]> SHOW CREATE TABLE students\G
*************************** 1. row ***************************
Table: students
Create Table: CREATE TABLE `students` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(30) CHARACTER SET latin1 NOT NULL,
`sex` enum('boy','girl') CHARACTER SET latin1 DEFAULT 'boy',
`age` tinyint(3) unsigned DEFAULT NULL,
`mobile` char(11) CHARACTER SET latin1 DEFAULT NULL,
`address` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
1 row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> ALTER TABLE students CHANGE name name VARCHAR(50) CHARACTER SET utf8mb4;
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0 MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> ALTER TABLE students CHANGE address adress VARCHAR(100) CHARACTER SET utf8mb4;
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0 MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW CREATE TABLE students\G
*************************** 1. row ***************************
Table: students
Create Table: CREATE TABLE `students` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) CHARACTER SET utf8mb4 DEFAULT NULL,
`sex` enum('boy','girl') CHARACTER SET latin1 DEFAULT 'boy',
`age` tinyint(3) unsigned DEFAULT NULL,
`mobile` char(11) CHARACTER SET latin1 DEFAULT NULL,
`adress` varchar(100) CHARACTER SET utf8mb4 DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
1 row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> ALTER TABLE students CHANGE name name VARCHAR(50) CHARACTER SET utf8mb4;
6>.
六.查
MariaDB [mysql]> HELP SHOW #查看SHOW命令的帮助信息
Name: 'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following: SHOW AUTHORS
SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
SHOW CONTRIBUTORS
SHOW CREATE DATABASE db_name
SHOW CREATE EVENT event_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
SHOW CREATE TABLE tbl_name
SHOW CREATE TRIGGER trigger_name
SHOW CREATE VIEW view_name
SHOW DATABASES [like_or_where]
SHOW ENGINE engine_name {STATUS | MUTEX}
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW EVENTS
SHOW FUNCTION CODE func_name
SHOW FUNCTION STATUS [like_or_where]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW MASTER STATUS
SHOW OPEN TABLES [FROM db_name] [like_or_where]
SHOW PLUGINS
SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE STATUS [like_or_where]
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
SHOW SLAVE HOSTS
SHOW SLAVE STATUS
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW TABLE STATUS [FROM db_name] [like_or_where]
SHOW [FULL] TABLES [FROM db_name] [like_or_where]
SHOW TRIGGERS [FROM db_name] [like_or_where]
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW WARNINGS [LIMIT [offset,] row_count] like_or_where:
LIKE 'pattern'
| WHERE expr If the syntax for a given SHOW statement includes a LIKE 'pattern'
part, 'pattern' is a string that can contain the SQL "%" and "_"
wildcard characters. The pattern is useful for restricting statement
output to matching values. Several SHOW statements also accept a WHERE clause that provides more
flexibility in specifying which rows to display. See
http://dev.mysql.com/doc/refman/5.5/en/extended-show.html. URL: http://dev.mysql.com/doc/refman/5.5/en/show.html MariaDB [mysql]>
MariaDB [mysql]> HELP SHOW #查看SHOW命令的帮助信息
MariaDB [(none)]> help SELECT #查看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
https://mariadb.com/kb/en/subqueries/. 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
https://mariadb.com/kb/en/select#select-expressions, and
https://mariadb.com/kb/en/functions-and-operators/. SELECT can also be used to retrieve rows computed without reference to
any table. URL: https://mariadb.com/kb/en/select/ MariaDB [(none)]>
MariaDB [(none)]> help SELECT #查看SELECT命令的帮助信息
1>.查看现有数据库
[[email protected] ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema | #该数据库在内存中存放，因此我们在MySQL数据存储目录中找不到该目录名称，但下面3个目录是可以看到的。
| mysql |
| performance_schema |
| test |
+--------------------+
rows in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SHOW DATABASES;
2>.查看数据库版本
MariaDB [(none)]> SELECT VERSION();
+----------------+
| VERSION() |
+----------------+
| 5.5.-MariaDB |
+----------------+
row in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SELECT VERSION();
3>.查看当前登录用户信息
MariaDB [(none)]> SELECT USER();
+----------------+
| USER() |
+----------------+
| root@localhost |
+----------------+
row in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SELECT USER();
4>.查看当前所在数据库
MariaDB [(none)]> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| NULL |
+------------+
row in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> USE mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A Database changed
MariaDB [mysql]>
MariaDB [mysql]> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| mysql |
+------------+
row in set (0.00 sec) MariaDB [mysql]>
MariaDB [mysql]>
MariaDB [mysql]> SELECT DATABASE();
5>.查看当前所在数据库中存在的表
MariaDB [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 (0.00 sec) MariaDB [mysql]>
MariaDB [mysql]> SHOW TABLES;
[[email protected] ~]# ll /var/lib/mysql/mysql #数据库的本质对应Linux就是一个文件夹
total
-rw-rw---- mysql mysql Oct : columns_priv.frm
-rw-rw---- mysql mysql Oct : columns_priv.MYD
-rw-rw---- mysql mysql Oct : columns_priv.MYI
-rw-rw---- mysql mysql Oct : db.frm
-rw-rw---- mysql mysql Oct : db.MYD
-rw-rw---- mysql mysql Oct : db.MYI
-rw-rw---- mysql mysql Oct : event.frm
-rw-rw---- mysql mysql Oct : event.MYD
-rw-rw---- mysql mysql Oct : event.MYI
-rw-rw---- mysql mysql Oct : func.frm
-rw-rw---- mysql mysql Oct : func.MYD
-rw-rw---- mysql mysql Oct : func.MYI
-rw-rw---- mysql mysql Oct : general_log.CSM
-rw-rw---- mysql mysql Oct : general_log.CSV
-rw-rw---- mysql mysql Oct : general_log.frm
-rw-rw---- mysql mysql Oct : help_category.frm
-rw-rw---- mysql mysql Oct : help_category.MYD
-rw-rw---- mysql mysql Oct : help_category.MYI
-rw-rw---- mysql mysql Oct : help_keyword.frm
-rw-rw---- mysql mysql Oct : help_keyword.MYD
-rw-rw---- mysql mysql Oct : help_keyword.MYI
-rw-rw---- mysql mysql Oct : help_relation.frm
-rw-rw---- mysql mysql Oct : help_relation.MYD
-rw-rw---- mysql mysql Oct : help_relation.MYI
-rw-rw---- mysql mysql Oct : help_topic.frm
-rw-rw---- mysql mysql Oct : help_topic.MYD
-rw-rw---- mysql mysql Oct : help_topic.MYI
-rw-rw---- mysql mysql Oct : host.frm
-rw-rw---- mysql mysql Oct : host.MYD
-rw-rw---- mysql mysql Oct : host.MYI
-rw-rw---- mysql mysql Oct : ndb_binlog_index.frm
-rw-rw---- mysql mysql Oct : ndb_binlog_index.MYD
-rw-rw---- mysql mysql Oct : ndb_binlog_index.MYI
-rw-rw---- mysql mysql Oct : plugin.frm
-rw-rw---- mysql mysql Oct : plugin.MYD
-rw-rw---- mysql mysql Oct : plugin.MYI
-rw-rw---- mysql mysql Oct : proc.frm
-rw-rw---- mysql mysql Oct : proc.MYD
-rw-rw---- mysql mysql Oct : proc.MYI
-rw-rw---- mysql mysql Oct : procs_priv.frm
-rw-rw---- mysql mysql Oct : procs_priv.MYD
-rw-rw---- mysql mysql Oct : procs_priv.MYI
-rw-rw---- mysql mysql Oct : proxies_priv.frm
-rw-rw---- mysql mysql Oct : proxies_priv.MYD
-rw-rw---- mysql mysql Oct : proxies_priv.MYI
-rw-rw---- mysql mysql Oct : servers.frm
-rw-rw---- mysql mysql Oct : servers.MYD
-rw-rw---- mysql mysql Oct : servers.MYI
-rw-rw---- mysql mysql Oct : slow_log.CSM
-rw-rw---- mysql mysql Oct : slow_log.CSV
-rw-rw---- mysql mysql Oct : slow_log.frm
-rw-rw---- mysql mysql Oct : tables_priv.frm
-rw-rw---- mysql mysql Oct : tables_priv.MYD
-rw-rw---- mysql mysql Oct : tables_priv.MYI
-rw-rw---- mysql mysql Oct : time_zone.frm
-rw-rw---- mysql mysql Oct : time_zone_leap_second.frm
-rw-rw---- mysql mysql Oct : time_zone_leap_second.MYD
-rw-rw---- mysql mysql Oct : time_zone_leap_second.MYI
-rw-rw---- mysql mysql Oct : time_zone.MYD
-rw-rw---- mysql mysql Oct : time_zone.MYI
-rw-rw---- mysql mysql Oct : time_zone_name.frm
-rw-rw---- mysql mysql Oct : time_zone_name.MYD
-rw-rw---- mysql mysql Oct : time_zone_name.MYI
-rw-rw---- mysql mysql Oct : time_zone_transition.frm
-rw-rw---- mysql mysql Oct : time_zone_transition.MYD
-rw-rw---- mysql mysql Oct : time_zone_transition.MYI
-rw-rw---- mysql mysql Oct : time_zone_transition_type.frm
-rw-rw---- mysql mysql Oct : time_zone_transition_type.MYD
-rw-rw---- mysql mysql Oct : time_zone_transition_type.MYI
-rw-rw---- mysql mysql Oct : user.frm
-rw-rw---- mysql mysql Oct : user.MYD
-rw-rw---- mysql mysql Oct : user.MYI
[[email protected] ~]#
[[email protected] ~]# ll /var/lib/mysql/mysql #数据库的本质对应Linux就是一个文件夹
6>.查看表字段(列名)信息
MariaDB [yinzhengjie]> DESC students;
+---------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | | NULL | |
| sex | enum('boy','girl') | YES | | boy | |
| age | tinyint(3) unsigned | YES | | NULL | |
| mobile | char(11) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
+---------+---------------------+------+-----+---------+----------------+
6 rows in set (0.01 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> DESC students;
MariaDB [yinzhengjie]> SHOW COLUMNS FROM students;
+---------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | | NULL | |
| sex | enum('boy','girl') | YES | | boy | |
| age | tinyint(3) unsigned | YES | | NULL | |
| mobile | char(11) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
+---------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW COLUMNS FROM students;
7>.查看mysql数据库user表的部分字段
MariaDB [mysql]> SELECT host,user,password FROM user;
+----------------------------+------+----------+
| host | user | password |
+----------------------------+------+----------+
| localhost | root | |
| node102.yinzhengjie.org.cn | root | |
| 127.0.0.1 | root | |
| :: | root | |
| localhost | | |
| node102.yinzhengjie.org.cn | | |
+----------------------------+------+----------+
rows in set (0.00 sec) MariaDB [mysql]>
MariaDB [mysql]> SELECT host,user,password FROM user;
8>.显示当前系统时间
MariaDB [mysql]> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| -- :: |
+---------------------+
row in set (0.00 sec) MariaDB [mysql]>
MariaDB [mysql]> SELECT NOW();
9>.查看MySQL配置文件中的变量
MariaDB [(none)]> SHOW VARIABLES LIKE 'datadir';
+---------------+--------------+
| Variable_name | Value |
+---------------+--------------+
| datadir | /data/mysql/ |
+---------------+--------------+
row in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SHOW VARIABLES LIKE 'datadir';
MariaDB [(none)]> SELECT @@datadir;
+--------------+
| @@datadir |
+--------------+
| /data/mysql/ |
+--------------+
row in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SELECT @@datadir;
10>.查看系统支持的字符集(包含每个字符集在磁盘上占用字节大小)
MariaDB [(none)]> SHOW CHARACTER SET;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | |
| dec8 | DEC West European | dec8_swedish_ci | |
| cp850 | DOS West European | cp850_general_ci | |
| hp8 | HP West European | hp8_english_ci | |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | |
| latin1 | cp1252 West European | latin1_swedish_ci | |
| latin2 | ISO - Central European | latin2_general_ci | |
| swe7 | 7bit Swedish | swe7_swedish_ci | |
| ascii | US ASCII | ascii_general_ci | |
| ujis | EUC-JP Japanese | ujis_japanese_ci | |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | |
| hebrew | ISO - Hebrew | hebrew_general_ci | |
| tis620 | TIS620 Thai | tis620_thai_ci | |
| euckr | EUC-KR Korean | euckr_korean_ci | |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | |
| greek | ISO - Greek | greek_general_ci | |
| cp1250 | Windows Central European | cp1250_general_ci | |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | |
| latin5 | ISO - Turkish | latin5_turkish_ci | |
| armscii8 | ARMSCII- Armenian | armscii8_general_ci | |
| utf8 | UTF- Unicode | utf8_general_ci | |
| ucs2 | UCS- Unicode | ucs2_general_ci | |
| cp866 | DOS Russian | cp866_general_ci | |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | |
| macce | Mac Central European | macce_general_ci | |
| macroman | Mac West European | macroman_general_ci | |
| cp852 | DOS Central European | cp852_general_ci | |
| latin7 | ISO - Baltic | latin7_general_ci | |
| utf8mb4 | UTF- Unicode | utf8mb4_general_ci | |
| cp1251 | Windows Cyrillic | cp1251_general_ci | |
| utf16 | UTF- Unicode | utf16_general_ci | |
| utf16le | UTF-16LE Unicode | utf16le_general_ci | |
| cp1256 | Windows Arabic | cp1256_general_ci | |
| cp1257 | Windows Baltic | cp1257_general_ci | |
| utf32 | UTF- Unicode | utf32_general_ci | |
| binary | Binary pseudo charset | binary | |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | |
+----------+-----------------------------+---------------------+--------+
rows in set (0.00 sec) MariaDB [(none)]
MariaDB [(none)]> SHOW CHARACTER SET;
11>.查看默认的排序规则
MariaDB [(none)]> SHOW COLLATION; #查看字符集对应的默认排序规则
+------------------------------+----------+------+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+------------------------------+----------+------+---------+----------+---------+
| big5_chinese_ci | big5 | 1 | Yes | Yes | 1 |
| big5_bin | big5 | 84 | | Yes | 1 |
| big5_chinese_nopad_ci | big5 | 1025 | | Yes | 1 |
| big5_nopad_bin | big5 | 1108 | | Yes | 1 |
| dec8_swedish_ci | dec8 | 3 | Yes | Yes | 1 |
| dec8_bin | dec8 | 69 | | Yes | 1 |
| dec8_swedish_nopad_ci | dec8 | 1027 | | Yes | 1 |
| dec8_nopad_bin | dec8 | 1093 | | Yes | 1 |
| cp850_general_ci | cp850 | 4 | Yes | Yes | 1 |
| cp850_bin | cp850 | 80 | | Yes | 1 |
| cp850_general_nopad_ci | cp850 | 1028 | | Yes | 1 |
| cp850_nopad_bin | cp850 | 1104 | | Yes | 1 |
| hp8_english_ci | hp8 | 6 | Yes | Yes | 1 |
| hp8_bin | hp8 | 72 | | Yes | 1 |
| hp8_english_nopad_ci | hp8 | 1030 | | Yes | 1 |
| hp8_nopad_bin | hp8 | 1096 | | Yes | 1 |
| koi8r_general_ci | koi8r | 7 | Yes | Yes | 1 |
| koi8r_bin | koi8r | 74 | | Yes | 1 |
| koi8r_general_nopad_ci | koi8r | 1031 | | Yes | 1 |
| koi8r_nopad_bin | koi8r | 1098 | | Yes | 1 |
| latin1_german1_ci | latin1 | 5 | | Yes | 1 |
| latin1_swedish_ci | latin1 | 8 | Yes | Yes | 1 |
| latin1_danish_ci | latin1 | 15 | | Yes | 1 |
| latin1_german2_ci | latin1 | 31 | | Yes | 2 |
| latin1_bin | latin1 | 47 | | Yes | 1 |
| latin1_general_ci | latin1 | 48 | | Yes | 1 |
| latin1_general_cs | latin1 | 49 | | Yes | 1 |
| latin1_spanish_ci | latin1 | 94 | | Yes | 1 |
| latin1_swedish_nopad_ci | latin1 | 1032 | | Yes | 1 |
| latin1_nopad_bin | latin1 | 1071 | | Yes | 1 |
| latin2_czech_cs | latin2 | 2 | | Yes | 4 |
| latin2_general_ci | latin2 | 9 | Yes | Yes | 1 |
| latin2_hungarian_ci | latin2 | 21 | | Yes | 1 |
| latin2_croatian_ci | latin2 | 27 | | Yes | 1 |
| latin2_bin | latin2 | 77 | | Yes | 1 |
| latin2_general_nopad_ci | latin2 | 1033 | | Yes | 1 |
| latin2_nopad_bin | latin2 | 1101 | | Yes | 1 |
| swe7_swedish_ci | swe7 | 10 | Yes | Yes | 1 |
| swe7_bin | swe7 | 82 | | Yes | 1 |
| swe7_swedish_nopad_ci | swe7 | 1034 | | Yes | 1 |
| swe7_nopad_bin | swe7 | 1106 | | Yes | 1 |
| ascii_general_ci | ascii | 11 | Yes | Yes | 1 |
| ascii_bin | ascii | 65 | | Yes | 1 |
| ascii_general_nopad_ci | ascii | 1035 | | Yes | 1 |
| ascii_nopad_bin | ascii | 1089 | | Yes | 1 |
| ujis_japanese_ci | ujis | 12 | Yes | Yes | 1 |
| ujis_bin | ujis | 91 | | Yes | 1 |
| ujis_japanese_nopad_ci | ujis | 1036 | | Yes | 1 |
| ujis_nopad_bin | ujis | 1115 | | Yes | 1 |
| sjis_japanese_ci | sjis | 13 | Yes | Yes | 1 |
| sjis_bin | sjis | 88 | | Yes | 1 |
| sjis_japanese_nopad_ci | sjis | 1037 | | Yes | 1 |
| sjis_nopad_bin | sjis | 1112 | | Yes | 1 |
| hebrew_general_ci | hebrew | 16 | Yes | Yes | 1 |
| hebrew_bin | hebrew | 71 | | Yes | 1 |
| hebrew_general_nopad_ci | hebrew | 1040 | | Yes | 1 |
| hebrew_nopad_bin | hebrew | 1095 | | Yes | 1 |
| tis620_thai_ci | tis620 | 18 | Yes | Yes | 4 |
| tis620_bin | tis620 | 89 | | Yes | 1 |
| tis620_thai_nopad_ci | tis620 | 1042 | | Yes | 4 |
| tis620_nopad_bin | tis620 | 1113 | | Yes | 1 |
| euckr_korean_ci | euckr | 19 | Yes | Yes | 1 |
| euckr_bin | euckr | 85 | | Yes | 1 |
| euckr_korean_nopad_ci | euckr | 1043 | | Yes | 1 |
| euckr_nopad_bin | euckr | 1109 | | Yes | 1 |
| koi8u_general_ci | koi8u | 22 | Yes | Yes | 1 |
| koi8u_bin | koi8u | 75 | | Yes | 1 |
| koi8u_general_nopad_ci | koi8u | 1046 | | Yes | 1 |
| koi8u_nopad_bin | koi8u | 1099 | | Yes | 1 |
| gb2312_chinese_ci | gb2312 | 24 | Yes | Yes | 1 |
| gb2312_bin | gb2312 | 86 | | Yes | 1 |
| gb2312_chinese_nopad_ci | gb2312 | 1048 | | Yes | 1 |
| gb2312_nopad_bin | gb2312 | 1110 | | Yes | 1 |
| greek_general_ci | greek | 25 | Yes | Yes | 1 |
| greek_bin | greek | 70 | | Yes | 1 |
| greek_general_nopad_ci | greek | 1049 | | Yes | 1 |
| greek_nopad_bin | greek | 1094 | | Yes | 1 |
| cp1250_general_ci | cp1250 | 26 | Yes | Yes | 1 |
| cp1250_czech_cs | cp1250 | 34 | | Yes | 2 |
| cp1250_croatian_ci | cp1250 | 44 | | Yes | 1 |
| cp1250_bin | cp1250 | 66 | | Yes | 1 |
| cp1250_polish_ci | cp1250 | 99 | | Yes | 1 |
| cp1250_general_nopad_ci | cp1250 | 1050 | | Yes | 1 |
| cp1250_nopad_bin | cp1250 | 1090 | | Yes | 1 |
| gbk_chinese_ci | gbk | 28 | Yes | Yes | 1 |
| gbk_bin | gbk | 87 | | Yes | 1 |
| gbk_chinese_nopad_ci | gbk | 1052 | | Yes | 1 |
| gbk_nopad_bin | gbk | 1111 | | Yes | 1 |
| latin5_turkish_ci | latin5 | 30 | Yes | Yes | 1 |
| latin5_bin | latin5 | 78 | | Yes | 1 |
| latin5_turkish_nopad_ci | latin5 | 1054 | | Yes | 1 |
| latin5_nopad_bin | latin5 | 1102 | | Yes | 1 |
| armscii8_general_ci | armscii8 | 32 | Yes | Yes | 1 |
| armscii8_bin | armscii8 | 64 | | Yes | 1 |
| armscii8_general_nopad_ci | armscii8 | 1056 | | Yes | 1 |
| armscii8_nopad_bin | armscii8 | 1088 | | Yes | 1 |
| utf8_general_ci | utf8 | 33 | Yes | Yes | 1 |
| utf8_bin | utf8 | 83 | | Yes | 1 |
| utf8_unicode_ci | utf8 | 192 | | Yes | 8 |
| utf8_icelandic_ci | utf8 | 193 | | Yes | 8 |
| utf8_latvian_ci | utf8 | 194 | | Yes | 8 |
| utf8_romanian_ci | utf8 | 195 | | Yes | 8 |
| utf8_slovenian_ci | utf8 | 196 | | Yes | 8 |
| utf8_polish_ci | utf8 | 197 | | Yes | 8 |
| utf8_estonian_ci | utf8 | 198 | | Yes | 8 |
| utf8_spanish_ci | utf8 | 199 | | Yes | 8 |
| utf8_swedish_ci | utf8 | 200 | | Yes | 8 |
| utf8_turkish_ci | utf8 | 201 | | Yes | 8 |
| utf8_czech_ci | utf8 | 202 | | Yes | 8 |
| utf8_danish_ci | utf8 | 203 | | Yes | 8 |
| utf8_lithuanian_ci | utf8 | 204 | | Yes | 8 |
| utf8_slovak_ci | utf8 | 205 | | Yes | 8 |
| utf8_spanish2_ci | utf8 | 206 | | Yes | 8 |
| utf8_roman_ci | utf8 | 207 | | Yes | 8 |
| utf8_persian_ci | utf8 | 208 | | Yes | 8 |
| utf8_esperanto_ci | utf8 | 209 | | Yes | 8 |
| utf8_hungarian_ci | utf8 | 210 | | Yes | 8 |
| utf8_sinhala_ci | utf8 | 211 | | Yes | 8 |
| utf8_german2_ci | utf8 | 212 | | Yes | 8 |
| utf8_croatian_mysql561_ci | utf8 | 213 | | Yes | 8 |
| utf8_unicode_520_ci | utf8 | 214 | | Yes | 8 |
| utf8_vietnamese_ci | utf8 | 215 | | Yes | 8 |
| utf8_general_mysql500_ci | utf8 | 223 | | Yes | 1 |
| utf8_croatian_ci | utf8 | 576 | | Yes | 8 |
| utf8_myanmar_ci | utf8 | 577 | | Yes | 8 |
| utf8_thai_520_w2 | utf8 | 578 | | Yes | 4 |
| utf8_general_nopad_ci | utf8 | 1057 | | Yes | 1 |
| utf8_nopad_bin | utf8 | 1107 | | Yes | 1 |
| utf8_unicode_nopad_ci | utf8 | 1216 | | Yes | 8 |
| utf8_unicode_520_nopad_ci | utf8 | 1238 | | Yes | 8 |
| ucs2_general_ci | ucs2 | 35 | Yes | Yes | 1 |
| ucs2_bin | ucs2 | 90 | | Yes | 1 |
| ucs2_unicode_ci | ucs2 | 128 | | Yes | 8 |
| ucs2_icelandic_ci | ucs2 | 129 | | Yes | 8 |
| ucs2_latvian_ci | ucs2 | 130 | | Yes | 8 |
| ucs2_romanian_ci | ucs2 | 131 | | Yes | 8 |
| ucs2_slovenian_ci | ucs2 | 132 | | Yes | 8 |
| ucs2_polish_ci | ucs2 | 133 | | Yes | 8 |
| ucs2_estonian_ci | ucs2 | 134 | | Yes | 8 |
| ucs2_spanish_ci | ucs2 | 135 | | Yes | 8 |
| ucs2_swedish_ci | ucs2 | 136 | | Yes | 8 |
| ucs2_turkish_ci | ucs2 | 137 | | Yes | 8 |
| ucs2_czech_ci | ucs2 | 138 | | Yes | 8 |
| ucs2_danish_ci | ucs2 | 139 | | Yes | 8 |
| ucs2_lithuanian_ci | ucs2 | 140 | | Yes | 8 |
| ucs2_slovak_ci | ucs2 | 141 | | Yes | 8 |
| ucs2_spanish2_ci | ucs2 | 142 | | Yes | 8 |
| ucs2_roman_ci | ucs2 | 143 | | Yes | 8 |
| ucs2_persian_ci | ucs2 | 144 | | Yes | 8 |
| ucs2_esperanto_ci | ucs2 | 145 | | Yes | 8 |
| ucs2_hungarian_ci | ucs2 | 146 | | Yes | 8 |
| ucs2_sinhala_ci | ucs2 | 147 | | Yes | 8 |
| ucs2_german2_ci | ucs2 | 148 | | Yes | 8 |
| ucs2_croatian_mysql561_ci | ucs2 | 149 | | Yes | 8 |
| ucs2_unicode_520_ci | ucs2 | 150 | | Yes | 8 |
| ucs2_vietnamese_ci | ucs2 | 151 | | Yes | 8 |
| ucs2_general_mysql500_ci | ucs2 | 159 | | Yes | 1 |
| ucs2_croatian_ci | ucs2 | 640 | | Yes | 8 |
| ucs2_myanmar_ci | ucs2 | 641 | | Yes | 8 |
| ucs2_thai_520_w2 | ucs2 | 642 | | Yes | 4 |
| ucs2_general_nopad_ci | ucs2 | 1059 | | Yes | 1 |
| ucs2_nopad_bin | ucs2 | 1114 | | Yes | 1 |
| ucs2_unicode_nopad_ci | ucs2 | 1152 | | Yes | 8 |
| ucs2_unicode_520_nopad_ci | ucs2 | 1174 | | Yes | 8 |
| cp866_general_ci | cp866 | 36 | Yes | Yes | 1 |
| cp866_bin | cp866 | 68 | | Yes | 1 |
| cp866_general_nopad_ci | cp866 | 1060 | | Yes | 1 |
| cp866_nopad_bin | cp866 | 1092 | | Yes | 1 |
| keybcs2_general_ci | keybcs2 | 37 | Yes | Yes | 1 |
| keybcs2_bin | keybcs2 | 73 | | Yes | 1 |
| keybcs2_general_nopad_ci | keybcs2 | 1061 | | Yes | 1 |
| keybcs2_nopad_bin | keybcs2 | 1097 | | Yes | 1 |
| macce_general_ci | macce | 38 | Yes | Yes | 1 |
| macce_bin | macce | 43 | | Yes | 1 |
| macce_general_nopad_ci | macce | 1062 | | Yes | 1 |
| macce_nopad_bin | macce | 1067 | | Yes | 1 |
| macroman_general_ci | macroman | 39 | Yes | Yes | 1 |
| macroman_bin | macroman | 53 | | Yes | 1 |
| macroman_general_nopad_ci | macroman | 1063 | | Yes | 1 |
| macroman_nopad_bin | macroman | 1077 | | Yes | 1 |
| cp852_general_ci | cp852 | 40 | Yes | Yes | 1 |
| cp852_bin | cp852 | 81 | | Yes | 1 |
| cp852_general_nopad_ci | cp852 | 1064 | | Yes | 1 |
| cp852_nopad_bin | cp852 | 1105 | | Yes | 1 |
| latin7_estonian_cs | latin7 | 20 | | Yes | 1 |
| latin7_general_ci | latin7 | 41 | Yes | Yes | 1 |
| latin7_general_cs | latin7 | 42 | | Yes | 1 |
| latin7_bin | latin7 | 79 | | Yes | 1 |
| latin7_general_nopad_ci | latin7 | 1065 | | Yes | 1 |
| latin7_nopad_bin | latin7 | 1103 | | Yes | 1 |
| utf8mb4_general_ci | utf8mb4 | 45 | Yes | Yes | 1 |
| utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 |
| utf8mb4_unicode_ci | utf8mb4 | 224 | | Yes | 8 |
| utf8mb4_icelandic_ci | utf8mb4 | 225 | | Yes | 8 |
| utf8mb4_latvian_ci | utf8mb4 | 226 | | Yes | 8 |
| utf8mb4_romanian_ci | utf8mb4 | 227 | | Yes | 8 |
| utf8mb4_slovenian_ci | utf8mb4 | 228 | | Yes | 8 |
| utf8mb4_polish_ci | utf8mb4 | 229 | | Yes | 8 |
| utf8mb4_estonian_ci | utf8mb4 | 230 | | Yes | 8 |
| utf8mb4_spanish_ci | utf8mb4 | 231 | | Yes | 8 |
| utf8mb4_swedish_ci | utf8mb4 | 232 | | Yes | 8 |
| utf8mb4_turkish_ci | utf8mb4 | 233 | | Yes | 8 |
| utf8mb4_czech_ci | utf8mb4 | 234 | | Yes | 8 |
| utf8mb4_danish_ci | utf8mb4 | 235 | | Yes | 8 |
| utf8mb4_lithuanian_ci | utf8mb4 | 236 | | Yes | 8 |
| utf8mb4_slovak_ci | utf8mb4 | 237 | | Yes | 8 |
| utf8mb4_spanish2_ci | utf8mb4 | 238 | | Yes | 8 |
| utf8mb4_roman_ci | utf8mb4 | 239 | | Yes | 8 |
| utf8mb4_persian_ci | utf8mb4 | 240 | | Yes | 8 |
| utf8mb4_esperanto_ci | utf8mb4 | 241 | | Yes | 8 |
| utf8mb4_hungarian_ci | utf8mb4 | 242 | | Yes | 8 |
| utf8mb4_sinhala_ci | utf8mb4 | 243 | | Yes | 8 |
| utf8mb4_german2_ci | utf8mb4 | 244 | | Yes | 8 |
| utf8mb4_croatian_mysql561_ci | utf8mb4 | 245 | | Yes | 8 |
| utf8mb4_unicode_520_ci | utf8mb4 | 246 | | Yes | 8 |
| utf8mb4_vietnamese_ci | utf8mb4 | 247 | | Yes | 8 |
| utf8mb4_croatian_ci | utf8mb4 | 608 | | Yes | 8 |
| utf8mb4_myanmar_ci | utf8mb4 | 609 | | Yes | 8 |
| utf8mb4_thai_520_w2 | utf8mb4 | 610 | | Yes | 4 |
| utf8mb4_general_nopad_ci | utf8mb4 | 1069 | | Yes | 1 |
| utf8mb4_nopad_bin | utf8mb4 | 1070 | | Yes | 1 |
| utf8mb4_unicode_nopad_ci | utf8mb4 | 1248 | | Yes | 8 |
| utf8mb4_unicode_520_nopad_ci | utf8mb4 | 1270 | | Yes | 8 |
| cp1251_bulgarian_ci | cp1251 | 14 | | Yes | 1 |
| cp1251_ukrainian_ci | cp1251 | 23 | | Yes | 1 |
| cp1251_bin | cp1251 | 50 | | Yes | 1 |
| cp1251_general_ci | cp1251 | 51 | Yes | Yes | 1 |
| cp1251_general_cs | cp1251 | 52 | | Yes | 1 |
| cp1251_nopad_bin | cp1251 | 1074 | | Yes | 1 |
| cp1251_general_nopad_ci | cp1251 | 1075 | | Yes | 1 |
| utf16_general_ci | utf16 | 54 | Yes | Yes | 1 |
| utf16_bin | utf16 | 55 | | Yes | 1 |
| utf16_unicode_ci | utf16 | 101 | | Yes | 8 |
| utf16_icelandic_ci | utf16 | 102 | | Yes | 8 |
| utf16_latvian_ci | utf16 | 103 | | Yes | 8 |
| utf16_romanian_ci | utf16 | 104 | | Yes | 8 |
| utf16_slovenian_ci | utf16 | 105 | | Yes | 8 |
| utf16_polish_ci | utf16 | 106 | | Yes | 8 |
| utf16_estonian_ci | utf16 | 107 | | Yes | 8 |
| utf16_spanish_ci | utf16 | 108 | | Yes | 8 |
| utf16_swedish_ci | utf16 | 109 | | Yes | 8 |
| utf16_turkish_ci | utf16 | 110 | | Yes | 8 |
| utf16_czech_ci | utf16 | 111 | | Yes | 8 |
| utf16_danish_ci | utf16 | 112 | | Yes | 8 |
| utf16_lithuanian_ci | utf16 | 113 | | Yes | 8 |
| utf16_slovak_ci | utf16 | 114 | | Yes | 8 |
| utf16_spanish2_ci | utf16 | 115 | | Yes | 8 |
| utf16_roman_ci | utf16 | 116 | | Yes | 8 |
| utf16_persian_ci | utf16 | 117 | | Yes | 8 |
| utf16_esperanto_ci | utf16 | 118 | | Yes | 8 |
| utf16_hungarian_ci | utf16 | 119 | | Yes | 8 |
| utf16_sinhala_ci | utf16 | 120 | | Yes | 8 |
| utf16_german2_ci | utf16 | 121 | | Yes | 8 |
| utf16_croatian_mysql561_ci | utf16 | 122 | | Yes | 8 |
| utf16_unicode_520_ci | utf16 | 123 | | Yes | 8 |
| utf16_vietnamese_ci | utf16 | 124 | | Yes | 8 |
| utf16_croatian_ci | utf16 | 672 | | Yes | 8 |
| utf16_myanmar_ci | utf16 | 673 | | Yes | 8 |
| utf16_thai_520_w2 | utf16 | 674 | | Yes | 4 |
| utf16_general_nopad_ci | utf16 | 1078 | | Yes | 1 |
| utf16_nopad_bin | utf16 | 1079 | | Yes | 1 |
| utf16_unicode_nopad_ci | utf16 | 1125 | | Yes | 8 |
| utf16_unicode_520_nopad_ci | utf16 | 1147 | | Yes | 8 |
| utf16le_general_ci | utf16le | 56 | Yes | Yes | 1 |
| utf16le_bin | utf16le | 62 | | Yes | 1 |
| utf16le_general_nopad_ci | utf16le | 1080 | | Yes | 1 |
| utf16le_nopad_bin | utf16le | 1086 | | Yes | 1 |
| cp1256_general_ci | cp1256 | 57 | Yes | Yes | 1 |
| cp1256_bin | cp1256 | 67 | | Yes | 1 |
| cp1256_general_nopad_ci | cp1256 | 1081 | | Yes | 1 |
| cp1256_nopad_bin | cp1256 | 1091 | | Yes | 1 |
| cp1257_lithuanian_ci | cp1257 | 29 | | Yes | 1 |
| cp1257_bin | cp1257 | 58 | | Yes | 1 |
| cp1257_general_ci | cp1257 | 59 | Yes | Yes | 1 |
| cp1257_nopad_bin | cp1257 | 1082 | | Yes | 1 |
| cp1257_general_nopad_ci | cp1257 | 1083 | | Yes | 1 |
| utf32_general_ci | utf32 | 60 | Yes | Yes | 1 |
| utf32_bin | utf32 | 61 | | Yes | 1 |
| utf32_unicode_ci | utf32 | 160 | | Yes | 8 |
| utf32_icelandic_ci | utf32 | 161 | | Yes | 8 |
| utf32_latvian_ci | utf32 | 162 | | Yes | 8 |
| utf32_romanian_ci | utf32 | 163 | | Yes | 8 |
| utf32_slovenian_ci | utf32 | 164 | | Yes | 8 |
| utf32_polish_ci | utf32 | 165 | | Yes | 8 |
| utf32_estonian_ci | utf32 | 166 | | Yes | 8 |
| utf32_spanish_ci | utf32 | 167 | | Yes | 8 |
| utf32_swedish_ci | utf32 | 168 | | Yes | 8 |
| utf32_turkish_ci | utf32 | 169 | | Yes | 8 |
| utf32_czech_ci | utf32 | 170 | | Yes | 8 |
| utf32_danish_ci | utf32 | 171 | | Yes | 8 |
| utf32_lithuanian_ci | utf32 | 172 | | Yes | 8 |
| utf32_slovak_ci | utf32 | 173 | | Yes | 8 |
| utf32_spanish2_ci | utf32 | 174 | | Yes | 8 |
| utf32_roman_ci | utf32 | 175 | | Yes | 8 |
| utf32_persian_ci | utf32 | 176 | | Yes | 8 |
| utf32_esperanto_ci | utf32 | 177 | | Yes | 8 |
| utf32_hungarian_ci | utf32 | 178 | | Yes | 8 |
| utf32_sinhala_ci | utf32 | 179 | | Yes | 8 |
| utf32_german2_ci | utf32 | 180 | | Yes | 8 |
| utf32_croatian_mysql561_ci | utf32 | 181 | | Yes | 8 |
| utf32_unicode_520_ci | utf32 | 182 | | Yes | 8 |
| utf32_vietnamese_ci | utf32 | 183 | | Yes | 8 |
| utf32_croatian_ci | utf32 | 736 | | Yes | 8 |
| utf32_myanmar_ci | utf32 | 737 | | Yes | 8 |
| utf32_thai_520_w2 | utf32 | 738 | | Yes | 4 |
| utf32_general_nopad_ci | utf32 | 1084 | | Yes | 1 |
| utf32_nopad_bin | utf32 | 1085 | | Yes | 1 |
| utf32_unicode_nopad_ci | utf32 | 1184 | | Yes | 8 |
| utf32_unicode_520_nopad_ci | utf32 | 1206 | | Yes | 8 |
| binary | binary | 63 | Yes | Yes | 1 |
| geostd8_general_ci | geostd8 | 92 | Yes | Yes | 1 |
| geostd8_bin | geostd8 | 93 | | Yes | 1 |
| geostd8_general_nopad_ci | geostd8 | 1116 | | Yes | 1 |
| geostd8_nopad_bin | geostd8 | 1117 | | Yes | 1 |
| cp932_japanese_ci | cp932 | 95 | Yes | Yes | 1 |
| cp932_bin | cp932 | 96 | | Yes | 1 |
| cp932_japanese_nopad_ci | cp932 | 1119 | | Yes | 1 |
| cp932_nopad_bin | cp932 | 1120 | | Yes | 1 |
| eucjpms_japanese_ci | eucjpms | 97 | Yes | Yes | 1 |
| eucjpms_bin | eucjpms | 98 | | Yes | 1 |
| eucjpms_japanese_nopad_ci | eucjpms | 1121 | | Yes | 1 |
| eucjpms_nopad_bin | eucjpms | 1122 | | Yes | 1 |
+------------------------------+----------+------+---------+----------+---------+
322 rows in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SHOW COLLATION; #查看字符集对应的默认排序规则
12>.查看数据库当前使用的字符集
MariaDB [(none)]> SHOW CREATE DATABASE yinzhengjie;
+-------------+------------------------------------------------------------------------+
| Database | Create Database |
+-------------+------------------------------------------------------------------------+
| yinzhengjie | CREATE DATABASE `yinzhengjie` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+-------------+------------------------------------------------------------------------+
row in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SHOW CREATE DATABASE yinzhengjie;
MariaDB [(none)]> SHOW CREATE DATABASE yinzhengjie2019;
+-----------------+-----------------------------------------------------------------------------+
| Database | Create Database |
+-----------------+-----------------------------------------------------------------------------+
| yinzhengjie2019 | CREATE DATABASE `yinzhengjie2019` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+-----------------+-----------------------------------------------------------------------------+
1 row in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SHOW CREATE DATABASE yinzhengjie2019;
13>.查看表创建命令
MariaDB [yinzhengjie]> SHOW CREATE TABLE students;
+----------+-------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table
|+----------+-------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| students | CREATE TABLE `students` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`sex` enum('boy','girl') DEFAULT 'boy',
`age` tinyint(3) unsigned DEFAULT NULL,
`mobile` char(11) DEFAULT NULL,
`address` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+-------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW CREATE TABLE students;
MariaDB [yinzhengjie]> SHOW CREATE TABLE students\G
*************************** 1. row ***************************
Table: students
Create Table: CREATE TABLE `students` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`sex` enum('boy','girl') DEFAULT 'boy',
`age` tinyint(3) unsigned DEFAULT NULL,
`mobile` char(11) DEFAULT NULL,
`address` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW CREATE TABLE students\G
14>.查看表状态
MariaDB [yinzhengjie]> SHOW TABLE STATUS LIKE 'students';
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-
--------------------+-------------+------------+-------------------+----------+----------------+---------+| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment |
Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-
--------------------+-------------+------------+-------------------+----------+----------------+---------+| students | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | 1 |
2019-10-27 09:23:27 | NULL | NULL | latin1_swedish_ci | NULL | | |+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-
--------------------+-------------+------------+-------------------+----------+----------------+---------+1 row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW TABLE STATUS LIKE 'students';
MariaDB [yinzhengjie]> SHOW TABLE STATUS LIKE 'students'\G
*************************** 1. row ***************************
Name: students
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 1
Create_time: 2019-10-27 09:23:27
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW TABLE STATUS LIKE 'students'\G
MariaDB [yinzhengjie]> SHOW TABLE STATUS FROM mysql; #如果指定的是库名的话就不需要给"mysql"加单引号啦~
+---------------------------+--------+---------+------------+------+----------------+-------------+--------------------+--------------+----------
-+----------------+---------------------+---------------------+---------------------+-------------------+----------+--------------------+---------------------------------------------------+| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free
| Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |+---------------------------+--------+---------+------------+------+----------------+-------------+--------------------+--------------+----------
-+----------------+---------------------+---------------------+---------------------+-------------------+----------+--------------------+---------------------------------------------------+| column_stats | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 4096 | 0
| NULL | 2019-10-26 22:17:15 | 2019-10-26 22:17:15 | NULL | utf8_bin | NULL | | Statistics on Columns || columns_priv | MyISAM | 10 | Fixed | 0 | 0 | 0 | 282037926664077311 | 4096 | 0
| NULL | 2019-10-26 22:17:15 | 2019-10-26 22:17:15 | NULL | utf8_bin | NULL | | Column privileges || db | MyISAM | 10 | Fixed | 0 | 0 | 1264 | 177892185281134591 | 9216 | 1264
| NULL | 2019-10-26 22:17:15 | 2019-10-26 23:43:41 | 2019-10-26 22:17:15 | utf8_bin | NULL | | Database privileges || event | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 2048 | 0
| NULL | 2019-10-26 22:17:15 | 2019-10-26 22:17:15 | NULL | utf8_general_ci | NULL | | Events || func | MyISAM | 10 | Fixed | 0 | 0 | 0 | 162974011515469823 | 1024 | 0
| NULL | 2019-10-26 22:17:15 | 2019-10-26 22:17:15 | NULL | utf8_bin | NULL | | User defined functions || general_log | CSV | 10 | Dynamic | 2 | 0 | 0 | 0 | 0 | 0
| NULL | NULL | NULL | NULL | utf8_general_ci | NULL | | General log || gtid_slave_pos | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0
| NULL | 2019-10-26 23:25:39 | NULL | NULL | latin1_swedish_ci | NULL | | Replication slave GTID position || help_category | MyISAM | 10 | Dynamic | 39 | 28 | 1092 | 281474976710655 | 3072 | 0
| NULL | 2019-10-26 22:17:15 | 2019-10-26 22:17:15 | NULL | utf8_general_ci | NULL | | help categories || help_keyword | MyISAM | 10 | Fixed | 464 | 197 | 91408 | 55450570411999231 | 16384 | 0
| NULL | 2019-10-26 22:17:15 | 2019-10-26 22:17:15 | NULL | utf8_general_ci | NULL | | help keywords || help_relation | MyISAM | 10 | Fixed | 1028 | 9 | 9252 | 2533274790395903 | 19456 | 0
| NULL | 2019-10-26 22:17:15 | 2019-10-26 22:17:15 | NULL | utf8_general_ci | NULL | | keyword-topic relation || help_topic | MyISAM | 10 | Dynamic | 508 | 798 | 405476 | 281474976710655 | 20480 | 0
| NULL | 2019-10-26 22:17:15 | 2019-10-26 22:17:15 | NULL | utf8_general_ci | NULL | | help topics || host | MyISAM | 10 | Fixed | 0 | 0 | 0 | 110056715893866495 | 2048 | 0
| NULL | 2019-10-26 22:17:15 | 2019-10-26 22:17:15 | NULL | utf8_bin | NULL | | Host privileges; Merged with database privileges || index_stats | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 4096 | 0
| NULL | 2019-10-26 22:17:15 | 2019-10-26 22:17:15 | NULL | utf8_bin | NULL | | Statistics on Indexes || innodb_index_stats | InnoDB | 10 | Dynamic | 7 | 2340 | 16384 | 0 | 0 | 0
| NULL | 2019-10-26 23:25:39 | 2019-10-27 09:23:27 | NULL | utf8_bin | NULL | stats_persistent=0 | || innodb_table_stats | InnoDB | 10 | Dynamic | 2 | 8192 | 16384 | 0 | 0 | 0
| NULL | 2019-10-26 23:25:39 | 2019-10-27 09:23:27 | NULL | utf8_bin | NULL | stats_persistent=0 | || plugin | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 1024 | 0
| NULL | 2019-10-26 22:17:15 | 2019-10-26 22:17:15 | NULL | utf8_general_ci | NULL | | MySQL plugins || proc | MyISAM | 10 | Dynamic | 2 | 564 | 1128 | 281474976710655 | 4096 | 0
| NULL | 2019-10-26 22:17:15 | 2019-10-26 22:17:15 | NULL | utf8_general_ci | NULL | | Stored Procedures || procs_priv | MyISAM | 10 | Fixed | 0 | 0 | 0 | 347340121260949503 | 4096 | 0
| NULL | 2019-10-26 22:17:15 | 2019-10-26 22:17:15 | NULL | utf8_bin | NULL | | Procedure privileges || proxies_priv | MyISAM | 10 | Fixed | 2 | 1269 | 2538 | 357191745445822463 | 10240 | 0
| NULL | 2019-10-26 22:17:15 | 2019-10-26 22:17:15 | 2019-10-26 22:17:15 | utf8_bin | NULL | | User proxy privileges || roles_mapping | MyISAM | 10 | Fixed | 0 | 0 | 0 | 186336434582454271 | 4096 | 0
| NULL | 2019-10-26 22:17:15 | 2019-10-26 22:17:15 | NULL | utf8_bin | NULL | | Granted roles || servers | MyISAM | 10 | Fixed | 0 | 0 | 0 | 447263737993232383 | 1024 | 0
| NULL | 2019-10-26 22:17:15 | 2019-10-26 22:17:15 | NULL | utf8_general_ci | NULL | | MySQL Foreign Servers table || slow_log | CSV | 10 | Dynamic | 2 | 0 | 0 | 0 | 0 | 0
| NULL | NULL | NULL | NULL | utf8_general_ci | NULL | | Slow log || table_stats | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 2048 | 0
| NULL | 2019-10-26 22:17:15 | 2019-10-26 22:17:15 | NULL | utf8_bin | NULL | | Statistics on Tables || tables_priv | MyISAM | 10 | Fixed | 0 | 0 | 0 | 347621596237660159 | 4096 | 0
| NULL | 2019-10-26 22:17:15 | 2019-10-26 22:17:15 | NULL | utf8_bin | NULL | | Table privileges || time_zone | MyISAM | 10 | Fixed | 0 | 0 | 0 | 1970324836974591 | 1024 | 0
| 1 | 2019-10-26 22:17:15 | 2019-10-26 22:17:15 | NULL | utf8_general_ci | NULL | | Time zones || time_zone_leap_second | MyISAM | 10 | Fixed | 0 | 0 | 0 | 3659174697238527 | 1024 | 0
| NULL | 2019-10-26 22:17:15 | 2019-10-26 22:17:15 | NULL | utf8_general_ci | NULL | | Leap seconds information for time zones || time_zone_name | MyISAM | 10 | Fixed | 0 | 0 | 0 | 55450570411999231 | 1024 | 0
| NULL | 2019-10-26 22:17:15 | 2019-10-26 22:17:15 | NULL | utf8_general_ci | NULL | | Time zone names || time_zone_transition | MyISAM | 10 | Fixed | 0 | 0 | 0 | 4785074604081151 | 1024 | 0
| NULL | 2019-10-26 22:17:15 | 2019-10-26 22:17:15 | NULL | utf8_general_ci | NULL | | Time zone transitions || time_zone_transition_type | MyISAM | 10 | Fixed | 0 | 0 | 0 | 10696049115004927 | 1024 | 0
| NULL | 2019-10-26 22:17:15 | 2019-10-26 22:17:15 | NULL | utf8_general_ci | NULL | | Time zone transition types || user | MyISAM | 10 | Dynamic | 3 | 113 | 608 | 281474976710655 | 4096 | 268
| NULL | 2019-10-26 22:17:15 | 2019-10-26 23:43:38 | NULL | utf8_bin | NULL | | Users and global privileges |+---------------------------+--------+---------+------------+------+----------------+-------------+--------------------+--------------+----------
-+----------------+---------------------+---------------------+---------------------+-------------------+----------+--------------------+---------------------------------------------------+30 rows in set (0.01 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW TABLE STATUS FROM mysql; #如果指定的是库名的话就不需要给"mysql"加单引号啦~
MariaDB [yinzhengjie]> SHOW TABLE STATUS FROM mysql\G
*************************** 1. row ***************************
Name: column_stats
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 281474976710655
Index_length: 4096
Data_free: 0
Auto_increment: NULL
Create_time: 2019-10-26 22:17:15
Update_time: 2019-10-26 22:17:15
Check_time: NULL
Collation: utf8_bin
Checksum: NULL
Create_options:
Comment: Statistics on Columns
*************************** 2. row ***************************
Name: columns_priv
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 282037926664077311
Index_length: 4096
Data_free: 0
Auto_increment: NULL
Create_time: 2019-10-26 22:17:15
Update_time: 2019-10-26 22:17:15
Check_time: NULL
Collation: utf8_bin
Checksum: NULL
Create_options:
Comment: Column privileges
*************************** 3. row ***************************
Name: db
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 1264
Max_data_length: 177892185281134591
Index_length: 9216
Data_free: 1264
Auto_increment: NULL
Create_time: 2019-10-26 22:17:15
Update_time: 2019-10-26 23:43:41
Check_time: 2019-10-26 22:17:15
Collation: utf8_bin
Checksum: NULL
Create_options:
Comment: Database privileges
*************************** 4. row ***************************
Name: event
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 281474976710655
Index_length: 2048
Data_free: 0
Auto_increment: NULL
Create_time: 2019-10-26 22:17:15
Update_time: 2019-10-26 22:17:15
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment: Events
*************************** 5. row ***************************
Name: func
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 162974011515469823
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2019-10-26 22:17:15
Update_time: 2019-10-26 22:17:15
Check_time: NULL
Collation: utf8_bin
Checksum: NULL
Create_options:
Comment: User defined functions
*************************** 6. row ***************************
Name: general_log
Engine: CSV
Version: 10
Row_format: Dynamic
Rows: 2
Avg_row_length: 0
Data_length: 0
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment: General log
*************************** 7. row ***************************
Name: gtid_slave_pos
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2019-10-26 23:25:39
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment: Replication slave GTID position
*************************** 8. row ***************************
Name: help_category
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 39
Avg_row_length: 28
Data_length: 1092
Max_data_length: 281474976710655
Index_length: 3072
Data_free: 0
Auto_increment: NULL
Create_time: 2019-10-26 22:17:15
Update_time: 2019-10-26 22:17:15
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment: help categories
*************************** 9. row ***************************
Name: help_keyword
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 464
Avg_row_length: 197
Data_length: 91408
Max_data_length: 55450570411999231
Index_length: 16384
Data_free: 0
Auto_increment: NULL
Create_time: 2019-10-26 22:17:15
Update_time: 2019-10-26 22:17:15
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment: help keywords
*************************** 10. row ***************************
Name: help_relation
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 1028
Avg_row_length: 9
Data_length: 9252
Max_data_length: 2533274790395903
Index_length: 19456
Data_free: 0
Auto_increment: NULL
Create_time: 2019-10-26 22:17:15
Update_time: 2019-10-26 22:17:15
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment: keyword-topic relation
*************************** 11. row ***************************
Name: help_topic
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 508
Avg_row_length: 798
Data_length: 405476
Max_data_length: 281474976710655
Index_length: 20480
Data_free: 0
Auto_increment: NULL
Create_time: 2019-10-26 22:17:15
Update_time: 2019-10-26 22:17:15
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment: help topics
*************************** 12. row ***************************
Name: host
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 110056715893866495
Index_length: 2048
Data_free: 0
Auto_increment: NULL
Create_time: 2019-10-26 22:17:15
Update_time: 2019-10-26 22:17:15
Check_time: NULL
Collation: utf8_bin
Checksum: NULL
Create_options:
Comment: Host privileges; Merged with database privileges
*************************** 13. row ***************************
Name: index_stats
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 281474976710655
Index_length: 4096
Data_free: 0
Auto_increment: NULL
Create_time: 2019-10-26 22:17:15
Update_time: 2019-10-26 22:17:15
Check_time: NULL
Collation: utf8_bin
Checksum: NULL
Create_options:
Comment: Statistics on Indexes
*************************** 14. row ***************************
Name: innodb_index_stats
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 7
Avg_row_length: 2340
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2019-10-26 23:25:39
Update_time: 2019-10-27 09:23:27
Check_time: NULL
Collation: utf8_bin
Checksum: NULL
Create_options: stats_persistent=0
Comment:
*************************** 15. row ***************************
Name: innodb_table_stats
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 2
Avg_row_length: 8192
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2019-10-26 23:25:39
Update_time: 2019-10-27 09:23:27
Check_time: NULL
Collation: utf8_bin
Checksum: NULL
Create_options: stats_persistent=0
Comment:
*************************** 16. row ***************************
Name: plugin
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 281474976710655
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2019-10-26 22:17:15
Update_time: 2019-10-26 22:17:15
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment: MySQL plugins
*************************** 17. row ***************************
Name: proc
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 2
Avg_row_length: 564
Data_length: 1128
Max_data_length: 281474976710655
Index_length: 4096
Data_free: 0
Auto_increment: NULL
Create_time: 2019-10-26 22:17:15
Update_time: 2019-10-26 22:17:15
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment: Stored Procedures
*************************** 18. row ***************************
Name: procs_priv
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 347340121260949503
Index_length: 4096
Data_free: 0
Auto_increment: NULL
Create_time: 2019-10-26 22:17:15
Update_time: 2019-10-26 22:17:15
Check_time: NULL
Collation: utf8_bin
Checksum: NULL
Create_options:
Comment: Procedure privileges
*************************** 19. row ***************************
Name: proxies_priv
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 2
Avg_row_length: 1269
Data_length: 2538
Max_data_length: 357191745445822463
Index_length: 10240
Data_free: 0
Auto_increment: NULL
Create_time: 2019-10-26 22:17:15
Update_time: 2019-10-26 22:17:15
Check_time: 2019-10-26 22:17:15
Collation: utf8_bin
Checksum: NULL
Create_options:
Comment: User proxy privileges
*************************** 20. row ***************************
Name: roles_mapping
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 186336434582454271
Index_length: 4096
Data_free: 0
Auto_increment: NULL
Create_time: 2019-10-26 22:17:15
Update_time: 2019-10-26 22:17:15
Check_time: NULL
Collation: utf8_bin
Checksum: NULL
Create_options:
Comment: Granted roles
*************************** 21. row ***************************
Name: servers
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 447263737993232383
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2019-10-26 22:17:15
Update_time: 2019-10-26 22:17:15
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment: MySQL Foreign Servers table
*************************** 22. row ***************************
Name: slow_log
Engine: CSV
Version: 10
Row_format: Dynamic
Rows: 2
Avg_row_length: 0
Data_length: 0
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment: Slow log
*************************** 23. row ***************************
Name: table_stats
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 281474976710655
Index_length: 2048
Data_free: 0
Auto_increment: NULL
Create_time: 2019-10-26 22:17:15
Update_time: 2019-10-26 22:17:15
Check_time: NULL
Collation: utf8_bin
Checksum: NULL
Create_options:
Comment: Statistics on Tables
*************************** 24. row ***************************
Name: tables_priv
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 347621596237660159
Index_length: 4096
Data_free: 0
Auto_increment: NULL
Create_time: 2019-10-26 22:17:15
Update_time: 2019-10-26 22:17:15
Check_time: NULL
Collation: utf8_bin
Checksum: NULL
Create_options:
Comment: Table privileges
*************************** 25. row ***************************
Name: time_zone
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 1970324836974591
Index_length: 1024
Data_free: 0
Auto_increment: 1
Create_time: 2019-10-26 22:17:15
Update_time: 2019-10-26 22:17:15
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment: Time zones
*************************** 26. row ***************************
Name: time_zone_leap_second
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 3659174697238527
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2019-10-26 22:17:15
Update_time: 2019-10-26 22:17:15
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment: Leap seconds information for time zones
*************************** 27. row ***************************
Name: time_zone_name
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 55450570411999231
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2019-10-26 22:17:15
Update_time: 2019-10-26 22:17:15
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment: Time zone names
*************************** 28. row ***************************
Name: time_zone_transition
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 4785074604081151
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2019-10-26 22:17:15
Update_time: 2019-10-26 22:17:15
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment: Time zone transitions
*************************** 29. row ***************************
Name: time_zone_transition_type
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 10696049115004927
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2019-10-26 22:17:15
Update_time: 2019-10-26 22:17:15
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment: Time zone transition types
*************************** 30. row ***************************
Name: user
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 3
Avg_row_length: 113
Data_length: 608
Max_data_length: 281474976710655
Index_length: 4096
Data_free: 268
Auto_increment: NULL
Create_time: 2019-10-26 22:17:15
Update_time: 2019-10-26 23:43:38
Check_time: NULL
Collation: utf8_bin
Checksum: NULL
Create_options:
Comment: Users and global privileges
30 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW TABLE STATUS FROM mysql\G
15>.查看表上的索引
MariaDB [yinzhengjie]> SHOW INDEXES FROM students;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+----
-----------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Ind
ex_comment |+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+----
-----------+| students | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | |
|+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+----
-----------+1 row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW INDEXES FROM students;
MariaDB [yinzhengjie]> SHOW INDEXES FROM students\G
*************************** 1. row ***************************
Table: students
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW INDEXES FROM students\G
16>.查看一张表中有多少行数据
MariaDB [yinzhengjie]> SELECT COUNT(*) FROM students;
+----------+
| COUNT(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT COUNT(*) FROM students;
17>.查看表中个别字段并为其设置别名显示
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+-----------+------+------+--------+---------+
| id | name | sex | age | mobile | adress |
+----+-----------+------+------+--------+---------+
| 1 | Jason Yin | boy | 27 | 10000 | beijing |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
+----+-----------+------+------+--------+---------+
2 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT name AS 姓名 ,age 年龄, adress AS 地址 FROM students;
+-----------+--------+---------+
| 姓名 | 年龄 | 地址 |
+-----------+--------+---------+
| Jason Yin | 27 | beijing |
| Jay | 40 | Taiwan |
+-----------+--------+---------+
2 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT name AS 姓名 ,age 年龄, adress AS 地址 FROM students;
18>.对查询的某列数据去重
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+---------+-----------+
| id | name | sex | age | mobile | adress |
+----+---------------------+------+------+---------+-----------+
| 1 | Jason Yin | boy | 27 | 10000 | beijing |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
| 5 | Gloria Tang Tsz-Kei | girl | 28 | 7474741 | Hong Kong |
| 6 | 刘亦菲 | girl | 32 | NULL | 湖北 |
| 7 | 汪苏泷 | boy | 31 | 5201314 | 辽宁 |
| 8 | 张娜拉 | girl | 38 | NULL | 首尔 |
+----+---------------------+------+------+---------+-----------+
6 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT DISTINCT sex FROM students;
+------+
| sex |
+------+
| boy |
| girl |
+------+
2 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT DISTINCT sex FROM students;
19>.LIKE语句
%:
任意长度的任意字符 _:
任意单个字符
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+---------+-----------+
| id | name | sex | age | mobile | adress |
+----+---------------------+------+------+---------+-----------+
| 1 | Jason Yin | boy | 27 | 10000 | beijing |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
| 5 | Gloria Tang Tsz-Kei | girl | 28 | 7474741 | Hong Kong |
| 6 | 刘亦菲 | girl | 32 | NULL | 湖北 |
| 7 | 汪苏泷 | boy | 31 | 5201314 | 辽宁 |
| 8 | 张娜拉 | girl | 38 | NULL | 首尔 |
+----+---------------------+------+------+---------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students WHERE name LIKE 'J%';
+----+-----------+------+------+--------+---------+
| id | name | sex | age | mobile | adress |
+----+-----------+------+------+--------+---------+
| 1 | Jason Yin | boy | 27 | 10000 | beijing |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
+----+-----------+------+------+--------+---------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students WHERE name LIKE 'J%'; #查询name这一列以为字符"J"开头的行
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+---------+-----------+
| id | name | sex | age | mobile | adress |
+----+---------------------+------+------+---------+-----------+
| 1 | Jason Yin | boy | 27 | 10000 | beijing |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
| 5 | Gloria Tang Tsz-Kei | girl | 28 | 7474741 | Hong Kong |
| 6 | 刘亦菲 | girl | 32 | NULL | 湖北 |
| 7 | 汪苏泷 | boy | 31 | 5201314 | 辽宁 |
| 8 | 张娜拉 | girl | 38 | NULL | 首尔 |
+----+---------------------+------+------+---------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students WHERE name LIKE '%a%';
+----+---------------------+------+------+---------+-----------+
| id | name | sex | age | mobile | adress |
+----+---------------------+------+------+---------+-----------+
| 1 | Jason Yin | boy | 27 | 10000 | beijing |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
| 5 | Gloria Tang Tsz-Kei | girl | 28 | 7474741 | Hong Kong |
+----+---------------------+------+------+---------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students WHERE name LIKE '%a%'; #查询name这一列包含字符"a"的行
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+---------+-----------+
| id | name | sex | age | mobile | adress |
+----+---------------------+------+------+---------+-----------+
| 1 | Jason Yin | boy | 27 | 10000 | beijing |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
| 5 | Gloria Tang Tsz-Kei | girl | 28 | 7474741 | Hong Kong |
| 6 | 刘亦菲 | girl | 32 | NULL | 湖北 |
| 7 | 汪苏泷 | boy | 31 | 5201314 | 辽宁 |
| 8 | 张娜拉 | girl | 38 | NULL | 首尔 |
+----+---------------------+------+------+---------+-----------+
6 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students WHERE name LIKE '_ay';
+----+------+------+------+--------+--------+
| id | name | sex | age | mobile | adress |
+----+------+------+------+--------+--------+
| 2 | Jay | boy | 40 | 10086 | Taiwan |
+----+------+------+------+--------+--------+
1 row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students WHERE name LIKE '_ay';
20>.RLIKE语句
RLIKE：
正则表达式，索引失效，不建议使用
REGEXP：
匹配字符串可用正则表达式书写模式，同上
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+---------+-----------+
| id | name | sex | age | mobile | adress |
+----+---------------------+------+------+---------+-----------+
| 1 | Jason Yin | boy | 27 | 10000 | beijing |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
| 5 | Gloria Tang Tsz-Kei | girl | 28 | 7474741 | Hong Kong |
| 6 | 刘亦菲 | girl | 32 | NULL | 湖北 |
| 7 | 汪苏泷 | boy | 31 | 5201314 | 辽宁 |
| 8 | 张娜拉 | girl | 38 | NULL | 首尔 |
+----+---------------------+------+------+---------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students WHERE name RLIKE '^J';
+----+-----------+------+------+--------+---------+
| id | name | sex | age | mobile | adress |
+----+-----------+------+------+--------+---------+
| 1 | Jason Yin | boy | 27 | 10000 | beijing |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
+----+-----------+------+------+--------+---------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students WHERE name RLIKE '^J'; #使用正则表达式查询以字符"J"开头的行
21>.WHERE子句
指明过滤条件(布尔表达式)以实现“选择”的功能：
算术操作符：
01.+
02.-
03.*
04./
05.%
比较操作符：
01.=
02.<=>（相等或都为空）
03.<>
04.!=(非标准SQL)
05.>
06.>=
07.<
08.<=
09.BETWEEN min_num AND max_num
10.IN (element1, element2, ...)
11.IS NULL
12.IS NOT NULL
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+---------+-----------+
| id | name | sex | age | mobile | adress |
+----+---------------------+------+------+---------+-----------+
| 1 | Jason Yin | boy | 27 | 10000 | beijing |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
| 5 | Gloria Tang Tsz-Kei | girl | 28 | 7474741 | Hong Kong |
| 6 | 刘亦菲 | girl | 32 | NULL | 湖北 |
| 7 | 汪苏泷 | boy | 31 | 5201314 | 辽宁 |
| 8 | 张娜拉 | girl | 38 | NULL | 首尔 |
+----+---------------------+------+------+---------+-----------+
6 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students WHERE sex != 'boy';
+----+---------------------+------+------+---------+-----------+
| id | name | sex | age | mobile | adress |
+----+---------------------+------+------+---------+-----------+
| 5 | Gloria Tang Tsz-Kei | girl | 28 | 7474741 | Hong Kong |
| 6 | 刘亦菲 | girl | 32 | NULL | 湖北 |
| 8 | 张娜拉 | girl | 38 | NULL | 首尔 |
+----+---------------------+------+------+---------+-----------+
3 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students WHERE sex != 'boy';
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+---------+-----------+
| id | name | sex | age | mobile | adress |
+----+---------------------+------+------+---------+-----------+
| 1 | Jason Yin | boy | 27 | 10000 | beijing |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
| 5 | Gloria Tang Tsz-Kei | girl | 28 | 7474741 | Hong Kong |
| 6 | 刘亦菲 | girl | 32 | NULL | 湖北 |
| 7 | 汪苏泷 | boy | 31 | 5201314 | 辽宁 |
| 8 | 张娜拉 | girl | 38 | NULL | 首尔 |
+----+---------------------+------+------+---------+-----------+
6 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students WHERE sex <> 'boy';
+----+---------------------+------+------+---------+-----------+
| id | name | sex | age | mobile | adress |
+----+---------------------+------+------+---------+-----------+
| 5 | Gloria Tang Tsz-Kei | girl | 28 | 7474741 | Hong Kong |
| 6 | 刘亦菲 | girl | 32 | NULL | 湖北 |
| 8 | 张娜拉 | girl | 38 | NULL | 首尔 |
+----+---------------------+------+------+---------+-----------+
3 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students WHERE sex <> 'boy';
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+---------+-----------+
| id | name | sex | age | mobile | adress |
+----+---------------------+------+------+---------+-----------+
| 1 | Jason Yin | boy | 27 | 10000 | beijing |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
| 5 | Gloria Tang Tsz-Kei | girl | 28 | 7474741 | Hong Kong |
| 6 | 刘亦菲 | girl | 32 | NULL | 湖北 |
| 7 | 汪苏泷 | boy | 31 | 5201314 | 辽宁 |
| 8 | 张娜拉 | girl | 38 | NULL | 首尔 |
+----+---------------------+------+------+---------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students WHERE mobile is null;
+----+-----------+------+------+--------+--------+
| id | name | sex | age | mobile | adress |
+----+-----------+------+------+--------+--------+
| 6 | 刘亦菲 | girl | 32 | NULL | 湖北 |
| 8 | 张娜拉 | girl | 38 | NULL | 首尔 |
+----+-----------+------+------+--------+--------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students WHERE mobile is null; #查询mobile这一列为空的行
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+---------+-----------+
| id | name | sex | age | mobile | adress |
+----+---------------------+------+------+---------+-----------+
| 1 | Jason Yin | boy | 27 | 10000 | beijing |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
| 5 | Gloria Tang Tsz-Kei | girl | 28 | 7474741 | Hong Kong |
| 6 | 刘亦菲 | girl | 32 | NULL | 湖北 |
| 7 | 汪苏泷 | boy | 31 | 5201314 | 辽宁 |
| 8 | 张娜拉 | girl | 38 | NULL | 首尔 |
+----+---------------------+------+------+---------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students WHERE mobile is not null;
+----+---------------------+------+------+---------+-----------+
| id | name | sex | age | mobile | adress |
+----+---------------------+------+------+---------+-----------+
| 1 | Jason Yin | boy | 27 | 10000 | beijing |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
| 5 | Gloria Tang Tsz-Kei | girl | 28 | 7474741 | Hong Kong |
| 7 | 汪苏泷 | boy | 31 | 5201314 | 辽宁 |
+----+---------------------+------+------+---------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students WHERE mobile is not null; #查询mobile这一列不为空的行
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+---------+-----------+
| id | name | sex | age | mobile | adress |
+----+---------------------+------+------+---------+-----------+
| 1 | Jason Yin | boy | 27 | 10000 | beijing |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
| 5 | Gloria Tang Tsz-Kei | girl | 28 | 7474741 | Hong Kong |
| 6 | 刘亦菲 | girl | 32 | NULL | 湖北 |
| 7 | 汪苏泷 | boy | 31 | 5201314 | 辽宁 |
| 8 | 张娜拉 | girl | 38 | NULL | 首尔 |
+----+---------------------+------+------+---------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students WHERE age = 28 or age = 38;
+----+---------------------+------+------+---------+-----------+
| id | name | sex | age | mobile | adress |
+----+---------------------+------+------+---------+-----------+
| 5 | Gloria Tang Tsz-Kei | girl | 28 | 7474741 | Hong Kong |
| 8 | 张娜拉 | girl | 38 | NULL | 首尔 |
+----+---------------------+------+------+---------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students WHERE age IN (28,38);
+----+---------------------+------+------+---------+-----------+
| id | name | sex | age | mobile | adress |
+----+---------------------+------+------+---------+-----------+
| 5 | Gloria Tang Tsz-Kei | girl | 28 | 7474741 | Hong Kong |
| 8 | 张娜拉 | girl | 38 | NULL | 首尔 |
+----+---------------------+------+------+---------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students WHERE age IN (28,38); #查询age=28或者age=38的行
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+---------+-----------+
| id | name | sex | age | mobile | adress |
+----+---------------------+------+------+---------+-----------+
| 1 | Jason Yin | boy | 27 | 10000 | beijing |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
| 5 | Gloria Tang Tsz-Kei | girl | 28 | 7474741 | Hong Kong |
| 6 | 刘亦菲 | girl | 32 | NULL | 湖北 |
| 7 | 汪苏泷 | boy | 31 | 5201314 | 辽宁 |
| 8 | 张娜拉 | girl | 38 | NULL | 首尔 |
+----+---------------------+------+------+---------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students WHERE age >= 25 and age <= 28;
+----+---------------------+------+------+---------+-----------+
| id | name | sex | age | mobile | adress |
+----+---------------------+------+------+---------+-----------+
| 1 | Jason Yin | boy | 27 | 10000 | beijing |
| 5 | Gloria Tang Tsz-Kei | girl | 28 | 7474741 | Hong Kong |
+----+---------------------+------+------+---------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students WHERE age BETWEEN 25 AND 28;
+----+---------------------+------+------+---------+-----------+
| id | name | sex | age | mobile | adress |
+----+---------------------+------+------+---------+-----------+
| 1 | Jason Yin | boy | 27 | 10000 | beijing |
| 5 | Gloria Tang Tsz-Kei | girl | 28 | 7474741 | Hong Kong |
+----+---------------------+------+------+---------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students WHERE age BETWEEN 25 AND 28; #查询age在25-28之间的行
MariaDB [yinzhengjie]> SELECT 10*30;
+-------+
| 10*30 |
+-------+
| 300 |
+-------+
row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT 10*30;
22>.逻辑操作符
01.NOT
02.AND
03.OR
04.XOR
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+---------+-----------+
| id | name | sex | age | mobile | adress |
+----+---------------------+------+------+---------+-----------+
| 1 | Jason Yin | boy | 27 | 10000 | beijing |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
| 5 | Gloria Tang Tsz-Kei | girl | 28 | 7474741 | Hong Kong |
| 6 | 刘亦菲 | girl | 32 | NULL | 湖北 |
| 7 | 汪苏泷 | boy | 31 | 5201314 | 辽宁 |
| 8 | 张娜拉 | girl | 38 | NULL | 首尔 |
+----+---------------------+------+------+---------+-----------+
6 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students WHERE NOT name RLIKE '^J';
+----+---------------------+------+------+---------+-----------+
| id | name | sex | age | mobile | adress |
+----+---------------------+------+------+---------+-----------+
| 5 | Gloria Tang Tsz-Kei | girl | 28 | 7474741 | Hong Kong |
| 6 | 刘亦菲 | girl | 32 | NULL | 湖北 |
| 7 | 汪苏泷 | boy | 31 | 5201314 | 辽宁 |
| 8 | 张娜拉 | girl | 38 | NULL | 首尔 |
+----+---------------------+------+------+---------+-----------+
4 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students WHERE NOT name RLIKE '^J';
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+---------+-----------+
| id | name | sex | age | mobile | adress |
+----+---------------------+------+------+---------+-----------+
| 1 | Jason Yin | boy | 27 | 10000 | beijing |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
| 5 | Gloria Tang Tsz-Kei | girl | 28 | 7474741 | Hong Kong |
| 6 | 刘亦菲 | girl | 32 | NULL | 湖北 |
| 7 | 汪苏泷 | boy | 31 | 5201314 | 辽宁 |
| 8 | 张娜拉 | girl | 38 | NULL | 首尔 |
+----+---------------------+------+------+---------+-----------+
6 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students WHERE age = 28 OR age = 38;
+----+---------------------+------+------+---------+-----------+
| id | name | sex | age | mobile | adress |
+----+---------------------+------+------+---------+-----------+
| 5 | Gloria Tang Tsz-Kei | girl | 28 | 7474741 | Hong Kong |
| 8 | 张娜拉 | girl | 38 | NULL | 首尔 |
+----+---------------------+------+------+---------+-----------+
2 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students WHERE age = 28 OR age = 38;
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+---------+-----------+
| id | name | sex | age | mobile | adress |
+----+---------------------+------+------+---------+-----------+
| 1 | Jason Yin | boy | 27 | 10000 | beijing |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
| 5 | Gloria Tang Tsz-Kei | girl | 28 | 7474741 | Hong Kong |
| 6 | 刘亦菲 | girl | 32 | NULL | 湖北 |
| 7 | 汪苏泷 | boy | 31 | 5201314 | 辽宁 |
| 8 | 张娜拉 | girl | 38 | NULL | 首尔 |
+----+---------------------+------+------+---------+-----------+
6 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students WHERE age = 28 XOR age = 38;
+----+---------------------+------+------+---------+-----------+
| id | name | sex | age | mobile | adress |
+----+---------------------+------+------+---------+-----------+
| 5 | Gloria Tang Tsz-Kei | girl | 28 | 7474741 | Hong Kong |
| 8 | 张娜拉 | girl | 38 | NULL | 首尔 |
+----+---------------------+------+------+---------+-----------+
2 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students WHERE age = 28 XOR age = 38;
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+---------+-----------+
| id | name | sex | age | mobile | adress |
+----+---------------------+------+------+---------+-----------+
| 1 | Jason Yin | boy | 27 | 10000 | beijing |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
| 5 | Gloria Tang Tsz-Kei | girl | 28 | 7474741 | Hong Kong |
| 6 | 刘亦菲 | girl | 32 | NULL | 湖北 |
| 7 | 汪苏泷 | boy | 31 | 5201314 | 辽宁 |
| 8 | 张娜拉 | girl | 38 | NULL | 首尔 |
+----+---------------------+------+------+---------+-----------+
6 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students WHERE age = 27 AND name = 'Jason Yin';
+----+-----------+------+------+--------+---------+
| id | name | sex | age | mobile | adress |
+----+-----------+------+------+--------+---------+
| 1 | Jason Yin | boy | 27 | 10000 | beijing |
+----+-----------+------+------+--------+---------+
1 row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students WHERE age = 27 AND name = 'Jason Yin';
23>.GROUP
根据指定的条件把查询结果进行“分组” 以用于做“聚合”运算
avg(), max(), min(), count(), sum()
HAVING:
对分组聚合运算后的结果指定过滤条件
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+---------+-----------+
| id | name | sex | age | mobile | adress |
+----+---------------------+------+------+---------+-----------+
| 1 | Jason Yin | boy | 27 | 10000 | beijing |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
| 5 | Gloria Tang Tsz-Kei | girl | 28 | 7474741 | Hong Kong |
| 6 | 刘亦菲 | girl | 32 | NULL | 湖北 |
| 7 | 汪苏泷 | boy | 31 | 5201314 | 辽宁 |
| 8 | 张娜拉 | girl | 38 | NULL | 首尔 |
+----+---------------------+------+------+---------+-----------+
6 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT sex AS 性别,COUNT(*) AS 人数 FROM students GROUP BY sex;
+--------+--------+
| 性别 | 人数 |
+--------+--------+
| boy | 3 |
| girl | 3 |
+--------+--------+
2 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT sex AS 性别,COUNT(*) AS 人数 FROM students GROUP BY sex; #统计男生和女生的人数
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+---------+-----------+
| id | name | sex | age | mobile | adress |
+----+---------------------+------+------+---------+-----------+
| 1 | Jason Yin | boy | 18 | 10000 | beijing |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
| 5 | Gloria Tang Tsz-Kei | girl | 28 | 7474741 | Hong Kong |
| 6 | 刘亦菲 | girl | 32 | NULL | 湖北 |
| 7 | 汪苏泷 | boy | 31 | 5201314 | 辽宁 |
| 8 | 张娜拉 | girl | 38 | NULL | 首尔 |
+----+---------------------+------+------+---------+-----------+
6 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT sex AS 性别,AVG(age) AS 平均年龄 FROM students GROUP BY sex;
+--------+--------------+
| 性别 | 平均年龄 |
+--------+--------------+
| boy | 29.6667 |
| girl | 32.6667 |
+--------+--------------+
2 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT sex AS 性别,AVG(age) AS 平均年龄 FROM students GROUP BY sex; #统计男生和女生的平均年龄
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+---------+-----------+
| id | name | sex | age | mobile | adress |
+----+---------------------+------+------+---------+-----------+
| 1 | Jason Yin | boy | 18 | 10000 | beijing |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
| 5 | Gloria Tang Tsz-Kei | girl | 28 | 7474741 | Hong Kong |
| 6 | 刘亦菲 | girl | 32 | NULL | 湖北 |
| 7 | 汪苏泷 | boy | 31 | 5201314 | 辽宁 |
| 8 | 张娜拉 | girl | 38 | NULL | 首尔 |
+----+---------------------+------+------+---------+-----------+
6 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT sex AS 性别,AVG(age) AS 平均年龄 FROM students GROUP BY sex HAVING sex = 'boy';
+--------+--------------+
| 性别 | 平均年龄 |
+--------+--------------+
| boy | 29.6667 |
+--------+--------------+
1 row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT sex AS 性别,AVG(age) AS 平均年龄 FROM students GROUP BY sex HAVING sex = 'boy';
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+---------+-----------+
| id | name | sex | age | mobile | adress |
+----+---------------------+------+------+---------+-----------+
| 1 | Jason Yin | boy | 18 | 10000 | beijing |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
| 5 | Gloria Tang Tsz-Kei | girl | 28 | 7474741 | Hong Kong |
| 6 | 刘亦菲 | girl | 32 | NULL | 湖北 |
| 7 | 汪苏泷 | boy | 31 | 5201314 | 辽宁 |
| 8 | 张娜拉 | girl | 38 | NULL | 首尔 |
+----+---------------------+------+------+---------+-----------+
6 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT sex AS 性别,AVG(age) AS 平均年龄 FROM students WHERE sex = 'boy' GROUP BY sex;
+--------+--------------+
| 性别 | 平均年龄 |
+--------+--------------+
| boy | 29.6667 |
+--------+--------------+
1 row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT sex AS 性别,AVG(age) AS 平均年龄 FROM students WHERE sex = 'boy' GROUP BY sex; #注意和上条SQL之间的区别
24>.ORDER BY
根据指定的字段对查询结果进行排序 升序：
ASC
降序：
DESC
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+---------+-----------+
| id | name | sex | age | mobile | adress |
+----+---------------------+------+------+---------+-----------+
| 1 | Jason Yin | boy | 18 | 10000 | beijing |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
| 5 | Gloria Tang Tsz-Kei | girl | 28 | 7474741 | Hong Kong |
| 6 | 刘亦菲 | girl | 32 | NULL | 湖北 |
| 7 | 汪苏泷 | boy | 31 | 5201314 | 辽宁 |
| 8 | 张娜拉 | girl | 38 | NULL | 首尔 |
+----+---------------------+------+------+---------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students ORDER BY age ASC; #将排序的结果升序序显示
+----+---------------------+------+------+---------+-----------+
| id | name | sex | age | mobile | adress |
+----+---------------------+------+------+---------+-----------+
| 1 | Jason Yin | boy | 18 | 10000 | beijing |
| 5 | Gloria Tang Tsz-Kei | girl | 28 | 7474741 | Hong Kong |
| 7 | 汪苏泷 | boy | 31 | 5201314 | 辽宁 |
| 6 | 刘亦菲 | girl | 32 | NULL | 湖北 |
| 8 | 张娜拉 | girl | 38 | NULL | 首尔 |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
+----+---------------------+------+------+---------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students ORDER BY age; #默认就是将排序的结果升序显示
+----+---------------------+------+------+---------+-----------+
| id | name | sex | age | mobile | adress |
+----+---------------------+------+------+---------+-----------+
| 1 | Jason Yin | boy | 18 | 10000 | beijing |
| 5 | Gloria Tang Tsz-Kei | girl | 28 | 7474741 | Hong Kong |
| 7 | 汪苏泷 | boy | 31 | 5201314 | 辽宁 |
| 6 | 刘亦菲 | girl | 32 | NULL | 湖北 |
| 8 | 张娜拉 | girl | 38 | NULL | 首尔 |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
+----+---------------------+------+------+---------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students ORDER BY age ASC; #将排序的结果升序序显示
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+---------+-----------+
| id | name | sex | age | mobile | adress |
+----+---------------------+------+------+---------+-----------+
| 1 | Jason Yin | boy | 18 | 10000 | beijing |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
| 5 | Gloria Tang Tsz-Kei | girl | 28 | 7474741 | Hong Kong |
| 6 | 刘亦菲 | girl | 32 | NULL | 湖北 |
| 7 | 汪苏泷 | boy | 31 | 5201314 | 辽宁 |
| 8 | 张娜拉 | girl | 38 | NULL | 首尔 |
+----+---------------------+------+------+---------+-----------+
6 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students ORDER BY age DESC; #将排序的结果降序显示
+----+---------------------+------+------+---------+-----------+
| id | name | sex | age | mobile | adress |
+----+---------------------+------+------+---------+-----------+
| 2 | Jay | boy | 40 | 10086 | Taiwan |
| 8 | 张娜拉 | girl | 38 | NULL | 首尔 |
| 6 | 刘亦菲 | girl | 32 | NULL | 湖北 |
| 7 | 汪苏泷 | boy | 31 | 5201314 | 辽宁 |
| 5 | Gloria Tang Tsz-Kei | girl | 28 | 7474741 | Hong Kong |
| 1 | Jason Yin | boy | 18 | 10000 | beijing |
+----+---------------------+------+------+---------+-----------+
6 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students ORDER BY age DESC; #将排序的结果降序显示
25>.LIMIT [[offset,]row_count]
对查询的结果进行输出行数数量限制
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+---------+-----------+
| id | name | sex | age | mobile | adress |
+----+---------------------+------+------+---------+-----------+
| 1 | Jason Yin | boy | 18 | 10000 | beijing |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
| 5 | Gloria Tang Tsz-Kei | girl | 28 | 7474741 | Hong Kong |
| 6 | 刘亦菲 | girl | 32 | NULL | 湖北 |
| 7 | 汪苏泷 | boy | 31 | 5201314 | 辽宁 |
| 8 | 张娜拉 | girl | 38 | NULL | 首尔 |
+----+---------------------+------+------+---------+-----------+
6 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students ORDER BY age DESC;
+----+---------------------+------+------+---------+-----------+
| id | name | sex | age | mobile | adress |
+----+---------------------+------+------+---------+-----------+
| 2 | Jay | boy | 40 | 10086 | Taiwan |
| 8 | 张娜拉 | girl | 38 | NULL | 首尔 |
| 6 | 刘亦菲 | girl | 32 | NULL | 湖北 |
| 7 | 汪苏泷 | boy | 31 | 5201314 | 辽宁 |
| 5 | Gloria Tang Tsz-Kei | girl | 28 | 7474741 | Hong Kong |
| 1 | Jason Yin | boy | 18 | 10000 | beijing |
+----+---------------------+------+------+---------+-----------+
6 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students ORDER BY age DESC LIMIT 3; #查询年龄最大的前3位
+----+-----------+------+------+--------+--------+
| id | name | sex | age | mobile | adress |
+----+-----------+------+------+--------+--------+
| 2 | Jay | boy | 40 | 10086 | Taiwan |
| 8 | 张娜拉 | girl | 38 | NULL | 首尔 |
| 6 | 刘亦菲 | girl | 32 | NULL | 湖北 |
+----+-----------+------+------+--------+--------+
3 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students ORDER BY age DESC LIMIT 3; #查询年龄最大的前3位
MariaDB [yinzhengjie]> SELECT * FROM students;
+----+---------------------+------+------+---------+-----------+
| id | name | sex | age | mobile | adress |
+----+---------------------+------+------+---------+-----------+
| 1 | Jason Yin | boy | 18 | 10000 | beijing |
| 2 | Jay | boy | 40 | 10086 | Taiwan |
| 5 | Gloria Tang Tsz-Kei | girl | 28 | 7474741 | Hong Kong |
| 6 | 刘亦菲 | girl | 32 | NULL | 湖北 |
| 7 | 汪苏泷 | boy | 31 | 5201314 | 辽宁 |
| 8 | 张娜拉 | girl | 38 | NULL | 首尔 |
+----+---------------------+------+------+---------+-----------+
6 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students ORDER BY age DESC;
+----+---------------------+------+------+---------+-----------+
| id | name | sex | age | mobile | adress |
+----+---------------------+------+------+---------+-----------+
| 2 | Jay | boy | 40 | 10086 | Taiwan |
| 8 | 张娜拉 | girl | 38 | NULL | 首尔 |
| 6 | 刘亦菲 | girl | 32 | NULL | 湖北 |
| 7 | 汪苏泷 | boy | 31 | 5201314 | 辽宁 |
| 5 | Gloria Tang Tsz-Kei | girl | 28 | 7474741 | Hong Kong |
| 1 | Jason Yin | boy | 18 | 10000 | beijing |
+----+---------------------+------+------+---------+-----------+
6 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students ORDER BY age DESC LIMIT 3,2; #跳过显示年龄最大的前三个，从第三个开始显示共计显示2个
+----+---------------------+------+------+---------+-----------+
| id | name | sex | age | mobile | adress |
+----+---------------------+------+------+---------+-----------+
| 7 | 汪苏泷 | boy | 31 | 5201314 | 辽宁 |
| 5 | Gloria Tang Tsz-Kei | girl | 28 | 7474741 | Hong Kong |
+----+---------------------+------+------+---------+-----------+
2 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM students ORDER BY age DESC LIMIT 3,2; #跳过显示年龄最大的前三个，从第三个开始显示共计显示2个
26>.对查询结果中的数据请求施加“锁”
FOR UPDATE:
写锁，独占或排它锁，只有一个读和写 LOCK IN SHARE MODE:
读锁，共享锁，同时多个读
27>.查看数据库现有进程
MariaDB [yinzhengjie]> SHOW PROCESSLIST\G
*************************** 1. row ***************************
Id: 1
User: system user
Host:
db: NULL
Command: Daemon
Time: NULL
State: InnoDB purge worker
Info: NULL
Progress: 0.000
*************************** 2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Daemon
Time: NULL
State: InnoDB purge coordinator
Info: NULL
Progress: 0.000
*************************** 3. row ***************************
Id: 3
User: system user
Host:
db: NULL
Command: Daemon
Time: NULL
State: InnoDB purge worker
Info: NULL
Progress: 0.000
*************************** 4. row ***************************
Id: 4
User: system user
Host:
db: NULL
Command: Daemon
Time: NULL
State: InnoDB purge worker
Info: NULL
Progress: 0.000
*************************** 5. row ***************************
Id: 5
User: system user
Host:
db: NULL
Command: Daemon
Time: NULL
State: InnoDB shutdown handler
Info: NULL
Progress: 0.000
*************************** 6. row ***************************
Id: 10
User: root
Host: localhost
db: yinzhengjie
Command: Query
Time: 0
State: init
Info: SHOW PROCESSLIST
Progress: 0.000
6 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW PROCESSLIST\G #如果某张表被别人上锁了，如果你有权限的话查看到进程后可以使用kill干掉哟~