您好,登錄后才能下訂單哦!
一、安裝MariaDB及其配置步驟
1)創(chuàng)建存放數(shù)據(jù)目錄及其用戶
mkdir -pv /mydata/data
groupadd -r mysql
useradd -g mysql -r mysql
2)授權數(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ù)庫提供配置文件及其啟動腳本
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ù)庫
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)啟動數(shù)據(jù)庫
/etc/init.d/mysqld start 或service mysqld start
二、服務端命令
1.DDL(數(shù)據(jù)定義語言),分別主要用于管理數(shù)據(jù)庫組件,例如索引,視圖,用戶,存儲過程: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ù)庫: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));
查看表結構:desc students;
查看建表過程:show create tablestudents\G
查看表的狀態(tài)信息:show table status like 'students'; show table status like '%stu%';
查看表數(shù)據(jù)庫支持的存儲引擎: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ù)操縱語言):主要用于管理表中的數(shù)據(jù),實現(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ù)值則不需使用引號,使用也行。
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:指定庫上的存儲過程或函數(shù)
實例:grant selct,updateon mydb.students to 'testuser'@'192.168.%.%' identified by 'paswd'
grant all *.* to'root'@'localhost' identified by 'password'
免責聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權內(nèi)容。