MySQL学习笔记（4）

1. 创建表

create table 表名 (
      字段1 数据类型,
      字段2 数据类型 default 值,
      ......
      );

常见的约束：

1. 非空约束(not null)
2. 唯一约束(unique)
3. 主键约束(primary key, PK)
4. 外键约束(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)

2. 插入数据

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)

3. 复制表

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)

4. 修改表中的数据

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)

5. 删除数据

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)

6. 外键约束

// 建表的时候先建父表再建子表
// 外键约束中，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)