测试人员必会SQL命令
一、数据库相关的SQL
1.创建数据库 test
CREATE DATABASE test; CREATE DATABASE your_database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
2.查看数据库创建是否成功
SHOW DATABASES;
3.删除库 test
DROP DATABASE test;
4.进入testdb6 库
USE testdb6 ;
二、表相关的SQL
1.创建test和weisi表
CREATE TABLE test ( id INT(10) NOT NULL UNIQUE PRIMARY KEY , uname VARCHAR(20) NOT NULL , sex VARCHAR(4) , birth YEAR, department VARCHAR(20) , address VARCHAR(50) , weisi VARCHAR(20) ); weisi表 CREATE TABLE weisi ( id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT , stu_id INT(10) NOT NULL , c_name VARCHAR(20) , test VARCHAR(50) , grade INT(10) );
2.显示所有表
SHOW TABLES;
3.复制test表结构,创建新表test2
注:可以有两种方式
CREATE TABLE test2 LIKE test ;
或
CREATE TABLE weisi2 AS SELECT * FROM weisi WHERE 2=1;
4.复制weisi表结构和数据,创建新表weisi3
CREATE TABLE weisi3 AS SELECT * FROM weisi
5.复制weisi表结构的 id,stu_id,test三个字段,创建新表weisi3
CREATE TABLE weisi3 AS SELECT id,stu_id,test FROM weisi WHERE 1<>1;
6.删除表 test2
DROP TABLE test2 ;
7.同时删除表weisi2和weisi3
DROP TABLE weisi2,weisi3 ;
8.修改test表,添加一个字段test6 (字符类型VARCHAR,长度100,不允许为空)
ALTER TABLE test ADD COLUMN test6 VARCHAR(100) NOT NULL;
9.查看表中的所有字段
DESC test
10.修改test表,删除字段test6
ALTER TABLE test DROP test6
11.把表weisi6,改名为weisi
RENAME TABLE weisi6 TO weisi; 或 ALTER TABLE weisi6 RENAME weisi;
12.把weisi表,改名为 weisi6
ALTER TABLE 旧表名 RENAME TO 新表名 ; ALTER TABLE weisi RENAME TO weisi681 ;
13.修改表weisi,把字段test ,改为test6(字符类型varchar,长度160 )
ALTER TABLE weisi CHANGE test test6 VARCHAR(160);
14.在数据库 testdb2 ,创建weisi表,直接拷贝 test库weisi表的数据和结构 ;
CREATE TABLE testdb2.weisi AS SELECT * FROM test.weisi;
三、表中数据相关的SQL
1.向表中插入数据
向表test插入数据
id = 1 ,uname = weisi ,weisi = 2020
INSERT INTO test(id,uname,sex) VALUES(14,"weisi6",2);
向表weisi插入数据
id=4,stu_id=11,c_name=weisi,grade=90
id=5,stu_id=12,c_name=lin,grade=100
id=6,stu_id=33,c_name=test,grade=20
INSERT INTO weisi(id,stu_id,c_name,grade) VALUES(4,11,"weisi",90),(5,12,"lin",100),(6,33,"test",20); SELECT * FROM weisi;
造数据 ,把test表的所有数据,插入到 weisi表
字段关系
id 取id
stu_id 取id
c_name 取 uname
test 和 grade字段,给默认值 60
INSERT INTO weisi(id,stu_id,c_name,test,grade) SELECT id,id,uname,60,60 FROM test ;
2.查询表中的数据
#查询test表 id = 1的内容
SELECT * FROM test WHERE id = 1;
#查找weisi表,名称(c_name)包含 “i” 的数据
SELECT * FROM weisi WHERE c_name LIKE '%i%' ;
#查找test表,id 包含 “1” 的数据,按id降序
SELECT * FROM test WHERE id LIKE '%1%' ORDER BY id DESC ;
#查找test表,id 包含 “1” 的数据 ,取id最大的三个
SELECT * FROM test WHERE id LIKE '%1%' ORDER BY id DESC LIMIT 3 ;
#找出weisi表中,分数最高的同学和分数;
SELECT c_name,grade AS "maxvalue" FROM weisi WHERE grade IN (SELECT MAX(grade) FROM weisi ) ;
#找出weisi表中,分数最低的同学和分数;
SELECT c_name,grade AS "minvalue" FROM weisi WHERE grade IN (SELECT MIN(grade) FROM weisi );
#找出test表,sex为空的的数据;
SELECT * FROM test WHERE sex IS NULL ;
#查询test表,有多少行数据
SELECT COUNT(sex) FROM test;
#查询test表,有性别类型数量(sex字段,去重)
SELECT COUNT(DISTINCT sex) FROM test;
#查找weisi表,成绩在80 - 100区间的学生 ;
SELECT * FROM weisi WHERE grade BETWEEN 80 AND 100;
#查找test表,id 为 2,11,12 的数据 ;
SELECT * FROM test WHERE id IN (2,11,12) ;
limit是mysql的语法
select * from table limit m,n
其中m是指记录开始的index,从0开始,表示第一条记录
n是指从第m+1条开始,取n条。
select * from tablename limit 2,4
即取出第3条至第6条,4条记录
#排名3 - 6名的学生 和分数 ;
SELECT c_name,grade FROM weisi ORDER BY grade DESC LIMIT 2,4;
#左连接
SELECT * FROM weisi a LEFT JOIN test b ON a.stu_id=b.id;
#内连接
SELECT * FROM weisi a INNER JOIN test b ON a.stu_id=b.id;
#右连接
SELECT * FROM weisi a RIGHT JOIN test b ON a.stu_id=b.id;
3.修改表中的数据
#更新test表,sex为空的,设置为0(性别未知)
UPDATE test SET sex = 0 WHERE sex IS NULL ;
#关联更新
UPDATE 表1 INNER JOIN 表2 ON 表1.`id_` = 表2.`order_id` INNER JOIN 表3 ON 表1.`id_` = 表3.`order_id` SET 表1.`update_time_`= 1650617212846000, 表2.`update_time_` = 1650617212846000, 表3.`update_time_` = 1650617212846000 WHERE 表1.user LIKE 'zs%';
4.删除表中的数据
#删除 test表,id大于12的数据 ;
DELETE FROM test WHERE id > 12;
#关联删除
DELETE 表1,表2,表3 FROM 表1 INNER JOIN 表2 ON 表1.id_ = 表2.order_id INNER JOIN 表3 ON 表1.id_ = 表3.order_id WHERE 表1.user LIKE 'zs%';
#清空weisi表的数据
TRUNCATE TABLE weisi; 或 DELETE FROM weisi;
四、查看数据库版本、字符集
1.查看Mysql版本
SELECT VERSION();
2.修改数据库的字符集
ALTER DATABASE 数据库名 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
3.修改表的字符集
ALTER TABLE 表名 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
五、日期时间相关
1.获取当前系统的日期和时间
select now();
2.日期转时间戳
SELECT UNIX_TIMESTAMP('2022-01-01 00:00:00');
如果想得到13位的时间戳,需要再乘以1000
SELECT 1000*UNIX_TIMESTAMP('2022-01-01 00:00:00');
3.sql中实现一个日期减去几天,几个月,几年
SELECT DATE_SUB(NOW(),INTERVAL 1 DAY);#1天前的日期 SELECT DATE_SUB(NOW(),INTERVAL 1 MONTH);#1月前的日期 SELECT DATE_SUB(NOW(),INTERVAL 1 YEAR);#1年前的日期 sql中实现一个日期加上几天,几个月,几年 SELECT DATE_ADD(NOW(),INTERVAL 1 DAY);#1天后的日期 SELECT DATE_ADD(NOW(),INTERVAL 1 MONTH);#1月后的日期 SELECT DATE_ADD(NOW(),INTERVAL 1 YEAR);#1年后的日期
六、使用变量
1. 定义变量:
2. 使用变量
SET @starttime =1000*UNIX_TIMESTAMP('2022-01-01 00:00:00'); SELECT @starttime;
七、常见问题汇总
1.插入insert into 语句中字段包含中文时报错
解决:
插入中文时报错的原因可能是字符集设置不正确。请确保数据库、表和列的字符集设置为支持中文的字符集,如utf8或utf8mb4。
2.在MySQL中,如果表已经创建并添加了数据,如何设置id字段的默认值自增
- 首先,确保id字段是整数类型(如INT、BIGINT等)。
- 然后,为id字段设置AUTO_INCREMENT属性。
- 最后,将id字段设置为表的主键。
```sql
-- 修改id字段为自增主键
ALTER TABLE 表名
MODIFY id INT AUTO_INCREMENT,
ADD PRIMARY KEY (id);
```
3.Cannot add foreign key constraint
需要先创建从表,再创建主表并添加外键约束。
4.查询数据库中的表和字段信息
select * from information_schema.`COLUMNS` where COLUMN_COMMENT like '%供应商%';
select * from information_schema.`TABLES` where TABLE_COMMENT like '%用户收益%';
5.已知字段名,如何查找该字段所在的表
SELECT
table_schema AS `Database`,
table_name AS `Table`
FROM
information_schema.columns
WHERE
column_name = 'field_name'
AND table_schema = 'your_database'; -- 替换为你的数据库名称
(持续更新中...)
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步