MySql基础语法

MySQL基础语法

1.数据库

查看数据库

show databases;
show databases like '数据库名';

新建数据库

create database '数据库名';

删除数据库

drop database '数据库名';

修改数据库

alter database '数据库名' default charcter set utf8 collate utf8_general_ci;
alter database '数据库的名称' default character set gbk collate gbk_chinese_ci;

使用数据库

use '数据库名'；

2.表

create table `表名称` (
	id int(11) unsigned [unique not null] primary key auto_increment comment '注释', # 主键写法1
    name varchar(255) unique null comment '注释'，
    fid foreign key references `被引用的表`.`字段(key|index)`, # 外键写法1
    sex bit null default b'1',
    birthday datetime default 'xxxx-xx-xx xx:xx:xx',
    register timestamp default '15124',
    primary key(id), # 主键写法2
    constraint `fkey` foreign key(`当前表字段`) references `被引用的表`.`字段(key|index)`, # 外键写法2
);

查看表

show tables;	# 查看有哪些表
desc `表名称`;	  # 查看表的结构
show create table `表名称`;	# 查看表的创建语句
show table status from `数据库名称`; # 查看状态

删除表

drop table `表名称`;

修改表

alter table `表名称` add `列名称` datetime default 'xxxx-xx-xx xx:xx:xx';
alter table `表名称` modify column	`列名称` int(11) unsigned [unique not null] primary key auto_increment
alter table `表名称` drop column `列名称`;
alter table `表名称` engine innodb|myisam;

3.表内数据

插入数据

insert into `表名称`(`需要插入的字段的名称，逗号隔开`) 
value (`需要插入的字段的值，逗号隔开`);
example:insert into `user`(`id`,`name`) value(5,'curry')
insert into `表名称`(`需要插入的字段的名称，逗号隔开`) values (`需要插入的字段的值，逗号隔开`),(`需要插入的字段的值，逗号隔开`),(`需要插入的字段的值，逗号隔开`);  # 个数不限
example:insert into `user`(`id`,`name`) values (5,'curry'),(6,'lebron')

删除数据

delete from `表名称` where `条件`
example： delete from `user` where id = 1;  

修改数据

update `表名` set `列名`=`值`,`列名`=`值` where `条件`;
example: update `user` set `name`='lebron',`source`=80 where id = 10;

查询数据

select `字段，逗号隔开` from `表名称1` left|inner|right join `表名称2` on `表名称1`.`表名称2id`=`表名称2`.id
where `查询条件1` and `查询条件2` or `查询条件3`
group by `字段，逗号隔开`
order by `字段，逗号隔开` `desc|asc`
having `扩展条件`
limit `起始位置,条数`;

4.函数

聚合函数

函数名称	说明
avg(列名称)	求分组中所有行的平均值
count(列名称|*)	列名：返回这个列不为NULL的函数；*：返回所有函数包含空
group_concat(列名称 separator 分隔符号)	将分组中所有该字段的值合并成一个字符串，用分隔符分开
max(列名称)	求最大值
min(列名称)	求最小值
sum(列名称)	求和

标量函数

函数名称	说明
concat(列1，列2，列3，…)	将N个字段的值连接成一个字符串
ucase(列名称)	将字段的值转换为大写
lcase(列名称)	将字段的值转换为小写
index(列名称,查询下标的值)	该值在该字段中的位置下标
left(列名称,截取的个数)	截取该字段做开头的N个字符
right(列名称,截取的个数)	截取该字段做结尾的N个字符
round(列名称,位数)	将小数保留多少位小数
mod(列名称1,列名称2)	求余数

函数名称	说明
now()	获取当前日期和时间
curdate()	获取当前日期
curtime()	获取当前时间
format(日期列名称，Y_M_D h:m:s)	将指定时间转换成指定格式
date_format(日期列名称，Y_M_D h:m:s)	将指定时间转换成指定格式
date(日期列名称)	提取指定时间的日期部分
extract(date-part from 日期列名称)	提取指定字段的【date-part】部分；date-part : year |quarter |month |week |day |hour |minute |second |millisecond
date_add(日期列名称 interval 数值 date-part)	将该字段日期增加指定的【date-part】
date_sub(日期列名称 interval 数值 date-part)	将该字段日期减少指定的【date-part】
datediff(日期列名称1,日期列名称2)	获取两个之间的天数

5.视图

将某个查询语句的结构存起来的虚拟表

创建视图

create view `视图的名称` select语句

删除试图

drop view `视图的名称`;

查询：和普通表一样

更新：删除重建

6.索引

目录，大大提高查询效率

创建索引

create index `索引名称` on `表名称`.`列名称`;	# 普通索引
create index `索引名称` on `表名称`.`列名称`(长度);	# 短索引
create unique index `索引名称` on `表名称`.`列名称`(长度);	# 短索引# 唯一索引
create unique index `索引名称` on `表名称`(`列名称1`,`列名称2`) # 复合索引
create full index `索引名称` on `表名称`(`列名称1`,`列名称2`) # 全文索引

key和index的区别：key包括约束和索引，index就只是索引

7.外键

用于将两张表相关联的列(key|index)

条件：

1. 数据库引擎必须是innodb
2. 外键和被外键引用的字段必须类型一致长度一致

约束：

1. restrict：子表被引用父表不能更新外键

2. cascade：级联更新和删除，更新和删除父表子表跟着更新删除（默认）
3. set null：子表被删除后父表位空
4. no action：什么都不做

8.事务

特性：

1. 原子性：不可再分割，组成某个功能的最小单元（下单：购物车删除，订单表添加数据，商品表减库存）

2. 一致性：事务中的每一个子任务的状态都一致（都成功或者都失败），成功的状态都要一致
3. 隔离性：解决事务并发的问题（脏读、幻读、不可重复读）隔离机制
4. 持久性：事务的更改是永久

事物隔离级别：

级别	说明
read uncommitted	读未提交：能读取到另一个事务未提交的数据（脏读|幻读|不可重复读）
read committed	读已提交：能读取到另一个事务已提交的数据（幻读|不可重复读）
repeatable read	可重复读，读不能同时写（幻读）
serializable	序列化，都不能同时读写（效率低）

使用过程

1. 设置为不自动提交：set autocommit=0;

2. 开始事务：begin | start transaction;
3. 添加保存点：savepoint `保存点名称`;
4. 回滚：rollback [to `保存点名称`];
5. 提交：commit;
6. 结束事务：end | stop transaction;

9.自定义函数

前提：开启函数构造器

set global log_bin_trust_function_creators=true;

创建的语法：

delimiter $$
create function `数据库名`.`方法名`
	returns `返回值类型`
	begin
	# 定义变量，必须写在前面
	declare `变量名` `类型`;
	# if
	if `条件1` then
		# 语句1;
	elseif `条件2` then
		# 语句2;
	else
		# 语句3;
	end if;
	# case
	case `变量` when `值1` then
		# 语句1;
	case `变量` when `值2` then
		# 语句2;
	end case;
	# while
	while `条件` do
		# 语句;
	end while;
	end$$
delimiter ;

使用函数

select id,`方法名`(name) from table1;
insert into table1 values (1,`方法名`(张三));

10.存储过程

处理一条语句无法做到的复杂任务，只需要在存储过程中使用select语句就能返回结果集

创建语法：

delimiter $$
create procedure `数据库名`.`存储过程名`
	begin
	# 定义变量，必须写在前面
	declare `变量名` `类型`;
	# if
	if `条件1` then
		# 语句1;
	elseif `条件2` then
		# 语句2;
	else
		# 语句3;
	end if;
	# case
	case `变量` when `值1` then
		# 语句1;
	case `变量` when `值2` then
		# 语句2;
	end case;
	# while
	while `条件` do
		# 语句;
	end while;
	end$$
delimiter ;

使用存储过程

call `存储过程名`();

11.事件

定时任务

date-part : year |quarter |month |week |day |hour |minute |second |millisecond

创建语法：

delimiter $$
create event `数据库名`.`事件名`
	on schedule every `间隔数` `date-part`
	begin
	# 定义变量，必须写在前面
	declare `变量名` `类型`;
	# if
	if `条件1` then
		# 语句1;
	elseif `条件2` then
		# 语句2;
	else
		# 语句3;
	end if;
	# case
	case `变量` when `值1` then
		# 语句1;
	case `变量` when `值2` then
		# 语句2;
	end case;
	# while
	while `条件` do
		# 语句;
	end while;
	end$$
delimiter ;

开关事件：

alter event `uniadmin`.`test` ON completion preserve enable;
alter event `uniadmin`.`test` ON completion preserve disable;

12.触发器

对表进行增删改的时候自动触发的任务

创建语法：

delimiter $$
create trigger `数据库名`.`触发器名`
	before|after insert|update|delete
	on `数据库名`.`表名`
	for each row 
	# 语句
	begin
	end$$
delimiter ;