mysql增删改查

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;