邢台网站建设好蜘蛛,百度app下载并安装最新版,网站工程前端,资阳住房和城乡建设厅网站目录
常用查询
按关键字排序
区间判断及查询不重复记录
对结果进行分组
限制结果条目
设置别名#xff08;alias —— as#xff09;
通配符
子查询
MySQL视图
NULL 值
连接查询 常用查询
#xff08;增、删、改、查#xff09; 对 MySQL 数据库的查询#xf…目录
常用查询
按关键字排序
区间判断及查询不重复记录
对结果进行分组
限制结果条目
设置别名alias —— as
通配符
子查询
MySQL视图
NULL 值
连接查询 常用查询
增、删、改、查 对 MySQL 数据库的查询除了基本的查询外有时候需要对查询的结果集进行处理。 例如只取 10 条数据、对查询结果进行排序或分组等等
按关键字排序
PS:类比于windows 任务管理器
使用 SELECT 语句可以将需要的数据从 MySQL 数据库中查询出来如果对查询的结果进行排序可以使用 ORDER BY 语句来对语句实现排序并最终将排序后的结果返回给用户。这个语句的排序不光可以针对某一个字段也可以针对多个字段
ASC|DESC
ASC 是按照升序进行排序的是默认的排序方式即 ASC 可以省略。SELECT 语句中如果没有指定具体的排序方式则默认按 ASC方式进行排序。 DESC 是按降序方式进 行排列。当然 ORDER BY 前面也可以使用 WHERE 子句对查询结果进一步过滤。
#显示一个字段或者多个字段的所有内容
SELECT “字段” FROM 表名
#distinct对字段去重查询 最好只对单个字段进行去重
SELECT DISTINCT 字段名 FROM 表名
#where 有条件查询
SELECT “字段” FROM 表名 WHERE 条件例如sales ! 100;
#and or 查看多个条件
SELECT “字段” FROM 表名 WHERE 条件例如sales ! 100or/and sales 500 ;
SELECT “字段” FROM 表名 WHERE 条件例如sales ! 100or (sales 200 and sales 500 ); #查找销售额大于1000或者大于200小于500的
#in 显示已知的值的数据记录
SELECT “字段” FROM “表名” WHERE ‘字段名’ in xxx,yyy;
SELECT “字段” FROM “表名” WHERE ‘字段名’not in xxx,yyy;
#between 显示两个值范围内的数据
SELECT 字段 FROM 表名 WHERE 字段 BETWEEN xxx and yyy ; #包含xxx和yyy的值也可以是时间 SELECT 字段 FROM 表名 WHERE DATE BETWEEN 2020-12-05 and 2020-12-08
模板表
数据库有一张info表记录了人员的id姓名分数地址和爱好
create table info (id int,name varchar(10) primary key not null ,score decimal(5,2),address varchar(20),hobbid int(5));insert into info values(1,liuyi,80,beijing,2);
insert into info values(2,wangwu,90,shengzheng,2);
insert into info values(3,lisi,60,shanghai,4);
insert into info values(4,tianqi,99,hangzhou,5);
insert into info values(5,jiaoshou,98,laowo,3);
insert into info values(6,hanmeimei,10,nanjing,3);
insert into info values(7,lilei,11,nanjing,5);mysql select * from info;
--------------------------------------------
| id | name | score | address | hobbid |
--------------------------------------------
| 6 | hanmeimei | 10.00 | nanjing | 3 |
| 5 | jiaoshou | 98.00 | laowo | 3 |
| 7 | lilei | 11.00 | nanjing | 5 |
| 3 | lisi | 60.00 | shanghai | 4 |
| 1 | liuyi | 80.00 | beijing | 2 |
| 4 | tianqi | 99.00 | hangzhou | 5 |
| 2 | wangwu | 90.00 | shengzheng | 2 |
--------------------------------------------
7 rows in set (0.00 sec)
按分数排序默认不指定是升序排列
mysql select id,name,score from info order by score;------------------------
| id | name | score |
------------------------
| 6 | hanmeimei | 10.00 |
| 7 | lilei | 11.00 |
| 3 | lisi | 60.00 |
| 1 | liuyi | 80.00 |
| 2 | wangwu | 90.00 |
| 5 | jiaoshou | 98.00 |
| 4 | tianqi | 99.00 |
------------------------
7 rows in set (0.00 sec)
分数按降序排列
mysql select id,name,score from info order by score desc;------------------------
| id | name | score |
------------------------
| 4 | tianqi | 99.00 |
| 5 | jiaoshou | 98.00 |
| 2 | wangwu | 90.00 |
| 1 | liuyi | 80.00 |
| 3 | lisi | 60.00 |
| 7 | lilei | 11.00 |
| 6 | hanmeimei | 10.00 |
------------------------
7 rows in set (0.00 sec)
order by还可以结合where进行条件过滤筛选地址是杭州的人员按分数降序排列
mysql select name,score from info where addresshangzhou order by score desc;---------------
| name | score |
---------------
| tianqi | 99.00 |
---------------
1 row in set (0.00 sec)
ORDER BY 语句也可以使用多个字段来进行排序当排序的第一个字段相同的记录有多条的情况下这些多条的记录再按照第二个字段进行排序ORDER BY 后面跟多个字段时字段之间使用英文逗号隔开优先级是按先后顺序而定 但order by 之后的第一个参数只有在出现相同值时第二个字段才有意义
① 查询人员信息先按兴趣id降序排列相同分数的id也按降序排列
mysql select id,name,hobbid from info order by hobbid desc,id desc;-------------------------
| id | name | hobbid |
-------------------------
| 7 | lilei | 5 |
| 4 | tianqi | 5 |
| 3 | lisi | 4 |
| 6 | hanmeimei | 3 |
| 5 | jiaoshou | 3 |
| 2 | wangwu | 2 |
| 1 | liuyi | 2 |
-------------------------
7 rows in set (0.00 sec)
② 查询人员信息先按兴趣id降序排列相同分数的id按升序排列
mysql select id,name,hobbid from info order by hobbid desc,id;-------------------------
| id | name | hobbid |
-------------------------
| 4 | tianqi | 5 |
| 7 | lilei | 5 |
| 3 | lisi | 4 |
| 5 | jiaoshou | 3 |
| 6 | hanmeimei | 3 |
| 1 | liuyi | 2 |
| 2 | wangwu | 2 |
-------------------------
7 rows in set (0.00 sec)
区间判断及查询不重复记录
① AND/OR ——且/或
mysql select * from info where score 70 and score 90;-----------------------------------------
| id | name | score | address | hobbid |
-----------------------------------------
| 1 | liuyi | 80.00 | beijing | 2 |
| 2 | wangwu | 90.00 | shengzheng | 2 |
-----------------------------------------
2 rows in set (0.00 sec)mysql select * from info where score 70 or score 90;--------------------------------------------
| id | name | score | address | hobbid |
--------------------------------------------
| 6 | hanmeimei | 10.00 | nanjing | 3 |
| 5 | jiaoshou | 98.00 | laowo | 3 |
| 7 | lilei | 11.00 | nanjing | 5 |
| 3 | lisi | 60.00 | shanghai | 4 |
| 1 | liuyi | 80.00 | beijing | 2 |
| 4 | tianqi | 99.00 | hangzhou | 5 |
| 2 | wangwu | 90.00 | shengzheng | 2 |
--------------------------------------------
7 rows in set (0.00 sec)#嵌套/多条件
mysql select * from info where score 70 or (score 75 and score 90);-------------------------------------------
| id | name | score | address | hobbid |
-------------------------------------------
| 5 | jiaoshou | 98.00 | laowo | 3 |
| 1 | liuyi | 80.00 | beijing | 2 |
| 4 | tianqi | 99.00 | hangzhou | 5 |
| 2 | wangwu | 90.00 | shengzheng | 2 |
-------------------------------------------
4 rows in set (0.00 sec)
添加 ② distinct 查询不重复记录 语法:
select distinct 字段 from 表名
mysql select distinct hobbid from info;--------
| hobbid |
--------
| 3 |
| 5 |
| 4 |
| 2 |
--------
4 rows in set (0.00 sec)
对结果进行分组
通过 SQL 查询出来的结果还可以对其进行分组使用 GROUP BY 语句来实现 GROUP BY 通常都是结合聚合函数一起使用的常用的聚合函数包括计数COUNT、 求和SUM、求平均数AVG、最大值MAX、最小值MINGROUP BY 分组的时候可以按一个或多个字段对结果进行分组处理。
1语法 SELECT column_name, aggregate_function(column_name)FROM table_name WHERE column_name operator value GROUP BY column_name;
按hobbid相同的分组计算相同分数的人员个数基于name个数进行计数
mysql select count(name),hobbid from info group by hobbid;---------------------
| count(name) | hobbid |
---------------------
| 2 | 2 |
| 2 | 3 |
| 1 | 4 |
| 2 | 5 |
---------------------
4 rows in set (0.00 sec)
结合where语句筛选分数大于等于80的分组计算人员个数
mysql select count(name),hobbid from info where score80 group by hobbid;---------------------
| count(name) | hobbid |
---------------------
| 2 | 2 |
| 1 | 3 |
| 1 | 5 |
---------------------
3 rows in set (0.00 sec)
全部人员成绩表
count(name)计数
score 分数
score80 优秀
score 60 and score 80 :优-
结合order by把计算出的人员个数按升序排列
mysql select count(name),score,hobbid from info where score80 group by hobbid order by count(name) asc;----------------------------
| count(name) | score | hobbid |
----------------------------
| 1 | 98.00 | 3 |
| 1 | 99.00 | 5 |
| 2 | 80.00 | 2 |
----------------------------
3 rows in set (0.00 sec)
限制结果条目
limit 限制输出的结果记录 在使用 MySQL SELECT 语句进行查询时结果集返回的是所有匹配的记录行。有时候仅 需要返回第一行或者前几行这时候就需要用到 LIMIT 子句
1语法
SELECT column1, column2, ... FROM table_name LIMIT [offset,] number
LIMIT 的第一个参数是位置偏移量可选参数是设置 MySQL 从哪一行开始显示。 如果不设定第一个参数将会从表中的第一条记录开始显示。需要注意的是第一条记录的 位置偏移量是 0第二条是 1以此类推。第二个参数是设置返回记录行的最大数目。
查询所有信息显示前4行记录
mysql select * from info limit 3;-----------------------------------------
| id | name | score | address | hobbid |
-----------------------------------------
| 6 | hanmeimei | 10.00 | nanjing | 3 |
| 5 | jiaoshou | 98.00 | laowo | 3 |
| 7 | lilei | 11.00 | nanjing | 5 |
-----------------------------------------
3 rows in set (0.00 sec)
从第4行开始往后显示3行内容
mysql select * from info limit 3,3;---------------------------------------
| id | name | score | address | hobbid |
---------------------------------------
| 3 | lisi | 60.00 | shanghai | 4 |
| 1 | liuyi | 80.00 | beijing | 2 |
| 4 | tianqi | 99.00 | hangzhou | 5 |
---------------------------------------
3 rows in set (0.00 sec)
结合order by语句按id的大小升序排列显示前三行
mysql select id,name from info order by id limit 3;--------------
| id | name |
--------------
| 1 | liuyi |
| 2 | wangwu |
| 3 | lisi |
--------------
3 rows in set (0.00 sec)
#基础select 小的升阶 怎么输出最后三行
mysql select id,name from info order by id desc limit 3;-----------------
| id | name |
-----------------
| 7 | lilei |
| 6 | hanmeimei |
| 5 | jiaoshou |
-----------------
3 rows in set (0.00 sec)设置别名alias —— as
在 MySQL 查询时当表的名字比较长或者表内某些字段比较长时为了方便书写或者 多次使用相同的表可以给字段列或表设置别名。使用的时候直接使用别名简洁明了增强可读性
1语法
对于列的别名SELECT column_name AS alias_name FROM table_name;
对于表的别名SELECT column_name(s) FROM table_name AS alias_name;
在使用 AS 后可以用 alias_name 代替 table_name其中 AS 语句是可选的。AS 之后的别名主要是为表内的列或者表提供临时的名称在查询过程中使用库内实际的表名 或字段名是不会被改变的
列别名设置示例
select name as 姓名,score as 成绩 from info;
如果表的长度比较长可以使用 AS 给表设置别名在查询的过程中直接使用别名 临时设置info的别名为i
select i.name as 姓名,i.score as 成绩 from info as i;
查询info表的字段数量以number显示
mysql select count(*) as number from info;--------
| number |
--------
| 7 |
--------
1 row in set (0.00 sec)
不用as也可以一样显示
mysql select count(*) number from info;--------
| number |
--------
| 7 |
--------
1 row in set (0.00 sec)使用场景
1、对复杂的表进行查询的时候别名可以缩短查询语句的长度
2、多表相连查询的时候通俗易懂、减短sql语句
此外AS 还可以作为连接语句的操作符。
创建t1表将info表的查询记录全部插入t1表
mysql create table t1 as select * from info;mysql create table t1 as select * from info;
Query OK, 7 rows affected (0.02 sec)
Records: 7 Duplicates: 0 Warnings: 0mysql select * from t1;--------------------------------------------
| id | name | score | address | hobbid |
--------------------------------------------
| 6 | hanmeimei | 10.00 | nanjing | 3 |
| 5 | jiaoshou | 98.00 | laowo | 3 |
| 7 | lilei | 11.00 | nanjing | 5 |
| 3 | lisi | 60.00 | shanghai | 4 |
| 1 | liuyi | 80.00 | beijing | 2 |
| 4 | tianqi | 99.00 | hangzhou | 5 |
| 2 | wangwu | 90.00 | shengzheng | 2 |
--------------------------------------------
7 rows in set (0.00 sec)
#此处AS起到的作用1、创建了一个新表t1 并定义表结构插入表数据与info表相同2、但是”约束“没有被完全”复制“过来 #但是如果原表设置了主键那么附表的default字段会默认设置一个0相似 克隆、复制表结构create table t1 (select * from info);#也可以加入where 语句判断create table test1 as select * from info where score 60;在为表设置别名时要保证别名不能与数据库中的其他表的名称冲突。 列的别名是在结果中有显示的而表的别名在结果中没有显示只在执行查询时使用。
通配符
通配符主要用于替换字符串中的部分字符通过部分字符的匹配将相关结果查询出来。
通常通配符都是跟 LIKE 一起使用的并协同 WHERE 子句共同来完成查询任务。常用的通配符有两个分别是
%百分号表示零个、一个或者多个字符
_: 下划线表示单个字符
A-Z所有以‘A’起头另一个任何值的字符且以‘Z’为结尾的字符串。例如‘ABZ’和‘A2Z’
‘ABC%’所有以ABC开头的字符串
‘%XYZ’所有以XYZ结尾的字符串
‘%ABC%’所有含有ABC字符的的字符串
‘_AN%’所有第二个字母为A第三个字母为N的字符串例如‘SAN FRANCISCO’
SELECT STORE_NAME,DATE,SALES FROM STORE_INFO WHERE STORE_NAME LIKE LO%;
子查询
子查询也被称作内查询或者嵌套查询是指在一个查询语句里面还嵌套着另一个查询语 句。子查询语句是先于主查询语句被执行的其结果作为外层的条件返回给主查询进行下一 步的查询过滤。
PS: 子语句可以与主语句所查询的表相同也可以是不同表
select name,score from info where id in (select id from info where score 80);
以上同表示例
主语句select name,score from info where id
子语句(集合) select id from info where score 80
PS:子语句中的sql语句是为了最后过滤出一个结果集用于主语句的判断条件
in: 将主表和子表关联/连接的语法
不同表/多表示例
mysql create table ky30(id int);
Query OK, 0 rows affected (0.01 sec)mysql insert into ky30 values(1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0mysql select id,name,score from info where id in (select * from ky30);
---------------------
| id | name | score |
---------------------
| 3 | lisi | 60.00 |
| 1 | liuyi | 80.00 |
| 2 | wangwu | 90.00 |
---------------------
3 rows in set (0.01 sec)
子查询不仅可以在 SELECT 语句中使用在 INERT、UPDATE、DELETE 中也同样适用。在嵌套的时候子查询内部还可以再次嵌套新的子查询也就是说可以多层嵌套。
1语法
IN 用来判断某个值是否在给定的结果集中通常结合子查询来使用
语法 表达式 [NOT] IN 子查询
当表达式与子查询返回的结果集中的某个值相等时返回 TRUE否则返回 FALSE。 若启用了 NOT 关键字则返回值相反。需要注意的是子查询只能返回一列数据如果需 求比较复杂一列解决不了问题可以使用多层嵌套的方式来应对。 多数情况下子查询都是与 SELECT 语句一起使用的
查询分数大于80的记录
mysql select name,score from info where id in (select id from info where score80);-----------------
| name | score |
-----------------
| jiaoshou | 98.00 |
| tianqi | 99.00 |
| wangwu | 90.00 |
-----------------
3 rows in set (0.00 sec)子查询还可以用在 INSERT 语句中。子查询的结果集可以通过 INSERT 语句插入到其 他的表中
将t1里的记录全部删除重新插入info表的记录
mysql insert into t1 select * from info where id in (select id from info);
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0mysql select * from t1;
--------------------------------------------
| id | name | score | address | hobbid |
--------------------------------------------
| 6 | hanmeimei | 10.00 | nanjing | 3 |
| 5 | jiaoshou | 98.00 | laowo | 3 |
| 7 | lilei | 11.00 | nanjing | 5 |
| 3 | lisi | 60.00 | shanghai | 4 |
| 1 | liuyi | 80.00 | beijing | 2 |
| 4 | tianqi | 99.00 | hangzhou | 5 |
| 2 | wangwu | 90.00 | shengzheng | 2 |
| 6 | hanmeimei | 10.00 | nanjing | 3 |
| 5 | jiaoshou | 98.00 | laowo | 3 |
| 7 | lilei | 11.00 | nanjing | 5 |
| 3 | lisi | 60.00 | shanghai | 4 |
| 1 | liuyi | 80.00 | beijing | 2 |
| 4 | tianqi | 99.00 | hangzhou | 5 |
| 2 | wangwu | 90.00 | shengzheng | 2 |
--------------------------------------------
14 rows in set (0.00 sec)
UPDATE 语句也可以使用子查询。UPDATE 内的子查询在 set 更新内容时可以是单独的一列也可以是多列。
将wangwu的分数改为50
mysql update info set score50 where id in (select * from ky30 where id2);
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql select * from info;
--------------------------------------------
| id | name | score | address | hobbid |
--------------------------------------------
| 6 | hanmeimei | 10.00 | nanjing | 3 |
| 5 | jiaoshou | 98.00 | laowo | 3 |
| 7 | lilei | 11.00 | nanjing | 5 |
| 3 | lisi | 60.00 | shanghai | 4 |
| 1 | liuyi | 80.00 | beijing | 2 |
| 4 | tianqi | 99.00 | hangzhou | 5 |
| 2 | wangwu | 50.00 | shengzheng | 2 |
--------------------------------------------
7 rows in set (0.00 sec)
update info set score100 where id not in (select * from member where id 1);
表示 先匹配出member表内的id字段为基础匹配的结果集2,3) 然后再执行主语句以主语句的id 为基础 进行where 条件判断/过滤
DELETE 也适用于子查询
删除分数大于80的记录
mysql delete from info where id in (select id where score80);
Query OK, 2 rows affected (0.00 sec)mysql select id,name,score from t1;
------------------------
| id | name | score |
------------------------
| 6 | hanmeimei | 10.00 |
| 5 | jiaoshou | 98.00 |
| 7 | lilei | 11.00 |
| 3 | lisi | 60.00 |
| 1 | liuyi | 80.00 |
| 4 | tianqi | 99.00 |
| 2 | wangwu | 90.00 |
| 6 | hanmeimei | 10.00 |
| 5 | jiaoshou | 98.00 |
| 7 | lilei | 11.00 |
| 3 | lisi | 60.00 |
| 1 | liuyi | 80.00 |
| 4 | tianqi | 99.00 |
| 2 | wangwu | 90.00 |
------------------------
14 rows in set (0.00 sec)
在 IN 前面还可以添加 NOT其作用与IN相反表示否定即不在子查询的结果集里面
删除分数不是大于等于80的记录
mysql delete from t1 where id not in (select id where score80);
Query OK, 6 rows affected (0.00 sec)mysql select id,name,score from t1;
-----------------------
| id | name | score |
-----------------------
| 5 | jiaoshou | 98.00 |
| 1 | liuyi | 80.00 |
| 4 | tianqi | 99.00 |
| 2 | wangwu | 90.00 |
| 5 | jiaoshou | 98.00 |
| 1 | liuyi | 80.00 |
| 4 | tianqi | 99.00 |
| 2 | wangwu | 90.00 |
-----------------------
8 rows in set (0.00 sec)EXISTS 这个关键字在子查询时主要用于判断子查询的结果集是否为空。如果不为空 则返回 TRUE反之则返回 FALSE
子查询别名as
#查询info表id,name 字段
select id,name from info;
以上命令可以查看到info表的内容
#将结果集做为一张表进行查询的时候我们也需要用到别名示例
需求从info表中的id和name字段的内容做为内容 输出id的部分
mysql select id from (select id,name from info);
ERROR 1248 (42000): Every derived table must have its own alias
#此时会报错原因为
select * from 表名 此为标准格式而以上的查询语句表名的位置其实是一个完整结果集mysql并不能直接识别而此时给与结果集设置一个别名以”select a.id from a“的方式查询将此结果集视为一张表就可以正常查询数据了如下
select a.id from (select id,name from info) a;
相当于
select info.id,name from info;
select 表.字段字段 from 表
MySQL视图
视图优化操作安全方案
数据库中的虚拟表这张虚拟表中不包含真实数据只是做了真实数据的映射
视图可以理解为镜花水月/倒影动态保存结果集数据
基础表info 7行记录 映射投影--视图
作用场景[图]:
针对不同的人权限身份提供不同结果集的“表”以表格的形式展示
作用范围
select * from info; #展示的部分是info表
select * from view_name; #展示的一张或多张表
功能
简化查询结果集、灵活查询、可以针对不同用户呈现不同结果集、相对有更高的安全性 本质而言视图是一种select(结果集的呈现)
PS:视图适合于多表连接浏览时使用!不适合增、删、改
而存储过程适合于使用较频繁的SQL语句这样可以提高执行效率!
##视图和表的区别和联系#区别
①、视图是已经编译好的sql语句。而表不是②、视图没有实际的物理记录。而表有。
show table status\G③、表只用物理空间而视图不占用物理空间视图只是逻辑概念的存在表可以及时对它进行修改但视图只能有创建的语句来修改④、视图是查看数据表的一种方法可以查询数据表中某些字段构成的数据只是一些SQL语句的集合。从安全的角度说视图可以不给用户接触数据表从而不知道表结构。⑤、表属于全局模式中的表是实表视图属于局部模式的表是虚表。⑥、视图的建立和删除只影响视图本身不影响对应的基本表。但是更新视图数据是会影响到基本表的#联系
视图(view)是在基本表之上建立的表它的结构(即所定义的列)和内容(即所有数据行)都来自基本表它依据基本表存在而存在。一个视图可以对应一个基本表也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系。示例
需求满足80分的学生展示在视图中
PS这个结果会动态变化同时可以给不同的人群例如权限范围展示不同的视图#创建视图(单表)
mysql create view v_score as select * from ky29 where score80;
Query OK, 0 rows affected (0.01 sec)#查看表状态
show table status\G#查看视图
mysql select * from v_score;
-------------------------------------------
| id | name | score | address | hobbid |
-------------------------------------------
| 1 | liuyi | 100.00 | beijing | 2 |
| 4 | tianqi | 100.00 | hangzhou | 5 |
| 5 | jiaoshou | 100.00 | laowo | 3 |
| 6 | hanmeimei | 100.00 | nanjing | 3 |
| 7 | lilei | 100.00 | nanjing | 5 |
-------------------------------------------#查看视图与源表结构
mysql desc v_score;
--------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
--------------------------------------------------
| id | int(11) | YES | | NULL | |
| name | varchar(10) | NO | | NULL | |
| score | decimal(5,2) | YES | | NULL | |
| address | varchar(20) | YES | | NULL | |
| hobbid | int(5) | YES | | NULL | |
--------------------------------------------------
5 rows in set (0.01 sec)mysql desc ky29;
--------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
--------------------------------------------------
| id | int(11) | YES | | NULL | |
| name | varchar(10) | NO | PRI | NULL | |
| score | decimal(5,2) | YES | | NULL | |
| address | varchar(20) | YES | | NULL | |
| hobbid | int(5) | YES | | NULL | |
--------------------------------------------------
5 rows in set (0.00 sec)
#多表创建视图
创建test01表
create table test01 (id int,name varchar(10),age char(10));
insert into test01 values(1,zhangsan,20);
insert into test01 values(2,lisi,30);
insert into test01 values(3,wangwu,29);需求需要创建一个视图需要输出id、学生姓名、分数以及年龄mysql create view v_info(id,name,score,age) as select info.id,info.name,info.score,test01.age from info,test01 where info.nametest01.name;
Query OK, 0 rows affected (0.00 sec)mysql select * from v_info;
---------------------------
| id | name | score | age |
---------------------------
| 3 | lisi | 60.00 | 30 |
| 2 | wangwu | 90.00 | 29 |
---------------------------
2 rows in set (0.00 sec)
#修改原表数据
mysql update info set score60 where nameliuyi;
Query OK, 1 row affected (1.62 sec)
Rows matched: 1 Changed: 1 Warnings: 0#查看视图
mysql select * from v_score;
-------------------------------------------
| id | name | score | address | hobbid |
-------------------------------------------
| 4 | tianqi | 100.00 | hangzhou | 5 |
| 5 | jiaoshou | 100.00 | laowo | 3 |
| 6 | hanmeimei | 100.00 | nanjing | 3 |
| 7 | lilei | 100.00 | nanjing | 5 |
-------------------------------------------
4 rows in set (0.00 sec)
#同时可以通过视图修改原表
mysql update v_score set score120 where nametianqi;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql select * from v_score;
-------------------------------------------
| id | name | score | address | hobbid |
-------------------------------------------
| 4 | tianqi | 120.00 | hangzhou | 5 |
| 5 | jiaoshou | 100.00 | laowo | 3 |
| 6 | hanmeimei | 100.00 | nanjing | 3 |
| 7 | lilei | 100.00 | nanjing | 5 |
-------------------------------------------
4 rows in set (0.00 sec)mysql select * from info;
---------------------------------------------
| id | name | score | address | hobbid |
---------------------------------------------
| 1 | liuyi | 60.00 | beijing | 2 |
| 2 | wangwu | 50.00 | shengzheng | 2 |
| 3 | lisi | 50.00 | shanghai | 4 |
| 4 | tianqi | 120.00 | hangzhou | 5 |
| 5 | jiaoshou | 100.00 | laowo | 3 |
| 6 | hanmeimei | 100.00 | nanjing | 3 |
| 7 | lilei | 100.00 | nanjing | 5 |
---------------------------------------------
7 rows in set (0.00 sec)修改表不能修改以函数、复合函数方式计算出来的字段
查询方便、安全性
查询方便索引速度快、同时可以多表查询更为迅速视图不保存真实数据视图本质类似select
安全性我们实现登陆的账户是root ——》所拥有权限 视图无法显示完整的约束
NULL 值
在 SQL 语句使用过程中经常会碰到 NULL 这几个字符。通常使用 NULL 来表示缺失 的值也就是在表中该字段是没有值的。如果在创建表时限制某些字段不为空则可以使用 NOT NULL 关键字不使用则默认可以为空。在向表内插入记录或者更新记录时如果该字段没有 NOT NULL 并且没有值这时候新记录的该字段将被保存为 NULL。需要注意 的是NULL 值与数字 0 或者空白spaces的字段是不同的值为 NULL 的字段是没有 值的。在 SQL 语句中使用 IS NULL 可以判断表内的某个字段是不是 NULL 值相反的用 IS NOT NULL 可以判断不是 NULL 值。
查询info表结构name字段是不允许空值的
null值与空值的区别(空气与真空)
空值长度为0不占空间NULL值的长度为null占用空间
is null无法判断空值
空值使用“或者”来处理
count计算时NULL会忽略空值会加入计算mysql desc info;
-----------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-----------------------------------------------------------
| id | int(3) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | UNI | NULL | |
| score | decimal(5,2) | YES | | NULL | |
| address | varchar(50) | YES | | 未知 | |
| hobbid | int(3) | YES | | NULL | |
-----------------------------------------------------------
5 rows in set (0.00 sec)插入一条记录分数字段输入null显示出来就是null#验证
alter table info add column addr varchar(50);update info set addrnj where score 70;#统计数量检测null是否会加入统计中
select count(addr) from info;#将info表中其中一条数据修改为空值
update info set addr where namewangwu;#统计数量检测空值是不会被添加到统计中
select count(addr) from info;#查询null值
mysql select * from info where addr is NULL;
-----------------------------------------------
| id | name | score | address | hobbid | addr |
-----------------------------------------------
| 6 | hanmeimei | 10.00 | nanjing | 3 | NULL |
| 7 | lilei | 11.00 | nanjing | 5 | NULL |
-----------------------------------------------
2 rows in set (0.00 sec)#查询不为空的值
ysql select * from info where addr is not null;
-------------------------------------------------
| id | name | score | address | hobbid | addr |
-------------------------------------------------
| 1 | liuyi | 80.00 | beijing | 2 | nj |
| 2 | wangwu | 90.00 | shengzheng | 2 | nj |
| 3 | lisi | 60.00 | shanghai | 4 | |
| 4 | tianqi | 99.00 | hangzhou | 5 | nj |
| 5 | jiaoshou | 98.00 | laowo | 3 | nj |
| 1 | xiaoer | 80.00 | hangzhou | 3 | nj |
-------------------------------------------------
6 rows in set (0.00 sec)
连接查询
MySQL 的连接查询通常都是将来自两个或多个表的记录行结合起来基于这些表之间的 共同字段进行数据的拼接。首先要确定一个主表作为结果集然后将其他表的行有选择 性的连接到选定的主表结果集上。使用较多的连接查询包括内连接、左连接和右连接
模板:
create table test1 (
a_id int(11) default null,
a_name varchar(32) default null,
a_level int(11) default null);create table test2 (
b_id int(11) default null,
b_name varchar(32) default null,
b_level int(11) default null);insert into test1 values (1,aaaa,10);
insert into test1 values (2,bbbb,20);
insert into test1 values (3,cccc,30);
insert into test1 values (4,dddd,40);insert into test2 values (2,bbbb,20);
insert into test2 values (3,cccc,30);
insert into test2 values (5,eeee,50);
insert into test2 values (6,ffff,60);
1、内连接
MySQL 中的内连接就是两张或多张表中同时符合某种条件的数据记录的组合。通常在 FROM 子句中使用关键字 INNER JOIN 来连接多张表并使用 ON 子句设置连接条件内连接是系统默认的表连接所以在 FROM 子句后可以省略 INNER 关键字只使用 关键字 JOIN。同时有多个表时也可以连续使用 INNER JOIN 来实现多表的内连接不过为了更好的性能建议最好不要超过三个表
1语法 SELECT column_name(s)FROM table1 INNER JOIN table2 ON table1.column_name table2.column_name;
模板表
create table infos(name varchar(40),score decimal(4,2),address varchar(40));insert into infos values(wangwu,80,beijing),(zhangsan,99,shanghai),(lisi,100,nanjing);mysql select * from infos;
---------------------------
| name | score | address |
---------------------------
| wangwu | 80.00 | beijing |
| zhangsan | 99.00 | shanghai |
| lisi | 99.99 | nanjing |
---------------------------mysql select info.id,info.name from info inner join infos on info.nameinfos.name;
--------------
| id | name |
--------------
| 2 | wangwu |
| 3 | lisi |
--------------
2 rows in set (0.00 sec)内连查询通过inner join 的方式将两张表指定的相同字段的记录行输出出来
内连查询面试直接了当的说 用inner join 就可以
2、左连接
左连接也可以被称为左外连接在 FROM 子句中使用 LEFT JOIN 或者 LEFT OUTER JOIN 关键字来表示。左连接以左侧表为基础表接收左表的所有行并用这些行与右侧参 考表中的记录进行匹配也就是说匹配左表中的所有行以及右表中符合条件的行。
mysql select * from info left join infos on info.nameinfos.name;
---------------------------------------------------------------------------
| id | name | score | address | hobbid | addr | name | score | address |
---------------------------------------------------------------------------
| 2 | wangwu | 50.00 | shengzheng | 2 | nj | wangwu | 80.00 | beijing |
| 3 | lisi | 50.00 | shanghai | 4 | nj | lisi | 99.99 | nanjing |
| 1 | liuyi | 60.00 | beijing | 2 | nj | NULL | NULL | NULL |
| 4 | tianqi | 100.00 | hangzhou | 5 | | NULL | NULL | NULL |
| 5 | jiaoshou | 100.00 | laowo | 3 | NULL | NULL | NULL | NULL |
| 6 | hanmeimei | 100.00 | nanjing | 3 | NULL | NULL | NULL | NULL |
| 7 | lilei | 100.00 | nanjing | 5 | NULL | NULL | NULL | NULL |
| 7 | lilei | 100.00 | nanjing | 5 | NULL | NULL | NULL | NULL |
| 8 | abn | 81.00 | bj | 1 | nj | NULL | NULL | NULL |
---------------------------------------------------------------------------左连接中左表的记录将会全部表示出来而右表只会显示符合搜索条件的记录右表记录不足的地方均为 NULL。
3、右连接
右连接也被称为右外连接在 FROM 子句中使用 RIGHT JOIN 或者 RIGHT OUTER JOIN 关键字来表示。右连接跟左连接正好相反它是以右表为基础表用于接收右表中的所有行并用这些记录与左表中的行进行匹配
mysql select * from info right join infos on info.nameinfos.name;
--------------------------------------------------------------------------
| id | name | score | address | hobbid | addr | name | score | address |
--------------------------------------------------------------------------
| 2 | wangwu | 50.00 | shengzheng | 2 | nj | wangwu | 80.00 | beijing |
| NULL | NULL | NULL | NULL | NULL | NULL | zhangsan | 99.00 | shanghai |
| 3 | lisi | 50.00 | shanghai | 4 | nj | lisi | 99.99 | nanjing |
--------------------------------------------------------------------------
3 rows in set (0.00 sec)在右连接的查询结果集中除了符合匹配规则的行外还包括右表中有但是左表中不匹 配的行这些记录在左表中以 NULL 补足
----------ORDER BY -----------按关键排序
SELECT “字段” FROM “表名” [WHERE 条件] ORDER BY ‘字段’[ASC,DESC]
#ASC 是按照升序进行排序的默认排序方式就是升序
#DESC 是按照降序进行排序的
--------------------函数-------------------------
数学函数
abs(x) :返回x的绝对值
rand():返回0到1的随机数
modx,y返回x除以y以后的余数
powerx,y返回x的y次方
round(x):返回离x最近的整数
♢round(x,y)保留x的y位小数四舍五入后的值
sqrt(x)返回x的平方根
♢truncate(x,y)返回数字x截断为y位小数的值
ceil(x)返回大于或等于x的最小整数
floor(x)返回小于或等于x的最大整数
♢greatest(x1,x2,x3...)返回集合中最大的值也可以返回多个字段的最大的值
♢least(x1,x2,x3...):返回集合中最小的值也可以返回多个字段的最小的值
聚合函数
♢avg返回指定列的平均值
♢count返回指定列中非NULL值的个数
♢min返回指定列的最小值
♢max返回指定列的最大值
♢sumx返回指定列的所有值纸盒
♢count*所有字段行数不忽略NULL行
字符串函数
trim返回去除指定格式的值
♢ concatx,y将提供的参数x和y拼接成一个字符串
substrx,y获取从字符串x中第y个位置开始的字符串跟substring函数作用相同
♢ substr(x,y,z)获取从字符串x中第y个位置开始长度为z的字符串
♢ lengthx返回字符串x的长度
♢ replacex,y,z将字符串z代替字符串x中的字符串y
upperx将字符串x的所有字母变成大写字母
leftx,y返回字符串x前y个字符
rightx,y返回字符串x后y个字符
repeat(x,y)将字符串x重复y次
spacex返回x个空格
srcmpx,y比较x和y返回的值可以为-1,0,1
reverse(x)将字符串x反转
字符串分片 select substr(123456789,4,3); 为456第四个开始后面三个
select trim leading xx from xx yy; 从xx yy中移除xx
select trim trailing yy from xx yy; 从xx yy中移除yy
--------------GROUP BY ---------------------
group by 有一个原则凡是在group by后面出现的字段必须在select后面出现
凡是在select后面出现的且未在聚合函数中出现的字段必须出现在group by后面
SELECT STORE_NAME,SUM(SALES) FROM STORE_INFO GROUP BY STORE_NAME ORDER BY SALES DESC;
#分组也有去重功能
#group by分组之后不再能使用where语句进行过滤
----------------HAVING------------------------
通常和group by一起使用对group by语句之后再进行过滤
select store_name,sum(sales) from store_info where sales 2000 group by store_name having sum(sales) 1000;
顺序先sales小于2000然后进行分组然后对sales进行求和最后having对求和大于1000进行过滤
--------------别名---------------------
select region,replace(region,st,stion) as new_region,store_name from location as A;
原字段 as 别名 可以空格代替as
-----------子查询-------------------
连接表格在where子句或having子句中插入另一个SQL语句
select “字段1”FROM “表格1” WHERE “字段2” [比较运算符]like in between #外查询
(select 字段1 FROM“表格2” WHERE “条件”);
#内查询
select sum(sales) from store_info where store_name in (select store_name from location where regionwest); #求西部城市的销售总额
where exists 测试内查询是否有结果有结果的话就会执行外查询并且返回外查询执行结果
----------------连接查询----------------------
inner join(内连接)只返回两个表中联结字段相等的行
left join(左连接)返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右连接)返回包括右表中的所有记录和坐标中联结字段相等的记录
select * from location A inner join store_info B on A.store_nameB.store_name;
-----------------UNION---------------联集---
将两个SQL语句合并起来两个SQL语句所产生的字段需要是同样的数据记录种类
UNION生成结果的数据记录值将没有重复自动排序
UNION ALL将生成结果的记录值全合并不管是否有重复也不排序 派生表别名
select A.store_name ,count(A.store_name) from (select distinct store_name from location union all select distinct store_name from store_info) as A group by A.store_name having count(A.store_name) 1; #求无交集
