一 G
作用：改变输出结果的显示方式，使输出按列显示。
注意：G为大写字母，使用 G 参数后，sql语句不加分隔符；。如加上，会报一下错误：
mysql> select * from mysql.user G *************************** 1. row *************************** Host: localhost User: root Password: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y
PS：
mysql> select * from mysql.user G; *************************** 1. row *************************** Host: localhost User: root Password: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: Y References_priv: Y Index_priv: Y Alter_priv: Y Show_db_priv: Y Super_priv: Y Create_tmp_table_priv: Y
ERROR:
No query specified
二 插入表格
两种操作的对比：
create操作，表格先前不存在
mysql> create table t1(x char(60),y char(16)) select host,user from mysql.user; Query OK, 11 rows affected (0.57 sec) Records: 11 Duplicates: 0 Warnings: 0 mysql> create table t2(host char(60),user char(16)) select host,user from mysql.user; Query OK, 11 rows affected (0.58 sec) Records: 11 Duplicates: 0 Warnings: 0 mysql> desc t1; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | x | char(60) | YES | | NULL | | | y | char(16) | YES | | NULL | | | host | char(60) | NO | | | | | user | char(16) | NO | | | | +-------+----------+------+-----+---------+-------+ 4 rows in set (0.01 sec) mysql> desc t2; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | host | char(60) | YES | | NULL | | | user | char(16) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.01 sec)
mysql> create table t1(id int ,name char(10)); Query OK, 0 rows affected (0.30 sec) mysql> create table t2(id int ,name char(10)); Query OK, 0 rows affected (0.29 sec) mysql> insert t2 values -> (1,'alex'), -> (2,'egon'); Query OK, 2 rows affected (0.29 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> insert t1 values -> (1,'wupeiqi'); Query OK, 1 row affected (0.30 sec) mysql> insert t1 select id,name from t2; Query OK, 2 rows affected (0.32 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from t1; +------+---------+ | id | name | +------+---------+ | 1 | wupeiqi | | 1 | alex | | 2 | egon | +------+---------+ 3 rows in set (0.00 sec)
select as 用法
mysql> create table t1(主机地址 char(40),用户名 char(20)) select host as 主机地址,user as 用户名 from mysql.user; Query OK, 11 rows affected (0.57 sec) Records: 11 Duplicates: 0 Warnings: 0 mysql> select * from t1; +--------------+-----------+ | 主机地址 | 用户名 | +--------------+-----------+ | % | alex | | % | egon | | % | egon1 | | % | yuanhao | | % | zuo | | % | zuo1 | | 127.0.0.1 | root | | ::1 | root | | localhost | | | localhost | root | | localhost | zuo | +--------------+-----------+ 11 rows in set (0.00 sec)
insert 操作 表格先前存在
mysql> create table t1(id int ,name char(10)); Query OK, 0 rows affected (0.30 sec) mysql> create table t2(id int ,name char(10)); Query OK, 0 rows affected (0.29 sec) mysql> insert t2 values -> (1,'alex'), -> (2,'egon'); Query OK, 2 rows affected (0.29 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> insert t1 values -> (1,'wupeiqi'); Query OK, 1 row affected (0.30 sec) mysql> insert t1 select id,name from t2; Query OK, 2 rows affected (0.32 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from t1; +------+---------+ | id | name | +------+---------+ | 1 | wupeiqi | | 1 | alex | | 2 | egon | +------+---------+ 3 rows in set (0.00 sec)
三 单表查询语法
select [distinct] 字段1，字段2 from tab_name
where
group by
having
order by
limint
步骤
1 from ta_name
2 where
3 group by
4 having
5 select
6 order by
7 limint
1 查询的时候可以对结果进行四则运算。
mysql> select concat('name:',name,' ','年薪',salary*12) from employee; +---------------------------------------------+ | concat('name:',name,' ','年薪',salary*12) | +---------------------------------------------+ | name:egon 年薪87603.96 | | name:alex 年薪12000003.72 | | name:wupeiqi 年薪99600.00 | | name:yuanhao 年薪42000.00 | | name:liwenzhou 年薪25200.00 | | name:jingliyang 年薪108000.00 | | name:jinxin 年薪360000.00 | | name:成龙 年薪120000.00 | | name:歪歪 年薪36001.56 | | name:丫丫 年薪24004.20 | | name:丁丁 年薪12004.44 | | name:星星 年薪36003.48 | | name:格格 年薪48003.96 | | name:张野 年薪120001.56 | | name:程咬金 年薪240000.00 | | name:程咬银 年薪228000.00 | | name:程咬铜 年薪216000.00 | | name:程咬铁 年薪204000.00 | +---------------------------------------------+ 18 rows in set (0.00 sec)
2 concat_ws：concat with separator的缩写，第一个参数是分隔符
mysql> select concat_ws('|',name,age,salary) from employee; +--------------------------------+ | concat_ws('|',name,age,salary) | +--------------------------------+ | egon|18|7300.33 | | alex|78|1000000.31 | | wupeiqi|81|8300.00 | | yuanhao|73|3500.00 | | liwenzhou|28|2100.00 | | jingliyang|18|9000.00 | | jinxin|18|30000.00 | | 成龙|48|10000.00 | | 歪歪|48|3000.13 | | 丫丫|38|2000.35 | | 丁丁|18|1000.37 | | 星星|18|3000.29 | | 格格|28|4000.33 | | 张野|28|10000.13 | | 程咬金|18|20000.00 | | 程咬银|18|19000.00 | | 程咬铜|18|18000.00 | | 程咬铁|18|17000.00 | +--------------------------------+ 18 rows in set (0.00 sec)
3 where子语句
in 的应用，多个 或等于。
mysql> select name,salary from employee where age in(10,20,18,78); +------------+------------+ | name | salary | +------------+------------+ | egon | 7300.33 | | alex | 1000000.31 | | jingliyang | 9000.00 | | jinxin | 30000.00 | | 丁丁 | 1000.37 | | 星星 | 3000.29 | | 程咬金 | 20000.00 | | 程咬银 | 19000.00 | | 程咬铜 | 18000.00 | | 程咬铁 | 17000.00 | +------------+------------+ 10 rows in set (0.29 sec)
like 模糊匹配，% 代表多个字符，_ 代表一个字符。
mysql> select name,salary from employee where name like 'eg%'; +------+---------+ | name | salary | +------+---------+ | egon | 7300.33 | +------+---------+ 1 row in set (0.28 sec) mysql> select name,salary from employee where name like 'eg-'; Empty set (0.00 sec)
4 count（）函数：count()函数里面的参数是列名的的时候,那么会计算这个字段有值项的次数，计算的总行数。
可以看到结果相同，因为行数一样。
mysql> select count(id) from employee; +-----------+ | count(id) | +-----------+ | 18 | +-----------+ 1 row in set (0.27 sec) mysql> select count(post) from employee; +-------------+ | count(post) | +-------------+ | 18 | +-------------+ 1 row in set (0.00 sec)
示例2
mysql> select post,count(id) from employee group by post; +-----------------------------------------+-----------+ | post | count(id) | +-----------------------------------------+-----------+ | operation | 5 | | sale | 5 | | teacher | 7 | | 老男孩驻沙河办事处外交大使 | 1 | +-----------------------------------------+-----------+ 4 rows in set (0.00 sec)
5 group by 分组
可以对一个或多个列队结果进行分组。在分组的基础上可以使用聚合函数。
汉字中以‘每’为关键字，遇到‘每xx’，基本上可以断定xx就是分组的依据。
注意：
分组后，select 只能用分组的字段；
在’set global sql_mode='only_full_group_by'的前提下，select 未分组的字段，会报错
想要查看组内内容，只能借助聚合函数，max，min，avg，sum，count。
聚合函数和分组的字段用，隔开。
PS:聚合函数
SQL基本函数，聚合函数对一组值执行计算，并返回单个值。除了 COUNT 以外，聚合函数都会忽略空值。 聚合函数经常与 SELECT 语句的 GROUP BY 子句一起使用。
聚合函数的性质：
所有聚合函数都具有确定性。任何时候用一组给定的输入值调用它们时，都返回相同的值。
示例：
mysql> select sex,avg(salary) from employee group by sex; +--------+---------------+ | sex | avg(salary) | +--------+---------------+ | male | 110920.077000 | | female | 7250.183750 | +--------+---------------+ 2 rows in set (0.00 sec)
6 having
having字句可以让我们筛选成组后的各种数据，where字句在聚合前先筛选记录，也就是说作用在group by和having字句前。而 having子句在聚合后对组记录进行筛选。
WHERE 子句不能包含聚集函数； 因为试图用聚集函数判断那些行输入给聚集运算是没有意义的。 相反，HAVING 子句一般包含聚集函数。
把 HAVING 加入 SQL 的原因是，WHERE 无法应用于聚合函数。
having通常和group by联合使用。
示例1：
取出员工数大于3的部门
mysql> select post from employee group by post having count(id)>3; +-----------+ | post | +-----------+ | operation | | sale | | teacher | +-----------+ 3 rows in set (0.00 sec)
示例2：
查询各岗位包含的员工个数小于6的岗位名，岗位内包含员工名字，个数。
mysql> select post,group_concat(name),count(id) from employee group by post having count(id)<6; +-----------------------------------------+------------------------------------------------+-----------+ | post | group_concat(name) | count(id) | +-----------------------------------------+------------------------------------------------+-----------+ | operation | 程咬铁,程咬铜,程咬银,程咬金,张野 | 5 | | sale | 格格,星星,丁丁,丫丫,歪歪 | 5 | | 老男孩驻沙河办事处外交大使 | egon | 1 | +-----------------------------------------+------------------------------------------------+-----------+ 3 rows in set (0.00 sec)
7 order by
order by xxx asc；升序 asc 是ascend的缩写
order by xxx desc；降序 desc是descend的缩写
order by id，salary desc；第一顺序按id升序，第二条件按salary降序。
示例：
mysql> select name,salary from employee where salary>5000 order by salary desc; +------------+------------+ | name | salary | +------------+------------+ | alex | 1000000.31 | | jinxin | 30000.00 | | 程咬金 | 20000.00 | | 程咬银 | 19000.00 | | 程咬铜 | 18000.00 | | 程咬铁 | 17000.00 | | 张野 | 10000.13 | | 成龙 | 10000.00 | | jingliyang | 9000.00 | | wupeiqi | 8300.00 | | egon | 7300.33 | +------------+------------+ 11 rows in set (0.00 sec)
8 limit 限制数目
mysql> select name,salary from employee where salary>5000 order by salary desc limit 5; +-----------+------------+ | name | salary | +-----------+------------+ | alex | 1000000.31 | | jinxin | 30000.00 | | 程咬金 | 20000.00 | | 程咬银 | 19000.00 | | 程咬铜 | 18000.00 | +-----------+------------+ 5 rows in set (0.00 sec)
9 利用正则表达式 REGEXP(regular expression)
示例：
mysql> select * from employee where name regexp '^a'; +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+ | 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+ 1 row in set (0.00 sec)
示例2：
mysql> select * from employee where post regexp 'eache'; #echer在post中某个直接中 +----+------------+--------+-----+------------+---------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------------+--------+-----+------------+---------+--------------+------------+--------+-----------+ | 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | | 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | | 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | +----+------------+--------+-----+------------+---------+--------------+------------+--------+-----------+ 7 rows in set (0.00 sec) mysql>
10 distinct 去重
示例：
distinct 用与没用的区别
mysql> select distinct post from employee ; +-----------------------------------------+ | post | +-----------------------------------------+ | 老男孩驻沙河办事处外交大使 | | teacher | | sale | | operation | +-----------------------------------------+ 4 rows in set (0.00 sec) mysql> select post from employee ; +-----------------------------------------+ | post | +-----------------------------------------+ | 老男孩驻沙河办事处外交大使 | | teacher | | teacher | | teacher | | teacher | | teacher | | teacher | | teacher | | sale | | sale | | sale | | sale | | sale | | operation | | operation | | operation | | operation | | operation | +-----------------------------------------+ 18 rows in set (0.00 sec)
11 concat(str1,str2..)
字符串连接函数
引申：
concat_ws(separator,str1,str2....)，concat with separator。
group_concat([distinct]要连接的字段[order by asc/desc][separator ])
手册上说明：该函数返回带有来自一个组的连接的非null值的字符串结果。
mysql> select post,group_concat(name) from employee group by post; +-----------------------------------------+---------------------------------------------------------+ | post | group_concat(name) | +-----------------------------------------+---------------------------------------------------------+ | operation | 程咬铁,程咬铜,程咬银,程咬金,张野 | | sale | 格格,星星,丁丁,丫丫,歪歪 | | teacher | 成龙,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex | | 老男孩驻沙河办事处外交大使 | egon | +-----------------------------------------+---------------------------------------------------------+ 4 rows in set (0.00 sec) mysql> select post from employee group by post; +-----------------------------------------+ | post | +-----------------------------------------+ | operation | | sale | | teacher | | 老男孩驻沙河办事处外交大使 | +-----------------------------------------+ 4 rows in set (0.00 sec) mysql> select post,group_concat(name),count(id) from employee group by post; +-----------------------------------------+---------------------------------------------------------+-----------+ | post | group_concat(name) | count(id) | +-----------------------------------------+---------------------------------------------------------+-----------+ | operation | 程咬铁,程咬铜,程咬银,程咬金,张野 | 5 | | sale | 格格,星星,丁丁,丫丫,歪歪 | 5 | | teacher | 成龙,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex | 7 | | 老男孩驻沙河办事处外交大使 | egon | 1 | +-----------------------------------------+---------------------------------------------------------+-----------+ 4 rows in set (0.00 sec) mysql> select post,group_concat(name),count(id) from employee group by post,age; +-----------------------------------------+-----------------------------------------+-----------+ | post | group_concat(name) | count(id) | +-----------------------------------------+-----------------------------------------+-----------+ | operation | 程咬铁,程咬铜,程咬银,程咬金 | 4 | | operation | 张野 | 1 | | sale | 星星,丁丁 | 2 | | sale | 格格 | 1 | | sale | 丫丫 | 1 | | sale | 歪歪 | 1 | | teacher | jinxin,jingliyang | 2 | | teacher | liwenzhou | 1 | | teacher | 成龙 | 1 | | teacher | yuanhao | 1 | | teacher | alex | 1 | | teacher | wupeiqi | 1 | | 老男孩驻沙河办事处外交大使 | egon | 1 | +-----------------------------------------+-----------------------------------------+-----------+ 13 rows in set (0.00 sec) mysql> select post,age,group_concat(name),count(id) from employee group by post,age; +-----------------------------------------+-----+-----------------------------------------+-----------+ | post | age | group_concat(name) | count(id) | +-----------------------------------------+-----+-----------------------------------------+-----------+ | operation | 18 | 程咬铁,程咬铜,程咬银,程咬金 | 4 | | operation | 28 | 张野 | 1 | | sale | 18 | 星星,丁丁 | 2 | | sale | 28 | 格格 | 1 | | sale | 38 | 丫丫 | 1 | | sale | 48 | 歪歪 | 1 | | teacher | 18 | jinxin,jingliyang | 2 | | teacher | 28 | liwenzhou | 1 | | teacher | 48 | 成龙 | 1 | | teacher | 73 | yuanhao | 1 | | teacher | 78 | alex | 1 | | teacher | 81 | wupeiqi | 1 | | 老男孩驻沙河办事处外交大使 | 18 | egon | 1 | +-----------------------------------------+-----+-----------------------------------------+-----------+ 13 rows in set (0.00 sec)
mysql> select post,age,group_concat(name,age),count(id) from employee group by post,age; +-----------------------------------------+-----+-------------------------------------------------+-----------+ | post | age | group_concat(name,age) | count(id) | +-----------------------------------------+-----+-------------------------------------------------+-----------+ | operation | 18 | 程咬铁18,程咬铜18,程咬银18,程咬金18 | 4 | | operation | 28 | 张野28 | 1 | | sale | 18 | 星星18,丁丁18 | 2 | | sale | 28 | 格格28 | 1 | | sale | 38 | 丫丫38 | 1 | | sale | 48 | 歪歪48 | 1 | | teacher | 18 | jinxin18,jingliyang18 | 2 | | teacher | 28 | liwenzhou28 | 1 | | teacher | 48 | 成龙48 | 1 | | teacher | 73 | yuanhao73 | 1 | | teacher | 78 | alex78 | 1 | | teacher | 81 | wupeiqi81 | 1 | | 老男孩驻沙河办事处外交大使 | 18 | egon18 | 1 |
mysql> select group_concat(name,age),count(id) from employee group by post,age; +-------------------------------------------------+-----------+ | group_concat(name,age) | count(id) | +-------------------------------------------------+-----------+ | 程咬铁18,程咬铜18,程咬银18,程咬金18 | 4 | | 张野28 | 1 | | 星星18,丁丁18 | 2 | | 格格28 | 1 | | 丫丫38 | 1 | | 歪歪48 | 1 | | jinxin18,jingliyang18 | 2 | | liwenzhou28 | 1 | | 成龙48 | 1 | | yuanhao73 | 1 | | alex78 | 1 | | wupeiqi81 | 1 | | egon18 | 1 | +-------------------------------------------------+-----------+ 13 rows in set (0.00 sec)
mysql> select group_concat(name,salary),count(id) from employee group by post,age; +-------------------------------------------------------------------------+-----------+ | group_concat(name,salary) | count(id) | +-------------------------------------------------------------------------+-----------+ | 程咬铁17000.00,程咬铜18000.00,程咬银19000.00,程咬金20000.00 | 4 | | 张野10000.13 | 1 | | 星星3000.29,丁丁1000.37 | 2 | | 格格4000.33 | 1 | | 丫丫2000.35 | 1 | | 歪歪3000.13 | 1 | | jinxin30000.00,jingliyang9000.00 | 2 | | liwenzhou2100.00 | 1 | | 成龙10000.00 | 1 | | yuanhao3500.00 | 1 | | alex1000000.31 | 1 | | wupeiqi8300.00 | 1 | | egon7300.33 | 1 | +-------------------------------------------------------------------------+-----------+ 13 rows in set (0.00 sec)