- 数据库相关SQL
- 查询所有数据库 show databases;
- 创建数据库 create database db1 character set utf8/gbk;
- 查看数据库详情 show create database db1;
- 删除数据库 drop database db1;
- 使用数据库 use db1;
- 表相关SQL
- 创建表 create table t1(name varchar(10),age int) engine=innodb/myisam charset=utf8/gbk;
- 查询所有表 show tables;
- 查询表详情 show create table t1;
- 查询表字段 desc t1;
- 删除表 drop table t1;
- 修改表名 rename table t1 to t2;
- 修改表引擎和字符集 alter table t1 engine=innodb/myisam charset=utf8/gbk;
- 添加表字段 alter table t1 add 字段名 类型 first/after xxx;
- 删除表字段 alter table t1 drop 字段名;
- 修改字段名和类型 alter table t1 change 原名 新名 新类型
- 修改字段类型和位置 alter table t1 modify 字段名 新类型 first/after xxx;
- 数据相关SQL
- 插入数据 insert into 表名 values(值1,值2),(值1,值2),(值1,值2);
insert into 表名 (字段1名,字段2名) values(值1,值2),(值1,值2),(值1,值2); - 查询数据 select 字段信息 from 表名 where 条件;
- 用于返回唯一不同的值 select distinct 字段信息 from 表名;
- 修改数据 update 表名 set 字段名=值 where 条件;
- 删除数据 delete from 表名 where 条件;
- 两个库查询数据合并 select * from [数据库1].dbo.[表1] where 字段=’?’ union all
select * from [数据库2].dbo.[表2] where 字段=’?’ - 两个库关联查询 select * from 库名.dbo.表名 as t inner join 库名.dbo.表名 as t1 on t.id=t1.id
修改数据密码:
格式:mysql> set password for 用户名@localhost = password(‘新密码’);
查找当前用户:set user();
复制表:create table 新表 as select * from 旧表;
复制表的数据:insert into 表名 字段名 select 字段名 from 表名 where 条件
查看建表语句 select create table t1
4、主键约束 primary key
- 什么是主键: 表示数据唯一性的字段称为主键
- 什么是约束: 是创建表时给表字段添加的限制条件
- 主键约束: 让该字段的数据唯一且非空(不能重复,不能null)
- 格式: create table t1(id int primary key,name varchar(10));
insert into t1 values(1,‘AAA’); //成功!
insert into t1 values(1,‘BBB’);//报错 不能重复
insert into t1 values(null,‘CCC’); //报错 不能为null
###主键约束+自增 - 自增数值只增不减,从历史最大值基础上+1
- 格式: create table t2(id int primary key auto_increment,name varchar(10));
insert into t2 values(null,‘aaa’); //1
insert into t2 values(null,‘bbb’); //2
insert into t2 values(3,‘ccc’); //3
insert into t2 values(10,‘ddd’); //10
insert into t2 values(null,‘eee’); //11
delete from t2 where id>=10;
insert into t2 values(null,‘fff’); //12 - 注释格式:
create table t3
(id int primary key auto_increment comment ‘这是主键’,name varchar(10) comment ‘这是名字’);
###`的作用 - 用于修饰表名和字段名,可以省略
create tablet4
(id
int,name
varchar(10));
###冗余 - 由于表设计不够合理导致的大量重复数据称为数据冗余
- 创建表
create table item(id int primary key auto_increment,name varchar(10),price int,num int,category_id int);
create table category(id int primary key auto_increment,name varchar(10),parent_id int); - 插入数据:
insert into category values(null,‘家电’,null),(null,‘电视机’,1),(null,‘办公用品’,null),(null,‘打印机’,3);
insert into item values(null,‘小米电视’,1888,200,2),(null,‘惠普打印机’,1500,100,4);
###事务
- 什么是事务:事务是数据库中执行同一业务多条SQL语句的工作单元,可以保证多条SQL全部执行成功或全部执行失败.
- 事务相关指令:
- 开启事务 begin;
- 提交事务 commit;
- 回滚 rollback;
- 验证转账流程:
create table user(id int primary key auto_increment,name varchar(10),money int,status varchar(5));
insert into user values(null,‘超人’,50,‘冻结’),(null,‘蝙蝠侠’,5000,‘正常’),(null,‘灭霸’,20,‘正常’); - 转账的SQL:
update user set money=money-2000 where id=2 and status=‘正常’;
update user set money=money+2000 where id=1 and status=‘正常’; - 有事务保护的情况下 回滚流程:
- 开启事务
begin; - 蝙蝠侠-2000
update user set money=money-2000 where id=2 and status=‘正常’; - 此时在当前终端查询数据时 数据已经改变(因为查询到的是内存中的改动), 开启另外一个终端查询数据发现数据是没有改变的(因为新的终端查询到的是磁盘的数据)
- 超人+2000
update user set money=money+2000 where id=1 and status=‘正常’; - 此时从执行结果中发现一条成功一条失败,应该执行回滚操作
rollback;
- 有事务保护的情况下 提交流程:
- 开启事务
begin; - 蝙蝠侠-2000
update user set money=money-2000 where id=2 and status=‘正常’; - 此时仍然是在内存中改动 磁盘数据没有发生改变
- 灭霸+2000
update user set money=money+2000 where id=3 and status=‘正常’; - 此时两次改动都是在内存中改完,发现两次全部成功,所以执行提交
commit;
- 保存回滚点:
begin;
update user set money=1 where id=2;
savepoint s1;
update user set money=2 where id=2;
savepoint s2;
update user set money=3 where id=2;
rollback to s2; - 事务的ACID特性
保证事务正确执行的四大基本要素
- Atomicity原子性: 最小不可拆分 保证全部执行成功或全部执行失败
- Consistency一致性: 从一个一致状态到另一个一致状态
- Isolation隔离性: 多个事务之间互相隔离互不影响
- Durability持久性: 当事务提交后数据保存到磁盘中持久生效
###SQL分类
####DDL Data Definition Language数据定义语言
- truncate table 表名;
删除表并创建新表 让自增数值清零 - 包括: create drop alter truncate
- 不支持事务
####DML Data Manipulation Language数据操作语言 - 包括: insert update delete select
- 支持事务
####DQL Data Query Language 数据查询语言 - 只包括: select
####TCL Transaction Control Language 事务控制语言 - 包括: begin,commit,rollback,savepoint xxx, rollback to xxx;
####DCL Data Control Language 数据控制语言 - 负责分配用户权限相关的SQL
###数据类型
- 整数: int(m) 和 bigint(m) m代表的是显示长度,需要结合zerofill使用
create table t_int(id int,age int(10) zerofill);
insert into t_int values(1,18);
select * from t_int; - 浮点数: double(m,d) m代表的是总长度 d代表小数长度,超高精度的浮点数decimal(m,d)
25.234 m=5 d=3 - 字符串:
- char(m) 固定长度 最大长度255 好处执行效率略高
- varchar(m) 可变长度 好处节省空间 最大长度65535(但是超过255建议使用text)
- text(m) 可变长度 最大长度65535
- 日期:
- date: 保存年月日
- time: 保存时分秒
- datetime: 保存年月日时分秒,默认值为null,最大9999-12-31
- timestamp(时间戳):保存年月日时分秒,默认值为当前系统时间,最大值2038-1-19
create table t_date(t1 date,t2 time,t3 datetime,t4 timestamp);
insert into t_date values(‘2019-6-20’,null,null,null);
insert into t_date values(null,‘16:46:30’,‘2019-06-20 16:30:30’,null);
###导入*.sql文件 - windows系统 把文件放在C或D盘的根目录
source d:/tables.sql; - linux系统 把文件放在桌面
source /home/soft01/桌面/tables.sql
如果是乱码 执行 set names gbk;
- 主键约束: 唯一且非空 primary key
- 主键+自增 auto_increment
只增不减 从历史最大值+1 - 注释 comment
- ` 用于修饰表名和字段名 可以省略
- 冗余: 由于表设计不够合理导致的大量重复数据 ,通过合理拆分表解决
- 事务: 数据库中执行同一业务多条SQL语句的工作单元,可以保证多条SQL全部执行成功或全部执行失败
- 相关指令: 开启事务begin 提交事务 commit 回滚 rollback
- 保存回滚点 savepoint xxx; rollback to xxx;
- 四大特性
- 原子性: 最小不可拆分 保证全部成功或全部失败
- 一致性: 从一个一致状态到另一个一致状态
- 隔离性: 多个事务互相隔离 互不影响
- 持久性: 事务提交后 数据保存到磁盘中持久生效
- 数据类型
- 整数: int 和bigint(m) m代表显示长度 结合zerofill使用
- 浮点数: double(m,d) m代表总长度 d小数长度 超高精度浮点数 decimal(m,d)
- 字符串: char 固定长度 最大255 执行效率高 varchar(m) 可变长度 最大65535超过255建议使用text text 可变长度 最大65535
- 日期: date 年月日 time 时分秒 datetime 最大9999-12-31 默认null timestamp 最大2038-1-19 默认当前系统时间
- 导入sql文件
source 路径;
9.函数
- 求和 sum()
- 计数 count()
- 平均值 avg()
- 最大值 max()
- 最小值 min()
- 返回当前系统的日期和时间 now()
- 返回文本字段中值的长度 length()
- 用于把数值字段舍入为指定的小数位数 round()
- 可以实现在分组统计数据基础上再进行相同的统计(SUM,AVG,COUNT…)with rollup 汇总
select name,sum(singin) singin_count from user group bu name with rollup; - coalesce 设置一个代替null的名称
select coalesce(name, ‘总数’), SUM(singin) as singin_count from employee_tbl group by name WITH ROLLUP;