mysql---DDL语句

最新推荐文章于 2022-09-14 16:42:37 发布

原创最新推荐文章于 2022-09-14 16:42:37 发布 · 391 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #mysql

数据库专栏收录该内容

15 篇文章

订阅专栏

本文介绍了MySQL数据库的DDL操作，包括如何连接数据库、创建与删除数据库、选择和查看数据表、创建、删除及修改表结构，以及字段的增删改查等。重点讲解了各种DDL语句的使用方法及其效果。

数据库连接以后，一般通过

mysql -uroot -ppassword;

连接到数据库,mysql代表客户端命令，-u后面跟连接的数据库用户，-p后面表示需要输入密码，命令符的结束以;或者\g结束
创建user数据库

 create database user;
 Query OK, 1 row affected (0.01 sec)

query ok代表执行成功，1 row affected，受影响行数，0.01 sec代表操作时间
如果重复创建数据库会出现下列提示

mysql> create database user;
ERROR 1007 (HY000): Can't create database 'user'; database exists

查看当前系统中所有数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| localhost          |
| mysql              |
| performance_schema |
| sys                |
| test1              |
| user               |
+--------------------+
7 rows in set (0.01 sec)

information_schema 存储系统中的一些数据库对象信息，比如用户信息，列信息，权限信息，
mysql 存储用户权限信息
选择数据库

mysql> use user;
Database changed

显示当前数据库中的数据表

mysql> show tables;
Empty set (0.00 sec)

删除数据库

mysql> drop database test1;
Query OK, 0 rows affected (0.05 sec)

创建表emp

mysql> create table emp (ename varchar(10), hiredate date, sal decimal(10, 2), deptno int(2));
Query OK, 0 rows affected (0.04 sec)

查看emp表

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(10)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

更详细的信息

mysql> show create table emp \G;
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `ename` varchar(10) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` decimal(10,2) DEFAULT NULL,
  `deptno` int(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ERROR: 
No query specified

可以看到engine和charset \G让记录可以按照字段竖着排列

删除表

mysql> drop table emp;
Query OK, 0 rows affected (0.03 sec)

修改表

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| age      | int(3)        | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> alter table emp modify ename varchar(30);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(30)   | YES  |     | NULL    |       |
| age      | int(3)        | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

修改表增加一个column

mysql> alter table emp add column sex varchar(6);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(30)   | YES  |     | NULL    |       |
| age      | int(3)        | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| sex      | varchar(6)    | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

修改表，删除一个column

mysql> alter table emp drop sex;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(30)   | YES  |     | NULL    |       |
| age      | int(3)        | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

修改表的column字段名称和类型

mysql> alter table emp change ename name varchar(40);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| name     | varchar(40)   | YES  |     | NULL    |       |
| age      | int(3)        | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

change 和 modify都是修改表 change可以修改表字段而modify不可以

after添加一个字段在某个字段之后

mysql> alter table emp add address after name;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'after name' at line 1
mysql> alter table emp add address varchar(30) after name;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| name     | varchar(40)   | YES  |     | NULL    |       |
| address  | varchar(30)   | YES  |     | NULL    |       |
| age      | int(3)        | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

修改一个字段的位置让他处于表的最前方

mysql> alter table emp modify age int(5) first;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| age      | int(5)        | YES  |     | NULL    |       |
| name     | varchar(40)   | YES  |     | NULL    |       |
| address  | varchar(30)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

表改名

mysql> alter table emp rename userinfo;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+----------------+
| Tables_in_user |
+----------------+
| userinfo       |
+----------------+
1 row in set (0.00 sec)