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