mysql查询表格方法汇总1
use onlinedb; SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for users -- ---------------------------- DROP TABLE IF EXISTS `users`; CREATE TABLE `users` ( `uID` int(11) NOT NULL AUTO_INCREMENT, `uName` varchar(30) NOT NULL, `uPwd` varchar(30) NOT NULL, `uSex` varchar(2) DEFAULT ‘男‘, `uBirth` datetime DEFAULT ‘0000-00-00 00:00:00‘, `uCity` varchar(50) DEFAULT NULL, `uPhone` varchar(20) DEFAULT NULL, `uEmail` varchar(50) DEFAULT NULL, `uQQ` varchar(20) DEFAULT NULL, `uCredit` int(11) DEFAULT NULL, `uRegTime` datetime DEFAULT NULL, `uImage` varchar(100) DEFAULT NULL, PRIMARY KEY (`uID`) ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of users -- ---------------------------- INSERT INTO `users` VALUES (‘1‘, ‘郭炳颜‘, ‘123‘, ‘男‘, ‘1994-12-28 00:00:00‘, ‘长沙‘, ‘17598632598‘, ‘‘, ‘2155789634‘, ‘213‘, ‘2010-03-17 16:55:34‘, ‘1.jpg‘); INSERT INTO `users` VALUES (‘2‘, ‘蔡准‘, ‘123‘, ‘男‘, ‘1998-10-28 00:00:00‘, ‘北京‘, ‘14786593245‘, ‘‘, ‘1515645‘, ‘79‘, ‘2009-09-07 10:36:47‘, ‘2.jpg‘); INSERT INTO `users` VALUES (‘3‘, ‘段湘林‘, ‘123‘, ‘男‘, ‘2000-03-01 00:00:00‘, ‘长沙‘, ‘18974521635‘, ‘‘, ‘24965752‘, ‘85‘, ‘2015-10-29 14:25:42‘, ‘3.jpg‘); INSERT INTO `users` VALUES (‘4‘, ‘盛伟刚‘, ‘123‘, ‘男‘, ‘1994-04-20 00:00:00‘, ‘上海‘, ‘13598742685‘, ‘‘, ‘36987452‘, ‘163‘, ‘2012-09-07 11:36:47‘, ‘1.jpg‘); INSERT INTO `users` VALUES (‘5‘, ‘李珍珍‘, ‘123‘, ‘女‘, ‘1989-09-03 00:00:00‘, ‘上海‘, ‘14752369842‘, ‘‘, ‘98654287‘, ‘986‘, ‘2003-01-27 10:38:52‘, ‘1.jpg‘); INSERT INTO `users` VALUES (‘6‘, ‘常浩萍‘, ‘123‘, ‘女‘, ‘1985-09-24 00:00:00‘, ‘北京‘, ‘16247536915‘, ‘‘, ‘96585236‘, ‘12‘, ‘2013-11-07 05:36:47‘, ‘6.jpg‘); INSERT INTO `users` VALUES (‘7‘, ‘柴宗文‘, ‘123‘, ‘男‘, ‘1983-02-19 00:00:00‘, ‘北京‘, ‘18245739214‘, ‘‘, ‘2548965‘, ‘34‘, ‘2016-09-07 16:39:24‘, null); INSERT INTO `users` VALUES (‘8‘, ‘李莎‘, ‘123‘, ‘女‘, ‘1994-01-24 00:00:00‘, ‘重庆‘, ‘17632954782‘, ‘‘, ‘785412‘, ‘196‘, ‘2014-07-31 19:46:19‘, ‘3.jpg‘); INSERT INTO `users` VALUES (‘9‘, ‘陈瑾‘, ‘123‘, ‘女‘, ‘2001-07-02 00:00:00‘, ‘长沙‘, ‘15874269513‘, ‘‘, ‘63589426‘, ‘254‘, ‘2012-07-26 16:49:32‘, ‘5.jpg‘); INSERT INTO `users` VALUES (‘10‘, ‘次旦多吉‘, ‘123‘, ‘男‘, ‘2008-12-23 00:00:00‘, ‘长沙‘, ‘17654289375‘, ‘‘, ‘85796321458‘, ‘1000‘, ‘2004-03-10 17:46:43‘, ‘4.jpg‘); INSERT INTO `users` VALUES (‘11‘, ‘冯玲芬‘, ‘123‘, ‘女‘, ‘1983-09-12 00:00:00‘, ‘长沙‘, ‘19875236942‘, ‘‘, ‘2365897‘, ‘158‘, ‘2015-11-26 06:32:56‘, ‘5.jpg‘); INSERT INTO `users` VALUES (‘12‘, ‘范丙全‘, ‘123‘, ‘男‘, ‘1984-04-29 00:00:00‘, ‘长沙‘, ‘17652149635‘, ‘‘, ‘5236987523‘, ‘72‘, ‘2013-07-18 16:47:12‘, null);
首先创建新表，结果如下
查询
mysql> select uname,year(now())-year(ubirth) from users;
备注：查询当前年龄减去出生年龄，也就是查询年龄，结果如下
+--------------+--------------------------+ | uname | year(now())-year(ubirth) | | ----- | ------------------------ | | | | +--------------+--------------------------+ | 郭炳颜 | 26 | | -------- | ---- | | | | | 蔡准 | 22 | | ---- | ---- | | | | | 段湘林 | 20 | | ------ | ---- | | | | | 盛伟刚 | 26 | | ------ | ---- | | | | | 李珍珍 | 31 | | ------ | ---- | | | | | 常浩萍 | 35 | | ------ | ---- | | | | | 柴宗文 | 37 | | ------ | ---- | | | | | 李莎 | 26 | | ---- | ---- | | | | | 陈瑾 | 19 | | ---- | ---- | | | | | 次旦多吉 | 12 | | -------- | ---- | | | | | 冯玲芬 | 37 | | ------ | ---- | | | | | 范丙全 | 36 | | ------ | ---- | | | | +--------------+--------------------------+ 12 rows in set (0.00 sec)
mysql> select gdname as 商品名,gdprice as 价格,gdcity as 城市 from goods;
查询商品名，价格，城市，并用中文
为查询列表指定标题，在后面加as
mysql> select uname as 姓名,year(now())-year(ubirth) as 年龄 from users;
结果
+--------------+--------+ | 姓名 | 年龄 | | ---- | ---- | | | | +--------------+--------+ | 郭炳颜 | 26 | | -------- | ---- | | | | | 蔡准 | 22 | | ---- | ---- | | | | | 段湘林 | 20 | | ------ | ---- | | | | | 盛伟刚 | 26 | | ------ | ---- | | | | | 李珍珍 | 31 | | ------ | ---- | | | | | 常浩萍 | 35 | | ------ | ---- | | | | | 柴宗文 | 37 | | ------ | ---- | | | | | 李莎 | 26 | | ---- | ---- | | | | | 陈瑾 | 19 | | ---- | ---- | | | | | 次旦多吉 | 12 | | -------- | ---- | | | | | 冯玲芬 | 37 | | ------ | ---- | | | | | 范丙全 | 36 | | ------ | ---- | | | | +--------------+--------+ 12 rows in set (0.00 sec)
mysql> select uID,uName from users
-> where uID=8;###############################################################################################################
mysql> select uID,uName,uPhone,uBirth from users
-> where uBirth>=‘2000-1-1‘;运算符
mysql> select gdname,gdprice from goods
-> where not(gdprice>50);结果+--------------+---------+
| gdname | gdprice | | ------ | ------- | | | | +--------------+---------+ | 咖啡壶 | 50 | | --------- | ---- | | | | | LED小台灯 | 29 | | --------- | ---- | | | | +--------------+---------+ 2 rows in set (0.00 sec)
mysql> select gdname,gdprice,gdcity
-> from goods
-> where(gdcity=‘长沙‘or gdcity=‘西安‘)and gdprice<=50;结果
+--------------+---------+--------+ | gdname | gdprice | gdcity | | ------ | ------- | ------ | | | | | +--------------+---------+--------+ | LED小台灯 | 29 | 长沙 | | --------- | ---- | ---- | | | | | +--------------+---------+--------+ 1 row in set (0.00 sec)