返回
网站建设知识
MySql学习笔记
2025-07-22 10:03  点击:0

数据库管理

查询所有的数据库 创建数据库 查看数据库的默认字符集 修改数据库 删除数据库
-- 查看数据库SHOW DATAbaseS;-- information_schema  mysql元数据，基础数据-- mysql               mysql配置数据库，其中包含用户信息。（用户名和密码，权限管理）-- performance_schema  mysql数据库软件的运行数据，日志信息，性能数据-- test                测试数据库。空的-- 创建数据库CREATE DATAbase day08 DEFAULT CHARACTER SET utf8;-- 查看数据库的默认字符集SHOW CREATE DATAbase day08;-- 修改数据库ALTER DATAbase day08 DEFAULT CHARACTER SET gbk;-- 删除数据库DROP DATAbase day08;

创建数据库使用create，查看数据库使用show，修改数据库使用alter，删除数据库使用drop。

表管理

查看所有表 创建表 查看表结构 删除表 修改表
添加字段 修改字段类型 修改字段名称 修改表名称 删除字段
-- 选择数据库USE mysqlstudy;-- 查看所有表SHOW TABLES;-- 创建表CREATE TABLE student(    id INT PRIMARY KEY AUTO_INCREMENT,    NAME VARCHAR(20) NOT NULL,    gender VARCHAR(2),    age INT);-- 查看表结构DESC student;-- 修改表-- 添加字段ALTER TABLE student ADD COLUMN english INT;-- 删除字段ALTER TABLE student DROP COLUMN english;-- 修改字段类型ALTER TABLE student MODIFY COLUMN NAME VARCHAR(30);-- 修改字段名称ALTER TABLE student CHANGE COLUMN NAME sname VARCHAR(30);-- 修改表名称ALTER TABLE student RENAME TO MyStudent;-- 删除表DROP TABLE MyStudent;

增删改数据

添加数据 修改数据 删除数据（**）
-- 1.1添加数据-- 插入所有字段。依次按顺序插入。不能少或者多字段INSERT INTO student VALUES(1,"Tom","男",10);-- 插入部分字段INSERT INTO student(NAME) VALUES("Jack");-- 1.2 修改数据-- 修改所有的数据UPDATE student SET gender="男";-- 带条件的修改UPDATE student SET gender="女" WHERE id=2;-- 修改多个字段UPDATE student SET gender="男",age="20" WHERE id=2;ALTER TABLE student ADD servlet INT,ADD jsp INT,ADD address VARCHAR(20); -- 天剑servlet jsp字段UPDATE student SET address="郑州";-- 1.3删除数据-- 删除所有的数据delet FROM student;-- 带条件的删除DELETE FROM student WHERE id=2;-- 另一种方式truncateTRUNCATE TABLE student;

delete from 1、以带条件删除 2、只能删除表数据，不能删除表的约束 3、数据可以回滚
truncate table 1、不能带条件删除 2、可以删除表数据，也可以删除表的约束 3、数据不可以回滚

查询数据（**）

查询所有的列 查询指定的列 查询时添加常量列 查询时合并列 查询时去除重复记录 distinct 条件查询
逻辑条件 and or 比较条件 > < >= <= = <> 判空条件（Null和空字符串） is null is not null 模糊条件 聚合查询 分页查询 查询排序 分组查询 分组后筛选
-- 2.1查询所有的列SELECT * FROM student;-- 2.2查询时指定列SELECT id,NAME,gender FROM student;-- 2.3查询时指定别名（AS）-- 在多表查询时经常使用表的别名SELECT id AS '编号',NAME AS '姓名' FROM student;-- 2.4查询时添加常量列SELECT id,NAME,gender,"Java就业班" AS "年级" FROM student;-- 2.5 查询时和并列ALTER TABLE student ADD COLUMN servlet INT;ALTER TABLE student ADD COLUMN jsp INT;UPDATE student SET servlet=90;UPDATE student SET jsp=90;SELECT id,NAME ,(servlet+jsp) AS "总成绩" FROM student;-- 2.6 查询时去除重复记录SELECT DISTINCT gender FROM student;-- 2.7 条件查询-- 2.7.1 逻辑条件AND，ORSELECT * FROM student WHERE id=1 AND NAME="Tom";-- 2.7.2 比较条件 > < >= 《= == <> between andSELECT * FROM student WHERE servlet>=90;SELECT * FROM student WHERE servlet BETWEEN 80 AND 90;-- 2.7.3 判空条件（NULL 空字符串）-- Null 表示没有值-- 空字符：有值的！SELECT * FROM student WHERE address IS NULL OR address="";-- 查询有地址的学生SELECT * FROM student WHERE address IS NOT  NULL AND address<>"";-- 2.7.4 模糊查询-- % 表示任意字符-- _下划线代表两个字符SELECT * FROM student WHERE NAME LIKE "T%"-- 2.8 聚合查询-- 常用的聚合函数 sum() avg() max() min() count()SELECT SUM(jsp) AS "jsp的总成绩" FROM student;SELECT AVG(jsp) AS "jsp的平均成绩" FROM student;-- count(*) ：使用count统计表的记录数，要使用不包含null值得字段-- count(id):统计的数量不包含null的数据SELECT COUNT(NAME) AS "学生人数" FROM student;-- 2.9 分页查询（limit 起始行，查询几行）：起始行从0开始SELECT * FROM student LIMIT 0,2;-- 2.10 查询排序-- order by 字段 asc/descSELECT * FROM student ORDER BY jsp ASC;-- 多个排序条件SELECT * FROM student ORDER BY jsp ASC,servlet DESC;-- 分组查询 （group by）SELECT gender,COUNT(*) FROM student GROUP BY gender;-- 分组查询后筛选-- 分组前使用where 分组后使用havingSELECT gender,COUNT(*) FROM student GROUP BY gender HAVING COUNT(*)>=1;

数据约束

默认值 非空 唯一 UNIQUE 主键 自增长 外键 级联操作
CREATE TABLE dept(    id INT PRIMARY KEY AUTO_INCREMENT ,-- 主键    deptName VARCHAR(20) NOT NULL -- 非空);CREATE TABLE employee(    id INT PRIMARY KEY AUTO_INCREMENT,    empName VARCHAR(20) NOT NULL,    salary INT ,    deptId INT ,    ConSTRAINT employee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE  -- 外键 级联操作);

* 被约束的表称为副表，约束别人的表称为主表，外键设置在副表上的！！！主表的参考字段通用为主键！添加数据： 先添加主表，再添加副表；修改数据： 先修改副表，再修改主表；删除数据： 先删除副表，再删除主表 *

数据库的设计

设计原则： 建议设计的表尽量遵守三大范式。
第一范式： 要求表的每个字段必须是不可分割的独立单元
第二范式： 在第一范式的基础上，要求每张表只表达一个意思。表的每个字段都和表的主键有依赖。
第三范式： 在第二范式基础，要求每张表的主键之外的其他字段都只能和主键有直接决定依赖关系。

简要说明：比如数据库中有一name字段，它存在名字有now_name和old_name ，就违反了第一范式。在员工数据表中，有一个订单名称的字段，则违反了第二字段。同样在员工数据表中，部门编号和部门名两个字段，员工id可以确定部门名，而部门编号也可以确定一个唯一的部门名，所以违反了第三范式。

关联查询

内连接查询 左连接查询 右连接查询 自连接查询
-- 需求：查询员工及其所在部分（显示员工姓名，部门名字）-- 1.1 交叉连接查询（产生笛卡尔乘积现象）INSERT INTO dept(deptName) VALUES("软件开发部门");INSERT INTO dept(deptName) VALUES("财务部门");INSERT INTO dept(deptName) VALUES("总部门");INSERT INTO employee(empName,salary,deptId) VALUES("Tom1",5000,1);INSERT INTO employee(empName,salary,deptId) VALUES("Tom2",5000,1);INSERT INTO employee(empName,salary,deptId) VALUES("Tom3",5000,2);INSERT INTO employee(empName,salary,deptId) VALUES("Tom4",5000,1);SELECT empName,deptName FROM employee,dept-- 1.2 内连接查询（只有满足条件的结果才会显示）-- 确定查询哪些表  确定查询哪些字段 表与表之间的关系:(连接条件数量=表数量-1)SELECT  empName,  deptNameFROM employee AS e,  dept AS dWHERE e.deptId = d.id;-- 另一种方式SELECT e.empName,d.deptName    FROM employee AS e    INNER JOIN dept AS d    ON e.deptId=d.id-- 1.3 左外连接查询：使用左边表的数据去匹配右边边的数据，-- 如果符合连接条件的结果则显示，如果不符合的连接，则显示NUll-- 注意：左边的数据一定会完成显示！--  需求：查询每个部门的员工SELECT d.deptName,e.empName    FROM dept AS d    LEFT OUTER JOIN employee AS e    ON d.id=e.deptId; -- 1.4 右连接查询SELECT d.deptName,e.empName    FROM employee AS e    RIGHT OUTER JOIN dept AS d    ON d.id=e.deptId;-- 1.5 自连接查询-- 需求：查询员工及其上司ALTER TABLE employee ADD COLUMN bossId INT;UPDATE employee SET bossId=1 WHERE id=2;UPDATE employee SET bossId=2 WHERE id=3;UPDATE employee SET bossId=3 WHERE id=4;SELECT e.empName,b.empName    FROM employee e     LEFT OUTER JOIN employee b    ON e.bossId=b.id;

存储过程

什么是存储过程

存储过程，带有逻辑的sql语句之前的sql没有条件判断，没有循环存储过程带上流程控制语句（if while）

存储过程的特点

执行效率非常快！存储过程是在数据库的服务器端执行的 移植性很差！不同数据库的存储过程是不能移植。

存储过程语法

-- 创建存储过程DELIMITER $       -- 声明存储过程的结束符CREATE PROCEDURE pro_test()           --存储过程名称(参数列表)BEGIN             -- 开始    -- 可以写多个sql语句;          -- sql语句+流程控制    SELECT * FROM employee;END $            -- 结束 结束符-- 执行存储过程CALL pro_test();          -- CALL 存储过程名称(参数);参数：IN：   表示输入参数，可以携带数据带存储过程中OUT： 表示输出参数，可以从存储过程中返回结果INOUT： 表示输入输出参数，既可以输入功能，也可以输出功能

Example

DELIMITER $CREATE PROCEDURE pro_test()BEGIN     SELECT * FROM employee;END $;CALL pro_test();-- 3.1 带有输入的参数存储过程DELIMITER $CREATE PROCEDURE pro_findById(IN eid INT)BEGIN    SELECT * FROM employee WHERE id=eid;END $CALL pro_findById(4);-- 3.2 带有输出的参数存储过程DELIMITER $CREATE PROCEDURE pro_testOut(OUT str VARCHAR(20))BEGIN    SET str="这是一个字符串";END $CALL pro_testOut(@str);SELECT @str;-- 3.3 带有输入输出的参数存储过程DELIMITER $CREATE PROCEDURE pro_testInOut(INOUT n INT)BEGIN    SELECT n;    SET n=500;END $SET @n=10;CALL pro_testInOut(@n);SELECT @n;-- 3.4 带有条件的DELIMITER $CREATE PROCEDURE pro_testIf(IN num INT,OUT str VARCHAR(20))BEGIN    IF num=1 THEN        SET str="星期一";    ELSEIF num=2 THEN        SET str="星期二";    ELSE         SET str="输入错误";    END IF;END $SET @num=1;CALL pro_testIf(@num,@str);SELECT @str;-- 3.5带有循环功能的存储过程DELIMITER $CREATE PROCEDURE pro_testWhile(IN num INT,OUT result INT)BEGIN    DECLARE i INT DEFAULT 1;    DECLARE vsum INT DEFAULT 0;    WHILE i
Mysql变量
全局变量
全局变量（内置变量）：mysql数据库内置的变量 （所有连接都起作用）
查看所有全局变量： show variables查看某个全局变量： select @@变量名修改全局变量： set 变量名=新值character_set_client: mysql服务器的接收数据的编码character_set_results：mysql服务器输出数据的编码
会话变量
只存在于当前客户端与数据库服务器端的一次连接当中。如果连接断开，那么会话变量全部丢失！
定义会话变量: set @变量=值查看会话变量： select @变量
局部变量
在存储过程中使用的变量就叫局部变量。只要存储过程执行完毕，局部变量就丢失！
DECLARE vsum INT DEFAULT 0;