SQL基础知识巩固

作者: 加禄 | 来源:发表于2018-12-25 15:19 被阅读0次

巩固了下SQL的相关知识。学习网站:www.w3school.com.cn。以下知识皆是来自这个网站。大部分照着敲了一遍，来巩固知识

sql基础

sql语法

sql对大小写不敏感

dml：数据操作语言

select获取数据
update更新数据
delete删除数据
insert into插入数据

ddl：数据定义语言

create database创建数据库
alter database修改数据库
create table创建新表
alter table变更数据库表
drop table删除表
create index创建索引
drop index删除索引

select语句

select语句用来从表中选取数据。结果被存储在一个结果表中(被称为结果集)

语法

select 列 from 表 
select *  from 表//(*)选取所有列的快捷方式

//例
select name,age 
from studentTable

select distinct语句

返回唯一不同的值

select distinct 列名称 
from 表名称

//例
select distinct company
from student

where

用于规定选择的标准，如需有条件地从表中选取数据。可将where子句添加到select语句

select 列名称 from 表名称 where 列 运算符 值
//between 在某个范围内
//like 搜索某种模式
//<> 不等于,有的可以写成 !=
//例
select *
from person
where city='beijing'
//sql使用单引号来环绕文本值(大部分数据库也接受双引号)
//如果是数值请不要使用引号

and和or运算符

用于基于一个以上地条件对记录进行过滤
and和or可以在where 子语句中把两个或多个条件结合在一起

如果第一个和第二个条件都成立，则and运算符显示一条记录
如果第一条和第二条件中只要有一个成立，则or运算算符显示一条记录

select *
from preson
where (firstName='thomas' or firstName='william') and lastname='carter'
//使用圆括号来组成复杂地表达式

order by子句

用于对结果集进行排序。

用于根据指定地列对结果集进行排序
按照升序对记录进行排序
desc可以对记录进行降序

select company,orderNumer 
from table 
order by company,orderNumber

//desc降序
select company,orderNumber 
from table
order by company desc,orderNumber asc
//默认为升序，asc也表示升序

原表

１.jpg

结果

2.jpg
先以字母顺序显示公司名称，并以数字顺序显示序号

insert into

用于向表格中插入新的行

insert into 表名称 values(值1,值2...)
//例 插入新的行
insert into persons values('','','')
//在指定的列中插入数据
insert into persons
(LastName,Address)
values('','')

update

用于修改表中的数据

update 表名称 set 列名称=新值 
where 列表名=某值

//例 更新某一行中的一列
update person
set firstName='fred' 
where lastName='wilson'

//例 更新某一行的若干列
update person 
set address='zhongsan',city='nanjing'
where lastname='wilson'

delete

用于删除表中的行

delete from 表名称 
where 列名称=值

//删除某行
delete from person
where lastname='wilson'

//删除所有行
delete from table_name
//或
delete * from table_name

sql高级教程

top

top子句用于规定要返回的记录的数目
对于拥有数千条记录的大型表来说，top子句非常有用(并非所有数据库系统都支持top子句)

select top number|percent column_name(s) 
from table_name

//ep sql top
select top 2 * 
from persons//选取头两条记录

//ep top persent 
select top 50 percent * 
from persons//选取表的50%的记录

//ep mysql top 
select * 
from persons limit 5

//oracle 语法
select * 
from persons
where rownum <=5

like 操作符

用于在where子句中搜索列中的指定模式

select column_name(s)
from table_name
where column_name like pattern

//ep1
select *
from persons
where city like 'N%'
//'%'可用于定义通配符(模式缺少的字母) 以n开头

//ep2
select * 
from persons
where city like '%g'//以g结束

//ep3
select * 
from persons 
where city not like '%lon%'//不包含lon

通配符

在搜索数据库中的数据时，你看用使用sql通配符

通配符看用代替一个或多个字符
通配符必须和like一起使用

%代替一个或多个字符。
_代替一个字符。
[charlist] 字符列中的任何单一字符
[^charlist]或[!charlist]不在字符列中任何单一字符

select * 
from persons
where city like '[aln]'//城市以a或l或n开头

in

操作符允许在where子句中规定多个值

select column_name(s) 
from table_name 
where column_name in(value1,value2,value3)

//ep
select * 
from persons
where lastName in('adams','carter')

between

选取介于两个值之间的数据范围

//显示adams(包含)和carter(不包含)之间的人
select * from persons 
where lastname
between 'adams' and 'carter'

//显示范围之外的
select * from persons 
where lastname
not between 'adams' and 'carter'

不同的数据库对between...and操作符的处理方式不一样。orcal、mysql两边都包含。

alias(as)

通过使用sql，可以为列名称和表名称指定别名

//表的sql alias语法
select column_name(s)
from table_name
as alias_name

//列的sql alias语法
select column_name as alias_name 
from table_name

//例  表
select po.orderID,p.lastName,p.firstName
from persons as p,product_orders as po
where p.lastname='adams' and p.firstname='john'

//例   列
select lastName as family,firstName as name
from person

join

用于根据两个或多个表中的列之间的关系，从这些表中查询数据
persons表

3.jpg
orders表

4.jpg

//可以引用两个表的方式，从表中获取数据
select persons.lastname,persons.firstname,orders.orderNo
from persons,orders
where persons.id_p=order.Id_p

//使用inner join(内连接)
select persons.lastName,persons.firstname,orders.orders.orderno
from persons
inner join orders
on persons.id_p=orders.id_p
order by persons.lastname

join：如果表中有至少一个匹配,返回行
left join:即使右表中没有匹配，也从左表返回所有行
right join：即使左表中没有匹配，也从右表返回所有的行
full join 只要有一个表中存在匹配，就返回行

select persons.lastname,persons.firstname.orders.orderno
from person 
left join orders
on persons.id_p=orders.id_p
order by person.lastname

5.jpg

right join 也是类似
full join 则是两个表都会显示

union操作符

用于合并两个或多个select语句的结果集
注意union内部的select语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条select语句的列的顺序必须相同

//union 语法
select column_name(s) from table_name
union
select column_name(s) from table_name2
//默认的 union操作符选取不同的值。如果允许重复的值，使用union all


//union all 语法
select column_name(s) 
from table_name
union all
select column_name(s) 
from table_name2
//另外，union集合中的列名总是等于第一个select语句中的列名

select e_name 
from employees_china
union
select e_name from employees_usa

6.jpg

7.jpg

union all 和union命令几乎是等效的。不过union all命令会列出所有的值

select into

从一个表中选取数据，然后把数据插入另一个表中。常用于创建表的备份复件或者用于对记录进行存档

//把所有的列插入新表
select * 
into new_table_name[in externaldatabase]
from old_tablename

//把需要的列插入新表
select column_name(s)
into new_table_name[in externaldatabase]
from old_tablename

//例 向另一个数据库中拷贝表
select *
into person in 'backup.mdb'
from persons

//带有where子句
select lastname,firstname
into persons_backup
from persons
where city='beijing'

create database

用于创建数据库

create database database_name

create table

用于创建数据库中的表

create table 表名称{
    列名称1 数据类型,
    ...
}

//例
create table persons{
    id_p int,
    lastname varchar(255),
    firstname varchar(255),
    address varchar(255),
    city varchar(255)
}

数据类型

8.jpg

sql约束

用于限制加入表的数据的类型
可以在创建表规定约束(通过create语句)

not null 约束强制列不接受null值
unique 唯一（一张表可以有多个unique）
primary key 主键(唯一)(not null)
foreign key 外键指向里一个表的主键
check 限制列中的值的范围
default 插入默认值

//当表已经创建时，如需在id_p成绩unique约束
alter table persons
add unique(id_p)

//撤销unique约束
alter table persons
drop constraint uc_personId

//表已经创建后,增加主键
alter table person
add primary key(id_p)

//撤销主键约束
alter table person
drop constraint pk_personid

//外键
create table orders(
...
id_p int foreign key references persons(id_p)
)

//表已经存在的情况下创建外键
alter table orders
add foreign key(id_p) references persons(id_p)

/撤销外键
alter table orders
drop constraint fk_perorders

//表已经存在的情况下 创建check约束
alter table persons
add check (id_p>0)

//撤销check约束
alter table persons
drop constraint chk_person

//default
create table person(
...
city varchar(255) default 'beijing'
)

//表已经存在创建约束
alter table persons
alter city set default 'beijing'

//撤销default约束
alter table persons
alter column city drop default

create index

用于在表中创建索引。在不读取整个表的情况下，索引使数据库应用程序可以更快地查找数据

索引

用户无法看到索引，它们只能被用来加速搜索/查询

//create index 语法
create index index_name
on table_name(col_name)//column_name

//create unique index 语法
create unique index index_name
on table_name(column_name)

//ep 创建一个名为personindex地检索，在person表地lastname列 
create index personIndex
on person(lastName)

//ep2
create index personindex
on person(lastname,firstname ,desc)

撤销索引、表以及数据库

通过使用drop语句，可以轻松地删除索引、表和数据库

//drop table 删除表()
drop table 表名称

//drop database 删除数据库
drop database 数据库名称

//删除表内地数据，不删除表本身
truncate table 表名称

alter table

用于在已有地表中添加、修改或删除列

//在表中添加列
alter table table_name
add column_name dataatype

//要删除表中的列
alter table table_name
drop column column_name

//改变列表中的数据类型
alter table table_name
alter column column_name datatype

//ep 向表中加一列
alter table persons
add birthday date//birthday的类型是date

//ep 改变数据的类型
alter table persons
alter column birthday year//数据类型是year，可以存放2位或4位格式的年份

//ep 删除person表的birthday列
alter table person
drop column birthday

auto-increment

会在新纪录插入表中时生成一个唯一的数字

//mysql
create table persons(
p_id int not null auto_increment,
...
primary key(p_id))//从1开始，每次加一。在插入数据时就不需要为p_id列规定值(会自动添加一个唯一的值)

//以其他位起始
alter table persons auto_increment=100

//sql
create table persons(
p_id int primary key identity,
...)//sql用identity代替auto-increment

//要规定从20开始，每次加10
p_id int primary key indentity(20,10)

view

视图是可视化的表
在sql中，视图是基于sql语句的结果集的可视化的表。视图包含行和列，就像一个真实的表。视图中的字段。

//create view的语法
create view view_name as
select column_name
from table_name
where condition

//ep
create view[current product list] as
select products
where products
where discontinued=no

//查询
select * from [current product list] 

//sql更新视图 ep
create view[current product list] as
select productid,productname,catgory
from products
where discontinued=no

//sql撤销
sql drop view syntax
drop view view_name

data

mysql data函数

9.jpg

sql data函数

10.jpg

sql data 数据类型

11.jpg

如果希望查询简单更易维护，请不要在日期中使用时间部分

null值

默认地表的列可以存放null值。
无法比较null和0.它们是不等价的
使用is null和is not null操作符来测试null

//带有null值的记录
select lastname,firstname,address from persons
where address is null
//is not null 
select lastname,firstname,address from persons
where address is not null

sql的函数

avg()

12.jpg

select avg(orderprice) as orderAverage
from orders

13.jpg

//查找orderprice高于平均值的顾客
select customer 
from orders
where orderprice>(select avg(orderprice)from orders)

count()

返回指定列的值的数量

//表中记录数
select count(*) 
from table

//计算某顾客的订单数
select count(customer) as customerNilsen 
from orders
while customer='carter'

//记录不同的顾客
select count(distinct customer) as numberofcustomers from orders

first()、last()

返回指定的字段中第一个记录的值

返回指定的字段中最后一个记录的值

max() 、min()、sum()

返回一列中的最大值,null值不包括在计算中
返回一列中的最小值
返回数值列的总数

group by

用于结合合计函数，根据一个或多个列对结果进行分组

select customer,sum(orderprice) 
from orders
group by customer//会把相同名称的customer的orderprice相加放在sum中

having

增加having子句的原因是where关键字无法与合计函数一起使用

select cusomer,sum(orderprice) from orders
group by customer
having sum(orderPrice)<2000

ucase( lcase()

将字段的值转换为大写
将字段的值转换为小写

select ucase(lastname) as LastName 
from persons

mid()

从文本字段中提取字符

//语法
select mid(column_name,start,lenth)
from table_name
//start规定开始的位置(起始是1),length可选，要返回的字符串。如果省略则返回剩余文本

len()

返回文本字段中值的长度

round()

把数值字段舍入为指定的小数位数

//语法
select round(column_name,decimals)
from table_name
//第一个参数是要舍入的字段。第二个参数为规定要返回的小数位数

format()

对字段的显示进行格式化

select format(column_name,format)
from table_name
//column_name 必需。要格式化的字段
//format必须。规定格式