CRUD语句
create、read、update、delete
1.insert添加数据
2.update更新数据
3.delete删除数据
4.select查找数据
insert添加数据
例子:
#创建商品表
#插入两条数据
CREATE TABLE `goods`(
id INT,
goods_name VARCHAR(10),
price DOUBLE);
INSERT INTO `goods` (id,goods_name,price)
VALUES(10,'煞笔',2);
INSERT INTO `goods` (id,goods_name,price)
VALUES(11,'ss',2);
使用插入语句时要注意:
1.插入的数据要与字段的类型相同
INSERT INTO `goods` (id,goods_name,price)
VALUES('10','煞笔',2);
虽然是单引号引起来的`10`,但MySQL会尝试转换,所以是成功的
2.数据的长度要在规定的范围内
3.数据顺序要对应
4.字符和日期要包含在单引号中
5.列可以插入空值(前提是该字段允许为空)
6.一次性添加多条数据
INSERT INTO `goods` (id,goods_name,price)
VALUES('10','qqq',4),('11','www',5)
7.如果是给表中的所有字段添加数据,可以不写前面的字段名称
8.默认值的使用,当不给某个字段值时,如果有默认值就会添加默认值,否则为 null 。
人话:如果某个列 没有指定 not null , 那么当添加数据时,没有给定值,则默认给null
update语句
修改表中数据
例子:
修改所有:
UPDATE `goods`
SET `price` = 100
指定修改:
UPDATE `goods`
SET `price` = 1000
WHERE `id` = 10
指定添加:
UPDATE `goods`
SET `price` = `price` + 1000
WHERE `id` = 11
使用update语句时要注意:
1.update语句可以用新值更新原有表行中的各列
2.set语句指示要修改哪些列和要给予哪些值
3.where语句指定应该更新哪些行。如果没有where,则更新所有行(慎用)。
4.如果要修改多个字段,可以通过:
set 字段1=值1,
set 字段2=值2…
UPDATE `goods`
SET `price` = 1000 , `goods_name` = 'aa'
WHERE `id` = 10
delete语句
例子:
指定删除数据:
DELETE FROM `goods`
WHERE `id` = 10
删除所有数据:
DELETE FROM `goods`
注意:
1.如果不适用where,将删除表中所有数据
2.delete语句不能删除某一“列”的值(应该要用update语句)
3.使用delete语句只删除记录,不删除表本身。要删除表,使用drop table table_name
select语句
分单表和多表
SELECT [DISTINCT] *
FORM TABLENAME;
SELECT 后面指定查询字段,*表示查询所有列(全部)
DISTINCT 加上这个,当查询后显示的数据如果有相同,则只显示一行。
FORM表示查那张表
单表:
SELECT DISTINCT `id`,`price` FROM goods
select使用表达式对查询语句的列进行运算
在select语句中可使用as语句
SELECT `id` , (`id`+`price`) FROM `goods`
SELECT `id` , (`id`+`price`) AS `end` FROM `goods`
SELECT `id` AS `名字` , (`id`+`price`) AS `end` FROM `goods`
where子句中经常使用到的运算符
比较运算符、逻辑运算符
比较运算符:
> < <= >= = != 判断大小
between ....and ..... 显示在某一区间的值
in(set) 显示在in列表中的值,例如:in(100,200)
like '张ppp' 模糊查询
not like ''
is null 判断是否为空
and 多个条件同时成立
or 多个条件任意成立
ont 不成立 例子:where not(salary>100);
例子:
SELECT * FROM `student`
WHERE `name` = 'ldt'
SELECT * FROM `student`
WHERE `yuwen` > 60
SELECT * FROM `student`
WHERE (`yuwen`+`shuxue`+`yingyu`) > 190
SELECT * FROM `student`
WHERE `yuwen` > 70 AND id > 3
SELECT * FROM `student`
WHERE `yuwen` > `shuxue`
SELECT * FROM `student`
WHERE (`yuwen`+`shuxue`+`yingyu`) > 190 AND
`shuxue` > `yuwen` AND `name` LIKE 'lead%'
SELECT * FROM `student`
WHERE `yuwen` >= 60 AND `yuwen` <= 70
SELECT * FROM `student`
WHERE `yuwen` BETWEEN 60 AND 70
SELECT * FROM `student`
WHERE `shuxue` = 90 OR `shuxue`=87
SELECT * FROM `student`
WHERE `shuxue` IN (90 ,87)
order by 子句排序查询结果
order by 列名 这个列名可以是列名,也可以是select语句后(AS)的别名。
asc 升序 desc降序
order by 子句应位于select语句的结尾
SELECT * FROM `student`
ORDER BY `shuxue` ASC;
SELECT * FROM `student`
ORDER BY `shuxue` DESC;
SELECT `name` , ( `yuwen` + `shuxue` + `yingyu` ) AS `zongfen` FROM `student`
ORDER BY `zongfen` DESC;
count合计、统计函数
例子:
SELECT COUNT(*) FROM student;
SELECT COUNT(*) FROM student
WHERE ( `yuwen` + `shuxue` ) > 150;
SELECT `name` , ( `yuwen` + `shuxue` + `yingyu` ) AS `zongfen` FROM `student`
ORDER BY `zongfen` DESC;
SELECT COUNT(*) FROM `student`
SELECT COUNT(`yuwen`) FROM `student`
区别:* null也算上,有字段则计算非空
sum合计函数
sum函数返回满足where条件的行的和,使用在数值列
只对数值起作用,否则会报错
例子:
SELECT SUM(`shuxue`) FROM `student`;
SELECT SUM(`yuwen`),SUM(`shuxue`),SUM(`yingyu`) FROM `student`;
SELECT SUM(`yuwen`+`shuxue`+`yingyu`) AS `end` FROM `student`;
avg平均值
例子:
SELECT AVG(`yuwen`+`shuxue`+`yingyu`) FROM student;
最大值和最小值
max、min
SELECT MAX(`yuwen`+`shuxue`+`yingyu`),MIN(`yuwen`+`shuxue`+`yingyu`) FROM student;
group by 子句对列进行分组
having 子句对分组后的结果进行过滤
注意
CREATE TABLE `yuangong`(
`id` INT UNSIGNED NOT NULL DEFAULT 0,
`name` VARCHAR(20) NOT NULL DEFAULT ' ',
`qian` INT NOT NULL DEFAULT 0,
`dengji` INT UNSIGNED NOT NULL DEFAULT 0);
INSERT INTO `yuangong` (`id`,`name`,`qian`,`dengji`)
VALUES (1,'a',1001,1),(2,'b',5032,2),(3,'c',1486,3),(4,'d',1315,2),(5,'e',5134,1),
(6,'f',6112,1),(7,'g',7435,3),(8,'h',1356,2),(9,'i',6135,3);
SELECT * FROM `yuangong`
SELECT AVG(`qian`) ,MAX(`qian`),`dengji`
FROM `yuangong` GROUP BY `dengji`;
可以按多个列分组
SELECT AVG(`sal`),MIN(`sql`),`deptno`,`job`
FROM `emp` GROUP BY `deptno`,`job`;
带上HAVING
SELECT AVG(`qian`) ,MAX(`qian`),`dengji`
FROM `yuangong` GROUP BY `dengji` HAVING AVG(`qian`)<4100;
使用别名减少一次运输
SELECT AVG(`qian`) AS avg_qian ,`dengji`
FROM `yuangong` GROUP BY `dengji` HAVING avg_qian < 5000;