MySQL自学笔记

初始mysql数据库

alter table 表名 convert to character set gbk;

1.概念

1.1 DB database数据库 存数据

2. 数据库分类：

关系型数据库(sql)：

mysql,Orcale,Sql Server,DB2,SQLlite

非关系型数据库（NoSQL）not only

Redis，MongDB

3.DBMS（数据库管理系统）处理数据

数据库的管理软件，科学有序维护获取数据

~~ Ctrl+R mspaint 画图工具~~

4.MySQL 简介：

前瑞典MySQL AB公司

后属于Oracle旗下产品

navicat软件使用

查询IP地址：win+R ipconfig

2操作数据库

操作数据库>表>表中数据

mysql不区分大小写、

2.1操作数据库（了解）

创建 create database [if not exists]库名；

删除数据库 drop database [if exists] kuming;

使用数据库 use 库名；-------库名为特殊字符用``票号

查看所有的数据库 show 库名；

数据库的列类型

数值：从小到大

·

create databases kuming;
show databases;
drop database kuming;

---

2.2 数据库列的类型

数值

• tinyint 十分小的数据 一个字节
• smallint 较小的数据 2个字节
• mediumint 中等大小的数据 三个字节
• int 标准的整数 4个字节 常用的int
• bigint 较大的数据 8个字节
• float 浮点数 8个字节
• double 浮点数 8 个字节
• decimal 字符串形式的浮点数 金融计算一般使用

字符串

• char 字符固定大小 0~255
• **varchar 可变字符串 0~65535 常用String **
• tinyint 微型文本 2^8-1
• text 文本串 2^16 -1

时间日期 Java.util.Date

• data YYYY-MM-DD 日期 格式
• time HH：mm：ss 时间格式
• datatime YYYY-MM-DD HH: mm:ss 最常用的时间格式
• timestamp 时间戳 1970.1.1到现在的毫秒数
• year 年份表示

null

• 没有值 未知
• 不要用null运算

2.3数据库的字段属性（重点）

Unsigned

• 无符号的整数
• 声明该列不能为负数

zerofill

• 0填充的
• 不足的位数，使用0来填充，int（3），5……005

自增

• 通常理解为自增，自动在上一条记录的基础+1 （默认）
• 通常用来设置唯一的主键~ index 必须是整数类型
• 可自定义设置主键自增的起步值和步长

非空 null not null

• 假设设置 not null，如果不给它赋值,就会报错！
• null，如果不填写值，默认就是null

默认

操作表

# 注意点 ，使用英文（） 表的名称和字段尽量用``
#字符串使用单引号括起来！
#所有的语句后加  ，----英文 
#最后一个不用加
/* comment  '名' 注释
auto_increment   自增
default  '名' ///  default null   默认值
primary key  主键  一般一个表有一个唯一的主键
*/
create table if not exists `stedents`(
`id` int(4) not null auto_increment  comment `学号`,
 `name` varchar(30)  not null default '匿名'
primary key(`id`)

)engine=innodb  default charset=utf-8;

create table [if not exists]  `表名`(
`字段名` 列类型 [注释][索引][注释],
    `字段名` 列类型 [注释][索引][注释],
    ....
    `字段名` 列类型 [注释][索引][注释]
)[表类型][字符集设置][注释]

3.mysql数据管理

3.1外键（了解）

创建：

方式一：创建表时创建

方式二;创建表的时候没有外键

alter table 表 ADD

alter table 'employees'
add constrainf 'FK_departmentid' foreing
key('departmemtid') references 'departments' (departmentid);

4.DQL数据查询

4.1指定查询字段

语法： select 字段，… from 表名

字段名不见名知意可以起别名（字段名as新名from表名as新表名

select 字段 from表明

查询全部学生：select *from student;

查询指定字段 select ‘字段名’ ，’字段名‘ ，… from student

给字段起别名，也可以给表其别名：select ’Name’ AS 姓名，‘ID’ AS 学号 from student AS 学生表

##函数 拼接字符串concat(a,b)
select concat ('姓名:',Name) AS 新姓名  froms tudent

--发现重复数据，去重  ==distinct==
 select distinct 'ID' from employees;

4.2数据库的列（表达式）

语法：select 表达式 from表名

select version() --查询版本
select 299*3-3 as 计算结果     ---用来计算
select @@auto_increment_increment  ---咨询自增步长
------学习成绩+1分
select 'studentscores'+1 AS '提分后'from result

4.3 where语句

作用：检索数据中符合条件的值

搜索的条件由一个或者多个表达式组成！结果 布尔值

1.逻辑运算符

运算符	语法	描述
and &&	a and b a&&b	与
or ||	a or b a||b	或
not ！	not a ! a	非

--查询收入在两千~3000	
select employeesid,'income' from salary
where income>=2000 and  income>=3000；
------模糊区间查询
select employeesid,'income' from salary
where  income between 2000 and  3000；
-----取反
select employeesid,'income' from salary
where  income！=2000；

模糊查询：比较运算符

运算符	语法	描述
is null	a is null	如果操作符为null,结果为真
is not null	a is not null	如果操作符不为null,结果为真
between	a between b and c	若a在b和c之间，则结果为真
like	a like b	SQL匹配，如果a匹配b，则结果为真
in	a in(a1,a2,a3)	假设a在a1,或者a2…其中的某一个值有，结果为真

=====模糊查询=====
select 'employeeid' 'Name' from employees
where Name like '王%'

单引号不要忘写

====null not null====
--查询note为空的的部门--
select note from departments
where departmentid=''OR  departmentid IS NULL
--where 'zifucuan' is null

4.4联表查询join

join（ 链接的表） on （判断的条件）连接查询

where等值查询

思路：

1. 分析需求，分析查询的字段来自哪些表（连接查询）

2. 确定运用哪种连接方式 7种

   确定交叉点 （数据相同的）

   判断 kc.课程号=xs

----全连接查询----
select distinct kc.课程名,xs_kc.课程号
from kc ， xs_kc
where kc.课程名=xs_kc.课程号；
-----join连接----
---1.内连接  笛卡尔积  inner join
---2.

on用于批量，where用于单个

on不会筛选主表信息，

操作	描述
inner join	IF 表中至少有一个匹配，就返回行
left join	即使右表中没有匹配，也会从左表中返回所有的值
right join	即使左表中没有匹配，也会从右表中返回所有的值 （没有–null值）

from a left join b on（条件）

自连接----自己的表和自己的表链接

核心：一张表拆为两张一样的表 即可

操作：查询对应子类与父类的关系

--查询父类信息
select a.'categoryid' AS'父栏目', b.'categoryid' AS'子栏目'
from 'category' AS a,'category' AS b
where a.'categoryid' = b.'categoryid' ;--链接条件

4.6子查询

where （值是计算出来的）

本质：在where语句中嵌套一个子查询语句

(由立即外)

where （select* from）

5.mysql函数

5.1常用函数

#数学运算
select ABS(-5) #绝对值
select ceiling(9.8)#向下取整
select floor(98.8)#向上取整
select rand()  #返回一个0~1之间的随机数
select sing(10)#判断一个数的符号  0-0  负数返回-1  正数返回1
#字符串函数
select char_length('加油')#字符串长度
select concat('我','是','你')# 拼接字符串
select insert('我like你',2,4,'喜欢')# 查询，从某个位置开始替换某个长度
select lower('Txy')#转成小写字母
select upper('Txy')#转成大写字母
select instr('txy','t')#返回第一出现的子串的索引
select replace('坚持就能成功','坚持','努力')#替换出现的制定字符串
select substr('坚持就能成功',4,3)#  返回指定的字符串（原字符创,截取的位置，截取的长度）
select reverse('清晨我上马')#反转字符串


#查询姓 周 的同学，并将姓改为  zhou  显示在屏幕
select replace (NAME,'周','zhou') from employees
where name  like '周%';

#时间 和日期函数!!!!
select current_date()   #获得当前日期
select CURDATE()  #获得当前日期
select now()      #获得当前的时间  
select localtime  #本地时间
select sysdate()   #系统时间

#系统 
select system_user()
select user()
select version()

5.2聚合函数

# 聚合函数

#sount（）三种   都能统计表中的数据（想查询一个表中有多少记录，用count（））
select count(name) from employees;
#count(指定列) 会忽略所有的null值
select count(*) from employees;
#count(*) 不会忽略null值   计算行数
select count(1) from employees;
#count(1) 不会忽略null值，本质 计算行数

select sum(`income`) as 总和 from salary;

复习

顺序很重要
select  去重  要查询的字段 
from  表名（注意：表和字段可以区别名）
xxx join  要连接的表 on 等值判断
where （具体的值 or 子查询语句）
group by (通过哪个字段排序的字段名)
Having （过滤分组后的信息，条件和where是一样的，位置不同）
Order By （通过哪个字段排序）[降序/升序]
limit startindex,pagesixe

事务

6.1 什么是事务

要么都失败，要么都成功

将一组Sql 放在一个批次

事务ACID  
1. 原子性（Atomicity）  要么都失败，要么都成功
2. 一致性 （Consistency）事务前后数据要保持一致
3. 持久性（Durability）
表示事务结束后的数据不随着外界原因导致数据丢失
事务没有提交，数据恢复到原状
         事务没有提交，数据持续化数据库
         事务一但被提交，事务不可逆
4.隔离性（Isolation）
针对多个用户同时操作，主要是排除其他事务对本次事务的影响
多个用户同时操作   
        事务隔离级别（隔离所导致的文问题）：
       脏读：
指一个事务读取了另外一个事务未提交的数据。
        不可重复读：
在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）
       虚读(幻读)
是指在一个事务内读取到了别的事务插入的数据，导致前后读取数量总量不一致。    

mysql 是默认开启事务，自动提交的

#mysql 是默认开启事务，自动提交的
#修改系统变量
set @@autocommit = 0 #关闭
set @@autocommit = 1 #开启（默认）


#手动处理事务
set autocommit = 0 #关闭自动条件
#事务开启（显式）：
start transaction
#或者（等价）
begin work
#标记一个事务的开始，从这个之后的sql窦在同一个事务内
#提交：持久化（成功！）
commit
# 回滚：回到原来的样子
rollback
#事务结束
set autocommit = 1 #开启自动提交
savepoint 保存点名  #设置一个事务的保存点

存储过程

--准备：声明结束符
delimiter $$

游标

#声明
declare 游标名 cursor for select 语句
Declare lp_flag boolean default true; #标记
将 select 语句的结果值交给游标（名）
--打开 open 
--取值 -用变量接收 fetch 游标名 into 局部变量

/*注意：
1.当fetch到底 报错no data
2.fetch 接收的内容要匹配
*/
#改造（fetch只能取一次）
emp_loop:loop
fetch emp_cursor into 局部变量;
end loop emp_loop;
#结果fetch到底 报错no data
-- 控制异常 handler 句柄#声明一个handler

emp_loop:loop
fetch emp_cursor into 局部变量;

#声明一个handler
Declare continue（继续执行）/exit（直接退出存储过程）handler for not found(1327的别名)  set lp_flag=false; 
1329

if lp_flag then 
select局部变量;
else
leave  emp_loop;
end if;

end loop emp_loop;
set @end_flag =”end”;

--关闭 close 
close emp_loop；

书写顺序

在语法中，变量声明、游标声明、handler声明 不能颠倒

mysql 备份

为什么要备份？

• 保证重要数据不丢失
• 数据转移

MYSQL 数据库备份方式

• 直接拷贝data物理文件

• 在可视化工具中手动导出

• 使用命令行导出 mysqldump

  导入

#@@@打开命令行——————无需登录
# 导一张表：     
#       mysqldump [-h [主机名] -u [用户名] -p [密码]]  数据库 [表名] >  物理磁盘位置/文件名
mysqldump -h localhost -u root  -p 123456
yggl employees > C:/a.sql
# 导多张表：     
#       mysqldump [-h [主机名] -u [用户名] -p [密码]]  数据库 [表名] [表名] [表名] >  物理磁盘位置/文件名
mysqldump -h localhost -u root  -p 123456
yggl employees salary deparments > C:/b.sql

# 导数据库：     
#       mysqldump [-h [主机名] -u [用户名] -p [密码]]  数据库  >  物理磁盘位置/文件名
mysqldump -h localhost -u root  -p 123456
yggl  > C:/c.sql

#导入

#在登录的情况下，切换到指定的数据库名
# 打开命令行，登录mysql -uroot -p 123456
use 数据库名；
#source 备份文件     source  C:/c.sql

mysql -u 用户名 -p 密码 库名< 备份文件

用户权限

SQL 命令 操作：

用户表 mysql.user

#创建用户
create user 用户名 identified by '密码'；
#修改密码（当前用户密码）
set password=passward('密码');
#修改密码（当前指定密码）
set password for 用户名 =passward('密码');
# 重命名
rename user 原来名字 to 新名字
#用户授权  all privileges除了给别人授权(with grant option)，其他都能够干
grant all privileges for *.* to 用户名；
#查询权限
show grants for 制定用户名；
show grants for root@localhost;####
#撤销权限
revoke all privileges for *.* to 用户名；