欢迎来到wjc的博客

学习，不知道我几斤几两，不终生学习，被淘汰的就是我。不

mysql

MYSQL

1.安装

2.用法

  • sql语句可单行或多行，以分号结尾；
  • MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写。
  • 单行注释: -- 注释内容 或 #注释内容(MySQL 特有)
  • 多行注释: /* 注释 */

3.mysql数据类型

​ 数值：

  • tinyint:1个字节（小整数类型）

  • int：4个字节（大整数类型）

  • double：浮点类型（总长度，小数点后保留位数）

​ 日期：

  • date：日期值（只保留年月）
  • datetime：混合日期和时间值（年月日时分秒）

​ 字符串：

  • char：定长字符串（如果储存数据字符不足10个，也会占10个空间）
  • varchar：变长自字符串（如果储存数据字符不足10个，那么是几个占几个空间）
需求：设计一张学生表，请注重数据类型、长度的合理性
	1. 编号
	2. 姓名，姓名最长不超过10个汉字
	3. 性别，因为取值只有两种可能，因此最多一个汉字
	4. 生日，取值为年月日
	5. 入学成绩，小数点后保留两位
	6. 邮件地址，最大长度不超过 64
	7. 家庭联系电话，不一定是手机号码，可能会出现 - 等字符
	8. 学生状态（用数字表示，正常、休学、毕业...）
create table student(
	id int,
    name varchar(10),
    birthday date,
    score double(5,2),
    email varchar(64),
    tel varchar(15),
    status tinyint
);

4.分类使用

1.DDL：操作数据库，表

操作库

查询所有数据库：show databases;
创建数据库（如果存在数据库，忽略该操作）：create database if not exists 库名;
删除数据库：drop database if exists 库名;
修改数据库名：alter database  旧库名 rename to 新库名;
使用数据库：use 库名;

操作表

查看当前数据库：select database;
查询所有表名：show tables;
查询所有表结构：desc 表名;
创建表：create table if not exists 表名（
	字段名1	数据类型1,
	字段名2	数据类型2
	）;
删除表：drop table if exists 表名;
修改表：
	修改表名：alter table 旧表名 rename to 新表名;
	添加列：alter table 表名 add 列名 数据类型;
	添加多列：alter table 表名 add 列名1 数据类型1,add 列名2 数据类型2,add 列名3 数据类型3;
	修改数据类型：alter table 表名 modify 列名 新数据类型;
	修改列名和数据类型：alter table 表名 change 旧列名 新列名 数据类型;
	删除列alter table 表名 drop 列名;

2.DML：对表中数据增删改查

添加数据：
	给指定列添加数据：insert into 表名(列名1,列名2,列名3) values(值1,值2,值3); 
	给全部列添加数据：insert into 表名 values(值1,值2,值3,值4);
	批量添加数据：
	insert into 表名(列名1,列名2,列名3) values (值1,值2,值3),(值1,值2,值3),(值1,值2,值3);
	insert into 表名 values (值1,值2,值3,值4),(值1,值2,值3,值4),(值1,值2,值3,值4);
删除数据：
delete from 表名 [where 条件];
修改数据：
update 表名 set 列名1=值1,列名2=值2,列名3=值3[where 条件]; 
查询数据：
select * from 表名

3.DQL：对数据进行查询操作

简单查询

简单查询：
select 字段名1，字段名2 from 表名;
去重简单查询：
select distinct 字段名1 from 表名;
去重，起别名简单查询：
select distinct 字段名1 as 别名 from 表名;
select distinct 字段名1 别名 from 表名;

条件查询

select 字段名1 from 表名 where 条件;
	条件举例：
		age>20;
		age<=20;
		age>=20 && age<=30;等同 age>=20 and age<=30;等同 age between 20 and 30;
		age !=20;等同 age<>20;
		age=20 or age=30;等同age in (20,30);
		age is null;
		age is not null;

模糊查询

关键字：like
_：代表单个任意字符；
%：代表任意个数字符；
select 字段名1,字段名2,字段名3 from 表名 where 字段名1 like '%w_c%';

排序查询

按字段1升序：
select 字段名1,字段名2 from 表名 order by 字段名1;
按字段1降序：
select 字段名1,字段名2 from 表名 order by 字段名1 desc;
按字段1降序，如果字段1一样，再按字段2升序：
select 字段名1,字段名2,字段名3 from 表名 order by 字段名1 desc,字段名2 asc;

聚合函数

注意：null值不参与聚合运算

select 聚合函数名(列名) from 表名;
	聚合函数举例：
	count(列名)	--一般使用count(*)统计个数，因为null不进行运算
	max(列名)
	min(列名)
	sum(列名)
	avg(列名)

分组查询

注意：

​ 分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义

​ having可以对聚合函数进行过滤，where不能。

select 分组字段名,聚合函数 from 表名 [where 分组前的限定] group by 分组字段 [having 分组后的条件限定]
举例：查询男同学和女同学各自的语文平均分，以及各自人数，要求：分数低于60分的不参与分组，分组之后人数大于10个的
select sex,avg(chinese),count(*) from student where score>=60 group by sex having count(*)>10;

分页查询

select 字段名 from 表名 limit 起始索引,查询条数;
案例：
查询第一页的3条数据：
select 字段名 from 表名 limit 0,3; 
查询第2页的3条数据：
select 字段名 from 表名 limit 3,3; 
查询第3页的3条数据：
select 字段名 from 表名 limit 6,3; 
注意：起始索引=(当前页码-1) * 每页显示的条数

4.DCL：定义访问权限和安全级别及创建用户

授权用户权限：
1.授权select 权限：
grant select on 数据库名.表名 to '用户名@主机名';
2.授权select,update,insert权限：
grant select,insert,update on 数据库名.表名 to '用户名@主机名';
3.授权所有权限：
grant all privileges on 数据库名.* to '用户名@主机名';
撤销用户权限：
1.撤销select 权限：
revoke select on 数据库名.表名 to '用户名@主机名';
2.撤销所有权限：
revoke all privileges on 数据库名.* to '用户名@主机名';
显示用户权限：
show grant from '用户名@主机名';

5.约束

1.概念

​ 约束是作用于表中列上的规则，用于限制加入表的数据

2.分类

​ 非空约束（保证列不为空值）：not null

​ 唯一约束（保证列数据不相同）：unique

​ 主键约束（非空且唯一）：primary key

​ 检查约束（保证列的值满足某一条件）：check

​ 注意：MySQL有点版本不能使用检查约束

3.使用

-- 非空约束
-- 1.建表时添加约束
create table 表名(
	列名 数据类型 not null,
);
-- 2.建表后添加约束
alter table 表名 modify 字段名 数据类型 not null;
 
-- 删除约束
alter table 表名 modify 字段名 数据类型;
-- 唯一约束
-- 1.建表时添加约束
create table 表名(
	列名1 数据类型1 unique [auto_increment],
    -- AUTO_INCREMENT: 当不指定值时自动增长
    列名2 数据类型2 unique [auto_increment]
);
 
create table 表名(
	列名1 数据类型1,
    列名2 数据类型2
    unique(列名1)
);
-- 2.建表后添加唯一约束
alter table 表名 modify 字段名 数据类型 unique;
 
-- 删除约束
alter table 表名 drop index 字段名;
-- 主键约束
-- 1.建表时添加约束
create table 表名(
	列名1 数据类型1 primary key [auto_increment],
    -- AUTO_INCREMENT: 当不指定值时自动增长
    列名2 数据类型2 primary key [auto_increment]
);
 
create table 表名(
	列名1 数据类型1,
    列名2 数据类型2
   	primary key(列名1)
);
-- 2.建表后添加唯一约束
alter table 表名 add primary key(字段名);
 
-- 删除约束
alter table 表名 drop primary key;
-- 默认约束
-- 1.建表时添加约束
create table 表名(
	列名1 数据类型1 default 默认值,
    列名2 数据类型2 default 默认值
);
-- 2.建表后添加唯一约束
alter table 表名 alter 列名 set default 默认值;
 
-- 删除约束
alter table 表名 alter 列名 drop default;

4.练习

-- 员工表
CREATE TABLE emp (
	id INT,  -- 员工id，主键且自增长
    ename VARCHAR(50), -- 员工姓名，非空且唯一
    joindate DATE,  -- 入职日期，非空
    salary DOUBLE(7,2),  -- 工资，非空
    bonus DOUBLE(7,2)  -- 奖金，如果没有将近默认为0
);
drop table if exists emp;
create table emp(
	id int primary key auto_increment, -- 主键且自增长
   	ename VARCHAR(50) not null unique, -- 非空且唯一
    joindate DATE not null,  -- 非空
    salary DOUBLE(7,2) not null,  -- 非空
    bonus DOUBLE(7,2) default 0
);

5.外键约束

使用原因：保证数据的一致性和完整性

例如：员工表有（员工id，姓名，年龄，部门id），

​ 部门表有（部门id，部门名称，地址）

​ 当我们去删除部门表的1号部门时，在员工表就会出现错误数据。所以我们需要用外键让这两个表在数据库层面产生关系，这样你要删除部门表中的1号部门的数据将无法删除。

使用语法

-- 创建表时添加约束
create table 表名(
	列名 数据类型
    [constraint] [外键名称] foreign key (外键列名)
references 主表(主表列名)   
)
-- 创建表后添加外键约束
alter table 表名 add constraint 外键名称 foreign key (外键列名称) REFERENCES 主表名称(主表列名);
 
-- 删除约束
alter table 表名 drop foreign key 外键名称;

练习

-- 删除表
drop table if exists emp;
drop table if exists dept;
 
-- 部门表
create table dept(
	id int primary key auto_increment,
	dep_name varchar(20),
	addr varchar(20)
);
-- 员工表 
create table emp(
	id int primary key auto_increment,
	name varchar(20),
	age int,
	dep_id int,
 
	-- 添加外键 dep_id,关联 dept 表的id主键
    constraint fk_emp_dept foreign key (dep_id)
references dept(id)  
);
-- 删除外键
alter table emp drop foreign key fk_emp_dept;
-- 重新添加
alter table emp add constraint fk_emp_dept foreign key(dep_id) references dept(id);

6.数据库设计

一对一：

create table tb_user_desc (
	id int primary key auto_increment,
	city varchar(20),
	edu varchar(10),
	income int,
	status char(2),
	des varchar(100)
);
 
create table tb_user (
	id int primary key auto_increment,
	photo varchar(100),
	nickname varchar(50),
	age int,
	gender char(1),
	desc_id int unique,
	-- 添加外键
	CONSTRAINT fk_user_desc FOREIGN KEY(desc_id) REFERENCES tb_user_desc(id)	
);

一对多（对多添加外键）：

-- 删除表
DROP TABLE IF EXISTS tb_emp;
DROP TABLE IF EXISTS tb_dept;
 
-- 部门表
CREATE TABLE tb_dept(
	id int primary key auto_increment,
	dep_name varchar(20),
	addr varchar(20)
);
-- 员工表 
CREATE TABLE tb_emp(
	id int primary key auto_increment,
	name varchar(20),
	age int,
	dep_id int,
 
	-- 添加外键 dep_id,关联 dept 表的id主键
	CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES tb_dept(id)	
);

多对多（对新建表添加外键）：

-- 删除表
DROP TABLE IF EXISTS tb_order_goods;
DROP TABLE IF EXISTS tb_order;
DROP TABLE IF EXISTS tb_goods;
 
-- 订单表
CREATE TABLE tb_order(
	id int primary key auto_increment,
	payment double(10,2),
	payment_type TINYINT,
	status TINYINT
);
 
-- 商品表
CREATE TABLE tb_goods(
	id int primary key auto_increment,
	title varchar(100),
	price double(10,2)
);
 
-- 订单商品中间表
CREATE TABLE tb_order_goods(
	id int primary key auto_increment,
	order_id int,
	goods_id int,
	count int
);
 
-- 建完表后，添加外键
alter table tb_order_goods add CONSTRAINT fk_order_id FOREIGN key(order_id) REFERENCES tb_order(id);
alter table tb_order_goods add CONSTRAINT fk_goods_id FOREIGN key(goods_id) REFERENCES tb_goods(id);

6.多表查询

内连接

外连接

子查询

7.事务

  • 开启事务

    		START TRANSACTION;
    		或者  
    		BEGIN;

  • 提交事务

    		commit;

  • 回滚事务

    		rollback;

8.检测是否符合正太分布

/** oracle程序块结构 / 固定结构 **/
DECLARE
	-- 变量与常量初始化
BEGIN 
	-- 执行部分
EXCEPTION
	-- 异常处理部分 / 可省略
END
 
/** 输出 hello world 示例 **/
DECLARE
	message VARCHAR2(50) := 'hello world';
BEGIN
	DBMS_OUTPUT.PUT_LINE(message);
END; 
create table if not exists student(
    score int
);
 
insert into student values (1),(2),(3),(4),(6);
select * from student;
 
create trigger check_normal_distribution 
-- 这部分是创建触发器的命令，触发器的名称是check_normal_distribution
before insert on student 
-- 这部分指定触发器是在向your_table表插入新行之前触发
for each row 
-- 这部分表示这个触发器是行级触发器，也就是说，每当有新的行被插入到your_table时，这个触发器都会被触发
begin    
    declare std_dev float;  
   	declare min_val float;  
    declare max_val float;  
      
    -- 计算标准差  
    set std_dev = sqrt(pow(student.score.value - student.score.mean, 2) / (student.score.count - 1));  
      
    -- 计算最小值和最大值  
    set min_val = student.score.mean - 3 * std_dev;  
    set max_val = student.score.mean + 3 * std_dev;  
      
    -- 检查是否超出范围  
    if student.score.value < min_val OR student.score.value > max_val then  
        -- 如果超出范围，抛出异常或进行其他处理  
        -- 这里可以选择抛出异常或修改NEW.value的值使其符合约束  
        set student.score.value = NULL; -- 修改NEW.value的值使其符合约束  
    END IF;  
END //  
  
delimiter;
-- 结束语句
 
insert into student values (100);
select * from student;
posted @   Wang_JC  阅读(19)  评论(0)  编辑  收藏  举报
相关博文：
·  sql 练习（hive，spqrk）
·  Mybatis
·  MySQL_基础语法_刘益长
·  mysql
·  MySQL基本语法 约束条件 查询条件
阅读排行：
· 周边上新：园子的第一款马克杯温暖上架
· 分享 3 个 .NET 开源的文件压缩处理库，助力快速实现文件压缩解压功能！
· Ollama——大语言模型本地部署的极速利器
· DeepSeek如何颠覆传统软件测试？测试工程师会被淘汰吗？
· 使用C#创建一个MCP客户端
Title
点击右上角即可分享
微信分享提示
AI IDE Trae