番禺网站(建设信科网络),西安网站推广排名,短网站生成,私人域名可以做公司网站备案吗文章目录 一、CRUD1.1 数据库操作1.2 表操作1.2.1 五大约束1.2.2 创建表1.2.3 修改表1.2.3 删除表1.2.4 表数据的增删改查1.2.5 去重方式 二、高级查询2.1 基础查询2.2 条件查询2.3 范围查询2.4 判空查询2.5 模糊查询2.6 分页查询2.7 查询后排序2.8 聚合查询2.9 分组查询2.10 联… 文章目录 一、CRUD1.1 数据库操作1.2 表操作1.2.1 五大约束1.2.2 创建表1.2.3 修改表1.2.3 删除表1.2.4 表数据的增删改查1.2.5 去重方式 二、高级查询2.1 基础查询2.2 条件查询2.3 范围查询2.4 判空查询2.5 模糊查询2.6 分页查询2.7 查询后排序2.8 聚合查询2.9 分组查询2.10 联表查询2.11 子查询/合并查询 三、视图四、触发器4.1 要素4.2 语法4.3 例子 五、权限管理5.1 创建用户5.2 授权 一、CRUD
CRUD操作是指对数据库进行创建Create、读取Read、更新Update和删除Delete等操作的过程。
1.1 数据库操作
创建、选择、删除数据库
# 创建
CREATE DATABASE database_name DEFAULT CHARACTER SET utf8;
# 选择
USE database_name;
# 查看数据库
SHOW database_name;
# 删除
DROP DATABASE database_name;1.2 表操作
1.2.1 五大约束
1非空约束Not Null 非空约束用于限制列中的值不能为空。这样可以确保表中的特定列不包含空值。
2自增约束auto_increment 可以指定初始值没有指定默认是0插入节点的时候会自增。
3唯一约束Unique 唯一约束用于确保表中的某个列或一组列的取值是唯一的但可以包含空值。一个表可以有多个唯一约束。
4主键约束Primary Key 主键约束用于标识表中的唯一记录。一个表只能有一个主键主键列不能包含重复值或空值。
5外键约束Foreign Key 外键约束用于维护表之间的关联关系。它定义了一个或多个列与另一个表的主键或唯一键之间的关系。外键约束可以防止无效引用和维护数据的完整性。
1.2.2 创建表
# 创建表
CREATE TABLE table_name (column_name column_type);
# 显示表的创建过程
SHOW CREATE TABLE table_name ;
# 获取指定表的结构信息
DESC table_name 例子
CREATE TABLE IF NOT EXISTS schedule (id INT AUTO_INCREMENT COMMENT 编号,course VARCHAR(100) NOT NULL COMMENT 课程,teacher VARCHAR(40) NOT NULL COMMENT 讲师,PRIMARY KEY (id)
) ENGINEinnoDB DEFAULT CHARSETutf8 COMMENT 课程表;SELECT * FROM schedule;NOT NULL表示输入字段不能为NULLENGINE指定引擎DEFAULT CHARSET指定默认编码方式
1.2.3 修改表
# ALTER 修改表
# 添加列:table_name是要修改的表名column_name是要添加的列名datatype是列的数据类型
ALTER TABLE table_name ADD column_name datatype;
# 修改列
ALTER TABLE table_name MODIFY column_name datatype;
# 删除列
ALTER TABLE table_name DROP column_name;例子
ALTER TABLE schedule ADD begin_time DATE DEFAULT 2022-10-24;
# 用于修改表schedule中的列begin_time的数据类型为DATE并通过AFTER指定其放置在列id之后。
ALTER TABLE schedule MODIFY begin_time DATE AFTER id;
ALTER TABLE schedule DROP begin_time;1.2.3 删除表
# 删除表
DROP TABLE table_name ;
# 截断表
TRUNCATE TABLE table_name;
# 清空表
DELETE FROM table_name;1.2.4 表数据的增删改查
# 插入数据
INSERT INTO table_name ( field1, field2,...fieldN )VALUES( value1, value2,...valueN );
# 删除数据
DELETE FROM table_name [WHERE condition];# 修改数据
UPDATE tableName SET fieldnew_value [WHERE condition]# 查询数据
SELECT field... FROM table_name [WHERE condition]1.2.5 去重方式
1GROUP BY GROUP BY 是一个在 SQL 查询中使用的子句用于将结果集按照一个或多个列进行分组并对每个分组执行聚合函数计算。
SELECT 列1, 列2, ...
FROM 表名
WHERE 条件
GROUP BY 列1, 列2, ...例子假设有一张名为 orders 的表包含以下几个列order_id, customer_id, product_id, 和 order_amount。我们想要按照 customer_id 进行分组并计算每个客户的订单总金额。可以使用 GROUP BY 来实现这个目标
SELECT customer_id, SUM(order_amount) as total_amount
FROM orders
GROUP BY customer_id查询结果将按照 customer_id 进行分组并计算每个客户的订单总金额。SUM() 函数用于求和 order_amount 列的值。示例输出
---------------------------
| customer_id | total_amount |
---------------------------
| 1 | 1500 |
| 2 | 2500 |
| 3 | 1800 |
---------------------------2SELECT DISTINCT SELECT DISTINCT 语句用于检索指定列中的唯一值即去除重复的值。它返回列中不同的值每个值只出现一次。
SELECT DISTINCT 列
FROM 表名;例如假设有一张名为 employees 的表包含以下几个列employee_id, first_name, last_name, 和 department。我们希望获取所有不同的部门名称。可以使用 SELECT DISTINCT 来实现这个目标
SELECT DISTINCT department
FROM employees;查询结果将返回 employees 表中去重后的部门名称。示例输出
---------------
| department |
---------------
| Sales |
| Marketing |
| HR |
| Finance |
---------------二、高级查询
结合实例介绍高级查询
SET FOREIGN_KEY_CHECKS0;-- ----------------------------
-- Table structure for class
-- ----------------------------
DROP TABLE IF EXISTS class;
CREATE TABLE class (cid int(11) NOT NULL AUTO_INCREMENT,caption varchar(32) NOT NULL,PRIMARY KEY (cid)
) ENGINEInnoDB AUTO_INCREMENT5 DEFAULT CHARSETutf8;-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO class VALUES (1, 1908班);
INSERT INTO class VALUES (2, 2004班);
INSERT INTO class VALUES (3, 2101班);
INSERT INTO class VALUES (4, 2109班);-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS course;
CREATE TABLE course (cid int(11) NOT NULL AUTO_INCREMENT,cname varchar(32) NOT NULL,teacher_id int(11) NOT NULL,PRIMARY KEY (cid),KEY fk_course_teacher (teacher_id),CONSTRAINT fk_course_teacher FOREIGN KEY (teacher_id) REFERENCES teacher (tid)
) ENGINEInnoDB AUTO_INCREMENT6 DEFAULT CHARSETutf8;-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO course VALUES (1, 数学, 1);
INSERT INTO course VALUES (2, 语文, 2);
INSERT INTO course VALUES (3, 英语, 4);
INSERT INTO course VALUES (4, 理综, 2);
INSERT INTO course VALUES (5, 文综, 1);-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS score;
CREATE TABLE score (sid int(11) NOT NULL AUTO_INCREMENT,student_id int(11) NOT NULL,course_id int(11) NOT NULL,num int(11) NOT NULL,PRIMARY KEY (sid),KEY fk_score_student (student_id),KEY fk_score_course (course_id),CONSTRAINT fk_score_course FOREIGN KEY (course_id) REFERENCES course (cid),CONSTRAINT fk_score_student FOREIGN KEY (student_id) REFERENCES student (sid)
) ENGINEInnoDB AUTO_INCREMENT58 DEFAULT CHARSETutf8;-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO score VALUES (1, 1, 1, 10);
INSERT INTO score VALUES (2, 1, 2, 59);
INSERT INTO score VALUES (3, 4, 5, 100);
INSERT INTO score VALUES (4, 5, 4, 67);
INSERT INTO score VALUES (5, 2, 2, 66);
INSERT INTO score VALUES (6, 2, 1, 58);
INSERT INTO score VALUES (8, 2, 3, 68);
INSERT INTO score VALUES (9, 2, 4, 99);
INSERT INTO score VALUES (10, 3, 1, 77);
INSERT INTO score VALUES (11, 3, 2, 66);
INSERT INTO score VALUES (12, 3, 3, 87);
INSERT INTO score VALUES (13, 3, 4, 99);
INSERT INTO score VALUES (14, 4, 1, 79);
INSERT INTO score VALUES (15, 4, 2, 11);
INSERT INTO score VALUES (16, 4, 3, 67);
INSERT INTO score VALUES (17, 4, 4, 100);
INSERT INTO score VALUES (18, 5, 1, 79);
INSERT INTO score VALUES (19, 5, 2, 11);
INSERT INTO score VALUES (20, 5, 3, 67);
INSERT INTO score VALUES (21, 5, 5, 100);
INSERT INTO score VALUES (22, 6, 1, 9);
INSERT INTO score VALUES (23, 6, 2, 100);
INSERT INTO score VALUES (24, 6, 3, 67);
INSERT INTO score VALUES (25, 6, 4, 100);
INSERT INTO score VALUES (26, 7, 1, 9);
INSERT INTO score VALUES (27, 7, 2, 100);
INSERT INTO score VALUES (28, 7, 3, 67);
INSERT INTO score VALUES (29, 7, 4, 88);
INSERT INTO score VALUES (30, 8, 1, 49);
INSERT INTO score VALUES (31, 8, 2, 100);
INSERT INTO score VALUES (32, 8, 3, 67);
INSERT INTO score VALUES (33, 8, 4, 88);
INSERT INTO score VALUES (34, 9, 1, 91);
INSERT INTO score VALUES (35, 9, 2, 88);
INSERT INTO score VALUES (36, 9, 5, 67);
INSERT INTO score VALUES (37, 9, 4, 22);
INSERT INTO score VALUES (38, 10, 1, 90);
INSERT INTO score VALUES (39, 10, 2, 77);
INSERT INTO score VALUES (40, 10, 3, 43);
INSERT INTO score VALUES (41, 10, 4, 87);
INSERT INTO score VALUES (42, 11, 1, 90);
INSERT INTO score VALUES (43, 11, 2, 77);
INSERT INTO score VALUES (44, 11, 5, 43);
INSERT INTO score VALUES (45, 11, 4, 87);
INSERT INTO score VALUES (46, 12, 1, 90);
INSERT INTO score VALUES (47, 12, 2, 77);
INSERT INTO score VALUES (48, 12, 3, 43);
INSERT INTO score VALUES (49, 12, 5, 87);
INSERT INTO score VALUES (50, 13, 3, 87);
INSERT INTO score VALUES (51, 14, 2, 33);
INSERT INTO score VALUES (52, 15, 3, 22);
INSERT INTO score VALUES (53, 15, 5, 11);
INSERT INTO score VALUES (54, 13, 1, 99);
INSERT INTO score VALUES (55, 13, 2, 99);
INSERT INTO score VALUES (56, 13, 4, 67);
INSERT INTO score VALUES (57, 13, 5, 87);-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS student;
CREATE TABLE student (sid int(11) NOT NULL AUTO_INCREMENT,gender char(1) NOT NULL,class_id int(11) NOT NULL,sname varchar(32) NOT NULL,PRIMARY KEY (sid),KEY fk_class (class_id),CONSTRAINT fk_class FOREIGN KEY (class_id) REFERENCES class (cid)
) ENGINEInnoDB AUTO_INCREMENT17 DEFAULT CHARSETutf8;-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO student VALUES (1, 男, 1, 罗谊);
INSERT INTO student VALUES (2, 女, 1, 戴巧);
INSERT INTO student VALUES (3, 男, 1, 叶黎);
INSERT INTO student VALUES (4, 男, 3, 邵柴);
INSERT INTO student VALUES (5, 女, 1, 韩琪);
INSERT INTO student VALUES (6, 男, 3, 尹伸);
INSERT INTO student VALUES (7, 女, 2, 孙燕);
INSERT INTO student VALUES (8, 男, 2, 廖宽);
INSERT INTO student VALUES (9, 男, 2, 孙行);
INSERT INTO student VALUES (10, 女, 2, 宋贤);
INSERT INTO student VALUES (11, 男, 2, 谭国兴);
INSERT INTO student VALUES (12, 女, 3, 于怡瑶);
INSERT INTO student VALUES (13, 男, 4, 文乐逸);
INSERT INTO student VALUES (14, 男, 4, 邹乐和);
INSERT INTO student VALUES (15, 女, 5, 邓洋洋);
INSERT INTO student VALUES (16, 男, 5, 秦永福);-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS teacher;
CREATE TABLE teacher (tid int(11) NOT NULL AUTO_INCREMENT,tname varchar(32) NOT NULL,PRIMARY KEY (tid)
) ENGINEInnoDB AUTO_INCREMENT6 DEFAULT CHARSETutf8;-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO teacher VALUES (1, 王天一老师);
INSERT INTO teacher VALUES (2, 谢小二老师);
INSERT INTO teacher VALUES (3, 廖阿三老师);
INSERT INTO teacher VALUES (4, 吴启四老师);
INSERT INTO teacher VALUES (5, 谢飞五老师); 2.1 基础查询
-- 全部查询
SELECT * FROM student;
-- 只查询部分字段
SELECT sname, class_id FROM student;
-- 别名 列明 不要用关键字
SELECT sname AS 姓名 , class_id AS 班级ID
FROM student;
-- 把查询出来的结果的重复记录去掉
SELECT distinct class_id FROM student;2.2 条件查询
-- 查询姓名为 邓洋洋 的学生信息
SELECT * FROM student WHERE name 邓洋洋;
-- 查询性别为 男并且班级为 2 的学生信息
SELECT * FROM student WHERE gender男 AND class_id2;2.3 范围查询
-- 查询班级id 1 到 3 的学生的信息
SELECT * FROM student WHERE class_id BETWEEN 1 AND 3;2.4 判空查询
SELECT * FROM student WHERE class_id IS NOT NULL; #判断不为空
SELECT * FROM student WHERE class_id IS NULL; #判断为空
SELECT * FROM student WHERE gender ; #判断不为空字符串
SELECT * FROM student WHERE gender ; #判断为空字符串2.5 模糊查询
使用 LIKE 关键字%代表任意数量的字符”_”代表占位符
-- 查询名字为 m 开头的学生的信息
SELECT * FROM teacher WHERE tname LIKE 谢%;
-- 查询姓名里第二个字为 小 的学生的信息
SELECT * FROM teacher WHERE tname LIKE _小%;2.6 分页查询
分页查询主要用于查看第N条 到 第M条的信息通常和排序查询一起使用 使用LIMIT 关键字第一个参数表示从条记录开始显示第二个参数表示要显示的数目。表中默认第一条记录的参数为0。
-- 查询第二条到第三条内容
SELECT * FROM student LIMIT 1,2;2.7 查询后排序
ORDER BY field
# ASC升序, DESC降序
SELECT * FROM score ORDER BY num ASC;
-- 按照多个字段排序
SELECT * FROM score ORDER BY course_id DESC,num DESC;2.8 聚合查询
聚合函数描述sum()计算某列的总和avg()计算某列的平均值max()计算某列的最大值min()计算某列的最小值count()计算某列的行数
SELECT sum(num) FROM score;
SELECT avg(num) FROM score;
SELECT max(num) FROM score;
SELECT min(num) FROM score;
SELECT count(num) FROM score;2.9 分组查询
-- 可以把查询出来的结果根据某个条件来分组显示
SELECT gender FROM student GROUP BY gender;
-- 分组加group_concat
SELECT gender, group_concat(sname) as name FROM student GROUP BY gender;
-- 分组加聚合
SELECT gender, count(*) as num FROM student GROUP BY gender;
-- 分组加条件
SELECT gender, count(*) as num FROM student GROUP BY gender HAVING num 6;2.10 联表查询 2.10.1 INNER JOIN 只取两张表有对应关系的记录
SELECT
cid
FROM
course
INNER JOIN teacher ON course.teacher_id
teacher.tid;2.10.2 LEFT JOIN 在内连接的基础上保留左表没有对应关系的记录
SELECT
course.cid
FROM
course
LEFT JOIN teacher ON course.teacher_id teacher.tid;2.10.3 RIGHT JOIN 在内连接的基础上保留右表没有对应关系的记录
SELECT
course.cid
FROM
course
RIGHT JOIN teacher ON course.teacher_id
teacher.tid;2.11 子查询/合并查询
2.11.1 单行子查询
SELECT * from course WHERE teacher_id (SELECT tid FROM teacher WHERE tname 谢小二老师)2.11.2 多行子查询 多行子查询即返回多行记录的子查询.
IN 关键字运算符可以检测结果集中是否存在某个特定的值如果检测成功就执行外部的查询。
EXISTS 关键字内层查询语句不返回查询的记录。而是返回一个真假值。如果内层查询语句查询到满足条件的记录就返回一个真值 true 否则将返回一个假值 false 。当返回的值为 true 时外层查询语句将进行查询当返回的为false时外层查询语句不进行查询或者查询不出任何记录。
ALL 关键字表示满足所有条件。使用 ALL 关键字时只有满足内层查询语句返回的所有结果才可以执行外层查询语句。
ANY 关键字允许创建一个表达式对子查询的返回值列表进行比较只要满足内层子查询中的任意一个比较条件就返回一个结果作为外层查询条件。
在 FROM 子句中使用子查询子查询出现在 from 子句中这种情况下将子查询当做一个临时表使用。
select * from student where class_id in (select cid from course where teacher_id 2);select * from student where exists(select cid from course where cid 5);select student_id,sname
FROM (SELECT * FROM score WHERE course_id 5 OR course_id 2) AS A
LEFT JOIN student ON A.student_id student.sid;三、视图
视图 view 不是表是一种虚表没有实体并不实际存储数据其内容由查询 select 定义。用来创建视图的表称为基表通过视图可以展现基表的部分数据。
视图的优点 1简单使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件对用户来说已经是过滤好的复合条件的结果集。
2安全使用视图的用户只能访问他们被允许查询的结果集对表的权限管理并不能限制到某个行某个列但是通过视图就可以简单的实现。
3数据独立一旦视图的结构确定了可以屏蔽表结构变化对用户的影响源表增加列对视图没有影响源表修改列名则可以通过修改视图来解决不会造成对访问者的影响。
CREATE VIEW 视图名 AS SELECT 语句例如查询 ‘数学’ 课程比 ‘语文’ 课程成绩高的所有学生的学号
CREATE VIEW view_test1 AS SELECT A.student_id FROM
((SELECT student_id,num FROM score WHERE course_id (SELECT cid FROM course WHERE cname 数学) ) AS AINNER JOIN(SELECT student_id,num FROM scoreWHERE course_id (SELECT cid FROM course WHERE cname 语文) ) AS BON A.student_id B.student_id
)
WHERE A.num B.num; SELECT * FROM view_test1;作用
1权限管理。可以隐藏表结构视图的表结构可以开放给用户但不会开放基表的表结构。即某些表对用户屏蔽但是可以给该用户通过视图来对该表操作。
2可复用。减少重复语句书写类似程序中函数的作用。
3节约资源。一些列表的关联查询构成的数据包比较大而使用 select … from view构成的数据包就小多了。
4重构利器。假如因为某种需求需要将 user 拆成表 usera 和表 userb如果应用程序使用 sql 语句select * from user那就会提示该表不存在若此时创建视图
create view user as select a.name,a.age,b.sex
from usera as a, userb as b
where a.nameb.name; 则只需要更改数据库结构而不需要更改应用程序。
5逻辑更清晰屏蔽查询细节关注数据返回。
四、触发器
触发器trigger是一种对表执行某操作后会触发执行其他命令的机制。
4.1 要素
监视对象table 监视事件insert、update、delete 触发时间before after 触发事件insert、update、delete
4.2 语法
-- 创建触发器
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW
BEGINtrigger_body
END-- trigger_time: { BEFORE | AFTER }
-- trigger_event: { INSERT | UPDATE | DELETE }
-- trigger_order: { FOLLOWS | PRECEDES }
-- trigger_bodytbl_name表更新前(OLD, 列名)表更新后(NEW, 列名)-- 确认触发器
SHOW TRIGGERS-- 删除触发器
SHOW TRIGGER trigger_name4.3 例子
DELIMITER 是在 MySQL 数据库中使用的一个关键字用于指定 SQL 语句的分隔符。它的作用是告诉 MySQL 解析器在遇到指定的分隔符时将整个语句作为一个整体进行处理而不会将其中的分号视为语句的结束。通常指定 $$ 或 ||
准备
-- 创建触发器
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW
BEGINtrigger_body
END-- trigger_time: { BEFORE | AFTER }
-- trigger_event: { INSERT | UPDATE | DELETE }
-- trigger_order: { FOLLOWS | PRECEDES }
-- trigger_bodytbl_name表更新前(OLD, 列名)表更新后(NEW, 列名)-- 确认触发器
SHOW TRIGGERS-- 删除触发器
SHOW TRIGGER trigger_nameDROP TABLE IF EXISTS goods;
DROP TABLE IF EXISTS order;CREATE TABLE goods (gid INT PRIMARY KEY auto_increment,name VARCHAR (32),num SMALLINT DEFAULT 0
);CREATE TABLE order (id INT PRIMARY KEY auto_increment,gid INT,quantity SMALLINT COMMENT 下单数量
);DROP TRIGGER if EXISTS trig_order_1;
DROP TRIGGER if EXISTS trig_order_2;需求1客户新建订单购买的数量商品表的库存数量自动改变
delimiter //
CREATE TRIGGER trig_order_1 AFTER INSERT ON order FOR EACH ROW
BEGINUPDATE goods SET num num - new.quantity WHERE gid new.gid;
END //
delimiter ; -- 测试1新建订单
INSERT INTO order VALUES (NULL, 1, 2);
INSERT INTO order VALUES (NULL, 2, 2);
INSERT INTO order VALUES (NULL, 3, 2);SELECT * FROM order;需求2客户修改订单购买的数量商品表的库存数量自动改变
delimiter // EACH ROW
BEGINUPDATE goods SET num num old.quantity - new.quantity WHERE gid new.gid;
END //
delimiter ;
INSERT INTO goods VALUES (NULL, cat, 10);
INSERT INTO goods VALUES (NULL, dog, 10);
INSERT INTO goods VALUES (NULL, pig, 10);-- 测试2修改订单
UPDATE order SET quantity quantity 2 WHERE gid 1;SELECT * FROM goods;五、权限管理
5.1 创建用户
CREATE USER usernamehost IDENTIFIED BY password; host 指定该用户在哪个主机上可以登陆如果是本地用户可用localhost 如果想让该用户可以从任意远程主机登陆可以使用通配符 % ;
5.2 授权
5.2.1 对表授权
GRANT privileges ON database_name.table_name TO usernamehost WITH GRANT OPTION;privileges 用户的操作权限如 SELECT INSERT UPDATE 等如果要授予所的权限则使用ALL
databasename.tablename 如果是.表示任意数据库以及任意表
WITH GRANT OPTION 这个选项表示该用户可以将自己拥有的权限授权给别人。注意经常有人在创建操作用户的时候不指定WITH GRANT OPTION 选项导致后来该用户不能使用 GRANT 命令创建用户或者给其它用户授权。 如果不想这个用户有这个 grant 的权限则不要加该 WITHGRANT OPTION 选项
5.2.2 对视图授权
GRANT SELECT, SHOW VIEW ON database_name.view_name TO usernamehost;5.2.3 刷新权限
-- 修改权限后需要刷新权限
FLUSH PRIVILEGES;
