一、安装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(数据操纵