Database之一 sql

DDL:CREATE、ALTER、DROP、DECLARE
DML：SELECT INSERT DELETE UPDATE
DCL:GRANT REVOKE COMMIT ROLLBACK


/****************** 最简单的sql语句  ***************************/
选择：select * from table1 where 范围
插入：insert into table1(field1,field2) values(value1,value2)
删除：delete from table1 where 范围
更新：update table1 set field1=value1 where 范围
查找：select * from table1 where field1 like ’%val%’ 
排序：select * from table1 order by field1,field2 [desc]
总数：select count * as totalcount from table1
求和：select sum(field1) as sumvalue from table1
平均：select avg(field1) as avgvalue from table1
最大：select max(field1) as maxvalue from table1
最小：select min(field1) as minvalue from table1


/****************** alter  ***************************/
//增加一列
alter table abc add column birthday date;


//删除一列
alter table abc drop column birthday;


//修改某一列的长度
alter table abc modify column aa varchar(200);


//修改某一列的字符集
alter table abc modify column aa character set utf8;


//修改某一列的名称
alter table abc change column aa bb;


//为表添加索引
alter table abc add index _uid(uid);


//删除索引
alter table abc drop index _uid;


/****************** select  ***************************/
//in not in
select * from abc where uname in (select uname from abd);
select * from abc where uname not in (select uname from abd);


//between
select * from abd where age between 20 and 45;


//not exists 选择表一中存在而表二中不存在的记录
select * from abc where not exists (select * from abd where abc.uid = abd.uid);


//limit
select * from abc order by uid limit 3; == select * from abc order by uid limit 0,3;


/****************** insert  ***************************/
//插入一条数据
insert into abc(uname) values('aa');


//批量插入语句
insert into abc(uname) values('aa'),('bb');


//插入其他表中的数据
insert into abd select * from abc;



/****************** not classified  ***************************/
//连接数据库
mysql -h172.16.22.55 -uroot -p123;


//显示所有数据库
show databases;


//进入某个数据库
mysql use test;


//显示所有表
show tables;


//查看表结构
describe aa;


//备份数据库
mysqldump -uroot -p123 test>test.sql;


//还原数据库
mysqldump -uroot -p123 test<test.sql;


//执行一个脚本
mysql> use test;
mysql> source d:/aa.sql;


//创建数据库
careate database tt default character set utf8 default collate utf8_general_ci;


//删除数据库
drop databases if exists tt;


//创建表
create table abc(uid int(10) primary key auto_increment, uname varchar(64)) character set utf8);


--mysql数据库
//通过现有表创建新表--不存在表
create table abd select * from abc;
//已存在表
insert into abd select * from abc;


//--SQLServer
select * into abd from abc


//显示创建表的sql语句
show create table abc;


//查看mysql的版本信息
select version();


//查看当前使用的数据库
select database();


//查看数据库中的存储过程
select name from mysql.proc where db='数据库名'


//显示创建数据库中某存储过程的创建语句
show create procedure 数据库名.存储过程名;


//修改表名
rename table aa to abc;


//创建视图
create view pview as select a.uid,a.uname,b.age from abc a, aa b where a.uid=b.uid;
//删除视图
drop view if exists pview;