sql学习笔记（一）

• sql学习笔记
  
  • 使用sqlcmd连接数据库
  • sql语句加强

主要是自己的积累和网上的摘抄

sql学习笔记

使用sqlcmd连接数据库

• 直接在终端输入sqlcmd以Windows账户登入默认的数据库实例。
• 使用use database_name连接需要使用的数据库上下文环境
• 输入exit退出sqlcmd环境

sql语句加强

distinct

select distinct--语句返回唯一不同的值

order by

order by 关键字对结果集进行排序

order by 关键字用于对结果集按照一列或者多格列进行排序
order by 关键字默认按照升序对记录进行排列。如果需要按照降序，需要使用desc关键字

select * from websites
order by alexa desc;--按照alexa进行降序排序

select * from websites
order by country,alexa;--先根据country进行降序排序，然后在country降序的基础上降序排序alexa

identity

sql server在建表的时候设置ID字段自增，加入关键字identity(1,1)，表示从1开始增加，每次增加1。

create table users(
    id int identity(1,1) not null, 
    username varchar(20),
    password varchar(20));
 insert into users(username,password) values("小明","123456");
 insert into users(username,password) values("小红","qwerdf");

delete

delete语句用于删除表中的记录

delete * from table_name;--删除表中说有的记录
delete from table_name
where some_column=some_value;--删除表中某条记录等于某值得记录

select top,limit,rownum

select top子句用于规定返回记录的数目，这对于拥有数千条记录的大型数据表来说是非常有用的

--可以是百分比，亦可以是具体的数据(sql server)
select top 50 percent * from websites;--打印表中前50%的记录
select top 2 * from websites;--打印前2条记录

like

like操作符用于在where子句中搜索列中的指定模式

--选取 name 以字母 "G" 开始的所有客户
select * from websites
where name like "G%";
--选取 name 以字母"k"结尾的所有客户
select * from websites
where name like "%k";
-- 选取 name 包含模式 "oo" 的所有客户
select * from websites
where name like "%oo%";

%是通配符,通配符可用于替代字符串中的任何其他字符
* %替代0个或者多个字符
* _替代一个字符
* [charlist]字符列中的任何一个字符
* [^charlist]或者[!charlist]不在字符列中的任何一个字符

--通配符使用的举例
select * from websites
whrer name regexp '[GF]';

in操作符

--选取 name 为 "Google" 或 "菜鸟教程" 的所有网站
select * from websites
where name in("Google","菜鸟教程");

sql 别名

列的sql别名语法

--将column列设置别名alias
select column_name as alias_name
from table_name;

表的sql别名语法

select column_name from table_name as alias_name;

举例

--设置列的别名，在显示的时候列名改变
--将name设置为n,将country设置为c
 select name as n, country as c
 from websites;

使用别名的情况

• 在查询中涉及超过一个表
• 在查询中使用了函数
• 列名称很长或者可读性差
• 需要把两个列或者多个列结合在一起

join

SQL JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段
最常见的sql inner join从多个表中返回满足 JOIN 条件的所有行
sql join的类别
* INNER JOIN：如果表中有至少一个匹配，则返回行
* LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
* RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
* FULL JOIN：只要其中一个表中存在匹配，则返回行

-- inner join
select w.id,w.name,a.count,a.date
from websites as w 
inner join access_log as a
on w.id=a.site_id
order by a.count;

-- left join
-- Websites 作为左表，access_log 作为右表
--LEFT JOIN 关键字从左表（Websites）返回所有的行，即使右表（access_log）中没有匹配
select w.name,a.count,a.date
from websites as w
left join access_log as a
on w.id = a. site_id
order by a.count desc;

right join同left join

--FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果
select w.name,a.count,a.date
from websites as w
full outer join access_log as a
on w.id = a. site_id
order by a.count desc;

select into

SELECT INTO语句从一个表复制数据，然后把数据插入到另一个新表中

-- 语法
CREATE TABLE 新表 SELECT * FROM 旧表 

SELECT *
INTO newtable [IN externaldb]
FROM table1;

SELECT column_name(s)
INTO newtable [IN externaldb]
FROM table1;

INSERT INTO SELECT

NSERT INTO SELECT语句从一个表复制数据，然后把数据插入到一个已存在的表中

INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;

sql的约束

• NOT NULL - 指示某列不能存储 NULL 值。
• UNIQUE - 保证某列的每行必须有唯一的值。
• PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
• FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
• CHECK - 保证列中的值符合指定的条件。
• DEFAULT - 规定没有给列赋值时的默认值

UNIQUE 约束唯一标识数据库表中的每条记录。
UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。
PRIMARY KEY 约束拥有自动定义的 UNIQUE 约束。
请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束

在创建标的时候设置unique约束

--MySql
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
)
--SQL Server / Oracle / MS Access
CREATE TABLE Persons
(
P_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
--如需命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)

使用 alter修改unique约束

--添加
ALTER TABLE Persons
ADD UNIQUE (P_Id);--没有别名

ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)--添加别名

--删除
--MySQL：
ALTER TABLE Persons
DROP INDEX uc_PersonID
--SQL Server / Oracle / MS Access：
ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID

primary 的使用方法同unique

设置foreign key

--添加外键
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)
--修改
ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
--删除
--MySQL：
ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders
--SQL Server / Oracle / MS Access：
ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders

设置check约束

--创建表的时候设置
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)
--添加
ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
--撤销
--SQL Server / Oracle / MS Access：
ALTER TABLE Persons
DROP CONSTRAINT chk_Person
--MySQL：
ALTER TABLE Persons
DROP CHECK chk_Person

设置default约束

--创建表的时候设置
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)
--修改
--MySQL：
ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES'
--SQL Server / MS Access：
ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'SANDNES'
--Oracle：
ALTER TABLE Persons
MODIFY City DEFAULT 'SANDNES'
--撤销
--MySQL：
ALTER TABLE Persons
ALTER City DROP DEFAULT
--SQL Server / Oracle / MS Access：
ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT