MYSQL常用操作 知识梳理

MYSQL常用操作  知识梳理




==================================增删改查=======================================
创建数据库
CREATE DATABASE nxcms ;








创建数据表
CREATE TABLE nx_user(
id INT    UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(20)     NOT NULL DEFAULT '',
age         TINYINT UNSIGNED NOT NULL DEFAULT 0,
password    VARCHAR(32)     NOT NULL DEFAULT '' 
) ENGINE=MyISAM DEFAULT CHARSET=utf8;








查看创建表结构的语句
SHOW CREATE TABLE nx_user;








【插入一条数据】
INSERT INTO nx_user() VALUES("","zhou","26","123qwe");












【修改数据】
UPDATE nx_user set username = 'djkk';


指定条件
UPDATE nx_user set username = 'djkk' where id=1;




批量搜索替换
UPDATE nx_user SET password = REPLACE(password,"123456","123qwe");












【删除数据】
DELETE FROM nx_user WHERE id>2;








变更表结构
添加字段
ALTER TABLE nx_user ADD COLUMN ctime VARCHAR(11) NOT NULL DEFAULT '';




修改字段
ALTER TABLE nx_user MODIFY COLUMN ctime BIGINT(11) NOT NULL DEFAULT '';




删除字段
ALTER TABLE nx_user DROP COLUMN ctime;




更改表引擎
ALTER TABLE nx_user engine = Innodb;


=====================================================================================
表的结构复制
CREATE TABLE nx_user2  LIKE  nx_user;
表的数据复制
INSERT INTO  nx_user2 SELECT * from nx_user;
=====================================================================================




创建索引 
CREATE INDEX index_password ON nx_user(password);
唯一索引
CREATE UNIQUE INDEX un_username on nx_user(username);




查看索引
SHOW INDEX FROM nx_user;




删除索引 
ALTER TABLE nx_user DROP INDEX age_index;
ALTER TABLE nx_user DROP PRIMARY KEY;




添加索引
ALTER TABLE nx_user ADD IDNEX in_username(username);
ALTER TABLE nx_user ADD UNIQUE un_username(username);








=========================================================
创建视图




CREATE VIEW v_user AS SELCT * FROM nx_user where id>=3;




=========================================================




内置函数
字符串函数
CONCAT()     链接字符串
LCASE()       转换成小写
UCASE()       转换成大写
LENGTH()     string长度
LTRIM()       去掉左边的空格
RTRIM()       去掉右边的空格
REPEAT(string,n)   重复N次字符串
REPLACE(str,search,replace) 搜索search替换成replace
SUBSTRING(str,position,length)  从position开始，取length个字符串
SPACE(n) 生产N个空格
MD5(str)                        将str进行MD5加密








数学函数
BIN(number)   十进制转成二进制
CEILING(number) 向上取整  
FLOOR(number) 向下取整
MAX(num1,num2) 取最大值
MIN(num1,num2) 取最小值
SQRT(number) 开平方
RAND() 返回0~1以内的随机数字








日期函数
CURDATE()       返回当前日期   
CURTIME() 返回当前时间
NOW() 返回当前的日期时间
UNIX_TIMESTAMP(date) 返回当前date的UNIX时间戳
FROM_UNIXTIME() 返回UNIX时间戳的日期值
WEEK(date) 返回日期date为一年中的第几周
YEAR(date) 返回date的年份
DATEDIFF(expr1,expr2) 返回起始时间expr1,和结束时间expr2间的天数




DATEDIFF("1991-09-10","2017-05-15")   返回-9379    负数代表已经过去了9379天




=========================================================








预处理语句




创建一条预处理语句 
prepare test from 'select * from nx_user where id>?';




设置一个变量
SET @i = 2;




执行预处理语句
execute test using @i;




执行  select * from nx_user where id>2;
结果：返回id大于2的数据。








删除预处理语句
DROP prepare test;












=========================================================




事务处理  【注意！MyISAM表不支持事务机制】




关闭自动提交
set autocommit =0;




查询所有用户
select * from nx_user;




删除一条数据
delete from nx_user where id=2;








创建还原点
savepoint p1;












回滚 
rollback




回滚 指定还原点  (之后的还原点都会消失)
rollback to p1;




提交！
commit




=========================================================




创建存储
create procedure add_user()
begin
set @i =1 ;
while @i <=100 do
insert into nx_user(username,age,password) values(concat("user",@i) , @i , concat("123qwe_",@i));
set @i = @i+1;
end while;
end; 








执行存储
call add_user  
=========================================================
触发器  




如果nx_user 有数据插入 就触发
create trigger nx_user before insert on nx_user for each row
begin
insert into nx_user2(username,age,password) values(new.username,new.age,new.password);
end




如果nx_user 有数据被删除 就触发
create trigger del_nx_user before delete on nx_user for each row
begin
delete from nx_user2 where id = old.id;
end
=========================================================
创建一个用户
grant all privileges on liumanDB.* to liuman@localhost identified by '123456';