©
This document usesPHP Chinese website manualRelease
确认已安装MySQL服务器并启动mysqld服务
新建数据库,并将如下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 result values ('张三','数学',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
显示数据库:
show databases;
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | app_blogcurder | | mysql | | performance_schema | | shop | +--------------------+ 5 rows in set (0.00 sec)
选择数据库:
use dbname;
mysql> use test; Database changed
另外:
通过
\s
查看当前所选择的数据库
mysql> \s mysql Ver 14.14 Distrib 5.5.29, for Linux (x86_64) using readline 5.1 Connection id: 3 Current database: test Current user: root@localhost SSL: Not in use
创建数据库:
create database dbname charset utf8;
建议指定字符集,罗在工作中遇到一个关于不同类型数据库导入到处字符集的问题
mysql> create database test charset utf8; Query OK, 1 row affected (0.00 sec)
删除数据库:
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;
修改表之增加列
alter table tbName add 列名称1 列类型 [列参数] [not null default] [comment '备注'] [after filedName];
add之后的旧列名之后的语法和创建表时的列声明一样
举例
alter table user add phone char(11) not null default '' comment '手机号码' after `name`;
修改表之修改列
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];
修改表之减少列
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
修改表之增加主键
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;
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
列 | 实存字符i | 实占空间 | 利用率 |
---|---|---|---|
char(M) | 0<=i<=M | M | i/m<=100% |
varchar(M) | 0<=i<=M | i+1,2 | i/i+1/2<100% |
列类型 | 解释 |
---|---|
year | YYYY 范围:1901~2155. 可输入值2位和4位(如98,2012) |
date | YYYY-MM-DD 如:2010-03-14 |
time | HH:MM:SS 如:19:26:32 |
datetime | YYYY-MM-DD HH:MM:SS 如:2010-03-14 19:26:32 |
timestamp | YYYY-MM-DD HH:MM:SS 特性:不用赋值,该列会为自己赋当前的具体时间 |
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=newvalN where 条件;
delete from tablenaeme where 条件;
条件查询
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;
与左连接相同
左右连接都是以在左边的表的数据为准,沿着左表查右表.
内连接是以两张表都有的共同部分数据为准,也就是左右连接的数据之交集
内层sql的返回值在where后作为条件表达式的一部分
例句
select * from tableA where colA = (select colB from tableB where ...);
内层sql查询结果,作为一张表,供外层的sql语句再次查询
例句
select * from (select * from ...) as tableName where ...
exists子查询就是对外层表进行循环,再对内表进行内层查询。和in ()差不多,但是它们还是有区别的。主要是看两个张表大小差的程度。
若子查询表大则用exists(内层索引),子查询表小则用in(外层索引);
以下查询基于ecshop网站的商品表(goods)
在练习时可以只取部分列,方便查看。
查出满足以下条件的商品
select goods_id,goods_name,shop_price from goods where goods_id=32;
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 shop_price > 3000;
select goods_id,cat_id,goods_name,shop_price from goods where shop_price <= 100;
select goods_id,cat_id,goods_name,shop_price from goods where cat_id in(4,11);
select goods_id,cat_id,goods_name,shop_price from goods where cat_id = 4 or cat_id = 11;
select goods_id,cat_id,goods_name,shop_price from goods where shop_price between 100 and 500;
select goods_id,cat_id,goods_name,shop_price from goods where (shop_price >=100) and (shop_price <= 500);
select goods_id,cat_id,goods_name,shop_price from goods where (cat_id != 3) and (cat_id != 11);
select goods_id,cat_id,goods_name,shop_price from goods where cat_id not in (3,11);
select goods_id,cat_id,goods_name,shop_price from goods where (300 > shop_price > 100) or (5000 > shop_price > 4000);
select goods_id,cat_id,goods_name,shop_price,click_count from goods where (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_count from goods where goods_name like "诺基亚%";
select goods_id,cat_id,goods_name,shop_price,click_count from goods where goods_name like "诺基亚N__";
select goods_id,cat_id,goods_name,shop_price,click_count from goods where goods_name not like "诺基亚%";
select goods_id,cat_id,goods_name,shop_price,click_count from goods where cat_id = 3 and shop_price > 1000 and shop_price < 3000 and click_count > 5 and goods_name like '诺基亚%';
select max(shop_price) from goods;
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;
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;
select goods_id,goods_name,market_price,shop_price,market_price-shop_price as sheng from goods where market_price-shop_price > 200
select goods_id,goods_name,market_price,shop_price,market_price-shop_price as sheng from goods having sheng > 200;
select cat_id,sum(goods_number*shop_price) as chao from goods group by cat_id having chao > 20000;
有如下表及数据
name | subject | score |
---|---|---|
张三 | 数学 | 90 |
张三 | 语文 | 50 |
张三 | 地理 | 40 |
李四 | 语文 | 55 |
李四 | 政治 | 45 |
王五 | 政治 | 30 |
要求:查询出2门及2门以上不及格者的平均成绩?
select name,sum(score < 60) as gk ,avg(score) as pj from stu group by name having gk >=2;
select goods_id,cat_id,goods_name,shop_price,click_count from goods order by shop_price desc;
select goods_id,cat_id,goods_name,shop_price from goods order by cat_id ,shop_price desc;
select goods_id,cat_id,goods_name,shop_price from goods order by shop_price desc limit 0,3;
select goods_id,cat_id,goods_name,shop_price from goods order by click_count desc limit 2,3;
select g.goods_name,c.cat_name,shop_price from goods as g left join category as c on g.cat_id = c.cat_id;
select g.goods_name,c.cat_name,shop_price from goods as g,category as c where g.cat_id = c.cat_id;
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;
select goods_name,cat_name,brand_name from goods left join category on goods.cat_id=category.cat_id left join brand on goods.brand_id=brand.brand_id where goods.cat_id = 4;
select comment_id,user_id,user_name,email,content,add_time from comment limit 4 union select 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);
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 ;
select goods_name,cat_id from goods where goods_id in (select max(goods_id) from goods group by cat_id);
select * from (select goods_id,cat_id,goods_name from goods order by goods_id desc) as t group by cat_id;
select * from category where exists (select * from goods where goods.cat_id=category.cat_id);