mysql增删改查

叁歲伎倆 2022-06-08 10:19 403阅读 0赞

USE AAA;
CREATE TABLE IF NOT EXISTS TEACHER(
TID INT UNSIGNED NOT NULL AUTO_INCREMENT,
TNAME VARCHAR(50),
TAGE TINYINT UNSIGNED,
TBIR DATETIME,
TADDRESS VARCHAR(50),
TSCORE DECIMAL(4,2),
PRIMARY KEY(TID)
)ENGINE=MYISAM AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8;

-- insert 插入操作
INSERT INTO TEACHER VALUES(NULL,’张三’,19,’1990-02-02’,’辽宁省沈阳市铁西区保工街’,88);
INSERT INTO TEACHER VALUES(NULL,’张三’,19,’1990-02-02’,’辽宁省沈阳市’,99);
INSERT INTO TEACHER VALUES(NULL,’张三’,19,’1990-02-02’,’辽宁省鞍山市’,55);

-- INSERT 另一种插入方式
INSERT TEACHER VALUES(NULL,’张三’,NULL,’1999-02-02’,’辽宁省海城市’,59.99);

-- INSERT 同一时间插入多条数据
INSERT into TEACHER
VALUES(NULL,’李四’,20,NOW(),’上海’,60.01),
(NULL,’李5’,20,NOW(),’上海’,60.01),
(NULL,’李6’,21,NOW(),’深圳’,50.01),
(NULL,’李7’,22,NOW(),’广州’,50.81),
(NULL,’李8’,23,NOW(),’北京’,70.01);

-- 插入的另一种方式
INSERT INTO TEACHER(TID,TNAME,TAGE,TBIR,TADDRESS,TSCORE) VALUES
(NULL,’李8’,23,NOW(),’北京’,70.01);

INSERT TEACHER(TNAME,TADDRESS) VALUES(‘赵六’,’天津’),(‘老四’,’北京’);

REPLACE TEACHER(TNAME,TADDRESS) VALUES(‘赵六1’,’天津1’),(‘老四1’,’北京1’);

SELECT * FROM TEACHER;

-- 复制本表的数据并且插入本表
INSERT INTO TEACHER(TNAME,TAGE,TBIR,TADDRESS,TSCORE)
SELECT TNAME,TAGE,TBIR,TADDRESS,TSCORE FROM TEACHER;

-- 修改

UPDATE TEACHER SET TADDRESS=’北京’ WHERE TID=1;
-- 修改
UPDATE TEACHER SET TNAME=’知道是谁’,TAGE=11,TBIR=’1999/02/01’,TADDRESS=’上海’,TSCORE=99 WHERE TID=25;

-- 删除
DELETE FROM TEACHER WHERE TID=28;
DELETE FROM TEACHER — 无条件，删除所有记录
-- 直接清空数据表记录全部删除，anto_INCREMENT 从一开始

TRUNCATE TABLE TEACHER;

-- SELECT 查询语句
USE AAA;

SELECT
*
FROM
TEACHER;

-- 只显示姓名
SELECT
TNAME
FROM
TEACHER;

-- 别名
SELECT
TNAME 姓名,
TAGE 年龄,
TADDRESS 家庭住址,
TBIR 出生日期
FROM
TEACHER;

-- 基本查询条件 < > <= >= !=
SELECT
*
FROM
TEACHER
WHERE
TSCORE < 60;

SELECT
*
FROM
TEACHER
WHERE
TSCORE > 60;

-- 查询不等于的三条语句 查询结果一样
SELECT
*
FROM
TEACHER
WHERE
TSCORE != 99;

SELECT
*
FROM
teacher
WHERE
TSCORE <> 99;

SELECT
*
FROM
teacher
WHERE
NOT TSCORE = 99;

-- 范围查询
SELECT
*
FROM
teacher
WHERE
TSCORE BETWEEN 69
AND 80;

-- 不在这个范围查询
SELECT
*
FROM
teacher
WHERE
TSCORE NOT BETWEEN 69
AND 80;

SELECT
*
FROM
teacher
WHERE
TBIR BETWEEN ‘1990-01-01’
AND ‘1990-12-31’;

-- 模糊查询 LIKE
-- 查询姓名是两个字的老师信息
SELECT
*
FROM
teacher
WHERE
TNAME LIKE ‘__‘;

-- 查询姓张的人员信息
SELECT
*
FROM
teacher
WHERE
TNAME LIKE ‘张_‘;

-- 查询名字里面带三的人员信息 %代表0个或者多个任意字符
-- “_”代表1个任意字符
SELECT
*
FROM
teacher
WHERE
TNAME LIKE ‘%三%’;

-- 删除所有名字中带有三的名字
DELETE
FROM
teacher
WHERE
TNAME LIKE ‘%三%’;

-- 基本条件 in()
SELECT
*
FROM
TEACHER
WHERE
TID IN(1, 10);

-- 不在in 这个范围内的
SELECT
*
FROM
teacher
WHERE
TID NOT IN(1, 10)— 基本条件null运算查询没有成绩的老师
SELECT
*
FROM
TEACHER
WHERE
TSCORE IS NULL;

-- 将没有成绩的学生 改成成绩是0分
UPDATE TEACHER
SET TSCORE = 0
WHERE
TSCORE IS NULL;

SELECT
*
FROM
TEACHER
WHERE
TSCORE = 0;

-- 查询老师年龄是空字符串的人
SELECT
*
FROM
TEACHER
WHERE
TAGE = ‘’;

-- 查询老师年龄是null的人
SELECT
*
FROM
TEACHER
WHERE
TAGE IS NULL;

发表评论

表情：
评论列表 （有 0 条评论，403人围观）

还没有评论，来说两句吧...

相关阅读

    相关 MySQL 增删改查

    MySQL 增删改查 虽然经常对数据库进行操作，但有时候一些sql语句还是会忘记，因此总结一下，方便记忆。 一 对库操作 1 创建数据库 > create dat

    梦里梦外;梦里梦外;/ 0 赞/ 476 阅读