MySQL复习

复习前的准备

1. 确认已安装MySQL服务器并启动mysqld服务

1. 新建数据库，并将如下SQL导入到数据库中

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;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);CREATE TABLE `brand` (  `brand_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,  `brand_name` varchar(60) NOT NULL DEFAULT '',  `brand_logo` varchar(80) NOT NULL DEFAULT '',  `brand_desc` text NOT NULL,  `site_url` varchar(255) NOT NULL DEFAULT '',  `sort_order` tinyint(3) unsigned NOT NULL DEFAULT '50',  `is_show` tinyint(1) unsigned NOT NULL DEFAULT '1',  PRIMARY KEY (`brand_id`),  KEY `is_show` (`is_show`)) ENGINE=MyISAM DEFAULT CHARSET=utf8;INSERT INTO `brand` VALUES (1,'诺基亚','1240803062307572427.gif','公司网站：http://www.nokia.com.cn//n/n客服电话：400-880-0123','http://www.nokia.com.cn/',50,1),(2,'摩托罗拉','1240802922410634065.gif','官方咨询电话：4008105050/n售后网点：http://www.motorola.com.cn/service/carecenter/search.asp ','http://www.motorola.com.cn',50,1),(3,'多普达','1240803144788047486.gif','官方咨询电话：4008201668/n售后网点：http://www.dopod.com/pc/service/searchresult2.php ','http://www.dopod.com ',50,1),(4,'飞利浦','1240803247838195732.gif','官方咨询电话：4008800008/n售后网点：http://www.philips.com.cn/service/mustservice/index.page ','http://www.philips.com.cn ',50,1),(5,'夏新','1240803352280856940.gif','官方咨询电话：4008875777/n售后网点：http://www.amobile.com.cn/service_fwyzc.asp ','http://www.amobile.com.cn',50,1),(6,'三星','1240803412367015368.gif','官方咨询电话：8008105858/n售后网点：http://cn.samsungmobile.com/cn/support/search_area_o.jsp ','http://cn.samsungmobile.com',50,1),(7,'索爱','1240803482283160654.gif','官方咨询电话：4008100000/n售后网点：http://www.sonyericsson.com/cws/common/contact?cc=cn&lc=zh ','http://www.sonyericsson.com.cn/',50,1),(8,'LG','1240803526904622792.gif','官方咨询电话：4008199999/n售后网点：http://www.lg.com.cn/front.support.svccenter.retrieveCenter.laf?hrefId=9 ','http://cn.wowlg.com',50,1),(9,'联想','1240803578417877983.gif','官方咨询电话：4008188818/n售后网点：http://www.lenovomobile.com/service/kf-wanglou.asp','http://www.lenovomobile.com/',50,1),(10,'金立','','官方咨询电话：4007796666/n售后网点：http://www.gionee.net/service.asp ','http://www.gionee.net',50,1),(11,'  恒基伟业','1240803736391383580.gif','官方咨询电话：4008899126/n售后网点：http://www.htwchina.com/htwt/wexiu.shtml ','http://www.htwchina.com',50,1);CREATE TABLE `comment` (  `comment_id` int(10) unsigned NOT NULL AUTO_INCREMENT,  `comment_type` tinyint(3) unsigned NOT NULL DEFAULT '0',  `id_value` mediumint(8) unsigned NOT NULL DEFAULT '0',  `email` varchar(60) NOT NULL DEFAULT '',  `user_name` varchar(60) NOT NULL DEFAULT '',  `content` text NOT NULL,  `comment_rank` tinyint(1) unsigned NOT NULL DEFAULT '0',  `add_time` int(10) unsigned NOT NULL DEFAULT '0',  `ip_address` varchar(15) NOT NULL DEFAULT '',  `status` tinyint(3) unsigned NOT NULL DEFAULT '0',  `parent_id` int(10) unsigned NOT NULL DEFAULT '0',  `user_id` int(10) unsigned NOT NULL DEFAULT '0',  PRIMARY KEY (`comment_id`),  KEY `parent_id` (`parent_id`),  KEY `id_value` (`id_value`)) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;INSERT INTO `comment` VALUES (1,0,12,'ecshop@ecshop.com','ecshop','很好，我很喜欢',5,1242107120,'0.0.0.0',1,0,1),(2,0,22,'ecshop@ecshop.com','ecshop','这个我不是很适合我',5,1242107295,'0.0.0.0',0,0,1);DROP TABLE IF EXISTS `feedback`;CREATE TABLE `feedback` (  `msg_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,  `parent_id` mediumint(8) unsigned NOT NULL DEFAULT '0',  `user_id` mediumint(8) unsigned NOT NULL DEFAULT '0',  `user_name` varchar(60) NOT NULL DEFAULT '',  `user_email` varchar(60) NOT NULL DEFAULT '',  `msg_title` varchar(200) NOT NULL DEFAULT '',  `msg_type` tinyint(1) unsigned NOT NULL DEFAULT '0',  `msg_status` tinyint(1) unsigned NOT NULL DEFAULT '0',  `msg_content` text NOT NULL,  `msg_time` int(10) unsigned NOT NULL DEFAULT '0',  `message_img` varchar(255) NOT NULL DEFAULT '0',  `order_id` int(11) unsigned NOT NULL DEFAULT '0',  `msg_area` tinyint(1) unsigned NOT NULL DEFAULT '0',  PRIMARY KEY (`msg_id`),  KEY `user_id` (`user_id`)) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;INSERT INTO `feedback` VALUES (1,0,1,'ecshop','ecshop@ecshop.com','三星SGH-F258什么时候到',4,0,'三星SGH-F258什么时候有货',1242107197,'',0,0);create table category (cat_id smallint unsigned auto_increment primary key,cat_name varchar(90) not null default '',parent_id smallint unsigned)engine myisam charset utf8;INSERT INTO `category` VALUES (1,'手机类型',0),(2,'CDMA手机',1),(3,'GSM手机',1),(4,'3G手机',1),(5,'双模手机',1),(6,'手机配件',0),(7,'充电器',6),(8,'耳机',6),(9,'电池',6),(11,'读卡器和内存卡',6),(12,'充值卡',0),(13,'小灵通/固话充值卡',12),(14,'移动手机充值卡',12),(15,'联通手机充值卡',12);CREATE TABLE `result` (  `name` varchar(20) DEFAULT NULL,  `subject` varchar(20) DEFAULT NULL,  `score` tinyint(4) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8;insert into resultvalues('张三','数学',90),('张三','语文',50),('张三','地理',40),('李四','语文',55),('李四','政治',45),('王五','政治',30);create table a (id char(1),num int)engine myisam charset utf8;insert into a values ('a',5),('b',10),('c',15),('d',10);create table b (id char(1),num int)engine myisam charset utf8;insert into b values ('b',5),('c',15),('d',20),('e',99); create table m(     mid int,     hid int,     gid int,     mres varchar(10),     matime date)engine myisam charset utf8;create table t (     tid int,     tname varchar(20))engine myisam charset utf8;insert into m     values     (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');  insert into t     values     (1,'国安'),     (2,'申花'),     (3,'布尔联队');create table mian ( num int) engine myisam;insert into mian values (3),(12),(15),(25),(23),(29),(34),(37),(32);create table user (uid int primary key auto_increment,name varchar(20) not null default '',age smallint unsigned not null default 0) engine myisam charset utf8;

基础知识

数据库的连接

mysql -h -u -p-h host 主机-u usernmae 用户名-p password 密码

例如：

mysql -h127.0.0.1 -uroot -paaaaaa

库级知识

1. 显示数据库:

   show databases;

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || app_blogcurder     || mysql              || performance_schema || shop               |+--------------------+5 rows in set (0.00 sec)

1. 选择数据库:

   use dbname;

mysql> use test;Database changed

另外： 通过 /s 查看当前所选择的数据库

mysql> /smysql  Ver 14.14 Distrib 5.5.29, for Linux (x86_64) using readline 5.1Connection id:      3Current database:   testCurrent user:       root@localhostSSL:            Not in use

1. 创建数据库:

create database dbname charset utf8;

建议指定字符集，罗在工作中遇到一个关于不同类型数据库导入到处字符集的问题

mysql> create database test charset utf8;Query OK, 1 row affected (0.00 sec)

1. 删除数据库:

   drop database dbname;

mysql> drop database test;Query OK, 0 rows affected (0.15 sec)

表级操作

显示库下面的表

show tables;

例如

mysql> show tables;+----------------+| Tables_in_test |+----------------+| user           |+----------------+1 row in set (0.00 sec)

查看表的结构

desc tableName;

查看表的创建过程

 show create table tableName;

创建表

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;

修改表

1. 修改表之增加列

alter table tbName add 列名称１　列类型　[列参数]　[not null default] [comment '备注'] [after filedName];

add之后的旧列名之后的语法和创建表时的列声明一样

举例

alter table user add phone char(11) not null default '' comment '手机号码' after `name`;

1. 修改表之修改列

   add

I. change

alter table tbName change 旧列名  新列名  列类型　[列参数]　[not null default] [comment '备注'] [after filedName];

注:旧列名之后的语法和创建表时的列声明一样

举例

alter table user change phone tel char(11) not null default '' comment '手机号码' after `name`;

II. modify

alter table tbName modify phone 列类型　[列参数]　[not null default] [comment '备注'] [after filedName];

1. 修改表之减少列

   drop

alter table tbName drop 列名称;

mysql> alter table user drop tel;Query OK, 0 rows affected (0.03 sec)Records: 0  Duplicates: 0  Warnings: 0

1. 修改表之增加主键

alter table tbName add primary key(主键所在列名);

例:alter table goods add primary key(id) 该例是把主键建立在id列上

1. 修改表之删除主键

alter table tbName drop primary key;

1. 修改表之增加索引

alter table tbName add [unique|fulltext] index 索引名(列名);

1. 修改表之删除索引

alter table tbName drop index 索引名;

1. 清空表的数据

truncate tableName;

MySQL中的列类型

列类型

整型

tinyint (0~255/-128~127)

smallint (0~65535/-32768~32767)

mediumint

int

bigint (参考手册11.2)

参数解释
unsigned 无符号(不能为负)
zerofill 0填充  M 填充后的宽度

举例

tinyint unsigned;tinyint(6) zerofill;

数值型

浮点型:floatdouble

格式:float(M,D) unsigned,zerofill;

字符型

char(m) 定长

varchar(m) 变长

text

日期时间类型

增删改查基本操作

插入数据

insert into 表名(col1,col2,……) values(val1,val2……); -- 插入指定列insert into 表名 values (,,,,); -- 插入所有列insert into 表名 values   -- 一次插入多行 (val1,val2……),(val1,val2……),(val1,val2……);

修改数据

update tablename set col1=newval1,  col2=newval2,......colN=newvalNwhere 条件;

删除数据

delete from tablenaeme where 条件;

select 查询

1. 条件查询

   where

a. 条件表达式的意义，表达式为真，则该行取出b.  比较运算符  = ，!=，< > <=  >=c.  like , not like ('%'匹配任意多个字符,'_'匹配任意单个字符) in , not in , between andd. is null , is not null

1. 分组

   group by

一般要配合5个聚合函数使用:max,min,sum,avg,count

1. 筛选 having

2. 排序 order by

3. 限制取出条目 limit

连接查询

左连接 left join

.. 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

左右连接都是以在左边的表的数据为准,沿着左表查右表.
内连接是以两张表都有的共同部分数据为准,也就是左右连接的数据之交集

子查询

where 型子查询

内层sql的返回值在where后作为条件表达式的一部分

例句

select * from tableA where colA = (select colB from tableB where ...);

from 型子查询

内层sql查询结果,作为一张表,供外层的sql语句再次查询

例句

select * from (select * from ...) as tableName where ...

exists 型子查询

exists子查询就是对外层表进行循环，再对内表进行内层查询。和in ()差不多，但是它们还是有区别的。主要是看两个张表大小差的程度。

若子查询表大则用exists（内层索引），子查询表小则用in（外层索引）;

查询知识

以下查询基于ecshop网站的商品表(goods)
在练习时可以只取部分列,方便查看。

基础查询 where

查出满足以下条件的商品

主键为32的商品

select goods_id,goods_name,shop_price      from goods     where goods_id=32;

不属第3栏目的所有商品

select goods_id,cat_id,goods_name,shop_price from goods where cat_id != 3;

本店价格高于3000元的商品

select goods_id,cat_id,goods_name,shop_price from goods where shop_price > 3000;

本店价格低于或等于100元的商品

select goods_id,cat_id,goods_name,shop_price from goodswhere shop_price <= 100;

取出第4栏目或第11栏目的商品(不许用or)

select goods_id,cat_id,goods_name,shop_price from goodswhere cat_id in(4,11);

使用or查询

select goods_id,cat_id,goods_name,shop_price from goodswhere cat_id = 4 or cat_id = 11;

取出100<=价格<=500的商品(不许用and)

select goods_id,cat_id,goods_name,shop_price from goodswhere shop_price between 100 and 500;

使用and查询

select goods_id,cat_id,goods_name,shop_price from goodswhere (shop_price >=100) and (shop_price <= 500);

取出不属于第3栏目且不属于第11栏目的商品(and,或not in分别实现)

使用and查询

select goods_id,cat_id,goods_name,shop_price from goodswhere (cat_id != 3) and (cat_id != 11);

使用not in查询

select goods_id,cat_id,goods_name,shop_price from goodswhere cat_id not in (3,11);

取出价格大于100且小于300,或者大于4000且小于5000的商品

select goods_id,cat_id,goods_name,shop_price from goodswhere (300 > shop_price > 100) or (5000 > shop_price > 4000);

取出第3个栏目下面价格<1000或>3000,并且点击量>5的系列商品

select goods_id,cat_id,goods_name,shop_price,click_countfrom goodswhere (cat_id = 3) and (3000>shop_price or shop_price>1000) and (click_count>5);

取出名字以"诺基亚"开头的商品

select goods_id,cat_id,goods_name,shop_price,click_countfrom goodswhere goods_name like "诺基亚%";

取出名字为"诺基亚Nxx"的手机

select goods_id,cat_id,goods_name,shop_price,click_countfrom goodswhere goods_name like "诺基亚N__";

取出名字不以"诺基亚"开头的商品

select goods_id,cat_id,goods_name,shop_price,click_countfrom goodswhere goods_name not like "诺基亚%";

取出第3个栏目下面价格在1000到3000之间,并且点击量>5 "诺基亚"开头的系列商品

select goods_id,cat_id,goods_name,shop_price,click_countfrom goodswhere cat_id = 3 and shop_price > 1000 and shop_price < 3000 and click_count > 5 and goods_name like '诺基亚%';

分组查询 group by

查出最贵的商品的价格

select max(shop_price) from goods;

查出最大(最新)的商品编号[goods_id最大为最新]

select max(goods_id) from goods;

查出最便宜的商品的价格

select min(shop_price) from goods;

查出最旧(最小)的商品编号

select min(goods_id) from goods;

查询该店所有商品的库存总量

select sum(goods_number)from goods;

查询所有商品的平均价

select avg(goods_number)from goods;

查询该店一共有多少种商品

select count(*)from goods;

having与group综合运用查询

查询该店的商品比市场价所节省的价格

select market_price-shop_price as sheng from goods;

查询每个商品所积压的货款(提示:库存*单价)

select shop_price*goods_number from goods;

查询该店积压的总货款

select sum(shop_price*goods_number) from goods;

查询该店每个栏目下面积压的货款

select cat_id,sum(shop_price*goods_number) from goods group bu cat_id;

查询比市场价省钱200元以上的商品及该商品所省的钱(where和having分别实现)

where实现

select goods_id,goods_name,market_price,shop_price,market_price-shop_price as shengfrom goodswhere market_price-shop_price > 200

having实现

select goods_id,goods_name,market_price,shop_price,market_price-shop_price as shengfrom goodshaving sheng > 200;

查询积压货款超过2W元的栏目,以及该栏目积压的货款

select cat_id,sum(goods_number*shop_price) as chao from goods group by cat_id having chao > 20000;

where-having-group综合练习题

有如下表及数据

要求:查询出2门及2门以上不及格者的平均成绩?

select name,sum(score < 60) as gk ,avg(score) as pj from stugroup by name having gk >=2;

order by 与 limit查询

按价格由高到低排序

select goods_id,cat_id,goods_name,shop_price,click_countfrom goodsorder by shop_price desc;

按栏目由低到高排序,栏目内部按价格由高到低排序

select goods_id,cat_id,goods_name,shop_price from goodsorder by cat_id ,shop_price desc;

取出价格最高的前三名商品

select goods_id,cat_id,goods_name,shop_price from goodsorder by shop_price desc limit 0,3;

取出点击量前三名到前5名的商品

select goods_id,cat_id,goods_name,shop_price from goodsorder by click_count desc limit 2,3;

连接查询

取出所有商品的商品名,栏目名,价格

使用左连接查询

select g.goods_name,c.cat_name,shop_pricefrom goods as gleft join category as c on g.cat_id = c.cat_id;

可以使用如下语句

select g.goods_name,c.cat_name,shop_pricefrom goods as g,category as cwhere g.cat_id = c.cat_id;

取出第4个栏目下的商品的商品名,栏目名,价格

左连接查询

select g.goods_name,c.cat_name,g.shop_price from goods as g left join category as c on g.cat_id=c.cat_id where g.cat_id = 4;

可以使用下列语句

select g.goods_name,c.cat_name,g.shop_price from goods as g ,category as c where g.cat_id=c.cat_id and g.cat_id = 4;

取出第4个栏目下的商品的商品名,栏目名,与品牌名

select goods_name,cat_name,brand_name from goods left join categoryon goods.cat_id=category.cat_idleft join brand on goods.brand_id=brand.brand_idwhere goods.cat_id = 4;

union查询

把comment,feedback两个表中的数据,各取出4列,并把结果集union成一个结果集.

select comment_id,user_id,user_name,email,content,add_time from comment limit 4unionselect msg_id,user_id,user_name,user_email,msg_content,msg_time from feedback limit4;

子查询

查询出最新一行商品(以商品编号最大为最新,用子查询实现)

select goods_id,goods_name from goods where goods_id = (select max(goods_id) from goods);

查询出编号为19的商品的栏目名称(用左连接查询和子查询分别)

左连接实现

select g.goods_name,g.cat_id,cat_name from goods as g left join category as c on g.cat_id = c.cat_id where g.goods_id = 19;

子查询实现

select cat_id,cat_name from category as c where cat_id = (select cat_id from goods where goods_id= 19 );

普通查询方式实现

select c.cat_id,cat_name from category as c,goods as g where c.cat_id =g.cat_id and  g.goods_id= 19 ;

用where型子查询把goods表中的每个栏目下面最新的商品取出来

select goods_name,cat_id from goods where goods_id in (select max(goods_id) from goods group by cat_id);

用from型子查询把goods表中的每个栏目下面最新的商品取出来

select * from (select goods_id,cat_id,goods_name from goods order by goods_id desc) as t group by cat_id;

用exists型子查询,查出所有有商品的栏目

select * from categorywhere exists (select * from goods where goods.cat_id=category.cat_id);