sql 学习笔记

sql基础知识点

1.查询 select

1.基本查询

select * from table_name;     //选取所有列
select name from table_name;  //选取name一列   name:列名   table_name 表名称
select name,age from table_name;//选取name,age 两列 

2.where条件查询

SELECT 列名称 FROM 表名称 WHERE 列 运算符 值
select * from table_name where name="hj";

运算符：

操作符	描述
=	等于
<> / !=	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于
BETWEEN	在某个范围内
LIKE	搜索某种模式

3.and 和 or

当有多个条件时使用and 或 or 连接起来

select * from table_name a>1 and b>1; //and 第一个条件 和 第二个条件都得成立
select * from table_name a>1 or b>1;  //or 第一个条件 和 第二个条件有一个成立就可以

4.order by 排序

默认排序是 按 升序号 asc
需要降序 可以使用 desc

select * from table_name order by name;  //按顺序显示name 
select * from table_name order by id desc; //按id降序排列
select * from table_name order by name desc,age asc; //按降序显示name 并按顺序显示age

5.distinct 列出不同值

select distinct name from table_name ; //返回name不同的值  （相同name的值只会返回一条）

6.like

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

“%” 可用于定义通配符（模式中缺少的字母）

select * from users where nickname like '黄%';//找出users表中nickname以'黄'开头的所有列
select * from users where nickname like '%军';//找出users表中nickname以'军'结尾的所有列
select * from users where nickname like '%黄%';//找出users表中nickname中含有'黄'的列

7.通配符

通配符必须与like运算符一起使用

1.’%’ 代替一个或多个字符

select * from users where nickname like '王%'; //以王开头  王*  王**  王***

2.’_'仅代替一个字符

select * from users where nickname like '王_';//仅能查出 王*

3.’ [abc] '字符列中任何单一字符

select * from users where nickname like '[abc]%';//从users表中找出nickname以‘a’或‘b’或‘c’开头的数据

4.’[ ^ abc]’ 或者 ‘[!abc]’ 不在字符列中的任何单一字符

select * from users where nickname like '[!abc]%';//不以‘a’或‘b’或‘c’开头的数据

8. in 规定多个值

select * from users where shop_id in (2,8);//查找shop_id是2或者8的数据

9.between…and 选取介于两个值之间的数据范围

select * from table_name where amount between 'A' and 'B';
select * from users where amount between 100 and 200; //查找amount在100到200之间的数据

注意：不同数据库对between … and 的处理是不同的

​ 有的 含头不含尾部； 有的含头含尾 ；有的不含头不含尾部； 使用时先检测是如何使用的

要显示不在范围之内的数据 在前面加 not就可以

select * from table_name where amount not between 'A' and 'B';

10.Alias (AS) 为列名和表名指定别名

select * from table_name AS table_1 ;  //为表名指定别名
select name AS nickname from table_name ; //为列名指定别名

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

连接方式：
inner join :内连接 只连接匹配的行
JOIN: 如果表中有至少一个匹配，则返回行
LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
FULL JOIN: 只要其中一个表中存在匹配，就返回行

1.inner join

inner join 与 join相同，返回的是两个表的交集。

select * from table1 INNER JOIN table2 on table1.a =table2.b;

2.left join

select * from table1 LEFT JOIN table2 on table1.name = table2.nickname;

LEFT JOIN 返回 左表（table1）的全部行 和 右表（table2）满足on 条件的行

3.right join

select * from table1 RIGHT JOIN table2 on table1.name = table2.nickname;

RIGHT JOIN 返回右表的全部行和左表满足on条件的行

4.full join

在某些数据库中， FULL JOIN 称为 FULL OUTER JOIN

full join 是在特定的数据库中才能使用的关键词，在heidiSQL中full join 错误。

select * from table1 FULL JOIN table2 on table1.name = table2.name;

从左边和右边的表里返回所有的行

12.union 和union all

UNION 用于合并两个或多个select语句的结果集

union 选取不同的值；允许重复值 使用union all.

select name from table_name1
UNION
select name from table_name2;

13. isnull()

SELECT * FROM suppliers WHERE isnull(main_product);
查找main_product值为null的数据

2.插入 insert

语法

INSERT INTO 表名称 VALUES (值1, 值2,....)

我们也可以指定所要插入数据的列：

INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

insert into table_name (name,age) values ('hj',34);

1.NOT NULL

约束强制不接受NULL值,如果不向字段添加值，就无法插入新记录或者更新记录

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

2.unique 约束

UNIQUE 约束唯一标识数据库表中的每条记录

UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。

PRIMARY KEY 拥有自动定义的 UNIQUE 约束。

请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (Id_P)
)

3.primary key 主键

唯一标识数据库表中的每条记录。

主键必须包含唯一的值 ,不能包含 NULL 值,每个表都应该有一个主键，并且每个表只能有一个主键。

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (Id_P)
)

4.auto increment 新记录插入表中时生成一个唯一的数字

插入新记录时，自动地创建主键字段的值

CREATE TABLE Persons
(
P_Id int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)

3.修改 update

语法：

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

update table_name set address='cs' where name='hj';

4.删除 delete

语法

DELETE FROM 表名称 WHERE 列名称 = 值

delete from table_name where id=8;  //删除id 等于8的数据
delete from table_name;   //删除所有的行
delete * from table_name; //删除所有的行

5.备份复件 select into

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

select * into new_table_name  from table_name;
select name,city into persons_backup from persons where city='beijing';
从persons表中提取住在beijing的人的信息，创建了一个带有两个列的名为persons_backup的表

6.创建数据库

create database database_name;  //创建名为database_name的数据库

7.创建数据表

CREATE TABLE 表名称
(
列名称1 数据类型,
列名称2 数据类型,
列名称3 数据类型,
....
)

CREATE TABLE Persons
(
Id_P int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

8.数据类型

数据类型	描述
integer(size)int(size)smallint(size)tinyint(size)	仅容纳整数。在括号内规定数字的最大位数。
decimal(size,d)numeric(size,d)	容纳带有小数的数字。“size” 规定数字的最大位数。“d” 规定小数点右侧的最大位数。
char(size)	容纳固定长度的字符串（可容纳字母、数字以及特殊字符）。在括号中规定字符串的长度。
varchar(size)	容纳可变长度的字符串（可容纳字母、数字以及特殊的字符）。在括号中规定字符串的最大长度。
date(yyyymmdd)	容纳日期。

9.删除

drop table table_name;  //删除表
drop database database_name;//删除数据库
truncate table table_name ;//删除表中的数据

10.Date

函数	描述
NOW()	返回当前的日期和时间
CURDATE()	返回当前的日期
CURTIME()	返回当前的时间
DATE()	提取日期或日期/时间表达式的日期部分
EXTRACT()	返回日期/时间按的单独部分
DATE_ADD()	给日期添加指定的时间间隔
DATE_SUB()	从日期减去指定的时间间隔
DATEDIFF()	返回两个日期之间的天数
DATE_FORMAT()	用不同的格式显示日期/时间

SELECT NOW();
SELECT CURDATE();
SELECT CURTIME();
SELECT DATE('2018/08/01 12:56:23');
SELECT EXTRACT()
SELECT DATEDIFF('2018/12/26','2018/12/15');
SELECT DATE_SUB('2018/12/25',INTERVAL 24 hour)
SELECT DATE_ADD('2018/12/25',INTERVAL 24 hour)
SELECT EXTRACT(HOUR FROM '2018/12/31 12:32:25')
SELECT DATE_FORMAT('2018/12/31 12:32:25','%Y-%m-%d %H:%i:%s')

11.函数

1.avg( ) 返回某列的平均值 （不包括 NULL 值）

select avg(amount) from orders; 

2.count( ) 返回某列的行数（不包括 NULL 值）

select count(amount) from orders;

3.count( ) 返回被选行数

select count(*) from orders;

4.max()返回某列最高值

select max(amount) from orders;

5.min() 返回某列最低值

select min(amount) from orders;

6.sum() 返回某列的总和

select sum(amount) from orders;

7.group by() 根据一个或多个列对结果集 进行分组

select name,sum(amount) from orders group by name;
group by 将相同name的数据的amount 合并在一起

8/1 -8/12 易默愁 所属会员 个数 配送个数