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

  1. 云栖社区>
  2. 博客>
  3. 正文

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

科技小先锋 2017-11-22 20:30:00 浏览650
展开阅读全文

一、安装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 

    查看所有的数据库:showdatabases; 

    查看支持的字符集: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 notexists students(id int unsigned not null,name varchar(20),age tinyintunsigned,gender enum('f','m'),primary key(id));

    查看表结构:desc students; 

    查看建表过程:show create tablestudents\G  

    查看表的状态信息:show table statuslike 'students'; show table status like '%stu%';

    查看表数据库支持的存储引擎:showengines; 

修改表: 

    ALTER[ONLINE|OFFLINE] [IGNORE] TABLE tbl_name [alter_spcification]  

字段:

    添加字段:ADD [column] col_namecolumn_definition  

    alter tablestudents add number int(13) not null; 

    alter tablestudents add number int(13) not null after age;  

    删除:DROP [COLNUM]col_name 

    alter tablestudents drop number; 

    修改:CHANGE [COLNUM]old_col_name new_col_name column_definition   

             MODIFY  [COLNUM] old_col_name new_col_name column_definition  

    alter tablestudents modify name varchar(30) after age;  

添加键:

    add {primarykey|unique key|foreign key}(col1,col2....)  

    alter tablestudents add primary key(number,id);

删除键:

    drop primarykey  

    alter tablestudents drop primary key 

索引:

    添加:add {index|key}{index_name} 

    删除:drop {index|key}{index_name}  

    alter table addindex age (age); 

    查看表上的索引信息:show indexes fromtbl_name;     

创建:

    CREATE[ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name

    [index_type]

    ON tbl_name(index_col_name,...)

    [index_option]...

    create indexname_and_class on students (name,age);

删除:

    drop indexname_and_class on students;

            

图示:

 wKioL1fzFqXBlGZFAABNjuLLmuQ894.png

2.DML(数据操纵语言):主要用于管理表中的数据,实现数据的增删改查(insert,delete,update,select) 

inset into:

    insert into:insert intotable_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; 

    selectcol1,col2,col3,....from tbl_name;    

    select id,namefrom students;

    selectcol1,col2 ... from tbl_name where clause; 

    select name,agefrom students where age >11 and age <30;

    select name,agefrom students where age between 11 and 30; 

    select name,agefrom students where age is not null;  

    select id,namefrom students order by id desc; #desc升序

    selectid,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'    

 

本文转自chengong1013 51CTO博客,原文链接:http://blog.51cto.com/purify/1858677,如需转载请自行联系原作者

网友评论

登录后评论
0/500
评论
科技小先锋
+ 关注