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语句分类:

  1. 数据定义语言(DDL

{create table , create view,create index,alter table,drop table,drop view,drop index }

   创建,修改或删除数据库中的各种对象。包括表。视图。索引

  1. 查询语言(DQL)

    {select....from...where}

    按照指定的组合,条件表达式或排嗦已存在的数据库中的数据

  1. 数据操纵语言(DML)

   {insert,update,delete}

    对已存在的数据库进行元组的插入,删除,修改等操作。

  1. 数据控制语言(DCL)

{grant,revoke,commit,rollback}

    给予授权和收回数据库的某种特权

 

 

 

可以用yum安装mariadbmysql一样。简单快捷,

[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)