mysql 增删改

##表的创建、更新、修改

1. 创建表

Create table actor

Actor_id smallint(5) notnull Primary key,

First_name varchar(45) not null,

Last_name varchar(45) not null,

Last_update timestamp not null

Default (datetime(‘now’,’locatetime’))

 

33、

insert INTO actor #tablename

ValueS (1,PENELOPE,GUINESS,2006-02-15 12:34:33),

(2,NICK,WAHLBERG,2006-02-15 12:34:33)

 

34、

Insert ignore into

ValueS (1,PENELOPE,GUINESS,2006-02-15 12:34:33)

 

35、

Create table actor_name

As

Select first_name,last_name from actor

 

1. 创建索引

Create unique index unique_idx_firstname on actor(first_name)

 

Create index index_lastname on actor(last_name)

 

1. 临时试图

Create view actor_name_view

As

Select first_name as first_name_v,last_name as last_name_v

From actor

 

1. 增加一列

Alter table actor add column

Create_date datetime not null

Default ‘0000-00000 00:00:00’

 

1. 触发器

Create triggle audit_log

After insert into employee_test  

Begin insert into audit

Values (NEW.id,NEW.NAME)

END

 

1. 删除重复记录，保留小的id

Select emp_no,title,from_date,to_date,min(id) as id

From title_test t

Group by emp_no,title,from_date,to_date

 

Delete  title_test t

Where id not in (select min(id) from title_test group by emp_no)

 

42 更新

Update title_set set from_date=’2001-01-01’and to_date = null

Where to_date = ‘9999-01-01’

 

43、

Repalce into titles_test

as

Select 5,10005,title ,from_date,to_date

From title_test where id = 5

1. 更改表明

Alter table titles_test rename to titles_2017

 

 

1. 创建外检约束

Alter table audit add foreign key(emp_no)

Reference emplyees_test(id)

 

1. 选出和视图一样的数据

Select em.* from emplyees as em,emp_v as ev

Where em.emp_no = ev.emp_no

 

Select * from emp_v

 

1. 获奖员工当前工资增加

Update 

Salaries s

Set salary = salary *1.1

Where s.emp_no in (select emp_no from emp_bonus) and s.to_date=’9999-01-01’