软件测试学习笔记丨 SQL操作命令集合(数据库操作、数据表操作、表数据操作)

创建指定字符集的数据库

create database if not exists test_db character set = utf8;

查看数据库

show databases;

使用数据库

use hogwarts_stu;

修改数据库的字符集

alter database hogwarts_stu character set = GBK;

删除数据库

drop database if exists test_db;

---

创建数据表

create table user_wm (id int not null default 0 primary key, name varchar(20) not null default “姓名”, age int not null default 0, sex varchar(2) not null default “性别”, address varchar(20) null default “地址”);

复制数据表

create table user_wm1 like user_wm;

查询所有数据库表

show tables;

查看数据表中所有列的数据属性

describe user_wm;

查看数据表中某列的数据属性

describe user_wm name;

添加新列

alter table user_wm add email varchar(20) null default “邮箱”;

修改列的数据类型

alter table user_wm modify email char(20);

修改列名

alter table user_wm change email email1 char(20);

修改列名并指定列的默认值

alter table user_wm change email1 email char(20) default “邮箱1”;

删除列

alter table user_wm drop email;

修改表名方式1

alter table user_wm rename as user_wangmin;

修改表名方式2

rename table user_wangmin to user_wm;

删除数据表

drop table user_wm;

---

单表查询

select * from user_wm;

字段查询

select name, age, sex from user_wm;

为表和字段取别名(别名临时性生效)

select name as name1 , age as age1 , sex as sex1 from user_wm user_wm2;

去重(查询结果去掉多个指定字段的重复行)

select distinct sex, address from user_wm;

查询结果参与运算

select id , age+3 from user_wm;

条件查询-比较大小(>,>=,=,<=,<,<>,!=)

select name, sex, address from user_wm where age <> 27;

条件查询-范围限定(between … and …)

select name, sex, address from user_wm where age between 27 and 28;

条件查询-子集限定

select name, sex, address from user_wm where age in (27,28);

条件查询-为空限定

select name, sex, age from user_wm where address is null;

条件查询-不为空限定

select name, sex, age from user_wm where address is not null;

条件查询-逻辑运算符-多个条件同时成立(and 或 &&)

select name, age, sex from user_wm where sex = “男” and address is not null;

条件查询-逻辑运算符-多个条件任一成立(or 或 ||)

select name, age, sex from user_wm where sex = “男” or address is not null;

条件查询-逻辑运算符-有个条件不成立(not)

select name, age from user_wm where not sex = “男”;

条件查询-通配符-条件内容为匹配任意多个字符

select name, sex, address from user_wm where address like “%省%市%”;

条件查询-通配符-条件内容为匹配唯一一个字符

select name, sex, address from user_wm where address like ‘浙江省杭州_’;

条件查询-对查询结果排序-单列排序-升序排序(asc,默认)

select id, name, sex, address from user_wm where id between 1 and 5 order by id asc;

条件查询-对查询结果排序-单列排序-降序排序(desc)

select id, name, sex, address from user_wm where id between 1 and 5 order by id desc;

条件查询-对查询结果排序-组合排序-降序排序(desc)-处理多个列有重复值下的情形

select id, name, age, sex, address from user_wm where id between 0 and 1 and age in (26,27) order by id desc, age desc;

条件查询-聚合函数-统计指定列不为null的记录行数(count( *)、count(列名)、count(distinct 列名))

select count(* ) from user_wm where sex = “男”;

---

插入表中多条数据并写出完整列名

insert into user_wm (id, name, age, sex, address) values (0, “wangmin”, 26, “男”, “浙江省杭州市”),(1, “liuyi”, 27, “女”, “湖南省湘潭市”),(2, “test2”, 28, “女”, null);

插入表中多条数据，不带列名

insert into user_wm values (2,“test2”, 31, “男”, “浙江省杭州市”), (3,“test3”, 32, “女”, “浙江省杭州市”);

插入表一行中某几列的值

insert into user_wm (id, name, age, sex) values (4, “test4”, 30, “女”);

修改表中指定列对应的所有行数据(不带条件的修改)

需要关闭sql_safe_updates模式

set sql_safe_updates = 0;
update user_wm set age = 25 , sex = “男”;

一次修改多个列(不带条件的修改)

update user_wm set name = “test0”, age = “0”, sex = “男”, address = “湖南省湘潭市”;

修改表中指定列对应的指定列数据(带条件的修改)

update user_wm set name = “wangmin” where sex = “男”;

一次修改多个列(带条件的修改)

update user_wm set name = “test4”, age = “30”, sex = “男”, address = “浙江省杭州市” where id = “0”;

删除表中指定列对应的行数据(带条件的修改)

delete from user_wm where id = 5;

删除表中的全部数据

truncate table user_wm;

获取更多软件测试知识