SQL总结

一.基础

注意：SQL语句一般不区分大小写，但是一般为了区分保留字和变量名，把保留字大写，变量名和数据小写。

1.打开MySQL服务

sudo service mysql start

2.用root登录

mysql -u root

3.创建数据库

格式：CREATE DATABASE <数据库名字>;

eg:

CREATE DATABASE mysql_shiyan;

4.查看数据库

show databases;

5.连接数据库

格式：use <数据库名字>

eg:

use mysql_shiyan

6.查看表

show tables;

7.创建表

CREATE TABLE employee(id int(10),name char(20),phone int(12));

8.查看表中所有数据

SELECT * FROM employee;

9.插入数据

INSERT INTO employee(id,name,phone) VALUES(01,'Tom',110110110);INSERT INTO employee VALUES(02,'Jack',119119119);INSERT INTO employee(id,name) VALUES(03,'Rose');

10.创建表的同时设置id自增，主键和默认值。注意：varchar后面必须给数据长度。

CREATE TABLE student(sid int(10) auto_increment primary key not null, sname varchar(20), gender varchar(10) default 'male');

11.创建表的同时设置多个外键，并且照外表的列 当外键的值删除 本表中对应的列筛除 当外键的值改变 本表中对应的列值改变。。

CREATE TABLE mark(mid int(10) primary key,sid int(10),cid int(10),score int,foreign key(sid) references student(sid) on delete cascade on update cascade,foreign key(cid) references course(cid) on delete cascade on update cascade);

12.SELECT语句的基本格式：

SELECT 要查询的列名 FROM 表名字 WHERE 限制条件;

SELECT name,age FROM employee;SELECT name,age FROM employee WHERE age>25;

13.WHERE 后面可以有不止一条限制，而根据条件之间的逻辑关系，可以用 OR(或) 和 AND(且) 连接：

SELECT name,age FROM employee WHERE age<25 OR age>30;

14.And

SELECT name,age FROM employee WHERE age>25 AND age<30;SELECT name,age FROM employee WHERE age BETWEEN 25 AND 30;

15.IN和NOT IN

用于筛选‘在’或‘不在’某个范围内的结果

SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt in('dpt3','dpt4');SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt NOT IN('dpt1','dpt3');

16.通配符：关键字LIKE和通配符一起使用，通配符代表未知字符，‘_’代表一个未指定字符，‘%’代表不定个未指定字符

SELECT name,age,phone FROM employee WHERE phone LIKE '110110__';SELECT name,age,phone FROM employee WHERE name LIKE 'J%';

17.对结果排序，用ORDER BY 关键词，默认情况下为升序排列，而使用关键词ASC 和DESC 可指定升序或降序排序。eg:按salary降序

SELECT name,age,salary,phone FROM employee ORDER BY salary DESC;

18内置函数和计算：COUNT（计数），SUM（求和）， AVG（求平均值），MAX（最大值），MIN（最小值）。AS关键词可以重命名

SELECT MAX(salary) AS max_salary,MIN(salary) FROM employee;

19.子查询。例如：想要知道名为“Tom”的员工所在部门做了几个工程。员工信息储存在project表中。

SELECT of_dpt,COUNT(proj_name) AS count_project FROM project WHERE of_dpt IN (SELECT in_dpt FROM employee WHERE name='Tom');

20.连接查询,基本思想是把两个或多个表当作一个新的表来操作。其中员工的id和name来自employee表，people_num来自department表：

SELECT id,name,people_numFROM employee,departmentWHERE employee.in_dpt = department.dpt_nameORDER BY id;

或者使用JOIN ON语法：

SELECT id,name,people_numFROM employee JOIN departmentON employee.in_ept = department.dpt_nameORDER BY id;

21.查询统计用GROUP BY
MysqL group by 用法解析

二.数据库及表的修改和删除

1.删除数据库

DROP DATABASE <数据库名>;

2.重命名一张表

RENAME TABLE 原名 TO 新名字;ALTER TABLE 原名 RENAME 新名;ALTER TABLE 原名 RENAME TO 新名;

3.删除一张表

DROP TABLE 表名字;

4.增加一列

ALTER TABLE 表名字 ADD COLUMN 列名字 数据类型 约束;或： ALTER TABLE 表名字 ADD 列名字 数据类型 约束;

设置位置可用AFTER或FIRST关键词（放最后）

5.删除一列

ALTER TABLE 表名字 DROP COLUMN 列名字;或： ALTER TABLE 表名字 DROP 列名字;

6.重命名一列

ALTER TABLE 表名字 CHANGE 原列名 新列名 数据类型 约束;

ALTER TABLE 表名字 MODIFY 列名字 新数据类型;

7.修改表中的某个值

ＰＳ：如果不加条件，会导致全表的列全改变。

UPDATE 表名字 SET 列1=值1,列2=值2 WHERE 条件;

8.删除一行记录

UPDATE 表名字 SET 列1=值1,列2=值2 WHERE 条件;

三.基本操作

3.1索引

索引是一种与表有关的结构，它的作用相当于书的目录，可以根据目录中的页码快速找到所需的内容。目的：加快查询速度

语句格式：

ALTER TABLE 表名字 ADD INDEX 索引名 (列名);CREATE INDEX 索引名 ON 表名字 (列名);

使用命令SHOW INDEX FROM <表名字>; 可以查看该表的索引。

3.2视图

视图是从一个或多个表中导出来的表，是一种虚拟存在的表。通过视图可以看到系统专门提供的数据。

创建视图的语句格式为：

CREATE VIEW 视图名(列a,列b,列c) AS SELECT 列1,列2,列3 FROM 表名字;

视图也可以建立有多张表上，只需在SELECT 语句中使用子查询或连接查询。
3.3导入

导入操作，可以把一个文件里的数据保存进一张表。

导入语句格式：

LOAD DATA INFILE '文件路径' INTO TABLE 表名字;

3.4导出

导出与导入是相反的过程，是把数据库某个表中的数据保存到一个文件之中。

导出语句基本格式为：

SELECT 列1，列2 INTO OUTFILE '文件路径和文件名' FROM 表名字;

3.5备份

备份是把数据库的结构，包括数据、约束、视图等全部另存为一个文件。

使用mysqldump备份的语句：

mysqldump -u root 数据库名>备份文件名;   #备份整个数据库mysqldump -u root 数据库名 表名字>备份文件名;  #备份整个表

3.6恢复

用备份文件恢复数据库

source /tmp/SQL6/MySQL-06.sql

3.7模式匹配

由MySQL提供的模式匹配的其它类型是使用扩展正则表达式。当你对这类模式进行匹配测试时，使用REGEXP和NOT REGEXP操作符（或RLIKE和NOT RLIKE ，它们是同义词）。

• ‘.’匹配任何单个的字符。

• 字符类“[...]”匹配在方括号内的任何字符。例如，“[abc]”匹配“a”、“b”或“c”。为了命名字符的范围，使用一个“-”。“[a-z]”匹配任何字母，而“[0-9]”匹配任何数字。

• “ ”匹配零个或多个在它前面的字符。例如，“x”匹配任何数量的“x”字符，“[0-9]”匹配任何数量的数字，而“.”匹配任何数量的任何字符。

如果REGEXP模式与被测试值的任何地方匹配，模式就匹配(这不同于LIKE模式匹配，只有与整个值匹配，模式才匹配)。 为了定位一个模式以便它必须匹配被测试值的开始或结尾，在模式开始处使用“^”或在模式的结尾用“$”。

eg:为了找出以“b”开头的名字，使用“^”匹配名字的开始：

mysql> SELECT * FROM pet WHERE name REGEXP '^b';

如果你想强制使REGEXP比较区分大小写，使用BINARY关键字使其中一个字符变为二进制字符串。该查询只匹配名称首字母的小写‘b’。

mysql> SELECT * FROM pet WHERE name REGEXP BINARY '^b';

为了找出以”fy“结尾的名字，使用”＄“匹配名字的结尾：

mysql> SELECT * FROM pet WHERE name REGEXP 'fy$';

为了找出包含一个”w“的名字，使用以下查询：

mysql> SELECT * FROM pet WHERE name REGEXP 'w';

为了找出包含正好5个字符的名字，使用”^“和”$“匹配名字的开始和结尾，和5个”."实例在两者之间：

mysql> SELECT * FROM pet WHERE name REGEXP '^.....$';

也可以使用“{n}”重复n次操作符，重写前面的查询：效果同上

mysql> SELECT * FROM pet WHERE name REGEXP '^.{5}$';

3.8计算行数

mysql> SELECT COUNT(*) FROM pet;

想知道每个主人有多少宠物

mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;