数据库学习之Mysql

今天整理的是自己平时使用Mysql数据库中常用的Mysql的命令，分为基础和加强两部分，有些需要注意的地方我加粗加黑了字体和标记了醒目的颜色。

基础部分

一、数据库管理

1.查询所有数据库

show databases;

--information_schema mysql元数据，基础数据

--mysql mysql配置数据库，其中包含用户信息。（用户名和密码，权限管理）

--performance_schema mysql数据库软件的运行数据，日志信息，性能数据

--test 测试数据库，空的

2.创建数据库

create database 数据库名

default character set utf8; --指定默认字符集

3.查看数据库默认字符集

show create database 数据库名

4.删除数据库

drop database 数据库名

5.修改数据库

alter database 数据库名 default character set 字符集类别

二、表管理

1.查看所有的表

先选择数据库

select 数据库名

show tables

2.创建表

create table 表名(

字段 字段类型,

字段 字段类型

);

3. 查看表结构

desc 表名

4.删除表

drop table 表名

5.修改表

--添加字段

alter table 表名 add column 字段 字段类型

--删除字段

alter table 表名 drop column 字段

--修改字段类型

alter table 表名 modify column 字段 新字段类型

--修改字段名称

alter table 表名 change column 旧字段 新字段 新字段类型

--修改表名称

alter table 表名 rename to 新表名

三、增删改数据

1.增加数据

--插入所有字段，一定要依次按顺序插入

insert into 表名 values(字段1值,字段2值)

--插入部分字段

insert into 表名(字段1) values(字段1值)

2.修改数据

--修改所有数据

update 表名 set 字段名=字段值

--带条件的修改

update 表名 set 字段名=字段值 where 字段名=条件值

3.删除数据

--删除所有数据

delete from 表名

--带条件删除

delete from 表名 where 字段名=条件值

delete from 可以全表删除 可以带条件删除 只能删除表的数据，不能删除表的约束 删除的数据可以回滚(事务)

truncate table 表名 可以全表删除 不可以带条件删除 既可以删除表的数据，也可以删除表的约束 删除的数据不可以回滚

四、查询数据

1.查询所有列

select * from 表名

2.查询指定列

select 字段名1,字段名2 from 表名

3.查询时添加常量列

select 字段名1,字段名2 新加列内容 as 新加列名 from 表名

4.查询时合并列

select (字段1+字段2) as 别名 from 表名

--合并列只能合并数值类型的字段

5.查询时去除重复记录

select distinct 字段名 from 表名

--或者 select distinct(字段名) from 表名

6.条件查询

--逻辑条件

and or

--比较条件

> < >= <= = <>(不等于) between and(包前包后) (>= <=)

--判空条件

null 空字符串

is null is not null =' ' <>' '

--null表示没有值

--空字符串是有值的

--模糊条件

like

--%任意个字符

--_ 一个字符

聚合查询

--聚合函数

sum() avg() max() min() count()

--count()函数统计的数量不包含null的数据

分页查询

--limit 起始行,查询几行

分页查询当前页数据的sql -->select * from 表名 limit (当前页-1)*每页显示多少条,每页显示多少条

查询排序

order by 字段名 asc/desc

--asc:顺序，正序 数值：递增 字母：a-z

--desc:和asc相反

分组查询

group by

分组查询后筛选

having

加强部分

一、数据约束

1.默认值

default 默认值

2.非空

not null

--非空字符必须赋值且不能赋null

3.唯一

--unique

--可插入null且可以插入多个null

4.主键

primary key

--非空+唯一

5.自增长

auto_increment

6.外键

主表

create table 表名1（

字段1 字段类型 primary key,

字段2 字段类型

)

副表/从表

create table 表名2(

字段3 字段类型 primary key,

字段4 字段类型,

constraint 外键名称 foreign key(字段3) peferences 表名1(字段1)

^

|

外键

)

--注意

1）被约束的表称为副表，约束别人的表称为主表，外键设置在副表上的！！！

2）主表的参考字段通用为主键！

3）添加数据： 先添加主表，再添加副表

4）修改数据： 先修改副表，再修改主表

5）删除数据： 先删除副表，再删除主表

级联操作

级联修改 on update cascade

级联删除 on delete cascade

注意： 级联操作必须在外键基础上使用

三大范式

**************************************

第一范式： 要求表的每个字段必须是不可分割的独立单元

                                                                 student     :   name              -- 违反第一范式

                                                                                               张小名|狗娃                                            

                                                                 sutdent    ： name    old_name    --符合第一范式

                                                                                             张小名    狗娃

 

                   第二范式： 在第一范式的基础上，要求每张表只表达一个意思。表的每个字段都和表的主键有依赖。

                                              

                                               employee（员工）: 员工编号  员工姓名 部门名称   订单名称  --违反第二范式

 

                                               员工表：员工编号  员工姓名 部门名称  

 

                                               订单表：  订单编号  订单名称             -- 符合第二范式

                                                    

                   第三范式： 在第二范式基础，要求每张表的主键之外的其他字段都只能和主键有直接决定依赖关系。

 

                                               员工表： 员工编号（主键） 员工姓名  部门编号  部门名 --符合第二范式，违反第三范式                                                                                                                                                       （数据冗余高）

 

                                               员工表：员工编号（主键） 员工姓名  部门编号    --符合第三范式（降低数据冗余）

                                               部门表：部门编号  部门名

*******************************************************************

多表查询规则：1）确定查询哪些表   2）确定哪些哪些字段   3）表与表之间连接条件 (规律：连接条件数量是表数量-1)

--内连接查询

select 字段1,字段2 from 表1 inner join 表2 on 表1.字段 = 表2.字段

或者 select 字段1,字段2 from 表1,表2 where 表1.字段 = 表2.字段

--左外连接查询：使用左边表的数据去匹配右边表的数据，如果符合连接条件的结果则显示，如果不符合连接条件则显示null

left outer join on

--右外连接查询：使用右边表的数据去匹配左边表的数据，如果符合连接条件的结果则显示，如果不符合连接条件则显示null

right outer join on

二、存储过程

--1）执行效率非常快！存储过程是在数据库的服务器端执行的！！！

    2）移植性很差！不同数据库的存储过程是不能移植。

-- 创建存储过程

DELIMITER $       -- 声明存储过程的结束符

CREATE PROCEDURE pro_test()           --存储过程名称(参数列表)

BEGIN             -- 开始

         -- 可以写多个sql语句;          -- sql语句+流程控制

         SELECT * FROM 表名;

END $            -- 结束 结束符

 

-- 执行存储过程

CALL pro_test();          -- CALL 存储过程名称(参数);

 

参数：

IN：   表示输入参数，可以携带数据带存储过程中

OUT： 表示输出参数，可以从存储过程中返回结果

INOUT： 表示输入输出参数，既可以输入功能，也可以输出功能

*************************

--  全局变量（内置变量）：mysql数据库内置的变量 （所有连接都起作用）

        -- 查看所有全局变量： show variables

        -- 查看某个全局变量： select @@变量名

        -- 修改全局变量： set 变量名=新值

        -- character_set_client: mysql服务器的接收数据的编码

        -- character_set_results：mysql服务器输出数据的编码

       

--  会话变量： 只存在于当前客户端与数据库服务器端的一次连接当中。如果连接断开，那么会话变量全部丢失！

        -- 定义会话变量: set @变量=值

        -- 查看会话变量： select @变量

       

-- 局部变量： 在存储过程中使用的变量就叫局部变量。只要存储过程执行完毕，局部变量就丢失！！

三、触发器

-- 需求： 当向员工表插入一条记录时，希望mysql自动同时往日志表插入数据

-- 创建触发器(添加)

CREATE TRIGGER tri_empAdd AFTER INSERT ON employee FOR EACH ROW    -- 当往员工表插入一条记录时

     INSERT INTO test_log(content) VALUES('员工表插入了一条记录');

    

-- 插入数据

INSERT INTO employee(id,empName,deptId) VALUES(7,'扎古斯',1);

INSERT INTO employee(id,empName,deptId) VALUES(8,'扎古斯2',1);

 

-- 创建触发器(修改)

CREATE TRIGGER tri_empUpd AFTER UPDATE ON employee FOR EACH ROW    -- 当往员工表修改一条记录时

     INSERT INTO test_log(content) VALUES('员工表修改了一条记录');

    

-- 修改

 UPDATE employee SET empName='eric' WHERE id=7;

 

-- 创建触发器(删除)

CREATE TRIGGER tri_empDel AFTER DELETE ON employee FOR EACH ROW    -- 当往员工表删除一条记录时

     INSERT INTO test_log(content) VALUES('员工表删除了一条记录');

 

-- 删除

 DELETE FROM employee WHERE id=7;

四、Mysql权限问题

-- mysql数据库权限问题：root ：拥有所有权限（可以干任何事情）

-- 权限账户，只拥有部分权限（CURD）例如，只能操作某个数据库的某张表

-- 如何修改mysql的用户密码？

-- password: md5加密函数(单向加密)

 SELECT PASSWORD('root'); -- *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B

 

-- mysql数据库，用户配置 : user表

USE mysql;

 

SELECT * FROM USER;

 

-- 修改密码

UPDATE USER SET PASSWORD=PASSWORD('123456') WHERE USER='root';

 

-- 分配权限账户

GRANT SELECT ON day16.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';

GRANT DELETE ON day16.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';