mysql数据库基本学习

1.jdbc连接mysql数据库


2.修改MySQL\MySQL Server 5.0\my.ini配置文件，修改字符集default-character-set=utf8，
修改存储引擎default-storage-engine=INNODB(若配置过，就不用修改了)


3.自增长的字段必须是主键，外键的两个属性必须相似


4.insert语句，当所有字段都要插入的时候，可以省略，自增长的可以写成null/default，into可以省略
多个插入values(),(),(),有的字段没有录入可以写成default，另外insert还有insert set,insert select，
但不常用,insert select:INSERT other SELECT id,username FROM USER WHERE id>1(将查询的结果写进指定的表)


5.SELECT sex FROM user GROUP BY sex/1


6.having条件的字段必须出现在select中或者是聚合函数,SELECT sex FROM USER GROUP BY sex HAVING MAX(id)>1


7.order by，默认asc，SELECT * FROM USER ORDER BY age,id DESC


8.limit,两种语法形式SELECT * FROM USER LIMIT 2;(多少条，从第一条开始)，SELECT * FROM USER LIMIT 2,2；(从第几条开始,0是起始项;多少条)


9.having只能和group by连用，having后面只能是聚合函数或者是select中出现的字段


10.SELECT ROUND(AVG(price),2) FROM goods:四舍五入，2位小数


子查询(必须出现在小括号里):
any/some,all...比较运算符：=,!=,>=,<=
1.SELECT name FROM goods WHERE price >= (SELECT ROUND(AVG(price),2) FROM goods):括起来


2.any/some,all用于子查询any与some等价,SELECT name FROM goods WHERE price >= ANY(SELECT price FROM goods WHERE cate='笔记本')




not in.in
1.SELECT name FROM goods WHERE price NOT IN(SELECT price FROM goods WHERE cate = '笔记本')


exists/not exist
1.如果子查询返回任何航，exists将返回true，否则为false)(不多用)


多表更新
方式一：建表，UPDATE goods INNER JOIN goods_cates ON cate=goods_cates.name SET cate=goods_cates.id，参照别的表，更新本表


方式二：CREATE TABLE goods_brands(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(40) NOT NULL
)
SELECT brand_name NAME FROM goods GROUP BY brand_name：建表，插入，更高效，select和create的字段名要一致


连接(外键的逆向操作)
内连接，仅显示连接的记录,SELECT goods_cates.id,goods_cates.name FROM goods_cates INNER JOIN goods ON goods_cates.id=goods.cate_id;
外连接
左外连接，显示左边的全部记录及右表符合连接条件的记录
SELECT goods_cates.id,goods_cates.name FROM goods_cates LEFT JOIN goods ON goods_cates.id=goods.cate_id;
右外连接，显示右边的全部记录及右表符合连接条件的记录
SELECT goods_cates.id,goods_cates.name FROM goods_cates RIGHT JOIN goods ON goods_cates.id=goods.cate_id;


多表查询
SELECT g.id,g.name,gc.name,gb.name,price FROM goods g INNER JOIN goods_brands gb INNER JOIN goods_cates gc 


无限分类的数据表设计
SELECT s.id,s.name,COUNT(p.name) parent_name FROM goods_types s LEFT JOIN goods_types p ON s.parent_id=p.id GROUP BY s.name ORDER BY id


多表删除
DELETE t1 FROM goods t1 LEFT JOIN (SELECT id,NAME FROM goods GROUP BY NAME HAVING COUNT(NAME)>1) t2 ON t1.name=t2.name WHERE t1.id>t2.id




运算符和函数
字符函数：
concat():SELECT CONCAT('guo','-','hao');字符连接
concat_ws():SELECT CONCAT_WS('-','a','b','c');使用指定的分隔符进行字符连接
format():SELECT FORMAT(1234560.75,1);数字格式化，小数点的位数
lower():小写函数；
upper():大写函数；
left():SELECT UPPER(LEFT('mysql',2))获取左边字符;
right():获取右边字符;
length():获取字符串长度
ltrim():删除前导空格
rtrim()：删除后导空格
trim():删除前后导空格，SELECT TRIM(LEADING '?' FROM '??mysql??')：删除前导'?',SELECT TRIM(TRAILING '?' FROM '??mysql??'),SELECT TRIM(BOTH '?' FROM '??mysql??')
replace():SELECT REPLACE('??my??sql???','?','')
substring():SELECT SUBSTRING('mysql',1,2),(pos,length),SELECT SUBSTRING('mysql',1),SELECT SUBSTRING('mysql',-1),倒着数
[not] like:SELECT 'mysql' LIKE 'm%',SELECT * FROM USER WHERE username LIKE '%1%%' ESCAPE '1',escape告诉1后面一个的不需要当成通配符了
数值运算符：
ceil():上取整
floor()：下取整
div：整数除法 selecr 3 div 4
mod：取模，对证书和浮点数都行
power():幂运算,power(3,3)
round():四舍五入
truncate():数字截断,truncate(125,89,1)=125.8
[not] between ... and
[not] in():select 13 in(5,10,15,20)
is [not] null


日期函数
now():当前日期和时间
curdate():当前日期
curtime():当前时间
date_add():SELECT DATE_ADD('2014-3-12',INTERVAL 365 DAY),SELECT DATE_ADD('2014-3-12',INTERVAL -365 DAY)
datediff():SELECT DATEDIFF('2013-3-12','2014-3-11'),日期相差天数
date_format():SELECT DATE_FORMAT('2014-3-12','%m/%d/%Y')，SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒')


信息函数
connection_id():链接id
database():当前数据库
last_insert_id():最后插入的主键、自增长的id值
user():当前用户
version():mysql版本信息


聚合函数:只有一个返回值
avg()
count()
max()
min()
sum()


加密函数
md5():信息摘要算法，一般用这个
password()：密码算法,修改客户端密码时用


自定义函数，user-defined function  UDF,参数，返回值
不带参数：
CREATE FUNCTION chinese_time() RETURNS VARCHAR(30)
RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒')
带参数：
CREATE FUNCTION f2(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED)
RETURNS FLOAT(10,2) UNSIGNED
RETURN (num1+num2)/2


DELIMITER //
CREATE FUNCTION adduser(username VARCHAR(20))
RETURNS INT UNSIGNED
BEGIN
INSERT USER(username) VALUES(username);
RETURN LAST_INSERT_ID();
END


;是mysql默认分隔符,符合结构则使用begin...end语句


存储过程:是SQL语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理.
sql命令的执行过程：sql命令->mysql引擎 分析->语法正确->可识别命令  执行->执行结果  返回->客户端
优点：增强sql语句的功能和灵活性，实现较快的执行速度，减少网络流量
创建无参sp：CREATE PROCEDURE sp1() SELECT VERSION()
调用：CALL sp1
创建有参sp：DELIMITER //
CREATE PROCEDURE removeUserById(IN _id INT UNSIGNED)
BEGIN
DELETE FROM USER WHERE id=_id;
END
参数不能和字段名一样.,若一样数据库将认为都是字段名
调用：CALL removeUserById(3)
IN和OUT
DELIMITER //
CREATE PROCEDURE removeUserAndReturnUserNums(IN _id INT SIGNED,OUT userNums INT UNSIGNED)
BEGIN
DELETE FROM USER WHERE id=_id;
SELECT COUNT(id) FROM USER INTO userNums;
END


执行：CALL removeUserAndReturnUserNums(1,@nums),nums是用户变量,只对当前用户使用的客户端生效,用户变量
查看：SELECT @nums
UPDATE USER SET username=CONCAT(username,"--imooc") WHERE id<=2
//存储过程，用age删除记录，并返回删除的记录数，还剩多少记录
DELIMITER //
CREATE PROCEDURE removeUserByAgeAndReturnInfos(IN _age SMALLINT UNSIGNED,OUT delUsers SMALLINT UNSIGNED,OUT userCounts SMALLINT UNSIGNED)
BEGIN
DELETE FROM USER WHERE age=_age;
SELECT ROW_COUNT() INTO delUsers;ROW_COUNT()是上次更新的数目
SELECT COUNT(id) INTO userCounts;
END
执行；call removeUserByAgeAndReturnInfos(20,@delNums,@userNums)
     select @delNums
     select @userNums


存储引擎
并发控制：当多个链接对记录修改时保证数据的一致性和完整性
锁：读锁：在同一时间段内，多个用户可以读取同一个资源，读取过程中数据不会发生任何变化；写锁：在任何时候只能有一个用户写入资源，当进行写锁时会阻塞其他的读锁或者写锁操作
锁颗粒：表所，是一种开销最下的锁策略，对表锁；行锁，是一种开销最大的锁策略，对记录锁。
事务处理：保证数据库的完整性
事物的特征：原子性，一致性，隔离性，持久性
外键：是保证数据一致性的策略
索引：是对数据表中一列或多列的值进行排序的一种结构,普通索引，唯一索引，全文索引，btree索引，hash索引