- 1
- 视图可以做的操作:视图的操作和表的操作一样,可以做增删改查。
- 12 视图的注意事项:视图不能更改由基表通过聚合函数得到的表,对视图的数据所做的操作会影响到基表(数据的同步)。
- 13 */
- 14 CREATE VIEW v1
- 15 AS
- 16 SELECT * FROM student WHERE sno IN(SELECT sno FROM sc WHERE cno=(SELECT cno FROM course WHERE cname='语文'));
- 17 -- 对视图中的数据进行处理
- 18 UPDATE v1 SET sname='王华' WHERE sno=1;
- 19 SELECT * FROM v1
- 20 DELETE FROM v1 WHERE sno=1
- 21 INSERT INTO v1 VALUES(1,'王华',18,'男')
- 22 SELECT ssex,COUNT(ssex) FROM v1 GROUP BY ssex
- 23 UPDATE v1 SET ssex='女' WHERE sno=1;
- 24 SELECT * FROM v1 ORDER BY sno ASC
- 25 SELECT * FROM v1 WHERE ssex='女' ORDER BY sage DESC,sno
- 26 SELECT ssex,COUNT(ssex) AS num FROM v1 GROUP BY ssex ORDER BY num DESC
- 27
- 28 DROP VIEW v1 --删除视图
- 29 DESC v1--查看视图结构
- 30 SHOW CREATE VIEW v1
- 31
- 32
- 33
- 34 INSERT INTO student SELECT * FROM student;
- 35 SELECT DISTINCT * FROM student;
- 36
- 37 /*
- 38 什么是索引:是一种有效组合数据的方法。
- 39 索引的分类:普通索引、唯一索引、全文索引、单列索引、多列索引和空间索引。
- 40 索引的好处:可以提高查找速度,缩短查询时间。
- 41 索引的缺点:太多的索引会占用磁盘空间,而且索引的创建和维护会占用时间。因此,在创建索引时要权衡利弊。
- 42 适合创建索引的情况:
- 43 .经常被查询的字段,即在WHERE子句中出现的字段。
- 44 .在分组的字段,即在GROUP BY子句中出现的字段。
- 45 .存在依赖关系的子表和父表之间的联合查询,即主键或外键字段。
- 46 .设置唯一完整性约束的字段。
- 47
- 48 */
- 49 -- 为表中字段添加索引
- 50 -- 1.创建普通索引:5
- 51
- 52 -- 在创建表的时候创建索引:
- 53 -- 直接在表的创建语句中创建索引。
- 54 -- 为已经存在的表创建索引有两种方式:
- 55 -- 第一种
- 56 ALTER TABLE student ADD INDEX ix(sno)
- 57 -- 第二种
- 58 CREATE INDEX ix ON student(sno)
- 59 -- 删除索引
- 60 DROP INDEX ix ON student
- 61 -- 检验索引是否被使用
- 62 EXPLAIN SELECT * FROM student WHERE sno=1
- 63 -- 显示表结构
- 64 SHOW CREATE TABLE student t
- 65 -- 删除表中重复的数据
- 66 -- 1.用distinct关键字筛选目标数据(不重复的数据),然后创建一个临时表来保留目标数据
- 67 CREATE TABLE tm SELECT DISTINCT * FROM student;
- 68 -- 2.删除重复的表
- 69 DROP TABLE student;
- 70 -- 3.把临时表的名字改为原表的名字
- 71 ALTER TABLE tm RENAME TO student;
- 72
- 73
- 74
- 75
- 76 -- 存储过程
- 77 -- 好处:减少网络流量
- 78 DROP PROCEDURE IF EXISTS fn;
- 79 DELIMITER $$
- 80 CREATE PROCEDURE fn ()
- 81 BEGIN
- 82 DECLARE i INT;
- 83 DECLARE sun INT;
- 84 SET i=0;
- 85 SET sun=0;
- 86WHILE i<101 DO
- 87 SET sun=sun+i;
- 88 SET i=i+1;
- 89 END WHILE;
- 90 SELECT sun;
- 91 END$$
- 92 DELIMITER ;
- 93 CALL fn();
- 94
- 95
- 96 DELIMITER $$
- 97 CREATE PROCEDURE fn2()
- 98 BEGIN
- 99 DECLARE i INT;
- 100 SET i=0;
- 101 IF i=0 THEN
- 102 SELECT '你欠费了';
- 103 ELSE
- 104 SELECT '你现在资金充裕';
- 105 END IF;
- 106 END$$
- 107 DELIMITER ;
- 108 CALL fn2();
- 109
- 110 DELIMITER $$
- 111 CREATE PROCEDURE fg(IN id INT)
- 112 BEGIN
- 113 SELECT * FROM student WHERE sno=id;
- 114 END $$
- 115 DELIMITER ;
- 116
- 117 CALL fg(1);
- 118
- 119 DELIMITER $$
- 120 CREATE PROCEDURE fgout(OUT ascore INT)
- 121 BEGIN
- 122 SELECT AVG(score) INTO ascore FROM sc;
- 123 END$$
- 124 DELIMITER ;
- 125
- 126 CALL fgout(@avg);
- 127 SELECT @avg;
- 128 SET @sno='sno';
- 129 SELECT @sno
- 130
- 131 -- 函数 (不能返回表,要想返回表,可以用存储过程)
- 132 -- 创建一个函数,用来根据性别查询人数
- 133 DELIMITER $$
- 134 CREATE FUNCTION fn(m VARCHAR(20))
- 135 RETURNS INT
- 136 BEGIN
- 137 SELECT COUNT(*) INTO @num FROM student WHERE ssex=m;
- 138 RETURN @num;
- 139 END$$
- 140 DELIMITER ;
- 141 -- 删除函数名为fn的函数
- 142 DROP FUNCTION fn;
- 143 -- 调用函数
- 144 SELECT fn('女');
- 145
- 146 -- 创建一个函数,用来统计指定学生的平均成绩
- 147
- 148 DELIMITER $$
- 149 CREATE FUNCTION fn1(sn INT)
- 150 RETURNS DOUBLE
- 151 BEGIN
- 152 SELECT AVG(score) INTO @avg FROM sc WHERE sno=sn;
- 153 RETURN @avg;
- 154 END$$
- 155 DELIMITER ;
- 156
- 157 -- 触发器
- 158 -- 为测试触发器创建两个表 bank 、info
- 159 CREATE TABLE bank(
- 160 id VARCHAR(10) PRIMARY KEY,
- 161 NAME VARCHAR(10),
- 162 money INT
- 163 );
- 164
- 165 CREATE TABLE info(
- 166 cardid VARCHAR(10),
- 167 operatDate DATETIME,
- 168 operattype VARCHAR(6),
- 169 operatMoey INT
- 170 );
- 171 -- 插入测试数据
- 172 INSERT INTO bank VALUES('1001','王华',1000);
- 173 INSERT INTO bank VALUES(1002,'张三',300);
- 174 -- 创建一个触发器
- 175 DELIMITER $$
- 176 CREATE TRIGGER triBank
- 177 AFTER UPDATE ON bank
- 178 FOR EACH ROW
- 179 BEGIN
- 180 IF(old.money-new.money>0) THEN
- 181
- 182 INSERT INTO info VALUES(old.id,CURRENT_TIMESTAMP(),'支取',old.money-new.money);
- 183
- 184 ELSE
- 185
- 186 INSERT INTO info VALUES(old.id,CURRENT_TIMESTAMP(),'存款',new.money-old.money);
- 187
- 188 END IF;
- 189
- 190 END$$
- 191 DELIMITER ;
- 192
- 193 -- 触发触发器
- 194 UPDATE bank SET money=money+200 WHERE id='1002';
- 195 -- 利用左外连接查询所需所有信息
- 196 SELECT bank.*,info.* FROM bank LEFT JOIN info ON bank.id=info.cardid;
- 197
- 198 DELIMITER $$
- 199 CREATE TRIGGER tribank1
- 200 BEFORE UPDATE ON bank
- 201 FOR EACH ROW
- 202 BEGIN
- 203 IF(old.money-new.money>500) THEN
- 204 SET new.money=old.money-500;
- 205 ELSE
- 206 INSERT INTO info VALUES(old.id,CURRENT_TIMESTAMP(),'存款',new.money-old.money);
- 207 END IF;
- 208
- 209 END$$
- 210 DELIMITER ;
- 211
- 212 DROP TRIGGER tribank1;
- 213 UPDATE bank SET money=money-600 WHERE id='1001';
- 214
- 215 -- case when
- 216 /*
- 217
- 218 Case具有两种格式。简单Case函数和Case搜索函数。
- 219 --简单Case函数
- 220 CASE sex
- 221 WHEN '1' THEN '男'
- 222 WHEN '2' THEN '女'
- 223 ELSE '其他' END
- 224 --Case搜索函数
- 225 CASE WHEN sex = '1' THEN '男'
- 226 WHEN sex = '2' THEN '女'
- 227 ELSE '其他' END
- 228
- 229 这两种方式,可以实现相同的功能。简单Case函数的写法相对比较简洁,但是和Case搜索函数相比,功能方面会有些限制,比如写判断式。
- 230 还有一个需要注意的问题,Case函数只返回第一个符合条件的值,剩下的Case部分将会被自动忽略。
- 231 --比如说,下面这段SQL,你永远无法得到"第二类"这个结果
- 232 CASE WHEN col_1 IN ( 'a', 'b') THEN '第一类'
- 233 WHEN col_1 IN ('a') THEN '第二类'
- 234 ELSE'其他' END
- 235 */
- 236 SELECT sno AS '学号',cno,
- 237 CASE
- 238 WHEN score>80 THEN '优秀'
- 239WHEN score<60 THEN '不及格'
- 240 ELSE '良好' END AS '成绩评级'
- 241 FROM sc;
- 242
- 243 SELECT sno AS '学号',sname AS '姓名',ssex AS '性别',
- 244 CASE
- 245 WHEN sage>18 THEN '青年'
- 246WHEN sage<1 THEN '儿童'
- 247 ELSE '少年' END AS '年龄划分'
- 248 FROM student;
- 249
- 250 -- case when在聚合函数中应用
- 251 创建一个测试表
- 252 CREATE TABLE studentInfo(
- 253 id INT PRIMARY KEY,
- 254 sex VARCHAR(10),
- 255 province VARCHAR(10)
- 256 );
- 257
- 258 -- 插入测试数据
- 259 -- 统计各个省份的男女比
- 260 INSERT INTO studentInfo VALUES(1,'男','江西省');
- 261 INSERT INTO studentInfo VALUES(2,'男','广东省');
- 262 INSERT INTO studentInfo VALUES(3,'男','浙江省');
- 263 INSERT INTO studentInfo VALUES(4,'女','江西省');
- 264 INSERT INTO studentInfo VALUES(5,'男','浙江省');
- 265 INSERT INTO studentInfo VALUES(6,'女','浙江省');
- 266 -- 查询studentinfo中所有的信息
- 267 SELECT * FROM studentinfo;
- 268
- 269 -- 统计浙江省的男女比
- 270 SELECT sex,COUNT('浙江省') AS '浙江省' FROM studentinfo GROUP BY sex;
- 271
- 272 -- 统计总的男女比
- 273 SELECT sex,COUNT(*) FROM studentinfo GROUP BY sex;
- 274
- 275 -- 统计各个省的男女比
- 276 SELECT sex,COUNT((CASE province WHEN '浙江省' THEN '浙江省' END )) AS '浙江省',
- 277 COUNT((CASE province WHEN '江西省' THEN '江西省' END )) AS '江西省',
- 278 COUNT((CASE province WHEN '广东省' THEN '广东省' END )) AS '广东省'
- 279 FROM studentinfo GROUP BY sex;
- 280
- 281 -- 创建一个测试表
- 282 CREATE TABLE wwwpopution(
- 283 country VARCHAR(10),
- 284 sex VARCHAR(10),
- 285 population INT
- 286 );
- 287
- 288 -- 插入测试数据
- 289 -- 按照国家和性别进行分组
- 290 INSERT INTO wwwpopution(country,sex,population) VALUES('中国','1',340),
- 291 ('中国','2',260), ('美国','1',45),
- 292 ('美国','2',55), ('加拿大','1',51),
- 293 ('加拿大','2',49),('英国','1',40),
- 294 ('英国','2',60);
- 295
- 296 SELECT * FROM wwwpopution;
- 297 -- 利用case when
- 298 SELECT country AS '国家',SUM(CASE WHEN sex = '1' THEN
- 299 population ELSE 0 END) AS '男',SUM(CASE WHEN sex = '2' THEN
- 300 population ELSE 0 END) AS '女' FROM wwwpopution GROUP BY country
- 301 SELECT country,SUM(CASE popution WHEN '1' THEN '1' END) FROM wwwpopution GROUP BY country
- 302
- 303 -- 事务
- 304 -- 创建一个测试表
- 305 CREATE TABLE depart(
- 306 did CHAR(1) PRIMARY KEY NOT NULL,
- 307 dname VARCHAR(20) NULL,
- 308 dmaster CHAR(3) NULL,
- 309 droom CHAR(10) NULL
- 310 );
- 311 -- 在存储过程中事务的回滚Demo
- 312 -- 无参存储过程
- 313 DELIMITER $$
- 314 DROP PROCEDURE IF EXISTS test_sp1
- 315 CREATE PROCEDURE test_sp1( )
- 316 BEGIN
- 317 DECLARE t_error INTEGER DEFAULT 0; -- 设置一个事务标识位
- 318 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1; -- 声明一个sql异常,当触发这个异常时,把标识位置为1
- 319
- 320 START TRANSACTION;
- 321 INSERT INTO depart VALUES('5', '科研部','002','4201');
- 322 INSERT INTO depart VALUES('6', '宣传部','004','4202');
- 323 INSERT INTO depart VALUES('7', '工会','006','4203');
- 324
- 325 IF t_error = 1 THEN -- 标识位为1,事务回滚
- 326 ROLLBACK;
- 327 ELSE -- 为0提交事务
- 328 COMMIT;
- 329 END IF;
- 330 SELECT test_sp1; -- 返回标识位的结果集;(1.代表回滚 0.代表提交)
- 331 END$$
- 332 DELIMITER ;
- 333
- 334 CALL test_sp1();
- 335
- 336 -- 有参数的存储过程
- 337
- 338 DELIMITER $$
- 339 #DROP PROCEDURE IF EXISTS test_sp2
- 340 CREATE PROCEDURE test_sp2(IN id CHAR(1),IN dn VARCHAR(20),IN dm CHAR(3),IN dr CHAR(10))
- 341 BEGIN
- 342 DECLARE t_error INTEGER DEFAULT 0; -- 设置一个事务标识位
- 343 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1; -- 声明一个sql异常,当触发这个异常时,把标识位置为1
- 344
- 345 START TRANSACTION;
- 346 INSERT INTO depart VALUES(id,dn,dm,dr);
- 347 IF t_error = 1 THEN -- 标识位为1,事务回滚
- 348 ROLLBACK;
- 349 ELSE -- 为0提交事务
- 350 COMMIT;
- 351 END IF;
- 352 SELECT t_error; -- 返回标识位的结果集;(1.代表回滚 0.代表提交)
- 353 END$$
- 354 DELIMITER ;
- 355
- 356 CALL test_sp2('5','科研部','002','4201');
- 357 CALL test_sp2('1','财务部','003','2201');
- 358 CALL test_sp2('2','人事处','005','2209');
- 359 CALL test_sp2('3','市场部','009','3201');
- 360 CALL test_sp2('4','开发部','001','3206');
- 361
- 362 -- 带输出参数的存储过程
- 363 DELIMITER $$
- 364 #DROP PROCEDURE IF EXISTS test_sp3
- 365 CREATE PROCEDURE test_sp3(IN id CHAR(1),IN dn VARCHAR(20),IN dm CHAR(3),IN dr CHAR(10),OUT err INT)
- 366 BEGIN
- 367 DECLARE t_error INTEGER DEFAULT 0; -- 设置一个事务标识位
- 368 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1; -- 声明一个sql异常,当触发这个异常时,把标识位置为1
- 369
- 370 START TRANSACTION;
- 371 INSERT INTO depart VALUES(id,dn,dm,dr);
- 372 IF t_error = 1 THEN -- 标识位为1,事务回滚
- 373 ROLLBACK;
- 374 ELSE -- 为0提交事务
- 375 COMMIT;
- 376 END IF;
- 377 SET err=t_error; -- 返回标识位的结果集;(1.代表回滚 0.代表提交)
- 378 END$$
- 379 DELIMITER ;
- 380
- 381 CALL test_sp3('4','开发部','001','3206',@m);
- 382 SELECT @m;
来源: http://www.bubuko.com/infodetail-1959778.html