MYSQL(三)

接上。。。点击打开链接

一、 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语句块中