mysql

最新推荐文章于 2023-07-22 18:14:59 发布

weixin_101

最新推荐文章于 2023-07-22 18:14:59 发布

阅读量187

点赞数

分类专栏： python Linux 笔记文章标签：数据库 mysql python

本文链接：https://blog.csdn.net/weixin_41560737/article/details/114736246

版权

Linux 同时被 3 个专栏收录

70 篇文章 4 订阅

订阅专栏

python

25 篇文章 0 订阅

订阅专栏

笔记

19 篇文章 0 订阅

订阅专栏

库基本操作

show databases; #查看已有库
create database 库名 character set utf8; # 创建库
use 库名; #切换库
select database(); # 查看当前所在库
drop database 库名; # 删除库

-------------------——------------------------------------------------------------

数据类型：
数字： tinyint int float bit

             字符串： char  varchar   text   enum   set  

             时间类型数据：DATE   TIME      YEAR      DATETIME     TIMESTAMP 
                               日期时间函数：
                                                        now() 返回服务器当前时间日期，格式对应datetime类型
                                                        curdate（） 返回当前日期，格式对应date类型
                                                         curtime（） 返回当前时间，格式对应time类型

字段约束： primary key auto_increment
unsigned
not null
default [val]

DDL: CREATE DROP ALTER
DML: SELECT DELETE UPDATE INSERT
DCL: COMMIT ROLLBACK GRANT REVOKE

数据表管理

create table 表名(字段名数据类型约束，字段名数据类型约束，… ) #创建表约束条件：无符号unsigned 不允许为空 not null
desc 表名； #看表结构
show create table 表名； #查看表的创建信息
drop table 表名; #删除表

表数据基本操作( 增删改查 )

增（insert）
insert into 表名 values(值1),(值2),… # 插入(insert)
insert into 表名 (字段1，字段2,…) values (值1),(值2),… #插入内容与字段对应

查询（select）
select * from 库名.表名 #查询所有字段
select * from 库名.表名 [where 条件] #查询所有字段，加筛选条件
select 字段1,字段2,… from 库名.表名 [where 条件] #查询某一字段，加筛选条件

where 条件：
= 等于
!= 不等于
> 大于
< 小于
<= 小于等于
>= 大于等于
BETWEEN 在两值之间
NOT BETWEEN 不在两值之间
IN 在集合中
NOT IN 不在集合中
IS NULL 为空
IS NOT NULL 不为空

             **逻辑运算符**：    NOT   逻辑非      AND    逻辑与        OR          逻辑或

更新表记录（update）改
update 表名 set 字段1=值1，字段2 = 值2,… where 条件;
#注意：如果update 语句后如果不加where 条件，所有记录全部更新

删除表记录（delete）
delect from 表名 where 条件；
#注意：delete 语句后如果不加where 条件，所有记录全部清空

表字段的操作（alter）
语法：alter table 表名执行动作;

      **1.添加字段（add）**
       alter table 表名 add 字段名 数据类型;
       alter table 表名 add 字段名 数据类型 first；
       alter table 表名 add 字段名 数据类型 after 字段名；
   
       **2.删除字段（drop）**
       alter table 表名 drop 字段名 ;

       **3.修改数据类型（modify）**
       alter table 表名 modify 字段名 新数据类型;

       **4.修改字段名（change）**
       alter table 表名 change 旧字段名 新字段名 新数据类型；

       **5.表重命名（rename）**
       alter table 表名  rename 新表名；

高级查询语句 ( like )

模糊查询和正则查询
1. 模糊查询（like）
like 用于where字句中进行模糊查询，sql like 字句中使用
% 来表示任意0个或多个字符，
_ 下划线表示任意一个字符

           select * from stu.cls where name like "L%";   #查询L开头的name 
           select * from stu.cls where name like "____";  #查询四个字符的name

2.正则查询（regexp）
select * from stu.cls where name regexp “^A.+”; #不区分大小写，如果不加^号，其他的也匹配到
select * from cls where name regexp “^.{3}$”; #三个字符的name

3.重命名（as）
在sql语句中 as 用于给字段或者表重命名
select name as 姓名 , age as 年龄 from cls; #查询后给字段命名—当前语句有效

3.排序（order by）
ORDER BY 字句设定你想按哪个字段哪种方式进行排序，再返回搜索结果
升序（ASC）默认
SELECT * FROM cls order by score; #默认按升序排序

降序（DESC）
SELECT * FROM cls where sex=“m” order by score desc; #查询性别是男生的按照降序排序

复合排序 ：对多个字段排序，即当第一排序相同按照第二排序项排序
select *from cls order by age desc,score; # 年龄相同按分数降序排序

4.限制（limit）
select *from cls where sex=“w” limit 2; #限制
select *from cls where sex=“m” order by score desc limit 1; #查询男生分数最高的男生

5.联合查询（UNION）
union 操作符用于连接两个以上的select 语句的结果组合到一个结果集合中。多个select语句会删除重复数据。
UNION 操作符语法格式：
默认 union 后卫 distinct 表示删除结果
select * from cls where sex=“w” union select *from cls where score >90; #1语句联结 2语句查询
select * from cls where sex=“w” union all select *from cls where score >90; #默认是去除重复, 加上all 显示重复
select name,age,score from cls where score>90 union select name,hobby,price from stu.hobb
select name,age,score from cls union select name,hobby,price from stu.hobby order by name; # union 加order by 排序

*6.子查询（select …from（select） | select from 表 where （select ）））
当一个查询包含另一个select查询语句，则称之为有子查询的语句
1.from之后，此时子查询的内容作为一个新的表内容，在进行外层select查询
select * from (select *from cls where sex=“w”) as w where score>80; #需要将子查询结果重命名方便where 引用查询
select * from(select *from cls where sex=“w”) as w where s.corce >80; #作用一样
2.在where 字句中，此时select 查询到的内容作为外层查询的条件值
select * from cls where score >(select score from cls where name=“Abby”); #查询分数比Abby高的值
select *from cls where score in (select score from cls where name =“Abby” or name=“Tom”); #范围内数据

7.聚合操作
聚合函数：
avg （该字段的平均值） max（该字段的最大值） min（该字段的最小值）sum（该字段所有记录的和）cout（统计该字段的个数）
select max(attack) from sanguo;
select max(attack) as 最大 , avg(attack) as 平均 from sanguo;
select count(name) as from sanguo;

         **聚合分组（group by）：**
                   group by     给查询结果进行分组 
                   select  gender from sanguo group by gender;     #按性别分组 ，按什么分组就只能写什么字段
                   select country,count(*) from sanguo group by country;
                   select country,avg(attack),count(*) from sanguo group by country;   #按国家分组，计算攻击力平均值
                   select country,gender,avg(attack)  from sanguo group by country,gender;        #按多个字段分组
                  select * from cls where score in (select max(score) from cls where sex in ("m","w") group by sex);   #查询男女生最高分
                  select country,count(id) as num from sanguo where gender="男" group by country order by num desc limit 2;  
    
          **聚合筛选（having）** 
                     having 语句必须联合group by 语句使用
                     select country,avg(attack) from sanguo group by country having avg(attack)>180;     #配合group by联合使用 
                    

          **去重语句（distinct）**
                      select distinct country,gender from sanguo; # distinct 去重

          **聚合运算：**
                        + - * / %  
                       select name,attack*2 from sanguo;   #攻击力双倍

8.索引操作：
索引是对数据库表中一列的值进行排序的一种结构，使用索引可快速访问数据库中的特定信息
索引分类：
普通索引（MUL）
普通索引：字段无约束，key标志位null
唯一索引（UNI）unique
唯一索引：字段不允许重复，但可为null，key标志为null
主键索引（PRI） primary key
一个表中只能有一个主键字段，主键字段不允许重复，且不能为null
key标志为PRI 。通常设置记录编号字段ID,能唯一锁定一条记录

               **索引创建**
                            创建表时直接创建索引
                            create table 表名  字段名 数据类型，字段名 数据类型， index 索引名（字段名）unique 索引名（字段名）；
                            
                            在已有表中创建索引：
                             create [unique] index 索引名 on 表名（字段名）;  或 create unique index 索引名 on 表名(字段)；
             
                             主键索引添加 
                             alter table 表名  add primary key（id）
                        
                             查看索引
                              desc 表名；     ------key 标志为MUL\ UNI  
                              show index from 表名;      

                              删除索引  
                              drop index 索引名 on 表名;
                              alter table 表名 drop primary key;   #删除主键

完整的执行步骤:
（5）select distinct <select_list>
(1) from <left_table> <join_type> join <right_table> on <on_predicate>
(2) where <where_predicate>
(3)group by <group_by_specification>
(4)having <having_predicate>
(6)order by <order_by_list>
(7)limit <limit_number>

9.外键约束和表关联关系
contraint [外键名]
foreign key(外键字段)
references 主表(主表字段)

                 外键约束 ：
                                约束：约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性，唯一性
                  foreign key 功能： 建立表与表之间的某种的关系，由于这种关系的存在，能够让表与表之间的数据，更加的完整，
                                    关联性更强，为了具体说明创建如下部门表和人员表
                    例子：      #创建部门表
                                    create table dept (id  int primary key auto_increment ,dname varchar(50) not null);       
                                    #创建人员表
                                    create table person(
                                                          id int primary key auto_increment,
                                                          name varchar(32) not null,
                                                          age tinyint default 0,
                                                          sex enum("m","w","o") default "o",
                                                          salary decimal(8,2) default 250.00,
                                                          hire_date date not null,
                                                          dept_id int
                                                                  );   
                     foreign key 外键的定义语法：
                                  [CONSTRAINT symbo]  FOREIGN KEY (外键字段)  REFERENCES 表名(主表主键)
                                  该语法可以在create table 和alter table 时使用

例 1 创建表使用 ：
create table person(
id int primary key auto_increment,
name varchar(32) not null,
age tinyint default 0,
sex enum(“m”,“w”,“o”) default “o”,
salary decimal(8,2) default 250.00,
hire_date date not null,
dept_id int,
constraint dept_fk foreigh key(dept_id) references dept(id)
); —dept_fk 外键名是自己自定义起名
例2 已存在表使用：
alter table person add constraint dept_fk foreign key (dept_id) references dept(id);

                                       #通过外键名称解除外键约束：
                                      alter table person drop foreign key dept_fk;   
                                       show create table person;                              #查看外键名称
                                       注意：删除外键后发现dese查看索引标志还在，其实外键也是一种索引，
                                       需要将外键名称的索引删除之后才可以。 
                                       drop index dept_fk on person;

          **级联动作** ：
                              restrict（默认）: on delete restrict    on  update restrict  
                              cascade:   数据级联更新 on delete cascade on update cascade 
                              selt null:  on delete ste null    on update set null          
                               当主表删除记录时，从表外键字段变为null
                               当主更改主键字段值时，从表外键字段值变为null  
                                 
                                  #从表关联主表数据，那么主表数据改动从表也随之改动 
                          例： alter table person add constraint dept_fk foreign key(dept_id) references dept(id) on 
                                  delete cascade on update cascade;
                                  
                                  #从表关联主表数据，那么主表数据改动从表会变为null 
                                  alter table person add constraint dept_fk   --起名字
                                                              foreign key(dept_id)     --指定外键字段
                                                              references dept(id)        --指定主表字段
                                                              on delete set null on update set null ;

10. 表关联设置：
一对一关系
：一张表的一条记录一定只能与另外一张表的一条记录进行对应
例：
create table student(
id int primary key auto_increment,
name varchar(50) not null
);
create table record (
id int primary key auto_increment,
comment text not null ,
st_id int unique,
constraint st_fk foreign key(st_id) references student(id) on
delete cascade on update cascade
);

                   **一对多关系**
                                     :一张表中有一条记录可以对应另一张表中的多条记录；但是返过来另外一张表的记录只能
                                      对应第一张表的一条记录，这种关系就是一对多或多对一
                                   create table person(
                                                                id varchar(32) primary key,
                                                                name varchar(30),
                                                                sex  char(1),
                                                                age int
                                                                );
                                    create table car(
                                                              id varchar(32) primary key,
                                                              name varchar(30),
                                                              price decimal(10,2),
                                                              pid varchar(32),
                                                              constraint car_fk foreign key(pid) references person(id)
                                                             );

                    **多对多关系**
                                    : 一对表中（A）的一条记录能够对应另外一张表（B）中的多条记录，同时B表中的一条记录
                                      也能对应A中的多条记录
                                   例：一个运动员可以报多个项目，每个项目也会有多个运动员参加，
                                          这时为了表达多对多关系需要单独创建关系表
                                         create table athlete(
                                                                    id int primary key auto_increment,
                                                                    name varchar(30),
                                                                    age tinyint not null,
                                                                    country varchar(30) not null,
                                                                    description varchar(30)
                                                                    );
                                                                    
                                          create table item(
                                                                     id int primary key auto_increment,
                                                                     rname varchar(30) not null
                                                                     );
                                                                     
                                         create table athlete_item(
                                                                      id int primary key auto_increment,
                                                                      aid int not null,
                                                                      tid int not null,
                                                                      constraint athlete_fk foreign key(aid) references athlete(id),                                               
                                                                      constraint item_fk foreign key(tid) references item(id)
                                                                      );

10.E-R 模型
定义：E-R 模型即实体----关系
实体 ---- 属性 ------关系

11 . 内连接： (官方推荐使用)
内连接查询只会查询到符合条件的记录，其实结果和表关联查询时一样的，官方更推荐使用内连接查询
select 字段名 from 表1 inner join 表2 on 表1.字段 = 表2.字段 ; #数据量大的表放在前面
select cls.name,cls.score,hobby.hobby from cls inner join hobby on cls.name = hobby.name where score >90;
select name,dname from dept inner join person on dept.id = person.dept_id;

    外连接：左连接为主表，显示右表中与左表匹配的项  
                 左连接：
                           select name,dname,salary from dept left join person on dept.id =person.dept_id;
                 右连接：
                            select name,dname,salary from dept right join person on dept.id =person.dept_id; 
       
     多个表进行外链接查询：
            select * from (class left join student on class.cid = stduent.class_id) 
             left join score on student.sid = socre.student_id;

12. 视图（view）虚拟的表
视图概念：
视图是存储的查询语句，当调用的时候，产生结果集。视图充当的是虚拟表的角色
创建视图：
语法结构： create [or replace] view [view_name] as [select_statement]
create view student_hobby as select cls.name,score,hobby,price from cls
inner join hobby on cls.name = stu.hobby.name;

        #查看视图， 
        show * from student_hobby ;     

  # 视图（符合条件增删改除都可以）
             select * from student_hobby where score>70;          
          
          #查询数据库哪些是视图   
          show full tables in stu where table_type like "VIEW";    
            
          #删除视图
           drop view [if exists]  视图名;   #IF EXISTS 表示如果存在，这样即使没有指定视图也不会报错
           drop view if exists student_hobby; 

          #修改视图
          参考创建视图，将create关键字改为alter 
           alter view good_view as select * from cls where score>85;
           
           #对原有视图覆盖 (没有就创建有就直接覆盖)
           create or replace view good_student as select * from cls where score>90;

演示例子：

create database stu charset=utf8;        或     create database stu character set utf8;
use stu
create table class_1(
                              id int primary key auto_increment,
                              name varchar(30) not null,
                              age tinyint unsigned,
                              sex enum("m","w","o"),
                              score float default 0
                              );

create table hobby(
                                 id int primary key auto_increment,
                                 name varchar(30) not null,
                                 hobby set("sing","dance","draw"),
                                 level char(2),
                                 price decimal(7.2),
                                 remark text
                               );

create table marathon (
                              id int primary key auto_increment,
                              althle varchar(32),
                              birthday date,
                              registration_time datetime,
                              preformance time
                              );

insert into class_1 values
                                      (1,"lily","18","w","92"),
                                      (2,"tom","17","m","95");

insert into class_1 (name,age,sex,score) values
                                       ("Abby","17","w","92"),
                                       ("Baron","18","w","85");

insert into hobby(name,hobby,level,price,remark) values
                                         ("Joy","sing","A","15800","天籁之音"),
                                         ("tom","sing,dance","B","668000.88","骨骼惊奇");

insert into marathon values
                                        (1,"尼古拉斯","1993-02-28","2020-1-6 18:20:36","2:28:44");



create table 表名  字段名 数据类型，字段名 数据类型， index 索引名（字段名）unique 索引名（字段名）；
create [unique] index 索引名 on 表名（字段名）;  或 create unique index 索引名 on 表名(字段)；# 在已有表中创建索引：

select name,age,score from class_1 where score >90;     
select * from class_1 where age+1=18;
select * from class_1 where sex="m" and score >90;
select *from class_1 where sex="w" or score>90;
select *from stu.class_1 where not sex="m";
select *from stu.class_1 where age in (8,9);
select *from stu.class_1 where between 8 and 10;
select *from stu.class_1 where age >8;
select * from stu.marathon  where performance <="2:30:00"

select * from stu.marathon where registration_time <now();     #函数充当一个值来使用
select * from stu.cls where name like "L%";   #查询L开头的name 
select * from stu.cls where name like "____";  #查询四个字符的name
select * from stu.cls where name regexp "^A.+";          #不区分大小写，如果不加^号，其他的也匹配到
select * from cls where name regexp "^.{3}$";             #三个字符的name 
select name as 姓名 , age as 年龄 from cls;      #查询后给字段命名---当前语句有效 
select * from cls order by score;          #默认按升序排序
select * from cls where sex="m" order by score desc;     #查询性别是男生的按照降序排序
select *from cls order by age desc,score;               # 年龄相同按 分数降序排序
select *from cls where sex="w" limit 2;                 #只查询女生二条信息                    
select *from cls where sex="m" order by score desc limit 1;     #查询男生分数最高的男生
select * from cls where sex="w" union select *from cls where score >90;    #1语句 联结 2语句查询 
select * from cls where sex="w" union all  select *from cls where score >90;      #默认是去除重复 
select name,age,score from cls union select name,hobby,price from stu.hobby order by name;    # union 加order by 排序 
select name,age,score from cls where score>90 union select name,hobby,price from stu.hobb    
select * from (select *from cls where sex="w") as w where score>80;        #子查询 
select * from (select *from cls where sex="w") as w where score>80;    #需要将子查询结果重命名方便where 引用查询
select * from(select *from cls where sex="w")  as w where s.corce >80;  
select * from cls where score >(select score from cls where name="Abby");     #查询分数比Abby高的值 
select *from cls where score in (select score from cls where name ="Abby" or name="Tom");    #范围内数据
select max(attack) from sanguo;
select max(attack) as 最大 , avg(attack) as 平均 from sanguo; 
select count(*) as from sanguo;           #查询表中有多少条记录 
select  gender from sanguo group by gender;     #按性别分组 ，按什么分组就只能写什么字段
select country,count(*) from sanguo group by country;
select country,avg(attack),count(*) from sanguo group by country;   #按国家分组，计算攻击力平均值
select country,gender,avg(attack)  from sanguo group by country,gender;        #按多个字段分组
select country,count(id) as num from sanguo where gender="男" group by country order by num desc limit 2; 
select country,avg(attack) from sanguo group by country having avg(attack)>180;     #配合group by联合使用 
select distinct country,gender from sanguo;              # distinct 去重复  
select name,attack*2 from sanguo;                    #攻击力双倍

update stu.class_1 set age = 11 where name = "Abby";
update class_1 set sex="m",socre = 91 where name ="tom"
update stu.class_1 set sex="o" where sex is null;          

delete from class_1 where name="joy";

drop index 索引名 on 表名;  #删除索引  


alter table stu.hobby add tel char(11) after price;   #添加字段名
alter table stu.hobby drop level;                 #删除某一字段
alter table stu.hobby modify tel char(16);   #修改字段类型
alter table stu.hobby change tel phon char(16);  #修改字段名，属性要一致
alter table stu.class_1 rename cls;                    #修改表名
alter table marathon modify registration_time datetime default now();  
alter table 表名 drop primary key;   #删除主键 
alter table 表名  add primary key（id）;  #主键索引添加

show index from 表名; 
desc 表名；     ------key 标志为MUL\ UNI

练习：

创建一个数据库 books 使用utf8编码
create database books charset=utf8;

–在数据库下创建一个表 book
use books;
create table book(
id int primary key auto_increment,
title varchar(50) not null,
author varchar(30) not null,
publication varchar(50),
price float default 0,
comment text
);

–字段：id 书名作者出版社价格备注
—类型和约束
—价格30-120
insert into book
(title,author,publication,price,comment)
values
(“边城”,“沈从文”,“机械工业出版社”,36,“小城故事多”),
(“骆驼祥子”,“老舍”,“机械工业出版社”,43,“小城故事多”),
(“茶馆”,“老舍”,“中国文学出版社”,55,“小城故事多”),
(“呐喊”,“鲁迅”,“人民教育出版社”,72,“小城故事多”),
(“朝花夕拾”,“鲁迅”,“中国文学出版社”,53,“小城故事多”),
(“围城”,“钱钟书”,“中国文学出版社”,44,“小城故事多”),
(“林家铺子”,“茅盾”,“机械工业出版社”,51,“小城故事多”),
(“冰心全集”,“冰心”,“人民教育出版社”,47,“小城故事多”);

查询：
select * from book where author in (“鲁迅”,“茅盾”);
select * from book where price=“30”;

例2 ：
在stu 创建数据表 sanguo
use stu;
create table sanguo(
id int primary key auto_increment,
name varchar(30),
gender enum(“男”,“女”),
country enum(“魏”,“蜀”,“吴”),
attack smallint,
defence tinyint
);

insert into sanguo values
(1,“小乔”,“女”,“吴”,188,39),
(2,“周瑜”,“男”,“吴”,190,39),
(3,“吕蒙”,“男”,“蜀”,190,50),
(4,“曹操”,“男”,“吴”,150,100)
(5,“甄姬”,“女”,“吴”,188,39);

函数和存储过程

把多条语句封装在一起（一般使用自定义函数）
函数语法：(常用自定义符 $$ 或 // ) (调用函数 select 函数名(); )

      **delimiter**  自定义符号      ---如果函数体只有一条语句，begin和end 可以省略，同时delimiter 也可以省略
           
               create function 函数名（形参列表） returns  返回类型     --注意是returns
               begin
                        函数体             ---函数语句集，set @a 定义变量  
                        return val  
               end  自定义符号 
      delimiter ;         --把符号改回; 结束 
    释义： delimiter 自定义符号，是为了在函数内些语句方便制作除之；之外的符号作为函数书写结束标志；

    **例:1：**
     #无参数的函数调用 
      delimiter $$
      create function st() returns int  
      begin 
      return (select score from cls order by score desc limit 1);  --#一般数据为增删改  ，查询只能一个字段 return返回只能一个字段
      end $$
      delimiter ;

select st(); # 调用函数

     例2：
      delimiter $$
      create function st2() returns int  
      begin 
      update cls set score =score+1 where id =1; return 1;  
      end $$
      delimiter ;        
      select st2();    # 调用 
     
     #定义变量使用函数  set @a 定义变量       
       delimiter $$
       create function get_name() returns varchar(30)
       begin 
       declare name varchar(30);
       set s=(select 'name' from cls where id =1);          #变量名不可以和查询字段名一致 
       return s;
       end $$
       delimiter ;
       select get_name();  


      #含有参数的函数调用      
       delimiter $$
       create function queryNameByID( uid int ) returns varchar(20)
       begin 
       declare name varchar(30);
       return (select name from cls where id =uid );
       end $$
       delimiter ;
       select queryNameByID(1);

存储过程创建（调用存储过程 call 存储名(); ）
创建存储过程语法与创建函数基本相同，但是没有返回值
delimiter 自定义符号
create procedure 存储过程名(形参列表)
begin
存储过程 —存储过程语句集，set @a 定义变量
end 自定义符号
delimiter ;

       存储过程：若干sql语句组成，如果只有一条语句也可以不写delimiter 和begin ，end 


      例：
          delimiter $$
           create procedure st() 
           begin 
                   select name,age from cls;
                   select name,score from cls order by score desc;
           end $$
           delimiter ;
           call st()           #调用存储

存储过程三个参数的区别：
IN 类型参数可以接收变量也可以接收常量，传入的参数在存储过程内部使用即可，
但是在存储过程内部的修改无法传递到外部
delimiter $$
create procedure st()
begin
select

             OUT 类型参数只能接收一个变量，接收的变量不能够在存储过程内部使用（内部为null）
             但是可以在存储过程中对这个变量进行修改 
           delimiter $$
           create procedure p_out ( out num int ) 
           begin 
                  select  num;
                  set num=100;
                  select num;
           end $$
           delimiter ;
         
           set @num =10;
           call p_out(@num)

3.使用show status 语句查看存储过程和函数的信息
show [function | procedure ]status like “st”; #查看st 函数或者名叫st 的存储创建过程

4.使用show create 语句查看存储过程和函数的定义
语法： show create { procedure | function } 存储过程或者存储函数的名称

5. 查看所有函数或者存储过程  
    select name from mysql.proc where db="stu" and type ="[ function | procedure ]"

6. 删除存储过程或者存储函数 
    drop {procedure | function } [if exists ]  sp_name

事务控制 .

一 . 事务操作
1.开启事务
begin; #直接输入begin; 方法一

                        输入要执行语句
             
              commit;           #事务中sql 命令都执行成功，提交到数据库,结束  ！
              rollback;           #如果有sql命令执行失败，回顾到初始状态，不更改，结束！

    事务操作 不可以恢复 DDL语句

2.事务四大特征（acid）**
1. 原子性（atomicity）
2.一致性（consistency）
3.隔离性（isolation）
数据库允许多个并发事务同时对其数据库进行读写和修改的能力，而多个事务相互独立
隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
隔离级别：
读未提交：read uncommitted
读已提交： read committed
事务A 和事务 B，事务A 提交的数据
可重复读 : repeatable read
串行化： serializable

               隔离级别            脏数据              不可重复读             幻像读
               读未提交            可能                      可能                   可能
               读已提交            不可能                   可能                   可能
               可重复读            不可能                  不可能                可能
               串行化               不可能                  不可能                不可能

4.持久性（durability）
一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也
不会丢失

数据库优化

1. 数据库设计范式：
设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，
这些不同的规范要求被称不同的范式
目前关系数据库有六种范式：
第一范式（1NF）、第二范式（2NF）、第三范式（3NF）
巴斯-科德范式（BCNF）、第四范式（4NF）第五范式（5NF,又称完美范式）

                              一般遵循前三范式就可以

2.MySQL 存储引擎
定义：mysql数据库管理系统中用来处理表的处理器
基本操作
1. 查看所有存储引擎
show engines;
2. 查看已有表的引擎
show create table 表名;
3. 创建表指定
create table 表名(…) engine =MyISAM;
4.已有表指定
alter table 表名 engine = InnoDB;

3.常用存储引擎特点
InnoDB
1.支持行级锁，仅对指定的记录进行加锁，这样其它进程还是可以对同一个表中的其它记录进行操作
2. 支持外键、事务、事务回滚
3. 表字段和索引同存储在一个文件中
1. 表名.frm : 表结构
2.表名.idb : 表记录及索引文件

           **MyISAM**
                 1.支持表级锁，在锁定期间，其它进程无法对该表进行操作。如果你是写锁，则其它进程则读也不允许
                 2. 表字段和索引分开存储
                              1. 表名.frm  : 表结构 
                              2. 表名.MYI : 索引文件（my index）
                              3.表名.MYD : 表记录（my data）

4.如何选择存储引擎
1. 执行查询多的表 MyISAM（使用InnoDB浪费资源）
2. 执行写操作多的表用 InnoDB
例：
create table tb_stu(
id int(11) not null auto_increment,
name varchar(30) default null,
sex varchar(2) default null,
primary key (id)
) ENGINE = MyISAM;

5.字段数据类型选择
优先程度数字> 时间日期> 字符串
同一级别占用空间小的 > 占用空间多的
对数据存储精准不要求 float > decimal
如果很小被查询可以用 TIMESTAMP (时间戳实际是整形存储)

6.键的设置
InnoDB 如果不设置主键也会自己设置隐含的主键，所以最好自己设置
尽量设置占用空间小的字段为主键

7.explain 语句
使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的
SQL 语句的，这可以帮你分析你的查询语句或是表结构或是表结构的性能瓶颈。通过explain
命令可以得到：
表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询
例：
explain select * from 表名 where id <5;
EXPLAIN 主要字段解析：
table ：显示哪张表
type：显示查询使用了何种类型，这是最重要的字段之一，最好到最差的
连接类型为 system 、const 、eq_reg 、ref 、range 、index 和 ALL
一般来说，保证查询至少达到 range 级别，最好能达到 ref

8.SQL 优化
尽量选择数据类型占空间少，在where ，group by ，order by 中出现的频率高的字段建立索引
尽量避免使用 select * … .用具体字段代替 * ，不要返回用不到的任何字段
少使用 like %

   9.表的拆分

数据库备份和用户管理

1. 表的复制
1.表能根据实际需求复制数据
2.复制表是不会出现把 key 属性复制过来
语法：
create table 表名 select 查询语句;

2.数据备份
1.备份命令格式（linux系统终端操作不是mysql终端）
mysqldump -u 用户名 -p 源库名 > 路径/xx.sql

          2. 恢复命令格式（linux系统终端操作不是mysql终端）
           mysql -u 用户名 -p 目标库名  < 路径/xx.sql

3.用户权限管理
开启Mysql 远程连接
1.更改配置文件，重启服务
vim /etc/mysql/mysql.conf.d/mysql.cnf
#bind-address = 127.0.0.1

         2. 修改用户表host值  （%是任何ip ）
             use mysql;
             update user set host = "%" where user = "root";               
         3. 刷新权限 
             flush privileges ;

4. 添加授权用户
1. 用root 用户登录 mysql
2. 添加用户 %是表示自动选择可用IP
create user “用户名”@"%" identified by “密码”; #%是任意主机
3. 权限管理
增加权限：
grant 权限列表 on 库.表 to “用户名”@"%" identified by “密码” with grant option;
删除权限：
revoke insert ,update,select on 库.表 from “user”@"%";
4.刷新权限
flush privileges ;

         5. 删除用户 
             drop user "用户名"@"%";

5.权限列表
all privileges 、 select 、 insert 、 update 、 delete 、 alter 等
. 代表所有库的所有表
库.表

weixin_101

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql

库基本操作show databases; #查看已有库create database 库名 character set utf8; # 创建库use 库名; #切换库select database();
复制链接

扫一扫

专栏目录

mysql

库基本操作

数据表管理

演示例子：

函数和存储过程

事务控制 .

数据库优化

数据库备份 和用户管理

数据库备份和用户管理