mysql基础入门SQL基本语法

小丫么小二郎~

已于 2022-09-20 16:36:51 修改

阅读量602

点赞数 1

文章标签： mysql 数据库 sql

于 2022-09-13 13:42:48 首次发布

本文链接：https://blog.csdn.net/weixin_42833736/article/details/126827711

版权

SQL：Structure Query Language(结构化查询语言)简称SQL，数据库管理系统可以通过SQL管理数据库；定义和操作数据，维护数据的完整性和安全性。

SQL的分类：

1、DDL（Data Definition Language）数据定义语言，对数据的结构和形式进行定义，一般用于数据库和表的创建、删除、修改等。

2、DML(Data Manipulation Language) 数据操作语言，用来操作数据库中表里的数据；常用语句：INSERT、 UPDATE、 DELETE
3、DCL(Data Control Language) 数据控制语言，用来操作访问权限和安全级别；常用语句：GRANT、DENY
4、DQL(Data Query Language) 数据查询语言，用来查询数据常用语句：SELECT

一、DDL数据库操作

1.1 查询数据库

# 显示当前mysql中的数据库列表：
show databases;
# 显示指定名称的数据的创建的SQL指令：
show create database <dbName>;

1.2 创建数据库

# 创建数据库，dbName表示创建的数据库名称，可以自定义：
create database <dbName>;
# 创建数据库，当指定名称的数据库不存在时创建：
create database if not exists  <dbName>;
# 在创建数据库的同时指定数据库的字符集（z字符集：数据存储在数据库中采用的编码格式 utf8 gbk）:
create database <dbName> character set utf8;

1.3 修改数据库

# 修改数据库的字符集：
alter database db_test character set utf8;

1.4 删除数据库：（删除数据库时会删除当前数据库中所有表和数据）

drop database <dbName>
# 使用/切换数据库:
use <dbName>

1.5 创建数据表

# 查询数据表：
show tables;
# 查询表结构：
desc <tableName>;
# 删除数据表：
drop table <tableName>;
# 当数据表存在时删除：
drop table if exists <tableName>;
# 修改数据表：
# 修改表名：
alter table students rename to 新名；
# 数据表也是有字符集的,默认字符集和数据库一致：
alter table <tableName> character set utf8;
# 添加字段（列）：
alter table <tableName> add 字段名 类型；
# 修改字段的列表和类型：
alter table <tableName> change 字段名 新的字段名 新的类型；
# 只修改字段的类型： 
alter table <tableName> modify 字段名 新的类型；
# 删除字段： 
alter table <tableName> drop 字段名；

二、mysql数据类型

数据类型指的是数据表中的列支持存放数据的类型

2.1 数值类型

在mysql中有多种数据类型可以存放数值，不同的类型存放的数值的范围或者形式是不同的

类型	内存空间大小/byte	范围	说明
tinyint	1	有符号： -128到127 无符号0-255	特小型整数
smallint	2	有符号： -32768到32767 无符号0-65535	小型整数
mediumint	3	有符号： -2^31到2^31-1 无符号0-2^31-1	中型整数
int	4		整数
bigint	8
float	4	单精度
double	8	双精度
decimal		decimal(10,2)表示一共有10位，小数2位

2.2 字符串类型

存储字符序列的类型

类型	字符序列的长度范围	说明
char	0-255	定长字符串，最多可以存储255个字符串，指定char(n)
varchar	0-65535	可变长度字符，此类型最多65536个字符串，指定varchar(n)
tinyblob	0-255	存储二进制字符串
blob	0-65535	存储二进制字符串
mediumblob	0-1677215	存储二进制字符串
longblob	0-4294367295	存储二进制字符串
tinytext	0-255	文本数据（字符串）
text	0-65535	文本数据（字符串）
mediumtext	0-1677215	文本数据（字符串）
longtext	0-4294367295	文本数据（字符串）

2.3 日期类型：

在mysql数据库中，可以使用字符串来存储时间，如果需要基于时间字段进行查询

data	2021-09-13	只能存储年月日
time	11：12：13	只能存储时分秒
year	2021	年份
datatime	2021-09-13 11：12：13	存储年月日时分秒
timestamp	20210913111213	时间戳

2.4 字段约束

在创建数据表的时候，指定的对数据表的列的数据限制的要求
字段常见的约束：
非空约束(not null): 限制此列的值必须提供，不能为null
唯一约束（unique）:在表中的多条数据，此列的值不能重复
主键约束（primary key）:非空+唯一，能够唯一标识数据表中的一条数据
外键约束（foreign key）：建立不同表之间的关联关系

create table books(
    book_isbn char(4) unique primary key,
    book_name varchar(10) not null, 
    book_author varchar(6),
    primary key(字段名)
    );

删除数据表主键约束：atler table 表名 drop primary key;
创建表之后添加主键约束：alter taable 表名 modify 字段名类型 primary key；
主键自动增长：自动增长从1开始，每添加一条记录，列会自动+1，但如果删除某条记录，自动增长的数据不会重复
定义

create table type(
    type_id int primary key auto_increment,
    type_name varchar(20) not null,
    type_remark varchar(100)
);

联合主键
将数据表中的多列组合在一起设置为表的主键
定义联合主键

create table grade(
    stu_num char(8),
    course_id int,
    score int,
    primary key(stu_num,course_id)
);

外键约束：在多表关联部分

三、DML数据操纵语言

用于完成对数据表中数据的插入，删除，修改操作

3.1 插入数据

insert into 表名(字段1，字段2....) values(value1,value2....);

示例：
insert into students(stu_num,stu_name,stu_gender,stu_age,stu_tel) values('20120101','张三','男','18','1300000000');
数据表后的字段名可以与表中字段顺序不一致，但值要与插入时的字段顺序对应
给表中所有字段添加数据，可以不写字段名，但是值必须要与数据表字段顺序对应

3.2 删除数据

从数据表中删除满足特定条件的数据

 delete from 表名 where conditions；

示例：
delete from students where stu_num='20210101';
如果删除语句没有where，则表示删除当前数据表中所有记录

3.3 修改数据

对数据表已经添加的记录进行修改

  update 表名 set 字段名=value [where conditions]

示例：
update students set stu_name='李四' where stu_num='20210101';
修改多列：update students set stu_name='李四',stu_age='20' where stu_num='20210101';
如果update语句没有where，则表示修改当前表中所有行

四、DQL数据查询语言

从数据表中提取满足特定条件的记录
单标查询
多表联合查询

4.1 查询基础语法

# 指定查询列:
 select  字段名1,字段名2，字段名3..... from 表名;
# 查询所有列：
 select  * from 表名;

4.2 where子句

在删除、修改及查询的语句后都可以添加where语句（条件），用于筛选满足特定的条件的数据进行删除、修改、查询操作
条件：= ！= > < >= <= between and
多条件查询：and or not
like:在where子句的条件中，可以使用like关键字实现模糊查询
select * from 表名 where 字段名 like 'reg';
在like关键字后的reg表达式中：%表示任意多个字符（%o%包含字母o） _表示任意一个字符（_o%第二个字母为o）

4.3 对查询结果的处理

# 设置查询的列：
select 字段1，字段2... from students where 条件；
# 计算列：对从数据表中查询的记录进行一定的运算再显示出来。
select stu_name,2021-stu_age from students;
# 字段别名：可以为查询结果的列名取一个语义性更强的别名。
select stu_name as 别名,2021-stu_age as 别名 from students;
#消除重复：从查询结果中将重复的删除：
select distinct 字段名 from 表名;

# 查询结果排序order by排序：

   升序：select * from 表名 where 条件 order by 字段名 asc;(默认)
   降序：select * from 表名 where 条件 order by 字段名 desc;
   多字段排序（先满足第一个，若1相同再按照2排序）：select * from 表名 where 条件 order by 字段名1 desc，字段名2 asc;

4.4 聚合函数：

sql中提供了一些可以对查询的记录列进行计算的函数

count()统计函数:select count(字段名) from students where 条件;
max()最大值:select max(字段名) from students where 条件;
min()最小值:select min(字段名) from students where 条件;
sum()计算和:select sum(字段名) from students where 条件;
avg()求平均值:select avg(字段名) from students where 条件;

4.5 日期函数：

当我们向日期类型的列添加数据时，可以通过字符串类型赋值（字符串的格式必须是yyyy-MM-dd hh:mm:ss）
如果想要获取当前系统时间添加到日期类型的列，可以使用now() 或者 sysdata()
示例：
insert into students（stu_num,stu_name,stu_gender,stu_age,stu_tel,stu_qq,stu_enterence） value('20120104','hh','男','18','1300000004',10101010,2022-05-28 02:22:36);
insert into students（stu_num,stu_name,stu_gender,stu_age,stu_tel,stu_qq,stu_enterence） value('20120104','hh','男','18','1300000004',10101010,now());

4.6 字符串函数：

# concat拼接：将列的数据使用符号连接：
select concat(字段名1,'-',字段名2) from 表名;
# upper 将字段中所有转换成大写。
select upper(字段名) from 表名;
# lower 将字段中所有转换成小写。
select lower(字段名) from 表名;
# substring截取 (截取字段名，截取开始位置，截取长度)：
select 主键,substring(字段名,7,4) from 表名;

4.7 分组查询

分组就是将数据表中的记录按照指定的类进行分组。

select 分组字段/聚合函数 from 表名 【where 条件】group by 分组字段 [having 条件]

select 后使用*显示对查询结果进行分组后，显示每组的第一条记录

select后通常显示分组字段和聚合函数（对分组后的数据进行统计，求和，平均值等）

语句执行顺序：先根据where条件从数据库查询记录，对查询记录进行分组，having对分组的结果再进行筛选。

示例：

#先对查询的学生信息按照性别分组，然后再分别统计每组学生的个数

select stu_gender,count(stu_num) from students group by stu_gender;

#按年龄进行分组，然后分别统计每组的人数，再筛选当前组人数大于1的组显示出来

select stu_age,count(stu_num) from students group by stu_age having count(stu_num)>1 order by stu_age;

分页查询

当数据表中的记录比较多的时候，如果一次性全部查询出来，用户的可读性就不好，因此我们对这些数据进行分页展示

select * from 表名 where... limit param1,param2

param1 int,表示获取查询语句的结果中的第一条数据的索引

param2 int,表示获取的查询记录条数

五、数据表的关联关系

mysql是一个关系型数据库，不仅可以存储数据，还可以维护数据与数据之间的关系——通过在数据表中添加字段建立外键约束

数据与数据之间的关系分为四种：

一对一关联

一对多关联

多对一关联

多对多关联

5.1 一对一关联

方案1：主键关联——两张数据表中主键一对一关联

constraint   FK_STUDENTS_CLASSES foreign key(外键名) reference classes（class_id）

方案二：唯一外键——在任意一张表中添加一个字段添加外键约束与另一张表主键关联，并且将外键列添加唯一约束

alter table 表名 add constraint   外键名 foreign key(列名) reference 主表明（列名）;

删除外键约束：alter table students drop foregin key FK_STUDENTS_CLASSES;

5.2 一对多与多对一

方案：在多的一端添加外键，与一的一端主键进行关联

5.3 多对多

方法：额外创建一张关系表来维护多对多关联——在关系表中定义两个外键，分别与两个数据表的主键进行关联

5.4 外键约束

外键约束——将一个列添加外键约束与另一张表的主键（唯一列）进行关联之后，这个外键约束的列添加的数据必须要在关联的主键字段中存在

5.5 外键约束级联操作

在添加外键时，设置级联修改和级联删除

#删除原有外键

alter table students drop foreign key FK-STUDENTS_CALSSES;

#重新添加外键，并设置级联修改和级联删除

alter table students add constraint FK-STUDENTS_CALSSES foreign key(cid) references classes(class_id) ON UPDATE CASCADE ON DELETE CASCADE;

六、连接查询（从多张表中提取数据）

在mysql中可以使用join实现多张表的联合查询——连接查询，join按照其功能不同分为三个操作：

inner join 内连接

left join 左连接

right join 右连接

6.1数据准备

创建班级信息表和学生信息表

6.2内连接 inner join

select * from tablename1 inner join tablename2;

6.2.1笛卡尔积

如果直接执行select * from tablename1 inner join tablename2;会获取两种数据表中的数据集合的笛卡尔积（依次使用tablename1表中的每一条记录去匹配tablename2的每条数据）

6.2.2内连接条件

使用where：先生成笛卡尔积再过滤（效率低）

select *from students inner join classes where students.cid=classes.class_id;

使用ON设置连接查询条件：先判断条件是否成立，再生成查询结果

select *from students inner join classes where students.cid=classes.class_id;

6.3左连接left join

显示左表中的所有数据，如果在右表中存在于左表记录满足匹配条件的数据，则进行匹配，如果右表中不存在匹配数据，则显示为null

select * from lefttable left join righttable on 匹配条件；

示例：select * from students left join classes ON students.cid=classes.class_id;

6.4右连接right join

显示右表所有数据

select * from lefttable right join righttable on 匹配条件；

示例：select *from students right join classes ON students.cid=classes.class_id;

6.5数据表别名

如果在连接查询的多张表中存在相同名字的字段，我们可以使用表名.字段名来进行区分。如果表名太长不便于编写，可以使用数据表别名

示例：select s.*,c.class_name from students s inner join classes c on s.cid =c.class_id;

6.6子查询/嵌套查询

第二次查询是基于第一次查询结果进行的。

示例：查询班级名称为“Java2104”班级中的学生信息

传统：a.查询Java2104班的班级编号

select class_id from classes where class_name='Java2104';

b.查询此班级编号下的学生信息

select * from students where cid=1;

子查询：

select * from students where cid=（select class_id from classes where class_name='Java2104'）；

如果第一次查询结果的返回值是单列多行，使用in

select * from students where cid in (select class_id from classes where class_name like 'Java%');

如果第一次查询结果的返回是多行多列，将第一次的返回结果作为一个整体的虚拟表，再基于这个虚拟表查询（虚拟表需要别名）

select *from (select *from students where cid=1) t where t.stu_gender='男';