基本select查询
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 默认的存储引擎:innodb
show engines; 查看有哪些存储引擎
根据作用域的不同,可分为:
global @@sql_mode 全局sql_mode
session@@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
master
root@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
TIMESTAMP
SELECT 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 l
eagueno='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,则结果为true
and
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 300000
select Tbl_Product.ProductId "产品编号",Name "产品名称", sum(Tbl_Order.Qutity) "购买总数",sum(Tbl_Order.Price) "购买总金额" from Tbl_Product,Tbl_Order
where 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
having
count()的用法
root@TENNIS 09:31 mysql>select count(*) from PLAYERS where t
own='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 case
simple 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)