MariaDB
| OS7-YUM安装mariadb |
| yum install mariadb-server mariadb mariadb-libs -y |
| 查看版本 |
| # rpm -qi mariadb |
| 启动DB |
| systemctl start mariadb |
| 查看是否运行 |
| # ps -ef | grep mysqld |
| # mysql |
| 启动并设置开机自启动 |
| [root@CentOS7-104 ~]# systemctl start mariadb |
| [root@CentOS7-104 ~]# systemctl enable mariadb |
| Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service. |
| 初始化数据库 |
| [root@CentOS7-104 ~]# mysql_secure_installation |
| 设置root 密码初始为空， |
| Enter current password for root (enter for none): |
| OK, successfully used password, moving on... |
| Setting the root password ensures that nobody can log into the MariaDB |
| root user without the proper authorisation. |
| Set root password? [Y/n] y |
| New password: |
| Re-enter new password: |
| Password updated successfully! |
| Reloading privilege tables.. |
| ... Success! |
| Normally, root should only be allowed to connect from 'localhost'. This |
| ensures that someone cannot guess at the root password from the network. |
| Disallow root login remotely? [Y/n] n |
| ... skipping. |
| By default, MariaDB comes with a database named 'test' that anyone can |
| access. This is also intended only for testing, and should be removed |
| before moving into a production environment. |
| Remove test database and access to it? [Y/n] y |
| - Dropping test database... |
| ... Success! |
| - Removing privileges on test database... |
| ... Success! |
| Reloading the privilege tables will ensure that all changes made so far |
| will take effect immediately. |
| Reload privilege tables now? [Y/n] y |
| ... Success! |
| Cleaning up... |
| All done! If you've completed all of the above steps, your MariaDB |
| installation should now be secure. |
| Thanks for using MariaDB! |
| 登录DB |
| [root@CentOS7-104 ~]# mysql -uroot -p |
| 创建用户 |
| MariaDB [(none)]> CREATE USER bshen@localhost IDENTIFIED BY '123456'; |
| Query OK, 0 rows affected (0.00 sec) 修改密码格式 >set password for user@localhost=password('密码'); |
| 以插入的形式创建 |
| MariaDB [pengyou]> insert into mysql.user(user,host,password) values('damao','localhost',password('123456')); |
| Query OK, 1 row affected, 4 warnings (0.00 sec) |
| 查看当前用户 |
| MariaDB [(none)]> select user(); |
| +--------------------+ |
| | user() | |
| +--------------------+ |
| | bshen@localhost | |
| +--------------------+ |
| 1 row in set (0.00 sec) |
| 查看数据库上的所有用户 |
| MariaDB [pengyou]> select user from mysql.user; |
| +----------+ |
| | user | |
| +----------+ |
| | bshen | |
| | root | |
| | root | |
| | root | |
| | damao | |
| +----------+ |
| 7 rows in set (0.00 sec) |
| 查看 用户权限 |
| MariaDB [(none)]> show grants for bshen@localhost; |
| 看不到权限表记得刷新一下 |
| MariaDB [pengyou]> show grants for damao@localhost; |
| ERROR 1141 (42000): There is no such grant defined for user 'damao' on host 'localhost' |
| MariaDB [pengyou]> flush privileges; |
| Query OK, 0 rows affected (0.00 sec) |
| MariaDB [pengyou]> show grants for damao@localhost; |
| +--------------------------------------------------------------------------------------------------------------+ |
| | Grants for damao@localhost | |
| +--------------------------------------------------------------------------------------------------------------+ |
| | GRANT USAGE ON *.* TO 'damao'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' | |
| +--------------------------------------------------------------------------------------------------------------+ |
| 1 row in set (0.00 sec) |
| 给普通用户授权 |
| GRANT all privileges ON databasename.tablename TO 'username'@'localhost' |
| grant all privileges on *.* to bshen@localhost ;#授权所有库全部操作仅限于localhost主机通过该用户访问本地库的所有权限 |
| 例如 |
| MariaDB [pengyou]> grant all on pengyou.* to wangtao@localhost identified by 'wangtao'; Query OK, 0 rows affected (0.00 sec) |
| 创建库 |
| MariaDB [mysql]> create database tongshi; |
| Query OK, 1 row affected (0.00 sec) |
| 查看库 |
| MariaDB [mysql]> show databases; |
| +--------------------+ |
| | Database | |
| +--------------------+ |
| | information_schema | |
| | mysql | |
| | performance_schema | |
| | tongshi | |
| +--------------------+ |
| 4 rows in set (0.00 sec) |
| 创建表 |
| MariaDB [tongshi]> create table yuwen (id varchar(20),name varchar(20),fenshu varchar(20)); |
| Query OK, 0 rows affected (2.26 sec) |
| 查看表 |
| MariaDB [tongshi]> show tables; |
| +-------------------+ |
| | Tables_in_tongshi | |
| +-------------------+ |
| | yuwen | |
| +-------------------+ |
| 1 row in set (0.00 sec) |
| 查看表&查询表数据 |
| MariaDB [tongshi]> insert into yuwen values ("01","xiaoming","78"); |
| Query OK, 1 row affected (0.00 sec) |
| MariaDB [tongshi]> select * from yuwen; |
| +------+----------+--------+ |
| | id | name | fenshu | |
| +------+----------+--------+ |
| | 01 | xiaoming | 78 | |
| +------+----------+--------+ |
| 1 row in set (0.00 sec) |
| select 用法 |
| select version(); #显示数据库版本 |
| select now(); #显示时间 |
| select database();#查看当前使用的数据库 |
| MariaDB [pengyou]> select user from mysql.user;#查看数据库用户列表 |
| +----------+ |
| | user | |
| +----------+ |
| | baosheng | |
| show tables; #查看当前库下所有表 |
| show create database databasename;#查看创建数据的语句 |
| create database databasename charset=utf8#创建数据库 |
| 创建表 |
| -- int unsigned 无符号整型 |
| -- auto_increment 自动增长 |
| -- not null 不能为空 |
| --primary key 主键 |
| -- default 默认值 |
| create table tablename（字段 类型 约束[字段 类型 约束]); |
| create table tablename(id int unsigned primary key not null auto_increment,name varchar(20)); |
| create table studnts(id int unsigned primary key not null auto_increment,name varchar(20),age int unsigned,high decimal(5,2),gender enum("男","女","保密") default"保密",cls_id int); |
| 修改表 |
| alter table classes add jkjkjk varchar(20);#添加字段 |
| alter table classes modify jkjkjk varchar(22);#修改字段 |
| alter table classes change jkjkjk mafan varchar(20);#修改名字类型及约束 |
| alter table classes drop mafan;#删除字段 |
| use databasename;#切换使用数据库 |
| drop database databasename;#删除数据库 |
| drop table tablename;#删除表 |
| desc tablename; #查看表机构 |
| flush privileges;#刷新权限 |
| MariaDB [pengyou]> drop user bshen@"localhost";#删除用户:@"%"/@"localhost"/@"hostIP"(注意三种格式） |
| Query OK, 0 rows affected (0.00 sec) |
| 退出 |
| exit; |
| \q |
| quit |