一、mysql 语法规范
1、不区分大小写,但建议关键字大写,(表名、列名小写,关键字不区分,表、列不区分)
mysql 默认数据不区分大小写。
2、每条命令用英语分号结尾。
3、注释:
单行注释:#注释文字
单行注释:-- 注释文字 注意!!!-- 后面必须加空格。
多行注释:/* 注释文字 */
二、创建数据库
1、创建数据库
create database 数据库名;
2、查看数据库
show databases;
3、删除数据库
drop database 数据库名;
三、创建表
1、mysql常用的数据类型
整数类型 | 浮点数类型 | 字符串类型 | 日期类型 |
int,tinyint | float,double,decimal,numeric | char,varchar | date,datetime |
tinyint:取值范围是-128到127。
int:范围是-2147483648到2147483647
numeric:代表整数和小数的位数之和;scale,代表小数的位数
char:定长字符串。存定长,速度快,存在空间浪费的可能,会处理尾部空格,上限255
varchar:可变长度字符串。存变长,速度慢,不存在空间浪费,不处理尾部空格,上限65535
date:只有日期部分,没有时间部分
datetime:时间格式为 YYYY-MM-DD hh:mm:ss,默认精确到秒
timestamp:把客户端插入的时间从当前时区转化为UTC(世界标准时间)进行存储
2、约束管理
主键约束 primay key 唯一性,非空性
唯一约束 unique 唯一性,可以为空
检查约束 check 对该列数据的范围、格式的限制 mysql不实现
不能为空 not null 对数据不能为空
外键约束 foreign key 需要建立两表见的关系并引用主表的列
auto_increment 自增长
例:
create table 表名
(
字段名 数据类型 约束管理,
字段名 数据类型,
……,
);
create TABLE t_student
(
sid int(8) not null primary key auto_increment,
sname varchar(20) not null,
ssex char(2),
sbirthday datetime
);
3、表修改
修改表名:alter table 原表名 as 新表名;
为表添加字段:alter table 表名 add 字段名 数据类型;
修改表字段修改类型:alter table 表名 modify 字段名 数据类型;
修改表字段名: alter table 表名 change 原字段名 新字段名;
删除字段:alter table 表名 drop 字段名;
4、表删除
drop table [if not exists] 表名;
四、增、删、改
1、新增语法
1)insert into 表名 (字段1,字段2,……) values('值1','值2',……);
指定插入的字段行进行新增,要求字段与字段值必须对应。
2)insert into 表名 (字段1,字段2,……) values('值1','值2'……),('值1','值2'……),('值1','值2'……);
一次性插入多笔数据资料,要求字段和字段值必须一 一对应,且记录之间用逗号隔开。
3)insert into 表名 values('值1','值2',……);
不写插入的字段行,则默认插入所有字段数据(自增主键除外,需要在后续中输入所有字段数据,如有自增主键,则可以提供null值);
4)批量新增语法:insert into 表名 select语句;,例如:INSERT INTO t SELECT id,age,name from t_old where id < 10;
2、修改语法
update 表名 set [字段1]='值1',[字段2]='值2' where [字段]='值';
3、删除语法
delete from 表名 where 条件;
五、简单查询
1、不带条件的简单查询
1)查询全部数据
select * from 表名;
2)查询指定列数据
select 字段1,字段2…… from 表名;
3)查询指定列数据并为列起别名
select 字段1 as 别名,字段2 as 别名,…… from 表名;
4)去除重复行数据
select distinct 字段1,字段2,…… from 表名;
5)对列进行运算,常用的运算符有+、-、*、/等
select 字段1+字段2 from 表名;
(!!!!!! 运算里面如果有null,要特别注意,一个数+null=null,因为null值代表未知的无意义的。)
2、聚合函数
聚合函数也是写在字段表达式的位置,可以对字段输出进行列的相关计算
1)计算人数(计数)
select count(字段名) from 表名;
2)求和
select sum(字段名) from 表名;
3) 最大值
max(字段名),用法同上
4)最小值
min(字段名)
5)平均值
avg(字段名)
3、where语句(对数据进行条件过滤)
1)关系运算符
利用where 关键字进行条件过滤的时候,我们通常会用到关系运算符,下面是常用到的关系运算符, >、<、=(等于,不是赋值)、>=、<=、!=
示例1:查询员工工资大于等于3000的员工信息
select * from 表名 where sal(工资字段)>=3000;
示例2:查询所有的女同学
select * from 表名 where ssex(性别字段)='女';
2)模糊查询
like 是模糊查询的关键字,可以使用%(任意字符长度)和 _ (任意一个字符)作为通配符。
示例1:查询姓张的学生信息
select * from 表名 where sname(姓名字段) like '张%';
示例2:查询是三个字的同学
select * from 表名 where sname(姓名字段) like '___';
3)IN, NOT IN关键字
IN:判断某个值,出现在某个集合内,集合使用括号(),内部是集合元素列表,元素与元素之间用逗号隔开。
示例1:查询出部门编号为1,4,5的员工信息
select * from 表名 where deptno(部门编号字段) in (1,4,5);
示例2:查询出部门编号部位1,4,5的员工信息
select * from 表名 where deptno(部门编号字段) not in (1,4,5);
4)between ... and ... ,not between ... and ...
between… and…:用于判断一个数据在某个范围之内,适合用于数字和时间的比较。
示例1:查询工资在3000-5000之间的员工信息
select * from 表名 where sal(工资字段) between 3000 and 5000;
示例2:查询所有工资小于3000或者工资大于6000之间的员工信息
select * from 表名 where sal(工资字段) not between 3000 and 5000;
5) is null ,is not null 关键字
null在数据库里面是很特殊的存在,他代表未知或者无意义。如果需要判断一个字段是否为null,则需要使用is null/is not null
示例1:查询所有性别为null的学生信息
select * from 表名 where ssex is null;
6)逻辑运算符
逻辑运算符包含:逻辑与(and)并且 逻辑或(or)或者 逻辑非(not)
示例1:求工资在3000到5000之间的员工
select *from 表名 where sal>=3000 and sal <=5000;
7)group by 语句(对数据进行分组,分组:按照一定的字段条件将数据进行分类,分类的主要目的主要是进行统计和查询的。
示例1:按照性别分组统计数量
select ssex(性别字段),count(*) from 表名
group by ssex;
示例2:求每个部门的平均工资
select deptno(部门字段),AVG(sal) from 表名
group by deptno;
8)HAVING字句(服分组后数据进行过滤)
where 是对分组前的数据进行过滤,having是对分组后的数据进行过滤。
示例1:查询每一种工种的总人数在2人以上的工种。
select job,count(*) from 表名
group by job
having count(*)>2;
示例2:统计每一个工种的男性总人数在2人以上(包含2人)的工种
select
job,count(*) from 表名
where esex='男'
group by job
having count(*) >= 2;
9)ORDER BY 字句(对查询结果进行排序)
升序:asc(可省略) 降序:desc
示例1:查询所有的男同学,并且按照出生日期进行升序,学号的降序进行排序。
select * from 表名
where ssex=1(1代表男)
order by
sbirthday asc,sid desc;
示例2:按照同学的年龄大小进行排序
select * from 表名
order by sbirthday desc;
10)limit子句(限定结果数量)
limit子句用于限制结果数量。语法:limit [$start,] $size;
从$start所表示的记录开始,获取最多$size条记录。(注意:不是开始和结束)
示例1:从学生表中按学生编号升序获取2条记录
select * from 表名 order by sid limit 2;
示例2:从学生表中按学生编号升序获取2条记录
select * from 表名
order by sid asc
limit 2;
总结:limit n,k :代表 从索引位置第n笔开始,取得k条数据,包含第n条,索引从0开始算起。
示例3:求学生的第三页数据,每页4笔数据。
分析:从索引的第几笔开始,查4笔数据
select * from t_student limit 8,4;
六、mysql 复杂查询
1、子查询
1)where 子查询
示例1:查询所有修了课程编号为1的学生信息。
select * from 学生信息的表名 where sid(学生编号字段) in(
select sid from 课程编号字段的表名 where cid(课程编号字段) = 1
);
示例2:查询部门地理位置在北京的员工信息。
select * from emp(员工信息表)
where deptno(部门信息表) in(
select deptno from dept where loc='北京'
)
2)from子查询
在from子句中,from后要求出现的是一个表,因此需要给该子查询查询到的表数据起个别名,相当于生成一个临时的表,然后去用这个临时表的数据。
3)exists子查询
该关键字用来判断是否存在的,他会根据返回的真假值,来确定当前记录是否被查询出来。
示例1:查询所有有部门的员工(部分员工有部门)
select * from emp(员工表)
where exists (
select * from dept(部门表) where emp.deptno=dept.deptno
)
4)多表查询
多表连接解决需要查询的数据来自于多张表的情况。
①、等值连接
概念:等值连接时在乘的操作上进行的主外键等值条件的过滤
语法: select 列1,列2… from 表1,表2… where 表1,列1=表2,列1 and…;
示例1:查询所有员工信息 要求显示员工信息,员工所在的部门信息
select ename(部门名称),job(工种名称),emp.deptno(部门编号),loc(部门所在城市)
from emp,dept(两个关联的表)
where emp.deptno=dept.deptno(两个主外键);
②、内连接
内连接的效果等效于等值连接,但是,语法结构是不一样的,语法关键字为INNER JOIN ....ON....。
SELECT
empno,ename,job,emp.deptno,dname,loc
FROM emp INNER JOIN dept ON emp.deptno = dept.deptno;
③、外连接
我们在这边主要讲解最常用的左外连接,其语法关键字为LEFT OUTER JOIN...ON...,左外连接和内连接都可以实现多表查询,但是,他们的区别在于,左外连能够以关键字左边的表为主表,能够与右边的表匹配上则该数据被查询出来,而如果匹配不上,则左边的表数据出来,右边的表的数据因为匹配不上用NULL值填充。
示例1:查询所有员工信息,要求显示empno,ename,job,deptno,dname,loc
ELECT
empno,ename,job,emp.deptno,dname,loc
FROM emp LEFT OUTER JOIN dept ON emp.deptno = dept.deptno;
5)集合操作
UNION联合查询:可以将多个查询结果需要汇集到一个结果集里面,要注意的是多个SELECT查询的字段数量必须一致。为了让大家理解的更好,下面用图来说明:
示例:求第四季度的销售记录。
SELECT 日期 ,销售额 FROM 10月份销售记录表
UNION
SELECT 日期 ,销售额 FROM 11月份销售记录表
UNION
SELECT 日期 ,销售额 FROM 12月份销售记录表
注意:UNION后面还可以跟ALL,不加ALL的话,数据会去重,如果加上ALL,则会保留重复数据。
七、mysql 内置函数
1、运算函数
select ABS( 表达式 ); -- 绝对值
select CEILING( 表达式 ); -- 向上取整
示例:4.56向上取整
select CEILING(4.56);
select FLOOR( 表达式 ); -- 向下取整
select RAND( ); -- 0~1的随机数
2、字符串函数
select CHAR_LENGTH('abcde') -- 字符串长度
select CONCAT('a','b','c') -- 字符串拼接
select INSERT('nmcdef',1,2,'ab') -- 字符串替换
insert(原字符串,位置,长度,新字符串)
select LOWER(表达式) -- 转小写
select UPPER(表达式) -- 转大写
select INSTR(str,subtsr) -- 返回第一次出现的子串索引
示例:查找出所有姓林的同学 ,姓林是条件
select * from t_Student where INSTR(sname , '林') = 1;
select REPLACE(str,str1,nstr) -- 把原字符串str中的str1内容替换为nstr
select SUBSTR(str,n,m) -- 返回原字符串从n开始长度为m的子串
select SUBSTR(str,n,m) -- 返回原字符串从n开始长度为m的子串
str:要分割哪一个字符串
n:从第几个字符位置开始截取
m:截取几个字符
如果只设置str,n这两个参数,那么表示从第n位开始截取str字符串,一直截取到最后
示例:显示学生信息( 学生编号,学生姓 , 学生名 )
select
sid , substr( sname , 1 , 1) , substr( sname , 2 )
from t_student;
select REVERSE(表达式); -- 反转字符串
select LEFT(表达式,偏移量); --从左边截取偏移量个字符
示例:求所有姓王的员工信息
SELECT * FROM emp WHERE LEFT(ename,1) = ‘王’;
3、时间日期函数
select CURRENT_DATE() -- 获取当前日期
select CURDATE() -- 获取当前日期
select NOW() -- 获取当前日期时间
select SYSDATE() -- 系统时间
select YEAR(NOW()) -- 获取日期的年的部分 2021
select MONTH(NOW()) -- 获取日期的月的部分 6
select DAY(NOW()) -- 获取日期的日的部分
select HOUR(NOW()) -- 获取日期的小时的部分
select MINUTE(NOW()) -- 获取日期的分的部分
select SECOND(NOW()) -- 获取日期的秒的部分
示例:查询出所有1998年出生的学生信息
SELECT * FROM t_student WHERE YEAR(sbirthday) = 1998;
4、系统函数
select SYSTEM_USER() -- 系统登录用户
select VERSION() --MySQL的版本
5、对null值进行处理的函数
ifnull函数:ifnull() 函数用于判断第一个表达式是否为 null,如果为 null则返回第二个参数的值,如果不为 null则返回第一个参数的值。
IFNULL() 函数语法格式为:IFNULL(expression, alt_value);
示例:求员工收入(员工编号,员工姓名,收入)
SELECT empno , ename , IFNULL( sal , 0 ) + IFNULL(comm , 0) FROM emp;
注意:这个题目一定要对NULL进行处理,因为不处理的话,一个数字与NULL求和,结果为NULL。