SQL基本语句学习

========================13:36 2015/8/21


约束和修改数据表


约束-为了保证数据的完整性和一致性
表级约束和列级约束
类型
NOT NULL    非空约束
PRIMARY KEY 主键约束
UNIQUE KEY  唯一约束
DEFAULT     默认约束
FOREIGN KEY 外键约束
外键约束：实现数据表的一对一或一对多关系
要求：1、父表和子表必须使用相同的存储引擎InnoDB,禁止使用临时表
2、外键列参照列必须具有相似的数据类型，其中数字的长度或是否有符号必须相同；二字符的长度则可以不同。
3、外键列和参照列必须创建索引。如果外键列不存在索引的话，MTSQL将自动创建索引。


mysql配置文件-存储引擎
default-storage-engine =INNODB
重启mysql


CREATE TABLE users(
...
pid SMALLINT UNSIGNED,    --类型要一样
FOREIGN KEY (pid)PEFERENCES provinces (id)
);


SHOW ENDEXES FORM provinces\G;  --显示索引


添加单列
ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST|AFTER col_name]
ALTER TABLE users ADD password VARCHAR(32) NOT NULL AFTER username;
删除单列
ALTER TABLE users DROP age;


增加行
ALTER TABLE user2 ADD id SMALLINT UNSIGNED;
添加主键
ALTER TABLE user2 ADD CONSTRAINT PK_user2_id PRIMARY KEY (id);
添加唯一约束
ALTER TABLE user2 ADD UNIQUE (username);
添加外键约束
ALTER TABLE user2 ADD PROEIGN KEY (pid) PREFERENCES provinces (id);


显示表的创建代码
SHOW CREATE TABLE user2;


ALTER TABLE user2 ALTER age DEFAULT 15;


删除DEFAULT约束
ALTER TABLE user2 ALTER age DROP DEFAULT;


修改数据表--删除约束
删除主键约束
ALTER TABLE user2 DROP PROMARY KEY;
删除唯一约束
SHOW INDEXES FROM user2; --显示唯一约束
ALTER TABLE user2 DROP INDEX username;
删除外键约束
SHOW CREATE TABLE user2; --查看外键约束名字
ALTER TABLE user2 DROP FOREIGN user2_ibfk_1;
删除索引
ALTER TABLE user2 DROP INDEX pid;


修改数据表--修改列定义和更名数据表名
ALTER TABLE user2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST; --位置放到第一


ALTER TABLE user2 CHANGE pid p_id TINYINT UNSIGNED NOT NULL;


ALTER TABLE user2 RENAME user3;


RENAME TABLE user3 TO user2;
============================================
查询记录
SELECT select_expr[,select_expr...]
[
FROM table_references
[WHERE where_condition]
[GROUP BY {col_name|opsition}[ASC|DESC,...]]
[HAVING where_condition]
[ORDER BY {col_name|expr|posion}[ASC|DESC,...]]
[LIMIT] {[offset],col_count|row_count OFFSET offset}]
]


查询结果分组


聚合函数
永远只有一个返回结果。平均值，最大值。


倒序查询
SELECT * FROM user ORDER BY id DESC;


SELECT * FROM users ORDER BY age,id DESC;


limit 限制查询结果返回的数量
SELECT * FROM users LIMIT 2;
SELECT * FROM users LIMIT 2,2;


查找结果插入到表中
INSERT test(username) SELECT username FROM users WHERE age >= 30


INSERT UPDATE DELETE SELECT
===================================2015/8/21 14:05:51
SELECT * FROM tdb_goods\G; --网格显示


SET NAMES gbk;


子查询Subquery 先在其他SQL语句内的SELECT子句
SLECT * FROM t1 WHERE col1 =(SELECT col2 FROM t2);
嵌套在查询内部的查询
返回值可以是标量、一行、一列或子查询
SELECT AVG(goods_price) FROM tdb_goods;  --求平均值
SELECT ROUNG(AVG(goods_price),2) FROM tdb_goods; --留两位
比较运算符
=、>、<、>=、<=、<>、!=、<=>
修饰比较运算符
ANY   --符合其中一个
SOME  --符合其中一个
ALL   --全部满足
**** >= ANY *******条件
[NOT]  !=ALL  都是子查询
子查询返回任意行，EXISTS将返回TRUE;否则为FALSE.
新建表格-做分类
SELECT goods_cate FROM tdb_goods GROUP BY good_cate;
INSERT [INTO] tbl_name [(...)] SELECT ...
INSERT tdb_goods_cates(cate_name) SELECT good_cateS FORM tdb_goods GROUP BY good_cate;


多表更新
参照类型表的更新商品表
表的参照方式JOIN
连接类型
1、内连接JOIN / CROSS JOIN / INNER JOIN
UPDATE tbd_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name SET goods_cate = cate_id;
仅显示符合连接条件的记录
2、左外连接 LEFT [OUTER] JOIN
显示左表中的全部和右边中的符合连接条件的记录
3、右外连接 RIGHT [OUTER] JOIN
显示右表中的全部和左边中的符合连接条件的记录
CREATE...SELECT 
创建数据表的同时查询结果写入到数据表
CREATE TABLE tdb_goods_brands(
brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
brand_name VARCHAR(40) NOT NULL)
SELECT brand_name FROM tdb_goods GROUP BY brand_name;
参照品牌表更新商品表,
UPDATE tdb_goods INNER JOIN tdb_goods_brands ON brand_name = brand_name SET brand_name = brand_id;  --失败，有两个brand_name，使用起别名
UPDATE tdb_goods AS g INNER JOIN tdb_goods_brands AS b ON g.brand_name = b.brand_name SET g.brand_name = b.brand_id;
改变表的结构
ALTER TABLE tdb_goods
CHANGE good_cate cate_id SMALLINT UNSIGNED NOT NULL,
CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;


连接，多表查、删、改
table_reference
{[INNER|CROSS]JOIN|{LEFT|RIGHT}[OUTER] JOIN}
table_reference
ON conditional_expr


ON 来设定连接条件
WHERE 结果集记录的过滤


多表连接 3个
SELECT goods_id,goos_name,cate_name,brand_name,goods_price FROM tdb_goods AS g 
INNER JOIN tdb_goods_cates AS c ON g.cate_id =c.cate_id
INNER JOIN tdb_goods_brands AS b ON g.cate_id =b.cate_id
--存储的是int，查询的时候连接，获取其他表的信息


无线分类数据表--多表连接
CREATE TABLE tbd_goods_types(
type_id SMALLINT UNSIGNED PRIMARY KEY AURO_INCREMENT,
type_name VARCHAR(20) NOT NULL,
parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0
);


多表删除
DELETE t1 FORM tdb_goods AS t1 LEFT JOIN (SELECT goods_id,goods_name FORM tdb_goods GROUP BY goods_name HAVING count(good_name)>=2) AS t2 ON t1.goods_name =t2.goods_name WHERE t1.goods_id > t2.goods_id;


===================2015/8/22 12:50:21
1、字符函数
2、数值运算符与函数
3、比较运算符与函数
4、日期时间函数
5、信息函数
6、聚合函数
7、加密函数


字符函数
SELECT CONCAT('imooc','MYSQL');  --字符连接
SELECT CONCAT(name,age) AS fullname FROM test;
  
  SELECT CONCAT_WS('|','imooc','MYSQL','Funtion');   --imocc|MYSQL|Funtion
  
  SELECT FORMAT(123.87,2);  --保留小数点位数
  
  LOWER()  --转化为小写
  UPPER()  --大写
  
  SELECT LEFT('MYSQL',2) RIGHT('MYSQL',2)   --获取左侧右侧的字符
SELECT LENGTH('MYSQL')   --获取字符长度
SELECT LTRIM('  MYSQL')  --删除前导空格
SELECT RTRIM('MYSQL  ')  --删除后续空格
SELECT TRIM(LEADING '?' FROM '??MYSQL???');  --删除字符串前导中的?字符
SELECT TRIM(TRAILING '?' FROM '??MYSQL???'); --后续
SELECT TRIM(BOTH '?' FROM '??MYSQL???');     --全部删除

SELECT REPLACE('??MY??SQL???','?','');  --替换

SELECT SUBSTRING('MYSQL','1','2');  --MY  从第一个获取两个字符

[NOT] LIKE 模式比配
SELECT 'MYSQL' LIKE 'M%';
SELECT * FROM test WHERE name LIKE '%o%';  --%为通配符
SELECT * FROM test WHERE name LIKE '%1%%' ESCAPE '1'; --1后面的%不是通配符

% 代表任意个字符
_ 代表任意一个字符

数值运算符和函数
SELECT CEIL(3.01);  --4 进一取整
SELECT FLOOR(3.01); --2 舍一取整

SELECT 3 DRV 4; --整数除法
MOD --去余数
POWER(3,3); --幂乘法
ROUND(3.652,2) --四舍五入
TRUNCATE(125.89,1) --数字截取

比较运算符和函数
[NOT] BETWEEN...AND --[不] 在什么之间
SELECT 35 BETWEEN 1 AND 22;

SELECT 10 IN(5,10,15,20); --10在不在他们之间

SELECT '' IS NOT NULL;

日期时间函数--很少用到
NEW()  --当前日期和时间
CURDATE() --当前日期
CURTIME() --当前时间
DATE_ADD() --日期变化
DATEDIFF() --日期差值
DATE_FORMAT() --日期格式化

SELECT DATE_ADD('2014-3-12',INTERVAL 365 DAY); --加上356天
SELECT DATE_ADD('2014-3-12',INTERVAL -365 DAY); --减上356天
SELECT DATE_ADD('2014-3-12',INTERVAL 1 YEAR);
SELECT DATE_ADD('2014-3-12',INTERVAL 3 WEEK);

SELECT DATEDIFF('2013-3-12','2014-3-12');

SELECT DATE_FORMAT('2014-3-12','&m/%d/Y'); --03/12/2014

信息函数
SELECT CONNECTION_ID --连接线程ID
SELECT DATEBASE();   --当前数据库
LAST_INSERT_ID()     --最后插入记的ID号
USER()               --打印当前用户
VERSION()            --版本信息

聚合函数
AVG() --平均值
COUNT() --计数
MAX()
MIN()
SUM()

加密函数
MD5() --信息摘要算法 web页面做准备
PASSWORD() --密码算法


-------------------
自定义函数
必要条件：1、参数 2、返回值（任意类型）

创建无参数
CREATE FUNCTION f1() RETURNS VARCHAR(30)
RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒');
调用
SELECT f1();


创建带参数
CREATE FUNCTION f2(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED)
RETURNS FLOAT(10,2) UNSIGNED
RETURN (num1+num2)/2;
调用
SELECT f2(10,15);


--DELIMITER //  结束符为//


创建具有复合结构函数体
CREATE FUNCTIONG adduser(username VARCHAR(20))
RETURNS INT UNSIGNED
BEGIN
INSERT test(username) VALUES(username);
RETURN LAST_INSERT_ID();
END
//
调用
SELECT adduser('Rose');


删除函数
DROP FUNCTION [IF EXISTS] function_name


==============================2015/8/22 14:23:01
存储过程    ！！！是不是可以用作登录？？？？
存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。
优点：
1、增强SQL语句的功能和灵活性
2、实现较快的执行速度
3、减少了网络流量

|--------------------------------------------------
| SQL命令--》MYSQL引擎--分析--》语法正确
| |
| ↓
| 返回客户端《----执行结果《--执行--可识别命令
|--------------------------------------------------


创建存储过程-不带参数  --不能创建数据库和表
CREATE PROCEDURE sp1() SELECT VERSION();
调用存储过程
CALL sp1();


创建带IN参数的存储过程     传递的参数不能喝数据表中参数同名
DELIMITER //
CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id = p_id;
END
//
DELIMITER ;
调用存储过程
CALL removeUserById(3);
删除存储过程
DROP PROCEDURE [IF EXISTS] sp_name


创建自带IN和OUT类型参数的存储过程
DELIMITER //
CREATE PROCEDURE removeUserAndReturnUserNums(IN p_id INT UNSIGNED,OUT userNums INT UNSIGNED)
BEGIN   --局部变量
DELETE FROM users WHERE id = p_id;
SELETE count(id) FROM users INTO usersNums;
END
//
DELIMITER ;
调用存储过程
CALL removeUserAndReturnUserNums(3,@nums);
SELECT @nums;
-- 使用SET 和@ 定义的变量是用户变量


创建带有多个OUT类型参数的存储过程
      SELECT ROW_COUNT(); --获取被修改的所有总数
DELIMITER //
CREATE PROCEDURE removeUserByAgeAndReturnInfos(IN p_age SMALLINT UNSIGNED,OUT deleteUser SMALLINT UNSIGNED,OUT userCounts SMALLINT UNSIGNED)
BEGIN   --局部变量
DELETE FROM users WHERE age = p_age;
SELECE ROW_COUNT() INTO deleteUsers;
SELECT COUNT(id) FROM users INTO userCounts;
END
//
DELIMITER ;
调用存储过程
CALL removeUserByAgeAndReturnInfos(20,@a,@b);
SELECT @a,@b;


存储过程与自定义函数的区别
存储过程实现的功能要复杂一些；二函数的针对性更强
存储过程可以返回多个值，函数只能返回一个值
存储过程一般独立来执行；而函数可以作为其他SQL语句的组成部分来出现


修改存储过程，只能修改一些简单的过程体


----------------------------------------2015/8/22 17:05:35
存储引擎
MYSQL可以将数据以不同的技术存储在文件（内存）中，这种技术就称为存储引擎。
每一种存储引擎使用不同的存储机制、索引技巧、锁定水平，最终提供广泛且不同的功能。

1、MyISAM
适用于事务的处理不多的情况
存储限制可达256TB，支持索引，表级锁定、数据压缩
2、InnoDB
适用于事务处理比较多，需要有外键支持的情况
存储限制为64TB，支持事务和索引，锁颗粒为行锁
3、Memory
4、CSV
5、Archive


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

事务处理
事务是区别于文件系统的只要区别之一，用于保证数据库的完整性
转账 ---------这个很有用处！！！！！！！！！！
用户A  ----200元----》   用户B
特征：
原子性 Atomicity
一致性 Consistency
隔离性 Isolation
持久性 Durability

外键和索引
索引   ------还有终极课程？？？这个也很重要！！！！！
是对数据表中一列或多列的值进行排序的一种结构。
书的目录，可以快速查找。


BlockHole 写入会消失，用来复制


设置存储引擎
1、default-storage-engine = engine
2、CREATE TABLE tp1(
s1 VARCHAR(10)
)ENGINE = MyISAM;

3、ALTER TABLE tbale_name ENGINE [=] engine_name;


------------------------------2015/8/22 23:07:49
MYSQL 管理工具


phpMyAdmin
网页的，去官网下载
Apachar2

Navicat for MySQL
一般般，可备份，可导出、导入。。这个可以用来和WINDOW SERVER 交互
MySQL Workbench
官网的
=================================END======2015/8/22 23:28:31