MySQL은 단일 테이블 쿼리를 어떻게 구현합니까? MySQL 단일 테이블 쿼리 문
- 青灯夜游앞으로
- 2018-10-17 15:52:521832검색
MySQL은 단일 테이블 쿼리를 어떻게 구현하나요? 이 기사에서는 MySQL 단일 테이블 쿼리 방법을 소개합니다. 도움이 필요한 친구들이 참고할 수 있기를 바랍니다.
먼저 테이블을 만들고 데이터를 삽입합니다.
# 创建表
mysql> create table company.employee5(
id int primary key AUTO_INCREMENT not null,
name varchar(30) not null,
sex enum('male','female') default 'male' not null,
hire_date date not null,
post varchar(50) not null,
job_description varchar(100),
salary double(15,2) not null,
office int,
dep_id int
);
# 插入数据
mysql> insert into company.employee5(name,sex,hire_date,post,job_description,salary,office,dep_id) values
('jack','male','20180202','instructor','teach',5000,501,100),
('tom','male','20180203','instructor','teach',5500,501,100),
('robin','male','20180202','instructor','teach',8000,501,100),
('alice','female','20180202','instructor','teach',7200,501,100),
('tianyun','male','20180202','hr','hrcc',600,502,101),
('harry','male','20180202','hr',NULL,6000,502,101),
('emma','female','20180206','sale','salecc',20000,503,102),
('christine','female','20180205','sale','salecc',2200,503,102),
('zhuzhu','male','20180205','sale',NULL,2200,503,102),
('gougou','male','20180205','sale','',2200,503,102);
# 查看表结构
mysql> desc employee5;
+-----------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | | NULL | |
| sex | enum('male','female') | NO | | male | |
| hire_date | date | NO | | NULL | |
| post | varchar(50) | NO | | NULL | |
| job_description | varchar(100) | YES | | NULL | |
| salary | double(15,2) | NO | | NULL | |
| office | int(11) | YES | | NULL | |
| dep_id | int(11) | YES | | NULL | |
+-----------------+-----------------------+------+-----+---------+----------------+
쿼리 구문
SELECT 字段1,字段2... FROM 表名 WHERE 条件 GROUP BY field HAVING 筛选 ORDER BY field LIMIT 限制条数；
테이블의 모든 데이터 보기
mysql> select * from employee5; +----+-----------+--------+------------+------------+-----------------+----------+--------+--------+ | id | name | sex | hire_date | post | job_description | salary | office | dep_id | +----+-----------+--------+------------+------------+-----------------+----------+--------+--------+ | 1 | jack | male | 2018-02-02 | instructor | teach | 5000.00 | 501 | 100 | | 2 | tom | male | 2018-02-03 | instructor | teach | 5500.00 | 501 | 100 | | 3 | robin | male | 2018-02-02 | instructor | teach | 8000.00 | 501 | 100 | | 4 | alice | female | 2018-02-02 | instructor | teach | 7200.00 | 501 | 100 | | 5 | tianyun | male | 2018-02-02 | hr | hrcc | 600.00 | 502 | 101 | | 6 | harry | male | 2018-02-02 | hr | NULL | 6000.00 | 502 | 101 | | 7 | emma | female | 2018-02-06 | sale | salecc | 20000.00 | 503 | 102 | | 8 | christine | female | 2018-02-05 | sale | salecc | 2200.00 | 503 | 102 | | 9 | zhuzhu | male | 2018-02-05 | sale | NULL | 2200.00 | 503 | 102 | | 10 | gougou | male | 2018-02-05 | sale | | 2200.00 | 503 | 102 | +----+-----------+--------+------------+------------+-----------------+----------+--------+--------+ 10 rows in set (0.00 sec)#🎜 🎜 # Simple QuerySimple Query
mysql> SELECT * FROM employee5;
mysql> SELECT name, salary, dep_id FROM employee5;
DeduplicationDISTINCTmysql> SELECT post FROM employee5;
mysql> SELECT DISTINCT post FROM employee5;
注：不能部分使用DISTINCT，通常仅用于某一字段。
4개의 산술 연산을 통한 쿼리mysql> SELECT name, salary, salary*14 FROM employee5;
mysql> SELECT name, salary, salary*14 AS Annual_salary FROM employee5;
mysql> SELECT name, salary, salary*14 Annual_salary FROM employee5;
디스플레이 정의 형식 CONCAT() 函数用于连接字符串
mysql> SELECT CONCAT(name, ' annual salary: ', salary*14) AS Annual_salary FROM employee5;
조건부 쿼리a、语法
select * from 表名 where 条件
b、比较运算符
大于 小于 大于等于 小于等于 不等于 > < >= <=
!=或<>
c、逻辑运算符
并且 或者 非 and or notd、模糊查询
like % 表示任意多个任意字符
_ 表示一个任意字符
e、范围查询 in 表示在一个非连续的范围内
between...and... 表示在一个连续的范围内
f、空判断
判断空：is null
判断非空：is not null
g、优先级
小括号,not 比较运算符， 逻辑运算符
and比or优先级高，如果同时出现并希望先选or，需要结合()来使用
단일 조건 쿼리mysql> SELECT name,post FROM employee5 WHERE post='hr';
다중 조건 쿼리mysql> SELECT name,salary FROM employee5 WHERE post='hr' AND salary>5000;
키워드 BETWEEN AND 쿼리# 🎜🎜#mysql> SELECT name,salary FROM employee5 WHERE salary BETWEEN 5000 AND 15000; mysql> SELECT name,salary FROM employee5 WHERE salary NOT BETWEEN 5000 AND 15000;
키워드 IS NULL 쿼리
mysql> SELECT name,job_description FROM employee5 WHERE job_description IS NULL; mysql> SELECT name,job_description FROM employee5 WHERE job_description IS NOT NULL; mysql> SELECT name,job_description FROM employee5 WHERE job_description='';
키워드 IN 컬렉션 쿼리
mysql> SELECT name, salary FROM employee5 WHERE salary=4000 OR salary=5000 OR salary=6000 OR salary=9000 ; mysql> SELECT name, salary FROM employee5 WHERE salary IN (4000,5000,6000,9000) ; mysql> SELECT name, salary FROM employee5 WHERE salary NOT IN (4000,5000,6000,9000) ;
키워드 LIKE 퍼지 쿼리
通配符’%’ mysql> SELECT * FROM employee5 WHERE name LIKE 'al%'; 通配符’_’ mysql> SELECT * FROM employee5 WHERE name LIKE 'al___';
쿼리 정렬# 🎜🎜#
단일 열로 정렬mysql> SELECT * FROM employee5 ORDER BY salary;
mysql> SELECT name, salary FROM employee5 ORDER BY salary ASC;
mysql> SELECT name, salary FROM employee5 ORDER BY salary DESC;
여러 열로 정렬mysql> SELECT * FROM employee5 ORDER BY hire_date DESC,salary ASC;
# 先按入职时间，再按薪水排序
mysql> SELECT * FROM employee5 ORDER BY hire_date DESC, salary DESC;
# 先按职位，再按薪水排序
mysql> SELECT * FROM employee5 ORDER BY post, salary DESC;
페이징 쿼리 제한mysql> SELECT * FROM employee5 ORDER BY salary DESC LIMIT 5; //默认初始位置为0
mysql> SELECT * FROM employee5 ORDER BY salary DESC LIMIT 0,5;
mysql> SELECT * FROM employee5 ORDER BY salary DESC LIMIT 3,5; //从第4条开始，共显示5条
집계 함수 쿼리 #🎜 🎜#a、count(*) 表示计算总行数，括号中可以写*和列名 b、max(列) 表示求此列的最大值 c、min(列) 表示求此列的最小值 d、sun(列) 表示求此列的和 e、avg(列) 表示求此列的平均值 mysql> SELECT COUNT(*) FROM employee5; mysql> SELECT COUNT(*) FROM employee5 WHERE dep_id=101; mysql> SELECT MAX(salary) FROM employee5; mysql> SELECT MIN(salary) FROM employee5; mysql> SELECT AVG(salary) FROM employee5; mysql> SELECT SUM(salary) FROM employee5; mysql> SELECT SUM(salary) FROM employee5 WHERE dep_id=101;
그룹 쿼리
# 单独使用GROUP BY关键字分组 mysql> SELECT post FROM employee5 GROUP BY post; 注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数GROUP BY 키워드는 group_concat() 함수와 함께 사용됩니다
# 按照id分组，并查看组内成员 mysql> SELECT dep_id,GROUP_CONCAT(name) FROM employee5 GROUP BY dep_id; mysql> SELECT dep_id,GROUP_CONCAT(name) as emp_members FROM employee5 GROUP BY dep_id;
#🎜 🎜 #GROUP BY는 집계 함수와 함께 사용됩니다
# 按照dep_id 分组， 并计算组内成员工资总和 mysql> SELECT dep_id,SUM(salary) FROM employee5 GROUP BY dep_id; # 按照dep_id分组，并计算组内成员工资平均值 mysql> SELECT dep_id,AVG(salary) FROM employee5 GROUP BY dep_id;정규식 쿼리
mysql> SELECT * FROM employee5 WHERE name REGEXP '^j';
mysql> SELECT * FROM employee5 WHERE salary REGEXP '[5]+.*';
mysql> SELECT * FROM employee5 WHERE salary REGEXP '[5]{2}.*';
관련 권장 사항: mysql 데이터베이스 그래픽 튜토리얼
위 내용은 MySQL은 단일 테이블 쿼리를 어떻게 구현합니까? MySQL 단일 테이블 쿼리 문의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!
성명：
이 기사는 cnblogs.com에서 복제됩니다. 침해가 있는 경우 admin@php.cn으로 문의하시기 바랍니다. 삭제