主要內容:
- DDL類語句
- 創(chuàng)建表
- 刪除表
- DML類語句
數據的增刪改查語句
DDL類
1,數據庫管理命令
查看數據庫中的可用數據庫
.database/.databases
查看數據庫中的表
.tables
查看表的完整信息
.schema company
編程的時候就不能用點命令了
select tb1_name from sqlite_master where type = 'table'
顯示命令:
.header on 打開字段名
.mode column 每個列10個字符的寬度
.mode width 各個列的寬度可以自定義設置
.mode line 打開行模式
2,創(chuàng)建表
create table company(
id int primary key not null,
name text not null,
age int not null,
address char(50),
salary real
);
create table DEPARTMENT(
id int primary key not null,
dept char(50) not null,
emp_id int not null
);
3,刪除表
drop table databse_name.table_name
DML類操作
1,插入數據
insert into table_name [] values ();
為所有字段添加值,可以省略掉指定列名稱的命令
insert into company (id,name,age,address,salary)
values (1,'mayun',55,'hangzhou',20000.00);
insert into company (id,name,age,address,salary)
values (2,'mahuateng',49,'shenzhen',55000);
insert into company (id,name,age,address,salary)
values (3,'renzhengfei',70,'shenzhen',10000);
insert into company (id,name,age,address,salary)
values(4,'liyanhong',48,'shanxi',30000);
insert into company (id,name,age,address,salary)
values(5,'mali',34,'hebei',1000000.00);
insert into company values (6,'xxx',18,'China',10000.00);
還可以通過使用其它表來填充另一個表
2,查看數據
select * from tableName;
select column1,column2,...,columnN from tableName;
where條件
where子句用于指定從一個表或者多個表中獲取數據的條件
如果滿足給定的條件,為真,則從表中返回特定值,用于過濾記錄
一般使用比較運算符或者邏輯運算符指定過濾條件
實例:
select * from company where age>=25 and salary >10000.00;
select * from company where age>=25 and salary >20000.00
select * from company where age is not null;
select * from company where name like 'ma%%';
select * from company where name glob 'li_____' *號能在glob里面使用
select * from company where age in (55,34);
select * from company where age between 1 and 60;
select age from company
where exists (select age from company where salary>10000.00);
條件查詢: 通過子查詢,給了age一個條件,還有另外一種寫法
select * from company
where age >(select age from company where salary >10000.00);
模糊查詢like: %(多個字符) _(單一字符)的使用
select column_list from tableName where column like '通配符模板';
模糊查詢glob: *(多個字符) ?(單一字符)的使用 (大小寫敏感)
limit限制提取的行數
offset 在哪個位置開始取
排序: order by
select column_list from table_name
where condition
order by column1,column2,...,columnN ASC|DESC
group by查詢:
放在where條件后,order BY子句之前
目的是使用函數對分組后的記錄做匯總
having過濾查詢:
select column_list
from where
group by
having #注意這里位置
order by
實例:
select * from company
group by name
having count(name)<6
select * from company
group by name
having count(name)>6
去重查詢:
distinct
select distinct column1,column2,...,columnn
from table_name
where condition
3,修改數據
update table_name
set column1=value1,column2=value2,...,columnN = vlaueN
[where condition];
實例:
update company set address='China' where id=5;
4,刪除數據
delete from company where id = 6;
delete from company; #刪除全部
3,運算符
算數運算符: + - * / %
比較運算符:
== =
!= <>
< > >= <= !< !>
邏輯運算符:
and
or
not
between...and...: 最大值和最小值范圍內的搜索值
in: 某個值與一系列指定列表的值比較
not in:
like: 某個值域通配運算符的相似值你行比較
glob: 同like,不過是大小寫敏感的
is : =
is not: !=
||: 連接運算符
unique: 搜索指定表中每一行,確保唯一性
exists: 在滿足一定條件的指定表中搜索行的存在
位運算符
|
&
~
<<
>>
select 1|0
4,表達式
布爾表達式
where之后的條件
數值表達式
select (1+1)
select 1+1;
select (1+1) as addxxxxxx; 注意不能用add
日期表達式
select current_timestamp;
小馬哥正在針對Python的所有常見知識進行匯總,更會有大量實戰(zhàn)項目不斷補充進來.
點擊-->全棧工程師養(yǎng)成---Python內容導航頁<--查看所有Python內容
