接上。。。点击打开链接
一、 DQL的操作语句(数据库查询语句,表中的数据不会发生变化)
语法:
SELECT
selection_list /1:要查询的字段的名称(可以查询单个、多个列、所以列*)
FROM
table_list /2:要查询的表名称
WHERE
condition /3:过滤数据--条件查询
GROUP BY
grouping_columns /4:排序检索数据--对查询的结果分组
HAVING
condition /5:读分组的结果进行过滤
ORDER BY
sorting_columns /6:对结果进行排序(asc--默认升序、desc--降序)
LIMIT /7:分页查询
offset_start, row_count /8:结果限定(分页查询)
补充:
需求1:如何不想要每个值每次都出现,可以在查询的字段前使用distinct关键字(去除重复)。
需求2:可能表比较多,以前一直通过列名引用列,现在可以使用全限定名来引用列(同时使用表名和字段名),以免混淆。
需求3:经常需要按不只一个列进行数据排序,可以指定多个列对结果进行排序(主次之分),列名以逗号隔开。
条件查询:where 子句(字段后面)
(1)=、!=、<>(不等于)、<、<=、>、>=;
(2)BETWEEN…AND;在什么范围之间(举例:100<a<200)
(3)IN(值1,值2。。。);
(4)IS NULL(为空):检查null值的的列
(5)IS NOT NULL 不为空
(6)AND(并且)、OR(或者)、NOT(非)
(7)LIKE :常与%(任意字符),_(一个字符)
注意1:查询时null值的情况,where price=null(错误)与where price is null(正确)。
注意2:IN操作符的用法--where 字段名称 IN(值1,值2。。。)
注意3:NOT操作符--否定它之后跟的任何条件(除了什么以外),例如:where 字段名称 NOT IN(值1,值2。。。)。
注意4:如果要在搜索子句中使用通配符(搜索模式),必须使用LIKE操作符,注意搜索是分大小写的。
例如:where 字段名称 like ‘搜索模式(*--任意;_任意一个)’
---------------------------------
二、聚合函数
分类:平均(AVG)、计数(COUNT)、最大最小值、总和
讨论一些细节性的问题:
(1)COUNT函数的细节
count(*):不管列表中中是否包含NULL值,也算上。
count(字段名称):忽略NULL值
(2)ifnull(column,0)--把null值也作为运算,如果是null就转化为0。
---------------------------------------------------
三、约束
分类:非空约束(not null)、唯一约束(unique)、主键约束(primary key)、外键约束(foreign key)、自增长约束
细节:主键约束=非空约束+唯一约束,但是主键约束在一张表中只能有一个,而"非空约束+唯一约束"的形式可以有多个。
主键约束的方式
(1)建表的同时添加约束(常用)
create table pk01(
id int primary key, -- 在当前字段的后面添加约束说明
username varchar(20)
);
(2)建表的同时在约束区域添加约束(常用)
约束区域:所有的字段声明完成之后,就是约束区域了
create table pk01(
id int,
username varchar(20),
primary key (id)-- 约束区域
);
(3)建表之后,通过修改表结构添加约束(未想好在哪添加主键时)
create table pk02(
id int,
username varchar(20)
);
--注意修改时机
alter table pk02 add primary key(id);
注意:修改表结构的方式要在未插入数据前修改
唯一约束(方式同上)
特点:被修饰过的字段唯一,对null不起作用
补充:添加联合唯一
alter table 表名 add unique(字段1,字段2);-- 添加的联合唯一
特点:两个值相同约束才生效
外键约束(三种方式)
-- 语法:alter table 多表名称 add foreign key(外键名称) references 一表名称(主键);
alter table orders add foreign key(user_id) references user(id);
外键约束目的:为了保证数据的有效性和完整性。
------------------------------
主表和附表的理解:
过程:主表设置主键(一般是id),附表设置外键关联主键(注意:两种关联方式)
语法(外部方式):alter 显示的字段 table from 主表,附表 where 主表的字段=附表的字段 AND 。。。
主键--用来保证数据完整性;外键--用来和其他表建立联系用的
主表:不能删除与从表中有关联的数据(以免从表中的数据没有意义)
附表:不能添加主表中没有存在的数据(无意义)。
需求:如果添加了外键后想删除主表中的数据
方式1: ALTER TABLE orders ADD FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE;
--级联删除(慎用)然后你就可以删除主表中的数据了
方式2:先把带有外键的多表的数据删除,再删除一表中的数据(常用)
补充:ER图可以描述实体于实体之间的关系
(1)实体用矩形表示
(2)属性用椭圆表示
(3)关系用菱形表示
------------------------------
四、内连接和外连接
内连接★
语法:
格式1:显式的内连接
select a.*,b.* from a [inner] join b on ab的连接条件
格式2:隐式的内连接
select a.*,b.* from a,b where ab的连接条件
外连接★
左外连接:★
select a.*,b.* from a left [outer] join b on 连接条件; outer 可以不写
意思:先展示join左边的(a)表的所有数据,根据条件关联查询join右边的表(b),符合条件则展示出来,不符合以null值展示.
右外连接:★
select a.*,b.* from b right [outer] join a on 连接条件; outer 可以不写
意思:先展示join右边的表(a)表的所有数据,根据条件关联查询join左边的表(b),符合条件则展示出来,不符合以null值展示.
练习
--查询用户的订单,没有订单的用户不显示
隐式内连接:
select user.*,orders.* from user ,orders where user.id=orders.user_id;
显示内连接
select user.*,orders.* from user join orders on user.id=orders.user_id;
查询所有用户的订单详情
左外连接: user 在左
select user.*,orders.* from user left join orders on user.id=orders.user_id;
查询所有订单的用户详情
右外连接:orders在右
select orders.*,user.* from user right join orders on user.id=orders.user_id;
五、分页查询:limit
语法
字段 limit 0,5 -- 参数1:开始的记录索引;参数2:每一页显示的条数(索引从0开始)
--算法:开始的记录索引 = (页码-1)*每一页显示的条数
需求:显示前三页的数据(每页5条记录)
select * from student limit 0,5 ; 这是第一页 5 条记录
select * from student limit 5,5 ; 这是第二页 5条记录
select * from student limit 10,5 这是第三页 5条记录
补充:
oracle:rownum 分页方言
sqlserver:top 分页方言
六、存储过程(带循环)
参数(in、out、inout)
WHILE循环,LOOP循环以及REPEAT循环
这几个循环语句的格式如下:
(1)WHILE……DO……END WHILE
(2)REPEAT……UNTIL END REPEAT
(3)LOOP……END LOOP
例1:方式1
DELIMITER $
CREATE PROCEDURE pro_testWhile(IN num INT,OUT result INT)
BEGIN
-- 定义一个局部变量(没有@)
DECLARE i INT DEFAULT 1;
DECLARE vsum INT DEFAULT 0;
WHILE i<=num DO --条件为真时执行
SET vsum = vsum+i;
SET i=i+1;
END WHILE;
SET result=vsum;-- 把参数带出去
END $
例2:方式2
-- 定义存储过程
DELIMITER $
CREATE PROCEDURE proRepeat(OUT outnum INT)
BEGIN
DECLARE i INT DEFAULT 0;
REPEAT
SET i=i+1;
UNTIL i>=5 -- 注意这里不要加分号,不然语法报错 UNTIL 结束条件
END REPEAT;
SET outnum=i; --
END $
-- 调用存储过程
CALL proRepeat(@num);
-- 显示带出的值
SELECT @num;
例3:方式3(loop需要一个结束条件)
DELIMITER $
CREATE PROCEDURE proLoop(OUT outnum INT)
BEGIN
DECLARE i INT DEFAULT 0;
myloop:LOOP -- 这里的myloop是我给循环起的一个标号或者说名字,这是语法要求,不起就会报错
SET i=i+1;
IF i>=5 THEN
LEAVE myloop; -- 根据我的循环标号中断循环"leave"是中断循环的意思
END IF;
END LOOP;
SET outnum=i; -- 注意带出值的时机
END $
-- 调用存储过程
CALL proLoop(@num);
SELECT @num;
----------------------------------
会话变量
-----------------------------------
七、函数
(1)系统函数
特点:任何函数都有返回值,函数的调用是通过select调用,因为有返回值,所以只能用select 调用。
需求1:截取字符串()
set @username='你好世界'; --定义一个会话变量
select substring(@username,1,1); -- 调用系统函数,从索引1位置截取一个字符
说明:mysql中索引都是从1开始的;其他系统函数的用法省略
(2)自定义函数
创建函数的语法
create function 函数名(参数列表可有可无)
returns 数据类型 --规定要返回的数据类型
begin
-- 函数体(单条语句)
return 返回值 --(返回值--与我们指定的类型一致);
end
测试1
-- 创建函数(省略了部分)
create function show() returns int
return 100;
-- 调用函数
select show();
常识
-- 查看所有函数 show functions status;
-- 查看创建函数 show create function test;
-- 删除函数 drop function test;
复合结构定义语法(常用)
DELIMITER $
CREATE FUNCTION delById(uid INT) RETURNS INT -- 参数(可以可无)不用写输入输出类型,这里的形参的作用,必须有返回值
BEGIN
DELETE FROM USER WHERE id=uid; --根据id删除用户
RETURN (SELECT COUNT(*) FROM USER); --统计用户的人数 返回去 --这里注意给整体的select 语句带上括号
END $
DELIMITER;
SELECT delById(7); --调用函数
说明:在函数体中,如果包含多条语句,我们需要把多条语句放到BEGIN...END语句块中