mysql基础语法
# 命令行登录
mysql -uroot -proot
# 基本
show tables
show databases
use DATABASENAME
# 创建表
create table TABLENAME (
id int primary key auto_increment,
name varchar(10) unique key,
age tinyint unsigned
)engin innodb charset utf8
# 删除表
drop table TABLENAME
# 创建数据库
create database DATABASENAME charset utf8
# 删除数据库
drop database DATABASENAME
# 数据类型
整数型： tinyint int(m) mediumint bigint
m是用于指定0填充长度，只有当列具备zerofill属性时才有用。
浮点型：double、float、decimal(m,d)
decimal(m,d)
m是整数位数，最大65，默认10
d是小数位数，最大30，默认0，小数位数必须小于等于整数位数
文本型： char(m) varchar(m) text set(val,val2,...) enum(val,val2,....)
char和varchar的区别：
1、char的检索速度比较快。
2、char如果存储文本的后面有空格，取出来时并没有空格。
文本数据量大于255是建议使用text，因为后面text的检索速度比char和varchar都快。
set和enum：
1、set是多选，插入数据时不需要跟着顺序。
底层：有值就取1，没有就取0，然后二进制倒转来写然后取十进制数结果插入。
insert into TABLE values (null,"篮球,足球,羽毛",18,"张三")
2、enum是单选，
insert into TABLE values ("篮球") # 基于value的形式插入
insert into TABLE values (1) # 基于index的形式插入
日期时间： date time year timestamp
timestamp的作用：会自动记录update此行的时间。
二进制： blob
# 修改表名
rename table TABLE to NEW_TABLENAME
# 复制表（不包含数据，只是复制并创建一个相同结构的表）
create table TABLE like TABLENAME
# 蠕虫复制
insert into TABLE select * from TABLE
# 基本查询
select COLUMN from TABLE,.....
# 去重
select distinct COLUMN from TABLE
# 定义语句结束符合
delimiter [符号]
# 增加数据
insert into TABLE [(COLUMN1,COLUMN2...)] values (VAL1,VAL2,......)
# 代替插入
replace into TABLE [(COLUMN1,COLUMN2...)] values (VAL1,VAL2,......)
# 删除数据
delete from TABLE [where <CAUSE>] [limit N]
# 修改数据
update TABLE set COLUMN=VAL where CAUSE
# 新增列
alter table TABLE add [column] CLOUMNNAME COLUMN_TYPE COLUMNATTRIBUTE [first|after COLUMNNAME]
# 删除列
alter table TABLE drop COLUMNAME
# 修改列的属性
alter table TABLE modify CLOUMNNAME COLUMN_TYPE COLUMNATTRIBUTE [first|after COLUMNNAME]
# 修改列的属性和名字
alter table TABLE change COLUMNNAME NEW_COLUMN_NAME COLUMN_TYPE COLUMNATTRIBUTE [first|after COLUMNNAME]
# 主键primary key，主键属于索引的一种。
主键具备的特性：
1、一张表只能有一个主键
2、主键插入的值不能为空，且唯一（有主键相当于具备NOT NULL属性）
添加主键:
1、创建表时添加
2、alter table TABLE add primary key (COLUMNNAME)
删除主键：
alter table TABLE drop primary key
# 自增长auto_increment属性
1、自增长只能添加在主键上
2、自增长相关的初始化变量: show variables like "%auto_increment%"
# 唯一键unique key ，唯一键属于索引的一种。
唯一键具备的特性：
1、一个表可以有多个唯一键
2、唯一键插入的值可以为NULL，但是必须唯一。
# 外键 foreign key，外键并不是索引的一种
添加外键的要求：
1、外键只有innodb才有，myisam并不支持
1、外键只能在索引列上添加
2、列的数据类型要确保一致
3、小心一些设置了not null但是约束规则确实set null时会添加失败
# 添加外键：
alter table TABLE add foreign key(COLUMN) references TABLE.COLUMN [on update {district,cascade,set null}] [on delete {district,cascade,set null}]
# 删除外键：
alter table TABLE drop foreign key COLUMN
# where语句
比较运算符：
等于： =
不等于： “ ! = ”或者 “ <>”
>
>=
<
<=
between xxx and xxx
not between xxx and xxx
in(val,val2,...)
not in(val,val2,...)
逻辑运算符：
and &&
or ||
not ！
模糊匹配：like、not like
通配符号：
% 多个字符
_ 一个字符
[] 区间内
[^] 区间外
is null 和 is not null
判断是否为空和是否为非空
正则匹配：rlike
. 一个字符
* 前面的字符出现0或者多次
+ 前面的字符出现1或者多次
? 前面的字符出现0或者1次
[] 区间内
[:upper:] [:lower:] [:alpha:] [:alnum:] [:digit:] [:punct:] [:space:]
[^] 区间外
< 词首
> 词尾
<单词> 精确匹配某个单词
{n} 前面字符刚好出现n次
{n,} 前面字符至少出现n次
{n,m} 前面字符至少出现n到m次
^ 行首
$ 行尾
# group by 语句
根据字段进行排序
语法：
select * from TABLE where CAUSE group by COLUMN,COLUMN2,.... [desc|asc]
聚合函数：
sum()
avg()
min()
max()
count()
count(*) count(1) count(COLUMN) 的区别：
count(*) 和 count(1) 如果数据中出现NULL数据照样会统计进来
而count(COLUMN) 则不会。
# having 语句：
having本质上和where一样时用于筛选符合条件的数据
having主要用于筛选经过group by语句分组后的数据，having只能跟着group by 出现，不能单独使用。
select * from TABLE where CAUSE group by COLUMN,.... having CAUSE
having中可以直接使用聚合函数，但是where中是不能的，另外having可以直接使用as别名，where也不行
原因在于：where是将数据从磁盘拿到内存，别名as是数据进入到内存之后的操作。
where语句之后的都是内存操作。
# order by语句：
用于排序
select * from TABLE where CAUSE group by COLUMN having CAUSE order by COLUMN [desc|asc],....
# limit 和 offset
limit用于指定返回结果集的行数，多用于分页查询。
offset则是用于指定偏移值，指定从第几行开始。
语法一：limit和offset分开写
select * from TABLE limit N offset M
语法二：limit集成offset功能
select * from TABLE limit M,N
# 多表查询：
select from TABLE,TABLE
# 联合查询
select from TABLE union select from TABLE
如果使用到group by 要使用括号包裹并使用limit来设置得到的结果集数
(select * from TABLE ) union (select * from TABLE group by CLOUMN limit N)
# 交叉查询
select from TABLE cross join table
# 连接查询
## 内连接
select * from TABLE inner join on CAUSE
## 外连接：
### 左外：select * from TABLE left join table on CAUSE
### 右外：select * from TABLE right join table on CAUSE
# 子查询：
## 按功能分：
## 标量子查询，子查询返沪一行一列数据
select * from table where Column=(子查询)
## 行子查询。子查询返回一行多列
select * from table where COLUMN in (子查询)
## 列子查询，子查询返回一列多行，需要构建行元素
select * from table where (COLUMN)=(子查询)
## 按位置分：
from子查询
select * from (子查询)
where子查询
## 特殊查询
exists子查询，先查询主查询，然后根据子查询的条件来显示最终结果
select * from table where exists (子查询)
# 视图View
视图本质上就是一个虚拟的二维表，只有在运行时才会取查询数据并返回结果集。
任何可以在select语句上使用的，都可以在view上使用。
视图的作用：
1、增加了安全性。
比如：某张表的有些列的数据不能随意查看，可以先创建视图，然后设置视图的权限，最后将不能随意查看的表设置权限。
2、减少查询的复杂冗余。
比如：有一个很复杂的查询语句，每次都要输入太麻烦了，就可以创建成视图，下次直接运行“select * from 视图名”即可。
语法：
create view VIEWNAME as select 语句
# 备份与恢复
mysqldump -uroot -proot DATABASE.TABLES > xxx.sql
mysql -uroot -proot DATABASE < xxx.sql
# 创建用户
create user 用户名@主机地址 [identified by PASSWORD]
主机地址可以使用“” 表示任意，即：create user swq@"" identified by 123456
# 权限赋予与撤回
grant all privileges on 数据库.表 to 用户名@主机地址 [identified by PASSWORD]
revoke all privileges from 数据库.表 to 用户名@主机地址
# 事务
ACID特性:
原子性、一致性、隔离性、持久性
并发事务产生可能产生的问题：
脏读，一个事务读取到另外一个事务还未提交的数据
不可重复读，一个事务读取到另外一个事务修改的数据
虚读（幻读）， 一个事务读取到另外一个事务新增的数据
解决方案：
读未提交，性能最好，但是什么问题都解决不了，用我就一定出问题
读已提交，可以解决脏读的问题，oracle数据默认级别。
可重复读，可以解决脏读和不可重复读问题，mysql默认隔离级别。
串行化（序列化），所以问题都可以解决，但是性能最差。
start transaction # 启动事务
commit # 提交事务
savepoint POINTNAME # 保存回滚点
roolback to [pointname] # 回滚上一个点或者回滚到指定点
# 变量
·系统变量，两个@@
1、全局系统变量，在新启动的所有终端中有效。
#设置全局系统变量：
set @@global.变量名=值
set global 变量名=值
# 查看全局系统变量：
select @@global.变量名
2、局部系统变量，仅仅在当前的终端中有效。
# 设置局部系统变量：
set @@[session|local].变量名=值
·用户变量，一个@
set @变量名=值
·局部变量，定义在语句块BEGIN END之间。
declare 变量名 变量类型 变量属性 [default value] [=value]
·select语句中保存值到变量中
select 变量:=column from TABLE # 此种方法具备返回性
select column from TABLE into 变量 # 此种返回不具备返回性
# 判断流程结构if\if elseif
if 表达式 then
else
end if;
# while
while 表达式 do
end while;
# 函数的定义和使用function
函数必须要在BEING END 之间返回，函数体中不能出现带有返回性的语句出现，不然会报错
create function 函数名([参数名 参数类型 参数属性,...]) returns 返回值类型
BEGIN
return 返回值
END
使用函数:
select 函数名(参数)
查看函数：
show function 函数名
show function status
查看函数的创建语句：
show create function 函数名
# 存储过程 procedure
存储过程和函数的不同点：
1、存储过程不需要返回，因此BEGIN...END语句内可以使用具备返回性质的查询语句。
create procedure 存储过程名({IN|OUT|INOUT} 参数名 参数类型 参数属性,...)
BEGIN
...
END
IN：传参只读，就算结构体中修改了传参的值，也不会影响外面实际的值。
OUT: 传递过来的参数首先会被置空，结构体中修改了传参的值会影响外面实际的值。
INOUT：传递的参数不会被置空，结构体中修改了传参的值会影响外面实际的值。
查看存储过程：
show procedure status [like PATTERN]
查看创建存储过程的语句：
show creat procedure 存储过程名
使用存储过程：
call 存储过程名([参数])
删除存储过程：
drop procedure 存储过程名
# 触发器trigger
根据给定的条件进而自动运行某个sql语句.
触发器是一种特殊的存储过程。但是触发器在BEGIN...END语句内不能使用带有返回性的select等语句。
创建触发器：
create trigger 触发器名 {before|after} {insert|update|delete} on 数据库.表 for each row
BEGIN
...
END
触发器内部可以使用new和old关键词，用于代表insert、update、delete前后的字段数据
insert是只有new
update有new和old
delete只有old
# 索引index
合理的使用索引可以提高查询的速度。
索引能提高查询速度，但是会降低修改等动作的速度，因为在修改数据的同时还要保存索引，而且所有也需要占用一定的磁盘空间。
where语句中经常出现的列可以适度增加索引
散列性太低的列不适合使用索引，比如性别。
不要在大量NULL的列中添加索引。
mysql中有什么索引：
1、主键
alter table TABLE add primary key(COLUMNNAME)
复合主键:
alter table TABLE add primary key(COLUMNNAME,...)
删除主键：
alter table TABLE drop primary key
2、唯一键
alter table TABLE add unique key(COLUMNNAME)
复合唯一键:
alter table TABLE add unique key(COLUMNNAME,...)
删除唯一键：
alter table TABLE drop index COLUMNNAME
3、普通索引
alter table TABLE add index(COLUMNNAME)
复合索引:
alter table TABLE add index(age,address,phonenumber)
相当于构建了三个索引
age
age,address,
age,address,phonenumber
删除普通索引：
alter table TABLE drop index COLUMNNAME
4、全文索引（myisam）
用得不是很多， 只能在varchar和text数据类型的列上创建。
alter table TABLE add fulltext(COLUMNNAME)
5、最左索引，设置lenght的长度小于实际列的长度，可以加快检索速度。
alter table TABLE add index column(length)
本文来自博客园，作者：蕝戀，转载请注明原文链接：https://www.cnblogs.com/juelian/p/14528916.html
