mysql笔记

Mysql

alter table 表名 AUTO_INCREMENT=1; 主键从1开始自增
AUTO_INCREMENT 设置自动增长列
tee 路径 ----------- notee//保存过程

mysql -u root -p1234(进入)

数据库：

查看数据库：show databases;

创建数据库：create database 数据库名称（characetr set 编码;（改变类型）;

查看数据库的语句: show create database 数据库名称；

修改数据库：alter database 数据库名称 character set 编码;

删除数据库：drop database 数据库名称;

打开数据库: use 数据库名称;
查看库中有哪些表: show tables;

创建表：

表结构设计：一个表描述一个实体，或者是实体间的一种联系
避免表之间出现重复字段
表中的字段应该是原始字段
表中应有唯一标识表中记录的字段（主键）

表结构： 那些字段 字段名 字段类型 宽度

数据类型：数值：整数 int 小数 decimal(m,d)
字符串：char 定长 varchar 变长 备注 varchar(100)
text 大文本类型

如果存放的是数字，但是这些数字不进行计算，就使用字符串类型。
日期时间：date	出生日期	date	"1998-11-5"
	datetime(加时间)	
二进制类型：可以存放图片ptf

一般进行开发时，表名和字段名都用英文的。

创建表命令

表要创建在数据库中

create table 表名(
字段名1 类型（宽度）约束,
字段名2 类型（宽度）,
)

查看表结构：desc 表名；
查看表所有记录：select*from 表名
插入记录：insert into表名values（值1,值2……）；
insert into表明values(123,‘zxc’,‘qwe’);
查看创建表的语句：
show create table 表名；

修改表，修改表的结构，包括 增加字段，修改字段，删除字段

alter table 表名 ……
1.修改表名：
alter table 旧表名 rename {to} 新表名；

2.修改字段 修改字段的类型和宽度
alter table 表名 modify 旧字段名 类型（宽度）;

3.修改字段名
alter table 表名 change 旧字段名 新字段名 类型（宽度）；
注意：如果新字段类型和原来一致，也要加上。

4.增加字段
alter table 表名 add 新字段名称 类型（宽度）【after 旧字段名/first】;
默认添加到最后

4.删除字段
alter table 表名 drop 字段名称；

6.修改字段的排列位置
alter table 表名 modify 旧字段名 类型（宽度）first|after 字段名；

alter table  表名
add   新字段名 类型（宽度）【first|after 字段名】
|modify	旧字段名 类型（宽度）【first|after 字段名】
|change    旧字段名 新字段名 类型（宽度）
|drop    旧字段名
|rename   新表名

删除表：drop table 表名；

一般创建约束有两种模式：
1.创建表的同事添加约束

主键（primary key ）一个表只能有一个主键约束
唯一标识表中的记录 主键字段的值不能出现重复，不能为空
主键可以建在单个字段上，也可以是多个字段组合。

create table stu(
-> sid int(11) primary key,

alter table 表名
add[constraint[约束名]]
primary key[index_tydex]
alter table 表名
add primary key(字段名,…);
注意：如果是通过修改表结构添加记录,要保证表中的记录满足约束要求，这样才能添加成功

非空约束：not null
一边创建表的时候添加；

唯一性约束(unique)：不能出现重复值但可以为空 一个表可以创建多个唯一性约束

默认约束（default）一般默认约束建表同时添加

set names gbk;改变编码。

表和表之间的关系：
一对一： a表中的一条记录，在 b表中有一条记录和它对应，反过来，b表中的一条记录在a表中也有一条记录和它对应。

emp salary
eid ename eid money
101 aa 101 4000
102 bb 102 5000
103 cc 103 3000

外建约束：
一对多：a表中的一条记录，在 b表中有多条记录和它对应，反过来，b表中的一条记录在a表中只有一条记录和它对应。

depart
did dname
11 软件系
12 计算机应用
13 智能系

emp
eid ename did
101 aa 11
102 bb 11
103 cc 12

多对多：a表中的一条记录在b表中有多条记录和它对应，
反过来，b表中的一条记录，在a表中也有多条记录和它对应。
注意，多对多的关系在数据库管理系统中表示不出来，
必须将一个多对多的关系转换为两个一对多的关系。
增加一个表 ，那增加的这个表的字段一般包含，原来两表的主键，和自身的属性。

student
sid sname sex
101 aa
102 bb
103 cc

course
cid cname hours
c1 MySQL 90
c2 Java 180
c3 实训 36

score （成绩表）
sid cid grade
101 c1 80
101 c2 90
102 c3 70
103 c3 89
…

外键约束：为了保证表和表之间的数据的一致性。
两个表 将一个表的主键添加到另一个表中，
成为这个表的外键。
a表与b表：
alter table 表名
add foreign key(字段名)references 表名(主键)；

外键：
1.建表的同时添加
create table 表名(
…,
…,
foreign key(外键字段)references 参照表名(主键)
);

2.修改表结构添加：
alter table 表名
add[constraint 外键名]foreign key(外键字段)references 参照表名(主键)；

删除外键：
alter table 表名
drop foreign key 外键名；

如果有自动增长列

alter table 表名
alter 字段名 set default ‘值’；

insert into 表名 （字段名1，字段名2，…）
values（值1，值2，…),(值1，值2，…);
没有插入记录的字段名系统会自动添加默认值；

更新数据，修改数据（记录）
update 表名 set 字段名1=值1[，字段名2=值2，。。。]
[where 条件]

update student set birthday =‘1981-11-15’
where sno =‘99102’;
sc表中成绩（score）都加5分
update sc set score = score+5;

删除记录
delete from 表名 [ where 条件];
delete from sc where score<80

truncate table 语句，用于清空一个表
truncate table 表名；

在进行 insert delete updat 主要外键。

执行sql文件
在mysql提示符下
source 文件路径\文件名.sql
source d:\jilu.sql

单表查询 select

做查询，首先要确定数据源（表）
1.查询所有列*
select sno,sname from student;
2.查询制定列
select 字段名，
3
4.给列加列标题
列名[as]列标题

select sno,sname,year(now())-year(birthday) as age
from student;
select sname 姓名，sex 性别，birthday 出生日期
fromstudent;

选择行
select…from 表名 where 条件；
可以用关系运算符;
使用逻辑运算符
逻辑与 and
逻辑或 or
逻辑非 not

查询成绩在70-90之间的成绩记录
select*from sc
where score >=70 and score<=90;

3.like 关键字 （模糊查询）

表达式[not]like 匹配表达式

匹配符 % _
% 代表多个字符
_ 代表一个字符

where name like ‘东%’

4.带 between and 关键字 ，使用查询范围

查询70-90
where score between 70and90

where not(sdept='软件’or sdept=‘数媒’);
where sdept not in(‘软件’,‘数媒’);

select * from student
where birthday between ‘1981-1-1’ and ‘1981-12-31’;
where year(birthday) =1981;

5. 带in关键字 使用查询列表
   where 字段名 [not] in（值1，值2…）

6 查询空值 null

字段名 is [not] null

查询成绩score为null的记录

select * from sc where score is null;

7 带 distinct 关键字的查询

去掉重复的行

select [distinct] 字段名,… from 表名 where 条件;

查看student表的学生都来自于哪些系
select distinct sdept from student;

排序：order by 字段名1,字段名2…

select [distinct] *|字段名1,字段名2…
from 表名
where 条件
order by 字段名1[asc|desc]，字段名2…

asc: 升序 ，默认值
desc：降序

将sc的记录按成绩升序排列

select * from sc
order by score;

查询sc表的数据，先按cno的升序排列，当cno相同的时候按score的降序排列

select * from sc
order by cno ,score desc;

使用 limit 关键字
select… from …
where…
order by…
limit [n1],n2
n1: 指定从第几条记录开始显示,行号是从0 开始的,可以省略，如果省略，就是从第一条记录开始显示

n2: 显示的总行数，必须是非负的整数

高级查询：

聚合函数：
count(*):计数,统计记录的条数
count(字段名):统计字段有值的记录数，去掉null值
count(distinct 字段名):去掉重复着之后在统计有值的个数

统计学生的总人数

select count(*) as 人数 from student;

查看几个系
select count (distinct sdept)from studengt;

sum(字段名):求和

avg(字段名)：平均值，不考虑null;

max(表达式)：求最大值
min(表达式)：最小值

查询cid为20010203的总成绩
select sum(score) from sc
where cid=20010203;

统计女生人数

select count(*) from student where sex=‘女’;

group by字段名：根据分组字段的值，分组字段值相同的记录，认为是一组
就是将分组字段的值相同的记录进行压缩合并为一条记录，在和平的过程中进行相应的计算

如果想在分组的结果上在进行挑选，可以使用having

select....from.......
where......
group by ...
having条件；

全连接方式：
select…from 表1，表2…
where 表1.字段名 = 表2.字段名 and 表2.字段名=表3.字段名;

如果要查看的字段在多个表中都存在，那么要在字段名前加表名

学号，姓名，课程号 成绩 student sc

select student.sno,sname,cno,score
from student sc
where student.sno=sc.sno

from 表名 表别名

加了表别名 一定要使用表别名
笛卡尔积：

***join连接方式：
交叉连接：select…from 表1 cross join 表2;
笛卡尔积

sele * from student cross join sc;

内链接:[inner]join
符合连接条件的记录出现在查询结果当中

学号，姓名，课程号 成绩
select student.sid,sname,cid,score
from student inner join sc
on student.sid = sc.sid;

外连接：：
左外连接（left join）:左边表的所有记录和右边表满足条件的记录

右外连接（right join）:右边所有记录，左边满足条件记录。

left join sc on s.sid=sc.sid;
select 。。。。from 表1
left join表2 on 表1.字段名=表2.字段名

自连接：一个表自己连接自己。使用自连接时必须为表指定两个别名

select e.id,e.name,b.name
from employee e inner join employee b
on e.head_id = b.id

子连接：在select中嵌入一个select 先执行内层；
select sname from student where sdept =（
select sdept from student where sname=‘贾东’）；

子查询返回的值是单列多值，不能用等于（=）

any >any 大于子查询中的某个值（大于最小）
>=all(大于最大)
all 比较子查询的所有值

带关键字exists 的子查询，相关子查询
查询选修了20010203课程的学生姓名

---

普通
select sname from student where sid is(
select sid from sc where cid=‘20010203’);
exists模式
select sname from student
where exists(
select*from sc where sid = student.sid and cid=‘20010203’);

select … from 表1
where [not] exists(
select*from 表2 where sid = 表1.sid and cid=’…’);

合并结果集（union）
select…
union [ all ]
select…
order by …

所有查询的列数要相同，数据类型必须兼容，顺序要一致。
如果合并的结果要排序，order
by跟在最后一个select语句中，使用的字段应该是第一个select语句中的字段
默认去掉重复记录（保留重复记录添加 all）

索引：

一般建在经常查询的列上。
一般在表的主键外键上创建索引
经常用于表连接的字段上创建索引

什么情况不建或者少建索引
内容表、不经常查询的列上不要建索引
当写的性能比查询更重要时，少建或者不建索引

当进行查询时，有一个查询优化器，会自动使用相关的索引，提高查询速度。

索引分类：
普通索引
唯一性索引
单列索引
多列索引

如何创建索引：
1.建表的同时创建索引

2.用create index语句 创建索引

create [unique] index 索引名
on 表名(字段名 lasc|desc,…);

3.通过修改表结构添加索引

create table t1(id INT,name Varchar(20),
score float,
index(id));
删除 索引
用drop index 删除
drop index 索引名字 on 表名；
用 alter table 删除
alter table 表名字
-> drop index 索引名字;

注意：
当创建外键的时候会建上相应的索引，删除外键，索引还在，可以在删除索引。
但不能先删除索引，因为索引被外键用着。

当创建唯一性约束时，会自动创建上唯一性索引。删除唯一性约束，是通过删除索引来完成的。

视图：虚拟表，和表相似，有结构有记录。
视图是从一个或多个表提取出来的数据。

create[or replace] view 视图名[(列名1，列名2，…)]
as
select 字段名1，字段名2
from 表名1，[表名2];
[where student.sid=sc.sid;]
[whith check option]

or replace 覆盖，替换.

whith check option 作用：保证通过视图添加的数据，通过视图还能看到。
desc 视图名，查看视图。

show table status like’视图名’

修改视图：
可以使用create or replace view 重建视图的同时修改视图中的select语句；

alter view 旧视图名
as
select…from 表名

删除视图：drop view 视图名；

如果视图基表是多个表，那么通过视图去修改基表的数据时，只能涉及一个表的数据。

系统变量

用户变量

显示变量的值：
select @变量名
定义变量
方式一：
set @变量名 = 值；

将99109学生所在系的值保存在一个用户变量中。

set @sdept = (select sdept from student where sid = ’ 99108’);
方式二：
select@变量名:=值；

运算符：算术运算符 + - * / %

函数：
数学函数

字符串函数

trim();
ltrim();
rtrim();

substring(str,n,len)
日期时间函数

curdate()当前日期
curtime()当前时间
now();
sysdate();

adddate(curdate(),30)当前日期加30天
subdata(curdate(),30)当前日期减30天

条件函数
if(expr,v1,v2)
ifnull(v1,v2)
select sid,cid,ifnull(score,0)from sc;

case 表达式
when 值1 then 语句1
when 值2 then 语句2
…
[else 语句n]
end;

第二种写法
case
when 条件1 then 语句1
when 条件2 then 语句2
…
[else 语句n]
end;

select sid,cid,case
when score>90 then ‘优秀’
else ’ 不合格’
end 成绩等级
from sc;

加密函数

md5(str)

encode(str,pwd_str);加密//set @password = encode(123,‘ab’);
decode(str,pwd_str);解密//select decode(@password,‘ab’);

创建存储过程：

delimiter xx 宏替换
create procedure 存储过程名([out/in 参数1,参数2… 类型])//有参数加参数 没有参数只写括号
begin
sql代码;
endxx

参数：
输入参数 in
输出参数 out
输入输出参数 inout

执行存储过程：
call 存储过程名(@参数);

存储过程体
变量
定义局部变量
declare 变量名 类型;

declare v1，v2，v3 int；
set v1=10，v2=20；
set v3=v1+v2；

select…into 变量,…

流程控制语句

if 条件 then 语句;
end if;

if 条件 then语句1;
else 语句2;
end if;

if条件1 then 语句1;
elseif 条件2 then 语句2;
…
else 语句n;
end if;

create procedure bjdx(in v1 int,in v2 int,out n char(6))
-> begin
-> if v1>v2 then
-> set n=‘大于’;
-> elseif v1=v2 then
-> set n=‘等于’;
-> else
-> set n=‘小于’;
-> end if;
-> end//
call bjdx(10,20,@n);
select @n;

case语句

case
when xx then 语句1
when yy then 语句2
else 语句3
end case;

while循环：
while 条件 do
循环体
end while；
declare i int default 1//i从1开始

repeat循环:
repeat
循环体
until 为真继续循环
end repeat；

leave 退出循环
iterate 进入下一次循环

查看存储过程
1.show procedure status like ’ ';
2.show create proceure 存储过程名
3.到information_schema.routines中查看

修改存储过程

删除存储过程

drop procedure 存储过程名;

条件的定义：将一个错误和一个自定义的名字联系起来，可以起一个别名

declare 条件名 condition for mysql 错误代码 (四位)|sqlstate(五位)

处理程序: 有了错误后如何进行处理。
三种方式： continue/跳过错误继续执行 exit/遇到错误退出 undo/遇到错误撤销上一条 不支持

declare 处理方式 handler for 条件1，条件2…

光标的使用（游标）

declare 游标名 cursor for select语句 ；

declare cursor_student cursor for select sname,sex,from student;

使用游标
打开游标：open 游标名字；
open cursor_student;
取到数据：fetch 游标名字 into 变量名1，变量名2.。。。

关闭光标
-close 游标名

delimiter //
create procedure p_result()
begin
//定义变量
declare tbh int;
declare txm varchar(10);
declare tzonghe;tzhuanye float(4,1);
declare tflag char(10);
declare c_score cursor for select bh,xm,zonghe,zhuanye from score;
declare exit handler for not found close c_scor;
open c_score;
repeat
fetch c_score into tbh,txm,tzonghe,tzhuanye;
set tzf = tzonghe+tzhuanye;
if(tzf >= 350 and tzonghe>=150 and tzhuanye>=150)then
set tflag=‘luqu’;
else
set tflag=‘luobang’;
end if;
insert into result values(tbh,txm,tzonghe,tzhuanye,tzf,tflag);//插入记录;
until 0 end repeat;
end//

commit 提交

rollback 撤销操作(提交事务后无法撤销)

事务的4个特性(ACID):原子性 一致性 隔离性 持久性

因为有并发访问(多个线程同时开启事务)，所以会出现并发问题，为了解决并发问题，要进行事务的隔离级别设置

并发问题：
脏读：一个事务读到了另一个事务没有提交的数据。

不可重复读：一个事务中两次查询的结果不一样。

幻读：一个事务中俩次查询结果不一样。和不可重复读相似，但是读到了另一个

开启隔离
set session transaction isolation level **********(级别);
star transaction;//开启事务

查看默认隔离级别:select @@tx_isolation;

备份数据库
mysqldump -u root(用户名) -p密码 数据库[表1 表2…]>文件.sql

备份多个数据库
mysqldump -u root(用户名) -p密码 --databases 数据库名1[数据库名2…]>文件.sql

备份所有数据库
mysqldump -u root(用户名) -p密码 --all-databases>文件.sql

恢复命令
mysql -u root(用户名) -p密码 数据库<备份m文件.sql

使用source 恢复.

创建用户

1.grant创建用户,同时赋予权限.

grant 权限 on 数据库.表to’用户名’@'主机名(localhost)'identified by ‘密码’;

查看mysql.user中有那些用户
select host,user,password FROM user;

2.使用create user 创建用户，新用户 没有任何权限
create user ‘用户名’@'主机名’identified by ‘密码’

3.使用insert 语句向mysql.user表中添加记录，来添加新用户。(不建议使用)

删除用户

1.drop user语句

drop user ‘用户名’@‘主机名’[,…];

2.直接删除mysql.user表中的记录(不建议使用);

修改用户密码
1.使用mysqladmin修改root密码

mysqladmin - u 用户名 [-h 主机名] -p password 新密码

mysqladmin - u root -p password abc
回车后，提示输入旧密码

2.进入mysql状态，自己改自己密码
set password = password(‘新密码’);

3.通过update 修改mysql.user表中的密码

4.root 用户可以修改普通用户密码
(1) grant usage on . to ‘用户名’@'主界面’identified by ‘新密码’;

(2) 通过update 修改 mysql.user表中的密码

(3)set password for’用户名’@‘主机名’=password(‘新密码’)

权限管理

1.授予权限
grant 权限 on 数据库.表 to 用户名

2.查看权限
show grants for’用户名’@‘主机名’

3.收回权限
revoke privileges

Navicat 破译码

破译————————————————————————————
NAVJ-E6YF-JULL-KKIG
NAVE-BOCL-CE3X-TAGY
NAVC-KAIA-NU5I-SPOX
NAVL-FE27-KNTQ-YJXC
NAVK-LXKO-3XHL-HC5T
NAVI-EM52-4TQV-GYYB
NAVN-HQKB-TGPA-FNNL
NAVI-IQLT-MUDA-MMII

NAVK-E42T-YX5H-CR2L

NAVH-2BMX-HJ6J-S5NP

NAVO-FLPD-Q5IS-HTKY
NAVJ-IFVK-YR2N-TGEL
NAVM-RGMH-RWQZ-FBRD
NAVE-ZFGR-EZXJ-7TYD
NAVN-DQPU-5X4Z-ZNL2