数据库MYSQL最全笔记

数据库中的元素：

​ 数据库：database

​ 表：table

​ 字段：field

​ 记录：record

创建表

​ Create table 表名（

字段名 数据类型

​ 字段名 数据类型

​ ）；

​ CREATE TABLE b(

​ NAME VARCHAR(10),

​ height DECIMAL(5, 2) -- 数据类型为小数，相当于float，其中两位小数

​ );

​ CREATE TABLE c(

​ id INT,

​ NAME VARCHAR(20),

​ age TINYINT UNSIGNED --无符号小整数（0-255）

​ );

插入数据

​ 语法：insert into 表名 values （值，值，值）

往表c插入一条记录

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

​ 指定字段插入，语法：insert into 表名 （字段名，字段名） values（值， 值）；

​ INSERT INTO c (id, NAME) VALUES (3, '曹操');

插入多条数据：（）

​ INSERT INTO c VALUES (5, '周瑜', 25);

​ INSERT INTO c (id, NAME) VALUES (19, '孙膑');

​ INSERT INTO c (NAME) VALUES ('诸葛亮');

一条语句插入多条数据：

	INSERT INTO c VALUES (10, '张三', 29),
    (11, '李四', 28),
	(12, '王五', 27);

select查询表

​ 语法：select * from 表名；

​ 查询表的所有字段

-- 查询表c的所有字段
select* from c;

​ 指定字段名查询

​ 语法：select 字段名， 字段名 from 表名;

-- 查询表c的所有字段，但是顺序自定义
select name, id, age from c;

UPDATE 修改数据

​ 语法： update 表名 set 字段=值， 字段=值 where 条件

​ 如果没有where条件代表修改表中的记录

-- 例1：修改表c，所有人的年龄（age字段）改为 50
update c set age = 50;

​ 带有条件的update语句

update c set name = '狄仁杰', age = 20 where id = 3

-- id大于10的记录，长一岁
update c set age = age + 1 where id >10;

DELETE 删除数据

-- 删除表c中id为3 的记录
DELETE FROM c WHERE id = 3

truncate table 删除表的所有数据

语法：trancate table 表名

-- 删除表c的所有数据
truncate table c

truncate和delete的区别

image-20220702190053996

drop table 删除表

​ 语法一：drop table 表名

-- 删除表a
DROP TABLE a;

​ 语法二： drop table if exists 表名

-- 如果表a存在，就删除表a，如果不存在，什么也不做
DROP TABLE if exists a;

字段约束

主键（数值只能是唯一的，不能重复）

​ 语法：字段 primary key

​ 如果在添加数据时，没有设置id值，那么id会自动给予值，自动增长aotu_increment

-- 设置id为主键
CREATE TABLE d (
id INT UNSIGNED PRIMARY KEY,
NAME VARCHAR(10),
age INT
);
-- 如果不指定字段，主键自增长字段的值可以用占位符，0或者null表示

非空

​ 非空not null

​ 这个字段必须优质，如果没有值，insert插入会失败

create table a(
id int unsigned,
name varchar(10) not null,
age int
);

唯一

​ 唯一unique

​ 字段的约束为唯一，表示字段的值不能重复

create table f(
    id int,
    name varchar(10) unique,
    age int
);

默认值

​ default值

​ 当一个字段有默认值约束，插入数据时，如果指定了值，那么默认值无效，如果没有指定值，会使用默认值

-- 指定age的默认值为30
create table f(
    id int,
    name varchar(10) unique,
    age int default 30
);

字段名的别名

​ 通过 字段名 as 别名 的语法，可以给字段奇异果别名，别名可以使中文

​ as可以省略

​ 字段名 as 别名 和字段名 别名 结果是一样的

select card as 身份证, name as 姓名, sex as 性别 from student;
select card 身份证, name 姓名, sex 性别 from student;
-- 给表起一个别名stu
select * from students as stu;

distinct过滤重复记录

​ 通过select distinct 字段名， 字段名 from 表名 来过滤select查询结果中的重复记录

select DISTINCT sex, class from students;

where条件查询

​ where 后面跟一个条件，实现由选择的查询

​ select * from 表名 where 条件

-- 查询students表中学号，studentNo 等于 '001'的记录所有字段
select * from students where studentsNo = '001';
-- 查询students表中学号，studentNo 等于 '001'的记录字段为name和sex
select name, sex from students where studentsNo = '001';

select查询的基本规律

​ select *或者select 字段名 控制了查询返回什么样的字段（列）

​ where 条件 控制了查询返回什么样的记录（行）

比较运算符

​ < 小于

​ > 大于

​ <= 小于等于

​ >= 大于等于

​ ！=和<> 不等于

逻辑运算符

​ and与

​ 条件1 and 条件2

​ 两个条件必须都满足

​ or或

​ 条件1 or 条件2

​ 两个条件只要一个满足即可

​ not非

​ not 条件

​ 条件成立，not以后就不成立，条件不成立，not以后就成立

like模糊查询

​ like

​ %表示任意多个字符

​ _表示任意一个字符

​ 字段名 like '字符%'

​ 指定字符开始，后面任意多个字符

--查询name姓名中以'孙'开头的学生记录
select * from students where name like '孙%';
--查询name姓名中以'孙'开头的学生记录,且名只有一个字的学生记录
select * from students where name like '孙_';

范围查找

​ in （值， 值， 值）

​ 非连续范围查找

​ between 开始值 and 结束值

​ 连续范围查找，包含开始值 包含结束值

-- 查询hometown 家乡是’北京‘或’上海‘或’广东‘的学生记录
select * from students where hometown i  ('北京', '上海', '广州');
-- 查询age年龄为25至30 的学生记录
select * from students where age between 25 and 30;

空判断

​ null不是0，也不是”，null在sql里面代表空，什么也没有

​ null不能用标胶运算符的判断

​ is null --是否为null

​ is not null --是否不为null

​ 不能用 字段名 = null或 字段名 ！= null 这些写法

order by 排序

​ order by 字段 [asc/desc]

​ asc 代表从小到大，升序，asc可以省略

​ desc 代表从大到小，不可以省略

-- 倒序
SELECT * FROM c ORDER BY age DESC;
-- 升序
SELECT * FROM c ORDER BY age ASC;

​ 当一条select语句出现了where和order by

​ select * from 表名 where 条件 order by 字段1， 字段2；

聚合函数

​ 聚合函数不能用到where后面的条件里

count求select返回的记录总数

​ count(字段名)

-- 查询学生总数
SELECT COUNT(*) FROM c;
SELECT COUNT(NAME) FROM c;

SELECT COUNT(DISTINCT age) FROM c;
select count(sex) from c where sex = '男';

max求select返回的记录中的最大值

​ max(字段名)

select max(age) from c;
select max(age) from c where sex = '女';

min查询最小值

​ min(字段名)

​ 查询指定字段的最小值

select min(age) from c;
select min(age) from c where sex = '女';

sum求和

​ sum(字段名)

​ 指定字段的值求和

select sum(age) from c;
select sum(age) from c where sex = '女';

avg求平均数

​ avg(字段名)

​ 指定字段的平均值

select avg(age) from c;
select avg(age) from c where sex = '女';

​ avg的字段中如果有null，null不做为分母计算平均值

数据分组

​ group by 字段名

​ select 聚合函数 from 表名 where 条件 group by 字段

​ select 聚合函数 from 表名 group by 字段

​ group by 就是配合聚合函数使用的

-- 分别查询男女同学的数量
select count(*) from c where sex = '男';
select count(*) from c where sex = '女';

select sex, count(*) from c group by sex;

group by 例子

where 和 group by 和order by 的顺序

​ select * from 表名 where 条件 group by 字段 order by 字段;

-- 分别查询各个年龄段的同学数量
select age, count(*) from c group by age;

​ where和 group by

-- 分别查询'1班'不同性别的同学数量
select sex, count(*) from c where class = '1班' group by sex;

分组聚合值之后的数据筛选

having 子句

​ 总是出现在group by 之后

​ select * from 表名 group by 字段 having 条件

-- 用where查询男生总数
-- where先筛选符合条件的记录，然后在聚合统计
select count(*) from c where sex = '男';

-- having 先分组聚合统计，在统计的结果中筛选
select count(*) from c group by sex having sex = '男';

having配合聚合函数的使用

​ where后面调价不能使用聚合函数，having可以使用聚合函数

-- 求班级人数大于3人的班级名字
select class from c group by class having count(*) > 3;

having与where 筛选的区别

​ where是对表的原始数据进行筛选

​ having是对group by 之后已经分组过的数据进行筛选

​ having可以使用聚合函数，where 不能用聚合函数

limit 显示指定的记录数

​ 语法：select * from 表名 where 条件 group by 字段 order by 字段 limit start, count;

​ limit总是是出现在select语句的最后，

​ start代表开始行号，行号从0开始编号

​ count 代表要显示多少行

​ 省略start，默认从0开始，从第一行开始

-- 查询前三行记录
select * from c limit 0, 3;
select * from c limit 3;
-- 查询从第4条记录开始的三条记录
select * from c limit 3, 3;

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

-- 查询年龄最小的女同学信息
select * from c where sex = '女' order by age limit 1;

数据分页显示

​ m每页显示多少条记录

​ n，第几页

​ （n-1）*m，m

​ 把计算结果写道limit后面

-- 每页显示4条记录，第3页的结果
select * from c limit 8, 4;
-- 每页显示4条记录，第2页的结果
select * from c limit 4, 4;

​ 已知每页记录数，求一张表需要几页显示完

​ 求总页数

​ 总页数/每页的记录数

​ 如果结果是整数，那么就是总页数，如果结果是小数，那么就在结果的整书上+1

链接查询

内连接

​ 把两张表相同的地方查询出来

image-20220703204542229

语法：

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

​ 内连接最重要的是，找对两张表要关联的字段

select * from a inner join b on a.id = b.id;

隐式内连接语法：

​ select * from 表1, 表2 where 两个表的链接条件

-- 隐式内连接
select * from students, scores where students.studentNo = scores.studentsNo;

​ 带有where的内连接

​ 语法：select * from 表1 inner join 表2 on 表1.字段 = 表2.字段 where 条件；

​ 带有and 的where的条件的内连接

select name, score from a inner join b on a.studentNo = b.studentNo
where a.name = '张飞' and b.score < 90;

多表内连接

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

​ 带有order by的联合查询

select name, score, courseName from students inner join scores 
on students.studentNo = scores.studentsNo
inner join courses on scores.courseNo = courses.courseNo
where sex = '男'
order by score desc limit 1;

左链接

​ 包括了内连接，同时还查询左表特有的内容

image-20220703204655996

语法：

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

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

右链接

​ 包括了内连接，同时害查询右表特有的内容

image-20220703204851863

语法：

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

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

多表联合查询，同名字段的处理方式

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

select students.studentNo from students left join scores 
on students.studentNo = scores.studentNo;

自关联

-- 查询有多少个省
select count(*) from areas where pid is null;
-- 查询有多少个市
select count(*) from areas where pid is not null;

​ 自关联是同一张表做链接查询

​ 自关联下，一定找到同一张表可关联的不同字段

-- 查询广东省的所有城市
select name from areas a1 inner join areas a2
on a1.id = a2.pid
where a1.name = '广东省';

写SQL三步法

​ 第一步：搭框架

​ 基本的celect语句框架搭建起来，如果有多表，把相应的多表也联合起来

​ 第二步：看条件

​ 决定where后面的具体条件

​ 第三步：返回字段名

​ select后面到底要显示什么字段

子查询

​ 子查询是嵌套到著查询里面的

​ 子查询作为著查询的数据源或者条件

​ 子查询是独立可以单独运行的查询语句

​ 子查询不能独立运行，依赖子查询的结果

-- 查询大于平均年龄的学生记录
select * from students where age > (select avg(age) from students);

标量子查询

​ 子查询返回结果只有一行，一列

列子查询

​ 子查询返回一列多行

select * from scores where studentNo in
(select studentNo from students where age = 30)

表级子查询

​ 子查询返回结果为多行多列

select * from (select * from students where sex = '女') stu
inner join scores sc
on stu.studentNo = sc.studentNo;

​

concat拼接字符串函数

​ concat(参数1,参数2,参数3,参数n)

​ 参数可以是数字，也可以是字符串

​ 把所有的参数连接成一个完整的字符串

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

length返回字符串字符的个数

​ 一个utf8格式的汉字，length返回3

select length('abc')   -- 结果是3
select length('我和me')   -- 结果是3+3+2=8

mysql内置函数可以在where条件后面使用

-- 查询表c中name长度等于9（三个utf8格式的汉字）的学生信息
select * from c where length(name) = 9;

left从字符串左(右)侧截取指定数量字符

​ left(字符串, n)、right(字符串, n)

​ n代表从字符串左侧截取n个字符

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

substring从字符串指定位置截取指定数量字符

​ substring(字符串, 起始位置,n)

​ 起始位置从1开始

​ n代表截取的数量

-- 结果一样的
SELECT SUBSTRING('我和你abcd', 3, 4)
SELECT SUBSTRING('我和你abcd', -5, 4)

内置函数可以用在select显示的字段名中

-- 截取students表中所有学生的姓
select left(name, 1) from students;
select substring(name, 1, 1) from students;

ltrim去除字符串左侧空格

​ ltrim(带空格的字符串)

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

rtrim去除字符串右侧空格

​ rtrim(带空格的字符串)

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

trim去除字符串两侧空格

​ trim(带空格的字符串)

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

round四舍五入

​ round(数字, d)

​ d代表要保留的小数位，省略d默认认为只保留整数部分

select round(1.633, 2);
-- 查询学生的平均年龄，并四舍五入
select round(avg(age), 2) from students;

rand随机数

​ rand()

​ 每次运行会产生一个从0到1之间的浮点数

​ 经常用rand对一张表进行随机排序

​ order by rand()

-- 从学生表中随机抽出一个学生
select * from students order by rand() limit 1;

current返回系统的日期、时间等

current_date返回系统日期

​ current_date()

current_time返回系统时间

​ current_time()

now返回系统日期和时间

​ now()

select current_date();
select current_time();
select now();
-- 插入数据时可以用到
insert into a values (1, now());

存储过程

-- 创建存储过程 stu(),查询students表所有学生信息
create procedure str()
begin
	select * from students;
end

-- 调用存储过程
call stu();

-- 删除存储过程，删除的时候不用写名字后面的()
drop procedure stu;
drop procedure if exists stu;

视图

​ 视图就是对select语句的封装

​ 视图可以理解为一张只读的表，针对视图只能用select，不能用delete和update

-- 创建一个视图，查询所有男生的信息
create view stu_man as
select * from c where sex = '男';

-- 使用视图
select * from stu_man inner join scores 
on stu_man.studentNo = scores.stidentNo;

--删除视图
drop view stu_man;
drop view if exists stu_man;

事务

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

​ 一个集合数据有一致性，要么就都失败，要么久都成功

​ begin-- 开始事务

​ rollback-- 回滚事务，放弃对表的修改

​ commit-- 提交事务，对表的修改生效

回滚事务的操作

​ 注意：如果engine=myisam是回滚不了的，要是innoDB才行

-- 开始事务
BEGIN;
INSERT INTO d VALUES (3, 'zhangfei', 29);
-- 回滚事务，放弃更改
ROLLBACK;
SELECT * FROM d;

提交事务commit

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

索引（index）

​ index

​ 给表简历索引，目的是加快select查询的速度

​ 如果一个表记录少，不用索引

​ 表的记录特别多，如果没有索引，select语句效率会非常低

创建索引语法

​ create index 索引名 on 表名(字段)

​ 如果字段为字符串，需要写明创建表字段的时候字符串的长度

-- 为表students的name字段创建索引，名为name_index
create index name_index on students (name(10))

-- 自动调用索引
-- 不需要显示的写调用索引的语句，只要where条件后面用到的字段建立了索引，那么系统自动调用
select * from students where name = '李白';

查看索引

​ 语法：show index from 表名

​ 对于主键，系统会自动建立索引

删除索引

​ 语法：drop index 索引名 on 表名

索引的优缺点

优点

​ 提高select的查询速度

缺点

​ 降低update，delete和insert语句的执行速度

​ 项目中80%以上是select，所以index是必须的

​ 在实际工作中，如果涉及到大量的数据修改操作，修改之前把索引删除，修改完成之后再把索引建立起来

基于命令行的mysql

​ mysql -h mysql服务器的地址 -u 用户名 -p

​ -h 如果是使用本机的mysql，-h可以省略

mysql登录之后的常用命令

show databases

​ 显示系统所有的数据库

use 数据库名

​ 使用指定的一个数据库

-- 使用mydb数据库
use mydb

show tables

​ 查看指定数据库有多少个表

如果命令行默认字符集与数据库默认字符集不同

​ 在Windows默认字符集是gbk

​ set names gbk

​ 高数mysql，客户端用到字符集是gbk

查看表的字段结构

​ desc 表名;

​ 在命令中每条sql语句用;结尾

创建和删除数据库

​ 创建数据库：create database 数据库名 default charset 字符集

-- 创建数据库
create database mytest default charset utf8;
-- 删除数据库mytest
drop database mytest;
drop database if exists mytest;
posted @ 2022-09-03 10:32  超级无敌代码人  阅读(200)  评论(0)    收藏  举报