单表查询
mysql> select 2*7;
+-----+
| 2*7 |
+-----+
| 14 |
+-----+
mysql> select '13412' as id;
+-------+
| id |
+-------+
| 13412 |
+-------+
from
后面跟哪一张表
mysql> insert into t_1 values(1, 'leo');
mysql> insert into t_1 values(2, 'jerry');
mysql> insert into t_2 values(98, 93);
mysql> insert into t_2 values(87, 99);
mysql> select *from t_1;
+------+-------+
| id | name |
+------+-------+
| 1 | leo |
| 2 | jerry |
+------+-------+
mysql> select *from t_2;
+--------+--------+
| score1 | score2 |
+--------+--------+
| 98 | 93 |
| 87 | 99 |
+--------+--------+
#笛卡尔积
mysql> select * from t_1,t_2;
+------+-------+--------+--------+
| id | name | score1 | score2 |
+------+-------+--------+--------+
| 1 | leo | 98 | 93 |
| 2 | jerry | 98 | 93 |
| 1 | leo | 87 | 99 |
| 2 | jerry | 87 | 99 |
+------+-------+--------+--------+
dual
select 2*7 as res from dual;
+-----+
| res |
+-----+
| 14 |
+-----+
mysql> select 2*7 from dual;
+-----+
| 2*7 |
+-----+
| 14 |
+-----+
概念详细看https://blog.csdn.net/lpioneer/article/details/5940366
where
<，>，=，>=，<=，!=， not，and，or，not
mysql> create table t_3(
-> id int,
-> age int
-> );
mysql> insert into t_3 values(1,19);
mysql> insert into t_3 values(2,15);
mysql> insert into t_3 values(3,18);
mysql> select * from t_3;
+------+------+
| id | age |
+------+------+
| 1 | 19 |
| 2 | 15 |
| 3 | 18 |
+------+------+
mysql> select * from t_3 where age >=18;
+------+------+
| id | age |
+------+------+
| 1 | 19 |
| 3 | 18 |
+------+------+
mysql> select * from t_3 where age <18;
+------+------+
| id | age |
+------+------+
| 2 | 15 |
+------+------+
mysql> select * from t_3 where age <=18;
+------+------+
| id | age |
+------+------+
| 2 | 15 |
| 3 | 18 |
+------+------+
mysql> select * from t_3 where age =18 or age=15;
+------+------+
| id | age |
+------+------+
| 2 | 15 |
| 3 | 18 |
+------+------+
in
mysql> select * from t_3 where age in(18,19);
+------+------+
| id | age |
+------+------+
| 1 | 19 |
| 3 | 18 |
+------+------+
#not in
mysql> select * from t_3 where age not in(18,19);
+------+------+
| id | age |
+------+------+
| 2 | 15 |
+------+------+
between...and
mysql> insert into t_3 values (4,20);
mysql> insert into t_3 values(5,22);
mysql> select * from t_3;
+------+------+
| id | age |
+------+------+
| 1 | 19 |
| 2 | 15 |
| 3 | 18 |
| 4 | 20 |
| 5 | 22 |
+------+------+
mysql> select * from t_3 where age between 15 and 20;
#闭区间
+------+------+
| id | age |
+------+------+
| 1 | 19 |
| 2 | 15 |
| 3 | 18 |
| 4 | 20 |
+------+------+
#not between ...and
mysql> select * from t_3 where age not between 15 and 20;
+------+------+
| id | age |
+------+------+
| 5 | 22 |
+------+------+
is null
mysql> insert into t_3 values(6,NULL);
mysql> select * from t_3 where age is null;
+------+------+
| id | age |
+------+------+
| 6 | NULL |
+------+------+
#not null
mysql> select * from t_3 where age is not null;
+------+------+
| id | age |
+------+------+
| 1 | 19 |
| 2 | 15 |
| 3 | 18 |
| 4 | 20 |
| 5 | 22 |
+------+------+
聚合函数
MySQ-L自带的函数，用来做统计
mysql> create table score(
-> id int,
-> chinese int,
-> english int,
-> math int
-> );
mysql> desc score;
+---------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| chinese | int(11) | YES | | NULL | |
| english | int(11) | YES | | NULL | |
| math | int(11) | YES | | NULL | |
+---------+---------+------+-----+---------+-------+
mysql> insert into score values(1,98,99,100);
mysql> insert into score values(2,93,94,80);
mysql> select sum(chinese) from score;
+--------------+
| sum(chinese) |
+--------------+
| 191 |
+--------------+
#98+93
mysql> select avg(math) from score;
+-----------+
| avg(math) |
+-----------+
| 90.0000 |
+-----------+
mysql> select max(chinese) from score;
+--------------+
| max(chinese) |
+--------------+
| 98 |
+--------------+
mysql> select min(chinese) from score;
+--------------+
| min(chinese) |
+--------------+
| 93 |
+--------------+
#统计次数
mysql> select count(chinese) from score;
+----------------+
| count(chinese) |
+----------------+
| 2 |
+----------------+
#count(*)与count(1)的优缺点
客户端的使用
navicat安装
like模糊查询
mysql> select * from student;
+------+-------+--------+------+-------------+
| id | name | gender | age | phone |
+------+-------+--------+------+-------------+
| 2 | Jack | 1 | 28 | 17777777777 |
| 3 | Jerry | 1 | 20 | 12356548 |
| 4 | Mary | 2 | 19 | 1235457952 |
| 5 | Maria | 2 | 19 | 156459628 |
| 6 | leo | 1 | 24 | 43212354 |
+------+-------+--------+------+-------------+
# %代表多个字符，查询name为M开头的
mysql> select *from student where name like 'M%';
+------+-------+--------+------+------------+
| id | name | gender | age | phone |
+------+-------+--------+------+------------+
| 4 | Mary | 2 | 19 | 1235457952 |
| 5 | Maria | 2 | 19 | 156459628 |
+------+-------+--------+------+------------+
mysql> select *from student where name like 'M_';
mysql> select *from student where name like 'M___';
+------+------+--------+------+------------+
| id | name | gender | age | phone |
+------+------+--------+------+------------+
| 4 | Mary | 2 | 19 | 1235457952 |
+------+------+--------+------+------------+
mysql> select *from student where name like 'M____';
+------+-------+--------+------+-----------+
| id | name | gender | age | phone |
+------+-------+--------+------+-----------+
| 5 | Maria | 2 | 19 | 156459628 |
+------+-------+--------+------+-----------+
order by 排序查询
mysql> select * from score;
+------+---------+---------+------+
| id | chinese | english | math |
+------+---------+---------+------+
| 1 | 98 | 99 | 100 |
| 2 | 93 | 94 | 80 |
+------+---------+---------+------+
#asc->按照升序排序
mysql> select * from score order by chinese asc;
+------+---------+---------+------+
| id | chinese | english | math |
+------+---------+---------+------+
| 2 | 93 | 94 | 80 |
| 1 | 98 | 99 | 100 |
+------+---------+---------+------+
#desc->降序排序
mysql> select * from score order by chinese desc;
+------+---------+---------+------+
| id | chinese | english | math |
+------+---------+---------+------+
| 1 | 98 | 99 | 100 |
| 2 | 93 | 94 | 80 |
+------+---------+---------+------+
group by 分组查询
mysql> select * from info;
+----+------+--------+---------+
| id | age | gender | address |
+----+------+--------+---------+
| 1 | 25 | 男 | 上海 |
| 2 | 23 | 女 | 上海 |
| 3 | 26 | 女 | 北京 |
| 4 | 23 | 男 | 北京 |
| 5 | 21 | 男 | 上海 |
+----+------+--------+---------+
#分别计算出男人和女人的平均年龄
mysql> select avg(age) as '年龄' ,gender as '性别' from info group by gender;
+---------+------+
| 年龄 | 性别 |
+---------+------+
| 24.5000 | 女 |
| 23.0000 | 男 |
+---------+------+
#按照地区计算平均年龄
mysql> select avg(age) as '年龄',address as '地区' from info group by address;
+---------+------+
| 年龄 | 地区 |
+---------+------+
| 23.0000 | 上海 |
| 24.5000 | 北京 |
+---------+------+
group by 查询必须是分组字段和聚合函数。
group_concat
mysql> select * from student;
+------+-------+--------+------+-------------+
| id | name | gender | age | phone |
+------+-------+--------+------+-------------+
| 2 | Jack | 1 | 28 | 17777777777 |
| 3 | Jerry | 1 | 20 | 12356548 |
| 4 | Mary | 2 | 19 | 1235457952 |
| 5 | Maria | 2 | 19 | 156459628 |
| 6 | leo | 1 | 24 | 43212354 |
+------+-------+--------+------+-------------+
mysql> select group_concat(name),gender from student group by gender;
+--------------------+--------+
| group_concat(name) | gender |
+--------------------+--------+
| Jack,Jerry,leo | 1 |
| Mary,Maria | 2 |
+--------------------+--------+
having
mysql> select avg(age) as 'age',address as 'address' from info group by address;
+---------+---------+
| age | address |
+---------+---------+
| 23.0000 | 上海 |
| 24.6667 | 北京 |
+---------+---------+
mysql> select avg(age) as 'age',address as 'address' from info group by address having age>24;
+---------+---------+
| age | address |
+---------+---------+
| 24.6667 | 北京 |
+---------+---------+
having 是从group by产生的虚拟表中查询
limit
# 从0开始，往后2个
mysql> select * from info limit 0,2;
+----+------+--------+---------+
| id | age | gender | address |
+----+------+--------+---------+
| 1 | 25 | 男 | 上海 |
| 2 | 23 | 女 | 上海 |
+----+------+--------+---------+
mysql> select * from info limit 1,2;
+----+------+--------+---------+
| id | age | gender | address |
+----+------+--------+---------+
| 2 | 23 | 女 | 上海 |
| 3 | 26 | 女 | 北京 |
+----+------+--------+---------+
mysql> select * from info limit 2,3;
+----+------+--------+---------+
| id | age | gender | address |
+----+------+--------+---------+
| 3 | 26 | 女 | 北京 |
| 4 | 23 | 男 | 北京 |
| 5 | 21 | 男 | 上海 |
+----+------+--------+---------+
#根据年龄降序查前三个
mysql> select * from info order by age desc limit 3;
+----+------+--------+---------+
| id | age | gender | address |
+----+------+--------+---------+
| 3 | 26 | 女 | 北京 |
| 1 | 25 | 男 | 上海 |
| 6 | 25 | 男 | 北京 |
+----+------+--------+---------+
默认是从零开始
distinct all
mysql> select address from info;
#默认为select all address from info;
+---------+
| address |
+---------+
| 上海 |
| 上海 |
| 北京 |
| 北京 |
| 上海 |
| 北京 |
+---------+
#去重
mysql> select distinct address from info;
+---------+
| address |
+---------+
| 上海 |
| 北京 |
+---------+
mysql> select count(distinct address) from info;
+-------------------------+
| count(distinct address) |
+-------------------------+
| 2 |
+-------------------------+
浙公网安备 33010602011771号