mariadb与mysql语法_mysql基础之mariadb库管理和表管理语句

一、数据库管理语句

1、Syntax:

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name

[create_specification] ...

create_specification:

[DEFAULT] CHARACTER SET [=] charset_name

| [DEFAULT] COLLATE [=] collation_name

(1)在其他关系型数据库中，database与schema是有区别的，但是在mysql5.0.2以后，我们在创建数据库时可以不加区分的使用database和schema，我们可以将create schema理解为create database的代名词。

(2)if not exists 表示在对应的数据库不存在的时候才会创建，if not exists被中括号括起来，表示此项为可选，建议在sql脚本中使用create命令创建数据库时加入此项，以免对应名称的数据库已经存在导致sql脚本终止。

(3)create_specification 表示我们可以在创建数据库时指定对应的数据库规范。

(4)我们可以在创建数据库时指定数据库的字符集，使用character set对应字符集名称即可指定使用什么字符集，如果使用了default关键字，那么这个数据库中创建的所有表默认都会继承这个数据库的字符集，default为可选选项。

(5)我们可以在创建数据库时指定数据库的排序规则；同一种字符集下可能有多种排序规则，但是一种排序规则只能对应于一种字符集。

2、创建数据库

(1)创建名为test1的数据库

MariaDB [(none)]> create database test1;

(2)如果名为test2的数据库不存在，则创建

MariaDB [(none)]> create database if not exists test2;

(3)如果名为test1的数据库不存在，则创建，并且设置其字符集为utf8

MariaDB [mysql]> create database test3 character set utf8;

MariaDB [(none)]> create database if not exists test1 default character set utf8;

3、查看数据库

(1)列出所有已存在的数据库(查看所有数据库)

MariaDB [(none)]> show databases;

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

| Database           |

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

| help               |

| information_schema |

| mysql              |

| performance_schema |

| ren                |

| test1              |

| test2              |

| yan                |

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

(2)列出创建对应数据库的sql语句(可以看到对应的字符集)(查看状态)

MariaDB [(none)]> show create database test1;

(3)列出所有可用的字符集

MariaDB [(none)]> show character set;

(4)查看排序方式

MariaDB [(none)]> show collation;

4、进入数据库(使用数据库)

MariaDB [(none)]> use mysql;

查看当前数据库与当前连接的概要信息：

MariaDB [mysql]>status;--------------

mysql Ver 15.1 Distrib 10.2.26-MariaDB, for Linux (x86_64) using readline 5.1Connection id:21　　　　#当前连接的ID号

Current database: mysql　　　#当前选择使用的数据库Current user: root@localhost　　#当前连接的登录用户SSL:Not in use　　　　#是否使用了ssl

Currentpager: stdout

Using outfile:''Using delimiter: ;　　　　　　#当前会话的行终结符是分号

Server: MariaDB　　　　#当前mysql版本为mariadb分支

Server version:10.2.26-MariaDB MariaDB Server　　#当前mysql服务器的版本号

Protocol version:10　　　　#协议版本Connection: Localhost via UNIX socket　　　　#使用的连接类型，通过本机的套接字文件进行连接

Server characterset: latin1　　#服务器使用的字符类型

Db characterset: latin1　　#当前数据库使用的字符类型

Client characterset: utf8　　　　#当前mysql客户端使用的字符类型

Conn. characterset: utf8　　　　#当前连接使用的字符类型

UNIX socket:/var/lib/mysql/mysql.sock　　#套接字文件路径

Uptime:11 hours 35 min 12sec　　　　#mysql数据库的启动时长

Threads:7 Questions: 564 Slow queries: 0 Opens: 66 Flush tables: 1 Open tables: 36 Queries per second avg: 0.013

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

5、修改数据库

修改数据库的基本语法：

ALTER {DATABASE | SCHEMA} [db_name]

alter_specification ...

ALTER {DATABASE | SCHEMA} db_name

UPGRADE DATA DIRECTORY NAME

alter_specification:

[DEFAULT] CHARACTER SET [=] charset_name

| [DEFAULT] COLLATE [=] collation_name

修改数据库就是修改数据库的字符集，或者修改字符集的排序规则

(1)查看数据库的字符集：

MariaDB [mysql]> show variables like 'character%';

(2)修改数据库的字符集：

MariaDB [mysql]> alter database test1 character set utf8;

(3)修改数据库的字符集，并设置为默认字符集，数据库中的表都会继承此字符集：

MariaDB [mysql]> alter database test1 default character set utf8;

6、删除数据库(注意：删除操作无法恢复)

删除数据库的基本语法：

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

例子：

MariaDB [mysql]> drop database if exists test3;

MariaDB [mysql]> drop database test3;

二、表管理语句

1、查看表

(1)查看当前数据库上的所有表：

MariaDB [ren]> show tables;

(2)查看当前数据库中所有表的具体属性信息：

MariaDB [ren]> show table status\G;   #\G表示垂直显示

(3)查看当前数据库中的某张表的具体属性信息，可以使用like匹配表名称：

MariaDB [ren]> show table status like 'students'\G;

或者使用通配符：

MariaDB [ren]> show table status where name like '%stu%'\G;

(4)查看表的结构(字段、类型)describe

MariaDB [ren]> desc students;

(5)查看某张表被创建时对应的sql语句：

MariaDB [ren]> show create table students\G;

2、创建表

创建一个新表的基本语法格式：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

(create_definition,...)

[table_options]

[partition_options]

创建表的三种方式：

1、create table test (id int,name varchar);

2、create table test like Old_Table;   　　<

3、create table test as select * from Old_Table;　　　　<

数据类型：

bit[(M)]二进制位(101001)，m表示二进制位的长度(1-64)，默认m＝1

tinyint[(m)] [unsigned] [zerofill]小整数，数据类型用于保存一些范围的整数数值范围：

有符号：-128 ～ 127.

无符号：～255特别的： MySQL中无布尔值，使用tinyint(1)构造。int[(m)][unsigned][zerofill]整数，数据类型用于保存一些范围的整数数值范围：

有符号：-2147483648 ～ 2147483647无符号：～4294967295特别的：整数类型中的m仅用于显示，对存储范围无限制。例如：int(5),当插入数据2时，select 时数据显示为： 00002

bigint[(m)][unsigned][zerofill]大整数，数据类型用于保存一些范围的整数数值范围：

有符号：-9223372036854775808 ～ 9223372036854775807无符号： ～18446744073709551615

decimal[(m[,d])] [unsigned] [zerofill]准确的小数值，m是数字总个数(负号不算)，d是小数点后个数。 m最大值为65，d最大值为30。

特别的：对于精确数值计算时需要用此类型

decaimal能够存储精确值的原因在于其内部按照字符串存储。FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]单精度浮点数(非准确小数值)，m是数字总个数，d是小数点后个数。

无符号：-3.402823466E+38 to -1.175494351E-38,1.175494351E-38 to 3.402823466E+38有符号：1.175494351E-38 to 3.402823466E+38

**** 数值越大，越不准确 ****

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]双精度浮点数(非准确小数值)，m是数字总个数，d是小数点后个数。

无符号：-1.7976931348623157E+308 to -2.2250738585072014E-308

2.2250738585072014E-308 to 1.7976931348623157E+308有符号：2.2250738585072014E-308 to 1.7976931348623157E+308

**** 数值越大，越不准确 ****

char(m)

char数据类型用于表示固定长度的字符串，可以包含最多达255个字符。其中m代表字符串的长度。

PS: 即使数据小于m长度，也会占用m长度varchar(m)

varchars数据类型用于变长的字符串，可以包含最多达255个字符。其中m代表该数据类型所允许保存的字符串的最大长度，只要长度小于该最大值的字符串都可以被保存在该数据类型中。

注：虽然varchar使用起来较为灵活，但是从整个系统的性能角度来说，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。因此，用户在设计数据库时应当综合考虑各方面的因素，以求达到最佳的平衡texttext数据类型用于保存变长的大字符串，可以组多到65535 (2**16 − 1)个字符。

mediumtext

ATEXT column with a maximum length of 16,777,215 (2**24 − 1) characters.

longtext

ATEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 − 1) characters.

enum

枚举类型，

An ENUMcolumn can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.)

示例：CREATE TABLEshirts (

nameVARCHAR(40),

size ENUM('x-small', 'small', 'medium', 'large', 'x-large')

);INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');set集合类型

ASET column can have a maximum of 64 distinctmembers.

示例：CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');

DATE

YYYY-MM-DD(1000-01-01/9999-12-31)

TIME

HH:MM:SS('-838:59:59'/'838:59:59')YEARYYYY(1901/2155)DATETIMEYYYY-MM-DD HH:MM:SS(1000-01-01 00:00:00/9999-12-31 23:59:59Y)TIMESTAMPYYYYMMDD HHMMSS(1970-01-01 00:00:00/2037 年某时)

约束条件：

主键primary key：物理上存储的顺序，并且拥有唯一性(一个表中只能有一个主键，一个主键可以包含多个字段)，字段不能为空(有索引的作用)

非空notnull：此字段不能为空，不设置默认为null，表示对应字段可以为空

唯一unique：此字段不允许重复(一个表中可以有多个唯一键)

默认default：当不填写此值时会使用默认值，如果填写则已填写为准

anto_increment：表示对应字段使用自动增长，一个表中只有一个字段能被设置为自动增长，而且这个字段必须被定义为key(或者索引)

外键foreignkey：对关系字段进行约束，当为关系字段填写值时，会到关联的表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并抛出异常

check(expr)：用于定义检查性约束

[comment 'string']：设置字段的描述信息，此设置可省

{index|key}：定义索引

{fulltext|spatial}：全文索引和空间索引

例子：

--创建classes表(id, name)

create tablezzzz(

idint primary key not nullauto_increment,

namevarchar(20)，

ageint);

--创建students表(id, name, age, high, gender, cls_id)

create tablestudents (

idint unsigned not null auto_increment primary key,

namevarchar(20),

agetinyint unsigned default 0,

highdecimal(5,2),

gender enum('男','女','中性','保密') default '保密',

cls_idintunsigned

);

表的其它选项(通过help命令查询)：

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]...)

3、删除表

删除表的语句如下：

MariaDB [ren]> drop table students;

MariaDB [ren]> drop table if exists classes,students;

可以删除指定的单张表，也可以一次删除多张表，表之间用逗号隔开；

删除表的做法比较危险，如果不是确定要删而且必须要删，请勿随意删除，此处没有回收站，数据无价。

4、修改表

如果在表中还没有任何数据时，我们可以相对轻松的修改表结构，但是如果表中已经存在了很多数据，那么我们应该充分的考虑修改表结构以后给我们带来的麻烦。

修改表时，往往是添加、删除、修改表中的字段、索引、约束等。

修改表中的基本语法：

ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name

[alter_specification [, alter_specification] ...]

[partition_options]

(1)修改表名、重命名表

MariaDB [ren]> alter table new rename as test;

(2)添加字段(无任何属性)

MariaDB [ren]> alter table test add column id int;(column可省)

MariaDB [ren]> alter table test add id int;

(3)添加字段，同时为添加的字段设定相应的约束

MariaDB [ren]> alter table test add ageint not nulldefault 0;

(4)为表添加字段，同时指定新添加的字段在表中的位置

MariaDB [ren]> alter table test add brith datetime first;

将新添加的brith字段设置为表中的第一个字段

MariaDB [ren]> alter table test add high int after name;

将新添加的high字段添加到name字段的后面

(5)删除字段

MariaDB [ren]> alter table test drop brith;

(6)修改字段

修改字段数据类型可以使用两种语法，modify和change

MariaDB [ren]> alter table test modify id tinyint;

MariaDB [ren]> alter table test change age age char(20);       #字段名需写两次

(7)重命名字段

MariaDB [ren]> alter table test changeclass homechar(20);