sql笔记(查询练习与外键约束)

1 创建数据表

创建"京东"数据库

mysql> create database jing_dong charset=utf8;
Query OK, 1 row affected (0.01 sec)

使用"京东"数据库

mysql> use jing_dong;
Database changed

创建一个商品goods数据表

mysql> create table good(
    -> id int unsigned primary key auto_increment,
    -> name varchar(150) not null,
    -> cate_name varchar(40) not null,-- 分类名称
    -> brand_name varchar(40) not null,-- 品牌名称
    -> price decimal(10,3) not null default 0,
    -> is_show bit not null default 1,-- 是否展示
    -> is_saleoff bit not null default 0 -- 是否卖光
    -> );
Query OK, 0 rows affected (0.02 sec)

2 插入数据

修改表名

mysql> alter table good rename to goods;
Query OK, 0 rows affected (0.06 sec)

mysql> show tables;
+---------------------+
| Tables_in_jing_dong |
+---------------------+
| goods               |
+---------------------+
1 row in set (0.01 sec)

mysql> insert into goods values(0,"r510vc 15.6英寸笔记本","笔记本","华硕",3399,default,default);
insert into goods values(0,"y400n 14.0英寸笔记本电脑","笔记本","联想",4999,default,default);
insert into goods values(0,"g150th 15.6英寸游戏本","游戏本","雷神",8499,default,default);
insert into goods values(0,"x550cc 15.6英寸笔记本","笔记本","华硕",2799,default,default);
insert into goods values(0,"x240 超极本","超级本","联想",4880,default,default);
insert into goods values(0,"u330p 13.3英寸超级本","超级本","联想",4299,default,default);
insert into goods values(0,"svp13226scb 触控超极本","超级本","索尼",7999,default,default);
insert into goods values(0,"ipad mini 7.9英寸平板电脑","平板电脑","苹果",1998,default,default);
insert into goods values(0,"ipad air 9.7英寸平板电脑","平板电脑","苹果",3388,default,default