Mysql数据库笔记

easy_tom

已于 2024-05-14 10:36:20 修改

阅读量224

点赞数

文章标签：数据库 mysql

于 2023-07-30 11:17:27 首次发布

本文链接：https://blog.csdn.net/easy_tom/article/details/132005013

版权

1.数据库简介

1.1 关系型数据库

Oracle、MySql、SQL Server、SQLite

1.2 数据库组成

表（由行和列组合成的二维表格）
行（记录）
列（字段）

1.3 SQL语言

结构化查询语言，分为DQL，DDL，DML，TPL

2.Navicat使用

图形化界面的操作，熟练掌握

连接测试，测试是否连接成功，首先要创建数据库
用鼠标新建数据库
打开数据库，新建查询

2.1 SQL语言的注释

单行注释用–

多行注释用/* */

3.SQL语言基础

3.1 MySql常用数据类型

整数：int
小整数：tinyint
小数：decimal
字符串：varchar
日期时间：datetime

3.2 表、字段、记录

表（table）：

表，数据库中存储数据的基本单位，表是一个由行和列组成的二维表结构
字段（filed）：

表中的列，在数据库中，叫做字段（filed）
记录（record）：

表中的一行，在数据库中，叫做记录（record）

3.3 创建数据库（create table）

语法

create table 表名{
	字段名 数据类型,
	字段名 数据类型,
	…
};

创建示例：

CREATE TABLE a(
	name VARCHAR(10)
);
CREATE TABLE b (
	NAME VARCHAR ( 10 ),
	height DECIMAL ( 5, 2 )
);
CREATE TABLE c ( 
	id INT, 
	name VARCHAR (20), 
	age TINYINT UNSIGNED 
);

3.4 插入数据（insert）

插入的常规语法

insert into 表名 values(…);

示例1：

INSERT into c VALUES (0,'张飞', 30);

指定字段插入的语法

insert into 表名 (字段1,…)values(值1,…);

示例2:

INSERT INTO c (id,name) VALUES (1,'老夫子');
INSERT INTO c (id,age) VALUES (2,'30');

写多条insert语句，语句之间用英文分号隔开

INSERT INTO c (id,name,age) VALUES (3,'张良',29);
INSERT INTO c (id,name,age) VALUES (4,'马超',24);
INSERT INTO c (id,name,age) VALUES (3,'吕布',19);

写一条insert语句，添加多条记录，数据之间用英文逗号隔开

INSERT INTO c VALUES (6,'季布',23),(7,'诸葛亮',22),(8,'刘禅',11);

写一条insert语句，设定指定字段值

insert into 表名 (字段1,…) values (值1,...),(值2,...)...;

示例

INSERT INTO c (id,name) VALUES (9,'墨子'),(10,'庄子'),(11,'孟子');

3.5 查询（select）

查询所有字段
语法

select * from 表名;

示例

SELECT * FROM c;

查询指定字段
语法

select 字段1,字段2,…from 表名;

示例

SELECT id,age FROM c;
SELECT name,id,age FROM c;

3.6 修改数据（update）

语法

update 表名 set 字段1=值1,字段2=值2… where 条件;

示例

UPDATE c set age = 50;
-- 带有条件的update语句
UPDATE c set age = 50,name = '李白' WHERE id = 11;
-- 修改name为刘禅的记录为刘备
update c set name = '刘备' where name ='刘禅';
-- id大于10的记录，长1岁
update c set age = age + 1 where id > 10;

3.7 删除记录（delete）

语法

delete from 表名 where 条件;

示例

-- 删除表c中id为6的记录
delete from c where id = 6;
-- 删除表c的所有记录
delete from c;
delete from c WHERE name = '诸葛亮';
DELETE from c WHERE age > 50;
-- 删除id小于3的记录
delete from c where id > 3;

3.8 删除表中的记录（truncate）

语法：（删除表中的记录）

truncate table 表名;

示例

-- 删除表c的所有记录
truncate table c;

3.9 delete和truncate的区别

在速度上，truncate>delete；
如果想删除部分数据用delete，注意带上where字句；
如果想保留表而将所有数据删除，自增长字段恢复从1开始，用truncate；

3.10 小结

增
- insert
删
- delete
改
- update
查
- select

3.11 删除表（drop table）

语法

drop table 表名;
drop table if exists 表名;

示例

-- 删除表a
drop table a;
-- 如果表a存在，就删除表a，如果不存在，什么也不做
DROP TABLE IF EXISTS a;
-- 如果表b存在，就删除表b，如果不存在，什么也不做
DROP TABLE IF EXISTS b;

4.字段的约束

4.1 常用约束介绍

主键（primary key）：值不能重复，auto_increment代表自动增长；
非空（not null）：此字段不允许填写空值；
唯一（unique）：此字段的值不允许重复；
默认值（default）：当不填写此值会使用默认值，如果填写时以填写为准；

4.2 创建带约束的语法格式

create table 表名{
	字段名 数据类型 约束,
	字段名 数据类型 约束,
	…
};

4.3 主键

主键的值不能重复
自增长，auto_increment
- 值会自动维护，自动增长

/*创建一个表d,字段要求如下；
id：数据类型为无符号整数，主键，自增长，
姓名：数据类型为字符串，长度为10；
年龄：数据类型为整数；*/
CREATE TABLE d (
	id int UNSIGNED PRIMARY KEY auto_increment,
	name VARCHAR(10),
	age INT
);
INSERT INTO d (name,age)VALUES('张三',23);
INSERT INTO d (name,age)VALUES('李四',23);
INSERT INTO d (name,age)VALUES('王老五',23);
-- 插入指定id的值
INSERT INTO d (id,name,age)VALUES(5,'王麻子',23);
-- 插入不指定id的值
INSERT INTO d (name,age)VALUES('李贵',23);
SELECT * FROM d;
-- 如果为不指定字段，主键自增长字段的值可以用占位符，0 或者null
INSERT INTO d VALUES (0,'王菲',30);
INSERT INTO d VALUES (null,'王刚',30);
INSERT INTO d VALUES (null,'李倩',20);
SELECT * FROM d;

4.4 非空

带有not null（非空）的字段，值不能为空；
语法

create table 表名(
	字段名 数据类型 not null;
    …
);

示例：

/*创建表e，字段要求：
id：数据类型为int unsigned （无符号整数）；
name 姓名：数据类型为 varchar（字符串）长度为10.not null（非空）， 
age年龄：数据类型为int（整数）；*/
create table e (
	id int unsigned,
 	name varchar(10) not null,
 	age int
);
INSERT INTO e VALUES (1,'张三',20);
-- INSERT INTO e (id,age) VALUES (1,20);
SELECT * FROM e;

4.5 唯一

唯一（unique）：此字段的值不允许重复；
语法

create table 表名(
	字段名 数据类型 unique;
    …   
);

示例

/*创建表f，字段要求如下：
id：数据类型为整数
name：数据类型为字符串，长度为10，唯一
age：数据类型为整数；*/
CREATE TABLE f (
	id INT,
	name VARCHAR(10) UNIQUE,
	age INT
);
INSERT INTO f VALUES (1,'张三',20);
INSERT INTO f VALUES (2,'李四',20);
SELECT * FROM f;

4.6 默认值

默认值（default）：当不填写此值时会使用默认值，如果填写时以填写为准；
语法

create table 表名(
	字段名 数据类型 default 值,
    …
);

示例

/*创建表g，字段要求如下：
id：数据类型为整数
name：数据类型为字符串，长度为10
age：数据类型为整数，default（默认值）30；*/
CREATE TABLE g (
	id INT,
	name VARCHAR(10),
	age INT DEFAULT 30
);
INSERT INTO g VALUES (1,'李四',20);
-- 插入的时候不指定age的值
INSERT INTO g (id,name)VALUES (2,'王权');
SELECT * FROM g;

5.别名与重复记录

5.1 准备工作

drop table if exists students;
create table students (
studentNo varchar(10) primary key,
name varchar(10),
sex varchar(1),
hometown varchar(20),
age tinyint(4),
class varchar(10),
card varchar(20)
);

insert into students values
('001', '王昭君', '女', '北京', '20', '1班', '340322199001247654'),
('002', '诸葛亮', '男', '上海', '18', '2班', '340322199002242354'),
('003', '张飞', '男', '南京', '24', '3班', '340322199003247654'),
('004', '白起', '男', '安徽', '22', '4班', '340322199005247654'),
('005', '大乔', '女', '天津', '19', '3班', '340322199004247654'),
('006', '孙尚香', '女', '河北', '18', '1班', '340322199006247654'),
('007', '百里玄策', '男', '山西', '20', '2班', '340322199007247654'),
('008', '小乔', '女', '河南', '15', '3班', null),
('009', '百里守约', '男', '湖南', '21', '1班', ''),
('010', '妲己', '女', '广东', '26', '2班', '340322199607247654'),
('011', '李白', '男', '北京', '30', '4班', '340322199005267754'),
('012', '孙膑', '男', '新疆', '26', '3班', '340322199000297655');

5.2 查询所有字段

示例

select * from students;

5.3 查询指定字段

示例

select card,name,sex from students;

5.4 字段的别名

给字段起一个别名，别名可以是中文
as可以省略，结果是一样的

-- 给字段取别名
-- select 字段名1 as 别名1，字段名2 as 别名2 from 表名;
select name as 姓名,sex as 性别,age as 年龄 from students;
select name 姓名,sex 性别,age 年龄 from students;

5.5 表的别名

通过表名 as 别名给表起一个别名，as可以省略

-- 通过as给表起别名
select * from students as s;
select * from students s;
select s.name,s.sex,s.age from students as s;

5.6 消除重复记录

在select后面的字段前使用distinct可以消除重复的记录,过滤select查询结果中的重复记录
语法

select distinct 字段 from 表名;

示例

select distinct sex from students;
select distinct sex,class from students;

6.条件查询

6.1 where

使用where对表中的数据筛选，符合条件的数据会出现在结果中
语法

selcet 字段1,字段2…from 表名 where 条件;

示例

-- 查询students表中学号studentNo等于'001'的记录
select * from students where studentNo = '001';
-- 查询students表中年龄age等于30的姓名name，班级class
select name 姓名, class 班级 from students where age = 30;

查询的基本规则
- select * 或者select 字段名控制查询返回的是列
- where 条件控制查询返回的是行

6.3 比较运算符

等于：=;大于：>;大于等于：>=;小于：>;小于等于：<=;不等于：!= 或 <>
示例

-- 查询小乔的年龄
select age from students where name='小乔';
-- 查询30岁以下的学生
select * from students where age < 30;
-- 查询家乡不在北京的学生
select * from students where hometown != '北京';
select * from students where hometown <> '北京';

6.4 逻辑运算符

and(且，同时符合对应的条件)
- 语法：条件1 and 条件2
or (或，符合其中的一个条件)
- 语法：条件1 or 条件2
not (非，不符合该条件)
- 语法：not 条件
示例

-- 查询年龄等于30的女生的记录(and)
select * from students where age < 30 and sex = '女';
-- 查询出女学生或者是1班的学生
select * from students where sex = '女' or class = '1班';
-- 查询非天津学生的记录
select * from students where hometown != '天津';
select * from students where hometown <> '天津';
select * from students where not hometown = '天津';

6.5 模糊查询

like关键字
%:匹配多个任意个字符
_:匹配任意单个字符
一般LIKE关键字只用来匹配字段类型为字符串的

示例

-- 例1:查询姓孙的学生
select * from students where name like '孙%';
-- 例2:查询姓孙且名字是一个字的学生
select * from students where name like '孙_';
-- 例3:查询姓名以'乔'结尾的学生
select * from students where name like '%乔';
-- 例4: 查询姓名中包含'白'的学生
select * from students where name like '%白%';

6.6 范围查找

in:查询非连续范围内的数据
示例

-- 查询家乡为北京或上海或广东的学生
select * from students where hometown in ('北京','上海','广东');
-- 相同实现方式
select * from students where hometown='北京' or hometown='上海' or hometown='广东';

between …and :查询连续范围内的数据(用来数值型字段中)
示例

-- 查询年龄在18到20之间的学生
select * from students where age between 18 and 20;
-- 相同实现方式
select * from students where age >=18 and age <= 20;

6.7 空判断

null 和 ''空字符串不一样
null:表示什么都没有
‘’:表示长度为0的字符串
空判断: is null
- 示例

-- 查询出学生身份证号为空的信息
select * from students where card is null;

非空判断: is not null
- 示例

-- 查询出学生身份证不为空的信息
select * from students where card is not null;

6.8 拓展

where语句在update与delete语句中同样有效

示例

-- 修改age为25,并且name为孙尚香的学生class为2班
update students set class = '2班' where age =18 and name = '孙尚香';
-- 删除class为1班并且age大于20的学生记录
delete from students where class = '1班' and age > 20;

6.9 排序

为了方便查看数据，可以对数据进行排序
字段的排序规则默认为升顺排列 (从小到大)
asc: 表示从小到大排序 (升序)
desc: 表示从大到小排序 (降序)
语法

select * from 表名 order by 字段1 asc|desc,字段2 asc|desc .......

示例

-- 查询所有学生信息，按年龄从小到大排序
select * from students order by age asc;
select * from students order by age;
-- 查询所有学生信息，按年龄从大到小排序
select * from students order by age desc;
-- 查询所有学生信息按年龄从大到小排序，年龄相同时，再按学号从小到大排序
select * from students order by age desc, studentNo;
-- 查询所有学生信息，按班级从小到大排序， 班级相同时，再按学号从小到大排序
select * from students order by class, studentNo;

当一条select语句出现了where和order by
- select * from 表名 where 条件 order by 字段1,字段2;
- 一定要把where写在order by 之前

SELECT * FROM students where sex= '男' order by class, studentNo desc;

7.聚合函数

为了快速得到统计数据，经常会用到以下一些聚合函数

注意：聚合函数不能在where后面的条件中使用

7.1 count计录总数

求表的总的记录数，括号中写 *与字段名，结果是相同的
语法

count(字段名)

示例

-- 查询学生表的总记录数
select count(*) from students;
select count(name) from students;
select count(class) from students;
select count(distinct class) from students;
select count(distinct sex) from students;
-- 查询女同学数量
select count(*) from students where sex = '女';

7.2 max最大值

查询对应字段的最大的值
语法

max(字段名)

示例

-- 查询学生的最大年龄
select max(age) from students;
-- 查询女生的最大年龄
select max(age) from students where sex = '女';
-- 查询1班学生的最大年龄
select max(age) from students where class = '1班';

7.3 min最小值

查询对应字段的最小的值
语法

min(字段名)

示例

-- 查询学生的最小年龄
select min(age) from students;
-- 查询女生的最小年龄
select min(age) from students where sex = '女';
-- 查询1班的最小年龄
select min(age) from students where class='1班';

7.4 sum求和

查询对应字段的值的总和
语法

sum(字段名)

示例

-- 查询学生的年龄总和
select sum(age) from students;
-- 查询女生的年龄总和
select sum(age) from students where sex = '女';
-- 查询1班的年龄总和
select sum(age) from students where class='1班';
-- 查询北京学生的年龄总和
select sum(age) from students where hometown='北京';

7.5 avg平均值

查询对应字段的值的平均数
avg忽略null值，该字段不参与计算平均值
语法

avg(字段名)

示例

-- 查询学生的平均年龄
select avg(age) from students;
-- 查询女生的平均年龄
select avg(age) from students where sex='女';
-- 查询1班的平均年龄
select avg(age) from students where class='1班';

8.数据分组

分组的目的是对每一组的数据进行统计(使用聚合函数)
语法

select 字段名1,字段名2,聚合函数... from 表名 group by 字段名1,字段名2....

示例

-- 查询各性别的人数
select sex,count(*)from students group by sex;
-- 查询各性别年龄最大的
select sex,max(age) from students group by sex;
-- 查询各班级的人数
select class,count(*) from students group by class;
-- 查询各班级中不同性别的人数
select class,sex,count(*) from students group by class,sex;
-- 查询各年龄的人数
select age,count(*) from students group by age;
-- 查询1班不同性别学生数量
select sex,count(*) from students where class = '1班' group by sex;

where和group by和order by的顺序
- select * from 表名 where 条件 group by 字段 order by 字段；

9.分组后的数据筛选

将分组之后的数据当成是一个表数据，然后再通过having 条件来对当前的表数据进行筛选
语法

select 字段名1,字段名2,聚合函数... from 表名 
group by 字段名1,字段名2...
having 字段,...聚合...

having 条件运算符跟where 条件运算符是一样的
在having后面可以使用聚合聚函数
where 是对 from 后面指定的表进行数据筛选，属于对原始数据的筛选。
having 是对 group by 的结果进行筛选。
having 后面的条件中可以用聚合函数，where后面不可以。
示例

-- 查询男生总人数
select sex,count(*) from students where sex='男';
select count(*)from students group by sex having sex='男';
-- 查询每个班级男生的总记录数
select class,sex,count(*) from students group by class,sex having sex='男';
-- 查询所有班级中不同性别的记录数大于1的信息
select class, sex,count(*) from students group by class,sex having count(*)>1;
-- 求班级人数大于3人的班级
select class from students group by class having count(*) > 3;

10.数据分页显示

获取部分行

limit 开始行，获取行数
语法

select * from 表名 where 条件 group by 字段 order by 字段 limit start, count

start 表示的是开始的记录，索引是从0开始。
0表示第一条记录count 表示的是从start开始，查询多少条记录
示例

-- 查询前3行的学生信息
select * from students limit 0,3;
select * from students limit 3;
-- 查询从第4行记录开始的前3条学生信息
select * from students limit 3,3;

当有where或者group by 或者order by，limit总是出现在最后

select name from students order by age desc limit 1;
select * from students where sex = '女' order by age limit 1;

分页查询实现

select * from students limit (n-1)*m, m;

n表示的是页数
n = 1, 2, 3, 4
m表示的是每页显示的记录数
m=3
(n-1)*m,m 是公式，并不是语法格式,不能直接写在SOL语句中，需要替换为具体的数字

11.连接查询

11.1 基本概念

当查询结果来源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的结果返回;
连接查询可以通过连接运算符 (连接条件)可以实现多个表查询；
内连接:查询的结果只显示两个表中满足连接条件的部分;
左连接: 连接两个表时，取的是左表中特有的数据，对于右表中不存在的数据，用null来填充
右连接: 连接两个表时，取的是右表中特有的数据，对于左表中不存在的数据，用null来填充。

11.2 内连接

语法

select * from 表名1 
inner join 表名2 on 表1.字段 = 表2.字段;
-- 隐式内连接语法
select * from students
where 表1.字段 = 表2.字段;

查询的是两个表的交集的数据
表1的列与表2的列一定是存在关联关系
内连接连接时可以连接多个表

示例

-- select * from a inner join b on a.id = b.id;
select * from students 
inner join scores on students.studentNo = scores.studentno;
-- 隐式内链接
select * from students,scores 
where students.studentNo = scores.studentno;

-- 显示指定字段查询结果
select name,courseNo,score from students 
inner join scores on students.studentNo = scores.studentno;
-- 表的别名在查询中的使用
select name 姓名,courseNo 课程编号,score 成绩 from students st
inner join scores sc on st.studentNo = sc.studentno;

带有where条件的内连接
语法

select * from 表1 
inner join 表2 表1.字段 = 表2.字段
where 条件;

示例

-- 查询王昭君的信息，要求只显示姓名，课程号，成绩
select name,courseNo,score from students st
inner join scores sc on st.studentNo = sc.studentno
where st.name = '王昭君';

带有and逻辑运算符的内连接查询
示例

-- 查询姓名为王昭君的并且成绩小于90的信息，要求只显示姓名，成绩
select name,score from students st
inner join scores sc on st.studentNo = sc.studentno
where st.name = '王昭君' and score < 90;

多表内连接查询
示例

-- 查询学生信息和成绩以及成绩对应的课程名称
select * from students st
inner join scores sc on st.studentNo = sc.studentno
inner join courses co on sc.courseNo = co.courseNo;

-- 查询所有学生的linux课程成绩，要求只显示姓名，成绩，课程名
select name 姓名,score 成绩,coursename 课程名 from students st
inner join scores sc on st.studentNo = sc.studentno
inner join courses co on sc.courseNo = co.courseNo
where co.coursename = 'linux';

带有order by 排序的内连接查询
示例

-- 查询成绩最高的男生信息，要求显示姓名、课程名、成绩
select name,coursename,score from students st
inner join scores sc on st.studentNo = sc.studentno
inner join courses co on co.courseNo = sc.courseNo
where sex = '男'
order by score desc limit 1;

11.3 左连接

语法

select * from 表1 left join 表2 on 表1.字段 = 表2.字段;

示例

-- 查询所有学生的信息及成绩，包括没有成绩的学生
select * from students st
left join scores sc on st.studentNo = sc.studentno;

11.4 右连接

语法

select * from 表1 right join 表2 on 表1.字段 = 表2.字段

右连接查询的是右表特有的数据，对于左表中不存在的数据用null来填充

示例

-- 查询所有的课程信息，包括没有成绩的课程
select * from scores
right join courses on scores.courseNo = courses.courseNo;

11.5 多表联合查询

同名字段处理方式：

如果一条select要用到多个表，表中有同名字段，就需要表名.字段名加以区分

select students.studentNo from students 
inner join scores on students.studentNo = scores.studentno;

12.自关联

自关联介绍
自连接的应用场景
- 省、市、区的信息，一般不会分开放在不同的表里面进行存储，而是放在同一个表当中。
要通过自关联进行查询时，当前自关联的表当中一定会存在两个相关联的字段自关联要用别名

语法格式

select from 表名 as 别名1 
inner join 表名 as 别名2 on 别名1.字段=别名2.字段

示例

-- 查询一共有多少省
select count(*) from areas where pid is null;
-- 查询一共有多少市
select count(*) from areas where pid is not null;
-- 查询河南省所有的市
select * from areas a1 
inner join areas a2 on a1.aid = a2.pid
where a1.atitle = '河南省';

13.子查询

将一条SOL查询的语句嵌入在其他的SOL语句中，被嵌入的SOL语句称之为子查询，其他的SOL称之为主查询
子查询select语句，要么是充当条件，要么充当数据源
子查询语句是一条完整的select语句，且可以单独执行

13.1 主查询

外层的第一条select语句为主查询

13.2 标量子查询

子查询返回的结果只有一个值（一行一列），称为标量子查询

13.3 列子查询

子查询返回的结果是一列（一列多行），这种称为列子查询

13.4 表级子查询

子查询返回的结果是多行多列（一个表），这种称为表级子查询

-- 查询大于平均年龄的学生记录
select * from students where age > (select avg(age) from students);
-- 查询20岁的学生的成绩
select * from scores where studentno in (select studentNo from students where age = 20);
-- 查询所有女生的信息和成绩
select * from (select * from students where sex = '女') st
inner join scores sc on st.studentNo = sc.studentno;

14.MySql内置函数

14.1 字符串函数

拼接字符串 concat(str1,str2…)

-- 把 12,34,'ab'拼接为一个字符串'1234ab
select concat(12,34,'ab');

包含字符个数 length(str)
- 如果字符串中包含 utf8 格式的汉字，一个汉字 length 返回 3

-- 计算字符串'abc'的长度
select length('abc');
-- 计算字符串'我和你'的长度
select length('我和你');
-- 计算字符串'我和you'的长度
select length('我和you');
-- 查询表 students 中 name 长度等于9(三个utf8 格式的汉字)的学生信息
select * from students where length(name)=9;

截取字符串
- left(str,len)返回字符串 str 的左端 len 个字符，中文与英文字母个数 len 一致;
- right(str,len)返回字符串 str 的右端 len 个字符，中文与英文字母个数 len一致;

-- 截取字符串'我和你abc'的左端 3 个字符
select left('我和你abc',3);
-- 截取字符串'我和你abc'的左端 4 个字符
select left('我和你abc',4);

-- 截取字符串'我和你abc'的右端 3 个字符
select right('我和你abc',3);
-- 截取字符串'我和你abc'的右端 4 个字符
select right('我和你abc',4);

substring(str,pos,len)返回字符串 str 的位置 pos 起len 个字符,pos从1开始计数;

-- 截取字符串'我和你 abc'从第2个字符开始的的3个字符
select substring('我和你 abc',2,3);
-- 截取 students 表中所有学生的姓
select left(name,1) from students;
-- 查询 students 表的 card 字段，截取出生年月日，显示李白的生日
select substring(card,6,8) from students where name = '李白';

去除空格
- ltrim(str)返回删除左侧空格的字符串 str;

-- 去除字符串' abcd '左侧空格 
select ltrim('     abcd       ');

rtrim(str)返回删除右侧空格的字符串 str;

-- 去除字符串' abcd '右侧空格 
select rtrim('   abcd      ');

trim(str)返回删除两侧空格的字符串str;

-- 去除字符串' abcd '两侧空格 
select trim('   abcd      ');

14.2 数学函数

求四舍五入值 round(n,d)
- n表示原数，d表示小数位置，默认为0

-- 1.653 四舍五入，保留整数位
select round(1.653);
-- 1.653 四舍五入，保留小数点后2位
select round(1.653,2);
-- 查询 students 表中学生的平均年龄，并四舍五入
select round(avg(age)) from students;

随机数rand()
- 值为0-1.0的浮点数

-- 返回一个从0到1.0的小数
select rand();
-- 随机排序
select * from students order by rand() limit 1;

14.3 日期时间函数

当前日期 current_date()

-- 返回当前日期
select current_date();

当前时间 current_time()

-- 返回当前时间
select current_time();

当前时间与日期 now()

-- 返回当前日期与时间
select now();

时间和日期函数的示例

-- 插入记录时，插入系统当前时间
create table a (id int, indate datetime);
insert into a values (1,'2010-09-10 12:01:02'); 
insert into a values (1, now()); select * from a;

15.存储过程

定义

存储过程 PROCEDURE,也翻译为存储程序,是一条或者多条 SQL 语句的集合。

创建存储过程
- 语法

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

示例

-- 创建存储过程stu（），查询students表所有学生的信息
delimiter //
create procedure stu()
begin
	select * from students;
end
// 
delimiter;
-- 调用存储过程stu
call stu();
-- 删除存储过程stu
drop procedure stu;
drop procedure stu if exists stu;

16.视图

定义
- 对于复杂的查询，在多个地方被使用，如果需求发生了改变，需要更改 sql语句，
  
  则需要在多个地方进行修改，维护起来非常麻烦;
视图本质就是对查询的封装;
创建视图
- 语法

create view 视图名称 as select 语句;

示例

-- 创建视图,名叫stu_nan，查询所有男生信息

删除视图
- 语法

drop view 视图名称;
drop view if exists 视图名称;

示例

drop view stu_nan;
drop view if exists stu_nan;

17.事务

事务广泛的运用于订单系统，银行系统等多种场景

事务是数据库维护一致性的单位，每个事务结束时，都能保持数据一致性

事务是多条更改数据操作的sql语句集合

没有写begin代表没有事务，没有事务的操作都是实时生效的

如果只写了begin，没有rollback，也没有commit，系统退出，结果是rollback

17.1 事务命令

开启事务
- begin;
- 开启事务后执行修改UPDATE或删除DELETE记录语句，变更会写到缓存中，而不会立即生效
回滚事务
- rollback;
- 放弃修改

如果开始了一个事务，执行了begin之后，没有rollback也没有commit，中间系统出现问题了，会默认执行rollback；

-- 开启事务，删除students中studentNo是001的记录，
-- 同时删除scores中studentno为001的记录，回滚事务，两个表同时放弃删除
BEGIN;
DELETE FROM students WHERE studentNo = '001';
DELETE FROM scores WHERE studentno = '001';
ROLLBACK;
SELECT * FROM students;
SELECT * FROM scores;

提交事务
- commit;
- 将修改的数据写入实际的表中

-- 开启事务，删除students中studentNo是001的记录，
-- 同时删除scores中studentno为001的记录，提交事务，两个表同时生效
BEGIN;
DELETE FROM students WHERE studentNo = '001';
DELETE FROM scores WHERE studentno = '001';
COMMIT;
SELECT * FROM students;
SELECT * FROM scores;

18.索引

当表中数据量很大时，查找数据会很慢，可以添加类似书的目录，加快查找，在数据库中这个叫索引（index）

18.1 创建索引

语法

create index 索引名称 on 表名(字段名称(长度));

指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致；
字段类型如果不是字符串，可以不填写长度部分。

-- 为表 students的age 字段创建索引，名为 age_index
create index age index on students(age);
-- 为表 students的 name 字段创建索引，名为 name index
create index name_index on students(name(10));
-- 查询表中age等于20的学生,会自动调用索引
select * from students where age = 20;

18.2 查看索引

语法

show index from 表名;

对于主键，系统会自动生成索引

示例

-- 查看students表的所有索引
show index from students;

18.3 删除索引

语法

drop index 索引名称 on 表名;

示例

-- 删除students表的索引name_index
drop index name_index on students;

优点:
- 索引大大提高了 SELECT 语句的查询速度
缺点:
- 虽然索引提高了查询速度，同时却会降低更新表的速度，例如对表进行INSERT、UPDATE 和 DELETE 操作。因为更新表时，不仅要保存数据，还要保存索引文件;
- 在实际应用中，执行 SELECT 语句的次数远远大于执行INSERT、UPDATE 和DELETE 语句的次数，甚至可以占到 80%~90%，所以为表建立索引是必要的。
- 在大量数据插入时,可以先刷除索引,再批量插入数据,最后再添加索引,这样就可以提高数据插入的效率。

19.MySql命令行

win+R，输入cmd，打开cmd窗口，进入到 mysql bin目录的路径下
第一步：启动mysql服务，可以通过“net start myql”命令实现；
第二步：先使用DOS命令进入mysql的安装目录下的bin目录中；
第三步：在命令行输入：mysql -u 用户名 -p密码；回车；-h表示服务器名，localhost表示本地，-hlocalhost 可不输入；-u为数据库用户名，root是mysql默认用户名；-p为密码，如果设置了密码，可直接在-p后链接输入，如：-p123456，用户没有设置密码，显示Enter password时，直接回车即可。）

cmd下的mysql操作

数据库有关操作

1.查询时间：select now();

2.查询当前用户：select user();

3.查询数据库版本：select version();

4.列出数据库：show databases;

5.选择数据库：use databaseName;

6.建立数据库：create database databaseName;

7.查看新创建的数据库信息：show create database databaseName;

8.删除数据库：drop database databaseName;

数据表有关操作

1.查看数据表存储引擎：show engines；

2.列出表格：show tables；

3.创建表：CREATE TABLE tableName(
c_num int (11) not null unique primary key auto_increment,
c_name varchar (50),
c_contact varchar (50),
c_city varchar (50),
c_birth datetime not null);

4.查看表结构：desc tableName;

5.显示表格列的属性：show columns from tableName;

6.修改字段类型：alter table tableName modify fieldName newFieldType;

7.字段改名：alter table tableName change oldFieldName newFieldName newFieldType;

8.表改名：alter table oldTableName rename newTableName;

9.复制表：create table tableName2 select * from ttableName1;

10.插入表中一行记录：insert into tableName values (“value1”,“value2”,“value3”…);

11.删除表中一行记录：delete from tableName where columnName=value; //不加where将删除全部数据

12.更新表中一行记录：update tableName set columnName=value where columnName=value;

13.查询表中所有记录：select * from tableName;

14.删除表：drop TABLE tableName;

easy_tom

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
Mysql数据库笔记

数据库学习笔记
复制链接

扫一扫