Mysql是一个真正的多线程,多应用的sql数据库服务软件。
优点:查询速度快,高性能。高可靠和易于使用等特性。
关系型数据库:
商业:Oracle, Sybase lnfomix SQL Server DB2
开源:mySQL postgreSQL pgsql,EnterpriseDB
非关系型数据库NOsql:
MongoDB(文档型数据库) Redis HBase Memcached
Mysql -u 指定用户名
Mysql -p 指定密码(选项和密码之间不能有空格)
-h 指定主机
-P 指定端口
-S 指定Socket文件
-e 指定SQL命令语句(非交互)
SQL语句分类:
- 数据定义语言(DDL)
{create table , create view,create index,alter table,drop table,drop view,drop index }
创建,修改或删除数据库中的各种对象。包括表。视图。索引
- 查询语言(DQL)
{select....from...where}
按照指定的组合,条件表达式或排嗦已存在的数据库中的数据
- 数据操纵语言(DML)
{insert,update,delete}
对已存在的数据库进行元组的插入,删除,修改等操作。
- 数据控制语言(DCL)
{grant,revoke,commit,rollback}
给予授权和收回数据库的某种特权
可以用yum安装mariadb与mysql一样。简单快捷,
[root@localhost ~]# yum -y install mariadb mariadb-server
启动mariadb
[root@localhost ~]# systemctl start mariadb
查看端口:
[root@localhost ~]# netstat -lnpt
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 3335/mysqld
添加进入数据库进入密码:
[root@localhost ~]# mysqladmin -uroot password '123'
如果要修改密码:
mysqladmin -u root -p旧的密码 password '新的密码'
[root@localhost ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 5.5.64-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
==============================================================================================================================================================
登录数据库;
mysql -uroot -p
Enter password: ---后面跟密码
查看数据库列表信息:
mysql> show databases;
显示数据表的结构:
mysql> describe mysql.user
-> ;
切换表:
mysql> use mysql;
创建一个新库:
mysql> create database auth;
Query OK, 1 row affected (0.00 sec)
切换:
mysql> use auth;
在库内添加这个新表:
mysql> create table users (user_name char(20) not null, user_passwd char(30) default " ", primary key (user_name));
Query OK, 0 rows affected (0.01 sec)
在auth库中,创建名为users的表,表内两个字段为(user_name 最多20字节 不能为空,user_passwd最多30字节 默认为空,索引关键字 user_name)
删除数据表:
MariaDB [auth]> drop table users;
Query OK, 0 rows affected (0.00 sec)
删除数据库:
MariaDB [auth]> drop database lisi;
Insert插入新数据,
MariaDB [auth]> insert into users(user_name,user_passwd) values('zhangsan','123456');
Query OK, 1 row affected (0.00 sec)
加密存储密码:
MariaDB [auth]> insert into users values('lisi',password('123'));
Query OK, 1 row affected, 1 warning (0.01 sec)
Update更改原有数据
清空lisi的密码:
MariaDB [auth]> update auth.users set user_passwd=password('')where user_name='lisi';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
查询表中的数据;
MariaDB [auth]> select * from auth.users;
+-----------+--------------------------------+
| user_name | user_passwd |
+-----------+--------------------------------+
| lisi | |
| zhangsan | *23AE809DDACAF96AF0FD78ED04B6A |
+-----------+--------------------------------+
2 rows in set (0.00 sec)
查询数据库有哪些表
MariaDB [auth]> show tales;
查询条件内容:
MariaDB [auth]> select * from users where user_name='zhangsan';
查询前三列:
MariaDB [auth]> select Host,User,Password from mysql.user;
查询条件:
MariaDB [auth]> select Host,User,Password from mysql.user where Host='127.0.0.1';
+-----------+------+----------+
| Host | User | Password |
+-----------+------+----------+
| 127.0.0.1 | root | |
+-----------+------+----------+
1 row in set (0.00 sec)
把密码改成密文显示密码:
MariaDB [auth]> update users set user_passwd=password('123456') where user_name='zhangsan';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
批量添加密码:
MariaDB [mysql]> select user,password from user;
+------+-------------------------------------------+
| user | password |
+------+-------------------------------------------+
| root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | |
| root | |
| root | |
| | |
| | |
+------+-------------------------------------------+
6 rows in set (0.00 sec)
MariaDB [mysql]> update user set password=password('123456') where user='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
MariaDB [mysql]> select user,password from user;
+------+-------------------------------------------+
| user | password |
+------+-------------------------------------------+
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| | |
| | |
+------+-------------------------------------------+
6 rows in set (0.00 sec)
刷新授权表:
MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
删除数据,不是删除表,不一样:
MariaDB [auth]> delete from users where user_name='lisi';
Query OK, 1 row affected (0.00 sec)
收回权限:
MariaDB [auth]> revoke select on crushlinux.* from 'mengwei'@'localhost';
Query OK, 0 rows affected (0.00 sec)
查看当前的登录用户:
MariaDB [auth]> show grants;
授权客户机远程登录:
MariaDB [auth]> grant all on *.* to 'root'@'192.168.200.110' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
MariaDB [auth]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
列:
[root@localhost ~]# cd /var/lib/mysql/mysql/
[root@localhost mysql]# ls
columns_priv.frm general_log.CSM help_topic.frm proc.frm slow_log.CSM time_zone_name.frm
columns_priv.MYD general_log.CSV help_topic.MYD proc.MYD slow_log.CSV time_zone_name.MYD
columns_priv.MYI general_log.frm help_topic.MYI proc.MYI
func.MYI help_relation.MYI plugin.MYI servers.MYI time_zone.MYI
查看表的结构:
MariaDB [mysql]> describe user;
这是在外面查询数据库结构
MariaDB [mysql]> describe mysql.user;
授权,并创建用户:
MariaDB [auth]> GRANT select ON crushlinux.* TO 'mengwei'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
授权客户机远程登录:
MariaDB [auth]> grant all on *.* to 'root'@'192.168.200.110' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
MariaDB [auth]> flush privileges;
Query OK, 0 rows affected (0.00 sec)