Mysql_以案例为基准之查询
查询数据操作
1.查询记录:
SELECT select_expr [,select_expr...] [ FROM table_references [WHERE 条件] [GROUP BU {col_name | position} [ASC | DESC ], ..分组] [HAVING 条件 对分组结果进行二次筛选] [ORDER BY {col_name|position}[ASC|DESC],...排序] [LIMIT 限制显示条数] ]
2.查询表达式
1.每一个表达式表示想要的一列,必须至少有一列,多个列之间以逗号分隔 2.*表示所有列,tbl_name.*可以表示命名表的所有列 3.模糊查询:LIKE "%" 代表0个一个或多个任意字符, "_"代表1个任意字符,(下划线) 4.查询表达式可以使用[AS]alias_name 为其赋予别名 5.GROUP BY查询结果分组 1.配合GROUP_CONCAT()得到分组详情 2.配合聚合函数: COUNT()----注意单条字段统计不统计Null值; MAX() AVG() SUM() 3.配合WITH ROLLUP记录上面所有记录的总和 6.HAVING子句: 通过HAVING子句对分组结果进行二次筛选 7.ORDER BY 排序(ASC升序,DESC升序,RAND()随机数): 通过ORDER BU 对查询结果排序,比如某宝的销量啊,价格啊等 8.LIMIT限制查询结果显示条数 1.LIMIT 显示条数 2.LIMIT 偏移量,显示条数
以上图为例进行语句讲解
2.1|2.2
SELECT cms_admin.* FROM cms_admin;不建议用*,效率低
SELECT id,username FROM cms_admin;
SELECT username,id,role FROM cms_admin;
----表来自哪个数据下db_name.tbl_name
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT id,username,role FROM cms.cms_admin;
-----字段来自于哪张表
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT cms_admin.id, cms_admin.username FROM cms.cms_admin;
---给表名起别名
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT id,username FROM cms_admin AS a;
SELECT a.id,a.username, a.email,a.role FROM cms_admin AS a;
---给字段起别名
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT id AS '编号',username AS '用户名', role '角色' FROM cms_admin;
--表名和字段名都起别名
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT a.id AS i,a.username AS u,a.email as e,a.role AS r FROM cms_admin AS a ;
---查询编号在3~10之间的用户
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT * FROM cms_user WHERE id BETWEEN 3 AND 10; SELECT * FROM cms_user WHERE id NOT BETWEEN 3 AND 10;
---查新编号为1,3,5,7,9的用户
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT * FROM cns_user WHERE id IN(1,3,5,7,20,100);
----查询proid为1 和3 的用户
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT * FROM cns_user WHERE proId IN (1,3);
---查询用户名为king,queen,zhangsan, lisi的记录
SELECT * FROM cms_user WHERE username IN('king', 'queen', 'zhangsan', 'lisi');
模糊查询
%:代表0个一个或多个任意字符
_ : 代表1个任意字符,(下划线)
查询姓张的用户
SELECT * FROM cms_user WHERE username LIKE '张%';
查询用户名中包含in的用户
SELECT * FROM cms_user WHERE username LIKE '%in%';
查询用户名为3位的用户
SELECT * FROM cms_user WHERE username LIKE '___'; SELECT * FROM cms_user WHERE username LIKE '_I%';
逻辑运算符 and or --通常用于用户登录验证
--查询用户名为king并且密码为king的用户
SELECT * FROM cmd_user WHERE user='king' and password='king';
---查询编号大于等于3的变量,年龄不为NULL 的用户
SELECT * FROM cmd_user WHERE id >=3 and age IS NOT NULL;
---查询编号大于等于3的变量,年龄不为NULL 的用户,并且proid为3
SELECT * FROM cmd_user WHERE id >=3 and age IS NOT NULL and proid =3;
---查询编号在5~10的用户并且用户名为4位的用户
SELECT * FROM cmd_user WHERE ID BETWEEN 5 AND 10 and username LIKE '____'; SELECT id,age,proid FROM cmd_user WHERE id >=3 and age IS NOT NULL and proid =3;
GROUP BY查询分组查询
group by
---按照用户所属身份分组proId
SELECT * FROM cms_user GROUP BY proId;--只会显示组中第一条记录
--向用户表中添加性别字段
ALTER TABLE cms_user ADD sex ENUM('男','女','保密'); UPDATE cmd_user SET sex='男' WHERE id IN(1,3,5,7,9);--更新数据 UPDATE cmd_user SET sex='女' WHERE id IN(2,4,6,8,10);
--按照性别分组
SELECT * FROM cms_user GROUP BY sex;
--加入条件
查询编号大于等于5的用户按照sex分组
SELECT * FROM cms_user WHERE id >=5 GROUP BY SEX;
GROUP_CONCAT()
---查询id,sex, 用户名详情按照性别分组
SELECT id,sex,GROUP_CONCAT(username) FROM cms_user GROUP BY sex;
--查询proId, 性别详情,注册时间详情,用户名详情按照proId
SELECT proId,GROUP_CONCAT(sex),GROUP_CONCAT(username),GROUP_CONCAT(register) FROM cms_user GROUP BY proId;
---查询编号,sex,用户名详情以及组中总人数按照sex分组ds
SELECT id,sex,GROUP_CONCAT(usernmae),COUNT(*) FROM cms_user GROUP BY sex;
聚合函数---GROUP BY
COUNT()----注意单条字段统计不统计Null值; MAX() AVG() SUM()
---统计表中所有记录
SELECT COUNT(*) AS totalUsers FROM cms_user;
---COUNT(字段) 单条默认不统计NULL值
SELECT COUNT(age) AS count_age FROM cms_user;
---查询编号,性别,用户名详情,组中总人数,组中最大年龄,最小年龄,
平均年龄,以及年龄总和按照性别来分组;
SELECT id,sex,GROUP_CONCAT(username),COUNT(*) AS totleUsers,MAX(age) AS max_age
,MIN(age) AS min_age,AVG(age) AS avg_age,SUM(age) AS sum_age FROM cms_user GROUP BY sex;
WITH ROLLUP记录上面所有记录的总和
SELECT id,sex,COUNT(*) AS totleUsers,MAX(age) AS max_age
,MIN(age) AS min_age FROM cms_user GROUP BY sex WITH ROLLUP;
HAVING 子句对分组进行二次筛选
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 排序(ASC升序,DESC升序,RAND()随机数)
通过ORDER BU 对查询结果排序,比如某宝的销量啊,价格啊等
SELECT * FROM cms_user ORDER BY age ASC; 降序就是把ASC 改成DESC; =---随机提取 SELECT * FROM cms_user ORDER BY RAND();
LIMIT限制查询结果显示条数(offset)
----------显示条数
显示前3行
SELECT * FROM cms_user LIMIT 3; 分页显示 SELECT * FROM cms_user LIMIT 0,5; --》注意这个5是每次加5条数据。, limit 起始数据, 加多少条 SELECT * FROM cms_user LIMIT 10,5; SELECT * FROM cms_user LIMIT 15,5;
SELECT * FROM cms_user LIMIT 0 OFFSET 10;
MYSQL 连接查询多表查询
1.什么是连接查询 连接查询是将两个或两个以上的表按某个条件连接起来,重装选取需要的数据。 连接查询是同时查询两个或两个以上的表示使用的。 当不用的表中存在相同意义的字段时,可以通过该字段连接这几个表 2.内连接查询 JOIN|CROSS JOIN |INNER JOIN 通过ON连接条件 显示两个表中符合连接条件的记录 3.外连接查询 左外连接 LEFT [OUTER] JOIN 显示左表的全部记录及右表符合连接条件的记录 右外连接 RIGHT [OUTER] JOIN 显示右表的全部记录以及左表符合条件的记录
查询cms_user, id, username
-------provinces, proName
SELECT cms_user.id, username, proName FROM cms_user,provinces;--注意如果两个表中都有的列请标明是谁的
-------cms_user的proId对应省份表中的id
SELECT cms_user.id, username, proName FROM cms_user, provinces WHERE cms_user.proid=provinces.id;
INNER JOIN
-------查询cms_user表中id,username,email, sex
查询provinces表proName
SELECT c.id,c.username,c.email,c.sex,p.proName
FROM cms_user AS c
INNER JOIN provinces AS p
ON c.proId=p.id;
----查询cms_user id,username, sex
查询provinces proName
条件是cms_user 的性别为男的用户
SELECT u.id,u.username,u.sex,p.proName FROM cms_user AS u JOIN provinces AS p ON u.proId=p.id WHERE cms_user.sex="男";
SELECT u.id,u.username,u.sex,p.proName,COUNT(*) AS totalUsers,GROUP_CONCAT(username), FROM cms_user AS u JOIN provinces AS p ON u.proId=p.id WHERE cms_user.sex="男" GROUP BY p.proName;
-------------3张表连接内连接
---cms_user id, title
---cms_cate cateName
---cms_admin username,role
SELECT u.id,u.title,c.cateName,a.username,a.role FROM cms_user AS u JOIN cms_cate AS c ON u.cId=c.id JOIN cms_admin AS a ON c.aId=a.id;
--------------------------左外连接(以左表为准,先列取左表进行匹配)
SELECT u.id,u.usernaem,u.email,u.sex,p.proName FROM cms_user AS u LEFT JOIN provinces AS p ON u.proId=p.id; ---LEFT 主要是以前面的为主
--------------------------右外连接
SELECT u.id,u.usernaem,u.email,u.sex,p.proName FROM cms_user AS u RIGHT JOIN provinces AS p ON u.proId=p.id; ---RIGHT 主要是以后面的为主
另一种多表查询, 1张主表,三张子表
SELECT
a.*, b.*, c.* ,d.*
FROM
meta_questionnaire a,
meta_questionnaire_files b,
meta_questionnaire_info c,
meta_questionnaire_users d
WHERE
a.QuesID = b.QuesID
AND a.QuesID = c.QuesID
AND a.QuesID = d.QuesID
AND a.QuesID='2017051912311291949250090258';
外键FOREIGN KEY()
1.外键是表的一个特殊字段。被参照的表示主表,外键所在字段的表为子表。 设置外键的原则需要记住,就是依赖于数据库中已存在的表的主键。外键的作用是建立该表与父表的关联关系。 父表中对记录做操作是,子表中与之对应的信息也有相应的改变。 2.外键作用保持数据的一致性和完整性 3.可以实现一对一或一对多的关系 4.注意: 1.父表和子表必须使用相同的存储引擎,而且禁止使用临时表 2.数据表的存储引擎只能为InnoDB 3.外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同;二字符的长度则可以不同 4.外键列和参照列必须创建索引。如果外键列不存在索引 的话,MySQL将自动创建索引。 5.外键约束的参照操作: 1.CASCADE:从父表删除或更新且自动删除或更新子表中匹配的行。(**级联的**) 配合ON DELETE|UPDATE 2.SET NULL:从父表删除或更新行,并设置子表中的外键列为NULL。如果使用该选项,必须保证子表列灭有指定NOT NULL:从父表删除或更新行,并设置子表中的外键列为NULL。 3.RESTRICT:拒绝对父表的删除或更新操作。 4.NO ACTION:标准SQL的关键字,在MYsql中与RESTRICT相同。 6.删除外键 ALTER TABLE employee DROP FOREIGN KEY 外键名; 7.添加外键 ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY(depId) REFERENCES 主表名; ALTER TABLE employee ADD CONSTRAINT emp_fk_dep FOREIGN KEY(depId) REFERENCES department(); 联合查询 UNION UNION ALL UNION 和UNION ALL 区别是UNION 去掉相同记录,UNION ALL 是简单的合并到一起。
--------创建部门表department(主表)
CREATE TABLE IF NOT EXISTS department( id INT UNSIGNED AUTO_INCREMENT KEY, depname VARCHAR(20) NOT NULL UNIQUE )ENGINE=INNODB;
INSERT department(id,depname) VALUES(1,'教学部'), (2,'市场部'), (3,'运营部'), (4,'督导部');
---创建员工表employee(子表)
--id,username,depid
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), ('queen',2), ('zhang',3), ('lisi',4), ('kin',2);
INSERT employee(username,depid) VALUES('k',1);
-----以上环境已经创建完毕
--查看员工所属的部门名称
SELECT e.id,e.username,d.depname
FROM employee AS e
JOIN department AS d
ON e.depid=d.id;
-----创建外键(员工表的)
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;
这样设置了外键以后再想直接删除部门就不行了,得先删除对应员工才行
同样,你想添加数据,也得对应添加才行,否则添加不成功。
-------------在建表的是时候指定外键名称
-------------在建表的是时候指定外键名称
CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depid TINYINT UNSIGNED,
CONSTRAINT emp_fk_dep FOREIGN KEY(depid) REFERENCES department(id)
)ENGINE=INNODB;
---对应删除外键
ALTER TABLE employee DROP FOREIGN KEY emp_fk_dep;
------CASCADE级联删除,主表动子表动(这个是部门表)
CREATE TABLE IF NOT EXISTS employee( id SMALLINT UNSIGNED AUTO_INCREMENT KEY, username VARCHAR(20) NOT NULL UNIQUE, depid TINYINT UNSIGNED, CONSTRAINT emp_fk_dep FOREIGN KEY(depid) REFERENCES department(id) ON DELETE CASCADE ON UPDATE CASCADE )ENGINE=INNODB;
验证: DELETE FROM department WHERE id=1;-删除部门 UPDATE department SET id=id+10; --都可以成功
------------联合查询
SELECT username FROM employee UNION SELECT username FROM cms_user;---这样写去重而(UNION ALL 则不会)
子查询---先执行内部的语句,由内向外执行
1.什么是子查询? 子查询是将一个查询语句嵌套在另一个查询语句中。内层查询语句的查询结果,可以作为外层查询语句提供条件。 2.引发子查询的情况? 1.使用[NOT]IN 的字查询 2.使用比较运算符 的子查询= > < >= <= <> != <=> 3.使用[NOT]EXISTS 的子查询 4.使用ANY|SOME或者ALL的子查询配合比较运算符 3.将查询结果写入到数据表:INSERT [INTO] tbl_name [(col_name,...)] SELECT....
---由[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 student WHERE score>=(SELECT level FROM scholarship WHERE id=1);
----EXISTS 相当于检测,如果内部为真,才会执行外部的语句
SELECT id,username FROM employee WHERE EXISTS(SELECT * FROM department WHERE id=5);这个内部为假,外部则不执行
-----使用ANY|SOME或者ALL的形式查询
-- ANY SOME ALL>=语法案例
SELECT id,username FROM student WHERE score>=ANY(SELECT level FROM scholarship); SELECT id,username FROM student WHERE score>=SOME(SELECT level FROM scholarship); SELECT id,username FROM student WHERE score>=ALL(SELECT level FROM scholarship); ---score >= 里面的最大值
-----<=语法一样
--- = 和ANY | SOME
SELECT id,username,score FROM student WHERE score=ANY(SELECT level FROM scholars);
SELECT id,username,score FROM student WHERE score IN(SELECT level FROM scholars);
---与上一条语句效果是一样的,那么NOT IN呢?
---<> != 相当于NOT IN | ALL
SELECT id,username,score FROM student WHERE score<>All(SELECT level FROM scholars);
SELECT id,username,score FROM student WHERE score NOT IN(SELECT level FROM scholars);
----------将我们的查询结果写入到另一个表中
INSERT test1(id,num)
SELECT id,score FROM student;
-----在查询一个表的同时创建一个表然后写入进去
CREATE TABLE test2(
id TINYINT UNSIGNED AUTO_INCREMENT,
num TINYINT UNSIGNED
)SELECT id,score FROM student;
注意在这里查询一下表结构和内容 desc test2;select * from test2;看看有什么不同
CREATE TABLE test2( id TINYINT UNSIGNED AUTO_INCREMENT, score TINYINT UNSIGNED )SELECT id,score FROM student;
mysqy使用正则查询
1.REGEXP '匹配方式'
2.常用匹配方式
--查询用户已t开始的用户
SELECT * FROM cms_user WHERE username REGEXP '^t';....
出处:http://www.cnblogs.com/renfanzi/
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接。