八 条件语句在各个部分的应用
更新，插入，删除记录的中的数据
- 条件更新、条件删除
- 连接查询
--条件更新
UPDATE cms_user SET age = age - 3 WHERE username LIKE '____';
--更新前三条记录，让已有年龄+10
UPDATE cms_user SET age = age +10 LIMIT 3;
--按照id降序排列
UPDATE cms_user SET age = age +10 ORDER BY id DESC LIMIT 3;
--条件删除
DELETE FROM cms_user WHERE sex = 'man' ORDER BY age DESC LIMIT 3;
--连接查询
--将两个或两个以上的表按某个条件链接起来，从中个选取出需要的数据
--链接插叙是同时查询两个或两个以上的表时使用的。
--当不同的表中存在相同意义的字段是，可以通过该字段链接这几个表
--内连接查询
--显示连个表中符合链接条件的记录
--JOIN / CROSS JOIN / INNER JOIN
--通过on连接条件
--查询cms_user表中id，username
--查询provinces表中，proName；
SELECT cms_user.id ,username ,proName FROM cms_user,provinces;
--并不能实现想要的目的
--cms_user的ProId对应省份表中的id,使用where第一次筛选，与内连接语句是等价的
SELECT cms_user.id ,username ,proName FROM cms_user,provinces
WHERE cms_user.proId = provinces.id;
--用内连接的方式把各个表中的相关信息统一为同一个表中的信息
--查询cms_user表中的id,username,email,sex;
--查询procinces表中的proName
SELECT u.id,u.username,u.email,u.sex,p.proName
FROM cms_user AS u
INNER JOIN provinces AS p
ON u.proId = p.id;
--通过on连接条件
SELECT u.id,u.username,u.email,u.sex,p.proName
FROM cms_user AS u
CROSS JOIN provinces AS p
ON u.proId = p.id;
--查询cms_user表中id，username,sex
--查询procinces表中的proName
--条件是cms_user 的性别是男的用户
--根据p.proName进行分组GROUP
--对分组结果进行二次筛选，跳出组中人数大于等于一的人。
SELECT u.id,u.username,u.email,u.sex,p.proName,COUNT(*)AS totalUsers
FROM cms_user AS u
JOIN provinces AS p
ON u.proId = p.id
WHERE u.sex = 'man'
GROUP BY p.proName
HAVING COUNT(*) >= 1
ORDER BY u.id ASC
LIMIT 0,2;
--外联结查询
--左外连接LEFT[OUTER]JOIN显示左全部记录及右表符合链接条件的记录
--右外连接RIGHT[OUTER]JOIN显示右表全部记录及左表符合链接条件的记录
SELECT u.id,u.username,u.email,u.sex,p.proName,COUNT(*)AS totalUsers
FROM cms_user AS u
LEFT JOIN provinces AS p
ON u.proId = p.id;
--左外连接就是一左边的表为查询主表，从左边寻找满足条件要求的项，并在右表中找到对应的数据
九 外键的相关操作
- 外键操作
- 外键的创建、删除
- 联合查询
--外键操作
--作用：保证数据的一致性和完整性
--外键是表的一个特殊字段。被参照的表是主表，外键所在的字段的表为字表，是指外键的原则需要记住
--就是依赖于数据库中已存在的表的主键。外间的作用时间里该表与其附表的关联关系，
--附表中对记录做操作时，子表中与之对应的信息也应该有相应的改变。
--可以实现一对一或一对多的关系
--父表和子表必须使用相同的存储引擎，而且禁止使用临时表
--数据表的存储引擎智能为innodb
--外键列和参照列必须具有相似的数据类型。其中数字的长度或是否为有符号位必须相同；而字符长度可以不同
--外键列和参照列必须创建索引。如果外键列不存在索引的话，MySQL自动创建索引
--实践操作，创建部门表（主表）
CREATE TABLE IF NOT EXISTS department (
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depName VARCHAR(20) NOT NULL UNIQUE
)ENGINE = INNODB;
INSERT department(depName)VALUES('teach'),
('market'),
('undergo'),
('watch');
--创建员工表（字表）
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('kin',1),
('joe',2 ),
('est',3),
('bob',4),
('tom',5);
--内连接实现
SELECT e.id,e.username,d.depName FROM
employee AS e
JOIN
department AS d
ON e.depId = d.id;
--删除watch部门,虽然部门没有了，但是子表中部门下的人仍然存在。
DELETE FROM department WHERE depName = 'watch';
--所以以上内容都是不符合要求的
DROP TABLE department , employee;
--下面是通过外键常见的表
CREATE TABLE IF NOT EXISTS department (
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depName VARCHAR(20) NOT NULL UNIQUE
)ENGINE = INNODB;
INSERT department(depName)VALUES('teach'),
('market'),
('undergo'),
('watch');
--创建员工表（字表）
CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED
FOREINGN KYE(depId)REFERENCES department(id)
)ENGINE = INNODB;
INSERT employee(username ,depId)VALUES('kin',1),
('joe',2 ),
('est',3),
('bob',4),
('tom',5);
--如果子表中有某个父表记录下的内容，则父表中不能执行删除操作
--如果父表中没有相应的外键，子表在插入时所使用的外键超出父表所使用的范围，会报错
--添加和删除外键的操作
--添加外键
ALTER TABLE employee DROP FOREIGN KEY em_fk_dep; --em_fk_dep是外键的索引
--删除外键
ALTER TABLE employee ADD CONSTRANINT emp_fk_dep FOREIGN KEY(depId) REFERENCES department(id);
--CASCADE:从附表中珊瑚或更新且自动删除或更新子表中匹配行
--SET NULL:从附表中删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL
--RESTRICT:拒绝对父表的删除和更新操作
--NO ACTION:在MySQL中，与restrict关键字相同
--创建员工表（字表）
--ON DELETE CASCADE在删除的时候是及连的
--ON UPDATE CASCADE在更新的时候是及连的
CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED
FOREINGN KYE(depId)REFERENCES department(id) ON DELETE CASCADE ;
)ENGINE = INNODB;
INSERT employee(username ,depId)VALUES('kin',1),
('joe',2 ),
('est',3),
('bob',4),
('tom',5);
--删除部门表中的一个部门
DELETE FROM department WHERE id = 1;
--更新部门表中的数据
UPDATE department SET id = id = 10;
--联合查询
--union 和union all
--NNION 合并相同信息
SELECT username FORM employee UNION SELECT username from cms_user;
--UNION ALL 不合并相同信息
SELECT username FORM employee UNION ALL SELECT username from cms_user;
十 子查询 &正则表达式查询 &运算符的使用
- 子查询
- 正则表达式查询
- 运算符的使用
--子查询的使用
--就是用查询的语句代替条件查询的一部分，查询嵌套
--由[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 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);
--由[NOT]EXISTS 引发的子查询
SELECT id FROM department WHERE id = 5;
SELECT id ,username FROM employee WHERE EXISTS(SELECT id FROM department WHERE id = 5);
--子查询的其他形式
--使用ANY/SOME/ALL 的子查询
--ANY/SOME表示存在性问题
--ALL 表示任意性问题
--查询所有获得奖学金的人
SELECT id ,username,score FROM student WHERE score >= ANY(SLECT level FROM scholarship);
--将结果集出入到另一张表中
SELECT id ,score FROM student;
INSERT test1(id,num)
--创建表的同时将查询结果插入
CREATE TABEL test2(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
score TINYINT UNSIGNED
)SELECT id ,score FROM student;
--正则表达式查询
--REGEXP'匹配方式'
--常用的匹配方式
--^匹配字符开始的部分
--&匹配字符串结尾的部分
--.代表字符串中热议一个字符
--[字符集合]匹配字符集合中的任意一个字符
--[^字符集合]匹配除了字符集合意外的的任意一个字符，只要含有者之外的字符的字符串都能匹配到
--s1,s2,s3匹配s1,s2,s3张任意一个字符串
--*代表零个一个或着多个其前的字符
--+代表1个或者多个其前的字符
--STRING{N}字符出现N次
--字符串{M,N }字符串至少出现M次，最多N次
--查询用户名以t开始的用户
SELECT * FROM cms_user WHERE username REGEXP '^t';
--查询以g结尾的用户
SELECT * FROM cms_user WHERE username REGEXP 'g$';
--
SELECT *FROM cms_user WHERE username REGEXP 'R..G';
--可以用模糊查询实现相同的效果
SELECT * FROM cms_user WHERE username LIKE 'R__G';
--
SELECT * FROM cms_user WHERE username REGEXP '[lto]';
--除了字符集合内容中的内容都会出现
SELECT * FROM cms_user WHERE username REGEXP '[^l]';
--匹配集合中的任何一个字符串
SELECT * FROM cms_user WHERE username REGEXP 'nf|qu|tf';
--匹配前边的字符
SELECT * FROM cms_user WHERE username REGEXP 'que*';
SELECT * FROM cms_user WHERE username REGEXP 't+';
SELECT * FROM cms_user WHERE username REGEXP 'que{2}';
SELECT * FROM cms_user WHERE username REGEXP 'que*{1,3}';
--MySQL中运算符的使用
--算数运算符+ - * / div %
--比较运算符> < = != <=>(检测是否为null) IS NULL NOT BETWEEN AND OR IN
--逻辑运算符&& AND || OR ! NOT XOR
--运算符的优先级
十一 函数与表达式的应用
- 数学函数
- 字符串函数
- 日期时间函数
- 条件判断函数和系统函数
--数学函数库中的函数
CEIL()--进一取整
FLOOR()--舍一取整
MOD()--取余数
POWER()--幂运算
ROUND()--四舍五入
TRUNCATE()--数字截取
ABS()--取绝对值
PI()--圆周率
RAND()--返回0~1之间的随机数
SIGN()--返回x的符号
EXP()--计算e的几次方
--字符串函数库
--CHAR_LENGTH(S)返回字符串的字符数
--length()返回字符串的长度
--concat(s1,s2...)将字符串合并成为一个字符串
--CONCAT_WS(X,S1,S2...)指定分隔符连接字符串
--UPPER(S)/UCASE(S)将字符串转换为大写
--LOWER(S)LCASE(S)将字符串转换为小写
--LEFT(S,N)/RIGHT(S,N)返回字符串前或后n个字符
--LPAD(S1，LEN,S2)/RPAD(S1，LEN,S2)将字符串S1用S2填充到制定的LEN.
--LTRIM(S)/RTRIM(S)/TRIM(S)去掉字符串中的空格
--TRIM(S1 FROM S)去掉字符串s中开始处和结尾处的字符串
--REPEAT(S,N)重复字符串指定次数
--SPACE(N)返回N个空格
--REPLACE(S,S1，S2)将字符串s中搜索s1，替换成s2
--STRCMP(S1,S2)比较字符串，>=<分别返回1,0，-1，不区分大小写
--SUBSTRING(S,N,LEN)截取字符串
--REVERSE(S)反转字符串
--ELT(N,S1,S2...)返回指定位置的字符串
--日期时间函数
--CURDATE(),CURRENT_DATE()返回当前的日期
--CURTIME(),CURRENT_TIME()当前时间
--NOW()当前的日期和时间
--MONTH(D)返回日期中月份的值
--MONTHNAME(D)返回日期中月份的名称
--DAYNAME(D)返回是星期几
--DAYOFWEEK(D)返回一周内的第几天
--WEEKDAY(D)返回星期
--WEEK(D)一年中的低多少个星期
--YEAR(D)返回年份值
--HOUR(T)返回小时值
--MINUTE(T)返回分钟值
--SECOND(T)返回秒数
--DATEDIFF(D1，D2)返回两个日期之间相隔的天数
--条件判断函数和系统函数
--IF (EXPR,V1，V2)如果表达式成立，返回结果v1，否则返回V2
--IFNULL(V1，V2)如果v1不为空，就显示v1的值，否则v2
CASE WHEN exp1
THEN V1
[WHEN EXP2 THEN V2]
[ELSE VN]
END
--case表示函数的开始，end表示函数结束。如果表达式exp1成立时，返回v1
--否则exp2成立时返回v2，一次类推，知道else成立
--系统信息函数
VERSION()--返回数据可的版本号
CONNECTION_ID()--返回服务器的连接数
DATABASE(),SCHEMA()--返回当前数据库
USER(),SYSTEM_USER()--返回当前用户
CURRENT_USER()--返回当前用户
CURRENT_USER--返回当前用户
CHARSET(STR)--返回字符串str的字符集
COLLATION(STR)--返回字符串str的校验字符集
LAST_INSERT_ID()--返回最近生成的AUTO_INCREMET自增长值
--其它常用的函数
--常用到的加密函数
MD5(STR)--信息摘要算法
PASSWORD(STR)--密码算法
ENCODE(str.pwd_str)--加密结果为二进制
DECODE(crypt_str,pwd_str)--对encode加密的的结果反向解密
FROMAT(x,n)--将数字x进行格式化，将x保留到小数点
ASCII(S)--返回字符串s的第一个字符的ascii码值
BIN(X)--返回x的二进制编码
HEX(X)--返回x的十六进制编码
OCT(X)--返回x的八进制编码
CONV(X,F1,F2)--将x从f1进制数编程f2进制数
INET_ATON(IP)--将IP地址转换为数字
INET_NTOA(n)--将数字转换成ip地址
GET_LOCT(name,time)--定义锁
IS_FREE_LOCK('KING')--判断锁是否存在
RELEASE_LOCK(name)--解锁
十二 索引的使用和数据库的管理
- 索引的使用
- 管理数据库
--索引的使用
--索引有一列或多了组合而成，起作用是提高对表中数据的查询速度
--缺点是创建和维护索引需要耗费时间
--索引可以提高查询速度，减慢写入速度
--索引的分类bitree索引和hash索引
--普通索引，类似书签 index = 索引名称[索引字段]
--唯一索引，unique key或者主键unique key = 索引名称{索引字段}
--全文索引，只支持字符串字段，只能建立在全英文的内容上FULLTEXT KEY = 索引名称 索引字段
--单列索引，一个字段上的索引INDEX in_test1(test1)
--多列索引，多个字段上的索引INDEX 多列索引的名称(字段1，字段2，字段3...)
--空间索引，SPACIAL INDEX spa_test(test1);
--如何创建索引
--索引和索引名称不同，索引名称呢就像是某个目录的名字，叫小明索引，索引是指字段
--创建表的时候创建索引
CREATE TABLE tbl_name(
字段名称 字段类型[完整性约束条件],
...,
[UNIQUE|FULLTEXT|SPATIAL]INDEX|KEY[索引名称](字段名称)
[(长度)][asc|desc]
);
--在已经存在的表上创建索引
CREATE[UNIQUE|FULLTEXT|SPATIAL]INDEX 索引名称
ON 表明{字段名称
[(长度)][asc|desc]}
--以 id为普通索引
CREATE INDEX in_id ON test4(id);
ALTER TABLE tbl_name ADD [UNIQUI|FULLTEXT|SPECIAL]
(字段名称)[(字段名称)][ASC|DESC];
ALTER TABLE test4 ADD INDEX in_username(username);
--删除索引
DROP INDEX 索引名称 ON tbl_name
ALTER tbl_name DROP INDEX 索引名称
--管理数据库
--workbench
--通过web方式控制和操作MySQL数据库
--PHPmyadmin
--通过客户端管理MySQL
MySQL中常用的索引结构有：B+树索引和哈希索引两种。目前建表用的B+树索引就是BTREE索引。
在MySQL中，MyISAM和InnoDB两种存储引擎都不支持哈希索引。只有HEAP/MEMORY引擎才能显示支持哈希索引。
创建索引：
CREATE TABLE userInfo(
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`username` varchar(3) NOT NULL COMMENT '用户名',
`age` int(10) NOT NULL COMMENT '年龄',
`addr` varchar(40) NOT NULL COMMENT '地址',
PRIMARY KEY (`id`),
KEY `ind_user_info_username` (`username`) USING BTREE, --此处普通索引
key 'ind_user_info_username_addr' ('username_addr') USING BTREE, --此处联合索引
unique key(uid) USINGBTREE, --此处唯一索引
key 'ind_user_info_addr' (addr(12)) USINGBTREE —-此处 addr列只创建了最左12个字符长度的部分索引
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';
