目录
创建数据库
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;