数据查询
这是完整形式
SELECT select_expr [, select_expr ...]
[
FROM table_references
[WHERE 条件]
[GROUP BY {col_name | position} [ASC | DESC], ... 分组]
[HAVING 条件 对分组结果进行二次筛选]
[ORDER BY {col_name | position} [ASC | DESC], ...排序]
[LIMIT 限制显示条数]
]
查询表达式
SELECT cms_admin.* FROM cms_admin;#查询cms_admin中所有内容
SELECT id,username FROM cms_admin;#查询cms_admin中的id username两个列
SELECT id,username FROM cms.cms_admin;#查询cm数据库的cms_admin中的id username两个列
SELECT cms_admin.id,cms_admin.username FROM cms.cms_admin#在查询时自动cms_admin下的列
你可能注意到这个表名实在太长了,你也可以使用一些缩写代替,就是第三条AS 缩写名
SELECT a.id,a.username FROM cms_admin AS a;
同样的字段也可以使用AS
SELECT id AS '编号' ,username AS '用户名' FROM cms_admin;
这样在显示的时候也就显示你写的别名了。
条件查询
可使用的条件
<==>也是等于的意思。
<>是不等于的意思。
如果查询值为NULL的内容,使用等号是用不了的,需要用<==>来检测
between and 的使用
例:查询cms_user中id介于3到10之间的行数据。
SELECT * FROM cms_user WHERE id BETWEEN 3AND 10;
指定集合
例:查询cms_user中id是集合(1,3,5,7)的内容。集合中的值可以在表中没有,这样查询对应的不会显示。
SELECT * FROM cms_user WHERE id IN(1,3,5,7);
LIKE模糊查询
需要用到下面的操作符1. % 表示0个1个或者任意多个字符;2. _ 表示1个任意字符。
例:查询cms_user 中username列里以a开头的内容。
SELECT * FROM cms_user WHERE username LIKE 'a%'
分组查询
按照cms_user中的am列进行分组
这样分组只会显示组中第一条记录
SELECT * FROM cms_user GROUP BY am;
SELECT * FROM cms_user GROUP BY am,sex;按照am分组,再进一步按sex分
配合GROUP_CONCAT()来查看分组的详细内容
在cms_user中通过sex分类,得到username的分类详情,前面的id并没有什么实际意义。
SELECT id,sex GROUP_CONCAT(username) FROM cms_user GROUP BY sex;
如果你想显示多个详细内容例如详细显示a和b,那么需要写GROUP_CONCAT(a),GROUP_CONCAT(b)
通常还配合聚合函数使用COUNT统计SUM求和AVG平均MAX最大MIN最小
按照sex分类,并统计每个组中的人数
SELECT id,sex,GROUP_CONCAT(username)AS totalUsers user,COUNT(*) AS FROM cms_user GROUP BY sex;
统计不会记录NULL的单元,如果不分组的话COUNT会统计总的人数
WITH ROLLUP会在最后添加一个统计行,对应相应列的操作(统计、最大、最小)
二次筛选
HAVING,必须配合分组进行,否则HAVING的内容失效。
例:在按照sex分组的前提下再次筛选组内个数大于2的组。
SELECT sex,GROUP_CONCAT(username) AS users,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
SUM(age) AS sum_age
FROM cms_user
GROUP BY sex
HAVING COUNT(*)>2;
保密组就被二次筛选掉了
对查询结果进行排序
ORDER BY 降序排序:DESC 升序排序:ASC NULL值会被作为最小值
例:对cms_user根据id进行升序排序(默认)
SELECT * FROM cms_user ORDER BY id;
按照多个条件进行排序
例:对cms_user根据age进行升序排列后在根据id降序排列,这里两个排序是父子关系,是想age排序如果age相同,在相同组中进行id排序
SELECT * FROM cms_user ORDER BY age ASC,id DESC;
随机选取
SELECT * FROM cms_user ORDER BY RAND();
限制显示条数
只指定条数,就会显示前n条的内容
SELECT * FROM cms_user LIMIT 3;
使用起始值与偏移量。LIMIT m,n 从第m-1条开始,共显示n条。第一条实际为第0条。
SELECT * FROM cms_user LIMIT 0,1;#显示第一条
这个方法也可以用来更新内容,但只能使用第一条语句。
例:给前三条内容的age加10。不能写成limit 0,3
UPDATE cms_user SET age=age+10 LIMIT 3;
连接查询(多个表之间查询)
将两个或两个以上的表按照某个条件连接起来,从中选取需要的数据。连接查询是同时查询两个或两个以上的表时使用的。当不同的表存在相同的字段时,可以通过该字段连接这几个表。
内连接查询
使用此方法的同时也可以嵌套上面的数据查询方法。
显示两个表中复合连接条件的记录。数据必须能对应上,如果有对应不上的,就不会显示。
例:这个表里的proid是省份的代码,省份的实际内容在另一个表中
从cms_user,provinces两个表中载入,都有id列所以要指定,proName是provinces表中的内容,它的内容时根据cms_user.proId=provinces.id对应起来的。
SELECT cms_user.id,username,proName
FROM cms_user,provinces
WHERE cms_user.proId=provinces.id;
使用JOIN的话格式为:
SELECT cms_user.id,username,proName
FROM cms_user
JOIN provinces
ON cms_user.proId=provinces.id;
如果是多表(不止两个)连接
FROM 表1 JOIN 表2 ON 条件1 JOIN 表3 ON 条件2 …
外连接查询
不符合条件的值会以NULL显示
语句上与内连接相比值多添加了LEFT或RIGHT
左外连接
LEFT JOIN显示左表的全部记录及右表符合连接条件的记录。
SELECT cms_user.id,username,proName
FROM cms_user
LEFT JOIN provinces
ON cms_user.proId=provinces.id;
右外连接
RIGHT JOIN显示右表的全部记录及左表符合连接条件的记录
SELECT cms_user.id,username,proName
FROM cms_user
RIGHT provinces
ON cms_user.proId=provinces.id;
外键操作
外键是一个表的特殊字段,保持数据的一致性和完整性。被参照的表是主表(父表),外键所在字段的表为字表,父表中对记录操作时,字表中与之对应的信息也应有相应的改变。
注意:1.父表与子表需要是相同的存储引擎,不能使用临时表,只有INNODB支持外键。(那为啥不直接说只支持innodb使用该功能?)
2.外键列与参照列要是相近的数据类型
3.外键列和参照列必须创建索引
举个例子,有ABCD四个部门,有若干人在里面工作,现在有表1存储部门信息,表2存储人的信息,现在D部门被解散,删除表1中对应的部门的前提是想把表2中对应员工处理好。
创建外键
在创建表时添加外键,主键是department的id(要先创建主表department),外键是depId(这是不指定外键索引的,会自动创建)
外键还可以约束垃圾信息,如果内容不包含在主表值中,添加会报错(表1没有部门F,如果你在表2中添加部门F的员工会直接报错)
这是在创建的时候指定外键索引。
删除外键
emp_fk_dep是外键的索引
删除外键之后就可以随意增删内容了。
添加外键
添加前要注意内容一致。
给employee表添加depID的外键索引为emp_fk_dep,主键是department的id。
ALTER TABLE employee ADD CONSTRAINT emp_fk_dep FOREIGN KEY(depID) REFERENCE department(id);
关联操作
默认是不关联的,CASCADE可以同步更新或删除内容。
同步删除,对父表操作,字表也会对应操作。
同步更新,对父表操作,字表也会对应操作。
也可以将对应操作设置为NULL,但是要求原来的完整条件不能有非NULL的要求,这样父表操作时,子表对应的内容会变为NULL。
联合查询
UNION会去除重复的记录。
将employee的username与cms_user的username进行联合查询。
SELECT username FROM employee UNION SELECT username FROM cms_user;
UNION ALL简单合并,不会去除重复。
将employee的username与cms_user的username进行联合查询,不去重。
SELECT username FROM employee UNION ALL SELECT username FROM cms_user;
子查询
子查询是将一个查询语句嵌套在另一个查询语句中。内层查询语句的查询结构,可以作为外层查询语句提供条件。
相当于嵌套,先执行内部的语句,在执行外部的。(绿色的是一组,红色的是一组。绿色第二句实际上是第一句执行后的结果)
ANY或SOME的使用,对应表中的含义:
把查询结果写入表中
tbl_name这个表要提前建好才能使用!!
INSERT tbl_name(col_name,...)
SELECT (col_name,...) FROM tbl_name1;
在创建的同时插入
但字段的名字不能变,变了就无法对应了。
正则表达式
与前面的模糊匹配比较像
匹配以t开头的内容
匹配以g结尾的内容
匹配字符集合,只要含有其中的一个即可
匹配que,e字符出现1次0次或者多次
正好e出现两次
运算符
算数运算符
注意:
除数为0时,结果为NULL
对NULL运算结果始终为NULL
mysql支持数据类型转换,具体什么原理,我也不太明白。
比较运算符
用比较运算符判断username=king的行
逻辑运算符
运算符优先级
可以通过括号改变优先级顺序
数据函数库
mysql中的系统函数,使用的时候完全按照一般的编程语言写法用就可以,显示就用SELECT(相当于python的print)
数学函数
字符串函数
注意,CONCAT_WS如果用NULL进行连接,结果为NULL;如果内容中有NULL不会使结果变为NULL。
LPAD是在字符左侧填充,RPAD是右侧填充。LTRIM与RTRIM也是去除左端与右端,TRIM去掉两端。
REPLACE函数执行的时候区分大小写。STRCMP函数不区分大小写。
时间日期函数
条件判断函数
系统信息函数
加密函数
我好像用不到这种东西…
其他函数
这个锁是干嘛的啊?没讲…
索引使用
索引由数据库中一列或多列组合而成,其作用是提高对表中数据的查询速度;索引的优点是可以提高检索数据的速度;索引的缺点是创建和维护索引需要耗费时间;索引可以提高查询速度,会减慢写入速度。
索引分类
1.普通索引
2.唯一索引,不允许重复
3.全文索引,值支持字符型、英文
4.单列索引,一个字段上
5.多列索引,多个字段上
6.空间索引,字段类型为空间类型(不知道啥意思)
创建索引
创建普通索引,in_id与in_username就是索引名称,后期用来删除索引时候用的。括号内是指定索引对应的字段。(INDEX或KEY都可以创建普通索引)
创建唯一索引
UNIQUE KEY或UNIQUE
创建全文索引
创建单列索引
创建多列索引
创建空间索引,用的较少
GEOMETRY是几何类型(之前也没讲这玩意啊),而且禁止为空才能用这个空间索引,引擎必须为MyISAM
删除索引
删除tbl_name的索引名称的索引
DROP INDEX 索引名称 ON tbl_name
添加索引
普通索引不用填方括号中内容;
多列索引只要在字段名中多填几个即可;
唯一索引在方括号中选择UNIQUE;
全文索引在方括号中选择FULLTEXT;
空间索引索引在方括号中选择SPATIAL;
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名称 ON 表名(字段名称);
#或
ALTER TABLE tbl_name ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名称(字段名称);
索引应在添加数据时禁用,否则会降低效率