MariaDB命令详解

来源：互联网发布：淘宝网如何注册网店编辑：程序博客网时间：2024/05/17 02:12

mysql客户端程序：

命令行交互式客户端程序：mysql

mysql

mysql [OPTIONS] [database]

# mysql --help

配置文件的读取次序：/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf rpm安装的读取方法

如果有相同的内容最后读取的会覆盖前面读取的，不同版本的顺序可能不同，用mysql --help查看

我们自己用二进制包安装的路径读取方法

[root@yph7s ~]# /usr/local/mysql/bin/mysqld --verbose --help ---

常用选项：---这些选项后可以有空格也可无空格

-uUSERNAME：用户名，默认为root；

-hHOST：远程主机（即mysql服务器）地址，默认为localhost;

-p[PASSWORD]：USERNAME所表示的用户的密码；可省略，默认为空；

mysql的用户账号由两部分组成，即：'USERNAME'@'HOST';

其中HOST用于限制此用户可通过哪些远程主机连接当前的mysql服务；

HOST主机的表示方式，支持使用通配符：”%”,和”_”

%：匹配任意长度的任意字符；

172.16.%.%, 172.16.0.0/16

_：匹配任意单个字符；

-Ddb_name：连接到服务器端之后，设定默认数据库；如果连接后执行命令时没有指定某数据库，就默认在此数据库下工作。

[root@yph7s ~]# mysql -uroot -p -Dmysql

MariaDB [mysql]> ------中括号的指默认查看的数据库

之后可以用use db_name 切换数据库

-e'SQL_COMMAND;'：连接至服务器并让其执行此命令后直接退出；不进入交互式模式

[root@yph7s ~]# mysql -e'SHOW DATABASES;'

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| test |

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

客户端命令：本地执行

mysql> help ----查看有哪些本地命令

\u db_name：设定哪个库为默认数据库

\q：退出；

\d CHAR：设定新的语句结束符；

\g：go，语句结束标记；

\G：语句结束标记，结果竖排方式显式；

\s：status，连接状态

\c：类似shell的Ctrl+c；

注意：客户端每个语句必须有语句结束符，默认为分号(;)，不加分号可能要等你输入分号，否则无法进一步执行。

MariaDB [(none)]> \s

mysql Ver 15.1 Distrib 5.5.44-MariaDB, for Linux (x86_64) using readline 5.1-----客户端版本

Connection id:9 -----当前连接的线程id

Current database: ------------当前默认数据库；现在是没指数据库

Current user:root@localhost -------当前登陆用户

SSL:Not in use -----有没有ssl方式的远程连接

Using delimiter:; --------默认结束符为分号

Server:MariaDB

Server version:5.5.46-MariaDB-log 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 ----Unix sock路径

Uptime:45 min 31 sec

服务端命令：通过mysql连接发往服务器执行并取回结果；

命令可以不区分大小写

DDL：数据定义语言，主要用于管理数据库组件，例如表、索引、视图、用户、存储过程

CREATE：创建 ALTER：修改 DROP：删除

DML：数据操纵语言，主要用管理表中的数据，实现数据的增、删、改、查；

INSERT：插入 DELETE：删除 UPDATE：修改 SELECT：查看

获取命令帮助：

mysql> help create

mysql> help create database

数据库管理：

CREATE：创建数据库：

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

[DEFAULT] CHARACTER SET [=] charset_name

[DEFAULT] COLLATE [=] collation_name

创建：

MariaDB [(none)]> create schema mydb;

MariaDB [(none)]> create schema if not exists mydb;

查看指定数据库：

SHOW DATABASES LIKE ‘mysql’;

查看支持的所有字符集：SHOW CHARACTER SET

查看支持的所有排序规则：SHOW COLLATION

查看数据库支持的所有存储引擎类型：mysql> SHOW ENGINES;

数据库是什么：

我们手动在安装时指定数据库目录下创建一个mydb目录，然后把属主属组改成mysql，这样再查看数据库时就能看的刚才创建的目录了，现在知道数据库时什么了吧。但是这样的数据库不完整，不能用。

[root@yph7s ~]# cd /mydata/data

[root@yph7s data]# mkdir mytestdb

[root@yph7s data]# chown -R mysql:mysql mytestdb

MariaDB [(none)]> show databases;

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

| Database |

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

| |mytestdb

ALTER：修改数据库属性，一般为字符集和排序规则

ALTER {DATABASE | SCHEMA} [db_name] [DEFAULT] CHARACTER SET [=] charset_name

[DEFAULT] COLLATE [=] collation_name

DROP：删除数据库；

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

表管理：

查看表：

查看指定数据库中的表列表

MariaDB [(none)]> show tables from mysql;

| Tables_in_mysql |

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

| columns_priv |

| db |

| event |

查看某表结构：包含字段，主键等

MariaDB [mysql]> desc stu;

MariaDB [(none)]> desc testdb.mage;

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

| id | int(10) unsigned | NO | PRI | NULL | |

查看指定表属性信息

MariaDB [mysql]> show table status like 'stu' \G

MariaDB [(none)]> show table status from mydb like 'mytab' \G

查看某表的存储引擎类型：

mysql> SHOW TABLE STATUS [LIKE 'tbl_name']

MariaDB [mysql]> SHOW TABLE STATUS like 'mytab'; -----不支持绝对路径mydb.mytab

创建表：

CREATE TABLE [IF NOT EXISTS] tbl_name (create_defination) [table_options]

表定义：create_defination:

字段：col_name data_type

键：键是索引的一部分

PRIMARY KEY (col1, col2, ...)主键

UNIQUE KEY (col1, col2,...)唯一键

FOREIGN KEY (column)外键

索引：

KEY|INDEX [index_name] (col1, col2,...)

表选项：

ENGINE [=] engine_name -----存储引擎类型

创建表：

MariaDB [(none)]> create table mydb.mytesttab(name int not null);

MariaDB [(none)]> create table mydb.mytab(id int unsigned not null,name char(30) not null, age tinyint unsigned, gender enum('f','m'),primary key(id,gender),class char(30));

表，名为mytab；有id ，name， age， gender 四个字段

id int unsigned not null：字段id，无符号整形，2^32个字符长度，并且不能为空

name char(30) not null：字段name，定长30位字符，非空

age tinyint unsigned：字段age，

gender enum('f','m')：字段gender性别，枚举型，只能从f和m选一。

primary key(id,gender)：定义主键，把id和name定义为主键

默认数据库为mysql，所以可以不指定；

在指定数据库里创建表

MariaDB [mysql]> create table testdb.mage(name char(20) not null,age tinyint );

另外一种定义主键的方法：

(id int unsigned not null primary key,name char(30) not null)

MariaDB [mysql]> desc mydb.mytab; -----查看表结构

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

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

| id | int(10) unsigned | NO | PRI | NULL | |

| age | tinyint(3) unsigned | YES | | NULL | |

| gender | enum('f','m') | NO | PRI | f | |

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

MariaDB [mysql]> drop table mytab; ---删除表

MariaDB [mysql]> show table status \G ----------查看该数据库内所有表的属性信息

MariaDB [mysql]> show table status like 'mytab' \G ---------查看指定表stu的属性信息

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

Name: mytab

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 0

Avg_row_length: 0

Data_length: 16384

Max_data_length: 0

Index_length: 0

Data_free: 0

Auto_increment: NULL

Create_time: 2016-01-16 11:49:53

Update_time: NULL

Check_time: NULL

Collation: latin1_swedish_ci

Checksum: NULL

Create_options:

Comment:

修改表属性：

ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name [alter_specification [, alter_specification] ...]

alter_specification:

字段：

添加字段：ADD [COLUMN] col_name data_type [FIRST | AFTER col_name ]

默认添加到最后一个字段后面，first指添加到第一个字段，after指添加到某字段后面

MariaDB [mysql]> alter table mytab add class varchar(100) not null after age;

MariaDB [mysql]> desc mytab

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

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

| age | tinyint(3) unsigned | YES | | NULL | |

| gender | enum('f','m') | NO | PRI | f | |

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

删除字段：DROP [COLUMN] col_name

MariaDB [mysql]> alter table mytab drop class;

修改字段：

修改字段名和字段属性：

CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]

仅修改字段属性：只要不改名字就可用modify

MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]

键：

添加：ADD {PRIMARY|UNIQUE|FOREIGN} KEY (col1, col2,...)

MariaDB [mysql]> alter table mytab add unique key (name);

删除键：

MariaDB [mysql]> alter table mytab drop primary key;

一张表中只有一个主键，删除就不在有主键，不管主键有几个字段。

主键：DROP PRIMARY KEY

外键：DROP FOREIGN KEY fk_symbol

索引：

查看表索引：

mysql> SHOW INDEXES FROM tbl_name;

MariaDB [mysql]> show indexes from mytab;

给表添加索引：ADD {INDEX|KEY} [index_name] (col1, col2,...)

MariaDB [mysql]> alter table mytab add index ages (age); 给字段添加索引，索引名为ages；索引名与字段名可以相同

删除表索引：DROP {INDEX|KEY} index_name

MariaDB [mysql]> alter table mytab drop index ages ; ------删除式指定的是索引名，不是字段名。

修改表选项：

主要是改引擎，但是非常危险，ENGINE [=] engine_name

删除表：

DROP TABLE [IF EXISTS] tbl_name [, tbl_name] ...

MariaDB [(none)]> drop table mydb.mytab;

表的第二种创建方式：

复制表结构；

表的第三种创建方式：

复制表数据；

索引管理：

索引：要有索引名称；索引是特殊的数据结构；

查看索引：

MariaDB [(none)]> show indexes from mydb.mytab;

创建索引：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [BTREE|HASH] ON tbl_name (col1, col2,,...)

唯一键，全文索引

btree索引，哈希索引，一般只有btree索引，哈希索引几乎很少受到创建

MariaDB [(none)]> create index name_class on mydb.mytab (name,class);

删除索引：

DROP INDEX index_name ON tbl_name

MariaDB [(none)]> drop index name_class on mydb.mytab;

DML：INSERT， DELETE， UPDATE， SELECT

INSERT INTO：

INSERT [INTO] tbl_name [(col1,...)] {VALUES|VALUE} (val1, ...),(...),...

MariaDB [(none)]> insert mydb.mytab values(1,'yang guo',36,'xikuang','m');

MariaDB [mydb]> insert mytab (id,name,class) values (2,'xiaolongnv','gumu'),(3,'jinlun','menggu');

MariaDB [mydb]> select * from mytab;

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

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

| 1 | yang guo | 36 | xikuang | m |

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

SELECT：

(1) SELECT * FROM tbl_name;

(2) SELECT col1, col2, ... FROM tbl_name;

MariaDB [mydb]> select * from mytab;

MariaDB [mydb]> select name,class from mytab;

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

| name | class |

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

| yang guo | xikuang |

| xiaolongnv | gumu |

| jinlun | menggu |

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

显示时，字段可以显示为别名；

col_name AS col_alias

MariaDB [mydb]> select id as tabID,class,age from mytab; ---只是显示出的替换，文本并未替换。

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

| tabID | class | age |

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

| 1 | xikuang | 36 |

| 2 | gumu | NULL |

| 3 | menggu | NULL |

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

(3) SELECT col1, ... FROM tbl_name WHERE clause;

WHERE clause：用于指明挑选条件；

col_name 操作符 value：

age > 30;

操作符(1) ：

>, <, >=, <=, ==, !=

MariaDB [mydb]> select * from mytab where id<=2;

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

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

| 1 | yang guo | 36 | xikuang | m |

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

MariaDB [mydb]> select * from mytab where name="xiaolongNV"; ----不区分大小写

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

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

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

组合条件：

and

not

MariaDB [mydb]> select name,class,age from mytab where age>30 and age<60;

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

| name | class | age |

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

| yang guo | xikuang | 36 |

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

操作符(2) ：

BETWEEN ... AND ...

MariaDB [mydb]> select name,class,age from mytab where age between 30 and 70;

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

| name | class | age |

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

| yang guo | xikuang | 36 |

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

LIKE 'PATTERN'

NOT LIKE ‘PATTERN’

通配符：

%：任意长度的任意字符；

_：任意单个字符；

MariaDB [mydb]> select name,class from mytab where name like '%ng%';

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

| name | class |

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

| yang guo | xikuang |

| xiaolongnv | gumu |

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

RLIKE 'PATTERN'

MariaDB [mydb]> select name,class from mytab where name rlike '^.*ng.*$';

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

| name | class |

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

| yang guo | xikuang |

| xiaolongnv | gumu |

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

正则表达式对字符串做模式匹配；

IS NULL

IS NOT NULL

MariaDB [mydb]> select name,class,age from mytab where age is null;

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

| name | class | age |

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

| xiaolongnv | gumu | NULL |

| jinlun | menggu | NULL |

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

(4) SELECT col1, ... FROM tbl_name [WHERE clause] ORDER BY col_name, col_name2, ... [ASC|DESC];

ASC: 升序；

DESC：降序；

MariaDB [mydb]> select name,id,age from mytab order by age asc;

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

| name | id | age |

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

| xiaolongnv | 2 | NULL |

| jinlun | 3 | NULL |

| yang guo | 1 | 36 |

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

DELETE：删除表中所有行

DELETE FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]

(1) DELETE FROM tbl_name WHERE where_condition

(2) DELETE FROM tbl_name [ORDER BY ...] [LIMIT row_count]

给表添加年龄随机的100个用户，注意：是在shell中输入，不是mysql中。

for i in {1..100};do AGE=$[$RANDOM%100]; mysql -e "insert mydb.mytab (id,name,age) values($i,\"menggu$i\",$AGE);";done

MariaDB [mydb]> delete from mytab order by age desc limit 20;

UPDATE：更新所有行的属性

UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1=value1 [, col_name2=value2] ... [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]

MariaDB [mydb]> update mytab set age=age-5 order by id desc limit 10;

MariaDB [mydb]> update mytab set age=age-10 where name not like 'stu%';

用户账号及权限管理：

用户账号：'username'@'host'

host：此用户访问当前mysql服务器时，允许其通过哪些主机远程创建连接；

表示方式：IP，网络地址、主机名、通配符(%和_)；

禁止检查主机名：my.cnf ----检测主机名会消耗大量系统资源

[mysqld]

skip_name_resolve = ON

创建用户账号：

CREATE USER 'username'@'host' [IDENTIFIED BY 'password'];

MariaDB [mydb]> create user 'myuser'@'172.16.%.%' identified by 'magedu';

MariaDB [(none)]> flush privileges;----------刷新授权表

[root@yph7s data]# mysql -umyuser -h172.16.59.3 -pmagedu

只有进来访问有限数据库的权限。

删除用户账号：

DROP USER ’user‘@’host' [, user@host] ...

授权：

权限级别：管理权限、数据库、表、字段、存储例程；

GRANT priv_type,... ON [object_type] db_name.tbl_name TO 'user'@'host' [IDENTIFIED BY 'password'];

给用户查看权限

MariaDB [(none)]> grant select on mydb.mytab to 'myuser'@'172.16.%.%';

MariaDB [(none)]> flush privileges;

重新用myuser登陆，此时有查看权限。

MariaDB [(none)]> select name,id from mydb.mytab where age > 70;

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

| name | id |

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

| sdxl16 | 16 |

| sdxl18 | 18 |

给用户写入权限

MariaDB [(none)]> grant insert on mydb.mytab to 'myuser'@'172.16.%.%';

MariaDB [(none)]> flush privileges;

切换到用户myuser

MariaDB [(none)]> insert mydb.mytab value (90,'jinlun',55,'menggu','m');

MariaDB [(none)]> select * from mydb.mytab where name like 'jinlun';

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

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

| 90 | jinlun | 55 | menggu | m |

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

MariaDB [(none)]> show grants for 'myuser'@'172.16.%.%'; ---查看某用户的所有授权

MariaDB [(none)]> revoke insert on mydb.mytab from 'myuser'@'172.16.%.%';

MariaDB [(none)]> flush privileges;

MariaDB [(none)]> show grants for 'myuser'@'172.16.%.%';

| GRANT SELECT ON `mydb`.`mytab` TO 'myuser'@'172.16.%.%'

priv_type： ALL [PRIVILEGES]

db_name.tbl_name：

*.*：所有库的所有表；

db_name.*：指定库的所有表；

db_name.tbl_name：指定库的特定表；

db_name.routine_name：指定库上的存储过程或存储函数；此时必须用object_name区分是否表名

[object_type]

TABLE

FUNCTION

PROCEDURE

查看指定用户所获得的授权：

SHOW GRANTS FOR 'user'@'host'

SHOW GRANTS FOR CURRENT_USER;

回收权限：

REVOKE priv_type, ... ON db_name.tbl_name FROM 'user'@'host';

注意：MariaDB服务进程启动时，会读取mysql库的所有授权表至内存中；

(1) GRANT或REVOKE命令等执行的权限操作会保存于表中，MariaDB此时一般会自动重读授权表，权限修改会立即生效；

(2) 其它方式实现的权限修改，要想生效，必须手动运行FLUSH PRIVILEGES命令方可；

在用rpm包安装完mysql后，加固mysql服务器，在安装完成后，运行mysql_secure_installation命令；给root加上密码。

在面试中mysql是收先被问到的一批知识之一。

数据库图形管理组件：

phpMyAdmin

运行于lamp；

Navicat

Mysql-Front

ToadForMySQL

SQLyog

安装phpMYadmin：

安装httpd php php-mysql mariadb-server

lftp 172.16.0.1/pub/Sources/source/httpd/phpMyAdmin mget 放到httpd的根目录下，unzip展开

[root@yph7s html]# unzip phpMyAdmin-4.4.14.1-all-languages.zip

[root@yph7s html]# ln -sv phpMyAdmin-4.4.14.1-all-languages pma

‘pma’ -> ‘phpMyAdmin-4.4.14.1-all-languages’

[root@yph7s html]# cd pma

[root@yph7s pma]# cp config.sample.inc.php config.inc.php

[root@yph7s pma]# openssl rand -base64 15

qy7Kc61lR73hl6WjBb56

[root@yph7s pma]# vim config.inc.php -----把刚才的代码填到这里

$cfg['blowfish_secret'] = 'qy7Kc61lR73hl6WjBb56'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */

[root@yph7s pma]# systemctl start httpd.service

[root@yph7s pma]# yum install -y php-mbstring

[root@yph7s pma]# systemctl reload httpd.service

浏览器打开。浏览器输入pma不可以，但是输入目录原名可以，不知为啥。

创建用户：

MariaDB [(none)]> create database pmadb;

MariaDB [(none)]> grant all on pmadb.* to 'pma'@'172.16.%.%' identified by 'magedu';

MariaDB [(none)]> grant all privileges on pmadb.* to 'pma'@'172.16.%.%' identified by 'magedu';

数据类型：

表：行和列

创建表：定义表中的字段；

定义字段时，关键的一步即为确定其数据类型；

用于确定：数据存储格式、能参与运算种类、可表示的有效的数据范围；

整形：0-255或-127到128

字符型：字符集

码表：在字符和二进制数字之间建立映射关系；

MariaDB [(none)]> show character set; ----查看所有可用字符集

种类：

字符型：

定长字符型：会导致空间浪费

CHAR(#)：不区分字符大小写；#表示定义多少个字符

BINARY(#)：区分字符大小写

变长字符型：

VARCHAR(#)：不区分字符大小写；#表示最长多少个字符，最终占用#+1个字符，因为有个结束符。

VARBINARY(#)：区分字符大小写；

对象存储：只存一个指针

TEXT：2的32次方的字节

BLOB：

内置类型：

SET：集合，例如集合中有啊，a,b,c,d;必须是这些字符进行组合后的一个

ENUM：枚举，例如性别有两个选项，必须从中选择一个，不能有其他的

数值型：

精确数值型：

INT（TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT）

近似数值型：

FLOAT

DOBULE

日期时间型：

日期型：DATE

时间型：TIME

日期时间型：DATETIME

时间戳：TIMESTAMP

年份：YEAR(2), YEAR(4)

数据类型有修饰符：

UNSIGNED：无符号；

NOT NULL：非空；

DEFAULT value：默认值；

阅读全文

0 0