开发版sql语句

mysql

基础语法

# 建库
create database mydb character set utr-8;
create database mydb character set utr-8;
# 修改字符集
alter database mydb character set utf8;
# 查看建库
show create database mydb;
show create database mydb;
# 查看所有库
show databases;
show databases;
# 删除库
drop database mydb
#进入
use mydb;

# 类型
int
bigint

char(字符数)    如定长 手机号/性别
varchar(字符数) 如java中string
text           文本类型

decimal(总数位,小数位)
float
double

year  年
date  年月日
time  时分秒
timestamp  可以设置当前操作时间
datetime

# 创表
create table class(
	id int primary key auto_increment,
    name varchar(10),
    sex char(1),
    phone char(11)
)
# 查看单表
show create table class；
# 删表
drop table class;
# 修改表名
alter table class rename newclass;
# 添加列
alter table class add `status` int;
# 删除列
alter table class drop `status`;
# 修改列名和数据类型
alter table class change sex gender char(1);
alter table class modify sex char(1);

# 复制表
create table new_class like class;
create table new_class as (select * form class)

# 查询
select * from class

# 插入
insert into class values(列值1,列值2,...),(列值1,列值2,...)；

# 修改
update class set 列名1=值，列名2=值 where ...
 
# 查存储引擎
show variable like '%engine%'

高级特性

# 用户变量
set @name = 'wangning';
select @name;

# 系统变量-系统配置相关
show global variables like '%engine%'
# 会话变量-当前链接复制系统变量
show session variables like '%port%'
set port = 3307;

# -----局部变量-------
create procedure pro_var()
begin
	# 局部变量申明
	declare name,pwd varchar(10) default 123;
	# 设置固定值
	set name = 'wning';
	# 设置查询值
	select phone into pwd from student where name = 'wning';
	# 查看变量值
	select pwd;
end

#调用存储过程
call pro_var()

# 删除存储过程
drop procedure pro_var;

#  --------------------------------
# if elseif else
create procedure pro_if(score int)
begin
 # if分支
 if score >=80 then
 	select '优秀'
 elseif score >=60 then
 	select '及格'
 else
 	select '不及格'
 end if;
end;

# while 循环---------------------
create procedure pro_while(num int)
begin
	declare result int default 1;
	declare number int default 0;
	# 循环体
	while number < num do
		set result = result + number;
		set number = number + 1;	
	end while;
	
		select result;	
end;
# 调用
call pro_while(10)

# 输入输出参数----------------------
create procedure pro_var(inout info varchar(11))
begin
	select phone into info from student where name=info;
end;
# 调用
set @username = '豆豆';
call pro_var(@username) ;
select @username;

# 触发器---------------------------
create trigger tri_gender_chang before update on student for each row
begin
	if new.gender != '男' and new.gender !='女' then
		set new.gender = old.gender;
		set new.modify = null;

	end if;
end; 
# 更新
update student set gender = '女',`modify`=now() where id=3 ;
# 更新
update student set gender = '男',`modify`=now() where id=2 ;