SQL语句基础操作汇总
目录
新建 Excel-sheet-填入数据
新建数据库(DataBase)>新建表(able)>插入数据/更新数据/删除数据
下面的几类SQL语句就是在帮助我们完成上面的流程
DDL语句(Data Definition Language数据定义语言)
创建数据库
创建表
修改表结构等
结构性操作,创建或修改数据库/表结构,不涉及具体存储的数据
DML语句(Data Manipulation Language数据操纵语言)
针对 table数据表中数据的增删改,使用DML
DQL语句(Data Query Language数据查询语言)
针对 table数据表中数据的查询操作,使用DML
DCL语句(Data Controll Language数据控制语言)
事务的提交/回滚等
一、DDL语句(Data Definition Language)
(一)DDL操作数据库(database)
1、创建数据库
命令 | 说明 |
---|---|
create database 数据库名; | 创建指定名称的数据库 |
create database 数据库名 character set 字符集; | 创建指定名称的数据库,并且指定字符集(一般都指定utf8) |
切换数据库:
use 数据库名;
查看当前数据库:
select database ();
查看有哪些数据库:
show databases;
查看数据库定义信息:
show create database 数据库名;
2、修改数据库
修改数据库字符集
命令 | 说明 |
---|---|
alter database 数据库名 character set 字符集 | 数据库的修改字符集操作 |
3、删除数据库
命令 | 说明 |
---|---|
drop database 数据库名 | 从MySq中永久的删除某个数据库 |
(二)DDL操作数据表(table)
一个数据库中可以n个表,表中又有字段(列),在建表的时候就需要定义好字段(指定字段属性—>字段数据类型+字段长度等)
1、常用的数据类型:
类型 | 描述 |
---|---|
int | 整型 |
double | 浮点型 |
varchar | 字符串型 |
date | 日期类型,给是为yyy-mm-dd,只有年月日,没有时分秒 |
注意: MySql中的char类型与 varchar类型,区别在于:
char类型是固定长度:根据定义的字符串长度分配足够的空间
varchar类型是可变长度:只使用字符串长度所需的空间
2、创建表
语法格式:
CREATE TABLE 表名(
字段名称1 字段类型(长度),
字段名称2 字段类型 #注意最后一列不要加逗号
);
快速创建一个表结构相同的表(复制表结构)
语法格式:
create table 新表名 like 旧表名;
3、查看表
命令 | 说明 |
---|---|
show tables; | 查看当前数据库中的所有表名 |
desc 表名; | 查看数据表的结构 |
查看数据表的建表语句:
show create table 表名;
4、删除表
命令 | 说明 |
---|---|
drop table 表名; | 删除表 |
删除前如果先判断是否存在表:
drop table if exists 表名;
5、修改表
1.修改表名
语法格式:
rename table 旧表名 to 新表名;
2.向表中添加列,关键字ADD
语法格式:
alert table 表名 add 字段名称 字段类型;
3.修改表中列的数据类型或长度,关键字 MODIFY
语法格式:
alter table modify 表名 字段名称 字段类型;
4.修改列名称,关键字 CHANGE
语法格式:
alter table表名 change 旧列名 新列名 类型(长度);
5.删除列,关键字DROP
语法格式
alter table 表名 drop 列名;
二、DML语句(Data Manipulation Language数据操纵语言)
(一)DML操作表中数据
SQL中的DML用于对表中的数据进行增删改操作。
1、插入数据
语法格式:
insert into 表名(字段名1,字段名2 ...)
values(字段值1,字段值2 ...);
方式1:插入全部字段,将所有字段名都写出来
insert into 表名(字段名,...) values(值,...);
方式2:插入全部字段,不写字段名
insert into 表名 values(字段值1,字段值2 ...);
方式3:插入指定字段的值
insert into 表名(字段名1,字段名4,字段名7...) values(值1,值4,值7...);
注意:
1)值与字段必须要对应,个数相同&数据类型相同
2)值的数据大小,必须在字段指定的长度范围内
3) varchar char date类型的值必须使用单引号包裹
4)如果要插入空值,可以忽略不写,或者插入null
5)如果插入指定字段的值,必须要上写列名
2、更改数据
语法格式1:不带条件的修改
update 表名 set 列名=值;
语法格式2:带条件的修改
update 表名 set 列名=值 [where 条件表达式 :字段名=值;
3、删除数据
语法格式1:删除所有数据
delete from 表名
语法格式2:指定条件删除数据
delete from 表名 [where 字段名=值];
如果要删除表中的所有数据有两种做法
delete from 表名
;不推荐。有多少条记录就执行多少次删除操作效率低truncate table 表名
:推荐。先删除整张表,然后再重新创建一张一模一样的表。效率高
(二)常见的约束
NOT NULL:非空,该字段的值必填
UNIQUE:唯一,该字段的值不可重复
DEFAULT:默认,该字段的值不用手动插入有默认值
CHECK:检查,mysql不支持
PRIMARY KEY:主键,该字段的值不可重复并且非空 unique+not null
FOREIGN KEY:外键,该字段的值引用了另外的表的字段
1、主键和唯一约束
1、区别:
①、一个表至多有一个主键,但可以有多个唯一
②、主键不允许为空,唯一可以为空
2、相同点
都具有唯一性
都支持组合键,但不推荐
外键:
1、用于限制两个表的关系,从表的字段值引用了主表的某字段值
2、外键列和主表的被引用列要求类型一致,意义一样,名称无要求
3、主表的被引用列要求是一个key(一般就是主键)
4、插入数据,先插入主表
删除数据,先删除从表
可以通过以下两种方式来删除主表的记录
注意:
支持类型 可以起约束名
列级约束 除了外键 不可以
表级约束 除了非空和默认 可以,但对主键无效
列级约束可以在一个字段上追加多个,中间用空格隔开,没有顺序要求
2、创建表时添加约束
create table 表名(
字段名 字段类型 not null,#非空
字段名 字段类型 primary key,#主键
字段名 字段类型 unique,#唯一
字段名 字段类型 default 值,#默认
constraint 约束名 foreign key(字段名) references 主表(被引用列)
)
3、修改表时添加或删除约束
1、非空
添加非空
alter table 表名 modify column 字段名 字段类型 not null;
删除非空
alter table 表名 modify column 字段名 字段类型
;
2、默认
添加默认
alter table 表名 modify column 字段名 字段类型 default 值;
删除默认
alter table 表名 modify column 字段名 字段类型 ;
3、主键
添加主键
alter table 表名 add【 constraint 约束名】 primary key(字段名);
删除主键
alter table 表名 drop primary key;
4、唯一
添加唯一
alter table 表名 add【 constraint 约束名】 unique(字段名);
删除唯一
alter table 表名 drop index 索引名;
5、外键
添加外键
alter table 表名 add【 constraint 约束名】 foreign key(字段名) references 主表(被引用列);
删除外键
alter table 表名 drop foreign key 约束名;
4、自增长列
特点:
1、不用手动插入值,可以自动提供序列值,默认从1开始,步长为1
auto_increment_increment
如果要更改起始值:手动插入值
如果要更改步长:更改系统变量
set auto_increment_increment=值;
2、一个表至多有一个自增长列
3、自增长列只能支持数值型
4、自增长列必须为一个key
一、创建表时设置自增长列
create table 表(
字段名 字段类型 约束 auto_increment
)
二、修改表时设置自增长列
alter table 表 modify column 字段名 字段类型 约束 auto_increment
三、删除自增长列
alter table 表 modify column 字段名 字段类型 约束
三、DQL语句(Data Query Language数据查询语言)
(一)基础查询
1、语法
select 查询列表
from 表名;
2、特点
1、查询列表可以是字段、常量、表达式、函数,也可以是多个
2、查询结果是一个虚拟表
3、示例
1、查询单个字段
select 字段名 from 表名;
2、查询多个字段
select 字段名,字段名 from 表名;
3、查询所有字段
select * from 表名
4、查询常量
select 常量值;
注意:字符型和日期型的常量值必须用单引号引起来,数值型不需要
5、查询函数
select 函数名(实参列表);
6、查询表达式
select 100/1234;
7、起别名
①as
②空格
8、去重
select distinct 字段名 from 表名;
9、+
作用:做加法运算
select 数值+数值;
直接运算
select 字符+数值;
先试图将字符转换成数值,如果转换成功,则继续运算;否则转换成0,再做运算
select null+值;
结果都为null
10、【补充】concat函数
功能:拼接字符
select concat(字符1,字符2,字符3,...);
11、【补充】ifnull函数
功能:判断某字段或表达式是否为null,如果为null 返回指定的值,否则返回原本的值
select ifnull(commission_pct,0) from employees;
12、【补充】isnull函数
功能:判断某字段或表达式是否为null,如果是,则返回1,否则返回0
(二)条件查询
1、语法
select 查询列表
from 表名
where 筛选条件
2、筛选条件的分类
①、简单条件运算符
> < = <> != >= <= <=>安全等于
②、逻辑运算符
&& and
|| or
! not
③、模糊查询
like:一般搭配通配符使用,可以判断字符型或数值型
通配符:%任意多个字符,_任意单个字符
between and
in
is null /is not null:用于判断null值
(三)排序查询
1、语法
select 查询列表
from 表
where 筛选条件
order by 排序列表 【asc/desc】
2、特点
①、asc :升序,如果不写默认升序
desc:降序
②、排序列表 支持 单个字段、多个字段、函数、表达式、别名
③、order by的位置一般放在查询语句的最后(除limit语句之外)
(四)常见函数
1、概述
好处:提高重用性和隐藏实现细节
调用:select 函数名(实参列表);
2、单行函数
①、字符函数
concat:连接
substr:截取子串
upper:变大写
lower:变小写
replace:替换
length:获取字节长度
trim:去前后空格
lpad:左填充
rpad:右填充
instr:获取子串第一次出现的索引
②、数学函数
ceil:向上取整
round:四舍五入
mod:取模
floor:向下取整
truncate:截断
rand:获取随机数,返回0-1之间的小数
③、日期函数
now:返回当前日期+时间
year:返回年
month:返回月
day:返回日
date_format:将日期转换成字符
curdate:返回当前日期
str_to_date:将字符转换成日期
curtime:返回当前时间
hour:小时
minute:分钟
second:秒
datediff:返回两个日期相差的天数
monthname:以英文形式返回月
④、其他函数
version 当前数据库服务器的版本
database 当前打开的数据库
user当前用户
password('字符'):返回该字符的密码形式
md5('字符'):返回该字符的md5加密形式
⑤、流程控制函数
⑴if(条件表达式,表达式1,表达式2)
:如果条件表达式成立,返回表达式1,否则返回表达式2
⑵case情况1
case 变量或表达式或字段
when 常量1 then 值1
when 常量2 then 值2
...
else 值n
end
⑶case情况2
case
when 条件1 then 值1
when 条件2 then 值2
...
else 值n
end
3、分组函数
①、分类
max 最大值
min 最小值
sum 和
avg 平均值
count 计算个数
②、特点
⑴语法
select max(字段) from 表名;
⑵支持的类型
sum和avg一般用于处理数值型
max、min、count可以处理任何数据类型
⑶以上分组函数都忽略null
⑷都可以搭配distinct使用,实现去重的统计
select sum(distinct 字段) from 表;
⑸count函数
count(字段):统计该字段非空值的个数
count(*):统计结果集的行数
count(1):统计结果集的行数
效率上:
MyISAM存储引擎,count()最高
InnoDB存储引擎,count()和count(1)效率>count(字段)
⑹ 和分组函数一同查询的字段,要求是group by后出现的字段
(五)分组查询
一、语法
select 分组函数,分组后的字段
from 表
【where 筛选条件】
group by 分组的字段
【having 分组后的筛选】
【order by 排序列表】
二、特点
使用关键字 | 筛选的表 | 位置 | 放置函数? | |
---|---|---|---|---|
分组前筛选 | where | 原始表 | group by的前面 | 不可以放分组函数 |
分组后筛选 | having | 分组后的结果 | group by的前面 | 可以放分组函数 |
1)group by的字段必须出现在前面 select的位置
2)前面select的位置,除了 group by的字段、聚合函数,不能出现其他字段
(六)分页查询
1、应用场景
当要查询的条目数太多,一页显示不全
2、语法
select 查询列表
from 表
limit 【offset,】size;
注意:
offset代表的是起始的条目索引,默认从0
size代表的是显示的条目数
公式:
假如要显示的页数为page,每一页条目数为size
select 查询列表
from 表
limit (page-1)*size,size;