SQL语句分为四类，DDL,DML,DQL,DCL。
DDL 对数据库，表进行操作。
mysql> show databases; -- 显示当前MySQL下数据库的名称
+--------------------+
| Database |
+--------------------+
| db1； |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> create database if not exists db2;-- 创建数据库
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> drop database if exists db2;-- 删除数据库
Query OK, 0 rows affected (0.00 sec)
mysql> use db1;-- 使用数据库
Database changed
mysql> select database();-- 查看当前使用的数据库
+------------+
| database() |
+------------+
| db1 |
+------------+
1 row in set (0.00 sec)
mysql> show tables;-- 查询当前数据库下有哪些表
Empty set (0.01 sec)
mysql> desc func;-- 查询具体表的结构
+-------+------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| name | char(64) | NO | PRI | | |
| ret | tinyint | NO | | 0 | |
| dl | char(128) | NO | | | |
| type | enum('function','aggregate') | NO | | NULL | |
+-------+------------------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> create table tb_user( -- 创建表
-> id int,
-> username varchar(20),
-> password varchar(32)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> desc tb_user;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| username | varchar(20) | YES | | NULL | |
| password | varchar(32) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> drop table if exists student;-- 删除表
Query OK, 0 rows affected (0.01 sec)
mysql> alter table tb_user rename to stu;-- 修改表名
Query OK, 0 rows affected (0.01 sec)
mysql> desc stu;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| username | varchar(20) | YES | | NULL | |
| password | varchar(32) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> alter table stu add address varchar(50);-- 在表中添加一列
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc stu;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| username | varchar(20) | YES | | NULL | |
| password | varchar(32) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> alter table stu modify address char(50); -- 修改表中数据类型
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table stu change address addr varchar(30);-- 一起修改表中列名和数据类型
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc stu;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| username | varchar(20) | YES | | NULL | |
| password | varchar(32) | YES | | NULL | |
| addr | varchar(30) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> alter table stu drop addr; -- 删除表中列
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc stu;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| username | varchar(20) | YES | | NULL | |
| password | varchar(32) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
DML 对表中数据进行增删改。
DQL 对表中数据进行查询。
DCL 对数据库进行权限控制。
