=
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‘,
‘214896335@qq.com‘,
‘2155789634‘,
‘213‘,
‘2010-03-17 16:55:34‘,
‘1.jpg‘);
INSERT INTO `users` VALUES (‘2‘,
‘蔡准‘,
‘123‘,
‘男‘,
‘1998-10-28 00:00:00‘,
‘北京‘,
‘14786593245‘,
‘258269775@qq.com‘,
‘1515645‘,
‘79‘,
‘2009-09-07 10:36:47‘,
‘2.jpg‘);
INSERT INTO `users` VALUES (‘3‘,
‘段湘林‘,
‘123‘,
‘男‘,
‘2000-03-01 00:00:00‘,
‘长沙‘,
‘18974521635‘,
‘127582934@qq.com‘,
‘24965752‘,
‘85‘,
‘2015-10-29 14:25:42‘,
‘3.jpg‘);
INSERT INTO `users` VALUES (‘4‘,
‘盛伟刚‘,
‘123‘,
‘男‘,
‘1994-04-20 00:00:00‘,
‘上海‘,
‘13598742685‘,
‘24596325@qq.com‘,
‘36987452‘,
‘163‘,
‘2012-09-07 11:36:47‘,
‘1.jpg‘);
INSERT INTO `users` VALUES (‘5‘,
‘李珍珍‘,
‘123‘,
‘女‘,
‘1989-09-03 00:00:00‘,
‘上海‘,
‘14752369842‘,
‘24589632@qq.com‘,
‘98654287‘,
‘986‘,
‘2003-01-27 10:38:52‘,
‘1.jpg‘);
INSERT INTO `users` VALUES (‘6‘,
‘常浩萍‘,
‘123‘,
‘女‘,
‘1985-09-24 00:00:00‘,
‘北京‘,
‘16247536915‘,
‘2157596@qq.com‘,
‘96585236‘,
‘12‘,
‘2013-11-07 05:36:47‘,
‘6.jpg‘);
INSERT INTO `users` VALUES (‘7‘,
‘柴宗文‘,
‘123‘,
‘男‘,
‘1983-02-19 00:00:00‘,
‘北京‘,
‘18245739214‘,
‘225489365@qq.com‘,
‘2548965‘,
‘34‘,
‘2016-09-07 16:39:24‘,
null);
INSERT INTO `users` VALUES (‘8‘,
‘李莎‘,
‘123‘,
‘女‘,
‘1994-01-24 00:00:00‘,
‘重庆‘,
‘17632954782‘,
‘458963785@qq.com‘,
‘785412‘,
‘196‘,
‘2014-07-31 19:46:19‘,
‘3.jpg‘);
INSERT INTO `users` VALUES (‘9‘,
‘陈瑾‘,
‘123‘,
‘女‘,
‘2001-07-02 00:00:00‘,
‘长沙‘,
‘15874269513‘,
‘2159635874@qq.com‘,
‘63589426‘,
‘254‘,
‘2012-07-26 16:49:32‘,
‘5.jpg‘);
INSERT INTO `users` VALUES (‘10‘,
‘次旦多吉‘,
‘123‘,
‘男‘,
‘2008-12-23 00:00:00‘,
‘长沙‘,
‘17654289375‘,
‘2459632@qq.com‘,
‘85796321458‘,
‘1000‘,
‘2004-03-10 17:46:43‘,
‘4.jpg‘);
INSERT INTO `users` VALUES (‘11‘,
‘冯玲芬‘,
‘123‘,
‘女‘,
‘1983-09-12 00:00:00‘,
‘长沙‘,
‘19875236942‘,
‘25578963@qq.com‘,
‘2365897‘,
‘158‘,
‘2015-11-26 06:32:56‘,
‘5.jpg‘);
INSERT INTO `users` VALUES (‘12‘,
‘范丙全‘,
‘123‘,
‘男‘,
‘1984-04-29 00:00:00‘,
‘长沙‘,
‘17652149635‘,
‘2225478@qq.com‘,
‘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)
mysql查询表格方法汇总1
标签：sel sql查询 uem efault key pwd reg mysql mysql查询
