mysql常用语法汇总

增删改查

-- 新增
insert into tb_student (`name`,age,sex,classid) values('李四',18,1,1);
-- 删除
delete from tb_student where id=1;
-- 修改
update tb_student set `name`='王五' where id=2;
-- 查询
select * from tb_student where name like '王%' and age=18;

排序、分页、去重、分组

-- 排序
select * from tb_student order by id desc;
-- 分页
select * from  tb_student where name like 'a%' order by id desc LIMIT 10,5 ;
-- 去重
select distinct age from tb_student
-- 分组
select age,count(1) as num from tb_student group by age

 表、字段操作

-- 建表
create table `tb_class` (
  `classid` int not null auto_increment,
  `classname` varchar(255) default null,
  `classtype` varchar(10) default null,
  primary key (`classid`)
) engine=innodb default charset=utf8mb4 collate=utf8mb4_0900_ai_ci;
-- 新增字段
alter table tb_class add createtime date;
-- 修改字段
alter table tb_class modify column  createtime varchar(20);
-- 删除字段
alter table tb_class drop column createtime;

视图

-- 创建视图
drop view if exists `view_student1`;
create view view_student1
as
select * from tb_student;
-- 查看视图字段信息
DESCRIBE view_student1;

事务

-- 开启事务
begin; -- 或start transaction
insert into tb_class (classname,classtype) value('一班','特级班');
update tb_class set classname='二班' where classname='一班';
-- commit; 提交事务
rollback; -- 回滚事务
-- 查看事务的隔离级别
select @@TRANSACTION_ISOLATION;
-- 设置事务的隔离级别
SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE};

存储过程

-- 创建存储
delimiter // -- 用来声明SQL语句的结束符号
drop procedure if exists proc_student1;
create procedure proc_student1 (in classname varchar(20),out result varchar(20))
begin
declare classtype varchar(20);
set classtype='特级班';
insert into tb_class (classname,classtype) value(classname,classtype);
update tb_class set classname='二班' where classname=classname;
set result='成功';
end //
-- 当创建完一个存储过程之后再将分隔符替换为分号，为了不影响其他的操作
delimiter ;

-- 调用存储过程，并且查询输出参数的值
call proc_student1('二班',@result);
select @result;

函数

-- 创建函数
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
 DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
 DECLARE return_str VARCHAR(255) DEFAULT '';
 DECLARE i INT DEFAULT 0;
 WHILE i < n DO
 SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
 SET i = i + 1;
 END WHILE;
 RETURN return_str;
END $$
-- 调用函数
select rand_string(10)

索引

-- 普通索引
create index ix_stdent1 on tb_student (`name`);
-- 唯一索引
create unique index ux_student1 on tb_student(`name`);
-- 联合索引
create index ix_stdent1 on tb_student (`name`,age);
-- 查看SQL语句执行走哪个索引
explain select * from tb_student where name like 'a%' and age = 10