Mysql02

命令行启动问题

• 查看环境变量path
• 进入mysqlsever\bin下路径
• C:\Program Files (x86)\MySQL\MySQL Server 5.0\bin
• net start mysql
• mysql -u root -p
• 修改密码：mysqladmin -u username -p password 新密码

命令行操作mysql

-- 登录mysql
mysql -u root(用户名) -p
-- 查看有哪些数据库
show databases;
-- 查看数据库的表
show tables from database_name;
-- 创建数据库
create database database_name;
-- 使用数据库
use database_name;
-- 查看表结构
desc table_name;
-- 退出mysql
exit;
-- 修改密码
mysqladmin -u root -p 旧密码 password 新密码；
-- 刷新数据库
flush dbname;
-- 增加新用户
 grant select on 数据库.* to 用户名@登录主机 identified by “密码”;
-- 表的重命名
alter table t1 rename t2;
-- 备份数据库
mysqldump -u root -p dbname > dbnameFile.sql;
-- 备份数据库中的某些表
mysqldump -u root -p dbname tablename > tablenameFile.sql; 
-- 恢复数据库
mysql -u root -p newdbname < dbnameFile.sql;

数据库排序

• order by 字段[desc/asc]；
• desc 表示降序；
• asc默认排序规则，表示升序；

聚合函数

聚合函数主要是为了快速得到结果，经常使用的聚合函数有：

• count 统计行数；
• max 计算最大值；
• min 计算最小值；
• 数学函数；
  • floor(x)向下取整；
  • ceiling(x)向上取整；
  • round(x,d)四舍五入到最近的整数；
  • mod(N,M)%取模；
• sum 求和；
• avg 求平均数；
• round(deciml,num) 函数，保留num位小数；
• 时间函数；
  • select now()显示当前时间及日期；
  • curdate()当前日期；
  • curtime()当前时间；
• substr (string，start，length)函数，截取字符串，mysql的start从1开始；
• left(str,len)，返回字符串str的最左边len个字符；
• right(str,len)，返回字符串str的最右边len个字符；
• length(str)，返回str长度，utf8一个汉字3字节；
• upper(str)，字符串大小写；
• lower(str)，字符串小写；
• concat()，拼接字符串；

分组查询

group by

分组后不能使用where做条件过滤，需要使用一个新的having函数

limit分页

如果数据量很大的话，一次性将所有数据查询出来，不方便查看而且耗费传输带宽，使用分页功能，一次查询一页；

分页语法

select * from students limit start,count;

mysql三种连接查询

**内连接查询:**查询结果为两个表匹配到的数据，两个表都能匹配上的数据将返回给结果集；

select * from table1 inner join table2 on table1.col = table2.col;

**右连接查询：**查询的结果为两个表匹配到的数据，右表特有的数据，对于左表中中不存在的数据使用null填充；

select * from table1 right join table2 on table1.col = table2.col;

**左连接查询：**查询的结果为两个表匹配到的数据，左表特有的数据，对于右表中不存在的数据使用null填充；

select * from table1 left join table2 on table1.col = table2.col;

子查询

在一个select语句中嵌入了另外一个select语句；

子查询是辅助主查询的，充当数据源，或者是充当条件。子查询是一条独立的语句，即使单独拿出子查询也是可以正常执行的；

子查询的四种类型：

1、标量子查询：子查询返回一行一列（值）的数据；

2、列级子查询：子查询返回的是一列多行的数据，可用关键字有in、all、any、some；

3、行级子查询：子查询返回的是一行多列的数据；

4、表级子查询：子查询返回的是多行多列的数据；

sql语句中any、some、all的使用

都是用在子查询里面，用作比较运算；

any表示任意一个，有一个满足了结果就为true，和some是一样的效果；

all是全部满足，结果才为true；

保存查询结果

可以将查询到结果直接保存到表里；

insert into 表格 (列1，列2) select…

insert into table_info(id,name,class,age)
select s.id,s.name,c.name as class_name,s.age
from students as s inner join class as c on s.class_id = c.id;

合并查询

**union all：**将两次查询的结果集合合并到一起显示；

**union：**将两个查询的结果集先去重后合并到一起显示；

创建用户并分配权限

新版本：

创建用户：create user 用户名 @ ip地址 identified by ‘密码’

授权操作：grant 权限 on 数据库 to 用户名@IP地址

Mysql中的权限有create、alter、drop、insert、update、delete、select等；

可以直接分配所有权限all privileges；

**flush privileges：**表示让赋予的权限立即生效；

**查看用户的权限：**show grants for 用户名；

**回收权限：**revoke select on 数据库.表 from ‘用户名’@’%’;

root账户修改普通用户的密码：

update mysql.user set authentication_string=password(新密码)

where user=‘用户名’;

删除用户：

drop user ‘用户名’@’%’;

delete from user where user = ‘用户名’;

-- 权限,mysql.*表示mysql数据库下所有的表
create user 'estella'@'%'identified by '123456';
grant select on mysql.* to 'estella'@'%';
grant update,insert on mysql.* to 'estella'@'%';
flush priviledes;
show grants for estella;
revoke insert,update on mysql.* from 'estella'@'%';

事务

**事务：**也称工作单元，是由一个或多个SQL语句所组成的操作序列，这些SQL语句作为一个完整的操作单元，要么全部执行成功，要么全部执行失败。在数据库中，通过事务来保证数据的一致性；

**事务处理语言：**TPL，主要用来对组成事务的DML语句的操作结果进行确认或取消。确认也就是使DML操作生效，使用提交commit命令实现。取消就是使DML操作失效，使用回滚rollback命令实现；

**通过事务的使用，能防止数据库中出现数据不一致的现象。MySQL是支持事务的。**mysql中支持多种引擎，默认使用Innodb引擎支持事务。

事务的特性ACID

**原子性Atomicity：**事务就像原子，不可被分割，组成事务的DML操作语句要么全成功，要么全失败，不可能出现部分成功部分失败的情况；

**一致性Consistency：**一旦事务完成，不管是否成功，整个系统处于数据一致的状态；

**隔离性Isolation：**一个事务的执行不会被另一个事务所打扰。

**持久性Durability：**也称永久性，指事务一旦提交，对数据的改变就是永久的，不可以再被回滚；

事务处理

**begin，rollback，commit：**用begin开启事务后在没有commit提交之前执行修改命令，变更会维护到本地缓存中，而不维护到物理表中，只有在commit提交之后才会更新到务理表中。如果中间执行错误，那么用rollback回滚事务，恢复到执行事务前的状态。

视图与索引

视图

视图是一张虚拟的表，这个表的结构和数据是由select语句来指定的，不会生成真实的文件，本质上就是对查询的封装。

适用情况：

某个查询结果出现的非常频繁，经常用这个查询结果来做子查询，此时可用视图，用户就可以将注意力集中在所关心的数据上；

出于保密的诉求，用视图来过滤敏感数据，比如工资表，可以用视图过滤掉敏感字段；

视图创建

create view 视图名称 as select 语句；

查看视图

show tables;-- 查看表会将所有的视图也列出来

show Table status;

删除视图

drop view 视图名称；

调用视图

select * from 视图名称；

-- 视图创建
create view v_stu_score as select stuid '学号',avg(mark) '平均成绩'
from tb_score group by stuid;
select * from v_stu_score;

索引

创建索引

• primary key：主键索引
  • alter table 表名 add primary key (列名)；
• unique：唯一索引
  • alter table 表名 add unique (列名)；
• index：普通索引
  • alter table 表名 add index 索引名(列名)；
• fulltext：全文索引
  • alter table add fulltext (列名)
• 组合索引
  • alter table 表名 add index 索引名 (列1，列2，列3);

为了更好的提高mysql效率，允许建立组合索引；

查看索引

show index from 表名；

删除索引

drop index 索引名 on 表名；

存储过程

**存储过程，**也称存储程序，是一条或者多条SQL语句的集合，可以视为批处理，但是其作用不仅仅局限于批处理。存储过程也可以调用其他存储过程。

创建存储过程

delimiter //
create procedure 存储过程名称(参数列表)
begin 
sql语句
end
//
delimiter;

delimiter用于设置sql语句分隔符，默认为分号；

在sql语句部分编写的语句需要以分号结尾，此时回车会直接执行，所以要创建存储过程前需要指定其他符号作为分隔符，此处使用//，也可以使用其它字符；

查看创建的存储过程

查看所有的存储过程：

show procedure status;

调用存储过程

call 存储过程名称；

delimiter //
create procedure sel_stu_pro()
begin 
select * from user where gender = 1;
END
//
delimiter
-- 查看所有存储过程
show procedure status;
-- 调用存储过程
call procedure sel_stu_pro();

存储过程中的变量

要在存储过程中声明一个变量，可以使用declare语句；

语法：

​ declare var_name datatype default default_value;

变量的赋值可以用set进行赋值，也可以用查询方法进行赋值；

declare total_sale int default 0;
-- 声明变量
declare total_count int default 0;
-- 赋值变量
set total_count = 10;
-- 赋值变量
select count(*) into total_count from students; 

if语法：

if expression then
	statements;
end if;

-- if-else
if expression then
	statements;
else
	else_statements;
end if;

-- if-elseif-else
if expression1 then
	statements；
elseif expression2 then
	elseif_statements;
else
	else_statements;
end if;

while循环语句：

while expression do
	statements
end while;

存储过程

-- 创建存储过程
delimiter $$
drop procedure if exists auto_insertdata_pro $$
create procedure auto_insertdata_pro()
begin
declare num int default 0;
set num = 1;
-- 关闭自动提交
set autocommit = 0;
start transaction;
while num<=500000 do
	insert into myindex value(0,concat('tset',convert(num,char)));
	num = num +1;
end while;
commit;
end
$$
delimiter

-- 查看存储过程
show PROCEDURE STATUS;
-- 调用存储过程,执行时间3.779s
call auto_insertdata_pro();
-- 查看索引
show index from myindex;
-- 创建索引
alter table myindex add index index_my_tsert(test);

数据库函数

内置函数

• 查看字符的ascii码值：select ascii(‘a’);
• 查看ascill码值对应的字符：select char(97);
• 拼接字符串：concat(str1,str2…);
• 包含字符个数：length(str);
• 截取字符串
  • left(str,len)，返回字符串str的左端len个字符；
  • right(str,len)，返回字符串str的右端len个字符；
  • substring(str,pos,len)，返回字符串str的位置pos起len个字符；

注：select调用函数

• 去除空格

  • ltrim(str)，返回删除了左空格的字符串str；
  • rtrim(str)，返回删除了右空格的字符串str；
  • trim([方向 remstr from str])，返回从某侧删除remstr后的字符串str，方向词有both，leading，trailing；

• 返回由n个空格字符组成的一个字符串：space(n)；

• 替换字符串：replace(str,fromstr,tostr)；

• 大小写转换：lower(str)，upper(str)；

• 日期函数

  • year(date),返回date中的年份；
  • month(date)
  • day(date)
  • hour(time)、minute(time)、second(time)

• 日期计算，使用+ - 运算符，数字后面的关键字为year、month、day、hour、minute、second；

  • select ’ 2021-10-10’+interval 1 day;

• 日期格式化：date_format(date,format)

  • format可选值：%Y%y%m%d%h%H%i%s

• 当前日期：current_date();

• 当前时间：current_time();

• 当前日期时间：now();

• 自定义函数

语法：

delimiter $$
create function 函数名称(参数列表) returns 返回类型
begin
sql语句
end
$$
delimiter

-- 创建函数，无参函数
delimiter $$
create function hello() returns varchar(20)
BEGIN
return 'hello mysql!';
END
$$
delimiter ;
-- 查看是否创建成功
show function status;
-- 调用函数
select  hello(); 

-- 创建有参函数
delimiter $$
drop function if exists hello$$
create function hello(uname varchar(10)) returns varchar(20)
BEGIN
return CONCAT('hello',uname);
END
$$
delimiter ;
show function status;
-- 调用
select hello(username) from user;

查看自定义函数：show function status；

存储过程和函数区别

1、函数的限制比较多，函数中不能使用临时表，只能使用表变量，存储过程限制比较小；

2、存储过程实现的功能逻辑相对来说要复杂一些，而函数的实现功能针对性强一些；

3、返回值不同，函数必须要有返回值，而且仅仅返回一个结果集，存储过程可以没有返回值，但是能返回结果集；

4、调用语法不同，函数通过select调用，存储过程用call关键字调用；

python操作数据库

连接数据库

Mysql数据类型

Mysql支持多种类型，大致分为三类：数值、日期/时间、字符串(字符)类型

数值类型

日期和时间类型

字符串类型

---

select * from student where id>3;
select * from student where id <>8;
use java_project;
select * from user;
SELECT * from user where userid <> 6;
select * from user where userid != 6;
alter table user add hometown varchar(255) DEFAULT '上海';
select * from user;
update user set hometown = '南京' where userid BETWEEN 1 and 3;
UPDATE user set hometown = '北京' where userid >3 and userid <5;
alter table students add class_id int default null;
update students set class_id = 1 where id<10;
update students set class_id = 2 where id>9;
-- 聚合函数
SELECT MAX(userid)'最大id',MIN(userid)'最小id' from user;
select now();
select curdate();
select curtime();
update students set age = 18 where id<9;
update students set age = 28 where id >=9;

-- 查询学生表中，2班的学生年龄都大于1班的所有学生
select * from students where class_id = 2 and age > all(select age from students where class_id = 1);
select * from students where (age,class_id) = (
	select max(age),class_id from students where class_id = 1
);

-- 表级子查询
select t1.xs as 学生姓名,t1.bj as 班级姓名(select b1.name as xs，b2.name as bj from students as b1 inner join class as b2 on b1.class_id = b2.id) as t1;

-- 练习
create database Mydb charset='utf8';
use Mydb;
create table tb_students(
	stuid int not null comment'学号',
 	sname varchar(50) not null comment '姓名',
 	gender bit default 1 comment '性别',
 	birth date not null comment '出生日期',
 	addr varchar(255) default '' comment '地址',
 	collid int not null comment '所属学员编号',
 	primary key(stuid)
);
desc tb_students;
-- 查询80后学生的信息
select sname as 姓名,gender as 性别,birth as 出生日期
from tb_students 
where birth between '1980-1-1' and '1989-12-31';
-- 给性别加入流程控制
if(gender,'男','女')
case gender when 1 then '男' else '女' end

select sname as 姓名,if(gender,'男','女') as 性别,birth as 出生日期
from tb_students 
where birth between '1980-1-1' and '1989-12-31';
select sname as 姓名,case gender when 1 then '男' else '女' end as 性别,birth as 出生日期
from tb_students 
where birth between '1980-1-1' and '1989-12-31';

-- 查询名字有4个中文字符的学生姓名学号,length()返回的是字节长度，1个汉字3个字节
select sname '姓名',stuid '学号' 
from tb_students where length(sname)/3 =4;

select distinct * from tb_score;
select sname '姓名',birth '生日' 
from tb_students
where gender = 1
order by birth;
select stuid '学号',avg(mark) '平均成绩'
from tb_score
group by stuid;
select sname '姓名' from tb_students where stuid in (
	select sid from tb_score
    group by sid
    having count(sid)>2
);

select sname '姓名',avgmark '平均分' 
from tb_students as t1  inner join (select sid,avg(matk) as avgmark from tb_score group by sid)as t2 on t1.stuid = t2.sid;

select sname '姓名',ifnull(c_count,0) as '选课数量'
from tb_students as t1 left join 
(select sid ,count(sid) as c_count from tb_score group by sid) as t2 on t1.stuid = t2.sid;

select sname '姓名',birth '生日' 
from tb_students
where gender = 1
order by birth;
select stuid '学号',avg(mark) '平均成绩'
from tb_score
group by stuid;
select sname '姓名' from tb_students where stuid in (
	select sid from tb_score
    group by sid
    having count(sid)>2
);

select sname '姓名',avgmark '平均分' 
from tb_students as t1  inner join (select sid,avg(matk) as avgmark from tb_score group by sid)as t2 on t1.stuid = t2.sid;

select sname '姓名',ifnull(c_count,0) as '选课数量'
from tb_students as t1 left join 
(select sid ,count(sid) as c_count from tb_score group by sid) as t2 on t1.stuid = t2.sid;