SQL语句 操作实例

CREATE DATABASE IF NOT EXISTS `school`;

USE school;

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`(
`studentno` INT(4) NOT NULL AUTO_INCREMENT COMMENT'学号',
`loginpwd` VARCHAR(20) DEFAULT NULL,
`studentname` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
`sex` TINYINT(1) DEFAULT NULL COMMENT '性别，0或1',
`gradeid` INT(11) DEFAULT NULL COMMENT '年级编号',
`phone` VARCHAR(50) NOT NULL COMMENT '联系电话，允许为空',
`address` VARCHAR(255) NOT NULL COMMENT '地址，允许为空',
`borndate` DATETIME DEFAULT NULL COMMENT '出生时间',
`email` VARCHAR (50) NOT NULL COMMENT '邮箱账号允许为空',
`identitycard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
PRIMARY KEY (`studentno`),
UNIQUE KEY `identitycard`(`identitycard`),
KEY `email` (`email`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;




DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`(
`gradeid` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET = utf8;-- AUTO_INCREMENT = 6



DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`(
`subjectno`INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
`subjectname` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
`classhour` INT(4) DEFAULT NULL COMMENT '学时',
`gradeid` INT(4) DEFAULT NULL COMMENT '年级编号',
PRIMARY KEY (`subjectno`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;




DROP TABLE IF EXISTS `result`;
CREATE TABLE `result`(
`studentno` INT(4) NOT NULL COMMENT '学号',
`subjectno` INT(4) NOT NULL COMMENT '课程编号',
`examdate` DATETIME NOT NULL COMMENT '考试日期',
`studentresult` INT (4) NOT NULL COMMENT '考试成绩',
 KEY `subjectno` (`subjectno`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;


SHOW TABLES;

SELECT * FROM `grade`;

INSERT INTO `grade`(`gradename`)VALUES('大一'),('大二'),("大三"),("大四");


INSERT INTO `student` 
VALUES(1,'123456','张三',0,1,'12345678912','天津市津南区南开大学津南校区计算机学院','1996-10-10','zhangsan@163.com','123456789987456123'),
(2,'123456','李四',1,3,'12345678912','天津市津南区南开大学津南校区网络空间安全学院','1994-10-10','lisi@163.com','987456123123456789');



INSERT INTO `result` 
VALUES(1,1,'2018-1-4',95),(1,2,'2018-1-9',85),(1,3,'2018-1-12',90),(2,1,'2018-1-4',92),(2,3,'2018-1-12',88);


-- 给字段或表起别名
SELECT `studentno` AS '学号' , `studentname` FROM `student` AS s;


-- 拼接字符串 CONCAT
SELECT CONCAT('姓名：',`studentname`)AS '新名字' FROM `student`;


-- 去重 distinct
-- 查询哪些学生参加了考试
SELECT DISTINCT `studentno` FROM `result`;


-- 查询MySQL版本 (函数)
SELECT VERSION();

-- 计算 （计算表达式）
SELECT 100*3 AS '100*3';

-- 查询自增步长 （系统变量）
SELECT @@auto_increment_increment;



-- 全部考试成绩都加一分后查看
SELECT `studentno`,`studentresult`+1 AS '提分后' FROM `result`;


-- select 表达式 from 表
-- 表达式：文本值，列，null，函数，计算表达式，系统变量




-- 逻辑运算符：and或&&，or或||，!=或not
-- 查询考试成绩在90-95分之间的学生学号和课程编号
SELECT `studentno`,`subjectno` FROM `result` WHERE `studentresult` BETWEEN 90 AND 95;
SELECT `studentno`,`subjectno` FROM `result` WHERE `studentresult` >=90 AND `studentresult`<=100;
SELECT `studentno`,`subjectno` FROM `result` WHERE `studentresult` >=90 && `studentresult`<=100;

-- 查询除3号课程外的课程成绩
SELECT `studentno`,`subjectno` FROM `result` WHERE `subjectno` != 3; 
SELECT `studentno`,`subjectno` FROM `result` WHERE NOT `subjectno` = 3;



-- 模糊查询
-- 比较运算符：is null，is not null，between，like，in，

-- 查询姓张的同学 like %（0到任意个字符） _（1个字符）
SELECT studentno, studentname FROM student WHERE studentname LIKE '张%';


ALTER TABLE `school`.`student` CHANGE `phone` `phone` VARCHAR(50) CHARSET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '联系电话，允许为空', 
CHANGE `address` `address` VARCHAR(255) CHARSET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '地址，允许为空', 
CHANGE `email` `email` VARCHAR(50) CHARSET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '邮箱账号允许为空'; 



INSERT INTO student(studentname) VALUES ('张嘉佳');
INSERT INTO student(studentname) VALUES ('李嘉诚');

-- 查询名字中有“嘉”字的学生
SELECT studentno, studentname FROM student WHERE studentname LIKE '%嘉%';

-- 查询学号为1、2、3的学生姓名
SELECT studentname FROM student WHERE studentno IN (1,2,3);


UPDATE student SET address = '北京' WHERE studentno=3;
UPDATE student SET address = '北京市东城区清华大学东门' WHERE studentno=4;

-- 查询地址为北京的学生
SELECT studentno, studentname FROM student WHERE address IN ('北京');

-- 查询在天津的学生
SELECT studentno, studentname FROM student WHERE address LIKE ('北京%');

-- 查询有出生日期的学生
SELECT studentno, studentname FROM student WHERE borndate IS NOT NULL;


-- 联表查询 7种join理论
-- 查询参加了考试的同学（学号 姓名 课程编号 分数）
SELECT s.studentno,studentname,subjectno,studentresult 
FROM student AS s
INNER JOIN result AS r
WHERE s.`studentno`=r.`studentno`;


SELECT s.studentno,studentname,subjectno,studentresult 
FROM student s
RIGHT JOIN result r
ON s.`studentno`=r.`studentno`;


SELECT s.studentno,studentname,subjectno,studentresult 
FROM student s
LEFT JOIN result r
ON s.`studentno`=r.`studentno`;

/*
inner join：如果表中至少有一个匹配，则返回行（两表中都有的列要指明哪个表，eg:s.studentno）
left join：会从左表中返回所有的值，即使右表中没有匹配（左表student，右表result）
right join：会从右表中返回所有的值，即使左表中没有匹配（左表student，右表result）
*/

-- 查询缺考的同学
SELECT s.studentno,studentname,subjectno,studentresult 
FROM student s
LEFT JOIN result r
ON s.`studentno`=r.`studentno`
WHERE studentresult IS NULL;
-- join on 连接查询
-- where 等值查询


SELECT * FROM `subject`;
SELECT * FROM `result`;
INSERT INTO `subject`(`subjectname`)VALUES('高数-微分'),('线代'),('高数-积分'),('专业数学');

-- 查询参加了考试的学生信息（学号 姓名 科目名 成绩）
SELECT stu.studentno,studentname,subjectname,studentresult
FROM student stu
RIGHT JOIN result r
ON stu.`studentno`=r.`studentno`
INNER JOIN `subject` sub
ON r.`studentno`=sub.`subjectno`;
-- 多张表查询，先两张表查询再逐一加表


-- 自连接
-- 自己的表和自己的表连接，核心：一张表拆为两张一样的表即可，父类表和子类表
DROP TABLE IF EXISTS `category`;
CREATE TABLE IF NOT EXISTS `category`( 
`categoryid` INT(3) NOT NULL COMMENT 'id', 
`pid` INT(3) NOT NULL COMMENT '父id 没有父则为1', 
`categoryname` VARCHAR(10) NOT NULL COMMENT '种类名字', 
PRIMARY KEY (`categoryid`) 
) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci; 

INSERT INTO `category`  VALUES (2, 1, '信息技术'),(3, 1, '软件开发'), (5, 1, '美术设计'),(4, 3, '数据库'),
(8, 2, '办公信息'), (6, 3, 'web开发'), (7, 5, 'ps技术');

SELECT * FROM `category`;

-- 查询父类及其相应的子类
SELECT p.`categoryname` AS '父',s.`categoryname` AS '子'
FROM `category` AS p ,`category` AS s -- 一张表拆为两张表
WHERE s.`pid`=p.`categoryid`;


-- 查询学生所属年级（学号 姓名 年级名）
SELECT s.studentno,studentname,gradename
FROM student s
LEFT JOIN grade g
ON s.`gradeid`=g.`gradeid`;

-- 查询参加了某门考试的学生的成绩（学号，姓名，成绩）
SELECT r.`subjectno`,studentname,studentresult
FROM student s
INNER JOIN result r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` sub
ON sub.`subjectno`=r.`subjectno`
WHERE subjectname='高数-微分';


-- 分页 limit 和排序 order by
-- 排序 升序ASC 降序DESC
SELECT studentno,studentresult FROM result
ORDER BY studentresult ASC;
-- 分页 缓解数据库压力 瀑布流
-- limit 起始值, 每页数量
SELECT studentno,studentresult FROM result
LIMIT 0,3;-- 第一页0,3；第二页3,3；第三页6;3
/*
(i-1)*pagesize,pagesize
i：第i页
pagesize：每页数量
(i-1)*pagesize：当前页的起始值位置
*/


-- 查询某门课程排名前三并且分数大于80分的学生信息（学号，姓名，课程名，分数）
SELECT s.studentno, studentname, subjectname, studentresult
FROM student s
INNER JOIN result r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` sub
ON sub.`subjectno`=r.`subjectno`
WHERE subjectname='高数-微分' AND studentresult>80 -- 分数大于80
ORDER BY studentresult DESC
LIMIT 0,3;-- 排名前三



-- 子查询
-- 在where语句中嵌套一个查询
-- 降序输出某门课程的考试成绩（学号，课程编号，成绩）
-- 连接查询
SELECT studentno,s.subjectno,studentresult
FROM result r
INNER JOIN `subject` s
ON s.`subjectno`=r.`subjectno`
WHERE subjectname='高数-微分'
ORDER BY studentresult DESC;
-- 子查询 由里及外
-- 先查询相应课程的课程编号，再查询成绩
SELECT studentno,subjectno,studentresult
FROM result
WHERE subjectno = (SELECT subjectno FROM `subject` WHERE subjectname='高数-微分');

-- 查询分数不小于90的学生的学号和姓名
SELECT DISTINCT(s.studentno),studentname
FROM student s
INNER JOIN result r
ON s.`studentno`=r.`studentno` 
WHERE studentresult>=90;

SELECT studentno,studentname
FROM student
WHERE studentno IN (SELECT studentno FROM result WHERE studentresult>=90);

-- 查询某课程分数不小于90的学生的学号和姓名
SELECT DISTINCT(s.studentno),studentname
FROM student s
INNER JOIN result r
ON s.`studentno`=r.`studentno` 
WHERE studentresult>=90 AND subjectno=(SELECT subjectno FROM `subject` WHERE subjectname ='高数-微分' );

SELECT DISTINCT(s.studentno),studentname
FROM student s
INNER JOIN result r
ON s.`studentno`=r.`studentno` 
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
WHERE studentresult>=90 AND subjectname='高数-微分';

SELECT studentno,studentname FROM student
WHERE studentno IN (
	SELECT studentno FROM result WHERE studentresult>=90 AND subjectno=(
		SELECT subjectno FROM `subject` WHERE subjectname='高数-微分'
	)
);

-- 函数
SELECT ABS(-8); -- 绝对值
SELECT CEILING(9.4); -- 向上取整
SELECT FLOOR(9.4); -- 向下取整
SELECT RAND(); -- 0-1之间随机数
SELECT SIGN(-10) -- 判断数的符号，正数返回1，负数返回-1,0返回0
SELECT CHAR_LENGTH('hello world'); -- 字符串长度
SELECT CONCAT('测试 ','hello world',' 成功！'); -- 拼接字符串
SELECT UPPER('hello world'); -- 转大写字母
SELECT LOWER('Hello World'); -- 转小写字母
SELECT INSTR('hello world','or'); -- 返回第一次出现的子串的索引
SELECT REPLACE('hello world','hello','bye'); -- 替换字符串内容
SELECT SUBSTR('hello world',1,5); -- 从指定位置截取指定长度的字符串
SELECT REVERSE('hello world'); -- 反转字符串
SELECT INSERT('hello world',1,1,'bye'); -- 从指定位置开始将某个长度的内容替换为子串

-- 时间和日期
SELECT CURRENT_DATE();
SELECT CURDATE();
SELECT CURRENT_TIME();
SELECT NOW();
SELECT LOCALTIME();
SELECT SYSDATE();
SELECT YEAR(NOW());
SELECT DAY(NOW());


-- 系统
SELECT SYSTEM_USER();
SELECT USER();
SELECT VERSION();



-- 聚合函数 count() sum() avg() max() min()
SELECT COUNT(email) FROM student;-- 会忽略null值
SELECT COUNT(*) FROM student; -- 不会忽略null值
SELECT COUNT(1) FROM student; -- 不会忽略null值



-- 分组和过滤 group by 和 having 
-- 计算不同课程的最高分最低分平均分
SELECT subjectname,MAX(studentresult) 最高分,MIN(studentresult) 最低分,AVG(studentresult) 平均分
FROM result r
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
GROUP BY sub.`subjectno`;

-- 计算平均分不小于90分的课程的最高分最低分平均分
SELECT subjectname,MAX(studentresult) 最高分,MIN(studentresult) 最低分,AVG(studentresult) 平均分
FROM result r
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
GROUP BY sub.`subjectno`
HAVING 平均分>90; -- 不能用where


SELECT studentresult FROM result;
SELECT MAX(studentresult) FROM result GROUP BY subjectno,studentno;




-- ================事务==============

-- mysql默认开启事务自动提交
SET autocommit = 0 关闭

-- 开启事务
START TRANSACTION -- 标记一个事务的开始，之后的sql都在同一个事务内

COMMIT -- 提交 持久化 （成功）

ROLLBACK -- 回滚 （失败）

-- 事务结束
SET autocommit = 1 -- 开启自动提交

SAVEPOINT 保存点名 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 删除保存点


-- 转账
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci;

USE shop;
CREATE TABLE `account`(
`id` INT(4) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) NOT NULL,
`money` DECIMAL(9,2) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO account(`name`,`money`)
VALUES('A',800),('B',200);

SET autocommit=0;
START TRANSACTION;
UPDATE account SET money=money-200 WHERE `name`='A';
UPDATE account SET money=money+200 WHERE `name`='B';

COMMIT;
ROLLBACK;
SET autocommit=1;



USE school;
SHOW INDEX FROM student;-- 显示所有的索引信息
ALTER TABLE student ADD FULLTEXT INDEX studentname(`studentname`);-- 增加全文索引 ALTER TABLE 表名 ADD FULLTEXT INDEX 索引名(列名);
EXPLAIN SELECT * FROM `student`;-- EXPLAIN 分析sql执行状况 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(studentname)AGAINST('张');-- EXPLAIN 分析sql执行状况 全文索引 数据量太小时没有效果



-- 插入100万数据.
CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT '',
`eamil` VARCHAR(50) NOT NULL,
`phone` VARCHAR(20) DEFAULT '',
`gender` TINYINT(4) UNSIGNED DEFAULT '0',
`password` VARCHAR(100) NOT NULL DEFAULT '',
`age` TINYINT(4) DEFAULT NULL,
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;


SET GLOBAL log_bin_trust_function_creators=1; -- 开启创建函数功能


DELIMITER $$ -- 将 mysql 解释器命令行的结束符由”;” 改成了”$$”，让函数内的命令遇到”;” 不执行
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 0;
	WHILE i<num DO
		INSERT INTO `app_user`(`name`,`eamil`,`phone`,`gender`)VALUES(CONCAT('用户',i),'19224305@qq.com','123456789',FLOOR(RAND()*2));
		SET i=i+1;
	END WHILE;
	RETURN i;
END$$

DELIMITER ; -- 将 mysql 解释器命令行的结束符由”$$”改回”;” 

SELECT mock_data(); -- 执行此函数 生成一百万条数据


SELECT * FROM app_user WHERE `name`='用户99999';-- 执行耗时 : 0.339 sec

EXPLAIN SELECT * FROM app_user WHERE `name`='用户99999'; -- rows：995233

CREATE INDEX id_app_user_name ON app_user(`name`);

SELECT * FROM app_user WHERE `name`='用户99999'; -- 执行耗时   : 0.015 sec

EXPLAIN SELECT * FROM app_user WHERE `name`='用户99999'; -- rows：1



-- 权限管理
-- 创建用户
CREATE USER prin@loaclhost IDENTIFIED BY '111111';-- create user 用户名 identified by 密码;
-- 修改密码
SET PASSWORD = '123456';-- 修改当前用户密码
SET PASSWORD FOR prin@loaclhost = '123456';-- 修改指定用户密码
-- 重命名
RENAME USER prin@loaclhost TO new_name_prin@loaclhost;
-- 用户授权
GRANT ALL PRIVILEGES ON *.* TO new_name_prin@loaclhost;-- 将所有数据库中所有表的全部权限授予给指定用户（但该用户不能给其他用户授权）
-- 查询权限
SHOW GRANTS FOR new_name_prin@loaclhost;
SHOW GRANTS FOR root@localhost;
-- 撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM new_name_prin@loaclhost;-- 删除指定用户的所有数据库中所有表的全部权限
-- 删除用户
DROP USER new_name_prin@loaclhost;