个人使用mysql_mysql使用 - hexiaoming123的个人空间 - OSCHINA - 中文开源技术交流社区...

select * from user where name like '%%%' ;

select * from user where name like '%1%%' escape 1;

select *,RIGHT(name,LENGTH(name) - POSITION('@' in name)) from user;

#对name列中b不为 null的计数 select COUNT(name = 'b' or null) as b , COUNT(name = 'c' or null) as c ,(select COUNT(id) from user where name is null) as nan from user ;

mysql存储过程

-- 存储过程创建

DELIMITER //

CREATE PROCEDURE SimpleCompare(n INT, m INT,k INT,h INT)

begin

START TRANSACTION; -- 开始事务

insert ignore into t values(n,'第一条',m);

if ()>0 then

set k =0;

COMMIT;

insert into t values(n+1,'第二条',m+1);

else

set k =-1;

ROLLBACK;

end if;

select * from t ;

end

//

DELIMITER ;

-- 存储过程调用

call SimpleCompare(133,66,@a,@b);

mysql中使用if case when

select if('322'='322',34,65)

select CASE address WHEN 1 THEN '男' ELSE '女' END as ssva from t where address != ''

DELIMITER //

CREATE PROCEDURE proc_getuserbyid(IN uId int)

BEGIN

SELECT * FROM user WHERE id=uId;

END

//

DELIMITER ;

call proc_getuserbyid(21)

mysql外键级联

MySQL中利用外键实现级联删除、更新

MySQL支持外键的存储引擎只有InnoDB，在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引。在创建索引的时候，可以指定在删除、更新父表时，对子表进行的相应操作，包括RESTRICT、NO ACTION、SET NULL和CASCADE。其中RESTRICT和NO ACTION相同，是指在子表有关联记录的情况下父表不能更新；CASCADE表示父表在更新或者删除时，更新或者删除子表对应记录；SET NULL则是表示父表在更新或者删除的时候，子表的对应字段被SET NULL。因为只有InnoDB引擎才允许使用外键，所以，我们的数据表必须使用InnoDB引擎。

我所使用的版本是Mysql5.7版本的，过程如下：

CREATE TABLE `user` (

`id` int(4) NOT NULL,

`sex` enum('f','m') DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `userinfo` (

`sn` int(4) NOT NULL AUTO_INCREMENT,

`userid` int(4) NOT NULL,

`info` varchar(20) DEFAULT NULL,

PRIMARY KEY (`sn`),

KEY `userid` (`userid`),

CONSTRAINT `userinfo_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `user` (`id`)

ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#添加数据

INSERT INTO `test`.`user`(`id`, `name`, `age`) VALUES (22, 'c', 0);

INSERT INTO `test`.`userinfo`(`sn`, `userid`, `info`) VALUES (1, 22, '2');

#验证数据

DELETE FROM `test`.`userinfo` WHERE `sn` = 1 #这行不会删除user表数据；

DELETE FROM `test`.`user` WHERE `id` = 22#这行不会删除userinfo表对应userid为22的数据

#userinfo删除约束

ALTER TABLE `test`.`userinfo` DROP FOREIGN KEY `userinfo_ibfk_1`

触发器

删除后会放到旧的表中。OLD.id

新插入的id会放到NEW表中

用户账号管理

创建用户 CREATE USER [IF NOT EXISTS]

user [auth_option] [, user [auth_option]] ...

[REQUIRE {NONE | tls_option [[AND] tls_option] ...}]

[WITH resource_option [resource_option] ...]

[password_option | lock_option] ...

示例： CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';

首次创建的帐户没有特权。要分配特权，请使用以下GRANT 语句。 GRANT

priv_type [(column_list)]

[, priv_type [(column_list)]] ...

ON [object_type] priv_level

TO user [auth_option] [, user [auth_option]] ...

[REQUIRE {NONE | tls_option [[AND] tls_option] ...}]

[WITH {GRANT OPTION | resource_option} ...]

帐户的主机名部分(如果省略)默认为 '%'。

通常，数据库管理员首先用于 CREATE USER创建帐户并定义其非特权特征，例如其密码，是否使用安全连接以及对服务器资源的访问限制，然后再用于 GRANT定义其特权。 ALTER USER可用于更改现有帐户的非特权特征。例如： CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';

GRANT ALL ON db1.* TO 'jeffrey'@'localhost';

GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';

ALTER USER 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;

删除用户 DROP USER [IF EXISTS] user [, user] ...

该DROP USER语句删除一个或多个MySQL帐户及其特权。它从所有授权表中删除该帐户的特权行。 DROP USER 'jeffrey'@'localhost';

帐户名的主机名部分(如果省略)默认为 '%'。

授权

以下语句全局授予 SELECT和 INSERT特权： GRANT SELECT, INSERT ON *.* TO u1;

授予 全局访问特权 GRANT ALL ON *.* TO u1;

重命名用户

示例： RENAME USER 'jeffrey'@'localhost' TO 'jeff'@'127.0.0.1';

撤销特权

要撤消所有特权，该语法将删除一个或多个指定用户的所有全局，数据库，表，列和例程特权： REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

示例： #撤销localhost机器上jeffrey的INSERT所有权限

REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';

SET PASSWORD语句

示例：创建明文密码 ALTER USER user IDENTIFIED BY 'auth_string';

该语句 使用子句设置命名帐户的密码，该密码必须存在： FOR user SET PASSWORD FOR 'jeffrey'@'localhost' = 'auth_string';

如果没有no 子句，该语句将为当前用户设置密码： FOR user SET PASSWORD = 'auth_string';

使用该PASSWORD()函数(自MySQL 5.7.6起不推荐使用) SET PASSWORD FOR 'jeffrey'@'localhost' = PASSWORD('password');

该PASSWORD()参数是明文(未加密的)密码。 PASSWORD()散列密码并返回加密的密码字符串，以存储在mysql.user系统表的帐户行中 。

该PASSWORD()函数使用由old_passwords系统变量值的值确定的哈希方法对密码进行哈希处理 。确保 old_passwords该值具有与该帐户关联的身份验证插件所需的哈希方法相对应的值。例如，如果帐户使用 mysql_native_password插件，则该 old_passwords值必须为0： SET old_passwords = 0;

SET PASSWORD FOR 'jeffrey'@'localhost' = PASSWORD('password');

活锁和死锁解决方法

活锁

活锁出现的场景 很多个执行事务进来，T1到了执行。T2进来优先级级别低，同时T3....很多事务进来都比T2优先级别高，导致T2一直等待，造成活锁。

解决方案： 加个队列执行。

死锁

一次性锁请求(把相关的事务都加到一次请求锁中，几乎不可能实现)

锁请求排序

序列化处理

资源剥夺(给锁加超时时间，让资源回滚，然后重写请求)

不去防止 (定时检测死锁，去解除死锁)

可串行 一组事务是可串行的。(把并行的转换成串行)

两段封锁法

备份还原

备份

修改/etc/my.conf

[mysqld]标签下添加 #所以目录都可以导入导出

secure_file_priv=''

执行导出

t1表导出到/tmp/user.csv

格式： into outfile ‘导出的目录和文件名’

指定导出的目录和文件名

fields terminated by ‘字段间分隔符’

定义字段间的分隔符

optionally enclosed by ‘字段包围符’

定义包围字段的字符(数值型字段无效)

lines terminated by ‘行间分隔符’

定义每行的分隔符

select a,b,a+b from t1 into outfile '/tmp/user.txt' fields terminated by ',' optionally enclosed by '"' lines terminated by '\r\n';

还原

这里(a,b,c)指定了列，如果不指定列就是导入数据到所有列，这里导入数据会插入到之前的数据后边。 LOAD DATA INFILE '/tmp/user.csv' INTO TABLE t1 fields terminated by ',' optionally enclosed by '"' lines terminated by '\r\n' (a,b,c);