酷 网站模板,做小程序的公司有哪些比较好?,网络营销营销型网站建设,铁盒 东莞网站建设mysql复习
一:复习前的准备
1:确认你已安装wamp
2:确认你已安装ecshop,并且ecshop的数据库名为shop二 基础知识:
1.数据库的连接
mysql -u -p -h
-u 用户名
-p 密码
-h host主机2:库级知识
2.1 显示数据库: show databases;
2.2 选择数据库: use dbname;
2.3 创建数据库: creat…mysql复习
一:复习前的准备
1:确认你已安装wamp
2:确认你已安装ecshop,并且ecshop的数据库名为shop二 基础知识:
1.数据库的连接
mysql -u -p -h
-u 用户名
-p 密码
-h host主机2:库级知识
2.1 显示数据库: show databases;
2.2 选择数据库: use dbname;
2.3 创建数据库: create database dbname charset utf8;
2.3 删除数据库: drop database dbname;3: 表级操作:
3.1 显示库下面的表
show tables;3.2 查看表的结构:
desc tableName;3.3 查看表的创建过程:
show create table tableName;3.4 创建表:create table tbName (
列名称1 列类型 [列参数] [not null default ],
....列2...
....
列名称N 列类型 [列参数] [not null default ]
)engine myisam/innodb charset utf8/gbk3.4的例子:
create table user (id int auto_increment,name varchar(20) not null default ,age tinyint unsigned not null default 0,index id (id))engineinnodb charsetutf8;
注:innodb是表引擎,也可以是myisam或其他,但最常用的是myisam和innodb,
charset 常用的有utf8,gbk;3.5 修改表
3.5.1 修改表之增加列:
alter table tbName
add 列名称 列类型 [列参数] [not null default ] #(add之后的旧列名之后的语法和创建表时的列声明一样)3.5.2 修改表之修改列
alter table tbName
change 旧列名 新列名 列类型 [列参数] [not null default ]
(注:旧列名之后的语法和创建表时的列声明一样)3.5.3 修改表之减少列:
alter table tbName
drop 列名称;3.5.4 修改表之增加主键
alter table tbName add primary key(主键所在列名);
例:alter table goods add primary key(id)
该例是把主键建立在id列上3.5.5 修改表之删除主键
alter table tbName drop primary key;3.5.6 修改表之增加索引
alter table tbName add [unique|fulltext] index 索引名(列名);3.5.7 修改表之删除索引
alter table tbName drop index 索引名;3.5.8 清空表的数据
truncate tableName;4:列类型讲解
列类型:整型:tinyint (0~255/-128~127) smallint (0~65535/-32768~32767) mediumint int bigint (参考手册11.2)参数解释:unsigned 无符号(不能为负) zerofill 0填充 M 填充后的宽度举例:tinyint unsigned;tinyint(6) zerofill;
数值型浮点型:float double格式:float(M,D) unsigned\zerofill;字符型char(m) 定长varchar(m)变长text列 实存字符i 实占空间 利用率char(M) 0iM M i/m100%varchar(M) 0iM i1,2 i/i1/2100%year YYYY 范围:1901~2155. 可输入值2位和4位(如98,2012)
日期时间类型 date YYYY-MM-DD 如:2010-03-14time HH:MM:SS 如:19:26:32datetime YYYY-MM-DD HH:MM:SS 如:2010-03-14 19:26:32timestamp YYYY-MM-DD HH:MM:SS 特性:不用赋值,该列会为自己赋当前的具体时间 5:增删改查基本操作5.1 插入数据 insert into 表名(col1,col2,……) values(val1,val2……); -- 插入指定列insert into 表名 values (,,,,); -- 插入所有列insert into 表名 values -- 一次插入多行 (val1,val2……),(val1,val2……),(val1,val2……);5.3修改数据update tablename set col1newval1, col2newval2,......colNnewvalNwhere 条件;5.4删除数据 delete from tablenaeme where 条件;5.5 select 查询1 条件查询 where a. 条件表达式的意义表达式为真则该行取出b. 比较运算符 ! c. like , not like (%匹配任意多个字符,_匹配任意单个字符) in , not in , between andd. is null , is not null 2 分组 group by 一般要配合5个聚合函数使用:max,min,sum,avg,count3 筛选 having4 排序 order by5 限制 limit6: 连接查询6.1 左连接.. left join .. ontable A left join table B on tableA.col1 tableB.col2 ; 例句:select 列名 from table A left join table B on tableA.col1 tableB.col2
2. 右链接: right join
3. 内连接: inner join左右连接都是以在左边的表的数据为准,沿着左表查右表.
内连接是以两张表都有的共同部分数据为准,也就是左右连接的数据之交集.7 子查询where 型子查询:内层sql的返回值在where后作为条件表达式的一部分例句: select * from tableA where colA (select colB from tableB where ...);from 型子查询:内层sql查询结果,作为一张表,供外层的sql语句再次查询例句:select * from (select * from ...) as tableName where ....8: 字符集客服端sql编码 character_set_client服务器转化后的sql编码 character_set_connection服务器返回给客户端的结果集编码 character_set_results快速把以上3个变量设为相同值: set names 字符集存储引擎 engine1\21 Myisam 速度快 不支持事务 回滚2 Innodb 速度慢 支持事务,回滚①开启事务 start transaction②运行sql; ③提交,同时生效\回滚 commit\rollback触发器 trigger监视地点:表监视行为:增 删 改触发时间:after\before触发事件:增 删 改创建触发器语法create trigger tgNameafter/before insert/delete/update on tableNamefor each rowsql; -- 触发语句删除触发器:drop trigger tgName;索引提高查询速度,但是降低了增删改的速度,所以使用索引时,要综合考虑.索引不是越多越好,一般我们在常出现于条件表达式中的列加索引.值越分散的列索引的效果越好索引类型primary key主键索引index 普通索引unique index 唯一性索引fulltext index 全文索引综合练习:
连接上数据库服务器
创建一个gbk编码的数据库
建立商品表和栏目表,字段如下:商品表:goods
goods_id --主键,
goods_name -- 商品名称
cat_id -- 栏目id
brand_id -- 品牌id
goods_sn -- 货号
goods_number -- 库存量
shop_price -- 价格
goods_desc --商品详细描述栏目表:category
cat_id --主键
cat_name -- 栏目名称
parent_id -- 栏目的父id建表完成后,作以下操作:
删除goods表的goods_desc 字段,及货号字段
并增加字段:click_count -- 点击量在goods_name列上加唯一性索引
在shop_price列上加普通索引
在clcik_count列上加普通索引
删除click_count列上的索引对goods表插入以下数据:
------------------------------------------------------------------------------------------------------------
| goods_id | goods_name | cat_id | brand_id | goods_sn | goods_number | shop_price | click_count |
------------------------------------------------------------------------------------------------------------
| 1 | KD876 | 4 | 8 | ECS000000 | 10 | 1388.00 | 7 |
| 4 | 诺基亚N85原装充电器 | 8 | 1 | ECS000004 | 17 | 58.00 | 0 |
| 3 | 诺基亚原装5800耳机 | 8 | 1 | ECS000002 | 24 | 68.00 | 3 |
| 5 | 索爱原装M2卡读卡器 | 11 | 7 | ECS000005 | 8 | 20.00 | 3 |
| 6 | 胜创KINGMAX内存卡 | 11 | 0 | ECS000006 | 15 | 42.00 | 0 |
| 7 | 诺基亚N85原装立体声耳机HS-82 | 8 | 1 | ECS000007 | 20 | 100.00 | 0 |
| 8 | 飞利浦99v | 3 | 4 | ECS000008 | 17 | 399.00 | 9 |
| 9 | 诺基亚E66 | 3 | 1 | ECS000009 | 13 | 2298.00 | 20 |
| 10 | 索爱C702c | 3 | 7 | ECS000010 | 7 | 1328.00 | 11 |
| 11 | 索爱C702c | 3 | 7 | ECS000011 | 1 | 1300.00 | 0 |
| 12 | 摩托罗拉A810 | 3 | 2 | ECS000012 | 8 | 983.00 | 14 |
| 13 | 诺基亚5320 XpressMusic | 3 | 1 | ECS000013 | 8 | 1311.00 | 13 |
| 14 | 诺基亚5800XM | 4 | 1 | ECS000014 | 4 | 2625.00 | 6 |
| 15 | 摩托罗拉A810 | 3 | 2 | ECS000015 | 3 | 788.00 | 8 |
| 16 | 恒基伟业G101 | 2 | 11 | ECS000016 | 0 | 823.33 | 3 |
| 17 | 夏新N7 | 3 | 5 | ECS000017 | 1 | 2300.00 | 2 |
| 18 | 夏新T5 | 4 | 5 | ECS000018 | 1 | 2878.00 | 0 |
| 19 | 三星SGH-F258 | 3 | 6 | ECS000019 | 0 | 858.00 | 7 |
| 20 | 三星BC01 | 3 | 6 | ECS000020 | 13 | 280.00 | 14 |
| 21 | 金立 A30 | 3 | 10 | ECS000021 | 40 | 2000.00 | 4 |
| 22 | 多普达Touch HD | 3 | 3 | ECS000022 | 0 | 5999.00 | 15 |
| 23 | 诺基亚N96 | 5 | 1 | ECS000023 | 8 | 3700.00 | 17 |
| 24 | P806 | 3 | 9 | ECS000024 | 148 | 2000.00 | 36 |
| 25 | 小灵通/固话50元充值卡 | 13 | 0 | ECS000025 | 2 | 48.00 | 0 |
| 26 | 小灵通/固话20元充值卡 | 13 | 0 | ECS000026 | 2 | 19.00 | 0 |
| 27 | 联通100元充值卡 | 15 | 0 | ECS000027 | 2 | 95.00 | 0 |
| 28 | 联通50元充值卡 | 15 | 0 | ECS000028 | 0 | 45.00 | 0 |
| 29 | 移动100元充值卡 | 14 | 0 | ECS000029 | 0 | 90.00 | 0 |
| 30 | 移动20元充值卡 | 14 | 0 | ECS000030 | 9 | 18.00 | 1 |
| 31 | 摩托罗拉E8 | 3 | 2 | ECS000031 | 1 | 1337.00 | 5 |
| 32 | 诺基亚N85 | 3 | 1 | ECS000032 | 1 | 3010.00 | 9 |
------------------------------------------------------------------------------------------------------------三 查询知识
注:以下查询基于ecshop网站的商品表(ecs_goods)
在练习时可以只取部分列,方便查看.1: 基础查询 where的练习:查出满足以下条件的商品
1.1:主键为32的商品
select goods_id,goods_name,shop_price from ecs_goodswhere goods_id32;
1.2:不属第3栏目的所有商品
select goods_id,cat_id,goods_name,shop_price from ecs_goodswhere cat_id!3;1.3:本店价格高于3000元的商品select goods_id,cat_id,goods_name,shop_price from ecs_goodswhere shop_price 3000;1.4:本店价格低于或等于100元的商品
select goods_id,cat_id,goods_name,shop_price from ecs_goods where shop_price 100;1.5:取出第4栏目或第11栏目的商品(不许用or)
select goods_id,cat_id,goods_name,shop_price from ecs_goodswhere cat_id in (4,11);1.6:取出100价格500的商品(不许用and)
select goods_id,cat_id,goods_name,shop_price from ecs_goodswhere shop_price between 100 and 500;1.7:取出不属于第3栏目且不属于第11栏目的商品(and,或not in分别实现)
select goods_id,cat_id,goods_name,shop_price from ecs_goods where cat_id!3 and cat_id!11;select goods_id,cat_id,goods_name,shop_price from ecs_goods where cat_id not in (3,11);1.8:取出价格大于100且小于300,或者大于4000且小于5000的商品()
select goods_id,cat_id,goods_name,shop_price from ecs_goods where shop_price100 and shop_price 300 or shop_price 4000 and shop_price 5000;1.9:取出第3个栏目下面价格1000或3000,并且点击量5的系列商品
select goods_id,cat_id,goods_name,shop_price,click_count from ecs_goods where
cat_id3 and (shop_price 1000 or shop_price3000) and click_count5;1.10:取出第1个栏目下面的商品(注意:1栏目下面没商品,但其子栏目下有)
select goods_id,cat_id,goods_name,shop_price,click_count from ecs_goodswhere cat_id in (2,3,4,5);1.11:取出名字以诺基亚开头的商品
select goods_id,cat_id,goods_name,shop_price from ecs_goods where goods_name like 诺基亚%;1.12:取出名字为诺基亚Nxx的手机
select goods_id,cat_id,goods_name,shop_price from ecs_goods where goods_name like 诺基亚N__;1.13:取出名字不以诺基亚开头的商品
select goods_id,cat_id,goods_name,shop_price from ecs_gooswhere goods_name not like 诺基亚%;1.14:取出第3个栏目下面价格在1000到3000之间,并且点击量5 诺基亚开头的系列商品
select goods_id,cat_id,goods_name,shop_price from ecs_goods where
cat_id3 and shop_price1000 and shop_price 3000 and click_count5 and goods_name like 诺基亚%;select goods_id,cat_id,goods_name,shop_price from ecs_goods where
shop_price between 1000 and 3000 and cat_id3 and click_count5 and goods_name like 诺基亚%;一道面试题
有如下表和数组
把num值处于[20,29]之间,改为20
num值处于[30,39]之间的,改为30mian表
------
| num |
------
| 3 |
| 12 |
| 15 |
| 25 |
| 23 |
| 29 |
| 34 |
| 37 |
| 32 |
| 45 |
| 48 |
| 52 |
------练习题:
把good表中商品名为诺基亚xxxx的商品,改为HTCxxxx,
提示:大胆的把列看成变量,参与运算,甚至调用函数来处理 .
substring(),concat()2 分组查询group:
2.1:查出最贵的商品的价格
select max(shop_price) from ecs_goods;2.2:查出最大(最新)的商品编号
select max(goods_id) from ecs_goods;2.3:查出最便宜的商品的价格
select min(shop_price) from ecs_goods;2.4:查出最旧(最小)的商品编号
select min(goods_id) from ecs_goods;2.5:查询该店所有商品的库存总量
select sum(goods_number) from ecs_goods;2.6:查询所有商品的平均价select avg(shop_price) from ecs_goods;2.7:查询该店一共有多少种商品select count(*) from ecs_goods;2.8:查询每个栏目下面
最贵商品价格
最低商品价格
商品平均价格
商品库存量
商品种类
提示:(5个聚合函数,sum,avg,max,min,count与group综合运用)
select cat_id,max(shop_price) from ecs_goods group by cat_id;3 having与group综合运用查询:
3.1:查询该店的商品比市场价所节省的价格
select goods_id,goods_name,market_price-shop_price as j from ecs_goods ;3.2:查询每个商品所积压的货款(提示:库存*单价)
select goods_id,goods_name,goods_number*shop_price from ecs_goods3.3:查询该店积压的总货款
select sum(goods_number*shop_price) from ecs_goods;3.4:查询该店每个栏目下面积压的货款.
select cat_id,sum(goods_number*shop_price) as k from ecs_goods group by cat_id;3.5:查询比市场价省钱200元以上的商品及该商品所省的钱(where和having分别实现)
select goods_id,goods_name,market_price-shop_price as k from ecs_goods
where market_price-shop_price 200;select goods_id,goods_name,market_price-shop_price as k from ecs_goods
having k 200;3.6:查询积压货款超过2W元的栏目,以及该栏目积压的货款
select cat_id,sum(goods_number*shop_price) as k from ecs_goods group by cat_id
having k200003.7:where-having-group综合练习题
有如下表及数据
----------------------
| name | subject | score |
----------------------
| 张三 | 数学 | 90 |
| 张三 | 语文 | 50 |
| 张三 | 地理 | 40 |
| 李四 | 语文 | 55 |
| 李四 | 政治 | 45 |
| 王五 | 政治 | 30 |
----------------------要求:查询出2门及2门以上不及格者的平均成绩## 一种错误做法
mysql select name,count(score60) as k,avg(score) from stu group by name having k2;
---------------------
| name | k | avg(score) |
---------------------
| 张三 | 3 | 60.0000 |
| 李四 | 2 | 50.0000 |
---------------------
2 rows in set (0.00 sec)mysql select name,count(score60) as k,avg(score) from stu group by name;
---------------------
| name | k | avg(score) |
---------------------
| 张三 | 3 | 60.0000 |
| 李四 | 2 | 50.0000 |
| 王五 | 1 | 30.0000 |
---------------------
3 rows in set (0.00 sec)mysql select name,count(score60) as k,avg(score) from stu group by name having k2;
---------------------
| name | k | avg(score) |
---------------------
| 张三 | 3 | 60.0000 |
| 李四 | 2 | 50.0000 |
---------------------
2 rows in set (0.00 sec)#加上赵六后错误暴露
mysql insert into stu - values - (赵六,A,100),- (赵六,B,99),- (赵六,C,98);
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0#错误显现
mysql select name,count(score60) as k,avg(score) from stu group by name having k2;
---------------------
| name | k | avg(score) |
---------------------
| 张三 | 3 | 60.0000 |
| 李四 | 2 | 50.0000 |
| 赵六 | 3 | 99.0000 |
---------------------
3 rows in set (0.00 sec)#正确思路,先查看每个人的平均成绩
mysql select name,avg(score) from stu group by name;
------------------
| name | avg(score) |
------------------
| 张三 | 60.0000 |
| 李四 | 50.0000 |
| 王五 | 30.0000 |
| 赵六 | 99.0000 |
------------------
4 rows in set (0.00 sec)mysql # 看每个人挂科情况
mysql select name,score 60 from stu;
------------------
| name | score 60 |
------------------
| 张三 | 0 |
| 张三 | 1 |
| 张三 | 1 |
| 李四 | 1 |
| 李四 | 1 |
| 王五 | 1 |
| 赵六 | 0 |
| 赵六 | 0 |
| 赵六 | 0 |
------------------
9 rows in set (0.00 sec)mysql #计算每个人的挂科科目
mysql select name,sum(score 60) from stu group by name;
-----------------------
| name | sum(score 60) |
-----------------------
| 张三 | 2 |
| 李四 | 2 |
| 王五 | 1 |
| 赵六 | 0 |
-----------------------
4 rows in set (0.00 sec)#同时计算每人的平均分
mysql select name,sum(score 60),avg(score) as pj from stu group by name;
--------------------------------
| name | sum(score 60) | pj |
--------------------------------
| 张三 | 2 | 60.0000 |
| 李四 | 2 | 50.0000 |
| 王五 | 1 | 30.0000 |
| 赵六 | 0 | 99.0000 |
--------------------------------
4 rows in set (0.00 sec)#利用having筛选挂科2门以上的.
mysql select name,sum(score 60) as gk ,avg(score) as pj from stu group by name having gk 2;
---------------------
| name | gk | pj |
---------------------
| 张三 | 2 | 60.0000 |
| 李四 | 2 | 50.0000 |
---------------------
2 rows in set (0.00 sec)4: order by 与 limit查询
4.1:按价格由高到低排序
select goods_id,goods_name,shop_price from ecs_goods order by shop_price desc;4.2:按发布时间由早到晚排序
select goods_id,goods_name,add_time from ecs_goods order by add_time;4.3:接栏目由低到高排序,栏目内部按价格由高到低排序
select goods_id,cat_id,goods_name,shop_price from ecs_goodsorder by cat_id ,shop_price desc;4.4:取出价格最高的前三名商品
select goods_id,goods_name,shop_price from ecs_goods order by shop_price desc limit 3;4.5:取出点击量前三名到前5名的商品
select goods_id,goods_name,click_count from ecs_goods order by click_count desc limit 2,3;5 连接查询
5.1:取出所有商品的商品名,栏目名,价格
select goods_name,cat_name,shop_price from
ecs_goods left join ecs_category
on ecs_goods.cat_idecs_category.cat_id;5.2:取出第4个栏目下的商品的商品名,栏目名,价格
select goods_name,cat_name,shop_price from
ecs_goods left join ecs_category
on ecs_goods.cat_idecs_category.cat_id
where ecs_goods.cat_id 4;5.3:取出第4个栏目下的商品的商品名,栏目名,与品牌名
select goods_name,cat_name,brand_name from
ecs_goods left join ecs_category
on ecs_goods.cat_idecs_category.cat_id
left join ecs_brand
on ecs_goods.brand_idecs_brand.brand_id
where ecs_goods.cat_id 4;5.4: 用友面试题根据给出的表结构按要求写出SQL语句。
Match 赛程表
字段名称字段类型描述matchIDint主键hostTeamIDint主队的IDguestTeamIDint客队的IDmatchResultvarchar(20)比赛结果如2:0matchTimedate比赛开始时间 Team 参赛队伍表 字段名称字段类型描述teamIDint主键teamNamevarchar(20)队伍名称 Match的hostTeamID与guestTeamID都与Team中的teamID关联 查出 2006-6-1 到2006-7-1之间举行的所有比赛并且用以下形式列出 拜仁 20 不来梅 2006-6-21 mysql select * from m; ----------------------------------- | mid | hid | gid | mres | matime | ----------------------------------- | 1 | 1 | 2 | 2:0 | 2006-05-21 | | 2 | 2 | 3 | 1:2 | 2006-06-21 | | 3 | 3 | 1 | 2:5 | 2006-06-25 | | 4 | 2 | 1 | 3:2 | 2006-07-21 | ----------------------------------- 4 rows in set (0.00 sec) mysql select * from t; ---------------- | tid | tname | ---------------- | 1 | 国安 | | 2 | 申花 | | 3 | 传智联队 | ---------------- 3 rows in set (0.00 sec) mysql select hid,t1.tname as hname ,mres,gid,t2.tname as gname,matime - from - m left join t as t1 - on m.hid t1.tid - left join t as t2 - on m.gid t2.tid; -------------------------------------------------- | hid | hname | mres | gid | gname | matime | -------------------------------------------------- | 1 | 国安 | 2:0 | 2 | 申花 | 2006-05-21 | | 2 | 申花 | 1:2 | 3 | 传智联队 | 2006-06-21 | | 3 | 传智联队 | 2:5 | 1 | 国安 | 2006-06-25 | | 2 | 申花 | 3:2 | 1 | 国安 | 2006-07-21 | -------------------------------------------------- 4 rows in set (0.00 sec) 6 union查询 6.1:把ecs_comment,ecs_feedback两个表中的数据,各取出4列,并把结果集union成一个结果集. 6.2:3期学员碰到的一道面试题 A表: ------------ | id | num | ------------ | a | 5 | | b | 10 | | c | 15 | | d | 10 | ------------ B表: ------------ | id | num | ------------ | b | 5 | | c | 15 | | d | 20 | | e | 99 | ------------ mysql # 合并 ,注意all的作用 mysql select * from ta - union all - select * from tb; ------------ | id | num | ------------ | a | 5 | | b | 10 | | c | 15 | | d | 10 | | b | 5 | | c | 15 | | d | 20 | | e | 99 | ------------ 要求查询出以下效果: ---------------- | id | sum(num) | ---------------- | a | 5 | | b | 15 | | c | 30 | | d | 30 | | e | 99 | ---------------- 参考答案: mysql # sum,group求和 mysql select id,sum(num) from (select * from ta union all select * from tb) as tmp group by id; ---------------- | id | sum(num) | ---------------- | a | 5 | | b | 15 | | c | 25 | | d | 30 | | e | 99 | ---------------- 5 rows in set (0.00 sec) 7: 子查询: 7.1:查询出最新一行商品(以商品编号最大为最新,用子查询实现) select goods_id,goods_name from ecs_goods where goods_id (select max(goods_id) from ecs_goods); 7.2:查询出编号为19的商品的栏目名称(用左连接查询和子查询分别) 7.3:用where型子查询把ecs_goods表中的每个栏目下面最新的商品取出来 select goods_id,goods_name,cat_id from ecs_goods where goods_id in (select max(goods_id) from ecs_goods group by cat_id); 7.4:用from型子查询把ecs_goods表中的每个栏目下面最新的商品取出来 select * from (select goods_id,cat_id,goods_name from ecs_goods order by goods_id desc) as t group by cat_id; 创建触发器: CREATE trigger tg2 after insert on ord for each row update goods set goods_numbergoods_number-new.num where idnew.gid CREATE trigger tg3 after delete on ord for each row update goods set goods_numbergood_numberold.num where idold.gid CREATE trigger tg4 after update on ord for each row update goods set goods_numbergoods_numberold.num-new.num where idold.gid 2012-03-25更新,添加了面试案例转载于:https://www.cnblogs.com/forphp/p/3428510.html
