mysql制空_MySql学习

-- 注释

-- 启动mysql

mysql -hlocalhost -uroot -proot

-- 创建数据库

create database 数据库名 [库选项]

-- 查看数据库

show databases

--查看指定的数据库

show databases like 'pattern' --pattern 是匹配模式

-- '%':匹配多个字符的模式

-- '_':匹配单个字符的模式

-- 更新数据库

alter database 数据库名字 [库选项]

-- 删除数据库

drop database 数据库名字

-- 使用数据库

use 数据库名字

-- 增加数据表

create table 表名(

字段名字 数据类型,

字段名字 数据类型

) [表选项]

表选项:

字符集：charset utf8/gbk

校对集:

-- 查看数据表

show tables;

-- 查看部分表：模糊匹配

show tables like 'parrent' --pattern是匹配模式

-- 查看表结构

desc 表名

describe 表名

show columns from 表名

-- 修改数据表

修改表本身：

rename table 旧名 to 新名

alter table 表名 [表选项] = 新值

修改字段：

alter table 表名 add [column] 字段名 数据类型 [列属性][位置];

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

-- 删除数据表

drop table 表名1,表明2....

-- 数据操作

-- 新增数据

insert into 表名 values(值列表) --全表添加

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

-- 查看数据

select * from 表名 [where 条件]

select 字段名 from 表名 where 条件

-- 更新数据 --不一定会成功

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

-- 删除数据

delete from 表名 [where 条件]

-- 排序

升序：order by 字段名 asc

降序：order by 字段名 desc

-- 二、中文编码

查看服务器认为的字符集：

show variables like 'character_set%';

查看校对集：

show collation;

解决方法：修改服务器认为的客户端数据的字符集 --解决插入的不报错，显示仍然是乱码

set character_set_client = gbk;

快捷方式：set names 字符集

解决方法：修改服务器给客户端的字符集

set character_set_results = utf8;

-- 三、web的乱码

-- 四、数据类型

1.整数

2.浮点数

a.浮点型：整数部分超出长度会进位，小数超过长度会四舍五入

b.定点型：整数部分超出长度不会进位，小数多少位都没事

3.时间日期类型

datetime YYYY-mm-dd HH:ii:ss

date YYYY-mm-dd

time HH:ii:ss

timestamp YYYY-mm-dd hh:ii:ss --如果该字段的行被修改，那么该字段也会更新

year YYYY

4.字符串类型

char --定长字符串，设置存储的长度

char(L):可以存厨的长度，符号是字符，最长是255

varchar --变长，根据具体的数据确定分配空间的长度，最长是65536，但实际上只要超过255，就考虑使用text

定长比较浪费磁盘空间，但是效率高;变长节省空间，但是效率低

text --文本字符串，通常超过255个字符

blog：存储2进制数据

enum --枚举字符串

enum(可能出现的元素列表)

set --集合字符串

set(元素列表)

insert into 表名 values(num) --num对应的是二进制数

-- 五、字符和字节：

字节：字节是网络传输信息(或者硬盘或内存中存储信息)的单位

字符：字符是人们使用的一种标号，有两种不同编码：

ansi:一个汉字占用两个字节，其余的占用一个字节

unicode:都是占用两个字节

utf8:一个字符占用3个字节

-- 六、字段

1.列属性：

空属性：NULL和NOT NULL

2.列描述：

comment:用来说明

create table my_student(

name varchar(20) no null comment '姓名',

age int,

sex varchar(10)

)charset utf8;

3.默认值：

default:

create table my_default(

name varchar(20) not null,

age int,

gender enum('male','framale','secret') default 'male'

)charset utf8;

insert into my_default values('zhangsan',23,default);

4.主键：

primary key: 不能为空

create table my_default(

name varchar(20) not null,

age int,

gender enum('male','framale','secret') default 'male'

number char(10) primary key

)charset utf8;

追加主键：alter table 表名 add primary key(字段列表)

主键约束：主键对应的数据不允许重复

更新/删除主键：

alter table 表名 drop primary key;

主键的分类：

一般情况下用没有业务逻辑的字段作为主键，也就是没有实际存在的意义

5.自增长：

auto_increment:自增长，在添加数据时，设置成null就可以实现自增长

insert into 表名 values(null,字段1，字段2...)

查看下一次自增长的数是多少：show create table 表名

修改下一个自增长的值:alter table 表名 auto_increment = 值

追加自增长：alter table 表名 modify 字段 类型 auto_increment;

删除自增长：alter table 表名 modify 字段 类型;

6.唯一键：

unique/unique key:

不为空的唯一键就是主键

追加唯一键：alter table 表名 add unique key(字段)

删除唯一键：alter table 表名 drop index 字段

7.索引：

提升查询数据的效率

1.主键索引：primary key

2.唯一索引:unique key

3.全文索引:fulltext index -- 文章关键字的索引

4.普通索引；index

8.外键：

foreign key

一张表的字段(非主键)指向另一张表的主键

创建表的时候添加：foreign key(字段) references 含主键的表名(字段)

-- foreign key(c_id) references student(id)

创建表之后添加:

alter table 表名 add foreign key(字段) references 含主键的表名(字段)

查看外键：

show create table 表名;

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

外键的作用：

约束字表的数据添加，因为一旦字表的外键在主表中没有找到，那么会显示添加失败;

约束主表的删改，如果主表的删改的字段已经被字表引用，则删改出错;

外键条件：

外键字段的类型必须与父表的主键类型完全一致

外键名称不能重复

外键约束：

1.district:严格模式(默认)

2.cascade:级联模式,父表的操作，对应字表关联的数据也跟着更新或者删除

3.set null:置空模式，父表的操作，对应字表关联的数据被置空

foreign key(外键字段) references 父表(字段) on delete set null update cascade;

常用的语法：'删除制空，更新级联！'

-- 七、关系

1.一对一：一张表的字段对应的信息对应另一张表的信息

2.一对多：一张表的记录对应另一张表的多条记录，应该在多条记录的表中增加主键

3.多对多：一张表的一条记录可以对应另一张的多条记录，并且另一张的一条对应多条的另一张。需要增加中间表

4.id值一般只是作为逻辑的主键，理论上来讲是用其他的字段作为业务主键

-- 八、数据高级操作

1.主键冲突：

解决方案：更新操作 --替换主键的值

insert into 表名 [(字段)] values (值列表) on duplicate key update 字段=新值

：替换 --替换主键对应的行的字段值

replace into 表名 values (值列表);

2.蠕虫复制：

从已有的数据中获取数据，然后将数据进行更新操作

insert into 新的表 [(字段)] select 字段或者* from 旧表

可以自己复制自己

3.更新数据:

update 表名 set 字段 = 值 [where 条件] limit 数量;

4.删除数据：

delete from 表名 [where 条件] limit 数量;

删除数据的操作不会改变表的结构，也就是说自增长的值不会重置

5.查询数据：

完整语法：select [select选项] 字段列表[字段别名] from 数据源 [where 条件子句][group by 子句][having 子句][order by 子句][limit 子句]

select选项：

all:默认的

distinct:去重，必须所有字段全部相同

字段别名:

字段 [as] 别名

可以在查看的时候看到自己的别名，方便用户查看

数据源：

单表数据源、多表数据源

子查询：

select 字段 from (select * from 表名) as 别名;

where语句：

select * from 表名 where 条件 -- in(数值) 范围在in里面的

select * from 表名 where 条件 -- between and 类似那种大于多少，小于多少的

group by:

分组筛选

count(字段名) 计数总数

max(字段名) 最大数

min(字段名) 最小数

avg(字段名) 平均数

sum(字段名) 总数

多字段分组：先分组之后再在分组成功之后的组里面分组

select 字段1，字段2 from 表名 group by 字段名1，字段名2;

having：

having 在分组完成之后可以自己操作

having 能够使用别名操作，where只能使用字段名

select 字段名 from 表名 group by 字段名 having ...

select 字段名 from 表名 where 条件 group by 字段名 ... --报错，where之后的条件需要分组结果进入内存之后才会生效，但是此处还未进入

order by:

排序

order by [字段名][asc/desc] --默认升序

limit：

限制数量

limit 位置,数量 --位置从0开始

limit offset,length -- offset = (页码-1)*length;

-- 九、连接查询

关键字：join

左表 join 右表

1.内连接：

[inner] join

从一张表中循环取出每一条数据，去逐个匹配另一张表的数据,匹配必须是某个条件成立时 --最常用

左表 inner join 右表 on 左表.字段 = 右表.字段;

select * from class inner join student on class.id = student.id;

-- 为了避免查询出来的结果出现相同的字段名，可以使用表别名，字段别名去有选择的显示

select s.*,c.classroom,c.classname from student as s inner join class as c on s.c_id = c.id;

2.外连接：

outer join

从一张表中循环取出每一条数据，去逐个匹配另一张表的数据,能匹配正确保留，不能匹配，其他表字段都制空null

左表 left join 右表 on 左表.字段 = 右表.字段;

select c.classroom,c.classname,s.* from class as c left join student as s on c.id = s.c_id;

有两种分类:

左连接：left join

右连接：right join

3.自然连接：

natural join

就是自动匹配连接条件，系统以字段名字作为作为匹配条件(同名就作为条件)

4.交叉连接：

cross join

从一张表中循环取出每一条数据，去逐个匹配另一张表的数据 --没用的，形成了交叉连接

左表 cross join 右表

-- 十、

1.联合查询

联合查询的字段数必须相同，不关字段类型

union[unicon选项]

all:重复查询

distinct:去重查询

select * from class union select * from student;

注意事件：

联合查询使用order by 要用括号括起两个查询语句

(select * from student where sex = 'boy' order by age asc) union (select * from student where sex = 'girl' order by age desc);

2.子查询：

按位置分类：

from子查询：

select name,height,weight from (select * from student where sex='boy') as student2;

where子查询：

exists子查询:

按结果查询：

标量子查询：

得到的结果是一行一列

列子查询：

得到的结果是多行一列，需要使用in作为条件匹配

select * from student where c_id in(select id from class)

行子查询：

得到的结果是一行多列，或者是多行多列

select * from student where (age,height) = (select max(age),max(height) from student);

-- 以上查询在where之后

表子查询：

from 后面必须是表名

select * from (select * from student order by height desc) as student2 group by c_id;

-- 子查询在from之后

-- 十一、视图

1.创建：

单表：create view 视图名字 as select语句

多表: create view 视图名字 as select s.*,c.classroom,c.classname from student as s left join class as c on s.c_id = c.id;

2.查看：

show tables;

desc table

show create view 表名

3.使用：

主要是为了查询，跟表没什么区别

select * from my_v1;

修改视图：

修改视图的来源(select 语句)

alter view my_v1 as 新的select语句

删除视图：

drop view 视图名字

-- drop view my_v1;

4.意义：

将复杂的sql语句进行保存，便于接下去的增删改查

5.视图数据操作：

新增数据：

不允许添加基表中不允许为空的数据

删除数据：

多表视图不能删除

单表视图可以删除

delete from my_v2 where id = 2;

更新数据：

单表视图或者多表视图都可以更新操作

update my_v2 set 字段名 = 值

6.视图算法

7.数据备份与还原

1.数据表备份:直接到对应的数据库文件夹下拷贝数据表的文件和表结构

数据表备份有前提条件，根据不同的存储引擎有不同的区别

存储引擎：mysql进行数据存储的方式，主要有两种：innodb和myisam

frm是结构，MYD是数据，MYI是索引

-- create table my_myisam(id int)charset utf8 engine=myisam;

备份时，直接拷贝三个文件到对应的数据下就可以了

单表数据备份：

select * into outfile 路径 from 表名 [fileds或者lines选项]

fileds选项:

enclosed by:字段内容被什么包裹

terminated by:字段以什么结束

escaped by:特殊符号是什么方式处理

lines：行处理

starting by:每行以什么开始

-- select * into outfile 'E://learngit/Mysql/class.txt' fields enclosed by '"' from class;

还原数据：

load data infile 路径 into table 数据库名字 [fileds或者lines选项];

-- load data infile 'E://learngit/Mysql/class.txt' into table class fields enclosed '"';

2.sql备份：

备份的是mysql语句，还原时只要执行sql指令就可以

mysqldump -hPup(端口号、用户名、密码) 数据库名 表名 > 路径

-- mysqldump -uroot -proot mydatabase student > E:/learngit/Mysql/student.sql

还原：

方案一：

mysql -hPup 数据库名字 < 路径

方案二：使用sql指令

source 路径;

-- 十二、事务

需求：A用户给B用户打钱，但是中途断电了。

1.事务操作：

a.自动事务

autocommit

现在最常见的方式

b.手动事务

+ 开启事务:start transaction

+ 事务操作：比如说是更新数据操作

+ 关闭事务：

提交事务：commit;

回滚事务：rollback;

+ 事务原理：

+ 回滚点：

设置回滚点：savepoint 回滚点名字

回到回滚点语法：rollback to 回滚点名字

2.事务特性：

-- 十三、变量

1.系统变量

查看系统变量：show variables;

查看系统变量值：select @@变量名

修改系统变量：

会话级别：只对当前客户端生效，一旦退出，恢复原来的初始值

-- set 变量名 = 值;set @@变量名 = 值;

全局级别：一次修改，永久生效

-- set global 变量名 = 值

2.自定义变量 --也是会话级别

设置变量:

-- set @变量名 := 值;

查看变量：

-- select @变量名

从数据表中查询一列赋值给自定义变量

-- select @变量名 := 字段名,字段名 from 表名;

一次赋值一条

-- select 字段1，字段2 from 表名 where 条件 into @变量名1,@变量名2; 这里的条件要保证得到的是一行数据

-- 十四、触发器

trigger

为某张表绑定一段代码，当表中的某些内容发生改变时，系统会自动执行这段代码

事件类型：

增删改

触发时间：

before after

触发对象：

表中的每一条记录

创建触发器：

delimiter 自定义符号 后续代码中只有碰到自定义符号才算结束

-- create trigger 触发器名字 触发时间 事件类型 on 表名 for each row;

begin

-- 每一条记录要执行的操作

end

自定义符号

-- 重新设置自定义符号

delimiter ;

使用触发器：

if分支:

if 条件 then

...

else

...

end if

循环结构：

while循环(没有for循环)

while 条件 do

-- 执行代码

-- 变更条件

end while

循环控制：

iterate:迭代,类似continue,后面的代码不执行，循环重新来过

leave：离开,类似break

使用方法：

leave iterate 循环名字

-- 定义循环名字

循环名字：while 条件 do

...

leave iterate 循环名字

end while

函数: -- 字符串的操作单位是字符，所以不会出现乱码问题

有返回值，所有mysql用select收集结果

内置函数：

substring(str,pos,len); --字符串截取

char_length(str) --字符长度

length(str) -- 字节长度

instr(str,'target_str') -- 返回字符串在某一个字符串中的位置

lpad(str,len,'target-str') --左填充，以字符为单位，添加的长度原本的长度也要算上

insert(str,pos,len,newstr) --替换，找到目标位置，替换新的字符串

strcmp(str1,str2) -- 字符串比较，逐位比较,不区分大小写

自定义函数：

1.创建函数：

create function 函数名([参数列表]) returns 数据类型

begin

函数体

return 返回值

end

2.查看函数

show function status; --查看所有函数

show create function 函数名 --查看创建函数的信息

3.函数参数

需求:求1到任意数的和

delimiter $$

create function display2(num int) returns int

begin

set @i = 1;

set @res = 0;

while @i < num do

set @res = @res + @i;

set @i = @i + 1;

end while;

return @res;

end

$$

delimiter ;

4.函数变量作用域

声明变量

declare 变量名 变量类型 default 值

-- declare i int default 1

delimiter $$

create function display3(num int) returns int

begin

declare i int default 1;

declare res int default 0;

mywhile:while i < num do

if i % 5 = 0 then

set i = i + 1;

iterate mywhile

end if

set res = res + i;

set i = i + 1;

end while;

return res;

end

$$

delimiter ;