admin 管理员组文章数量: 887006
2. mysql基本查询
一、演示表介绍
1.employees员工表
| 字段名称 | 字段含义 |
|---|---|
| employee_id | 员工编号 |
| first_name | 名 |
| last_name | 姓 |
| 邮箱 | |
| phone_number | 电话号码 |
| job_id | 工种编号 |
| salary | 工资 |
| commission_pct | 奖金率 |
| manager_id | 上级领导的编号 |
| department_id | 部门编号 |
| hiredate | 入职日期 |
2.departments部门表
| 字段名称 | 字段含义 |
|---|---|
| department_id | 部门编号 |
| department_name | 部门名称 |
| manager_id | 部门领导id |
| location_id | 位置编号 |
3.location 位置表
| 字段名称 | 字段含义 |
|---|---|
| location_id | 位置编号 |
| street_address | 街道 |
| postal_code | 右边 |
| city | 成是 |
| state_province | 州/省 |
| country_id | 国家编号 |
4.jobs工种表
| 字段名称 | 字段含义 |
|---|---|
| job_id | 工种编号 |
| job_title | 工种名称 |
| min_salary | 最低工资 |
| max_salary | 最高工资 |
三、基础查询
语法：
select 查询列表 from 表名称;
1.查询所有字段
mysql> use myemployees;
mysql> show tables;
+-----------------------+
| Tables_in_myemployees |
+-----------------------+
| departments |
| employees |
| jobs |
| locations |
+-----------------------+
4 rows in set (0.00 sec)mysql> select * from jobs;
+------------+---------------------------------+------------+------------+
| job_id | job_title | min_salary | max_salary |
+------------+---------------------------------+------------+------------+
| AC_ACCOUNT | Public Accountant | 4200 | 9000 |
| AC_MGR | Accounting Manager | 8200 | 16000 |
| AD_ASST | Administration Assistant | 3000 | 6000 |
| AD_PRES | President | 20000 | 40000 |
| AD_VP | Administration Vice President | 15000 | 30000 |
| FI_ACCOUNT | Accountant | 4200 | 9000 |
| FI_MGR | Finance Manager | 8200 | 16000 |
| HR_REP | Human Resources Representative | 4000 | 9000 |
| IT_PROG | Programmer | 4000 | 10000 |
| MK_MAN | Marketing Manager | 9000 | 15000 |
| MK_REP | Marketing Representative | 4000 | 9000 |
| PR_REP | Public Relations Representative | 4500 | 10500 |
| PU_CLERK | Purchasing Clerk | 2500 | 5500 |
| PU_MAN | Purchasing Manager | 8000 | 15000 |
| SA_MAN | Sales Manager | 10000 | 20000 |
| SA_REP | Sales Representative | 6000 | 12000 |
| SH_CLERK | Shipping Clerk | 2500 | 5500 |
| ST_CLERK | Stock Clerk | 2000 | 5000 |
| ST_MAN | Stock Manager | 5500 | 8500 |
+------------+---------------------------------+------------+------------+
19 rows in set (0.00 sec)2.查询单个字段
mysql> select job_id from jobs;
+------------+
| job_id |
+------------+
| AC_ACCOUNT |
| AC_MGR |
| AD_ASST |
| AD_PRES |
| AD_VP |
| FI_ACCOUNT |
| FI_MGR |
| HR_REP |
| IT_PROG |
| MK_MAN |
| MK_REP |
| PR_REP |
| PU_CLERK |
| PU_MAN |
| SA_MAN |
| SA_REP |
| SH_CLERK |
| ST_CLERK |
| ST_MAN |
+------------+
19 rows in set (0.00 sec)3.查询多个字段
mysql> select job_id,job_title from jobs;
+------------+---------------------------------+
| job_id | job_title |
+------------+---------------------------------+
| AC_ACCOUNT | Public Accountant |
| AC_MGR | Accounting Manager |
| AD_ASST | Administration Assistant |
| AD_PRES | President |
| AD_VP | Administration Vice President |
| FI_ACCOUNT | Accountant |
| FI_MGR | Finance Manager |
| HR_REP | Human Resources Representative |
| IT_PROG | Programmer |
| MK_MAN | Marketing Manager |
| MK_REP | Marketing Representative |
| PR_REP | Public Relations Representative |
| PU_CLERK | Purchasing Clerk |
| PU_MAN | Purchasing Manager |
| SA_MAN | Sales Manager |
| SA_REP | Sales Representative |
| SH_CLERK | Shipping Clerk |
| ST_CLERK | Stock Clerk |
| ST_MAN | Stock Manager |
+------------+---------------------------------+
19 rows in set (0.00 sec)4.查询常量
mysql> select 10010;
+-------+
| 10010 |
+-------+
| 10010 |
+-------+
1 row in set (0.00 sec)mysql> select "hello world";
+-------------+
| hello world |
+-------------+
| hello world |
+-------------+
1 row in set (0.00 sec)mysql> select 'mysql very good';
+-----------------+
| mysql very good |
+-----------------+
| mysql very good |
+-----------------+
1 row in set (0.00 sec)5.查询表达式
mysql> select 100 / 3;
+---------+
| 100 / 3 |
+---------+
| 33.3333 |
+---------+
1 row in set (0.00 sec)
6.查询函数
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.36 |
+-----------+
1 row in set (0.00 sec)mysql> select database();
+-------------+
| database() |
+-------------+
| myemployees |
+-------------+
1 row in set (0.00 sec)mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2020-12-20 11:14:42 |
+---------------------+
1 row in set (0.00 sec)7.起别名
mysql> select now() as 时间;
+---------------------+
| 时间 |
+---------------------+
| 2020-12-20 11:22:30 |
+---------------------+
1 row in set (0.00 sec)mysql> select version() as "版本";
+--------+
| 版本 |
+--------+
| 5.6.36 |
+--------+
1 row in set (0.00 sec)给表中的字段起别名，其中得as也可以省略
mysql> select min_salary as 最少工资,max_salary as 最多工资 from jobs;
+--------------+--------------+
| 最少工资 | 最多工资 |
+--------------+--------------+
| 4200 | 9000 |
| 8200 | 16000 |
| 3000 | 6000 |mysql> select min_salary 最少工资,max_salary 最多工资 from jobs;
+--------------+--------------+
| 最少工资 | 最多工资 |
+--------------+--------------+
| 4200 | 9000 |
| 8200 | 16000 |
| 3000 | 6000 |
| 20000 | 40000 |给表起别名,一般在多表查询的时候用的较多。
mysql> select * from employees as e;
8.去重distinct
查询员工表中涉及到的所有的部门编号.
mysql> select department_id from employees limit 15;
+---------------+
| department_id |
+---------------+
| NULL |
| 10 |
| 20 |
| 20 |
| 30 |
| 30 |
| 30 |
| 30 |
| 30 |
| 30 |
| 40 |
| 50 |
| 50 |
| 50 |
| 50 |
+---------------+
15 rows in set (0.00 sec)这里查询到的数据是重复的，去重的方法就是在字段前面加上去重关键字distinct
mysql> select distinct department_id from employees limit 15;
+---------------+
| department_id |
+---------------+
| NULL |
| 10 |
| 20 |
| 30 |
| 40 |
| 50 |
| 60 |
| 70 |
| 80 |
| 90 |
| 100 |
| 110 |
+---------------+
12 rows in set (0.00 sec)四、条件查询
语法：select 查询列表 from 表名 where 筛选条件;
1.按条件表达式筛选
条件运算符号： > < = != <> >= <=
例子1：查询工资>12000的员工信息
mysql> mysql> select * from employees where salary > 12000;
例子2：查询部门编号不等于90号的员工和部门编号
select last_name,department_id from employees where department_id != 90;
或者
select last_name,department_id from employees where department_id <> 90;2.按照逻辑表达式筛选
逻辑运算符: && || !
and or not
逻辑运算符作用:连接多个表达式
例子1：查询工资在10000到20000之间的员工名、工资以及奖金
select last_name,salary,commission_pct
fromemployees
wheresalary >= 10000 and salary <= 20000;
3.模糊查询
(1)like
例子1：查询员工名称中包含字符a的员工信息
# "%"代表统配符，代表任意长度的任意字符。
# "_" 代表任意单个字符
select *
from employees
wherelast_name like '%a%';
(2)between and
例子：查询员工编号在120到120之间的员工信息
select*
from employees
whereemployee_id between 100 and 120;
(3)in
例子1：查询员工的工种编号是IT_PROG 、AD_VP、AD_PRES中的一个员工名和工种编号
selectlast_name,job_id
fromemployees
wherejob_id in('IT_PROG','AD_VP','AD_PRES');
(4)is null
例子1：查询没有奖金的员工名和奖金率
#在sql中 "=" 是不能判断null值得,所以用is ，is是配合NULL使用得
selectlast_name,commission_pct
fromemployees
wherecommission_pct is null;反之
selectlast_name,commission_pct
fromemployees
wherecommission_pct is not null;
五、排序查询
语法：
select *
from表名
where 查询条件order by 排序字段 [asc | desc];#asc是升序 desc是降序 默认是升序
例子1；查询员工得工资 按照低到高排序
mysql> select * from employees order by salary limit 10;
六、常见函数
1.字符函数
(1) length（）:统计字符得字节大小
如果是英文字符串得话，字符长度 = 字符所有占用空间大小
如果是中文得话，要看字符集设置，在UTF-8中一个汉字占用3个字节
mysql> select length('hello');
+-----------------+
| length('hello') |
+-----------------+
| 5 |
+-----------------+
1 row in set (0.00 sec)mysql> select length('中国你好');
+------------------------+
| length('中国你好') |
+------------------------+
| 12 |
+------------------------+
1 row in set (0.00 sec)(2) concat（）:拼接字符串
mysql> select concat('hello',' ','world') as '字符串拼接结果';
+-----------------------+
| 字符串拼接结果 |
+-----------------------+
| hello world |
+-----------------------+
1 row in set (0.00 sec)拼接字段也是一样得
mysql> select concat(last_name,'_',first_name) from employees limit 3;
+----------------------------------+
| concat(last_name,'_',first_name) |
+----------------------------------+
| K_ing_Steven |
| Kochhar_Neena |
| De Haan_Lex |
+----------------------------------+
3 rows in set (0.00 sec)(3)upper（）:转换为大写
mysql> select upper('hello');
+----------------+
| upper('hello') |
+----------------+
| HELLO |
+----------------+
1 row in set (0.00 sec)(4)lower（）:转换为小写
mysql> select lower('HELLO World');
+----------------------+
| lower('HELLO World') |
+----------------------+
| hello world |
+----------------------+
1 row in set (0.00 sec)(5)substr（）:字符串截取
substr(数据，开始索引，向后截取得长度)
注意：索引是从1开始得
mysql> select phone_number from employees limit 3;
+--------------+
| phone_number |
+--------------+
| 515.123.4567 |
| 515.123.4568 |
| 515.123.4569 |
+--------------+
3 rows in set (0.00 sec)mysql> select substr(phone_number,5) from employees limit 3;
+------------------------+
| substr(phone_number,5) |
+------------------------+
| 123.4567 |
| 123.4568 |
| 123.4569 |
+------------------------+
3 rows in set (0.00 sec)mysql> select substr(phone_number,5,3) from employees limit 3;
+--------------------------+
| substr(phone_number,5,3) |
+--------------------------+
| 123 |
| 123 |
| 123 |
+--------------------------+
3 rows in set (0.00 sec)(6)instr()：取出字串得索引
如果找不到返回0
mysql> select instr('张无忌爱周芷若','张') as ret;
+-----+
| ret |
+-----+
| 1 |
+-----+
1 row in set (0.00 sec)mysql> select instr('张无忌爱周芷若','周芷若') as ret;
+-----+
| ret |
+-----+
| 5 |
+-----+
1 row in set (0.00 sec)mysql> select instr('张无忌爱周芷若','小') as ret;
+-----+
| ret |
+-----+
| 0 |
+-----+
1 row in set (0.00 sec)(7)trim()：去空格函数
mysql> select length(' hello ') as ret;
+-----+
| ret |
+-----+
| 11 |
+-----+
1 row in set (0.00 sec)mysql> select length(trim(' hello ')) as ret;
+------+
| ret |
+------+
| 5 |
+------+
1 row in set (0.00 sec)
(8)replace():字符替换
mysql> select replace('张无忌喜欢周芷若，周芷若也爱张无忌','周芷若','赵敏') as ret;
+-----------------------------------------------+
| ret |
+-----------------------------------------------+
| 张无忌喜欢赵敏，赵敏也爱张无忌 |
+-----------------------------------------------+
1 row in set (0.00 sec)2.数学函数
(1)round()：四舍五入函数
mysql> select round(3.14);
+-------------+
| round(3.14) |
+-------------+
| 3 |
+-------------+
1 row in set (0.00 sec)mysql> select round(3.64);
+-------------+
| round(3.64) |
+-------------+
| 4 |
+-------------+
1 row in set (0.00 sec)#保留小数点后几位，然后在四舍五入
mysql> select round(3.64,1);
+---------------+
| round(3.64,1) |
+---------------+
| 3.6 |
+---------------+
1 row in set (0.00 sec)mysql> select round(3.66,1);
+---------------+
| round(3.66,1) |
+---------------+
| 3.7 |
+---------------+
1 row in set (0.00 sec)
(2)truncate()：直接保留小数点后边几位，不进行四舍五入
mysql> select truncate(3.1415,2);
+--------------------+
| truncate(3.1415,2) |
+--------------------+
| 3.14 |
+--------------------+
1 row in set (0.00 sec)mysql> select truncate(3.1415,3);
+--------------------+
| truncate(3.1415,3) |
+--------------------+
| 3.141 |
+--------------------+
1 row in set (0.00 sec)
3.日期函数
(1)now():系统日期+时间
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2020-12-20 16:08:07 |
+---------------------+
1 row in set (0.00 sec)(2)curdate():只显示日期
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2020-12-20 |
+------------+
1 row in set (0.00 sec)(3)curtime()：只显示时间
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 16:09:47 |
+-----------+
1 row in set (0.00 sec)(4)str_to_date():字符串转日期函数
| 序号 | 格式符号 | 功能 |
|---|---|---|
| 1 | %Y | 四位的月份 |
| 2 | %y | 两位的月份 |
| 3 | %m | 月份（01，02…11,12） |
| 4 | %c | 月份（1，2…11,12） |
| 5 | %d | 日（01，02） |
| 6 | %H | 小时（24小时制） |
| 7 | %h | 小时（12小时制） |
| 8 | %i | 分钟（00，01…59） |
| 9 | %s | 秒(00,01…59) |
例子：在转化的时候，后边要转换的位置和前边的数据位置对应上，最后的结果系统自动会按照’年 月 日’的顺序显示出来
mysql> select str_to_date('6-7 1997','%m-%d %Y') as 'date';
+------------+
| date |
+------------+
| 1997-06-07 |
+------------+
1 row in set (0.00 sec)mysql> select str_to_date('3/1/2020','%m/%d/%Y') as 'date';
+------------+
| date |
+------------+
| 2020-03-01 |
+------------+
1 row in set (0.00 sec)
(5)将日期转换成字符
mysql> select date_format(now(),'%Y年%m月%d日') as ret;
+-------------------+
| ret |
+-------------------+
| 2020年12月20日 |
+-------------------+
1 row in set (0.01 sec)mysql> select date_format(now(),'公元%Y年 %m月 %d日') as ret;
+---------------------------+
| ret |
+---------------------------+
| 公元2020年 12月 20日 |
+---------------------------+
1 row in set (0.01 sec)
4.其它函数
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.36 |
+-----------+
1 row in set (0.00 sec)mysql> select database();
+-------------+
| database() |
+-------------+
| myemployees |
+-------------+
1 row in set (0.00 sec)mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)5.流程控制函数
七、分组函数
功能：用作统计使用，又称为聚合函数或者统计函数
1.sum():求和
mysql> select sum(salary) from employees;
+-------------+
| sum(salary) |
+-------------+
| 691400.00 |
+-------------+
1 row in set (0.00 sec)2.avg():平均值
mysql> select avg(salary) from employees;
+-------------+
| avg(salary) |
+-------------+
| 6461.682243 |
+-------------+
1 row in set (0.00 sec)3.max():最大值
mysql> select max(salary) from employees;
+-------------+
| max(salary) |
+-------------+
| 24000.00 |
+-------------+
1 row in set (0.00 sec)4.min():最小值
mysql> select min(salary) from employees;
+-------------+
| min(salary) |
+-------------+
| 2100.00 |
+-------------+
1 row in set (0.00 sec)5.count():计算个数
用法1： count(字段名) ：统计字段一共多少行
mysql> select count(salary) from employees;
+---------------+
| count(salary) |
+---------------+
| 107 |
+---------------+
1 row in set (0.00 sec)
用法2：count(*) : 每一行中只要一个字段有数据，就算一行
6.注意：
1.sum avg 一般只用来处理数值类型数据
2.max min count 可以处理任何类型的数据
3.以上所有函数都忽略null
八、分组查询
语法：
select 分组函数，字段名(这个字段名还要出现在group by的后边)
from表名
where筛选条件group by 上边的字段名（也就是要分组的字段）
1.简单分组查询
例子1：查询每个工种的最高工资
mysql> select max(salary),job_id from employees group by job_id;
+-------------+------------+
| max(salary) | job_id |
+-------------+------------+
| 8300.00 | AC_ACCOUNT |
| 12000.00 | AC_MGR |
| 4400.00 | AD_ASST |
| 24000.00 | AD_PRES |
| 17000.00 | AD_VP |
| 9000.00 | FI_ACCOUNT |
| 12000.00 | FI_MGR |
| 6500.00 | HR_REP |
| 9000.00 | IT_PROG |
| 13000.00 | MK_MAN |
| 6000.00 | MK_REP |
| 10000.00 | PR_REP |
| 3100.00 | PU_CLERK |
| 11000.00 | PU_MAN |
| 14000.00 | SA_MAN |
| 11500.00 | SA_REP |
| 4200.00 | SH_CLERK |
| 3600.00 | ST_CLERK |
| 8200.00 | ST_MAN |
+-------------+------------+
19 rows in set (0.00 sec)2.带判断的分组查询
例子1：查询邮箱中包含a字符的，每个部门的平均工资
mysql> select avg(salary),department_id,email from employees where email like '%a%' group by department_id;
+--------------+---------------+----------+
| avg(salary) | department_id | email |
+--------------+---------------+----------+
| 7000.000000 | NULL | KGRANT |
| 4400.000000 | 10 | JWHALEN |
| 9500.000000 | 20 | MHARTSTE |
| 4460.000000 | 30 | DRAPHEAL |
| 6500.000000 | 40 | SMAVRIS |
| 3496.153846 | 50 | AFRIPP |
| 6200.000000 | 60 | AHUNOLD |
| 10000.000000 | 70 | HBAER |
| 8535.294118 | 80 | KPARTNER |
| 17000.000000 | 90 | NKOCHHAR |
| 8166.666667 | 100 | DFAVIET |
+--------------+---------------+----------+
11 rows in set (0.00 sec)
本文标签： 2 mysql基本查询
版权声明：本文标题：2. mysql基本查询 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.freenas.com.cn/jishu/1732352381h1533462.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论