mysql常见函数使用

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| day01              |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use day01
Database changed
mysql> show tables;
+-----------------+
| Tables_in_day01 |
+-----------------+
| staff           |
+-----------------+
1 row in set (0.00 sec)

mysql> create table student(
    -> id int,
    -> name varchar(20),
    -> chinese float,
    -> english float,
    -> math float
    -> );
Query OK, 0 rows affected (0.20 sec)

mysql> set character_set_client=gbk
    -> ;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into student
    -> values
    -> (1,'陈真',44,88,99);
Query OK, 1 row affected (0.10 sec)

mysql> select * from student;
+------+--------+---------+---------+------+
| id   | name   | chinese | english | math |
+------+--------+---------+---------+------+
|    1 | 闄堢湡   |      44 |      88 |   99 |
+------+--------+---------+---------+------+
1 row in set (0.00 sec)

mysql> set  character_set_results=gbk;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;
+------+------+---------+---------+------+
| id   | name | chinese | english | math |
+------+------+---------+---------+------+
|    1 | 陈真     |      44 |      88 |   99 |
+------+------+---------+---------+------+
1 row in set (0.04 sec)

mysql> insert into student
    -> values
    -> (2,'太君',99,33,88);
Query OK, 1 row affected (0.08 sec)

mysql> select * from student;
+------+------+---------+---------+------+
| id   | name | chinese | english | math |
+------+------+---------+---------+------+
|    1 | 陈真     |      44 |      88 |   99 |
|    2 | 太君    |      99 |      33 |   88 |
+------+------+---------+---------+------+
2 rows in set (0.04 sec)

mysql> select distinct id from student;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.07 sec)

mysql> select all from student;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from student' at line 1
mysql> select all id from student;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> select distinctrow from student;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from student' at line 1
mysql> select distinctrow  id from student;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> insert into student
    -> values
    -> (2,'meimei',88,68,88);
Query OK, 1 row affected (0.10 sec)

mysql> select distinctrow id from student;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> select (chinese+english+math) 总分 from student;
+------+
| 总分    |
+------+
|  231 |
|  220 |
|  244 |
+------+
3 rows in set (0.06 sec)

mysql> select id, (chinese+english+math) 总分 from student;
+------+------+
| id   | 总分    |
+------+------+
|    1 |  231 |
|    2 |  220 |
|    2 |  244 |
+------+------+
3 rows in set (0.00 sec)

mysql> select id,name,(chinese+english+math) 总分 from student;
+------+--------+------+
| id   | name   | 总分    |
+------+--------+------+
|    1 | 陈真       |  231 |
|    2 | 太君      |  220 |
|    2 | meimei |  244 |
+------+--------+------+
3 rows in set (0.00 sec)

mysql> select id,name,(chinese+english+math) 总分 from student order by 总分
    -> ;
+------+--------+------+
| id   | name   | 总分    |
+------+--------+------+
|    2 | 太君      |  220 |
|    1 | 陈真       |  231 |
|    2 | meimei |  244 |
+------+--------+------+
3 rows in set (0.03 sec)

mysql> select id,name,(chinese+english+math) 总分 from student order by 总分 desc;
+------+--------+------+
| id   | name   | 总分    |
+------+--------+------+
|    2 | meimei |  244 |
|    1 | 陈真       |  231 |
|    2 | 太君      |  220 |
+------+--------+------+
3 rows in set (0.00 sec)

mysql> select id,name,(chinese+english-math*chines/math) 四则运算 from student;
ERROR 1054 (42S22): Unknown column 'chines' in 'field list'
mysql> select id,name,(chinese+english-math*chinese/math) 四则运算 from student;
+------+--------+----------+
| id   | name   | 四则运算         |
+------+--------+----------+
|    1 | 陈真       |       88 |
|    2 | 太君      |       33 |
|    2 | meimei |       68 |
+------+--------+----------+
3 rows in set (0.03 sec)

mysql> select id,name,(chinese+english-math*chinese/math) 四则运算 from student where 四则运算>80;
ERROR 1054 (42S22): Unknown column '四则运算' in 'where clause'
mysql> select id,name,(chinese+english-math*chinese/math) as 四则运算 from student where 四则运算>80;
ERROR 1054 (42S22): Unknown column '四则运算' in 'where clause'
mysql> select id,name,(chinese+english-math*chinese/math) as 四则运算 from student where (chinese+english-math*chinese/math)>80;
+------+------+----------+
| id   | name | 四则运算         |
+------+------+----------+
|    1 | 陈真     |       88 |
+------+------+----------+
1 row in set (0.05 sec)

mysql> select id,name,(chinese+english-math*chinese/math) as aa from student where aa>80;
ERROR 1054 (42S22): Unknown column 'aa' in 'where clause'
mysql> select * from student t where t.english beteen 78 and 89;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'beteen 78 and 89' at line 1
mysql> select * from student t where t.english between 78 and 89;
+------+------+---------+---------+------+
| id   | name | chinese | english | math |
+------+------+---------+---------+------+
|    1 | 陈真     |      44 |      88 |   99 |
+------+------+---------+---------+------+
1 row in set (0.00 sec)

mysql> select * from student where english >78 and english <89;
+------+------+---------+---------+------+
| id   | name | chinese | english | math |
+------+------+---------+---------+------+
|    1 | 陈真     |      44 |      88 |   99 |
+------+------+---------+---------+------+
1 row in set (0.00 sec)

mysql> select * from student t where t.english=78 or t.english =88;
+------+------+---------+---------+------+
| id   | name | chinese | english | math |
+------+------+---------+---------+------+
|    1 | 陈真     |      44 |      88 |   99 |
+------+------+---------+---------+------+
1 row in set (0.00 sec)

mysql> select * from student  t where t.english in (78,88);
+------+------+---------+---------+------+
| id   | name | chinese | english | math |
+------+------+---------+---------+------+
|    1 | 陈真     |      44 |      88 |   99 |
+------+------+---------+---------+------+
1 row in set (0.02 sec)

mysql> select * from student t where t.name like '陈';
Empty set (0.00 sec)

mysql> select * from student t where t.name like '陈%';
+------+------+---------+---------+------+
| id   | name | chinese | english | math |
+------+------+---------+---------+------+
|    1 | 陈真     |      44 |      88 |   99 |
+------+------+---------+---------+------+
1 row in set (0.00 sec)

mysql> select * from student t where t.name like '%陈';
Empty set (0.00 sec)

mysql> select * from student t where t.name like '%真';
+------+------+---------+---------+------+
| id   | name | chinese | english | math |
+------+------+---------+---------+------+
|    1 | 陈真     |      44 |      88 |   99 |
+------+------+---------+---------+------+
1 row in set (0.00 sec)

mysql> select * from student t where t.name like '%真%';
+------+------+---------+---------+------+
| id   | name | chinese | english | math |
+------+------+---------+---------+------+
|    1 | 陈真     |      44 |      88 |   99 |
+------+------+---------+---------+------+
1 row in set (0.00 sec)

mysql> select * from student t where t.name like '%_真';
+------+------+---------+---------+------+
| id   | name | chinese | english | math |
+------+------+---------+---------+------+
|    1 | 陈真     |      44 |      88 |   99 |
+------+------+---------+---------+------+
1 row in set (0.00 sec)

mysql> select * from student t where t.name like '%__真';
Empty set (0.00 sec)

mysql> select * from student order by english desc;
+------+--------+---------+---------+------+
| id   | name   | chinese | english | math |
+------+--------+---------+---------+------+
|    1 | 陈真       |      44 |      88 |   99 |
|    2 | meimei |      88 |      68 |   88 |
|    2 | 太君      |      99 |      33 |   88 |
+------+--------+---------+---------+------+
3 rows in set (0.00 sec)

mysql> help desc
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
   EXPLAIN
   GROUP_CONCAT
   SELECT

mysql> select * from student order by english asc;
+------+--------+---------+---------+------+
| id   | name   | chinese | english | math |
+------+--------+---------+---------+------+
|    2 | 太君      |      99 |      33 |   88 |
|    2 | meimei |      88 |      68 |   88 |
|    1 | 陈真       |      44 |      88 |   99 |
+------+--------+---------+---------+------+
3 rows in set (0.00 sec)

mysql> select * from student order by english asc limit 0,1;
+------+------+---------+---------+------+
| id   | name | chinese | english | math |
+------+------+---------+---------+------+
|    2 | 太君    |      99 |      33 |   88 |
+------+------+---------+---------+------+
1 row in set (0.00 sec)

mysql> select * from student;
+------+--------+---------+---------+------+
| id   | name   | chinese | english | math |
+------+--------+---------+---------+------+
|    1 | 陈真       |      44 |      88 |   99 |
|    2 | 太君      |      99 |      33 |   88 |
|    2 | meimei |      88 |      68 |   88 |
+------+--------+---------+---------+------+
3 rows in set (0.00 sec)

mysql> select * from student limit 0,1;
+------+------+---------+---------+------+
| id   | name | chinese | english | math |
+------+------+---------+---------+------+
|    1 | 陈真     |      44 |      88 |   99 |
+------+------+---------+---------+------+
1 row in set (0.00 sec)

mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.06 sec)

mysql> select sum(english) from student;
+--------------+
| sum(english) |
+--------------+
|          189 |
+--------------+
1 row in set (0.00 sec)

mysql> select sum(english)/count(*) from student;
+-----------------------+
| sum(english)/count(*) |
+-----------------------+
|                    63 |
+-----------------------+
1 row in set (0.04 sec)

mysql> select sum(english)/count(*) 平均分 from student;
+--------+
| 平均分      |
+--------+
|     63 |
+--------+
1 row in set (0.00 sec)

mysql> select count(*) from student t where (t.english+t.chinese+t.math)>200;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

mysql> select * ,(t.english+t.chinese+t.math) 总分 from student t where (t.english+t.chinese+t.math)>200;
+------+--------+---------+---------+------+------+
| id   | name   | chinese | english | math | 总分    |
+------+--------+---------+---------+------+------+
|    1 | 陈真       |      44 |      88 |   99 |  231 |
|    2 | 太君      |      99 |      33 |   88 |  220 |
|    2 | meimei |      88 |      68 |   88 |  244 |
+------+--------+---------+---------+------+------+
3 rows in set (0.00 sec)

mysql> select * ,(t.english+t.chinese+t.math) 总分 from student t where (t.english+t.chinese+t.math)>240;
+------+--------+---------+---------+------+------+
| id   | name   | chinese | english | math | 总分    |
+------+--------+---------+---------+------+------+
|    2 | meimei |      88 |      68 |   88 |  244 |
+------+--------+---------+---------+------+------+
1 row in set (0.00 sec)

mysql> select avg(math) from student;
+-------------------+
| avg(math)         |
+-------------------+
| 91.66666666666667 |
+-------------------+
1 row in set (0.00 sec)

mysql> select max(math) from student;
+-----------+
| max(math) |
+-----------+
|        99 |
+-----------+
1 row in set (0.05 sec)

mysql> select min(math) from student;
+-----------+
| min(math) |
+-----------+
|        88 |
+-----------+
1 row in set (0.00 sec)

mysql> select count(*) from student where min(math);
ERROR 1111 (HY000): Invalid use of group function
mysql> create table order(
    -> id int,
    -> name varchar(20),
    -> address varchar(20),
    -> price float
    -> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order(
id int,
name varchar(20),
address varchar(20),
price float
)' at line 1
mysql> create table orders(
    -> id int,
    -> name varchar(20),
    -> address varchar(20),
    -> price float
    -> );
Query OK, 0 rows affected (0.20 sec)

mysql> insert into orders
    -> values
    -> (1,'a','guanzhou',22);
Query OK, 1 row affected (0.04 sec)

mysql> insert into orders
    -> values
    -> (2,'b','guanzhou',33);
Query OK, 1 row affected (0.05 sec)

mysql> insert into orders
    -> values
    -> (3,'b','beijing',33);
Query OK, 1 row affected (0.17 sec)

mysql> select * from student group by address;
ERROR 1054 (42S22): Unknown column 'address' in 'group statement'
mysql> select count(*) from student group by address;
ERROR 1054 (42S22): Unknown column 'address' in 'group statement'
mysql> select count(*) from orders group by address;
+----------+
| count(*) |
+----------+
|        1 |
|        2 |
+----------+
2 rows in set (0.00 sec)

mysql> select * from orders group by address;
+------+------+----------+-------+
| id   | name | address  | price |
+------+------+----------+-------+
|    3 | b    | beijing  |    33 |
|    1 | a    | guanzhou |    22 |
+------+------+----------+-------+
2 rows in set (0.00 sec)

mysql> select * from orders group by address;
+------+------+----------+-------+
| id   | name | address  | price |
+------+------+----------+-------+
|    3 | b    | beijing  |    33 |
|    1 | a    | guanzhou |    22 |
+------+------+----------+-------+
2 rows in set (0.00 sec)

mysql> insert into orders
    -> values;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2
mysql> insert into orders
    -> values
    -> (3,'c','beijing',32);
Query OK, 1 row affected (0.10 sec)

mysql> select * from orders;
+------+------+----------+-------+
| id   | name | address  | price |
+------+------+----------+-------+
|    1 | a    | guanzhou |    22 |
|    2 | b    | guanzhou |    33 |
|    3 | b    | beijing  |    33 |
|    3 | c    | beijing  |    32 |
+------+------+----------+-------+
4 rows in set (0.00 sec)

mysql> select * from orders group by address;
+------+------+----------+-------+
| id   | name | address  | price |
+------+------+----------+-------+
|    3 | b    | beijing  |    33 |
|    1 | a    | guanzhou |    22 |
+------+------+----------+-------+
2 rows in set (0.00 sec)

mysql> select *,sum(price) from orders group by address;
+------+------+----------+-------+------------+
| id   | name | address  | price | sum(price) |
+------+------+----------+-------+------------+
|    3 | b    | beijing  |    33 |         65 |
|    1 | a    | guanzhou |    22 |         55 |
+------+------+----------+-------+------------+
2 rows in set (0.00 sec)

mysql> select *,sum(price) from orders group by address having sum(price)>60;
+------+------+---------+-------+------------+
| id   | name | address | price | sum(price) |
+------+------+---------+-------+------------+
|    3 | b    | beijing |    33 |         65 |
+------+------+---------+-------+------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.36    |
+-----------+
1 row in set (0.04 sec)

mysql> select curent_date
    -> ;
ERROR 1054 (42S22): Unknown column 'curent_date' in 'field list'
mysql> select current_date;
+--------------+
| current_date |
+--------------+
| 2014-02-18   |
+--------------+
1 row in set (0.05 sec)

mysql> select 'huoniao';
+---------+
| huoniao |
+---------+
| huoniao |
+---------+
1 row in set (0.00 sec)

mysql> select date;
ERROR 1054 (42S22): Unknown column 'date' in 'field list'
mysql> select 1+2;
+-----+
| 1+2 |
+-----+
|   3 |
+-----+
1 row in set (0.00 sec)

mysql> select password('huoniao');
+-------------------------------------------+
| password('huoniao')                       |
+-------------------------------------------+
| *95787E1FB8F0CC3A73789D5F401212633777B679 |
+-------------------------------------------+
1 row in set (0.04 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2014-02-18 23:28:39 |
+---------------------+
1 row in set (0.00 sec)