本文共 38144 字，大约阅读时间需要 127 分钟。
1，设置系统变量@@sql_mode，有一些限制规则，日期不能为0，除数不能为0，自增不能从0开始，授权用户密码不能为空
@@sql_mode 一般不改lele@(none) 14:36 mysql>select @@sql_mode;+-------------------------------------------------------------------------------------------------------------------------------------------+| @@sql_mode |+-------------------------------------------------------------------------------------------------------------------------------------------+| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |+-------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
存储引擎：
mysql 默认的存储引擎：innodbshow engines; 查看有哪些存储引擎
根据作用域的不同，可分为：
global @@sql_mode 全局sql_modesession@@sql_mode 当前sql_mode
变量的创建和使用
root@(none) 14:44 mysql>set @sg="wangwang" ;Query OK, 0 rows affected (0.00 sec)root@(none) 14:51 mysql>select @sg;+----------+| @sg |+----------+| wangwang |+----------+1 row in set (0.00 sec)
sql语句的语法规则：
大写字母表示关键字
root@TENNIS 15:17 mysql>show tables;+-------------------+| Tables_in_TENNIS |+-------------------+| COMMITTEE_MEMBERS || MATCHES || PENALTIES || PLAYERS || TEAMS |+-------------------+5 rows in set (0.00 sec)
只取某个表的前三行
root@TENNIS 15:11 mysql>select * from PENALTIES LIMIT 3;+-----------+----------+--------------+--------+| PAYMENTNO | PLAYERNO | PAYMENT_DATE | AMOUNT |+-----------+----------+--------------+--------+| 1 | 6 | 1980-12-08 | 100.00 || 2 | 44 | 1981-05-05 | 75.00 || 3 | 27 | 1983-09-10 | 100.00 |+-----------+----------+--------------+--------+3 rows in set (0.00 sec)
排序 order by，默认升序（asc）,降序（desc）
root@TENNIS 15:15 mysql>SELECT * FROM PENALTIES ORDER BY AMOUNT DESC;+-----------+----------+--------------+--------+| PAYMENTNO | PLAYERNO | PAYMENT_DATE | AMOUNT |+-----------+----------+--------------+--------+| 1 | 6 | 1980-12-08 | 100.00 || 3 | 27 | 1983-09-10 | 100.00 || 2 | 44 | 1981-05-05 | 75.00 || 8 | 27 | 1984-11-12 | 75.00 || 4 | 104 | 1984-12-08 | 50.00 || 7 | 44 | 1982-12-30 | 30.00 || 5 | 44 | 1980-12-08 | 25.00 || 6 | 8 | 1980-12-08 | 25.00 |+-----------+----------+--------------+--------+8 rows in set (0.00 sec)
取罚款前三名
root@TENNIS 15:15 mysql>SELECT * FROM PENALTIES ORDER BY AMOUNT DESC LIMIT 3;+-----------+----------+--------------+--------+| PAYMENTNO | PLAYERNO | PAYMENT_DATE | AMOUNT |+-----------+----------+--------------+--------+| 1 | 6 | 1980-12-08 | 100.00 || 3 | 27 | 1983-09-10 | 100.00 || 2 | 44 | 1981-05-05 | 75.00 |+-----------+----------+--------------+--------+3 rows in set (0.00 sec)
从第2行后面开始取（不包括这一行），取三行
root@TENNIS 15:16 mysql>SELECT * FROM PENALTIES ORDER BY AMOUNT DESC LIMIT 2,3;+-----------+----------+--------------+--------+| PAYMENTNO | PLAYERNO | PAYMENT_DATE | AMOUNT |+-----------+----------+--------------+--------+| 2 | 44 | 1981-05-05 | 75.00 || 8 | 27 | 1984-11-12 | 75.00 || 4 | 104 | 1984-12-08 | 50.00 |+-----------+----------+--------------+--------+3 rows in set (0.00 sec)
select 取出来的默认不会排序
排序可以是单一字段来排，也可以根据多个字段，多个字段排序的时候，先根据一个字段来排，第一个字段相同，再根据后面的字段来排，如果在字段后面跟 desc 则指定该字段降序排列，不跟默认为升序排列。
root@TENNIS 15:27 mysql>SELECT * FROM PENALTIES ORDER BY AMOUNT,PLAYERNO DESC LIMIT 2,3;+-----------+----------+--------------+--------+| PAYMENTNO | PLAYERNO | PAYMENT_DATE | AMOUNT |+-----------+----------+--------------+--------+| 7 | 44 | 1982-12-30 | 30.00 || 4 | 104 | 1984-12-08 | 50.00 || 2 | 44 | 1981-05-05 | 75.00 |+-----------+----------+--------------+--------+3 rows in set (0.00 sec)
sql语句的大小写问题
关键字大小写不敏感库名，表名大小写敏感列名大小写不敏感
列别名
列名 别名 列名 as 别名 如果别名有多个单词组成，中间有空格，需要用“ ”引起来root@TENNIS 15:45 mysql>SELECT playerno p,amount a FROM PENALLTIES ORDER BY AMOUNT,PLAYERNO DESC LIMIT 2,3;+-----+-------+| p | a |+-----+-------+| 44 | 30.00 || 104 | 50.00 || 44 | 75.00 |+-----+-------+3 rows in set (0.00 sec)
查询所有的罚款信息，要求显示球员编号，罚款金额，罚款金额+300
root@TENNIS 15:49 mysql>SELECT playerno,amount,amount+300 FROM PENALTIES;+----------+--------+------------+| playerno | amount | amount+300 |+----------+--------+------------+| 6 | 100.00 | 400.00 || 44 | 75.00 | 375.00 || 27 | 100.00 | 400.00 || 104 | 50.00 | 350.00 || 44 | 25.00 | 325.00 || 8 | 25.00 | 325.00 || 44 | 30.00 | 330.00 || 27 | 75.00 | 375.00 |+----------+--------+------------+8 rows in set (0.00 sec)
给amount+300取别名
root@TENNIS 15:53 mysql>SELECT playerno,amount,amount+300 new_amount FROM PENALTIES;+----------+--------+------------+| playerno | amount | new_amount |+----------+--------+------------+| 6 | 100.00 | 400.00 || 44 | 75.00 | 375.00 || 27 | 100.00 | 400.00 || 104 | 50.00 | 350.00 || 44 | 25.00 | 325.00 || 8 | 25.00 | 325.00 || 44 | 30.00 | 330.00 || 27 | 75.00 | 375.00 |+----------+--------+------------+8 rows in set (0.00 sec)
select表达式遵循运算法则
NULL值
一个列具有NULL值，表示该值是未知的 NULL值不等于空的值或0，空值（有值，但是空的值）和null值（无值，真空）
root@TENNIS 16:04 mysql>select playerno,name,leagueno from PLAYERS;+----------+-----------+----------+| playerno | name | leagueno |+----------+-----------+----------+| 2 | Everett | 2411 || 6 | Parmenter | 8467 || 7 | Wise | NULL || 8 | Newcastle | 2983 || 27 | Collins | 2513 || 39 | Bishop | NULL || 44 | Baker | 1124 || 57 | Brown | 6409 || 83 | Hope | 1608 || 95 | Miller | NULL || 100 | Parmenter | 6524 || 104 | Moorman | 7060 || 112 | Bailey | 1319 |+----------+-----------+----------+13 rows in set (0.00 sec)root@TENNIS 16:03 mysql>select playerno,name,leagueno from PLAYERS where leagueno is not null;+----------+-----------+----------+| playerno | name | leagueno |+----------+-----------+----------+| 2 | Everett | 2411 || 6 | Parmenter | 8467 || 8 | Newcastle | 2983 || 27 | Collins | 2513 || 44 | Baker | 1124 || 57 | Brown | 6409 || 83 | Hope | 1608 || 100 | Parmenter | 6524 || 104 | Moorman | 7060 || 112 | Bailey | 1319 |+----------+-----------+----------+10 rows in set (0.00 sec)
拼接
[root@master ~]# cat /etc/hostname /etc/hostname master masterroot@TENNIS 16:05 mysql>select playerno,name,leagueno from PLAYERS where leagueno is not null;select concat(playerno," ",name) new_name,town from PLAYERS;+----------+-----------+----------+| playerno | name | leagueno |+----------+-----------+----------+| 2 | Everett | 2411 || 6 | Parmenter | 8467 || 8 | Newcastle | 2983 || 27 | Collins | 2513 || 44 | Baker | 1124 || 57 | Brown | 6409 || 83 | Hope | 1608 || 100 | Parmenter | 6524 || 104 | Moorman | 7060 || 112 | Bailey | 1319 |+----------+-----------+----------+10 rows in set (0.00 sec)+---------------+-----------+| new_name | town |+---------------+-----------+| 2 Everett | Stratford || 6 Parmenter | Stratford || 7 Wise | Stratford || 8 Newcastle | Inglewood || 27 Collins | Eltham || 39 Bishop | Stratford || 44 Baker | Inglewood || 57 Brown | Stratford || 83 Hope | Stratford || 95 Miller | Douglas || 100 Parmenter | Stratford || 104 Moorman | Eltham || 112 Bailey | Plymouth |+---------------+-----------+13 rows in set (0.00 sec)
指定分隔符
root@TENNIS 16:10 mysql>select concat_ws(",",playerno,name) new_name,town from PLAYERS; +---------------+-----------+| new_name | town |+---------------+-----------+| 2,Everett | Stratford || 6,Parmenter | Stratford || 7,Wise | Stratford || 8,Newcastle | Inglewood || 27,Collins | Eltham || 39,Bishop | Stratford || 44,Baker | Inglewood || 57,Brown | Stratford || 83,Hope | Stratford || 95,Miller | Douglas || 100,Parmenter | Stratford || 104,Moorman | Eltham || 112,Bailey | Plymouth |+---------------+-----------+13 rows in set (0.00 sec) 常量/字面量
日期格式：建议用 - 隔开 eg:2020-10-27三大数据类型：
数值型 字符串类型 日期时间类型 TIME DATE DATETIME TIMESTAMPSELECT NOW() ----当前时间
root@TENNIS 16:13 mysql>SELECT NOW();+---------------------+| NOW() |+---------------------+| 2020-10-27 16:19:31 |+---------------------+1 row in set (0.00 sec)
root@TENNIS 16:20 mysql>CREATE TABLE TIMESTAMP_TABLE(COLUMN1 TIMESTAMP);Query OK, 0 rows affected (0.05 sec)root@TENNIS 16:21 mysql>INSERT INTO TIMESTAMP_TABLE VALUES(NOW());Query OK, 1 row affected (0.01 sec)root@TENNIS 16:21 mysql>select * from TIMESTAMP_TABLE;+---------------------+| COLUMN1 |+---------------------+| 2020-10-27 16:21:41 |+---------------------+1 row in set (0.00 sec)
时区查看
root@TENNIS 16:22 mysql>select @@time_zone,@@system_time_zone;+-------------+--------------------+| @@time_zone | @@system_time_zone |+-------------+--------------------+| SYSTEM | CST |+-------------+--------------------+1 row in set (0.01 sec)
时间间隔：interval
root@TENNIS 16:31 mysql>select now() + interval 3 hour;+-------------------------+| now() + interval 3 hour |+-------------------------+| 2020-10-27 19:31:37 |+-------------------------+1 row in set (0.04 sec)
去重：distinct
root@TENNIS 16:33 mysql>select town from PLAYERS;+-----------+| town |+-----------+| Stratford || Stratford || Stratford || Inglewood || Eltham || Stratford || Inglewood || Stratford || Stratford || Douglas || Stratford || Eltham || Plymouth |+-----------+13 rows in set (0.00 sec)root@TENNIS 16:33 mysql>select distinct town from PLAYERS;+-----------+| town |+-----------+| Stratford || Inglewood || Eltham || Douglas || Plymouth |+-----------+5 rows in set (0.00 sec)
多列去重
root@TENNIS 16:34 mysql>select street,town from PLAYERS;+----------------+-----------+| street | town |+----------------+-----------+| Stoney Road | Stratford || Haseltine Lane | Stratford || Edgecombe Way | Stratford || Station Road | Inglewood || Long Drive | Eltham || Eaton Square | Stratford || Lewis Street | Inglewood || Edgecombe Way | Stratford || Magdalene Road | Stratford || High Street | Douglas || Haseltine Lane | Stratford || Stout Street | Eltham || Vixen Road | Plymouth |+----------------+-----------+13 rows in set (0.00 sec)root@TENNIS 16:36 mysql>select distinct street,town from PLAYYERS;+----------------+-----------+| street | town |+----------------+-----------+| Stoney Road | Stratford || Haseltine Lane | Stratford || Edgecombe Way | Stratford || Station Road | Inglewood || Long Drive | Eltham || Eaton Square | Stratford || Lewis Street | Inglewood || Magdalene Road | Stratford || High Street | Douglas || Stout Street | Eltham || Vixen Road | Plymouth |+----------------+-----------+11 rows in set (0.00 sec)
where 字句
where子句一般跟在from后面root@TENNIS 17:08 mysql>select name,town from PLAYERS;+-----------+-----------+| name | town |+-----------+-----------+| Everett | Stratford || Parmenter | Stratford || Wise | Stratford || Newcastle | Inglewood || Collins | Eltham || Bishop | Stratford || Baker | Inglewood || Brown | Stratford || Hope | Stratford || Miller | Douglas || Parmenter | Stratford || Moorman | Eltham || Bailey | Plymouth |+-----------+-----------+13 rows in set (0.00 sec)root@TENNIS 17:05 mysql>select name from PLAYERS where town='Stratford';+-----------+| name |+-----------+| Everett || Parmenter || Wise || Bishop || Brown || Hope || Parmenter |+-----------+7 rows in set (0.00 sec)
单条件限制和多条件限制
root@TENNIS 17:09 mysql>select name from PLAYERS where sex=';F';+-----------+| name |+-----------+| Newcastle || Collins || Moorman || Bailey |+-----------+4 rows in set (0.00 sec)#两个条件都要满足root@TENNIS 17:10 mysql>select name from PLAYERS where sex='F' and town='Inglewood';+-----------+| name |+-----------+| Newcastle |+-----------+1 row in set (0.00 sec)#只要满足其中一个root@TENNIS 17:11 mysql>select name from PLAYERS where sex='F' or town='Inglewood';+-----------+| name |+-----------+| Newcastle || Collins || Baker || Moorman || Bailey |+-----------+5 rows in set (0.00 sec)
字符集–校对规则
查看有哪些字符集
root@TENNIS 17:12 mysql>show character set;+----------+---------------------------------+---------------------+--------+| Charset | Description | Default collation | Maxlen |+----------+---------------------------------+---------------------+--------+| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 || dec8 | DEC West European | dec8_swedish_ci | 1 || cp850 | DOS West European | cp850_general_ci | 1 || hp8 | HP West European | hp8_english_ci | 1 || koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 || latin1 | cp1252 West European | latin1_swedish
查看本机使用的字符集
root@TENNIS 17:13 mysql>show variables like "%char%";+--------------------------+-------------------------------------+| Variable_name | Value |+--------------------------+-------------------------------------+| character_set_client | utf8 || character_set_connection | utf8 || character_set_database | utf8 || character_set_filesystem | binary || character_set_results | utf8 || character_set_server | utf8 || character_set_system | utf8 || character_sets_dir | /usr/local/sc_mysql/share/charsets/ |+--------------------------+-------------------------------------+8 rows in set (0.05 sec)
查看校对规则
root@TENNIS 17:15 mysql>show variables like "%collation%";+----------------------+-----------------+| Variable_name | Value |+----------------------+-----------------+| collation_connection | utf8_general_ci || collation_database | utf8_general_ci || collation_server | utf8_general_ci |+----------------------+-----------------+3 rows in set (0.00 sec)
查看某个库使用的是什么字符集
root@TENNIS 17:16 mysql>show create database TENNIS;+----------+-----------------------------------------------------------------+| Database | Create Database |+----------+-----------------------------------------------------------------+| TENNIS | CREATE DATABASE `TENNIS` /*!40100 DEFAULT CHARACTER SET utf8 */ |+----------+-----------------------------------------------------------------+1 row in set (0.00 sec)
比较操作符
root@TENNIS 17:18 mysql>select playerno from PLAYERS where leagueno='7060';+----------+| playerno |+----------+| 104 |+----------+1 row in set (0.00 sec)
找出赢两场输三场的比赛编号
root@TENNIS 17:22 mysql>select matchno from MATCHES where won=2 and lost=3;+---------+| matchno |+---------+| 11 |+---------+1 row in set (0.00 sec)
找出1962-1964年之间出生的球员的编号，出生日期
root@TENNIS 17:32 mysql>select playerno,birth_date from PLAYERS where year(birth_date) between 1962 and 1964;+----------+------------+| playerno | birth_date |+----------+------------+| 6 | 1964-06-25 || 7 | 1963-05-11 || 8 | 1962-07-08 || 27 | 1964-12-28 || 44 | 1963-01-09 || 95 | 1963-05-14 || 100 | 1963-02-28 || 112 | 1963-10-01 |+----------+------------+8 rows in set (0.00 sec)
IN 操作符
root@TENNIS 19:18 mysql>select playerno,year(birth_date)from PLAYERS where year(birth_date) in (1962,1963,1970);+----------+------------------+| playerno | year(birth_date) |+----------+------------------+| 7 | 1963 || 8 | 1962 || 44 | 1963 || 95 | 1963 || 100 | 1963 || 104 | 1970 || 112 | 1963 |+----------+------------------+7 rows in set (0.00 sec)
like 操作符
%：0个或任意个字符
_: 只表示一个任意字符root@TENNIS 20:36 mysql>select name from PLAYERS where year(birth_date) not in (1963,1964) and name like "%t%";+-----------+| name |+-----------+| Everett || Newcastle |+-----------+2 rows in set (0.00 sec)
regexp 操作符----正则
root@TENNIS 19:18 mysql>select playerno,name from PLAYERS where name regexp '^ba';+----------+--------+| playerno | name |+----------+--------+| 44 | Baker || 112 | Bailey |+----------+--------+2 rows in set (0.00 sec)
逻辑操作符
and or not xor :逻辑异或 当一个条件为true，而另一个条件为false，则结果为trueand
root@TENNIS 19:27 mysql>select playerno,name,sex,birth_date from PLAYERS where birth_date >'1962-12-31' and sex='F';+----------+---------+-----+------------+| playerno | name | sex | birth_date |+----------+---------+-----+------------+| 27 | Collins | F | 1964-12-28 || 104 | Moorman | F | 1970-05-10 || 112 | Bailey | F | 1963-10-01 |+----------+---------+-----+------------+3 rows in set (0.00 sec)
not
root@TENNIS 19:28 mysql>select playerno,name,sex,birth_date from PLAYERS where not town='Stratford';+----------+-----------+-----+------------+| playerno | name | sex | birth_date |+----------+-----------+-----+------------+| 8 | Newcastle | F | 1962-07-08 || 27 | Collins | F | 1964-12-28 || 44 | Baker | M | 1963-01-09 || 95 | Miller | M | 1963-05-14 || 104 | Moorman | F | 1970-05-10 || 112 | Bailey | F | 1963-10-01 |+----------+-----------+-----+------------+6 rows in set (0.00 sec)
小括号 —可以改变运算顺序
xor的用法：
root@TENNIS 19:36 mysql>select playerno,name,birth_date from PLAYERS where town='Stratford' xor (year(birth_date)=1963 andd sex='M');+----------+-----------+------------+| playerno | name | birth_date |+----------+-----------+------------+| 2 | Everett | 1948-09-01 || 6 | Parmenter | 1964-06-25 || 39 | Bishop | 1956-10-29 || 44 | Baker | 1963-01-09 || 57 | Brown | 1971-08-17 || 83 | Hope | 1956-11-11 || 95 | Miller | 1963-05-14 |+----------+-----------+------------+7 rows in set (0.00 sec)
order by -----排序，默认升序，desc ：降序
group by -----分组 须与聚合函数联用,如果@@sql_mode 里有only_full_group_by ，不与聚合函数联用的话就会报错 聚合函数：sum(),avg(),count()root@TENNIS 19:37 mysql>select playerno,sum(amount) from PENALTIES group by(playerno) order by sum(amount);+----------+-------------+| playerno | sum(amount) |+----------+-------------+| 8 | 25.00 || 104 | 50.00 || 6 | 100.00 || 44 | 130.00 || 27 | 175.00 |+----------+-------------+5 rows in set (0.00 sec)
set @@sql_mode=… ,only_full_group_by
select
where order by 一般放在最后典型例题
Tbl_Product(ProductId , Name, Price Qutity ,Status（1001–已上架 1002–已下架）, CreateTime) Tbl_Order(OrderId ,ProductId , Price Qutity , OrderTime)1,用sql语句，查找出2017年间创建，上架状态，且库存数量低于100的产品
select * from Tbl_Product where year(CreateTime)=2007 and Status=1001 and Qutity<100;
2，用sql语句统计各产品历史购买总数，及总金额，显示要求下:
产品编号 产品名称 购买总数 购买总金额
p0001 锤子 2000 300000select Tbl_Product.ProductId "产品编号",Name "产品名称", sum(Tbl_Order.Qutity) "购买总数"，sum(Tbl_Order.Price) "购买总金额" from Tbl_Product,Tbl_Orderwhere Tbl_Product.ProductId=Tbl_Order.ProductId group by Tbl_Order.ProductId
3，用sql语句找出那些产品在2016年从未被购买过
难点：订单表里并非全部的产品，产品表里的产品可能没有出现在订单表里
select ProductId from Tbl_Product where ProductId not in (select ProductId from Tbl_Order where year(OrderTime)=2016)
4，用sql语句，找出名称带有锤子字样，且销量前10的商品，并按销量倒序排列
select Name,Tbl_Order.Qutity from Tbl_Product,Tbl_Order where Tbl_Product.ProductId=Tbl_Order.ProductId and Name like "%锤子%" order by Tbl_Order.Qutity desc limit 10
组函数:聚集函数（aggregation function）
常见的聚合函数： avg() --求平均值 count（）-- 统计数量 sum() — group by havingcount()的用法
root@TENNIS 09:31 mysql>select count(*) from PLAYERS where town='Stratford';+----------+| count(*) |+----------+| 7 |+----------+1 row in set (0.00 sec)
count 与 distinct 联用
root@TENNIS 10:43 mysql>select town from PLAYERS; +-----------+| town |+-----------+| Stratford || Stratford || Stratford || Inglewood || Eltham || Stratford || Inglewood || Stratford || Stratford || Douglas || Stratford || Eltham || Plymouth |+-----------+13 rows in set (0.00 sec)root@TENNIS 10:44 mysql>select distinct town from PLAYERS;+-----------+| town |+-----------+| Stratford || Inglewood || Eltham || Douglas || Plymouth |+-----------+5 rows in set (0.00 sec)root@TENNIS 10:44 mysql>select count(distinct town) from PLAYYERS;+----------------------+| count(distinct town) |+----------------------+| 5 |+----------------------+1 row in set (0.00 sec)
root@TENNIS 10:48 mysql>select count(distinct year(birth_date)),count(distinct sex) from PLAYERS;+----------------------------------+---------------------+| count(distinct year(birth_date)) | count(distinct sex) |+----------------------------------+---------------------+| 7 | 2 |+----------------------------------+---------------------+1 row in set (0.00 sec)
max()—最大值 min() —最小值
root@TENNIS 10:56 mysql>select max(amount),min(amount) from PENALTIES;+-------------+-------------+| max(amount) | min(amount) |+-------------+-------------+| 100.00 | 25.00 |+-------------+-------------+1 row in set (0.00 sec)
统计每个城市有多少球员
root@TENNIS 10:57 mysql>select town,count(*) from PLAYERS group by town;+-----------+----------+| town | count(*) |+-----------+----------+| Douglas | 1 || Eltham | 2 || Inglewood | 2 || Plymouth | 1 || Stratford | 7 |+-----------+----------+5 rows in set (0.00 sec)
统计每个球队参加了多少比赛，赢了多少场
root@TENNIS 11:07 mysql>select teamno,count(matchno),sum(won)) from MATCHES group by teamno;+--------+----------------+----------+| teamno | count(matchno) | sum(won) |+--------+----------------+----------+| 1 | 7 | 13 || 2 | 5 | 9 |+--------+----------------+----------+2 rows in set (0.00 sec)
统计每个队的每个球员赢的总场数
root@TENNIS 11:09 mysql>select teamno,playerno,sum(won) from MATCHES group by teamno,playerno;+--------+----------+----------+| teamno | playerno | sum(won) |+--------+----------+----------+| 1 | 2 | 1 || 1 | 6 | 6 || 1 | 8 | 0 || 1 | 44 | 3 || 1 | 57 | 3 || 1 | 83 | 0 || 2 | 8 | 0 || 2 | 27 | 3 || 2 | 104 | 3 || 2 | 112 | 3 |+--------+----------+----------+10 rows in set (0.00 sec)
统计每个球员每年累计罚款多少钱？并统计罚款次数
root@TENNIS 11:17 mysql>select playerno,year(payment_date) year,sum(amount),count(*) from PENALTIES group by playerno,yearr(payment_date);+----------+------+-------------+----------+| playerno | year | sum(amount) | count(*) |+----------+------+-------------+----------+| 6 | 1980 | 100.00 | 1 || 8 | 1980 | 25.00 | 1 || 27 | 1983 | 100.00 | 1 || 27 | 1984 | 75.00 | 1 || 44 | 1980 | 25.00 | 1 || 44 | 1981 | 75.00 | 1 || 44 | 1982 | 30.00 | 1 || 104 | 1984 | 50.00 | 1 |+----------+------+-------------+----------+8 rows in set (0.00 sec)
对于penalties表中的每一年，得到支付罚款的总次数
root@TENNIS 11:21 mysql>select year(payment_date) year,count(*) from PENALTIES group by year(payment_date);+------+----------+| year | count(*) |+------+----------+| 1980 | 3 || 1981 | 1 || 1982 | 1 || 1983 | 1 || 1984 | 2 |+------+----------+5 rows in set (0.00 sec)
对于penalties表中的每一年，得到支付罚款的总次数，统计出罚款次数大于等于2的
root@TENNIS 11:21 mysql>select year(payment_date) year,count(*) from PENALTIES group by year(payment_date) having count(*)>=2;+------+----------+| year | count(*) |+------+----------+| 1980 | 3 || 1984 | 2 |+------+----------+2 rows in set (0.00 sec)
延伸
root@TENNIS 11:25 mysql>select year(payment_date) year,count(*) from PENALTIES group by year(payment_date) having count(*)>=2 order by count(*);+------+----------+| year | count(*) |+------+----------+| 1984 | 2 || 1980 | 3 |+------+----------+2 rows in set (0.00 sec)root@TENNIS 11:26 mysql>select year(payment_date) year,count(*) from PENALTIES group by year(payment_date) having count(*)>=2 order by count(*) desc;+------+----------+| year | count(*) |+------+----------+| 1980 | 3 || 1984 | 2 |+------+----------+2 rows in set (0.00 sec)
group_concat()的用法–将多个值放在一行显示
root@TENNIS 11:26 mysql>select teamno,group_concat(playerno)from MATCHES group by teamno;+--------+------------------------+| teamno | group_concat(playerno) |+--------+------------------------+| 1 | 6,6,44,83,2,57,8 || 2 | 27,104,112,112,8 |+--------+------------------------+2 rows in set (0.00 sec)
查询
root@TENNIS 11:45 mysql>select name from student group by name having min(fenshu)>80;+--------+| name |+--------+| 王五 |+--------+1 row in set (0.00 sec)root@TENNIS 11:45 mysql>select * from student;+--------+---------+--------+| name | kecheng | fenshu |+--------+---------+--------+| 张三 | 语文 | 81 || 张三 | 数学 | 75 || 李四 | 数学 | 90 || 王五 | 数学 | 100 || 王五 | 语文 | 81 || 王五 | 英语 | 90 || 李四 | 语文 | 76 |+--------+---------+--------+7 rows in set (0.00 sec)
查询每门课程不及格数大于2的课程信息
root@TENNIS 14:06 mysql>select t.kecheng from (select kecheng,fenshu from student where fenshu<60) as t group by kecheng having count(fenshu)>=2;+---------+| kecheng |+---------+| 英语 || 语文 |+---------+2 rows in set (0.00 sec)root@TENNIS 14:06 mysql>select * from student;+--------+---------+--------+| name | kecheng | fenshu |+--------+---------+--------+| 张三 | 语文 | 81 || 张三 | 数学 | 75 || 李四 | 数学 | 90 || 王五 | 数学 | 100 || 王五 | 语文 | 81 || 王五 | 英语 | 90 || 李四 | 语文 | 76 || 李四 | 英语 | 56 || 赵六 | 英语 | 46 || 赵六 | 语文 | 55 || 赵六 | 数学 | 49 || 钱七 | 语文 | 48 |+--------+---------+--------+12 rows in set (0.00 sec)
查询每科成绩最好的学生及成绩信息（有问题）
root@TENNIS 14:17 mysql>select name,kecheng,fenshu from studeent where fenshu in(select max(fenshu) from student group by kecheng);+--------+---------+--------+| name | kecheng | fenshu |+--------+---------+--------+| 李四 | 数学 | 90 || 王五 | 数学 | 100 || 王五 | 英语 | 90 || 钱八 | 语文 | 98 |+--------+---------+--------+4 rows in set (0.00 sec)
单行函数
upper() lower() ----作用于列
root@TENNIS 14:42 mysql>select name from PLAYERS;+-----------+| name |+-----------+| Everett || Parmenter || Wise || Newcastle || Collins || Bishop || Baker || Brown || Hope || Miller || Parmenter || Moorman || Bailey |+-----------+13 rows in set (0.00 sec)root@TENNIS 14:43 mysql>select upper(name) from PLAYERSS;+-------------+| upper(name) |+-------------+| EVERETT || PARMENTER || WISE || NEWCASTLE || COLLINS || BISHOP || BAKER || BROWN || HOPE || MILLER || PARMENTER || MOORMAN || BAILEY |+-------------+13 rows in set (0.00 sec)
concat()
concat_ws()root@TENNIS 14:45 mysql>select concat(playerno,name) frrom PLAYERS;+-----------------------+| concat(playerno,name) |+-----------------------+| 2Everett || 6Parmenter || 7Wise || 8Newcastle || 27Collins || 39Bishop || 44Baker || 57Brown || 83Hope || 95Miller || 100Parmenter || 104Moorman || 112Bailey |+-----------------------+13 rows in set (0.00 sec)root@TENNIS 14:45 mysql>select concat_ws(',',playerno,nname) from PLAYERS;+------------------------------+| concat_ws(',',playerno,name) |+------------------------------+| 2,Everett || 6,Parmenter || 7,Wise || 8,Newcastle || 27,Collins || 39,Bishop || 44,Baker || 57,Brown || 83,Hope || 95,Miller || 100,Parmenter || 104,Moorman || 112,Bailey |+------------------------------+13 rows in set (0.00 sec) substring()----字符串切片
root@TENNIS 14:45 mysql>select substring(name,1,3) fromm PLAYERS;+---------------------+| substring(name,1,3) |+---------------------+| Eve || Par || Wis || New || Col || Bis || Bak || Bro || Hop || Mil || Par || Moo || Bai |+---------------------+13 rows in set (0.00 sec)
lenth() ----计算字符串长度
root@TENNIS 14:48 mysql>select length(name) from PLAYERRS;+--------------+| length(name) |+--------------+| 7 || 9 || 4 || 9 || 7 || 6 || 5 || 5 || 4 || 6 || 9 || 7 || 6 |+--------------+13 rows in set (0.00 sec)
lpad() -----左填充
rpad() ------右填充root@TENNIS 14:48 mysql>select lpad(name,10,"#") from PLAYERS;+-------------------+| lpad(name,10,"#") |+-------------------+| ###Everett || #Parmenter || ######Wise || #Newcastle || ###Collins || ####Bishop || #####Baker || #####Brown || ######Hope || ####Miller || #Parmenter || ###Moorman || ####Bailey |+-------------------+13 rows in set (0.00 sec)
root@TENNIS 14:52 mysql>select rpad(name,10,"#") from PLAYERS;+-------------------+| rpad(name,10,"#") |+-------------------+| Everett### || Parmenter# || Wise###### || Newcastle# || Collins### || Bishop#### || Baker##### || Brown##### || Hope###### || Miller#### || Parmenter# || Moorman### || Bailey#### |+-------------------+13 rows in set (0.00 sec)
trim() —默认去掉空格
repeat() —重复 reverse（）----反转root@TENNIS 14:52 mysql>select reverse(name) from PLAYERS;+---------------+| reverse(name) |+---------------+| tterevE || retnemraP || esiW || eltsacweN || snilloC || pohsiB || rekaB || nworB || epoH || relliM || retnemraP || namrooM || yeliaB |+---------------+13 rows in set (0.00 sec)
char() ----- 数字对应–ascii码
root@TENNIS 14:58 mysql>select char(65) -> ;+----------+| char(65) |+----------+| A |+----------+1 row in set (0.00 sec)root@TENNIS 14:58 mysql>select char(165) -> ;+-----------+| char(165) |+-----------+| ¥ |+-----------+1 row in set (0.00 sec)
round()—四舍五入
root@TENNIS 15:01 mysql>select round(1.999,1);+----------------+| round(1.999,1) |+----------------+| 2.0 |+----------------+1 row in set (0.00 sec)
truncate() ----截取
root@TENNIS 14:58 mysql>select truncate(1.999,1);+-------------------+| truncate(1.999,1) |+-------------------+| 1.9 |+-------------------+1 row in set (0.00 sec)
ceil（）—返回不小于x的最小整数
floor() ----返回不大于x的最大整数rand()的用法
#默认产生0-1之间的随机数root@TENNIS 15:09 mysql>select rand();+--------------------+| rand() |+--------------------+| 0.3464466951288718 |+--------------------+1 row in set (0.00 sec)#产生1-100之间的随机数root@TENNIS 15:02 mysql>select rand()*100;+------------------+| rand()*100 |+------------------+| 9.14398050787298 |+------------------+1 row in set (0.00 sec)产生1-100之间的随机整数root@TENNIS 15:05 mysql>select truncate(rand()*100,0); +------------------------+| truncate(rand()*100,0) |+------------------------+| 88 |+------------------------+1 row in set (0.00 sec)#root@TENNIS 15:06 mysql>select rand()*10+20;+--------------------+| rand()*10+20 |+--------------------+| 21.759564626738022 |+--------------------+1 row in set (0.00 sec)root@TENNIS 15:07 mysql>select rand()*18+17;+-------------------+| rand()*18+17 |+-------------------+| 23.74753778869988 |+-------------------+1 row in set (0.00 sec)
now()
date()root@TENNIS 15:10 mysql>select now(),date(now());+---------------------+-------------+| now() | date(now()) |+---------------------+-------------+| 2020-10-29 15:12:11 | 2020-10-29 |+---------------------+-------------+1 row in set (0.00 sec)
date_add()
root@TENNIS 15:15 mysql>select date_add(now(),interval 20 day);+---------------------------------+| date_add(now(),interval 20 day) |+---------------------------------+| 2020-11-18 15:15:49 |+---------------------------------+1 row in set (0.00 sec)
root@TENNIS 15:15 mysql>select date_add(now(),interval 2 month);+----------------------------------+| date_add(now(),interval 2 month) |+----------------------------------+| 2020-12-29 15:16:27 |+----------------------------------+1 row in set (0.00 sec)
date_diff(expr1,expr2)----两个时间相差多少小时
root@TENNIS 15:18 mysql>select datediff('2020-12-3','20020-12-5') -> ;+-----------------------------------+| datediff('2020-12-3','2020-12-5') |+-----------------------------------+| -2 |+-----------------------------------+1 row in set (0.00 sec)root@TENNIS 15:19 mysql>select datediff('2020-12-3','2020-12-1');+-----------------------------------+| datediff('2020-12-3','2020-12-1') |+-----------------------------------+| 2 |+-----------------------------------+1 row in set (0.00 sec) timestampdiff()
root@TENNIS 15:24 mysql>select timestampdiff(year,(select birth_date from PLAYERS where name='Wise'),(select birth_date from PLAYERS where name='Bishop')) diff;+------+| diff |+------+| -6 |+------+1 row in set (0.00 sec)
查找出5月份出生的所有球员的名字和性别，并统计有多少人
root@TENNIS 15:34 mysql>select count(*) from (select name,sex,birth_date from PLAYERS where month(birth_date)=5) as t;+----------+| count(*) |+----------+| 3 |+----------+1 row in set (0.00 sec)
类型转换函数和case表达式
ifnull (expr1,expr2) —如果expr1不为null，就直接返回，expr2
root@TENNIS 19:24 mysql>select ifnull(1,0);+-------------+| ifnull(1,0) |+-------------+| 1 |+-------------+1 row in set (0.00 sec)root@TENNIS 19:25 mysql>select ifnull(null,10);+-----------------+| ifnull(null,10) |+-----------------+| 10 |+-----------------+1 row in set (0.00 sec)
nullif(expr1,expr2) —如果两个参数相等，就返回null，否则返回expr1
root@TENNIS 19:25 mysql>select nullif(1,1);+-------------+| nullif(1,1) |+-------------+| NULL |+-------------+1 row in set (0.00 sec)root@TENNIS 19:26 mysql>select nullif(1,2);+-------------+| nullif(1,2) |+-------------+| 1 |+-------------+1 row in set (0.00 sec)
if(1,2,3) 1–true -》2 ，否则-》3
root@TENNIS 15:37 mysql>select if(sex='M','男','女') from PLAYERS;+-------------------------+| if(sex='M','男','女') |+-------------------------+| 男 || 男 || 男 || 女 || 女 || 男 || 男 || 男 || 男 || 男 || 男 || 女 || 女 |+-------------------------+13 rows in set (0.00 sec)
CASE表达式
分为 simple case 和 searched casesimple case的用法：
root@TENNIS 16:20 mysql>select playerno,case sex when 'F' then 'female' else 'male' end sex,name from PLAYERS where joined>1980;+----------+--------+---------+| playerno | sex | name |+----------+--------+---------+| 7 | male | Wise || 27 | female | Collins || 57 | male | Brown || 83 | male | Hope || 104 | female | Moorman || 112 | female | Bailey |+----------+--------+---------+6 rows in set (0.00 sec)
town是Stratford 的，输出湖南人，Eltham 输出山东人，其他输出广东人
root@TENNIS 16:29 mysql>select name,sex, case town whenn 'Stratford' then '湖南人' when 'Eltham' then '山东人' else '广东人' end new_town from PLAYERS;+-----------+-----+-----------+| name | sex | new_town |+-----------+-----+-----------+| Everett | M | 湖南人 || Parmenter | M | 湖南人 || Wise | M | 湖南人 || Newcastle | F | 广东人 || Collins | F | 山东人 || Bishop | M | 湖南人 || Baker | M | 广东人 || Brown | M | 湖南人 || Hope | M | 湖南人 || Miller | M | 广东人 || Parmenter | M | 湖南人 || Moorman | F | 山东人 || Bailey | F | 广东人 |+-----------+-----+-----------+13 rows in set (0.01 sec)
searched case 的用法
root@TENNIS 16:33 mysql>select playerno,joined, case when joined<1980 then '青年组' when joined<1983 then '少年组' else '儿童组' end as age_group from PLAYERS order by joined;+----------+--------+-----------+| playerno | joined | age_group |+----------+--------+-----------+| 95 | 1972 | 青年组 || 2 | 1975 | 青年组 || 6 | 1977 | 青年组 || 100 | 1979 | 青年组 || 8 | 1980 | 少年组 || 39 | 1980 | 少年组 || 44 | 1980 | 少年组 || 7 | 1981 | 少年组 || 83 | 1982 | 少年组 || 27 | 1983 | 儿童组 || 104 | 1984 | 儿童组 || 112 | 1984 | 儿童组 || 57 | 1985 | 儿童组 |+----------+--------+-----------+13 rows in set (0.00 sec)
转载地址：http://nqlzi.baihongyu.com/