mysql-补充

mysql 新手必知必会

---

1–SQL

SQL语句结构
结构化查询语言包含6个部分：

一：数据查询语言（DQL:Data Query Language）：
其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。保留字SELECT是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有WHERE，ORDER BY，GROUP BY和HAVING。这些DQL保留字常与其他类型的SQL语句一起使用。

二：数据操作语言（DML：Data Manipulation Language）：
其语句包括动词INSERT，UPDATE和DELETE。它们分别用于添加，修改和删除表中的行。也称为动作查询语言。

三：事务处理语言（TPL: Transaction processing Language）：
它的语句能确保被DML语句影响的表的所有行及时得以更新。TPL语句包括BEGIN TRANSACTION，COMMIT和ROLLBACK。

四：数据控制语言（DCL: Data Control Language）：
它的语句通过GRANT或REVOKE获得许可，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。

五：数据定义语言（DDL, Data Define Language）：
其语句包括动词CREATE和DROP。在数据库中创建新表或删除表（CREAT TABLE 或 DROP TABLE）；为表加入索引等。DDL包括许多与人数据库目录中获得数据有关的保留字。它也是动作查询的一部分。

六：指针控制语言（CCL: Cusor Control Language）：
它的语句，像DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。

---

2–Mysql

---连接数据库
--mysql -u<username>  -p<password> -h<ip>   
mysql -uroot -pmysql
    
-- 不显示密码

mysql -uroot -p

-- 退出数据库**
--quit/exit/ctrl + d
exit

-- sql语句最后需要有分号;结尾
-- 显示数据库版本 version
select version();

-- 显示时间 now

select now();

---

3–database(数据库)操作

• 增

-- 创建数据库
create database python charset=utf8;

-- 使用数据库
use python;

• 删

-- 删除数据库*
-- drop database 数据库名;
drop database python;

• 改
  …
• 查

--查看当前使用的数据库
select database();

--查看所有数据库
show databases; 

-- 使用数据库***
-- use 数据库的名字
use python;

-- 查看创建数据库的语句
-- show create database 数据库名
show create database python;

---

4–table(表)操作

• 增

 -- 创建表
    -- int unsigned 无符号整形
    -- auto_increment 表示自动增长
    -- not null 表示不能为空
    -- primary key 表示主键
    -- default 默认值
    -- create table 数据表名字 (字段 类型 约束[, 字段 类型 约束]);
create table classes(id int unsigned primary key auto_increment,
name varchar(30) not null);

-- 创建 students 表(id、name、age、high (decimal)、
--gender (enum)、cls_id)**

create table student(id int unsigned primary key auto_increment,
name varchar(30) not null,age int unsigned,high decimal(3,2),
gender enum("男",'女','妖','保密') default "保密",cls_id int unsigned );

• 删

-- 删除表
    -- drop table 表名;
    drop table classes;
    -- drop database 数据库;
    drop database python25;

• 改

-- 修改表-添加字段 mascot (吉祥物)
    -- alter table 表名 add 列名 类型;

    alter table classes add mascot varchar(30);
   

    -- 修改表-修改字段：不重命名版
    -- alter table 表名 modify 列名 类型及约束;
   
    alter table classes modify mascot varchar(50) not null;


    -- 修改表-修改字段：重命名版
    -- alter table 表名 change 原名 新名 类型及约束;
    alter table classes change mascot jxw int ; 


    -- 修改表-删除字段
    -- alter table 表名 drop 列名;

    alter table classes drop jxw;

• 查

-- 查看当前数据库中所有表
show tables;

-- 查看表结构***
-- desc 数据表的名字;
desc classes;

-- 查看表的创建语句
-- show create table 表名字;
show create table student;

---

5–row(行)操作

• 增

+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)      | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
        -- 全列插入
        -- insert [into] 表名 values(...)
        -- 主键字段 可以用 0  null   default 来占位
        -- 向classes表中插入 一个班级

        insert into classes(id,name) values(0,'oldyang');

        部分插入

        insert into classes(name) values("oldyang2");

        insert into classes(name) values("oldyang3"),("oldyang3");
 -- 向students表插入 一个学生信息
+--------+-------------------------------------+------+-----+---------+----------------+
| Field  | Type                                | Null | Key | Default | Extra          |
+--------+-------------------------------------+------+-----+---------+----------------+
| id     | int(10) unsigned                    | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)                         | NO   |     | NULL    |                |
| age    | int(10) unsigned                    | YES  |     | NULL    |                |
| high   | decimal(5,2)                        | YES  |     | NULL    |                |
| gender | enum('男','女','中性','保密')       | YES  |     | 保密    |                |
| cls_id | int(11)                             | YES  |     | NULL    |                |
+--------+-------------------------------------+------+-----+---------+----------------+
        -- 全部插入
        insert into student(id,name,age,high,gender,cls_id) 
        values(0,'laowang',50,1.55 ,'妖',1);
        
        -- 部分插入
        -- insert into 表名(列1,...) values(值1,...)
        insert into student(name) values("小花");

        -- 多行插入
        insert into student(name) values("小花"),("大花");

        -- 批量插入数据 
        --insert into goods (name) values('123');

        insert into goods_cates(name) (select cate_name from goods group by cate_name);

• 删

-- 物理删除
        -- delete from 表名 where 条件

        delete from classes;
      

        -- 逻辑删除
        -- 用一个字段来表示 这条信息是否已经不能再使用了
        -- 给students表添加一个 is_delete 字段 bit 类型
        --alter table 表名 add 字段 类型 default 默认值;
        
        alter table student add is_delete bit default 0;

        update student set is_delete = 1 where id = 5;

• 改

 -- update 表名 set 列1=值1,列2=值2... where 条件;
        -- 全部修改
        update student set age = 30 ;
        
        -- 按条件修改

        update student set age = 18  where id = 3;
        
        -- 按条件修改多个值
        -- update students set gender ="",name = "xxx" where ;
        update student set gender = '女',age = 20 where id = 5;

        --2.update语句进行批量更新
        --这个有一个注意点select * from 这个去掉 

        update ( goods inner join goods_brands on goods.brand_name = goods_brands.name) set goods.brand_name = goods_brands.id;

• 查

---数据库操作前的准备
-- 创建数据库
create database python charset=utf8;

-- 使用数据库
use python;

--Student表
create table Student(SId varchar(10),name varchar(10),age datetime,gender varchar(10),height decimal(3,2));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男', 1.70);
insert into Student values('02' , '钱电' , '1990-12-21' , '男', 1.74);
insert into Student values('03' , '孙风' , '1990-12-20' , '男', 1.64);
insert into Student values('04' , '李云' , '1990-12-06' , '男', 1.65);
insert into Student values('05' , '周梅' , '1991-12-01' , '女', 1.82);
insert into Student values('06' , '吴兰' , '1992-01-01' , '女', 1.74);
insert into Student values('07' , '郑竹' , '1989-01-01' , '女', 1.69);
insert into Student values('09' , '张三' , '2017-12-20' , '女', 1.58);
insert into Student values('10' , '李四' , '2017-12-25' , '女', 1.67);
insert into Student values('11' , '李四' , '2012-06-06' , '女', 1.79);
insert into Student values('12' , '赵六' , '2013-06-13' , '女', 1.84);
insert into Student values('13' , '孙七' , '2014-06-01' , '女', 1.73);

--Course表
create table Course(CId varchar(10),Cname varchar(10),TId varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

--Teacher表
create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

--SC表(学生课程表)
create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

--简单查询
---------------------------查询练习-----------------------------
--查询一列
--select 字段名 from 表名;
select Sname from Student;

--查询多列
--select (字段名1,字段名2,...) from 表名;

select Sname, Sage from Student;

--查询所有列
-- select *  from 表名;
select * from Student;

-- 使用 as 给字段起别名
	-- select 字段 as 名字.... from 表名;
	select Sname as name from Student;

	

	-- select 表名.字段 .... from 表名;


	select Student.name from Student;
	
	
	-- 可以通过 as 给表起别名
	-- select 别名.字段 .... from 表名 as 别名;
	
	select s.name from Student as s;


-- 消除重复行(查姓名)

-- distinct 字段 (每个distinct只管自己字段)
select distinct Sname from Student;

-- 条件查询
	-- 比较运算符
	> >= < <= != = <>
		-- select .... from 表名 where .....
		-- >
		-- 查询年纪大于18岁的信息
		select * from Student where age > 18;




		-- <
		-- 查询年纪小于18岁的信息
		
		select * from Student where age < 18;

		-- >=
		-- <=
		-- 查询小于或者等于18岁的信息
		select * from Student where age <= 18;
		-- =
		-- 查询年龄为18岁的所有学生的名字
		select * from Student where age = 18;



		-- != 或者 <>
		-- 查询年龄不为18岁的所有学生的名字
		select * from Student where age != 18;
		select * from Student where age <> 18;
		
		

	-- 逻辑运算符
		-- and
		-- 18和28之间的所有学生信息
		select * from Student where age > 18 and age < 28;


		-- 18岁以上的女性

		select * from Student where age > 18 and gender = '女';


		-- or
		-- 18以上或者身高高过180(包含)以上

		select * from Student where age > 18 or height >= 180;
		

		-- not
		-- 不在 18岁以上的女性 这个范围内的信息
		
		select * from Student  where age > 18 and gender = '女';
		select * from Student  where not age > 18 and gender = '女';
		select * from Student  where not (age > 18 and gender = '女');
		


	-- 模糊查询(where name like 要查询的数据)
		-- like 
		-- % 替换任意个
		-- _ 替换1个
		-- 查询姓名中 以 "小" 开始的名字

		select * from Student where name like '小%';
		

		-- 查询姓名中 有 "小" 所有的名字

		select * from Student where name like "%小%";
		

		-- 查询有2个字的名字

		select * from Student where name like '__';
		


		-- 查询有3个字的名字
		select * from Student where name like '___';
												
		

		-- 查询至少有2个字的名字

		select * from Student where name like '__%';
		


	-- 范围查询
		-- in (1, 3, 8)表示在一个非连续的范围内
		-- 查询 年龄为18或34的姓名

		select * from Student where age = 18 or age = 34;
		
		select * from Student where age in (18,34);

		-- not in 不非连续的范围之内
		-- 年龄不是 18或34岁的信息

		select * from Student where age not in (18,34)

		
		-- between ... and ...表示在一个连续的范围内
		-- 查询 年龄在18到34之间的的信息

		-- 包含两端从小到大
		
		select * from Student where age between 18 and 34;

		
		-- not between ... and ...表示不在一个连续的范围内
		-- 查询 年龄不在18到34之间的的信息

		select * from Student where age not between 34 and 18;

		

	-- 空判断
		-- 判空is null
		-- 查询身高为空的信息
		select * from Student where height is null;


		-- 判非空is not null

		select * from Student where height is not null;
		


-- 排序
	-- order by 字段
	-- asc
	-- asc从小到大排列，即升序
	-- desc
	-- desc从大到小排序，即降序
	-- 查询年龄在18到34岁之间的男性，按照年龄从小到大到排序

	select * from Student where age > 18 and age < 34 
	and gender = '男'  order by age asc;


	-- 查询年龄在18到34岁之间的女性，身高从高到矮排序

	select * from Student where age > 12 and age < 56 
	and gender ='女' order by height desc;
	

	-- order by 多个字段
	-- 查询年龄在18到34岁之间的女性，身高从高到矮排序, 
	-- 如果身高相同的情况下按照年龄从小到大排序
select * from Student where age > 12 and age < 56 
	and gender ='女' order by height desc,age asc;
	
    -- 如果年龄也相同那么按照id从大到小排序
select * from Student where age > 12 and age < 56 
	and gender ='女' order by height desc,age asc,id desc;
	
	排序有优先级第一个优先级最高



--创建计算字段(表中实际不存在的字段,根据业务需求指标创建出来的字段)

  --拼接字段
  --concat() 拼接一个或多个指定串,各个串之间用逗号分隔.
  select concat(name,'--出生日:',age) from Student;


  --算数计算字段
  --比如及格率,优秀率.....大多根据统计学公式求得


--数据处理函数


    --文本处理函数
    --left(<字符串>,<从左数几个字符>)     返回串左边的字符
    select left(name,1) from Student;

    --right()    返回串右边的字符
    select right(name,1) from Student;

    --length()   返回串的长度
    select length(Sname) from Student;

    --locate(<要寻找位置的子字符串>,<字符串>,<查找的起始位置>)   找出串的子串的位置
    SELECT locate("雷",sname) FROM Student;
    select locate('k', salary) from company;

    --lower()    将串转换为小写(针对英文)
    SELECT lower(sname) FROM Student;

    --upper()    将串转换为大写(针对英文)
    SELECT upper(sname) FROM Student;

    --ltrim()    去掉串左边的空格
    SELECT ltrim(sname) FROM Student;

    --rtrim()    去掉串右边的空格
    SELECT ltrim(sname) FROM Student;

    --substring(<字符串>,<截取起始位置, 包括>,<截取长度>)返回子串的字符
    select substring(name,1,1) from Student;
    --soundex()  返回串的soundex值(暂时用不到)



    --日期时间处理函数
    --adddate()   增加一个日期(天 周等)
    --addtime()   增加一个时间(时分秒等)

    --now()       返回当前日期和时间
    --curdate()   返回当前日期
    --curtime()   返回当前时间

    --date()      返回日期时间的日期部分
    --time()      返回日期时间的时间部分
    --year()      返回一个日期的年份部分(1990), 四位
    --month()     返回一个日期的月份部分(09), 两位
    --day()       返回一个日期的天数部分(09), 两位
    --hour()      返回一个时间的小时部分(00), 两位
    --minute()      返回一个时间的分钟部分(00), 两位
    --second()      返回一个时间的秒部分(00), 两位

    --dayofweek()  返回一个日期对应的星期几
    --dayofmonth()  返回一个日期对应的当月多少号
    --dayofyear()  返回一个日期对应的当年第多少天

    --datediff()  计算两个日期之差(天数)
    select s.*,datediff(now(), Sage) as day FROM Student as s where datediff(now(), Sage) >7;
    --date_add()  高度灵活的日期运算函数

    --date_format 返回一个格式化的日期或时间
    SELECT Sage,date_format(Sage, '%Y/%m/%d/ %H:%i:%S') from Student;



select
  concat(Sname,'--出生日:',Sage), now(), curdate(),curtime(),date(now()) ,time(now()), YEAR(now()),month(now()),
  day(now()),hour(now()),minute(now()),second(now()),dayofweek(now()),dayofmonth(now()),dayofyear(now())
from Student;
    


    --数值处理函数	
    --abs()   返回一个数的绝对值
    --cos()   返回一个函数的余弦值
    --sin()   返回一个函数的正弦值
    --tan()   返回一个函数的正切值
    --mod()   返回一个操作数的余数
    --exp()   返回一个数的指数值
    --pi()     返回圆周率
    --rand()  返回一个随机数
    --sqrt()  返回一个数的平方根
    --round(<数据字段>,<小数位数>) 返回一个数的四舍五入值
	


-- 聚合函数
	-- 总数
	-- count()
	-- 查询男性有多少人

	select count(*) from students where gender = '男';
	
	-- 最大值
	-- max()
	-- 查询最大的年龄

	select max(age) from students ;
	
	-- 查询女性的最高 身高
	
	select max(height) from students where gender ='女';


	-- 最小值
	-- min()

	select min(height) from students where gender = '男';
	


	-- 求和()
	-- sum
	-- 计算所有人的年龄总和(所有行或特定行)

	select sum(age) from  students;
	
	
	-- 平均值
	-- avg
	-- 计算平均年龄

	select avg(age) from students;
	


	-- 计算平均年龄 sum(age)/count(*)

	select sum(age) /count(*) from students;


	-- 四舍五入 round(123.23 , 1) 保留1位小数
	-- 计算所有人的平均年龄，保留2位小数


	select round(avg(age),2) from students;


	-- 计算男性的平均身高 保留2位小数

	select avg(height) from students;

	select sum(height)/count(*) from students;

	select count(id) from students;

	聚合函数不会去计算 null值
	


-- 分组

	-- group by
	-- 按照性别分组,查询所有的性别
	select 分组字段 from 表名 group by 分组字段 having 分组的条件;

	select gender from students group by gender;



	-- 计算每种性别中的人数

	select gender,count(*) from students group by gender;



	-- group_concat(...)
	-- 查询同种性别中的姓名
	-- 返回一组的姓名 

	select gender,group_concat(name) from students group by gender;
	


	
	-- 查询每组性别的平均年龄

	select gender ,avg(age) from students group by gender;
	


	-- having(注意having和group by 连用 having后通常也要跟 聚合函数)
	-- 查询平均年龄超过30岁的性别，以及姓名
	
	select gender ,avg(age), group_concat(name) from students 
	group by gender having avg(age) > 30;

	
	-- 查询每种性别中的人数多于2个的信息

	select gender,count(*) from students group by gender
	 having count(*) > 2;
	



	-- with rollup 汇总的作用(了解)
	--select gender,count(*) from students group by gender with rollup;
    

	select gender,count(*) from students group by gender
	  with rollup having count(*) > 2 ;


-- 分页
	-- limit start, count
	-- limit 放在最后面(注意)

	起始人位置  = (页面-1)*每一页的个数
	
	-- 限制查询出来的数据个数
	-- 查询前5个数据

	select * from students limit 0,5;


	-- 每页显示2个，第1个页面

	select * from students limit 0,2;
	

	-- 每页显示2个，第2个页面
	select * from students limit 2,2;


	-- 每页显示2个，第3个页面
	select * from students limit 4,2;

    --也可用offset偏移 limit <取出页数> offset <开始页码>

    select * from students limit 2 offset 4;




	-- 每页显示2个，第4个页面

	select * from students limit 6,2;
	

	-- 每页显示2个，显示第6页的信息, 按照年龄从小到大排序

	select * from students order by age asc limit 10,2;

----------------------------------------------------------------------------------------------

-- 连接查询
	-- inner join ... on
	-- select ... from 表A inner join 表B;
	-- 查询 有能够对应班级的学生以及班级信息\

	select * from 表a inner join 表B on 条件;
	
	select * from students inner join classes
	 on students.cls_id = classes.id;


	select * from students inner join classes
	 on students.cls_id = classes.id where students.gender ='女';

	-- 按照要求显示姓名、班级
	select students.name,classes.name from students inner join classes
	 on students.cls_id = classes.id;


	-- 给数据表起名字
	select s.name,c.name from students as s 
	inner join classes as c on s.cls_id = c.id;


	-- 查询 有能够对应班级的学生以及班级信息，显示学生的所有信息 students.*，只显示班级名称 classes.name.
	select students.* ,classes.name from students inner join classes
	 on students.cls_id = classes.id;

	
	-- 在以上的查询中，将班级名显示在第1列
select classes.name ,students.*  from students inner join classes
	 on students.cls_id = classes.id;


	-- 查询 有能够对应班级的学生以及班级信息, 按照班级名进行排序
	
select classes.name ,students.*  from students inner join classes
	 on students.cls_id = classes.id order by classes.name;

	
	
	-- 当时同一个班级的时候，按照学生的id进行从小到大排序
	
select classes.name ,students.*  from students inner join classes
 on students.cls_id = classes.id 
 order by classes.name asc ,students.id asc;



	-- left join
	-- 查询每位学生对应的班级信息

	select * from students left join classes on 
	students.cls_id = classes.id;
	


	-- 查询没有对应班级信息的学生
	select * from students left join classes on 
	students.cls_id = classes.id where classes.name is null;
	

	
	-- right join   on
	-- 将数据表名字互换位置，用left join完成

	select * from students right join classes on 
	classes.id = students.cls_id;

----------------------------------------------------------------------------------	

-- 子查询
	-- 标量子查询: 子查询返回的结果是一个数据(一行一列)
	-- 列子查询: 返回的结果是一列(一列多行)
	-- 行子查询: 返回的结果是一行(一行多列)
	
	-- 查询出高于平均身高的信息(height)
	-- 得到平均身高
	select avg(height) from students;
	-- 大于平均 身高

	select * from students where height > 172.076923;


	select * from students where 
	height > (select avg(height) from students);


	-- 查询学生的班级号能够对应的 学生名字

	-- 有多少 个班级
	select name from classes;
	select id from classes;
	
	-- 学生对应 的班级信息
	select * from students where students.cls_id in(1,2);


select * from students where students.cls_id in(select id from classes);
	
	--数据操作前的准备
	--创建数据库表
	create table areas(
    aid int primary key,
    atitle varchar(20),
    pid int
	);
	--从sql文件中导入数据
	-- source 具体地址/areas.sql;

	source xxx.sql;

	
	--查询一共有多少个省

	select * from areas where pid is null;

	--例1：查询省的名称为“山西省”的所有城市

	-- 分两步
	-- 第一步查到山西 的编号 
	select * from areas where atitle = '山西省';
	select aid from areas where atitle = '山西省';

	-- 第二步查到pid是山西 的编号 

	select * from areas where pid = '140000';

	select * from areas where pid = (
		select aid from areas where atitle = '山西省'
	);

select * from areas where pid = (
		select aid from areas where atitle = '杭州市'
	);


	-- 自联

	select * from areas as a1 inner join areas as a2 
	on a1.pid = a2.aid  where a2.atitle = '杭州市'; 

	

	
	--例2：查询市的名称为“广州市”的所有区县
	
select * from areas where pid = (
		select aid from areas where atitle = '广州市'
	);

		select * from areas as a1 inner join areas as a2 
	on a1.pid = a2.aid  where a2.atitle = '上海'; 
 -- sql强化演练( goods 表练习)

-- 查询类型 cate_name 为 '超级本' 的商品名称 name 、价格 price 

select name,price from goods where cate_name = "超级本";


-- 显示商品的种类
-- 1 分组的方式( group by ) 

select cate_name from goods group by cate_name;


-- 2 去重的方法( distinct )
select distinct cate_name from goods;


-- 求所有电脑产品的平均价格 avg ,并且保留两位小数( round )
select round(avg(price),2) from goods;



-- 显示 每种类型 cate_name (由此可知需要分组)的 平均价格

select cate_name,avg(price) from goods group by cate_name;



-- 查询 每种类型 的商品中 最贵 max 、最便宜 min 、平均价 avg 、数量 count

select cate_name,max(price),min(price),avg(price),count(*) from goods group by cate_name;



-- 查询所有价格大于 平均价格 的商品，并且按价格降序 排序 order desc
--1.平均价格

select avg(price) from goods;
--2.大于平均价格

select * from goods where price > (select avg(price) from goods)  order by price desc;



-- 查询每种类型中最贵的电脑信息(难)

-- 1 查找 每种类型 中 最贵的 max_price 价格

select cate_name ,max(price) from goods group by cate_name;


-- 2 查询最贵的信息

select * from goods where (cate_name,price) = ('台式机',9188);


select * from goods where (cate_name,price) in (select cate_name ,max(price) from goods group by cate_name);


---------------------------------------------------------
--组合查询
--union


-------------------------------------------------------------
--第四部 修改表结构(重点是思路)

-- 创建表格的格式
--create table if not exists 表名 (
--id int unsigned primary key auto_increment, 这个是主键
--name varchar(40) not null); 这个名称


--第一步 创建 "商品种类表" -goods_cates
--id跟名称

create table goods_cates (id int unsigned primary key auto_increment,
name varchar(50) not null);


--第二步 创建 "商品品牌表" -goods_brands
--id跟名称
create table goods_brands (id int unsigned primary key auto_increment,
name varchar(50) not null);



--第三步 更新种类信息表
--1. 要把类型查询出来

select cate_name from goods group by cate_name;

--2. 批量插入数据 
--insert into goods (name) values('123');

insert into goods_cates(name) (select cate_name from goods group by cate_name);



# 批量插入的语句


--第四 更新品牌信息表
--1. 要把品牌信息查询出来

select brand_name from goods group by brand_name;

--2. 批量插入数据

insert into goods_brands(name) (select brand_name from goods group by brand_name);


--第五 更新种类信息成种类信息表中的id
--1. 种类信息跟要替换成表进行内联

select * from goods inner join goods_cates on goods.cate_name = goods_cates.name;




--2. 通过特殊的update 进行更新

--这个有一个注意点select * from 这个去掉

update (goods inner join goods_cates on goods.cate_name = goods_cates.name) set goods.cate_name = goods_cates.id;


--第六 更新品牌信息成品牌信息表的id
--1. 把要更新的表两张表进行内联
select * from goods inner join goods_brands on goods.brand_name = goods_brands.name;



--2.通过特殊的update语句进行更新 

update ( goods inner join goods_brands on goods.brand_name = goods_brands.name) set goods.brand_name = goods_brands.id;



	
--第七步 修改字段(注意类型必须跟外键的主键类型一致)
-- alter table 表名 change 旧字段 新字段 类型
alter table goods change cate_name cate_id int unsigned;
alter table goods change brand_name brand_id int unsigned;


-- 外键的使用(了解)

-- 向goods表里插入任意一条品牌数据"老王牌拖拉机"
-- 你会发现我们外键不能创建,因为没有对应的品牌


-- 约束 数据的插入 使用 外键 foreign key
-- alter table 主表名 add foreign key (主表的外键) references 外键表(外键主键);

alter table goods add foreign key (cate_id) references goods_cates(id);



-- 创建表的同时设置外键 (注意 goods_cates 和 goods_brands 两个表必须事先存在)
-- foreign key (主表的外键) references 外键表(主键)
create table if not exists goods_key(
    id int primary key auto_increment not null,
    name varchar(40) default '',
    price decimal(5,2),
    cate_id int unsigned,
    brand_id int unsigned,
    is_show bit default 1,
    is_saleoff bit default 0,
 	-- 添加外键
     foreign key (brand_id) references goods_brands(id)
);


-- 如何取消外键约束
-- 需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称
--show create table 表名;
show create table goods;


-- 获取名称之后就可以根据名称来删除外键约束
--alter table 表名 drop foreign key 外键名;

alter table goods drop foreign key goods_ibfk_1;

alter table goods drop key cate_id;

---

6–视图

• 为什么使用视图
  • 重用SQL语句
  • 安全
  • 简单
  • 高效
• 视图的规则和限制
  • 唯一命名
  • 数量没有限制
  • 足够的权限
  • 可以嵌套
  • 检索时使用的order by 会覆盖视图中的order by
  • 不可以索引
  • 视图可以和表一起使用

--创建视图
-- create view <视图名> as <sql语句>;

create view first_view AS
select * from Student where YEAR(Sage)='1990';

--使用视图(和使用正常表一样, 当然只能不存在的字段检索不出来)
select * from first_view;

--查看视图创建的语句
-- show create view <viewname>;
show create view first_view;

--查看有哪些视图, 系统把他当成表
show tables;

--删除视图
--drop view <viewname>;
drop view first_view;

--更新视图可以drop后再create, 也可以直接使用create or replace view

---

7–存储过程

存储过程,可以简单理解为批处理文件.

---创建存储过程
--create proc[edure] <存储过程名>(参数列表)
--begin
--sql;
--end;

-- delimiter // 更改分隔符(如果出错)

-- delimiter //
-- CREATE PROCEDURE first_procedure()
--   BEGIN
--     SELECT * from Student;
--   END//

CREATE PROCEDURE first_procedure()
  BEGIN
    SELECT * from Student;
  END;


--使用存储过程
call first_procedure();



--删除存储过程
drop procedure first_procedure;

--检查存储过程
show create procedure first_procedure;

所有变量都要用declare来 声明 数据类型
参数列表
out <变量名> <数据类型> (存储过程的返回值)
in  <变量名> <数据类型> (存储过程接收的参数)

方法体
into  <接收变量名>

用 @表示变量

--示例
create procedure ordertotal(
    IN onumber int,
    IN taxable boolean,
    OUT ototal decimal(8,2)
)
begin
    --declare variable for total
    declare total decimal(8,2);
    --declare tax percentage
    declare taxrate int default 6;

    --get the order total
    select sum(iten_price * quantity) 
    from orderitems
    where order_num = onumber
    INTO total;

    --is this taxable?
    if taxable then 
        --yes, so add taxrate to the total
        select total+(total/100*taxrate) INTO total; 
    end if;

    --and finally , save to out variable
    select total INTO ototal;  -- 将临时变量值传给ototal


--调用
CALL ordertotal(20005, @total)
select @total;  --可以用select @<变量名1>,@<变量名2>... ; 来显示变量内容

使用游标

• 灵活操控查询集里的行

CREATE PROCEDURE first_procedure(
    OUT o VARCHAR(50)
)
  BEGIN
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE temp VARCHAR(50);
#     创建游标
    DECLARE first_cursor CURSOR
    FOR
    SELECT Sname FROM Student;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
    #     打开游标
    OPEN first_cursor;
    REPEAT
      FETCH first_cursor INTO temp;
      SELECT temp INTO o;
    UNTIL  done END REPEAT;
    CLOSE first_cursor;
  END;


--调用存储过程
call  first_procedure(@o)
-- 查询
SELECT @o;

---

8–触发器

• 某条(些)语句在事件发生时自动执行, 使用触发器.
• 触发器就是mysql响应(delete, insert, update)任意语句而自动执行的一条或一组语句, 其他语句不支持触发器.

只有个表才支持触发器, 视图不支持

--创建触发器
create trigger first_trigger after insert on orders 
for each row select new.order_num;

9–管理事务处理

• 事务可以用来维护数据库的完整性,他保证成批操作要么完全执行,要么完全不执行.

• 事务(transaction) 指一组sql语句

• 回退(rollback) 指撤销指定sql语句的过程

• 提交(commit) 指将未存储的sql语句结果写入到数据库表;

• 保留点(savepoint) 指事务处理过程中设置的临时占位符(placeholder), 你可以对他发布回退(与回退整个事务处理不同).

事务特点

• 原子性
• 一致性
• 隔离性
• 持久性

--回退事务
select * from Student;
--开启事务
START TRANSACTION ;
DELETE from Student where SId = 1;
SELECT * FROM Student;
-- 回退到开启事务的地方
ROLLBACK ;
select * from Student;


--提交事务
select * from Student;
START TRANSACTION ;
UPDATE Student set Sname='刘莉' where SId = 1;
SELECT * FROM Student;
--提交事务后,结果持久化
COMMIT ;
select * from Student;


--设置保存点
--savepoint <保留点名>
savepoint first_point;


--回滚至保存点
ROLLBACK TO <保留点名>;


--保留点越多愈好


--保留点在事务结束后自动释放

--更改默认提交行为

SET autocommit=0;  --值为假

---

10–全球化和本地化

--查看mysql支持哪些字符集
show character set ;

--查看支持校对的完整列表
SHOW COLLATION ;

---

11–安全管理

mysql用户账号和信息一般存储在mysql数据库.

use mysql

-- 创建用户
-- create user <name> [indentified by ..]
create user ben;

--重命名
rename user ben to tom;


--删除用户
drop user tom;


--查看权限
show grants for tom;


--设置权限
--grant  <权限>  on <范围> to  user@host;
--每个grant添加或者更新用户的一个权限.

grant all on *.* to 'tom'@'%';
mysql> grant all privileges on *.* to 'alex'@'%' identified by '123456' with grant option;
-- •all privileges：表示将所有权限授予给用户。也可指定具体的权限，如：SELECT、CREATE、DROP等。
--  •on：表示这些权限对哪些数据库和表生效，格式：数据库名.表名，这里写“*”表示所有数据库，所有表。如果我要指定将权限应用到test库的user表中，可以这么写：test.user
--  •to：将权限授予哪个用户。格式：”用户名”@”登录IP或域名”。%表示没有限制，在任何主机都可以登录。比如：”alex”@”192.168.0.%”，表示alex这个用户只能在192.168.0IP段登录
--  •identified by：指定用户的登录密码
--  •with grant option：表示允许用户将自己的权限授权给其它用户 


--刷新权限
flush privileges;
 


--撤销权限
--revoke  <权限>  on <范围>  to user@'%';
--每个revoke撤销一个用户权限

revoke all on *.* from tom;



--修改密码()
-- 语法：set password for ‘用户名'@'登录地址'=password(‘密码')

---

12–数据库维护

--检查表状态
--analyze table <表1>,<表2>,<表2>;
analyze table orders;


--诊断启动


--查看日志文件

---

13–性能优化

建议

• mysql专用一个服务器
• 默认配置一段时间后,使用show variables;和show status;

---

14–配置与部署

---

15–其他

mysql中保留字

action  add  all alter  analyze  and as  asc  asensitive  

before between binary bit blob both by 

call cascade  case change  char  character check collate column condition

connection  constraint  continue  convert  create cross current_date   current_time  current_timestamp
current_user cursor 

database databases date  day_hour  day_microsecond  day_minute  day_second desc decimal  declare default 
delayed delete desc  describe deterministic distinct   distinctrow  div   double   drop dual 

each else elseif enclosed enum escaped  exists exit explain 

false fetch  float  for  force  foreign  from  fulltext 


goto  grant  group  


having  high_priority hour_microsecond  hour_minute  hour_second 



if ignore  in index  infile  inner inout insensitive  insert  int integer  interval  into is  iterate 

join 

key  keys kill 

leading  leave left like limit  lines load localtime  localtimestamp  lock long  longblob  longtext
loop  low_priority 

match  mediumintblob mediumint mediumtext middleint minute_microsecond minute_second mod modifies 

natural  no  no_write_to_binlog not null numeric 

on optimize option optionally or order out  outer outfile 

frecision primary procedure purge read  reads real  references regexp release rename repeat replace require 
restrict return revoke right rike 

schema  schemas second_microsecond select sensitive separator set show smallint soname spatial specific sql 
sql_big_result sql_calc_found_rows sql_small_result sqlexception sqlstate sqlwarning ssl starting straight_join

table terminated text then time timestamp tinyblob tinyint tinytext to trailing trigger true 

undo union  unique unlock unsigned  update usage use using utc_date utc_time  utc_timestamp 

values varbinary varchar varcharacter varying 

when where while with write xor year_month zerofill