数据库一些简单的sql语句

1、数据库下载

yum install mariadb-server mariadb -y

2、启动并查看

[root@web01 ~]# systemctl start mariadb.service     （启动数据库）
[root@web01 ~]# systemctl enable mariadb.service   （开机自启动）
[root@web01 ~]# ss -lntup |grep mysql 
tcp    LISTEN     0      50        *:3306                  *:*                   users:(("mysqld",pid=4336,fd=13))
[root@web01 ~]# ps -ef |grep mysql 
mysql     4174     1  0 12:08 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
mysql     4336  4174  0 12:08 ?        00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock
root      4374  3551  0 12:08 pts/0    00:00:00 grep --color=auto mysql

3、输入mysql进入

1.查看:show

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

2.查看系统中所有的用户

MariaDB [(none)]> select user,host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1       |
|      | localhost |
| root | web01     |
+------+-----------+
6 rows in set (0.00 sec)

3、创建数据库

MariaDB [(none)]> create   database  wordpress;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| wordpress          |
+--------------------+
5 rows in set (0.00 sec)

4、创建用户并设置密码

创建用户并设置密码
1.grant all      on wordpress.*       to 'wordpress'@'172.16.1.%'  identified by '123456';
2.grant all      on wordpress.*       to 'wordpress'@'localhost'  identified by '123456';
所有权限    wordpress数据库.所有表 '用户名'@'172.登录'     密码是 123456 

5、查询所有数据库

MariaDB [(none)]> show tables from mysql;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
24 rows in set (0.00 sec)

6、查看user表中字段 user和host字段

MariaDB [(none)]> select user,host  from mysql.user;
+-----------+------------+
| user      | host       |
+-----------+------------+
| root      | 127.0.0.1  |
| wordpress | 172.16.1.% |
| root      | ::1        |
|           | localhost  |
| root      | localhost  |
| wordpress | localhost  |
|           | web01      |
| root      | web01      |
+-----------+------------+
8 rows in set (0.00 sec)

7、数据比较多的时候使用\G

MariaDB [(none)]> select user,host  from mysql.user \G
*************************** 1. row ***************************
user: root
host: 127.0.0.1
*************************** 2. row ***************************
user: wordpress
host: 172.16.1.%
*************************** 3. row ***************************
user: root
host: ::1
*************************** 4. row ***************************
user: 
host: localhost
*************************** 5. row ***************************
user: root
host: localhost
*************************** 6. row ***************************
user: wordpress
host: localhost
*************************** 7. row ***************************
user: 
host: web01
*************************** 8. row ***************************
user: root
host: web01
8 rows in set (0.00 sec)

8、限制显示 limit

MariaDB [(none)]> select user,host  from mysql.user limit  1\G
*************************** 1. row ***************************
user: root
host: 127.0.0.1
1 row in set (0.00 sec)

9、查看当前用户

MariaDB [(none)]> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.07 sec)

10、显示当前在哪

MariaDB [(none)]>表示那个库都没进
MariaDB [mysql]> select database(); 显示当前使用的数据库
+------------+
| database() |
+------------+
| mysql      |
+------------+
1 row in set (0.00 sec)

11、use指定进入那个数据库用户

MariaDB [(none)]> use  mysql  进入数据库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

12、删除用户

drop  database   wordpresd；
drop  user 'oldboy'@'localhost';

13、修改完以后如何生效

MariaDB [(none)]> flush  privileges;   更新权限信息
Query OK, 0 rows affected (0.06 sec)

这就是一些简单的数据库基础sql语句！！！

如何退出
1.exit
2.ctrl+d