MySQL基础

1、主键：表中一列或者一组列，其值能够唯一区分表中每一行。

2、SQL：structured query language

3、MySQL是DBMS，数据库管理软件。

4、MySQL、Oracle、Microsoft SQL Server都是基于客户机-服务器的数据库，服务器负责数据的增删改查等操作，客户机与用户打交道。

5、SQL语句不区分大小写。

6、SQL语句：

• 选择数据库： use databaseName;
• 显示数据库列表：show databases;
• 显示数据库表列表：show tables;
• 显示某个表的列：show columns from tableName;

　　

• 另外一种显示表结构的语句：describe tableName;

　　

• 显示服务器状态： show status;
• 显示创建数据库的语句： show create database databaseName; (该数据库必须是已经创建的数据库)

　　

　　显示创建表的语句： show create table tableName; (该表必须是已存在的)

　　

• 显示授予用户的安全权限： show grants;

　　

• 显示服务器错误或警告： show errors; 或 show warnings;
• 选择数据库所有列： select * from tableName;
• distinct-去除所选数据中的重复行：select distinct columnName from tableName; 也可用来去除多列数据集中的重复行。

　　

　　

• limit来限制返回行数：1）limit num;　　用来限制从选出数据集的行数； 2） limit mum1, mum2;　　显示结果集中从num1开始（包含第num1行）的num2行数据；

　　

　　

　　【注：检索得到的数据集行号从0开始】

• 使用完全限定的列名或表名，使用 tableName.columnName 的形式来限定列名，使用 databaseName.tableName 的形式来限定表名。

　　

　　

• 使用 order by columnName1, columnName2; 子句来对数据集排序，其中用来排序的列可以是未出现在select子句中的列；

　　

　　【注：使用多列排序时，先按columnName1排序，再按columnName2排序】

• 使用 asc（升序，默认）或 desc（降序）来指定排序规则，如order by columnName desc；当order by子句的字段名后不带有desc或asc时，默认按照升序排序。多列排序时，可以分别指定按照每个字段的排序规则，如order by price desc, product_name; 则首先按照价格降序排序，若价格相同，按照商品名升序排序。

　　　若用来排序的字段都降序排序，则必须在每个字段后添加desc。

• 使用order by和limit 1 组合，可以检索某列的最大值或最小值，如选取价格的最小值：select price from products order by price limit 1;
• where子句用来筛选数据，where子句的操作符：

等于	=
不等于	!=
不等于	<>
在两个值之间	between... and...

使用between操作符来进行值范围的检查，如 select * from products p where p.price between 5 and 10;

• 字段空值检查，用 columnName IS NULL 来判断某个字段是否为空值。空值指的是列不包含值。
• 用 and 或 or 来组合条件，SQL语句优先处理 and 操作符。
• 使用 in 操作符来指定条件范围，如 in (条件1, 条件2, 条件3)等，in 操作符限定字段的合法值全部包含在括号中。

　　

　　in操作符可用or连接多个合法值来代替，但是in操作符一般比or操作符执行更快。

• not操作符，用来否定后面的条件，一般用来 not in(...) ; not between .. and ... ; not exists(...);

　　

• like通配符，使用时，like '搜索串'，like支持的通配符：

　　% 通配符：在搜索串中，%表示任意字符出现任意次数，包含0个字符的情形；如查询博客用户名中以“crazy”开头的博客名，select * from t_blog where blog_name like 'crazy%'; 通配符的位置是任意的，如 '%crazy%'（包含crazy），'crazy%snail'（以crazy开头，以snail结尾），'%crazy'（以crazy结尾）等；【注：%通配符不能匹配NULL】

　　_ 通配符：匹配1个字符，且只能是1个，不能多、不能少；

　 【注：like通配符是匹配整个串。如 like '1000' 这匹配字串为'1000'的行。】

• MySQL正则表达式：MySQL支持的正则表达式仅是正则表达式语言的一个子集。
  

　　正则表达式匹配时，不区分大小写。正则表达式匹配使用 REGEXP关键字。

7、表连接

　　教师表：

　　

　　学生表：

　　　　

• 外键：外键是表中的某一列，包含另外表的主键值，定义了两个表之间的关系。
• 多表连接查询，如：

SELECT t_teacher.id as teacher_id, t_student.id, student_name
FROM t_teacher, t_student
WHERE t_teacher.id = t_student.tid
ORDER BY teacher_id, id　　//ORDER BY语句中所使用的排序字段可以使用SELECT语句中的字段别名，如 teacher_id

　　查询结果为：

　　

• 由没有连接条件的表关系返回的结果为笛卡尔积。检索出的数据条目是两表条目的乘机。
• 内部连接（亦称等值连接）：以上连接基于两个表之间的相等测试，这种连接也称为内部连接。（内部连接，指连接结果仅包含符合连接条件的行，参与连接的两个表都应该符合连接条件。）

　　上述内部连接例子可以用不同的语法表示，如通过 tableA INNER JOIN tableB ON condition; 

SELECT t_teacher.id as teacher_id, t_student.id as student_id, student_name
FROM t_teacher INNER JOIN t_student
ON t_teacher.id = t_student.tid

　　查询结果为：

　　

　　【注：上述内部连接语句等价于

SELECT t_teacher.id as teacher_id, t_student.id as student_id, student_name
FROM t_teacher, t_student
WHERE t_teacher.id = t_student.tid

　　】

　　多表联接查询；

SELECT school_name, teacher_name, student_name
FROM t_school, t_teacher, t_student
WHERE t_school.id = t_teacher.sid AND t_teacher.id = t_student.tid

　　表的连接操作对资源消耗严重，使用要慎用。

　　可以使用表的连接查询来代替子查询操作。

8、多种表联接

　　t_student表新增一条记录。

　　

• 自连接，使用表别名来实现，如仅通过查询t_student表，查出学生“Xiaoming”的老师的所有学生。

SELECT t1.id, t1.student_name
FROM t_student as t1, t_student as t2
WHERE t1.tid = t2.tid and t2.student_name='Xiaoming'

　　查询结果为：

　　

• 自然连接，每个列只返回一次，排除列的多次出现。在对表进行连接时，至少有一个列（如被连接的列）不只出现在一个表中。标准的连接（内部连接）返回所有数据，包括相同的列。

　　通过仅对一个表使用通配符（如果需要列出所连接表的所有列），对其他包含相同列的表，明确在select语句中指出所要选择的列。

• 外部连接：在结果集中包含了那些在相关表中没有关联行的行，这种类型的连接叫做外部连接。（连接结果不仅包含符合连接条件的行同时也包含自身不符合条件的行。）

　　外部连接包括左外连接、优外连接、全外连接。

　　左外连接（tableA LEFT OUTER JOIN tableB ON 条件）
　　左边表数据行全部保留，右边表保留符合连接条件的行。使用语法 LEFT OUT JOIN 或 LEFT JOIN 来实现，左外连接就是在等值连接的基础上加上主表中的未匹配数据。如选出所有老师及该老师与学生的对照表，包括那些没有学生的老师。

SELECT *
FROM t_teacher LEFT OUTER JOIN t_student   
ON t_teacher.id = t_student.tid

　　查询结果为：

　　　

　　从查询结果中可以看到，就算是没有学生的Snail老师也被筛选出来了，即左表的数据全部保留。

　　右外连接（tableA RIGHT OUTER JOIN tableB ON 条件）：

　　右边表数据行全部保留，左边表保留符合连接条件的行。使用语法 RIGHT OUT JOIN 或 RIGHT JOIN 来实现，右连接就是在等值连接的基础上加上右边表中的为匹配数据。如选出所有学生及该学生与老师的对照表，包括那些还没选好老师的学生。

SELECT *
FROM t_teacher RIGHT OUTER JOIN t_student
ON t_teacher.id = t_student.tid

　　查询结果为：

　　

　　从查询结果中可见，没有选好老师的学生ADai也被筛选出来，即右表的数据全部保留。

　　【注：此处的全外连接只是说明全外连接的意义及用法，mysql不支持全外连接的语法。但是可以用 左外连接 UNION 右外连接 的方式来取得全外连接相同的结果。

　　全外连接：
　　　　左外连接 union 右外连接。使用语法 FULL OUTER JOIN 或 FULL JOIN 来实现。全外连接的等价写法是对两个表分别进行左连接和右连接，然后将得到的结果集进行union操作。

SELECT *
FROM t_teacher LEFT JOIN t_student
ON t_teacher.id = t_student.tid
UNION
SELECT *
FROM t_teacher RIGHT ON t_student
ON t_teacher.id = t_student.tid

 　　上述查询执行结果为：

　　

　　】

9、使用 UNION 来组合多个 SELECT 语句，实现组合查询。

10、MySQL的全文本搜索

　　MySQL的常用引擎MyISAM 和 InnoDB中，MyISAM支持全文本搜索，而InnoDB不支持全文本搜索。全文本搜索必须索引被搜索的列，且随着数据的变动不断重新索引。通过表的设计，MySQL可以自动进行索引和重新索引。

　　在索引建立后，可以通过SELECT语句与Match()和Against()函数一起执行搜索。Match函数用于指定匹配的列，Against函数用于指定要使用的搜索表达式。

11、INSERT语句

• 插入多行数据

INSERT INTO t_teacher(teacher_name)
VALUES('NewTeacher3'),
('NewTeacher4'),
('NewTeacher5');

　　在VALUES关键字之后，用括号将每组要插入的数据括起，每组数据间通过逗号分隔。执行多行插入比执行多次INSERT语句效率要高。【注：插入多行数据时，仅使用一个VALUES】

• 插入数据库检索的数据——INSERT ... SELECT...

　　如,选取学生表中的第一个学生作为助教,加入到教师表中。

INSERT INTO t_teacher(teacher_name)
SELECT student_name
FROM t_student
LIMIT 1

　　此时，经过插入多行数据和插入检索结果之后，t_teacher表更新为：

　　

12、删除整表的数据——TRUNCATE tableName;

　　TRUNCATE tableName执行时，首先是删除掉整个表，并新建一个新的tableName。使用TRUNCATE删除整表数据效率比 delete * from tableName 高。

13、创建表

　　创建表语句。

CREATE TABLE t_school (
  id int(11) NOT NULL AUTO_INCREMENT,
  school_name varchar(32) NOT NULL,
  school_desc varchar(100) NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

　　使用 CREATE TABLE 表名 来创建表，not null指明字段不允许为空， null 字段指明该字段允许为空。且默认情况下，允许字段为空，所以可以省略掉null关键字。使用primary key来指定主键，通过engine来指定MySQL数据库引擎，通过charset来指定数据库字符集。

　　建表时，通过DEFAULT列默认值，如指定表t_school得school_desc列默认值为""；

CREATE TABLE t_school (
  id int(11) NOT NULL AUTO_INCREMENT,
  school_name varchar(32) NOT NULL,
  school_desc varchar(100) NOT NULL DEFAULT '' ,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

14、更新表结构——alter 

• 为表增加新的列，并指定列属性；或者删除已有的列；

ALTER TABLE t_school 
ADD school_loc varchar(50) NOT NULL DEFAULT ''

ALTER TABLE t_school
DROP COLUMN school_desc

• 为表增加外键

　　创建新表t_city，含有两个列，建表：

CREATE TABLE t_city (
  city_id int(11) NOT NULL auto_increment,
  city_name varchar(32) NOT NULL default '',
  PRIMARY KEY  (city_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

　　【由上述语句可见，MySQL关键字不区分大小写。另外，指定为auto_increment的列必须声明为键，否则会报错。】

　　为表t_school增加外键city_id。

ALTER TABLE t_school 
ADD city_id int NOT NULL DEFAULT 0 #为t_school添加外键列

ALTER TABLE t_school
ADD CONSTRAINT fk_school_city
FOREIGN KEY(city_id) REFERENCES t_city (city_id);

• 对表有多个修改时，可用逗号分隔多个修改

alter table t_school
drop school_loc,
ADD school_loc VARCHAR(50) NOT NULL DEFAULT ''

15、删除表—— DROP TABLE 表名;

16、重命名表—— RENAME TABLE 原表名 TO 新表名；

17、视图

　　视图是虚拟的表，含有动态查询的结果。

• 创建视图——在select查询前，添加CREATE VIEW 视图名 AS即可；

CREATE VIEW school_city AS
SELECT s.school_name, c.city_name
FROM t_school s, t_city c
WHERE s.city_id = t.city_id

• 视图的更新，更新（insert、update、delete）一个视图将更新其基表，如果不能正确更新视图对应的基表，则无法更新该视图。

18、存储过程

　　存储过程，可以看做一条或多条MySQL语句的集合。存储过程的优点：简单、安全、高性能。

　　存储过程的创建和使用类似函数，可以接受入参、出参（返回值），可以用IF等控制语句来进行流程改写。

　　【准备工作：为了存储过程进行的表和数据的定义。】

use crazysnail;

CREATE TABLE student(
    id INT NOT NULL AUTO_INCREMENT COMMENT '主键',
    name VARCHAR(32) NOT NULL DEFAULT '' COMMENT '姓名',
    age INT NOT NULL DEFAULT 7 COMMENT '年龄',
    height INT COMMENT '身高，厘米为单位',
    PRIMARY KEY(id)
)ENGINE=INNODB DEFAULT CHARSET = utf8;

INSERT INTO student
VALUES(null, 'AAA', 8,100),
(null, 'BBB', 9, 105),
(null,'CCC', 8, 97);

• 创建存储过程——使用 CREATE PROCEDURE 

　　默认的MySQL分隔符为(;)。为了避免在命令行中创建存储过程时，由于存储过程本身包含的（;）引起错误，使用 DELIMITER // 来告诉命令行使用 // 来作为新的语句结束符。

DELIMITER $$

CREATE PROCEDURE statistics_student( 
IN age_limit INT, 
OUT num_of_students INT,
OUT above_limit_num INT,
OUT avg_height INT
) COMMENT '用来统计学生信息的存储过程'
BEGIN
    -- 声明临时变量 aaa 【貌似declare声明变量语句需要放到存储过程的开始处，否则报错】
    DECLARE aaa INT DEFAULT 0;

    -- select into 语句相当于赋值语句
    SELECT COUNT(*)
    INTO num_of_students 
    FROM student;

    SELECT AVG(height)
    FROM student
    INTO avg_height;
    
    SELECT 0 INTO above_limit_num;

    IF age_limit>0 THEN
        SELECT COUNT(*) FROM student 
        WHERE age>age_limit
        INTO above_limit_num;
    END IF;

END

$$

DELIMITER ;

　　注意，在创建存储过程时，存储过程的参数通过 IN、OUT、INOUT来指定入参、出参（结果值）、出入参。

　　DECLARE语句用来声明临时变量，不过DECLARE语句貌似只能出现在存储过程的开始处，否则可能会报错。

　　创建无参存储过程时，存储过程名后的括号中无需指定参数。

• 调用存储过程

　　对于无参的存储过程，直接通过 CALL 【存储过程名】(); 来调用。如，call procedurename();

　　对于含有参数的存储过程，入参可直接传入值，出参通过传入 @变量名 来调用，即CALL procedurename(入参值,@出参1,@出参2); 。如调用statistics_student。

CALL statistics_student(0, @total, @above_limit, @avg_h);

　　获取存储过程的出参值。

SELECT @total as total, @above_limit, @avg_h;

　　执行结果：

　　

　　【注：所有MySQL的变量都以@开头，如调用存储过程时传入的出参。】

• 删除存储过程—— DROP PROCEDURE procedurename [IF EXISTS];

19、触发器

　　触发器是响应DELETE、INSERT、UPDATE三者中的任意语句而自动执行的一条MySQL语句（或位于BEGIN和END之间的一组语句）。其他mysql语句不支持触发器。

　　关于触发器部分，查看触发器。

　　

20、常见MySQL引擎

InnoDB	支持事务处理
MyISAM	不支持事务处理

附注：

SQL语句的执行次序：

SQL Select语句完整的执行顺序：
1、from子句组装来自不同数据源的数据；
2、where子句基于指定的条件对记录行进行筛选；
3、group by子句将数据划分为多个分组；
4、使用聚集函数进行计算；
5、使用having子句筛选分组；
6、计算所有的表达式；
7、使用order by对结果集进行排序。
8、select 集合输出。