sql语句学习总结

先总结

sql语句按W3Cschool上分类有数据库操作语言和数据库定义语言两个部分组成，根据那那种分法可以让我们更好的理解SQL，另外自己在网上找到一张图片，可以让我更好的写出SQL语句。在后期的学习中在一一在每条SQL语句下补上学习心得。

mysql执行顺序.png
　　　　　　　　　　　　　　　mysql执行顺序

SQL包括两个部分：数据操作语言 (DML) 和 数据定义语言 (DDL)。

DML部分：

• SELECT - 从数据库获取数据
• UPDATE - 更新数据库数据
• DELETE - 删除数据库数据
• INSERT INTO - 向数据库中插入数据

DDL重要部分：

• CREATE DATABASE - 常见数据库
• ALTER DATABASE - 修改数据库
• CREATE TABLE - 创建表
• ALTER TABLE - 修改表
• DROP TABLE - 删除表
• CREATE INDEX - 创建索引
• DROP INDEX - 删除索引

数据库

• show database 查看所有数据库
• use database_name 选数据库
• show tables 查看指定数据库中的所有表
• rename table old_name to new_name 修改表名

基础用法

1.select语句：从表中选取数据，结果放在一个结果表中
　　　　　　　SELECT 列名称 FROM 表名称

2.select distinct：从表中选出唯一的值
　　　　　　　SELECT DISTINCT 列名称 FROM 表名称

3.where 子句：有条件的从表中选择数据
　　　　　　　SELECT 列名称 FROM 表名称 WHERE 列 运算符 值
　　　　　　　其中操作符between表示两者之间，like表示按某种搜索模式

４.and & or：基于一个以上的条件对记录进行过滤，用于where子句中

5.order by:用于根据指定的列（可以多列）对数据结果集进行排序；倒序DESC，ASC
　　　　　 正序。

6.insert into：向表中插入新的行
　　　　　　　INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

7.update：修改表中的数据
　　　　　　UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

８.delete：删除表中的行
　　　　　 DELETE FROM 表名称 WHERE 列名称 = 值

9.limit：规定要返回记录的数目
　　　　SELECT * FROM TABLE_NAME LIMIT NUM

---

where子句的各种操作

10.like:在where的子句中搜索列的指定模式
　　　　用好通配符：
　　　　　　　　%：替代一个或多个字符
　　　　　　　　－:替代一个字符
　　　　　　　 　[ ABC]:ABC中的任何一个
　　　　　　　 　[!ABC]:不包括ABC任何一个
11.in：规定where子句中规定多个值

12.between and：where子句中选取介于两个值之间的范围（mysql包括两端）

13.alias：为列名称和表名称建立别号
　 　　表的alias语法：select column_name from table_name as alias_name　
　　　列的alias语法：select column_name as alias_name from table_name

---

join操作

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

15.inner join：内连接，在表中存在至少存在一个匹配时，该关键字返回行
　　　　　　　SELECT column_name(s)　
　　　　　　　FROM table_name1
　　　　　　　INNER JOIN table_name2
　　　　　　　ON table_name1.column_name=table_name2.column_name

16.left join：左链接，此关键字会从左表(table_name1)中返回所有行，即 　　　　　　　使在有右表(table_name2)中没有匹配
　　　　　　SELECT column_name(s)
　　　　　　FROM table_name1
　　　　　　LEFT JOIN table_name2
　　　　　　ON table_name1.column_name=table_name2.column_name

17.right join:右链接，此关键字会在右表(table_name2)返回所有行，即使在左表
　　　　　　(table_name1)中没有匹配的行

18.full join:只要其中某个表存在匹配，此关键字就会返回左右表均有的行

多表操作

19.union：用于合并两个或者多个select语句，使用“union all”可以包括重复的值

20.select into：从一个表中选取数据插入另一个表，主要用于备份数据 　　　　　　　ＳELECT */column_name
　　　　　　　 INTO new_table_name [ in externaldatabase]
　　　　　　　 FROM table_name1
　　　　　　　 INNER JOIN table_name2
　　　　　　　ON Person.Id_P = Orders.Id_P

21.CREATE DATABASE database_name:创建数据库

22.CREATE TABLE table_name(
　　　　　　列名称1，列类型
　　　　　　列名称2，列类型
　　　　　　列名称3，列类型
)

sql数据类型.PNG

SQL约束

约束：用于显示加入表中列的数据类型，可在建表时（CREATE TABLE）或者建表后（ALTER TABLE）

23.NOT NULL：强制不接受NULL值，如果不包括值时就不能插入

24.UNIQUE：唯一标识数据库中的每条记录。

• UNIQUE（Id_P）
• CONSTRAINT uc_PersonID UNIQUE(Id_P, lastname)
• ALTER TABLE persons ADD UNIQUE(Id_P)
• ALTER TABLE persons ADD CONSTRAINT us_PersonID UNIQUE( Id_P, lastname )
• ALTER TABLE persons DROP INDEX person_id

25.PRIMARY KEY：用法与上面一样，但还是KEY每个表只有一个，而unique可以有多个

• PRIMARY KEY (id_p)
• CONSTRAINT us_personID PRIMARY KEY (id_p，last name)
• ALTER TABLE persons ADD PRIMARY KEY (id_P)
• ALTER TABLE persons ADD CONSTRAINT uc_personId PRIMARY KEY (p_id, person_id)
• ALTER TABLE persons DROP PRIMARY KEY

26.FOREIGN KEY：指向另一个表中的PRIMARY KEY

• FOREIGN KEY (p_id) REFERENCES persons (p_id)
• CONSTRAINT uc_personID FOREIGN KEY (p_id) REFERENCES persons (p_id)
• ALTER TABLE orders ADD FOREIGN KEY (p_id) REFERENCES persons (p_id)
• ALTER TABLE orders ADD CONSTRAINT uc_personID FOREIGN KEY (p_id) REFERENCES persons (p_id)
• ALTER TABLE orders DROP FOREIGN KEY (p_id)

27.CHECK:用于限制列中值的范围

• CHECK (ID_p > 0)
• CONSTRAINT chk_person CHECK( ID_P >0 AND city = 'Sandnes')
• ALTER TABLE persons ADD CHECK (ID_p > 0)
• ALTER TABLE persons ADD CONSTRAINT chk_persons CHECK( ID_P >0 AND city = 'Sandnes')
• ALTER TABLE persons DROP CHECK chk_person

28.DEFAULT:用于向列中插入默认值

• DEFAULT "值"
• ALTER TABLE persons
  ALTER City Set DEFAULT "值"
• ALTER TABLE persons
  ALTER City DROP DEFAULT

引用

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

29.CREATE INDEX 语法

• 普通的
  CREATE INDEX index_name
  ON table_name(column_name)
• 唯一的
  CREATE UNIQUE INDEX index_name
  ON table_name(column_name)

30.DROP语法
ALTER TABLE table_name DROP INDEX index_name
TRUNCATE TABLE 表名称——清空表

31.ALTER TABLE：用于在已有的表中添加、修改或者删除列

• 增加列
  ALTER TABLE table_name
  ADD column_name datatype
• 删除列
  ALTER TABLE table_name
  DROP COLUMN column_name
• 改变列
  ALTER TABLE table_name
  ALTER COLUMN column_name datatype

32.AUTO-INCREMENT：自动创建主键值

33.CREATE VIEW:创建视图

• 创建视图
  CREATE VIEW [view_name] AS
  SELECT column_name
  FROM table_name
  WHERE condition
• 撤销视图
  DROP VIEW vie_name
• 更新视图
  CREATE OR REPLACE VIEW [view_name] AS
  注释：视图储存的是一个sql语句，更新视图只能对没有计算过的视图进行操作，此处有一个algorithm=merge，temptable

34.mysql的Date数据类型

• DATE - 格式 : YYYY-MM-DD
• DATETIME - 格式: YYYY-MM-DD HH:MM:SS
• TIMESTAMP - 格式: YYYY-MM-DD HH:MM:SS
• YEAR - 格式: YYYY 或 YY

mysql的date函数.PNG

35.NULL值是可选字段，必须用IS NULL 和IS NOT NULL 操作符

• IFNULL(UnitsOnOrder,0)，null时用-0取代
• COALESCE(UnitsOnOrder,0)

SQL函数

    SELECT function(列) FORM 表

基本类型

• Aggregate 函数：此函数操作面向一系列的值，并返回单一的值
• Scalar 函数：此函数操作单一的值，返回基于输入值的但一值

• 1.AVG函数:求平均数
  SELECT AVG(column_name) from table_name

• 2.COUNT函数：返回指定列的值的数目
  SELECT COUNT(column_name) from table_name
  SELECT COUNT(DISTINCT column_name) from table_name

• 3.FIRST函数：返回指定字段中的第一个记录的值
  SELECT FIRST(column_name) from table_name

• 4.LAST函数：返回指定字段中指定的最后一个记录的值
  SELECT LAST(column_name) from table_name

• 5.MAX函数：返回指定列中最大的值，不包括NULL
  SELECT MAX(column_name) from table_name

• 6.MIN函数：返回指定列中最小的值，不包括NULL
  SELECT MIN(column_name) from table_name

• 7.SUM函数：返回数字列的总数
  SELECT SUM(column_name) from table_name

• 8.Group by函数：结合合计函数，根据一个或多个列对结果集进行分组
  SELECT column_name, aggregate_function(column_name)
  FROM table_name
  WHERE column_name operator value
  GROUP BY column_name1，column_name2
  注释：group没有顺序之分，只要不同就要按不同的组显示

• 9.Having子句：筛选结果

• 10.UCASE函数：把字段的值转换为大写
  SELECT UCASE(column_name) FROM table_name

• 11.LCASE函数：把字段的值转换为小写
  SELECT LCASE(column_name) FROM table_name

• 12.MID函数：从文本字段中提取字符
  SELECT MID(column_name,start,length) FROM table_name

• 13.LEN函数：返回文本字段中值的长度
  SELECT LEN(column_name) FROM table_name

• 14.ROUND函数：用于把数字字段舍入为指定的小数位数
  SELECT ROUND(column_name,decimals) FROM table_name

• 15.NOW函数：返回当前的日期和时间
  SELECT NOW() FORM table_name

• 16.FORMAT函数：用于对字段的显示进行格式化
  SELECT FORMAT(column_name,format) FROM table_name

唯一性索引：该数据列可以为空，但是只要有数据就必须保证此列取值的唯一性

数据类型：decimal 和 numeric（相同的类型）类型用于保存必须为确切精度的值，如货币数据，最大位数为65位。decimal(5,2):5位，小数点后2位

SQL模式：严格和不严格
查看模式：select @@sql_mode;
设置模式：set global sql_mode= '';

char和varchar区别：比较时var去掉空格，varchar不去掉空格；

找出非法ENUM列：
select * from tab_name where enum_col=0;