MySQL服务启动和关闭
以管理员身份进入命令行窗口
启动服务：net start mysql
关闭服务：net stop mysql
MySQL登录：mysql -uroot -proot1234 mysql -hip地址 -u别人的密码
MySQL退出：exit quit
查看当前版本：SELECT VERSION();
什么是SQL
其实就是定义了操作所有关系型数据库的规则，每一种数据库的操作方式存在不一样的地方，成为“方言”
注释：
单行注释 — 注释（两个-一个空格）或者 #注释
*多行注释 / */

DDL

1.操作数据库CRUD
1.C(Create):创建
2.R(Retrieve)：查询
3.U(Update)：修改
吧wx的字符集改为utf8

4.D(Delete)：删除
删除数据库

存在wx才删除

5使用数据库
use 数据库名称；
查询当前正在使用的数据库名称
*select database();

查询所有的数据库名称 show databases；

查询某个数据库的字符集show create database 数据库名称；

创建数据库

如过不存在mch则创建

创建数据库wx，设置默认字符集为gbk


创建数据库db1，判断是否存在，并指定字符集为gbk
create database if not exists db1 character set gbk;

2.操作表
**1.C(Create):创建
create table 表名(
列名1 数据类型1，
列名2 数据类型2，
列名3 数据类型3，
……..
);
_**最后一列不加逗号
**数据类型
整型—int
age int
__ 小数类型-double**
score double(5,2)—>小数一共5位小数点后保留2位
** 日期类型 date—>只包含年月日，yyyy-MM-dd
datetime日期类型—>包含年月日时分秒 yyyy-MM-dd HH-mm-ss
timestamp日期类型—>_包含年月日时分秒 yyyy-MM-dd HH-mm-ss 如果不给他赋值，或赋值为null。则使用当前系统时间自动赋值
_ 字符串类型varchar
**name varchar(20)—>name最多20个字符

//
create table student(
id int,
name varchar(20),
score double(4,1),
birthday date,
insert_time timestamp
);

//
**2.R(Retrieve)：查询
**查询某个数据库所有表名称
show tables；
查询表结构
desc 表名；
*3.U(Update)：修改
**修改表名
alter table 表名 rename to 新的表名；
修改表的字符集
查看表的字符集

alter table 表名 character set 字符集名称；

添加一列
alter table 表名 add 列名 数据类型；

修改表名称 类型
1.alter table 表名 change 原列名 新列名 新数据类型；

2.alter table student modify sex varchar(20);—>只改变列的数据类型

删除列
alter table 表名 drop 列名；

**4.D(Delete)：删除
**drop table 表名；
drop table if exists 表名；
复制一个表stu；

DML—>增删改表中的数据

1.添加数据
insert into 表名(列名1，列名2，列名3，…，列名n) values(值1，值2，值3…，值n);
注意：列名要和值一一对应，如果表名后面不加列名就是默认给所有的列添加值
2.删除数据
delete from 表名 [where 条件]
注意：如果不加条件，回删除所有记录，如果要删除所有记录有两种方式。
1.delete from 表名； 不推荐使用，效率低
2.truncate table 表名；删除表，在创建一直一模一样的新空表，效率高
3.修改数据
update 表名 set 列名1=值1，列名2=值2…..[where 条件]
**如果不加条件，会将所有记录都修改

DQL—>查询表中的记录

**select<br />字段列表<br />       from<br />表名列表<br /> where<br />条件列表<br /> group by<br />分组字段<br /> having<br />分组之后的条件<br /> order by<br />排序<br /> limit<br />分页限定<br />**计算列**<br />**   ** **一般可以使用四则运算计算一些列的值。<br />**ifnull(表达式1，表达式2)null参与的运算，计算结果都为null。<br />    **表达式1：代表那个字段需要判断为null。<br />    **表达式2：代表这个字段为null时的替换值。<br />***distinct去除重复**<br />![image.png](https://cdn.nlark.com/yuque/0/2021/png/12832295/1616821346486-d2479232-694b-4393-9804-c1a099e292b7.png#align=left&display=inline&height=45&margin=%5Bobject%20Object%5D&name=image.png&originHeight=89&originWidth=367&size=5968&status=done&style=none&width=183.5)

条件查询

*like查询

— 查询姓名中包含德的人
SELECT *FROM student3 WHERE NAME LIKE “%德%”;

运算符

排序查询

语法
order by 排序字段1 排序方式1，排序字段2 排序方式2，……
*ASC—升序 DESC—降序
— 按照数学成绩排序，如果数学一样，则按照英语排序
SELECT FROM student3 ORDER BY math ASC,english ASC;—>当前面的排序条件一样时，才会使用后面的排序条件

聚合函数—>将一列数据作为一个整体，进行纵向计算

1.count 计算个数
SELECT COUNT(id) FROM student3;
SELECT COUNT(*) FROM student3;
2.max 计算最大值
SELECT MAX(math) FROM student3;
3.min 计算最小值
SELECT MIN(math) FROM student3;
4.sum 计算和
SELECT SUM(math) FROM student3;
5.avg 计算平均值
SELECT AVG(math) FROM student3;
**聚合函数的计算回排除null值
比如english有一个为空可以这样处理，或者选择非空列计算
SELECT COUNT(english) FROM student3;— 7
SELECT COUNT(IFNULL(english,0)) FROM student3;— 8

分组查询

group by 分组字段
注意:分组后查询的字段为：分组字段、聚合函数
SELECT sex,AVG(IFNULL(english,0)),COUNT() FROM student3 GROUP BY sex;

english大于等于70才参与分组sql语句
SELECT sex,AVG(IFNULL(english,0)),COUNT() FROM student3 WHERE english>=70 GROUP BY sex;

english大于等于70才参与分组并且分组之后每一组人数要大于两个人sql语句
SELECT sex,AVG(IFNULL(english,0)),COUNT() FROM student3 WHERE english>=70 GROUP BY sex HAVING COUNT(id)>2;
english大于等于70才参与分组并且分组之后每一组数学平均成绩要大于72sql语句
SELECT sex,AVG(IFNULL(english,0)),COUNT() FROM student3 WHERE english>=70 GROUP BY sex HAVING AVG(math)>72;
**where和having之间的区别
where 在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足要求，则不会被查询出来**
where后面不可以跟聚合函数，having可以**
自己理解：where是用来限定分组的，如果不满足后面的条件则不会被分组。having是用来作分组查询的条件，如果不满足后面的条件则不会被查询出来
**

分页查询

语法：limit开始 的索引，每页查询的条数。limit是MySQL的一个方言
_**公式：开始的索引=（当前页码-1）每页显示的条数**_
SELECT FROM student3 WHERE sex IN (‘男’,’女’) LIMIT 0,3;— 第一页
SELECT *FROM student3 WHERE sex IN (‘男’,’女’) LIMIT 3,3;— 第二页

约束：对表中的数据进行限定，保证数据的正确性、有效性，完整性

**主键约束 primary key

**非空且唯一，一张表只能能一个主键，主键就是表中记录唯一标识
_删除主键：_ALTER TABLE stu DROP PRIMARY KEY;
后期添加主键：ALTER TABLE stu MODIFY id INT PRIMARY KEY;

**非空约束 not null

创建表添加非空约束
CREATE TABLE stu(
id INT,
NAME VARCHAR(20) NOT NULL
);
删除这个约束：ALTER TABLE stu MODIFY NAME VARCHAR(20);
后期添加非空约束：ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;

**唯一约束 unique

创建表添加唯一约束
CREATE TABLE stu(
id INT,
phone_number VARCHAR(20) UNIQUE
);
删除这个约束：ALTER TABLE stu DROP INDEX phone_number;
后期添加这个唯一约束：ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;

**外键约束 foreign key

CONSTRAINT 外键名称 FOREIGN KEY(外键列名称) REFERENCES 主表名(主表列名称)
删除外键：ALTER TABLE employee(有外键的表名) DROP FOREIGN KEY emp(外键名称);
后期添加外键：ALTER TABLE employee ADD CONSTRAINT emp(给这个约束起名) FOREIGN KEY(外键列名称) REFERENCES 主表名(主表列名称)
级联更新
级联删除
ALTER TABLE employee ADD CONSTRAINT emp FOREIGN KEY(dep_id) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE;（要更新或者删除主表里主键列去更新或者删除外键列**）

自动增长

如果某一列是数值类型的，使用auto_increment来完成值的自动增长
创建表添加auto_increment
CREATE TABLE stu(
id INT PRIMARY KEY AUTO_INCREMENT,
phone_number VARCHAR(20)
);
删除自动增长：ALTER TABLE stu MODIFY id INT;
**后期添加自动增长：ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

数据库设计

1.多表之间的关系
**1.一对一
如：人和身份证
一个人只有一个身份证，一个身份证只能对应一个人
2.一对多（多对一）
如：部门和员工
一个部门有多个员工，一个员工只能在一个部门
实现方式：在“多”的一方建立外键去指向“一”的一方主键
3多对多
如：学生和课程
一个学生能选多门课程，一门课也能被多名学生选择
实现方式：需要第三张中间表，中间表至少包含两个字段，分别是另外两张表的主键，这两个字段作为中间表的外键，分别指向两个主键。
中间表：

范式

**第一范式（1NF）：每一列都是不可分割的原子项数据

**第二范式（2NF）：在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.码：如果在一个表中，一个属性或者属性组，被其他所有属性完全依赖，则这个属性（属性组）为该表的码
例如：该表的码就是（学号，课程名称）
主属性：码属性组的所有属性
*非主属性：除码属性组的其他属性

**第三范式（3NF）：在2NF的基础上，任何非主属性不依赖于其他非主属性—>消除传递依赖

数据库的备份和还原

多表查询

1.内连接查询
1.隐式内连接
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 条件
查询的是右表全部数据及左右表交集
子查询
**例如1查询工资最高的员工信息
select from 表 where 工资=（select MAX（工资）from 表）
例如2查询财务部和市场部所有员工信息
第一步select id from dept where name=“财务部”or name=“市场部”；
第二部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;
内连接：select *from emp t1，dept t2 where t1.’dept_id’=t2.id and ‘join_date’>’2011-11-11’;

练习：

— 部门表
CREATE TABLE dept (
id INT PRIMARY KEY PRIMARY KEY, — 部门id
dname VARCHAR(50), — 部门名称
loc VARCHAR(50) — 部门所在地
);
— 添加4个部门
INSERT INTO dept(id,dname,loc) VALUES
(10,’教研部’,’北京’),
(20,’学工部’,’上海’),
(30,’销售部’,’广州’),
(40,’财务部’,’深圳’);
— 职务表，职务名称，职务描述
CREATE TABLE job (
id INT PRIMARY KEY,
jname VARCHAR(20),
description VARCHAR(50)
);
— 添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, ‘董事长’, ‘管理整个公司，接单’),
(2, ‘经理’, ‘管理部门员工’),
(3, ‘销售员’, ‘向客人推销产品’),
(4, ‘文员’, ‘使用办公软件’);
— 员工表
CREATE TABLE emp (
id INT PRIMARY KEY, — 员工id
ename VARCHAR(50), — 员工姓名
job_id INT, — 职务id
mgr INT , — 上级领导
joindate DATE, — 入职日期
salary DECIMAL(7,2), — 工资
bonus DECIMAL(7,2), — 奖金
dept_id INT, — 所在部门编号
CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);
— 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
(1001,’孙悟空’,4,1004,’2000-12-17’,’8000.00’,NULL,20),
(1002,’卢俊义’,3,1006,’2001-02-20’,’16000.00’,’3000.00’,30),
(1003,’林冲’,3,1006,’2001-02-22’,’12500.00’,’5000.00’,30),
(1004,’唐僧’,2,1009,’2001-04-02’,’29750.00’,NULL,20),
(1005,’李逵’,4,1006,’2001-09-28’,’12500.00’,’14000.00’,30),
(1006,’宋江’,2,1009,’2001-05-01’,’28500.00’,NULL,30),
(1007,’刘备’,2,1009,’2001-09-01’,’24500.00’,NULL,10),
(1008,’猪八戒’,4,1004,’2007-04-19’,’30000.00’,NULL,20),
(1009,’罗贯中’,1,NULL,’2001-11-17’,’50000.00’,NULL,10),
(1010,’吴用’,3,1006,’2001-09-08’,’15000.00’,’0.00’,30),
(1011,’沙僧’,4,1004,’2007-05-23’,’11000.00’,NULL,20),
(1012,’李逵’,4,1006,’2001-12-03’,’9500.00’,NULL,30),
(1013,’小白龙’,4,1004,’2001-12-03’,’30000.00’,NULL,20),
(1014,’关羽’,4,1007,’2002-01-23’,’13000.00’,NULL,10);
— 工资等级表
CREATE TABLE salarygrade (
grade INT PRIMARY KEY, — 级别
losalary INT, — 最低工资
hisalary INT — 最高工资
);
— 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);
— 需求：
— 1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
SELECT
t1.id,
t1.ename,
t1.salary,
t2.jname,
t2.description
FROM
emp t1,job t2
WHERE
t1.job_id=t2.id;
— 2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
SELECT
t1.id,
t1.ename,
t1.salary,
t2.jname,
t2.description,
t3.dname,
t3.loc
FROM
emp t1,job t2,dept t3
WHERE
t1.job_id=t2.id AND t1.dept_id=t3.id;
— 3.查询员工姓名，工资，工资等级
SELECT
t1.ename,
t1.salary,
t2.grade
FROM
emp t1,salarygrade t2
WHERE
t1.salary BETWEEN t2.losalary AND t2.hisalary;
— 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
SELECT
t1.ename,
t1.salary,
t2.jname,
t2.description,
t3.dname,
t3.loc,
t4.grade
FROM
emp t1,job t2, dept t3,salarygrade t4
WHERE
t1.job_id=t2.id
AND t1.dept_id=t3.id
AND t1.salary BETWEEN t4.losalary AND t4.hisalary;
— 5.查询出部门编号、部门名称、部门位置、部门人数
SELECT
t2.id,
t2.dname,
t2.loc,
COUNT(t1.id)
FROM
emp t1, dept t2
WHERE
t1.dept_id=t2.id
GROUP BY
t1.dept_id;

利用子查询
SELECT
t1.id,
t1.dname,
t1.loc,
t2.total
FROM
dept t1,(SELECT
dept_id,COUNT(id) total
FROM
emp
GROUP BY dept_id
) t2
WHERE
t1.id=t2.dept_id;
— 6.查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询
丶 左外连接
SELECT
t1.ename,
t1.mgr,
t2.id,
t2.ename
FROM
emp t1
LEFT JOIN
emp t2
ON
t1.mgr=t2.id;

事务

概念：如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。
操作
1.开启事务 start transaction
2.回滚 rollback
**3.提交 commit

START TRANSACTION;— 开启事务
UPDATE account SET balance=balance-500 WHERE NAME=”张三”;
— 出错了
UPDATE account SET balance=balance+500 WHERE NAME=”李四”;
COMMIT;— 么有问题，提交事务
ROLLBACK;— 发现问题，回滚事务
_
事务提交的两种方式
自动提交
Oracle默认是手动提交事务
musql就是自动提交
一条DML（增删改）语句回自动提交一次事务。
手动提交
需要先开启事务，再提交
查看事务提交方式 SELECT @@autocommit; 1—>自动 0—>手动
修改提交方式 set @@autocommit=0 or 1;

事务四大特征（容易出面试题）
1.原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败
2.持久性：当事务提交或者回滚后，数据库会持久化的保存数据
3.隔离性：多个事务之间，相互独立
4.一致性：事务操作前后，数据总量不变
**

DCL

**管理用户
1.添加用户
CREATE USER ‘用户名’@’主机名’ IDENTIFIED BY ‘密码’
2.删除用户
DROP USER ‘用户名’@’主机名’
3.修改用户密码
SET PASSWORD FOR ‘用户名’@’主机名’ =PASSWORD(‘新密码’)
_*mysql中如果忘了自己密码
1.以管理员身份运行cmd —>net stop mysql
2.使用无验证方式启动mysql服务：mysql —skip-grant-tables
3.打开新的cmd窗口，直接输入mysql回车，就可以登陆成功
4.use mysql
5.update user set password=password(‘新密码’) where user =’root’
6.关闭两个窗口
7.打开任务管理器，手动结束mysqld.exe进程
8.启动mysql服务 net start mysql
9.使用新密码登录_
**4.查询用户—mysql中的user看
**授权
1.查询权限—SHOW GRANTS FOR ‘用户名’@’主机名’
2.授权—GRANT 权限列表 ON 数据库名.表名 TO ‘用户名’@’主机名’
所有数据库的所有表授予所有权限—GRANT ALL ON . TO ‘用户名’@’主机名’
3.撤销权限—REVOKE 权限列表 ON 数据库名.表名 FROM ‘用户名’@’主机名’