MySQL的相关应用

2018-06-17 23:57:03来源:未知 阅读 ()

新老客户大回馈,云服务器低至5折

本文主要介绍数据库中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)

 

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:mysql数据库操作

下一篇:mysql按指定顺序排序