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() - 格式化某个字段的显示方式

posted @ 2023-03-10 18:18  根号三的泥泞之路  阅读(109)  评论(0编辑  收藏  举报