数据库（MySQL）学习总结

                        数据库（MySQL）总结

1. 什么是数据库

     数据库：长期储存在计算机内的、有组织的、可共享大量数据集合

关系型数据库：多表之间相互关联的数据模型，这样的数据库类型称为关系型数据库

1. MySQL的安装

    2.1

免安装版数据库---安装

1、解压

2、修改配置文件的mysql路径，和data路径，端口等等1111/*

3、配置环境变量

4、安装mysql服务

5、初始化mysql数据

6、启动mysql服务

7、设置密码，刷新设置

8、登录

 

免安装版,进行配置mysql

以管理员身份运行cmd；

第一步，安装mysql服务 ，后面没有指定服务名，默认为mysql

（1）mysqld -install

第二步，初始化mysql数据库

（2）mysqld --initialize-insecure --user=mysql    

第三步，启动mysql服务

（3）net start mysql

第四步，设置新的mysql密码

（4）mysqladmin -uroot -p password 你的密码

第五步，刷新设置/刷新权限

1. 登陆mysql后，输入 flush privileges;

 

   登录mysql

mysql -u用户名 -h服务器ip -P端口 -p密码

 

   2.2修改密码

修改root密码

1、使用root用户登录mysql

2、set password for 用户名@登录位置 = password('新密码') ;

3、flush privileges ;

这个方法的前提，知道root用户的旧密码

 

遗忘root密码的重置

1、在配置文件注明 skip-grant-tables 这个是略过密码权限认证

2、重启mysql服务

3、登录mysql

4、切换mysql数据库,有一张表user,这张表记录了用户的所有信息

5、更新用户的密码

update user set authentication_string=password('新密码') where user = root ;

6、flush privileges ;

 

1. 语言分类

3.1、数据定义语句(DDL)

Create

  创建数据库

   语法： create database 数据库名 default character set 字符集;

  查看所有数据库

   语法：show databases;

  创建表

create  table 表名(

字段1 数据类型 [约束]  [comment '字段备注'] ,

...

字段n 数据类型 [约束]  [comment '字段备注']

) [ comment '表备注'];

       创建表的同时，创建外键:

create table 表名(

id int(10),

[constraint 约束名] foreign key(id) references 主表(主键字段)

);

-- on delete cascade on update cascade  外键约束的级联权限

-- on delete cascade  级联删除 意味着主表删除主键，子表外键跟着自动删除

-- on update cascade  级联更新 意味着主表更新主键，子表外键跟着自动更新

中括号内容可以没有

-- 常用的数据类型：

整数   int   

浮点型 float  (小数)

字符   char  

字符串 varchar

时间   date/datetime

除了时间类型，其他数据类型都有长度设置

浮点型:float(x,y)  x 代表有效长度, y代表小数部分的长度

--常用约束

1、主键约束     primary key

2、唯一约束     unique

3、非空约束     not null

4、默认值约束   default

5、自增约束     auto_increment

6、外键         foreign key  

    查看某个数据的所有表

方法一：show tables from 数据库名;

方法二：use 数据库 ； show tables;

             查看表结构：

方法一：desc 表名 ; 以二维表的格式查看表字段信息

方法二：show create table 表名 ; 以sql格式查看表的结构

查看某表的所有索引

           show index from  表名;

create 特殊用法：和select一起使用备份表

                 语法：create table 表名 as select 字段1,字段2 ,, 字段n from 旧表 ;

   查询外键约束名：

1、show create table 表名;

2、select * from information_schema.key_column_usage where CONSTRAINT_SCHEMA    = '数据库名' and table_name = '表名';

# CONSTRAINT_SCHEMA: 约束所属schema(database)名称

# TABLE_NAME: 具有约束的表的名称

3、select * from information_schema.table_constraints where table_name = '表名';

Alter

修改表名

语法：alter table 表名 rename to 新的表名;

新增字段

alter table 表名 add 新字段 数据类型 [约束类型] comment '备注' first / after 旧字段;

删除字段

alter table 表名 drop 旧字段;

修改字段名

alter table 表名 change 旧字段 新字段 数据类型 [约束类型] comment '备注' first /after 字段；

新增主键：

alter table 表名 add primary key(字段);

新增唯一键：

alter table 表名 add unique(字段);

新增自增约束：

alter table 表名 modify 主键字段 数据类型 auto_increment ;

新增非空约束：

alter table 表名 modify 字段 数据类型 not null;

默认值约束:

alter table 表名 modify 字段 数据类型 default 默认值;

删除主键：

alter table 表名 drop primary key ;

删除唯一键：

alter table 表名 drop index 字段名 ;

删除非空，默认值,自增：

alter table 表名 modify 字段 数据类型;

创建外键

-- alter table 子表 add [constraint 约束名] foreign key(外键字段) references 主表(主键字段) ;

-- 删除表中的外键

-- 语法：alter table 表名 drop foreign key 外键约束名 ;

Drop

删除表：

drop table 表名；

删除库;

drop database 数据库名；

Rename

语法a：alter table 表名 rename to 新表名;

语法b：rename table 表名 to 新表名;

Truncate

语法：truncate 表名 ;

特点：

1、整表截断性删除

2、不支持事务

3、不支持条件

4、truncate删除，可以重置auto_increment

 

3.2、数据操纵语句(DML)

Insert

语法a：insert into 表名(字段n) values(值n),(值n);

语法b：insert into 表名 values(值n),(值n);

Update

       语法：update 表名 set 字段1=新值,...字段n=新值 where 条件表达式;

Delete

       语法：delete from 表名 where 条件表达式;

delete 和truncate区别：

1、delete的删除机制是逐行删除，truncate时候整表截断性删除

2、delete支持事务，truncate不支持事务

3、delete删除可以带条件，truncate 不支持条件

4、truncate删除，可以重置auto_increment

 

3.3、数据查询语句(DQL) ——Select

单表

   查询表的整张表

语法1：select 字段1，字段2,...字段3 from 表名 ;

语法2：select * from 表名 ;   -- * 通配符，通配表中的所有字段

 

查询符合条件的信息

语法1：select 字段1 ,,, 字段n from 表名 where 条件表达式 ;

语法2：select * from 表名 where 条件表达式 ;

-- 算术运算符

+ - * /  %;

-- 比较运算符

> <  >=  <=  =     !=  <>

-- 逻辑运算符

and 并且 (条件同时成立)   

or  或者  (只要其中一个成立即可)

is null 判断是否为null

 

 

between  and  区间

not  逻辑取反     

in  (匹配集合其中一项即可)

like 模糊匹配/模糊查询   通配符: % 匹配任意长度的字符   _ 匹配一个长度的字符

排序  order by  

-- 排序规则  desc 降序(从高到低)   asc 升序(从小到高)  默认

语法： select * from 表名 where 条件表达 order by 字段1 asc/desc , 字段2 asc/desc , 字段n asc/desc

 

- 分组 group by  根据像个相同的 字段/属性/列 进行统一分组   

-- 分组语法：group by 字段

-- limit 分页   

-- 用法1 limit x , y    从第x行记录后开始，返回后面y行记录

-- 用法2 limit x       返回x行记录

语法： select * from 表名 where 条件表达式 order by 字段 asc/desc limit x,y ;

 

-- having

配合分组的条件过滤，作用等同于where，但是having后面可以进行聚合函数的比较运算,where不能进行聚合函数的运算

-- 语法：select 字段 from 表 where 条件表达式 group by 分组字段 having 条件表达式 order by 字段 limit x, y ;

 

函数：

   聚合函数：COUNT()     统计函数

AVG()、      平均值

SUM()、      求和

MAX()、      最大值

MIN()、       最小值

GROUP_CONCAT()  分组拼接

   

单行函数：  

         -- 处理字符串的函数

upper(字段)      将字符串转换成大写

lower(字段)      将字符串转换成小写

replace( 字段 , '旧内容' , '新内容' )       替换

lpad( 字段 , 指定长度 , '补齐内容')      左补齐，如果长度不够，则在左边 

进行补齐，如果超过长度，则进行截取

 

rpad(字段,长度,'补齐内容')   右补齐，如果长度不够，则在右边进行补齐，如

果超过长度，则进行截取

      left(字段,长度)             左截取，从左边开始，截取指定长度

      right(字段,长度)            右截取 ，从右边开始，截取指定长度

      concat(字段1,字段2,……)   字段拼接将指定字段或者字符串进行拼接

      length()                   计算字段长度 (一个中文字符等于三个长度)  

char_length()              计算字段长度(一个中文字符是一个长度)

   

日期时间函数

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

           时间运算：DATE_ADD(具体时间 , INTERVAL 操作数量 操作类型)

  -- 语法 date_add( 目标字段 , interval 数值(当负数为减法) 时间类型 )

                         操作类型可以是year,month,day,分别代表年、月、日

           时间格式化：date_format( date , format ) 指定时间格式 date_format(字段,'指定                               的格式')

                   格式说明：

%Y   年份  大写Y

%m  月份  小写m

%d  天  小写d

%H  小时  大写H

%i  分钟  小写i

%S   秒数  大写S

 

month(字段) -- 获取月份

year(字段) -- 获取年份

day(字段) -- 获取天数

datediff( date1 , date2 )  返回两个时间点date1和date2之间相差的天数

timestampdiff( 时间单位 , 时间类型1 , 时间类型2)  

                返回两日期时间之间相差的值。

其中时间单位有如下几种：

second  秒

minute 分钟

hour   小时

day   天

week   星期

month   月

quarter   季度

year   年

 

           数值函数

               round( 数值 , 小数点保留位数 ) , 对数值进行截取操作，保留指定小数点 位数，截取的时候会进行四舍五入操作   

               truncate(数值,小数点保留位数),对数值进行截取操作，保留指定小数点位 数，截取的时候不会进行四舍五入操作

               abs(值1)返回值1的绝对值

               floor( 值1 ) 向下取最大整数

               ceiling(值1) 向上取最小整数

mod(值1,值2)取余，返回值1除以值2的余数

          控制流函数

               if( 表达式1 , x , y )   表示如果表达式1成立,则返回x,否则返回y

               ifnull( x , y ) 表示如果x为空,则返回y,否则返回x

               nullif( x , y ) 表示如果x=y,则返回null,否则返回x

               case when：

                     语法1：简单函数

(case 字段 when 值1 then 结果1

when 值2 then 结果2 …… else 结果n end)

 

      表示 如果字段=值1,则返回结果1,字段=值2,则返回结果2 …… 否则返回结果n

                     语法2： 搜索函数

(case when 表达式1 then 值1

when 表达式2 then 值2

。。。。。

else 值n end)

  表示如果表达式1成立,则返回值1,表达式1不成立,表达式2成立,则返回值2 …… 否则返回值n

多表关联

内连接

等值连接

         -- 语法一：select * from 表1 , 表2 where 表1.字段 = 表2.字段 ;

-- 语法二：select * from 表1 inner join 表2 on 表1.字段 = 表2.字段 where 条件表达式 ;

-- 内连接的特征，多表关联的时候，只返回同时满足匹配条件的数据

      不等值连接

          --语法：select * from 表1 , 表2 where 表1.字段 != 表2.字段 ;

自然连接 using(相同字段)

         -- 语法：select * from 表1 inner join 表2 using(两张表都存在的字段);

自连接

          -- 自连接 : 一张表和自己进行关联，表一定要使用别名

-- 语法：select * from 表 别名a , 表 别名b where a.字段= b.字段;

外连接：

     左外连接 left join  ：以左表作为主表，右表作为从表，左表返回所有数据，右表返                       回满足匹配条件的数据，右表不满足条件的记录显示为 null ;

           --   select * from 表一 left join 表二 on 表一字段= 表二字段 ;

右外连接 right join ：以右表作为主表，左表作为从表，右表返回所有数据，左表返                      回满足匹配条件的数据，左表不满足条件的记录显示为 null ;

           --   select * from 表一right join 表二 on 表一字段= 表二字段 ;

 

子查询

   单列单行

       子查询的返回值为一行一列

        ---  select * from 表名 where字段 = ( select  字段 from 表名 );

单列多行

    子查询的返回值为多行一列

select * from表名 where 字段 运算符 ( select 字段 from 条件表达式）

特殊运算符

in   和集合中值做等值比较，满足就返回对应的结果

not in 和集合中值做不等值比较，返回不匹配的结果

any  只要满足其中一个，就返回结果      

all  满足集合里所有的值，才返回结果   

多列多行

 子查询的返回值为多行多列

select * from 表名 where (字段1，字段2) = ((select  字段1,字段二 from 表名)

     

子查询 ，无论有多少个子查询，层级越深，优先级越高

 

 

视图

  

view  视图 虚拟存在

# 简化sql语句，一定上程度保护表结构

# 语法：create view 视图名 as 查询语句

更新视图结构

# 语法：create or replace view 视图名 as 查询语句 ;

        删除视图

drop view 视图名 ;

查看有哪些视图

show table status where comment='view' ;

索引

   索引 index 特殊数据结构

作用 提高数据查询效率

缺点  占用物理存储 , 影响 dml 操作效率

一般创建索引的情况：

1、用于条件过滤的字段

2、主键，唯一键

索引类型：普通索引，组合索引，主键索引 ，唯一索引 ，外键索引

 

创建索引

语法1：create index 索引名 on 表名(字段n);

示例：create index inx_emp_id on emp(id) ;

语法2：alter table 表名 add index 索引名(字段n) ;

示例：alter table emp add index inx_emp_id(id) ;

 查询某表中的所有索引

语法：show index from 表名;

 示例: show index from emp ;  作用等同于 show key from emp ;

删除索引

语法1：drop index 索引名 on 表名 ;

示例：drop index inx_emp_id on emp ;

语法2：alter table 表名 drop index 索引名 ;

示例：alter table emp drop index inx_emp_id ;

 

存储过程

   存储过程是函数的一种

 存储过程是一组为了完成特定功能的SQL语句集合

--  创建存储过程语法：

delimiter //  -- 自定义结束符为//

create procedure 存储过程名(输入输出类型 参数名1 参数类型,输入输出类型 参数名2 参数类型,……)

begin

sql语句集;

end //

    --  使用存储过程

call 存储名称( 参数1,参数2... );

-- 参数类型：

 in     输入

 out    输出

 inout   输入输出

 

 

-- 定义变量

-- 语法1   declare 变量名 数据类型(长度) default 默认值;

declare 申明的是一个局部变量,局部变量的作用域仅限于申明的存储过程里

-- 语法2   set @变量名=值 ;

          set申明的是全局变量，作用于在mysql,但注意，当变量名存在时，这里作用变       成赋值

查看数据库中所有存储过程基本信息  

#：SHOW PROCEDURE STATUS where name = '存储名' and db = '数据库名';

# select * from mysql.proc where db='你的数据库名称' and type ='PROCEDURE';

查看存储过程  

语法：SHOW CREATE PROCEDURE 存储过程名称

 

 

IF控制流语句

# 语法：

IF 条件1 THEN 动作1

     ELSEIF 条件2 THEN 动作2

……

ELSE 动作N

END IF

表示如果条件1成立,则执行动作1 , 否则进行第二个条件,如果条件2成立, 则执行动作2 , …… , 否则执行动作n , 判断过程中, 如果满足某一判断条件后 , 不再进行其他条件的判断 ; 一个条件表达式对应一个then动作，之后要加分号 ;

 

case when 控制流语句

语法1：

case 变量   

 when 值1 THEN 动作1 ;

     when 值2 THEN 动作2 ;

 …… ;

     else 动作N ;

     END case ;

表示如果变量值=值1,则执行动作1,否则如果变量值=值2,则执行动作2,……,否则执行动作N;

      语法2：      

case when 判断表达式1 THEN 动作1 ;

     when 判断表达式2 THEN 动作2 ;

 ……;

     else 动作N ;

     END case ;

表示如果表达式1成立,则执行动作1,否则如果表达式2成立,则执行动作2 , …… , 否则执行动作N ;

 

 

WHILE 循环语句

判断条件成立与否，如果成立则循环执行程序体

 

语法：[循环别名:]WHILE 条件 DO

     执行动作;

     END WHILE [循环别名];

 

REPEAT循环语句

先执行一次循环体，之后判断条件成立与否，如果成立退出循环

语法： [循环别名:]repeat

执行动作 ;

until 终止条件

end repeat [循环别名] ;

-- while 和  repeat区别

-- while是先判断条件，如果条件成立，则执行循环

-- repeat 是先执行一次，然后在判断条件是否成立

 

 

3.4、事务控制语句(TC)

Commit    提交

Rollback    事务回滚

Savepoint   存储点

 

3.5、数据控制语言(DCL)

        Grant  授权

Revoke   解除授权