Python-4.42 数据库单表查询

最新推荐文章于 2022-05-06 18:51:56 发布
最新推荐文章于 2022-05-06 18:51:56 发布
阅读量118 收藏
点赞数
分类专栏： python-4：网络编程进阶&数据库开发 文章标签： python
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_49729516/article/details/114283095
版权

单表查询的语法：

select distinct 字段1,字段2,字段3 from 库.表 
	where 条件
	group by 分组条件
	having 过滤
	order by 排序字段
	limit n;

关键字的执行优先级

from
where
group by
having
select
distinct
order by
limit

练习

company.employee
    员工id      id                  int             
    姓名        emp_name            varchar
    性别        sex                 enum
    年龄        age                 int
    入职日期     hire_date           date
    岗位        post                varchar
    职位描述     post_comment        varchar
    薪水        salary              double
    办公室       office              int
    部门编号     depart_id           int


#创建表
create table employee(
id int not null unique auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一个部门一个屋子
depart_id int
);



#查看表结构
mysql> desc employee;
+--------------+-----------------------+------+-----+---------+----------------+
| Field        | Type                  | Null | Key | Default | Extra          |
+--------------+-----------------------+------+-----+---------+----------------+
| id           | int(11)               | NO   | PRI | NULL    | auto_increment |
| name         | varchar(20)           | NO   |     | NULL    |                |
| sex          | enum('male','female') | NO   |     | male    |                |
| age          | int(3) unsigned       | NO   |     | 28      |                |
| hire_date    | date                  | NO   |     | NULL    |                |
| post         | varchar(50)           | YES  |     | NULL    |                |
| post_comment | varchar(100)          | YES  |     | NULL    |                |
| salary       | double(15,2)          | YES  |     | NULL    |                |
| office       | int(11)               | YES  |     | NULL    |                |
| depart_id    | int(11)               | YES  |     | NULL    |                |
+--------------+-----------------------+------+-----+---------+----------------+



#插入记录
#三个部门：教学，销售，运营
insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龙','male',48,'20101111','teacher',10000,401,1),

('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('丁丁','female',18,'20110312','sale',1000.37,402,2),
('星星','female',18,'20160513','sale',3000.29,402,2),
('格格','female',28,'20170127','sale',4000.33,402,2),

('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3)
;
  • where
mysql> select id,name,age from employee where id > 7;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  8 | 成龙   |  48 |
|  9 | 歪歪   |  48 |
| 10 | 丫丫   |  38 |
| 11 | 丁丁   |  18 |
| 12 | 星星   |  18 |
| 13 | 格格   |  28 |
| 14 | 张野   |  28 |
| 15 | 程咬金 |  18 |
| 16 | 程咬银 |  18 |
| 17 | 程咬铜 |  18 |
| 18 | 程咬铁 |  18 |
+----+--------+-----+
11 rows in set (0.00 sec)


mysql> select name,post,salary from employee where post='teacher' and salary > 8000;
+------------+---------+------------+
| name       | post    | salary     |
+------------+---------+------------+
| alex       | teacher | 1000000.31 |
| wupeiqi    | teacher |    8300.00 |
| jingliyang | teacher |    9000.00 |
| jinxin     | teacher |   30000.00 |
| 成龙       | teacher |   10000.00 |
+------------+---------+------------+
5 rows in set (0.00 sec)


mysql> select name,salary from employee where salary >= 20000 and salary <= 30000;
+--------+----------+
| name   | salary   |
+--------+----------+
| jinxin | 30000.00 |
| 程咬金 | 20000.00 |
+--------+----------+
2 rows in set (0.00 sec)


mysql> select name,salary from employee where salary between 2000 and 3000;
+-----------+---------+
| name      | salary  |
+-----------+---------+
| liwenzhou | 2100.00 |
| 丫丫      | 2000.35 |
+-----------+---------+
2 rows in set (0.00 sec)


mysql> select name,salary from employee where salary < 20000 or salary > 30000;
+------------+------------+
| name       | salary     |
+------------+------------+
| egon       |    7300.33 |
| alex       | 1000000.31 |
| wupeiqi    |    8300.00 |
| yuanhao    |    3500.00 |
| liwenzhou  |    2100.00 |
| jingliyang |    9000.00 |
| 成龙       |   10000.00 |
| 歪歪       |    3000.13 |
| 丫丫       |    2000.35 |
| 丁丁       |    1000.37 |
| 星星       |    3000.29 |
| 格格       |    4000.33 |
| 张野       |   10000.13 |
| 程咬银     |   19000.00 |
| 程咬铜     |   18000.00 |
| 程咬铁     |   17000.00 |
+------------+------------+
16 rows in set (0.00 sec)


mysql> select name,salary from employee where salary not between 20000 and 30000;
+------------+------------+
| name       | salary     |
+------------+------------+
| egon       |    7300.33 |
| alex       | 1000000.31 |
| wupeiqi    |    8300.00 |
| yuanhao    |    3500.00 |
| liwenzhou  |    2100.00 |
| jingliyang |    9000.00 |
| 成龙       |   10000.00 |
| 歪歪       |    3000.13 |
| 丫丫       |    2000.35 |
| 丁丁       |    1000.37 |
| 星星       |    3000.29 |
| 格格       |    4000.33 |
| 张野       |   10000.13 |
| 程咬银     |   19000.00 |
| 程咬铜     |   18000.00 |
| 程咬铁     |   17000.00 |
+------------+------------+
16 rows in set (0.00 sec)


mysql> select * from employee where age = 73 or age = 81 or age = 28;
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| id | name      | sex    | age | hire_date  | post      | post_comment | salary   | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
|  3 | wupeiqi   | male   |  81 | 2013-03-05 | teacher   | NULL         |  8300.00 |    401 |         1 |
|  4 | yuanhao   | male   |  73 | 2014-07-01 | teacher   | NULL         |  3500.00 |    401 |         1 |
|  5 | liwenzhou | male   |  28 | 2012-11-01 | teacher   | NULL         |  2100.00 |    401 |         1 |
| 13 | 格格      | female |  28 | 2017-01-27 | sale      | NULL         |  4000.33 |    402 |         2 |
| 14 | 张野      | male   |  28 | 2016-03-11 | operation | NULL         | 10000.13 |    403 |         3 |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
5 rows in set (0.00 sec)


mysql> select * from employee where age in (73,81,28);
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| id | name      | sex    | age | hire_date  | post      | post_comment | salary   | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
|  3 | wupeiqi   | male   |  81 | 2013-03-05 | teacher   | NULL         |  8300.00 |    401 |         1 |
|  4 | yuanhao   | male   |  73 | 2014-07-01 | teacher   | NULL         |  3500.00 |    401 |         1 |
|  5 | liwenzhou | male   |  28 | 2012-11-01 | teacher   | NULL         |  2100.00 |    401 |         1 |
| 13 | 格格      | female |  28 | 2017-01-27 | sale      | NULL         |  4000.33 |    402 |         2 |
| 14 | 张野      | male   |  28 | 2016-03-11 | operation | NULL         | 10000.13 |    403 |         3 |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
5 rows in set (0.00 sec)


mysql> select * from employee where post_comment is Null;
+----+------------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+
| id | name       | sex    | age | hire_date  | post                       | post_comment | salary     | office | depart_id |
+----+------------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+
|  1 | egon       | male   |  18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL         |    7300.33 |    401 |         1 |
|  2 | alex       | male   |  78 | 2015-03-02 | teacher                    | NULL         | 1000000.31 |    401 |         1 |
|  3 | wupeiqi    | male   |  81 | 2013-03-05 | teacher                    | NULL         |    8300.00 |    401 |         1 |
|  4 | yuanhao    | male   |  73 | 2014-07-01 | teacher                    | NULL         |    3500.00 |    401 |         1 |
|  5 | liwenzhou  | male   |  28 | 2012-11-01 | teacher                    | NULL         |    2100.00 |    401 |         1 |
|  6 | jingliyang | female |  18 | 2011-02-11 | teacher                    | NULL         |    9000.00 |    401 |         1 |
|  7 | jinxin     | male   |  18 | 1900-03-01 | teacher                    | NULL         |   30000.00 |    401 |         1 |
|  8 | 成龙       | male   |  48 | 2010-11-11 | teacher                    | NULL         |   10000.00 |    401 |         1 |
|  9 | 歪歪       | female |  48 | 2015-03-11 | sale                       | NULL         |    3000.13 |    402 |         2 |
| 10 | 丫丫       | female |  38 | 2010-11-01 | sale                       | NULL         |    2000.35 |    402 |         2 |
| 11 | 丁丁       | female |  18 | 2011-03-12 | sale                       | NULL         |    1000.37 |    402 |         2 |
| 12 | 星星       | female |  18 | 2016-05-13 | sale                       | NULL         |    3000.29 |    402 |         2 |
| 13 | 格格       | female |  28 | 2017-01-27 | sale                       | NULL         |    4000.33 |    402 |         2 |
| 14 | 张野       | male   |  28 | 2016-03-11 | operation                  | NULL         |   10000.13 |    403 |         3 |
| 15 | 程咬金     | male   |  18 | 1997-03-12 | operation                  | NULL         |   20000.00 |    403 |         3 |
| 16 | 程咬银     | female |  18 | 2013-03-11 | operation                  | NULL         |   19000.00 |    403 |         3 |
| 17 | 程咬铜     | male   |  18 | 2015-04-11 | operation                  | NULL         |   18000.00 |    403 |         3 |
| 18 | 程咬铁     | female |  18 | 2014-05-12 | operation                  | NULL         |   17000.00 |    403 |         3 |
+----+------------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+
18 rows in set (0.00 sec)


mysql> select * from employee where post_comment is not Null;
Empty set (0.00 sec)


mysql> select * from employee where name like "jin%";
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| id | name       | sex    | age | hire_date  | post    | post_comment | salary   | office | depart_id |
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
|  6 | jingliyang | female |  18 | 2011-02-11 | teacher | NULL         |  9000.00 |    401 |         1 |
|  7 | jinxin     | male   |  18 | 1900-03-01 | teacher | NULL         | 30000.00 |    401 |         1 |
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
2 rows in set (0.00 sec)


mysql> select * from employee where name like "jin___";
+----+--------+------+-----+------------+---------+--------------+----------+--------+-----------+
| id | name   | sex  | age | hire_date  | post    | post_comment | salary   | office | depart_id |
+----+--------+------+-----+------------+---------+--------------+----------+--------+-----------+
|  7 | jinxin | male |  18 | 1900-03-01 | teacher | NULL         | 30000.00 |    401 |         1 |
+----+--------+------+-----+------------+---------+--------------+----------+--------+-----------+
1 row in set (0.00 sec)
  • group by 分组之后，只能取分组的字段，以及每个组聚合结果
#聚合函数
max
min
avg
sum
count

mysql> select post from employee group by post;
+----------------------------+
| post                       |
+----------------------------+
| operation                  |
| sale                       |
| teacher                    |
| 老男孩驻沙河办事处外交大使 |
+----------------------------+
4 rows in set (0.00 sec)


mysql> select post,count(id) as emp_count from employee group by post;
+----------------------------+-----------+
| post                       | emp_count |
+----------------------------+-----------+
| operation                  |         5 |
| sale                       |         5 |
| teacher                    |         7 |
| 老男孩驻沙河办事处外交大使 |         1 |
+----------------------------+-----------+
4 rows in set (0.00 sec)


mysql> select post,max(salary) as emp_count from employee group by post;
+----------------------------+------------+
| post                       | emp_count  |
+----------------------------+------------+
| operation                  |   20000.00 |
| sale                       |    4000.33 |
| teacher                    | 1000000.31 |
| 老男孩驻沙河办事处外交大使 |    7300.33 |
+----------------------------+------------+
4 rows in set (0.00 sec)


#group_concat
mysql> select post,group_concat(name) from employee group by post;
+----------------------------+-------------------------------------------------------+
| post                       | group_concat(name)                                    |
+----------------------------+-------------------------------------------------------+
| operation                  | 张野,程咬金,程咬银,程咬铜,程咬铁                      |
| sale                       | 歪歪,丫丫,丁丁,星星,格格                              |
| teacher                    | alex,wupeiqi,yuanhao,liwenzhou,jingliyang,jinxin,成龙 |
| 老男孩驻沙河办事处外交大使 | egon                                                  |
+----------------------------+-------------------------------------------------------+
4 rows in set (0.00 sec)


mysql> select post,count(id) from employee where age > 50 group by post;
+---------+-----------+
| post    | count(id) |
+---------+-----------+
| teacher |         3 |
+---------+-----------+
1 row in set (0.00 sec)
  • having后跟分组之后的约束条件
mysql> select post,group_concat(name),count(id) from employee group by post having count(id) < 2;
+----------------------------+--------------------+-----------+
| post                       | group_concat(name) | count(id) |
+----------------------------+--------------------+-----------+
| 老男孩驻沙河办事处外交大使 | egon               |         1 |
+----------------------------+--------------------+-----------+
1 row in set (0.00 sec)


mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000;
+-----------+---------------+
| post      | avg(salary)   |
+-----------+---------------+
| operation |  16800.026000 |
| teacher   | 151842.901429 |
+-----------+---------------+
2 rows in set (0.00 sec)
  • order by
mysql> select * from employee order by age asc; #升序
+----+------------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+
| id | name       | sex    | age | hire_date  | post                       | post_comment | salary     | office | depart_id |
+----+------------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+
|  1 | egon       | male   |  18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL         |    7300.33 |    401 |         1 |
|  6 | jingliyang | female |  18 | 2011-02-11 | teacher                    | NULL         |    9000.00 |    401 |         1 |
|  7 | jinxin     | male   |  18 | 1900-03-01 | teacher                    | NULL         |   30000.00 |    401 |         1 |
| 11 | 丁丁       | female |  18 | 2011-03-12 | sale                       | NULL         |    1000.37 |    402 |         2 |
| 12 | 星星       | female |  18 | 2016-05-13 | sale                       | NULL         |    3000.29 |    402 |         2 |
| 15 | 程咬金     | male   |  18 | 1997-03-12 | operation                  | NULL         |   20000.00 |    403 |         3 |
| 16 | 程咬银     | female |  18 | 2013-03-11 | operation                  | NULL         |   19000.00 |    403 |         3 |
| 17 | 程咬铜     | male   |  18 | 2015-04-11 | operation                  | NULL         |   18000.00 |    403 |         3 |
| 18 | 程咬铁     | female |  18 | 2014-05-12 | operation                  | NULL         |   17000.00 |    403 |         3 |
|  5 | liwenzhou  | male   |  28 | 2012-11-01 | teacher                    | NULL         |    2100.00 |    401 |         1 |
| 13 | 格格       | female |  28 | 2017-01-27 | sale                       | NULL         |    4000.33 |    402 |         2 |
| 14 | 张野       | male   |  28 | 2016-03-11 | operation                  | NULL         |   10000.13 |    403 |         3 |
| 10 | 丫丫       | female |  38 | 2010-11-01 | sale                       | NULL         |    2000.35 |    402 |         2 |
|  8 | 成龙       | male   |  48 | 2010-11-11 | teacher                    | NULL         |   10000.00 |    401 |         1 |
|  9 | 歪歪       | female |  48 | 2015-03-11 | sale                       | NULL         |    3000.13 |    402 |         2 |
|  4 | yuanhao    | male   |  73 | 2014-07-01 | teacher                    | NULL         |    3500.00 |    401 |         1 |
|  2 | alex       | male   |  78 | 2015-03-02 | teacher                    | NULL         | 1000000.31 |    401 |         1 |
|  3 | wupeiqi    | male   |  81 | 2013-03-05 | teacher                    | NULL         |    8300.00 |    401 |         1 |
+----+------------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+
18 rows in set (0.00 sec)


mysql> select * from employee order by age desc; #降序
+----+------------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+
| id | name       | sex    | age | hire_date  | post                       | post_comment | salary     | office | depart_id |
+----+------------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+
|  3 | wupeiqi    | male   |  81 | 2013-03-05 | teacher                    | NULL         |    8300.00 |    401 |         1 |
|  2 | alex       | male   |  78 | 2015-03-02 | teacher                    | NULL         | 1000000.31 |    401 |         1 |
|  4 | yuanhao    | male   |  73 | 2014-07-01 | teacher                    | NULL         |    3500.00 |    401 |         1 |
|  8 | 成龙       | male   |  48 | 2010-11-11 | teacher                    | NULL         |   10000.00 |    401 |         1 |
|  9 | 歪歪       | female |  48 | 2015-03-11 | sale                       | NULL         |    3000.13 |    402 |         2 |
| 10 | 丫丫       | female |  38 | 2010-11-01 | sale                       | NULL         |    2000.35 |    402 |         2 |
|  5 | liwenzhou  | male   |  28 | 2012-11-01 | teacher                    | NULL         |    2100.00 |    401 |         1 |
| 13 | 格格       | female |  28 | 2017-01-27 | sale                       | NULL         |    4000.33 |    402 |         2 |
| 14 | 张野       | male   |  28 | 2016-03-11 | operation                  | NULL         |   10000.13 |    403 |         3 |
|  1 | egon       | male   |  18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL         |    7300.33 |    401 |         1 |
|  6 | jingliyang | female |  18 | 2011-02-11 | teacher                    | NULL         |    9000.00 |    401 |         1 |
|  7 | jinxin     | male   |  18 | 1900-03-01 | teacher                    | NULL         |   30000.00 |    401 |         1 |
| 11 | 丁丁       | female |  18 | 2011-03-12 | sale                       | NULL         |    1000.37 |    402 |         2 |
| 12 | 星星       | female |  18 | 2016-05-13 | sale                       | NULL         |    3000.29 |    402 |         2 |
| 15 | 程咬金     | male   |  18 | 1997-03-12 | operation                  | NULL         |   20000.00 |    403 |         3 |
| 16 | 程咬银     | female |  18 | 2013-03-11 | operation                  | NULL         |   19000.00 |    403 |         3 |
| 17 | 程咬铜     | male   |  18 | 2015-04-11 | operation                  | NULL         |   18000.00 |    403 |         3 |
| 18 | 程咬铁     | female |  18 | 2014-05-12 | operation                  | NULL         |   17000.00 |    403 |         3 |
+----+------------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+
18 rows in set (0.00 sec)


mysql> select * from employee order by age asc,id desc; #先按照age升序排，如果age相同则按照id降序排
+----+------------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+
| id | name       | sex    | age | hire_date  | post                       | post_comment | salary     | office | depart_id |
+----+------------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+
| 18 | 程咬铁     | female |  18 | 2014-05-12 | operation                  | NULL         |   17000.00 |    403 |         3 |
| 17 | 程咬铜     | male   |  18 | 2015-04-11 | operation                  | NULL         |   18000.00 |    403 |         3 |
| 16 | 程咬银     | female |  18 | 2013-03-11 | operation                  | NULL         |   19000.00 |    403 |         3 |
| 15 | 程咬金     | male   |  18 | 1997-03-12 | operation                  | NULL         |   20000.00 |    403 |         3 |
| 12 | 星星       | female |  18 | 2016-05-13 | sale                       | NULL         |    3000.29 |    402 |         2 |
| 11 | 丁丁       | female |  18 | 2011-03-12 | sale                       | NULL         |    1000.37 |    402 |         2 |
|  7 | jinxin     | male   |  18 | 1900-03-01 | teacher                    | NULL         |   30000.00 |    401 |         1 |
|  6 | jingliyang | female |  18 | 2011-02-11 | teacher                    | NULL         |    9000.00 |    401 |         1 |
|  1 | egon       | male   |  18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL         |    7300.33 |    401 |         1 |
| 14 | 张野       | male   |  28 | 2016-03-11 | operation                  | NULL         |   10000.13 |    403 |         3 |
| 13 | 格格       | female |  28 | 2017-01-27 | sale                       | NULL         |    4000.33 |    402 |         2 |
|  5 | liwenzhou  | male   |  28 | 2012-11-01 | teacher                    | NULL         |    2100.00 |    401 |         1 |
| 10 | 丫丫       | female |  38 | 2010-11-01 | sale                       | NULL         |    2000.35 |    402 |         2 |
|  9 | 歪歪       | female |  48 | 2015-03-11 | sale                       | NULL         |    3000.13 |    402 |         2 |
|  8 | 成龙       | male   |  48 | 2010-11-11 | teacher                    | NULL         |   10000.00 |    401 |         1 |
|  4 | yuanhao    | male   |  73 | 2014-07-01 | teacher                    | NULL         |    3500.00 |    401 |         1 |
|  2 | alex       | male   |  78 | 2015-03-02 | teacher                    | NULL         | 1000000.31 |    401 |         1 |
|  3 | wupeiqi    | male   |  81 | 2013-03-05 | teacher                    | NULL         |    8300.00 |    401 |         1 |
+----+------------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+
18 rows in set (0.00 sec)


mysql> select distinct post,count(id) as emp_count from employee
    -> where salary > 1000
    -> group by post
    -> having count(id) > 1
    -> order by emp_count desc
    -> ;
+-----------+-----------+
| post      | emp_count |
+-----------+-----------+
| teacher   |         7 |
| operation |         5 |
| sale      |         5 |
+-----------+-----------+
3 rows in set (0.00 sec)
  • limit
mysql> select * from employee limit 3;
+----+---------+------+-----+------------+----------------------------+--------------+------------+--------+-----------+
| id | name    | sex  | age | hire_date  | post                       | post_comment | salary     | office | depart_id |
+----+---------+------+-----+------------+----------------------------+--------------+------------+--------+-----------+
|  1 | egon    | male |  18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL         |    7300.33 |    401 |         1 |
|  2 | alex    | male |  78 | 2015-03-02 | teacher                    | NULL         | 1000000.31 |    401 |         1 |
|  3 | wupeiqi | male |  81 | 2013-03-05 | teacher                    | NULL         |    8300.00 |    401 |         1 |
+----+---------+------+-----+------------+----------------------------+--------------+------------+--------+-----------+
3 rows in set (0.00 sec)


mysql> select * from employee order by salary desc limit 1;
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
| id | name | sex  | age | hire_date  | post    | post_comment | salary     | office | depart_id |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
|  2 | alex | male |  78 | 2015-03-02 | teacher | NULL         | 1000000.31 |    401 |         1 |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
1 row in set (0.00 sec)


mysql> select * from employee limit 0,5;
+----+-----------+------+-----+------------+----------------------------+--------------+------------+--------+-----------+
| id | name      | sex  | age | hire_date  | post                       | post_comment | salary     | office | depart_id |
+----+-----------+------+-----+------------+----------------------------+--------------+------------+--------+-----------+
|  1 | egon      | male |  18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL         |    7300.33 |    401 |         1 |
|  2 | alex      | male |  78 | 2015-03-02 | teacher                    | NULL         | 1000000.31 |    401 |         1 |
|  3 | wupeiqi   | male |  81 | 2013-03-05 | teacher                    | NULL         |    8300.00 |    401 |         1 |
|  4 | yuanhao   | male |  73 | 2014-07-01 | teacher                    | NULL         |    3500.00 |    401 |         1 |
|  5 | liwenzhou | male |  28 | 2012-11-01 | teacher                    | NULL         |    2100.00 |    401 |         1 |
+----+-----------+------+-----+------------+----------------------------+--------------+------------+--------+-----------+
5 rows in set (0.00 sec)

正则表达式

mysql> select * from employee where name like 'jin%';
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| id | name       | sex    | age | hire_date  | post    | post_comment | salary   | office | depart_id |
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
|  6 | jingliyang | female |  18 | 2011-02-11 | teacher | NULL         |  9000.00 |    401 |         1 |
|  7 | jinxin     | male   |  18 | 1900-03-01 | teacher | NULL         | 30000.00 |    401 |         1 |
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
2 rows in set (0.00 sec)


mysql> select * from employee where name regexp '^jin';
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| id | name       | sex    | age | hire_date  | post    | post_comment | salary   | office | depart_id |
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
|  6 | jingliyang | female |  18 | 2011-02-11 | teacher | NULL         |  9000.00 |    401 |         1 |
|  7 | jinxin     | male   |  18 | 1900-03-01 | teacher | NULL         | 30000.00 |    401 |         1 |
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
2 rows in set (0.00 sec)


mysql> select * from employee where name regexp '^jin.*(g|n)$';
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| id | name       | sex    | age | hire_date  | post    | post_comment | salary   | office | depart_id |
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
|  6 | jingliyang | female |  18 | 2011-02-11 | teacher | NULL         |  9000.00 |    401 |         1 |
|  7 | jinxin     | male   |  18 | 1900-03-01 | teacher | NULL         | 30000.00 |    401 |         1 |
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
2 rows in set (0.00 sec)
想我憨憨的一生
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
chatgpt赋能python：Python查表法（TableLookup）在数据处理中的应用
laod112的博客
06-22 1084
查表法是指通过预先生成一张表格，根据输入参数直接查表得到结果的一种数据处理方法。查表法可以有效地减少重复的计算，提升程序的运行效率。在Python中，常用的查表法包括字典（Dictionary）以及Pandas库中的数据框（DataFrame）。本文由chatgpt生成，文章没有在chatgpt生成的基础上进行任何的修改。以上只是chatgpt能力的冰山一角。作为通用的Aigc大模型，只是展现它原本的实力。对于颠覆工作方式的ChatGPT，应该选择拥抱而不是抗拒，未来属于“会用”AI的人。
python mysql 单表查询 多表查询
aug38165的博客
08-31 348
一.外键 变种: 三种关系: 多对一 　　站在左表的角度: 　　(1)一个员工 能不能在 多个部门? 不成立 　　(2)多个员工 能不能在 一个部门? 成立 　　只要有一个条件成立:多 对 一或者是1对多 　　如果两个条件都成立: 多对多 要创建第三张表进行关联多对多 双向的多对一 一对一...
python 表格查询_python单表查询
weixin_39705931的博客
12-03 449
创建表create table employee(id intnotnull unique auto_increment,name varchar(20) notnull,sex enum('male','female') not null default 'male', #大部分是男的age int(3) unsigned not null default 28,hire_date dateno...
单表查询
python编程
10-02 307
水电费
python获得用户表单输入_python – 有关数据输入表单的查询
weixin_39626690的博客
12-03 155
我很好奇我在wakari网站上注册时遇到的问题.我输入了我的用户名,就像abc.def.ghi和所有其他信息一样,并提交了表格(或者至少试图提交！).它抛出了一个错误,上面写着“用户名必须是一个有效的python变量”,因此他们显然在后端使用用户名作为python变量.有人会向我解释这是否是他们正在使用的某种设计方案,其中他们将用户信息存储为python变量或类似的东西.我再次道歉,因为这不是一个...
perl-File-CheckTree-4.42-3.el7.noarch.rpm
11-30
离线安装包，亲测可用
Python库 | Pyro4-4.42-py2.py3-none-any.whl
02-19
**Python库Pyro4-4.42-py2.py3-none-any.whl详解** Pyro4是一个强大的Python远程对象接口库，它允许你通过网络透明地进行对象间通信。这个压缩包`Pyro4-4.42-py2.py3-none-any.whl`包含了Pyro4库的4.42版本，支持...
glpk-4.42.tar.gz
最新发布
03-04
《GNU GLPK 4.42：开源线性与整数规划库详解》 GLPK（GNU Linear Programming Kit）是GNU项目的一部分，它提供了一套用于解决线性规划（LP）、混合整数规划（MIP）和相关问题的工具。GLPK 4.42是该库的一个版本，其...
facebook-android-sdk-4.42.0
08-02
Facebook Android SDK 4.42.0 是一个用于在Android应用中集成Facebook功能的重要开发工具包。这个SDK包含了多个模块，让开发者能够轻松地添加社交元素到他们的应用程序中，如用户登录、分享内容、营销自动化以及与...
PyPI 官网下载 | Pyro4-4.42-py2.py3-none-any.whl
01-07
资源来自pypi官网。 资源全名：Pyro4-4.42-py2.py3-none-any.whl
Python学习笔记十九(MySQL、SQL、查找、单表查询)
DragonFangQy
05-13 399
查找 查找分为单表查询与多表查询 单表查询 查看现有数据表 查看所有数据 -- select * from 表名; 查找指定表的所有数据 select * from article; 查看某些字段 比如我只关心title 字段 或者 我只关心title 字段 和content_num 字段 -- select 字段名【,字段名，字段名...】 fr...
python如何查询数据库_Python操作Mysql数据库进阶篇——查询操作详解（一）
weixin_39516956的博客
11-21 669
前面我们已经介绍了在Python3.x中如何连接一个Mysql数据库，以及怎么样对这个数据库创建一个表，增删改查表里的数据。想必大家对Mysql数据库和简单的sql语句有了一定的了解，其实sql语句博大精深，里面的知识点非常的多，估计介绍一个星期也难以介绍全面。今天我们就来通过Mysql这种普遍使用的关系型数据库来深入了解一下sql语句中比较高级的查询部分。准备工作首先我们在Mysql里建立三张表...
FORM 表单的查询方法
汲水之人
11-10 1273
1、使用:parameter.G_query_find参数； IF (NAME_IN('PO_HEADERS.PO_HEADER_ID') IS NOT NULL) THEN     :parameter.G_query_find := 'TRUE';     app_find.find('CUX_PO_HEADERS_ADD_MESSAGE');     go_block('CUX_
python如何获取前端form表单数据（常用于模糊查询）？
qq_41905051的博客
05-06 2687
python如何获取前端form表单数据？ python模糊查询
python 实现简单查询页面_python实现简易数据库之二——单表查询和top N实现
weixin_39784972的博客
12-16 590
上一篇中，介绍了我们的存储和索引建立过程，这篇将介绍SQL查询、单表查询和TOPN实现。一、SQL解析正规的sql解析是用语法分析器，但是我找了好久，只知道可以用YACC、BISON等，sqlite使用的lemon，捣整了一天没实现，就用了python的正则表达式。1、删除无用的空格、跳格符、换行符等；我们以分号‘;’作为一个sql语句的结束符，在输入分号之前，我们将输入的sql语句串接成一个st...
python实现数据库查询的简单示例
热门推荐
终日而思，不如须臾之所学。
05-11 1万+
【代码】python实现数据库查询的简单示例。
python中查询数据库个数_python数据库单表查询
weixin_39595164的博客
12-04 384
单表查询就是对 MySQL 数据库中的一张数据表中的数据进行查询操作，即单表查询操作。首先我们来介绍简单查询：简单查询只需要通过 SELECT 语句来实现。单表查询的第一个就是查询所有字段，查询所有字段的 SQL 语法有两种：指定所有的字段、使用“*”通配符代替所有字段。查询所有字段时，需要在 SELECT 语句中指定所有的字段名，其使用的 SQL 语法格式如下所示：select column_n...
python3操作mysql数据库表01（封装查询单条、多条数据）
aipoqiu2566的博客
12-05 346
#!/usr/bin/env python# -*- coding:UTF-8 -*- import pymysql# import os'''封装查询单条、多条数据'''# os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'pdbinfo = {"host": '912.168.1.133', "...
python实现数据库查询_通过Python实现mysql查询数据库实例
weixin_39571179的博客
01-15 413
#coding:utf-8'''Created on 2017年10月25日@author: li.liu'''import pymysqldb=pymysql.connect('localhost','root','root','test',charset='utf8')m=db.cursor()'''try:#a=raw_input('请输入sql语句'+'\n')m.execute('sel...
e-studio.pro.v4.42.029a
01-25
e-studio.pro.v4.42.029a是一款专业的办公软件。它为用户提供了一系列功能强大的工具，帮助用户高效地管理和处理办公相关的任务。 首先，e-studio.pro.v4.42.029a具有高级的文档编辑和处理功能。用户可以使用该软件...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值