1.删除外键约束
alter table 表名 drop foreign key Fk_tID;
2.添加和修改系统数据-插入数据
任务场景数据库是存放数据的仓库,对数据表进行数据添加、更新和删除是对数据最基本的操作。实际应用中,众多的业务都需要对系统数据进行更改,如在网上商城系统中,用户可以将商品添加到购物车,修改购物车中的商品或删除购物车中的商品等操作。MySQL中,使用INSERT语何实现数据添加、使朋UPDATE语句实现数据修改、使用DELETE语包实现数据删除。
//查询表内容
mysql> select * from goodstype;
Empty set (0.01 sec)//表示数据为空
//添加表数据
mysql> INSERT INTO GoodsType VALUES(1,'学习用品');Query OK, 1 row affected (0.00 sec)
//查询修改后的表数据
mysql> select * from goodstype;
+-----+--------------+
| tID | tName |
+-----+--------------+
| 1 | 学习用品 |
+-----+--------------+
1 row in set (0.00 sec)
a.向user表中添加新的记录,用户名为“张小山”,密码为“123”,性别为“男”,用查询语句验证;
mysql> insert into user (uName,uPswd) values('张小山','123');Query OK, 1 row affected (0.00 sec)
select * from user;//验证
b.向user表中添加新记录(2,李平,男,123),(3,张顺,男,123),(4,刘田,女,adf),用查询语句查询验证。
mysql> insert into user (uName,uPswd) values('李平','123');
mysql> insert into user (uName,uPswd) values('张顺','123');
mysql> insert into user (uName,uSex,uPswd) values('刘田','女','adf');
mysql> select * from user; +-----+-----------+------+-------+
UIDUNAMEUSEXUPSWD
+-----+-----------+------+-------+
1张小山男123
2李平男123
3张顺男123
4刘田女ADF
+-----+-----------+------+-------+4 rows in set (0.00 sec)
delete from user where uID=4;//删除语句
替换语句
mysql> replace into user (uID,uName,uPswd) values('3','乐天天','111');Query OK, 2 rows affected (0.00 sec)
mysql> select * from user; +-----+-----------+------+-------+
UIDUNAMEUSEXUPSWD
+-----+-----------+------+-------+
1张小山男123
2李平男123
3乐天天男111
4刘田女ADF
+-----+-----------+------+-------+4 rows in set (0.00 sec)
插入多条数据
mysql> insert into user(uName,uSex,uPswd) values
-> ('郑霞','女','asd'),
-> ('李靖','男','555'),
-> ('朱啸澜','女','123');
Query OK, 3 rows affected (0.00 sec)Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from user;+-----+-----------+------+-------+
UIDUNAMEUSEXUPSWD
+-----+-----------+------+-------+
1张小山男123
2李平男123
3乐天天男111
4刘田女ADF
5郑霞女ASD
6李靖男555
7朱啸澜女123
+-----+-----------+------+-------+7 rows in set (0.00 sec)