-- ############################## 数据库操作:#############################
-- 登录到mysql数据库
mysql –u 用户名 -p密码
-- 创建数据库
create database 数据库名;
-- 查看,创建数据库的指令
show create databse 数据库名;
-- 显示数据库
show databases;
-- 删除数据库:
drop database 数据库名;
-- 使用某个数据库
use 数据库名;
-- 查看隔离级别
select @@tx_isolation;
-- 查看警告
show warnings;
-- #################### 备份数据库: ##################
-- 备份数据库 在doc控制台下直接执行
mysqldump –u 用户名 –p密码 数据名 > 存放路径 文件名.sql
--恢复数据库:
1.创建一个数据库 mydb2 ,但是这个数据库目前是空.
2.use 数据名
3.在mysql 控制台下 使用 source 备份文件路径
source 文件路径.sql
-- ############################## 表操作: #############################
-- 创建表
create table if not exists users9(
-- 键操作
id1 int primary key comment '学号', -- comment注释
id2 primary key auto_increment, -- auto_increment自增
id3 int unique, -- 唯一 (但是允许有多个NULL)
-- 小数类数据
_float float(5,2) zerofill, -- 共5位精度,其中2位小数
money decimal(10,2), -- 定点,无精度损失,超出的小数部分遵循四舍五入,但是不可进位(整数部分多一位),存放整数或小数
-- 日期类数据
birthday date, -- (年-月-日) 插入数据时加'...'单引号
hiredate1 datetime, -- (年-月-日 : 时:分:秒)
hiredate2 timestamp, -- update时,自动更新。格式同上
-- 如果你的表中有两个timestamp字段,只要你更新任何非timestamp字段的值,则第一个timestamp列会更新成当前时间,而第二人timestamp列的值不变。
year1 year, -- 默认year(4) 可填二位数:插入70为1970,69为2069。1970~2069
year2 year(2), -- 插入70为70
-- int类数据
int_1 tinyint unsigned, -- 1字节 -128~127 unsigned:0-255
int_2 smallint, -- 2字节 -2^15 ~ 2^15 - 1 unsigned:2^16 - 1
int_3 int, -- 4字节
int_4 bigint,
-- 其他类数据
name varchar(64) default NULL,
gender enum('男','女','保密') not null, -- 1,2,3...
_bit bit(64), -- 最大64,默认1 二进制存放,查询select _bit+0;
content text, -- 文本字符串
bool boolean -- 波尔类型
)character set utf8, collate utf8_general_ci, comment='表的注释';
-- 复制表的结构
create table 新表 like 原表;
-- 复制表结构和数据(无约束)
create table 新表
as
select 源表字段1 新表字段1, 源表字段2 新表字段2, ...
from 源表;
-- 蠕虫复制(批量复制数据)
insert into 新表 (新表字段) select 源表字段 from 原表;
-- 查看所有表
show tables;
-- 查看以x结尾的表
show tables like '%x';
-- 查看表结构
desc(describe) 表名;
show columns from student;
show create table test2;
-- 修改表名
rename table 原表名 to 新表名;
-- 修改表的字符集
alter table 表名 character set(=) 字符集名;
-- 修改表的注释
alter table test1 comment '修改后的表的注释';
-- 修改字段的注释
alter table test1 modify column id int comment '修改后的字段注释';
-- 查看表注释
show create table test1;
-- 查看字段注释
show full columns from test;
-- 删除表
drop table 表名;
truncate table 表名; -- (不可回滚,但速度快)
-- ############################## 事务: #############################
-- 开启事务
start transaction;
-- 回滚事务(回到开启事务前的状态)
rollback;
-- 提交事务(提交后不可回滚)
commit;
------------------------
-- 方法二:
-- (1)mysql 控制台是默认自动提交事务(dml)
-- (2)如果我们要在控制台使用事务,应该这样
set autocommit=false;
savepoint 保存点名称;
-- //经过操作后...(例如删除)... 注意回滚之前不要设置立即提交事务
rollback to 保存点名称;-- 回滚后,数据还在 但是使用truncate删除表后,没有该数据
commit; -- 提交事务
-- ############################## 字段操作: #############################
-- 添加新的列
alter table 表名 add 字段名 数据类型;
-- 添加tinyint宽度为2,zero填充格式。如:01,02
alter table 表名 add 字段名 tinyint(2) zerofill;
-- 添加新的列到第一个位置
alter table 表名 add 字段名 数据类型 first;
-- 删除某列
alter table 表名 drop 字段名;
-- 修改列数据类型
alter table 表名 modify 字段名 新的数据类型;
-- 修改列数据类型(且放到id之后)
alter table 表名 modify 字段名 新的数据类型 (after id);
-- 修改字段名和数据类型
alter table 表名 change /*column)*/ 原名 改后名 数据类型;
-- 建立索引
alter table 表名 add index 索引别名 (字段名 /* asc desc 降序升序*/);
-- 删除索引
drop index 索引别名 on 表名;
-- ############################## 增删改: #############################
-- 插入数据
insert into 表名(字段名) values(值);
-- 蠕虫复制(作用2.可以迅速的让表中的数据膨胀到一定的数量级: 测试表的压力以及效率)
insert into 表名 select * from 表名;
-- 修改(更新)数据 可以使用limit
update 表名 set 字段名 = 值;
-- 删除行 可以使用limit
delete from 表名 where 条件; -- (默认:不可回滚)
-- 复制表数据
create table 新表表名(字段1 字段1类型,字段2 字段2类型);
insert into 新表表名 (select 字段1, 字段2 from 原表表名);
-- 主键冲突(重复)时修改主键对应数据(内部实现:若发生主键冲突,1删除该行数据,2插入新数据)
insert into 表名 values('重复的主键值', '新数据') on duplicate key update 字段名 = '新数据';
-- ################################ 查询操作:####################################
-- distinct 剔除重复数据
select distinct * from 表名 where 条件;
-- 模糊查询 '_'单字 '%'多字
select * from 表名 where 字段名 like 'a%'; -- 查询以a开头的
-- NULL值使用指定值替换显示
select ifnull(字段名, 0) from 表名;
-- 查询结果使用别名显示
select english as '英语', math as 数学 from 表名;
-- 查询时另起别名进行查询
select 别名.* from 表名 as 别名;
select * from 表名 as 别名 where 别名.字段名 = 数据;
select * from 表名1 as 别名1 inner join 表名2 as 别名2 on 别名1.字段名 = 别名2.字段名;
-- in多匹配查询
select * from 表名 where 字段名 in (值1, 值2, ...);
-- 计数count(当*改为字段名时,NULL值不参与同类函数的计算)
select count(*) from 表名;
-- 分页查询limit: 从m行查询n条数据(包含m行; n可以超出表长; 一个参数表示从0下标开始查n条数据)
select * from student limit m-1, n-1;
-- 联合查询, 取并集, 默认去除重复行; [all]: 不去重
select * from 表名
union [all]
select * from 表名;
-- 显示的字段名取决于字段名1、2 且要求字段名1、2和字段名3、4类型完全一致
select 字段名1, 字段名2 from 表名1
union
select 字段名3, 字段名4 from 表名2;
-- 联合查询中使用order by: 加括号且使用limit限定各个查询数量
(select 字段名1, 字段名2 from 表名1 order by 字段名 desc limit 999)-- 降序
union
(select 字段名3, 字段名4 from 表名2 order by 字段名 limit 999);-- 升序
-- ################################ 连接查询:####################################
-- 交叉连接(原则:条件数量至少是表的个数-1)
select sno from 表1 ,/*cross join*/ 表2 where 条件;
-- #外连接: 显示数据左表一定在左边,右表一定在右边
-- 左连接(左表有的,而右表没的置为NULL) 查询结果一定 >= 左表数量
select 字段 from 表1
left join 表2
on 表1.字段名1 = 表2.字段名1;
-- 右连接(与上面相反)
select 字段 from 表1
right [outer] join 表2
on 表1.字段名1 = 表2.字段名1;
-- 内连接(左右连接的交集)
select 字段 from 表1
inner join 表2
on 表1.字段名1 = 表2.字段名1;
-- 自动匹配同名字段并合并显示(也就是自然连接↓) 所有连接都可以使用
select 字段 from 表1 inner join 表2 using(sno);
-- 内外自然连接(了解):自动匹配同名字段并合并显示
select 字段 from 表1
natural left join 表2;
-- ################################ 视图操作:####################################
-- 创建视图(视图:表的映射)当所依据表更新数据,视图也跟着更新
create view 视图名 as
select ...;
-- 修改视图
alter view 视图名
as
select ...;
-- 删除视图
drop view 视图名;
-- 单表插入数据(如果有基表的非空或者无默认值字段,必须在此视图中含有)
insert into 视图名 values(...);
-- 单表删除数据
delete from 视图名 where 条件;
-- 多表视图更新数据
update 视图名 set 条件;
-- 创建视图(限制字段更新)
create view 视图名
as
select ... where age > 30 with check option;-- 视图中不能出现小于30岁的 此时更新视图数据受到限制
-- 创建视图指定算法为临时表
create algorithm = temptable view 视图名
as
select ...;
-- ################################ 约束:####################################
-- mysql中key 、primary key 、unique key 与index区别
https://blog.csdn.net/nanamasuda/article/details/52543177
-- ############## 主键:################
-- 创建单一主键
create table test9(
id int primary key,
name char
);
-- 创建复合主键
create table test8(
id int,
name char,
primary key(id, name)
);
-- 添加主键 前提:无重复数据
alter table 表名 add primary key(字段名,字段名);
alter table 表名 modify 字段名 列数据类型 primary key; -- 不建议
-- 删除主键
alter table 表名 drop primary key; -- 删除所有主键
-- ############## 唯一键:################
-- 创建唯一键
create table unique1(
id int unique key,
name char
);
create table unique2(
id int,
name char,
unique(id, name)
);
-- 添加唯一键
alter table 表名 add unique /*(key)*/(字段名,字段名);
-- 删除唯一键
alter table unique1 drop index id;
-- ####### 自增长(一张表最多只能有一个):#######
-- 创建自增长
create table my_auto(id int /*auto_increment*/ primary key auto_increment, num int);
-- 添加自增长
alter table my_auto2 add primary key auto_increment(id);
-- 插入数据
insert into my_auto values(NULL,11); -- 1 11
insert into my_auto values(default,12); -- 2 12
insert into my_auto values(4,14); -- 4 14
insert into my_auto (num) values(15); -- 5 15
insert into my_auto values(3,13); -- 3 13
-- 修改下次自增长值 修改值必须大于当前值
alter table my_auto auto_increment = 8;
-- 删除自增长
alter table my_auto modify id int;
alter table my_auto drop primary key; -- 两步(需要维持主键依然存在)
-- 查看自增长对应的变量:
show variables like 'auto_increment%';
-- 修改自增步长 修改不仅此张表(修改是会话级)
set auto_increment_increment = 5;
-- 修改自增初始值
set auto_increment_increment = 100;
-- 清空表并初始化自增长值
truncate 表名;
-- ############## 外键:################
-- 创建指定模式外键:删除置空,更新级联(默认严格模式)
-- 删除置空的前提条件: 外键字段允许为空(如果不满足条件,外键无法创建)
create table student(
id int primary key comment '学生id'
);
create table class1(
class_id int primary key comment '班级id',
class_name varchar(20) not null,
id int comment '学生id',
foreign key(id) references student(id) -- 方式1 (默认别名为:表名_ibfk_1)
constraint 别名 foreign key(id) references student(id/*primary key/unique*/)on delete set null on update cascade -- 方式2
);
-- 添加外键:(子表的增改受父表约束,父表的删改受子表约束)
alter table 子表名 add [constraint 别名] foreign key(子表字段名) references 父表名(父表字段名);
-- 删除外键: (查看别名:show create table 表名; 默认:表名_ibfk_1)
alter table 表名 drop foreign key 别名;
-- ################################# 函数:####################################
-- ############## 日期和时间函数:################
-- 显示日期
select day(now());
select current_date();
-- 显示时间
select time(now());
select current_time();
-- 显示时间戳
select now();
select current_timestamp();
-- 显示秒...
select second(now());
select second(current_time());
-- 显示365天后的日期
select date_add(now(), interval + 365 day);
select date_sub(now(), interval - 365 day);
-- 查询两个小时内的消息 (该日期 + 2小时 >= 现在日期)
select * from 表名 where date_add(字段名(时间类型), interval 2 hour) >= now();
-- 计算日期相差几天 datediff(date1, date2)
-- 应用:查询入职大于100天的职工
select * from 表名 where datediff(now(), 字段名) > 100;
-- 计算时间相差多少,时:分:秒 timediff(date1, date2)
-- ############## 字符串函数:################
-- 查询某列字符集
select charset(字段名) from 表名;
-- 连接字符串
select concat('ab', 'c', 'd'...); -- abcd
select concat(字段名, 'abc') from 表名;
-- 返回子串在主串中出现的位置,没有返回0
select instr('abcdef', 'a'); -- 1
-- 转换为大写(字符串的位置都可用字符串类型的字段名代替)
select ucase('abc');
-- 转换为小写
select lcase('ABC');
-- 截头
select left('abcde', 3); -- abc
-- 截尾
select left('abcde', 3); -- cde
-- 截区间
select substring('abcd', 2, 2); -- bc
-- 返回字符串长度
select length('abc'); -- 3
-- 替换
select replace('abca', 'a', 'x'); -- xbcx
-- 比较大小 >返回1, <返回-1, =返回0
select strcmp('b', 'a'); -- 1
-- 删除前端空格
ltrim(string)
-- 删除后端空格
rtrim(string)
-- 删除字符串首尾中多余的空格
trim(string)
-- ############## 数学函数:################
-- 转换二进制
select bin(255); -- 11111111
-- 转换十六进制
select hex(255); -- FF
-- 向上取整
select ceiling(2.0000001); -- 3
-- 向下取整
select floor(2.999999); -- 2
-- 取小数位(四舍五入)
select format(2.345, 2); -- 2.35
-- 取模
select mod(3,2); -- 1
-- 随机数 范围[0, 1]
select rand();
-- ################################### 其他:##################################
-- delimiter 更改结束标志
delimiter || -- sql语句以||结束
delimiter ; -- 还原
-- begin与end语句 之间可执行的多个语句
begin
-- 执行语句;...; 这里需要用上delimiter
end
-- ################################ 权限操作:####################################
-- 创建用户并设置权限(记得刷新) 创建用户名和密码为 normal, 只能查询的用户, localhost 只能在本机IP地址登录
grant select on sport.* to 'normal'@'localhost' identified by password '*F3F91B23FC1DB728B49B1F22DEE3D7A839E10F0E' with grant option; -- with grant option 允许授权给其他用户
-- 查询出你的密码对应的字符串,如:"normal" 对应上面的 'F3F91B23FC1DB728B49B1F22DEE3D7A839E10F0E'
select password('你想输入的密码');
-- 刷新权限
flush privileges;
-- 查看当前用户的权限
show grants;
-- 查看某个用户的权限
show grants for 'normal'@'localhost';
-- 授权,把更新权限给 normal'@'localhost 用户(需要在root账户下操作)
grant update on sport.* to 'normal'@'localhost' with grant option;
-- 回收权限
revoke update on sport.* from 'normal'@'localhost';
-- 被授权 用户拥有 授权的权限(在授权时候后面加上)
with grant option;
-- 创建用户
create user 用户名@ip地址 identified by '密码';
-- 删除用户
drop user 用户名@IP地址;
-- ################################ 触发器操作:####################################
-- 创建
create trigger
trigger_name -- 触发器名称
trigger_time -- 触发时机: before 或 after
trigger_event -- 触发事件: insert 、 update 或 delete
ON tbl_name -- 建立触发器的表名
for each row
trigger_stmt -- 触发器程序体(有多行)
-- ############################# 存储过程和函数操作:#################################
-- 创建存储过程和函数
create procedure 函数名(in 参数名 参数类型) -- in代表输入
select * from 表名 where 字段名 = 参数名;
-- 调用函数
call 函数名(参数);
-- 删除存储过程和函数
drop procedure 函数名;
mysql常见不常见的所有命令
最新推荐文章于 2024-06-05 22:54:12 发布