菜鸟笔记
提升您的技术认知

MySQL数据库 表操作 ️

1. 表操作 ️

脑图

查看所有表

show tables;

mysql> show tables;
Empty set (0.00 sec)
mysql> show tables from db12;
Empty set (0.00 sec)

创建班级表 grade

id 主键
grade_name 班名 varchar(100)

mysql> create table grade(
    -> id int primary key auto_increment,
    -> grade_name varchar(100) comment "班级名字"
    -> );
Query OK, 0 rows affected (0.06 sec)

创建学生表 stu,添加外键约束

id 主键
name 学生姓名 varchar(100) not null
sex 学生性别

创建数据表 stu ,并在表 stu 上创建外键约束,让它的键 gradeId 作为外键关联到表 grade 的主键 id ,SQL这样写

[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [,字段名2,…]
REFERENCES <主表名> 主键列1 [,主键列2,…]
CREATE TABLE stu (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR (100) NOT NULL,
  age INT (3),
  bir DATE,
  english INT (3),
  chinese INT (3),
  gradeId INT,
  CONSTRAINT fk_stu_grade FOREIGN KEY (gradeId) REFERENCES grade (id)
) ;

注意:从表的外键关联的必须是主表的主键,且主键和外键的数据类型必须一致。例如,两者都是 INT 类型,或者都是 CHAR 类型。如果不满足这样的要求,在创建从表时,就会出现“ERROR 1005(HY000): Can’t create table”错误

在修改表时添加外键约束

假如在创建表时,没有添加外键约束,那么可以在创建表后通过修改表给表添加外键约束,语法如下

ALTER TABLE <数据表名> ADD CONSTRAINT <外键名>
FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);
CREATE TABLE stu (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR (100) NOT NULL,
  age INT (3),
  bir DATE,
  english INT (3),
  chinese INT (3),
  gradeId INT
) ;

ALTER TABLE stu 
  ADD CONSTRAINT fk_stu_grage FOREIGN KEY (gradeId) REFERENCES grade (id) ;

注意:在为已经创建好的数据表添加外键约束时,要确保添加外键约束的列的值全部来源于主键列,并且外键列不能为空。

删除外键约束

通过外键名删除外键,这里演示一下,删除之后会继续添加上的,因为需要这个外键约束

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;
ALTER TABLE stu DROP FOREIGN KEY fk_stu_grage;

查看表结构

DESC stu;

显示存储引擎的状态信息

SHOW ENGINES;

修改表

修改表名 rename to

mysql> alter table stu rename to stus;
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+----------------+
| Tables_in_db12 |
+----------------+
| grade          |
| stus           |
+----------------+
2 rows in set (0.00 sec)

添加一列 add

alter table 表名 add 列名 列数据类型;

修改数据类型 modify

alter table 表名 modify 列名 新的数据类型;

修改字段位置置顶 first

alter table 表名 modify 字段名 字段属性 first;

修改字段位置再什么之后 after

alter table 表名 modify 字段 字段属性 after 字段;

修改列名和数据类型 change

alter table 表名 change 原列名 新列名 新数据类型;

删除列 drop

alter table 表名 drop 列名;

删除表

直接删除

drop table 表名;

判断存在再删除

drop table if exists 表名;

4. 添加数据 insert

脑图

语法

INSERT INTO 表名(字段,字段……) VALUES(与字段对应);

mysql> desc stus;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| gradeId | int(11)      | YES  | MUL | NULL    |                |
| NAME    | varchar(100) | NO   |     | NULL    |                |
| age     | int(3)       | YES  |     | NULL    |                |
| bir     | date         | YES  |     | NULL    |                |
| english | int(3)       | YES  |     | NULL    |                |
| chinese | int(3)       | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)


mysql> desc grade;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| grade_name | varchar(100) | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

给指定列添加数据

INSERT INTO stus(NAME,age,bir,english,gradeId) VALUES("周棋洛",18,"2002-06-01",78,1);
mysql> select * from stus;
+----+---------+-----------+------+------------+---------+---------+
| id | gradeId | NAME      | age  | bir        | english | chinese |
+----+---------+-----------+------+------------+---------+---------+
|  1 |       1 | 周棋洛    |   18 | 2002-06-01 |      78 |    NULL |
+----+---------+-----------+------+------------+---------+---------+
1 row in set (0.00 sec)

给全部列添加数据

mysql> insert into stus values(2,2,"张郁苗",18,"2002-11-07",130,120);
Query OK, 1 row affected (0.03 sec)

给全部列添加数据时,可以简写,不建议,因为代码可读性变差,推荐写成下面这样,一眼就能知道要插入的字段是什么

mysql> insert into stus(gradeId,name,age,bir,english,chinese) values
    -> (1,"小猪佩奇",6,"2015-06-10",34,23);
Query OK, 1 row affected (0.03 sec)
mysql> select * from stus;


+----+---------+--------------+------+------------+---------+---------+
| id | gradeId | NAME         | age  | bir        | english | chinese |
+----+---------+--------------+------+------------+---------+---------+
|  1 |       1 | 周棋洛       |   18 | 2002-06-01 |      78 |    NULL |
|  2 |       2 | 张郁苗       |   18 | 2002-11-07 |     130 |     120 |
|  3 |       1 | 小猪佩奇     |    6 | 2015-06-10 |      34 |      23 |
+----+---------+--------------+------+------------+---------+---------+
3 rows in set (0.00 sec)

一次添加多条数据

mysql> insert into stus(gradeId,name,age,bir,english,chinese) values
    -> (3,"猪妈妈",7,"2012-09-11",56,78),
    -> (2,"谷歌",6,"2000-11-11",100,110);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from stus;
+----+---------+--------------+------+------------+---------+---------+
| id | gradeId | NAME         | age  | bir        | english | chinese |
+----+---------+--------------+------+------------+---------+---------+
|  1 |       1 | 周棋洛       |   18 | 2002-06-01 |      78 |    NULL |
|  2 |       2 | 张郁苗       |   18 | 2002-11-07 |     130 |     120 |
|  3 |       1 | 小猪佩奇     |    6 | 2015-06-10 |      34 |      23 |
|  4 |       3 | 猪爸爸       |    8 | 2012-09-12 |      34 |      56 |
|  5 |       3 | 猪妈妈       |    7 | 2012-09-11 |      56 |      78 |
|  6 |       2 | 谷歌         |    6 | 2000-11-11 |     100 |     110 |
+----+---------+--------------+------+------------+---------+---------+
6 rows in set (0.00 sec)

查看最后一次添加的主键值

select last_insert_id();

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                5 |
+------------------+
1 row in set (0.03 sec)

5. 更新数据 update

脑图

语法

update 表 set 要更新字段 = 要更新成啥 where 更新条件;

注意:如果不使用 where 字句进行限制,就会把表里所有记录都修改了,凉凉了,可别怪本帅哥没提醒你

更新一个字段

修改stus表名字叫周棋洛的生日,改为 2001-06-01

mysql> update stus set bir = "2001-06-01" where name = "周棋洛";
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql> select * from stus;
+----+---------+--------------+------+------------+---------+---------+
| id | gradeId | NAME         | age  | bir        | english | chinese |
+----+---------+--------------+------+------------+---------+---------+
|  1 |       1 | 周棋洛       |   18 | 2001-06-01 |      78 |    NULL |
|  2 |       2 | 张郁苗       |   18 | 2002-11-07 |     130 |     120 |
|  3 |       1 | 小猪佩奇     |    6 | 2015-06-10 |      34 |      23 |
|  4 |       3 | 猪爸爸       |    8 | 2012-09-12 |      34 |      56 |
|  5 |       3 | 猪妈妈       |    7 | 2012-09-11 |      56 |      78 |
|  6 |       2 | 谷歌         |    6 | 2000-11-11 |     100 |     110 |
+----+---------+--------------+------+------------+---------+---------+
6 rows in set (0.00 sec)

更新多个字段

把id为6的名字改为 胡歌,年龄改为24

mysql> update stus set name = "胡歌",age = 24 where id = 6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from stus;
+----+---------+--------------+------+------------+---------+---------+
| id | gradeId | NAME         | age  | bir        | english | chinese |
+----+---------+--------------+------+------------+---------+---------+
|  1 |       1 | 周棋洛       |   18 | 2001-06-01 |      78 |    NULL |
|  2 |       2 | 张郁苗       |   18 | 2002-11-07 |     130 |     120 |
|  3 |       1 | 小猪佩奇     |    6 | 2015-06-10 |      34 |      23 |
|  4 |       3 | 猪爸爸       |    8 | 2012-09-12 |      34 |      56 |
|  5 |       3 | 猪妈妈       |    7 | 2012-09-11 |      56 |      78 |
|  6 |       2 | 胡歌         |   24 | 2000-11-11 |     100 |     110 |
+----+---------+--------------+------+------------+---------+---------+
6 rows in set (0.00 sec)

ignore关键字

当使用update语句进行多行更新,如果在更新过程中出错了,则整个update语句都会被取消,恢复到更新之前,如果你想即使发生错误,也继续进行更新,可以使用ignore关键字

语法:

update ignore 表 set 要更新字段 = 要更新成啥 where 更新条件;

设置为null

当我们想要将记录的某个字段或多个字段改为空,就可以将它修改为 null ,而不是空字符串,mysql中的空为null

例如:
修改id为3的中文成绩为null空

mysql> update stus set chinese = null where id = 3;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from stus;
+----+---------+--------------+------+------------+---------+---------+
| id | gradeId | NAME         | age  | bir        | english | chinese |
+----+---------+--------------+------+------------+---------+---------+
|  1 |       1 | 周棋洛       |   18 | 2001-06-01 |      78 |    NULL |
|  2 |       2 | 张郁苗       |   18 | 2002-11-07 |     130 |     120 |
|  3 |       1 | 小猪佩奇     |    6 | 2015-06-10 |      34 |    NULL |
|  4 |       3 | 猪爸爸       |    8 | 2012-09-12 |      34 |      56 |
|  5 |       3 | 猪妈妈       |    7 | 2012-09-11 |      56 |      78 |
|  6 |       2 | 胡歌         |   24 | 2000-11-11 |     100 |     110 |
+----+---------+--------------+------+------------+---------+---------+
6 rows in set (0.00 sec)

6. 删除数据 delete

脑图

语法

delete from 表 where 限制条件;

删除一行记录

删除id为6的数据

mysql> delete from stus where id = 6;
Query OK, 1 row affected (0.03 sec)

mysql> select * from stus;
+----+---------+--------------+------+------------+---------+---------+
| id | gradeId | NAME         | age  | bir        | english | chinese |
+----+---------+--------------+------+------------+---------+---------+
|  1 |       1 | 周棋洛       |   18 | 2001-06-01 |      78 |    NULL |
|  2 |       2 | 张郁苗       |   18 | 2002-11-07 |     130 |     120 |
|  3 |       1 | 小猪佩奇     |    6 | 2015-06-10 |      34 |    NULL |
|  4 |       3 | 猪爸爸       |    8 | 2012-09-12 |      34 |      56 |
|  5 |       3 | 猪妈妈       |    7 | 2012-09-11 |      56 |      78 |
+----+---------+--------------+------+------------+---------+---------+
5 rows in set (0.00 sec)

删除所有记录

警告:不要省略 where 子句,如果不使用 where 子句进行限制,别怪我没提醒你,你会把表中所有行全部干掉,准备后事吧!兄弟

delete from stus;

这里就不执行了

注意:delete语句从表中删除行,甚至删除表中所有行,但是,delete不删除表本身

删除所有记录(效率高)

如果你想从表中删除所有数据,不要使用delete,可以使用 truncate 表名,它完成相同的工作,但是速度更快,因为它实际上是删除原来的表并重新创建一张表,而不是逐行删除表中的数据

mysql> select * from user;
+------+------+
| id   | name |
+------+------+
|    1 | hah1 |
+------+------+
1 row in set (0.00 sec)

mysql> truncate user;
Query OK, 0 rows affected (0.06 sec)

mysql> select * from user;
Empty set (0.00 sec)