数据库常用操作_sql语句_使用sql语句,在book表中添加一行记录

一、数据操作
1.增加数据
1）增加一行数据——增加全部值
insert into 表名 values(…);

注意：

a）数据值需要和表的字段完全对应（数据个数和数据类型）

b）主键列是自动增长，插入时需要占位，通常使用0或者default或者null来占位

举例：

insert into students values(0,'张三',28,1.78);

2）增加一行数据——增加部分值
insert into 表名(字段1,…) values(值1,…);

举例：

insert into students values(0,'张三',28,1.78);

insert into students(name,height) values('李四',1.68);

3）增加多行数据
a)方法一：多句执行单行插入语句，以分号隔开

举例：

insert into students values(0,'王五'，23，1.67)；

insert into students(name,height) values ('赵六'，1.82)；

b)方法二：在插入单行数据的语句上，value后边的数据进行多组化处理

insert into 表名 vaues(…),(…);

insert into 表名（列1,…）values(值1,…),(值1,…)…;

举例：

insert into students values(0,'张一',21,1.81),(1,'张二',30,1.65);

insert into students(name,height) values('李三',1.54),(李四'',1.87);

2.修改数据
1）修改数据
update 表名 set 列1=值1，列2=值2… where 条件;

注意：where如果省略，代表修改整列数据，不建议省略where

举例：update students set age=48 where id=9;

3.删除数据
1）删除数据
delete from 表名  where 条件;

注意：where如果省略，代表删除全部数据，不建议省略where

举例：delete from students where id=6;

2）逻辑删除
对于重要的数据，不能轻易执行delete删除语句，因为一旦删除，数据无法回复，这时会用到逻辑删除。表中增加字段isdelete，0代表未删除，1代表删除，默认值0，当需要删除某条数据时，设置该条数据的isdelete字段为1

举例：

update students set isdelete=1 where id=4;

select *from students where isdelete=0;

3)其他删除方法

delete from 表名 ——删除所有数据，但是不重置主键字段的计数

truncate table 表名 ——删除所有数据，并重置主键字段的计数

drop table 表名 ——删掉表（字段和数据均不再存在）

举例：

delete from students;

truncate table students;

drop table students;

4.查询语句
1）基本查询
a）查询表内所有数据

select *from 表名；

举例：select *from students;

b）查询表内部分字段

select 字段名1，字段名2 from 表名；

举例：select name,age from students;

c）字段起别名查询

select 字段名 as '别名' from 表名;    （引号可以省略，as也可以省略）

select name as '姓名' from students；

起别名的作用：1）美化数据结果的显示效果 2）可以起到隐藏真正字段名的作用

d）去重查询

select distinct(字段名) from 表名；

举例：select distinct(company) from goods;

2）条件查询
a）逻辑运算符

select *from goods where price >30 and company != ''PDD;

b）模糊查询

like %

举例：

select *from goods where remark like '%一次性%'；          ——关键词在中间

select *from goods where remark like '%一次性';               ——关键词在末尾

selects *from goods where remark like '一次性%';             ——关键词在开头

c）范围查询

查询价格范围在30-100的商品信息

举例：select *from goods where price between 30 and 100;

d）判断空查询

i）查询描述为空的所有商品

select *from goods where remark is null;

ii）查询有描述信息的所欲商品

select *from goods where remark is not null;

3）其他复杂查询
a）排序

查询说明：查询所有商品信息，按照价格从大到小排序，价格相同时，按照数量由少到多排序

select *from 表名 order by 列1 asc|desc ,列2 asc|desc,…

说明：order by ——排序；asc ——升序 ；desc ——降序

select *from goods order by price desc,count asc;   

默认排序为升序，上面的asc可以省略，修改为：select *from goods order by price desc,count;

b）聚合函数

i）统计记录总数

select count(*) from goods;

扩展：可以增加筛选条件，如：select count(*) from goods where name = '矿泉水';

ii）其他

select max(price) from goods;                                                             ——最高商品价格

select min(price) from goods;                                                              ——最低商品价格

select avg(price) from goods;                                                              ——商品平均价格

select sum(amount) from goods where remark like '%一次性%';        ——一次性商品的总金额

c）分组

i）分组查询

select 字段1，字段2，聚合… from 表名 group by 字段1，字段2…

select sum(amount) from goods group by company; ——按照公司名称分组统计各个公司的金额总数

还可以分组统计数量：select count(*) from goods group by company;

ii）分组后加条件过滤

select 字段1，字段2，聚合… from 表名 where 条件 group by 字段 having 条件;

举例：显示每个地区的总人口数和总面积，仅显示那些面积超过1000000的地区

select region,sum(population),sum(area) from bbc group by region having sum(area)>1000000;

where 和having的区别

where是对from后面指定的表进行数据筛选，属于对原始数据的筛选
having是对group by 的结果进行筛选
having后面的条件中可以用聚合函数，where后面不可以
4）分页查询

select *from 表名 limit start,count;

举例：

select *from goods limit 0,5;          ——获取前5条数据

select *from goods limit 5;             ——默认从一条取值，0可以省略

扩展：

select *from goods order by price desc limit 1;             ——查询商品价格最贵的一条数据信息

select *from goods order by price desc limit 3;            ——查询商品价格最贵的前三条数据信息

5.连接查询
1）内连接
select *from 表1 inner join 表2 on 表1.列=表2.列

内连接查询，按照图表表示如下：取的值是A和B的交集，也就是A和B都有的数据才能查出来

A表和B表结构如下：

select *from A inner join B on A.id=B.id;

还可以写成：select *from A join B on A.id=B.id; 或者select *from A , B where A.id=B.id;

取出来的数据是id为1，2，3

2）左连接（左外连接）
select *from 表1 left join 表2 on 表1.列=表2.列

左连接查询，按照图表表示如下：取的值是A的全部加上A和B的交集部分

select *from A left join B on A.id=B.id;

还可以写成：select *from A left outer join B on A.id=B.id;

取出来的数据是id为1，2，3，4

3）右连接（右外连接）
select *from 表1 right join 表2 on 表1.列=表2.列

右连接查询，按照图表表示如下：取的值是B的全部加上A和B的交集部分

select *from A right join B on A.id=B.id;

还可以写成：select *from A right outer join B on A.id=B.id;

取出来的数据是id为1，2，3

4）全连接
取的是A和B两个表的全部

select *from A left join B on A.id=B.id union select *from A right join B on A.id=B.id;

或者select *from A full join B on A.id=B.id;

 6.子查询语句充当查询条件或数据源
1）子查询语句充当查询条件
select *from goods where price > (select avg(price)from goods);          ——查询价格高于平均价的商品信息

2）子查询语句充当数据源
select *from (select *from goods go left join category ca on go.typeid=ca.typeid) new where new.company = ''PDD;                             ——查询所有来自PDD的商品信息，包含商品分类

其中，goods  别名go              category别名ca    

二、数据类型和约束
1.数据类型
1）整数: int
有符号范围（-2147483648 ~2147483647），⽆符号 （unsigned）范围（0 ~ 4294967295）

2）⼩数: decimal
例如：decimal(5,2) 表示共存5位数，⼩数占2位，整数占3 位

3）字符串: varchar
范围（0~65533），例如：varchar(3) 表示最多存3个字 符，⼀个中⽂或⼀个字 ⺟都占⼀个字符

4）⽇期时间: datetime
范围（1000-01-01 00:00:00 ~ 9999-12-31 23:59:59），例如：'2020-0101 12:29:59'

2.约束
1）主键（primary key）
能唯⼀标识表中的每⼀条记录的属性组

2）⾮空（not null）
此字段不允许填写空值

3）唯⼀（unique）
此字段的值不允许重复

4）默认值（default）
当不填写此值时会使⽤默认值，如果填写时以填写为准

5） 外键（foreign key）
⼀个表中的⼀个字段引⽤另⼀个表的主键

说明: 通过外部数据表的字段, 来控制当前数据表的数据内容变更, 以避免单⽅⾯移除数据, 导致关联表数据产⽣垃圾数据的⼀种⽅法。

注意: 如果⼤量增加外键设置, 会严重影响除数据查询操作以外的其他操作(增/ 删/改)的操作效率。

6）索引
说明: 为字段添加索引，可以⼤幅度提⾼查询语句的执⾏效率。

注意: 如果⼤量增加索引设置, 会严重影响除数据查询操作以外的其他操作(增/ 删/改)的操作效率, 故不⽅便过多添加。

引申：如果测试过程中，需要查询数据库表中是否使用了索引，可以使用show index from 表名;语句，该语句会返回该表的所有索引信息，包括索引名、索引类型、索引包含的列等。字段含义见下表：

字段名    含义
Non_unique    表示该索引是否是唯一索引，0代表是唯一，1代表不是唯一
Seq_in_index    表示该列在索引中的位置，如果索引是单列，则值为1；如果索引是组合索引，则该列的值为每列在索引中的顺序
Column_name    表示定义索引的列字段
Collation    表示列以何种顺序存储在索引中。在Mysql中国，A代表升序，若显示NULL，则表示无分类
Cardinality    索引中唯一值数目的估计值
Sub_part    表示列中被编入索引的字符的数量。若列只是部分被编入索引，则该列的值为被编入索引的字符的数目；若整列被编入索引，则该列的值为 NULL
Packed    指示关键字如何被压缩，若没有被压缩，值为 NULL
Null    用于显示索引列中是否包含 NULL。若列含有 NULL，该列的值为 YES。若没有，则该列的值为 NO
Index_type    显示索引使用的类型和方法（BTREE、FULLTEXT、HASH、RTREE）
Comment    显示评注