-- CREATE DATABASE IF NOT EXISTS cms DEFAULT CHARACTER SET utf8;-- USE cms;-- 管理员表cms_admin 内容管理系统CREATE TABLE cms_admin(id TINYINT UNSIGNED AUTO_INCREMENT KEY,username VARCHAR(20) NOT NULL UNIQUE,password CHAR(32) NOT NULL,email VARCHAR(50) NOT NULL DEFAULT 'admin@qq.com',role ENUM('普通管理员','超级管理员') DEFAULT '普通管理员');INSERT cms_admin(username,password,email,role) VALUES('admin','admin','admin@qq.com',2);INSERT cms_admin(username,password) VALUES('king','king'),('麦子','maizi'),('queen','queen'),('test','test'); -- 创建分类表cms_cateCREATE TABLE cms_cate(id TINYINT UNSIGNED AUTO_INCREMENT KEY,cateName VARCHAR(50) NOT NULL UNIQUE,cateDesc VARCHAR(200) NOT NULL DEFAULT '');INSERT cms_cate(cateName,cateDesc) VALUES('国内新闻','聚焦当今最热的国内新闻'),('国际新闻','聚焦当今最热的国际新闻'),('体育新闻','聚焦当今最热的体育新闻'),('军事新闻','聚焦当今最热的军事新闻'),('教育新闻','聚焦当今最热的教育新闻');-- 创建新闻表cms_newsCREATE TABLE cms_news(id INT UNSIGNED AUTO_INCREMENT KEY,title VARCHAR(50) NOT NULL UNIQUE,content TEXT,clickNum INT UNSIGNED DEFAULT 0,pubTime INT UNSIGNED,cId TINYINT UNSIGNED NOT NULL COMMENT '新闻所属分类，对应分类表中的id',aId TINYINT UNSIGNED NOT NULL COMMENT '哪个管理员发布的，对应管理员表中的id');INSERT cms_news(title,content,pubTime,cId,aId) VALUES('亚航客机失联搜救尚无线索 未发求救信号','马来西亚亚洲航空公司一架搭载155名乘客的客机28日早晨从印度尼西亚飞往新加坡途中与空中交通控制塔台失去联系，下落不明。',1419818808,1,2),('北京新开通四条地铁线路 迎接首位客人','12月28日凌晨，随着北京地铁6号线二期、7号线、15号线西段、14号线东段的开通试运营，北京的轨道交通运营里程将再添62公里，共计达到527公里。当日凌晨5时许，北京地铁7号线瓷器口换乘站迎来新线开通的第一位乘客。',1419818108,2,1),('考研政治题多次出现习近平讲话内容','新京报讯 （记者许路阳 (微博)）APEC反腐宣言、国家公祭日、依法治国……昨日，全国硕士研究生招生考试进行首日初试，其中，思想政治理论考题多次提及时事热点，并且多次出现习近平在不同场合的讲话内容。',1419818208,3,2),('深度-曾雪麟：佩兰别重蹈卡马乔覆辙','12月25日是前国足主帅曾雪麟的85岁大寿，恰逢圣诞节，患有尿毒症老爷子带着圣诞帽度过了自己的生日。此前，腾讯记者曾专访曾雪麟，尽管已经退休多年，但老爷子仍旧关心着中国足球，为国足揪心，对于国足近几位的教练，他只欣赏高洪波。对即将征战亚洲杯的国足，老爷子希望佩兰不要重蹈卡马乔的覆辙',1419818308,2,4),('国产JAD-1手枪枪架投入使用 手枪可变"冲锋枪"','日前，JAD-1型多功能手枪枪架通过公安部特种警用装备质量监督检验中心检验，正式投入生产使用。此款多功能枪架由京安盾(北京)警用装备有限公司开发研制，期间经广东省江门市公安特警支队试用，获得好评。',1419818408,4,4),('麦子学院荣获新浪教育大奖','麦子学院最大的职业IT教育平台，获奖了',1419818508,1,5),('麦子学院荣获腾讯教育大奖','麦子学院最大的职业IT教育平台，获奖了',1419818608,1,5),('麦子学院新课上线','麦子学院PHP课程马上上线了，小伙伴快来报名学习哈',1419818708,1,5);-- 创建份表 provincesCREATE TABLE provinces(id TINYINT UNSIGNED AUTO_INCREMENT KEY,proName VARCHAR(10) NOT NULL UNIQUE);INSERT provinces(proName) VALUES('北京'),('上海'),('深圳'),('广州'),('重庆');-- 创建用户表cms_userCREATE TABLE cms_user(id INT UNSIGNED AUTO_INCREMENT KEY,username VARCHAR(20) NOT NULL UNIQUE,password CHAR(32) NOT NULL,email VARCHAR(50) NOT NULL DEFAULT 'user@qq.com',regTime INT UNSIGNED NOT NULL,face VARCHAR(100) NOT NULL DEFAULT 'user.jpg',proId TINYINT UNSIGNED NOT NULL COMMENT '用户所属省份');INSERT cms_user(username,password,regTime,proId)VALUES('张三','zhangsan',1419811708,1),('张三丰','zhangsanfeng',1419812708,2),('章子怡','zhangsan',1419813708,3),('long','long',1419814708,4),('ring','ring',1419815708,2),('queen','queen',1419861708,3),('king','king',1419817708,5),('blek','blek',1419818708,1),('rose','rose',1419821708,2),('lily','lily',1419831708,2),('john','john',1419841708,2);-- 查询SELECT * FROM cms_admin;SELECT cms_admin.* FROM cms_admin;-- 查询管理员编号和名称SELECT id,username FROM cms_admin;SELECT username,id,role FROM cms_admin;-- 表来自于哪个数据库下db_name.tbl_nameSELECT id,username,role FROM cms.cms_admin;-- 字段来自于哪张表SELECT cms_admin.id,cms_admin.username FROM cms.cms_admin;-- 给表名起别名SELECT id,username FROM cms_admin AS a;SELECT id,username FROM cms_admin a;SELECT a.id,a.username,a.email,a.role FROM cms_admin AS a;-- 给字段起别名SELECT id AS '编号',username AS '用户名',email AS '邮箱',role '角色' FROM cms_admin;SELECT a.id AS i,a.username AS u,a.email as e,a.role AS r FROM cms_admin AS a; SELECT id AS proId,proId AS id,username FROM cms_user;SELECT * FROM cms_user;SELECT 1,2,3,4,5,id,username FROM cms_user;-- WHERE条件-- 查询编号为1的用户SELECT id,username,email FROM cms_user WHERE id=1;SELECT id,username,email FROM cms_user WHERE username='king';-- 查询编号不为1的用户 有数字时才可用<>SELECT * FROM cms_user WHERE id!=1;SELECT * FROM cms_user WHERE id<>1;-- 添加age字段ALTER TABLE cms_user ADD age TINYINT UNSIGNED DEFAULT 18;INSERT cms_user(username,password,regTime,proId,age)VALUES('test1','test1',1419811708,1,NULL);-- 查询表中记录age值为NULLSELECT * FROM cms_user WHERE age is not NULL;SELECT * FROM cms_user WHERE age<=>NULL;SELECT * FROM cms_user WHERE age<=>18;-- IS NULL 或者IS NOT NULLSELECT * FROM cms_user WHERE age IS NULL;-- 查询编号在3~10之间的用户SELECT * FROM cms_user WHERE id BETWEEN 3 AND 10;-- 查询编号为1，3，5，7，9，11，13，100SELECT * FROM cms_user WHERE id IN(1,3,5,7,9,11,13,100,1000);-- 查询proId为1 和3的用户SELECT * FROM cms_user WHERE proId IN(1,3);-- 查询用户名为king,queen,张三，章子怡的记录SELECT * FROM cms_user WHERE username IN('king','queen','张三','章子怡');SELECT * FROM cms_user WHERE username IN('KinG','QUEEN','张三','章子怡');-- 模糊查询-- %:代表0个一个或者多个任意字符-- _:代表1个任意字符-- 查询姓张的用户SELECT * FROM cms_user WHERE username LIKE '张%';-- 查询用户名中包含in的用户SELECT * FROM cms_user WHERE username LIKE '%in%';SELECT * FROM cms_user WHERE username LIKE '%';-- 查询用户名为3位的用户SELECT * FROM cms_user WHERE username LIKE '___';-- 用户名_i%SELECT * FROM cms_user WHERE username LIKE '_I%';SELECT * FROM cms_user WHERE username LIKE 'king';SELECT * FROM cms_user WHERE username NOT LIKE '_I%';-- 查询用户名为king并且密码为king的用户SELECT * FROM cms_user WHERE username='king' AND password='king';-- 查询编号大于等于3的变量年龄不为NULL的用户SELECT * FROM cms_user WHERE id>=3 AND age IS NOT NULL;-- 查询编号大于等于3的变量年龄不为NULL的用户 并且proId为的3SELECT * FROM cms_user WHERE id>=3 AND age IS NOT NULL AND proId=3;-- 查询编号在5~10的用户并且用户名为4位的用户SELECT * FROM cms_user WHERE id BETWEEN 5 AND 10 AND username LIKE '____';-- 查询用户名以张开始或者用户所在身份为2,4的记录SELECT * FROM cms_user WHERE username LIKE '张%' OR proId IN(2,4);-- 按照用户所属身份分组proIdSELECT * FROM cms_user GROUP BY proId;-- 向用户表中添加性别字段ALTER TABLE cms_user ADD sex ENUM('男','女','保密');UPDATE cms_user SET sex='男' WHERE id IN(1,3,5,7,9);UPDATE cms_user SET sex='女' WHERE id IN(2,4,6,8,10);UPDATE cms_user SET sex='保密' WHERE id IN(12,11);-- 按照用户性别分组SELECT * FROM cms_user GROUP BY sex;-- 按照字段位置分组SELECT * FROM cms_user GROUP BY 7;-- 按照多个字段分组SELECT * FROM cms_user GROUP BY sex,proId;-- 查询编号大于等于5的用户按照sex分组SELECT * FROM cms_user WHERE id>=5 GROUP BY sex;-- 查询id,sex,用户名详情按照性别分组SELECT id,sex,GROUP_CONCAT(username) FROM cms_user GROUP BY sex;-- 查询proId，性别详情，注册时间详情，用户名详情 按proId分组SELECT proId,GROUP_CONCAT(username),GROUP_CONCAT(sex),GROUP_CONCAT(regTime)FROM cms_user GROUP BY proId;UPDATE cms_user SET age=11 WHERE id=1;UPDATE cms_user SET age=21 WHERE id=2;UPDATE cms_user SET age=33 WHERE id=3;UPDATE cms_user SET age=44 WHERE id=4;UPDATE cms_user SET age=25 WHERE id=5;UPDATE cms_user SET age=77 WHERE id=6;UPDATE cms_user SET age=56 WHERE id=7;UPDATE cms_user SET age=88 WHERE id=8;UPDATE cms_user SET age=12 WHERE id=9;UPDATE cms_user SET age=32 WHERE id=10;UPDATE cms_user SET age=65 WHERE id=11;-- 查询编号,sex,用户名详情以及组中总人数按照sex分组SELECT id,sex,GROUP_CONCAT(username) AS users,COUNT(*) AS totalUsers FROM cms_user GROUP BY sex;-- 统计表中所有记录SELECT COUNT(*) AS totalUsers FROM cms_user;SELECT COUNT(id) AS totalUsers FROM cms_user;--COUNT(字段)不统计NULL值SELECT COUNT(age) AS totalUsers FROM cms_user;-- 查询编号,性别,用户名详情，组中总人数，组中最大年龄，最小年龄，-- 平均年龄，以及年龄总和按照性别分组SELECT id,sex,GROUP_CONCAT(username),COUNT(*) AS totalUsers,MAX(age) AS max_age,MIN(age) AS min_age,AVG(age) AS avg_age,SUM(age) AS sum_ageFROM cms_userGROUP BY sex;-- WITH ROLLUP 合计SELECT id,sex,COUNT(*) AS totalUsers,MAX(age) AS max_age,MIN(age) AS min_ageFROM cms_userGROUP BY sex WITH ROLLUP;SELECT id,sex,COUNT(*) AS totalUsers,MAX(age) AS max_age,MIN(age) AS min_age,SUM(age) AS sum_ageFROM cms_userGROUP BY sex WITH ROLLUP;-- 查询性别sex,用户名详情,组中总人数，最大年龄，年龄总和,根据性别分组SELECT sex,GROUP_CONCAT(username) AS users,COUNT(*) AS totalUsers,MAX(age) AS max_age,SUM(age) AS sum_ageFROM cms_user GROUP BY sex;-- 查询组中人数大于2的SELECT sex,GROUP_CONCAT(username) AS users,COUNT(*) AS totalUsers,MAX(age) AS max_age,SUM(age) AS sum_ageFROM cms_user GROUP BY sexHAVING COUNT(*)>2;-- 查询组中人数大于2并且最大年龄大于60的SELECT sex,GROUP_CONCAT(username) AS users,COUNT(*) AS totalUsers,MAX(age) AS max_age,SUM(age) AS sum_ageFROM cms_user GROUP BY sexHAVING COUNT(*)>2 AND MAX(age)>60;-- 查询编号大于等于2的用户SELECT sex,GROUP_CONCAT(username) AS users,COUNT(*) AS totalUsers,MAX(age) AS max_age,SUM(age) AS sum_ageFROM cms_user WHERE id>=2GROUP BY sex;HAVING COUNT(*)>2 AND MAX(age)>60;SELECT id,sex,GROUP_CONCAT(username) AS users,COUNT(*) AS totalUsers,MAX(age) AS max_age,SUM(age) AS sum_ageFROM cms_user WHERE id>=2HAVING COUNT(*)>2 AND MAX(age)>60;-- 按照id降序排列DESC 默认的是ASCSELECT * FROM cms_user ORDER BY id ;SELECT * FROM cms_user ORDER BY id ASC;SELECT * FROM cms_user ORDER BY id DESC;-- 按照年龄升序排列SELECT * FROM cms_user ORDER BY age ASC;SELECT * FROM cms_user ORDER BY 1 DESC;UPDATE cms_user SET age=12 WHERE id=5;-- 按照年龄升序，id降序排列SELECT * FROM cms_user ORDER BY age ASC,id DESC;SELECT id,age,sex,GROUP_CONCAT(username),COUNT(*) AS totalUsers,SUM(age) AS sum_age FROM cms_user WHERE id>=2 GROUP BY sexHAVING COUNT(*)>=2ORDER BY age DESC,id ASC;-- 实现记录随机SELECT * FROM cms_user ORDER BY RAND();-- 查询表中前3条记录SELECT * FROM cms_user LIMIT 3;SELECT * FROM cms_user ORDER BY id DESC LIMIT 5;-- 查询表中前一条记录SELECT * FROM cms_user LIMIT 1;SELECT * FROM cms_user LIMIT 0,1;SELECT * FROM cms_user LIMIT 1,1;SELECT * FROM cms_user LIMIT 0,5;SELECT id,sex,age,GROUP_CONCAT(username),COUNT(*) AS totalUsers,MAX(age) AS max_age,MIN(age) AS min_age,AVG(age) AS avg_age,SUM(age) AS sum_ageFROM cms_userWHERE id>=1GROUP BY sexHAVING COUNT(*)>=2ORDER BY age DESCLIMIT 0,2;show databases;-- 查询一张表中的字段SELECT cms_admin.* from cms_admin;-- 查询管理员编号和名称 多个字段逗号分隔，显示顺序以我写入的顺序SELECT id,username from cms_admin;-- 表来源于哪个数据库名db_name.tbl_nameSELECT username,id from cms.cms_admin;-- 字段来源于哪张表 表名.字段名SELECT cms_admin.id,cms_admin.username FROM cms.cms_admin;-- 给表名起别名SELECT id,username FROM cms_admin AS a;SELECT a.id,a.username,a.email,a.role from cms_admin as a;-- 给字段起别名SELECT id AS '编号',username as '用户名' from cms_admin;-- 给表和字段同时取别名SELECT a.id as i,a.username as u from cms_admin as a;-- 27课 where条件SELECT * from cms_user;SELECT id,username,email from cms_user WHERE id=1;SELECT id,username,email from cms_user WHERE id=111;SELECT id,username,email from cms_user WHERE username='king';SELECT * from cms_user where id>=5;-- 查询编号不等于 != <>SELECT * from cms_user where id!=5;-- 添加age字段alter table cms_user add age TINYINT UNSIGNED DEFAULT 18;SELECT * from cms_user;-- 查询表中记录age为null <=>可以检测null is nullSELECT * from cms_user where age<=>null;SELECT * from cms_user where age is null;SELECT * from cms_user where age is not null;-- 28课 指定范围 between 查询编号在3-10之间的用户SELECT * from cms_user where id between 3 and 10;SELECT * from cms_user where id not between 3 and 10;-- 指定集合的.查询时忽略大小写 in not inSELECT * from cms_user where id in(1,3,5,7,9,1000);SELECT * from cms_user where username in('king','QUEEN','张三');SELECT * from cms_user where id not in(1,3,5,7,9,1000);-- 29课 模糊查询 like %代表0个一个多个任意字符，_代表一个任意字符SELECT * from cms_user where username like '张%';SELECT * from cms_user where username like '%in%';-- 查询所有的，查询3位的用户SELECT * from cms_user where username like '%';SELECT * from cms_user where username like '___';SELECT * from cms_user where username like '_i%';SELECT * from cms_user where username like 'king';SELECT * from cms_user where username not like '%';-- 30课 逻辑运算符 and orSELECT * from cms_user where username='king' and password='king';SELECT * from cms_user where id>=3 and age is not null;SELECT * from cms_user where id>=3 and age is not null and proid=3;SELECT * from cms_user where id between 5 and 10 and username like '____';SELECT * from cms_user where username like '张%' or proid=2 or proid=3;-- and优先级高于orSELECT * from cms_user where (id in(5) or id in (2,3)) AND id in (4);-- 31课 分组group by 只会显示组中的第一条记录SELECT * from cms_user group by proid;-- 向用户表中添加性别,按性别分组alter TABLE cms_user add sex enum('男','女','保密');UPDATE cms_user set sex='男' where id in(1,3,5,7,9);UPDATE cms_user set sex='女' where id in(2,4,6,8,10);UPDATE cms_user set sex='保密' where id in(11,12);SELECT * from cms_user;SELECT * from cms_user group by sex;-- 按照位置分组，少用SELECT * from cms_user group by 9;-- 按多个字段分组SELECT * from cms_user group by sex,proid;-- 查询编号大于等于5的用户，并按照性别分组SELECT * from cms_user WHERE id>=5 group by sex;-- 32课 分组查询配合聚合函数 查询id sex 用户名详情按照性别分组SELECT id,sex, GROUP_CONCAT(username) from cms_user GROUP BY sex;-- 查询proid,性别详情，注册时间详情，用户名详情,按照proid分组SELECT proid,GROUP_CONCAT(username,SEX,REGTIME) from cms_user GROUP BY proid;SELECT proid,GROUP_CONCAT(username),GROUP_CONCAT(SEX),GROUP_CONCAT(REGTIME) from cms_user GROUP BY proid;update cms_user set age=11 WHERE id=1;update cms_user set age=21 WHERE id=2;update cms_user set age=31 WHERE id=3;update cms_user set age=41 WHERE id=4;update cms_user set age=51 WHERE id=5;update cms_user set age=61 WHERE id=6;update cms_user set age=12 WHERE id=7;update cms_user set age=13 WHERE id=8;update cms_user set age=14 WHERE id=9;update cms_user set age=15 WHERE id=10;update cms_user set age=16 WHERE id=11;SELECT * from cms_user;-- 查询编号，性别，用户名详情，以及组中总人数， 按照性别分组SELECT id,sex,GROUP_CONCAT(username) as users ,count(*) as totalusers from cms_user GROUP BY sex;-- 统计总数，和统计id效果类似， 但是count不统计null值SELECT count(*) as totalusers from cms_user;SELECT count(id) as totalusers from cms_user;SELECT count(age) as totalusers from cms_user;-- 查询编号，性别，用户名详情，组中总人数，组中最大年龄，最小年龄，平均年龄，以及年龄总和,按照性别分组。先把要查的内容列出来，再写sql语句SELECT id,sex,GROUP_CONCAT(username),count(*) as totalusers,MAX(age) as max_age,MIN(age) as min_age,AVG(age) as avg_age,SUM(age) as sum_agefrom cms_user GROUP BY sex WITH ROLLUP;-- 配合WITH ROLLUP 记录上面的所有记录的总和-- HAVING语句对于分组查询二次筛选。 HAVING语句一定要配合GROUP BY.查询性别，用户名详情，组中总人数，组中最大年龄，年龄总和SELECT sex,GROUP_CONCAT(username) as users,count(*) as totalusers,max(age) as max_age,SUM(age) as sum_agefrom cms_user GROUP BY sex;-- 查询组中人数大于2的,且最大年龄大于60SELECT sex,GROUP_CONCAT(username) as users,count(*) as totalusers,max(age) as max_age,SUM(age) as sum_agefrom cms_user GROUP BY sexHAVING count(*) >2 andmax(age) >60;-- 查询编号>=2的用户SELECT sex,GROUP_CONCAT(username) as users,count(*) as totalusers,max(age) as max_age,SUM(age) as sum_agefrom cms_user WHERE id >=2GROUP BY sexHAVING count(*) >2 andmax(age) >60;-- ORDER BY对查询结果进行排序。按照id降序排列DESC，默认的是ASC升序SELECT * from cms_user order by id ASC;SELECT * from cms_user order by id desc;-- 按照年龄升序SELECT * from cms_user order by age ASC;-- 按照id 使用字段的位置SELECT * from cms_user order by 1 desc;UPDATE cms_user set age=12 WHERE id=5;-- 组合排序SELECT * from cms_user order by age ASC, id desc;-- 结合前面学的分组一起排序SELECT id,age,sex,GROUP_CONCAT(username),count(*) as totalusers,sum(age) as sum_age from cms_user where id>=2 GROUP BY sex HAVING count(*)>=2 ORDER BY age DESC,id asc;-- 随机排序SELECT * from cms_user order by RAND();-- 使用limit限制显示条数,结果集中的前3条SELECT * from cms_user LIMIT 3;-- LIMIT 0,1 从第几条开始，每页显示的记录数，即偏移量SELECT * from cms_user LIMIT 1;SELECT * from cms_user LIMIT 0,1;SELECT * from cms_user LIMIT 1,1;SELECT * from cms_user LIMIT 0,5;SELECT * from cms_user LIMIT 5,5;SELECT * from cms_user LIMIT 10,5;-- 已经学完了select的所有语句啦!!!SELECT id,sex,age,GROUP_CONCAT(username),count(*) as totalusers,max(age) as max_age,min(age) as min_age,avg(age) as avg_age,sum(age) as sum_ageFROM cms_userGROUP BY sexhaving count(*)>=2order by age desclimit 0,3;-- 更新删除应用。更新用户名为4位的用户，让其已有年龄-3UPDATE cms_user set age=age-3 where username like'____';SELECT * from cms_user;-- 更新前三条用户，让其已有年龄+10.做更新删除时limit只能设置一个参数，不能设偏移量。UPDATE cms_user set age=age+10 LIMIT 3;SELECT * from cms_user;-- 按照id降序排列，更新前三条UPDATE cms_user set age=age+10 ORDER BY id desc LIMIT 3;SELECT * from cms_user;-- 删除用户性别为男的用户，按照年纪降序排列，删除前一条记录DELETE from cms_user where sex='男' order by age desc LIMIT 1;SELECT * from cms_user;-- 误删所有男同胞，补回INSERT cms_user(id,username,password,regTime,proId,age,sex)VALUES(1,'张三','zhangsan',1419811708,1,17,'男'),(3,'章子怡','zhangsan',1419813708,3,18,'男'),(5,'ring','ring',1419815708,2,19,'男'),(7,'king','king',1419817708,5,21,'男'),(9,'rose','rose',1419821708,2,25,'男');SELECT * from cms_user;-- 连接查询.查询省份表.没写关系就是乘SELECT id,username,proid from cms_user;SELECT cms_user.id,username,proname FROM cms_user,provinces;-- cms_user表中的id,对应省份表中的id.WHERE是第一次筛选SELECT u.id,u.username,u.email,u.sex,p.proname FROM cms_user u,provinces pwhere u.proid=p.id;-- 查询cms_user表中的id,username,email,sex-- 查询provinces表pronameSELECT u.id,u.username,u.email,u.sex,p.pronamefrom cms_user as u INNER JOIN provinces as pon u.proid=p.id;-- INNER JOIN和CROSS JOIN和JOIN一样的效果SELECT u.id,u.username,u.email,u.sex,p.pronamefrom cms_user as u CROSS JOIN provinces as pon u.proid=p.id;-- 查询cms_user表中的id,username,sex。查询provinces表proname。条件是cms_user性别为男的用户SELECT u.id,u.username,u.sex,p.pronamefrom cms_user as u INNER JOIN provinces as pon u.proid=p.idWHERE u.sex='男';-- 根据proname分组SELECT u.id,u.username,u.sex,p.pronamefrom cms_user as u INNER JOIN provinces as pon u.proid=p.idWHERE u.sex='男'GROUP BY p.proname;-- 得到分组中的组中人数SELECT u.id,u.username,u.sex,p.proname,COUNT(*) as totalusers,GROUP_CONCAT(username)from cms_user as u INNER JOIN provinces as pon u.proid=p.idWHERE u.sex='男'GROUP BY p.proname;-- 对分组结果二次筛选 挑出组中人数大于等于1的SELECT u.id,u.username,u.sex,p.proname,COUNT(*) as totalusers,GROUP_CONCAT(username)from cms_user as u INNER JOIN provinces as pon u.proid=p.idWHERE u.sex='男'GROUP BY p.pronameHAVING count(*)>=1;-- 按照id升序排列SELECT u.id,u.username,u.sex,p.proname,COUNT(*) as totalusers,GROUP_CONCAT(username)from cms_user as u INNER JOIN provinces as pon u.proid=p.idWHERE u.sex='男'GROUP BY p.pronameHAVING count(*)>=1order by u.id asc;-- 限制显示条数，前两条SELECT u.id,u.username,u.sex,p.proname,COUNT(*) as totalusers,GROUP_CONCAT(username)from cms_user as u INNER JOIN provinces as pon u.proid=p.idWHERE u.sex='男'GROUP BY p.pronameHAVING count(*)>=1order by u.id ascLIMIT 0,2;desc cms_news;-- 查询cms_news中的id,title 查询cms_cate中的catenameSELECT n.id,n.title,c.catename from cms_news as n JOIN cms_cate as con n.cid=c.id;-- 查询cms_news中的id,title cms_admin中的username,roleSELECT n.id,n.title,a.username,a.role from cms_news as n JOIN cms_admin as aon n.aid=a.id;-- 多表连接。查询cms_news中的id,title 查询cms_cate中的catename cms_admin中的username,roleSELECT n.id,n.title,c.catename,a.username,a.role from cms_news as n JOIN cms_cate as c on n.cid=c.idJOIN cms_admin as a on n.aid=a.id;-- 外连接查询-- 插入错误的数据.省份是20INSERT cms_user(username,password,regTime,proId)VALUES('test3','test3',1419811708,20);SELECT * from cms_user;-- 初始的内连接SELECT u.id,u.username,u.email,u.sex,p.pronamefrom cms_user as u CROSS JOIN provinces as pon u.proid=p.id;-- LEFT JOIN 左外连接 把左表的数据都找出来再连接右边数据,左表中有数据而右表中没有此项数据时会显示右表字段为空。right JOIN右外连接 把右表的数据都找出来再连接左边数据。SELECT u.id,u.username,u.email,u.sex,p.pronamefrom cms_user as u LEFT JOIN provinces as p on u.proid=p.id;SELECT u.id,u.username,u.email,u.sex,p.pronamefrom provinces as p LEFT JOIN cms_user as u on u.proid=p.id;-- right JOIN右外连接 把右表的数据都找出来再连接左边数据。SELECT u.id,u.username,u.email,u.sex,p.pronamefrom provinces as p right JOIN cms_user as u on u.proid=p.id;SELECT u.id,u.username,u.email,u.sex,p.pronamefrom cms_user as u right JOIN provinces as p on u.proid=p.id;-- 内连接用的多些，垃圾数据不是查找的目的-- 外键操作。只有innoDB支持外键-- 创建部门表，主表 departmentCREATE TABLE if not EXISTS department(id TINYINT UNSIGNED auto_increment key,depname VARCHAR(20) not null unique)ENGINE=INNODB;insert department(depname) VALUES('教学部'),('市场部'),('运营部'),('督导部');show TABLES;-- 创建员工表，子表CREATE TABLE if not EXISTS employee(id SMALLINT UNSIGNED auto_increment key,username VARCHAR(20) not null UNIQUE,depid TINYINT UNSIGNED)ENGINE=INNODB;insert employee(username,depid) VALUES('king',1),('kin',2),('ki',3),('aliya',4),('ali',1);SELECT * from employee;SELECT e.id,e.username,d.depname from employee as e JOIN department as d on e.depid = d.id-- 删除督导部.但是员工还在，不合理，按理说员工应该安置好后再解散delete from department where depname='督导部';SELECT * from department;-- 即应该先删员工表中教学部的人，再删除部门表中的教学部delete from employee where depid=1;delete from department where id=1;drop TABLE employee,department;-- 外键CREATE TABLE if not EXISTS department(id TINYINT UNSIGNED auto_increment key,depname VARCHAR(20) not null unique)ENGINE=INNODB;insert department(depname) VALUES('教学部'),('市场部'),('运营部'),('督导部');show TABLES;-- 创建员工表，子表CREATE TABLE if not EXISTS employee(id SMALLINT UNSIGNED auto_increment key,username VARCHAR(20) not null UNIQUE,depid TINYINT UNSIGNED,FOREIGN key(depid) REFERENCES department(id))ENGINE=INNODB;insert employee(username,depid) VALUES('king',1),('kin',2),('ki',3),('aliya',4),('ali',1);SELECT * from employee;desc employee;-- 删除主表记录.删不掉，因为受外键约束DELETE from department where id=1;-- 删除employee中的属于1部门的人才行DELETE from employee where depid=1;DELETE from department where id=1;SELECT * from department;SELECT * from employee;-- 这时候插入一条错误的员工部门信息-- Cannot add or update a child row: a foreign key constraint fails 因为有外键，所以不能添加外键元素insert employee(username,depid) VALUES('bin',6);-- 40课 添加删除外键操作.CONSTRAINT 约束强制,指定外键名称drop TABLE employee;CREATE TABLE if not EXISTS employee(id SMALLINT UNSIGNED auto_increment key,username VARCHAR(20) not null UNIQUE,depid TINYINT UNSIGNED,CONSTRAINT em_fk_dep FOREIGN key(depid) REFERENCES department(id))ENGINE=INNODB;show CREATE TABLE employee;SELECT * from department;-- 这时候插入有1部门的人，不会成功insert employee(username,depid) VALUES('king',1),('kin',2),('ki',3),('aliya',4),('ali',1);SELECT * from employee;insert employee(username,depid) VALUES('king',3),('kin',2),('ki',3),('aliya',4),('ali',2);SELECT * from employee;-- 删除外键alter table employee DROP FOREIGN key em_fk_dep;-- desc 看下表结构，创建时会自动创建索引，但是删除时没有自动删除掉索引depid列还有muldesc employee;-- 没有外键的约束，可以删除部门，即使部门里有人DELETE from department where id=2;SELECT * from employee; -- 添加外键.没有成功，因为刚刚删除了部门2，但是部门2里面有员工。所以需要删掉部门为2的员工alter TABLE employee ADD CONSTRAINT emp_fk_dep FOREIGN key(depid) REFERENCES department(id);DELETE FROM employee WHERE depid=2;show CREATE TABLE employee;DROP TABLE employee,department;-- 重新建表格CREATE TABLE if not EXISTS department(id TINYINT UNSIGNED auto_increment key,depname VARCHAR(20) not null unique)ENGINE=INNODB;insert department(depname) VALUES('教学部'),('市场部'),('运营部'),('督导部');-- 创建员工表，子表CREATE TABLE if not EXISTS employee(id SMALLINT UNSIGNED auto_increment key,username VARCHAR(20) not null UNIQUE,depid TINYINT UNSIGNED,-- 当删除父表记录，子表中对应的记录也会删除DELETE CASCADEFOREIGN key(depid) REFERENCES department(id) on DELETE CASCADE)ENGINE=INNODB;insert employee(username,depid) VALUES('king',1),('kin',2),('ki',3),('aliya',4),('ali',1);SELECT * from employee;desc employee;SELECT * from department;-- 删除部门1DELETE FROM department where id=1;SELECT * from department;SELECT * from employee;-- 更新操作.不会成功，因为id对应子表的depid会受影响update department set id=id+10;SELECT * from department;DELETE from employee;update department set id=id+10;SELECT * from department;drop TABLE employee;drop TABLE department;CREATE TABLE if not EXISTS department(id TINYINT UNSIGNED auto_increment key,depname VARCHAR(20) not null unique)ENGINE=INNODB;insert department(depname) VALUES('教学部'),('市场部'),('运营部'),('督导部');CREATE TABLE if not EXISTS employee(id SMALLINT UNSIGNED auto_increment key,username VARCHAR(20) not null UNIQUE,depid TINYINT UNSIGNED,-- 从父表删除或更新且自动删除或更新子表中匹配的行FOREIGN key(depid) REFERENCES department(id) on DELETE CASCADE on update CASCADE)ENGINE=INNODB;insert employee(username,depid) VALUES('king',1),('kin',2),('ki',3),('aliya',4),('ali',1);SELECT * from employee;DELETE FROM department where id=1;update department set id=id+10;SELECT * from department;SELECT * from employee;drop TABLE employee;drop TABLE department;CREATE TABLE if not EXISTS department(id TINYINT UNSIGNED auto_increment key,depname VARCHAR(20) not null unique)ENGINE=INNODB;insert department(depname) VALUES('教学部'),('市场部'),('运营部'),('督导部');CREATE TABLE if not EXISTS employee(id SMALLINT UNSIGNED auto_increment key,username VARCHAR(20) not null UNIQUE,depid TINYINT UNSIGNED,-- 当删除父表记录，子表中对应的记录设置为空FOREIGN key(depid) REFERENCES department(id) on DELETE set null on update set null )ENGINE=INNODB;insert employee(username,depid) VALUES('king',1),('kin',2),('ki',3),('aliya',4),('ali',1);DELETE FROM department where id=1;SELECT * from department;SELECT * from employee;update department set id=id+10;SELECT * from employee;-- 物理外键，逻辑外键-- 41课 联合查询 查询多个表的记录，并且连接到一起 UNION去掉相同记录 , UNION all简单的合并到一起SELECT username from employee;SELECT username FROM cms_user;SELECT username from employee UNION SELECT username FROM cms_user;SELECT username from employee UNION all SELECT username FROM cms_user;-- 两个表要对应字段数量SELECT id,username from employee UNION all SELECT username FROM cms_user;SELECT id,username from employee UNION all SELECT username,age FROM cms_user;-- 42课 子查询。由内向外查询drop TABLE employee;drop TABLE department;CREATE TABLE if not EXISTS department(id TINYINT UNSIGNED auto_increment key,depname VARCHAR(20) not null unique)ENGINE=INNODB;insert department(depname) VALUES('教学部'),('市场部'),('运营部'),('督导部');CREATE TABLE if not EXISTS employee(id SMALLINT UNSIGNED auto_increment key,username VARCHAR(20) not null UNIQUE,depid TINYINT UNSIGNED)ENGINE=INNODB;insert employee(username,depid) VALUES('king',1),('kin',2),('ki',3),('aliya',4),('ali',1);SELECT * from department;SELECT * from employee;SELECT id,username from employee where depid in(1,2,3,4);-- 由in或者 not in引发的子查询SELECT id from department;SELECT id,username from employee where depid in(1,2,3,4);-- 两条合为一条SELECT id,username from employee where depid in(SELECT id from department);SELECT id,username from employee where depid not in(SELECT id from department);-- 构造一条垃圾数据insert employee(username,depid) VALUES('999999',8);SELECT * from employee;-- 子查询是没有这个值的SELECT id,username from employee where depid in(SELECT id from department);SELECT id,username from employee where depid not in(SELECT id from department);-- 使用比较运算符的子查询，创建学员表和奖学金表CREATE TABLE if not EXISTS student(id TINYINT UNSIGNED auto_increment key,username VARCHAR(20) not null UNIQUE,score TINYINT UNSIGNED);INSERT student(username,score) VALUES('king',95),('king1',35),('king2',45),('king3',55),('king4',65),('king5',75),('king6',80),('king7',90),('king8',25);CREATE TABLE if not EXISTS scholarship(id TINYINT UNSIGNED auto_increment key,level TINYINT UNSIGNED);INSERT scholarship(level) VALUES(90),(80),(70);SELECT * from student;SELECT * from scholarship;-- 查询获得一等奖学金的学员SELECT LEVEL from scholarship WHERE id=1;SELECT id,username from student WHERE score>=90;-- 合并为一条SELECT id,username from student WHERE score>=(SELECT LEVEL from scholarship WHERE id=1);-- 子查询相当于一个判断。先满足内查询SELECT * from department where id=5;SELECT id,username from employee where not EXISTS(SELECT * from department where id=41);-- 43课 子查询的形式。查询所有获得奖学金学员信息.也就是大于等于最小值.使用any和some都是等价的SELECT * from scholarship;SELECT id,username,score from student where score>=any(SELECT level from scholarship);SELECT id,username,score from student where score>=some(SELECT level from scholarship);-- 查询获得一等奖学金的所有学员.使用allSELECT id,username,score from student where score>=all(SELECT level from scholarship);-- 查询学员表中没有获得奖学金的学员SELECT id,username,score from student where scoreall(SELECT level from scholarship); -- 将查询结果写入到数据表中 -- 把另一个表中的数据插入 CREATE TABLE test18( id TINYINT UNSIGNED auto_increment key, num TINYINT UNSIGNED); SELECT * from test18; -- 把奖学金表中的数据导入到test18中。先执行select,再插入到test18表中 insert test18(id,num) SELECT id,score from student; SELECT * from test18; CREATE TABLE test19( id TINYINT UNSIGNED auto_increment key, num TINYINT UNSIGNED) SELECT id,score from student; desc test19; -- 这时候有3个字段，num显示为空 SELECT * from test19; -- 把num改成score则显示两个字段 CREATE TABLE test20( id TINYINT UNSIGNED auto_increment key, score TINYINT UNSIGNED) SELECT id,score from student; desc test20; -- 两个表效果一样 SELECT * from test20; -- 44课 正则表达式查询 ^匹配字符开始的部分。查询用户名以t开头的用户 SELECT * from cms_user where username REGEXP '^t'; -- $匹配字符结尾的部分。 SELECT * from cms_user where username REGEXP 'g$'; -- . 代表字符串中的任意一个字符，包括回车和换行 SELECT * from cms_user where username REGEXP '.'; SELECT * from cms_user where username REGEXP 'r..g'; SELECT * from cms_user where username like 'r__g'; -- [字符集合] 匹配字符集合中的任意一个字符 查询用户名中包含lto的用户 SELECT * from cms_user where username REGEXP '[lto]'; -- [^字符集合] 匹配除了字符集合以外的任意一个字符 查询用户名中不包含l的用户 INSERT cms_user(username,password,regTime,proid) VALUES('lll','111',138212349,2), ('ttt','111',138212349,2), ('ooo','111',138212349,2); SELECT * from cms_user where username REGEXP '[^l]'; SELECT * from cms_user where username REGEXP '[^lto]'; SELECT * from cms_user where username REGEXP '[a-z]'; SELECT * from cms_user where username REGEXP '[^a-z]'; -- s1|s2|s3 匹配s1，s2，s3中的任意一个字符串 SELECT * from cms_user where username REGEXP 'ng|qu'; -- *代表0个或者1个或者多个其前的字符 +代表1个或者多个其前的字符 -- e出现0次或1次或多次 SELECT * from cms_user where username REGEXP 'que*'; -- t出现0次或1次或多次,会搜索出全部 SELECT * from cms_user where username REGEXP 't*'; -- t出现1次或多次,一定要有t出现 SELECT * from cms_user where username REGEXP 't+'; -- string {n}字符串出现n次，string {m,n}字符串最少出现m次，最多出现n次 -- e出现2次 SELECT * from cms_user where username REGEXP 'que{2}'; -- e最少出现1次，最多出现3次 SELECT * from cms_user where username REGEXP 'que{1,3}'; -- 45课 运算符的使用 select 1+1,1-1,2*4,3/8; select 1+'3maizi'; show WARNINGS; -- 除div，除以0后得null SELECT 3/0; -- % mod 取余 SELECT 3 mod 8; SELECT 3 % 8; -- 对于null进行任何运算结果还是null SELECT 3 % null; -- 比较运算符，结果都是真或者假 1真0假 SELECT 1=1; SELECT 1='1'; SELECT 1=0; SELECT username,username='king' FROM student; -- 不等于<> != SELECT username,username<>'king' FROM student; SELECT * FROM cms_user; -- 对于null值直接用=不行，需要<=> SELECT id,username,age,sex,age=null from cms_user; SELECT id,username,age,sex,age<=>null from cms_user; -- 大于等于 SELECT id,username,score,score>=70 from student; -- is null 或者 is not null 检测是否等于空 SELECT id,username,age,sex,age is null from cms_user; SELECT id,username,age,sex,age is not null from cms_user; -- BETWEEN and 或者not BETWEEN SELECT id,username,age,age BETWEEN 21 and 30 from cms_user; -- in 或者 not in 判断是否在固定范围内 SELECT id,username,age,age in(21,31,41,51) from cms_user; SELECT 1 in(1,2,3,4); -- like 或not like 判断是否匹配 SELECT 's' like'_'; SELECT id,username,username like '____' from cms_user; -- regexp 判断是否正则匹配 SELECT id,username,username REGEXP '^t' from cms_user; -- 逻辑运算符 && and并且 SELECT 1&&1,null&&1; SELECT 1 and 1; -- 逻辑值||或 SELECT 1 || 1,1 || 0,1 || null,0 || null; SELECT !1,!0,!null; -- XOR 异或，不同为真。同假异真 SELECT 1 XOR 0,0 XOR 1,1 XOR 1,0 XOR 0; -- 46课 数字函数库 系统函数 SELECT VERSION(); -- ceil() ceiling()进一取整 SELECT CEIL(1.2),CEILING(1.2); SELECT * from maizi.test4; SELECT num1,CEIL(num2),CEILING(num3) from maizi.test4; -- floor 舍一取整 SELECT floor(3.14); SELECT num1,FLOOR(num2),FLOOR(num3) from maizi.test4; -- mod取余数 SELECT mod(3,8); -- pow power 幂运算 SELECT pow(2,3),power(2,3); -- round 四舍五入 ROUND(参数，保留位数) SELECT round(3.14),round(3.87); SELECT round(3.145678,2); -- TRUNCATE 数字截取 单单截断，不进行四舍五入 SELECT TRUNCATE(3.145678,2); -- abs 取绝对值 SELECT abs(3.14),abs(-3.14); -- pi 圆周率 SELECT pi(); -- rand 返回0-1之间的随机数 rand(x)会返回一个固定的随机数 SELECT rand(); SELECT * from cms.cms_user order by rand(); SELECT rand(1); SELECT rand(1); -- sign(x) 返回x的符号，为负数时返回-1 SELECT sign(10),sign(0),sign(-10); -- exp 计算e的次方 SELECT exp(3); -- 47课 字符串函数 -- CHAR_LENGTH(str)返回字符串的字符数.length返回字符串的长度 SELECT CHAR_LENGTH('maizi'),LENGTH('maizi'); SELECT CHAR_LENGTH('麦子'),LENGTH('麦子'); -- CONCAT(str1,str2,...)将字符串合并为一个字符串.当字符串中有null时，结果为null SELECT concat('hello','world'); SELECT id,concat(username,'_') from student; SELECT concat('hello','world',null); -- CONCAT_WS(separator,str1,str2,...)以指定的分隔符连接字符串,当连接符是null时，结果为null；当字符串是null时，结果不为null； SELECT CONCAT_WS('^_^','a','b','c'); SELECT CONCAT_WS('','a','b','c'); SELECT CONCAT_WS(null,'a','b','c'); SELECT CONCAT_WS('^_^','a','b',null); -- upper(),UCASE(str) 将字符串转换为大写 SELECT upper('this is a test'),UCASE('this is a test'); -- LOWER(str)，LCASE(str) 大写转为小写 SELECT LOWER('HELLO WORLD'),LCASE('HELLO WORLD'); -- left right 返回字符串的前几个或者后几个字符 SELECT left('ABCDEFG',2),right('ABCDEFG',2); -- LPAD（s1,len,s2) RPAD 将字符串s1用字符串s2填充到指定的len,一个左边一个右边 SELECT lpad('a',5,'?'),rpad('a',5,'!'); -- LTRIM(str),RTRIM(str),TRIM(str)去掉字符串中的空格 SELECT ' ABC ',concat('__',trim(' ABC '),'__'),concat('__',ltrim(' ABC '),'__'),concat('__',rtrim(' ABC '),'__'); -- TRIM(s1 from s) 去掉字符串s中开始处和结尾处的字符串s1 SELECT trim('A' from 'ABCDA') -- REPEAT 重复字符串次数 SELECT repeat('H',5) -- SPACE(N) 返回N个空格 SELECT CONCAT('_',space(5),'_') -- REPLACE(str,from_str,to_str)字符串替换.区分大小写 SELECT replace('ABCDEF','A','_') SELECT replace('ABCDEF','a','_') -- STRCMP(expr1,expr2)比较两个字符串是否相同.比较的是码。前比后大返回1，小返回-1，相同返回0，不区分大小学 SELECT STRCMP('b','a'),STRCMP('a','b'),STRCMP('a','a'),STRCMP('A','a'); -- SUBSTRING(str FROM pos FOR len)截取字符串.字符串从1开始数 SELECT substring('ABCDEF',2,2); -- REVERSE(str)反转字符串 SELECT REVERSE('ABC'); -- ELT返回指定位置的字符串.字符串从1开始 SELECT ELT(2,'A','B','C'); -- 48课 日期时间函数 -- CURRENT_DATE() curdate()得到当前日期 SELECT CURRENT_DATE(),curdate(); -- CURRENT_TIME() curtime()得到当前时间 SELECT CURRENT_TIME(),curtime(); -- now 得到当前日期时间 SELECT now(); -- month 返回月份 SELECT month('2015-1-3'); SELECT month(now()); -- MONTHNAME(date) 返回月份的名字 SELECT MONTHNAME('2015-1-3'); SELECT MONTHNAME(now()); -- DAYNAME(date) 返回周几 SELECT dayname('2015-1-3'); SELECT dayname(now()); -- DAYOFWEEK(date)返回第几天，1代表周日，即从周日开始数 SELECT DAYOFWEEK('2015-1-3'); SELECT DAYOFWEEK(now()); -- WEEKDAY(date)返回第几天，0代表周一，即从周一开始数，从0开始数 SELECT WEEKDAY('2015-1-3'); SELECT WEEKDAY(now()); -- week 一年中的第几个星期 SELECT week('2015-1-3'); SELECT week(now()); -- year 返回年份值 SELECT year('2015-1-3'); SELECT year(now()); -- hour返回小时值 SELECT hour(now()); -- minute 返回分钟数 SELECT MINUTE(now()); -- second 返回秒数 SELECT second(now()); -- datediff 计算两个日期之间相隔的天数 SELECT datediff('2015-1-3','2015-1-1'); SELECT datediff(CURRENT_DATE(), '1993-06-03'); -- 49课 条件判断函数和系统函数 -- if(expr,v1,v2)如果expr成立，则返回v1,否则返回v2 SELECT * from student; SELECT id,username,score,if(score>=60,'及格', '不及格') from student; -- ifnull（v1,v2) 如果v1不为空就显示v1的值，否则显示 v2 SELECT * from cms_user; SELECT id,username,age,ifnull(age,'100') from cms_user; -- case when case表示函数开始，end表示函数结束，如果表达式1成立，返回第一个值；如果2成立，返回第二个值；以此类推，最后遇到else时，返回vn的值 SELECT id,username,score, case when score>80 then'很棒' when score=80 then'还可以' else '需要加油' end from student; -- 常用的系统信息函数 version 返回数据库的版本号 SELECT VERSION(); -- CONNECTION_ID()返回服务器的连接数 SELECT CONNECTION_ID(); -- DATABASE(),SCHEMA()返回当前数据库名 SELECT DATABASE(),SCHEMA(); -- user() SYSTEM_USER() 返回当前用户 SELECT user(),SYSTEM_USER() ; -- CURRENT_USER() CURRENT_USER 返回当前用户 SELECT CURRENT_USER(),CURRENT_USER; -- CHARSET(str)返回字符串str的字符集 SELECT charset('aaa'); -- COLLATION 返回字符串str的校验字符集 SELECT COLLATION ('aaa'); -- LAST_INSERT_ID()返回最近生成的auto_increment值 SELECT LAST_INSERT_ID(); INSERT student(username,score) values ('aliya aliya',150); SELECT LAST_INSERT_ID(); -- 50课 常用的加密函数 MD5(str)信息摘要算法.返回一个32位的字符串 select md5('admin'); SELECT length(md5('admin')); -- PASSWORD 密码算法.对系统用户管理，这时不能用md5 select PASSWORD('admin'); -- 少用. ENCODE(str,pass_str) 加密结果是一二进制数,必须使用blob类型字段保存 -- DECODE(crypt_str,pass_str)对通过ENCODE加密之后的内容解密 -- format(x,n) 将数字x进行格式化，将x保留到小数点后n位 SELECT format(3.145678,2); -- 通过ascii码（s),返回字符串s的第一个字符的ascii码 SELECT ascii('abc'); -- bin(x) 返回x的二进制编码 -- hex(x)返回x的16进制编码 -- oct(x)返回返回x的8进制编码 SELECT bin(5),hex(5),oct(5); -- conv（x,f1,f2)将x从f1进制数变成f2进制数 SELECT conv(5,10,2); SELECT conv(1000,10,2); -- INET_ATON(ip)将ip地址转化为数字 SELECT INET_ATON('127.0.0.1'); -- inet_NOTA(n)将数字转换成ip地址 SELECT INET_NTOA(2130706433); -- get_lock(name,time) 定义锁和时效 SELECT get_lock('king',10) ; -- 判断这个锁是否还存在 1不存在，0存在 SELECT is_free_lock('king') ; -- RELEASE_lock(name) 解锁 SELECT RELEASE_lock('king') ; -- 51课 索引的使用 提高查询速度，缺点是创建和维护需要时间 show tables; -- 创建普通索引 in CREATE TABLE test4( id TINYINT UNSIGNED, username VARCHAR(20), index in_id(id), key in_username(username)); -- 创建唯一索引 uni CREATE TABLE test5( id TINYINT UNSIGNED auto_increment key, username VARCHAR(20) not null UNIQUE, card char(18) not null, UNIQUE key uni_card(card)); -- 创建全文索引 full CREATE TABLE test6( id TINYINT UNSIGNED auto_increment key, username VARCHAR(20) not null UNIQUE, userdesc VARCHAR(20) not null, FULLTEXT index full_userdesc(userdesc)); desc test6 -- 创建单列索引 in 和建立普通索引一样的道理 CREATE TABLE test7( id TINYINT UNSIGNED auto_increment key, test1 VARCHAR(20) not null, test2 VARCHAR(20) not null, test3 VARCHAR(20) not null, test4 VARCHAR(20) not null, index in_test1(test1)); desc test7; -- 创建多列索引 mul CREATE TABLE test8( id TINYINT UNSIGNED auto_increment key, test1 VARCHAR(20) not null, test2 VARCHAR(20) not null, test3 VARCHAR(20) not null, test4 VARCHAR(20) not null, index mul_t1_t2_t3_t4(test1,test2,test3,test4)); desc test8; -- 创建唯一性索引 CREATE TABLE test9( id TINYINT UNSIGNED auto_increment key, test1 VARCHAR(20) not null, test2 VARCHAR(20) not null, test3 VARCHAR(20) not null, test4 VARCHAR(20) not null, UNIQUE key mul_t1_t2_t3_t4(test1,test2,test3,test4)); desc test9; -- 空间索引，必须空间数据类型，引擎需要是MyISAM CREATE TABLE test10( id TINYINT UNSIGNED auto_increment key, test geometry not null, SPATIAL INDEX spa_test(test)) ENGINE=MyISAM; desc test10; -- 删除索引 drop INDEX in_id on test4; drop INDEX in_username on test4; -- 在已有表中加索引 CREATE INDEX in_id on test4(id); alter TABLE test4 add index in_username(username); alter table test5 drop index uni_card; drop INDEX username on test5; desc test5; CREATE UNIQUE INDEX uni_card on test5(card); alter TABLE test5 add UNIQUE index uni_username(username); drop INDEX full_userdesc on test6; CREATE FULLTEXT INDEX full_userdesc on test6(userdesc); drop index mul_t1_t2_t3_t4 on test8; CREATE INDEX mul_t1_t2_t3_t4 on test8(test1,test2,test3,test4); desc test8; drop index spa_test on test10; CREATE SPATIAL INDEX spa_test on test10(test); -- 主键是索引，经常查询的字段去索引，尽量前缀索引，尽量名称短些 -- 52课 PHP MyAdmin mysql数据库的web管理工具 -- 53课 SQLyog
网友评论