-
常见的SQL语句:
1)DDL—数据定义语言(CREATE,ALTER,DROP)–>运维
2)DML—数据操作语言(SELECT,INSTERT,UPDATE,DELETE) -->开发
3)DCL—数据控制语言(GRANT,REVOKE,COMMT,ROLLBACK)–>运维 -
创建数据库:
语法:create database 库名 (注意:库名不可以以数字开头)
MariaDB [(none)]> create database aaa;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| aaa |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
查看创建库时的语句:
MariaDB [(none)]> show create database aaa\G;
*************************** 1. row ***************************
Database: aaa
Create Database: CREATE DATABASE `aaa` /*!40100 DEFAULT CHARACTER SET latin1 */
1 row in set (0.00 sec)
创建一个名为jyy_gbk字符集为gbk的数据库;
MariaDB [(none)]> create database jyy_gbk DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| aaa |
| jyy_gbk |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
- 删除数据库:
语法:drop database 数据库名;
MariaDB [(none)]> drop database jyy_gbk;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| aaa |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
- 连接数据库:
命令:use 数据库名 //相当于linux下的cd切换目录,use是切换数据库 - 查看各项信息:
select database(); //显示当前所在的数据库;相当于linux下面的pwd
select version(); //查看数据库版本;
select user(); //查看当前数据库用户;
select now(); //查看当前时间;
show tables; //查看数据库的表里的信息;
6.创建MySQL用户及赋予用户权限
GRANT ALL ON db_name.* TO ‘jyy’@’local