MySQL数据库基本知识

已于 2023-11-11 17:08:44 修改
阅读量1.7k 收藏 12
点赞数 18
分类专栏： 数据库 文章标签： 数据库 mysql navicat
于 2021-08-04 15:42:31 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43956649/article/details/119384547
版权

MySQL数据库基本知识

一、基本概念

1.1 概念

数据库的作用，把数据以表的形式存储起来，方便查询

1.2 RDBMS

关系型数据库系统
核心是以二维表存储数据
行：一条记录，一个事物的信息
列：字段，一个事物的某一个属性
一个表中的所有行是一类事物的集合
一个数据库由很多表组成

1.3 SQL

结构化查询语言，用于关系型数据库（不区分大小写）
重点是查询
1.4 MySQL
关系型数据库管理系统
开源 免费 支持多平台

二、Navicat使用

2.1 数据类型

整数：int，有符号范围（-2147483648 to 2147483647），无符号范围（0 to 4294967295），长度没有作用
小数：decimal，如decimal（5，2）表示共存5位数，小数占2位，整数占3位
字符串：varchar，范围（0 to 65535），如varchar（3）表示最多存3个字符，一个中文或一个字母或符号都占一个字符

2.2 约束

主键：int类型，无符号，自动递增，唯一的标识一条记录

三、SQL语言

3.1 创建表

例：创建学生表，字段要求如下：
姓名（长度为10）
create table student(
name varchar(10)
)

例：创建学生表，字段要求如下：
姓名（长度为10），年龄
CREATE TABLE student2(
name VARCHAR(10),
age INT UNSIGNED
)

例：创建学生表，字段要求如下：
姓名（长度为10），年龄，身高（保留小数点2位）
CREATE TABLE student3(
id int unsigned primary key auto_increment,
name VARCHAR(10),
age INT UNSIGNED,
height decimal(5,2)
)

3.2 删除表

格式一：drop table 类名
格式二：drop table if exists 表名

如果表存在则删除，如果不存在报错
drop table student

如果表存在则删除，如果不存在不删除，不报错
drop table if exists student2

先删除表再创建表
drop table if exists student3;
CREATE TABLE student3(
id int unsigned primary key auto_increment,
name VARCHAR(10),
age INT UNSIGNED,
height decimal(5,2)
)

3.3 增加数据

insert into 表名 values(…)
例：插入一个学生，设置所有字段的信息

给所有的字段设置数据
insert into student3 values(0,‘亚瑟’,20,123.1)
insert into student3 values(default,‘亚瑟’,20,123.1)
insert into student3 values(null,‘亚瑟’,20,123.1)

给指定的字段设置数据
insert into student3(name) values(‘鲁班’)
insert into student3(name,age) values(‘鲁班’,34)

插入多条数据

写多条插入语句
insert into student3 values(0,‘亚瑟5’,20,123.1);
insert into student3 values(0,‘亚瑟6’,20,123.1);
insert into student3 values(0,‘亚瑟7’,20,123.1)
一条语句插入多条记录
insert into student3(name) values(‘鲁班5’),(‘鲁班6’),(‘鲁班7’)

3.4 修改数据

格式：update 表名 set 列1=值1,列2=值2 … where 条件
例：修改id为5的学生数据，姓名改为 狄仁杰，年龄改为20

update student3 set age=19 where id=5
update student3 set name=‘狄仁杰’,age=20 where id=5

3.5 删除数据

格式：delete from 表名 where 条件
例：删除id为6的学生数据

delete from student3_copy1
delete from student3 where id=6

逻辑删除
1.设计表，给表添加一个字段isdelete/isdel，1代表删除，0代表没有删除
2.把所有的数据isdelete都改为0
3.要删除某一条数据时，更新它的isdelete为1
4.当要查询数据时，只查询isdelete为0的数据
update student3 set isdelete=0

update student3 set isdelete=1 where id=1

select * from student3 where isdelete=0

3.6 查询数据

3.6.1 简单查询

查询指定列，如果没有写where会把指定列的所有数据都显示出来
– select name,sex,hometown from students
给查询出来的字段起别名
– select name as 姓名,sex as 性别,hometown as 家乡 from students
给表起别名
– select s.name,s.sex,s.hometown from students as s

查询学生的性别有哪几种
去除重复数据，如果有重复的数据只显示一条
– select distinct sex from students
去除重复数据，当查询多个字段时，只有一行记录跟另一行记录完全一样时才是重复数据
– select distinct sex,class from students
– select distinct * from students

3.6.2 条件查询

3.6.2.1 比较运算

例1：查询小乔的年龄
– select name,age from students where name=‘小乔’

例2：查询20岁以下的学生
– select * from students where age<20

例3：查询家乡不在北京的学生
– select * from students where hometown!=‘北京’
– select * from students where hometown<>‘北京’

3.6.2.2 逻辑运算

例1：查询年龄小于20的女同学
– select * from students where age<20 and sex=‘女’

例2：查询女学生或’1班’的学生
– select * from students where sex=‘女’ or class=‘1班’

例3：查询非天津的学生
– select * from students where hometown!=‘天津’
– select * from students where not hometown=‘天津’

3.6.2.3 模糊查询

关键字 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 ‘%白%’

3.6.2.4 范围查询

in 表示在一个非连续的范围内
例1：查询家乡是北京或上海或广东的学生
– select * from students where hometown=‘北京’ or hometown=‘上海’ or hometown=‘广东’
– select * from students where hometown in(‘北京’,‘上海’,‘广东’)

between …and… 表示在一个连续的范围内
例2：查询年龄为18至20的学生
– select * from students where age>=18 and age<=20
– 小值在前面
– select * from students where age between 18 and 20

3.6.2.5 空判断

例1：查询没有填写身份证的学生
– select * from students where card is null

– select * from students where card=‘’

例2：查询填写了身份证的学生
– select * from students where not card is null

– insert into students(studentNo) values(20)

– insert into students(studentNo,name) values(21,null)

– insert into students(studentNo,name) values(22,‘’)

3.6.3 排序

例1：查询所有学生信息，按年龄从小到大排序
– select * from students order by age asc

例2：查询所有学生信息，按年龄从大到小排序，年龄相同时，再按学号从小到大排序
– select * from students order by age desc,studentNo

题目：
1.查询所有学生信息，按班级从小到大排序，班级相同时，再按学号从小到大排序
– select * from students order by class,studentNo
对中文数据进行排序
– select * from students order by convert(name using gbk)

3.6.4 聚合函数

例1：查询学生总数
count(*) 代表一行记录任意字段有值，就会统计在内
count(card) 代表只统计card字段个数，如果有null值不会被统计

– select count(*) from students
– select count(card) from students

例2：查询女生的最大年龄
– select max(age) from students where sex=‘女’

例3：查询1班的最小年龄
– select min(age) from students where class=‘1班’

例4：查询北京学生的年龄总和
– select sum(age) from students where hometown=‘北京’

例5：查询女生的平均年龄
– select avg(age) from students where sex=‘女’

3.6.5 分组

3.6.5.1 分组

例1：查询各种性别的人数
– select sex,count(*) from students group by sex

例2：查询各种年龄的人数
– select age,count(*) from students group by age

题目：
1.查询各个班级学生的平均年龄、最大年龄、最小年龄
– select class,avg(age),max(age),min(age) from students group by class
2.查询各个班级男女的人数
– select class,sex,count(*) from students group by class,sex

3.6.5.2 分组后的数据筛选

where是对from后面指定的表进行数据筛选，属于对原始数据的筛选
having是对group by的结果进行筛选
例1：查询男生总人数
– select count() from students where sex=‘男’
注：having要用在group by后面，没有group by的话不能用having
– select sex,count() from students group by sex having sex=‘男’

题目：
1.查询1班除外其他班级学生的平均年龄、最大年龄、最小年龄
– select class,avg(age),max(age),min(age) from students group by class having class!=‘1班’
– select class,avg(age),max(age),min(age) from students where class!=‘1班’ group by class

3.6.6 分页

3.6.6.1 获取部分行

例1：查询前3行学生信息
– select * from students limit 0,3

– select * from students limit 4,4
按照排序后的顺序获取
– select * from students order by age limit 0,3
题目：
1.查询第4到第6行学生信息
– select * from students limit 3,3

3.6.6.2 分页

例1：要求每页显示3条
– select count(*) from students
12/3 获取总页数

第一页
– select * from students limit 0,3 3*(1-1)
第二页
– select * from students limit 3,3 3*(2-1)
第三页
– select * from students limit 6,3 3*(3-1)
第四页
– select * from students limit 9,3 3*(4-1)
下面两种写法是一个意思
select * from students limit 7
select * from students limit 0,7

3.6.7 连接查询

3.6.7.1 等值连接

等值连接：笛卡尔积，通过where再进行过滤；笛卡尔积会生成的一个临时表，占用内存
例1：查询学生信息及学生的成绩
– select * from students,scores where students.studentNo=scores.studentno

3.6.7.2 内连接

内连接：先判断条件，不会产生笛卡尔积，不会产生临时表，性能高
select * from students inner join scores on students.studentNo=scores.studentno

例2：查询课程信息及课程的成绩
– select * from courses,scores where courses.courseNo=scores.courseNo
– select * from courses inner join scores on courses.courseNo=scores.courseNo

3.6.7.3 3个表连接

例3：查询学生信息及学生的课程对应的成绩
– SELECT
– *
– FROM
– students,
– courses,
– scores
– WHERE
– students.studentNo = scores.studentno
– AND scores.courseNo = courses.courseNo

– SELECT
– *
– FROM
– students
– INNER JOIN scores ON students.studentNo = scores.studentno
– INNER JOIN courses ON scores.courseNo = courses.courseNo

例4：查询王昭君的成绩，要求显示姓名、课程号、成绩
– SELECT
– students.NAME,
– courses.NAME AS 课程名,
– courses.courseNo,
– scores.score
– FROM
– students
– INNER JOIN scores ON students.studentNo = scores.studentno
– INNER JOIN courses ON scores.courseNo = courses.courseNo
– WHERE
– students.NAME = ‘王昭君’

3.6.7.4 连接查询后过滤

例5：查询王昭君的数据库成绩，要求显示姓名、课程名、成绩
– SELECT
– students.NAME,
– courses.NAME,
– scores.score
– FROM
– students,
– scores,
– courses
– WHERE
– students.studentNo = scores.studentno
– AND scores.courseNo = courses.courseNo
– AND students.NAME = ‘王昭君’
– AND courses.NAME = ‘数据库’

– SELECT
– students.name,
– courses.NAME AS 课程名,
– scores.score
– FROM
– students
– INNER JOIN scores ON students.studentNo = scores.studentno
– INNER JOIN courses ON scores.courseNo = courses.courseNo
– WHERE
– students.NAME = ‘王昭君’ and courses.NAME=‘数据库’

例6：查询所有学生的数据库成绩，要求显示姓名、课程名、成绩
– SELECT
– students.NAME,
– courses.NAME AS 课程名,
– scores.score
– FROM
– students
– INNER JOIN scores ON students.studentNo = scores.studentno
– INNER JOIN courses ON scores.courseNo = courses.courseNo
– WHERE
– courses.NAME=‘数据库’

例7：查询男生中最高成绩，要求显示姓名、课程名、成绩
– SELECT
– students.NAME,
– courses.NAME AS 课程名,
– scores.score
– FROM
– students
– INNER JOIN scores ON students.studentNo = scores.studentno
– INNER JOIN courses ON scores.courseNo = courses.courseNo
– WHERE
– sex=‘男’ order by scores.score desc limit 1

3.6.7.5 左连接

例1：查询所有学生的成绩，包括没有成绩的学生
– 左连接，join 前面的表称为左表，join 后面的表称为右表
– select * from students
– left join scores on students.studentNo=scores.studentno

例2：查询所有学生的成绩，包括没有成绩的学生，需要显示课程名
– select * from students
– left join scores on students.studentNo=scores.studentno
join 前面生成的结果作为左表，join 后面是右表
– left join courses on scores.courseNo = courses.courseNo

3.6.7.6 右连接

例1：查询所有课程的成绩，包括没有成绩的课程
– insert into courses values(0,‘语文’),(0,‘数学’)
– join 前面生成的结果作为左表，join 后面是右表,把右表的数据全部显示
– select * from scores
– right join courses on scores.courseNo = courses.courseNo

例2：查询所有课程的成绩，包括没有成绩的课程，包括学生信息
– select * from scores
– right join courses on scores.courseNo = courses.courseNo
– left join students on scores.studentno=students.studentNo

例3：查询所有学生的成绩，包括没有成绩的学生，需要显示课程名
– select * from scores
– inner join courses on scores.courseNo = courses.courseNo
– right join students on scores.studentno=students.studentNo

– select * from scores
– right join students on scores.studentno=students.studentNo
– left join courses on scores.courseNo = courses.courseNo
在这里插入图片描述

3.6.8 自关联

3.6.8.1 自关联查询

例1：查询河南省的所有城市
– select * from areas,areas_copy1 where areas_copy1.pid=areas.aid and areas.atitle=‘河南省’

数据源，从一个表中查询多次，必须起别名
– select * from areas as sheng,areas as shi where sheng.aid=shi.pid and sheng.atitle=‘河南省’

3.6.8.2 自关联关联3次

例2：查询郑州市所有的区
– select * from areas as sheng,areas as shi where sheng.aid=shi.pid and sheng.atitle=‘郑州市’

例3：查询河南省所有的区县
– select * from areas as sheng,areas as shi,areas as qu where sheng.aid=shi.pid and sheng.atitle=‘河南省’ and shi.aid=qu.pid

3.6.9 子查询

3.6.9.1 标量子查询

标量子查询：子查询返回的结果是一个值，一行一列
例1：查询大于平均年龄的学生
– select * from students where age>(select avg(age) from students)

例2：查询最小年龄的人
– select * from students where age=(select min(age) from students)

例3：查询王昭君的成绩，要求显示成绩
– select score from scores where studentno=(select studentNo from students where name=‘王昭君’)

例4：查询王昭君数据库成绩，要求显示成绩
– select score from scores where studentno=(select studentNo from students where name=‘王昭君’) and courseNo=(select courseNo from courses where name=‘数据库’)

3.6.9.2 列子查询

列子查询：子查询返回的结果是一列多行
例1：查询18岁的学生的成绩，要求显示成绩
– select * from scores where studentno in (select studentNo from students where age=18)

3.6.9.3 行子查询

行子查询：子查询返回的结果是一行多列
例1：查询男生中年龄最大的学生信息
– select * from students where sex=‘男’ and age=26
– select * from students where (sex,age)=(‘男’,26)
– select * from students where (sex,age)=(select sex,age from students where sex=‘男’ order by age desc limit 1)

3.6.9.4 表子查询

表子查询：子查询返回的结果是一个表，多行多列
例1：查询数据库和系统测试的课程成绩
– select * from scores
– inner join courses
– on scores.courseNo=courses.courseNo
– where courses.name in(‘数据库’,‘系统测试’)

– select * from scores
– inner join
把查询出来的结果当作数据源使用
– (select * from courses where name in(‘数据库’,‘系统测试’)) as c on scores.courseNo=c.courseNo

3.6.9.5 子查询中关键字

例1：查询18~20岁的学生信息
– select * from students where age in (select age from students where age between 18 and 20)
=any 等于 in 等于 =some
– select * from students where age=any (select age from students where age between 18 and 20)
– select * from students where age=some (select age from students where age between 18 and 20)
大于any = 大于some 大于子查询返回的结果中的任意一个值
– select * from students where age>any (select age from students where age between 18 and 20)
– select * from students where age>some (select age from students where age between 18 and 20)

– select * from students where age<all (select age from students where age between 18 and 20)

3.6.9.6 数据分表

– create table goods_cates(
– id int unsigned primary key auto_increment,
– cate_name varchar(10)
– )

– select cate from goods group by cate

– insert into goods_cates values(0,‘笔记本’),(0,‘游戏本’),(0,‘超极本’),(0,‘平板电脑’),(0,‘台式机’),(0,‘服务器/工作站’),(0,‘笔记本配件’)

把查询出来的数据插入到另一个表中
– insert into goods_cates(cate_name) select cate from goods group by cate

创建品牌表
– create table goods_brands(
– id int unsigned primary key auto_increment,
– brand_name varchar(10)
– )
– select distinct brand_name from goods

– create table … select …

– create table goods_back select * from goods

– create table goods_brands_back(
– id int unsigned primary key auto_increment,
– name varchar(10)
– )
– 查询出来的列必须对应表中的字段名，如果在表中找不到同名字段，会创建一个字段
– select distinct brand_name as name from goods

– select * from goods
– inner join goods_cates on goods.cate=goods_cates.cate_name

– update goods
– inner join goods_cates on goods.cate=goods_cates.cate_name
– set goods.cate=goods_cates.id

– select * from goods
– inner join goods_brands on goods.brand_name=goods_brands.brand_name

– update goods
– inner join goods_brands on goods.brand_name=goods_brands.brand_name
– set goods.brand_name=goods_brands.id

四、高级

4.1 命令行客户端

1.查看所有数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| ceshi              |
| db_base_project    |
| db_it_class        |
| db_online_shop     |
| information_schema |
| learn_db           |
| mooc               |
| music              |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| test               |
| test1              |
| world              |
| yymusic            |
+--------------------+
16 rows in set (0.00 sec)

2.使用数据库（重要！！！）

mysql> use ceshi;
Database changed

3.在ceshi数据库下执行SQL语句

mysql> select * from students;
+-----------+----------+------+----------+------+-------+--------------------+
| studentNo | name     | sex  | hometown | age  | class | card               |
+-----------+----------+------+----------+------+-------+--------------------+
| 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       | 李白     | 男   | 北京     |   15 | 4班   | 340322199005267754 |
| 012       | 孙膑     | 男   | 新疆     |   26 | 3班   | 340322199000297655 |
+-----------+----------+------+----------+------+-------+--------------------+
12 rows in set (0.00 sec)

4.查看当前使用的数据库

mysql> select database();
+------------+
| database() |
+------------+
| ceshi      |
+------------+
1 row in set (0.00 sec)

5.创建数据库

mysql> create database test1 charset='utf8';
Query OK, 1 row affected, 1 warning (0.03 sec)

6.删除数据库

mysql> drop database test1;
Query OK, 0 rows affected (0.02 sec)

7.查看当前数据库下有多少表

mysql> show tables;
+-----------------+
| Tables_in_ceshi |
+-----------------+
| areas           |
| areas_copy1     |
| courses         |
| goods           |
| goods_brands    |
| goods_cates     |
| heros           |
| scores          |
| stu             |
| student3        |
| student3_copy1  |
| students        |
+-----------------+
12 rows in set (0.00 sec)

8.查看表的结构

mysql> desc students;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| studentNo | varchar(10) | NO   | PRI | NULL    |       |
| name      | varchar(10) | YES  |     | NULL    |       |
| sex       | varchar(1)  | YES  |     | NULL    |       |
| hometown  | varchar(20) | YES  |     | NULL    |       |
| age       | tinyint(4)  | YES  |     | NULL    |       |
| class     | varchar(10) | YES  |     | NULL    |       |
| card      | varchar(20) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

mysql> desc goods_cates;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| cate_name | varchar(10)      | YES  |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

另外一种查看创建表的语句

mysql> show create table goods_cates;
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                      |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| goods_cates | CREATE TABLE `goods_cates` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `cate_name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8 |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

9.中文乱码的解决方法

set charset gbk;

10.创建表

mysql> create table test1(
    -> id int,
    -> name varchar(10)
    -> );
Query OK, 0 rows affected (0.09 sec)

11.删除表

mysql> drop table test1;
Query OK, 0 rows affected (0.03 sec)

12.备份数据库（要用管理员身份运行命令提示符）

C:\WINDOWS\system32>cd C:\Program Files\MySQL\MySQL Server 8.0\bin

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqldump -uroot -proot ceshi>C:\Users\DELL\Desktop\back.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

13.恢复数据库

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -uroot -proot back<C:\Users\DELL\Desktop\back.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

4.2 内置函数

4.2.1 字符串函数

1.拼接字符串

select name,hometown,concat(name,'的家乡是',hometown) from students

name	hometown	concat(name,'的家乡是',hometown)
王昭君	北京	        王昭君的家乡是北京
诸葛亮	上海	        诸葛亮的家乡是上海
张飞	南京	        张飞的家乡是南京
白起	安徽	        白起的家乡是安徽
大乔	天津	        大乔的家乡是天津
孙尚香	河北	        孙尚香的家乡是河北
百里玄策	山西	        百里玄策的家乡是山西
小乔	河南	        小乔的家乡是河南
百里守约	湖南	        百里守约的家乡是湖南
妲己	广东	        妲己的家乡是广东
李白	北京	        李白的家乡是北京
孙膑	新疆	        孙膑的家乡是新疆

2.包含字符个数

一个中文长度是3，其他的字符都是1
select length('我')
select * from students where length(name)=6

studentNo	name	sex	hometown	age	class	card
003	        张飞	男	南京	        24	3班	340322199003247654
004	        白起	男	安徽	        22	4班	340322199005247654
005	        大乔	女	天津	        19	3班	340322199004247654
008	        小乔	女	河南	        15	3班	
010	妲己	女	广东	        26	2班	340322199607247654
011	李白	男	北京	        15	4班	340322199005267754
012	孙膑	男	新疆	        26	3班	340322199000297655

3.截取字符串

截取字符串左边的2个字符
select left('abc',2)
截取字符串右边的2个字符
select right('abc',2)
截取字符串从第1个位置开始的2个字符
select substring('abc',1,2)
select name,sex,concat(left(name,1),'**') from students


name	sex	concat(left(name,1),'**')
王昭君	女	王**
诸葛亮	男	诸**
张飞	男	张**
白起	男	白**
大乔	女	大**
孙尚香	女	孙**
百里玄策	男	百**
小乔	女	小**
百里守约	男	百**
妲己	女	妲**
李白	男	李**
孙膑	男	孙**

4.去除空格

删除了左空格
select ltrim('  abc   ')
删除了右空格
select rtrim('  abc   ')
删除了左右空格
select trim('  abc   ')

5.大小写转换

小写
select lower('aBcD')
大写
select upper('aBcD')

4.2.2 数学函数

1.四舍五入

不保留小数
select round(1.6)
保留1位小数
select round(1.65,1)

2.x的y次幂

2的3次幂
select pow(2,3)

3.获取圆周率

select round(PI(),20)

4.随机数

随机1-10的整数
select round(rand()*10)
随机从一个表中取一条记录
select * from students order by rand() limit 1

4.2.3 日期时间函数

当前日期
select current_date()
当前时间
select current_time()
当前日期时间
select now()
日期格式化
select date_format(now(),'%Y')
select date_format(now(),'%y')
select date_format(now(),'%Y/%m/%d/%H/%i/%s')

4.2.4 流程控制

select 
case 33333
when 1 then 'one'
when 2 then 'two'
else 'zero'
end as result

select name,sex,
case sex 
when '男' then concat(left(name,1),'帅哥')
when '女' then concat(left(name,1),'美女')
else concat(left(name,1),'xx') end as res
from students

name	sex	res
王昭君	女	王美女
诸葛亮	男	诸帅哥
张飞	男	张帅哥
白起	男	白帅哥
大乔	女	大美女
孙尚香	女	孙美女
百里玄策	男	百帅哥
小乔	女	小美女
百里守约	男	百帅哥
妲己	女	妲美女
李白	男	李帅哥
孙膑	s	孙xx

4.2.5 自定义函数

1.navicat客户端

创建自定义函数
set global log_bin_trust_function_creators=1;
create function my_trim(str varchar(100)) returns varchar(100)
begin
return ltrim(rtrim(str));
end

使用自定义函数
select my_trim('   abc   ')

2.命令行客户端

mysql> delimiter $$
mysql> create function my_trim(str varchar(100)) returns varchar(100)
    -> begin
    -> return ltrim(rtrim(str));
    -> end
    -> $$
Query OK, 0 rows affected (0.01 sec)

4.3 存储过程

创建存储过程
create procedure proc_stu()
begin
select * from students;
end

使用存储过程
call proc_stu()

4.4 视图

创建视图
create view v_stu as
select stu.*,cs.name courseName,sc.score
from students stu
inner join scores sc on stu.studentNo=sc.studentno
inner join courses cs on cs.courseNo=sc.courseNo、

使用视图
select * from v_stu

隐藏真正的字段名
create view v_students as
select name 姓名,age 年龄 from students

select * from v_students

姓名	年龄
王昭君	20
诸葛亮	18
张飞	24
白起	22
大乔	19
孙尚香	18
百里玄策	20
小乔	15
百里守约	21
妲己	26
李白	15
孙膑	26

4.5 事务

begin;
所有操作都成功
commit;

begin;
任何一步失败
rollback;

所有操作都成功的情况
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update students set age=age-5 where name='大乔';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from students where name in('大乔','小乔');
+-----------+------+------+----------+------+-------+--------------------+
| studentNo | name | sex  | hometown | age  | class | card               |
+-----------+------+------+----------+------+-------+--------------------+
| 005       | 大乔 | 女   | 天津     |   14 | 3班   | 340322199004247654 |
| 008       | 小乔 | 女   | 河南     |   15 | 3班   | NULL               |
+-----------+------+------+----------+------+-------+--------------------+
2 rows in set (0.00 sec)

mysql> update students set age=age+5 where name='小乔';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from students where name in('大乔','小乔');
+-----------+------+------+----------+------+-------+--------------------+
| studentNo | name | sex  | hometown | age  | class | card               |
+-----------+------+------+----------+------+-------+--------------------+
| 005       | 大乔 | 女   | 天津     |   14 | 3班   | 340322199004247654 |
| 008       | 小乔 | 女   | 河南     |   20 | 3班   | NULL               |
+-----------+------+------+----------+------+-------+--------------------+
2 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

任何一步失败的情况
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update students set age=age-5 where name='大乔';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from students where name in('大乔','小乔');
+-----------+------+------+----------+------+-------+--------------------+
| studentNo | name | sex  | hometown | age  | class | card               |
+-----------+------+------+----------+------+-------+--------------------+
| 005       | 大乔 | 女   | 天津     |    9 | 3班   | 340322199004247654 |
| 008       | 小乔 | 女   | 河南     |   20 | 3班   | NULL               |
+-----------+------+------+----------+------+-------+--------------------+
2 rows in set (0.00 sec)

mysql> update students set age=age+5 where 5name='小乔';
ERROR 1054 (42S22): Unknown column '5name' in 'where clause'
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from students where name in('大乔','小乔');
+-----------+------+------+----------+------+-------+--------------------+
| studentNo | name | sex  | hometown | age  | class | card               |
+-----------+------+------+----------+------+-------+--------------------+
| 005       | 大乔 | 女   | 天津     |   14 | 3班   | 340322199004247654 |
| 008       | 小乔 | 女   | 河南     |   20 | 3班   | NULL               |
+-----------+------+------+----------+------+-------+--------------------+
2 rows in set (0.00 sec)

4.6 索引

1.命令行创建索引
开启运行时间监测
mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
查找第1万条数据test10000
mysql> select * from test_index where title='test18';
+--------+
| title  |
+--------+
| test18 |
+--------+
1 row in set (0.00 sec)
查看执行的时间
mysql> show profiles;
+----------+------------+-----------------------------------------------+
| Query_ID | Duration   | Query                                         |
+----------+------------+-----------------------------------------------+
|        1 | 0.00047600 | select * from test_index where title='test18' |
+----------+------------+-----------------------------------------------+
1 row in set, 1 warning (0.00 sec)
创建索引
mysql> create index title_index on test_index(title(10));
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from test_index where title='test18';
+--------+
| title  |
+--------+
| test18 |
+--------+
1 row in set (0.00 sec)

mysql> show profiles;
+----------+------------+---------------------------------------------------+
| Query_ID | Duration   | Query                                             |
+----------+------------+---------------------------------------------------+
|        1 | 0.00047600 | select * from test_index where title='test18'     |
|        2 | 0.17465050 | create index title_index on test_index(title(10)) |
|        3 | 0.00042425 | select * from test_index where title='test18'     |
+----------+------------+---------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

2.navicat创建索引
方式一：建表时创建索引

create table create_index(
id int primary key,
name varchar(10) unique,
age int,
key (age)
)

show index from create_index

Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Visible	Expression
create_index	0	PRIMARY	                   1	id	        A	                  0				        BTREE			                YES	
create_index	0	name	                   1	name	        A	                  0			        YES	BTREE			                YES	
create_index	1	age	                   1	age	        A	                  0			        YES	BTREE			                YES

方式二：对于已经存在的表，添加索引

索引是作用在某一个字段身上
create index i_name on test(name(10))
show index from test

create index i_age on test(age)
show index from test

select * from test where name=''
select * from test where age=

3.索引的原理
在这里插入图片描述

索引优点：提高查询的效率
缺点：降低更新表的速度
4.查看查询过程中是否用到索引

create index title_index on test_index(title(10))
explain
select * from test_index where title='test18'

id	select_type	table	partitions	type	possible_keys	key	        key_len	ref	rows	filtered
 1	SIMPLE	        test_index		ref	title_index	title_index	33	const	   1	  100.00
 
删除索引
drop index title_index on test_index
explain
select * from test_index where title='test18'

id	select_type	table	partitions	type	possible_keys	key	        key_len	ref	rows	filtered
 1	SIMPLE	        test_index		ALL					                  28	   10.00

4.7 外键

1.navicat创建外键
方式一：建表时创建外键

create table class(
id int unsigned primary key auto_increment,
name varchar(10)
)

create table stu(
name varchar(10),
class_id int unsigned,
foreign key(class_id) references class(id)
)

方式二：对于已经存在的数据表设置外键约束

alter table stu add foreign key (class_id) references class(id)

缺点：在实际开发中，很少会使用到外键约束，会极大的降低表更新的效率
2.删除外键

显示创建表时的语句
show create table stu

Table	Create Table
stu	        CREATE TABLE `stu` (
            `name` varchar(10) DEFAULT NULL,
            `class_id` int(10) unsigned DEFAULT NULL,
            KEY `class_id` (`class_id`),
            CONSTRAINT `stu_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`)
           ) ENGINE=InnoDB DEFAULT CHARSET=utf8

删除外键
alter table stu drop foreign key stu_ibfk_1
小圆快跑
关注
  • 18
    点赞
  • 踩
  • 12
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
数据库第三次实验
林鹏
04-23 746
<实验要求> 每次实验前学生必须根据实验内容认真准备。 在指导教师的帮助下能够完成实验内容。 实验结束后总结实验内容、书写实验报告。 遵守实验室规章制度、不缺席。 实验学时内必须做数据库的有关内容，不允许上网聊天或玩游戏。 <实验报告的书写要求> 明确实验的目的及要求; 记录实验的输入数据和输出结果; 说明实验中出现的问题和解决过程; ...
MySQL--查询
weixin_61500949的博客
05-15 9508
查询语法 中文 英文 SELECT 字段列表 FROM 表名列表 WHERE 条件列表 GROUP BY 分组字段 HAVING 分组后条件 DROER BY 排序字段 LIMIT 分页限定 基础查询 1.查询部分字段 SELECT 字段列表 FROM 表名; SELECT student_name -> FROM student; 2.查询全部字段 SELECT * FROM 表名; 星号代
数据库中的分组
linpeng123l的专栏
03-03 3985
数据库分组的作用分组是数据库中数据统计的一个重要功能，所谓分组就是将数据库查询得到的结果以某些字段作为分割条件将查询结果分成多组，然后我们就可以对每一组数据应用各种统计函数比如max，count等等。例子为了简化起见我们这里就使用一个表student表，建表和插入语句如下：create table student( id int PRIMARY key AUTO_INCREMENT, name VA
MySQL查询语句聚合函数基础操作
hubingzhong的博客
07-29 912
聚合函数 为了快速得到统计数据，经常会用到如下5个聚合函数 count(*)表示计算总行数，括号中写星与列名，结果是相同的 聚合函数不能在 where 中使用 例1：查询学生总数 select count(*) from students; max(列)表示求此列的最大值 例2：查询女生的最大年龄 select max(age) from students where sex='...
mysql_Day02
weixin_45833831的博客
05-12 2793
DQL语句（数据库查询语句） 基本查询语句 （select关键字） ddl语句最通用的语句： 查询表中所有数据 select * from 表名; 语法1：插入数据: ①插入单条数据 语法：insert into 表名 values （值1，值2.......）; ②插入表的数据支持一次插入多条数据 语法：insert into 表名 values（值1，值2.....）,(值3，值4.....）; ③插入部分字段（没有插 入的字段默认值为null，也支持插入多条数据） 语法：inser
Mysql数据库基本知识文档
12-24
这些基本概念和操作构成了MySQL数据库的基础知识体系，对于学习者来说，理解和熟练掌握这些内容是迈入数据库世界的首要步骤。通过不断的实践和练习，你可以逐渐深入到更高级的主题，如存储过程、触发器、分区表、...
包含MySQL数据库的基本所有知识点
01-01
包含MySQL数据库的基本所有知识点 MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle旗下产品。MySQL是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的RDBMS (Relational ...
Mysql数据库基础知识
最新发布
01-30
MySQL数据库是目前广泛应用的关系型数据库管理系统，其基础知识涵盖了多个方面，包括数据库的基本概念、MySQL的安装与使用、SQL语言的各个部分以及高级特性。对于数据库管理员、开发人员、数据分析师和学习者来说，...
MySQL数据库操作的基本命令
09-09
本文将深入探讨MySQL数据库操作的一些基本命令，这些命令对于任何初学者来说都是必须掌握的基础知识。 首先，我们来创建数据库。在MySQL中，使用`CREATE DATABASE`语句来创建新的数据库。例如，创建一个名为`my_...
MySql数据库基础知识点总结
09-08
MySQL数据库是世界上最流行的开源关系型数据库管理系统之一，其基础知识点对于任何想要从事数据库管理或开发工作的人来说至关重要。本文将深入探讨MySQL的一些核心概念，包括数据库的创建、查看、选择、删除，以及...
8. 对每一个系，求学生的平均年龄，并把结果存入数据库中的新表dept_age(Sdept,Avg_age)中
weixin_43939570的博客
05-14 1万+
INSERT INTO dept_age (Sdept, Avg_age) SELECT sdept, AVG(sage) FROM student GROUP BY sdept
mysql的基础查询语句_mysql基本查询语句及方法
weixin_31528001的博客
01-19 1143
一、语法执行顺序书写顺序select id,name from emp where id > 3 and id < 6;执行顺序from # 确定到底是哪站表where # 根据过来条件 筛选数据select # 拿出筛选出来的数据中的某些字段select * from emp\G; 当表字段特别多的时候，打印结果的排版可能会出现混乱的现象，这时在查询语句加\G来规范查询结果。二、常用...
01:求平均年龄
www.imayuan.com
09-02 1万+
原题链接 总时间限制: 1000ms 内存限制: 65536kB 描述 班上有学生若干名，给出每名学生的年龄（整数），求班上所有学生的平均年龄，保留到小数点后两位。 输入第一行有一个整数n（1 输出输出一行，该行包含一个浮点数，为要求的平均年龄，保留到小数点后两位。 样例输入 2 18 17 样例输出 17.50 源码 #incl
mysql分组查询学生平均年龄_MySQL学习-基础练习题
weixin_33228847的博客
01-19 7905
day1学生表操作：1. 查询出班级205有多少个男生2. 查询出名字为4个字的所有学生信息(编号、姓名，年龄，班级)3. 查询出所有姓王的学生信息(编号、姓名，年龄，班级)4. 查询出班级编号为201,202,203的女生总人数5. 查询出学号整十的所有女生信息(姓名、年龄、个人简介)6. 删除301班级中年龄在23岁以上的学生信息7. 把一个叫'卫然'的学生的姓别改成女的8. 把401班级中的...
计算数组中平均年龄是多少？
weixin_44315197的博客
12-28 1147
public class A { public static void main(String[] arsg){ int sumAge = 0; int agesLen = ages.length; for(int i=0;i&lt;agesLen;i++){ sumAge+=ages[i]; } double avgAge = sumAge*1.0/agesLen; System.out.p...
mysql数据库年龄_sql获取时间、年龄
weixin_42498643的博客
01-19 3280
mysql数据库获取年龄:TIMESTAMPDIFF(YEAR, [出生日期字段], CURDATE())select * from (select name 姓名,TIMESTAMPDIFF(YEAR, [出生日期字段], CURDATE()) 年龄 from [表名])a;sqlserver数据库获取年龄:DATEDIFF(yy,[出生日期字段],GETDATE())select id A...
SQL通过出生日期查年龄
热门推荐
最有趣的编程
02-26 2万+
mysql查询男女平均年龄_查询计算机系学生的姓名、性别和年龄
weixin_33550394的博客
02-02 1万+
【填空题】用 insert命令向学生表student里插入一条新记录:学号为18007,姓名为:张飞驰,男,20岁,师范系 () into student(sno,sname,sex,sdept) values(‘18007’, ‘张飞驰’‘男’, 20,’ 师范系 ’) ;【判断题】意识不仅是自然界长期发展的产生,而且是社会历史发展的产物。【填空题】1 0. 查询所有选择 C2课程的学生的学号,...
mysql数据库基本知识
08-26
MySQL是一种数据库管理系统(DBMS)，它用于管理和存储数据。数据库是一个结构化的数据集合，而MySQL是一种常用的关系型数据库管理系统。MySQL具有以下特点和基本知识： 1. 数据库分类：MySQL是一种关系型数据库管理系统，它采用了SQL（Structured Query Language）查询语言。 #### 引用[.reference_title] - *1* [【MySQL】数据库基础知识](https://blog.csdn.net/m0_51064412/article/details/124829675)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 50%"] - *2* *3* [Mysql数据库基础知识整理](https://blog.csdn.net/Kenneth0617/article/details/131729899)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 50%"] [ .reference_list ]
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值