MySql笔记

屏幕快照 2019-03-05 上午9.28.47.png

又很久没有写博客了 这篇笔记是边学边记的 当时比较仓促 所以有的地方可能会比较乱 但是大概的方法写的还是比较清楚了 等有时间回头再好好整理一下这篇文章。

库操作

show darabases; 查询数据库

create database 数据库名; 创建数据库

show create database 数据库名; 查询数据库创建语句

深度截图_选择区域_20190208210257.png

修改数据库

​ 说明:不能修改数据库名称,只能修改字符集和校对集

​ 语句:alter database 数据库名 charset=新字符集 collate=新的校对集

​ 注意:字符集和校对集是可以分开修改的,但是建议必须一起修改

​ 查看校对集:show collation; 查看字符集:show charset;

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

use 数据库名;选择数据库

表操作

创建表

Create table 表名(

​ 字段名称1 字段属性(类型) 字段选项,

​ 字段名称2 字段类型 字段选项,

……………………..

)表选项;

表选项:表字符集和数据引擎

字符集:charset=utf8;

数据引擎:默认innodb,语法:engine=引擎

这里的话可能会比较难理解

有一段比较官方的解释

存储引擎也叫“表类型”,是指一个表中的数据以何种方式存放在文件或内存中。不同的存储引擎(表类型)提供不同的性能特性和可用功能。没有一种各方面都又具有最佳性能又具有各种功能的存储引擎。我们要做的是要根据数据的具体使用情形(需求)来选择合适的存储引擎,有的要读取速度快,有的要写入速度快,有的要具有高安全可靠性,有的要海量存储,等等。常用的存储引擎是innoDB(默认)和Myisam。

图片1.png

字段类型:当前字段中存储数据类型必须是一开始指定好。 一开始制定了int 数据就不能存储float

字段选项:设置当前字段的一些选项-是否可以为空,是否是主键,是否是唯一键,是否加备注等。

Null|not null:是否可以为空

Default:是否有默认值

Auto_increment:是否可以自动增长,必须是数字,如:需要给每条数据一个编号,原因是数据内容可以重复,编号不能重复。

Primary key:设置主键,数据内容不能重复,在查询数据主查询条件,一个表中一个主键,一般都是id。

unique [key]:设定为唯一(键),即表中所有行的的数据在该字段中的值不能有重复。

Comment:设置备注,给当前字段设置说明

深度截图_选择区域_20190208220959.png

可以看到成功创建了students表

查看表结构:desc 表名;

​ 字段名 类型 是否为空 索引 默认值 其它

深度截图_选择区域_20190208225332.png

drop table 表名; 删除表

查看所有表:show tables;

查看创建表语句:show create table 表名;

修改表名: alter table 旧表名 rename to 新表名;

添加新字段:alter table 表名 add 新字段 字段类型 字段选项;

如下可见新增了字段address

深度截图_选择区域_20190208230708.png

修改表字段(包括字段名):

Alter table 表名 change 旧字段名 新字段名 新字段类型 新字段选项;

修改字段类型和选项:

Alter table 表名 modify 字段名 新字段类型 新字段选项;

删除字段:alter table 表名 drop 字段名;

数据操作

插入数据

当我们需要向数据库表中插入一条新记录时,就必须使用INSERT语句。

语句:insert into 表名 【(字段1,字段2,字段3,…..)】 values(值1,值2,值3,……);

深度截图_选择区域_20190209170525.png

如果一个字段有默认值,那么在INSERT语句中也可以不出现

要注意,字段顺序不必和数据库表的字段顺序一致,但值的顺序必须和字段顺序一致。也就是说,可以写INSERT INTO students (name, age, id, sex) ...,但是对应的VALUES就得变成('张三', 18, null, '男')

还可以一次性添加多条记录

1
2
3
4
insert into students (id,name,age,sex) values
-> (null,'李四',22,'女'),
-> (null,'王五',19,'男'),
-> (null,'马六',19,'女');

深度截图_选择区域_20190209171504.png

字段列表可以省略:省略后的数据必须对应上创建时字段顺序

修改数据

要更新数据库表中的记录,必须使用UPDATE语句。

UPDATE语句的基本语法是:

1
UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;

深度截图_选择区域_20190209203228.png

WHERE子句中写出需要更新的行的筛选条件id=1

因此利用WHERE我们也可以一次更新多条记录:

UPDATE students SET name='大佬', score=77 WHERE id>=2 AND id<=4;

UPDATE语句中,更新字段时可以使用表达式。例如,把所有20岁以下的年龄加上5岁:

深度截图_gnome-terminal-server_20190209203900.png

删除数据

如果要删除数据库表中的记录,可以使用DELETE语句。

DELETE语句的基本语法是:

1
DELETE FROM <表名> WHERE ...;

Where条件必须添加,否则删除所有数据

如果我们要删除students id=1的数据

深度截图_选择区域_20190209204804.png

普通删除后保留删除的id号,不再让数据占用。

DELETE语句的WHEREUPDATE类似,同理DELETE也能利用WHERE也能一次删除多条记录:

DELETE FROM students WHERE id>=2 AND id<=4;

还可以使用

1
truncate table 表名

truncate不能加where条件 直接删除全部记录id索引从1开始

查询数据

前面多次使用过 这里就说说基本查询 以后有时间在扩展呀

要查询数据库表的数据,我们使用如下的SQL语句:

1
SELECT * FROM <表名>

使用SELECT * FROM students时,SELECT是关键字,表示将要执行一个查询,*表示“所有列”,FROM表示将要从哪个表查询,

查询某些字段

1
SELECT name,age,sex,........ FROM <表名>`

使用SELECT *表示查询表的所有列,使用SELECT 列1, 列2, 列3可以返回指定列,这种操作称为投影

in语句:单独查询某些数据

单独查询id=2 id=4的数据

1
select * from students where id in (2,4);

深度截图_选择区域_20190209224951.png

between语法:(表示该字段的值在给定的两个值之间,含该俩值)

查询id=2到id=4的值

1
select * from students where id between 2 and 4;

Like:模糊查询

字段名 like ‘要查询的字符’; 如果是直接写字符,配置查询字符必须一致。

字段名 like ‘%要查询的字符%’; %是匹配任何与之相似的字符

顾名思义 like=’像‘

可能有点难理解这里我创建了几条类似的数据

深度截图_选择区域_20190209231800.png

1
select * from students where name like '%大佬%';

深度截图_选择区域_20190209231901.png

这样子应该会更好理解

distinct(去重)

1
select distinct 字段 from 表名;

显示去重后的结果 distinct 必须跟在select后面

image-20220920103153966

分组查询

使用group by 对查询结果分组

如果对数据进行分组统计就需要使用group by

group by将表按列值进行分组 列的值相同为一组

1
SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;

image-20220920111451397

一目了然地看出各个班级的学生人数

排序

order by 进行排序

order by 默认是升序排列

1
SELECT id, name,  score FROM students ORDER BY score;//成绩从低到高排列

QQ截图20190723162215.png

那么如何进行倒序排列

desc表示倒序

1
SELECT id, name, gender, score FROM students ORDER BY score DESC;//成绩从高到低排列

QQ截图20190723162621.png

使用分组查询和统计后可使用HAVING子句来对查询结果进行进一步的刷选

一般情况下我们都是用where

但是分组后where使用较少而是用HAVING来代替

更细节的东西看廖雪峰老师的博客排序

使用limit限定结果行数

limit后面可以跟两个参数,第一个参数是开始的索引号,默认是0开始,第二个参数取的个数(长度)

1
select * FROM student LIMIT 0,3;//从第0行开始返回三条记录

QQ截图20190723164822.png

还可以使用offset(偏移)

1
select * from student limit 3 offset 0

从第0行开始返回三条记录

常见的sql统计函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
聚合函数(较常用)
count(*) 统计数量
max(字段名)最大值
min(字段名)最小值
sum(字段名)求和
avg(字段名)求平均值
运算函数
abs(x) 返回x的绝对值
floor(x) 返回小于x的最大整数值
round(x,y) 返回参数x的四舍五入的有y位小数的值
mod(x,y) 返回x/y的模
greatest(1,2,3,4,5) 返回集合中最大的值
least(1,2,3,4,5) 返回集合中最小的值
字符串函数
trim(str) 去除字符串两端的空格
upper(str) 字符串转大写

ifnull

image-20220920110311569

ifnull(comm,0) 将comm字段为空的替换为0

用户管理

修改账号密码

1
2
3
4
Dos命令下修改账号密码
mysqladmin -u root -p password 1234 //修改root的密码为1234
mysql命令修改账号密码
set password fro 'root'@'localhost' = password('1234')

创建用户

1
create user 'username'@'host'identified by 'password';

username表示要创建的用户名。

host指定该用户在哪个主机上可以登陆,本地用户直接写localhost如果想让该用户任意远程主机登陆 可以使用%。

password表示该用户的登陆密码

删除用户

1
drop user 'username'@'host';

字段类型

字符串型

QQ图片20191027084527.png

整数型

QQ截图20191027084640.png

浮点型

QQ截图20191027085047.png

视图

视图的优势在于 安全和高效

创建视图

1
2
3
CREATE VIEW <视图名> AS <SELECT语句>
//下面写个例子 创建视图,作用是获取表 users 中地址为保定的学生信息
create view p as select * from users where address="保定";

查询视图

创建完成以后我们查询视图

1
select * from  <视图名>;

删除视图

1
drop <视图名>;

多表查询

image-20220929084202218

image-20220929084409790

image-20220929085206988

image-20220929085309351

image-20220929085454223

image-20220929085558436

事务

事务用于保证数据的一致性 他有一组相关的dml语句组成该组dml语句要么全部成功执行,要么全部失败。如网上转账 就是典型的事务处理。

1
2
3
4
5
6
//开始事务
begin;
//提交
commit;
//回滚
rollback;

默认情况下 输入sql语句则自动提交

如果开启了事务,就是关闭了自动提交的功能改成了输入commit执行提交

比如老板要给我转账

1
2
3
4
5
6
7
begin;
update users set money=money-100 where name = 'boss';
update users set money=money+100 where name = 'Sch0lar';
//开启事务后 我执行修改语句 如果两条语句都没有问题 则提交
commit;
//如果有一个出现问题 name回滚
rollback;

因为要保证数据的一致性 必须全部成功 有一个错误则回滚

存储过程的创建和调用

存储过程就是存储一段代码,用来完成特定的功能

1
2
3
4
5
6
DELIMITER //
CREATE PROCEDURE GetAllProducts()
BEGIN
SELECT * FROM products;//存储过程主体
END //
DELIMITER ;

第一个命令是DELIMITER //,它与存储过程语法无关。 DELIMITER语句将标准分隔符 - 分号(;)更改为://。 在这种情况下,分隔符从分号(;)更改为双斜杠//。为什么我们必须更改分隔符? 因为我们想将存储过程作为整体传递给服务器,而不是让mysql工具一次解释每个语句。 在END关键字之后,使用分隔符//来指示存储过程的结束。 最后一个命令(DELIMITER;)将分隔符更改回分号(;)。

GetAllProducts() 存储名

调用存储过程

1
CALL GetAllProducts();	//GetAllProducts()是存储名

删除

1
DROP PROCEDURE GetAllProducts()//GetAllProducts()是存储名

数据库备份

1
2
mysqldump -u root -p xss > d:/test/test.sql
备份xss数据库到 d盘下test目录

还原数据库

1
2
3
4
5
6
//首先建立你要还原的数据库
create database xss;
//使用这个数据库
use xss;
//导入备份文件
source d:/test/test.sql;

总结

边学边记录 虽然可能效率会比较低 但是印象会很深 等以后忘了还可以回头看看

把大佬们当做目标 肝起来