文章目录
查看数据库
\l
查看表
\d
使用某个数据库
\c+databaseName
创建表
CREATE TABLE Cambricon(
NAME CHAR(50) NOT NULL,
ID INT PRIMARY KEY NOT INT,
LOCATION TEXT NOT NULL
);
删除表
DROP TABLE tableName;
模式
表的集合,将表组织成逻辑组,便于管理
创建模式
CREATE SCHEMA schemaName;
在模式中创建表
CREATE TABLE schemaName.tableName;
查看数据库下面所有的schema
select * from information_schema.schemata;
查看一个schema下面所有的表
select * from information_schema.tables where table_schema = ‘schema_name’;
插入数据
insert into tableName (属性1,属性2)values (值1,值2);
查询
select 属性1 from tableName;
运算符
读取cambricon中工资大于50000的数据
select * from cambricon where salary > 50000;
读取cambricon中年龄小于50,工资大于50000的员工名字
select name from cambricon where salary > 50000 and age < 50;
update
给zhangyue升职加薪
update cambricon set salary=50000,position="manager" where name="zhangyue"
delete
删除整张表
delete from cambricon;
删除某个元素
delete from cambricon where name="zhangyue"
like、%、_
模糊查询,%通配符,_占位符
查询第二位是h后面不管不是啥有几位的人
select * from cambricon where name like '_h%'
查询不管前面是啥有几位,倒数第二位是y的人
select * from cambricon where name like '%y__'
limite、offset
从第三位开始查询三个记录
select * from cambricon limite 3 offset 2;
排序
公司年龄大于20的降序排列
select * from cambricon where age > 20 order by age desc
desc降序 asc升序
分组group by
按照部门名称升序,输出部门总工资
select department,sum(salary)from cambricon group by department order by department asc
group by在select之后,在order by之前
having 筛选分组后的各组数据
筛选分组后名字小于5的人
select name from cambricon group name having count(name) < 5;
DISTINCT 去重
select distinct name from cambricon;