DDL:
- 创建数据库,删除数据库,添加表字段,表索引等
- 主要关键词: create,drop
示例:
创建一个wzry的数据库,如果它不存在的话:
MySQL [(none)]> create database if not exists wzry;
Query OK, 1 row affected (0.00 sec)
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hahaha |
| mysql |
| performance_schema |
| test |
| wzry |
+--------------------+
6 rows in set (0.00 sec)
创建一个lol,指定字符集为utf8,支持中文,且不敏感大小写:
MySQL [(none)]> create database if not exists lol default charset utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hahaha |
| lol |
| mysql |
| performance_schema |
| test |
| wzry |
+--------------------+
7 rows in set (0.00 sec)
两者的区别:
MySQL [(none)]> show create database lol;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| lol | CREATE DATABASE `lol` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)
MySQL [(none)]> show create database wzry;
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| wzry | CREATE DATABASE `wzry` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
DQL
- 作用:从表中获取数据,确定数据应怎样在应用程序中输出;
- 主要关键字:select,
实例:
切换到mysql数据库中
MySQL [(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
Database changed
查看当前在哪个表:
MySQL [mysql]> select database();
+------------+
| database() |
+------------+
| mysql |
+------------+
1 row in set (0.00 sec)```
查看当前库中的表:
```bash
MySQL [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 [mysql]> desc user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | YES | | mysql_native_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
43 rows in set (0.00 sec)
查看user表中所有的信息:
MySQL [mysql]> select * from user;
指定字段查询:user,host,password
MySQL [mysql]> select user,host,password from user;
+------+----------------+-------------------------------------------+
| user | host | password |
+------+----------------+-------------------------------------------+
| root | localhost | *C70F0D3301B184823284BE84072EA5D41218F646 |
| root | mysql\_master1 | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | mysql\_master1 | |
| root | 192.168.10.% | *C70F0D3301B184823284BE84072EA5D41218F646 |
+------+----------------+-------------------------------------------+
7 rows in set (0.00 sec)
DML 数据操作:
- 主要作用:插入,更新,删除;
- 关键词: insert,update, delete
实例:
删除一些无用的用户数据:
delete from 数据库,数据表 where 条件;
mysql> select user,host,password from mysql.user;
+------+----------------+-------------------------------------------+
| user | host | password |
+------+----------------+-------------------------------------------+
| root | localhost | *C70F0D3301B184823284BE84072EA5D41218F646 |
| root | mysql\_master1 | |
| root | 127.0.0.1 | |
| | localhost | |
| | mysql\_master1 | |
| root | 192.168.10.% | *C70F0D3301B184823284BE84072EA5D41218F646 |
+------+----------------+-------------------------------------------+
6 rows in set (0.00 sec)
mysql> delete from mysql.user where user=' ';
Query OK, 2 rows affected (0.01 sec)
mysql> select user,host,password from mysql.user;
+------+----------------+-------------------------------------------+
| user | host | password |
+------+----------------+-------------------------------------------+
| root | localhost | *C70F0D3301B184823284BE84072EA5D41218F646 |
| root | mysql\_master1 | |
| root | 127.0.0.1 | |
| root | 192.168.10.% | *C70F0D3301B184823284BE84072EA5D41218F646 |
+------+----------------+-------------------------------------------+
4 rows in set (0.00 sec)
DCL授权控制:
通过GRANT 或REVOKE,授权用户许可,确定单个用户和用户组对数据库对象的访问。
实例:
#创建了一个用户,允许所有的网段登录,密码是jiegege
mysql> create user hujj@'%' identified by 'jiegege';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,password from mysql.user;
+------+----------------+-------------------------------------------+
| user | host | password |
+------+----------------+-------------------------------------------+
| root | localhost | *C70F0D3301B184823284BE84072EA5D41218F646 |
| root | mysql\_master1 | |
| root | 127.0.0.1 | |
| hujj | % | *C70F0D3301B184823284BE84072EA5D41218F646 |
| root | 192.168.10.% | *C70F0D3301B184823284BE84072EA5D41218F646 |
+------+----------------+-------------------------------------------+
5 rows in set (0.00 sec)
#查看用户权限:
USAGE,代表只有登录的权限
mysql> show grants for hujj@'%';
+-----------------------------------------------------------------------------------------------------+
| Grants for hujj@% |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'hujj'@'%' IDENTIFIED BY PASSWORD '*C70F0D3301B184823284BE84072EA5D41218F646' |
+-----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
给用户添加登录后执行DQL的权限:
#使用root给hujj用户添加权限,只允许用户看到mysql数据库user表;
#授权语句:
#允许hujj用户使用grant命令,结尾添加 with grant option
grant 给与的权限 on 数据库.数据表 to 用户@‘允许登录的主机’;
mysql> grant select on mysql.user to hujj@'%';
Query OK, 0 rows affected (0.00 sec)
#刷新状态:
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
#此时使用hujj用户登录:
#只能查看到mysql中有一个user表;
mysql> show tables from mysql;
+-----------------+
| Tables_in_mysql |
+-----------------+
| user |
+-----------------+
1 row in set (0.01 sec)
mysql> select user,host,password from mysql.user;
+------+----------------+-------------------------------------------+
| user | host | password |
+------+----------------+-------------------------------------------+
| root | localhost | *C70F0D3301B184823284BE84072EA5D41218F646 |
| root | mysql\_master1 | |
| root | 127.0.0.1 | |
| hujj | % | *C70F0D3301B184823284BE84072EA5D41218F646 |
| root | 192.168.10.% | *C70F0D3301B184823284BE84072EA5D41218F646 |
+------+----------------+-------------------------------------------+
5 rows in set (0.01 sec)