MySQL
MySQL
概念
存储数据的仓库,可以持久化的保存数据,进行数据管理
前端(html+css+vue)+javaee+数据库 全栈开发
登录
mysql -u用户名 -p密码 -hip
mysql -u用户名 -p 回车 输入密码
mysql --host=ip --user=用户名 --password=密码
添加用户
grant select,insert,update,delete on *.* to root@"别人的IP地址" Identified by "dboomysql"
grant select on *.* to root@"别人的IP地址" Identified by "dboomysql"
grant all on *.* to root@"别人的IP地址" Identified by "dboomysql"
第一个只授予了select、update、insert和delete权限,只能对表数据进行增删改查操作,不能对数据库进行操作,比如添加表、天机表字段、修改表字段等。
第二个是授予了全部权限。
grant select on temp.temp1 to 'xiaogang'@'%';
grant 权限 on 数据库.数据表 to '用户' @ '主机名';
create user xiaogang identified by '123456';
create user 用户名 identified by '密码';
退出
exit
quit
sql语句
结构化查询语言
-
多行或者单行书写,以分号结束
-
可以使用空格和缩进来增强语句的可读性
-
不区分大小写的,建议关键字大写
-
/*注释内容*/ -- 注释内容
语言分类
- DDL:数据定义语言,主要对数据库,表,列等进行操作 create drop alter等
- DML:数据操作语言,对数据库中的表数据进行增删改 insert delete update等
- DQL:数据查询语言,对数据进行查询 select where 等等
- DCL:数据控制语言,用来定义访问权限和安全级别的
DDL
数据库操作
查询
show databases;--查询所有的数据库
show create database 数据库名;--查询某个数据库的字符集
创建
create database 数据库名;--创建数据库
create database if not exists 数据库名;--创建数据库,判断不存在的话,再创建
create database 数据库名 character set 字符集名字; utf8 gbk
删除
drop database 数据库名;--删除数据库
drop database if exists 数据库名;--判断数据库是否存在,存在再删除
修改
alter database 数据库名 character set 字符集名字;--修改数据库字符集
使用数据库
select database();--查询正在使用的数据库
use 数据库名;--使用数据库
表操作
create table 表名(
列名 数据类型,
列名 数据类型,
...
);
最后一列,不要加逗号
创建一张学生表,包含编号,姓名,性别,年龄,成绩,生日,入学时间
create table student(id int,name varchar(100),sex char(1),age int,score double(4,1),birthday date,insert_time timestamp);
查询库中所有的表:
show tables;
查询表结构:
desc 表名;
复制表:
create table 表名 like 被复制的表名;
删除表:
drop table 表名;
drop table if exists 表名;
修改表名:
alter table 表名 rename to 新的表名;
修改列名,数据类型:
alter table 表名 change 列名 新列名 新的数据类型;
alter table 表名 modify 列名 新的数据类型;
删除一列:
alter table 表名 drop 列名;
添加一列:
alter table 表名 add 列名 数据类型;
修改表的字符集:
alter table 表名 character set 字符集名称;
数据类型
整数:int bigint
小数:double double(5,2)--999.99
定长字符串:char(1)
可变字符串:varchar(长度)
文本:text
日期: date 只包含年月日 yyyy-MM-dd
datatime 包含年月日时分秒 yyyy-MM-dd HH:mm:ss
timestamp:时间戳包含年月日时分秒,如果不给字段赋值,或者赋值为null,那么这个类型会将系统当前时间进行赋值
DML数据的增删改
添加数据:
insert into 表名(列名,列名...)values(值1,值2...);
列名和值一一对应
如果表名后,不定义列名,则默认给所有列添加值
除了数字类型,其他类型需要使用引号(单双都可以)引起来
删除数据:
delete from 表名 [where 条件];
truncate table 表名;——删除表中所有数据,推荐使用这种方式,先删除表,再创建y一张空表
修改数据:
update 表名 set 列名=值,列名=值...[where 条件];
DQL数据的查询
基本查询
select * from 表名;
select 字段名1,字段名2...from 表名;
as 去别名增强结果的可阅读性
ifnull(可能会有空值的列,替换的内容)
select distinct * from 表名 where 字段=值; 去重
运算符 > < <= >= = !=(<>)
&&(and) ||(or) !(not)
between...and
in(集合);
null不能使用=或者!=进行判断,使用is null或者is not null才可以
模糊查询
like
_ 单个任意字符
% 多个任意字符
排序查询
select 列名 from 表名 order by 排序列名 排序方式,排序列名 排序方式...;
asc升序 默认
desc降序
聚合函数
count():计算个数--null不参与计算
max():最大值
min():最小值
sum():和
avg():平均值
聚合函数排除null值
ifnull(),选择非空列进行计算
分组查询
group by 分组字段
分组之后查询的字段:分组字段,聚合函数
分组之后如果有条件限定,需要写having关键字
where和having的区别:
1.where是在分组之前进行限定 having是在分组之后进行限定
2.where后面不可以跟聚合函数,having后面可以跟聚合函数的
分页
select 列名 from 表 limit 开始的索引 每页显示的条数;
select * from stu limit 0,2;
开始的索引=(当前的页码-1)*每页显示的条数
约束
对表中数据进行限定,保证数据的正确性,有效性和完整性
- 主键约束 primary key
- 非空约束 not null
- 唯一约束 unique
- 外键约束 foreign key
- 默认值约束 default
- 检查约束 check(mysql不支持)
默认约束
默认约束:字段不设置值,就使用默认值
create table 表名(
字段名 字段类型 default 值,
字段名 字段类型
);
非空约束
非空约束:值不能为null
1.创建表的时候添加约束
create table 表名(
id int,
name varchar(20) not null
);
2.创建完表后,添加约束
alter table 表名 modify 列名 数据类型 not null;
3.删除约束
alter table表名 modify 列名 数据类型;
唯一约束
唯一约束:值不能重复
1.创建表的时候添加约束
create table 表名(
id int unique,
name varchar(20)
);
唯一和非空约束可以同时给某一列添加
mysql中,唯一约束可以定义多个null,因为null代表的是不确定的意思,不是重复
2.创建完表后,添加约束
alter table 表名 modify 列名 数据类型 unique;
3.删除约束
alter table 表名 drop index 列名;
主键约束
主键约束:
主键必须是唯一不重复的值
主键不能包含NULL值
1.创建表的时候添加约束
create table 表名 (
字段名 字段类型 primary key,
字段名 字段类型
);
create table 表名(
列名 数据类型,
[constraint] [约束名称] primary key(列名)
);
2.建表后单独添加主键约束
alter table 表名 add primary key(字段名);
3.删除主键约束
alter table 表名 drop primary key;
4.主键自增
字段名 字段类型 primary key auto_increment;
//段类型必须是数值类型
外键约束
外键:一张表中的某个字段引用其他表的主键,这个字段称为外键
主表: 将数据给别人用的表
副表/从表: 使用别人数据的表
1.新建表时增加外键约束
create table 表名 (
字段名 字段类型,
字段名 字段类型,
-- 添加外键约束
[constraint 外键约束名] foreign key(外键字段名) references 主表(主键字段名)
);
关键字:
constraint: 表示约束外键约束名: 给外键约束取个名字,将来通过约束名可以删除这个约束
alter table 表名
foreign key(外键字段名): 指定某个字段作为外键
references 主表(主键字段名) : 引用主表的主键的值
2.建表后单独添加外键约束
alter table 从表 add [constraint 外键约束名称] foreign key (外键字段名) references 主表(主键字段名);
软件设计步骤
需求分析——设计——编码——测试——安装部署
数据库设计
表关系
一对多实现方式
- 在多的一方建立外键关联一的一方主键
多对多实现方式
- 建立第三张中间表
- 中间表至少包含2个外键,分别关联双方主键
一对一实现方式
- 在任意一方建立外键,关联对方主键,并设置外键唯一
ER图
实体关系图
- 实体就是表
- 实体的属性就是表中的列
- 实体和实体的关系就是表和表的关系
多表查询
隐式内连接
select * from 表名1,表名2 where 表名1.外键=表名2.主键
显示内连接
select * from 表名1 inner join 表名2 on(表名1.外键=表名2.主键)
左外连接(显示左表中所有数据 )
select * from 左表 left outer join 右边 on(左表.外键=右边.主键)
右外连接(显示右表中所有数据 )
select * from 左表 right outer join 右边 on(左表.外键=右边.主键)
子查询
子查询结果是单列
select 查询字段 from 表 where 字段 = (子查询);
子查询结果是多行单列
select 查询字段 from 表 where 字段 in(子查询);
子查询结果是多列
select 查询字段 from(子查询) 表别名 where 条件;
事务
- 数据库的事务 (Transaction) 是一种机制、一个操作序列,包含了一组数据操作命令(事务包含多条SQL语句)
- 事务把所有的命令作为一个整体一起向系统提交或撤销操作请求,即这一组数据库命令要么同时成功,要么同时失败
- 事务是一个不可分割的工作逻辑单元
- 事务的作用是为了保证数据库中数据的准确性或者安全性
- 事务中存放的都是增删改的语句,增删改的语句会影响数据库中的数据
- 每一条增删改的语句都在各自的事务中
- 执行完毕之后事务会进行提交,把执行结果永久到保存到数据库中
begin;(start transaction)-- 开启事务
update emp set sal = sal-100 where ename = 'SMITH'
update emp set sal = sal+100 where ename = 'ALLEN'
commit;-- 提交事务
rollback;-- 回滚事务 (SQL语句执行失败,sql执行结果全部回退)
事务特性
事务特性 | 含义 |
---|---|
原子性(Atomicity) | 事务是不可分割的最小操作单位,要么同时成功,要么同时失败 |
一致性(Consistency) | 事务前后数据的完整性必须保持一致 |
隔离性(Isolation) | 是指多个事务并发访问数据库时,一个事务不能被其他的事务干扰,多个并发事务之间数据要相互隔离,不能相互影响 |
持久性(Durability) | 事务一旦提交或者回滚,他对数据库中的数据的改变就是永久的 |
mysql语句执行顺序
sql语句的执行顺序为
from子句
where 子句
group by 子句
having 子句
order by 子句
select 子句