mysql 语法

 create table test (
id int primary key,
name varchar(10),
age int
)

insert into test values(1,'jim',22);
insert into test values(2,'kate',22);
insert into test values(3,'hanmeimei',22);
insert into test values(4,'jack',24);

##创建视图
create view t_view as select * from test;
select * from t_view where id = 2;
##添加列
alter table test add column( note varchar(200));

insert into test values(5,'chenglong',52,'live in hongkong');
##删除列
alter table test drop column note;

##使用默认
create table default_test(
id int auto_increment primary key,
name varchar(10),
address varchar(100) default  'china'
)
success!
insert into default_test values(null,'chenglong');
ERROR 1136 (21S01): Column count doesn't match value count at row 1

insert into default_test values(null,'jim',null);
insert successful! but the result is null.

insert into default_test(id,name) values(null,'andylao');
success

##使用check
create table check_test(
id int auto_increment primary key,
name varchar(10),
age int  check(age>=0 and age<=100)
);
insert into check_test values(null,'jim',20);
insert into check_test values(null,'kate',20);
insert into check_test values(null,'weihua',1000);##一样能插入
##我在网上看了下，好像别人说也是这样，只是起摆设作用，做好前台控制

insert into check_test values(null,'test',20);
insert into default_test values(null,'test','taiwan');

##分组 group by 与order by
create table group_test (
id int auto_increment primary key,
name varchar(20),
time date,
earn float
);
insert into group_test values(null,'andylao',now(),200);
insert into group_test values(null,'andylao',now(),100);
insert into group_test values(null,'andylao',now(),-100);
insert into group_test values(null,'chenglong',now(),2000);
insert into group_test values(null,'chenglong',now(),1000);
insert into group_test values(null,'chenglong',now(),5000);
insert into group_test values(null,'chenglong',now(),-2000);
insert into group_test values(null,'周星驰',now(),1200);
insert into group_test values(null,'周星驰',now(),1200);

select name, max(earn),min(earn),avg(earn) from group_test group by name order by id;
##注意：group by 与order by 同时出现时 order by 要放在最后面。
##注意：同时出现having的时候，order by 也要出现在最后

 

##not in and not exists

exist 是一种存在性测试，
1 不能匹配1列或多列
2 只能用于相关子查询

select * from group_test where id not in (select id from check_test);
select id,name,earn from group_test where exists (
select * from check_test where id<4
);
##所有结果都被查出了

select id,name,earn from group_test g where exists (
select * from group_test where g.name = name
 group by name
having avg(earn)<1000
);

##复制表
select * into copy_test from group_test;
##下面两个要表先存在
create table copy_test (id int,name varchar(20),earn float);
insert into copy_test(id,name,earn) select id,name,earn from group_test;

 

##创建触发器
##基本格式：CREATE TRIGGER trigger_name trigger_time trigger_event    ON tbl_name FOR EACH ROW trigger_statement

##例1
create trigger triger_test before insert on group_test
for each ROW
insert into group_test values(null,'test',now(),123.00)

##例2
CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(  a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,  b4 INT DEFAULT 0);

 CREATE TRIGGER testref BEFORE INSERT ON test1
 FOR EACH ROW
 INSERT INTO test2 SET a2 = NEW.a1 ;

##复制表
create table new_table as select  name ,age from test;
##下面两个要表先存在
select name,age into new_table from test;
insert into new_table select name ,age from test;

 

use master
select name ,DB_ID(NAME) as DB_ID from sysdatabases order by dbid

master  DB_ID
1       master
2 tempdb
3 model
4 msdb
5 pubs
6 Northwind
7 zhouyong

 

insert into test values('def')
insert into test values('g')
insert into test values('h')

declare @mycount int
set @mycount = 0
while(@mycount<26)
begin
insert into test values(@mycount+ASCII('a'))
set @mycount = @mycount+1
end

select * from test

select top 10 * from test

select top 10 percent * from test

-- 分页1
select top 5 * from test where (id not in (
select top 10 -- 10= 5*2 表示第二页
id from test order by id ) ) order by id

-- 分页2
--select top 5 * from test where id > 10

select top 5 * from test where id >(
select max(id) from (
select top 10 -- 10= 5*2 表示第二页
id from test order by id ) as T
) order by id

select max(id) from (
select top 10
id from test )as T