mysql基础语法_values(`user`,new.sid,new.cid,now(),null,new.newgr

-- 新建数据库，并指定字符集编码[if not exists]可选，用于合理性判断
CREATE DATABASE IF NOT EXISTS db2 CHARSET 'utf8';

-- 删除数据库[IF EXISTS]可选
DROP DATABASE IF EXISTS db2;

-- 查看所有数据库
SHOW DATABASES;

-- 切换数据库
USE db1;

-- 跨库访问[库名称.表名称]
SELECT * FROM mysql.`user`;

-- 显示表结构[desc 表名称]
DESC SUBJECT;

-- 显示建表时的语句
SHOW CREATE TABLE SUBJECT;

-- 建表语句
CREATE TABLE SUBJECT(
    sbj_no INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    sbj_name VARCHAR(32),
    ClassHour INT,
    GrateId INT);

CREATE TABLE IF NOT EXISTS grade(
    GradeId INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    GradeName VARCHAR(50));

CREATE TABLE IF NOT EXISTS student(
    StudentNo INT(4) PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '学号',
    LoginPwd VARCHAR(20) NOT NULL DEFAULT'NULL',
    StudentName VARCHAR(20) NOT NULL  DEFAULT 'NULL' COMMENT'学生姓名',
    Sex TINYINT(1) NOT NULL COMMENT'性别，取值0或1',
    gradeId INT(11) NOT NULL  COMMENT '年级编号',
    Phone VARCHAR(50)  COMMENT '联系电话',
    Address VARCHAR(255)  COMMENT '地址',
    BornDate DATETIME NOT NULL  COMMENT '出生时间',
    Email  VARCHAR(50) COMMENT '邮箱账号',
    IdentifyCard VARCHAR(18) NOT NULL DEFAULT 'NULL' COMMENT '身份证号');

-- 建表时添加外键
-- 外键的作用:
-- 1、把从表和主表字段绑定，从表字段的值只能来自主表
-- 2、主表字段的索引和值不能随意变动，数据相对安全
CREATE TABLE student(
    stu_id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    stu_name VARCHAR(32),
    gradeId INT(11),
    phone VARCHAR(11),
    address VARCHAR(64),
    CONSTRAINT fk_stu_gra FOREIGN KEY (gradeId) REFERENCES grade(gradeID));

DROP TABLE student;

-- 向表中插入数据(可以选择插入的字段，没有选择需要插入全部字段)
-- INSERT INTO  表名  [ ( 字段1, 字段2, 字段3, … ) ]  VALUES  (  '值1', '值2', '值3', …)
INSERT INTO SUBJECT(sbj_no,sbj_name,ClassHour,GrateId) VALUES(1,'高等数学-1',120,1);

INSERT INTO grade VALUES
(1,'大一'),
(2,'大二'),
(3,'大三'),
(4,'大四');

INSERT INTO SUBJECT(sbj_name,ClassHour,GrateId) VALUES
    ('java',100,2),
    ('C语言',100,3),
    ('mysql',100,4),
    ('linux',100,1),
    ('hadoop',100,2);
    
-- 查询表中所有数据
SELECT * FROM grade;

-- 修改表名[RENAME AS]也可以
ALTER TABLE grades RENAME TO grade;

-- 修改列名和属性
ALTER TABLE grade CHANGE  GradeId gradeId INT(4);
ALTER TABLE SUBJECT CHANGE GrateId gradeId INT;

-- 修改列属性
ALTER TABLE grade MODIFY GradeName VARCHAR(30);

-- 增加表中一列 [COLUMN] [AFTER] [FIRST]可选
ALTER TABLE grade ADD COLUMN test VARCHAR(30) AFTER gradeId ;

-- 删除表中一列
ALTER TABLE grade DROP test;

-- 向表中添加外键
ALTER TABLE student ADD CONSTRAINT fk_stu_gra FOREIGN KEY (gradeId) REFERENCES grade(gradeID);

-- 向表中删除外键
ALTER TABLE student DROP FOREIGN KEY fk_stu_gra;

-- 修改表中数据
UPDATE grade SET GradeName='研一' WHERE gradeId = 5;

UPDATE SUBJECT SET ClassHour = ClassHour-10 
WHERE ClassHour>=110 
AND gradeId=2;

-- 批量修改表中数据
UPDATE SUBJECT SET sbj_name = '高等数学',
    ClassHour=150,
    gradeId=2
WHERE sbj_no = 1;

-- where子句条件查询
-- BETWEEN AND 是双闭合区间
SELECT * FROM SUBJECT WHERE    sbj_no BETWEEN 2 AND 4;
-- >= 和 <=
SELECT * FROM grade WHERE gradeId >= 2 AND gradeId<=4;

-- 删除表中数据
DELETE FROM SUBJECT WHERE sbj_no = 1;

-- 删除表并建立一个一样的新的空表
TRUNCATE TABLE student;
-- 删除表中所有数据
DELETE FROM student;

-- 索引(索引太多会降低增删改的效率)
-- 主键索引：primary key(可以添加多个字段，组成联合主键)，
ALTER TABLE student ADD PRIMARY KEY(stu_id,stu_name);
-- 唯一索引：unique [key] (unique可以与多个字段一起构成联合索引),
ALTER TABLE student ADD UNIQUE KEY(stu_id,stu_name);
-- 常规索引：index/key 
ALTER TABLE student ADD KEY(stu_id,stu_name);
-- 全文索引：fulltext ，注意：使用全文索引，引擎只能是myisam，适合大型文本集
-- 查看索引
-- show index/key from 表名
-- \G可以转为列显示(去命令行里执行)
EXPLAIN SELECT * FROM student WHERE stu_id=1\G;

SELECT @@tx_isolation;
START TRANSACTION; -- 开启事务
SET autocommit=TRUE;-- 0,1也可以控制，0是关闭

-- 事务
SET autocommit=FALSE;
START TRANSACTION;
    UPDATE account SET money=money-500 WHERE id=1;
    UPDATE account SET money=money+500 WHERE id=2;
    SELECT * FROM account;
ROLLBACK;
COMMIT;
SET autocommit=TRUE;

-- 触发器
CREATE TABLE account_acc(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(16));

DELIMITER $
CREATE TRIGGER tri_acc
BEFORE INSERT ON account
FOR EACH ROW
BEGIN
    INSERT INTO account_acc(NAME)
    VALUES(new.name);
END$
DELIMITER ;

INSERT INTO account VALUES(3,"张飞",1200);

DROP TRIGGER tri_acc;
DROP TABLE account_acc;

-- 触发器练习
CREATE TABLE stu_history(
    id INT PRIMARY KEY AUTO_INCREMENT,
    opration_type VARCHAR(16),
    operation_time DATETIME,
    operation_id INT,
    operation_detail VARCHAR(200));

DELIMITER $
CREATE TRIGGER stu_insert
AFTER INSERT ON student
FOR    EACH ROW
BEGIN
    INSERT INTO stu_history
    VALUES(NULL,'insert',NOW(),new.stu_id,
        CONCAT('插入数据{stu_id=',new.stu_id,
                ',stu_name=',new.stu_name,
                ',gradeId=',new.gradeId,
                ',password=',new.password,
                ',address=',new.address,'}'));
END $
DELIMITER ;

DELIMITER $
CREATE TRIGGER stu_delete
AFTER DELETE ON student
FOR    EACH ROW
BEGIN
    INSERT INTO stu_history
    VALUES(NULL,'delete',NOW(),old.stu_id,
        CONCAT('删除数据{stu_id=',old.stu_id,
                ',stu_name=',old.stu_name,
                ',gradeId=',old.gradeId,
                ',password=',old.password,
                ',address=',old.address,'}'));
END $
DELIMITER ;

DELIMITER $
CREATE TRIGGER stu_update
BEFORE UPDATE ON student
FOR    EACH ROW
BEGIN
    INSERT INTO stu_history
    VALUES(NULL,'delete',NOW(),new.stu_id,
        CONCAT('修改数据{stu_id：',IFNULL(NULLIF(new.stu_id,old.stu_id),'未修改'),
                ',stu_name：',IFNULL(NULLIF(new.stu_name,old.stu_name),'未修改'),
                ',gradeId：',IFNULL(NULLIF(new.gradeId,old.gradeId),'未修改'),
                ',password：',IFNULL(NULLIF(new.password,old.password),'未修改'),
                ',address：',IFNULL(NULLIF(new.address,old.address),'未修改'),'}'));
END $
DELIMITER ;

INSERT INTO student(stu_name,gradeId,PASSWORD,address) VALUES
('王小二',3,1587,'长安城'),
('王小三',4,158766,'长安城');

DELETE FROM student WHERE stu_id = 16;

UPDATE student SET PASSWORD=123456 WHERE stu_id = 15;

-- 视图练习

CREATE VIEW stu_score AS
SELECT t1.`stu_id`,t1.`stu_name`,t1.`gradeId`,t2.`sbj_no`,t2.`exam_date`,t2.`score`
FROM student t1
LEFT JOIN result t2
ON t1.`stu_id`=t2.`stu_id`;

CREATE VIEW stu_grade AS
SELECT t1.`stu_id`,t1.`stu_name`,t1.`gradeId`,t2.`gradeName`
FROM student t1
LEFT JOIN grade t2
ON t1.`gradeId`=t2.gradeId;

CREATE VIEW stu_score_sbj AS
SELECT t1.`stu_name`,t2.`sbj_name`,t1.`score`
FROM stu_score t1
LEFT JOIN `subject` t2
ON t1.`sbj_no`=t2.`sbj_no`;

CREATE VIEW stu_score_sbj2 AS
SELECT t2.*,t1.`gradeName`
FROM stu_grade t1
JOIN stu_score_sbj t2
ON t1.`stu_name`=t2.`stu_name`;

SELECT *
FROM stu_score_sbj2 a
WHERE 
    (SELECT COUNT(1)
    FROM stu_score_sbj2 b
    WHERE a.`gradeName`=b.`gradeName`
    AND a.`sbj_name`=b.`sbj_name`
    AND a.`stu_name`!=b.`stu_name`
    AND a.`score`<b.`score`)<5
ORDER BY 
        gradeName,
        sbj_name,
        stu_name,
        score DESC;

-- 存储过程
DROP PROCEDURE pro_test1;
DELIMITER $
CREATE PROCEDURE pro_test1(
    IN sid INT,
    IN sbj_name VARCHAR(16),
    OUT out_score DOUBLE(5,2),
    OUT out_examtime DATE)
BEGIN
    SELECT score,exam_date
    INTO out_score,out_examtime -- into 表示查询结果赋值给输出变量
    FROM result  a
    JOIN `subject` b
    WHERE a.stu_id=sid AND b.sbj_name=sbj_name
    GROUP BY exam_date;
END $
DELIMITER ;
-- 存储过程的调用
CALL pro_test1(1,'java',@out_score,@out_examtime);
-- 查询结果
SELECT @out_score,@out_examtime;

-- 函数的使用
DROP FUNCTION getGrade;
DELIMITER $
CREATE FUNCTION getGrade(sid INT, sbj_name VARCHAR(16))
RETURNS VARCHAR(32)
BEGIN
    DECLARE avgScore DOUBLE(5,2);
    DECLARE grade VARCHAR(32);
        SELECT ROUND(AVG(score),2)
        INTO avgScore
        FROM result  a
        JOIN `subject` b
        WHERE a.stu_id=sid AND b.sbj_name=sbj_name
        GROUP BY a.stu_id;
    IF
        avgScore BETWEEN 0 AND 60
    THEN
        SET grade='不及格';
    ELSEIF 
        avgScore BETWEEN 61 AND 89
    THEN
        SET grade='良好';
    ELSEIF 
        avgScore BETWEEN 90 AND 100
    THEN
        SET grade='优秀';
    ELSE
        SET grade='成绩有误';
    END IF;
    RETURN grade;
END $
DELIMITER ;

SELECT getGrade(1,'java') 成绩等级;

-- 游标的使用
CREATE TABLE temp(stu_name VARCHAR(16));

DROP PROCEDURE test_cur;
DELIMITER $
CREATE PROCEDURE test_cur(OUT rst VARCHAR(200))
BEGIN
DECLARE NAME VARCHAR(20) ;
DECLARE done INT;
DECLARE cur_stuName CURSOR FOR
    SELECT stu_name FROM student;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' 
    SET done=1;
OPEN cur_stuName;
REPEAT
FETCH cur_stuName INTO NAME;
    SELECT CONCAT_WS ( ', ',rst,NAME) INTO rst;
    INSERT INTO temp VALUES (NAME);
        
UNTIL done
END REPEAT;
CLOSE cur_stuName;
END $
DELIMITER ;

CALL test_cur (@rst) ;
SELECT @rst;

SELECT * FROM temp;