SQL语句介绍以及如何使用
DDL：创建
DQL：查询
DML：数据操作，修改
DCL：授权
一、DDL
创建数据库，删除数据库，添加表字段，表索引
1、创建数据库
mysql> create database lol ;
Query OK, 1 row affected (0.02 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| lol |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> create database if not exists lol;
Query OK, 1 row affected (0.00 sec)
mysql> create database if not exists lol2 default charset utf8 collate utf8_general_ci;
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show create database lol;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| lol | CREATE DATABASE `lol` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create database lol2;
+----------+---------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------+
| lol2 | CREATE DATABASE `lol2` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)
2、删除数据库
mysql> drop database lol;
Query OK, 0 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql>
二、DQL
1、查询mysql库中默认的用户表信息，进行展示
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| lol |
| lol2 |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
mysql> 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
Database changed
mysql> select database();（查询自己当前所在的数据库）
+------------+
| database() |
+------------+
| mysql |
+------------+
1 row in set (0.00 sec)
mysql> show tables;（查看数据库中的数据表）
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
28 rows in set (0.00 sec)
mysql> desc user;(查看数据表的结构)
mysql> select * from user;（查看user表中所有的信息）
mysql> select user,host,password from user;
+------+-----------------------+-------------------------------------------+
| user | host | password |
+------+-----------------------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | localhost.localdomain | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | localhost.localdomain | |
| root | 192.168.0.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------+-----------------------+-------------------------------------------+
7 rows in set (0.00 sec)
mysql>
三、DML
（insert、update、delete）
mysql> select user,host,password from user;
+------+-----------------------+-------------------------------------------+
| user | host | password |
+------+-----------------------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | localhost.localdomain | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | localhost.localdomain | |
| root | 192.168.0.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------+-----------------------+-------------------------------------------+
7 rows in set (0.00 sec)
mysql> delete from mysql.user where user='root' and host='localhost.localdomain';
Query OK, 1 row affected (0.00 sec)
mysql>
四、DCL授权控制
1、创建mysql的用户
语法：
create user 名字@'允许登录的主机网段' identified '密码'；
%：表示所有的主机网段，只要防火墙允许，都可以访问到数据库
mysql> create user peng@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,password from mysql.user;
+------+-----------------------+-------------------------------------------+
| user | host | password |
+------+-----------------------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| peng | % | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | localhost.localdomain | |
| root | 192.168.0.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------+-----------------------+-------------------------------------------+
7 rows in set (0.00 sec)
mysql>
2、给用户设置权限
语法：
grant 给予的权限 on 数据库.数据表 to 用户@'允许登录的主机'（with grant option）;
如果允许用户使用grant命令，在结尾加with grant option
mysql> show grants for peng@'%';（查看用户权限）
+-----------------------------------------------------------------------------------------------------+
| Grants for peng@% |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'peng'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+-----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
默认创建的用户只有一个USAGE权限，只能登录数据库，没有其他权限！
mysql> grant select on mysql.user to peng@'%';（给peng这个用户授予查询的权限）
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;（刷新权限）
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for peng@'%';（查看peng用户的权限）
+--------------------------------------------------------------------------------------------------------------+
| Grants for peng@% |
+--------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'peng'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT SELECT ON `mysql`.`user` TO 'peng'@'%' |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>
