MySql
一.mysql简介
1.介绍
1.1什么是数据库
数据库(Database):用来存储数据的仓库。
数据库管理系统: 用来管理数据库的软件系统。 程序员通过这个软件去操作数据库以及数据----- 增删改查
常见的数据库软件: MySql (甲骨文) Oracle(甲骨文) SQLServer(微软) DB2(IBM) sybases
1.2mysql
特点:
-
开源的(不要钱)
-
体积小
-
速度快
中小型网站都使用mysql
2.安装
2.1安装步骤
参考: 安装步骤
2.2 服务
安装mysql后,会在操作系统找中添加一个mysql服务
打开服务管理器: 在命令中输入 services.msc 找到mysql服务 确保mysql服务是启动状态 才能使用
测试mysql数据库是否能够连接上:
-
在命令中 输入 cmd 打开电脑的命令窗口
-
连接mysql数据库的命令: mysql -u 用户名 -p密码
用户名 都叫 root
二. 基本操作
1.连接mysql
语法:
mysql -u 用户名 -p密码
mysql -u 用户名 -p密码 -h 数据库服务器的地址(ip地址) -D 数据库名
安装mysql后,默认有一个管理员 root ,密码是安装是设置的密码
退出: exit
2.数据库
数据库软件可以为我们管理多个库。 数据库实质就是磁盘上的物理文件。
安装后有几个默认的数据库, 自带的数据库不要随意删除,和修改,尽量操作自己新建的数据库。
2.1 查看数据库
show databases : 查看可用的数据库
use 数据库名 : 选择要使用的数据库
select database(): 查看当前正在使用的数据库
mysql库是系统库,包含了mysql相关的系统信息,不要修改。
2.2创建库
语法:
create database 数据库名 charset utf8;
create database if not exists 数据库名 charset utf8; -- 不存在,才创建。
-- 示例:
create database if not exists wbs19082 charset utf8;
2.3删除库
语法:
drop database 数据库名; -- 很厉害 不要随便用
drop database if exists 数据库;
-- 示例
drop database itany;
3.表
数据库中存储的东西以表为单位,每个库中可以放多个表
表中用来存放数据, 需要定义数据的 名称(列名/字段名) 类型
3.1 数据库中的类型
整数: int smallint bigint
小数:float double
字符串: varchar ------- (char String)
需要指定字符长度 : varchar(30)
日期类型: date
其他: clob 存储文本大数据
blob 存储二进制数据
3.2创建表
语法:
create table 表名
(
列名 数据类型 特征,
列名 数据类型 特征,
...
列名 数据类型 特征
);
注意: 列名不要写中文
特征暂时没有
多个列之间用逗号分隔, 所有符号都必须是英文状态下的
-- 示例
create table userinfo
(
id int,
username varchar(30),
password varchar(30),
age int
);
show tables; -- 查看数据看中的表
desc 表名; -- 查看表的结构(列名 类型 特征)
-- student 表
-- stuid name age sex address height birthday
create table student(
stuid int,
name varchar(30),
age int,
sex varchar(5),
address varchar(50),
height float,
birthday date
)
3.3 更新表
-
添加列
alter table 表名 add 列名 数据类型; -- 示例 alter table student add weight float;
-
修改列的类型
alter table 表名 modify 列名 数据类型; -- 示例 alter table student modify weight double;
-
修改列名
alter table 表名 change 原列名 新列名 数据类型; -- 示例 alter table student change weight tizhong double;
-
删除列
alter table 表名 drop 列名; -- 示例 alter table student drop tizhong;
-
修改表名
alter table 原表名 rename 新表名; rename table 原表名 to 新表名; -- 示例 alter table student rename stu;
3.4删除表
语法
drop table 表名;
drop table if exists 表名;
4.约束
constraint 对表中的数据进行限制,保证数据的有效性。
4.1约束分类
五种约束:
-
主键约束 primary key
用来唯一标识一条数据,本身不能为空,表中设为主键的列可以没有实际含义,建议一个表设置一个主键(id)
-
唯一约束 unique
不允许重复
-
检查约束 check
判断数据是否符合指定的条件
mysql中检查约束不起作用, 后期可以通过sql编程来解决
-
非空约束 not null
不允许为null,但可以为空字符串
-
外键约束(foreign key)
用来关联两个表
班级表
班级编号(主键) 班级名称 班级人数 开班时间
1 java
2 前端
学生表
学号(主键) 姓名 年龄 性别 班级编号(外键列)
1001 tom 12 女 1
1002 jack 2
学生表中的班级编号不能随意填, 有约束(班级表中存在的), 需要引用班级表中的数据
此时, 班级编号设置为外键约束, 关联两张表
4.2 添加约束
4.2.1 创建表的同时创建约束
-- 主表
create table class(
cid int primary key,
cname varchar(30) not null
);
-- 子表
create table student(
stuid int primary key auto_increment, -- 主键约束
name varchar(30) not null, -- 非空
sex varchar(10),
phone varchar(30) unique, -- 唯一
age int check(age between 1 and 100),
classid int, -- 外键
foreign key (classid) references class(cid)
-- foreign key (外键的名称)references 要引用的表(具体要引用的列名)
);
注意: 先创建主表,再创建子表
先删除子表,再删除主表
4.2.2 表创建后单独创建约束
alter table 表名 add constraint 约束名 约束的类型 约束内容
约束名:自定义的一个名字
alter table student add constraint pk_stuid primary key (stuid);
alter table student add constraint ck_age check(age between 1 and 100);
alter table student add constraint uq_phone unique (phone);
alter table student add constraint fk_classid foreign key (classid) references class(cid)
4.2.3 删除约束
-
删除主键 alter table 表名 drop primary key
-
删除外键 alter table 表名 drop foreign key 约束名
(show create table 表名 表的具体信息, 可以看到约束名)
-
删除唯一约束 alter table 表名 drop index 约束名
-
删除非空 alter table 表名 modify 列名 类型 null
4.2.4 注意事项
- 建议一个表一个主键,可以有多个外键
- 先创建主表,再创建子表
- 先删除子表,再删除主表
- 外键和引用的列 数据类型一致,被引用的列是唯一的
5.名词说明
SQL 语句: 结构化查询语言,用来对数据库进行查询 更新 和管理的一种语言
包含了三个部分
-
DML : 数据操作语言
用于对数据库中存储的数据进行增删改查 : insert delete update select
-
DDL: 数据定义语言
用于定义数据的结构: create alter drop
-
DCL: 数据控制语言
用于定义数据库的权限: grant revoke
三.DML(增删改查 )
DML对数据的 增删改 — 更新
查 ------ 查询
1.insert
语法:
-- 语法一
insert into 表名 (列名1,列名2,,,) values (值1,值2,,,,);
select * from 表名; -- 查询表中的所有信息
-- 语法二
insert into 表名 (列名1,列名2,,,) values (值1,值2,,,,) , (值1,值2,,,,)
-- 示例
insert into userinfo(id,username,password) values(1,'tom','123');
insert into userinfo (id,username,password,age,sex) values
(3,'jack','456',12,'男' ),
(4,'rose','456',12,'男' ),
(5,'alice','456',12,'男');
注意:
-
varchar和date 类型的数据 需要用单引号将值引起来 ‘1990-01-01’
-
列与值要一一对应
dept 部门表
列名 | 类型 | 含义 |
---|---|---|
deptno | int 整数 | 部门编号 |
dname | varchar 字符串 | 部门名称 |
loc | varchar 字符串 | 部门位置 |
emp 员工表
列名 | 类型 | 含义 |
---|---|---|
empno | int 整数 | 员工编号 |
ename | varchar 字符串 | 员工姓名 |
job | varchar 字符串 | 职位、工作 |
mgr | int 整数 | 经理或上司的编号 |
hiredate | date 日期 | 入职日期 |
sal | double 小数 | 工资、薪水 |
comm | int 整数 | 奖金 |
deptno | int 整数 | 部门编号 |
salgrade 工资等级表
列名 | 类型 | 含义 |
---|---|---|
grade | int 整数 | 编号 |
losal | int 整数 | 工资下限 |
hisal | int 整数 | 工资上限 |
2.delete
语法:
delete from 表名; -- 删除整个表的数据 慎用!!!
delete from 表名 where 条件; -- 根据条件删除某些数据
-- 示例
-- 删除 名叫 jones的员工信息
delete from emp where ename='jones';
-- 删除 工作为秘书 的员工信息
delete from emp where job='';
-- 删除 销售部(deptno 为30)工资高于5000的员工
delete from emp where deptno=30 and sal >5000;
3.update
语法:
update 表名 set 列名1=值1,列名2=值2,,,, where 条件;
-- 示例
-- 修改王五的地址
update student set address='北极' where name='王五';
-- 将销售部修改为市场部
update dept set dname='市场部' where dname='销售部';
-- 将smith的 工资修改为888 奖金为666 职位为 经理
update emp set sal =888, comm=666,job='' where ename='';
4.select
4.1基本查询
语法:
-- 查询所有
select * from 表名;
-- 指定列查询
select 列名1,列名2,,,, from 表名;
-- 别名查询
select 列名 别名, 列名2 别名,,, from 表名;
-- 示例
-- 查询 所有员工的姓名
select ename from emp;
-- 查询 姓名 工资 工作
select ename, sal, job from emp;
-- 姓名 工资 入职时间
select ename 姓名,sal 工资,hiredate 入职时间 from emp;
-
可以使用四则运算 + - * /
-- 查询员工的姓名 和年薪 select ename 员工姓名,(sal+comm)*12 员工年薪 from emp; -- 有问题 -- ifnull(x,y ) 如果 x为空则用y来代替 ifnull(sal,100) select ename 员工姓名,(sal+ifnull(comm,0))*12 员工年薪 from emp; -- 使用ifnull()
注意: 在mysql中 null 与任何值进行运算,结果都为null(空)
-
distinct 去除重复列
-- 查询所有的职位 select distinct job from emp; -- 去掉重复的项
4.2限定查询
语法:
select 列名1,列名2,列名3,,,
from 表名
where 条件;
4.2.1 比较运算符
> < >= <= = !=
-- 查询 工资大于 1500的员工信息
select * from emp where sal > 1500;
-- 查询编号不是7369的员工信息
select * from emp where empno!=7369;
-- 查询姓名为 smith的员工 编号 姓名 工资 入职时间
select empno,ename,sal,hiredate from emp where ename='smith';
4.2.2 null 或者not null
-- 查询没有奖金的员工信息
select * from emp where comm is null or comm = 0;
select * from emp where comm is not null;
注意: 判断是否为null 使用is , 不能使用 =
4.2.3 and or
-- 查询 工资大于1000 并且有奖金的员工 姓名 工资 奖金
select ename , sal ,comm from emp where sal >1000 and comm is not null;
-- 查询从事销售工作,或者工资大于等于2000的员工信息
select * from emp where job ='销售员' or sal >=2000;
4.2.4 between and
-- 查询 工资 大于15000 小于3000 的信息
select * from emp where sal between 1500 and 3000;
注意:包含临界值
-- 查询1981 年入职的员工 姓名 入职时间 部门编号
select ename,hiredate,deptno from emp where hiredate between '1981-01-01' and '1981-12-31';
注意: 日期用单引号引起来
4.2.5 in 或者 not in
-- 查询编号为 7369,7566,7788 的员工信息
select * from emp where empno='7369' or empno = '7566' or empno='7788';
select * from emp where empno in(7369,7566,7788);
-- 查询 smith allen jack 的入职时间 工资
select hiredate,sal from emp where ename in('smith','','');
4.2.6 like
like 后接匹配的条件,需要用‘ ’ 引起来
用于进行模糊查询,需要和通配符一起使用
通配符:
- % 可以匹配任意长度的字符 张%
- _ 只能匹配单个字符 张__
-- 查询姓名以d 开头的员工信息
select * from emp where ename like 'd%';
-- 查询 员工姓名中包含 a的员工信息;
select * from emp where ename like '%a%';
-- 查询 从事销售工作,并且名字长度为4个字符的员工信息
select * from emp where job='销售员' and ename like '____';
4.3排序
4.3.1 基本用法
语法:
select ......
from 表名
where 条件
order by 排序列1 asc|desc, 排序列2 asc|desc
asc:升序 desc 降序, 默认 按照升序
-- 查询员工信息 , 工资的由低到高
select * from emp order by sal;
-- 查询部门编号10 的员工,按工资由高到底 排序,如果工资相同 ,则按入职时间的从早到晚排序
select * from emp where deptno=10 order by sal desc,hiredate;
-- 查询员工的编号 姓名 年薪 按年薪的由高到低排序
select empno, ename, (sal+ifnull(comm,0))*12 from emp order by (sal+ifnull(comm,0))*12 desc;
4.3.2limit
用来限制查询返回的记录数
-- 查询工资前三名的员工信息
select * from emp order by sal desc limit 3;
limit : 后面接一个整数 , 表示 查询几行
select * from 表名 limit 参数1,参数2;
参数1: 起始行的索引, 从0开始
参数2: 用来指定返回的行数
select * from emp limit 2,5;
、、、、、、、、
首页 上一页 下一页 尾页 跳转到_页 1/5
分为多少页: 总的行数 每一页显示多少行
5 21(数据库) 5(自定义的)
总行数%一页显示的行数 ==0 ? 总行数/一页显示的行数 : 总行数/一页显示的行数 +1
显示第3页的数据, 应该显示 11 15
查询: select * from emp limit 10,5;
select * from emp limit 一页显示的行数*(当前页数-1),一页显示的行数;
参数1: 是一个动态数据,根据用户传入的当前页数,算出具体值
4.4聚合函数和分组查询
4.4.1 聚合函数
聚合函数 , 称为统计函数
常用函数:
-
count() 求总的行数
count(*): 求表中总的行数 count(主键列名) count(0)
count(列名): 求有值的列的 行数
-
max() 最大值
-
min()最小值
-
sum() 和
-
avg() 平均值
-- emp表中的 总人数
select count(*) 总人数 from emp;
-- 有奖金的人数
select count(comm) 有奖金的人数 from emp;
-- 查询部门30的总人数
select count(*) from emp where deptno=30;
-- 工资最高的员工的工资
select max(sal) from emp;
-- 查询部门30的最高工资,最低工资,工资总和,平均工资;
select max(sal),min(sal),sum(sal),round(avg(sal),2) from emp where deptno = 30;
round(x,2): 将x这个数保留2为小数
4.4.2分组查询
语法
select .....
from 表名
where 分组前的条件
group by 分组列
having 分组后的条件
order by 排序列
-- 查询每个部门的平均工资
select deptno 部门编号,avg(sal) 平均工资 from emp group by deptno;
-- 查询 部门编号, 每个部门的员工人数
select deptno 编号,count(*) 员工的数量 from emp group by deptno;
-- 查询平均工资大于2000的部门编号 和 该部门的平均工资
select deptno 部门编号,avg(sal) 平均工资 from emp group by deptno having avg(sal) > 2000;
-- 查询每个部门中秘书的平均工资
select deptno, avg(sal) from emp where job='秘书' group by deptno;
四.多表查询
1. 简介
同时从多张表中查询出数据,多张表之间一定联系
2.基本用法
2.1语法
-
查询两张表数据
select * from 表1,表2; select * form emp,dept; -- 重复数据(笛卡尔积)
-
通过两张表中关联的列(字段)进行比较,去笛卡尔积
select ..... from 表1,表2 where 条件 表1.列名 = 表2.列名 -- ( 用外键关联) -- 员工 编号 姓名 工资 部门名称 部门位置 select empno,ename ,sal,dname,loc from emp,dept where emp.deptno = dept.deptno and sal > 3000;
3.连接查询
3.1 内连接
语法:
select ...
from 表1 inner join 表2 on 多表的关联关系
-- 查询所有学生的 sno cname degree;
select sno,cname,degree from score inner join course on score.cno=course.cno;
3.2 外连接
分类:
-
左外连接 left join … on 以左边的表为主表,无论怎样都会显示主表的数据
-
右外连接 right join… on 以右边的表为主表,无论怎样都会显示主表的数据
~~~mysql
select sno,cname,degree from score left join course on score.cno=course.cno;
select sno,cname,degree from score right join course on score.cno=course.cno;
~~~
五.子查询
1.简介
一个查询中嵌套另一个完整的查询,称为子查询
- 子查询放在小括号中
- 子查询可以出现任意位置 select from where
2.用法
2.1语法
select (子查询)
from (子查询)
where (子查询)
....
2.2 示例
-- 查询工资比 7566 高的员工信息
select * from emp where sal > (select sal from emp where empno=7566);
-- 子查询与运输符一起使用,子查询的结果只能返回一个结果
-- 查询工资比7654 高,同时又与 7900从事相同工作的员工信息
select * from emp where sal>(select sal from emp where empno=7654) and job = (select job from emp where empno=7900);
-- 查询工资最低的员工姓名
select ename from emp where sal = (select min(sal) from emp);
-- 查询工资高于公司 平均工资的员工信息
select * from emp where sal>(select avg(sal) from emp);
六.常用函数
1.字符串函数
-
concat(s1,s2,s3….) 拼接字符串
select concat(‘aa’,‘bb’,‘cc’)
select concat(‘aa’,‘bb’,‘cc’) from dual;
select CONCAT(‘编号为’,empno,‘的员工姓名为’,ename) from emp;
注:dual表是MySQL提供的一张虚拟表,主要是为了满足select…from…语法习惯,一般测试时使用,无实际意义 -
lower(s) 将字符串变为小写select lower(‘Hello’) from dual
-
upper(s) 将字符中变为大写select upper(‘Hello’) from dual
-
length(s) 获取字符串的长度select length(‘hello’) from dual
-
reverse(s) 将字符串反转select reverse(‘hello’) from dual
-
trim(s) 去除字符串两边的空格select trim(’ hello ') from dual,还有ltrim()和rtrim(),去除左边或右边的空格
-
replace(s,s1,s2) 将字符串s中的s1替换为s2
select replace(‘hello world’,‘o’,‘xx’) from dual -
repeat(s,n) 将字符串s重复n次后返回 select repeat(‘hello’,3) from dual
-
lpad(s,len,s1) 在字符串s的左边使用s1进行填充,直至长度为len
select lpad(‘hello’,8,‘x’) from dual -
rpad(s,len,s1) 在字符串s的右边使用s1进行填充,直至长度为lenselect rpad(‘hello’,8,‘x’) from dual
-
substr(s,i,len) 从第i个位置开始对字符串s进行截取,截取len个
select substr(‘hello’,2,3) from dual
2.数值函数
- ceil(n) 返回大于n的最小整数 select ceil(10.1) from dual
- floor(n) 返回小于n的最大整数 select floor(10.1) from dual
- round(n,y) 将n保留y位小数,四舍五入 select round(3.1415,3) from dual
- truncate(n,y) 将n保留y位小数,不四舍五入 select truncate(3.1415,3) from dual
- rand() 返回0到1的随机数 select rand() from dual
3.日期和时间函数
-
now() 返回当前日期时间 select now() from dual
-
curdate() 返回当前日期 select curdate() from dual
-
curtime() 返回当前时间 select curtime() from dual
-
year(date) 返回日期中的年 select year(‘2018-2-14’) from dual
-
month(date) 返回日期中的月 select month(‘2018-2-14’) from dual
-
day(date) 返回日期中的日 select day(‘2018-2-14’) from dual
-
timestampdiff(interval,datetime1,datetime2) 返回两个日期时间之间相隔的整数,
单位由interval定义
interval可取值:year、month、day、hour、minute、second
select timestampdiff(day,‘1993-9-23’,‘2018-11-22’) from dual -
date_format(date,pattern) 格式化日期
select date_format(now(),’%Y年%m月%d日 %H:%i:%s’) from dual
格式化参数:
%Y 表示四位数字的年
%m 表示两位数字的月
%d 表示两位数字的日
%H 表示两位数字的小时,24小时制
%i 表示两位数字的分钟
%s 表示两位数字的秒数
4.流程控制函数
-
if(f,v1,v2) 如果f为真,则返回v1,否则返回 v2 select if(5>2,‘yes’,‘no’) from dual
-
ifnull(v1,v2) 如果v1不为null,则返回v1,否则返回v2 select ifnull(null,‘0’) from dual
-
case when f1 then v1 when f2 then v2….else v end 如果f1为真,则返回v1;如果f2为真,则返回v2…否则返回v
select case when 5>2 then ‘yes’ end from dual;
select case when 5<2 then ‘yes’ else ‘no’ end from dual;
select case when 5<2 then 'one ’
when 6>4 then ‘two’
else ‘three’ end
from dual;
七.事务
1.简介
transaction 事务, 用来保证数据的操作完整性
一个业务中由多个小的业务构成,要么都成功,要么都失败
事务的特性:(ACID)
- 原子性(Atomicity): 不可再分
- 一致性(Consistentcy):要保证数据前后的一致性
- 隔离性(isolation): 两个事务的操作互不干扰
- 持久性(durability):一旦事务提交,不可回滚
2.事务
mysql默认的是自动提交事务,将一条sql语句都作为一个独立的事务操作
查询提交模式: show variables like ‘autocommit’
设置手动提交: set autocommit = off
提交:commit
回滚: rollback
八.用户和权限管理
1.创建用户并授权
语法:
grant 权限列表 on 库名.表名 to 用户@地址 identified by '密码';
grant select on abc.emp to jack@localhost identified by '123';
grant select,update on abc.emp to jack@localhost identified by '123';
grant all on abc.* to jack@localhost identified by '123';
grant all on *.* to jack@localhost identified by '123';
只有管理员才具有创建用户的权限
test库是安装时默认创建的,默认情况下所有的用户对该数据库具有最大权限
2.查询权限
show grants; -- 查看自己的权限
show grants for 用户名@地址; -- 查看其他用户权限
3.撤销权限
revoke 权限列表 on 库名.表名 from 用户@地址
revoke select on abc.emp from tom@localhost;
4.删除用户
drop user 用户名@localhost;
use mysql;
delete from user where User='用户名'
练习:
-- 基本查询
1、 查询Student表中的所有记录的Sname、Ssex和Class列。
select sname,ssex,classs from student;
2、 查询教师所有的单位即不重复的Depart列。
select distinctdepart from teachaer
3、 查询Score表的所有记录。
select * from score
4、 查询Score表中成绩在60到80之间的所有记录。
select * from score where degree between 60 and 80;
5、 查询Score表中成绩为85,86或88的记录。
select * from score where degree in(85,86,88);
6、 查询Student表中“95031”班或性别为“女”的同学记录。
select * from student where ssex='女' or class = 95031
7.查询分数大于70,小于90的Sno列。
select sno from score where degree between 70 and 90;
8.查询选修某课程的同学人数多于5人的教师姓名。
9.查询95033班和95031班全体学生的记录。
select * from student where class int(95033,95031);
10.查询Student表中不姓“王”的同学记录。
select * from student where sname not like '王%';
-- 排序
1.查询Score表中的最高分的学生学号和课程号。
select sno, cno from score order by degree desc limit 0,1;
2.以Cno升序、Degree降序查询Score表的所有记录
select * from score order by cno asc,degree desc;
-- 聚合函数
1、 查询“95031”班的学生人数。
select count(*) from student where class='95031';
2.查询Student表中最大和最小的Sbirthday日期值。
select max(sbirthday),min(sbirthday) from student;
-- 分组
1、 查询每门课的平均成绩。
select cno, avg(degree) from score group by cno;
2、查询至少有2名男生的班号。
select class from student where ssex='男' group by class having count(*)>1;
3、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
select avg(degree) from score where cno like '3%' group by cno having count(*) >=5;
-- 多表
1、 查询所有学生的Sname、Cno和Degree列。
select sname,cno ,degree from student ,score where student.sno=score.sno;
select sname,cno ,degree from student s,score sc where s.sno=sc.sno;
select sname,cno ,degree from student,score where sid=sno; -- 两个相关联的列列名不同
2、 查询所有学生的Sno、Cname和Degree列
select sno,cname,degree from score s,course c where s.cno=c.cno;
3、 查询所有学生的Sname、Cname和Degree列
select sname,cname,degree from student,score,course where student.sno=score.sno and score.cno=course.cno;
select sname,cname,degree from student
inner join score on student.sno=score.sno
inner join course on score.cno=course.cno;
mysql和oracle其中也是有不同的