1、mysql之select

一、单表查询

数据查询命令：select 字段1(可以对字段进行加减乘除),字段2...... from 表 where 限制条件1 and/or 限制条件1......

如：SELECT sal+100 FROM test_job

slelect 运算符：

+     -     *     /     ==     !=     <>     >=     <=

通配符：

%表示多个字符     _表示一个字符     *表示所有

与或非：

SELECT * FROM test_emp WHERE empno=20162001 AND ename="董事长"

SELECT * FROM test_emp WHERE empno=20165001 OR ename="董事长"

SELECT * FROM test_emp WHERE NOT empno=20161001

在两则之间

SELECT * FROM test_emp WHERE empno BETWEEN 20162001 AND 20164001

SELECT * FROM test_emp WHERE empno>=20162001 AND empno<=20164001

like 匹配一个字符模式，通配符%代表若干个字符，通配符_代表一个字符

SELECT * FROM test_emp WHERE empno LIKE "2016%001"

SELECT * FROM test_emp WHERE empno LIKE "2016_001"

in 匹配列出的值

SELECT * FROM test_emp WHERE empno IN(20161001,20162001)

is null 匹配空格

SELECT * FROM test_emp WHERE mgr IS NULL

distinct 去重复

SELECT deptno FROM test_emp

SELECT DISTINCT deptno FROM test_emp

设置别名 AS

SELECT empno 员工号,ename 姓名,hiredate 入职日期,deptno 部门号 FROM test_emp

SELECT empno AS 员工号,ename AS 姓名,hiredate AS 入职日期,deptno AS 部门号 FROM test_emp

order by 根据多个属性来排序

SELECT empno,ename,mgr,hiredate,deptno,jobno FROM test_emp ORDER BY deptno,jobno (ASC)

SELECT empno,ename,mgr,hiredate,deptno,jobno FROM test_emp ORDER BY deptno,jobno DESC

SELECT empno,ename,mgr,hiredate,deptno,jobno FROM test_emp ORDER BY deptno DESC,jobno DESC

count 统计表中所有的行数或是记录数或是元组数

#统计test_emp表中总员人数

SELECT COUNT(*) FROM test_emp

SELECT COUNT(empno) AS 总人数 FROM test_emp

AVG() 求平均值

SELECT AVG(sal) FROM test_job

sum()求和

SELECT SUM(sal+comm) 月工资总和 FROM test_job

max()：统计最大值

SELECT MAX(sal) FROM test_job

min()：统计最小值

SELECT MIN(sal) FROM test_job

列值拼接函数GROUP_CONCAT()

#拼接职位名称

SELECT GROUP_CONCAT(jobname) FROM test_job

指定分隔符

SELECT GROUP_CONCAT(jobname SEPARATOR '+') FROM test_job

group by...having...

SELECT deptno AS 部门号, COUNT(empno) AS 部门人数 FROM test_emp GROUP BY deptno HAVING COUNT(empno)>2

limit 限制查询结果显示

SELECT * FROM test_emp LIMIT 2

upper()将小写字符转成大写字符

SELECT UPPER("zhang")

lower()将x中的大写字符转成小写字母

SELECT LOWER("ZHANG")

length() 返回字符个数

#在length()函数中规定：一个英文占一个字符，一个数字占一个字符,一个中文字占3个字符

SELECT LENGTH(ename) FROM emp WHERE ename="orlando222为"

char_length()规定：一个中文占一个字符，一个字母和一个数字也占一个字符

SELECT CHAR_LENGTH(job),job FROM emp WHERE empno=1001

SELECT CHAR_LENGTH("你好smith")

replace(x,char1,char2)将x中的char1替换成Char2;大小写字母敏感

SELECT REPLACE(ename,"I","abc"),ename FROM emp WHERE empno=1001

SELECT REPLACE("ZHANG","ZH","w")

concat(x,y):字符拼接函数 --xy

SELECT CONCAT(ename,"hello") FROM emp WHERE empno=1001

SELECT CONCAT(ename,job) FROM emp WHERE empno=1001

SELECT CONCAT("hello","lidong")

curdate():返回当前日期，year-month-day

SELECT CURDATE()

curtime():返回当前时间 hour-min-second

SELECT CURTIME()

now():返回当前的日期和时间

SELECT NOW()

last_day(x)查询日期x所在月份的最后一天

SELECT LAST_DAY("1900-02-01")

SELECT LAST_DAY(20160201)

data_add(x,interval n f):

SELECT DATE_ADD("2016-02-01",INTERVAL 2 YEAR)

SELECT DATE_ADD(20160229,INTERVAL 2 DAY)

SELECT DATE_ADD(20161231,INTERVAL 2 MONTH)

date_format():返回用户指定的日期格式

SELECT DATE_FORMAT("2016-02-01 10:10:10","%Y-%m-%d")

SELECT DATE_FORMAT("2016-02-01 10:10:10","%y")

SELECT DATE_FORMAT("2016-02-01 10:10:10","%Y")

 

SELECT DATE_FORMAT("2016-02-01 10:10:10","%Y-%m-%d %T")

SELECT DATE_FORMAT("2016-02-01 10:10:10","%Y-%m-%d %W")

 

SELECT DATE_FORMAT("2016-02-01 10:10:10","%c")

SELECT DATE_FORMAT("2016-02-01 10:10:10","%m")

SELECT DATE_FORMAT("2016-02-01 10:10:10","%M")

================================================================================

#定义一个课程表,属性：课程编号，课程名称，学分,主键：课程名称,使用表级定义主键

CREATE TABLE class

(

id INT,

NAME VARCHAR(20),

xuefen INT,

PRIMARY KEY(NAME)

)

 

SHOW TABLES

DESC class

DROP TABLE class

SHOW TABLES

#定义一个课程表,属性：课程编号，课程名称，学分,主键：课程名称,使用列级定义主键

CREATE TABLE ach

(

id INT,

NAME VARCHAR(20) PRIMARY KEY,

xuefen INT

)

 

#定义一个成绩表,属性：学号，课程名称，成绩,主键：学号和课程名称

#如果主键由多个属性组成，则必须定义为表级约束

CREATE TABLE chengji

(

id INT,

NAME VARCHAR(20),

score INT,

PRIMARY KEY(id,NAME)

)

================================================================================

#1、查询test_emp表中名字长度超过3个字的员工姓名

#char_length(x):规定一个汉字占一个字符

#length(x):规定一个汉字占3个字符

SELECT ename FROM test_emp WHERE CHAR_LENGTH(ename)>3

SELECT ename FROM test_emp WHERE CHAR_LENGTH(ename)>=4

SELECT ename FROM test_emp WHERE ename LIKE "____%"

 

#2、查询test_emp表中员工‘总经理’入职月份的最后一天日期

SELECT LAST_DAY(hiredate) FROM test_emp WHERE ename="总经理"

 

#3. 查询test_emp表中员工‘总经理’入职前一天的日期

SELECT DATE_ADD(hiredate,INTERVAL -1 DAY) FROM test_emp WHERE ename="总经理"

 

#4、查询test_job表中各个职位的年薪，如果是董事长按15薪统计，总经理按13薪统计

#第一种方式

SELECT

CASE

WHEN jobname="董事长" THEN (sal+comm)*15

WHEN jobname="总经理" THEN (sal+comm)*13

ELSE (sal+comm)*12

END AS 年薪

FROM test_job

 

#第二种方式

SELECT

CASE jobname

WHEN "董事长" THEN (sal+comm)*15

WHEN "总经理" THEN (sal+comm)*13

ELSE (sal+comm)*12

END 年薪

FROM test_job

 

#第三种方式

SELECT (sal+comm)*

CASE

WHEN jobname="董事长" THEN 15

WHEN jobname="总经理" THEN 13

ELSE 12

END AS 年薪

FROM test_job

 

#第四种方式

SELECT (sal+comm)*

CASE jobname

WHEN "董事长" THEN 15

WHEN "总经理" THEN 13

ELSE 12

END AS 年薪

FROM test_job

================================================================================

多表查询：

1、#查询工作地点为北京的员工名称
#用到的表dept  emp
#查询字段emp.ename
#限制条件 loc='北京'
#表之间的依赖关系 emp.deptno=dept.deptno

 

#内联接，方法1

select emp.ename 员工名称 from emp,dept where emp.deptno=dept.deptno and loc='北京'

#内联系，方法2

select emp.ename from emp inner join dept on emp.deptno=dept.deptno where loc='北京'

#左连接

select emp.ename from emp left join dept on emp.deptno=dept.deptno where loc='北京'

#右连接

select emp.ename from emp right join dept on emp.deptno=dept.deptno where loc='北京'

#子查询

select ename from emp where deptno=(select deptno from dept where loc='北京')
 

 

#2、统计每个部门不同职位的员工人数（多表查询）

#用到的表 dept  emp job
#查询字段 dept.dname ,count(emp.jobno)
#限制条件 job.jobno=emp.jobno and emp.deptno=dept.deptno
#表之间的依赖关系

select dept.dname 部门名称,count(emp.jobno) 员工人数 from emp,job,dept where job.jobno=emp.jobno and emp.deptno=dept.deptno
group by emp.deptno