1.Mysql是关系型数据库,特点:
1.使用表存储数据格式同意便于维护
2.使用sql语言操作使用方
2.SQL通用语法
- SQL语句可以单行或多行书写,以分号结尾
- SQL语句可以使用空格和缩进来增强语句的可读性
- MySQL数据库的SQL不区分大小写,关键字建议使用大写
- 注释:
单行注释:–注释内容 或 #注释内容
多行注释:/注释内容/
3.SQL分类
- DDL:数据定义语言,用来定义数据库对象(数据库,表,字段)
- DML:数据操作语言,用来对数据库中的数据进行增删改查
- DQL:数据库查询语言,用来查询数据库中表的记录
- DCL:数据控制语言,用来创建数据库用户、控制数据库访问权限
4.DDL语句:
1.show database 查看所有数据库
2.creat database 创建数据库(if not exists)如果不存在就创建
3.default charset utf8 指定数据库字符集
4.drop database(数据库名)删除数据库 (if exists)如果存在就删除
5.select database() 查询当前所在数据库
5.DDL表操作语句:
1.查询所有表
Show tables
2.查询表结构
Desc 表名
6.DDL表创建:
creat table 表名(
字段1 字段1数据类型[comment 字段1注释],
字段2 字段2数据类型[comment 字段2注释],
字段3 字段3数据类型[comment 字段3注释],
字段n 字段n数据类型[comment 字段n注释],
)[comment 表注释];
注释:[注释] 为可选参数,字段与字段之间用逗号隔开
7.Mysql数据类型:
注意:在设计表时,数据类型影响数据库速度
1.数值型:
2.时间型:
3.字符串型
8.DDL表修改
Alter table 表名 add 字段名 数据类型(长度) [字段注释]
修改字段名和字段类型
Alert table 表名 change 旧字段名 新字段名 类型(长度) [comment注释] [约束]
删除字段
Alter table 表名 drop 字段名
修改表名
Alter table 表名 rename to 新表名
删除表
Drop table [if exists] 表名;
删除指定表,并重新创建该表(删除表数据,保留表结构)
Truncate table 表名;
9.DML(曾删改)
增加数据
Insert into 表名(字段名1,字段名2)values(数据1,数据2);
Insert into 表名 values
修改数据
Update 表名 set 字段 = 数据,字段1 = 数据1 [where 条件]
删除数据
Delete from 表名 [where 条件]
10.DQL(查询)
查询关键字
Select字段列表 from表名列表 where条件列表 group分组字段列表
Having 分组后条件列表 order by排序字段列表 limit分页参数
1. 查询多个字段
Select 字段1,字段2,字段3……from 表名;
Select * from 表名;在实际开发中尽量不要用*
2. 设置别名
Select 字段1[as别名1],字段2[as别名2]… from 表名
3. 去除重复记录
Select distinct 字段列表 from 表名
4. 条件查询
Select 字段列表 from 表名 where 条件列表;
5. 聚合函数
Count 统计数量
Max最大值
Min最小值
Avg平均值
Sum求和
Select 聚合函数[] from 表名;
6. 分组查询
Select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件]
注意:执行顺序:where > 聚合函数 > having
分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义.
7. 排序查询
Select 字段列表 from 表名 order by 字段1 排序方式1 ,字段2 排序方式2.
注意:如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序
8. 分页查询
Select 字段列表 from 表名 limt 起始索引,查询记录数
9. 执行顺序
From>where>group by>hiving>select>orderby>limit
注意:null值不参与聚合函数计算, 排序方式
Asc 升序(默认)
Desc降序
11.DCL(权限控制)
管理数据库用户,控制数据库访问权限
用户管理
1. 查询用户
Use mysql;
Select * from user;
2. 创建用户
Create user ‘用户名’@’主机名’ identifie by ‘密码’
3. 修改用户密码
Alter user ‘用户名’@’主机名’ identified with mysql_native_password by ‘新密码’;
4. 删除用户
Drop user ‘用户名’@’主机名’;
注意:主机名可以用%通配
12.权限控制
1.查询权限
show grants for ‘用户名’@’主机名’;
2.授予权限
Grant 权限列表 on 数据库名,表名 to ‘用户名’@’主机名’
3.撤销权限
Revoke 权限列表 on 数据库名,表名 from ‘用户名’@’主机名’;
13.函数
1.字符串函数
代码示例:
select concat('hello','mysql');
select lower('HELLO');
2.数值函数
代码示例:
select lpad(round(rand()*1000000),6,0);
3.日期函数
代码示例:
select datediff('2022-3-22','1999-7-27')/360;
4.流程控制函数
代码示例:
select if(true,'正确','错误');
14.约束
代码示例:
# 约束
create table user1(
id int primary key auto_increment comment '主键',
name varchar(20) not null unique comment '姓名',
age int comment '年龄',
status char(1) default'0' comment '状态',
gender char(1) comment '性别'
) comment '用户表';
注意:约束时作用于表中字段上的,可以在创建表/修改表的时候添加约束,多个约束用空格隔开
关键字自增:auto_increment
1.外键约束
注意:目前两张表在数据库层面并未建立外键关联,所以无法保证数据的一致性和完整性
语法
添加数据库外键:
alter table emp
add constraint fk_emp_dept_id
foreign key (dapt_id) references dept(id);
删除数据库外键:
alter table emp drop foreign key fk_emp_dept_id;
2.删除/更新行为
语法:
set null:
alter table emp add constraint fk_emp_dept_id
foreign key (dapt_id) references dept(id)
on update set null on delete set null ;
Cascade 棘连:
alter table emp add constraint fk_emp_dept_id
foreign key (dapt_id) references dept(id)
on update cascade on delete cascade ;
15.多表查询
1. 内连接
2. 外连接
左外连接
语法:Select 字段列表 from 表1 left[outer] join 表2 on 条件;
outer可以省略
右外连接
语法:Select 字段列表 from 表1 right[outer] join 表2 on 条件;
outer可以省略
3. 自连接
自连接查询可以是内连接也可以是外连接
语法:Select 字段列表 from 表a 别名a join 表2 别名2 on 条件…;
自连接查询必须取别名
4. 联合查询
5. 对于union查询,就是把多次查询的结果合并起来,形成一个新的查询结果
语法:select 字段列表 from 表a… union [all] select 字段列表 from 表b…;
注意:
1.在使用别名后不能用表名来限定条件
2.对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致
3.Union all会将查询到的数据直接合并在一起,union会对合并之后的数据去重
4.在实际开发中使用左连接比较多,且右连接可以变成左连接
16.事务
介绍:事务是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。
个人理解:事务包含多条语句,在事务未提交时只存储于缓存中,并没有修改数据,只有提交了事务才修改表中的数据
查询事务状态
select @@autocommit;
设置事务状态
Set @@ autocommit=0;
0是手动开启,1是自动开启
提交事务
commit;
回滚事务
rollback;
不修改事务状态时启动事务
开启事务
Start transact 或 begin
提交事务
commit
回滚事务
rollback
注:事务在没有提交时表数据不会修改,程序如果报错就要回滚,否则提交数据会
代码示例:
第一种事务开启方式:
# 设置事务为手动提交
set @@autocommit = 0;
# 0手动,1自动
# 转账操作,张三转账给李四
# 1.查询张三账户余额
select * from bank where name = '张三';
# 2.将张三余额-1000
update bank set money = money - 1000 where name = '张三';
程序执行报错...
# 3.将李四余额+1000
update bank set money = money + 1000 where name = '李四';
# 提交事务
commit;
# 回滚事务
rollback ;
第二种事务开启方式:
# 开启事务
start transaction ;
# 1.查询张三账户余额
select * from bank where name = '张三';
# 2.将张三余额-1000
update bank set money = money - 1000 where name = '张三';
程序执行报错...
# 3.将李四余额+1000
update bank set money = money + 1000 where name = '李四';
# 提交事务
commit;
# 回滚事务
rollback ;
并发事务问题
脏读:有两个事务,事务A和事务B,在B操作增加数据但还没提交事务时,事务A读取到了事务B的操作的数据。
不可重复读:事务A读取了数据,此时事务B修改了数据,并提交了事务,事务A再次读取数据,事务A读取的两次数据不同。
幻读:事务A查询表数据发现表中没有数据id列表没有值,此时事务B又插入了一条数据id=1,而事务A也插入了一条id=1的数据。
事务的隔离级别就是解决事务并发的问题
查看事务隔离级别
Select @@transaction_isolation;
设置事务隔离级别
Set [session | global] transaction isolation level{read uncommitted | read committed | repeatable read | serializable }
session是当前会话窗口有效
global是所有窗口都有效
{}括号中选一种隔离级别