1.3MySQL数据库
MySQL服务启动
命令提示符:
net start mysql:启动mysql服务
net stop mysql: 关闭mysql服务
MySQL登录
1.mysql -uroot
2.mysql -hip(ip为连接目标的IP地址) -root -p连接目标的密码
3.mysql --host=ip --user=root --password=连接目标的密码
SQL
1.SQL(structured query language):结构化查询语言
2.SQL通用语法:
1)以分号为结尾
2)不区分大小写。关键字建议大写
3)注释: 单行:--
多行:/*注释*/
3.SQL分类
1)DDL(Data Definition Language数据定义语言):用来定义数据库对象:数据库、表、列等。关键字:create、alter、drop。
2)DML(Data manipulation language数据操作语言):用来对数据库中表的数据进行增删改。关键字:insert、delete、update。
3)DQL(Data query language数据查询语言):用来查询数据库中表的记录(数据)。关键字:select,where等。
4)DCL(Data control language数据控制语言(了解)):用来定义数据库的访问权限和安全级别,及创建用户。关键字:grant、revoke等。
DDL:操作数据库、表
1.操作数据库:CRUD
1)C(Create):创建
创建数据库
create database (if not exists)数据库名称
创建数据库并设置字符集
create database (if not exists)数据库名称 character set 字符集名称
2)R(Retrieve)查询:
查询所有数据库的名称:
show database;
查询某个数据库的字符集;查询某个数据库的创建语句
show create database 数据库名称;
3)U(Update)修改:
修改数据库的字符集:
alter database 数据库名称 character set 字符集名称;
4)D(Delete)删除:
删除数据库:
drop database (if exists)数据库名称;
5)使用数据库:
查询当前正在使用的数据库名称:
select database();
使用数据库:
use 数据库名称;
2.操作表
1)C(Create):创建
create table 表名(
列名1 数据类型1,
列名2 数据类型2,
.....
列名n 数据类型n
);
复制表
create table 表名 like 被复制的表名;
2)R(Retrieve)查询:
查询某个数据库中所有的表名称:
show tables;
查询表结构
desc 表名;
3)U(Update)修改:
①修改表名
alter table 表名 rename to 新的表名;
②修改表的字符集
alter table 表名 character set 字符集名称;
③添加一列
alter table 表名 add 列名 数据类型;
④修改列名称 类型
alter table 表名 change 列名 新列明 新数据类型;
alter table 表名 modify 列名 新数据类型;
⑤删除列
alter table 表名 drop 列名;
4)D(Delete)删除:
drop table (if exists) 表名;
DML:增删改表中数据
1.添加数据:
insert into 表名 (列1,列2,...列n) values(值1,值2,...值n);
insert into 表名 values(值1,值2,...值n);
注:除了数字类型,其他类型需要使用引号' '或" "
2.删除数据:
delect from 表名 where 条件;
注:
delect from 表名; --不推荐使用,会删除所有数据
truncate table 表名; --推荐使用,效率更高,先删除表,然后再创建一张一样的表。
3.修改数据:
update 表名 set 列名1=值1, 列名2=值2, ...where 条件;
DQL:查询表中的记录
select * from 表名;
1.基本语法
select 列名 from 表名 where 条件
group by 分组
having 分组之后的条件
order by 排序
limit 分页限定
2.基础查询
1)去除重复:distinct
2)计算列
①用四则运算
②null参与的运算,计算结果都为null。这时可能需要用ifnull(出现null的列名,替换值)
3)起别名:as 列名别名(as 可以省略)
3.条件查询
1)where 条件
2)运算符
-- >、<、<=、>=、=、<>
-- between... and
-- in
-- like:模糊查询
占位符:
_ 单个任意字符
& 多个任意字符
-- is null
-- and 或 &&
-- or 或 ||
-- not 或 !
DQL:查询语句
1.排序查询
order by 排序字段1 排序方式1,排序字段2 排序方式2,...
排序方式:
ASC:升序,默认
DESC:降序。
注:如果有多个排序条件,则前一个排序值一样时,才会判断第二条件。
2.聚合函数:将一列数据作为一个整体,进行纵向的计算。
1)count:计算个数
2)max、min、sum、avg
注:聚合函数的计算,排除null值
3.分组查询:
1)语法:group by 分组列名;
2)注意:
where和having的区别:
1.where在分组之前进行限定,如果不满足条件,则不参与分组。having在分组之后进行限定,如果不满足结果,则不会被查询出来。
2.where后不可以跟聚合函数,having可以进行聚合函数的判断。
4.分页查询:
1.语法:limit 开始的索引,每页查询的条数;
2.公式:开始的索引=(当前的页码-1)*每页显示的条数
约束
分类
1.主键约束:primary key
2.非空约束:not null
3.唯一约束:unique
4.外键约束:foreign key
1)非空约束:not null
1.创建表(省略)
2.表已存在,添加非空约束
alter table 表名 modify name 数据类型 not null;
3.删除某列的非空约束
alter table 表名 modify name 数据类型;
2)唯一约束:unique,某一列的值不能重复
1.唯一约束的列的值可以有多个null值
2.删除唯一约束
alter table 表名 drop index 列名;
3.表已存在,添加唯一约束
alter table 表名 modify 列名 数据类型 unique;
3)主键约束:primary key
1.注意:
1)非空且唯一
2)一张表只能有一个字段为主键
2.删除主键
alter table 表名 drop primary key;
3.添加主键
alter table 表名 modify 列名 数据类型 primary key;
4.自动增长
①创建表时,添加主键约束,并且完成主键增长
create table stu(
id int primary key auto_increament
);
②删除自动增长
alter table 表名 modify 列名 数据类型;
③添加自动增长
alter table 表名 modify 列名 数据类型 auto_increament;
4)外键约束:foreign key
1.创建表:
create table 表名(
....
外键列
constraint 外键名称 foreign key (外键列名称)references 主表名称(主表列名称)
);
2.删除外键:
alter table 表名 drop foreign key;
3.添加外键:
alter table 表名 add constraint 外键名称 (外键列名称)foreign key references 主表名称(主表列名称) ;
4.级联操作
1.添加级联操作
alter table 表名 add constraint 外键名称 (外键列名称)foreign key references 主表名称(主表列名称)on update cascade on delete cascade ;
2.分类:
1)级联更新:on update cascade
2)级联删除:on delete cascade
数据库设计
1.多表之间的关系
分类:一对一(了解)、一对多、多对多
2.实现关系
1)一对多:在多的一方建立外键,指向一的一方的主键。
2)多对多:多对多关系实现需要借助第三张中间表,中间表至少包含两个字段,这两个字段作为第三张表的外键,分别指向两张表的主键。
3)一对一:可以在任意一方添加唯一外键指向另一方的主键。
3.数据库设计的范式
分类:
1.第一范式(1NF):每一列都是不可分割的原子数据项
2.第二范式(2NF):在1NF的基础上,非码属性必须完全依赖于码(在1NF的基础上消除非主属性对主码的部分函数依赖)
1)函数依赖:A→B,如果通过A属性(属性组)的值,可以确定唯一B属性的值,则称B依赖于A。
2)完全函数依赖:A→B,如果A是一个属性组,则B属性值的确定需要依赖于A属性组中所有的属性值。
3)部分函数依赖:A→B,如果A是一个属性组,则B属性值的确定只需要依赖于A属性组中某一些值即可。
4)传递函数依赖:A→B,B→C,如果通过A属性(属性组)的值,可以确定唯一B属性的值,在通过B属性(属性组)的值可以确定唯一C属性的值,则称C传递函数依赖于A。
5)码:如果在一张表中,一个属性或属性组,被其他所有属性所完全依赖,则称这个属性(属性组)为该表的码。
注:
主属性:码属性组中的所有属性。
非主属性:除了码属性组的属性。
3.第三范式(3NF):在2NF基础上,任何非主属性不依赖于其他非主属性(在2NF基础上消除传递依赖)
数据库的备份和还原
法一:命令行:
语法:
备份:mysqldump -u用户名 -p密码 >保存路径
还原:
1.登录数据库
2.创建数据库
3.使用数据库
4.执行文件。source 文件路径
法二:图形化工具SQLyog
多表查询
多表查询的分类
1.内连接查询:
1)隐式内连接:使用where条件消除无用数据
select 列名 from 表名 where ...
2)显式内连接:
select 字段列表 from 表名1 [inner] join 表名2 on 条件;
2外连接查询:
1)左外连接:查询的是左表所有数据及其交集部分
select 字段列表 from 表1 left join 表2 on 条件;
2)右外连接:查询的是右表所有数据及其交集部分
select 字段列表 from 表1 right join 表2 on 条件;
3)子查询:
子查询的不同情况:
1.子查询的结果是单行单列的:子查询可作为条件,使用运算符(> >= < <= =)去判断。
2.子查询的结果是多行单列的:子查询可作为条件,使用运算符in来判断。
3.子查询的结果是多行多列的:子查询可作为一张虚拟表参与查询。
事务
1.事务的基本介绍
1)概念:如果一个包含多个步骤的业务操作,被事务管理,那么这些操作要么同时成功,要么同时失败。
2)操作:
①开启事务:start transaction;
②回滚:rollback;
③提交:commit;
3)MySQL数据库中事务默认自动提交
事务提交的两种方式:
①自动提交:
mysql默认自动提交事务
一条DML语句会自动提交一次事务。
②手动提交:
oracle数据库默认手动提交事务
需要先开启事务,在提交
③ 修改事务的默认提交方式:
查看事务的默认提交方式:select @@autocommit;
1代表自动提交 0代表手动提交
修改默认提交方式:set @@autocommit=0;
2事务的四大特征:
1)原子性:是不可分割的最小操作单位,要么同时成功,要么同时失败。
2)持久性:当事务提交或回滚后,数据库会持久化的保存数据。
3)隔离性:多个事务之间,相互独立。
4)一致性:事务操作前后,数据总量不变。
3.事务的隔离级别
1)概念:多个事务之间,相互独立。但是如果多个事务操作同一批数据,则会引发一些问题,设置不同的隔离级别就可以解决这些问题
2)存在问题:
①脏读:一个事务,读取到另一个事务中没有提交的数据。
②不可重复读(虚读):在同一事务中,两次读取到的数据不一样。
③幻读:一个事务操作(DML)数据表中的所有记录,另一个事务添加一条数据,则第一个事务查询不到自己的修改。
3)隔离级别:
①read uncommitted:读未提交
产生的问题:脏读、不可重复读、幻读
②read committed:读已提交(oracle默认)
产生的问题:不可重复读、幻读
③repeatable read:可重复读(MySQL默认)
产生的问题:幻读
④serializable:串行化
可以解决所有问题
注意:隔离级别从小到大安全性越来越高,但是效率越来越低。
数据库查询隔离级别:
select @@tx_isolation;
数据库设置隔离级别:
set global transaction isolation level 级别字符串;
DCL:管理用户,授权
1.管理用户
1)添加用户
create user '用户名'@'主机名' identified by '密码';
2)删除用户
drop user '用户名'@'主机名';
3)修改用户密码
update user set password =password('新密码') where user='用户名';
或
set password for '用户名'@'主机名'=password('新密码');
注意:mysql中忘记了root用户的密码?
1.以管理员用户运行cmd→net stop mysql 停止sql服务
2.使用无验证方式启动sql服务:mysqld --skip-grant-tables
3.打开新的cmd窗口,直接输入mysql命令,敲回车。就可登录成功
4.use mysql;
5.update user set password =password('新密码') where user ='root';
6.关闭两个窗口
7.打开任务管理器,手动结束mysqld.exe的进程
8.启动mysql服务
9.使用新密码登录
4)查询用户:
1.切换到mysql数据库
use mysql;
2.查询user表
select * from user;
注:通配符:%表示可以在任意主机使用用户登记数据库
2.权限管理:
1)查询权限:
show grants for '用户名'@'主机名';
2)授予权限:
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
若授予所有权限
grant all on *.* to '用户名'@'主机名';
3)撤销权限:
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';