use 数据库 -- 选择进入数据库
show TABLES; -- 展示当前数据库的所有表
-- 以student表做测试
show ENGINES; -- 展示所有引擎
CREATE TABLE student(id int PRIMARY KEY ,name VARCHAR(20) not NULL,date1 Date ,sex varchar(10) DEFAULT '男',weiyi int unique ); -- 创建表,并设置主键、非空、默认、唯一约束
desc student; -- 查表结构 ,C为测试表;
SHOW CREATE TABLE student; -- 显示创建表时的CREATE TABLE语句
alter TABLE student rename to Stu; -- 修改表名
drop TABLE student; -- 删除表
alter TABLE student Drop weiyi; -- 1删除字段 唯一
alter table student MODIFY name char(20); -- 1修改字段类型
alter table student add love char(20); -- 增加字段
-- 删除
delete from student WHERE id=1; -- 按条件删除
DELETE from student; -- 删除表
insert into student(id,name ,date1,sex,love)values (1,'mxd','2020-11-01','女','and'); -- 插入
-- 修改表中记录
update student set sex='女'; -- 全部修改
update student set sex='女' where id=1; -- 按条件修改
-- 外键
create table sc1 (id int , FOREIGN KEY(id) REFERENCES student(id));
-- 类型 ,enum单选,set多选 自动增长策略 auto-increment
正则匹配
查询用表(经典师生关系)
-- 建表
-- 学生表
CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`));-- 课程表
CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`));-- 教师表
CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`));-- 成绩表
CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`));-- 插入学生表测试数据
insert into Student values('01','赵雷','1990-01-01','男');
insert into Student values('02','钱电','1990-12-21','男');
insert into Student values('03','孙风','1990-05-20','男');
insert into Student values('04','李云','1990-08-06','男');
insert into Student values('05','周梅','1991-12-01','女');
insert into Student values('06','吴兰','1992-03-01','女');
insert into Student values('07','郑竹','1989-07-01','女');
insert into Student values('08','王菊','1990-01-20','女');-- 课程表测试数据
insert into Course values('01','语文','02');
insert into Course values('02','数学','01');
insert into Course values('03','英语','03');-- 教师表测试数据
insert into Teacher values('01','张三');
insert into Teacher values('02','李四');
insert into Teacher values('03','王五');-- 成绩表测试数据
insert into Score values('01','01',80);
insert into Score values('01','02',90);
insert into Score values('01','03',99);
insert into Score values('02','01',70);
insert into Score values('02','02',60);
insert into Score values('02','03',80);
insert into Score values('03','01',80);
insert into Score values('03','02',80);
insert into Score values('03','03',80);
insert into Score values('04','01',50);
insert into Score values('04','02',30);
insert into Score values('04','03',20);
insert into Score values('05','01',76);
insert into Score values('05','02',87);
insert into Score values('06','01',31);
insert into Score values('06','03',34);
insert into Score values('07','02',89);
insert into Score values('07','03',98);
存储过程
-- 无参数的存储过程
CREATE PROCEDURE quertAllByStu()
BEGIN
select * from Student;
END
-- 调用
call quertAllByStu();-- 创建带入参的存储过程
CREATE procedure queryById(IN inid VARCHAR(20))
begin
select * from Student where s_id=inid;
end
call queryById('01');-- 调用
drop PROCEDURE queryById;-- 删除proc
show CREATE PROCEDURE queryById;-- 显示创建存储过程的语句
show procedure status;-- 显示所用proc的详细信息
-- out模式