SQL常用语法

查询数据库

show databases;
显示所有数据库
show tables;
显示所有数据库中的表

增删查改

INSERT

INSERT INTO 表名 （字段名1，字段名2，字段名3）VALUES ('值1','值2','值3');

UPDATE

UPDATE 表名 SET hospitalname='***', username='***' where id='***';

DELECT

delect from 表名 where  id='***';

SELECT

基本语法

select * from table

排序（order by）

默认不加desc，从小到大
加desc从大到小

select birthday from sys_doctor_info ORDER BY birthday desc

数据分页显示

select birthday from sys_doctor_info ORDER BY birthday desc LIMIT 10 offset 0

从第0条开始显示10条数据，第一页

select birthday from sys_doctor_info ORDER BY birthday desc LIMIT 10 offset 10

从第11条开始显示10条数据，第二页

排序和分页显示组合

select birthday from sys_user_doctor_info ORDER BY birthday desc LIMIT 10 offset 10

去重

select distinct  字段 from table

where

select 字段 from table where a=1
LIKE ‘%字符%’
<> 不等于，< 小于，> 大于，=等于
where price BETWEEN 10 AND 20;
where price IN (10,20,30)
and or not
举例：

select * from user_doctor_info  where idNumber LIKE '%1000%' and gender <> 0
SELECT * from user_doctor_info where username <> ''
两个select组合查询
select * from user_doctor_info  where idNumber IN(SELECT * from table)

where 和order by desc 和limit offset

select birthday from sys_user_doctor_info where gender =2 ORDER BY birthday desc LIMIT 10 offset 10

聚合查询

基本语法：group by 字段 having 条件
函数：count,min,max,sum,avg

SELECT count(*) from user_doctor_info where flagDoctorStatus=1
SELECT min(birthday) from user_doctor_info
SELECT max(birthday) from user_doctor_info
SELECT sum(doctorPower) from user_doctor_info where gender=2 and flagDoctorStatus=1
SELECT AVG(doctorPower) from user_doctor_info where gender=2 and flagDoctorStatus=1

select count(gender),gender from sys_user_doctor_info  GROUP BY gender having gender=1

group by 和 having in

select count(gender),gender from sys_user_doctor_info  GROUP BY gender having gender in (1,2)

where和group by having 怎么结合

select count(*),hospitalInfoCode from sys_user_doctor_info where gender=2 GROUP BY hospitalInfoCode HAVING hospitalInfoCode='***' 

LEFT JOIN ON

左连接：左表中独特的所有数据和两个表的交际
方法1：

select userName,birthday,hospitalName,departmentName from sys_user_doctor_info a LEFT JOIN hospital_info b on a.hospitalInfoCode=b.hospitalInfoCode LEFT JOIN hospital_department c on a.departmentId=c.hospitalDepartmentId

JOIN ON
内连接，两个表的交际
方法2：

select userName,birthday,hospitalName,departmentName from sys_user_doctor_info a LEFT JOIN (hospital_info b ,hospital_department c)on a.hospitalInfoCode=b.hospitalInfoCode and a.departmentId=c.hospitalDepartmentId