Mysql服务器启动
cmd-->services.msc 打开服务的窗口
使用管理员打开cmd
*net stop mysql :关闭mysql服务器
*net start mysql :启动mysql服务器
Mysql登录
1. mysql -uroot -p密码
2.mysql -hip -uroot -连接目的的密码
3.mysql --host=ip --user=root --password=连接目标的密码
MYSQL的退出
1.exit 2.quit
MYSQL的目录结构
1.MYSQL安装目录
*配置文件 my.ini
2.MYSQL数据目录
*数据库:文件夹
*表:文件
*数据:数据
如何操作数据库
#SQL
1.什么是SQL? 结构化查询语言
(Structures Query Language)
其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方称作“方言”
2. SQL通用语法
(1)SQL语句可以单行或多行书写,以分号结尾
(2)可以使用空格和缩进增强可读性
(3)MYSQL数据库的SQL语句不区分大小写
(4) *单行注释 -- 注释内容或 # 注释内容(mysql特有)
*多行注释 /*注释*/
3.SQL分类
*DDL (操作数据库,表)
*DML(增删改表中的数据)
*DQL (查询表中的数据)
*DCL (授权)
3.1 DDL(操作数据库,表)
*操作数据库:CRUD
1.C(create):创建
*创建数据库:
*create database 数据名称;
*创建数据库,判断不存在,在创建:
*create database if not exists 数据库名称;
*创建数据库并指定字符集:
*create database 数据库名称 character set 字符集;
*创建first4数据库,判断是否存在,并且指定字符集为GBK
*create database if not exists first4 character set gbk;
2.R(retrieve):查询
*查询所有数据库名称:show databases;
*查看某个数据库的字符集:show create database 数据库名称;
3.U(update):修改
*修改数据库的字符集
*alter database 数据库名称 character set 字符集名称;
4.D(delete):删除
*删除数据库
*drop database 数据库名称;
*判断数据库是否存在,在删除:
*drop database if exists 数据库名称;
5.使用数据库
* 查询当前正在使用的数据库名称:
*select database();
*使用数据库:
*use 数据库名称;
3.1.1 操作表
1.C(create):创建
*语法:
create table 表名(
列名1 数据类型1,
列名2 数据类型2,
.......
列名n 数据类型n
);*注意:最后一列,不需要加逗号(,)
*数据类型:
1.int:整数类型
*age int,
2.double:小数类型
*score double(5,2),
3.data:日期,只包含年月日, yyyy-MM-dd
4.datatime:日期,包含年月日时分秒 yyyy-MM-dd HH:mm:ss
5.timestamp:时间错类型 yyyy-MM-dd HH:mm:ss
*如果将来不给这个字段赋值,或赋值为null,则默认使用当前的系统时间,来自动赋值
6.varchar :字符串
*name varchar(20):姓名最大的20个字符
*创建表
create table student(
id int,
name varchar(32),
age int,
score double(4,1),
birthday date,
insert_time timestamp
);
*复制表:
*create table 表名 like 被复制的表名;
2.R(retrieve):查询
*查询某个数据库中所有表的名称:
*show tables;
*查询表结构:
*desc 表名称;
3.U(update):修改
1.修改表名
alter table 表名 rename to 新的表名;
2.修改表的字符集
alter table 表名 character set 字符集名称;
3.添加一列
alter table 表名 add 列名 数据类型;
4.修改列名称类型
alter table 表名 change 旧列名 新类别 新数据类型;
alter table 表名 modify 列名 新数据类型;
5.删除列
alter table 表名 drop 列名;
4.D(delete):删除
*删除表:
*drop table 表名;
*drop table if exists 表名;
##DML(增删该表中的数据)
1.添加数据:
*语法:
insert into 表名(列名1,列名2,........,列名n) valuse(值1,值2,.......,值n);
*注意:
1.列名和值要一一对应
2.如果表名后,不定义列名,则默认给所有列添加值
*insert into 表名 valuse(值1,值2,.......,值n);
3.除了数字类型,其他类型需要用引号引起来
2.删除数据
*语法:
*delete from 表名 [where 条件];
注:
1.如果不加where条件,则删除表中所有记录
2.如果要删除所有记录
* delete from 表名; --不推荐,有多少条记录就会执行多少次删除记录
* truncate table 表名;-- 先删除表,然后再创建一张一模一样的空表
3.修改数据
*语法
* update 表名 set 列名1=值1,列名2=值2,........[where 条件];
##DQL查询表中的记录
select * form 表名;
1.语法:
select
字段名列表
from
表名列表
where
条件列表
group by
分组字段
having
分组之后的条件
order by
排序
limit
分页限定
2.基础查询
1.查询多个字段
select 字段名1,字段名2,....from 表名;
2.去除重复
distinct
3.计算列
一般可以使用四则运算来计算一些列的值(数值型的计算)
ifnull(表达式1,表达式2)
表达式1:那个字段需要判断是否为null
表达式2:如果该字段为null后的替换值
4.起别名
3.条件查询
1. where字句后跟条件
2.运算符
* >,<,<=,>=,=,<>
* BETWEEN....AND
* IN(集合)
* LIKE: 模糊查询
* 占位符:
* _:单个任意字符
* %:任意多个字符
* IS NULL
* and 或 &&
* or 或 ||
* not 或!
DQL:查询语句
1.排序查询
*语法:order by 字句
* order by 排序字段1 排序方式1,排序字段2, 排序方式2.....
排序方式:
*ASC:升序排序(默认) *DESC:降序
*注:如果有多个排序条件,则当排序1条件值一样时,才会判断排序2
2.聚合函数:将一列数据作为一个整体,进行纵向计算
1.count:计算个数
1.一般选择选择非空的列:主键
2.count(*)
2.max:计算最大值
3.min:计算最小值
4.sum:计算和
5.avg:计算平均值
*注意:聚合函数计算,排除null值
3.分组查询:
1.语法:group by 分组字段;
2.注意:
1.分组之后查询的字段:分组字段,聚合函数
SELECT sex,AVG(math)FROM student GROUP BY sex;
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
*非空约束: not null 某一列的值不能为null
1.创建表时添加约束
CREATE TABLE stu1(
id INT,
NAME VARCHAR(20) NOT NULL -- name非空
);
2.-- 创建表完后,添加非空约束
ALTER TABLE stu1 MODIFY NAME VARCHAR(20) NOT NULL;
3.-- 删除name的非空约束
ALTER TABLE stu1 MODIFY NAME VARCHAR(20);
*唯一约束: unique 某一列的值不能重复
1.注意
*唯一约束可以有null值,但是只能有一条记录为null
2.-- 在创建表时,条件唯一约束
CREATE TABLE stu1(
id INT,
phone_number VARCHAR(20) UNIQUE
);
3.-- 在表创建完成后,添加唯一约束
ALTER TABLE stu1 MODIFY phone_number VARCHAR(20) UNIQUE;
4.-- 删除唯一约束
ALTER TABLE stu1 DROP INDEX phone_number;
5.自动增长:
1.概念:如果某一列是数值类型,使用 auto_increment 可以完成值的自动增长
2.在创建表时,添加主键约束,并完成主键的自增长
create table stu(
id int primary key auto_increment, -- 给id添加主键约束
name varchar(20)
);
3. 删除自动删除
ALTER TABLE stu2 MODIFY id INT;
4.添加自动增长
ALTER TABLE stu2 MODIFY id INT AUTO_INCREMENT;
*主键约束:primary key
1.注意:
* 含义:非空切唯一
* 一张表只能有一个字段为主键
* 主键就是表中记录的唯一标识
2.在创建表时,添加主键
create table stu(
id int primary key, -- 给id添加主键
name varchar(20)
);
3.删除主键 // 错误删除:alter table stu modify id int;
alter table stu drop primary key;
4.创建完表后,添加主键
alter table stu modify id int primary key;
*外键约束: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.分类
级联更新:on update cascade
级联删除:on delete cascade
##数据库的设计
1.多表之间的关系:
1.一对一:身份证关系
2.一对多(多对一):部门员工关系
3.多对多:学生和课程
2.实现关系:
1.一对多
实现方式:在多的一方去建立外键,指向一的一方的主键
2.多对多
实现方式:多对多关系实现需要借助第三张中间表,中间表至少包含两个字段,这两个字段
作为第三张表的外键,分别指向两张表的主键
3.一对一
实现方式:一对一实现,可以在任意一方添加唯一外键指向另一方的主键
2.数据库设计的范式:(要遵循后边的范式要求,必须先遵循前边的所有范式要求)
*概念:设计数据时,需要遵循的一些规范。
*分类:
1.第一范式:每一列都是不可分割的原子数据项
*存在问题( 1.存在非常严重的数据重复 2.数据添加时 数据不合法 3.数据删除时会主要信息删除)
2.第二范式:在1NF的基础上,非码属性必须完全依赖于候选码 (在1NF基础上消除非主属性对主码的部分函数依赖)
*几个概念:
1.函数依赖:A-->B,如果通过A属性(属性组)的值,可以确定唯一B属性的值。称B依赖与A
学号-->姓名 (学号,课程名称)-->成绩
2.完全依赖:A-->B 如果A是一个属性组,则B属性值得确定需要依赖A属性组中所有属性值
(学号,课程名称)-->成绩
3.部分函数依赖:如果A是一个属性组,则B属性值得确定只需要依赖于A属性组中某一些值即可
(学号,课程名称)-->姓名
4.传递函数依赖:A-->B,B-->C 如果通过A属性(属性组)的值,可以确定唯一B属性的值,在通过B属性(属性组)的值可以确定唯一C属性的值,则称C传递函数依赖于A
5.码:如果在一张中,一个属性或属性组被其他所有属性所完全依赖,则称这个属性(属性组)为该表的码
*主属性:码属性组中的所有属性
*非主属性:除过码属性组的属性
3.第三范式:在2NF的基础上,任何非主属性不依赖于其他主属性(在2NF基础上消除依赖传递)
##数据库的备份与还原
1.命令行
*语法 mysqldump -u用户名 -p密码 数据库名称 >保存的路径
还原:1.登录数据库
2.创建数据库
3.使用数据库
4.执行文件 source 文件路径
2.图形化工具
##多表查询
*查询语法:
select
列名列表
from
# 创建部门表
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
INSERT INTO dept (NAME) VALUES('开发部'),('市场部'),('财务部');
# 创建员工表
CREATE TABLE emp(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
gender CHAR(1), -- 性别
salary DOUBLE, -- 工资
join_date DATE, -- 入职日期
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES dept(id) -- 外键,关联部门表(部门的主键)
);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-02-14',1);
多表查询的分类:
1.内连接查询:
1.隐式内连接:使用where条件消除无用数据
-- 查询员工信息和对应的部门信息
SELECT*FROM emp,dept WHERE emp.dept_id=dept.id;
-- 查询员工表的名称,性别。部门名称
SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.dept_id=dept.id;
SELECT
t1.name,
t1.gender,
t2.name
FROM
emp t1,
dept t2
WHERE
t1.dept_id=t2.id;
2.显示内连接:
语法:select 字段列表 from 表名 inner join 表名 on 条件
SELECT*FROM emp INNER JOIN dept ON emp.dept_id=dept.id;
3.内连接查询
1.从哪些表查询数据
2.条件是什么
3.查询那些字段
2.外连接查询:
1.左外连接:
*语法:select 字段列表 from 表1 left outer join 表2 on 条件
SELECT t1.*,t2.name FROM emp t1 LEFT JOIN dept t2 ON t1.dept_id=t2.id;
*查询的左表(表1)所有的数据以及其交集部分
2.右外连接:
select 字段列表 from 表1 right outer join 表2 on 条件
*查询的右表(表2)所有的数据以及其交集部分
3.子查询:
*概念:查询中嵌套查询,称嵌套查询为子查询
SELECT *FROM emp WHERE emp.salary=(SELECT MAX(salary) FROM emp);
*子查询不同情况:
1.子查询的结果是单行单列的:
子查询作为条件,使用运算符去判断 运算符:> >= < <= =
*子查询可以作为条件,使用运算符去判断。
-- 查询员工工资小于平均工资的人
SELECT *FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);
2.子查询的结果是多行单列的:
子查询可以作为条件,使用运算符in来判断
-- 查询‘财务部’与'市场部'所有的员工的信息
SELECT id FROM dept WHERE NAME ='财务部' OR NAME='市场部';
SELECT *FROM emp WHERE dept_id=3 OR dept_id=2
SELECT *FROM emp WHERE dept_id IN(SELECT id FROM dept WHERE NAME ='财务部' OR NAME='市场部');
3.子查询的结果是多行多列的:
子查询可以作为一张虚拟表参与查询
-- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
SELECT *FROM dept t1,(SELECT *FROM emp WHERE emp.join_date >'2011-11-11') t2
WHERE t1.id=t2.dept_id; -- 目的是为了查询 在笛卡尔积中 员工的dept_id与部门id相吻合
-- 普通内连接
SELECT *FROM emp t1,dept t2 WHERE t1.dept_id=t2.id AND t1.join_date > '2011-11-11';
*多表查询的联系
##事务
1.事务的基本介绍
1.概念如果一个包含多个步骤的业务操作,被事务管理,那么这些操作要么同时成功,要么同时失败。
2.操作:
1.开启事务:start transaction;
2.回滚:rollback;
3.提交:commit;
2.例子
CREATE TABLE account(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
balance DOUBLE
);
-- 添加数据
INSERT INTO account(NAME,balance) VALUES('zhangsan',1000),('lisi',1000);
SELECT *FROM account;
UPDATE account SET balance=1000;
-- 张三给李四转账500
-- 0.开启事务
START TRANSACTION;
-- 1.张三账户-500
UPDATE account SET balance=balance-500 WHERE NAME='zhangsan';
-- 2.李四账户+500
UPDATE account SET balance=balance+500 WHERE NAME='lisi';
-- 发现执行没有问题,提交事务
COMMIT;
-- 发现出问题了,回滚事务
ROLLBACK;
3.MYSQL数据库中事务默认自动提交
*一条DML(增删改)语句会自动提交一次事务
*事务提交的两种方式:
*自动提交:一条DML(增删改)语句会自动提交一次事务
*手动提交:需要开启事务在提交
*修改事务的默认提交方式:
*SELECT @@autocommit; -- 1 代表自动提交 0 代表手动提交
*修改默认提交方式 SET @@autocommit=0;
2.事务的四大特征:
1.原子性:是不可分割的最小操作单位,要么同时成功,要么同时失败
2.持久性:当事务提交或回滚后,数据库会持久化的保存数据
3.隔离性:多个事务之间,相互独立
4.一致性:事务操作前后,数据总量不变
3.事务的隔离级别
*概念:多个事务之间,相互独立。但如果多个事务操作同一批数据,则会引发一些问题,设置不同的隔离级别就可以解决
*存在问题:
1.脏读:一个事务,读取到另一个事务没有提交的数据
2.不可重复读(虚读):在同一事务中,两次读取到的数据不一样
3.幻读:一个事务操作(DML)数据表中所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改
*隔离级别:
1.read uncommitted:读未提交
*产生问题:脏读,不可重复读,幻读
2.read committed:读已提交(Oracle)
*产生问题:不可重复读,幻读
3.repeatable read:可重复度(MYSQL默认)
*产生问提:幻读
4.serializable:串行化
*解决所有问题
*注意:隔离级别从小到大安全性越来越高,但是效率越来越低
*数据库查询隔离级别:
*select @@tx_isolation;
*数据库设置隔离级别:
* set global transaction isolation level 级别字符串;
##DCL
*SQL分类:
1.DDL:操作数据库和表
2.DML:增删改表中的数据
3.DQL:查询表中数据
4.DCL:管理用户,授权
DCL:管理用户,授权
1.管理用户
1.添加用户:
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
2.删除用户:
DROP USER '用户名'@'主机名';
3.修改用户密码:
UPDATE USER SET PASSWORD=PASSWORD('新密码') WHERE USER='用户名';
SET PASSWORD FOR '用户名'@'主机名'=PASSWORD('新密码');
4.查询用户:
-- 1.切换到mysql数据库
USE mysql;
-- 2.查询user表
SELECT*FROM USER;
*通配符:% 表示可以再任意主机使用用户登录数据库
2.授权:
1.查询权限:
SHOW GRANTS FOR '用户名'@'主机名';
2.授予权限:
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
-- 给用户授予所有权限 在任意数据库 任意表中
grant all on *.* '用户名'@'主机名';
3.撤销权限:
语法:
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';