MySQL 04 :增删改查
CREATE TABLE goods(
id INT,
goods_name VARCHAR(10),
price DOUBLE);
INSERT INTO goods (id,goods_name,price)
VALUES(1001,'酒',100.12);
INSERT INTO goods (id,goods_name,price)
VALUES(11,'手机',3000);
SELECT * FROM goods;
DESC goods;
INSERT INTO employee (id,user_name,birthday,entry_date,job,Salary,resume,image)
VALUES(101,'tim','2022-10-11','2022-10-11 10:10:10','看大门',100.123,'大王派我来巡山','asdas'),(101,'tim','2022-10-11','2022-10-11 10:10:10','看大门',100.123,'大王派我来巡山','asdas');
Insert 使用细节
–1。插入的数据应与字段的数据类型相同。比如把‘abc‘添加到int类型会错误
– 2。数据的长度应在列的规定范围内,例如:不能将一个长度为80的字符串加入到长度为40的列中。
– 3。在va1ues中列出的数据位置必须 与被加入的列的排列位置相对应。
– 4。字符和日期型数据应包含在单引号中。
– 5。列可以插入空值[前提是该字段允许为空],insert into table value(nul1)
– 6。 insert into tab name(列名。。)values(),(),()形式添加多条记录
– 7。如果是给表中的所有字段添加数据,可以不写前面的字段名称
– 8。 默认值的使用,当不给某个字段值时,如果有默认值就会添加,否则报错
如果某个列没有指定not nul。
1,那么当添加数据时,没有给定值,则会默认给null
2, 如果我们希望指定某个列的默认值,可以在创建表时指定
-9。values没有覆盖所有字段,则不可以省略表明后边的()
update
要求:在上面创建的employee表中修改表中的纪录
使用细节
-- 演示update语句
-- 要求:在上面创建的employee表中修改表中的纪录
-- 1。将所有员工薪水修改为5000元。[如果没有带where,会修改所有的记录,因此小心]
UPDATE employee SET salary = 5000;
UPDATE employee
SET salary = 3000
WHERE user_name = '韩顺平';
UPDATE employee
SET salary = salary + 1000
WHERE user_name = '韩顺平';
UPDATE employee
SET salary = salary + 1000 , job = '出主意'
WHERE user_name = '韩顺平';
SELECT * FROM employee;
DESC employee;
delete语句
不加where 删除表中所有记录,老师提醒,一定要小心
DELETE FROM employee
WHERE user_name ='tim';
SELECT *FROM employee;
DESC employee;
INSERT INTO employee (id,user_name,birthday,entry_date,job,Salary,resume,image)
VALUES(101,'tim','2022-10-11','2022-10-11 10:10:10','看大门',100.123,'大王派我来巡山','asdas'),(101,'tim','2022-10-11','2022-10-11 10:10:10','看大门',100.123,'大王派我来巡山','asdas')
-- 删除表中所有记录,老师提醒,一定要小心
DELETE FROM employee;
UPDATE employee SET job = '' WHERE user_name = 'tim' ;
DROP TABLE employee;
SELECT 单表、多表
基本语法
注意事项
使用表达式对查询的列进行运算
在selecti语句中可使用as语句
在where子句中经常使用的运算符
ALTER TABLE student CHANGE `NAME` `name` VARCHAR(32) NOT NULL DEFAULT ''
-- 查询姓名为赵云的学生成绩
SELECT * FROM student
WHERE `name` = '二飞';
-- 查询英语成绩大于90分的同学
SELECT * FROM student
WHERE english >= 80;
-- 查询总分大于200分的所有同学
SELECT 'name' AS '名字',(chinese + english + math) AS total_score FROM student;
SELECT * FROM student
WHERE (chinese + english + math)>250;
-- 先改一下值,
UPDATE student
-- SET id = 4
SET math =100
WHERE `name` = '张飞';
-- 查询math大于60并且(and)id大于4的学生成绩
SELECT * FROM student
WHERE math > 60 and id >1 ;
-- 查询英语成绩大于语文成绩的同学
SELECT * FROM student
WHERE english>chinese;
-- 查询总分大于200分并且数学成绩小于语文成绩,的姓韩的学生。
SELECT * FROM student
WHERE (english + chinese + math)>200 AND math < chinese AND `name` LIKE '李%';
SELECT * FROM student
-- WHERE english >=80 AND english <=90;
WHERE english BETWEEN 80 AND 90;
SELECT * FROM student
-- WHERE math = 89 OR math = 90 OR math = 91;
WHERE math IN(89,90,91);
SELECT * FROM student
WHERE `name` LIKE '李%';
SELECT * FROM student
WHERE math >80 AND chinese >80;
SELECT * FROM student
-- WHERE english >=80 AND english <=90;
WHERE english BETWEEN 80 AND 90;
SELECT * FROM student
-- WHERE math = 89 OR math = 90 OR math = 91;
WHERE math IN(89,90,91);
SELECT * FROM student
WHERE `name` LIKE '李%';
SELECT * FROM student
WHERE math >80 AND chinese >80;
SELECT * FROM student
WHERE chinese BETWEEN 50 AND 90;
SELECT (english + math + chinese) AS '总分' FROM student;
SELECT * FROM student
WHERE (english + math + chinese) IN (210,254,257);
-- 查询所有姓李或者姓宋的学生成绩。
SELECT * FROM student
WHERE `name` LIKE '李%' OR `name` LIKE '二%';
-- 查询数学比语文多30分的同学
SELECT * FROM student
WHERE (math-chinese) = 1;
使用order by子句排序查询结果。
-- 演示order by使用
-- 对数学成绩排序后输出【升序】。
SELECT * FROM student
ORDER BY math ASC; -- 默认就是升序
SELECT * FROM student
ORDER BY math DESC;
-- 对总分按从高到低的顺序输出
-- SELECT * FROM student
-- ORDER BY (english + math + chinese) DESC;
SELECT `name`,(english + math + chinese) AS total_score FROM student
ORDER BY total_score DESC;
-- 对姓李的学生成绩排序输出(升序)
SELECT `name`, (english + math + chinese) AS total_score FROM student
WHERE `name` LIKE '李%'
ORDER BY total_score;
DESC student;