MySQL常用语句记录

0、连接/远程连接

mysql -h 192.168.1.40 -u username -p

1、查询某个表的所有字段

    --方法一
    SHOW COLUMNS FROM personal;
    --包含备注等相关信息
    SHOW FULL COLUMNS FROM personal;
    --方法二
    SELECT CONCAT('"',REPLACE(GROUP_CONCAT(column_name),',','", "'),'"') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'populac' AND TABLE_NAME = 'personal';

2、reset table AUTO_INCREMENT

    ALTER TABLE `tablename` AUTO_INCREMENT = 1

3、日期格式转换YYYY-MM-DD to YYYYMMDD

select * from `jedt` where `way` > '' and exists (select * from `persons` where `jedt`.`personal_id` = `persons`.`personal_id`) and not exists (select 1 from `jsways` where `jsways`.`personal_id` = `jedt`.`personal_id` and date_format(str_to_date(`jsways`.`waydate`, '%Y-%m-%d'),'%Y%m%d') = `jedt`.`waydate`) and (`way` > '') order by `personal_id` asc limit 1

4、错误 Cannot truncate a table referenced in a foreign key constraint

SET FOREIGN_KEY_CHECKS = 0; 
TRUNCATE table $table_name; 
SET FOREIGN_KEY_CHECKS = 1;

5、表中添加、删除、查看索引

-- 添加
ALTER TABLE persons ADD INDEX persons_name1_unitcode_logout_deleted_at_index(`name1`,`unitcode`,`logout`,`deleted_at`);
ALTER TABLE persons ADD INDEX persons_unitcode_logout_deleted_at_index(`unitcode`,`logout`,`deleted_at`);
--查看
show index from persons;
--删除
drop index persons_unitcode_logout_deleted_at_index on persons;

6、复制表结构，含索引

--复制表结构和索引
CREATE TABLE t_persons LIKE persons;
--复制数据
insert into t_persons select * from persons;

7、删除表的多个字段

alter table persons drop checkunit,drop yzdate, drop incity, drop feeddate;

8、连表更新

UPDATE children
INNER JOIN child ON children.childorder=child.childorder and children.personal_id=child.personal_id
SET children.onlysign=child.onlysign, children.cardcode=child.cardcode, children.cardorgan=child.cardorgan
WHERE child.onlysign='01';

9、查找重复记录、删除重复记录只留一条

select * from hr_emp_1130 where fcode in (
select fcode from hr_emp_1130 GROUP BY fcode HAVING count(fcode)>1
) and id not in (
select MAX(id) as id from hr_emp_1130 GROUP BY fcode HAVING count(fcode)>1
)

delete from hr_emp_1130 where fcode in (
select a.fcode from (
select fcode from hr_emp_1130 GROUP BY fcode HAVING count(fcode)>1
)  a
) and id not in ( select b.id from (
select MAX(id) as id from hr_emp_1130 GROUP BY fcode HAVING count(fcode)>1) b
)

10、身份证15位转18位

UPDATE persons SET fcode = CONCAT
    (
    SUBSTRING(fcode,1,6),'19',SUBSTRING(fcode,7,9),SUBSTRING('10X98765432',
        (CAST(SUBSTRING(fcode,1,1)AS SIGNED)*7+
        CAST(SUBSTRING(fcode,2,1)AS SIGNED)*9+
        CAST(SUBSTRING(fcode,3,1)AS SIGNED)*10+
        CAST(SUBSTRING(fcode,4,1)AS SIGNED)*5+
        CAST(SUBSTRING(fcode,5,1)AS SIGNED)*8+
        CAST(SUBSTRING(fcode,6,1)AS SIGNED)*4+
        1*2+
        9*1+
        CAST(SUBSTRING(fcode,7,1)AS SIGNED)*6+
        CAST(SUBSTRING(fcode,8,1)AS SIGNED)*3+
        CAST(SUBSTRING(fcode,9,1)AS SIGNED)*7+
        CAST(SUBSTRING(fcode,10,1)AS SIGNED)*9+
        CAST(SUBSTRING(fcode,11,1)AS SIGNED)*10+
        CAST(SUBSTRING(fcode,12,1)AS SIGNED)*5+
        CAST(SUBSTRING(fcode,13,1)AS SIGNED)*8+
        CAST(SUBSTRING(fcode,14,1)AS SIGNED)*4+
        CAST(SUBSTRING(fcode,15,1)AS SIGNED)*2)%11+1,1))
        WHERE LENGTH(fcode)=15;

11、update Incorrect datetime value: '0000-00-00 00:00:00' for column 'created_at'

SET SQL_MODE='ALLOW_INVALID_DATES';

update posters set created_at=updated_at where created_at='0000-00-00 00:00:00';

12、表的某个字段后面新增字段

ALTER TABLE hotels
ADD COLUMN `ali_hotel_id` VARCHAR(30) AFTER `cn_hotel_id`;

ALTER TABLE hotel_rooms
ADD COLUMN `ali_room_id` VARCHAR(30) AFTER `cn_room_id`;

ALTER TABLE hotel_room_statuses
ADD COLUMN `ali_rate_id` VARCHAR(30) AFTER `cn_rate_id`;

ALTER TABLE hotel_room_rate_plans
ADD COLUMN `ali_rate_plan_id` VARCHAR(30) AFTER `cn_rate_plan_id`;

13、查看当前连接数

show full processlist;

14、修改root登录密码

--找到debian-sys-maint用户和密码：vi /etc/mysql/debian.cnf
mysql -u debian-sys-maint -p hTRH5bAZXkyxvF4Y
use mysql;
update user set authentication_string=PASSWORD("你要改的密码") where User='root';
update user set plugin="mysql_native_password";
flush privileges;
exit;

service msyql restart

15、修改默认值

ALTER TABLE hotel_room_rate_plans ALTER cn_rate_id SET DEFAULT 0;
ALTER TABLE hotel_room_rate_plans ALTER cn_rate_plan_id SET DEFAULT 0;
ALTER TABLE hotel_room_rate_plans ALTER cn_room_id SET DEFAULT 0;

16、 新增外键

注意，外键的父列和子列数据类型要一致，int与uint是不一样的类型

alter table hotel_room_statuses add FOREIGN KEY (hotel_room_rate_plan_id) REFERENCES `hotel_room_rate_plans`(`id`) ON DELETE cascade ON UPDATE cascade;