mysql 实例代码_mysql实例代码

-- 创建数据表stu

CREATE TABLE stu

(

id INT NOT NULL PRIMARY KEY,

name VARCHAR(25) NOT NULL,

sex VARCHAR(2) NOT NULL,

class_id INT,

age INT,

login_date DATE

);

-- 创建数据表class

CREATE TABLE class

(

id INT NOT NULL PRIMARY KEY,

name VARCHAR(25) NOT NULL,

grade VARCHAR(10) NOT NULL,

t_name VARCHAR(25) NOT NULL

);

-- 向数据表stu中插入记录

INSERT INTO stu VALUES

(101,'JAMES','M',01,20,'2014-07-31'),

(102,'HOWARD','M',01,24,'2015-07-31'),

(103,'SMITH','M',01,22,'2013-03-15'),

(201,'ALLEN','F',02,21,'2017-05-01'),

(202,'JONES','F',02,23,'2015-07-31'),

(301,'KING','F',03,22,'2013-01-01'),

(302,'ADAMS','M',03,20,'2014-06-01');

-- 向数据表class中插入记录

INSERT INTO class VALUES

(01,'MATH','One','JONH'),

(02,'HISTORY','Two','SIMON'),

(03,'PHYSICS','Three','JACKSON');

-- 查询每个班级年龄最大的学生

SELECT a.class_id,b.name,MAX(a.age)

FROM stu a,class b

WHERE a.class_id=b.id

GROUP BY a.class_id;

-- 查询学生JAMES所在班级名称和班主任姓名

SELECT a.name,b.name,b.t_name

FROM stu a,class b

WHERE a.class_id=b.id

AND a.name='JAMES';

-- 使用连接查询，查询所有学生的班级信息

SELECT a.id,a.name,b.name

FROM stu a,class b

WHERE a.class_id=b.id;

-- 在stu表中，计算每个班级各有多少名学生

SELECT b.name,COUNT(*)

FROM stu a,class b

WHERE a.class_id=b.id

GROUP BY b.name;

-- 在stu表中，计算不同班级学生的平均年龄

SELECT b.name,AVG(age)

FROM stu a,class b

WHERE a.class_id=b.id

GROUP BY b.name;

-- 指定所有字段名称插入记录

INSERT INTO students

(id,name,sex,class_id,age,login_date)

VALUES(101,'JAMES','M',01,20,'2014-07-31');

-- 不指定字段名称插入记录

INSERT INTO students VALUES

(102,'HOWARD','M',01,24,'2015-07-31');

-- 同时插入多条记录

INSERT INTO students VALUES

(103,'SMITH','M',01,22,'2013-03-15'),

(201,'ALLEN','F',02,21,'2017-05-01'),

(202,'JONES','F',02,23,'2015-07-31'),

(301,'KING','F',03,22,'2013-01-01'),

(302,'ADAMS','M',03,20,'2014-06-01');

-- 将学生JAMES的年龄增加1

UPDATE students SET age=age+1

WHERE name='JAMES';

-- 将学生JAMES的年龄增加1

UPDATE students SET sex='F',login_date='2016-08-31'

WHERE name='HOWARD';

-- 删除班级号为01的记录

DELETE FROM students

WHERE class_id=01;

-- 创建年龄超过22岁的学生的视图

CREATE VIEW stu_older(id,name,sex,age,login_date)

AS SELECT id,name,sex,age,login_date

FROM stu

WHERE age > 22;

-- 创建01班级的学生的视图

CREATE VIEW stu_class_one

(id,name,sex,age,login_date,class_name)

AS SELECT a.id,a.name,a.sex,a.age,a.login_date,b.name

FROM stu a,class b

WHERE class_id=01 AND a.class_id=b.id;

-- 更新学生HOWARD的年龄

UPDATE stu_class_one

SET age=age-1

WHERE name='HOWARD';

-- 查看创建的视图

SELECT * FROM information_schema.views\G

-- 删除创建的视图

DROP VIEW stu_orlder;

-- 【存储过程】(p299)

-- 创建存储过程

DELIMITER //

CREATE PROCEDURE getStuInfo

(IN id int(11))

BEGIN

SELECT id,a.name,b.name

FROM stu a,class b

WHERE a.class_id=b.id

AND a.id=id;

END //

-- 调用存储过程

DELIMITER ;

CALL getStuInfo(103);

-- 【触发器】(p338)

-- 创建数据表stu_login

CREATE TABLE stu_login

(

id INT NOT NULL PRIMARY KEY,

name VARCHAR(25) NOT NULL,

login_date DATE

);

-- 创建数据表stu_years

CREATE TABLE stu_years

(

id INT NOT NULL PRIMARY KEY,

name VARCHAR(25) NOT NULL,

years INT

);

-- 创建触发器get_years

CREATE TRIGGER get_years

AFTER INSERT ON stu_login

FOR EACH ROW

INSERT INTO stu_years

VALUES(NEW.id,NEW.name,

YEAR(CURDATE())-YEAR(NEW.login_date));

-- 向stu_login

INSERT INTO stu_login VALUES

(101,'JAMES','2014-07-31'),

(102,'HOWARD','2015-07-31'),

(103,'SMITH','2013-03-15');

-- 【索引】(p273)

-- 创建学生索引表

CREATE TABLE stu_index

(

id INT NOT NULL PRIMARY KEY,

name VARCHAR(25) NOT NULL,

sex VARCHAR(2) NOT NULL,

age INT NOT NULL,

login_date DATE NOT NULL,

UNIQUE INDEX uni_idx(id),

INDEX multi_idx(name(25),sex(2))

);

-- 创建班级索引表

CREATE TABLE class_index

(

id INT NOT NULL PRIMARY KEY,

name VARCHAR(25) NOT NULL,

grade VARCHAR(10) NOT NULL,

t_name VARCHAR(25) NOT NULL

);

-- 添加唯一索引

ALTER TABLE class_index

ADD UNIQUE INDEX uni_c_idx(id DESC);

-- 添加普通索引

ALTER TABLE class_index

ADD INDEX com_grade_idx(grade);

-- 添加组合索引

CREATE INDEX multi_col_idx

ON class_index(name,grade);

-- 删除索引

ALTER TABLE class_index

DROP INDEX com_tname_idx;

DROP INDEX multi_col_idx ON class_index;

-- 【用户与权限】(p368)

-- 创建新用户

GRANT SELECT,UPDATE(id,name,grade,t_name)

ON class

TO 'adminNew'@'localhost' IDENTIFIED BY '123'

WITH MAX_CONNECTIONS_PER_HOUR 30;

-- 查询账户信息

SELECT host,user,select_priv,update_priv

FROM mysql.user

WHERE user='adminNew';

-- 查询表权限信息

SELECT host,db,user,table_name,table_priv,column_priv

FROM mysql.tables_priv

WHERE user='adminNew';

-- 查询列权限信息

SELECT host,db,user,table_name,column_priv,column_priv

FROM mysql.columns_priv

WHERE user='adminNew';

-- 查看账户的权限信息

SHOW GRANTS FOR 'adminNew'@'localhost';

-- 收回账户权限

REVOKE SELECT,UPDATE

ON school.class

FROM 'adminNew'@'localhost';

-- 删除用户

DROP USER 'adminNew'@'localhost';

-- 【备份和还原】(p399)

-- 使用mysqldump备份

C:\Users\USER>mysqldump -u root -p school students > D:\mysql_backup\students_bk.sql

-- 使用mysqldump还原

SOURCE D:/mysql_backup/students_bk.sql;

-- 数据库备份

SELECT * FROM school.students

INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/students_out.txt'

FIELDS

TERMINATED BY ','

ENCLOSED BY '\"'

LINES

STARTING BY '

TERMINATED BY '>\r\n';

-- 数据库还原

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/students_out.txt'

INTO TABLE school.students

FIELDS

TERMINATED BY ','

ENCLOSED BY '\"'

LINES

STARTING BY '

TERMINATED BY '>\r\n';