慕课网 mysql

3-6  修改数据表--添加约束

添加约束<br>
1.ALTER TABLET tb_1 ADD PRIMARY KEY (字段名称)//为某一字段添加主键
2.ALTER TABLET tb_1 ADD UNIQUE KEY (字段名称)//为某一字段添加唯一约束
3.2.ALTER TABLET tb_1 ADD FOREIGN KEY (字段名称) references tb_2(id)//为某一字段添加外键
删除默认约束
1.ALTER TABLE tb_1 alter age set default 10;//设置age字段默认值为10;

2.ALTER TABLE tb_1 alter age drop default;//删除age字段默认值为10;

3-7 修改数据表--删除约束

删除主键约束：
ALTER TABLE users2 DROP PRIMARY KEY;//后面不加字段名称是因为主键只有一个
查看约束的名字：SHOW INDEXES FROM tb_name\G;// \G网格显示数据

删除唯一约束：ALTER TABLE users2 DROP INDEX username;//这里的username是被添加 了唯一约束的字段。

查看外键约束的名称：SHOW CREATE TABLE tb_name;
这里得到系统赋予的外键约束的名称为：user2_ibfk_1;
删除外键约束：ALTER TABLE user2 DROP FOREIGN KEY user2_ibfk_1;

顺便删除索引：ALTER TABLE user2 DROP INDEX pid;

3-7 修改数据表--修改列定义和更改数据表

修改列定义(类型或位置)： alter table tbl_name MODIFY col_name
             column_definition [first|after col_name]
              由大类型改为小类型可能造成数据丢失
修改列名称：alter table tbl_name CHANGE old_col_name
            new_col_name column_definition [first|after col_name]
数据表更名：
   方法1： alter table tbl_name RENAME [To|AS]  new_tbl_name
   方法2： RENAME table tbl_name TO new_tbl_name  

           [,tabl_name2 TO new_tbl_name2 ]....

3-9 总结

约束划分：
按功能：NOT NULL（非空约束，仅列级）、PRIMARY KEY（主键约束）、UNIQUE KEY（唯一约束）、
DEFAULT（默认约束，仅列级）、FOREIGN KEY（外键约束）
按数据列的数目：表级约束、列级约束

修改数据表：
针对字段的操作：添加/删除字段、修改列定义、修改列名称等
针对约束的操作：添加/删除各种约束

针对数据表的操作：数据表更名（两种方式）

4.操作数据表中的记录

插入记录insert 

方式1： insert [into] tbl_name [(col_name,...)] {values|value}
    ({expr|default},...),(...),...
    注：为自增长字段赋值时采用Null或default
方式2： insert [into] tbl_name SET col_name={expr|default},...
        注：与方式1区别在于，此方法可以使用子查询（SubQuery） 
方式3： insert [into] tbl_name [(col_name,...)] select ...

更新记录update

单表更新： update [low_priority] [ignore]  table_reference set
           col_name1={expr1|default} [,col_name2={expr2|default}]...

  [where where_condition] 

删除记录delete

  单表删除：  delete from tbl_name [where where_condition]
  注：删除后auto_increment按照原来已经递增到的数字继续

查询表达式解析select

  查找记录 
  select select_expr[,expr,...]
  [
    from table_references
    [where where_condition]
    [group by {col_name|position} [ASC|DESC] ,...]
    [having where_condition]
    [order by {col_name|expr|position} [ASC(默认)|DESC] ,...]
    [limit {[offser],row_count|row_count offset offset}]
  ]
  注 ：[having where_condition]  条件中字段要么已经出现在select中，要么是聚合函数

       [limit {[offser],row_count|row_count offset offset}]//限制查询结果返回的数量

5.子查询与连接

查看准备数据

（1）SELECT * FROM tdb_goods\G; // 网格形式输出查询结果
（2）由于存储时使用的编码方式为utf-8格式；客户端默认的为GBK格式，所以
需要使用 SET NAMES GBK; 设置读取信息的编码格式。（存储数据的属性没有不变化）

使用比较运算符的子查询

（1）SELECT AVG( price ) FROM goods;
（2）SELECT ROUND（ AVG(price),2 ）; // 它是个聚合函数——AVG求平均值进行四舍五入，保留 2 位小数
（3）SELECT id,name,price FROM goods WHERE price >= (SELECT ROUND(AVG(price),2) FROM goods); // 子查询方式
（4）在子查询时的关键字ANY / SOME / ALL
select goods_name,goods_price from tdb_goods where goods_price >= ANY (select goods_price from tdb_goods where goods_cate='超级本');
注意：
1. 子查询只能返回一行记录，不然会报错。
2. 子查询还可以包含子查询。
关键字 ANY SOME ALL
>、>= 最小值 最小值 最大值
<、<= 最大值 最大值 最小值
= 任意值 任意值
<>、！= 任意值

使用[not] in 的子查询

in 相当于=any
not in 相当于 ！=all 或者<>all ——不等于、不包含

子查询----exists not exists ——用得比较少
子查询返回任何行 exists 返回 true 反之 返回 false
-- = ANY 或 = SOME 等价于 IN
SELECT goods_id,goods_name,goods_price FROM tdb_goods
WHERE goods_price IN (SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本')

使用INSERT ... SET ...可以使用子查询

INSERT [INTO] tbl_name [(col_name,...)] SELECT ...将查询结果写入数据表
Eg：
insert into tdb_goods_cates(cate_name) select goods_cate from tdb_goods group by goods_cate;

多表更新之一步到位

创建数据表同时将查询结果写入到数据表 
create table [if not exists] tbl_name
[(create_definition,...)]
select_statement

连接的语法结构

1.语法结构

table reference A
{[INNER|CROSS] JOIN | {LEFT|RIGHT} [OUTER] JOIN}
table_reference B
ON condition_expr
2.数据表参照
table_reference
tbl_name [[AS] alias] | table_subquery [AS] alias
数据表可以使用tbl_name AS alias_name 或 tbl_name alias_name赋予别名。
table_subquery可以作为子查询使用在FROM子句中，这样的子查询必须为其赋予别名。

3.内连接 inner join:仅显示符合条件的连接

       使用内连接查找的记录在连接数据表中不存在，并且在WHERE子句中尝试一下操作：        column_name IS NULL 。如果 column_name 被指定为 NOT NULL，MySQL将在找到符         合连接着条件的记录后停止搜索更多的行（查找冲突）
  左外连接:显示左表中的全部记录和右表中符合连接条件的记录
  右外连接:显示右表中的全部记录和左表中符合连接条件的记录
      若某字段只存在某一表，则另一表的里字段返回 NULL

4.自身连接：同一个数据表对其自身进行连接

5.多表删除： DELETE tbl_name[.*] [, tbl_name[.*] ...] FROM table_references [WHERE where_definition]

6  运算符和函数

6-1 字符函数

（1）CONCAT函数 字符连接
SELECT CONCAT('IMOOC','-','MySQL');
SELECT CONCAT (first_name,last_name) AS fullname FROM test;
（2）CONCAT_WS() 使用指定的分隔符进行字符连接，（第一个位置指定分隔符，后面的 为分割的内容）
SELECT CONCAT_WS('%','asdsa','asdasda'); //第一个是指定的分隔符
（3）FORMAT() 数字格式化
例如：SELECT FORMAT(123560.75,2); 123,560.75
（4）LOWER() 转化小写
（5）UPPER() 转换大写
SELECT UPPER('mysql');
（6）LEFT() 获取左侧字符
SELECT LEFT ('MYSQL',2); MY
SELECT LOWER(LEFT('mYsql',2)); //函数嵌套
（7）RIGHT()获取右侧字符
（8）LENGTH()取得字符串长度
LENGTH('MYSQL’);
（9）TRIM() 删除前导、后续空格或者指定字符:
SELECT TRIM(' MySQL ');
SELECT TRIM(LEADING '?' FROM '??MYSQL???'); //删除前导？
SELECT TRIM(TRAILING '?' FROM '??MYSQL???'); //删除后续？
SELECT TRIM(BOTH '?' FROM '??MYSQL???'); //前后均删除，但不能删除中间的?
（10）REPLACE() 替换 :
SELECT REPLACE('MY???SQL','?',''); //将问号换成空白，即去掉？
SELECT REPLACE('MY???SQL','?','~~'); //可以将n个？换成m个组合符号
（11）SUBSTRING() 字符串截取
SELECT SUBSTRING('mYSQL',1,2); //从字符串中的第一个位置开始截取2个字符mY
SELECT SUBSTRING('mYSQL',3); //SQL 从第3的位置开始截取到结束
SELECT SUBSTRING('mYSQL',-2); //QL 从倒数第2位开始截取到结束

数值运算符与函数

1、CEIL(数值); 说明：就是向上取整，如：SELECT CEIL(3.01);结果是4；
2、FLOOR(数值); 说明：就是向下取整，如：SELECT FLOOR(3.99);结果是3；
3、DIV,例子：SELECT 3 DIV 4; 结果是0；因为3除以4，整数位为0；
4、MOD,相当于C语言的%取余函数运算符，也可以用%号代替；例子：SELECT 4 MOD 3; 结果为1；SELECT 5.3 MOD 3; 结果为2.3；
5、POWER(数值,数值);例子：SELECT POWER(3,3); 结果为27；
6、ROUND(数值,小数的位数)
7、TRUNCATE(数值，截取位数); 说明：和ROUND()相似，只是不四舍五入，截取位数还能是负数，如：SELECT TRUNCATE(125.68,-1); 结果为120；

比较运算符

（1）[ NOT] BETWEEN ... AND ... [不 ]在。。。范围之内
SELECT 35 BETWEEN 1 AND 22; // 0 false
（2）[ NOT ] IN() [ 不]在列出值范围内
SELECT 13 IN (5,10,15,20); // 0
SELECT 10 IN (5,10,15,20); // 1
（3）IS [ NOT ] NULL 
SELECT * FROM test WHERE first_name IS NULL;

日期时间函数

NOW() /*当前时间 含日期时间
CURDATE() /* 当前日期 只有日期
CURTIME() /*当前时间 值有时间
DATE_ADD() /*时间增减或减少
SELECT DATE_ADD('2014-3-12',INTERVAL 365 DAY); ==>2015-3-12 /*在原有给定的时间上增加365天
INTERVAL增加可以增加负值 单位 year ,month,week,day
SELECT DATEDIFF('2014-1-1','2015-1-1') ==> -365 /*时间差值计算 单位为日 前面时间减去后面时间
SELECT DATE_FORMAT('2014-3-2','%m/%d/%d'); ==> 03/02/2014 /*日期格式转换

信息函数

（1）CONNECTION_ID(); // 连接ID
（2）SELECT DATABASE(); // 当前数据库
（3）LAST_INSERT_ID(); // 最后句插入记录的 ID 号，如果是一次insert中插入的是多条记录，得到的是多条中的第一条（而不是最后一条！）
（4）VERSION(); // 版本的信息
（5)USER(); // 当前用户

聚合函数

AVG()求平均值
count()计数
MAX()最大值
MIN()最小值
SUM()求和

加密函数

MD5()用于加密对外的密码
PASSWORD()用于加密客户端自己的密码

7.自定义函数

1.自定义函数

用户自定义函数(user-defined function, UDF)是一种对MySQL扩展的途径, 其用法与内置函数相同。
自定义函数的两个必要条件:
a.参数
b.返回值：只有一个
函数可以返回任意类型的值,同样可以接受这些类型的参数；
函数的参数与返回值之间，没有必然的联系。

2.创建自定义函数

CREATE FUNCTION function_name
RETURNS
{STRING|INTEGER|REAL|DECIMAL}
routine_body - 函数体

3.关于函数体

(1)函数体由合法的SQL语法构成;
(2)函数体可以是简单的SELECT或INSERT语句;
(3)函数体如果为复合结构则使用BEGIN...END语句；
(4)复合结构可以包括声明，循环，控制结构。

无参数

create function f1() returns varchar(30)
return date_format(now(),'%Y %m %d : %H %i %s');

select f1();
drop function f1;

含参数

create function f2(num1 smallint unsigned,num2 smallint unsigned)
returns float(10,2) unsigned
return (num1+num2)/2;

select f2(2,3);

复合结构

在函数体如果有多个语句需要执行 需要 用BEGIN..END来构成聚合体
delimiter // ：表示用//来做结束标记 替代 默认的;
CREATE FUNCTION adderuser(username VARCHRA(20))
RETURNS INT UNSINGED
BEGIN
INSERT test(username) VALUES(username);
RETURN LAST_INSERT_ID();
END
//

8.存储过程

存储过程是SQL语句与控制语句的【预编译集合】，以【一个名称存储】作为【一个单元处理】

优点：

· 增强了语句的功能和灵活性：可以通过控制语句对流程进行控制和判断
· 实现较快的执行速度，只在【第一次调用时进行语法分析和编译】 ，以后直接从内存中得到结果
· 减少网络流量

存储过程语法结构分析

CREATE   [DEFINER = {user|CURRENT_USER}]
PROCEDURE sp_name ([proc_parameter[,...]]) //可以带0到多个参数
[characteristic ...]
 routine_body
其中参数
proc_parameter:
[IN | OUT | INOUT] param_name type
IN, 表示该参数的值必须在调用存储过程时指定
OUT, 表示该参数的值可以被存储过程改变，并且可以返回
INOUT, 表示该参数的值调用时指定，并且可以被改变和返回

2.调用存储过程

CALL sp_name([parameter[,...]]) - 带参数的存储过程的调用
CALL sp_name[()] - 不带参数的存储过程调用

3.删除存储过程

DROP PROCEDURE [IF EXISTS] sp_name;

4.创建存储过程

创建没有参数的存储过程

CREATE PROCEDURE sp1() SELECT VERSION();
调用：CALL sp1;
      CALL sp1();

创建带有INT类型参数的存储过程

DELIMITER //

CREATE PROCEDURE removeUserByID(IN id INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id = id;
END
//
调用：CALL removeUserById(3); //参数名称最好不要和表中的字段相同

创建带有IN OUT类型参数的存储过程

CREATE PROCEDURE removerUserAndReturnUserName(IN showID INT UNSIGNED,OUT showName INT UNSIGNED)
BEGIN
DELETE FROM user WHERE id = showID;
SELECT count(ID) FROM user INTO showName;
END
//
调用：CALL removerUserAndReturnUserName(10,@nums); /** @nums 所代表的就是用户变量，可用 SELECT @nums 输出 */
SELECT count(ID) FROM user INTO showName; /** 该语句中的 INTO 含义就是将 SELECT 语句结果的表达式返回到 showName 变量中 */

SELECT @nums; //@nums - 就是用户变量，只能存在于当前用户所使用的客户端有效。
DECLARE  声明的变量都是在BEGIN与END之间，是局部变量
SET @i = 7; //通过@或SET设置的变量称为用户变量

创建带有多个OUT类型参数的存储过程

ROW_COUNT() 系统函数 得到插入删除以及更新的被影响到的行数

CREATE PROCEDURE rmUserByAgeAndRtInfos(IN p_age SMALLINT UNSIGNED, OUT delNums SMALLINT UNSIGNED, OUT leftNums SMALLINT UNSIGNED)
BEGIN
DELETE FROM users WHERE WHERE age = p_age; //注意变量不同
SELECT ROW_COUNT() INTO delNums;
SELECT COUNT(id) FROM users INTO leftNums;
END
//
调用：CALL rmUserByAgeAndRtInfos(20, @a, @b);
      SELECT @a, @b;

存储过程与自定义函数的区别

A、存储过程实现的功能相对复杂，经常针对表做操作；函数针对性较强，很少用于对表做操作；
B、存储过程可以返回多个值，函数只能有一个返回值
C、存储过程一般独立执行，函数可以作为 sql 语句的组成部分来出现

修改存储过程: 只能修改属性

9.存储引擎

（1）MySQL可以将数据以不同的技术存储在文件（内存）中，这种技术就成为存储引擎。
每种存数引擎使用不同的存储机制、索引技巧、锁定水平，最终提供广泛且不同的功能。
（2）使用不同的存储引擎也可以说不同类型的表
（3）MySQL支持的存储引擎
1. MyISAM
2. InnoDB
3. Memory
4. CSV
5. Archive

并发控制：

（1）当多个连接对记录进行修改时保证数据的一致性和完整。
（2）
1. 共享锁(读锁) ： 在同一段时间段内，多个用户可以读取同一个资源，读取过程中数据不会发生任何变化
2. 排他锁（写锁） ：在任何时候只能有一个用户写入资源，当进入写锁时会阻塞其他的读锁或者写锁操作。
锁颗粒：
表锁：是一种开销最小的锁策略
行锁：是一种开销最大的锁策略，能提供最大的并发操作

事务处理：

整个过程每一个单元全部完成才算事务处理成功，某一个单元失败事务就会回滚.
主要作用：保证数据库的完整性
事务的特性：
原子性、一致性、隔离性、持久性
简称：A(Atomic)C(Consistency)I(Isolation)D(Durable)

使用最多的：MyISAM，InnoDB
CSV存储引擎：以逗号为分隔符，不支持索引；
BlackHole：黑洞引擎，写入的数据都会消失，一般用于做数据复制的中继；
MyISAM：适用于事务的处理不多的情况；
InnoDB：适用于事务处理比较多，需要有外键支持的情况。

设置存储引擎

（1）通过修改MySQL配置文件实现
default-storage-engine = engine
（2）通过创建数据表命令实现
CREATE TABLE table_name(\
...
) ENGINE = engine;
（3）通过修改数据表命令实现
ALTER TABLE table_name ENGINE [=] engine_name;