一、安裝MariaDB及其配置步驟   

成都創(chuàng)新互聯(lián)是一家專業(yè)提供通城企業(yè)網(wǎng)站建設(shè),專注與做網(wǎng)站、網(wǎng)站設(shè)計(jì)、H5開(kāi)發(fā)、小程序制作等業(yè)務(wù)。10年已為通城眾多企業(yè)、政府機(jī)構(gòu)等服務(wù)。創(chuàng)新互聯(lián)專業(yè)網(wǎng)站制作公司優(yōu)惠進(jìn)行中。

1)創(chuàng)建存放數(shù)據(jù)目錄及其用戶

mkdir -pv /mydata/data

groupadd -r MySQL   

useradd -g mysql -r mysql   

2)授權(quán)數(shù)據(jù)存放目錄

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)為數(shù)據(jù)庫(kù)提供配置文件及其啟動(dòng)腳本 

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)初始化數(shù)據(jù)庫(kù) 

cd /usr/local/mysql 

./configure --user=mysql--datadir=/mydata/data   

6)加入環(huán)境變量  

echo "exportPATH=/usr/local/mysql/bin:$PATH" >/etc/profile.d/mysql.sh  

source /etc/profile.d/mysql.sh  

7)啟動(dòng)數(shù)據(jù)庫(kù)  

/etc/init.d/mysqld start 或service mysqld start  

 二、服務(wù)端命令

1.DDL(數(shù)據(jù)定義語(yǔ)言)，分別主要用于管理數(shù)據(jù)庫(kù)組件，例如索引，視圖，用戶，存儲(chǔ)過(guò)程：create alter drop等。

DDL命令詳解：create,alter,drop   

創(chuàng)建：create 

    create{DATABASES|SCHEMA} [IF NOT EXISTS] db_name [create_spacification]....

    例如：create database if notexists testdb; 

    獲取幫助：help 或 help command 

    查看所有的數(shù)據(jù)庫(kù)：show databases; 

    查看支持的字符集：show character set;

    查看支持的所有排序的規(guī)則：show collation;

修改：alter 

    ALTER{DATABASE|SCHEMA} [db_name] alter_specification ...    

刪除：drop  

    DROP{DATABASE|SCHEMA} [db_name]  例如：drop database testdb;   

 表操作：

創(chuàng)建表

    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));

    查看表結(jié)構(gòu)：desc students; 

    查看建表過(guò)程：show create tablestudents\G  

    查看表的狀態(tài)信息：show table status like 'students'; show table status like '%stu%';

    查看表數(shù)據(jù)庫(kù)支持的存儲(chǔ)引擎：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;     

創(chuàng)建：

    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(數(shù)據(jù)操縱語(yǔ)言)：主要用于管理表中的數(shù)據(jù)，實(shí)現(xiàn)數(shù)據(jù)的增刪改查(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');

    注意：在使用字符型數(shù)據(jù)時(shí)需使用引號(hào)，數(shù)值則不需使用引號(hào)，使用也行。

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將序

刪除用戶賬號(hào)：

    drop user 'user'@'localhost';   

    dorp user 'testuser'@'192.168.%.%';  

授權(quán)： 

    grant privil_type on [object_type] db_name to'user'@'host' indentified by 'password'

privileges_type:

    *.*:所有的庫(kù)中的所有的表 

    db_name.*指定庫(kù)中的所有表

    db_name.tbl_name:指定用戶上的特定表 

    db_name>routine_name:指定庫(kù)上的存儲(chǔ)過(guò)程或函數(shù)   

    實(shí)例：grant selct,updateon mydb.students to 'testuser'@'192.168.%.%' identified by 'paswd'

              grant all *.* to'root'@'localhost' identified by 'password'