mariadb常用的客服端及其服务端命令
一、安装MariaDB及其配置步骤 1)创建存放数据目录及其用户mkdir -pv /mydata/datagroupadd -r mysql useradd -g mysql -r mysql 2)授权数据存放目录chown -R /mydata/data3)解压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/mysqldchmod +x /etc/init.d/mysqldchkconfig --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.shsource /etc/profile.d/mysql.sh7)启动数据库/etc/init.d/mysqld start 或service mysqld start 二、服务端命令1.DDL(数据定义语言)，分别主要用于管理数据库组件，例如索引，视图，用户，存储过程：create alter drop等。DDL命令详解：create,alter,drop 创建：create create{DATABASES|SCHEMA} db_name .... 例如：create database if notexists testdb; 获取帮助：help 或 help command 查看所有的数据库：show databases; 查看支持的字符集：show character set; 查看支持的所有排序的规则：show collation;修改：alter ALTER{DATABASE|SCHEMA} alter_specification ... 删除：drop DROP{DATABASE|SCHEMA} 例如：drop database testdb; 表操作：创建表 create talbe 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 TABLE tbl_name 字段: 添加字段：ADD col_namecolumn_definition alter table students add number int(13) not null; alter table students add number int(13) not null after age; 删除：DROP col_name alter table students drop number; 修改：CHANGE old_col_name new_col_name column_definition MODIFY 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 INDEX index_name ON tbl_name(index_col_name,...) ... 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 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 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'
页:
[1]