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)