测试人员必会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字段的默认值自增

  1. 首先,确保id字段是整数类型(如INT、BIGINT等)。
  2.  然后,为id字段设置AUTO_INCREMENT属性。
  3. 最后,将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'; -- 替换为你的数据库名称

(持续更新中...)

posted @   测试微思录-静水流深  阅读(48)  评论(0编辑  收藏  举报
努力加载评论中...
点击右上角即可分享
微信分享提示