一起学mysql 05.mysql 查询数据

最新推荐文章于 2024-09-10 14:55:57 发布
最新推荐文章于 2024-09-10 14:55:57 发布
阅读量197 收藏
点赞数
分类专栏： mysql 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/tangwei0928/article/details/97961562
版权

mysql 查询数据

基本查询语句

mysql> create table employee(num int(4),d_id varchar(20) NOT NULL primary key, name varchar(20), age int(4) NOT NULL,sex varchar(3),homeaddr varchar(20));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into employee values(1,'1001','张三','26','男','北京市海淀区');
Query OK, 1 row affected (0.00 sec)

mysql> insert into employee values(2,'1002','李四','24','女','北京市昌平区');
Query OK, 1 row affected (0.00 sec)

mysql> insert into employee values(3,'1003','王五','25','男','湖南长沙市');
Query OK, 1 row affected (0.00 sec)

mysql> insert into employee values(4,'1004','Aric','15','男','England');
Query OK, 1 row affected (0.00 sec)


mysql> select num,name,sex,homeaddr from employee;
+-----+--------+------+--------------------+
| num | name   | sex  | homeaddr           |
+-----+--------+------+--------------------+
|   1 | 张三   | 男   | 北京市海淀区       |
|   2 | 李四   | 女   | 北京市昌平区       |
|   3 | 王五   | 男   | 湖南长沙市         |
|   4 | Aric   | 男   | England            |
+-----+--------+------+--------------------+
4 rows in set (0.00 sec)


mysql> mysql> select num,d_id,name,sex,homeaddr from employee where age<26 order by d_id desc;
+------+------+--------+------+--------------------+
| num  | d_id | name   | sex  | homeaddr           |
+------+------+--------+------+--------------------+
|    4 | 1004 | Aric   | 男   | England            |
|    3 | 1003 | 王五   | 男   | 湖南长沙市         |
|    2 | 1002 | 李四   | 女   | 北京市昌平区       |
+------+------+--------+------+--------------------+
3 rows in set (0.00 sec)

单表查询

列出表的所有字段

mysql> desc employee;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| num      | int(4)      | YES  |     | NULL    |       |
| d_id     | varchar(20) | NO   | PRI | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| age      | int(4)      | NO   |     | NULL    |       |
| sex      | varchar(3)  | YES  |     | NULL    |       |
| homeaddr | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)


mysql> select num,d_id,name,age,sex,homeaddr from employee;
+------+------+--------+-----+------+--------------------+
| num  | d_id | name   | age | sex  | homeaddr           |
+------+------+--------+-----+------+--------------------+
|    1 | 1001 | 张三   |  26 | 男   | 北京市海淀区       |
|    2 | 1002 | 李四   |  24 | 女   | 北京市昌平区       |
|    3 | 1003 | 王五   |  25 | 男   | 湖南长沙市         |
|    4 | 1004 | Aric   |  15 | 男   | England            |
+------+------+--------+-----+------+--------------------+
4 rows in set (0.00 sec)


mysql> select num,name,age,sex,homeaddr,d_id from employee;
+------+--------+-----+------+--------------------+------+
| num  | name   | age | sex  | homeaddr           | d_id |
+------+--------+-----+------+--------------------+------+
|    1 | 张三   |  26 | 男   | 北京市海淀区       | 1001 |
|    2 | 李四   |  24 | 女   | 北京市昌平区       | 1002 |
|    3 | 王五   |  25 | 男   | 湖南长沙市         | 1003 |
|    4 | Aric   |  15 | 男   | England            | 1004 |
+------+--------+-----+------+--------------------+------+
4 rows in set (0.00 sec)

使用"*"查询所有字段

mysql> select * from employee;
+------+------+--------+-----+------+--------------------+
| num  | d_id | name   | age | sex  | homeaddr           |
+------+------+--------+-----+------+--------------------+
|    1 | 1001 | 张三   |  26 | 男   | 北京市海淀区       |
|    2 | 1002 | 李四   |  24 | 女   | 北京市昌平区       |
|    3 | 1003 | 王五   |  25 | 男   | 湖南长沙市         |
|    4 | 1004 | Aric   |  15 | 男   | England            |
+------+------+--------+-----+------+--------------------+
4 rows in set (0.00 sec)

查询指定字段

mysql> select num,name,sex,homeaddr from employee;
+------+--------+------+--------------------+
| num  | name   | sex  | homeaddr           |
+------+--------+------+--------------------+
|    1 | 张三   | 男   | 北京市海淀区       |
|    2 | 李四   | 女   | 北京市昌平区       |
|    3 | 王五   | 男   | 湖南长沙市         |
|    4 | Aric   | 男   | England            |
+------+--------+------+--------------------+
4 rows in set (0.01 sec)


mysql> select num,name,homeaddr,sex from employee;
+------+--------+--------------------+------+
| num  | name   | homeaddr           | sex  |
+------+--------+--------------------+------+
|    1 | 张三   | 北京市海淀区       | 男   |
|    2 | 李四   | 北京市昌平区       | 女   |
|    3 | 王五   | 湖南长沙市         | 男   |
|    4 | Aric   | England            | 男   |
+------+--------+--------------------+------+
4 rows in set (0.00 sec)

查询指定记录

mysql> select * from employee where d_id='1001';
+------+------+--------+-----+------+--------------------+
| num  | d_id | name   | age | sex  | homeaddr           |
+------+------+--------+-----+------+--------------------+
|    1 | 1001 | 张三   |  26 | 男   | 北京市海淀区       |
+------+------+--------+-----+------+--------------------+
1 row in set (0.00 sec)


mysql> select * from employee where d_id='1005';
Empty set (0.00 sec)

带in关键字查询

mysql> select * from employee where d_id in ('1001','1004');
+------+------+--------+-----+------+--------------------+
| num  | d_id | name   | age | sex  | homeaddr           |
+------+------+--------+-----+------+--------------------+
|    1 | 1001 | 张三   |  26 | 男   | 北京市海淀区       |
|    4 | 1004 | Aric   |  15 | 男   | England            |
+------+------+--------+-----+------+--------------------+
2 rows in set (0.33 sec)


mysql> select * from employee where name not in ('张三','李四');
+------+------+--------+-----+------+-----------------+
| num  | d_id | name   | age | sex  | homeaddr        |
+------+------+--------+-----+------+-----------------+
|    3 | 1003 | 王五   |  25 | 男   | 湖南长沙市      |
|    4 | 1004 | Aric   |  15 | 男   | England         |
+------+------+--------+-----+------+-----------------+
2 rows in set (0.00 sec)

带between and的范围查询

mysql> select * from employee where  age between 15 and 25;
+------+------+--------+-----+------+--------------------+
| num  | d_id | name   | age | sex  | homeaddr           |
+------+------+--------+-----+------+--------------------+
|    2 | 1002 | 李四   |  24 | 女   | 北京市昌平区       |
|    3 | 1003 | 王五   |  25 | 男   | 湖南长沙市         |
|    4 | 1004 | Aric   |  15 | 男   | England            |
+------+------+--------+-----+------+--------------------+
3 rows in set (0.00 sec)


mysql> select * from employee where  age not between 15 and 25;
+------+------+--------+-----+------+--------------------+
| num  | d_id | name   | age | sex  | homeaddr           |
+------+------+--------+-----+------+--------------------+
|    1 | 1001 | 张三   |  26 | 男   | 北京市海淀区       |
+------+------+--------+-----+------+--------------------+
1 row in set (0.00 sec)

带like的字符匹配查询

mysql> select * from employee where name like 'Aric';
+------+------+------+-----+------+----------+
| num  | d_id | name | age | sex  | homeaddr |
+------+------+------+-----+------+----------+
|    4 | 1004 | Aric |  15 | 男   | England  |
+------+------+------+-----+------+----------+
1 row in set (0.00 sec)


mysql> select * from employee where homeaddr like '北京%';
+------+------+--------+-----+------+--------------------+
| num  | d_id | name   | age | sex  | homeaddr           |
+------+------+--------+-----+------+--------------------+
|    1 | 1001 | 张三   |  26 | 男   | 北京市海淀区       |
|    2 | 1002 | 李四   |  24 | 女   | 北京市昌平区       |
+------+------+--------+-----+------+--------------------+
2 rows in set (0.00 sec)


mysql> select * from employee where name like 'Ar_c';
+------+------+------+-----+------+----------+
| num  | d_id | name | age | sex  | homeaddr |
+------+------+------+-----+------+----------+
|    4 | 1004 | Aric |  15 | 男   | England  |
+------+------+------+-----+------+----------+
1 row in set (0.00 sec)

mysql> select * from employee where name like 'A_i_';
+------+------+------+-----+------+----------+
| num  | d_id | name | age | sex  | homeaddr |
+------+------+------+-----+------+----------+
|    4 | 1004 | Aric |  15 | 男   | England  |
+------+------+------+-----+------+----------+
1 row in set (0.00 sec)


mysql> select * from employee where name like '张%';
+------+------+--------+-----+------+--------------------+
| num  | d_id | name   | age | sex  | homeaddr           |
+------+------+--------+-----+------+--------------------+
|    1 | 1001 | 张三   |  26 | 男   | 北京市海淀区       |
+------+------+--------+-----+------+--------------------+
1 row in set (0.00 sec)

mysql> select * from employee where name not like '张%';
+------+------+--------+-----+------+--------------------+
| num  | d_id | name   | age | sex  | homeaddr           |
+------+------+--------+-----+------+--------------------+
|    2 | 1002 | 李四   |  24 | 女   | 北京市昌平区       |
|    3 | 1003 | 王五   |  25 | 男   | 湖南长沙市         |
|    4 | 1004 | Aric   |  15 | 男   | England            |
+------+------+--------+-----+------+--------------------+
3 rows in set (0.01 sec)

查询空置

mysql> insert into employee (num,d_id,name,age,sex) values(5,'1005','tom','25','男');
Query OK, 1 row affected (0.00 sec)

mysql> select * from employee;
+------+------+--------+-----+------+--------------------+
| num  | d_id | name   | age | sex  | homeaddr           |
+------+------+--------+-----+------+--------------------+
|    1 | 1001 | 张三   |  26 | 男   | 北京市海淀区       |
|    2 | 1002 | 李四   |  24 | 女   | 北京市昌平区       |
|    3 | 1003 | 王五   |  25 | 男   | 湖南长沙市         |
|    4 | 1004 | Aric   |  15 | 男   | England            |
|    5 | 1005 | tom    |  25 | 男   | NULL               |
+------+------+--------+-----+------+--------------------+
5 rows in set (0.00 sec)



mysql> select * from employee where homeaddr is null;
+------+------+------+-----+------+----------+
| num  | d_id | name | age | sex  | homeaddr |
+------+------+------+-----+------+----------+
|    5 | 1005 | tom  |  25 | 男   | NULL     |
+------+------+------+-----+------+----------+
1 row in set (0.00 sec)

mysql> select * from employee where homeaddr is not null;
+------+------+--------+-----+------+--------------------+
| num  | d_id | name   | age | sex  | homeaddr           |
+------+------+--------+-----+------+--------------------+
|    1 | 1001 | 张三   |  26 | 男   | 北京市海淀区       |
|    2 | 1002 | 李四   |  24 | 女   | 北京市昌平区       |
|    3 | 1003 | 王五   |  25 | 男   | 湖南长沙市         |
|    4 | 1004 | Aric   |  15 | 男   | England            |
+------+------+--------+-----+------+--------------------+
4 rows in set (0.00 sec)

带and的多条件查询

mysql> select * from employee where age<26 and sex='男' and d_id<'1004';
+------+------+--------+-----+------+-----------------+
| num  | d_id | name   | age | sex  | homeaddr        |
+------+------+--------+-----+------+-----------------+
|    3 | 1003 | 王五   |  25 | 男   | 湖南长沙市      |
+------+------+--------+-----+------+-----------------+
1 row in set (0.00 sec)


mysql> select * from employee where num in (1,2,3) and age between 15 and 25 and homeaddr like '北京%';
+------+------+--------+-----+------+--------------------+
| num  | d_id | name   | age | sex  | homeaddr           |
+------+------+--------+-----+------+--------------------+
|    2 | 1002 | 李四   |  24 | 女   | 北京市昌平区       |
+------+------+--------+-----+------+--------------------+
1 row in set (0.00 sec)

带or的多条件查询

mysql> select * from employee where d_id='1001' or sex like '男';
+------+------+--------+-----+------+--------------------+
| num  | d_id | name   | age | sex  | homeaddr           |
+------+------+--------+-----+------+--------------------+
|    1 | 1001 | 张三   |  26 | 男   | 北京市海淀区       |
|    3 | 1003 | 王五   |  25 | 男   | 湖南长沙市         |
|    4 | 1004 | Aric   |  15 | 男   | England            |
|    5 | 1005 | tom    |  25 | 男   | NULL               |
+------+------+--------+-----+------+--------------------+
4 rows in set (0.00 sec)

mysql> select * from employee where num in (1,2,3) or age between 15 and 20 or homeaddr like '北京%';
+------+------+--------+-----+------+--------------------+
| num  | d_id | name   | age | sex  | homeaddr           |
+------+------+--------+-----+------+--------------------+
|    1 | 1001 | 张三   |  26 | 男   | 北京市海淀区       |
|    2 | 1002 | 李四   |  24 | 女   | 北京市昌平区       |
|    3 | 1003 | 王五   |  25 | 男   | 湖南长沙市         |
|    4 | 1004 | Aric   |  15 | 男   | England            |
+------+------+--------+-----+------+--------------------+
4 rows in set (0.00 sec)

查询结果不重复

mysql> insert into employee values(5,'1006','jerry','20','男','usa');
Query OK, 1 row affected (0.00 sec)

mysql> select * from employee;
+------+------+--------+-----+------+--------------------+
| num  | d_id | name   | age | sex  | homeaddr           |
+------+------+--------+-----+------+--------------------+
|    1 | 1001 | 张三   |  26 | 男   | 北京市海淀区       |
|    2 | 1002 | 李四   |  24 | 女   | 北京市昌平区       |
|    3 | 1003 | 王五   |  25 | 男   | 湖南长沙市         |
|    4 | 1004 | Aric   |  15 | 男   | England            |
|    5 | 1005 | tom    |  25 | 男   | NULL               |
|    5 | 1006 | jerry  |  20 | 男   | usa                |
+------+------+--------+-----+------+--------------------+
6 rows in set (0.00 sec)


mysql> select num from employee;
+------+
| num  |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    5 |
+------+
6 rows in set (0.00 sec)

mysql> select  distinct num from employee;
+------+
| num  |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)

对查询结果进行排序

mysql> select * from employee order by age;
+------+------+--------+-----+------+--------------------+
| num  | d_id | name   | age | sex  | homeaddr           |
+------+------+--------+-----+------+--------------------+
|    4 | 1004 | Aric   |  15 | 男   | England            |
|    5 | 1006 | jerry  |  20 | 男   | usa                |
|    2 | 1002 | 李四   |  24 | 女   | 北京市昌平区       |
|    3 | 1003 | 王五   |  25 | 男   | 湖南长沙市         |
|    5 | 1005 | tom    |  25 | 男   | NULL               |
|    1 | 1001 | 张三   |  26 | 男   | 北京市海淀区       |
+------+------+--------+-----+------+--------------------+
6 rows in set (0.00 sec)


mysql> select * from employee order by age asc;
+------+------+--------+-----+------+--------------------+
| num  | d_id | name   | age | sex  | homeaddr           |
+------+------+--------+-----+------+--------------------+
|    4 | 1004 | Aric   |  15 | 男   | England            |
|    5 | 1006 | jerry  |  20 | 男   | usa                |
|    2 | 1002 | 李四   |  24 | 女   | 北京市昌平区       |
|    3 | 1003 | 王五   |  25 | 男   | 湖南长沙市         |
|    5 | 1005 | tom    |  25 | 男   | NULL               |
|    1 | 1001 | 张三   |  26 | 男   | 北京市海淀区       |
+------+------+--------+-----+------+--------------------+
6 rows in set (0.00 sec)

mysql> select * from employee order by age desc;
+------+------+--------+-----+------+--------------------+
| num  | d_id | name   | age | sex  | homeaddr           |
+------+------+--------+-----+------+--------------------+
|    1 | 1001 | 张三   |  26 | 男   | 北京市海淀区       |
|    3 | 1003 | 王五   |  25 | 男   | 湖南长沙市         |
|    5 | 1005 | tom    |  25 | 男   | NULL               |
|    2 | 1002 | 李四   |  24 | 女   | 北京市昌平区       |
|    5 | 1006 | jerry  |  20 | 男   | usa                |
|    4 | 1004 | Aric   |  15 | 男   | England            |
+------+------+--------+-----+------+--------------------+
6 rows in set (0.00 sec)


mysql> select * from employee order by d_id asc,age desc;
+------+------+--------+-----+------+--------------------+
| num  | d_id | name   | age | sex  | homeaddr           |
+------+------+--------+-----+------+--------------------+
|    1 | 1001 | 张三   |  26 | 男   | 北京市海淀区       |
|    2 | 1002 | 李四   |  24 | 女   | 北京市昌平区       |
|    3 | 1003 | 王五   |  25 | 男   | 湖南长沙市         |
|    4 | 1004 | Aric   |  15 | 男   | England            |
|    5 | 1005 | tom    |  25 | 男   | NULL               |
|    5 | 1006 | jerry  |  20 | 男   | usa                |
+------+------+--------+-----+------+--------------------+
6 rows in set (0.00 sec)

分组查询

单独使用group by关键字来分组
mysql> select * from employee group by sex;
+------+------+--------+-----+------+--------------------+
| num  | d_id | name   | age | sex  | homeaddr           |
+------+------+--------+-----+------+--------------------+
|    2 | 1002 | 李四   |  24 | 女   | 北京市昌平区       |
|    1 | 1001 | 张三   |  26 | 男   | 北京市海淀区       |
+------+------+--------+-----+------+--------------------+
2 rows in set (0.00 sec)
group by关键字与group_concat() 函数一起使用
mysql> select sex,group_concat(name) from employee group by sex;
+------+------------------------------+
| sex  | group_concat(name)           |
+------+------------------------------+
| 女   | 李四                         |
| 男   | 张三,王五,Aric,tom,jerry     |
+------+------------------------------+
2 rows in set (0.00 sec)
group by关键字与集合·函数一起使用
mysql> select sex,count(sex) from employee group by sex;
+------+------------+
| sex  | count(sex) |
+------+------------+
| 女   |          1 |
| 男   |          5 |
+------+------------+
2 rows in set (0.00 sec)
group by关键字与having一起使用
mysql> select sex,count(sex) from employee group by sex having count(sex)>=3;
+------+------------+
| sex  | count(sex) |
+------+------------+
| 男   |          5 |
+------+------------+
1 row in set (0.00 sec)
按多个字段进行分组
mysql> select * from employee group by d_id,sex;
+------+------+--------+-----+------+--------------------+
| num  | d_id | name   | age | sex  | homeaddr           |
+------+------+--------+-----+------+--------------------+
|    1 | 1001 | 张三   |  26 | 男   | 北京市海淀区       |
|    2 | 1002 | 李四   |  24 | 女   | 北京市昌平区       |
|    3 | 1003 | 王五   |  25 | 男   | 湖南长沙市         |
|    4 | 1004 | Aric   |  15 | 男   | England            |
|    5 | 1005 | tom    |  25 | 男   | NULL               |
|    5 | 1006 | jerry  |  20 | 男   | usa                |
+------+------+--------+-----+------+--------------------+
6 rows in set (0.00 sec)
group by与with rollup一起使用
mysql> select sex,count(sex) from employee group by sex with rollup;
+------+------------+
| sex  | count(sex) |
+------+------------+
| 女   |          1 |
| 男   |          5 |
| NULL |          6 |
+------+------------+
3 rows in set (0.00 sec)

mysql> select sex,group_concat(name) from employee group by sex with rollup;
+------+-------------------------------------+
| sex  | group_concat(name)                  |
+------+-------------------------------------+
| 女   | 李四                                |
| 男   | 张三,王五,Aric,tom,jerry            |
| NULL | 李四,张三,王五,Aric,tom,jerry       |
+------+-------------------------------------+
3 rows in set (0.00 sec)

用linit限制查询结果数量

不指定初始位置
mysql> select * from employee limit 2;
+------+------+--------+-----+------+--------------------+
| num  | d_id | name   | age | sex  | homeaddr           |
+------+------+--------+-----+------+--------------------+
|    1 | 1001 | 张三   |  26 | 男   | 北京市海淀区       |
|    2 | 1002 | 李四   |  24 | 女   | 北京市昌平区       |
+------+------+--------+-----+------+--------------------+
2 rows in set (0.00 sec)
指定初始位置
mysql> select * from employee limit 0,2;
+------+------+--------+-----+------+--------------------+
| num  | d_id | name   | age | sex  | homeaddr           |
+------+------+--------+-----+------+--------------------+
|    1 | 1001 | 张三   |  26 | 男   | 北京市海淀区       |
|    2 | 1002 | 李四   |  24 | 女   | 北京市昌平区       |
+------+------+--------+-----+------+--------------------+
2 rows in set (0.00 sec)

mysql> select * from employee limit 1,2;
+------+------+--------+-----+------+--------------------+
| num  | d_id | name   | age | sex  | homeaddr           |
+------+------+--------+-----+------+--------------------+
|    2 | 1002 | 李四   |  24 | 女   | 北京市昌平区       |
|    3 | 1003 | 王五   |  25 | 男   | 湖南长沙市         |
+------+------+--------+-----+------+--------------------+
2 rows in set (0.00 sec)

使用集合函数查询

count()函数

mysql> select * from employee;
+------+------+--------+-----+------+--------------------+
| num  | d_id | name   | age | sex  | homeaddr           |
+------+------+--------+-----+------+--------------------+
|    1 | 1001 | 张三   |  26 | 男   | 北京市海淀区       |
|    2 | 1002 | 李四   |  24 | 女   | 北京市昌平区       |
|    3 | 1003 | 王五   |  25 | 男   | 湖南长沙市         |
|    4 | 1004 | Aric   |  15 | 男   | England            |
+------+------+--------+-----+------+--------------------+
4 rows in set (0.01 sec)

mysql> select count(*) from employee;
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)
mysql> select d_id,count(*) from employee group by d_id;
+------+----------+
| d_id | count(*) |
+------+----------+
| 1001 |        1 |
| 1002 |        1 |
| 1003 |        1 |
| 1004 |        1 |
+------+----------+
4 rows in set (0.00 sec)

sum(*)函数

mysql> create table grade(num int(4),course varchar(20),score int(4));
Query OK, 0 rows affected (0.01 sec)
mysql> insert  into  grade values(1001,'数学',80);
Query OK, 1 row affected (0.00 sec)

mysql> insert  into  grade values(1001,'语文',90);
Query OK, 1 row affected (0.00 sec)

mysql> insert  into  grade values(1001,'英语',95);
Query OK, 1 row affected (0.00 sec)

mysql> insert  into  grade values(1001,'计算机',85);
Query OK, 1 row affected (0.00 sec)

mysql> insert  into  grade values(1002,'数学',81);
Query OK, 1 row affected (0.00 sec)

mysql> insert  into  grade values(1002,'语文',88);
Query OK, 1 row affected (0.00 sec)

mysql> insert  into  grade values(1002,'英语',25);
Query OK, 1 row affected (0.00 sec)

mysql> insert  into  grade values(1002,'计算机',75);
Query OK, 1 row affected (0.00 sec)


mysql> select * from grade where num=1001;
+------+-----------+-------+
| num  | course    | score |
+------+-----------+-------+
| 1001 | 数学      |    80 |
| 1001 | 语文      |    90 |
| 1001 | 英语      |    95 |
| 1001 | 计算机    |    85 |
+------+-----------+-------+
4 rows in set (0.01 sec)

mysql> select num,sum(score) from grade where num=1001;
+------+------------+
| num  | sum(score) |
+------+------------+
| 1001 |        350 |
+------+------------+
1 row in set (0.00 sec)
mysql> select num,sum(score) from grade group by num;
+------+------------+
| num  | sum(score) |
+------+------------+
| 1001 |        350 |
| 1002 |        269 |
+------+------------+
2 rows in set (0.00 sec)

AVG(*)函数

mysql> select avg(score) from grade where num=1001;
+------------+
| avg(score) |
+------------+
|    87.5000 |
+------------+
1 row in set (0.00 sec)

mysql> select num,avg(score) from grade group by num;
+------+------------+
| num  | avg(score) |
+------+------------+
| 1001 |    87.5000 |
| 1002 |    67.2500 |
+------+------------+
2 rows in set (0.00 sec)

mysql> select course,avg(score) from grade group by course;
+-----------+------------+
| course    | avg(score) |
+-----------+------------+
| 数学      |    80.5000 |
| 英语      |    60.0000 |
| 计算机    |    80.0000 |
| 语文      |    89.0000 |
+-----------+------------+
4 rows in set (0.00 sec)

MAX(*)函数

mysql> select num,course,max(score) from grade where num=1001;
+------+--------+------------+
| num  | course | max(score) |
+------+--------+------------+
| 1001 | 数学   |         95 |
+------+--------+------------+
1 row in set (0.00 sec)

mysql> select num,course,max(score) from grade group by num;
+------+--------+------------+
| num  | course | max(score) |
+------+--------+------------+
| 1001 | 数学   |         95 |
| 1002 | 数学   |         88 |
+------+--------+------------+
2 rows in set (0.00 sec)

mysql> select course,max(score) from grade group by course;
+-----------+------------+
| course    | max(score) |
+-----------+------------+
| 数学      |         81 |
| 英语      |         95 |
| 计算机    |         85 |
| 语文      |         90 |
+-----------+------------+
4 rows in set (0.00 sec)

MIN(*)函数

mysql> select num,course,min(score) from grade where num=1001;
+------+--------+------------+
| num  | course | min(score) |
+------+--------+------------+
| 1001 | 数学   |         80 |
+------+--------+------------+
1 row in set (0.00 sec)

mysql> select num,course,min(score) from grade group by num;
+------+--------+------------+
| num  | course | min(score) |
+------+--------+------------+
| 1001 | 数学   |         80 |
| 1002 | 数学   |         25 |
+------+--------+------------+
2 rows in set (0.00 sec)

mysql> select course,min(score) from grade group by course;
+-----------+------------+
| course    | min(score) |
+-----------+------------+
| 数学      |         80 |
| 英语      |         25 |
| 计算机    |         75 |
| 语文      |         88 |
+-----------+------------+
4 rows in set (0.00 sec)

mysql> select min(age) from employee;
+----------+
| min(age) |
+----------+
|       15 |
+----------+
1 row in set (0.00 sec)

连接查询

内连接查询

mysql> select * from department;
+------+-----------+--------------+---------------------+
| d_id | d_name    | function     | address             |
+------+-----------+--------------+---------------------+
| 1001 | 科研部    | 科研产品     | 3号楼5层            |
| 1002 | 生成部    | 生成产品     | 5号楼1层            |
| 1003 | 销售部    | 策划销售     | 1号楼销售大厅       |
| 1004 | 人事部    | 公司人事     | 4号楼5层            |
+------+-----------+--------------+---------------------+
4 rows in set (0.00 sec)

mysql> select * from department;
+------+-----------+--------------+---------------------+
| d_id | d_name    | function     | address             |
+------+-----------+--------------+---------------------+
| 1001 | 科研部    | 科研产品     | 3号楼5层            |
| 1002 | 生成部    | 生成产品     | 5号楼1层            |
| 1003 | 销售部    | 策划销售     | 1号楼销售大厅       |
| 1005 | 人事部    | 公司人事     | 4号楼5层            |
+------+-----------+--------------+---------------------+
4 rows in set (0.00 sec)


mysql> select num,name,employee.d_id,age,sex,d_name,function from employee,department where employee.d_id=department.d_id;
+------+--------+------+-----+------+-----------+--------------+
| num  | name   | d_id | age | sex  | d_name    | function     |
+------+--------+------+-----+------+-----------+--------------+
|    1 | 张三   | 1001 |  26 | 男   | 科研部    | 科研产品     |
|    2 | 李四   | 1002 |  24 | 女   | 生成部    | 生成产品     |
|    3 | 王五   | 1003 |  25 | 男   | 销售部    | 策划销售     |
+------+--------+------+-----+------+-----------+--------------+
3 rows in set (0.00 sec)

外连接查询

左连接查询
mysql> select num,name,employee.d_id,age,sex,d_name,function from employee left join department on employee.d_id=department.d_id;
+------+--------+------+-----+------+-----------+--------------+
| num  | name   | d_id | age | sex  | d_name    | function     |
+------+--------+------+-----+------+-----------+--------------+
|    1 | 张三   | 1001 |  26 | 男   | 科研部    | 科研产品     |
|    2 | 李四   | 1002 |  24 | 女   | 生成部    | 生成产品     |
|    3 | 王五   | 1003 |  25 | 男   | 销售部    | 策划销售     |
|    4 | Aric   | 1004 |  15 | 男   | NULL      | NULL         |
+------+--------+------+-----+------+-----------+--------------+
4 rows in set (0.00 sec)
右连接查询
mysql>  select num,name,age,sex,department.d_id,d_name,function from employee right join department on employee.d_id=department.d_id;
+------+--------+------+------+------+-----------+--------------+
| num  | name   | age  | sex  | d_id | d_name    | function     |
+------+--------+------+------+------+-----------+--------------+
|    1 | 张三   |   26 | 男   | 1001 | 科研部    | 科研产品     |
|    2 | 李四   |   24 | 女   | 1002 | 生成部    | 生成产品     |
|    3 | 王五   |   25 | 男   | 1003 | 销售部    | 策划销售     |
| NULL | NULL   | NULL | NULL | 1005 | 人事部    | 公司人事     |
+------+--------+------+------+------+-----------+--------------+
4 rows in set (0.00 sec)

复合条件查询

mysql> select num,name,employee.d_id,age,sex,d_name,function from employee,department where employee.d_id=department.d_id and age>24;
+------+--------+------+-----+------+-----------+--------------+
| num  | name   | d_id | age | sex  | d_name    | function     |
+------+--------+------+-----+------+-----------+--------------+
|    1 | 张三   | 1001 |  26 | 男   | 科研部    | 科研产品     |
|    3 | 王五   | 1003 |  25 | 男   | 销售部    | 策划销售     |
+------+--------+------+-----+------+-----------+--------------+
2 rows in set (0.00 sec)


mysql> select num,name,employee.d_id,age,sex,d_name,function from employee,department where employee.d_id=department.d_id order by age asc;
+------+--------+------+-----+------+-----------+--------------+
| num  | name   | d_id | age | sex  | d_name    | function     |
+------+--------+------+-----+------+-----------+--------------+
|    2 | 李四   | 1002 |  24 | 女   | 生成部    | 生成产品     |
|    3 | 王五   | 1003 |  25 | 男   | 销售部    | 策划销售     |
|    1 | 张三   | 1001 |  26 | 男   | 科研部    | 科研产品     |
+------+--------+------+-----+------+-----------+--------------+
3 rows in set (0.00 sec)

子查询

带in关键字查询

mysql> select * from employee;
+------+------+--------+-----+------+--------------------+
| num  | d_id | name   | age | sex  | homeaddr           |
+------+------+--------+-----+------+--------------------+
|    1 | 1001 | 张三   |  26 | 男   | 北京市海淀区       |
|    2 | 1002 | 李四   |  24 | 女   | 北京市昌平区       |
|    3 | 1003 | 王五   |  25 | 男   | 湖南长沙市         |
|    4 | 1004 | Aric   |  15 | 男   | England            |
+------+------+--------+-----+------+--------------------+
4 rows in set (0.00 sec)

mysql> select * from department;
+------+-----------+--------------+---------------------+
| d_id | d_name    | function     | address             |
+------+-----------+--------------+---------------------+
| 1001 | 科研部    | 科研产品     | 3号楼5层            |
| 1002 | 生成部    | 生成产品     | 5号楼1层            |
| 1003 | 销售部    | 策划销售     | 1号楼销售大厅       |
| 1005 | 人事部    | 公司人事     | 4号楼5层            |
+------+-----------+--------------+---------------------+
4 rows in set (0.00 sec)



mysql> select * from employee where d_id in (select d_id from department);
+------+------+--------+-----+------+--------------------+
| num  | d_id | name   | age | sex  | homeaddr           |
+------+------+--------+-----+------+--------------------+
|    1 | 1001 | ÕÅÈý   |  26 | ÄÐ   | ±±¾©Êк£µíÇø       |
|    2 | 1002 | ÀîËÄ   |  24 | Å®   | ±±¾©ÊвýƽÇø       |
|    3 | 1003 | ÍõÎå   |  25 | ÄÐ   | ºþÄϳ¤É³ÊÐ         |
+------+------+--------+-----+------+--------------------+
3 rows in set (0.37 sec)

mysql> select * from employee where d_id not  in (select d_id from department);
+------+------+------+-----+------+----------+
| num  | d_id | name | age | sex  | homeaddr |
+------+------+------+-----+------+----------+
|    4 | 1004 | Aric |  15 | 男   | England  |
+------+------+------+-----+------+----------+
1 row in set (0.00 sec)

带比较运算符的子查询

mysql> create  table scholarship(level int(10),score int(4));
Query OK, 0 rows affected (0.35 sec)

mysql> insert into scholarship values(1,90);
Query OK, 1 row affected (0.00 sec)

mysql> insert into scholarship values(2,80);
Query OK, 1 row affected (0.01 sec)

mysql> insert into scholarship values(3,70);
Query OK, 1 row affected (0.00 sec)

mysql> create table computer_stu(id int(5),name varchar(10),score int(4));
Query OK, 0 rows affected (0.10 sec)

mysql> insert into computer_stu values(1001,'Lily',85);
Query OK, 1 row affected (0.00 sec)

mysql> insert into computer_stu values(1002,'Tom',91);
Query OK, 1 row affected (0.00 sec)

mysql> insert into computer_stu values(1003,'Jim',87);
Query OK, 1 row affected (0.00 sec)

mysql> insert into computer_stu values(1004,'Aric',77);
Query OK, 1 row affected (0.00 sec)

mysql> insert into computer_stu values(1005,'Lucy',65);
Query OK, 1 row affected (0.00 sec)

mysql> insert into computer_stu values(1006,'Andy',99);
Query OK, 1 row affected (0.01 sec)

mysql> insert into computer_stu values(1007,'Ada',85);
Query OK, 1 row affected (0.00 sec)

mysql> insert into computer_stu values(1008,'Jeck',70);
Query OK, 1 row affected (0.01 sec)


mysql> select * from scholarship;
+-------+-------+
| level | score |
+-------+-------+
|     1 |    90 |
|     2 |    80 |
|     3 |    70 |
+-------+-------+
3 rows in set (0.00 sec)

mysql> select * from computer_stu;
+------+------+-------+
| id   | name | score |
+------+------+-------+
| 1001 | Lily |    85 |
| 1002 | Tom  |    91 |
| 1003 | Jim  |    87 |
| 1004 | Aric |    77 |
| 1005 | Lucy |    65 |
| 1006 | Andy |    99 |
| 1007 | Ada  |    85 |
| 1008 | Jeck |    70 |
+------+------+-------+
8 rows in set (0.00 sec)


mysql> select id,name,score from computer_stu where score >= (select score from scholarship where level=1);
+------+------+-------+
| id   | name | score |
+------+------+-------+
| 1002 | Tom  |    91 |
| 1006 | Andy |    99 |
+------+------+-------+
2 rows in set (0.00 sec)


mysql> select * from department;
+------+-----------+--------------+---------------------+
| d_id | d_name    | function     | address             |
+------+-----------+--------------+---------------------+
| 1001 | 科研部    | 科研产品     | 3号楼5层            |
| 1002 | 生成部    | 生成产品     | 5号楼1层            |
| 1003 | 销售部    | 策划销售     | 1号楼销售大厅       |
| 1005 | 人事部    | 公司人事     | 4号楼5层            |
+------+-----------+--------------+---------------------+
4 rows in set (0.00 sec)

mysql> select * from employee;
+------+------+--------+-----+------+--------------------+
| num  | d_id | name   | age | sex  | homeaddr           |
+------+------+--------+-----+------+--------------------+
|    1 | 1001 | 张三   |  26 | 男   | 北京市海淀区       |
|    2 | 1002 | 李四   |  24 | 女   | 北京市昌平区       |
|    3 | 1003 | 王五   |  25 | 男   | 湖南长沙市         |
|    4 | 1004 | Aric   |  15 | 男   | England            |
+------+------+--------+-----+------+--------------------+
4 rows in set (0.00 sec)


mysql> select d_id,d_name from department where d_id != (select d_id from employee where age=24);
+------+-----------+
| d_id | d_name    |
+------+-----------+
| 1001 | 科研部    |
| 1003 | 销售部    |
| 1005 | 人事部    |
+------+-----------+
3 rows in set (0.00 sec)

带exists关键字的子查询

mysql> select * from employee where exists (select d_name from department where d_id='1003');
+------+------+--------+-----+------+--------------------+
| num  | d_id | name   | age | sex  | homeaddr           |
+------+------+--------+-----+------+--------------------+
|    1 | 1001 | 张三   |  26 | 男   | 北京市海淀区       |
|    2 | 1002 | 李四   |  24 | 女   | 北京市昌平区       |
|    3 | 1003 | 王五   |  25 | 男   | 湖南长沙市         |
|    4 | 1004 | Aric   |  15 | 男   | England            |
+------+------+--------+-----+------+--------------------+
4 rows in set (0.00 sec)

mysql> select * from employee where exists (select d_name from department where d_id='1006');
Empty set (0.00 sec)

mysql> select * from employee where age>24 and exists (select d_name from department where d_id='1003');
+------+------+--------+-----+------+--------------------+
| num  | d_id | name   | age | sex  | homeaddr           |
+------+------+--------+-----+------+--------------------+
|    1 | 1001 | 张三   |  26 | 男   | 北京市海淀区       |
|    3 | 1003 | 王五   |  25 | 男   | 湖南长沙市         |
+------+------+--------+-----+------+--------------------+
2 rows in set (0.00 sec)

mysql> select * from employee where not exists (select d_name from department where d_id='1003');
Empty set (0.00 sec)

带any关键字的子查询

mysql> select * from computer_stu;
+------+------+-------+
| id   | name | score |
+------+------+-------+
| 1001 | Lily |    85 |
| 1002 | Tom  |    91 |
| 1003 | Jim  |    87 |
| 1004 | Aric |    77 |
| 1005 | Lucy |    65 |
| 1006 | Andy |    99 |
| 1007 | Ada  |    85 |
| 1008 | Jeck |    70 |
+------+------+-------+
8 rows in set (0.00 sec)

mysql> select * from scholarship;
+-------+-------+
| level | score |
+-------+-------+
|     1 |    90 |
|     2 |    80 |
|     3 |    70 |
+-------+-------+
3 rows in set (0.00 sec)

mysql> select * from computer_stu where score>=any (select score from scholarship);
+------+------+-------+
| id   | name | score |
+------+------+-------+
| 1001 | Lily |    85 |
| 1002 | Tom  |    91 |
| 1003 | Jim  |    87 |
| 1004 | Aric |    77 |
| 1006 | Andy |    99 |
| 1007 | Ada  |    85 |
| 1008 | Jeck |    70 |
+------+------+-------+
7 rows in set (0.00 sec)

带all关键字的子查询

mysql> select * from computer_stu where score>=all (select score from scholarship);
+------+------+-------+
| id   | name | score |
+------+------+-------+
| 1002 | Tom  |    91 |
| 1006 | Andy |    99 |
+------+------+-------+
2 rows in set (0.00 sec)

合并查询结果

mysql> select d_id from department;
+------+
| d_id |
+------+
| 1001 |
| 1002 |
| 1003 |
| 1005 |
+------+
4 rows in set (0.00 sec)

mysql> select d_id from employee;
+------+
| d_id |
+------+
| 1001 |
| 1002 |
| 1003 |
| 1004 |
+------+
4 rows in set (0.00 sec)

mysql> select d_id from employee union select d_id from department;
+------+
| d_id |
+------+
| 1001 |
| 1002 |
| 1003 |
| 1004 |
| 1005 |
+------+
5 rows in set (0.01 sec)

mysql> select d_id from employee union all select d_id from department;
+------+
| d_id |
+------+
| 1001 |
| 1002 |
| 1003 |
| 1004 |
| 1001 |
| 1002 |
| 1003 |
| 1005 |
+------+
8 rows in set (0.00 sec)

为表和字段取别名

为表取别名

mysql> select * from department d where d.d_id=1001;
+------+-----------+--------------+-------------+
| d_id | d_name    | function     | address     |
+------+-----------+--------------+-------------+
| 1001 | 科研部    | 科研产品     | 3号楼5层    |
+------+-----------+--------------+-------------+
1 row in set (0.00 sec)

为字段取别名

mysql> select d_id as department_id,d_name as department_name from department;
+---------------+-----------------+
| department_id | department_name |
+---------------+-----------------+
| 1001          | 科研部          |
| 1002          | 生成部          |
| 1003          | 销售部          |
| 1005          | 人事部          |
+---------------+-----------------+
4 rows in set (0.00 sec)


mysql> select d.d_id as department_id,d.d_name as department_name from department d where d.d_id=1001;
+---------------+-----------------+
| department_id | department_name |
+---------------+-----------------+
| 1001          | 科研部          |
+---------------+-----------------+
1 row in set (0.00 sec)

使用正则表达式查询

模式字符含义
^匹配字符串开始的地方
$匹配字符串结束的地方
.代表字符串中任意一个字符
[…]匹配[…]中的任意一个字符
[^…]匹配除了[…]中的任意一个字符
a|b|c匹配a,b,c中任意一个字符
*代表多个该符号前的字符，大于等于0个
+代表多个该符号前的字符，大于等于1个
{N}字符串出现N次
{M,N}字符串出现最少M次，最多N次
mysql> create table info(id int(4), name varchar(20));
Query OK, 0 rows affected (0.35 sec)

mysql> insert into info values(1,'Aric');
Query OK, 1 row affected (0.01 sec)

mysql> insert into info values(2,'Eric');
Query OK, 1 row affected (0.00 sec)

mysql> insert into info values(3,'Jack');
Query OK, 1 row affected (0.00 sec)

mysql> insert into info values(4,'Lucy');
Query OK, 1 row affected (0.00 sec)

mysql> insert into info values(5,'aaa');
Query OK, 1 row affected (0.00 sec)

mysql> insert into info values(6,'Lily');
Query OK, 1 row affected (0.00 sec)

mysql> insert into info values(7,'Tom');
Query OK, 1 row affected (0.00 sec)

mysql> insert into info values(8,'dadaaa');
Query OK, 1 row affected (0.00 sec)

mysql> insert into info values(9,'aaa');
Query OK, 1 row affected (0.00 sec)

mysql> insert into info values(10,'aaabd');
Query OK, 1 row affected (0.00 sec)

mysql> insert into info values(11,'abc12');
Query OK, 1 row affected (0.00 sec)

mysql> insert into info values(12,'ad321');
Query OK, 1 row affected (0.00 sec)

mysql> insert into info values(13,'x11');
Query OK, 1 row affected (0.00 sec)



mysql> select * from info;
+------+--------+
| id   | name   |
+------+--------+
|    1 | Aric   |
|    2 | Eric   |
|    3 | Jack   |
|    4 | Lucy   |
|    5 | aaa    |
|    6 | Lily   |
|    7 | Tom    |
|    8 | dadaaa |
|    9 | aaa    |
|   10 | aaabd  |
|   11 | abc12  |
|   12 | ad321  |
|   13 | x11    |
+------+--------+
13 rows in set (0.00 sec)

查询特定字符和特定字符串开头的记录

mysql> select * from info where name regexp '^L';
+------+------+
| id   | name |
+------+------+
|    4 | Lucy |
|    6 | Lily |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from info where name regexp '^aaa';
+------+-------+
| id   | name  |
+------+-------+
|    5 | aaa   |
|    9 | aaa   |
|   10 | aaabd |
+------+-------+
3 rows in set (0.00 sec)

查询特定字符和特定字符串结尾的记录

mysql> select * from info where name regexp 'c$';
+------+------+
| id   | name |
+------+------+
|    1 | Aric |
|    2 | Eric |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from info where name regexp 'aaa$';
+------+--------+
| id   | name   |
+------+--------+
|    5 | aaa    |
|    8 | dadaaa |
|    9 | aaa    |
+------+--------+
3 rows in set (0.00 sec)

用符号“.”来替代字符串中的任意一个字符

mysql> select * from info where name regexp 'L...';
+------+------+
| id   | name |
+------+------+
|    4 | Lucy |
|    6 | Lily |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from info where name regexp 'L..y$';
+------+------+
| id   | name |
+------+------+
|    4 | Lucy |
|    6 | Lily |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from info where name regexp '.o.';
+------+------+
| id   | name |
+------+------+
|    7 | Tom  |
+------+------+
1 row in set (0.00 sec)

mysql> select * from info where name regexp '.';
+------+--------+
| id   | name   |
+------+--------+
|    1 | Aric   |
|    2 | Eric   |
|    3 | Jack   |
|    4 | Lucy   |
|    5 | aaa    |
|    6 | Lily   |
|    7 | Tom    |
|    8 | dadaaa |
|    9 | aaa    |
|   10 | aaabd  |
|   11 | abc12  |
|   12 | ad321  |
|   13 | x11    |
+------+--------+
13 rows in set (0.00 sec)

匹配指定字符中的任意一个

mysql> select * from info where name regexp '[ceo]';
+------+-------+
| id   | name  |
+------+-------+
|    1 | Aric  |
|    2 | Eric  |
|    3 | Jack  |
|    4 | Lucy  |
|    7 | Tom   |
|   11 | abc12 |
+------+-------+
6 rows in set (0.00 sec)

mysql> select * from info where name regexp '[0-9]';
+------+-------+
| id   | name  |
+------+-------+
|   11 | abc12 |
|   12 | ad321 |
|   13 | x11   |
+------+-------+
3 rows in set (0.00 sec)

mysql> select * from info where name regexp '[0-9a-c]';
+------+--------+
| id   | name   |
+------+--------+
|    1 | Aric   |
|    2 | Eric   |
|    3 | Jack   |
|    4 | Lucy   |
|    5 | aaa    |
|    8 | dadaaa |
|    9 | aaa    |
|   10 | aaabd  |
|   11 | abc12  |
|   12 | ad321  |
|   13 | x11    |
+------+--------+
11 rows in set (0.00 sec)

匹配指定字符以外的字符

mysql> select * from info where name regexp '[^a-w0-9]';
+------+------+
| id   | name |
+------+------+
|    4 | Lucy |
|    6 | Lily |
|   13 | x11  |
+------+------+
3 rows in set (0.00 sec)

匹配指定字符

mysql> select * from info where name regexp 'ic';
+------+------+
| id   | name |
+------+------+
|    1 | Aric |
|    2 | Eric |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from info where name regexp 'xyz';
Empty set (0.00 sec)

mysql> select * from info where name regexp 'rst';
Empty set (0.00 sec)

mysql> select * from info where name regexp 'ic|uc|ab';
+------+-------+
| id   | name  |
+------+-------+
|    1 | Aric  |
|    2 | Eric  |
|    4 | Lucy  |
|   10 | aaabd |
|   11 | abc12 |
+------+-------+
5 rows in set (0.00 sec)

用"*"来匹配多个字符

mysql> select * from info where name regexp 'a*c';
+------+-------+
| id   | name  |
+------+-------+
|    1 | Aric  |
|    2 | Eric  |
|    3 | Jack  |
|    4 | Lucy  |
|   11 | abc12 |
+------+-------+
5 rows in set (0.01 sec)

用"+"来匹配多个字符

mysql> select * from info where name regexp 'a+c';
+------+------+
| id   | name |
+------+------+
|    3 | Jack |
+------+------+
1 row in set (0.00 sec)

使用{M}来指定字符串连续出现的次数

mysql> select * from info where name regexp 'a{3}';
+------+--------+
| id   | name   |
+------+--------+
|    5 | aaa    |
|    8 | dadaaa |
|    9 | aaa    |
|   10 | aaabd  |
+------+--------+
4 rows in set (0.00 sec)

使用{M,N}来指定字符串连续出现的次数

mysql> select * from info where name regexp 'ab{1,3}';
+------+-------+
| id   | name  |
+------+-------+
|   10 | aaabd |
|   11 | abc12 |
+------+-------+
2 rows in set (0.00 sec)
隔壁小翔
关注
专栏目录
Note_python_note
Dream It Possible
08-30 2634
python__note import Image def test(): im = Image.open(‘D:\111.png’) (w,h)= im.size im.thumbnail((w//2,h//2)) im.save(‘D:\thumbnail.jpg’,’jpeg’) if name == ‘main‘: test()roun
MySQL-笔记-05.数据更新与单表查询
爱睡觉的欧包
04-08 2090
为了增加查询结果的可读性，可以通过指定列别名的方式改变查询结果的列标题列别名改变的只是查询结果的列标题，并没有改变数据表中的列名。【例12】查询 student表中全部学生的姓名和性别。要求用汉字作为列标题，且去掉重名的学生。SELECT DISTINCT sname AS 姓名, ssex 性别。
xv6—cha 0
liuchenjane的博客
05-13 895
操作系统接口：perating system interface interface features: simple and narrow; 接口：系统调用的集合 进程（process）:each running program has memory containing instructions, data, and a stack. shell: an ordinary program
淡sqlserver对like '%关键词%' 处理时的索引利用问题
答案是肯定的
11-23 1万+
说法一：百分号%通配符前置会让SQL查询不走索引，改走全表扫描。这种说法很流行 结论是错误的 事实上这种说法不太准确 通配符%前置会让SQL查找索引时效率极速下降，但在大多数情况下还是会走索引（不需要全文索引，只要建一个普通的索引就可以了） CREATE NONCLUSTERED INDEX [Ix_索引名] ON [dbo].[wkf_表名]  (  [db_tit
C#基础整理
u013733643的博客
03-07 607
C#基础
postgres中正则表达式及转义
08-22 1452
9.7. 模式匹配 PostgreSQL 提供了三种实现模式匹配的方法：传统 SQL 的 LIKE 操作符、SQL99 新增的 SIMILAR TO 操作符、...
45-MySQL单表2000万数据查询慢解决方案1
08-08
MySQL单表2000万数据查询慢解决方案1 本文主要讨论了如何解决MySQL单表2000万数据查询慢的问题，通过将表分区和使用时间触发器来实现数据的优化。 分区设计 在解决方案中，我们使用的是按照8周将单表分为8个区，...
MySQL学习资料.zip
02-03
6. **分组查询**：`GROUP BY`用于将数据按列值分组，常与聚合函数如`COUNT()`, `SUM()`, `AVG()`, `MAX()`和`MIN()`一起使用。 7. **联接查询**：`JOIN`关键字用于合并两个或更多表中的记录，比如`INNER JOIN`, `...
mysql笔记.zip
01-23
1. 数据库概念：MySQL是一个用于存储和管理数据的系统，它通过SQL（结构化查询语言）进行操作。 2. 安装与配置：在不同操作系统上安装MySQL，包括Windows、Linux和macOS，以及配置MySQL服务器设置，如端口号、用户...
MySQL学习之旅（一）查询一个字段中相同属性的最大值
12-14
此外，熟悉MySQL提供的各种函数和操作，如`UNION`、`GROUP BY`以及聚合函数如`MAX`和`GREATEST`，能够帮助我们更高效地处理复杂的数据查询需求。在学习过程中，建议多做练习，通过实际操作加深理解，这样才能在面对...
[767]MongoDB聚合运算
周小董
02-26 1258
数据 /* 1 */ { "_id" : "2020-02-01", "website_clf" : [ { "source" : "猎云网", "sum_num" : 3880, "day_num" : 11, "time_clf" : { ...
How do I interpret scsi status messages in RHEL like "sd 2:0:0:243: SCSI error: return code = 0x0800
Vic的博客
11-22 5684
Issue What does "return code = 0xNNNNNNNN" mean, for example 0x08000002 within the following: Raw Oct 23 14:56:25 uname kernel: sdas: Current: sense key: Aborted Command Oct 23 14:56:25 uname ke...
04-复杂一点的查询
Binbinhb的博客
12-19 288
1、视图 视图是一个虚拟的表，不同于直接操作数据表，视图是依据SELECT语句来创建的（会在下面具体介绍），所以操作视图时会根据创建视图的SELECT语句生成一张虚拟表，然后在这张虚拟表上做SQL操作。 区别： 《sql基础教程**第2版》用一句话非常凝练的概括了视图与表的区别—“是否保存了实际的数据”。所以视图并不是数据库真实存储的数据表，它可以看作是一个窗口，通过这个窗口我们可以看到数据库表中真实存在的数据。所以我们要区别视图和数据表的本质，即视图是基于真实表的一张虚拟的表，其数据来源均建立在真实
mysql 限定符_MySQL实战第71篇：正则表达式，常用的限定符，在MySQL中运用！
weixin_42110469的博客
01-27 206
欢迎来到MySQL实战第71篇，修炼500篇，就是为了给自己一个肯定。【理论基础】<1>* ：重复零次或更多次<2>+：重复一次或更多次<3>？：重复零次或多次<4>{n}:重复n次<5>{n,}:重复n次或更多次<6>{n,m}:重复n到m次<7>a|b:ab两项之间进行选择<8>[abc]:定义匹配...
docker镜像瘦身&优化
weixin_34162629的博客
05-12 847
原文首发于我的博客: lailin.xyz/post/51252.… 为什么在存储如此便宜的今天我们仍然需要对 Docker 镜像进行瘦身? 小镜像的优点 加速构建/部署 虽然存储资源较为廉价，但是网络 IO 是有限的，在带宽有限的情况下，部署一个 1G 的镜像和 10M 的镜像带来的时间差距可能就是分钟级和秒级的差距。特别是在出现故障，服务被调度到其他节点时，这个时间尤为宝贵。 ...
Python将base64编码转换为图片并存储
热门推荐
JiangNanMax的博客
09-05 6万+
Python将base64编码转换为图片并存储 Python将图片转换为base64编码：https://blog.csdn.net/J__Max/article/details/82424551 涉及到的就是文件的写入和base64库的使用，十分方便 import base64 pic_code = 'iVBORw0KGgoAAAANSUhEUgAAAjwAAAFpCAYAAACY...
mysql约束值_mysql的数据约束
weixin_39940182的博客
02-07 191
数据约束是指对用户插入的数据进行规范，1、默认值CREATE TABLE student(id INT,NAME VARCHAR(20),address VARCHAR(20) DEFAULT '山东淄博')当用户对使用默认值的字段不插入值的时候，就使用默认值。注意：1)对默认值字段插入null是可以的。2)对默认值字段可以插入非null2.、非空CREATE TABLE student(d IN...
MySQL的 where 1=1会不会影响性能
ning的博客
09-07 677
通过分析和实验，我们可以得出结论：在现代的MySQL版本中，使用where 1=1并不会对查询性能产生显著影响，因为MySQL的优化器会对其进行常量折叠优化。然而，选择最合适的方法还需要考虑你的具体使用场景和ORM框架。在任何情况下，编写清晰、可维护的代码始终是最重要的。版权声明：本博客内容为原创，转载请保留原文链接及作者信息。参考文章MySQL中where 1=1真的会影响性能么？MySQL的 where 1=1会不会影响性能？看完官方文档就悟了！
数据库运维实操优质文章文档分享（含Oracle、MySQL等） | 2024年8月刊
最新发布
Era666的博客
09-10 1967
本文为大家整理了墨天轮数据社区2024年8月发布的优质技术文章、文档，主题涵盖Oracle、MySQL、PostgreSQL等主流数据库系统以及国产数据库的技术实操！
MySQL DQL深入学习：查询与实战
"本次课程主要聚焦于MySQL的DQL（Data Query Language）学习，旨在帮助学员掌握如何查询数据库中的数据。课程重点包括SELECT语句的使用，从简单的单表查询到复杂的多表联合查询和嵌套查询。DQL是数据库语言中最核心...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值