索引练习
1、建立一个utf8编码的数据库test1
create database test1 character set utf8;
2、建立商品表goods和栏目表category
按如下表结构创建表:存储引擎engine myisam 字符集charset utf8
mysql> desc goods;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| goods_id | int(11) | NO | PRI | NULL | auto_increment |
| goods_name | varchar(20) | NO | | | |
| cat_id | int(11) | NO | | 0 | |
| brand_id | int(11) | NO | | 0 | |
| goods_sn | char(12) | NO | | | |
| shop_price | float(6,2) | NO | | 0.00 | |
| goods_desc | text | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
mysql> desc category;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| cat_id | int(11) | NO | PRI | NULL | auto_increment |
| cate_name | varchar(20) | NO | | | |
| parent_id | int(11) | NO | | 0 | |
+-----------+-------------+------+-----+---------+----------------+
create table goods( goods_id int(11) primary key, goods_name varchar(20), cat_id int(11) default 0, brand_id int(11) default 0, goods_sn char(12), shop_price float(6,2) default 0.00, goods_desc text not null) engine=myisam character set = utf8;
create table category( cat_id int(11) primary key, cate_name varchar(20), parent_id
int(11) )engine=myisam character set = utf8;
3、删除 goods 表中的 goods_desc 字段及货号字段,并增加 click_count 字段
alter table goods drop goods_desc;
alter table goods drop goods_sn;
alter table goods add click_count varchar(255);
4、在 goods_name 列上加唯一性索引(用alter table方式)
alter table goods add unique index(goods_name);
5、在 shop_price 列上加普通索引(用create index方式)
create index shop_price on goods(shop_price);
6、在 click_count 上增加普通索引,然后再删除 (分别使用drop index和alter table删除)
1、create
create index in_count on goods(click_count);
alter table goods drop index in_count;
2、alter
alter table goods add index in_count(click_count);
alter table goods drop index in_count;
试图练习
1、创建表
2、创建一视图 stu_info,查询全体学生的姓名,性别,课程名,成绩。
create view stu_info (name,sex,course,score) as select S.Sname,S.Ssex,C.Cname,SC.Scorere from SC,Course C,Student S where SC.Sno=C.Cno and SC.Cno=S.Sno;
3、删除视图 stu_info。
drop view stu_info;