- 创建表
create table 表名 (
字段1 数据类型,
字段2 数据类型 default 值,
......
);
常见的约束:
- 非空约束(not null)
- 唯一约束(unique)
- 主键约束(primary key, PK)
- 外键约束(foreign key, FK)
mysql> create table stu(
-> id int primary key auto_increment,
-> name varchar(255) not null,
-> sex char(1) default 1,
-> classno char(10),
-> email varchar(20) unique
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> desc stu;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
| sex | char(1) | YES | | 1 | |
| classno | char(10) | YES | | NULL | |
| email | varchar(20) | YES | UNI | NULL | |
+---------+--------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
- 插入数据
insert into 表名 (字段1, 字段2, ...) values (值1, 值2, ...), (...), ...
mysql> insert into stu values (1,'zhangsan', 1, 'gaosan1ban', '1243781@qq.com');
Query OK, 1 row affected (0.01 sec)
mysql> insert into stu (name, classno) values ('lisi', 'gaosan2ban');
Query OK, 1 row affected (0.01 sec)
mysql> select * from stu;
+----+----------+------+------------+----------------+
| id | name | sex | classno | email |
+----+----------+------+------------+----------------+
| 1 | zhangsan | 1 | gaosan1ban | 1243781@qq.com |
| 2 | lisi | 1 | gaosan2ban | NULL |
+----+----------+------+------------+----------------+
2 rows in set (0.00 sec)
- 复制表
create table 表名 as select 语句;
mysql> create table dept1 as select * from dept;
Query OK, 4 rows affected (0.04 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from dept1;
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.00 sec)
mysql> insert into dept1 select * from dept;
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from dept1;
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
8 rows in set (0.00 sec)
- 修改表中的数据
update 表名 set 字段1 = 值1,字段2 = 值2, ... where 条件
注意:没有where条件表示所有数据都更新
// 将部门10的loc改为上海,将部门名称改为renshibu
mysql> update dept1 set loc = 'shanghai', dname = 'renshibu' where deptno = 10;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from dept1;
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | renshibu | shanghai |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
| 10 | renshibu | shanghai |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
8 rows in set (0.00 sec)
- 删除数据
delete from 表名 where 条件
注意:没有where条件表示所有数据都删除
// 删除部门20的数据
mysql> delete from dept1 where deptno = 20;
Query OK, 2 rows affected (0.00 sec)
mysql> select * from dept1;
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | renshibu | shanghai |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
| 10 | renshibu | shanghai |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
6 rows in set (0.00 sec)
- 外键约束
// 建表的时候先建父表再建子表
// 外键约束中,classno的值应该在cno中,否则会报错
mysql> create table t_class(
-> cno int,
-> cname varchar(255),
-> primary key(cno)
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> create table t_stu(
-> sno int,
-> sname varchar(255),
-> classno int,
-> primary key(sno),
-> foreign key(classno) references t_class(cno)
-> );
Query OK, 0 rows affected (0.04 sec)
// 删表的时候先删子表再删父表
mysql> drop table if exists t_stu;
Query OK, 0 rows affected (0.02 sec)
mysql> drop table if exists t_class;
Query OK, 0 rows affected (0.02 sec)