软件测试学习笔记丨 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;
获取更多软件测试知识
版权声明: 本文为 InfoQ 作者【测试人】的原创文章。
原文链接:【http://xie.infoq.cn/article/4a4e33255fef94fc2afbe6c5a】。文章转载请联系作者。
评论