深入浅出MySQL(1)

来源：互联网发布：孟庭苇离婚知乎编辑：程序博客网时间：2024/05/22 15:24

创建一个数据库test1：

mysql> create database test1;Query OK, 1 row affected (0.03 sec)

查询系统中有哪些数据库:

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                || test1              |+--------------------+5 rows in set (0.23 sec)

都是一些内置数据库。接下来选择要操作的数据库,并查看数据表:

mysql> use test1Database changedmysql> show tables;Empty set (0.02 sec)

数据表为空，查看一下内置数据库mysql中的表:

mysql> use mysqlDatabase changedmysql> show tables;+---------------------------+| Tables_in_mysql           |+---------------------------+| columns_priv              || db                        || engine_cost               || event                     || func                      || general_log               || gtid_executed             || help_category             || help_keyword              || help_relation             || help_topic                || innodb_index_stats        || innodb_table_stats        || ndb_binlog_index          || plugin                    || proc                      || procs_priv                || proxies_priv              || server_cost               || 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                      |+---------------------------+31 rows in set (0.00 sec)

这个数据库存储的是系统的用户权限信息。
删除数据库:

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

在数据库中创建一张表的语法是:
CREATE TABLE tablename(column_name_1 column_type_1 constraints,column_name_2 column_type_2 constraints, ……column_name_n column_type_n constraints).
创建一个表emp,有3个字段，ename,hiredate,sal,类型为varchar(10),date,int(2):

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

查看表的定义:

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.03 sec)

查看表比较全面的定义信息:

mysql> show create table emp \G;*************************** 1. row ***************************       Table: empCreate 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=latin11 row in set (0.00 sec)ERROR:No query specified

使用alter table 更改表结构语法:
ALTER TABLE tablename MODIFY[COLUM]column_definition[FIRST | AFTER col_name]
如将emp字段的ename varchar(10)修改:

mysql> alter table emp modify ename varchar(20);Query OK, 0 rows affected (0.22 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc emp;+----------+---------------+------+-----+---------+-------+| Field    | Type          | Null | Key | Default | Extra |+----------+---------------+------+-----+---------+-------+| ename    | varchar(20)   | YES  |     | NULL    |       || hiredate | date          | YES  |     | NULL    |       || sal      | decimal(10,2) | YES  |     | NULL    |       || deptno   | int(2)        | YES  |     | NULL    |       |+----------+---------------+------+-----+---------+-------+4 rows in set (0.00 sec)

表中增加字段的语法:
ALTER TABLE tablename ADD[COLUM]column_definition[FIRST | AFTER col_name]
如增加字段age:

mysql> alter table emp add column age int(3);Query OK, 0 rows affected (0.73 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc emp;+----------+---------------+------+-----+---------+-------+| Field    | Type          | Null | Key | Default | Extra |+----------+---------------+------+-----+---------+-------+| ename    | varchar(20)   | YES  |     | NULL    |       || hiredate | date          | YES  |     | NULL    |       || sal      | decimal(10,2) | YES  |     | NULL    |       || deptno   | int(2)        | YES  |     | NULL    |       || age      | int(3)        | YES  |     | NULL    |       |+----------+---------------+------+-----+---------+-------+5 rows in set (0.00 sec)

删除表字段语法:
ALTER TABLE tablename DROP[COLUM] column_name

mysql> alter table emp drop column age;Query OK, 0 rows affected (0.44 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc emp;+----------+---------------+------+-----+---------+-------+| Field    | Type          | Null | Key | Default | Extra |+----------+---------------+------+-----+---------+-------+| ename    | varchar(20)   | YES  |     | NULL    |       || hiredate | date          | YES  |     | NULL    |       || sal      | decimal(10,2) | YES  |     | NULL    |       || deptno   | int(2)        | YES  |     | NULL    |       |+----------+---------------+------+-----+---------+-------+4 rows in set (0.00 sec)

给字段重命名的语法:
ALTER TABLE tablename CHANGE[COLUM]old_col_name column_definition [FIRST | AFTER col_name]

mysql> alter table emp change age age1 int(4);Query OK, 0 rows affected (0.09 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc emp;+----------+---------------+------+-----+---------+-------+| Field    | Type          | Null | Key | Default | Extra |+----------+---------------+------+-----+---------+-------+| ename    | varchar(20)   | YES  |     | NULL    |       || hiredate | date          | YES  |     | NULL    |       || sal      | decimal(10,2) | YES  |     | NULL    |       || deptno   | int(2)        | YES  |     | NULL    |       || age1     | int(4)        | YES  |     | NULL    |       |+----------+---------------+------+-----+---------+-------+5 rows in set (0.00 sec)

修改字段排列顺序,将birth 加载ename之后:

mysql> alter table emp add birth date after ename;Query OK, 0 rows affected (0.45 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc emp;+----------+---------------+------+-----+---------+-------+| Field    | Type          | Null | Key | Default | Extra |+----------+---------------+------+-----+---------+-------+| ename    | varchar(20)   | YES  |     | NULL    |       || birth    | date          | YES  |     | NULL    |       || hiredate | date          | YES  |     | NULL    |       || sal      | decimal(10,2) | YES  |     | NULL    |       || deptno   | int(2)        | YES  |     | NULL    |       || age1     | int(4)        | YES  |     | NULL    |       |+----------+---------------+------+-----+---------+-------+6 rows in set (0.00 sec)

将age1排在最前面:

mysql> alter table emp modify age1 int(3) first;Query OK, 0 rows affected (0.63 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc emp;+----------+---------------+------+-----+---------+-------+| Field    | Type          | Null | Key | Default | Extra |+----------+---------------+------+-----+---------+-------+| age1     | int(3)        | YES  |     | NULL    |       || ename    | varchar(20)   | YES  |     | NULL    |       || birth    | date          | YES  |     | NULL    |       || hiredate | date          | YES  |     | NULL    |       || sal      | decimal(10,2) | YES  |     | NULL    |       || deptno   | int(2)        | YES  |     | NULL    |       |+----------+---------------+------+-----+---------+-------+6 rows in set (0.00 sec)

修改表名：

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

以上都是简单的DDL语句。

1 0