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

SELECT 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 a.id,a.username, a.email,a.role FROM  cms_admin AS a;

 ---给字段起别名

 SELECT id AS '编号',username 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 ;

 ---查询编号在3~10之间的用户

 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的用户

SELECT * FROM  cns_user WHERE id IN(1,3,5,7,20,100);

----查询proid为1 和3 的用户

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';....