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 |
| bi |
| 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.1 Connection id: 21 #当前连接的ID号 Curbit database: mysql #当前选择使用的数据库 Curbit user: root@localhost #当前连接的登录用户 SSL: Not in use #是否使用了ssl Curbit pager: 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 12 sec #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 [bi]> show tables;
（2）查看当前数据库中所有表的具体属性信息：
MariaDB [bi]> show table status\G; #\G表示垂直显示
（3）查看当前数据库中的某张表的具体属性信息，可以使用like匹配表名称：
MariaDB [bi]> show table status like 'students'\G;
或者使用通配符：
MariaDB [bi]> show table status where name like '%stu%'\G;
（4）查看表的结构（字段、类型）describe
MariaDB [bi]> desc students;
（5）查看某张表被创建时对应的sql语句：
MariaDB [bi]> 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; <<会继承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%。因此，用户在设计数据库时应当综合考虑各方面的因素，以求达到最佳的平衡
text
text数据类型用于保存变长的大字符串，可以组多到65535 (2**16 − 1)个字符。
mediumtext
A TEXT column with a maximum length of 16,777,215 (2**24 − 1) characters.
longtext
A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 − 1) characters.
enum
枚举类型，
An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.)
示例：
CREATE TABLE shirts (
name VARCHAR(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
集合类型
A SET column can have a maximum of 64 distinct members.
示例：
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'）
YEAR
YYYY（1901/2155）
DATETIME
YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59 Y）
TIMESTAMP
YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）
约束条件：
主键primary key：物理上存储的顺序，并且拥有唯一性（一个表中只能有一个主键，一个主键可以包含多个字段），字段不能为空（有索引的作用） 非空not null：此字段不能为空，不设置默认为null，表示对应字段可以为空 唯一unique：此字段不允许重复（一个表中可以有多个唯一键） 默认default：当不填写此值时会使用默认值，如果填写则已填写为准
anto_increment：表示对应字段使用自动增长，一个表中只有一个字段能被设置为自动增长，而且这个字段必须被定义为key（或者索引） 外键foreign key：对关系字段进行约束，当为关系字段填写值时，会到关联的表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并抛出异常
check(expr)：用于定义检查性约束
[comment 'string']：设置字段的描述信息，此设置可省
{index|key}：定义索引
{fulltext|spatial}：全文索引和空间索引
例子：
--创建classes表(id, name)
create table zzzz(
id int primary key not null auto_increment,
name varchar(20)，
age int
);
--创建students表(id, name, age, high, gender, cls_id)
create table students (
id int unsigned not null auto_increment primary key,
name varchar(20),
age tinyint unsigned default 0,
high decimal(5,2),
gender enum('男','女','中性','保密') default '保密',
cls_id int unsigned
);
表的其它选项（通过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 [bi]> drop table students;
MariaDB [bi]> drop table if exists classes,students;
可以删除指定的单张表，也可以一次删除多张表，表之间用逗号隔开；
删除表的做法比较危险，如果不是确定要删而且必须要删，请勿随意删除，此处没有回收站，数据无价。
4、修改表
如果在表中还没有任何数据时，我们可以相对轻松的修改表结构，但是如果表中已经存在了很多数据，那么我们应该充分的考虑修改表结构以后给我们带来的麻烦。
修改表时，往往是添加、删除、修改表中的字段、索引、约束等。
修改表中的基本语法：
ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
[alter_specification [, alter_specification] ...]
[partition_options]
（1）修改表名、重命名表
MariaDB [bi]> alter table new biame as test;
（2）添加字段（无任何属性）
MariaDB [bi]> alter table test add column id int;（column可省）
MariaDB [bi]> alter table test add id int;
（3）添加字段，同时为添加的字段设定相应的约束
MariaDB [bi]> alter table test add age int not null default 0;
（4）为表添加字段，同时指定新添加的字段在表中的位置
MariaDB [bi]> alter table test add brith datetime first;
将新添加的brith字段设置为表中的第一个字段
MariaDB [bi]> alter table test add high int after name;
将新添加的high字段添加到name字段的后面
（5）删除字段
MariaDB [bi]> alter table test drop brith;
（6）修改字段
修改字段数据类型可以使用两种语法，modify和change
MariaDB [bi]> alter table test modify id tinyint;
MariaDB [bi]> alter table test change age age char(20); #字段名需写两次
（7）重命名字段
MariaDB [bi]> alter table test change class home char(20);
