MariaDB常用的客服端及其服务端命令

225次阅读

共计 4520 个字符，预计需要花费 12 分钟才能阅读完成。

一、安装 MariaDB 及其配置步骤

1)创建存放数据目录及其用户
mkdir -pv /mydata/data
groupadd -r mysql
useradd -g mysql -r mysql
2) 授权数据存放目录
chown -R /mydata/data
3) 解压 mariadb-5.5.48-linux-x86_64.tar.gz
tar xfmariadb-5.5.48-linux-x86_64.tar.gz -C /usr/loca/mysql
4)为数据库提供配置文件及其启动脚本
cd /usr/local/mysql
cp support-files/my-large.cnf /etc/my.cnf
cp support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
chkconfig –add /etc/init.d/mysqld
chkconfig mysqld on
5)初始化数据库
cd /usr/local/mysql
./configure –user=mysql–datadir=/mydata/data
6)加入环境变量
echo “exportPATH=/usr/local/mysql/bin:$PATH” >/etc/profile.d/mysql.sh
source /etc/profile.d/mysql.sh
7)启动数据库
/etc/init.d/mysqld start 或 service mysqld start

二、服务端命令
1.DDL(数据定义语言)，分别主要用于管理数据库组件，例如索引，视图，用户，存储过程：create alter drop 等。
DDL 命令详解：create,alter,drop
创建：create
create{DATABASES|SCHEMA} [IF NOT EXISTS] db_name [create_spacification]….
例如：create database if notexists testdb;
获取帮助：help 或 help command
查看所有的数据库：show databases;
查看支持的字符集：show character set;
查看支持的所有排序的规则：show collation;
修改：alter
ALTER{DATABASE|SCHEMA} [db_name] alter_specification …
删除：drop
DROP{DATABASE|SCHEMA} [db_name] 例如：drop database testdb;
表操作：
创建表
create[temporary] talbe [if not exists] tbl_name (create_definition,….)
例如：create table if not exists students(id int unsigned not null,name varchar(20),age tinyint unsigned,gender enum(‘f’,’m’),primary key(id));
查看表结构：desc students;
查看建表过程：show create tablestudents\G
查看表的状态信息：show table status like ‘students’; show table status like ‘%stu%’;
查看表数据库支持的存储引擎：show engines;
修改表：
ALTER[ONLINE|OFFLINE] [IGNORE] TABLE tbl_name [alter_spcification]
字段:
添加字段：ADD [column] col_namecolumn_definition
alter table students add number int(13) not null;
alter table students add number int(13) not null after age;
删除：DROP [COLNUM]col_name
alter table students drop number;
修改：CHANGE [COLNUM]old_col_name new_col_name column_definition
MODIFY [COLNUM] old_col_name new_col_name column_definition
alter table students modify name varchar(30) after age;
添加键：
add {primarykey|unique key|foreign key}(col1,col2….)
alter table students add primary key(number,id);
删除键：
drop primarykey
alter table students drop primary key
索引：
添加：add {index|key}{index_name}
删除：drop {index|key}{index_name}
alter table add index age (age);
查看表上的索引信息：show indexes from tbl_name;
创建：
CREATE[ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[index_type]
ON tbl_name(index_col_name,…)
[index_option]…
create index name_and_class on students (name,age);
删除：
drop index name_and_class on students;
DML(数据操纵语言)：主要用于管理表中的数据，实现数据的增删改查 (insert,delete,update,select)
inset into:
insert into:insert into table_name [cols…….] value (val1,val2,val3….)
例如：insert into studentsvalues (1,’alren’,25,’m’,’one’,169);
insert into students (id,name) values(1,’alren’),(2,’chen’);
注意：在使用字符型数据时需使用引号，数值则不需使用引号，使用也行。
delete:
delete from students where age is null;
delete from studnets order by age desc limit 20;
update
update students set age=age+15 where name like’%ren%’;
select:
select * fromtbl_name;
select col1,col2,col3,….from tbl_name;
select id,namefrom students;
select col1,col2 … from tbl_name where clause;
select name,age from students where age >11 and age <30;
select name,age from students where age between 11 and 30;
select name,age from students where age is not null;
select id,name from students order by id desc; #desc 升序
select id,name,age from students order by id asc; #asc 将序

删除用户账号：
drop user ‘user’@’localhost’;
dorp user ‘testuser’@’192.168.%.%’;
授权：
grant privil_type on [object_type] db_name to’user’@’host’ indentified by ‘password’
privileges_type:
*.*: 所有的库中的所有的表
db_name.* 指定库中的所有表
db_name.tbl_name: 指定用户上的特定表
db_name>routine_name: 指定库上的存储过程或函数
实例：grant selct,updateon mydb.students to ‘testuser’@’192.168.%.%’ identified by ‘paswd’
grant all *.* to’root’@’localhost’ identified by ‘password’