mysql1250,MySQL-语法-语法使用示例

添加表中测试数据

-- 创建员工表，先在数据库里面创建该表，为了后面做测试用

CREATE TABLE `dept` (

`department_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键，部门编码',

`dname` varchar(14) DEFAULT NULL COMMENT '部门名称',

`location` varchar(13) DEFAULT NULL COMMENT '部门城市地址',

PRIMARY KEY (`department_id`)

) ENGINE=InnoDB;

CREATE TABLE `employee` (

`employee_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',

`c_name` varchar(20) DEFAULT NULL COMMENT '员工中文名',

`e_name` varchar(20) DEFAULT NULL COMMENT '员工英文名',

`hiredate` date DEFAULT NULL COMMENT '雇佣日期，入职日期',

`salary` int(11) DEFAULT NULL COMMENT '薪水',

`comm` int(11) DEFAULT NULL COMMENT '奖金',

`job_id` int(11) DEFAULT NULL COMMENT '所属工种',

`department_id` int(11) NOT NULL COMMENT '部门编号',

`manager_id` int(11) DEFAULT NULL COMMENT '直接领导编号',

PRIMARY KEY (`employee_id`)

) ENGINE=InnoDB;

-- 表中插入数据

insert into dept values(10,'财务部','北京');

insert into dept values(20,'研发部','上海');

insert into dept values(30,'销售部','广州');

insert into dept values(40,'行政部','深圳');

insert into dept values(50,'人力资源','惠州');

-- 表中插入数据

insert into employee values(1,'刘一','liuyi','1980-12-17',7902,800,1,10,2);

insert into employee values(2,'陈二','chener','1981-02-20',7698,1600,3,30,3);

insert into employee values(3,'张三','zhangsan','1981-02-22',7698,1250,5,30,4);

insert into employee values(4,'李四','lisi','1981-04-02',7839,2975,2,20,5);

insert into employee values(5,'王五','wangwu','1981-09-28',7698,1250,1,40,0);

insert into employee values(6,'赵六','zhaoliu','1981-05-01',7839,2850,3,50,5);

表查询

distinct(去重)

limit(分页查询)

offset(跳过多少条)

UNION 和 UNION ALL(联合查询)

like(模糊查询)

where、between、in、or、and条件关键字

order by (asc升序、desc降序排序)

group by (分组查询)

having 关键字

case(流程控制)

-- distinct(去重)

select distinct 字段名 from 表名;

-- limit(初始记录行的偏移量是 0(而不是 1),第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。)

select * from 表名 limit 5,10; -- 检索记录行6-15

-- offset(跳过多少条)

selete * from employee limit 2 offset 1;

+----+----------+-------+--------------+------+

| 2 | lisi | 12000 | 40 | 90 |

| 3 | wangwu | 0 | 50 | 0 |

+----+----------+-------+--------------+------+

-- 注意：

-- 1.数据库数据计算是从0开始的

-- 2.offset X是跳过X个数据，limit Y是选取Y个数据

-- 3.limit X,Y 中X表示跳过X个数据，读取Y个数据

--union 和union all(union all是直接连接，取到得是所有值，记录可能有重复 union 是取唯一值，记录没有重复。)

-- UNION 的语法如下：

[SQL 语句 1]

UNION

[SQL 语句 2]

-- UNION ALL 的语法如下：

[SQL 语句 1]

UNION ALL

[SQL 语句 2]

-- UNION全连接查询，把部门表和员工表的所有数据都查出来，若有两个表都有匹配数据的就显示匹配数据，若其中有一个表在另一个表中没有匹配数据的输就显示为null

select e.ename,d.dname

FROM employee e

left JOIN dept d

ON e.department_id= d.department_id

UNION

select e.ename,d.dname

FROM employee e

right JOIN dept d

ON e.department_id= d.department_id;

+--------+----------+

| ename | dname |

+--------+----------+

| 刘一 | 财务部 |

| 陈二 | 销售部 |

| 张三 | NULL |

| 李四 | 研发部 |

| 王五 | 行政部 |

| 赵六 | 人力资源 |

+--------+----------+

-- 模糊查询：like，%标识匹配任意哥字符，_表示匹配一个字符

-- 查询employee表里面名字含有豪字的员工的全部信息

select * form employee where c_name like '%豪%';

-- 查询员工名中第三个字母为a，第五个字母为b的员工信息;

select * from employee where c_name like '__达_法%';

-- 当查询的信息信息看里面还有_这样的特殊字符;

select * from employee where c_name like '_\_%';

-- 查询工资在5000到6000之间的员工信息;

select * from employee where salary between 5000 and 6000;

-- in、or、and关键字

select * from dept where location in ('北京','上海');

select * from dept where location ='北京' or location ='上海' ;

select * from employee where (department_id=30 or department_id=40) and salary >3000;

-- 选择工资不在3000到5000的员工的姓名和工资，按工资降序

select name, salary, department_id from employees where salary not between 3000 and 5000 order by salary desc;

-- 查询每个部门的员工个数

select count (*) , department_ id from employee group by department_ id;

-- 给30号部门的增加500，40号部门增加1000，50号部门增加1500

select *, (

case department_id

when 30 then

salary+500

when 40 then

salary+1000

when 50 then

salary+1500

else salary

end

) '涨后工资'

from

employee;

-- 显示员工的薪资等级

select *, (

case

when salary >=7900 then

'高薪资'

when salary >=7800 then

'中等薪资'

when salary >=7700 then

'低薪资'

else

'太难了'

end

) '薪资等级'

from

employee

order by salary desc;

-- 语法格式

select 字段1,字段2,字段3 from 表名 [where 筛选条件] [group by 分组] [having 筛选条件1] [order by 排序列表]

mysql字符串函数

concat()：拼接字符串

substr()：截取字符串

instr()：获取子串第一次出现的索引

lpad()：左边以指定字符填充到指定长度

rpad()：右边以指定字符填充到指定长度

upper()：转换为大写

lover()：转换为小写

replace()：替换函数

length()：获取字节长度

trim()：去掉字符串前后空格

-- 将英文名全部转换大写和全部转换为小写，然后进行拼接。

select concat(upper(e_name) ,lower(e_name)) from employee;

-- substr，注意:索引从1开始，截取从指定索引处后面所有字符

select substr('欢迎关注非科班的科班，带你一起提升代码内功',7) str ;

-- 从指定索引截取指定长度的字符串substr(str,num1,num2)第二个参数时索引、第三个参数是指定的长度。

select substr('欢迎关注非科班的科班，带你一起提升代码内功',5,6) str;

-- 姓名中首字符大写，其他字符小写然后用_拼接，显示出来

SELECT CONCAT (UPPER (SUBSTR(last_ name,1,1)),'_' ,LOWER (SUBSTR(last_ name,2))) out_put

FROM employees;

-- instr返回子串在指定字符串第一次出现的素引，如果找不到返回0

select instr('欢迎关注非科班的科班，带你一起提升代码内功', '科班') as str;

-- length获取字符串长度、trim()去掉字符串前后的空字符串

select length(trim(" 非科班的科班 ")) as str;

-- lpad用指定的字符实现左填充指定长度

select lpad('非科班的科班',9,'*') as str;

-- rpad用指定的字符实现右填充指定长度

select rpad('非科班的科班',9,'*') as str;

-- replace 替换

select replace('非科班的科班', '科班','javaboy') as str;

mysql字符串函数

now：返回当前的日期和时间

year：返回年份

month：返回月份

monthname：以英文形式返回月份

day：返回日

hour：小时

mimute：分钟

second：秒

datediff：返回两个日期相差的天数

date_format：将时间日期转换为字符串

str_to_date：将字符转换成日期

curdate：返回当前日期，不包含时间

curtime：返回当前时间，不包含日期

-- now()返回当前系統日期+时间

select now();

-- curdate返回当前系统日期，不包含时间，curtime返回当前时间，不包含日期

select curdate();

select curtime();

-- 可以获取指定的部分，年、月、日、小时、分钟、秒

select year(now())年;

select year('2020-1-1') 年;

select year(hiredate) 年 from employee;

select month(now()) 月;

select monthname(now()) 月;

-- %Y 四位的年份

-- %y 2位的年份

-- %m 2位的月份( 01,02...11,12)

-- %c 1位的月份( 1,2...11,12)

-- %d 日( 01,02,.. )

-- %H 24小时制的小时

-- %h 12小时制的小时

-- %i 分钟( 00,01...59)

-- %s 秒( 00,01...59)

-- str_to_date:将字符串转换为指定格式的日期对象

select str_to_date('2-28-2020','%m-%d-%Y');

-- 查询入职日期为2020-2-28的员工信息

select * from employee where hiredate = '2020-2-28' ;

-- date_format:将日期转换成字符

select date_format('2020/02/28','%Y年%m月 %d日')；

select date_format(now(), '%y年%m月%d日') as date ;

-- 查询工资大于7800的员工的中文名、入职日期

select c_name 中文名, date_format(hiredate,'%m月/%d日 %y年') 入职日期 from employee where salary>7800;

mysql数学函数

ceil：中文意思表示天花板，表示向上取整

floor：中文意思表示地板，表示向下取整

round：对数字取四舍五入

rand：随机取0-1之间的所及小数

mod：取模运算

truncate：截取，类似于字符串的substr的用法

-- ceil表示向上取整。整数：返回本身。小数：返回的是与该数相邻并比该数大的整数

select ceil(2) ;

select ceil(2.21) ;

select ceil(-2) ;

select ceil(-2.1) ;

-- floor向下取整。整数：返回本身。小数：返回的是与该数相邻并比该数大的整数

select floor(2) ;

select floor(2.21) ;

select floor(-2) ;

select floor(-2.1) ;

-- rand在0-1之间随机去一个随机数

select round(rand()*10) ; --取一个0-10的随机整数

-- round对数字取四舍五入

select round(-1.55) ;

select round(1.567,2) ;

-- mod取余运算

select mod(3,2) ;

select 3%2;

-- truncate截取，第一个是要截取的数字，第二个是要截取的位数，从小数点后开始算

select truncate(2.3345534,1) ;

创建视图和索引

视图view，创建，查询视图，删除视图

索引index，创建索引，删除索引

语法格式如下：

create view [新字段名1,新字段名2,新字段名3,新字段名4,....] as

--创建一个员工的视图

create view v_employee (id,name,sal,department,hiredate)

as select employee_id,c_name,salary,department_id,hiredate

from employee ;

--查询视图

select * from v_employee where sal>7800;

--修改视图

alter view v_employee as select * from employee where salary>7800;

--删除视图

drop view v_employee ;

--基于多表创建视图

create view v_test as select e.c_name,e.hiredate,e.salary,d.dname,d.location from employee e,dept d where e.department_id=d.department_id;

select * from v_test

+--------+------------+--------+--------------+----------+

| c_name | hiredate | salary | dname | location |

+--------+------------+--------+--------------+----------+

| 刘一 | 1980-12-17 | 7902 | 财务部 | 北京 |

| 陈二 | 1981-02-20 | 7698 | 销售部 | 广州 |

| 张三 | 1981-02-22 | 7698 | 销售部 | 广州 |

| 李四 | 1981-04-02 | 7839 | 研发部 | 上海 |

| 王五 | 1981-09-28 | 7698 | 行政部 | 深圳 |

| 赵六 | 1981-05-01 | 7839 | 人力资源 | 惠州 |

+--------+------------+--------+--------------+----------+

--(1)使用alter table 语句创建索性,使用场景是在表创建完毕之后再添加索引。语法如下：

alter table 表名 add 索引类型 (unique,primary key,fulltext,index)[索引名](字段名)

-- 普通索引(当column_list有多个的时候使用逗号隔开)

alter table table_name add index index_name (column_list) ;

-- 唯一索引(当column_list有多个的时候使用逗号隔开)

alter table table_name add unique (column_list) ;

--主键索引(当column_list有多个的时候使用逗号隔开)

alter table table_name add primary key (column_list) ;

-- (2)使用create index语句对表增加索引,create index可用于对表增加普通索引或UNIQUE索引，可用于建表时创建索引。

create index index_name on table_name(username(length));

-- create只能添加这两种索引;

create index index_name on table_name(column_list);

create UNIQUE index index_name on table_name(column_list);

create index index_employee on employee(salary,hiredate,c_name);

-- (3)删除索引,删除索引可以使用ALTER TABLE或DROP INDEX语句来实现。

drop index index_name on table_name ;

alter table table_name drop index index_name ;

alter table table_name drop primary key ;

视图

是一个虚拟表(非真实存在)，其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，并可以将其当作表来使用，使用视图时，将其当作表进行操作即可，由于视图是虚拟表，所以无法使用其对真实表进行创建、更新和删除操作，仅能做查询用。

索引

数据库中将数据整齐的排列在磁盘阵列中，当获取数据的时候只需要逐个搜索，并返回结果，但是 如果开发的应用有几百上千万甚至亿级别的数据，那么不深入了解索引的原理， 写出来程序就根本跑不动，光查一个数据库就得好几天，因此就需要索引，能够快速的查找的需要的数据。

mysql连接查询

内连接：

等值连接

非等值连接

外连接：

左外连接：

右外连接：

自连接

-- 内连接

select e.c_name,d.dname,d.location from employee e inner join dept d on e.department_id= d.department_id;

+--------+----------+---------+

| c_name | dname | location |

+--------+----------+---------+

| 刘一 | 财务部 | 北京 |

| 陈二 | 销售部 | 广州 |

| 张三 | 销售部 | 广州 |

| 李四 | 研发部 | 上海 |

| 王五 | 行政部 | 深圳 |

| 赵六 | 人力资源 | 惠州 |

+--------+----------+---------+

-- 左外连接，是指以左边的表的数据为基准，去匹配右边的表的数据，如果匹配到就显示，匹配不到就显示为null

-- 查询employee表中的所有数据和dept表中与employee中相匹配的数据，若是没有匹配的就显示null

select e.c_name,d.dname from employee e left outer join dept d on d.department_id = e.department_id ;

-- 修改employee中的数据

update employee set department_id=60 where employee_id=3;

-- 重新查询，由于dept表中不存在60的数据，所以再dept表中没有对应的匹配数据，显示为null

select e.c_name,d.dname from employee e left outer join dept d on d.department_id = e.department_id ;

+--------+----------+

| ename | dname |

+--------+----------+

| 刘一 | 财务部 |

| 陈二 | 销售部 |

| 张三 | NULL |

| 李四 | 研发部 |

| 王五 | 行政部 |

| 赵六 | 人力资源 |

+--------+----------+

-- 右外连接和左外连接只不过是左右表相换也能达到同样的效果

-- 这里就是查询dept部门表对应所有部门和employee表中与之对应的数据，你会发现本来employee中有6条数据，只显示了5条数据，因为有一个人的部门60再dept中没有数据，所以就没有显示出来。

select e.c_name,d.dname from employee e right outer join dept d on d.department_id = e.department_id;

+--------+----------+

| ename | dname |

+--------+----------+

| 刘一 | 财务部 |

| 陈二 | 销售部 |

| 李四 | 研发部 |

| 王五 | 行政部 |

| 赵六 | 人力资源 |

+--------+----------+

-- 自连接查询就是当前表与自身的连接查询，关键点在于虚拟化出一张表给一个别名

-- 查询员工以及他的上司的名称，由于上司也是员工，所以这里虚拟化出一张上司表

select e.c_name 员工名,b.c_name 上司名 from employee e left join employee b on e.manager_id= b.employee_id;

+--------+--------+

| 员工名 | 上司名 |

+--------+--------+

| 刘一 | 陈二 |

| 陈二 | 张三 |

| 张三 | 李四 |

| 李四 | 王五 |

| 王五 | NULL |

| 赵六 | 王五 |

+--------+--------+

mysql子连接查询

按子查询出现在主查询中的不同位置分

select后面：仅仅支持标量子查询。

from后面：支持表子查询。

where或having后面：支持标量子查询(单列单行)、列子查询(单列多行)、行子查询(多列多行)

exists后面(即相关子查询)：表子查询(多行、多列)

-- select后面的子查询

-- 查询每个部门员工个数

SELECT d.*,

(SELECT count(*)

FROM employee b

WHERE b.department_id = d.department_id)

AS 员工个数

FROM dept d;

-- 查询员工号等于3的部门名称

SELECT

(SELECT a.dname

FROM dept a, employee b

WHERE a.department_id = b.department_id

AND b.employee_id = 3)

AS 部门名;

-- from后面的子查询

-- 查询每个部门平均工资的工资等级

-- (1)先查询每个部门平均工资

SELECT

department_id,

avg(a.salary)

FROM employee a

GROUP BY a.department_id;

-- (2)然后是查询薪资等级表

SELECT *

FROM job_grades;

-- (3)将上面2个结果连接查询，筛选条件:平均工资 between lowest_sal and highest_sal;

SELECT

t1.department_id,

avg_salary AS '平均工资',

t2.grade_level

FROM (SELECT

department_id,

avg(a.salary) avg_salary

FROM employees a

GROUP BY a.department_id) t1, job_grades t2

WHERE

t1.avg_salary BETWEEN t2.lowest_sal AND t2.highest_sal;

-- where和having后面的子查询

-- 查询谁的工资比javaboy的高？

-- (1)查询lisi的工资

SELECT salary FROM employee WHERE e_name = 'lisi';

-- (2)查询员工信息，满足salary>上面的结果

SELECT *

FROM employee a

WHERE a.salary > (SELECT salary

FROM employee

WHERE e_name = 'lisi');

--having后的子查询

--查询最低工资大于40号部门最低工资的部门id和其最低工资

-- (1)查询40号部门的最低工资

SELECT min(salary)

FROM employee

WHERE department_id = 40;

--(2)查询每个部门的最低工资

SELECT

min(salary),

department_id

FROM employee

GROUP BY department_id;

--(3)③在②的基础上筛选，满足min(salary)>①

SELECT

min(a.salary) minsalary,

department_id

FROM employee a

GROUP BY a.department_id

HAVING min(a.salary) > (SELECT min(salary)

FROM employee

WHERE department_id = 50);