【MySQL】建表（二）

定长类型会减少寻址速度，所以表中尽量使用定长：如：varchar改为char类型，或者是把定长类型的数据（不常用的信息）放一张表，其他放另一张表——以空间换时间

在开发中，会员的信息优化往往是 把频繁用到的信息，优先考虑效率，存储到一张表中，不常用的信息和比较占据空间的信息，有限考虑空间占用，存储到辅表中

建表语法：所谓建表就是一个声明列的过程

create table 表明 (
列名1 列类型1 列1参数,
列名 2 列类型2 列2参数,
...
...
列名 n 列类型n 列n参数
)engine myisam charset utf8/gbk/latinl..;

建一个member表

create table member (
id int unsigned auto_increment primary key,
username char(20) not null default '',
gender char(1) not null default '',
weight tinyint unsigned not null default 0,
brith date not null default '0000-00-00',
salary decimal(8,2) not null default 0.00,
lastlogin int unsigned not null default 0
)engine myisam charset utf8;

修改表的语法

一、增加列：add

1. 加的列在表的最后
   alter table 表名 add 列名称 列类型 列参数;
alter table m1 add username char(20) not null default '';
2. 把新列加在某列后
   alter table 表名 add 列名称 列类型 列参数 after 某列;
alter table m1 add gender char(20) not null default '' after username;
3. 把新列加第一列
   alter table 表名 add 列名称 列类型 列参数 first;
alter table m1 add pid int not null default 0 first;

二、删除列：drop

drop 列
alter table m1 drop pid

三、修改列类型：modify

将char(1)改为char(4)
alter table 表名 modify gender 新类型 新参数;
alter table m1 modify gender char(4) not null default '';

四、修改列名及列类型 change

alter table 表名 change 旧列名 新列名 新类型新参数;
alter table m1 change id uid int unsigned;

五、查询：select

数据准备(数据网站[https://www.cnblogs.com/lms520/p/5427685.html
]):

mysql> create table goods (
    ->   goods_id mediumint(8) unsigned primary key auto_increment,
    ->   goods_name varchar(120) not null default '',
    ->   cat_id smallint(5) unsigned not null default '0',
    ->   brand_id smallint(5) unsigned not null default '0',
    ->   goods_sn char(15) not null default '',
    ->   goods_number smallint(5) unsigned not null default '0',
    ->   shop_price decimal(10,2) unsigned not null default '0.00',
    ->   market_price decimal(10,2) unsigned not null default '0.00',
    ->   click_count int(10) unsigned not null default '0'
    -> ) engine=myisam default charset=utf8;
Query OK, 0 rows affected (0.09 sec)

mysql> insert into `goods` values (1,'kd876',4,8,'ecs000000',1,1388.00,1665.60,9),
    -> (4,'诺基亚n85原装充电器',8,1,'ecs000004',17,58.00,69.60,0),
    -> (3,'诺基亚原装5800耳机',8,1,'ecs000002',24,68.00,81.60,3),
    -> (5,'索爱原装m2卡读卡器',11,7,'ecs000005',8,20.00,24.00,3),
    -> (6,'胜创kingmax内存卡',11,0,'ecs000006',15,42.00,50.40,0),
    -> (7,'诺基亚n85原装立体声耳机hs-82',8,1,'ecs000007',20,100.00,120.00,0),
    -> (8,'飞利浦9@9v',3,4,'ecs000008',1,399.00,478.79,10),
    -> (9,'诺基亚e66',3,1,'ecs000009',4,2298.00,2757.60,20),
    -> (10,'索爱c702c',3,7,'ecs000010',7,1328.00,1593.60,11),
    -> (11,'索爱c702c',3,7,'ecs000011',1,1300.00,0.00,0),
    -> (12,'摩托罗拉a810',3,2,'ecs000012',8,983.00,1179.60,13),
    -> (13,'诺基亚5320 xpressmusic',3,1,'ecs000013',8,1311.00,1573.20,13),
    -> (14,'诺基亚5800xm',4,1,'ecs000014',1,2625.00,3150.00,6),
    -> (15,'摩托罗拉a810',3,2,'ecs000015',3,788.00,945.60,8),
    -> (16,'恒基伟业g101',2,11,'ecs000016',0,823.33,988.00,3),
    -> (17,'夏新n7',3,5,'ecs000017',1,2300.00,2760.00,2),
    -> (18,'夏新t5',4,5,'ecs000018',1,2878.00,3453.60,0),
    -> (19,'三星sgh-f258',3,6,'ecs000019',12,858.00,1029.60,7),
    -> (20,'三星bc01',3,6,'ecs000020',12,280.00,336.00,14),
    -> (21,'金立 a30',3,10,'ecs000021',40,2000.00,2400.00,4),
    -> (22,'多普达touch hd',3,3,'ecs000022',1,5999.00,7198.80,16),
    -> (23,'诺基亚n96',5,1,'ecs000023',8,3700.00,4440.00,17),
    -> (24,'p806',3,9,'ecs000024',100,2000.00,2400.00,35),
    -> (25,'小灵通/固话50元充值卡',13,0,'ecs000025',2,48.00,57.59,0),
    -> (26,'小灵通/固话20元充值卡',13,0,'ecs000026',2,19.00,22.80,0),
    -> (27,'联通100元充值卡',15,0,'ecs000027',2,95.00,100.00,0),
    -> (28,'联通50元充值卡',15,0,'ecs000028',0,45.00,50.00,0),
    -> (29,'移动100元充值卡',14,0,'ecs000029',0,90.00,0.00,0),
    -> (30,'移动20元充值卡',14,0,'ecs000030',9,18.00,21.00,1),
    -> (31,'摩托罗拉e8 ',3,2,'ecs000031',1,1337.00,1604.39,5),
    -> (32,'诺基亚n85',3,1,'ecs000032',4,3010.00,3612.00,9);
Query OK, 31 rows affected, 58 warnings (0.00 sec)
Records: 31  Duplicates: 0  Warnings: 58

1. 查询商品主键是32的商品
   select goods_id,goods_name,shop_price from goods where goods_id = 32;
2. 查出不属于第3个栏目的所有商品，即cat_id ！= 3
   select goods_id,cat_id,goods_name,shop_price from goods where cat_id != 3;
select goods_id,cat_id,goods_name,shop_price from goods where cat_id <> 3;
   以上两句效果一样：<> 和 != 一样的意思
3. 价格高于3000元的商品
   select goods_id,cat_id,goods_name,shop_price from goods where shop_price > 3000;
4. 取出第4和第11栏目的值（不用and）：
   select goods_id,cat_id,goods_name,shop_price from goods where cat_id in (4,11);
5. 取出价格>=100元<= 500元
   between包含了边界值
   select goods_id,cat_id,goods_name,shop_price from goods where shop_price between 100 and 500;
6. 取出不在第3个栏目且不在第11个栏目的商品，用not in 和and实现
   select goods_id,cat_id,goods_name,shop_price from goods where cat_id not in (3,11);
7. 查找在(100,300)之间或者在(4000,5000)之间
   and的优先级比or要高
   select goods_id,cat_id,goods_name,shop_price from goods where shop_price >= 100 and shop_price <= 300 or shop_price >= 4000 and shop_price <= 5000;
8. 取出第3个栏目下，价格小于1000或者大于3000，同时点击量大于5的商品
   select goods_id,cat_id,goods_name,cat_id,shop_price,click_count from goods where (shop_price < 1000 or shop_price > 5000) and cat_id = 3 and click_count > 5;
9. 取出1号栏目下面的商品
   select goods_id,cat_id,goods_name from goods where cat_id = 1;
   出现这个情况

mysql> select goods_id,cat_id,goods_name from goods where cat_id = 1;
Empty set (0.00 sec)

因为1号栏目下放的是其他栏目，不是直接放的商品信息，所以为空

10. 查出名称以“诺基亚”开头的商品
    如：诺基亚N96，诺基亚原装充电器等
    这就要要用到mysql的模糊查询
    like 模糊匹配
    % 通配任意字符（%也可以通配 空）
    _ 通配单一字符
    select goods_id,cat_id,goods_name from goods where goods_name like '诺基亚%';
11. 知道了一款手机是诺基亚Nxx系列，要求查询时，直接使用"_"匹配单一字符
    select goods_id,cat_id,goods_name from goods where goods_name like '诺基亚N__';