转自：https://blog.csdn.net/znyyjk/article/details/52717373
1.数据库的连接
mysql -u -p -h
-u 用户名
-p 密码
-h host主机
2.库级知识
- 显示数据库: show databases;
- 选择数据库: use dbname;
- 创建数据库: create database dbname charset utf8;
- 删除数据库: drop database dbname;
3. 表级操作
1. 显示库下面的表
show tables;
2. 查看表的结构:
desc tableName;
3. 查看表的创建过程:
show create table tableName;
4. 创建表:
create table tbName (
列名称1 列类型 [列参数] [not null default ],
....列2...
....
列名称N 列类型 [列参数] [not null default ]
)engine myisam/innodb charset utf8/gbk
例子:
create table user (
id int auto_increment,
name varchar(20) not null default '',
age tinyint unsigned not null default 0,
index id (id)
)engine=innodb charset=utf8;
注:innodb是表引擎,也可以是myisam或其他,但最常用的是myisam和innodb,charset 常用的有utf8,gbk;
5. 修改表
-
修改表之增加列:
alter table tbName add 列名称１ 列类型 [列参数] [not null default ] #(add之后的旧列名之后的语法和创建表时的列声明一样) -
修改表之修改列
alter table tbName change 旧列名 新列名 列类型 [列参数] [not null default ](注:旧列名之后的语法和创建表时的列声明一样)
-
修改表之减少列:
alter table tbName drop 列名称; -
修改表之增加主键
alter table tbName add primary key(主键所在列名); 例:alter table goods add primary key(id) 该例是把主键建立在id列上 -
修改表之删除主键
alter table tbName drop primary key; -
修改表之增加索引
alter table tbName add [unique|fulltext] index 索引名(列名); -
修改表之删除索引
alter table tbName drop index 索引名; -
清空表的数据
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) | 0<=i<=M | M | i/m<=100% |
| varchar(M) | 0<=i<=M | i+1,2 | i/i+1/2<100% |
-
日期时间类型
5. 增删改查基本操作
1. 插入数据
-- 插入指定列
insert into 表名(col1,col2,……) values(val1,val2……);
-- 插入所有列
insert into 表名 values (,,,,);
-- 一次插入多行
insert into 表名 values
(val1,val2……),
(val1,val2……),
2. 修改数据
update tablename
set
col1=newval1,
col2=newval2,
...
...
colN=newvalN
where 条件;
3. 删除数据
delete from tablenaeme where 条件;
4. select 查询
-
条件查询 where
a. 条件表达式的意义，表达式为真，则该行取出 b. 比较运算符 = ，!=，< > <= >= c. like , not like ('%'匹配任意多个字符,'_'匹配任意单个字符) in , not in , between and d. is null , is not null -
分组 group by
一般要配合5个聚合函数使用:max,min,sum,avg,count - 筛选 having
- 排序 order by
- 限制 limit
-
连接查询
-
左连接 .. left join .. on
table A left join table B on tableA.col1 = tableB.col2 ; 例句: select 列名 from table A left join table B on tableA.col1 = tableB.col2 - 右链接: right join
- 内连接: 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 字符集
存储引擎 engine=1\2
1 Myisam 速度快 不支持事务 回滚
2 Innodb 速度慢 支持事务,回滚
①开启事务 start transaction
②运行sql;
③提交,同时生效\回滚 commit\rollback
触发器 trigger
监视地点:表
监视行为:增 删 改
触发时间:after\before
触发事件:增 删 改
创建触发器语法
create trigger tgName
after/before insert/delete/update
on tableName
for each row
sql; -- 触发语句
删除触发器:drop trigger tgName;
索引
提高查询速度,但是降低了增删改的速度,所以使用索引时,要综合考虑.
索引不是越多越好,一般我们在常出现于条件表达式中的列加索引.
值越分散的列，索引的效果越好
索引类型
primary key主键索引
index 普通索引
unique index 唯一性索引
fulltext index 全文索引
查询知识
注:以下查询基于ecshop网站的商品表(ecs_goods)
在练习时可以只取部分列,方便查看.
1. 基础查询 where的练习:
查出满足以下条件的商品
1. 主键为32的商品
select goods_id,goods_name,shop_price
from ecs_goods
where goods_id=32;
2. 不属第3栏目的所有商品
select goods_id,cat_id,goods_name,shop_price from ecs_goods
where cat_id!=3;
3. 本店价格高于3000元的商品
select goods_id,cat_id,goods_name,shop_price from ecs_goods
where shop_price >3000;
4. 本店价格低于或等于100元的商品
select goods_id,cat_id,goods_name,shop_price from ecs_goods where shop_price <=100;
5. 取出第4栏目或第11栏目的商品(不许用or)
select goods_id,cat_id,goods_name,shop_price from ecs_goods
where cat_id in (4,11);
6. 取出100<=价格<=500的商品(不许用and)
select goods_id,cat_id,goods_name,shop_price from ecs_goods
where shop_price between 100 and 500;
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);
8. 取出价格大于100且小于300,或者大于4000且小于5000的商品
select goods_id,cat_id,goods_name,shop_price from ecs_goods where shop_price>100 and shop_price <300 or shop_price >4000 and shop_price <5000;
9. 取出第3个栏目下面价格<1000或>3000,并且点击量>5的系列商品
select goods_id,cat_id,goods_name,shop_price,click_count from ecs_goods where cat_id=3 and (shop_price <1000 or shop_price>3000) and click_count>5;
10. 取出第1个栏目下面的商品(注意:1栏目下面没商品,但其子栏目下有)
select goods_id,cat_id,goods_name,shop_price,click_count from ecs_goods
where cat_id in (2,3,4,5);
11. 取出名字以”诺基亚”开头的商品
select goods_id,cat_id,goods_name,shop_price from ecs_goods where goods_name like '诺基亚%';
12. 取出名字为”诺基亚Nxx”的手机
select goods_id,cat_id,goods_name,shop_price from ecs_goods where goods_name like '诺基亚N__';
13. 取出名字不以”诺基亚”开头的商品
select goods_id,cat_id,goods_name,shop_price from ecs_goos
where goods_name not like '诺基亚%';
14. 取出第3个栏目下面价格在1000到3000之间,并且点击量>5 “诺基亚”开头的系列商品
select goods_id,cat_id,goods_name,shop_price from ecs_goods where cat_id=3 and shop_price>1000 and shop_price <3000 and click_count>5 and goods_name like '诺基亚%';
15. 一道面试题
有如下表和数组
把num值处于[20,29]之间,改为20
num值处于[30,39]之间的,改为30
mian表
+------+
| num |
+------+
| 3 |
| 12 |
| 15 |
| 25 |
| 23 |
| 29 |
| 34 |
| 37 |
| 32 |
| 45 |
| 48 |
| 52 |
+------+
16. 练习题:
把good表中商品名为’诺基亚xxxx’的商品,改为’HTCxxxx’,
提示:大胆的把列看成变量,参与运算,甚至调用函数来处理 .
substring(),concat()
2.分组查询group:
-
查出最贵的商品的价格
select max(shop_price) from ecs_goods; -
查出最大(最新)的商品编号
select max(goods_id) from ecs_goods; -
查出最便宜的商品的价格
select min(shop_price) from ecs_goods; -
查出最旧(最小)的商品编号
select min(goods_id) from ecs_goods; -
查询该店所有商品的库存总量
select sum(goods_number) from ecs_goods; -
查询所有商品的平均价
select avg(shop_price) from ecs_goods; -
查询该店一共有多少种商品
select count(*) from ecs_goods; -
查询每个栏目下面
3. having与group综合运用查询:
-
查询该店的商品比市场价所节省的价格
select goods_id,goods_name,market_price-shop_price as j from ecs_goods ; -
查询每个商品所积压的货款(提示:库存*单价)
select goods_id,goods_name,goods_number*shop_price from ecs_goods -
查询该店积压的总货款
select sum(goods_number*shop_price) from ecs_goods; -
查询该店每个栏目下面积压的货款.
select cat_id,sum(goods_number*shop_price) as k from ecs_goods group by cat_id; -
查询比市场价省钱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;- 货款超过2W元的栏目,以及该栏目积压的货款
-
select cat_id,sum(goods_number*shop_price) as k from ecs_goods group by cat_id having k>20000 -
where-having-group综合练习题
有如下表及数据 +------+---------+-------+ | name | subject | score | +------+---------+-------+ | 张三 | 数学 | 90 | | 张三 | 语文 | 50 | | 张三 | 地理 | 40 | | 李四 | 语文 | 55 | | 李四 | 政治 | 45 | | 王五 | 政治 | 30 | +------+---------+-------+ 要求:查询出2门及2门以上不及格者的平均成绩 ## 一种错误做法 mysql> select name,count(score<60) as k,avg(score) from stu group by name having k>=2; +------+---+------------+ | name | k | avg(score) | +------+---+------------+ | 张三 | 3 | 60.0000 | | 李四 | 2 | 50.0000 | +------+---+------------+ 2 rows in set (0.00 sec) mysql> select name,count(score<60) 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(score<60) as k,avg(score) from stu group by name having k>=2; +------+---+------------+ | 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(score<60) as k,avg(score) from stu group by name having k>=2; +------+---+------------+ | 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查询
-
按价格由高到低排序
select goods_id,goods_name,shop_price from ecs_goods order by shop_price desc; -
按发布时间由早到晚排序
select goods_id,goods_name,add_time from ecs_goods order by add_time; -
接栏目由低到高排序,栏目内部按价格由高到低排序
select goods_id,cat_id,goods_name,shop_price from ecs_goods order by cat_id ,shop_price desc; -
取出价格最高的前三名商品
select goods_id,goods_name,shop_price from ecs_goods order by shop_price desc limit 3; -
取出点击量前三名到前5名的商品
5. 连接查询
-
取出所有商品的商品名,栏目名,价格
select goods_name,cat_name,shop_price from ecs_goods left join ecs_category on ecs_goods.cat_id=ecs_category.cat_id; -
取出第4个栏目下的商品的商品名,栏目名,价格
select goods_name,cat_name,shop_price from ecs_goods left join ecs_category on ecs_goods.cat_id=ecs_category.cat_id where ecs_goods.cat_id = 4; -
取出第4个栏目下的商品的商品名,栏目名,与品牌名
select goods_name,cat_name,brand_name from ecs_goods left join ecs_category on ecs_goods.cat_id=ecs_category.cat_id left join ecs_brand on ecs_goods.brand_id=ecs_brand.brand_id where ecs_goods.cat_id = 4; - 用友面试题
6. union查询
- 把ecs_comment,ecs_feedback两个表中的数据,各取出4列,并把结果集union成一个结果集.
- 3期学员碰到的一道面试题
7. 子查询:
-
查询出最新一行商品(以商品编号最大为最新,用子查询实现)
select goods_id,goods_name from ecs_goods where goods_id =(select max(goods_id) from ecs_goods); - 查询出编号为19的商品的栏目名称(用左连接查询和子查询分别)
-
用where型子查询把ecs_goods表中的每个栏目下面最新的商品取出来
-
用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;- 1
- 2
-
用exists型子查询,查出所有有商品的栏目
select * from category where exists (select * from goods where goods.cat_id=category.cat_id);
创建触发器:
CREATE trigger tg2
after insert on ord
for each row
update goods set goods_number=goods_number-new.num where id=new.gid
CREATE trigger tg3
after delete on ord
for each row
update goods set goods_number=good_number+old.num where id=old.gid
CREATE trigger tg4
after update on ord
for each row
update goods set goods_number=goods_number+old.num-new.num where id=old.gid
扫一扫
6031
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
