DDL
操作数据库
1.创建数据库
CREATE DATABASE dbname;
2.查看数据库
SHOW DATABASES;
3.使用数据库
use dbname;
4.删除数据库
DROP DATABASE dbname;
操作表
5.修改表
ALTER TABLE tablename MODIFY [COLUMN] column_definition [FIRST | AFTER col_name];
6.查看表结构
desc tbname;
7 修改表名称
ALTER TABLE tablename RENAME [TO] new_tablename;
修改字段
8.增加字段
alter table majiping add column play varchar(40) not null ;
9.删除字段
ALTER TABLE tablename DROP [COLUMN] col_name;
10.字段改名
ALTER TABLE tablename CHANGE [COLUMN] old_col_name column_definition [FIRST | AFTER col_name];
- 添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束
/*例如:*/
alter table student modify column age int not null;
- 添加表级约束
alter table 表名 add [constraint 约束名] 约束类型(字段名) [外键的引用]
/*例如添加外键:*/
alter table student add constraint fk_stu_class foreign key(class_id) references class(id);
添加主键:alter table student add constraint pk_stu primary key(id);
添加字段唯一:alter table student add unique(stu_nu);
修改约束
3.删除列级约束
例如删除非空约束
alter table student modify column age int null;
删除主键:
alter table student drop primary key;
删除唯一键(可以使用show index from student查看唯一)
alter table student drop index index_name;
删除外键:
alter table student drop foreign key fk_name;
事务
视图
创建视图
create view other as
select
a.name as username, b.name as goodsname
from
user as a, goods as b, ug as c
where
a.id=c.userid and c.goodsid=b.id;
查询视图
select * from other;
视图的修改
create or replace view viewName
as
alter view viewName
as
查看视图的创建语句
show create view viewName;
变量
1.查看所有全局变量
show global variables;
2.查看部分全局变量
show global variables like "%char%";
3.查看指定的全局变量的值
select @@global.autocommit;
select @@tx_isolation
4.为某个指定的全局变量赋值
set @@global.autocommit=0;
自定义变量:
1.用户变量
2.局部变量:
存储过程
#使用存储过程插入五条数据
delimiter %
create procedure myp1()
begin
insert into majiping(name,my_age) values
('majiping1',11),
('majiping2',12),
('majiping3',13),
('majiping4',14),
('majiping5',15);
end %
call myp1()%;
#创建存储过程判断用户登录是否成功
delimiter %
create procedure myp3(in username varchar(40), in password integer)
begin
declare c int default 0;
select count(*)
into c
from user u
where u.username = username
and u.password = password;
select IF(c > 0,'成功','失败');
end %
call myp3('majiping',123);
#创建存储过程判断用户登录是否成功
delimiter $
create procedure myp4(in username varchar(40), in password int, out h int)
begin
select count(*)
into h
from user u
where u.username = username
and u.password = password;
end $
call myp4('majiping',123,@hh);
select if(@hh>0,'成功','失败');
函数
分支结构
一.IF 函数
二.case 结构
三.if结构
while循环