数据库【SQL语言】

数据库【SQL语言】

1、数据定义语言DDL(Data Definition Language)

对象： 数据库和表

• 关键词： create 、alter、 drop、 truncate(删除当前表再新建一个一模一样的表结构)
• 创建数据库：create database 库名;
• 删除数据库：drop database 库名;
• 切换数据库：use 库名;
• 创建表：create table 表名(

字段名 类型(大小) 关系描述);

• 查看数据库里存在的表：show tables;
• 修改：alter table 表名 约束;
• 删除表：drop table 表名 ;
• 查看生成表的sql语句：show create table 表名 ;
• 查看表结构：desc 表名;

注意：

varchar类型的长度是可变的，创建表时指定了最大长度，定义时，其最大值可以取0-65535之间的任意值，但记录在这个范围内，使用多少分配多少，varchar类型实际占用空间为字符串的实际长度加1。这样，可有效节约系统空间。varchar是mysql的特有的数据类型。

char类型的长度是固定的，在创建表时就指定了，其长度可以是0-255之间的任意值。虽然char占用的空间比较大，但它的处理速度快。

基本sql语句：

# 创建数据库
create database 库名;
# 删除数据库
drop database 库名;
# 使用或者切换数据库
use 库名;
# 创建表
create table 表名 (
	id int(4) primary key auto_increment,
  	列名1 varchar(20),
  	列名2 int(3) 
);
# 查看数据库中存在的表
show tables;
# 添加表的字段(列)
alter table 表名 add 新字段名(列名) 数据类型;
# 删除表的字段(列)
alter table 表名 drop 字段名 ;
# 修改字段名(列名)
alter table 表名 change 旧字段名 新字段名 新数据类型 ;
# 修改字段的数据类型	
alter table 表名 modify 字段名 数据类型 ;
# 删除表
drop table 表名;
# 查看生成表的sql语句
show create table 表名;
# 查看表结构
desc 表名;

举例：

# 修改student表为teacher
alter table student rename to teacher;
# 在student表中添加password列
alter table student add password varchar(20);
# 修改字段名(将password列名改为pwd)
alter table student change password pwd varchar(20);
# 修改数据类型(将student表的password列的数据类型改为int)
alter table student modify password int;
# 删除student中的password列
alter table student drop password;
# 删除student表
drop table student;
# 查看生student表的sql语句
show create table student;
# 查看student表结构
desc student;

2、数据操纵语言DML(Data Manipulation Language)

对象：记录(行)

• 关键词：insert 、update 、delete
• 插入：insert into 表名 values(全属性); (插入所有的字段)

insert into 表名(属性1,属性2) values (属性1,属性2); (插入指定的字段)

• 更新：update 表名 set 属性1 = ‘值’,属性2= ‘值’ where 主键(通常) = 值;
• 删除：delete from 表名 where 主键(通常) = 值;

注意：

​ 开发中很少使用delete,删除有物理删除和逻辑删除，其中逻辑删除可以通过给表添加一个字段(isDel)，若值为1，代表删除；若值为0，代表没有删除。此时，对数据的删除操作就变成了update操作了。

truncate和delete的区别：

​ truncate是删除表，再重新创建这个表。属于DDL；

​ delete是一条一条删除表中的数据，属于DML。

# 指定所有字段名，插入指定的字段
insert into 表名(字段名1, 字段名2) values(值1, 值2);
insert into student(name,score) values ('kekebo',17),('someone',26); 

# 不指定字段名，插入所有的字段名
# 注意：由于insert语句中没有指定字段名，添加值的顺序，必须和字段在表中已经定义的顺序相同
insert into 表名 values(值1, 值2);
insert into student values(01,'tonbby',99);

#修改
update student set name = 'tonbby',score = '999' where id = 01;
#删除
delete from student where id = 4;

3、数据查询语言DQL(Data Query Language)

select … from student where 条件 group by 分组字段 having 条件 order by 排序字段

执行顺序：from->where->group by->having->order by

3.1、条件查询

简单条件运算符：> < = != <> >= <=

逻辑运算符：&& || ! and or not （用于连接条件表达式）

模糊查询：like、between and、in、is null

3.2、排序查询

• 语法：order by 子句
  • order by 排序字段1 排序方式1 ， 排序字段2 排序方式2...
• 排序方式：
  • ASC：升序，默认的。
  • DESC：降序。
• 注意：
  • 如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件。

3.3、聚合函数

将一列数据作为一个整体，进行纵向的计算。

1. count：计算个数

   一般选择非空的列：主键

   select count(id) from 表名

2. max：计算最大值

3. min：计算最小值

4. sum：计算和

5. avg：计算平均值

注意：聚合函数的计算，排除null值。

3.4、分组查询

1. 语法：group by 分组字段；
2. 注意：
   1. 分组之后查询的字段：分组字段、聚合函数
   2. where 和 having 的区别？
      • where 在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来
      • where 后不可以跟聚合函数，having可以进行聚合函数的判断。

注意：

​ group by 通常和聚合函数(avg(),count()…)一起使用 ,经常先使用group by关键字进行分组，然后再进行集合运算。

​ group by与having 一起使用，可以限制输出的结果，只有满足条件表达式的结果才会显示。

having和where的区别：

​ 两者起作用的地方不一样，where作用于表或视图，是表和视图的查询条件。having作用于分组后的记录，用于选择满足条件的组。

#所有字段查询
SELECT * FROM `student`
#带条件查询
SELECT * FROM `student` WHERE id > 1
SELECT * FROM `student`  WHERE name like '%on%'
SELECT * FROM `student`  WHERE id BETWEEN 1 AND 3
SELECT * FROM `student`  WHERE id not in(1,2,3)
# 综合
SELECT *,AVG(score) FROM `student`  WHERE id > 1 GROUP BY id HAVING AVG(score)>20 ORDER BY id DESC;

3.5、分页查询

1. 语法：LIMIT 开始的索引, 每页查询的条数;
2. 公式：开始的索引 = （当前的页码 - 1） * 每页显示的条数

SELECT * FROM `student`  WHERE id > 0 GROUP BY id  ORDER BY id LIMIT 1 ,1;