MySql 基础操作

最新推荐文章于 2024-12-21 16:23:12 发布

hccee

最新推荐文章于 2024-12-21 16:23:12 发布

阅读量511

点赞数 5

分类专栏： SQL 文章标签：数据库 mysql

本文链接：https://blog.youkuaiyun.com/hccee/article/details/143965010

版权

SQL 专栏收录该内容

1 篇文章

订阅专栏

一、MySql 安装

Sql 官网下载 msi 下载之后安装。密码可以设置 123456。
启动默认开机自动启动。服务器或者使用命令行启动 net start mysql80 和 net stop mysql80
连接使用my sql 自带终端。或者添加环境变量、使用cmd执行。

path 添加目录 bin 目录 C:\Program Files\MySQL\MySQL Server 8.0\bin\
然后执行
mysql -u root -p
然后输入密码 123456  即可连接mysql

二、SQL操作

1、db 操作

// 显示当前数据库
show databases;
// 创建数据库  设置默认字符格式 utf8mb4   mb4 的意思是四个字节
create database if not exists studb default charset utf8mb4;
// 切换数据库
use studb;
// 显示当前选择的数据库
select database();
// 删除数据库
drop database if exists studb;

2、表创建、查询表结构

// 查询所有表
show tables；

// 创建表
create table tb_user(
    id int comment '注释',
    name varchar(50),
    age int,
    sex varchar(1)
);

// 查看表结构
desc ta_user;

// 查看表创建语句
how create table tb_user;

3、数据类型

double 第一位是为数、第二位是精度

字符串使用的时候固定长度字符串用 char 。可变用 varchar 固定长度字符串、剩余空间会用空格补齐。可变会根据内容计算长度

date 可用于年月日的字段。例如生日。 dataTime 用于更精确的地方

案例如下

4、修改表

添加字段

alert table tableName add username varchar(18);

删除字段

alert table tableName drop username;

修改字段数据类型

alert table tableName modify username varchar(16);

修改字段名

alert table tableName change useraName newName varchar(16);

修改表名

alert table tableName rename to newTableName;

删除表

drop table if exists tableName;

truncate table tableName; // 会重新在创建一个table结构一样
两种删除、表中数据都会清空

5、DML语句

// 插入
insert into tb_user (id, name, age, gender) values (1, '赵敏', '18', '女');
// 插入所有字段清空下可以不写插入的字段
insert into tb_user values (1, '张无忌', '18', '男', '11');
insert into tb_user values (1, '韦一笑', '18', '男', '11'), (4, '张三丰', '18', '男', '11');

// 修改
update tb_user set name = '武超帅', age = '19' where id = 4;

// 删除
delete from tb_user where id = 4;

6、DQL 语句

基本查询

select * from  tb_user;
select name, age from tb_user;
select name  as 'username' from tb_user;
select distinct age from tb_user;

条件查询

select * from tb_user where age = 18;
select * from tb_user where age = 18 or age = 11;
select * from tb_user where age in (18, 11, 13);
select * from tb_user where age between 11 and 18;
select * from tb_user where name like '_无忌';
select * from tb_user where name like '%忌';

聚合查询

select COUNT(*) from  tb_user;
select COUNT(nickname) from  tb_user;
select max(age) from  tb_user;
select sum(age) from tb_user where gender = '女';

分组查询

// 根据男女分组、查询nickName = 11 且分组数量大于2的。
select gender, count(*) as number from tb_user where nickname = '11' group by gender having number > 2;

条件查询

// 按照年龄升序查询、年龄相等的时候按照name降序潘旭。
select * from tb_user order by age asc , name desc ;

分页查询

select * from tb_user limit 10;
select * from tb_user limit 10,10;

练习

-- 1. 查询年龄为20，21，22，23岁的女性员工信息
-- 2. 查询性别为 男， 并且年龄在20~40岁(含)以内的行为三个字的员工
-- 3. 统计员工表中，年龄小于60岁的，男性员工和女性员工人数
-- 4. 查询所有年龄小于等于35岁的给员工姓名和年龄，并且对查询的结果按照年龄升序排列，如果年龄相同按入职时间降序排列
-- 5. 查询性别为男，且年龄在20~40岁(含)以内的前5个员工信息，对查询的结果按照年龄升序排列，年龄相同按照入职时间升序排列

执行顺序

7、DCL

数据控制语言、用于管理数据库用户、控制数据库的访问权限。

用户表是在 mysql-user 里面进行管理的。

// 创建用户
create user 'yanggerry'@'localhost' identified by '12456';

// 修改用户密码
alter user 'yanggerry'@'localhost'  identified with mysql_native_password by '1234';

// 删除用户
drop user 'yanggerry'@'localhost';

// 显示用户权限
show grants for 'yanggerry'@'localhost';

// 给用户 emp 所有的表的所有权限
grant all on emp.* to 'yanggerry'@'localhost';

// 移除用户 emp 所有表的所有权限
revoke all on emp.* from 'yanggerry'@'localhost';

三、函数

字符串函数

select concat('hellow ', 'word');
select lower('Hellow');
select upper('hellow');
select LPAD('hellow', 10, '0');
select RPAD('hellow', 10, '0');
select trim('  hellow word  ');
select substring('hellow', 2, 3);

数值函数

select ceil(1.1);
select floor(1.9);
select mod(3,2);
select rand();
select round(2.335, 2);
// 取一个随机数6位验证码
select lpad(round(rand()*1000000, 0), 6, '0');

日期函数

select curdate();
select curtime();
select now();
select year(now());
select month(now());
select day(now());
select date_add(now(), interval 30 day);
select datediff(now(), '2022-03-15') as '被封天数';

流程函数

select if(1, 'true', 'errror');

select ifnull(null, 'default');
select ifnull(1, 'default');
select ifnull('', 'default');
select
    id,
    name,
    (case when age >= 16 then '成年' when age = 14 then '快成年' else '小屁孩' end) as '年龄',
    gender,
    nickname
    from tb_user;

四、约束

# id 主键 自增长
# 用户名 唯一不为空
# 年龄 check 约束
# id 提供默认值
create table emp(
    columnId int primary key auto_increment comment '主键',
    username varchar(10) unique not null comment '用户名',
    age int check ( age < 120 && age > 18) comment '年龄',
    dp_id int default '1' comment '部门id',
    grender varchar(1) comment '性别'
) comment '员工表';

insert into emp (username, age, dp_id, grender) values ('金庸', 40, '2', '男'),
                                                        ('张无忌', 30, '1', '男'),
                                                        ('赵敏', 22, '1', '男'),
                                                        ('韦一笑', 42, '1', '男'),
                                                        ('周芷若', 30, '3', '男'),
                                                        ('张三丰', 32, '3', '男'),
                                                        ('李莫愁', 33, '3', '男');

create table dpTable (
    dp_id int primary key auto_increment,
    name varchar(10) not null unique
) comment '部门表';

insert into dpTable (name) values ('研发部'),('董事长'),('市场'),('后勤'),('总经办');

# 添加外键约束
alter table emp add constraint fk_emp_dp_id foreign key (dp_id) references dptable(dp_id);

# 删除外键约束
alter table emp drop foreign key fk_emp_dp_id;

delete from dptable where dp_id = 1;

drop table dptable;
drop table emp;

# 部门删除或者更新的时候、子表也会跟着删除或者更新
alter table emp add constraint fk_emp_dp_id foreign key (dp_id) references dptable(dp_id) on update cascade on delete cascade ;

# 部门删除的时候、字表会变成null
alter table emp add constraint fk_emp_dp_id foreign key (dp_id) references dptable(dp_id) on update set null on delete set null ;

五、多表查询

多表关系

一对多

例如：部门和员工的关系（在多的一方建立外键）

多对多

例如：学生和课程的关系（建立中间表两个外键、一个关联课程、一个管理学生）

一对一例如：用户和用户详情。多用于单表的拆分。

多表查询概述

内连接、外链接、自链接

create table emp(
    columnId int primary key auto_increment comment '主键',
    username varchar(10) unique not null comment '用户名',
    age int check ( age < 120 && age > 18) comment '年龄',
    dp_id int default '1' comment '部门id',
    vid int default '1' comment '员工id',
    managerId int default '1' comment '经理id',
    grender varchar(1) comment '性别'
) comment '员工表';

insert into emp (username, age, dp_id,vid, managerId, grender) values ('金庸', 40, '2', 1, null, '男'),
                                                        ('张无忌', 30, '1', 2, 1,  '男'),
                                                        ('赵敏', 22, '1', 3, 1,  '男'),
                                                        ('韦一笑', 42, '1',  4, 3, '男'),
                                                        ('周芷若', 30, '3',  5, 3, '男'),
                                                        ('张三丰', 32, '3', 6, 5,  '男'),
                                                        ('李莫愁', 33, '3', 7, 6,  '男');

create table dpTable (
    dp_id int primary key auto_increment,
    name varchar(10) not null unique
) comment '部门表';

insert into dpTable (name) values ('研发部'),('董事长'),('市场'),('后勤'),('总经办');


# 内连接
select * from emp, dpTable where emp.dp_id = dpTable.dp_id;
# 内连接带别称
select e.username,d.name from  emp as e, dpTable as d where e.dp_id = d.dp_id;
# 内连接显示链接  select -- frome table1 join table2 on 条件
select * from emp e join dptable d on e.dp_id = d.dp_id;

# 外连接  左连接会查询两个集合重复的部分和整个坐标的库。 同样又链接也是 select -- frome table1 left join table2 on 条件
select * from emp e LEFT JOIN dptable d on e.dp_id = d.dp_id;
select d.name, e.username from emp e right JOIN dptable d on e.dp_id = d.dp_id;

# 自链接  就是一张表当成两张表来查
select a.username as '员工', b.username as '领导' from emp a, emp b where a.managerId = b.vid;

子查询、多表查询案例

create table dept(
    id   int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '部门名称'
)comment '部门表';

create table emp(
    id  int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '姓名',
    age  int comment '年龄',
    job varchar(20) comment '职位',
    salary int comment '薪资',
    entrydate date comment '入职时间',
    managerid int comment '直属领导ID',
    dept_id int comment '部门ID'
)comment '员工表';

-- 添加外键
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

INSERT INTO dept (id, name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部'), (4, '销售部'), (5, '总经办'), (6, '人事部');
INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id) VALUES
            (1, '金庸', 66, '总裁',20000, '2000-01-01', null,5),

            (2, '张无忌', 20, '项目经理',12500, '2005-12-05', 1,1),
            (3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),
            (4, '韦一笑', 48, '开发',11000, '2002-02-05', 2,1),
            (5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),
            (6, '小昭', 19, '程序员鼓励师',6600, '2004-10-12', 2,1),

            (7, '灭绝', 60, '财务总监',8500, '2002-09-12', 1,3),
            (8, '周芷若', 19, '会计',48000, '2006-06-02', 7,3),
            (9, '丁敏君', 23, '出纳',5250, '2009-05-13', 7,3),

            (10, '赵敏', 20, '市场部总监',12500, '2004-10-12', 1,2),
            (11, '鹿杖客', 56, '职员',3750, '2006-10-03', 10,2),
            (12, '鹤笔翁', 19, '职员',3750, '2007-05-09', 10,2),
            (13, '方东白', 19, '职员',5500, '2009-02-12', 10,2),

            (14, '张三丰', 88, '销售总监',14000, '2004-10-12', 1,4),
            (15, '俞莲舟', 38, '销售',4600, '2004-10-12', 14,4),
            (16, '宋远桥', 40, '销售',4600, '2004-10-12', 14,4),
            (17, '陈友谅', 42, null,2000, '2011-10-12', 1,null);


drop table dptable;
drop table emp;

# union all 是不去重的。 union 去重
select * from emp where salary < 5000
union
select * from emp where age > 50;

select * from emp where salary < 5000
union all
select * from emp where age > 50;

# 子链接
# 查询所有销售部门的员工信息
select * from emp where emp.dept_id = (select id from dept where dept.name = '销售部');
-- 2. 查询在 "方东白" 入职之后的员工信息
select * from emp where emp.name = '方东白';
select * from emp where emp.entrydate > (select entrydate from emp where emp.name = '方东白');

-- 列子查询
-- 1. 查询 "销售部" 和 "市场部" 的所有员工信息
select e.* from emp as e where e.dept_id in (select d.id from  dept as d where d.name = '市场部' or  d.name = '销售部');

-- 2. 查询比 财务部 所有人工资都高的员工信息
select * from emp where salary > all ( select salary from emp where dept_id = (select id from dept where name = '财务部') );

-- 3. 查询比研发部其中任意一人工资高的员工信息
select * from emp where salary > some ( select salary from emp where dept_id = (select id from dept where name = '研发部') );

-- 行子查询
-- 1. 查询与 "张无忌" 的薪资及直属领导相同的员工信息 ;
select * from emp where (emp.salary, emp.managerid) = (select salary, managerid from emp where emp.name = '张无忌');

-- 表子查询
-- 1. 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息
select * from emp where (job,salary) in ( select job, salary from emp where name = '鹿杖客' or name = '宋远桥' );

-- 2. 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id ;

六、事务

事务介绍

使用事务两种方式

事务的四大特性

事务产生的问题

事务的隔离性

具体代码

-- 数据准备
create table account(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    money int comment '余额'
) comment '账户表';
insert into account(id, name, money) VALUES (null,'张三',2000),(null,'李四',2000);

-- 恢复数据
update account set money = 2000 where name = '张三' or name = '李四';

select @@autocommit;

set @@autocommit = 0; -- 设置为手动提交

-- 转账操作 (张三给李四转账1000)
-- 1. 查询张三账户余额
select * from account where name = '张三';

-- 2. 将张三账户余额-1000
update account set money = money - 1000 where name = '张三';

程序报错.....
-- 3. 将李四账户余额+1000
update account set money = money + 1000 where name = '李四';


-- 提交事务
commit;

-- 回滚事务
rollback ;


-- 方式二
-- 转账操作 (张三给李四转账1000)
start transaction ;

-- 1. 查询张三账户余额
select * from account where name = '张三';

-- 2. 将张三账户余额-1000
update account set money = money - 1000 where name = '张三';

-- 3. 将李四账户余额+1000
update account set money = money + 1000 where name = '李四';

-- 提交事务
commit;

-- 回滚事务
rollback;
-- 查看事务隔离级别
select @@transaction_isolation;
-- 设置事务隔离级别

set session transaction isolation level read committed ;

set session transaction isolation level read uncommitted ;

set session transaction isolation level repeatable read ; -- 默认级别

set session transaction isolation level serializable ;