表(table)是一种结构化的文件,可以用来存储特定类型的数据,如:学生信息,课程信息,都可以放到表中。另外表都有特定的名称,而且不能重复。表中具有几个概念:列、行、主键。 列叫做字段(Column),行叫做表中的记录,每一个字段都有:字段名称/字段数据类型/字段约束/字段长度
创建表
在 MySQL 中,可以使用 CREATE TABLE 语句创建表。其语法格式为:
create table tableName(
columnName dataType(lenght),
.....
columnName dataType(lenght)
);
set character_set_results='gbk'
创建表的时候,表中有字段,每一个字段有:
* 字段名
* 字段数据类型
* 字段长度限制
* 字段约束
MySql常用数据类型
类型 | 描述 |
Char(长度) | 定长字符串,存储空间大小固定,适合作为主键或外键 |
Varchar(长度) | 变长字符串,存储空间等于实际数据空间 |
double(有效数字位数,小数位) | 数值型 |
Float(有效数字位数,小数位) | 数值型 |
Int( 长度) | 整型 |
bigint(长度) | 长整型 |
Date | 日期型 年月日 |
DateTime | 日期型 年月日 时分秒 毫秒 |
time | 日期型 时分秒 |
BLOB | Binary Large OBject(二进制大对象) |
CLOB | Character Large OBject(字符大对象) |
其它………………… |
|
例子: 建立学生信息表,字段包括:学号、姓名、性别、出生日期、email、班级标识。
mysql> create table t_student(
-> student_id int(10),
-> student_name varchar(20),
-> sex char(2),
-> birthday date,
-> email varchar(30),
-> classes_id int(3)
-> );
Query OK, 0 rows affected (0.18 sec)
mysql> desc t_student;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| student_id | int(10) | YES | | NULL | |
| student_name | varchar(20) | YES | | NULL | |
| sex | char(2) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| email | varchar(30) | YES | | NULL | |
| classes_id | int(3) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
6 rows in set (0.22 sec)
增加/删除/修改表结构
采用alter table来增加/删除/修改表结构,不影响表中的数据。
添加字段
如:需求发生改变,需要向t_student中加入联系电话字段,字段名称为:contatct_tel 类型为varchar(40)。
mysql> alter table t_student add contact_tel varchar(40);
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t_student;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| student_id | int(10) | YES | | NULL | |
| student_name | varchar(20) | YES | | NULL | |
| sex | char(2) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| email | varchar(30) | YES | | NULL | |
| classes_id | int(3) | YES | | NULL | |
| contact_tel | varchar(40) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
7 rows in set (0.21 sec)
修改字段
如:student_name无法满足需求,长度需要更改为100。
mysql> alter table t_student modify student_name varchar(100);
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t_student;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| student_id | int(10) | YES | | NULL | |
| student_name | varchar(100) | YES | | NULL | |
| sex | char(2) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| email | varchar(30) | YES | | NULL | |
| classes_id | int(3) | YES | | NULL | |
| contact_tel | varchar(40) | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+
7 rows in set (0.18 sec)
如sex字段名称感觉不好,想用gender那么就需要更爱列的名称
mysql> alter table t_student change sex gender char(2);
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t_student;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| student_id | int(10) | YES | | NULL | |
| student_name | varchar(100) | YES | | NULL | |
| gender | char(2) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| email | varchar(30) | YES | | NULL | |
| classes_id | int(3) | YES | | NULL | |
| contact_tel | varchar(40) | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+
7 rows in set (0.19 sec)
删除字段
如:删除联系电话字段。
mysql> alter table t_student drop contact_tel;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t_student;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| student_id | int(10) | YES | | NULL | |
| student_name | varchar(100) | YES | | NULL | |
| gender | char(2) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| email | varchar(30) | YES | | NULL | |
| classes_id | int(3) | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+
6 rows in set (0.19 sec)
数据库增删改
添加、修改和删出都属于DML,主要包含的语句:insert、update、delete
增(insert)
INSERT VALUES 的语法格式为:
INSERT INTO <表名> [ <列名1> [ , … <列名n>] ]
VALUES (值1) [… , (值n) ];
语法说明如下。
<表名>
:指定被操作的表名。<列名>
:指定需要插入数据的列名。若向表中的所有列插入数据,则全部的列名均可以省略,直接采用 INSERT<表名>VALUES(…) 即可。VALUES
或VALUE
子句:该子句包含要插入的数据清单。数据清单中数据的顺序要和列的顺序相对应。
省略字段的插入
mysql> insert into EMP values(9999,'zhangsan','MANAGER',null,null,3000,500,10);
Query OK, 1 row affected (0.09 sec)
mysql> select * from EMP where EMPNO=9999;
+-------+----------+---------+------+----------+---------+--------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+----------+---------+------+----------+---------+--------+--------+
| 9999 | zhangsan | MANAGER | NULL | NULL | 3000.00 | 500.00 | 10 |
+-------+----------+---------+------+----------+---------+--------+--------+
1 row in set (0.24 sec)
mysql>
不建议使用此种方式,因为当数据库表中的字段位置发生改变的时候会影响到insert语句.
指定字段的插入(建议使用此种方式)
mysql> insert into EMP (EMPNO,ENAME,JOB,SAL,COMM,DEPTNO) values(9998,'lisi','MANAGER',4000,300,10);
Query OK, 1 row affected (0.08 sec)
mysql> select * from EMP where EMPNO=9998;
+-------+-------+---------+------+----------+---------+--------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+---------+------+----------+---------+--------+--------+
| 9998 | lisi | MANAGER | NULL | NULL | 4000.00 | 300.00 | 10 |
+-------+-------+---------+------+----------+---------+--------+--------+
1 row in set (2.53 sec)
表复制
mysql> create table emp_bak as select EMPNO,ENAME,SAL from EMP;
Query OK, 16 rows affected (0.11 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql> select * from emp_bak;
+-------+----------+---------+
| EMPNO | ENAME | SAL |
+-------+----------+---------+
| 7369 | SMITH | 800.00 |
| 7499 | ALLEN | 1600.00 |
| 7521 | WARD | 1250.00 |
| 7566 | JONES | 2975.00 |
| 7654 | MARTIN | 1250.00 |
| 7698 | BLAKE | 2850.00 |
| 7782 | CLARK | 2450.00 |
| 7788 | SCOTT | 3000.00 |
| 7839 | KING | 5000.00 |
| 7844 | TURNER | 1500.00 |
| 7876 | ADAMS | 1100.00 |
| 7900 | JAMES | 950.00 |
| 7902 | FORD | 3000.00 |
| 7934 | MILLER | 1300.00 |
| 9998 | lisi | 4000.00 |
| 9999 | zhangsan | 3000.00 |
+-------+----------+---------+
16 rows in set (0.27 sec)
删除(delete)
使用 DELETE 语句从单个表中删除数据,语法格式为:
DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]
语法说明如下:
<表名>
:指定要删除数据的表名。ORDER BY
子句:可选项。表示删除时,表中各行将按照子句中指定的顺序进行删除。WHERE
子句:可选项。表示为删除操作限定删除条件,若省略该子句,则代表删除该表中的所有行。LIMIT
子句:可选项。用于告知服务器在控制命令被返回到客户端前被删除行的最大值。
改(update)
使用 UPDATE 语句修改单个表,语法格式为:
UPDATE <表名> SET 字段 1=值 1 [,字段 2=值 2… ] [WHERE 子句 ]
[ORDER BY 子句] [LIMIT 子句]
语法说明如下:
<表名>
:用于指定要更新的表名称。SET
子句:用于指定表中要修改的列名及其列值。其中,每个指定的列值可以是表达式,也可以是该列对应的默认值。如果指定的是默认值,可用关键字 DEFAULT 表示列值。WHERE
子句:可选项。用于限定表中要修改的行。若不指定,则修改表中所有的行。ORDER BY
子句:可选项。用于限定表中的行被修改的次序。LIMIT
子句:可选项。用于限定被修改的行数。
将job为manager的员工的工资上涨10%。
mysql> update EMP set SAL=SAL+SAL*0.1 where JOB='MANAGER';
Query OK, 5 rows affected (0.08 sec)
Rows matched: 5 Changed: 5 Warnings: 0
创建表加入约束
常见的约束
- 非空约束(not null)
- 唯一约束(unique)
- 主键约束(primary key)
- 外键约束(foreign key)
- 自定义检查约束,check(不建议使用)(在mysql中现在还不支持)
非空约束(not null)
非空约束,针对某个字段设置其值不为空,如:学生的姓名不能为空。
mysql> drop table if exists t_student;
mysql> create table t_student(
-> student_id int(10),
-> student_name varchar(20) not null,
-> sex char(2),
-> birthday date,
-> email varchar(30),
-> classes_id int(3)
-> );
Query OK, 0 rows affected (0.18 sec)
唯一约束(unique)
唯一性约束,它可以使某个字段的值不能重复,如:email不能重复:
mysql> drop table if exists t_student;
mysql> create table t_student(
-> student_id int(10),
-> student_name varchar(20) not null,
-> sex char(2),
-> birthday date,
-> email varchar(30) unique,
-> classes_id int(3)
-> );
Query OK, 0 rows affected (0.18 sec)
主键约束(primary key)
每个表应该具有主键,主键可以标识记录的唯一性,主键分为单一主键和复合(联合)主键,单一主键是由一个字段构成的,复合(联合)主键是由多个字段构成的。
mysql> drop table if exists t_student;
mysql> create table t_student(
-> student_id int(10) primary key,/*列级约束*/
-> student_name varchar(20) not null,
-> sex char(2),
-> birthday date,
-> email varchar(30) unique,
-> classes_id int(3)
-> );
Query OK, 0 rows affected (0.18 sec)
或者是在定义完所有字段之后指定主键,语法格式如下:
[CONSTRAINT <约束名>] PRIMARY KEY [字段名]
我们也可以通过表级约束为约束起个名称:
mysql> drop table if exists t_student;
mysql> create table t_student(
-> student_id int(10)
-> student_name varchar(20) not null,
-> sex char(2),
-> birthday date,
-> email varchar(30) unique,
-> classes_id int(3),
-> CONSTRAINT p_id primary key(student_id)
-> );
Query OK, 0 rows affected (0.18 sec)
外键约束(foreign key)
外键主要是维护表之间的关系的,主要是为了保证参照完整性,如果表中的某个字段为外键字段,那么该字段的值必须来源于参照的表的主键,如:emp中的deptno值必须来源于dept表中的deptno字段值。
建立学生和班级表之间的连接。
首先建立班级表t_classes
mysql> drop table if exists t_classes;
Query OK, 0 rows affected (0.55 sec)
mysql> create table t_classes(
-> classes_id int(3),
-> classes_name varchar(40),
-> constraint pk_classes_id primary key(classes_id)
-> );
Query OK, 0 rows affected (0.16 sec)
在t_student中加入外键约束
mysql> drop table if exists t_student;
Query OK, 0 rows affected (0.11 sec)
mysql> create table t_student(
-> student_id int(10)
-> student_name varchar(20) not null,
-> sex char(2),
-> birthday date,
-> email varchar(30) unique,
-> classes_id int(3),
-> CONSTRAINT p_id primary key(student_id),
-> CONSTRAINT fk_class_id foreign key(classes_id)
-> );