mysql values用法_【mysql基础】04、mysql基本使用

最新推荐文章于 2022-03-15 11:18:11 发布

weixin_39890652

最新推荐文章于 2022-03-15 11:18:11 发布

阅读量1.8k

点赞数

文章标签： mysql values用法

本文链接：https://blog.csdn.net/weixin_39890652/article/details/113594721

版权

一、Mysql客户端使用

1、交互式模式

交互式模式中的命令类别：

mysql常用客户端命令如下：

客户端命令：在客户端执行的命令，不需要以分号结尾，自行获取数据并且将结果返回给当前用户，在客户端执行，可以获取服务端的数据，不需要语句终止符

获取客户端命令的帮助信息：mysql> help

mysql命令本身不区分字符大小写，但是与文件系统相关的部分根据OS的不同可能区分字符的大小写，建议将关键字大写MariaDB [(none)]> \h

General information about MariaDB can be found at

http://mariadb.org

List of all MySQL commands:

Note that all text commands must be first on line and end with ';'

? (\?) Synonym for `help'.

clear (\c) Clear the current input statement.

connect (\r) Reconnect to the server. Optional arguments are db and host.

delimiter (\d) Set statement delimiter.

edit (\e) Edit command with $EDITOR.

ego (\G) Send command to mysql server, display result vertically.

exit (\q) Exit mysql. Same as quit.

go (\g) Send command to mysql server.

help (\h) Display this help.

nopager (\n) Disable pager, print to stdout.

notee (\t) Don't write into outfile.

pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.

print (\p) Print current command.

prompt (\R) Change your mysql prompt.

quit (\q) Quit mysql.

rehash (\#) Rebuild completion hash.

source (\.) Execute an SQL script file. Takes a file name as an argument.

status (\s) Get status information from the server.

system (\!) Execute a system shell command.

tee (\T) Set outfile [to_outfile]. Append everything into given outfile.

use (\u) Use another database. Takes database name as argument.

charset (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.

warnings (\W) Show warnings after every statement.

nowarning (\w) Don't show warnings after every statement.

For server side help, type 'help contents'

？、\?\help或\h 表示获取命令帮助

exit、quit或\q 表示退出mysql

go或\g 表示无论语句的结束符是什么都把语句送到服务器端执行，是在当被修改默认结束符后又不知道的情况下使用

\c 取消命令的执行

\. 导入sql脚本，相当于在shell命令行的"mysql < /path/to/mysql_script.sql"

例如：

mysql> select database()\g #显示当前所在的数据库

mysql> use或\u 数据库名称 #表示设定默认数据库

例如：

mysql> \u test #表示使用test这个数据库

mysql> ego或\G #表示sql命令取回的数据纵向显示

例如：MariaDB [(none)]> select user,host,password from mysql.user;

+------+-----------+-------------------------------------------+

| user | host | password |

+------+-----------+-------------------------------------------+

| root | localhost | *437F1809645E0A92DAB553503D2FE21DB91270FD |

| root | 127.0.0.1 | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |

| root | ::1 | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |

+------+-----------+-------------------------------------------+

3 rows in set (0.00 sec)

MariaDB [(none)]> select user,host,password from mysql.user\g

+------+-----------+-------------------------------------------+

| user | host | password |

+------+-----------+-------------------------------------------+

| root | localhost | *437F1809645E0A92DAB553503D2FE21DB91270FD |

| root | 127.0.0.1 | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |

| root | ::1 | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |

+------+-----------+-------------------------------------------+

3 rows in set (0.00 sec)

MariaDB [(none)]> select user,host,password from mysql.user\g;

+------+-----------+-------------------------------------------+

| user | host | password |

+------+-----------+-------------------------------------------+

| root | localhost | *437F1809645E0A92DAB553503D2FE21DB91270FD |

| root | 127.0.0.1 | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |

| root | ::1 | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |

+------+-----------+-------------------------------------------+

3 rows in set (0.00 sec)

ERROR: No query specified

MariaDB [(none)]> select user,host,password from mysql.user\G

*************************** 1. row ***************************

user: root

host: localhost

password: *437F1809645E0A92DAB553503D2FE21DB91270FD

*************************** 2. row ***************************

user: root

host: 127.0.0.1

password: *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1

*************************** 3. row ***************************

user: root

host: ::1

password: *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1

3 rows in set (0.00 sec)

MariaDB [(none)]>

mysql> system或\! #表示在不退出mysql客户端程序下执行shell命令，这个有点类似在vim中执行shell命令

mysql> status或\s #表示获取当前mysql的状态信息

mysql> delimiter或\d #表示更换语句结束符，也就是可把默认的分号结束符号更换成其他的符号MariaDB [mysql]> status

--------------

mysql Ver 15.1 Distrib 10.1.20-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:97

Current database:mysql

Current user:root@localhost

SSL:Not in use

Current pager:stdout

Using outfile:''

Using delimiter:;

Server:MariaDB

Server version:10.1.20-MariaDB MariaDB Server

Protocol version:10

Connection:Localhost via UNIX socket

Server characterset:latin1

Db characterset:latin1

Client characterset:utf8

Conn. characterset:utf8

UNIX socket:/tmp/mysql.sock

Uptime:22 hours 45 min 42 sec

Threads: 1 Questions: 399 Slow queries: 0 Opens: 38 Flush tables: 1 Open tables: 31 Queries per second avg: 0.004

--------------

MariaDB [mysql]>

常用服务器端命令：

命令在服务器端，需要语句终止符，通常默认为分号(;)，表示语句终止并发送给服务器端执行

mysql> select version(); 显示mysql数据库的版本号执行mysqld的BIF(内建函数)MariaDB [(none)]> select version(); # select可以直接执行函数

+-----------------+

| version() |

+-----------------+

| 10.1.20-MariaDB |

+-----------------+

1 row in set (0.02 sec)

MariaDB [(none)]> select 3+2 # select可以作算术运算

-> ;

+-----+

| 3+2 |

+-----+

| 5 |

+-----+

1 row in set (0.02 sec)

MariaDB [(none)]>

mysql> select col1, col2, ... from tb1，tb2，... where clause;

例如：where col1 > 30 只选择col1中大于30的字段

选取和投影

选取：从表中只选择指定行的操作叫选取

投影：从表中只选择指定字段的操作叫投影

mysql> show databases; 显示所有(自己有权限查看)数据库

mysql> show databases like 't%'; #显示以字母“t”开头的数据库

use database_name 选定当前操作的数据库

show tables 查看所有表

create database database_name 创建库

drop database database_name 删除数据库

mysql> show variables; #显示服务器参数变量，一样可以使用‘‘like’’这样的子句来做模糊查找

mysql> show variables like 'datadir%';

show engines

mysql> show status; #显示服务器状态变量

mysql> show table status\G #查看当前默认数据库中表状态，有“\G”结尾的不要加分号

mysql> show table status [from | in] 数据库\G #查看指定数据库中的表状态

mysql> help create table #获取创建表支持的数据类型

mysql> show character set; #显示mysql所支持的字符集

mysql> show collation; #显示字符集的排序规则

mysql> show processlist; #显示服务器当前所有mysql线程列表

mysql> show indexes from 表名; #显示表中的索引信息

获取服务器端命令的帮助：mysql> help COMMAND

MySQL默认的三个数据库：

information_schema 把mysql运行过程中产生的数据保存在内存中的数据库。(保证兼容)

mysql 存放数据库元数据

test 测试使用数据库

2、批处理模式

执行SQL脚本[root@Node2 ~]# mysql -p123321 -e "select user()" # 使用-e "SQL COMMAND;"

+----------------+

| user() |

+----------------+

| root@localhost |

+----------------+

[root@Node2 ~]# vim a.sql

[root@Node2 ~]# cat a.sql # SQL脚本每行一句，或使用分号分隔

select user();

create database mydb;

show databases;

[root@Node2 ~]# mysql

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

[root@Node2 ~]# mysql -p123321

user()

root@localhost

Database

information_schema

mydb

mysql

performance_schema

[root@Node2 ~]#

二、mysql的数据类型

1、数据类型的作用

决定数据的比较方式、排序方式

决定数据的存储空间、取值范围

决定参与的运算

2、字符型

char(N)：固定长度，N指字符数，不区分大小写，最多支持255个字符

尽量使用char虽然会有空间浪费，但性能好的多

varchar(N)：可变长度，需要结束符，结束符最少占据一个空间。

N指最大字符数，不区分大小写，最多支持65535个字符

binary(N)：固定长度，区分大小写

varbinary(N)：可变长度，区分大小写

text(N)：tinytext,text,mediumtext,longtext大段文本，不区分大小写

blob(N)：tinyblob,blob,mediumblob,longblob 大段文本，区分大小写

3、数值型

1)精确数值型：

int(×××)：tinyint(微，1B)，smallint(小，2B)，mediumint(中，3B)，int(4B)，bigint(大，8B)

decimal(十进制)：按照十进制的格式保存数值

2)近似数值型：浮点型

float(g,f)：单精度

double(g,f)：双精度

4、日期时间型

本质上要么存储为数值要么存储为字符

date 3B

time 3B

datetime 8B

timestamp 时间戳

year(2)，year(4)

5、布尔型

本质就是tinyint：1|0

6、 NULL

什么都没有

7、内置类型

ENUM：枚举型(一定范围内挑一个，字符型) 也属于字符型

SET：集合型(集合内字符任意组合，不能使用集合外的字符)也属于字符型

三、mysql的基本使用

1、mysql的常用术语

键：key

就是选取出来具有特殊目的字段(当作查找、处理标准)

主键：能唯一标识表中每一个记录(没一行的实体)的字段或字段的组合

候选键：能作为主键的字段都叫候选键(可以拿来做主键的字段或字段的组合)

约束：constraint

当填写数据不符合规定时，拒绝填入

主键约束：对一张表来讲，主键只能有一个，且主键数据不能重复出现，主键不能为空(NULL)

唯一键约束：可以为NULL，可以有多个，唯一键数据不能重复出现

外键约束：降低冗余

检查式约束：用户自定义有效取值范围，(限制数据的取值范围)

主键(primary key) 能够唯一标识表中某一行的属性或属性组。一个表只能有一个主键，但可以有多个候选索引。主键常常与外键构成参照完整性约束，防止出现数据不一致。主键可以保证记录的唯一和主键域非空,数据库管理系统对于主键自动生成唯一索引，所以主键也是一个特殊的索引。

外键(foreign key) 是用于建立和加强两个表数据之间的链接的一列或多列。外键约束主要用来维护两个表之间数据的一致性。简言之，表的外键就是另一表的主键，外键将两表联系起来。一般情况下，要删除一张表中的主键必须首先要确保其它表中的没有相同外键(即该表中的主键没有一个外键和它相关联)。

索引(index) 是用来快速地寻找那些具有特定值的记录。主要是为了检索的方便，是为了加快访问速度，按一定的规则创建的，一般起到排序作用。所谓唯一性索引，这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一。

总结：

主键一定是唯一性索引，唯一性索引并不一定就是主键。

一个表中可以有多个唯一性索引，但只能有一个主键。

主键列不允许空值，而唯一性索引列允许空值。

主键可以被其他字段作外键引用，而索引不能作为外键引用。

主键：

主键是数据表的唯一索引，比如学生表里有学号和姓名，姓名可能有重名的，但学号确是唯一的，你要从学生表中搜索一条纪录如查找一个人，就只能根据学号去查找，这才能找出唯一的一个，这就是主键;如：idint(10) not null primary key auto_increment ；自增长的类型；

外键：

定义数据表

假如某个电脑生产商，它的数据库中保存着整机和配件的产品信息。用来保存整机产品信息的表叫做Pc；用来保存配件供货信息的表叫做Parts。

在Pc表中有一个字段，用来描述这款电脑所使用的CPU型号；

在Parts 表中相应有一个字段，描述的正是CPU的型号，我们可以把它想成是全部CPU的型号列表。

很显然，这个厂家生产的电脑，其使用的CPU一定是供货信息表(parts)中存在的型号。这时，两个表中就存在一种约束关系(constraint)——Pc表中的CPU型号受到Parts表中型号的约束。

2、表：table

由行和列组成的二维关系

字段：字段由字段名，字段(数据)类型，约束组成

创建表：

CREATE TABLE table_name(col1_name col1_type 修饰符,col2 col2_type 修饰符...);

删除表：

DROP TABLE table_name;MariaDB [(none)]> use mysql

Database changed

MariaDB [mysql]> create table students (Name varchar(30), Age tinyint, Gender ENUM("F","M"));Query OK, 0 rows affected (0.50 sec)

MariaDB [mysql]> show tables;

+---------------------------+

| Tables_in_mysql |

+---------------------------+

| column_stats |

| columns_priv |

| db |

| event |

| func |

| general_log |

| gtid_slave_pos |

| help_category |

| help_keyword |

| help_relation |

| help_topic |

| host |

| index_stats |

| innodb_index_stats |

| innodb_table_stats |

| plugin |

| proc |

| procs_priv |

| proxies_priv |

| roles_mapping |

| servers |

| slow_log |

| students |

| table_stats |

| tables_priv |

| time_zone |

| time_zone_leap_second |

| time_zone_name |

| time_zone_transition |

| time_zone_transition_type |

| user |

+---------------------------+

31 rows in set (0.00 sec)

MariaDB [mysql]> desc students; # 查看表的详细结构

+--------+---------------+------+-----+---------+-------+

+--------+---------------+------+-----+---------+-------+

| Age | tinyint(4) | YES | | NULL | |

+--------+---------------+------+-----+---------+-------+

3 rows in set (0.02 sec)

MariaDB [mysql]> select Name,age,gender from students;

Empty set (0.00 sec)

MariaDB [mysql]> create table students (StuID int unsigned not null auto_increment primary key, Name varchar(30) not null, Age tinyint unsigned not null, Gender ENUM("F","M") not null default 'M');

Query OK, 0 rows affected (0.01 sec)

MariaDB [mysql]> desc students;

+--------+---------------------+------+-----+---------+----------------+

+--------+---------------------+------+-----+---------+----------------+

| Age | tinyint(3) unsigned | NO | | NULL | |

| Gender | enum('F','M') | NO | | M | |

+--------+---------------------+------+-----+---------+----------------+

4 rows in set (0.00 sec)

MariaDB [mysql]>

常用的修饰符：

字符要加上引号，数值不能加引号

CHAR、VARCHAR和TEXT几种字符型常用的字属性修饰符：

NOT NULL：不能为空

NULL：可为空

DEFAULT 'STRING'：默认值，字符要加引号，数值不能加引号，不适用于TEXT类型

character set ' '：设置使用的字符集

collation ' '：设置使用的排序规则

每种字符集可能存在多种不同的排序规则：

show character set；显示服务器支持的字符集

show variables like "character%"；显示服务器正在使用的字符集

show collation；字符集的排序方式

show variables like "collation%"; 显示服务器正在使用的字符排序规则

character set ' '：设置使用的字符集

collation ' '：设置使用的排序规则

默认会从表或数据库或全局中就继承

BINARY、VARBINARY和BLOB几种类型常用的属性修饰符：

日期时间型、ENUM和SET的修饰符：

NULL、NOT NULL、DEFAULT：不适用于BLOB

整型和浮点型的常用属性修饰符：

NULL,NOT NULL ,DEFAULT

UNSIGNED：无符号，无负值，用于数值

AUTO_INCREMENT：自动增长类型的字段必须为主键或唯一键，非负值，非空；删除数据时，不会重置，需要手动重置才可以

select last_insert_id();

trunccate TABLE_NAME;清空表的自动增长计数

PRIMARY KEY：主键，如何把多个字段定义为主键则用括号把多个字段括起来在后面加PRIMARY KEY

3、数据

插入：

INSERT INTO table_name (col1,col2,...) value|values(val1,val2,...)； # 填上对应列的值就可以

或INSERT INTO table_name VALUES(val1,val2,...)；# 必须每列都填上值

批量插入：

INSERT INTO table_name (col1,col2,...) value|values(val1,val2,...),(val1,val2,...);

INSERT INTO table_name VALUES(val1,val2,...),(val1,val2,...)；MariaDB [mysql]> insert into students(naME,AGE,gender) value(tom,17,F);

ERROR 1054 (42S22): Unknown column 'tom' in 'field list'

MariaDB [mysql]> insert into students(naME,AGE,gender) value('tom', 17, 'F');

Query OK, 1 row affected (0.00 sec)

MariaDB [mysql]> desc students;

+--------+---------------------+------+-----+---------+----------------+

+--------+---------------------+------+-----+---------+----------------+

| Age | tinyint(3) unsigned | NO | | NULL | |

| Gender | enum('F','M') | NO | | M | |

+--------+---------------------+------+-----+---------+----------------+

4 rows in set (0.00 sec)

MariaDB [mysql]> select Name,age,gender from students;

+------+-----+--------+

| Name | age | gender |

+------+-----+--------+

| tom | 17 | F |

+------+-----+--------+

1 row in set (0.00 sec)

MariaDB [mysql]> select stuid,Name,age,gender from students;

+-------+------+-----+--------+

+-------+------+-----+--------+

| 1 | tom | 17 | F |

+-------+------+-----+--------+

1 row in set (0.00 sec)

MariaDB [mysql]> select stuid,Name,age,gender from students\G

*************************** 1. row ***************************

stuid: 1

Name: tom

age: 17

gender: F

1 row in set (0.00 sec)

MariaDB [mysql]> insert into students values('xj', 25, "F");

ERROR 1136 (21S01): Column count doesn't match value count at row 1

MariaDB [mysql]> insert into students values(2, 'xj', 25, "F");

Query OK, 1 row affected (0.01 sec)

MariaDB [mysql]> select stuid,Name,age,gender from students;

+-------+------+-----+--------+

+-------+------+-----+--------+

| 1 | tom | 17 | F |

| 2 | xj | 25 | F |

+-------+------+-----+--------+

2 rows in set (0.00 sec)

MariaDB [mysql]> insert into students(name,gender) values(5,"xxj");

Query OK, 1 row affected, 2 warnings (0.02 sec)

MariaDB [mysql]> select stuid,Name,age,gender from students;

+-------+------+-----+--------+

+-------+------+-----+--------+

| 1 | tom | 17 | F |

| 2 | xj | 25 | F |

| 3 | 5 | 0 | |

+-------+------+-----+--------+

3 rows in set (0.00 sec)

MariaDB [mysql]> select * from students;

+-------+------+-----+--------+

+-------+------+-----+--------+

| 1 | tom | 17 | F |

| 2 | xj | 25 | F |

| 3 | 5 | 0 | |

+-------+------+-----+--------+

3 rows in set (0.00 sec)

MariaDB [mysql]> select stuid,Name,age,gender from students\G

*************************** 1. row ***************************

stuid: 1

Name: tom

age: 17

gender: F

*************************** 2. row ***************************

stuid: 2

Name: xj

age: 25

gender: F

*************************** 3. row ***************************

stuid: 3

Name: 5

age: 0

gender:

3 rows in set (0.00 sec)

# 批量添加数据

MariaDB [mysql]> insert into students(name,age) values("test1", 11),("test2",22);

Query OK, 2 rows affected (0.01 sec)

Records: 2 Duplicates: 0 Warnings: 0

MariaDB [mysql]> select * from students;

+-------+-------+-----+--------+

+-------+-------+-----+--------+

| 1 | tom | 17 | F |

| 2 | xj | 25 | F |

| 3 | 5 | 0 | |

| 4 | test1 | 11 | M |

| 5 | test2 | 22 | M |

+-------+-------+-----+--------+

5 rows in set (0.00 sec)

MariaDB [mysql]> insert into students values(5,"test3", 11,"F"),(6,"test4",22,"m");

ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'

MariaDB [mysql]> insert into students values(6,"test3", 11,"F"),(7,"test4",22,"m");

Query OK, 2 rows affected (0.23 sec)

Records: 2 Duplicates: 0 Warnings: 0

MariaDB [mysql]> select * from students;

+-------+-------+-----+--------+

+-------+-------+-----+--------+

| 1 | tom | 17 | F |

| 2 | xj | 25 | F |

| 3 | 5 | 0 | |

| 4 | test1 | 11 | M |

| 5 | test2 | 22 | M |

| 6 | test3 | 11 | F |

| 7 | test4 | 22 | M |

+-------+-------+-----+--------+

7 rows in set (0.00 sec)

MariaDB [mysql]>

删除：

DELETE FROM table_name; # 清空表，删除整张表

DELETE FORM table_name WHERE 条件表达式;MariaDB [mysql]> delete from students where name like 'x%';

Query OK, 1 row affected (0.00 sec)

MariaDB [mysql]> select * from students;

+-------+-------+-----+--------+

+-------+-------+-----+--------+

| 1 | tom | 17 | F |

| 3 | 5 | 0 | |

| 4 | test1 | 11 | M |

| 5 | test2 | 22 | M |

| 6 | test3 | 11 | F |

| 7 | test4 | 22 | M |

+-------+-------+-----+--------+

6 rows in set (0.00 sec)

MariaDB [mysql]>

更新：

UPDATE table_name SET col1=values,col2=values,...；

UPDATE table_name SET col1=values,... WHERE 条件表达式；MariaDB [mysql]> update steudents set age=77;

ERROR 1146 (42S02): Table 'mysql.steudents' doesn't exist

MariaDB [mysql]> update students set age=77;

Query OK, 6 rows affected (0.02 sec)

Rows matched: 6 Changed: 6 Warnings: 0

MariaDB [mysql]> select * from students;

+-------+-------+-----+--------+

+-------+-------+-----+--------+

| 1 | tom | 77 | F |

| 3 | 5 | 77 | |

| 4 | test1 | 77 | M |

| 5 | test2 | 77 | M |

| 6 | test3 | 77 | F |

| 7 | test4 | 77 | M |

+-------+-------+-----+--------+

6 rows in set (0.00 sec)

MariaDB [mysql]> update students set age=66 where name="tom";

Query OK, 1 row affected (0.33 sec)

Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [mysql]> select * from students;

+-------+-------+-----+--------+

+-------+-------+-----+--------+

| 1 | tom | 66 | F |

| 3 | 5 | 77 | |

| 4 | test1 | 77 | M |

| 5 | test2 | 77 | M |

| 6 | test3 | 77 | F |

| 7 | test4 | 77 | M |

+-------+-------+-----+--------+

6 rows in set (0.00 sec)

MariaDB [mysql]>

查询：

SELECT col1,col2,... FROM table_name WHERE 条件表达式；

SELECT * FROM table_name;

条件表达式：

=，>，=，<

like：通配符

%：任意长度的任意字符

_：匹配任意单个字符

rlike：正则表达式

组合条件：

and

notMariaDB [mysql]> select * from students;

+-------+-------+-----+--------+

+-------+-------+-----+--------+

| 1 | tom | 17 | F |

| 2 | xj | 25 | F |

| 3 | 5 | 0 | |

| 4 | test1 | 11 | M |

| 5 | test2 | 22 | M |

| 6 | test3 | 11 | F |

| 7 | test4 | 22 | M |

+-------+-------+-----+--------+

7 rows in set (0.00 sec)

MariaDB [mysql]> select name,age,gender from students where age>20;

+-------+-----+--------+

| name | age | gender |

+-------+-----+--------+

| xj | 25 | F |

| test2 | 22 | M |

| test4 | 22 | M |

+-------+-----+--------+

3 rows in set (0.00 sec)

MariaDB [mysql]> select name,age,gender from students where age>20 and gender=m;

ERROR 1054 (42S22): Unknown column 'm' in 'where clause'

MariaDB [mysql]> select name,age,gender from students where age>20 and gender="m";

+-------+-----+--------+

| name | age | gender |

+-------+-----+--------+

| test2 | 22 | M |

| test4 | 22 | M |

+-------+-----+--------+

2 rows in set (0.33 sec)

MariaDB [mysql]>

4、show命令

查看show命令的帮助信息：MariaDB [mysql]> help 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

https://mariadb.com/kb/en/extended-show/.

URL: https://mariadb.com/kb/en/show/

查看创建对象时所用的语句：MariaDB [mysql]> show create table students

-> ;

+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

| students | CREATE TABLE `students` (

`StuID` int(10) unsigned NOT NULL AUTO_INCREMENT,

`Name` varchar(30) NOT NULL,

`Age` tinyint(3) unsigned NOT NULL,

`Gender` enum('F','M') NOT NULL DEFAULT 'M',

PRIMARY KEY (`StuID`)

) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 |

1 row in set (0.02 sec)

MariaDB [mysql]>

查看支持的存储引擎：

show enginesMariaDB [mysql]> show engines;

+--------------------+---------+--------------------------------------------------------------------------------------------------+--------------+------+------------+

+--------------------+---------+--------------------------------------------------------------------------------------------------+--------------+------+------------+

| MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

| CSV | YES | CSV storage engine | NO | NO | NO |

| Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |

| MyISAM | YES | MyISAM storage engine | NO | NO | NO |

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

| InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, foreign keys and encryption for tables | YES | YES | YES |

| SEQUENCE | YES | Generated tables filled with sequential values | YES | NO | YES |

| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |

+--------------------+---------+--------------------------------------------------------------------------------------------------+--------------+------+------------+

8 rows in set (0.02 sec)

MariaDB [mysql]>

show table status;# 查看当前默认数据库中表状态

show table status like|rlike "";# 查看当前默认数据库中指定表的状态

mysql> show table status [from | in] 数据库\G # 查看指定数据库中的表状态MariaDB [mysql]> show table status like "s%" \G

*************************** 1. 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: 2017-02-02 01:52:48

Update_time: 2017-02-02 01:52:48

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options:

Comment: MySQL Foreign Servers table

*************************** 2. 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

*************************** 3. row ***************************

Name: students

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 6

Avg_row_length: 2730

Data_length: 16384

Max_data_length: 0

Index_length: 0

Data_free: 0

Auto_increment: 8

Create_time: 2017-02-02 23:32:55

Update_time: NULL

Check_time: NULL

Collation: latin1_swedish_ci

Checksum: NULL

Create_options:

Comment:

3 rows in set (0.00 sec)

MariaDB [mysql]>

5、mysql中的变量

Mysql服务器的工作特性的定义是通过服务器变量实现的:

show variables [like] 查看服务器变量(默认查看的是session的变量)

show global variables 查看全局变量

show session variables 查看会话的变量

show variables like 'data%';

MySQL服务器运行中的状态统计数据是通过状态变量输出的：

show status：服务器运行状态

show global status 查看全局变量

show session status 查看会话的变量

weixin_39890652

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql values用法_【mysql基础】04、mysql基本使用

一、Mysql客户端使用1、交互式模式交互式模式中的命令类别：mysql常用客户端命令如下：客户端命令：在客户端执行的命令，不需要以分号结尾，自行获取数据并且将结果返回给当前用户，在客户端执行，可以获取服务端的数据，不需要语句终止符获取客户端命令的帮助信息：mysql> helpmysql命令本身不区分字符大小写，但是与文件系统相关的部分根据OS的不同可能区分字符的大小写，建议将关键字大写M...
复制链接

扫一扫