MySQL登录、访问、退出操作

登录命令：mysql -h localhost -u root -p

-h：host主机名，后面要跟访问的数据库服务器地址

-u：user用户名，后面跟登陆数据的用户名

-p：password密码，一般不直接输入，而是回车后以保密的方式输入

显示MySQL中的数据库列表：show databases

切换当前数据库的命令：use mysql

显示当前数据库的所有数据表：show tables

退出数据库可以使用quit或者exit命令完成，也可以使用\q；完成退出操作

SQL语言

SQL语言分为五个部分：

· 数据查询语言（Data Query Language，DQL）：DQL主要用于数据的查询，其基本结构是使用SELECT子句，FROM子句和WHERE子句的组合来查询一条或多条数据。

· 数据操作语言（Data Manipulation Language，DML）：DML主要用于对数据库中的数据进行增加、修改和删除的操作，其主要包括：

INSERT：增加数据
UPDATE：修改数据
DELETE：删除数据

· 数据定义语言（Data Definition Language，DDL）：DDL主要用针对是数据库对象（数据库、表、索引、视图、触发器、存储过程、函数）进行创建、修改和删除操作。其主要包括：

CREATE：创建数据库对象
ALTER：修改数据库对象
DROP：删除数据库对象

· 数据控制语言（Data Control Language，DCL）：DCL用来授予或回收访问数据库的权限，其主要包括：

1. GRANT：授予用户某种权限

2. REVOKE：回收授予的某种权限

· 事务控制语言（Transaction Control Language，TCL）：TCL用于数据库的事务管理。其主要包括：

START TRANSACTION：开启事务
COMMIT：提交事务
ROLLBACK：回滚事务
SET TRANSACTION：设置事务的属性

创建数据库表

创建数据库表：

create table t_student(
    sno int(6),
    sname varchar(5),
    sex char(1),
    age int(3),
    enterdate date,
    classname varchar(10),
    email varchar(15)
 )

查看表结构：展示表的字段详细信息

desc t_student

查看表中的数据：

select * from t_student

查看建表语句：

show create table t_student

数据库表的类型：

整数类型

浮点数类型

字符串类型

日期和时间

添加、修改、删除语句

添加：

insert into t_student values (1,'张三','男',18,'2002-5-8','软件一班','234342@321.com')

insert into t_student (sno,sname,enterdate) values (10,'李四','2023-7-5')

修改：

update t_student set sex = '女'

update t_student set sex ='男' where sno =10

删除：

delete from t_student where sno = 2

修改、删除数据库表

增加一列：

alter table t_student add score double(5,2) --5：总位数 2：小数位数

增加一列放在最前面：

alter table t_student add score double(5,2) first

增加一列（放在sex列的后面）：

alter table t_student add score double(5,2) after sex

删除一列：

alter table t_student drop score

修改一列：

alter table t_student modify score float(4,1) --modify修改的是列的类型和定义，但是不会改变列的名字

alter table t_student change score scorel double(5,1) --change修改列名和列的类型定义

删除表：

drop table t_student

表的完整性约束

为防止不符合规范的数据存入数据库，在用户对数据进行插入、修改、删除等操作时，MySQL提供了一种机制来检查数据库中的数据是否满足规定的条件，以保证数据库中的数据的准确性和一致性，这种机制就是完整性约束

非外键约束

代码示例：

--第一种
create table t_student(
    sno int(6) primary key auto_increment,
    sname varchar(5) not null,
    sex char(1) defaule '男' check(sex='男' || sex='女'),
    age int(3) check(age>=18 and age<=50),
    enterdate date,
    classname varchar(10),
    email varchar(15) unique
)

--第二种
create table t_student(
    sno int(6) auto_increment,
    sname varchar(5) not null,
    sex char(1) defaule '男',
    age int(3),
    enterdate date,
    classname varchar(10),
    email varchar(15) unique,
    constraint pk_stu primary key (sno),
    constraint ck_stu_sex check (sex='男' || sex='女'),
    constraint ck_stu_age check (age>=18 and age<=50),
    constraint uq_stu_email unique (email)
    
    --第三种
create table t_student(
    sno int(6),
    sname varchar(5) not null,
    sex char(1) defaule '男',
    age int(3),
    enterdate date,
    classname varchar(10),
    email varchar(15) unique  
)
alter table t_student add constraint pk_stu primary key (sno);
alter table t_student modify sno int(6) auto_increment; --修改自增条件
alter table t_student add constraint ck_stu_sex check (sex='男' || sex='女');
alter table t_student add constraint ck_stu_age check (age>=18 and age<=50);
alter table t_student add constraint uq_stu_email unique (email);

外键约束

应用场景：有时候我们在具体任务中会遇到多个表，假如一个学生表包含了学生的基本信息和班级编号，一个班级表包含了班级名称，班级编号，教室。此时进行删除操作删除班级表中的教室，但是学生表中仍有删除教室编号，这便产生了问题，而解决这个问题就要用到外键约束

注意：外键约束只有表级约束，没有列级约束：

代码示意：

create table t_student(
    sno int(6) primary key auto_increment,
    sname varchar(5) not null,
    classno int(4),
    constraint fk_stu_classno foreign key (classno) references t_class (cno)
);

create table t_class(
    cno int(4) primary key auto_increment,
    cname varchar(10) not null,
    room char(4)
);

外键策略：

直接删除和修改主表数据时，因为有外键的束缚，会产生不必要的错误，这里就需要用到外键策略

策略1：no action 不允许操作

通过操作sql来完成：

先把班级2的学生对应的班级改为null

update t_student set classno = null where classno = 2;

然后再删除班级2：

delete from t_class where cno = 2;

策略2：cascade 级联操作：操作主表的时候影响从表的外键信息：

先删除之前的外键约束：

alter table t_student drop foreign key fk_stu_classno;

重新添加外键约束：

alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno) on update cascade on delete cascade;

策略3：set null 置空操作：

先删除之前的外键约束：

alter table t_student drop foreign key fk_stu_classno;

重新添加外键约束：

alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno) on update set null on delete set null;

DQL-查询操作

单表查询

起别名：

select empno 员工编号 from emp

select empno as 员工编号 from emp

去重操作：

select distinct job, deptno from emp

排序：

select * from emp order by sal; -- 默认情况下是按照升序排列的

select * from emp order by sal asc; -- asc 升序，可以默认不写

select * from emp order by sal desc; -- desc 降序

select * from emp order by sal asc ,deptno desc;-- 在工资升序的情况下，deptno按照降序排列

where子句 + 逻辑运算符and或&&

where子句 + 逻辑运算符or或in

where子句 + 模糊查询

select * from emp where ename like '%A%'

select * from emp where ename like '_A%'

关于null的判断

select * from emp where comm is null

select * from emp where comm is not null

单行函数

字符串函数：

数值函数:

日期与时间函数：

多行函数

group by

代码示意：

select deptno,avg(sal) from group by deptnko

having(分组后筛选)

select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000

单表查询语句总结：

select column,group_function(column)
from table
[where condition]
[group by group_by_expression]
[having group_condition]
[order by column]

多表查询

交叉连接

cross join

select *
from emp
cross join dept  --笛卡尔乘积

自然连接

natural join

优点：自动匹配所有同名项，同名列只展示一次

缺点：自动匹配表中所有的同名列，但是有时候我们希望只匹配部分同名列

select *
from emp
natural join dept;

内连接

select *
from emp
inner join dept d --inner可以省略不写
using (deptno)

select *
from emp e
inner join dept d
on (e.deptno = d.deptno)
where sal > 3500

外连接

左外连接

left outer join --左面的那个表的信息，即使不匹配也可以查看结果

select *
from emp e
left outer join dept d
on e.deptno = d.deptno

右外连接

right outer join --左面的那个表的信息，即使不匹配也可以查看结果

select *
from emp e
right outer join dept d
on e.deptno = d.deptno

union 取并集去重

union all 取并集不去重

不相关子查询

单行子查询

select ename,sal
from emp
where sal > (select avg(sal) from emp)

select ename,sal
from emp
where deptno = (select deptno from emp where ename = 'CLARK')
and
sal < (select sal from emp where ename ='CLARK')

多行子查询

select *
from emp
where deptno = 20
and job = any (select job from emp where deptno = 10)

select empno,ename,sal
from emp
where sal > all(select sal from emp where job = 'SALESMAN')

事务

手动开启事务：

start transaction;

update account set balance = balance - 200 where id = 1;

update account set balance = balance + 200 where id = 2;

手动回滚：刚才执行的操作全部取消：

rollback;

手动提交：

commit;

在回滚和提交之前，数据库中的数据都是操作的缓存中的数据，而不是数据库的真实数据

视图

-- 创建/替换单表视图：

create or replace view myview01
as
select empno,ename,job,deptno
from emp
where deptno = 20
with check option;
-- 查看视图：
select * from myview01;
-- 创建/替换多表视图：
create or replace view myview02
as
select e.empno,e.ename,e.sal,d.deptno,d.dname
from emp e
join dept d
on e.deptno = d.deptno
where sal > 2000 ;
select * from myview02;
-- 创建统计视图：
create or replace view myview03
as
select e.deptno,d.dname,avg(sal),min(sal),count(1)
from emp e
join dept d
using(deptno)
group by e.deptno ;
select * from myview03;
-- 创建基于视图的视图：
create or replace view myview04
as
select * from myview03 where deptno = 20;
select * from myview04;

存储过程

--实现模糊查询
无返回值
create procedure mypro01 (name varchar(10))
begin
    if name is null or name = "" then
        select * from emp;
    else
        select * from emp where ename like concat('%',name,'%');
    end if;
end;

call mypro01('R')

有返回值
create procedure mypro01 (in name varchar(10),out num int(3))
begin
    if name is null or name = "" then
        select * from emp;
    else
        select * from emp where ename like concat('%',name,'%');
    end if;
    select found_rows() into num;
end;

call mypro01('R',@aaa)
select @aaa

MySQL基本操作