mysql中的一些用法:
1、user():列出数据库的用户名;
version():列出mysql的版本;
current_date:当日日期;
now():当前时间;
show tables:列出数据库中所有的表名;
show database:列出所有的数据库;
describle table_name:列出表的列名,及列的类型等信息;
2、从文件中将数据导入数据到mysql
LOAD DATA LOCAL INFILE 'd:\file.txt' INTO TABLE table_name LINES TERMINATED BY '\r\n'
3、计算生日的sql语句
select name,birth,curdate(),(year(curdate())-year(birth))-RIGHT(curdate(),5)<RIGHT(birth,5)) as age from table name;
其中year(curdate())-year(birth))-RIGHT(curdate(),5)<RIGHT(birth,5)表示当出身那天的月、日比当前日期的月日小时,年分需较少一岁;
注:year():获取年份,month():获取月份,day()或dayofmonth()获取日;
4、选择下个月过生日的记录
方法一:select name,birth from table_name
where month(birth) = month(date_add(curdate(),interval 1 month));
以上方法对小于12月份的月都适用,可是在计算12月份的下个月时,再加一就会成为13月,显然与事实不符;需要用方法二
方法二:SELECT name, birth FROM pet
WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;
5、搜索各类书籍中价格最贵的书
select article,max(price) as Price from table_name where group by article;
6、搜索价格最贵的书籍
select article ,price from shop as s1 where price = (select max(s2.price) from shop s2 where s1.article = s2.article)
7、在mysql中可以将搜索到的数据存于一个变量当中,以便以后使用
变量的存储方式为:@var := some_thing
例:select @min_price := min(price),@max_price:=max(price) from shop;
select * from shop where price=@min_price or price=@max_price;
1、user():列出数据库的用户名;
version():列出mysql的版本;
current_date:当日日期;
now():当前时间;
show tables:列出数据库中所有的表名;
show database:列出所有的数据库;
describle table_name:列出表的列名,及列的类型等信息;
2、从文件中将数据导入数据到mysql
LOAD DATA LOCAL INFILE 'd:\file.txt' INTO TABLE table_name LINES TERMINATED BY '\r\n'
3、计算生日的sql语句
select name,birth,curdate(),(year(curdate())-year(birth))-RIGHT(curdate(),5)<RIGHT(birth,5)) as age from table name;
其中year(curdate())-year(birth))-RIGHT(curdate(),5)<RIGHT(birth,5)表示当出身那天的月、日比当前日期的月日小时,年分需较少一岁;
注:year():获取年份,month():获取月份,day()或dayofmonth()获取日;
4、选择下个月过生日的记录
方法一:select name,birth from table_name
where month(birth) = month(date_add(curdate(),interval 1 month));
以上方法对小于12月份的月都适用,可是在计算12月份的下个月时,再加一就会成为13月,显然与事实不符;需要用方法二
方法二:SELECT name, birth FROM pet
WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;
5、搜索各类书籍中价格最贵的书
select article,max(price) as Price from table_name where group by article;
6、搜索价格最贵的书籍
select article ,price from shop as s1 where price = (select max(s2.price) from shop s2 where s1.article = s2.article)
7、在mysql中可以将搜索到的数据存于一个变量当中,以便以后使用
变量的存储方式为:@var := some_thing
例:select @min_price := min(price),@max_price:=max(price) from shop;
select * from shop where price=@min_price or price=@max_price;