SQL语句
一、DDL-数据定义语言
create / alter / drop
创库:create database 库名;
删库:drop database 库名;
创表:create table 表名;
删表:drop table 表名;
改表:alter table 表名 add(增加列) |change/modify (修改列) drop(删除列);
alter table table_name_old rename to table_name_new;--修改表名称
alter table 表名 add 列名 数据类型 约束;--为了让新增的列名不和关键字冲突,可以把列名用反引号括起来
举例:alter table 表名 add 列名 字段类型;
alter table 表名 drop 列名;
-------------------------
alter table 表名 change 列名 列新名 数据类型;
alter table 表名 modify 列名 数据类型;
说明:change和modify的区别:modify不能修改字段名,也就是不能修改列名,只能用来修改字段的数据类型;change可以同时修改字段名和数据类型;
change关键字之后,紧跟着你要修改字段名,然后指定新字段名及类型,语法:
--------------------------
alter table 表名 modify 列名 数据类型 first; --将某列放到第一列,其中first关键字也可以用在add语句中
alter table 表名 modify 列名 数据类型 after 另一列名;--将某列放到另一列的后面
========索引=========
索引分为单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索引包含多个列。
实际上,索引也是一张表,该表保存了逐渐与索引字段,并指向实体表的记录。
但是过多的使用索引也不好,缺点:索然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行插入更新删除操作时,mysql不仅要保存数据,还要保存索引文件。建立索引会占用磁盘的索引文件。
创建索引:CREATE INDEX 索引名 ON 表名(列名);
修改表结构(添加索引):ALTER TABLE ADD INDEX 索引名(列名);
创建表的时候直接指定:
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
);
删除索引:DROP INDEX 索引名 ON 表名;
------------------------------------
唯一索引:它与前面普通索引类似,不同的就是:索引列的值必须唯一,但是允许有空值。如果是组合索引,则列值的组合必须唯一。
创建索引:CREATE UNIQUE INDEX indexName ON mytable(username(length))
修改表结构:ALTER table mytable ADD UNIQUE [indexName] (username(length))
创建表的时候直接指定:
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
UNIQUE [indexName] (username(length))
);
-------------------------------
使用ALTER 命令添加和删除索引
有四种方式来添加数据表的索引:
- ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。
- ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)。
- ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引,索引值可出现多次。
- ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ,用于全文索引。
============
==================================================================================
数据完整性约束
在 SQL 中,我们有如下约束:
- NOT NULL - 指示某列不能存储 NULL 值。
- UNIQUE - 保证某列的每行必须有唯一的值。
- PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列(或两个列多个列的结合)有唯一标识,有助于更容易更快速地找到表中的一个特定的记录。
- FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
- CHECK - 保证列中的值符合指定的条件。
- DEFAULT - 规定没有给列赋值时的默认值。
主键约束: primary key 唯一且不能为空 规定:在每一个表中有且仅有一列有主键约束,不能有多个
1:创表的时候设置主键约束
create table 表名(
列名 int primary key,
...
)
或者是
create table 表名(
列名1 int ,
...
PRIMARY KEY (列名1) //PRIMARY key 约束
)
2:在已有的表上添加主键约束: alter table 表名 add constraint 约束名(可以取PK_字段名) primary key(字段名);
3:删除主键约束 alter table 表名 drop primary key;
唯一约束:
特点:不能重复,可以为空,可以添加多个
关键字:unique
#创表的时候添加
create table 表名(
列名 数据类型 unique,
...
)
#在已经有表的情况下添加:
alter table 表名 add contraint 约束名称 unique(字段名);
#删除唯一约束
alter table 表名 drop constraint 约束名称
主键自增:
特点:从1开始,每次自身加1,只能在创建表的时候添加这个约束,而且必须是主键才可以添加
关键字:auto_increment
create table 表名(
列名 int primary key auto_increment,
...
)
#删除主键自增约束
1:去除自增性 alter table 表名 modify 列名 数据类型;
2:删除主键约束 alter table 表名 drop 列名 primary key;
2:域完整性:--只能在创建表的时候添加
域完整性约束保证字段的数据准确性的
域完整性包括类型约束、非空约束、默认值
1)非空约束
特点:字段不允许为空
关键字:not null
2)默认值
特点:设置默认额值
关键字:default
3:引用完整性:
一张表中通用列的取值必须参考另外一张表的主键
引用完整性有外键约束
1)外键约束:
关键字:foreign key
添加外界约束语法:
alter table 表名1 add constraint 约束名(FK_列名) foreign key(列名) reference 另一张表名(列名);
删除外界约束语法:
alter table 表名1 drop foreign key 约束名;
======================================================
DML 操作是指对数据库中表记录的操作,主要包括表记录的插入(insert)、更新(update)、删除(delete)和查询(select),是开发人员日常使用最频繁的操作。下面将依次对它们进行介绍
一:插入
#不指定字段 --这里需要注意值的排列顺序需要和表的字段顺序一致
insert into 表名 values(值1,值2,...);
#指定字段
insert into 表名 (列1,列2,列3)values(值1,值2,值3);
#批量插入多行,将不同行的值用逗号隔开即可
insert into 表名 (列1,列2,列3)
values
(值1,值2,值3),
(值4,值5,值6);
#复制某表数据到另一张表
insert into 表名1 select * from 表名2;---前提:要求目标表必须存在
1.复制表结构和数据: -- 通过复制另一张表的表结构和数据创建新表(无索引);如果想要索引的话可以使用create table 新表 like 源表;
create table table_name_new as select * from table_name_old;
2:只复制表结构:
create table table_name_new as select * from table_name_old where 1=0;
3:只复制表数据:
当两个表结构一样:insert into table_name_new select * from table_name_old;
当连个表结构不一样:insert into table_name_new(column1,column2...) select column1,column2... from table_name_old;
注意下insert into...select 和select ..into区别:
insert into 新表名 select * from 旧表名 where 条件; --插入一行,要求新表必须存在
select * into table_name_new from table_name_old where 条件; --也是插入一行,要求新表不存在,---mysql不支持该语句,可以使用上面的create table语句来复制一个只包含表结构的新表,然后insert into 语句来插入数据;
二:删除
#删除某行数据
delete from 表名 where 列名=列值;
#如果不加where条件,会删除整张表的数据
delete from 表名;
---以下是删除整张表的几种方式和之间的区别
====delete和trancate、drop的区别=====
DROP TABLE 语句: DROP TABLE 语句用于删除表。DROP TABLE table_name
drop会把整张表的行数据和表结构都删除,且不可恢复
TRUNCATE TABLE 表名
delete from table_name where [conditions]
区别:
1:效果等价,都是删除表的行数据,但是truncate table的效率更高
原因:truncate速度快,而且效率高是因为使用的系统和事务日志资源少。delete语句每次删除一行,并在事务日志中为所删除的每行记录一项。truncate通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放
2:delete from 语句可以删除指定行,但是truncate table 只能删除整张表
3:delete删除可以恢复,truncate不可恢复
三:修改数据
update 表名 set 列名=列值 【where 某列名=某列值】;#where条件最好用的主键列,这样的话可以提高效率,且更准确--在优化表的效率的时候可以考虑的地方
四:查询数据
算数运算符:加减乘除、取模/余(%或者mod关键字)、取整(用div关键字)
比较运算符:返回结果都是布尔值0和1,分别表示false和true; is null / is not null / between ..and (包含边界的) / in / not in
逻辑运算符:and 、or、 !(取反)
语法:
select 字段|表达式 from 表名|视图|结果集
[where条件]
#like模糊查询,和where子句结合使用
%:可以匹配0个或者多个长度的任意字符 举例:WHERE url LIKE '%oo%';
_: 匹配一个字符 举例:WHERE name LIKE '%k';
in: IN 操作符允许您在 WHERE 子句中规定多个值 where column IN (value1,value2,....) 这里的value指的就是列的可能值
[charlist] :字符列中的任何单一字符
[^charlist] 或者【!charlist】不在字符列中的任何单一字符
between :用于选取介于两个值之间的数据范围内的值 举例:where colunmn between value1 and value2; 边界值是包含的
==============
[group by 分组]
[having 分组之后进行检索] 在 SQL 中增加 HAVING 子句原因是,WHERE 关键字无法与聚合函数一起使用
[order by 排序] :order by 列名 (ASC /DESC 升序 /降序)用于对结果集按照一个列或者多个列进行排序
[limit 限制结果] :用来查询想要的行数--limit m,n m:开始的位置,索引从0开始, n:取值的长度
分组查询:group by ..having :分组之后进行检索
#求每个部门的平均工资
1:按照部门的编号就行分组:注意:分组之后,select 后面并不是所有的字段都可以接的,select后面要接的字段一定要和要分组的字段有一一对应的关系 ---分组:会把重复的值去掉,只会保留一个
使用举例:select depno,avg(sal) from emp group by depno having avg(sal) >2000;
--where :条件筛选,where后面不可以接聚合函数;单独使用
--having :可以接聚合函数;必须搭配group by 使用
==========================================
#去重 select distinct 列名 from 表名;
#别名 select 列名 as 新列名 from 表名;
#UNION操作符合并两个或多个select语句的结果,注意,union内部的每个select语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每个select语句中的列的顺序必须相同;
union结果集中的列名总是等于union中第一个select语句中的列名;union不能用于列出所有的值,union只会选取不同的值。可以使用union all 来选取重复的值
#ifnull(列名,0) 判断列值是否为null,如果是null,则赋值为0,用法如下:
select 列1,ifnull(列2,列n) 新列名 from 表名;
================
关联查询:连接查询,可分为:内连接,外连接、自连接
内连接:inner join ... on 就是取交集进行连接
select 表1某列名,表2某列名 from 表1 inner join 表2
on 表1.某列=表2.对应列;
+----+--------------+---------------------------+-------+---------+ | id | name | url | alexa | country | +----+--------------+---------------------------+-------+---------+ | 1 | Google | https://www.google.cm/ | 1 | USA | | 2 | 淘宝 | https://www.taobao.com/ | 13 | CN | | 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN | | 4 | 微博 | http://weibo.com/ | 20 | CN | | 5 | Facebook | https://www.facebook.com/ | 3 | USA | | 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND | +----+---------------+---------------------------+-------+---------+
mysql> SELECT * FROM access_log; +-----+---------+-------+------------+ | aid | site_id | count | date | +-----+---------+-------+------------+ | 1 | 1 | 45 | 2016-05-10 | | 2 | 3 | 100 | 2016-05-13 | | 3 | 1 | 230 | 2016-05-14 | | 4 | 2 | 10 | 2016-05-14 | | 5 | 5 | 205 | 2016-05-14 | | 6 | 4 | 13 | 2016-05-15 | | 7 | 3 | 220 | 2016-05-15 | | 8 | 5 | 545 | 2016-05-16 | | 9 | 3 | 201 | 2016-05-17 | +-----+---------+-------+------------+ 9 rows in set (0.00 sec)
请注意,"Websites" 表中的 "id" 列指向 "access_log" 表中的字段 "site_id"。上面这两个表是通过 "site_id" 列联系起来的。
然后,如果我们运行下面的 SQL 语句(包含 INNER JOIN):
SELECT Websites.id, Websites.name, access_log.count, access_log.date
FROM Websites
INNER JOIN access_log
ON Websites.id=access_log.site_id;
执行以上 SQL 输出结果如下:

外连接:左外连接:left join...on 遍历主表每一条记录去跟从表进行匹配,匹配的话拼接在一起,从表没有匹配项则用NULL填充进结果集中
select * from 主表 left join 从表 on 主表.某列=从表.对应列 ---left左边是主表,右边是从表
右外连接类似。
自连接:比较特殊的内连接,将一张表当成是两张表来使用,需要给表分别取一个别名
select * from 表名 as 别名1 left join 表名 as 别名2 on 别名1.列名=别名2.另一个列名;
不同的 SQL JOIN
在我们继续讲解实例之前,我们先列出您可以使用的不同的 SQL JOIN 类型:
- INNER JOIN:如果表中有至少一个匹配,则返回行
- LEFT JOIN:即使右表中没有匹配,也从左表返回所有的行
- RIGHT JOIN:即使左表中没有匹配,也从右表返回所有的行
- FULL JOIN:只要其中一个表中存在匹配,则返回行
=========
子查询:其实是作为另一个查询语句的条件使用
#单行子查询 :某列名=(查询语句)
#多行子查询:某列名 in (查询语句)
============
高级查询:
1:函数:已经定义好的,可以实现一定功能的代码块
日期函数、数字函数、字符函数、聚合函数
聚合函数:--一般跟分组函数一起使用group by
min():select min(列名) from 表名 [where子句];
max():select max(列名) from 表名 [where 子句];
avg():select avg(列名) from 表名 [where 子句];
count():select count(列 名) from 表名 [where 子句];--统计的是字段的非空值的数量
sum():select sum(列名) from 表名 [where 子句];
日期函数:
month() :获取指定日期的月份值
year():获取年份值
day():获取日值
==========
================
聚合函数aggregate函数: 计算从列中取得的值,返回一个单一的值。
AVG()--返回平均值
COUNT()--返回行数
FIRST() - 返回第一个记录的值
LAST() - 返回最后一个记录的值
MAX() - 返回最大值
MIN() - 返回最小值
SUM() - 返回总和
标量函数scalar函数:
UCASE() - 将某个字段转换为大写
LCASE() - 将某个字段转换为小写
MID() - 将某个文本字段提取字符,mysql中使用
SubString(字段,1, end) - 从某个文本字段提取字符
LEN() - 返回某个文本字段的长度
ROUND() - 对某个数值字段进行指定小数位的四舍五入
NOW() - 返回当前的额系统日期和时间
FORMAT() - 格式化某个字段的显示方式