SQL语句与相关操作

本文地址：https://blog.csdn.net/qq_18729023/article/details/103534050

第一部分 基础教程

一、导语

 

有sql基础吗？可以先测试一下自己的sql水平，然后看完本文，再检测一下自己的水平有没有提升。

测试地址：https://www.w3school.com.cn/quiz/quiz.asp?quiz=sql

测试完成后，本文引导按功能列表进行描述，数据库操作整体可以分为增、删、改、查四大类操作，以mysql语法为例，以示例形式进行语法查询演示。

 

二、数据库登入操作

       在进行查询等操作之前，需要完成数据库登入（连接）操作，获取对数据库的操作权限， 登入连接操作可以直接通过代码完成，但是为了方便理解，这里以mysql为例截图演示。

       首先连接上拥有完整mysql环境的机器，这里我使用的是个人电脑，界面可能和服务器略有不同，主要看文字理解。

2.1数据库登录

         Mysql登入操作需要拥有用户名和密码，这里我的用户名是’root’,

    操作指令为：

    mysql -u 用户 -p

 

         指令理解：‘mysql’为固定指令，相当于告诉电脑，我要进入mysql数据库，‘-u’告诉电脑后面数据的是用户名（’u’理解为user）,’-p’告诉电脑后面的是通过密码登录（‘p’理解为password）,注意，密码一定是要单独输入的，密码直接写在本条指令后没有用。

        输入登录指令后回车，提示需要输入密码，有些版本输入时是看不到输入情况的（光标不会动），正确输入就好。示例效果如下：

        正常登录后会看到MySQL版本号的一些信息，登录结束！

2.2 退出登录操作

        退出登录操作比较简单，常见语：

    Exit;

 

        直接输入exit后回车，退出登录操作。

三、表格操作

3.1 建表操作

        在对数据库进行增删改查操作前，首先得有操作对象，那就是数据库和数据表，在操作前，可以使用命令查看MySQL是否有我们需要的数据库：

3.1.1 查询数据库

    Show database;

        操作结果如下：

        可以看到这里有7条记录，在这里我们新建一个数据库。

3.1.2 新建数据库

        使用命令：     

    create database 数据库名；

        示例命令：

    create database studysql;

        使用 show databases 再次查看数据库：

        可以看到多了一个名为‘studysql’的数据库，创建数据库成功！

3.1.3 删除数据

        数据的删除命令使用drop，命令格式内容如下：

    Drop database 数据库名；

        示例命令:

    drop database studysql;

        查看发现数据库已经被删除：

3.1.4 选择数据库

        在完成数据库的创建后，如果需要对表格进行操作则需要进入指定的数据库内，进行更细力度的操作。

        操作指令：

    use 数据库名称；

        指令示例：

    use test;

        此时已经进入了指定的数据库，可以使用show tables；查看所有表清单：

3.1.5 创建表操作

        建表操作和建库操作一样，通常使用得比较少，一般在系统上线或者版本更新时才有可能用到，在建表操作前，需要了解几个简单的数据类型，数据库的每一列值通常都对应着不同的数据类型，不同的数据类型在不同的业务或者场景应用。

        1.数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 字节	(-128，127)	(0，255)	小整数值
SMALLINT	2 字节	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 字节	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 字节	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度 浮点数值
DOUBLE	8 字节	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度 浮点数值
DECIMAL	对DECIMAL(M,D) 如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

 

        2.日期和事件类型

类型	大小(字节）	范围·	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

 

        3.字符串类型

类型	大小	用途	类型	大小
CHAR	0-255字节	定长字符串	CHAR	0-255字节
VARCHAR	0-65535 字节	变长字符串	VARCHAR	0-65535 字节
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串	TINYBLOB	0-255字节
TINYTEXT	0-255字节	短文本字符串	TINYTEXT	0-255字节
BLOB	0-65 535字节	二进制形式的长文本数据	BLOB	0-65 535字节
TEXT	0-65 535字节	长文本数据	TEXT	0-65 535字节
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据	MEDIUMBLOB	0-16 777 215字节
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据	MEDIUMTEXT	0-16 777 215字节
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据	LONGBLOB	0-4 294 967 295字节
LONGTEXT	0-4 294 967 295字节	极大文本数据	LONGTEXT	0-4 294 967 295字节

数据类型不需要过分理解，可以在使用中慢慢积累。

创建表语句操作指令：

    Create table 表名（
                ……
                ……
                    ）;

示例指令：

    create table user_info (

              user_id varchar(32) not null comment '用户编码',  

              user_password varchar(64) not null comment '用户账号密码',     

              user_name varchar(20) not null comment '用户姓名', 

              user_sex int null comment '用户性别'

              );

        就 user_id varchar(32) not null comment '用户编码' 内容而言，理解如下：

        user_id 为定义的字段名 varchar(32) 表示长度为32的字符串类型，not null表示该字段不允许为空值，comment 为注释字段，后面跟着对该字段的注释内容。

        为了防止建表时出现问题，所以通常使用的建表语句为：

    create table if not exists user_info ( 
         user_id varchar(32) not null comment '用户编码',    
         user_password varchar(64) not null comment '用户账号密码', 
         user_name varchar(20) not null comment '用户姓名',  
         user_sex int null comment '用户性别'
        );

             理解为如果不存在该表就创建该表，防止创建表操作出错，导致该表被锁死。

3.1.6 添加主键与删除

        主键（PRIMARY KEY），也称“主键约束”。

        MySQL主键约束是一个列或者多个列的组合，其值能唯一地标识表中的每一行。

        这样的一列或多列称为表的主键，通过它可以强制表的实体完整性。

       1.添加主键指令：

    alter table 表名 add primary key (主键字段1,主键字段2);

        示例指令：

    alter table user_info add primary key (user_id);

     2.删除主键操作指令

    alter table 表名 drop primary key;

        指令示例：

    alter table user_info drop primary key;

3.2 删除表操作

         删除表操作指令：

    drop table 表名；

        示例指令：

    drop table test;

        首先查看表清单：

    show tables;

    删除test表并查看：

    drop table test; 
    Show tables;

3.3 更改表结构

3.3.1 查看表结构

        更改表结构主要是对表的字段名、字段类型进行修改或者新增删除表字段，在修改表结构之前可以通过指令查看表结构：

    查看表字段操作指令：

    Show columns from 表名；

        示例指令：

    show columns from stusql;

3.3.2 新增表字段

        新增表字段是对原有表字段不满足情况下进行新增，例如上面的的user_info（用户信息表），如果想新增用户的爱好（user_hobby）和归属地（user_addr）字段时，就需要进行字段的新增。

    新增字段语句：

    alter table 表名 add 字段1 字段1的字段类型,add 字段2 字段2的字段类型;

        此处示例：

    alter table user_info add user_hobby varchar(50), add user_addr varchar(20);

        可以看到字段添加成功！

3.3.3 删除表字段

        在某表中不需要该字段时，可以对该字段进行删除。

    删除字段操作：

    alter table 表名 drop column 字段名;

        这里示例删除user_addr字段：

    alter table user_info drop column user_addr;

        user_addr字段删除成功！

3.3.4 修改表字段

        修改表字段时主要分成两种情况，一种是修改表的列名，一种是修改表的列类型：

        1.修改列名

    指令模板：

    alter table 表名 change 老字段名 新字段名 字段类型;

        示例指令：

    alter table user_info change user_hobby user_like varchar(50);

        2.修改列类型

    修改命令：

    alter table 表名 modify 字段名 字段类型;

        这里我将user_sex字段由int(11)改为varchar(5)

        指令示例：

    alter table user_info modify user_sex varchar(5);

        修改成功！

        注意：如果表中已经存在数据，修改字段和删除字段时可能会存在问题，请谨慎操作！

四、插入操作（新增记录）

        插入操作是对表格中记录的新增，以上面建表的user_info表进行操作示例。

    插入操作命令：

    insert into 表名 （字段1名，字段名2……）values(值1，值2……);

        或者 

    Insert into 表名 values(值1，值2……);

        指令示例：

    insert into user_info values(“zhangsan”,”123456”,”张三”,”男”,18,”唱歌，篮球”);

 

        插入成功！

        注意：插入值的类型需要和表结构对应，例如这里性别长度是5，就不能超过该长度限定所以不能写成

    insert into user_info values(“zhangsan”,”123456”,”张三”,”张三真正的男人”,18,”唱歌，篮球”);

        这里性别内容超过5长度，报出了user_sex数据内容过长的提示,插入失败。

        在允许为空值的列对应值可以为空，user_info表值是否可为空的情况如下：

        所以这里可以写成如下形式：

    insert into user_info values(“zhangsan”,”123456”,”张三”,null,null,null);

五、删除操作

    删除记录操作指令：

    delete from 表名 where 条件;

        示例删除指令：

    delete from user_info where user_id=”lisi”;

        删除lisi记录的操作。

        操作结果：

        当where关键词后面的条件命中多行记录时，多条记录会一同删除。

    首先查看存在的记录：

    select * from user_info;

        如果删除所有16岁以下的用户：delete from user_info where user_age<16;

        结果如下：

 

六、修改操作

    修改操作命令:

    update 表名 set 更改内容 where 条件;

        例如用户张三需要修改密码，，

        示例SQL：

    update user_info set user_password=”976543210”;

        修改前：

        修改后：

 

七、查询操作

    查询语法：

    SELECT 列名称 FROM 表名称;

        示例sql:

    select user_name from user_info;

        查询所有列信息：

    select * from 表名称;

        示例sql:

    select * from user_info;

 

 

第二部分 高级教程

一、where后面的条件

1.1 and 和 or

and和or通常是用于where后多个条件之类的连接，and表示后续条件都满足，or表示多个条件中有一个满足。

在这里新建一张订单表：

create table if not exists order_list (
                order_id varchar(64) primary key comment "订单号",
                user_id varchar(32) comment "用户id",
                goods_id varchar(32) comment "商品号",
                goods_num int(11) comment "商品数量",
                order_sta int(11) comment "订单状态，1 订单下达，2 订单运输， 3 订单配送，4 订单收货（待评价），5 订单完成",
                order_time datetime comment "订单下达时间"
);

插入几条数据：

insert into order_list values ("1","zhangsan","5",5,1,"2019-12-12 00:10:20"),
                              ("2","lisi","3",1,1,"2019-12-12 00:10:20"),
                              ("3","zhangsan","1",1,1,"2019-12-12 00:10:20"), 
                              ("4","zhangsan","8",1,1,"2019-12-12 00:10:20");

查询记录：

select * from order_list;

比如查询lisi用户处于下达状态的订单：

select * from order_list where user_id ="lisi" and order_sta=1;

查询条件为 首先user_id为lisi，并且order_sta为1，所以使用and连接。

当查询 双12下单或者处于下达状态的订单信息：

select * from order_list where order_time>"2019-12-12 00:00:00" and order_time<"2019-12-13 00:00:00" or order_sta = 1;

查询结果：

两个条件满足一个即可，使用or连接。

1.2 in、like和between

1. in 表示在某集合之内

示例：

select * from order_list where goods_id in (1,3);

表示查询goods_id在1、3范围内的记录

展示结果：

2. like表示近似结果

范例sql：

select * from order_list where user_id like "li%";

查询以li开头的近似结果

其中like后的叫通配符，通配符大概有两类：“%”，“_”

第一类 ：%

%表示适配任意字符串，比如范例sql中的“li%”适配所有以li开头的字符串，如若写成“%san”则表示适配所有以san结尾的字符串。

“%is%”表示适配所有含有is的字符串。

select * from order_list where user_id like "%san";

select * from order_list where user_id like "%ang%";

第二类：_

_位置省略，直接看示例吧：

select * from order_list where user_id like "l_si";

该语句表示查询user_id匹配开始为“l”，第二个之后为“si”的字符串的记录：

3. between 表示在某区间内

比如原来表中有记录如下：

执行操作：

select * from order_list where user_id between "lisi" and "wanger";

可以看出查询除了user_id中lisi和wanger之间的记录。

1.3 null 和 not null

null和not null 用于筛选是记录否为空，例如：

select * from order_list where order_sta is null;

select * from order_list where order_sta is not null;

1.4 别名（AS）

as 语句赋予查询内容别名，示例如下：

select user_name as 姓名 from user_info;

不加as的查询结果如下:

1.5 (left/right/inner) join 语法（存在方法论,这里不做赘述）

jion语法用于两表关系的连接操作，分为左连接、右连接、全连接和内连接。

左连接以左表为标准做连接操作，当右表无记录时则为空。

user_info和order_list记录内容分别如下:

user_info:

order_list:

示例sql：

select a.user_name,b.goods_id,b.goods_num,b.order_time from user_info a left join order_list b on a.user_id=b.user_id;

查询结果：

右连接以右表为准，左表无记录时为空值。

示例sql：

select a.user_name,b.goods_id,b.goods_num,b.order_time from user_info a right join order_list b on a.user_id=b.user_id;

查询结果：

内连接是在连接时，只取两表均有记录的值。

示例sql：

select a.user_name,b.goods_id,b.goods_num,b.order_time from user_info a inner join order_list b on a.user_id=b.user_id;

1.6 distinct 操作

distinct是去重操作，对选定字段进行去重。

例如查询订单表中的所有user_id时，user_id存在重复，使用distinct去重：

示例sql：

select distinct user_id from order_list;