首页 > 代码库 > mysql进阶
mysql进阶
MYSQL约束
约束用于限制加入表的数据类型
常用的几种约束
- - NOT NULL 不能 为NULL值
- - UNIQUE 唯一值
- - PRIMARY KEY 主键约束
- - FOREIGN KEY 外键约束
- - CHECK CHECK约束
- - DEFAULT 默认约束值
NOT NULL 约束
NOT NULL 约束强制列不接受 NULL（空） 值。
NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。
语法
CREATE TABLE 表名 (字段名1 类型 NOT NULL)
UNIQUE约束
UNIQUE 约束唯一标识数据库表中的每条记录。
UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。
PRIMARY KEY 拥有自动定义的 UNIQUE 约束。
请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。
语法
CREATE TABLE 表名 (字段名1 类型 UNIQUE)
主键约束
PRIMARY KEY 约束唯一标识数据库表中的每条记录。
主键必须包含唯一的值。
主键列不能包含 NULL 值。
每个表都应该有一个主键，并且每个表只能有一个主键。
主键类似身份证号
语法
CREATE TABLE 表名 (字段名 类型 PRIMARY KEY );如需要设置主键自增长则在PRIMARY KEY AUTO_INCREMENTCREATE TABLE 表名 (字段名 类型 PRIMARY KEY AUTO_INCREMENT);
外键约束
作为外键一定要和关联主键的数据类型保持一致
主要目的是控制存储在外键表中的数据。 使两张表形成关联。
如下图
这时候两张表做外键约束。
FOREIGN KEY 约束用于预防破坏表之间连接的动作。
FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
外键约束对成绩表的含义：如果在信息表找不到相应的学号，则不允许做insert/update（学校里都没这个人信息的学号，则肯定不能对这个人加成绩）
外键约束对信息表的含义： 在信息表上不能进行update/delete以更新或删除
，因为信息表的行为取决于，在定义成绩表的外键时的级联操作
例如级联删除(ON DELETE CASCADED);
例：创建外键约束
CREATE TABLE school;创建学生信息表CREATE TABLE info (stu_id INT PRIMARY KEY ,name VARCHAR (45),gemder tinyint,ethnic CHAR ,id_card INT UNIQUE NOT NULL);创建学生成绩表CREATE TABLE score (stu_id INT ,chinese tinyint ,math tinyint,english tinyint,FOREIGN KEY (stu_id) REFERENCES info(stu_id)); 查看新建的表信息 mysql> SHOW CREATE TABLE score; score | CREATE TABLE `score` ( `stu_id` int(11) DEFAULT NULL, `chinese` tinyint(4) DEFAULT NULL, `math` tinyint(4) DEFAULT NULL, `english` tinyint(4) DEFAULT NULL, KEY `stu_id` (`stu_id`), CONSTRAINT `score_ibfk_1` FOREIGN KEY (`stu_id`) REFERENCES `info` (`stu_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |（解释下表信息中的这些内容 CONSTRAINT `score_ibfk_1` FOREIGN KEY (`stu_id`) REFERENCES `info` (`stu_id`) CONSTRAINT `score_ibfk_1` 约束 score_ibfk_1 这是外键名 FOREIGN KEY (`stu_id`) 这里的stu_id 是外键字段 REFERENCES `info` (`stu_id`) 以哪个表的(字段) 做为参考，做关联） 由于上面的id_card字段类型设置有问题这里给重新修改了一下ALTER TABLE info modify id_card VARCHAR (22); 往学生表里面插入记录INSERT INTO info VALUES(2014090001,"张三",1,"汉","123456754651"),(2014090002,"李四",0,"汉","469877036547"),(2014090003,"阿斗",0,"汉","643571593478"),(2014090004,"小红",1,"汉","964713954207");往成绩表里面插入记录INSERT INTO score VALUES(2014090001,88,99,78),(2014090002,99,80,70),(2014090003,93,90,69),(2014090004,85,79,89);
表里面有相应的一些记录，也做了外键约束，可以尝试对主表做修改，看看有什么不同
主表：被外键所绑定的表
子表：绑定了主键的表
尝试删除主表的记录
mysql> DELETE FROM info where name="张三";ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`school`.`score`, CONSTRAINT `score_ibfk_1` FOREIGN KEY (`stu_id`) REFERENCES `info` (`stu_id`))提示做了外键约束，不能删除为什么不能删除，上面已经提到了
如果想要修改，则在字表做级联操作
什么是级联(CASCADED)了
cascade 表示级联操作，就是说，如果主键表中被参考字段更新，外键表中也更新，主键表中的记录被删除，外键表中改行也相应删除
语法
ALTER TABLE 子表 ADD FOREIGN KEY(外键) REFERENCES 主表(主键)ON DELETE CASCADE;
例如级联删除：
添加级联ALTER TABLE score ADD FOREIGN KEY (s_id) REFERENCES info(stu_id)ON DELETE CASCADE;删除主表记录DELETE FROM info where name="张三";这时候就删除成功，信息表里面没有张三的记录信息，成绩表里也没有张三成绩的信息
删除外键
ALTER TABLE 子表 DROP FOREIGN KEY 外键名;
注：是外键名不是外键字段
CHECK约束
CHECK 约束用于限制列中的值的范围。
如果对单个列定义 CHECK 约束，那么该列只允许特定的值。
如果对一个表定义 CHECK 约束，那么此约束会在特定的列中对值进行限制。
DEFAULT约束
DEFAULT 约束用于向列中插入默认值。
如果没有规定其他的值，那么会将默认值添加到所有的新记录。
INNODB支持的外键4种方式
1.级联(cascade)方式 在主表上update/delete记录时，同步update/delete掉子表的匹配记录
外键的级联删除：如果父表中的记录被删除，则子表中对应的记录自动被删除
语法：
ALTER TABLE 子表 ADD FOREIGN KEY(外键) REFERENCES 主表(主键)ON DELETE CASCADE;
2.set null方式 在父表上update/delete记录时，将子表上匹配记录的列设为null
要注意子表的外键列不能为not null
语法：
ALTER TABLE 子表 ADD FOREIGN KEY(外键) REFERENCES 主表(主键)ON DELETE CASCADE SET NULL;
3.Restrict方式 :拒绝对父表进行删除更新操作(了解)
4.No action方式 在mysql中同Restrict,如果子表中有匹配的记录,则不允许对主表对应候选键
-- 进行update/delete操作（了解）。
多表查询
新建库
再新建两张表
建库：mysql> create database company;进库：mysql>use company;建员工表：mysql> create table emp( -> emp_id int auto_increment primary key not null, -> emp_name varchar(50), -> age int, -> dept_id int -> );建部门表mysql> create table dep( -> dept_id int, -> dept_name varchar(100) -> );往员工表插入记录mysql> insert into emp(emp_name,age,dept_id) values -> (‘A‘,19,200), -> (‘B‘,26,201), -> (‘C‘,30,201), -> (‘D‘,24,202), -> (‘E‘,20,200), -> (‘F‘,38,204);往部门表插入记录mysql> insert into dep values -> (200,‘人事部‘), -> (201,‘技术部‘), -> (202,‘销售部‘), -> (203,‘财政部‘);
MYSQL常用的几种连接查询方法
INNER JOIN 内连接
LEFT JOIN 左连接
RIGHT JOIN 右连接
MYSQL 没有全连接(FULL JOIN)
内连接查询
笛卡尔积查询：
假如一张表示M表，另一张表示N表
查询的总记录是：M*N记录
M的每一条记录都会匹配N的每一条记录
笛卡尔积，查询示例
select * from emp inner join dep; 和 select * from emp,dep查询结果都一样mysql> select * from emp,dep; +--------+----------+------+---------+---------+-----------+| emp_id | emp_name | age | dept_id | dept_id | dept_name |+--------+----------+------+---------+---------+-----------+| 1 | A | 19 | 200 | 200 | 人事部 || 1 | A | 19 | 200 | 201 | 技术部 || 1 | A | 19 | 200 | 202 | 销售部 || 1 | A | 19 | 200 | 203 | 财政部 || 2 | B | 26 | 201 | 200 | 人事部 || 2 | B | 26 | 201 | 201 | 技术部 || 2 | B | 26 | 201 | 202 | 销售部 || 2 | B | 26 | 201 | 203 | 财政部 || 3 | C | 30 | 201 | 200 | 人事部 || 3 | C | 30 | 201 | 201 | 技术部 || 3 | C | 30 | 201 | 202 | 销售部 || 3 | C | 30 | 201 | 203 | 财政部 || 4 | D | 24 | 202 | 200 | 人事部 || 4 | D | 24 | 202 | 201 | 技术部 || 4 | D | 24 | 202 | 202 | 销售部 || 4 | D | 24 | 202 | 203 | 财政部 || 5 | E | 20 | 200 | 200 | 人事部 || 5 | E | 20 | 200 | 201 | 技术部 || 5 | E | 20 | 200 | 202 | 销售部 || 5 | E | 20 | 200 | 203 | 财政部 || 6 | F | 38 | 204 | 200 | 人事部 || 6 | F | 38 | 204 | 201 | 技术部 || 6 | F | 38 | 204 | 202 | 销售部 || 6 | F | 38 | 204 | 203 | 财政部 |+--------+----------+------+---------+---------+-----------+24 rows in set (0.06 sec)
内连接查询示例
mysql> select emp.dept_id,emp.emp_name,dep.dept_name from emp inner join dep on emp.dept_id=dep.dept_id;+---------+----------+-----------+| dept_id | emp_name | dept_name |+---------+----------+-----------+| 200 | A | 人事部 || 201 | B | 技术部 || 201 | C | 技术部 || 202 | D | 销售部 || 200 | E | 人事部 |+---------+----------+-----------+5 rows in set (0.00 sec)
左连接查询
左连接，查询示例：
mysql> select emp.dept_id,emp.emp_name,dep.dept_name from emp left join dep on emp.dept_id=dep.dept_id;+---------+----------+-----------+| dept_id | emp_name | dept_name |+---------+----------+-----------+| 200 | A | 人事部 || 200 | E | 人事部 || 201 | B | 技术部 || 201 | C | 技术部 || 202 | D | 销售部 || 204 | F | NULL |+---------+----------+-----------+6 rows in set (0.00 sec)
右连接查询
右连接查询实例
mysql> select emp.dept_id,emp.emp_name,dep.dept_name from emp right join dep on emp.dept_id=ddep.dept_id;+---------+----------+-----------+| dept_id | emp_name | dept_name |+---------+----------+-----------+| 200 | A | 人事部 || 201 | B | 技术部 || 201 | C | 技术部 || 202 | D | 销售部 || 200 | E | 人事部 || NULL | NULL | 财政部 |+---------+----------+-----------+6 rows in set (0.00 sec)
多表查询之复合条件查询
查询员工年龄大于等于25岁的部门
SELECT DISTINCT department.dept_name FROM employee,department WHERE employee.dept_id = department.dept_id AND age>25;
多表查询之子查询
- - 子查询是将一个查询语句嵌套在另一个查询语句中。
- - 内层查询语句的查询结果，可以为外层查询语句提供查询条件。
- - 内层查询语句的查询结果，可以为外层查询语句提供查询条件。
- - 还可以包含比较运算符：= 、 !=、> 、<等
1. 带IN关键字的子查询
查询employee表，但dept_id必须在department表中出现过
mysql> select * from emp -> where dept_id IN -> (select dept_id from dep);+--------+----------+------+---------+| emp_id | emp_name | age | dept_id |+--------+----------+------+---------+| 1 | A | 19 | 200 || 2 | B | 26 | 201 || 3 | C | 30 | 201 || 4 | D | 24 | 202 || 5 | E | 20 | 200 |+--------+----------+------+---------+5 rows in set (0.00 sec)
2. 带比较运算符的子查询
=、!=、>、>=、<、<=、<>
查询员工年龄大于等于25岁的部门
mysql> select dept_id,dept_name from dep -> where dept_id IN -> (select DISTINCT dept_id from emp where age>=25);+---------+-----------+| dept_id | dept_name |+---------+-----------+| 201 | 技术部 |+---------+-----------+1 row in set (0.00 sec)
3. 带EXISTS关键字的子查询
EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。
而是返回一个真假值。Ture或False
当返回Ture时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询
EXISTS 返回为Ture，进行查询emp表
mysql> select * from emp -> WHERE EXISTS -> (SELECT dept_name from dep where dept_id=203);+--------+----------+------+---------+| emp_id | emp_name | age | dept_id |+--------+----------+------+---------+| 1 | A | 19 | 200 || 2 | B | 26 | 201 || 3 | C | 30 | 201 || 4 | D | 24 | 202 || 5 | E | 20 | 200 || 6 | F | 38 | 204 |+--------+----------+------+---------+6 rows in set (0.00 sec)
EXISTS 返回为False，进行查询emp，则查询结果为空
mysql> select * from emp -> WHERE EXISTS -> (SELECT dept_name from dep where dept_id=205);Empty set (0.00 sec)
复制表的内容
create table emp_bak (select * from emp)
提示：这个复制表的，只能表的字段 类型和记录，不能复制表的约束条件(比如：主键，外键)
索引
为什么要有索引
当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作；第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。
简单理解：假如我们有一本牛津词典,假如要查询professional的单词，并没有给你提供这个单词所在页数，你能快速的找到这个单词吗，答案：肯定是能找到的，但不会非常快速的找到，这时候我们就需要应用索引去查找了
索引特点：创建与维护索引会消耗很多时间与磁盘空间，但查询速度大大提高！一般索引是针对的大表的,来创建的
索引分类及语法
索引类型分为：分为唯一索引(UNIQUE INDEX)、全文索引(FULLTEXT INDEX)、空间索引(SPATIAL INDEX)、普通索引 (INDEX)
注：在mysql中MyISAM, InnoDB都支持空间列（存储空间数据类型的列）
空间索引有以下特性：
- - 只有myisam和innodb（mysql5.7.5以上版本）的表支持空间索引。
- - 被索引的列必须非NULL
- - 在mysql5.7中，列的前缀长度属性被禁用。空间索引直接索引完整宽度的列
- - 由于空间数据类型我们很少用到，所以空间索引我们接触的更少。这里不做深入阐述了
创建表时创建索引
语法CREATE TABLE 表名 ( 字段名1 数据类型 [完整性约束条件…], 字段名2 数据类型 [完整性约束条件…], 索引类型 索引名 (字段名), );1.创建普通索引示例：CREATE TABLE emp1 ( id INT, name VARCHAR(30) , resume VARCHAR(50), INDEX index_emp_name (name), ); 2.创建唯一索引示例： CREATE TABLE emp2 ( id INT, name VARCHAR(30) , bank_num CHAR(18) UNIQUE , resume VARCHAR(50), UNIQUE INDEX index_emp_name (name) ); 3.创建全文索引示例：CREATE TABLE emp3 ( id INT, name VARCHAR(30) , resume VARCHAR(50), FULLTEXT INDEX index_resume (resume) );4.创建多列索引示例：CREATE TABLE emp4 ( id INT, name VARCHAR(30) , resume VARCHAR(50), INDEX index_name_resume (name, resume) );
对已有表添加索引
第一种语法：CREATE 索引类型 索引名 ON 表名 (字段名)第二种语法： ALTER TABLE 表名 ADD 索引类型 索引名 (字段名) 示例：CREATE INDEX index_emp_name on emp1(name);ALTER TABLE emp2 ADD UNIQUE INDEX index_bank_num(band_num);
删除索引
语法：DROP INDEX 索引名 on 表名DROP INDEX index_emp_name on emp1;DROP INDEX bank_num on emp2;
小结
在SQL 连接查询中 on，where，having的区别
1、on、where、having这三个都可以加条件的子句中，on是最先执行，where次之，having最后。有时候如果这先后顺序不影响中间结果的话，那最终结果是相同的。但因为on是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的。
2、where应该比having快点的，因为它过滤数据后才进行sum，所以having是最慢的。但也不是说having没用，因为有时在步骤3还没出来都不知道那个记录才符合要求时，就要用having了。
3、在两个表联接时才用on的，所以在一个表的时候，就剩下where跟having比较了。在这单表查询统计的情况下，如果要过滤的条件没有涉及到要计算字段，那它们的结果是一样的，只是where可以使用rushmore技术，而having就不能，在速度上后者要慢。
4、如果要涉及到计算的字段，就表示在没计算之前，这个字段的值是不确定的，根据上篇写的工作流程，where的作用时间是在计算之前就完成的，而having就是在计算后才起作用的，所以在这种情况下，两者的结果会不同。
5、在多表联接查询时，on比where更早起作用。系统首先根据各个表之间的联接条件，把多个表合成一个临时表后，再由where进行过滤，然后再计算，计算完后再由having进行过滤。由此可见，要想过滤条件起到正确的作用，首先要明白这个条件应该在什幺时候起作用，然后再决定放在那里
查询的顺序如下
FROM ,ON , WHERE, SELECT , GROUP BY , ORDER BY ,HAVING
事务
什么是事务
事务(Transaction)是并发控制的基本单位。所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。例如，银行转帐工作：从一个帐号扣款并使另一个帐号增款，这两个操作要么都执行，要么都不执行。所以，应该把他们看成一个事务。事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性。
为什么要有事务
事务广泛的运用于订单系统、银行系统等多种场景。如果有以下一个场景：A用户和B用户是银行的储户。现在A要给B转账500元。那么需要做以下几件事：
1. 检查A的账户余额>500元；
2. A账户扣除500元；
3. B账户增加500元；
正常的流程走下来，A账户扣了500，B账户加了500，皆大欢喜。那如果A账户扣了钱之后，系统出故障了呢？A白白损失了500，而B也没有收到本该属于他的500。以上的案例中，隐藏着一个前提条件：A扣钱和B加钱，要么同时成功，要么同时失败。事务的需求就在于此。
事务的特性
<1> 原子性（Atomicity）：原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
<2> 一致性（Consistency）：事务前后数据的完整性必须保持一致。在事务执行之前数据库是符合数据完整性约束的，无论事务是否执行成功，事务结束后的数据库中的数据也应该是符合完整性约束的。在某一时间点，如果数据库中的所有记录都能保证满足当前数据库中的所有约束，则可以说当前的数据库是符合数据完整性约束的。
比如删部门表前应该删掉关联员工（已经建立外键），如果数据库服务器发生错误，有一个员工没删掉，那么此时员工的部门表已经删除，那么就不符合完整性约束了，所以这样的数据库也就性能太差啦！
<3>隔离性（Isolation）：事务的隔离性是指多个用户并发访问数据库时，一个用户的事务不能被其它用户的事务所干扰，多个并发事务之间数据要相互隔离。
<4>持久性（Durability）：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。
三、隔离性：
将数据库设计为串行化程的数据库，让一张表在同一时间内只能有一个线程来操作。如果将数据库设计为这样，那数据库的效率太低了。所以数据库的设计这没有直接将数据库设计为串行化，而是为数据库提供多个隔离级别选项，使数据库的使用者可以根据使用情况自己定义到底需要什么样的隔离级别。
不考虑隔离性可能出现的问题：
事务的命令
开启事务 start transaction; 回滚事务 回滚事务,即撤销指定的sql语句(只能回退insert delete update语句)，回滚到上一次commit的位置Rollback;提交事务提交事务，提交未存储的事务Commit;保留点事务处理中设置的临时占位符 你可以对它发布回退(与整个事务回退不同)savepoint
转账实例:
创建一个银行库create database bank; use bank;创建用户表create table user_info(id int PRIMARY KEY auto_increment,name VARCHAR(20),balance INT);插入两条用户账户记录A和BINSERT INTO user_info (name,balance) VALUE ("A",1000), ("B",1000); select * from user_info;开启事务start transaction;假如没有提交事务，那么转账完全失败UPDATE user_info set balance=balance-500 WHERE name=”A”;UPDATE user_info set balance=balance+500 WHERE name=”B";commit;提交事务则转账成功回滚只是回到commit提交之后的位置rollback;事务中所有sql语句执行正确则需要自己手动提交commit；否则有任何一条执行错误，需要自己提交一条rollback，这时会回滚所有操作，而不是commit会给你自动判断和回滚。mysql解决办法：设定一个保留点(插入总要的数据记录)savepoint 保留点名字;使用ROLLBACK TO 保留点名字;回滚到一个保存点。使用RELEASE 保留点名字;删除一个保存点，但是保留该保存点建立后执行的命令的效果。保存点只能在一个事务块里面建立。在一个事务里面可以定义多个保存点。
注：事务中所有sql语句执行正确则需要自己手动提交commit；否则有任何一条执行错误，需要自己提交一条rollback，这时会回滚所有操作，而不是commit会给你自动判断和回滚。
mysql解决办法：设定一个保留点(插入总要的数据记录)
保存点只能在一个事务块里面建立。在一个事务里面可以定义多个保存点。
MYSQL编码问题
如果出现中文乱码my.cnf文件 一般在/etc/my.cnf这个路径（这个不绝对，要看你安装时候的设置）在配置文件里面修改[mysqld]character-set-server = utf8[mysql]default-character-set = utf8[client]default-character-set = utf8修改保存完，记得重启服务在mysql解释行终端修改查看你mysql版本的详细信息用\s mysql> \s--------------mysql Ver 14.14 Distrib 5.6.29, for Linux (x86_64) using EditLine wrapperConnection id: 3Current database: Current user: root@localhostSSL: Not in useCurrent pager: stdoutUsing outfile: ‘‘Using delimiter: ;Server version: 5.6.29-log Source distributionProtocol version: 10Connection: Localhost via UNIX socketServer characterset: utf8mb4Db characterset: utf8mb4Client characterset: utf8Conn. characterset: utf8UNIX socket: /tmp/mysql.sockUptime: 7 hours 37 min 5 secThreads: 1 Questions: 85 Slow queries: 0 Opens: 77 Flush tables: 1 Open tables: 69 Queries per second avg: 0.003--------------修改库的默认字符编码alter database 库名 default character set utf8修改表的默认字符编码alter table 表名 default character set utf8
只是简单的基础，
做好当下
IF you fail,don‘t forget to learn your lesson
。。。。。
mysql进阶