MySQL的相关应用
本文主要介绍数据库中MySQL的基础知识,包括数据插入、数据更新、数据删除、数据查询、函数、CASE语句、表连接、子查询。
一、数据插入(insert 语句)
首先我创建一个表,如下:
接着,进行数据插入:
insert into userInfo(id,name,sex,score)values(8,'司马懿','男',14);
结果(注意:没有添加的信息默认为Null):
insert 用法小总结:
1) 后面的字段如果是字符串,一定要用单引号
2) 可以这样写
insert into userInfo(name)values('曹植');
//前提是别的字段可以为null的情况下
3) 这里的id,在创建表的时候,用的是自增id,实际上是可以不提供的(正常来说,就不该提供)。如果要手工提供,一定要大于当前数据中的最大ID。
4) 字段列表可以省略
insert into userInfo values(9,'司空','男',3,'2017-07-24 14:16:02');
(略写的要求是,不能有空缺项,例如,我下面的写法就不能通过)
insert into userInfo values(9,'司空','男',3);
二、数据更新(update 语句)
1.更新一个列
update userInfo set name='司马光' ---后面没有条件,表示全部更新
2.更新多个列
update userInfo set name='张春华',sex='女',score=20 where id=2
3.更新一部分数据
update userInfo set name='张春华2',sex='女',score=12 where id>7
小总结:Where中可以使用的其他逻辑运算符:or、and、not、<、>、>=、<=、!=(或<>)等。
【注意:最后一个逗号,不能加】
三、数据删除(delete 语句)
删除表中全部数据
delete from userInfo --不能写成 delete * from userInfo
注意:delete只是删除数据,表结构还在。这一点和 drop table 不一样。
delete 也可以和 where 子句一起用
delete from userInfo where name='张春华' select * from userInfo
回忆: truncate table 快速删除,主键重新开始
四、数据查询(select 语句)
嗯,首先执行代码,创建测试用的表和添加测试数据,如下:
CREATE TABLE F_Employee (FNumber VARCHAR(20),FName VARCHAR(20),FAge INT,FSalary NUMERIC(10,2),PRIMARY KEY (FNumber)); INSERT INTO F_Employee(FNumber,FName,FAge,FSalary) VALUES('DEV001','Java',25,8300); INSERT INTO F_Employee(FNumber,FName,FAge,FSalary) VALUES('DEV002','php',28,6300.80); INSERT INTO F_Employee(FNumber,FName,FAge,FSalary) VALUES('SALES001','C++',23,5000); INSERT INTO F_Employee(FNumber,FName,FAge,FSalary) VALUES('SALES002','C#',28,6200); INSERT INTO F_Employee(FNumber,FName,FAge,FSalary) VALUES('SALES003','.net',22,5200); select * from F_Employee
(一)简单的数据检索
1.简单的数据检索 :
SELECT * FROM F_Employee
2.只检索需要的列 :
SELECT FNumber FROM F_Employee
SELECT FName,FAge FROM F_Employee
3.列别名:
SELECT FNumber AS 编号,FName AS userName,FAge AS Age111 FROM F_Employee
4.使用where检索符合条件的数据:
SELECT FName FROM F_Employee WHERE FSalary>6000
5.可以检索和表无关的数据,例如
select 9 ---输出结果是9 select 9+90 ---输出结果是99 select 9 as result-----输出结果是以result为名,9为数据的一列表 select now() ---取当前日期 mysql函数
(二)聚合函数
SQL聚合函数:MAX(最大值)、MIN(最小值)、AVG (平均值)、SUM (和)、COUNT(数量)
大于25岁的员工的最高工资 :
select MAX(FSalary) FROM F_Employee WHERE FAge>23
最低工资和最高工资:
SELECT MIN(FSalary),MAX(FSalary) FROM F_Employee
利用count 查询条数:
select count(*) from T_Employee --查询条数 select count(1) from T_Employee --查询的也是条数,如果不加查询条件,和数据是不是null无关
(三)数据排序(order by 语句)
它位于SELECT语句的末尾,它允许指定按照一个列或者多个列进行排序,可以指定排序方式 。升ASC( 默认),降 DESC。
比如:按照年龄升序排序所有员工信息的列表:
select * from F_Employee order by FAge ---不写即默认升序
比如,按照年龄从大到小排序(如果年龄相同则按照工资从大到小排序)
新添加两行年龄与上面有重叠的数据,如下执行
INSERT INTO F_Employee(FNumber,FName,FAge,FSalary) VALUES('DEV003','Spring',28,8300); INSERT INTO F_Employee(FNumber,FName,FAge,FSalary) VALUES('SALES004','SQL',23,9000); select *from F_Employee SELECT * FROM F_Employee ORDER BY FAge DESC,FSalary DESC
注意:ORDER BY子句要放到WHERE子句之后。
select * From F_Employee where Fage>23 order by Fage DESC ,FSalary desc
(四)通配符过滤
1.like:单字符匹配的通配符为半角下划线“_”,它匹配单个出现的字符。
例:以任意字符开头,剩余部分为“erry” :
SELECT * FROM T_Employee WHERE FName LIKE '_erry'
2.多字符匹配的通配符为半角百分号“%”,它匹配任意次数(零或多个)出现的任意字符。
例:“k%”匹配以“k”开头、任意长度的字符串。
例: 检索姓名中包含字母“n”的员工信息 :
SELECT * FROM T_Employee WHERE FName LIKE '%n%'
另外,
[ ]括号中所指定范围内的一个字符
C Like '9W0[1-2]'
[^]不在括号中所指定范围内的一个字符
D Like '%[A-D][^1-2]'
(五)空值处理
数据库中,一个列如果没有指定值,那么值就为null,这个null和JAVA中的null不同,数据库中的null表示“不知道”,而不是表示没有。因此select null+1结果是null,因为“不知道”加1的结果还是“不知道”。
select count(*) from F_Employee where FName = null ---0 千万注意,查询null值一定不能用 = select count(*) from F_Employee where FName !=null ---0
SQL 中使用 is 来判断 null
INSERT INTO F_Employee(FNumber,FName,FAge,FSalary) VALUES('DEV004',null,30,4300); INSERT INTO F_Employee(FNumber,FName,FAge,FSalary) VALUES('SALES005',null,32,1200); select * from F_Employee where FName is null
select * from F_Employee where FName is not null
(六)多值匹配
select * from F_Employee where fage in (23,25,27) --上面的等效于 select * from F_Employee where fage= 23 or fage=25 or fage=27
另外,
---范围值: SELECT * FROM F_Employee WHERE FAGE>=23 AND FAGE <=27
(七)数据分组(group by)
select fage,count(*) from F_Employee group by fage
注意:
1.count(*) 是个聚合函数,它是指对分组后的每一组都要进行这样的操作。
2.GROUP BY子句必须放到WHERE语句的之。
3.没有出现在GROUP BY子句中的列是不能放到SELECT语句后的列名列表中的 (聚合函数中除外)。
错误:SELECT FAge,FSalary FROM T_Employee GROUP BY FAge 正确:SELECT FAge,AVG(FSalary) FROM T_Employee GROUP BY FAge select Age Max(工资),count(*) from 员工表 group by age //这样可以,取一组的最大工资 select age ,name ,count(*) from 员工表 group by age //这样不可以,name不应该在这里出现
(八)Having语句
对分组后的数据进行过滤,不能用where 必须用 having,它必须用在group by 之后。
SELECT FAge,COUNT(*) as 人数 FROM F_Employee GROUP BY FAge HAVING COUNT(*)>1
注意:Having中不能使用未参与分组的列,Having不能替代where。作用不一样,Having是对组进行过滤。
实际会遇到的代码,可以练习一下思维:
select 身份证号,count(*) from F_Employee group by 身份证号 having count(*) >1
(九)限制结果集行数(limit 语句)
原数据(select * from F_Employee)
limit
select * from F_employee limit 2 --只查前两条
下面的输出结果要注意一下:
select * from F_employee limit 2,4 --可以理解为从0开始的座标,是第三条,4代表4条
临时有时,晚上继续。。。。16:48:01
=============================================继续================================================
(十)去掉重复数据(distinct 语句)
首先,创建了一个有重复数据的表,
去掉重复数据如下指令:
select distinct Fage from F_Employee
(十一)联合结果集(Union 语句)
执行下列语句,形成一些测试数据:
CREATE TABLE T_TempEmployee (FIdCardNumber VARCHAR(20),FName VARCHAR(20),FAge INT, PRIMARY KEY (FIdCardNumber)); INSERT INTO T_TempEmployee(FIdCardNumber,FName,FAge) VALUES('7897845874','Sarani',33); INSERT INTO T_TempEmployee(FIdCardNumber,FName,FAge) VALUES('7896547841','Tom',26); INSERT INTO T_TempEmployee(FIdCardNumber,FName,FAge) VALUES('7841254963','Yala',38); INSERT INTO T_TempEmployee(FIdCardNumber,FName,FAge) VALUES('7854851258','Tina',26); INSERT INTO T_TempEmployee(FIdCardNumber,FName,FAge) VALUES('9658748574','Konkaya',29); INSERT INTO T_TempEmployee(FIdCardNumber,FName,FAge) VALUES('4158525874','Fotifa',46);
基本的原则:
每个结果集必须有相同的列数;每个结果集的列必须类型相容。
代码形式如下显示:(列数相同,类型一致)
SELECT FNumber,FName,FAge FROM T_Employee union SELECT FIdCardNumber,FName,FAge FROM T_TempEmployee
注意:
Union //去重
Union all //不去重,效率高
(Union 因为要进行重复值扫描,所以效率低,因此如果不是确定要合并重复行,那么就用UNION ALL)
例子:(和上面的不同,输出结构的第一列会出现你定义的名称,比如“正式员工最高年龄”、“正式员工最低年龄”等)
要求查询员工的最低年龄和最高年龄,临时工和正式员工要分别查询
select '正工员工最高年龄', max(Fage) from t_employee union all select '正工员工最低年龄', min(Fage) from t_employee union all select '临时工最低年龄', max(Fage) from T_TempEmployee union all select '临时工最低年龄', min(Fage) from T_TempEmployee
例子:(下面会有一行“合计”哦~~很酷吧)
查询每位正式员工的信息,包括工号、工资,并且在最后一行加上所有员工工资额合计。
select FNumber ,FSalary from t_employee union ALL select '合计' ,sum(FSalary) from t_employee
(十二)函数(平时最好别用函数,因为它不兼容!!!!!!!!)
1.数字函数
select abs(-1) //绝对值 select ceil(3.33) //4 舍入到最大整数 select floor(3.33) //3 舍入到最小整数 select round(3.5) //四舍五入 select round(3.2425926,3) 3.243
2.字符串函数
my-sql: select char_length('12345') //Mysql没有len函数,SQLServer中有 my-sql: select char_length( stuName) from stuExam my_sql: select char_length(NULL) select 的字段值为null 则得到的长度也是null //select IdCard, LEN(Idcard) from Student //如果select的字段值 为null,则len也是null select lower('aaaaAAA') select upper('aaaBBB') select rtrim(ltrim(' aaaaAAA ')) TRIM(str) select substring('0123456',2,3) // 得到234 select CONCAT(s1,s2...,sn)
3.日期函数
select now()
select HOUR(now()) --取当前日期的小时部分
(十三)case 语句
1.单值判断,相当于switch case
CASE expression --这个单词是表达示的意思 WHEN value1 THEN returnvalue1 WHEN value2 THEN returnvalue2 WHEN value3 THEN returnvalue3 ELSE defaultreturnvalue END
例子:查询年龄, 如果22的,显示小年轻;如果28显示老家伙;其他的显示中等家伙。
select Fname, ( case fage when 22 then '小青年' when 28 then '老家伙' else '中等家伙' end ) as 年龄类型 from F_employee
2.区间判断
select FName, ( case ----注意,这个case 后面没有直接跟fsalary when fsalary < 5000 then '低收入' when fsalary > 8000 then '高收入' else '普通收入' end ) as 赚钱 from F_employee
(十四)表连接
CREATE TABLE `stuinfo` ( `id` int(11) NOT NULL auto_increment, `name` varchar(20) default NULL, `age` int(11) default NULL, `schoolId` int(11) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 CREATE TABLE `schoolinfo` ( `schoolId` int(11) NOT NULL auto_increment, `schoolName` varchar(50) default NULL, `address` varchar(100) default NULL, PRIMARY KEY (`schoolId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
附:笛卡尔积:
如果没有对应关系,表1的每一条记录,和表2里的每一条记录交叉组合 。
select a.*, b.* from stuInfo a , schoolInfo b ---关联查询的时候,没有加入关联条件,结果就以乘积的方式呈现 select a.*, b.* from stuInfo a , schoolInfo b where a.schoolid=b.schoolid ---把两个表的信息全关联出来了 select a.name,a.age, b.schoolName from stuInfo a , schoolInfo b where a.schoolid=b.schoolid ---只关联出指定的列 常用
附:用 join on 连接(而今人们更喜欢用这个)
select a.name,a.age, b.schoolName from stuInfo a join schoolInfo b on a.schoolid=b.id----等价于以上表连接
注意:
1) 可以不用别名
select stuInfo.name,stuInfo.age, schoolInfo.schoolName from stuInfo join schoolInfo on stuInfo.schoolid=schoolInfo.id
2) 如果两个表中没有同名列,可以不用指定列所属的表名
select name,age, schoolName from stuInfo a , schoolInfo b where a.schoolid=b.id
附:左联接
比如,用来解决schoolName为空的学生不显示
select name ,age , b.schoolId,schoolName,address from stuinfo as a left join schoolinfo as b on a.schoolId=b.schoolId select name ,age , b.schoolId,schoolName,address from stuinfo as a right join schoolinfo as b on a.schoolId=b.schoolId
(十五)子查询
将一个查询语句做为一个结果集供其他SQL语句使用,就像使用普通的表一样,被当作结果集的查询语句被称为子查询。
(通俗解释就是说,对一个筛选后的查询结果再进行筛选查询)
select * from (select * from stuInfo where age<25) as xxxx where name like '刘_%' ---这个别名一定要取
例:查询年龄最小的人
select * from (select min(age) from stuInfo )---其实,这个表达也没啥意义,就为了演示子查询的格式 -----上面的这句也可以用下面这句来表达 select * from stuInfo where age = ( select min(age) from stuInfo)