创建数据库

CREATE DATABASE jxgl;

删除数据库

DROP DATABASE [IF EXISTS] db_name

数据表的创建与管理

CREATE TABLE students (
	sno CHAR ( 8 ) PRIMARY KEY NOT NULL,
	sname VARCHAR ( 20 ) NOT NULL,
	ssex ENUM ( "男", "女" ) DEFAULT "男" 
);

删除数据表

DROP TABLE [IF EXISTS] table_name;

修改字段数据类型

ALTER TABLE students
MODIFY sno char(4) PRIMARY KEY;

添加字段

ALTER TABLE students
ADD COLUMN description varchar(30);

字段更名

ALTER TABLE students
CHANGE description desc varchar(30);

删除字段

ALTER TABLE students
DROP desc;

表数据管理

插入数据

INSERT INTO students ( sno, sname, ssex )
VALUES
	( "1001", "张三", "男" ),
	( "1002", "李四", default),
	( "1003", "王五", "女" );

查询数据

SELECT
	sno, sname 
FROM
	students 
WHERE
	ssex = '男' 
HAVING
	sname LIKE '%三';

模糊匹配

• %：表示任意 0 个或多个字符，可匹配任意类型和长度的字符。中文用两个百分号 %% 表示
• _：表示匹配任意单个字符
• []：表示转义通配符，使查到%，_，[等特殊字符

排序与分组

SELECT * FROM
	students 
GROUP BY
	ssex 
ORDER BY
	sno DESC;

• 使用DESC或ASC关键字来设置查询结果是按照降序或升序排序，默认为ASC升序
• GROUP BY 中的表达式应与SELECT中的保持一致

HAVING子句查询

在查询语句中，使用HAVING子句为行分组或聚合组指定过滤条件。
HAVING子句与GROUP BY子句一起使用，以根据指定的条件过滤分组。

聚合函数

• AVG()：求平均值
• COUNT()：统计行的数量
• MAX()：求最大值
• MIN()：求最小值
• SUM()：求累加和
  聚合函数可以用在SELECT和HAVING中，不能直接使用在WHERE和GROUP BY中。

多表连接

• INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
• LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
• RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

-- 查询女教师所授课程的课程号,课程名称和学时。
SELECT
	teachers.tno, course.cno, cname, period
FROM
	teaching
	JOIN teachers ON teachers.tno = teaching.tno
	JOIN course ON course.cno = teaching.cno 
WHERE
	tsex = '女';

子查询

-- 查询成绩比所有课程的平均成绩高的学生的学号及成绩
SELECT
	sno, score 
FROM
	sc 
WHERE
	score > ANY ( SELECT avg( score ) FROM sc );

-- 查询‘0000008’号课程不及格的学生信息 
SELECT
* 
FROM
	students 
WHERE
	sno IN ( SELECT sno FROM sc WHERE score < 60 AND cno = '0000008' );

数据更新操作

INSERT

INSERT INTO 
	students(sno, sname)
VALUES 
	("1004",  "Jack");

UPDATE

UPDATE students
SET
	sname = "Mary"
WHERE 
	sno = "1004";

DELETE

DELETE FROM students
WHERE sno = "1004"；

-- 清空表数据，保留结构
DELETE [*] FROM table_name;
TRUNCATE [*] FROM table_name;

-- 删除表的数据和结构
DROP TABLE table_name;

视图的创建与管理

• 视图是虚拟存在的表，是逻辑意义的表，不包含数据
• 视图数据来自自定义视图查询中使用的表，可以展现基表的部分数据
• 使用视图的用户不必关心后面的表结构、关联条件和筛选条件
• 使用视图的用户只能访问被允许查询的结果集
• 保障数据安全性，提高查询效率

创建视图

CREATE VIEW v_stu
AS SELECT *
FROM students
WHERE ssex = "男";

修改视图

ALTER VIEW v_stu
AS SELECT sname
FROM students;

删除视图

DROP VIEW v_stu;

管理视图数据

-- 从视图查询信息
SELECT * FROM v_stu;

-- 修改视图信息
UPDATE v_stu
SET sname = "李华"
WHERE sno = "1003";

--- 向视图添加记录
INSERT INTO v_stu
VALUES
	("1004", "Dan", "女"),
	("1005", "Mike", "男");

-- 修改视图数据
UPDATE v_stu
SET ssex = "男"
WHERE sno = "1004";

-- 删除视图数据
DELETE FROM v_stu
WHERE sno = "1005";

索引的创建与管理

-- 创建索引
CREATE INDEX ix_stu
ON students(sno, sname);

-- 修改表结构添加索引
ALTER TABLE students
ADD INDEX ix_stu(sno, sname);

-- 删除索引
DROP INDEX ix_stu(sno, sname)
ON students;
-- 或
ALTER TABLE students
DROP INDEX ix_stu;

-- 显示索引信息
SHOW INDEX FROM table_name;

存储过程

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

即SQL语言层面的代码封装与调用。

• BEGIN ... END表示存储过程的开始和结束
• IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
• OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
• INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

-- 创建不带参数存储过程
CREATE PROCEDURE proc_1()
BEGIN
	SELECT sno, sname, ssex
	FROM students
	WHERE ssex = "男"
END;

-- 调用存储过程
CALL proc_1;

-- 创建带参数的存储过程
CREATE PROCEDURE proc_2(IN psno char(4))
BEGIN
	SELECT *
	FROM students
	WHERE sno = psno;
END;

CALL proc_2("1001")

-- 创建带输出参数的存储过程
CREATE PROCEDURE proc_3(IN psno char(4), OUT name varchar(20))
BEGIN
	SELECT sname INTO name
	FROM students
	WHERE sno = psno;
END;

CALL proc_3("1001", @name)
SELECT @name;

数据库权限控制

用户管理

• 创建用户

CREATE USER user_name IDENTIFIED BY "password";

• 用户改名

RENAME USER user_name TO new_name;

• 更改用户密码

SET PASSWORD FOR user_name = PASSWORD("new_password");

• 删除用户

DROP USER user_name;

权限管理

• 查看用户权限

SHOW GRANTS FOR user_name;

• 赋予权限

GRANT SELECT, UPDATE ON db_name TO user_name;
FLUSH PRIVILIGES;

• 回收权限

REVOKE UPDATE ON db_name FROM user_name;