MySQL 操作 语法记录
MySQL登录
- mysql -u root -p 密码
- mysql -h ip -u root -p 连接目标的密码
- mysql --host=op --user=root --password=连接目标的密码
MYSQL退出
- exit
- quit
MySQL目录结构
MySQL安装目录
- 配置文件 mysql.ini
MySQL数据目录
几个概念
- 数据库：文件夹
- 表：文件
- 数据：数据
什么是SQL
- Structured Query Language：结构化语言
- 定义了操作所有关系型数据的规则。每一种数据库的操作存在不一样的地方，称为“方言”。
SQL通用语法
- SQL 语句可以单行或者多行书写，以分号结尾。
- 可使用空格和缩进来增强语句的可读性。
- MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
- 3种注释
- 单行注释：-- 注释内容 //两个横杠一个空格之后加注释内容
- #注释内容
- 多行注释：/注释/
SQL的分类
- DDL(Data Definition Language)：数据定义语言，用来定义数据库对象：数据库，表，列等。关键字：create, drop, alter 等。
- DML(Data Manipulation Language)：数据操作语言，用来对数据库种表的数据进行增删改。 关键字：insert, delete, update 等。
- DQL(Data Query Language)：数据查询语言，用来查询数据库中的表的记录（数据）。关键字：select where 等。
- DCL(Data Control Language)：数据控制语言（了解），用来定义数据库的访问权限和安全级别，以及创建用户。关键字：GRANT,REVOKE等。
DDL
操作数据库：CRUD
- C(Create)：创建
- 创建数据库：
- create database 数据库名称；
- 创建数据库，判断不存在，在创建：
- create database if not exists 数据库名称；
- 创建数据库，并指定字符集：
- create database 数据库名称 character set 字符集名称
- 联系：创建db4数据库，判断是否存在，并指定字符集为gbk
- create database if not exists db4 character set gbk;
- 创建数据库：
- R(Retrieve)：查询
- 查询所有数据库的名称：
show datebases; - 查看某个数据库的字符集：
show create database 数据库名；
- 查询所有数据库的名称：
- U(Update)：修改
- 修改数据库的字符集
- alter database 数据库名称 character set 字符集名称；
- 修改数据库的字符集
- D(Delete：)删除
- 删除数据库
- drop database 数据库名称；
- 判断数据库存在，存在再删除
- drop database if exists 数据库名称；
- 删除数据库
- 使用数据库
- use 数据库名称；
操作表
C(Create):创建
-
创建
create table 表名(
列名1 数据类型1，
列名2 数据类型2，
…
列名n 数据类型n); //最后一列不加逗号
-
数据库类型
- int:整数类型
age int, - double:小数类型“
score double(5,2), - date:日期，只包含年月日，yyyy-MM-dd
- datetime:日期，包含年月日时分秒 yyyy-MM-dd HH:mm:dd
- timestamp:时间戳类型 包含年月日时分秒 yyyy-MM-dd HH:mm:ss
- varchar:字符串类型
name varchar(20),姓名最大20个字符
- int:整数类型
-
复制表
- create table 表名 like 被复制的表名
R(Retrieve):查询
- 查询数据库中所有的表的名称
show tables - 查询表结构
desc 表名
U(Update):修改
- 修改表名
alter table 表名 rename to 新的表名; - 修改表的字符集
alter table 表名 character set 字符集名称； - 修改列名称 类型
alter table 表名 change 列名 新的列名 新的数据类型；
alter table 表名 modify 列名 数据类型 - 删除列
alter table 表名 drop 列名;
D(Delete):删除
- 删除表
- drop table 表名；
- drop table if exists 表名；
DML:增删改表中数据
添加数据：
- 语法：
- insert into 表名(列名1，列名2，列名3，…，列名n) values(值1，值2，值3，…，值n);
- 注意
- 列名和值要一一对应
- 如果表名后，不定义列名，则默认给所有列添加值
insert into 表名 values(值1，值2，…，值n)
删除数据
- 语法：
- delete from 表名[where 条件]
- 注意：
- 如果不加条件，则删除表中所有的记录。
- 如果要删除所有记录
- delete from 表名；//推荐使用，有多少条记录就会执行多少次删除操作
- TRUNCATE TABLE 表名； //推荐使用，效率更高，先删除表，然后创建一张一模一样的表
修改数据
- 语法：
- update 表名 set 列名1 = 值1，…，列名2 = 值2[where 条件]；
- 注意：
- 如果不加任何条件 ，则会将表中所有记录全部修改
DQL:查询表中的数据
语法
select
字段列表
from
表名列表
where
条件列表
group by
分组字段
having
分组之后的条件
order by
排序
limit
分页限定
基础查询
-
多个字段的查询
select 字段名1，字段名2，…from 表名;
-
去除重复
select distinct 字段名1，字段名2，…from 表名;
- 重复是只的所有字段全部相同
-
计算列
- 一般可以使用四则运算计算一些列的值。（一般只会进行数值型的运算）
- ifnull（表达式1，表达式2）：null参与的运算，计算结果都为null
- 表达式1：哪个字段需要判断是否为null
- 如果该字段为null后的替换值。
-
起别名
- as：as可以用空格代替
条件查询
- where子句后面跟条件
- <=、>=、>、<、=、<>
- select 列名 from 表名 where 属性名 符号 属性值;
- select name from student where age > 18;
- between…and
- select 列名 from 表名 where 属性名 between 属性值 and 属性值;
- select name from student where student.age BETWEEN 10 AND 30;
- 包含端点
- in(集合)
- select 列名 from 表名 where 属性名 in (集合);
- select * from student where age in (21,30);
- like：模糊查询
- 占位符
- ‘_’： 任意单个字符
- ‘%’：任意多个字符
- select name from student where name like ‘张%’;
- 占位符
- is null
- select 列名 from 表名 where 属性名 is null;
- select name from student where score is null;
- and 或 &&
- or 或 ||
- not 或 ！
- <=、>=、>、<、=、<>
排序查询
- 语法：order by 语句;
- order by 排序字段1 排序方法1，排序字段2 排序方法2…;
- 排序方式
- ASC:升序 默认的
- DESC:降序
- 注意：
- 如果有多个排序条件，则当第一个条件相同时，才会判断第二个条件。
聚合函数
- 定义
- 将一列数据作为整体，进行纵向的计算。
- count：计算个数
- 一般选择不包含非空的列：主键
- count(*)
- max：计算最大值
- min：计算最小值
- sum：计算和
- avg：计算平均值
- 注意
- 聚合函数的计算，排除null值
- 解决方案
- 选择不包含非空的列
- IFNULL函数
分组查询
-
语法：group by 分组字段；
-
注意：
- 分组后查询的字段：分组字段，聚合函数
- where 和 having 的区别
- where 在分组前进行限定，如果不满足条件，则不参与分组。havaing 在分组后进行限定，如果不满足条件，则不会被查询出来
- where 后不可以跟聚合函数，having可以进行聚合函数的判断
-
代码示例：
select sex, AVG(math) from student group by sex; select sex AVG(math), COUNT(id) from student where math > 70 group by sex ; select sex AVG(math), COUNT(id) from student where math > 70 group by sex having COUNT(id) > 2;
分页查询
- 语法
- limit 开始的索引，每页查询的条数；
- 公式
- 开始的索引= （当前页码-1）*每页显示的条数
- 代码示例
- select * from student LIMIT 0, 3;
- 注意：不同的数据库实现分页的操作不一样
约束
概念
对表中的数据进行限定，保证数据的正确性，有效性和完整性。
分类
-
主键约束：primary key 值不能为空
-
注意
- 含义：非空且唯一
- 一张表只能有一个字段为主键
- 主键就是表中记录的唯一标识
-
在船舰表时添加主键约束
create table stu( id int primary key, -- 给id添加主键约束 name varchar(20) ); -
删除主键
- alter table stu drop primary key;
-
创建完表后，添加主键
- alter table stu modify id int primary key;
-
自动增长
-
概念：如果某一列是数字类型的，使用 auto_increment 可以来完成完成值的自动增长
-
创建表时，添加主键约束，并且自动增长
create table stu( id int primary key AUTO_INCREMENT, -- 给id添加主键约束 name varchar(20) ); -
删除自动增长：alter table stu modify id int;
-
添加自动增长：alter talble stu modify id int auto_increment;
-
-
-
非空约束：not null
-
创建表的时候添加
create table stu( id int, name varchar(20) not null -- name非空 ); -
创建表后，添加非空约束
alter table stu modify name varchar(20) not null; -
删name非空约束
alter table stu modify name varchar(20);
-
-
唯一约束：unique
-
创建表时，添加唯一约束
create table stu( id int, name varchar(20) UNIQUE -- name唯一 ); -
在创建表后，添加唯一约束
- alter table stu modify name varchar(20) UNIQUE;
-
注意：mysql中，唯一约束限定的列的值可以有多个null
-
删除唯一约束
- alter table stu drop index name;
-
-
外键约束：foreign key，让表与表产生关系，从而保证数据的正确性
-
在创建表的时候，可以添加外键
-
语法
create table 表名{…
外键列，
constraint 外键名称 foreign key (外键名称) references 主表名称(主表列名称)
};
-
删除外键
alter table drop foreign key 外键名称； -
创建表之后，添加外键
alter table 表名 add constraint 外键名称 foreign key (外键字段名称) references 主表名称(主表列名称); -
级联操作
- 添加级联操作
- 代码示例
alter table employee add constraint emp_dept_fk foreign key (dep_id) references department(id) on update cascade on delete cascade; - 语法
alter table 表名 add constraint 外键名称 foreign key (外键字段名称) references 主表名称(主表列名称) on update cascade on delete cascade; - 分类：
- 级联更新：on update cascade
- 级联删除：on delete cascade
- 注意：在实际开发中慎用级联操作
- 代码示例
- 添加级联操作
-
数据库设计
多表之间的关系
- 一对一（了解）：
- 如：人和身份证
- 分析：一个人只有一个身份证，一个身份证只能对应一个人
- 一对多（多对一）：
- 如：部门和员工
- 分析：一个部门有多个员工，一个员工对应多个部门
- 多对多：
- 如：学生和课程
- 分析：一个学生可以选择很多们课程，一个课程也可以被很多学生选择
实现多表之间的关系
- 一对多（多对一）：
- 如：部门和员工
- 实现方式：在多的一方建立外键，指向一的一方主键
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qUZD9nmq-1591261337506)(Image\表的一对多关系.png)]
- 多对多：
- 如：学生和课程
- 实现方式：创建一个中间表，进行两个表的对应
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7HuuaAVz-1591261337514)(Image\表的多对多关系.png)]
- 一对一（了解）：
- 如：人和身份证
- 实现方式：在任意一方添加唯一外键指向另一方的主键
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fLHlBZhD-1591261337521)(Image\表的一对一关系.png)]
数据库设计的范式
概念
设计数据库时，需要遵守的一些规范。要遵循后面的范式要求，必须先遵循前面所有范式要求
设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。
分类
- 第一范式：每一列都是不可分割的原子数据项
- 第二范式：在1NF的基础上，非码属性必须完全依赖于码（在1NF的基础上消除非主属性对主码的部分函数依赖）
- 几个概念
- 函数依赖：
- A—>B，如果通过A属性(属性组)的值，可以确定唯一B属性的值。则称B依赖与A
- 例如：学号—>姓名。 （学号，课程名称）---->分数
- 完全函数依赖：
- 如果A是一个属性组，则B属性值确定只需要依赖于A属性组中所有属性的值。
- 例如：（学号，课程名称 ）-- >分数
- 部分函数依赖：
- 如果A是一个属性组，则B属性值确定只需要依赖于A属性组中的某一些值即可。
- 例如：（学号，课程名称 ）-- >姓名
- 传递函数依赖
- A–>B, B–>C, 如果通过A属性（属性组）的值，可以唯一确定B属性的值，通过B属性（属性组）的值，可以唯一确定C属性的值。
- 例如：学号–>系名 系名–>系主任
- 码：
- 如果在一张表中，一个属性或属性组，被其他所有属性完全依赖，则称这个属性（属性组）为该表的码。
- 例如：该表中码为：（学号，课程名称）
主属性：码属性组中的所有属性
非主属性：除码属性组的属性
- 函数依赖：
- 几个概念
- 第三范式：在2NF基础上，任何非主属性不依赖于其他非主属性（在2NF基础上消除传递依赖）
- 巴斯-科德范式：
- 第四范式：
- 第五范式：
数据库的备份与还原
- 语法
- 备份：mysqldump -u用户名 -p密码 >保存的路径
- 还原：
- 登录数据库
- 创建数据库
- 使用数据库
- 执行
多表查询
查询语法
select
列名列表
from
表名列表
where…
多表查询的分类
-
概念：
- 笛卡尔积：有两个集合A,B去这两个集合所有的组成情况
-
内连接查询：
- 隐式内连接：使用where条件来消除无用的数据
- 显示内连接：select 字段列表 from 表名1 [inner] join 表名2 on 条件
- 例如：
- select * from emp inner jion dept on emp.‘dept_id’ = dept.‘id’;
- select * from emp jion dept on emp.‘dept_id’ = dept.‘id’;
-
外连接查询：
- 左外连接：
- 语法：select 字段列表 from 表1 left [outer] join 表2 on 条件；
- 查询的是左表的所有数据机器交集部分
- 右外连接
- 语法：select 字段列表 from 表1 右 [outer] join 表2 on 条件；
- 查询的是右表所有数据及其交际部分
- 左外连接：
-
子查询：
-
概念：查询中嵌套查询，成嵌套查询为子查询
-
子查询的不同情况
-
子查询的结果是单行单列的：
- 子查询可以作为条件，使用运算符去判断
- 示例：
- select * from emp where emp.salary < (select avg(salary) from emp);
-
子查询的结果是多行单列的：
- 子查询可以作为条件，使用运算符in来判断
- 示例：
- 一般查询：
- select id from dept where name = ‘财务部’ or name = ‘市场部’;
select * from emp where dept_id =3 or dept_id = 2;
- select id from dept where name = ‘财务部’ or name = ‘市场部’;
- 子查询：
- select * from emp where dept_id in (select id from dept where name in (‘财务部’ ,‘市场部’));
- 一般查询：
-
子查询的结果是多行多列的：
-
概念：子查询可以作为一张虚拟表参与查询
-
示例：查询员工入职日期是2011-11-11日之后的员工信息和部门信息
-
子查询
select * from dept t1, (select * from emp where emp.‘join_date’ > ‘2011-11-11’) t2 where t1.id = t2.dept_id; -
普通内连接
select *from emp t1, dept t2 where t1.‘dept_id’ = t2.‘id’ and t1.‘join_date’ > ‘2011-11-11’;
-
-
-
-
事务
事务的基本介绍
- 概念：
如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。 - 操作：
- 开启事务：start transaction;
- 回滚：rollback;
- 提交：commit;
- MySQL数据库中事务默认自动提交
- 事务提交的两种方式：
- 自动提交
- mysql就是自动提交的
- 一条DML（增删改）语句会自动提交一次事务
- 手动提交
- Oracle数据库默认是手动提交事务
- 需要先开启事务在提交
- 修改事务的默认提交方式：
- 查看事务的默认提交方式：SELECT @@autocommit; – 1 代表自动提交 0 代表手动提交
- 修改默认提交方式：set @@autocommit = 0;
- 自动提交
- 事务提交的两种方式：
- 事务的四大特征：
- 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败
- 持久性：当事务提交或者回滚后，数据库会持久化的保存数据
- 隔离性：多个事务之间。相互独立。
- 事务操作前后，数据总量不变
- 事物的隔离级别（了解）：
- 概念：多个事务之间隔离的，相互独立。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题
- 存在问题：
- 脏读：一个事务读取到另一个事务中没有提交的数据
- 不可重复读（虚读）：在同一个事务中，两次读取到的数据不一样
- 幻读：一个事务操作（DML）数据表中所有的记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。
- 隔离级别：
- read uncommitted:读未提交
- 产生问题：脏读，不可重复读，幻读
- read committed：读已提交
- 产生问题：不可重复读，幻读
- repeatable read: 可重复读（MySQL默认）
- 产生问题：幻读
- serializable:串行化
- 可以解决所有的问题
- 注意：
- 隔离级别从小到大安全性越来越高，但是效率越来越低
- 数据库查询隔离级别
- select @@tx_isolation;
- 数据库设置隔离级别
- set global transaction isolation level 级别字符串;
- read uncommitted:读未提交
DCL:管理用户，授权
管理用户
- 添加用户：
- 语法：create user ‘用户名’@‘主机名’ identified by ‘密码’;
- 删除用户：
- 语法：drop user ‘用户名’@‘主机名’;
- 修改用户密码：
- 语法：upadate user set password = password(‘新密码’) where user = ‘用户名’;
- set password for ‘用户名’@‘主机名’ = password(‘新密码’);
- mysql中忘记root用户密码
- net stop mysql 停止mysql服务 //需要管理员权限运行cmd
- 使用五验证方式启动mysql服务
mysqld --skip-grant-tables - 打开新的cmd窗口直接输入mysql命令，敲回车，直接进入mysql
- use mysql
- upadate user set password = password(‘新密码’) where user = ‘用户名’;
- 在任务管理器手动关闭mysqld
- 启动mysql服务使用新密码登录
- 查询用户：
- 切换到mysql数据库
use mysql； - 查询user表
select * from user; - 通配符：%可以表示在任意主机使用用户登录数据库
- 切换到mysql数据库
权限管理
- 查询权限：
- 查询权限
show grants for ‘用户名’@‘主机名’;
- 查询权限
- 授予权限：
- grant 权限列表 on 数据库名.表名 to ‘用户名’@‘主机名’;
给用户授予所有库的所有权限
grant all on “* . *”(没有引号，不用空格) to ‘用户名’@‘主机名’;
- grant 权限列表 on 数据库名.表名 to ‘用户名’@‘主机名’;
- 撤销权限：
- revoke 权限列表 on 数据库名.表名 from ‘用户名’@‘主机名’;
1057
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
