正文

数据库：数据操作-单表查询

mike-liu  mike-liu  2022-12-16  757

关键词：

一单表查询的语法

select 字段1，字段2....from 表名
                        where  条件
                        group by field
                        having    筛选    
                        order   by  field
                        limit    限制条数

二关键字的执行优先级（重点）

关键字的执行优先级
from
where
group by
having
select
distinct
order by
limit

1.找到表：from

2.拿着where指定的约束条件，去文件/表中取出一条条记录

3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组

4.将分组的结果进行having过滤

5.执行select

6.去重

7.将结果按条件排序：order by

8.限制结果的显式条数

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




# 创建表
mysql> 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_comment varchar(100),
    -> salary double(15,2),
    -> office int,
    -> depart_id int
    -> );
Query OK, 0 rows affected (0.04 sec)

# 查看表结构
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_comment | varchar(100)          | YES  |     | NULL    |                |
| salary       | double(15,2)          | YES  |     | NULL    |                |
| office       | int(11)               | YES  |     | NULL    |                |
| depart_id    | int(11)               | YES  |     | NULL    |                |
+--------------+-----------------------+------+-----+---------+----------------+
9 rows in set (0.01 sec)


#插入记录
#三个部门：教学，销售，运营
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)
;

#简单查询
    SELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id 
    FROM employee;

    SELECT * FROM employee;

    SELECT name,salary FROM employee;

# 避免重复distinct
select distinct post from employee;

# 通过四则运算
select name,salary*12 from employee;

select name,salary*12 as Annual_salary from employee;

select name,salary*12 Annual_salary from employee;


# 定义显式格式
concat（）函数用于连接字符串


mysql> select concat(‘姓名‘,name,‘ 年薪: ‘, salary*12) as Annual_salary from employee;

concat_ws（） : 第一个参数为分隔符
mysql> select concat_ws(‘:‘,name,salary*12) as Annual_salary from employee;

1、查出所有员工的名字，薪资,格式为

<
名字:egon
>
<
薪资:3000
>

mysql> select concat(‘<名字:‘,name,‘>  ‘,‘<薪资:‘,salary,‘>‘) from employee;

2、 查出所有的岗位（去掉重复）
mysql> select distinct depart_id from employee;

3 查出所有员工名字，以及他们的年薪,年薪的字段名为annual_year


mysql> select name, salary*12 annual_salary from employee;

四 where约束

where字句中可以使用：

比较运算符：><>=<=<>!=
between 80 and 100 值在80到100之间
in(80,90,100)值是80或90或100
like ‘egon%’,pattern 可以是%或_,　　%：表示任意多字符；　　_:表示一个字符
逻辑运算符：在多个条件直接可以使用逻辑运算符：and or not

#1:单条件查询
    SELECT name FROM employee
        WHERE post=‘sale‘;

#2:多条件查询
    SELECT name,salary FROM employee
        WHERE post=‘teacher‘ AND salary>10000;

#3:关键字BETWEEN AND
    SELECT name,salary FROM employee 
        WHERE salary BETWEEN 10000 AND 20000;

    SELECT name,salary FROM employee 
        WHERE salary NOT BETWEEN 10000 AND 20000;

#4:关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS)
    SELECT name,post_comment FROM employee 
        WHERE post_comment IS NULL;

    SELECT name,post_comment FROM employee 
        WHERE post_comment IS NOT NULL;

    SELECT name,post_comment FROM employee 
        WHERE post_comment=‘‘; 注意‘‘是空字符串，不是null
    ps：
        执行
        update employee set post_comment=‘‘ where id=2;
        再用上条查看，就会有结果了

#5:关键字IN集合查询
    SELECT name,salary FROM employee 
        WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ;

    SELECT name,salary FROM employee 
        WHERE salary IN (3000,3500,4000,9000) ;

    SELECT name,salary FROM employee 
        WHERE salary NOT IN (3000,3500,4000,9000) ;

#6:关键字LIKE模糊查询
    通配符’%’
    SELECT * FROM employee 
            WHERE name LIKE ‘eg%‘;

    通配符’_’
    SELECT * FROM employee 
            WHERE name LIKE ‘al__‘;

1. 查看岗位是teacher的员工姓名、年龄
2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄
3. 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资
4. 查看岗位描述不为NULL的员工信息
5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪



select name,age from employee where post = ‘teacher‘;
select name,age from employee where post=‘teacher‘ and age > 30; 
select name,age,salary from employee where post=‘teacher‘ and salary between 9000 and 10000;
select * from employee where post_comment is not null;
select name,age,salary from employee where post=‘teacher‘ and salary in (10000,9000,30000);
select name,age,salary from employee where post=‘teacher‘ and salary not in (10000,9000,30000);
select name,salary*12 from employee where post=‘teacher‘ and name like ‘jin%‘;

五分组查询：group by

一什么是分组？为什么要分组？

#1、首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的

#2、分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等

#3、为何要分组呢？
    取每个部门的最高工资
    取每个部门的员工数
    取男人数和女人数

小窍门：‘每’这个字后面的字段，就是我们分组的依据


#4、大前提：
    可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助于聚合函数

二 ONLY_FULL_GROUP_BY

#查看MySQL 5.7默认的sql_mode如下：
mysql> select @@global.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

#！！！注意
ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义，简单的说来，在ONLY_FULL_GROUP_BY模式下，target list中的值要么是来自于聚集函数的结果，要么是来自于group by list中的表达式的值。


#设置sql_mole如下操作(我们可以去掉ONLY_FULL_GROUP_BY模式)：
mysql> set global sql_mode=‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION‘;

！！！SQL_MODE设置！！！

mysql> select @@global.sql_mode;
+-------------------+
| @@global.sql_mode |
+-------------------+
|                   |
+-------------------+
row in set (0.00 sec)

mysql> select * from emp group by post; 
+----+------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+
| id | name | sex    | age | hire_date  | post                       | post_comment | salary     | office | depart_id |
+----+------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+
| 14 | 张野 | male   |  28 | 2016-03-11 | operation                  | NULL         |   10000.13 |    403 |         3 |
|  9 | 歪歪 | female |  48 | 2015-03-11 | sale                       | NULL         |    3000.13 |    402 |         2 |
|  2 | alex | male   |  78 | 2015-03-02 | teacher                    | NULL         | 1000000.31 |    401 |         1 |
|  1 | egon | male   |  18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL         |    7300.33 |    401 |         1 |
+----+------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+
rows in set (0.00 sec)


#由于没有设置ONLY_FULL_GROUP_BY,于是也可以有结果，默认都是组内的第一条记录，但其实这是没有意义的

mysql> set global sql_mode=‘ONLY_FULL_GROUP_BY‘;
Query OK, 0 rows affected (0.00 sec)

mysql> quit #设置成功后，一定要退出，然后重新登录方可生效
Bye

mysql> use db1;
Database changed
mysql> select * from emp group by post; #报错
ERROR 1055 (42000): ‘db1.emp.id‘ isn‘t in GROUP BY
mysql> select post,count(id) from emp group by post; #只能查看分组依据和使用聚合函数
+----------------------------+-----------+
| post                       | count(id) |
+----------------------------+-----------+
| operation                  |         5 |
| sale                       |         5 |
| teacher                    |         7 |
| 老男孩驻沙河办事处外交大使 |         1 |
+----------------------------+-----------+
rows in set (0.00 sec)

三 GROUP BY

单独使用GROUP BY关键字分组
    SELECT post FROM employee GROUP BY post;
    注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数

GROUP BY关键字和GROUP_CONCAT()函数一起使用
    SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post;#按照岗位分组，并查看组内成员名
    SELECT post,GROUP_CONCAT(name) as emp_members FROM employee GROUP BY post;

GROUP BY与聚合函数一起使用
    select post,count(id) as count from employee group by post;#按照岗位分组，并查看每个组有多少人

　　强调

如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义
多条记录之间的某个字段值相同，该字段通常用来作为分组的依据

五小练习：

1. 查询岗位名以及岗位包含的所有员工名字
mysql> select post,group_concat(name) from employee group by post;   
+-----------------------------------------+---------------------------------------------------------+
| post                                    | group_concat(name)                                      |
+-----------------------------------------+---------------------------------------------------------+
| operation                               | 程咬铁,程咬铜,程咬银,程咬金,张野          |
| sale                                    | 格格,星星,丁丁,丫丫,歪歪                      |
| teacher                                 | 成龙,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex |
| 老男孩驻沙河办事处外交大使 | egon                                                    |
+-----------------------------------------+---------------------------------------------------------+

2. 查询岗位名以及各岗位内包含的员工个数
mysql> select post,count(id) from employee group by post;
+-----------------------------------------+-----------+
| post                                    | count(id) |
+-----------------------------------------+-----------+
| operation                               |         5 |
| sale                                    |         5 |
| teacher                                 |         7 |
| 老男孩驻沙河办事处外交大使 |         1 |
+-----------------------------------------+-----------+

3. 查询公司内男员工和女员工的个数

mysql> select sex, count(id) from employee group by sex;
+--------+-----------+
| sex    | count(id) |
+--------+-----------+
| male   |        10 |
| female |         8 |
+--------+-----------+

4. 查询岗位名以及各岗位的平均薪资
mysql> select post,avg(salary) from employee group by post;
+-----------------------------------------+---------------+
| post                                    | avg(salary)   |
+-----------------------------------------+---------------+
| operation                               |  16800.026000 |
| sale                                    |   2600.294000 |
| teacher                                 | 151842.901429 |
| 老男孩驻沙河办事处外交大使 |   7300.330000 |
+-----------------------------------------+---------------+

5. 查询岗位名以及各岗位的最高薪资
mysql> select post,max(salary) from employee group by post;   
+-----------------------------------------+-------------+
| post                                    | max(salary) |
+-----------------------------------------+-------------+
| operation                               |    20000.00 |
| sale                                    |     4000.33 |
| teacher                                 |  1000000.31 |
| 老男孩驻沙河办事处外交大使 |     7300.33 |
+-----------------------------------------+-------------+

6. 查询岗位名以及各岗位的最低薪资
mysql> select post,min(salary) from employee group by post;  
+-----------------------------------------+-------------+
| post                                    | min(salary) |
+-----------------------------------------+-------------+
| operation                               |    10000.13 |
| sale                                    |     1000.37 |
| teacher                                 |     2100.00 |
| 老男孩驻沙河办事处外交大使 |     7300.33 |
+-----------------------------------------+-------------+
7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资

mysql> select sex,avg(salary) from employee group by sex;        
+--------+---------------+
| sex    | avg(salary)   |
+--------+---------------+
| male   | 110920.077000 |
| female |   7250.183750 |
+--------+---------------+

六 having过滤

　having与where不一样的地方在于：

# 执行优先级从高到低：where > group by > having
# 1.where 发生在分组group by之前，因而where中可以有任意字段，但是均对不能使用聚合函数

# 2. having发生在分组group by之后，因而having中可以使用分组的字段，无法直接取到其它字段，可以使用聚合函数
mysql> select @@sql_mode;
+--------------------+
| @@sql_mode         |
+--------------------+
| ONLY_FULL_GROUP_BY |
+--------------------+
row in set (0.00 sec)

mysql> select * from emp where salary > 100000;
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
| 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 |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
row in set (0.00 sec)

mysql> select * from emp having salary > 100000;
ERROR 1463 (42000): Non-grouping field ‘salary‘ is used in HAVING clause

mysql> select post,group_concat(name) from emp group by post having salary > 10000;#错误，分组后无法直接取到salary字段
ERROR 1054 (42S22): Unknown column ‘salary‘ in ‘having clause‘
mysql> select post,group_concat(name) from emp group by post having avg(salary) > 10000;
+-----------+-------------------------------------------------------+
| post | group_concat(name) |
+-----------+-------------------------------------------------------+
| operation | 程咬铁,程咬铜,程咬银,程咬金,张野 |
| teacher | 成龙,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex |
+-----------+-------------------------------------------------------+
rows in set (0.00 sec)

　　小练习

1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
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 |
+-----------------------------------------+--------------------+-----------+

2. 查询各岗位平均薪资大于10000的岗位名、平均工资
mysql> select post,avg(salary) from employee  group by post having avg(salary)>10000;
+-----------+---------------+
| post      | avg(salary)   |
+-----------+---------------+
| operation |  16800.026000 |
| teacher   | 151842.901429 |
+-----------+---------------+

3. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
mysql> select post,avg(salary) from employee  group by post having avg(salary) between 10000 and 20000;
+-----------+--------------+
| post      | avg(salary)  |
+-----------+--------------+
| operation | 16800.026000 |
+-----------+--------------+

七查询排序：order by

按单列排序
    SELECT * FROM employee ORDER BY salary;
    SELECT * FROM employee ORDER BY salary ASC;
    SELECT * FROM employee ORDER BY salary DESC;

按多列排序:先按照age排序，如果年纪相同，则按照薪资排序
    SELECT * from employee
        ORDER BY age,
        salary DESC;

　　小练习

1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序
mysql> select * from employee order by age,hire_date desc;

2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列

mysql> select post,avg(salary) from employee group by post having avg(salary)>10000 order by avg(salary) asc; 
+-----------+---------------+
| post      | avg(salary)   |
+-----------+---------------+
| operation |  16800.026000 |
| teacher   | 151842.901429 |
+-----------+---------------+

3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列
mysql> select post,avg(salary) from employee group by post having avg(salary)>10000 order by avg(salary) desc;
+-----------+---------------+
| post      | avg(salary)   |
+-----------+---------------+
| teacher   | 151842.901429 |
| operation |  16800.026000 |
+-----------+---------------+

八限制查询的记录数：limit

示例：
    SELECT * FROM employee ORDER BY salary DESC 
        LIMIT 3;                    #默认初始位置为0 

    SELECT * FROM employee ORDER BY salary DESC
        LIMIT 0,5; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条

    SELECT * FROM employee ORDER BY salary DESC
        LIMIT 5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条

1. 分页显示，每页5条
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                                 |              | 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 |
+----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
rows in set (0.00 sec)

mysql> select * from  employee limit 5,5;
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| 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 |
|  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 |
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
rows in set (0.00 sec)

mysql> select * from  employee limit 10,5;
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| id | name      | sex    | age | hire_date  | post      | post_comment | salary   | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| 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 |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
rows in set (0.00 sec)

九使用正则表达式查询

SELECT * FROM employee WHERE name REGEXP ‘^ale‘;

SELECT * FROM employee WHERE name REGEXP ‘on$‘;

SELECT * FROM employee WHERE name REGEXP ‘m2‘;


小结：对字符串匹配的方式
WHERE name = ‘egon‘;
WHERE name LIKE ‘yua%‘;
WHERE name REGEXP ‘on$‘;

　　小练习

查看所有员工中名字是jin开头，n或者g结果的员工信息
select * from employee where name regexp ‘^jin.*[gn]$‘;

数据库单表查询-简单筛选查询(代码片段)

数据库单表查询-简单筛选查询本文关键字：数据库、数据查询语言、DQL之前我们已经了解了SQL语言的分类，可以划分为：DDL（数据定义语言）、DML（数据操纵语言）、DQL（数据查询语言）、DCL（数据控制语言）、TPL（事务处理... 查看详情

hibernate学习---单表查询

我们都知道SQL是非常强大的，为什么这么说呢？相信学过数据库原理的同学们都深有体会，SQL语句变化无穷，好毫不夸张的说可以实现任意符合我们需要的数据库操作，既然前面讲到Hibernate非常强大，所以Hibernate也是能够实现SQL... 查看详情

mysql单表查询进阶(代码片段)

...可以根据开发的需求，设计高效的查询操作，把数据库中存储的数据展示给用户。文章目录前言1.基础查询1.1基础查询语法1.2基础查询练习2.条件查询2.1条件查询语法2.2条件查询练习3.排序查询3.1排序查询语法3.2排序查询... 查看详情

django模型层：单表多表操作f与q查询(代码片段)

DJango模型层单表操作使用有几步：settings.py中配置连接数据库的地址，端口在init.py中使用pymysql在models.py里写类，一个类对应一个表，写属性数据库迁移命令，第一个命令是纪录，第二个命令才是真正的数据库同步导入models到views... 查看详情

数据库---37行（记录）操作单表查询

一.插入数据 inset　　① insertinto表名（字段1，字段2，...）values(值1，值2，...);　　　指定字段插入数据，插入的值要和你之前的字段相匹配　　② insert into表名 values(值1，值2，....);　　　不指定字段时，就按... 查看详情

查询语句select[单表操作](代码片段)

...单表简单查询1、单表操作&别名的使用1--查询表的所有数据：select*from表名;通配符*代表所有2select*fromemp;1--查询表中指定字段的值：select字段名1,字段名2,...from表名;2selectempno,ename,job,sal,hiredate,commfromemp;1--查询结果中的字段使用... 查看详情

django模型层：单表操作(代码片段)

...ionalMapping，简称ORM）模式是一种为了解决面向对象与关系数据库存在的互不匹配的现象的技术查询数据层次图解：如果操作mysql，ORM是在pymysq之上又进行了一层封装MVC或者MTV框架中包括一个重要的部分，就是ORM，它实现了数... 查看详情

mysql单表查询

...行过滤，如何使用ORDERBY子句查询概述：使用数据库保存数据，我们对数据库的操作主要是增，删，改，查操作，其中从数据库中查询数据更为基础，使用不同的查询方式，具有不同的查询... 查看详情

数据库——单表查询

一、简单查询select语句：指定所有字段：select字段1,字段2,···from 表名;使用星号(*)通配符代替所有字段：select*from表名; (一般情况下最好不使用星号通配符，虽然查询数据多但是效率低)查询指... 查看详情

零基础学mysql--数据库最常用的操作查询基础篇--单表查询(代码片段)

目录📔一、最简单的select语句1️⃣基本语法2️⃣基本练习3️⃣补充说明(1)使用表达式对查询的列进行运算(2)在select语句中可使用as语句取别名📕二、使用where子句进行过滤查询1️⃣基本语法2️⃣在where子句中经常使用... 查看详情

数据库原理-数据单表查询

格式：select[all|distinct]<目标列表达式>,[目标列]...from<表名或视图名>[,<表名或视图名>|(seslect语句)[as]<别名>][where<条件名>][groupby<列名1>[having<条件表达式>]][orderby<列名1>[asc|desc]]单表查询查看详情

数据库之表查询，单表多表，子查询

基本查询语句及方法 from where groupby having distinct orderby limit ....连表 innerjoin leftjoin rightjoin union子查询书写顺序 selectid,namefromempwhereid>3andid<6;执行顺序 from#确定到底是哪站表 where#根据过来条件筛选数据 s 查看详情

数据库之单表查询(代码片段)

#创建表createtableemployee(idintnotnulluniqueauto_increment,namevarchar(20)notnull,sexenum(‘male‘,‘female‘)notnulldefault‘male‘,#大部分是男的ageint(3)unsignednotnulldefault28,hire_datedatenotnull,postvarchar(5 查看详情

mybatis单表操作实现完全java代码封装

之前在项目中用到mybtis操作数据库时都是手动写sql,对于我这种sql水平不是很好地人来说痛苦死了；动态查询的sql我表示到现在还不会写呀！还好，利用数据库表反向生成的工具可以帮我解决大部分的sql;(mybatisgenerator你懂的)首先... 查看详情

联合查询的select选项和单表查询的select选项有啥区别?

...的SELECT选项的主要区别是，联合查询可以从多个表中查询数据，而单表查询只能从一个表中查询数据。在联合查询中，SELECT选项可以用来指定要从哪些表中查询数据，以及查询出来的数据应该包含哪些列。例如，如果你要在表A... 查看详情

djangoorm操作必知必会13条单表查询(代码片段)

...Person.objects.get(pk=101)#获取不到或者多个就报错获取某一条数据对象ret=models.Person.objects.filter(name=‘alex‘)#QuerySet对象列表满足条件ret=models.Person.objects.exclude(name=‘alex‘)#满足条件后取反--反选对象列表ret=models.Person.objects.values()#Quer... 查看详情

mysql|总结|常用的查询语句(单表查询)(代码片段)

1.查询单表全部　　select*from数据表名;2.查询单表中一个或者多个字段　　select字段1,字段2from数据表名;3.查询单表中的指定信息　　select*from数据表名where属性=.....；　　　查询所有红色的产品号SELECTProductNumberfromSalesLT.ProductWHERECo... 查看详情

头歌mysql数据库实训答案有目录(代码片段)

头歌MySQL数据库答案特别感谢黄副班、小青提供代码，有问题联系公众号【学思则安】留言更正其他作业链接数据库1-MySQL数据定义与操作实战MySQL数据库-初识MySQLMySQL数据库-数据库和表的基本操作（一）MySQL数据库-数... 查看详情