MySQL学习笔记_16:39:52create table `student`( `student_id` int p

MySQL学习笔记

一、简介

1.MySQL是关系型数据库。以行和列存储的。（非关系型是键值对方式的）

2.基本语句（cmd模式下）

①新建数据库：create database 库名；（每个数据库在本地硬盘上都是一个文件夹）
②查看数据库列表：show database；
③进入指定库：use 库名；

二、DDL（数据库定义语言）

定义数据库对象：创建库、表、列等。

1.创建表

creat table 表名(
列名1 列的类型 (约束),
列名2 列的类型 (约束),
列名3 列的类型 (约束)
);

create table student(
id bigint,
stu_name varchar(50),
stu_age int
);
1
2
3
4
5

2.修改表

①表中追加列
alter table 表名 add 字段名 类型；

alter table student add stu_gender char;
1

②表中修改列
alter table 表名 modify 字段 类型；

alter table student modify id int;
1

③重命名表名
rename table 当前表名 to 新表名

rename table student to newstudent
1

④修改表列名
alter table 表名 change 当前列名 新列名 数据类型；

alter table student change stu_name name varchar(50);
1

⑤删除列
alter table 表名 drop 字段名；

alter table student drop stu_name;
1

⑥删除表
drop table 表名；

drop table student;
1

⑦建立主键

id int primary key
-或
creat table student(
id int,
name varchar(50)
primary key(id))
或
creat table student(
id int,
name varchar(50)
primary key(id,name))
或建表后追加
alter table student add constraint primary key（id）；
1
2
3
4
5
6
7
8
9
10
11
12
13

三、DML（数据操作语言）

从来操作数据库表中数据的。

1.查询数据

select * from student where id = 1;
1

2.插入数据

inster into student (id,name) values (1,'zs');
1

3.更新数据

update student set id = 1 ,name = 'ls'
1

4.删除操作

delete  from student where id = 1;
1

四、DQL（数据查询语言）

用来查询数据的。

1.排序（asc升序、Desc 降序）

select * from student order by id desc
1

2. 聚合函数

①count：统计总条数
②Max：计算指定列的最大值 例：select Max(id) from student
③Min：计算指定列的最小值
④sum：计算指定列的数值和 例：select sum(salary) from student
⑤Avg：计算指定列的平均值

3.分组函数

①简单分组

select department from student group by department
1

②分组查询其他信息

select department,group_concat('name') from student group by department
select department,name from student group by department,name
select department,group_concat('name'),count(*) from student group by department
1
2
3

③having
先分组再添加条件

select department,group_concat(salary),sum(salary) from student group by department
having sum(salary)>=9000;
1
2

having和where的区别：

• having在分组后对数据进行过滤。
• where在分组前对数据进行过滤。
• having后面可以使用分组函数（统计函数）
• where后面不可以使用分组函数。

4.分页查询

从哪条开始，获取几条。（开发分页中需要将开始条数减1，再乘获取几条数）
select * from sutdent limit (curpage-1)*page,pageSize;

select * from student limit 0,3;
1

五、DCL（数据控制语言）

用来定义访问权限和安全级别的。

六、多表查询

1. 合并结果集

①union查询出的结果去重
②union all查询出的结果不去重

select * from a union select * from b
select * from a union all select * from b
1
2

2.笛卡尔积

①同时查询两张表，出现的就是笛卡尔积结果。如：

select * from a,b
1

②解决笛卡尔积现象。

• 给查询表起别名
• 在查询时保证主键和外键的一致性

select * from student a,address b where a.id = b.id
1

3.连接查询

①内连接(inner join)

select * from stu st inner join score sc no st.id = sc.sid where st.name ='zs';
1

②左连接（left-join）
左边表数据全部查出，右边表满足条件的查出

select * from stu st left join score sc on st.id = sc.did ;
1

③右连接（right-join）
右边表数据全部查出，左边表满足条件的查出

select * from stu st right join score sc on st.id = sc.did ;
1

七、函数

1.字符串函数

①concat（）字符串拼接
任何字符与null连接结果都是null

select concat('a','b','c')  #结果为abc
1

②insert（）字符串替换

select insert('test123',5,3,'ed')  #结果为tested
1

③replace（）字符串替换

select replace('test','e','i')  #结果为tist
1

八、事务

1.简介

• 原子性：事务包含的所有操作要么全部成功，要么全部失败。
• 一致性：让数据保证合理性。
• 隔离性：每个事务都是相互独立的。
• 持久性：事务对数据库的操作是永久性的。

2.事务提交

数据库事务操作顺序：①开启事务，②执行语句，③提交事务

start transaction
语句1
语句2
语句3
commit
1
2
3
4
5

3.事务回滚

start transaction
语句1
语句2
rollback
1
2
3
4

4.脏读

脏读是指一条数据被人修改后还没有提交到数据库中，这时另一用户查询这条数据所看到的是不真实的数据，称之为脏读。
解决办法：read committed，读提交。

九、视图

1.简介

视图是一个虚拟表，内容可根据查询定义。行和列由自定义的查询表定义。视图不存储具体的数据，随着基本表数据的改变而改变。视图的查询性能高，同时也提高了数据的独立性。

2.创建视图

create view student_view as (select * from student where age < 20);
1

3.更改视图内容

create or replace view student_view as (select * from student where age > 20)
1

4.删除视图

drop view student_view
1

十、存储过程

1.简介

存储过程是一组可编程的函数，用来完成一些特定的功能。存储过程保存在数据字典中，将反复性高的一些操作提取出来，从而简化了sql的调用。

2.存储过程的创建与使用

①创建存储过程

delimiter $$ 
create procedure show_student()
begin
select * from student;
end$$;
1
2
3
4
5

②将结束符调整回；

delimiter ;
1

③运行存储过程

call show_student();
1

④查询库中存储过程

show procedure status;
或
show procedure status where db=‘库名’;
1
2
3

⑤删除存储过程

drop procedure show_student;
1

3.存储过程声明变量

①声明变量

delimiter $$ 
create procedure show_student()
begin
declare res varchar(100) default = ‘’
declare a,b int default = 0;
select * from student;
end$$;
1
2
3
4
5
6
7

②修改变量

set a = 1;
declare avgRes int default 0；
1
2

③给变量赋值

select count(*) into 变量名 from student;
1

4.存储过程参数传递

①创建带有入参的存储过程

delimiter $$ 
create procedure show_student(in name varchar(100))
begin
select * from student where ename = name;
end$$;
1
2
3
4
5

②创建带有入参、出参的存储过程

delimiter $$ 
create procedure show_student(in name varchar(100),out sarlary1 int)
begin
select sarlary into sarlary1 from student where ename = name;
end$$;

调用存储过程
call show_student('张三',@s);
select @s;
1
2
3
4
5
6
7
8
9

③inout参数（即是输入，又是输出）

delimiter $$ 
create procedure show_student(inout number,in num int)
begin
set number = number + num;
end$$;

调用存储过程
set @number1 =1;
call show_student(@number1,4);
输出结果为：5
1
2
3
4
5
6
7
8
9
10

④生成一个指定个数的字符串

 delimiter $$ 
  create function rand_str(n int) returns varchar(100)
  begin
  # 声明一个str 52个字母
  declare str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
  #记录当前hi第几个
  declare i int default 0;
  #生成结果
  declare res_str varchar(100) default '';
  #随机生成一个指定个数的字符串
  while i < n do
  set res_str = concat(res_str,substr(str,floor(1+rand()*52),1));
  set i = i + 1;
  end while;
  return res_str;
  end$$;
 delimiter ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

⑤向表中插入数据的存储过程

 delimiter $$ 
  create PROCEDURE	insert_emp(in startNum int,in max_num int) 
  begin
  # 声明一个记录当前条数
	DECLARE i int default 0;
	# 默认是自动提交sql
	set autocommit = 0;
	repeat
	set i = i+1;
	#插入数据
	insert into emp values(startNum+i,rand_str(5),floor(10+rand()*30));
	until i = max_num
	end repeat;
		commit;
  end$$;
   delimiter ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

十一、索引

1.简介

①索引用于快速找出某个列中特定的行
②不适用索引的情况下，mysql是从第一条记录开始读完整张表，直到找出数据
③类似于目录一样，提高查询效率
④索引虽然大大提高了查询速度，同时也会降低更新表的速度。
⑤索引的分类：

• 单值索引：一个索引只包含单列，一个表可同时存在多个单值索引
• 唯一索引：索引列必须是唯一的，允许为空
• 复合索引：一个索引包含多个列
• 全文索引：只能在char，varchar，text类型字段中使用
• 空间索引：对空间数据类型的字段建立索引

2.操作索引

①创建索引
建表时的主键列（唯一索引）和外键列（普通索引），数据库会自动创建索引

create index 索引名 on table (字段)
1

②删除索引

drop index 索引名 on 表名
1

③查看索引

show index from 表名
1