MySQL - 数据操作DML 与 数据查询SQL(小白入门)

1.MySQL数据操作DML

1.1添加数据(字段)

#格式：	insert into 表名[(字段列表)] values(值列表...);

--标准添加(指定所有字段 给定所有的值)
	insert into users(id,name,age) values(1,'sss',21);
--若id设置自动添加自增，这里可设置为null

--指定部分字段添加
	insert into users(age) values(21);

--不指定字段添加(值的顺序必须与字段顺序一致)
	insert into users values(1,'sss',21);

--后面给定多个括号进行批量添加，中间使用逗号隔开

1.2修改数据

#格式：	updata 表名 set 字段1=值1,字段2=值2,... where 条件;

--将id为11的age改为35，sex改为m
	updata users set age=35,sex='m' where id=11;

--将id为12和14的数据值sex改为m，classid改为lamp11
	updata users set sex='m',classid='lamp11' where id=12 or id=14;
	--等价于下面
	updata users set sex='m',classid='lamp11' where id in(12,14);

1.3删除数据

#格式：	delete from 表名 [where 条件;]

重点是where条件的书写

--删除users表中的id为100的数据
	delete from users where id=100;

--删除users表中的id为20~30的数据
	delete from users where id>=20 and id<=30;--等价于下面的写法
	delete from users where id between 20 and 30;

--删除users表中的idd大于100的数据
	delete from users where id>100;

2.DQL-MySQL数据查询SQL

2.1检索数据select

检索单个列

select 列名 from 表名

#如果没有明确排序，查询的结果是无意义的，放回的数据可能是被添加的数据顺序，也可能不是

检索多个列

select 列名1,列名2,... from 表名

#多个列名之间使用逗号隔开，最后一个列名不追加逗号

检索所有列

select * from 表名

#使用通配符(*),一般不使用，会降低性能

检索不同的行 DISTINCT(重复的不显示)

select distinct 列名 from 表名

	DISTINCT关键字指示MySQL只返回不同的值

注意：
	select distinct 列名1,列名2,... from 表名
	还是会遵循不重复原则

限制结果 LIMIT

--查看前面m列数据
	select * from 表名 limit m

--跳过m列，取n列,取到是第m列到第m+n列的数据
	select * from 表名 limit m,n

使用完全限定的表名

--取出stu表中的name字段数据
	select name from stu
	
--从stu表中取出stu表中name字段的数据
	select stu.name from stu
	
--从指定的库中指定的stu表中取出stu中的name数据(多库的时候常用)
	select stu.name from 库名.stu

2.2对检索数据进行排序 ORDER BY

排序数据 ORDER BY(对结果进行排序)

--按升序排列
	select * from 表名 order by 字段名;
	
	--把stu表中的age字段数据按照升序排列
	select * from stu order by age;

按多个列排序

--按升序排列,会按照字段的前后顺序依次对比排序
	select * from 表名 order by 字段名1,字段名2,...
	
	--先依据classid升序排序，后在classid的基础之上按照age进行升序排序
	select * from stu order by classid,age

--order by 的默认排序是升序，可使用DESC设置降序
--先对classid进行升序排序，后在clssid升序的基础之上进行age的降序排列(在后面加上desc)
	select * from stu order by classid,age DESC

注意

1.ORDER BY 子句的位置在给出ORDER BY子句时，应该保证它位于FROM子句之后
2.如果使用LIMIT，它必须位于OEDER BY之后，使用子句次序不对将产生错误信息
	select * from stu order by classid,age DESC limit 3,2

2.3数据检索过滤 WHERE

过滤数据WHERE

1.过滤条件由where子句指定
	select 字段名 from 表名 where 条件;
	select name from stu where age=20;
	
2.在同时使用ORDER BY 和 WHERE 子句的时候where在前

WHERE 子句操作符

操作符	说明
=(取null值不能使用改操作符)	等于
!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
BETWEEN…AND…	指定两个值之间
IS NULL	空值

组合WHERE 子句

多个WHERE子句使用and/or方式连接
	1.AND
		select * from where age = 22 and sex = 'm';
		满足所有给定条件的行
	
	2.OR
		select * from where age = 22 or sex = 'm';
		满足任一条件的行

AND 与 OR

AND的优先级高于OR
使用括号进行操作可解决
	select * from stu where class_id = 1 or class_id = 2 and sex = '女';
		得到的结果等价于
	class_id=1 or (class_id=2 and sex='女';)

IN与NOT

1.IN操作符用来指定条件范围，每个条件都可以匹配
	select * from 表名 where 字段名 in(值1,值2,...)
	WHERE匹配OR，功能类似于OR
	
2.IN优于OR的点
	更清除且直观、计算次序更容易管理、执行更快
	可以包含其他的selet语句，更动态的建立WHERE子句
	
3.NOT WHERE子句用来否定后跟条件的关键字
	select * from 表名 where 字段名 not in(值1,值2,...)

2.4通配符的使用

LIKE与通配符(配合WHERE)

用于检索包含某种文本的数据
LIKE指示MySQL，后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较

1.百分号(%)通配符在搜索中，%表示任何字符出现的任意次数
	--检索name字段以a开头的数据
	select name from 表名 where name like 'a%';
	
	--检索name字段以a结尾的数据
	select name from 表名 where name like '%a';
	
	--检索name字段含有a的数据
	select name from 表名 where name like '%a%';
	
2.下划线(_)通配符，下划线的用途与%一样，但下划线匹配单个字符
	--检索name字段以a开头、长度为2的数据
	select name from 表名 where name like 'a_';

使用技巧

通配符的处理比前面的所有搜索所花的时间更长
	1.少使用通配符
	2.尽量不要把通配符放在搜索模式的开始处(最慢)
	3.注意位置，放错会返回不想要的数据

了解MySQL的正则REGEXP

所有种类的程序设计语言、文本编辑器、操作系统都支持正则表达式

select * from 表名 where 字段名 regexp '[0-5]abc';

2.5字段的计算

字段计算并不实际存在于数据库表中，计算字段是运行时在SELECT语句内创建的

拼接CONCAT

--把数据拼接显示 name(age)
select concat(name,'(',phone,')') from 表名;

使用别名AS

一个未命名的列不要用于客户机应用，因为客户机无法引用到它，为解决这个问题MySQL支持别名
--数据拼接以后没有名字，这里给它定义一个名字为name_age
select concat(name,'(',phone,')') from 表名 as name_age from 表名;

2.6函数的使用

文本处理函数

网址:dev.mysql.com/doc/refman/5.7/en/string-functions.html

常用函数	说明
Left()	返回字符串左边的字符
Length()	返回长度
Locate()	找出串的一个子串
Lower()	将串转换为小写
LTrim()	去掉串左边的空格
Right()	返回串右边的字符
RTrim()	去掉串右边的字符
SubString()	返回子串的字符 **
Upper()	将字符串转换为大写
Concat()	拼接字符串 **

日期和时间处理函数

网址:dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html

常用函数	说明
AddTime()	增加一个时间(时、分、秒)
AddDate()	增加一个天数
CurDate()	返回当前日期 **
CurTime()	返回当前时间 **
Date()	返回日期时间的日期部分
DateDiff()	计算两个日期之差**
Date_Add()	高度灵活的日期运算函数
Date_Format()	返回一个格式化的日期或时间串
DayOfWeek()	对于一个日期，返回对应的星期几
Hour()	返回一个时间的小时部分
Minute()	返回一个时间的分钟部分
Month()	返回一个日期的月份部分
Now()	返回当前日期和时间 **
Second()	返回一个时间的秒部分
Time()	返回一个日期的时间部分
Year()	返回一个日期的年份部分

数值处理函数

网址:dev.mysql.com/doc/refman/5.7/en/numeric-functions.html

常用函数	说明
Abs()	返回一个数的绝对值
Cos()	返回一个角度的余弦
Exp()	返回一个数的指数值
Mod()	返回操作的余数
Pi()	返回圆周率
Rand()	返回一个随机数 **
Sin()	返回一个角度的正弦
Sqrt()	返回一个数的平方根
Tan()	返回一个角度的正切

2.7聚集函数与分组 Group By 和过滤 HAVING

在汇总数据而不用把它们实际检索出来，MySQL官方提供了聚集函数用于检索数据，以便分析和报表生成

常见聚集函数	说明
COUNT()	返回某列的行数(数据中的NULL不会被统计进去)
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUN()	返回某列值之和
AVG()	返回某列的平均值，直接avg(i),返回字段为i的所有数据的平均值

使用count()时，指定列名，则指定的列的值为空的行被忽略，但如果count()函数中用的是星号(*),则不忽略

select count(id) as num,max(age) max_age,min(age) min_age from stu;
--得到的时一个列表，名字被修改为as后面的数据，as可以省略

数据分组GROU BY与HAVING

GROUP BY

SQL聚集函数可用来汇总数据，而不用检索所有数据
目前为止的所有计算都是在表的所有数据或匹配特定的WHERE子句的数据上进行的

当我们需要分组了，分组允许把数据分为多个逻辑组，以便对每个组进行进行聚集计算
--以 字段名1,字段名2,... 为统计对象，统计它的count(*)和avg()
	select 字段名1,字段名2...,count(*) as 别名,avg(字段名) from 表名 group by 字段名1,字段名2...;

--得到的数据就以表格的形式进行展现
	select classs_id,count(*) as nums from stu group by classs_id;

group by后面默认的模式的写上select后面的字段，它们得对应(聚合函数不用写)

HAVING

出来能有GROUP BY分组数据外，MySQL还允许过滤分组，规定包括哪些分组，排除哪些分组
目前为止学过的所类型的WHERE子句都可以用HAVING替代，唯一的差别是where过滤行，having过滤分组

--获取class_id为统计对象的nums表
	select class_id,count(*) as nums from stu gruop by class_id;
--获取class_id为统计对象的nums>=5的表
	select class_id,count(*) as nums from stu gruop by class_id having nums >= 5;

在使用Group By进行分组时的注意点

在使用group by进行分组时，按照标准的SQL模式，需要把select查询中的所有列(除了聚集函数外)全部都列在group by后面
	-- 例
	select name,age from stu group by name;		#语法错误
	select name,age from stu group by name，age;	#语法正确