sql整理

1一、数据库操作

1、创建数据库：CREATE DATABASE 数据库名;

2、删除数据库：drop database <数据库名>;

3、选择数据库：

[root@host]# mysql -u root -p
Enter password:******
mysql> use test1;
Database changed

 

二、数据表操作

1、创建表

CREATE TABLE table_name (column_name column_type);

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、删除表：DROP TABLE table_name ;

 

三、数据操作

1、插入数据

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

 

2、查询数据

ELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]

• 查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
• SELECT 命令可以读取一条或者多条记录。
• 可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
• 可以使用 WHERE 语句来包含任何条件。
• 可以使用 LIMIT 属性来设定返回的记录数。
• 可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。

 

3、更新数据

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

• 可以同时更新一个或多个字段。
• 可以在 WHERE 子句中指定任何条件。

 

4、删除数据

DELETE FROM table_name [WHERE Clause]

• 如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。
• 可以在 WHERE 子句中指定任何条件

 

5、LIKE子句

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

 

6、UNION

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

• expression1, expression2, ... expression_n: 要检索的列。
• tables: 要检索的数据表。
• WHERE conditions: 可选， 检索条件。
• DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。
• ALL: 可选，返回所有结果集，包含重复数据。
• UNION ALL 返回所有结果集，包含重复数据；UNION 默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。

 

7、排序

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]

• 可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。
• 可以设定多个字段来排序。
• 可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升序排列。
• 可以添加 WHERE...LIKE 子句来设置条件。

 

8、分组

GROUP BY 语句根据一个或多个列对结果集进行分组。在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

原始表数据如下：

（1）使用 GROUP BY 语句将数据表按名字进行分组，并统计每个人有多少条记录

SELECT name, COUNT(*) FROM  employee_tbl GROUP BY name;

（2）使用 WITH ROLLUP

WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。

SELECT name, SUM(signin) as signin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;

查询结果如下：

 其中记录 NULL 表示所有人的登录次数。

我们可以使用 coalesce 来设置一个可以取代 NUll 的名称，coalesce 语法：select coalesce(a,b,c);

参数说明：如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。

完善SQL:

SELECT coalesce(name, '总数'), SUM(signin) as signin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;

 

9、HAVING

先通过sql语句把所有数据查询出来，再用 group by 进行8分组，然后把分完组的数据用聚合函数进行统计，只不过查询语句和聚合函数之间需要用having连接；（group by 、having、聚合函数通常一起使用）

SELECT emp_no, count(salary) t     #在工资表里查找员工编号、统计发工资的次数（取别名t）
from salaries 
where creat_date between '2020-01-01' and '2020-12-31'
group by emp_no        #通过员工编号编组
HAVING t>15       #查询发工资次数大于15次的

 

10、正则表达式

MySQL同样支持其他正则表达式的匹配，MySQL中支持使用regexp操作符来进行正则表达式匹配

举例子：

（1）查找name字段中以'st'为开头的所有数据：

SELECT name FROM person_tbl WHERE name REGEXP '^st';

（2）查找name字段中以元音字符开头或以'ok'字符串结尾的所有数据：

SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';

（3）查找name字段中包含'mar'字符串的所有数据：

SELECT name FROM person_tbl WHERE name REGEXP 'mar';

 

11、NULL处理

我们已经知道 MySQL 使用 SQL SELECT 命令及 WHERE 子句来读取数据表中的数据,但是当提供的查询条件字段为 NULL 时，该命令可能就无法正常工作。

为了处理这种情况，MySQL提供了三大运算符:

• IS NULL: 当列的值是 NULL,此运算符返回 true
• IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
• <=>: 比较操作符（不同于 = 运算符），当比较的的两个值相等或者都为 NULL 时返回 true。

注意：

关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。

在 MySQL 中，NULL 值与任何其它值的比较（即使是 NULL）永远返回 NULL，即 NULL = NULL 返回 NULL 。

MySQL 中处理 NULL 使用 IS NULL 和 IS NOT NULL 运算符。

 

四、其他

1、处理重复数据

（1）防止表中出现其他数据

　　可以在表中设置指定的字段为 PRIMARY KEY（主键） 或者 UNIQUE（唯一） 索引来保证数据的唯一性。

　　如果我们设置了唯一索引，那么在插入重复数据时，SQL 语句将无法执行成功,并抛出错。

INSERT IGNORE INTO 与 INSERT INTO 的区别就是 INSERT IGNORE INTO 会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据

（2）统计重复数据

　　<1> 如果需要读取不重复的数据可以在 SELECT 语句中使用 DISTINCT 关键字来过滤重复数据。

SELECT DISTINCT last_name, first_name FROM person_tbl;

　　<2> 也可以使用 GROUP BY 来读取数据表中不重复的数据：

SELECT last_name, first_name  FROM person_tbl GROUP BY (last_name, first_name);

（3）删除重复数据

　　<1> 先查询出不重复数据新建表；删除重复表；重命名表

 CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl  GROUP BY (last_name, first_name, sex);

 DROP TABLE person_tbl;

 ALTER TABLE tmp RENAME TO person_tbl;

　　<2> 也可以在数据表中添加 INDEX（索引） 和 PRIMAY KEY（主键）这种简单的方法来删除表中的重复记录

ALTER IGNORE TABLE person_tbl  ADD PRIMARY KEY (last_name, first_name);

 

2、SQL注入

所谓SQL注入，就是通过把SQL命令插入到Web表单递交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。

举例：

$name = "Qadir'; DELETE FROM users;";
 mysqli_query($conn, "SELECT * FROM users WHERE name='{$name}'");

防止SQL注入，我们需要注意以下几个要点：

1.永远不要信任用户的输入。对用户的输入进行校验，可以通过正则表达式，或限制长度；对单引号和 双"-"进行转换等。
2.永远不要使用动态拼装sql，可以使用参数化的sql或者直接使用存储过程进行数据查询存取。
3.永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。
4.不要把机密信息直接存放，加密或者hash掉密码和敏感的信息。
5.应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装。
6.sql注入的检测方法一般采取辅助软件或网站平台来检测，软件一般采用sql注入检测工具jsky，网站平台就有亿思网站安全平台检测工具。MDCSOFT SCAN等。采用MDCSOFT-IPS可以有效的防御SQL注入，XSS攻击等。