mysql随笔

mysql随笔

1、启动mysql服务

net start mysql

2、连接mysql

mysql -h localhost -P 3306 -u root -p
或者
mysql -h localhost -P 3306 -u root -proot

3、常用命令

show databases;
use [数据库名];//切换到指定数据库
show tables;//显示所有数据表
select database();//查看所在数据库

//创建数据库并设置默认的字符集
create database test01 default character set utf8 COLLATE utf8_general_ci;

//创建数据表
CREATE TABLE `test01`.`userinfo`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NULL,
  PRIMARY KEY (`id`)
)engine=InnoDB DEFAULT CHARSET=utf8;

//查看数据库版本
方式1：select version();
方式2（dos方式）：mysql --version 或 mysql -V

4、规范

不区分大小写，建议关键字大写，表名、列名小写；
每条命令建议分号结束；
缩进；
注释：单行注释#注释文字说明
单行注释--【空格】注释文字说明
多行注释/* 注释文字说明 */

5、图形化界面操作

工具：SQLyog\Navicat

6、mysql中"+"只作为运算符

7、显示表结构

DESC userinfo

8、模糊查询

like  _（匹配单个字符）%（匹配多个字符） 若要匹配下划线（“_”）使用\_进行转义
\为默认的转义字符，还可以使用指定字符作为转义字符，like '_$_' ESCAPE '$'

BETWEEN [值1] AND [值2] 包含临界值 

9、IS NULL 对比 <=>(安全等与)

is null:智能判断是否为null
   <=>:既可以判断是否为null,还可以判断是否等与普通数值

10、去重distincts

11、Order by 支持别名

执行过程：form 表，where筛选条件，select字段，order by 排序

12、常见函数

LENGTH()#判断字节长度 一个汉字gbk字符集占2个，utf8占3个
//SELECT LENGTH('如意123')#utf8字符集下长度是9

SHOW VARIABLES LIKE '%char%'#查看字符集

concat()#拼接字符串

upper('a') lower('A')

SUBSTR('123456',2)
SUBSTR('123456',2,5)

INSTR('123456','4567') #返回子串在父串的索引

TRIM(' 1 2 3 4 5 6 ') #去除前后空格

TRIM('a'FROM'a1a2a3a4a5a6a')#去除前后指定字符串

LPAD('123456',10,'0')#左填充指定字符串

RPAD('123456',10,'0')#右填充指定字符串

REPLACE('123456','123','456')#字符串替换


ROUND(1.567)#四舍五入
ROUND(1.567, 2)
ROUND(- 1.567, 2)

CEIL(1.567)#向上取整	
CEIL(- 1.567)

FLOOR(1.567)#向下取整
FLOOR(- 1.567)

TRUNCATE(1.123, 3)#截断

#取余a-a/b*b
  10 % 3,
  - 10 % 3,
  10 % - 3,
  - 10 % - 3,
  MOD(10, 3),
  MOD(- 10, 3),
  MOD(10, - 3),
  MOD(- 10, - 3)




NOW(),
CURDATE(),
CURTIME(),
YEAR(NOW()),
MONTH(NOW()),
MONTHNAME(NOW())

STR_TO_DATE('2021-05-23', '%Y-%m-%d')
DATE_FORMAT(NOW(), '%Y年%m月%d日')

if 函数

case 函数
case when then
when then
…
else

13、分页查询

//limit [起始索引] ,显示条数
limit (page-1)*size ,size

14、联合查询

union
//应用场景：两个不一样的表，牵引车表，挂车表

15、插入数据

insert into tablename (列名1。。。) values (值1。。。)，(值1。。。)。。。

insert into tablename set 列名1 = 值1，列名2 = 值2.。。。

insert into tablename (列名1。。。)  select (列名1。。。) 

16、更新数据

单表更新

多表更新

17、删除数据
单表删除

多表删除
delete t01，t02
from 表1 t01，表2 t02
where 筛选条件

//清空表（不用条件，效率高，删除后自增长值从1开始，没有返回值，删除后不能回滚）
truncate table tablename

DDL
1、数据库操作

//如果想要创建的数据库不存在就创建数据库
create database if not exists databasename

//修改库名
rename database 旧库名 to 新库名

//修改数据库字符集
alter database 库名 character set utf8

//如果要删除的数据库存在就删除数据库
drop database if exists 库名

2、数据表操作

//修改数据表列名
ALTER TABLE userinfo CHANGE COLUMN username NAME VARCHAR(10)

//修改数据表列类型
ALTER TABLE userinfo MODIFY age INT;

//添加新列
ALTER TABLE userinfo ADD COLUMN age INT; 

//删除列
ALTER TABLE userinfo DROP  COLUMN  age ;

//修改表名
ALTER TABLE 旧表名 RENAME TO 新表名

//删除表
drop table if exists 表名

/

/表的复制
//仅仅复制表的结构
create table 新表名like 被复制的表名
//复制表的结构加数据
create table 新表名
select * from 被复制的表名
//仅仅复制某些字段，不要数据
CREATE TABLE 新表名 
SELECT [列1.。。] FROM 被复制的表名WHERE 1=2[不成立的条件]

//数据类型
//长度只代表显示的位数，不够用0来填充

//浮点数据类型（M,D）
M:整数位数+小数位数
    D:小数位数

3、约束
//约束
分列级约束（6大约束基本都支持），表级约束（除了非空和默认约束都可以）

1、非空(not null)
2、默认（default 1）
3、主键（primary key）
4、唯一（unique）
5、检查约束(check(sex = ‘男’ or sex = ‘女’))
6、外键约束（references 外部表名（列名））

//查看索引
show index from 表名 包含主外键、唯一

//表级约束
constraint pk primary key(id)#主键
constraint uq unique(列名)#唯一键
constraint ck check(sex = '男' or sex = '女')#检查约束
constraint fk foreign key(列名) references 表名（列名）#外键

//外键要求主表的字段是主键或者唯一
//删除数据时，先删除主表，再删除从表

//通过修改表添加主键
alter table 表名 modify column 列名 数据类型 primary key 
alter table 表名 add primary key(列名)

//通过修改表添加唯一键
alter table 表名 modify column 列名 数据类型 unique
alter table 表名 add unique(列名)

//通过修改表添加外键
alter table 表名 add foreign key(列名) references 主表名(主键或者唯一键列名)

/

/删除默认约束
alter table 表名 modify column 列名 数据类型 

//删除主键
alter table 表名 drop primary key

//删除唯一键
alter table 表名 drop index 唯一键索引名

//删除外键
alter table 表名 drop foreign key 外键名称

//查询索引名称
show index from 表名

4、标识列

//自增长，一个表至多一个自动增长列；数值类型
auto_increment

5、TCL(Transaction Control Language)事务控制语言

//查看支持的存储引擎
SHOW ENGINES;

//支持事务:InnoDB
//不支持事务：MEMORY、MyISAM

ACID
原子性（atomicity）:事务的最小单位，要么都成功，要么都失败。
一致性（consistency）：一个事务的执行从一种一致状态转换成另一种一致状态。
隔离性（isolation）：一个事务的执行不受其他事务的影响。
持久性（durability）：一个事务的提交对数据库数据的影响是永久性的。

事务的创建
#查看自动提交事务的状态
SHOW VARIABLES LIKE 'autocommit' 
#关闭自动提交事务
SET autocommit = 0

#事务使用步骤
#开启事务
SET autocommit = 0;
START TRANSACTION;
#编写一堆语句
update 。。。
update 。。。
#结束事务，要么提交，要么回滚
commit;#ROLLBACK;

#脏读：读取到未提交的数据
#不可重复：两次读结果不一样
#幻读：两次读取数据行数不一样，针对插入

#事务隔离级别
#mysql有4种:repeatable read(默认)、read uncommited、read commited
#oracle有2种:serializable\readcommited

#查看事务隔离级别
SELECT @@tx_isolation

#设置事务的隔离级别
SET GLOBAL|SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

 脏读 不可重复读 幻读
read uncommited √ √ √
read commited × √ √
repeatable read × × √
serializable × × ×
#savepoint使用，可以用来回滚到指定节点
SET autocommit = 0;
START TRANSACTION;
DELETE FROM userinfo WHERE id = '1';
SAVEPOINT a;#设置保存节点
DELETE FROM userinfo WHERE id = '2';
ROLLBACK TO a;

6、视图

#出现于5.0.1版本
#只保存了sql逻辑
#应用场景：多个地方用到了同样的查询结果；该查询结果使用的SQL语句较为复杂。

#视图创建
create view [视图名] as 查询语句

#视图修改
create or replace view [视图名] as 查询语句
alter view [视图名] as 查询语句

#删除视图
drop view 视图1，视图2。。。

#查看视图
desc 视图名
show create view 视图名#更详细

#视图数据的更新
#可以进行insert into 
#可以进行update
#可以进行删除delete
#建议设置视图只读权限，不建议使用视图更新数据

#以下情况视图是不能更新的
1、包含以下关键字：distinct\group by\having\union\union all
2、常量视图
3、select中包含子查询
4、join
5、from 一个不能更新的视图
6、where 子句中引用了已查询的表

7、变量

#系统变量（全局变量、会话变量）
#自定义变量（用户变量、局部变量）

SHOW GLOBAL|[SESSION ]VARIABLES like'%%';
#查看系统变量
SHOW GLOBAL VARIABLES ;
#查看会话变量
SHOW SESSION VARIABLES ;

#查看某个指定的系统变量值
select @@global|[session].系统变量名

#变量赋值
set @@global|[session].系统变量名 = 值
set @@global|[session] 系统变量名 = 值

#自定义变量

#用户变量
#声明并初始化
set @用户变量名 = 值
set @用户变量名 := 值
select @用户变量名 := 值

#变量赋值
set @用户变量名 = 值
set @用户变量名 := 值
select @用户变量名 := 值
select 字段 into 变量 from 表

#查看变量
select @用户变量名

#局部变量（在begin and中有效）
#声明
declare 变量名 类型;
declare 变量名 类型 default 值;

变量赋值
set 局部变量名 = 值
set 局部变量名 := 值
select @局部变量名 := 值
select 字段 into @局部变量名 from 表

#查看变量
select 局部变量名

8、存储过程

#create procedure 存储过程名(参数列表)
#begin
#end

#参数列表
#参数模式 参数名 参数类型
#参数模式：in（输入）、out（输出）、inout（既输入又输出）
#参数名
#参数类型

#如果存储过程只有1句话可以省略begin end
#存储过程中每条sql需要用分号（;）结尾
#存储过程的结尾可以使用 DELIMITER 结束标记 重新定义

#调用
call 存储过程名() 结束标记

#删除存储过程
drop procedure 存储过程1，存储过程2。。。

#查看存储过程
show create procedure 存储过程名

9、函数

#函数有且仅有一个返回

#创建函数
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
    DECLARE res INT DEFAULT 0;
    RETURN res;
END;

#调用函数
SELECT 函数名(参数列表)

#查看函数
show create function 函数名

#删除函数
drop function 函数1，函数2。。。

10、流程控制结构

#顺序结构
#分支结构
IF ... THEN ... ELSEIF THEN END IF
CASE WHEN ... THEN ... ELSE ... END CASE;

#循环结构
#while loop repeat
[循环名]WHILE 循环条件 DO
循环体;
END WHILE [循环名]

[循环名] ioop 
循环体;
END ioop [循环名]

[循环名] REPEAT 
循环体;
UNTIL 结束循环条件 
END REPEAT [循环名]

#循环控制语句
#iterate 类似于continue,结束本次循环，继续下一次
#leave 类似于break;结束当前所在循环