#***********創(chuàng)建Student表**********
CREATE TABLE IF NOT EXISTS Student (
stuId INT NOT NULL auto_increment PRIMARY KEY,
stuName VARCHAR(255),
stuAge INT
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

#*********查看表結構************
desc  Student;

#*********查看創(chuàng)建表的sql語句************
SHOW create TABLE Student;

#*********查看表的狀態(tài)信息************
SHOW table STATUS LIKE 'Student'; 

#*******修改表的名稱**********
ALTER TABLE Students RENAME Student;

#********查看各字段的詳細信息**********
SHOW COLUMNS FROM Student;

#********添加字段**************
ALTER TABLE Student ADD  stuBirthday date;
-- 添加字段并設置到指定位置
ALTER TABLE Student ADD stuSex INT after stuName;

#********刪除字段**************
ALTER TABLE Student DROP stuBirthday;

#*******修改字段信息************
-- 使用change：可以修改字段名和字段類型
ALTER TABLE Student CHANGE Sex stuSex INT;
-- 使用modify: 可以修改字段類型，但不能修改字段名
ALTER TABLE Student MODIFY Sex CHAR(255);


INSERT INTO Student(stuName,stuSex,stuAge,stuBirthday) VALUE("student1",0,19,CURDATE());

#*****************用戶變量******************
SET @var_1=1;
SELECT @var_1;

SELECT @var_1:= stuAge FROM Student;

#**********創(chuàng)建視圖**********
CREATE VIEW sel_Stu AS SELECT * FROM Student;
SELECT * FROM sel_Stu;

delimiter $

#********創(chuàng)建存儲過程*********
CREATE PROCEDURE test_pro()
BEGIN

DECLARE NAME CHAR(255) DEFAULT '';
SELECT stuName INTO NAME FROM Student WHERE stuId =1;

SELECT NAME;

END $

CALL test_pro();

DROP PROCEDURE test_pro;

delimiter $

CREATE PROCEDURE test_pro1(IN id INT, OUT name CHAR(255))
BEGIN
SELECT stuName INTO name from Student WHERE stuId = id;
SET id = 2;
END $

SET @uid = 1;
SET @name = '';
CALL test_pro1(@uid,@name);
SELECT @uid,@name;


delimiter $

/**
IN:輸入變量
OUT：輸出變量
INOUT: 輸入輸出變量
*/

CREATE PROCEDURE test_pro1(IN id INT, OUT name CHAR(255))
BEGIN
SELECT stuName INTO name from Student WHERE stuId = id;
SET id = 2;
END $

SET @uid = 1;
SET @name = '';
CALL test_pro1(@uid,@name);
SELECT @uid,@name;


#************創(chuàng)建函數(shù)**********
delimiter $

CREATE FUNCTION test_fun1(id INT) RETURNS CHAR(50)
BEGIN

DECLARE NAME CHAR(50) DEFAULT '';
SELECT stuName INTO NAME FROM  Student WHERE stuId = id;
RETURN NAME;

END $

DROP FUNCTION  test_fun1;

SELECT test_fun1(1);

#*******索引************
CREATE INDEX test_index ON Student(stuName);

EXPLAIN SELECT * FROM Student where stuName = 'student1';