查库:select schema_name from information_schema.schemata;
查表:select table_name from information_schema.tables where table_schema='security';
查列:select column_name from information_schema.columns where table_name='users';
查字段:select username,password from security.users;
基本函数
left() 函数
left(a,b) 从左侧截取a的前b位,正确返回1,错误返回0
mysql> select left(database(),3)='s'; // 截取前三位,但是右边就只有一位
+------------------------+
| left(database(),3)='s' |
+------------------------+
| 0 | // 错误
+------------------------+
1 row in set (0.00 sec)
mysql> select left(database(),3)='sec';
+--------------------------+
| left(database(),3)='sec' |
+--------------------------+
| 1 |
+--------------------------+
1 row in set (0.00 sec)
mysql> select left(database(),1)='s';
+------------------------+
| left(database(),1)='s' |
+------------------------+
| 1 |
+------------------------+
1 row in set (0.00 sec)
例如:
concat_ws(',','1','2','3')
输出的值为:1,2,3
//concat_ws() 全称:concat with separator
第一个参数为分隔符,其他的为值,输出结果为每个值用分隔符分开。值的数量是无限的
group_concat() 函数
select id,group_concat(name) from aa group by id;
+------+--------------------+
| id| group_concat(name) |
+------+--------------------+
|1 | 10,20,20|
|2 | 20 |
|3 | 200,500|
+------+--------------------+
3 rows in set (0.00 sec)
————————————————
//该函数为把返回的值满足条件的输出到一个框框里面
group by 函数
//根据一个或者多个列对结果集进行分组
//最重要的就是去掉重复的数据
Orders表
O_Id OrderDate OrderPrice Customer
1 2008/12/29 1000 Bush
2 2008/11/23 1600 Carter
3 2008/10/05 700 Bush
4 2008/09/28 300 Bush
5 2008/08/06 2000 Adams
6 2008/07/21 100 Carter
mysql > SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer;
Customer SUM(OrderPrice)
Bush 2000
Carter 1700
Adams 2000
count() 函数
//返回表中记录数(横为记录,竖为字段)
mysql count(*) from table_name; //返回 table_name 表中的所有记录数
Orders表
O_Id OrderDate OrderPrice Customer
1 2008/12/29 1000 Bush
2 2008/11/23 1600 Carter
3 2008/10/05 700 Bush
4 2008/09/28 300 Bush
5 2008/08/06 2000 Adams
6 2008/07/21 100 Carter
mysql > SELECT COUNT(Customer) AS CustomerNilsen FROM Orders WHERE Customer='Carter'
+-------------------+
| CustomerNilsen |
+-------------------+
| 2 |
+-------------------+
1 row in set, 1 warning (0.00 sec)
floor() 函数
//向下取整
mysql > select floor(20.11);
+----------------------+
| floor(20.11) |
+-----------