SQL语句大全

SQL语句

-- 创建thenumb数据库字符集为utf8，先判断有无此数据库
CREATE DATABASE IF NOT EXISTS thenumb CHARACTER SET utf8; 
SHOW DATABASES; -- 查看所有数据库
SHOW CREATE DATABASE thenumb; -- 查看thenumb中的字符集
ALTER DATABASE thenumb CHARACTER SET UTF8; -- 修改thenumb的字符集
DROP DATABASE DB1; -- 删除数据库
DROP DATABASE IF EXISTS db_1; -- 判断db_1是否存在再删除
SELECT DATABASE(); -- 查看当前的库名
USE thenumb; -- 使用thenumb数据库

-- 数据库中的表
-- 创建表
CREATE TABLE thenumb(
NAME VARCHAR(25),
sid INT,
age INT,
height DOUBLE(5,2), -- 指定五位数，小数点后两位。175.55
生日 DATE,  -- 2019-11-22
设置时间 DATETIME, -- 2019-11-22 13:52:22
当前时间 TIMESTAMP-- 2019-11-22 13:52:22
)

-- 数据类型
tinyInt 微整型：很小的整数(占 8 位二进制)
smallint 小整型：小的整数(占 16 位二进制)
mediumint 中整型：中等长度的整数(占 24 位二进制)
int(integer) 整型：整数类型(占 32 位二进制)
整数
float 单精度浮点数，占 4 个字节
小数
double 双精度浮点数，占 8 个字节
time 表示时间类型
日期 date 表示日期类型
datetime 同时可以表示日期和时间类型
char(m) 固定长度的字符串， 无论使用几个字符都占满全部， M 为 0~255 之间的整
字符串 数
varchar(m) 可变长度的字符串， 使用几个字符就占用几个， M 为 0~65535 之间的整数
tinyblob
Big Large Object
允许长度 0~255 字节
blob 允许长度 0~65535 字节
mediumblob 允许长度 0~167772150 字节
大二进制
longblob 允许长度 0~4294967295 字节
大文本 tinytext 允许长度 0~255 字节24 / 35
text 允许长度 0~65535 字节
mediumtext 允许长度 0~167772150 字节
longtext 允许长度 0~4294967295 字节




CREATE TABLE thenumb3 LIKE thenumb;-- 复制表 复制一张相同结构的空表thenumb为被复制的表

SHOW TABLES; -- 查看当前数据库中的所有表
DESC thenumb; -- 查看thenumb表结构

ALTER TABLE thenumb1 RENAME TO thenumb2; -- 修改表名

ALTER TABLE thenumb2 CHARACTER SET utf8; -- 修改标的字符集
ALTER TABLE thenumb2 ADD address VARCHAR(20); -- 添加一列address VARCHAR(20)
ALTER TABLE thenumb2 CHANGE 旧列名 新列名 新数据类型; -- 更换新列名及新数据类
ALTER TABLE thenumb2 MODIFY 生日 INT; -- 修改指定列名的数据类型

ALTER TABLE thenumb2 DROP 设置时间; -- 删除指定列

DROP TABLE thenumb3; -- 删除表
DROP TABLE IF EXISTS thenumb3; -- 删除表先判断存在

-- 添加数据  数据需要一一对应，如添加多行，可用逗号隔开。
INSERT INTO thenumb(NAME,sid,age,height,生日,设置时间,当前时间)VALUES('zhangsan',001,15,175.25,'1994-11-22','2019-11-22 13:52:22',NULL),
('lisi',001,15,175.25,'1994-11-22','2019-11-22 13:52:22',NULL),
('wangwu',001,15,175.25,'1994-11-22','2019-11-22 13:52:22',NULL),
('007',001,15,175.25,'1994-11-22','2019-11-22 13:52:22',NULL);

-- 如表名后不跟列名，默认依次添加
INSERT INTO thenumb VALUES('zhangsan',15,15,158,'1994-11-22','2019-11-22 13:52:22',NULL); 

DELETE FROM thenumb WHERE NAME=007; -- 删除name = 007的那一行

 DELETE FROM thenumb2; -- 不加限制条件默认逐条删除，删除很多次
 TRUNCATE TABLE thenumb2; -- 如果非要删除，推荐这一条，效率高，先删除表，在建一张同样的结构表
 
 UPDATE thenumb SET NAME='zhaoyi' WHERE sid=1; -- 将sid= 1，的行中name改为zhaoyi
 
 
 
 1.语法
 select 字段列表
 from 表名列表
 where 条件列表
 group by 分组字段
 having 分组之后的条件
 order by 排序
 
 where 和 having区别 
 where 在分组之前限定 如不满足 则不参与分组，having在分组后限定如不满足，则不会被查询出来
 where 后跟聚合函数，having后跟判定聚合函数
 
 
 <>(可大于可小于，相当于不等于),<,>,<=,>=,!=,
 between  and   
 
 
 
 查询并且计算
 select * from 表名;  -- 查询数据库
 select 列名1，列名2...列名1+列名2 from 表名；-- 查询列1，2，。。。，和列1+2的结果
 select name ，math，English，math+ifnull（English，0） from 表名；--假如英语成绩有一人为null时候。null参与运算结果都为null。需要用0替换 ifnull
 select 列名1，列名2...列名1+列名2 as 别名 from 表名；-- 给列1+列2加个名称，别名中有空格整体需要加引号
 select distinct 列名 from 表名;  -- 查询某列并去除重复 
 
 查询且给予条件判断
 select * from 表名 where 列名1>20;查询列名1值大于20的数据
 select * from 表名 where 列名1>20 and 列名1<30;查询列名1值大于20小于30的数据
 select * from 表名 where 列名1 between 20 and 30;列名1值在20~30之间的，包括20，30.注意between后边跟小值，and后大值。
 
 select * from 表名 where 列名1=值1 or 列名1=值2 or 列名1=值3; 列名1中值为值123的人
 select * from 表名 where 列名1 in（值1，值2，值3）；同上，简写
 
 select * from 表名 where 列名 is not null； 查询列名值不为null
 
 查询模糊查询
 _占一位，%占0或多位
 select * from 表名 where 名字 like '王%';  -- 姓王的人
 select * from 表名 where 名字 like '%王%';-- 带王字的人
 select * from 表名 where 名字 like '王__';-- 姓王，且王后边为两个字的人
 select * from 表名 where 名字 like '___';-- 三个字名字的人
 
 排序查询
 select * from 表名 order by 列名 asc； -- 默认按列名升序排列
 select * from 表名 order by 列名 desc； -- 降序排列
 select * from 表名 order by 列名1 desc，列名2 desc；-- 当列名1降序排序出现相等时，按列名2降序排序
 
 聚合函数 计算时排除null值
 count：计算个数 一般选着非空列   主键
 max：
 min：
 sum：
 avg：
 
 select count（列名） from 表名；-- 统计个数
 select count（ifnull(列名,0)） from 表名；-- 如果列名中值有null值 使用该语句计算个数
 
 
 分组
 语法：
 select 分组字段 ，聚合函数 from 表名 group by --分组字段；
 
 select sex ，avg（math） from 表名 group by sex；-- 按照性别分组分别查询男女平均分
 
 select sex ，avg（math），count（id） from 表名 group by sex； --  按照性别分组分别查询男女平均分，人数
 
  select sex ，avg（math），count（id） from 表名 where math>70 group by sex；-- 按照性别分组分别查询男女平均分，人数  要求低于70分不参与分组
  
  select sex ，avg（math），count（id） from 表名 where math>70 group by sex having count(id)>2；-- 按照性别分组分别查询男女平均分，人数  要求大于70分参与分组,且人数大于2显示
                                                                               
 
 分页
 select * from 表名 limit 0，3；-- 第一页
 select * from 表名 limit 3，3；-- 第二页
 select * from 表名 limit 6，3；-- 第三页
 select * from 表名 limit 当前页第一条的码，从此条开始显示的个数；
 公式  开始的索引 = （当前页码-1）*显示条数
 limit 是一个mysql  方言
 
 
 约束
 对表中数据进行限定，保证数据的有效性完整性安全性
 在定义表结构时  对表结构定义一定的规则，从而保证数据完整型有效性安全性
 
 约束分类：
主键 primary key
非空 not null
唯一  unique
外键


非空约束 not null
-- 建表时添加非空约束
create table stu（
ID int，
name varchar（20） not null   -- null非空
）
-- 删除非空约束
alter table 表名 modify 字段 字段类型；
-- 后来添加非空约束
alter table 表名 modify 字段 字段类型 not null；
 
 
 唯一约束 unique 限制某列数据不可重复，无法限制null（可以出现多个null）
 -- 创建表示添加唯一约束
 create table stu（
 id int，
 phone_number varchar(20) unique  -- 添加唯一约束
 )
-- 注意 mysql中唯一约束限定的值可以有多个的null
 --删除非空约束
 alter table 表名 drop index 列名；
 -- 后来添加非空约束
 alter table 表名 modify 字段 字段类型 unique；

 主键约束 primary key 
 特点：非空且唯一 一张表只能有一列为主键 
 作用：表中记录唯一标示
 
 -- 春关键表示添加主键约束
 create table 表名（
 id int primary key， -- 给id主键约束
 name varchar（20）
 ） 
 -- 删除主键约束 
 alter table 表名 drop primary key；
 -- 后来添加主键约束
alter table 表名 modify 设置为主键的列名 类型 primary key；

-- 主键约束 自动增长
-- 创建时添加
create table 表名（
 id int primary key auto_increment， -- 给id主键约束自动增长
 name varchar（20）
 ） 
 -- 删除主键约束自动增长
 alter table 表名 drop primary key auto_increment；
 -- 后来添加主键约束自动增长
 alter table 表名 modify 设置为主键的列名 类型 primary key auto_increment2；
 
 
 
 --外键约束 foreign key
 让多张表之间取得关键，保证关联表的数据正确性完整性
 可存在多个外键
 
  创建表时添加外键
  create table 表名（
  ...
  外键字段 外键类型
  constraint 外键名称仅仅只是标示 foreign key（外键字段） references 主表名称（主表列名称（主键列或者唯一约束列））
  ）
  
 删除外键
 alter table 表名 drop foreign key 外键名称；
 
 后添加外键
alter table  表名  add constraint 外键名称仅仅只是标示 foreign key（外键字段） references 主表名称（主表列名称（主键列或者唯一约束列））；



--级联操作
级联更新
on update cascade



级联删除
on delete cascade

-- 数据库的设计
1.多表之间的关系
	A一对一
	
	
	
	B一对多（多对一）
	通过外键实现
	
	
	
	C多对多
	借助第三张中间表实现，中间表只至少包含两个字段。关联两张表的主键。




-- 给字段家默认值
default'男'；


-- 联合主键
一个主键包含多个列；   
两个字段加起来非空且唯一；

-- char 与varchar
char char（） 括号中指定长度为多少位，就固定占几位。无论你输入数据是多少位。
varchar varchar（） 括号中指定为最大长度 。


-- 数据库备份恢复

1.命令
备份：mysqldump -uroot -proot 数据库名称 > 路径
mysqldump -uroot -proot db_1 > D:\A.sql;

恢复： source 路径
create database db_1;
use db_1;
source D:\A.sql;