学习MySQL中对于表这种MySQL对象的单表增删改查操作后,列出一些实例,在此用于记录对应功能的语法细节,便于日后遗忘时查看。
需求:插入一条学生信息
1.插入一条数据
INSERT INTO t_student (name) VALUES('陆小凤')
2.插入多条数据
INSERT INTO t_student(name,email,age) VALUES('A','B@',18),('B','B@',52)
4.插入查询结果(测试)
INSERT INTO t_student(name) SELECT name FROM t_student
需求:修改陆小凤的名字为西门吹雪
UPDATE t_student SET name = '西门吹雪' WHERE NAME = '陆小凤'
例子
UPDATE t_student SET name = 'LHLJ',age = 15, email ='5564@' WHERE id = 3
需求:删除id为3的学生信息
DELETE FROM t_student WHERE id=3
查询所有信息
SELECT * FROM t_student
查询名字
SELECT id FROM t_student
去重查询
SELECT DISTINCT dir_id from product
查询货品id,名称和批发价(卖价*折扣)
SELECT id,producrNmae,cutoff*saleprice FROM product
设置别名
SELECT id AS pid FROM product
需求:不要输出表,要输出“xxx商品的零售价为xxx”
SELECT CONCAT(productname,'商品的零售价为',saleprice) FROM product
需求:条件查询:查询所有货品名为'罗技'
SELECT * FROM product WHERE productName = '罗技'
需求:查询分类号为2或3的商品
SELECT * FROM product where dir_id = 2 OR dir_id = 3
SELECT * FROM product where dir_id IN (2,4)
范围查询:零售价300-400的货品
SELECT * FROM product where saleprice BETWEEN 300 AND 400
IS NULL 用来判断列的值是否为空
SELECT * FROM product where saleprice IS NULL
模糊查询:如搜索“李“ 关键字:LIKE
LIKE 的通配符:
_:必须占据一个位置,任意字符
%:可以占据0~n个位置,任意字符
比如:
WHERE name LIKE '张_三' 搜索张某三
WHERE name LIKE '张%' 搜索张某某
WHERE name LIKE '%张%' 搜索名字中带“张”
需求:查询货品平均价
SELECT AVG(aslePrice ) FROM product
排序结合分页(先排序再分页)
SELECT id,productname FROM product ORDER BY salePrice DESC LIMIT 0,5