Liupeng
Aug 29, 2020
数据库再一次初体验
建库
create database party;
建表
主键位置I
create table beijing
(
id int primary key not null ,
name varchar(20),
birthday date,
age int,
hometown varchar(40),
constraint beijing_pk
primary key (id)
);
主键位置II
create table beijing
(
id int not null ,
name varchar(20),
birthday date,
age int,
hometown varchar(40),
constraint beijing_pk
primary key (id)
);
查看表结构
desc party.beijing;
增
增加一行数据
insert into party.beijing value (1, '维尼', '1953-06-15', 67, '陕西富平');
INSERT INTO party.beijing (id, name, birthday, age, hometown) VALUES (1, '维尼', '1953-06-13', 67, '陕西富平')
增加多行数据
INSERT INTO party.beijing
(id, name, birthday, age, hometown)
VALUES
(4, '狗头军师', '2020-08-04', 65, '上海'),
(5, '狗头军师', '2020-08-04', 65, '上海'),
(6, '狗头军师', '2020-08-04', 65, '上海');
删
删除几行数据
delete from party.beijing where party.beijing.id > 3;
删除一个表
delete from party.beijing;
删除一个数据库
delete database_name;
改(更新数据)
update party.beijing
set
party.beijing.birthday = '1955-10-06',
party.beijing.age = 65
where party.beijing.name = '狗头军师';
查(简单查询)
select * from party.beijing;
函数
创建函数
create function getNameByID(tempId int)
returns varchar(20)
begin
return (select name from party.beijing where id = tempId);
end;
报错
This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
解决问题
set global log_bin_trust_function_creators=1;
执行函数
select getNameByID(2) as name;
索引
存储过程
创建无参数存储过程
/*
存储过程 ： paly001
创建人： liupeng
主要功能：返回一个数据集（Table)内部的数据长度。
运行实例：SELECT COUNT(*) FROM party.beijing;
*/
DELIMITER //
CREATE PROCEDURE paly001()
BEGIN
SELECT COUNT(*) FROM party.beijing;
END //
DELIMITER ;
执行无参数存储过程
call paly001();
删除无参数存储过过程
drop procedure paly001;
创建一个有参数的的存储过程
/*
存储过程 ： paly003
创建人： liupeng
参数：tempID
主要功能：返回一个数据.
运行实例：SELECT * FROM party.beijing where id = tempID;
*/
DELIMITER //
CREATE PROCEDURE paly003(in tempID int)
BEGIN
SELECT * FROM party.beijing where id = tempID;
END //
DELIMITER ;
执行有参数的存储过程
call paly003(3);
定义一个有返回值的存储过程
/*
存储过程 ： paly004
创建人： liupeng
参数：tempCount
主要功能：返回一个数据.
运行实例：SELECT count(*) into tempID FROM party.beijing;
*/
DELIMITER //
CREATE PROCEDURE paly004(out tempCount int)
BEGIN
SELECT count(*) into tempCount FROM party.beijing;
END //
DELIMITER ;
执行有返回值的存储过程
set @count = 0;
call paly004(@count);
查看结果
select @count as nnnn;
视图
创建视图