目录
1.创建数据库
CREATE DATABASE 数据库名字;
2.删除数据库
DROP DATABASE database-name;
3.创建新表
CREATE TABLE [IF NOT EXISTS] `表名`(
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
........
`字段名` 列类型 [属性] [索引] [注释]
)[表类型] [字符集设置] [注释]
4.根据已有的表创建新表:
create table tab_new like tab_old (使用旧表B创建新表A)
5.删除新表
drop table tabname;
6.增加一个列
alter table tabname add column column_name type
7.主键
添加主键: Alter table tabname add primary key(col)
删除主键: Alter table tabname drop primary key
-- 一个数据表只可以有一个主键,所以不存在删除某一列的主键.
8.索引
创建索引:create [unique] index idxname on tabname(col….)
删除索引:drop index idxname
-- 注:索引是不可更改的,想更改必须删除重新建。
9.视图
创建视图:create view viewname as select statement
删除视图:drop view viewname
10.简单的基本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 ’%value1%’
排序:select * from table1 order by field1,field2 [desc]
-- desc:降序,asc:升序
11.聚合函数
总数: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