mysql表、视图、索引、函数、触发器相关示例

目录

  • 表相关
  • 1、创建
  • 2、修改
  • 3、删除
  • 视图相关
  • 1、创建
  • 2、修改
  • 3、删除
  • 4、查看
  • 索引相关
  • 1、创建
  • 2、删除
  • 存储函数创建删除和使用
  • 触发器创建删除和使用

表相关

1、创建

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `year` int(11) NOT NULL COMMENT '年',
  `month` tinyint(4) NOT NULL COMMENT '月',
  `is_xxx` unsigned tinyint COMMENT '表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型是 unsigned tinyint（1 表示是，0 表示否）',
  `updator` varchar(11) NOT NULL,
  `updateTime` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=321 DEFAULT CHARSET=utf8 COMMENT='测试表';


实例解析：
  1.表名、字段名必须使用小写字母或数字,，禁止出现数字开头，禁止两个下划线中间只出现数字
  2.如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为NULL ，就会报错。
  3.AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
  4.PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。
  5.ENGINE 设置存储引擎，CHARSET 设置编码。

2、修改

（1）修改表名：

alter table table_name rename table_new_name

（2）新增字段：

ALTER TABLE table_name ADD column_name datatype

（3）修改字段名：

ALTER TABLE table_name ALTER COLUMN column_name new_column_name varchar(100);

（4）修改字段类型：

ALTER TABLE table_name ALTER COLUMN column_name datatype

（5）删除字段：

ALTER TABLE table_name DROP COLUMN column_name

3、删除

DROP TABLE `test`;

视图相关

1、创建

CREATE VIEW view_name AS
    SELECT column_name(s) FROM table_name
        WHERE condition;

2、修改

CREATE OR REPLACE VIEW v_test1 AS
    SELECT * FROM ceshi ;
    
--方案2
alter view bankview as select bid from bank;

3、删除

DROP VIEW view_name

4、查看

show table status where comment='VIEW';

索引相关

1、创建

（1）在表上创建一个简单的索引

CREATE INDEX index_name ON table_name (column_name,column_name2)

（2）在表上创建一个唯一的索引

CREATE UNIQUE INDEX index_name ON table_name (column_name)

2、删除

ALTER TABLE table_name DROP INDEX index_name

存储函数创建删除和使用

--创建
create function hello(s char(20) charset utf8)
returns char(50)
reads sql data
begin
  return concat('hello ',s,' !');
end
$

--调用
select hello('hdw')$
+--------------+
| hello('hdw') |
+--------------+
| hello hdw ! |
+--------------+

--删除
drop function hello$

--创建存储函数
create function getcid(n char(20) charset utf8)
returns int
reads sql data
begin
  return (select cid from stu where sname=n);
end
$
--存储函数可以用在sql语句中
select cname from class where cid=getcid('小猫')$

触发器创建删除和使用

--删除班级自动触发删除学生
create trigger del_class_stu after delete on class
for each row
begin
  delete from stu where cid=old.cid;
end
$

--触发器作业
创建文章表含标题、作者、发布时间字段
如果只添加了标题,发布时间字段自动设置为当前时间,
作者字段设置为123网
\d $
create trigger this_name before insert on this_table for each row
begin
if new.uname is null then
set new.uname='123';
end if;
if new.timer is null then
set new.timer=unix_timestamp(now());
end if;
end
$

--查询已有触发器
show triggers;
--注释：触发器是设置好当执行某一个行为时执行另一个方法！