char和varchar的区别:
创建数据库并指定字符集—CREATE
CREATE DATABASE mybase CHARACTER SET UTF8;
查看所有数据库—SHOW
SHOW DATABASES;
查看当前使用的数据库—SELECT
SELECT DATABASE();
修改数据库编码—ALTER
ALTER DATABASE mybase CHARACTER SET UTF8;
删除数据库—DROP
DROP DATABASE mybase;
切换数据库—USE
USE mybase;
show databases;
create database mydb1 charset utf8;
drop database if exists mydb1;
use mydb1; show tables;
create table stu( id int, name varchar(50), gender varchar(10), birthday date, score double );
drop table stu;
**查看**表结构:
desc stu;
insert into stu(id,name,gender,birthday,score) values(1,'张飞','男','2000-1-1',89); insert into stu values(2,'赵云','男','2001-12-11',75);
update stu set score=score+10; update stu set score=80 where id=1;
delete from stu; -- 删除stu表中的所有记录 delete from stu where id>3; -- 删除复合条件的记录
select name, sal, bonus from emp; select distinct bonus from emp; -- distinct用于剔除重复记录
WHERE子句查询语法:SELECT 列名称 | * FROM 表名称 WHERE 列 运算符 值
WHERE子句后面跟的是条件,条件可以有多个,多个条件之间用连接词(or | and)进行连接。
select name, sal+bonus from emp where sal+bonus > 3500; select name, sal+ifnull(bonus,0) from emp where sal+ifnull(bonus,0) > 3500;
select name as 姓名, sal+ifnull(bonus,0) as 总薪资 from emp where sal+ifnull(bonus,0) > 3500; select name 姓名, sal+ifnull(bonus,0) 总薪资 from emp where sal+ifnull(bonus,0) > 3500;
不等于 <> 或者 !=
select name, sal from emp where sal<>5000; select name, sal from emp where sal!=5000;
select name,sal from emp where sal=1400 or sal=1600 or sal=1800; select name,sal from emp where sal in(1400,1600,1800);
select name,sal from emp where not(sal=1400 or sal=1600 or sal=1800); select name,sal from emp where sal not in(1400,1600,1800); select * from emp where not(dept is null); -- 查询dept列不为null的 -- 或 select * from emp where dept is not null; -- 查询dept列不为null的
LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。
可以和通配符( %、 _ )配合使用,其中 " % " 表示0或多个任意的字符 , " _ " 表示一个任意的字符。
语法:SELECT 列 | * FROM 表名 WHERE 列名 LIKE 值
select name from stu where name='刘'; select name from stu where name like '刘'; -- 和上面的语句效果相同 select name from stu where name like '刘%'; -- 查询以刘开头的 select name from stu where name like '刘_'; -- 查询以刘开头的,并且名字是两个字的记录 select name from stu where name like '%涛%'; -- 查询姓名中包含'涛'字的员工;
GROUP BY 语句根据一个或多个列对结果集进行分组。
在分组的列上我们可以使用 COUNT,SUM,AVG,MAX,MIN等函数。
语法:·SELECT 列 | * FROM 表名 [WHERE子句] GROUP BY 列;
-- 按照部门分组(3个组) select * from emp group by dept; -- 对上面3个组的人数进行统计 select dept,count(*) from emp group by dept;
-- 按照职位分组(3个组) select * from emp group by job; -- 对上面3个组的人数进行统计 select job,count(*) from emp group by job;
-- 根据部门进行分组(3个组) select * from emp group by dept; -- 求每个部门(每个组)的最高薪资 select dept,max(sal) from emp group by dept;
多行函数也叫做聚合(聚集)函数,根据某一列或所有列进行统计。 包装结果列字段,对结果统计
多行函数不能用在where子句中
多行函数在统计时会对null值进行过滤,直接将null值丢弃,不参与统计。
使用多行函数时,表记录有没有分组查询的结果有很大区别:
select count(*) from emp;
如果没有分组, 所有的查询结果默认是一个组,统计时,一个组就统计出一个结果
select count(*) from emp group by job;
如果有分组, 有多少个组就统计出多少个结果,按照职位分为可以分为5个组,此时可count(*)会分别对这五个组进行统计,因此返回五个组的统计结果。
count(*|列名) -- 统计查询结果有多少行记录 max(列) -- 求某一列中所有值的最大值 min(列) -- 求某一列中所有值的最小值 sum(列) -- 求某一列中所有值的和 avg(列) -- 求某一列中所有值的平均值
select count(id) from emp where sal>3000; -- 7 select count(bonus) from emp where sal>3000; -- 6 -- 由于bonus中有null值,在统计时,null值直接别丢弃,不参与统计
-- emp表中的最高薪资(max函数) select max(sal) from emp; -- emp表中的最低薪资(min函数) select min(sal) from emp; -- 求emp表中薪资最高的员工 select name,max(sal) from emp; -- 上面的结果,max(sal)是求薪资中的最大值,而name只是name列中的 --第一个姓名,因此name和max(sal)很可能是不对应的。因此结果是错的!
select sum(sal) from emp; -- 求薪资这一列所有值的和 select sum(bonus) from emp; -- 求奖金这一列所有值的和
select avg(sal) from emp; -- 薪资平均值(总薪资/12) select avg(bonus) from emp; -- 奖金平均值(总奖金/11),有null值
多行函数需要注意的问题:
统计emp表中的人数
-- 如果没有分组,默认整个查询结果是一个组 select count(*) from emp; -- 14
结果返回的就是emp表中的所有人数
根据职业对所有员工进行分组,再统计每组的人数,显示职业和对应人数
-- 根据职业分组,最终分为五个组,统计五个组的人数(3,4,3,3,1) select gender,count(*) from emp group by job;
使用 ORDER BY 子句将结果集中记录根据指定的列排序后再返回
语法:SELECT 列名 FROM 表名 ORDER BY 列名 [ASC|DESC]
ASC(默认)升序,即从低到高;DESC 降序,即从高到低。
order by 要排序的列 asc: 默认的, 升序, 从低到高 desc: 降序, 从高到低 select * from emp order by sal; -- 按照薪资升序排序 select * from emp order by sal asc; -- 按照薪资升序排序 select * from emp order by bonus desc; -- 按照奖金降序排序 select * from emp order by sal+ifnull(bonus,0) desc; -- 按照总薪资降序排序
-- 分页查询:每页显示3条,返回第1页数据: select * from emp limit 0, 3; -- 分页查询:每页显示3条,返回第2页数据: select * from emp limit 3, 3; -- 分页查询:每页显示3条,返回第3页数据: select * from emp limit 6, 3; -- 分页查询:每页显示3条,返回第4页数据: select * from emp limit 9, 3;
-- 按照薪资对员工信息进行排序,降序排序 select name,sal from emp order by sal desc; -- 在上面查询的基础上,分页查询,每页显示3条,查询第一页 select name,sal from emp order by sal desc limit 0, 3;
curdate() -- 返回一个年月日格式的日期 curtime() -- 返回一个时分秒格式的时间 sysdate() -- 返回一个年月日 时分秒格式的时期加时间 now() -- 返回一个年月日 时分秒格式的时期加时间 year(),month(),day(),hour(),minute(),second() -- 上面的函数分别用于获取年月日时分秒中的年份/月份/天数/小时/分钟/秒值 concat(s1,s2,s3...sn) -- 将s1,s2 等多个字符串合并为一个字符串 concat_ws(x,s1,s2,s3...sn) -- 将传入的多个字符串连接在一起,每两个连接时会通过x作为分隔符进行连接
select name,birthday from emp where birthday between 1993 and 1995; -- birthday(日期) 和 1993、1995不能比较,因为类型不同 -- 解决方案1:将1993、1995转换成日期后,再和birthday进行比较 select name,birthday from emp where birthday between '1993-1-1' and '1995-12-31'; -- 解决方案2:将birthday中的年份取出,和1993、1995进行比较 select name,birthday from emp where year(birthday) between 1993 and 1995;
select * from emp where month(now())=month(birthday);-- 求本月过生日的员工 select * from emp where month(now())+1=month(birthday); -- 求下个月过生日的员工 select * from emp where ( month(now())+1 )%12=month(birthday); -- 问题:如果当前是12月份 select * from emp where ( month(now())+1 )%12=month(birthday)%12;-- 问题:如果当前是11月份 最终版本:求下个月过生日的员工
select name,concat(sal,'(元)') from emp; select name,concat(sal,'/元') from emp; select name,concat_ws('/',sal,'元') from emp;
如果一个列添加了主键约束, 那么这个列中的值就不能为空, 且不能重复 除此之外, 主键的列可以唯一的表示一行表记录(可以作为一行表记录的唯一标识, 这就和身份证类似, 身份证可以作为人的唯一标识)
drop table if exists stu; -- 删除stu表 create table stu( -- 重新创建stu表时,指定主键自增 id int, name varchar(20), gender char(1), birthday date ); desc stu; -- 查看表结构 -- 上面的stu表没有添加主键及主键自增,现在通过修改表的语句来进行添加 alter table stu modify id int primary key auto_increment; -- 如果要删除主键及自增, 先删除自增 alter table stu modify id int; -- 这是删除自增 -- 再删除主键约束 alter table stu drop primary key; -- 这是删除主键约束
如果一个列添加了非空约束, 那么这个列的值就不能是空的(null), 但可以重复
create table stu( -- 重新创建stu表时,指定主键自增 gender varchar(10) not null, ); -- 如果建表时每天添加非空约束, 也可以在建表之后再添加! alter table stu modify gender char(1) not null;
如果一个列添加了唯一约束, 那么这个列的值就是唯一的(也是这个列的值不能重复),但可以为空
create table stu( email varchar(20) unique, ); create table user( username varchar(20) unique not null, );
外键其实就是用于通知数据库两张表数据之间对应关系的这样一个列。
这样数据库就会帮我们维护两张表中数据之间的关系。
创建表的同时添加外键
外键: 用来通知数据库 两张表数据之间具有对应关系的列
create table emp( id int, name varchar(50), dept_id int, foreign key(dept_id) references dept(id) );
常见的表关系分为以下三种:
一对多(多对一)·、一对一、多对多
相同点: 都是对查询的结果进行筛选过滤
不同点:
where是在分组之前对数据进行筛选过滤; 而having是在分组之后对数据进行筛选过滤;
where子句中不能使用多行数据, 也不能使用列别名, 但可以使用表别名; 而having中可以使用多行函数, 也可以使用列别名和表别名;
ON 条件是在生成临时表时使用的条件,它不管 ON 中的条件是否为真,都会返回左边表中的记录;
WHERE 条件是在临时表已经生成后,对临时表进行的过滤条件。因为此时已经没有 LEFT JOIN 的含义(必须返回左侧表的记录)了,所以如果 WHERE 条件不为真的记录就会被过滤掉。
select * from dept,emp;
上面的查询中存在大量错误的数据,一般我们不会直接使用这种查询。
select * from dept,emp where emp.dept_id=dept.id; select * from dept inner join emp on emp.dept_id=dept.id; -- 内连接查询,结果和上面的连接查询相同!
– 因为id在两张表中都存在,所以为了区分,必须在列名前面加上[表名.],通过where子句将笛卡尔积查询中的错误数据剔除,保留正确的数据,这就是连接查询!
-- 查询的结果中都是有员工的部门和有部门的员工 select * from dept, emp where emp.dept_id=dept.id; -- 如果要查询部门表中的所有部门,以及部门对应的员工 select * from dept left join emp on emp.dept_id=dept.id;
左外连接查询:即左表全显示,右表无值则为NULL
可以将左边表中的所有记录都查询出来,右边表只显示和左边相对应的数据,如果左边表中某些记录在右边没有对应的数据,右边显示为null即可。
-- 查询的结果中都是有员工的部门和有部门的员工 select * from dept, emp where emp.dept_id=dept.id; -- 如果要查询所有员工及员工对应的部门 select * from dept right join emp on emp.dept_id=dept.id;
右外连接查询:可以将右边表中的所有记录都查询出来,左边表只显示和右边相对应的数据,如果右边表中某些记录在左边没有对应的数据,可以显示为null。
如果想将两张表中的所有数据都查询出来(左外+右外并去除重复记录),可以使用全外连接查询,但是mysql又不支持全外连接查询。
可以使用union将左外连接查询的结果和右外连接查询的结果合并在一起,并去除重复的记录。
注意:union将两条SQL语句执行的结果合并的前提是:
select * from dept left join emp on emp.dept_id=dept.id union select * from dept right join emp on emp.dept_id=dept.id;
将一条SQL语句执行的结果作为另外一条SQL语句的条件来执行,这种查询叫做子查询
-- 求出'王海涛'的薪资 select sal,name from emp where name='王海涛'; -- 2450 -- 求比'王海涛'薪资还高的员工 select name,sal from emp where sal>(select sal from emp where name='王海涛');
-- 求出'刘沛霞'从事的职位 select job from emp where name='刘沛霞'; -- 推销员 -- 求出和'刘沛霞'从事相同职位的员工 select name,job from emp where job=(select job from emp where name='刘沛霞');
select dept.name, emp.name from emp,dept where emp.dept_id=dept.id;
②求出部门名称为’培优部’的所有员工
select dept.name, emp.name from emp,dept where emp.dept_id=dept.id and dept.name='培优部';
③ 为emp和dept表分别指定表别名
-- 为emp和dept表分别指定表别名 select d.name '部门', e.name '员工姓名' from emp e,dept d where e.dept_id=d.id and d.name='培优部';
将emp表看做两张表, emp e1(员工表) emp e2(上级表)
显示的列: e1.name, e2.id, e2.name
查询的表: emp e1, emp e2
连接条件: e1.topid=e2.id
select e1.name, e2.id, e2.name from emp e1, emp e2 where e1.topid=e2.id;
①求出每种职位的最低薪资(将员工按照职位分组,求每组的最低薪资)
select job,min(sal) from emp group by job;
②在上面查询的基础上, 筛选出最低薪资大于1500的职位
select job,min(sal) from emp group by job having min(sal)>1500;
select dept_id 部门编号, count(*) 员工人数, avg(sal) 平均薪资 from emp group by dept_id;