Mysql入门

数据库

数据库:存储数据的仓库

1.数据库的数据类型

tinyInt	微整型：很小的整数(占 8 位二进制
smallint	小整型：小的整数(占 16 位二进)
mediumint	中整型：中等长度的整数(占 24 位二进制)
int(integer)	整型：整数类型(占 32 位二进制)
float	单精度浮点数，占 4 个字
double	双精度浮点数，占 8 个字节
time	表示时间类型
date	表示日期类型,格式为yyyy-MM-dd,中有年月日,没有时分秒
datetime	同时可以表示日期和时间类型
char(m)	固定长度的字符串，无论使用几个字符都占满全部，M为 0~255 之间的整 数
varchar(m)	可变长度的字符串，使用几个字符就占用几个，M为 0~65535 之间
tinyblob Big Large Obj	允许长度 0~255 字节
blob	允许长度 0~65535 字节
mediumblob	允许长度 0~167772150 字节
longblob	允许长度 0~4294967295 字
tinytext	允许长度 0~255 字
text	允许长度 0~65535 字
mediumtext	允许长度 0~16777215
longtext	允许长度 0~4294967295

2.数据库的注释

-- 单行注释
/*
    多行注释
*/
#特殊注释

3.DDL

库的增删查改

查询当前所有数据库名称

show databases;

创建数据库

create database 数据库名;

判断是否存在数据库,如果不存在,创建数据库

create database if not exists 数据库名;

创建数据库并指定字符集

create database 数据库名 character set 字符集;

删除数据库

drop database 数据库名;

判断是否存在数据库,如果存在,删除数据库

drop database if exists 数据库名;

查询数据库的默认字符集格式

show create database 数据库名;

默认查询当前数据库中所有的带字符集的字符格式

show variables like '%character%';

修改数据库默认的字符集

alter database myjavaee character set 字符集格式

使用数据库/改变数据库

use 数据库名;

表的增删查改

创建表

create table 表名(
    字段名称1 字段类型1,
    字段名称2 字段类型2,
    字段名称3 字段类型3,
    ...                 -- 注意:最后一个字段类型后面不需要在加逗号
)

查询这个库中的所有表

show tables ;

查询表的结构

desc 表名;

修改表的字段名称

alter table 表名 change 旧字段名称 新字段名称 数据类型;

修改字段的数据类型

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

添加一个新的字段

alter table 表名 add 新的字段名称 数据类型;

将某一个字段删除

alter table 表名 drop 字段名称;

复制一个新表,与之前的表结构相同

create table 新的表名 like 之前的表名 ;

修改表的名称

alter table 旧表名 rename to 新的表名;

删除表

drop table 表名;

判断是否存在该表,如果存在则删除

drop table if exists 表名;

4.DML

表的记录的操作语句

查询全表

-- 最基本的查询语句,开发中不建议使用
select * from 表名;

给表中插入数据

-- 注意事项:插入字段值的时候,必须和当前字段的先后顺序对应上
-- 语法1,插入一条记录的部分字段
insert into 表名(字段名称1,字段名称2...) values(值1,值2...);
-- 语法2,插入一条记录的全部字段
insert into 表名 values(字段名称1的值,字段名称2的值....);
-- 语法3,一次性插入多条记录
insert into 表名 values(字段名称1的值,字段名称2的值....),(字段名称1的值,字段名称2的值....);

修改表的语句

-- 修改语句,并且加入约束条件 where 条件表达式
update 表名 set 字段名称=值 where 条件语句;
-- 修改字段多个字段
update 表名 set 字段名称1=值1,字段名称2=值2,... where 条件语句;
-- 批量修改
update 表名 set 字段名称=值;

删除语句

-- 语法1,删除语句,删除单条记录
delete from 表名 where 条件表达式
-- 语法2,删除全表记录
delete from 表名;
-- 语法3,删除全表记录
truncate table 表名;
/*
语法2与语法3的区别:
    语法2仅仅只删除表的记录,不会删除表的结构,对自增长约束的字段不影响
    语法3是删除了这个表,并且再重建一张一模一样的空表,与之前的表结构一样,会影响一些自增长的约束字段
*/

5.DQL

查询语句

-- 查询全表
select 全部字段名称 from 表名;
-- 查询指定字段
select 字段名称1,字段名称2,.. from 表名;
-- 查询时指定字段的的时候指定别名  :字段名称 as 别名名称(as可以省略)
select 
    字段名称1 as '别名1',
    字段名称2 as '别名2',
    ...  -- 最后一个后面不加逗号
from
    表名;
--查询时使用表的别称.字段名称;多表查询中使用
select 
    表的别称.字段名称1 '别名1',
    表的别称.字段名称2 '别名2',
    ...   -- 最后一个后面的逗号不加
from
    表名 表的别称;
-- 查询字段时去重
select 
    distinct(字段名称)
from 表名;
-- 查询时求和
select 
    字段名称1 '别名1',
    字段名称2 '别名2',
    (字段名称3+字段名称4) '总分',
    ...
from 表名;
/*
注意事项:
    (1)两个数据类型一致
    (2)如果有一个字段的值为null,没有意义
    (3)解决方法:字段名称1+ifnull(字段名称2,预期值);
*/

运算符

-- 比较运算符,也可以使用!=
    >、<、<=、>=、=、<>(不等于,等价于!=)
-- 并列关系,也可以使用java中的运算符 &&
and
between and
-- 或,也可以使用java中的运算符 ||
or  
-- 非 也可以使用java中的运算符 !
not
-- 判断为空的字段名称
某个字段名称 is null;
-- 判断不为空的字段名称
某个字段名称 is not null;
-- in关键字,in里面的每一个数据都会作为一次条件,只要满足条件就会执行
select
    *
from
    表名
where
    字段名称 in(数据1,数据2,...);

模糊查询

-- %:包含任意字符
-- _:包含单个字符
--查询字段包含空格的人的信息
select
    *
from
    表名
where
    字段名称 like '% %';
-- 查询字段包含三个字符的人的信息
select 
    *
from
    表名
where
    字段名称 like '___';

聚合函数

-- count(字段名称):查询总记录数
-- max(字段名称):求某个字段列表中的最大值
-- min(字段名称):求某个字段列表中的最小值
-- sum(字段名称):对某个字段列表进行求和
-- avg(字段名称):求某个字段列表的平均值
select
    count(字段名称),
    max(字段名称),
    min(字段名称),
    sum(字段名称),
    avg(字段名称)
from
    表名;

排序查询

-- asc:升序排序,默认排序
-- desc:降序排序
select 
    *
from
    student
order by
    字段名称 排序方式;

分组查询

-- group by:分组查询
/*
注意事项:
    (1)where语句要在group by之前
    (2)group by的后面不能使用聚合函数
*/
-- 按性别分组
select
    sex
from
    表名
group by
    sex;

筛选

-- having :筛选,过滤
/*
having与group by的区别:
    (1)having后面可以跟聚合函数;group by后面不能跟聚合函数
    (2)如果存在where语句,先where语句,接着group by 最后才是having
*/
select
    *
from
    表名
where
    条件表达式
group by                                                                                                                                                                                                                                                                                                            
    字段名称
having
    筛选条件;

分页查询(*)

-- limit :分页查询
select 字段列表 from 表名 limit 起始行数,每页显示的条数;
-- 起始行数(起始索引):从0开始计算=(当前页码-1)*每页显示的条数
-- 查询当前表的第一页数据,每页显示2条数据
select
    *
from
    表名
limit (1-1)*2,2;

数据库的约束:约束用户操作表时候的行为,防止无意义的数据在表中存在

1. 默认约束:default

   创建的时候给字段添加默认值,当使用部分字段插入数据的时候,没有插入的字段就为空,此时没有意义,所以就加入一个默认约束来加入这个字段的默认值.

2. 非空约束:not null ,当一些字段的必须存在值,这只非空约束

   -- 添加非空约束
   alter table 表名 modify 字段名称 数据类型 not null;
   -- 删除非空约束
   alter table 表名 modify 字段名称 数据类型;

3. 唯一约束:unique ,字段值不能重复,如每个人的qq号,手机号等等..

   -- 添加唯一约束
   alter table 表名 modify 字段名称 数据类型 unique;
   -- 删除唯一约束
   alter table 表名 drop index 字段名称;

4. 主键约束:primary key(非空且唯一)

   -- 添加主键约束
   alter table 表名 modify 字段名称 数据类型 primary key;
   -- 删除主键约束
   alter table 表名 drop primary key ;
   ​

5. 自增长约束:auto_increment

   它默认从字段行开始,从0开始自增1,一直往前移动;它通常与主键一起使用,都是作用在非业务字段上,如id等

6. 外键约束:foreign key ,可以进行级联操作

   -- 创建一个员工表(外键所在的表:从表(子表))
   create table employee(
       id int primary key auto_increment,  -- 员工编号,设置主键约束,和自增长约束
       name varchar(50),                   -- 员工姓名
       age int,                            -- 员工年龄
       gender varchar(5),                  -- 员工性别
       dept_name varchar(10)               -- 员工所属部门名称
       constraint dept_emp_fk   -- 声明和设置外键名称
       foreign key (dept_id)    -- 作用在外键上
       references dept(id)      -- 关联与主表的主键id
   );
   -- 向员工表插入记录
   insert into employee values
   ('张三',35,'男','开发部'),('李四',40,'男','运维部'),('王五',30,'测试部');
   ​
   -- 创建一个部门表(主表)
   create table dept(
       id int primary key auto_increment, -- 部门编号,设置主键约束,并且设置自增长约束
       dept_name varchar(20)              -- 部门名称
   );
   -- 向部门表中插入记录
   insert into dept values('开发部'),('运维部'),('测试部');
   ​
   -- 外键约束的添加
   alter table employee add
       constraint 外键名称
       foreign key(dept_id)
       references dept(id);
       
       
       
   -- 删除外键约束
   alter table 表名 drop foreign key 外键名称 ;

级联操作:CASCADE

用户在操作主表的时候,跟主表相关的从表的数据也随之更改;如果不使用操作,针对修改或删除都应该先执行从表然后在执行主表.

-- 级联删除
on delete cascade
-- 级联修改
on update cascade
​
-- 与外键约束一起使用

6.数据库的备份与还原

数据库的备份

-- 方式1:命令行方式
    win+r 进入dos控制台 , 输入:mysqlump -uroot -p数据库密码 数据库 > 要存放的地址
-- 方式2:图形界面化方式

数据库的还原

-- 方式1:命令行方式
    (1)进入mysql自动控制台或dos控制台登录mysql
    (2)创建一个新的数据库,使用use切换到这个数据库
    (3)输入souce 备份数据库的地址
-- 方式2:图形界面化
    (1)在sqlYog或者navicat里使用
    (2)创建一个新库,右键选择import--->Execute Sql Scripts:执行脚本
    (3)选择备的地址,执行sql文件

7.表与表之间的关系

(1)一对多

(2)多对多:采用中间表完成

(3)一对一

8.数据库的三大范式

第一范式(1NF):列不可再分

数据库的表中的每一列是不可在拆分的原子项数据,是一个独立的列

第二范式(2NF):属性完全依赖于主键

在第一范式的基础上,每一张表的非主键字段不许完全依赖于主键字段,不能产生部分依赖

第三范式(3NF):属性不依赖与其他非主属性,直接依赖与主键

在第二范式的基础上,属性完全依赖于主键,非主键字段不能产生传递依赖

外键约束可以消除传递依赖.

9.多表查询

笛卡尔乘积:

select * from 表名1,表名2;
-- 查询两个表的时候,每有连接条件就会造成"笛卡尔乘积",即查询的结果为表1中的记录和表2中的记录的乘积.

多表查询的步骤:

(1)确定要查询的表

(2)确定要查询的字段

(3)确定连接条件

内连接查询

(1)隐式内连接:where语句作为条件

select 字段列表 from 表名1,表名2,... where 连接条件 ;

(2)显示内连接

select 字段名称 from 表名1 inner join 表名2 on 连接条件 ;

(3)建议:多表查询时,连接条件不多的情况下,尽量使用隐式内连接的where语句

(4)内连接查询的缺点:当从表插入一条新的记录时,主表还没有分配数据时,此时只能查询到两个表的交集

外连接查询

(1)左外连接(推荐)

select 字段列表 from 表名1 left outer join 表名2 on 连接条件 ;

(2)右外连接

select 字段列表 from 表名1 right outer join 表名2 on 连接条件 ;

子查询:select语句嵌套select语句

(1)单行单列:利用where条件后面携带运算符,如:

-- 查询一个员工表中最高工资的员工信息
select * from 员工表名 where 工资字段 = (select max(工资字段) from 员工表名) ;

(2)多行多列:查询数据时利用or或者in语句,如

-- 查询员工表总

(3)复杂查询

​

10.视图

创建视图

create view 视图名 as select 字段列表 where 限制条件 ;

查询所有表,并查询表的类型

show full table

删除视图

drop view 试图名 ;

修改视图

update 视图名 set 字段名称=值 where 条件语句 ;
-- 注意事项:修改的数据要在视图的限制范围内

给视图添加数据,并加入检查语言

alter view 视图名 as select 字段列表 where 限制条件 with check option ;
-- with check option  :检查语言,避免添加记录时不满足条件时报错

查询视图表

select * from 视图名 ;

视图不可修改的情况:

(1)使用了聚合函数

(2)视图中不能使用系统变量或用户变量

(3)临时表中不能创建视图

11.数据库的事物

事务:在执行业务功能时,这个业务功能同时执行多个sql语句时,将这多个sql语句的执行过程看成一个整体,要么同时执行成功,要么同时执行失败

传统型事务的特点:ACID

(1)原子性:事务不可再拆分,是一个整体\

(2)一致性:事务在操作业务数据前后总量保持不变

(3)隔离性:业务与业务之间时独立的,不能互相影响

(4)持久性:对数据的修改,一旦提交commit,对数据的操作性是永久的.

事务的隔离级别

(1)读未提交:一个事务读取带另一个没有提交事务的数据,会出现脏读,安全性最差,但是执行效率最高

read uncommitted

(2)读已提交:可以防止脏读,但是会出现不可重复读,即一个事务中读取的两次数据是不一致的

read committed

(3)可重复读:mysql的默认级别,能够防止脏读,不可重复读

repeatable read

(4)串行化:只要一个事务没有提交,另一个事务是不能操作的,安全性最高

serializable

事务隔离级别的查询

select @@transaction_isolation
-- mysql的默认事务级别是: repeatable read

设置事务的隔离级别

set global transaction_isolation level 级别的名称 ;