常用SQL

一、常用SQL

1、修改表主键自增为1

alter table mdm_access AUTO_INCREMENT=1;

2、创建数据库指定编码、排序

CREATE DATABASE netcast DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

CREATE DATABASE test DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

3、修改数据库密码

use mysql;
update user set authentication_string=PASSWORD("123456") where user='root' and Host ='localhost';
update user set plugin="mysql_native_password";
flush privileges;

4、备份数据库

mysqldump -u root -p zimudou > /var/www/zimudou.sql

5、备份单张表

mysqldump -u root -p yanfadi yfd_news > yfd_news.sql

6、新增字段

alter table car_gps add lowv tinyint(1) unsigned not Null;
alter table car_gps add lostv tinyint(1) unsigned not Null;

7、插入数据

insert into user (name, pwd) values ('test1', '123456');
insert into user (name, pwd) values ('test2', '123456'), ('test3', '123456');

8、修改数据

update user set name='test4' , pwd='123' where id = 1;

// 两张表之间复制数据
update user set
    name = CASE id
        when 1 then 'test1'
        when 2 then 'test5'
    end,
    pwd = CASE id
        when 1 then '1234'
        when 2 then '1234'
    end
where id in(1, 2);

9、删除数据

delete from user where id in (2, 3) limit 2;

10、创建一张表

CREATE TABLE IF NOT EXISTS `user`(
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL DEFAULT '',
    `pwd` VARCHAR(100) NOT NULL DEFAULT '',
PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

11、 数据表字段复制迁移

update user_detail f1 INNER JOIN user_authentication f2 on f2.user_id = f1.user_id and f2.`status` = 2
set f1.auth_recommend_user_id = f2.auth_recommend_user_id,
f1.auth_time = f2.auth_time;


UPDATE opp f1 INNER JOIN
(
    SELECT
    * FROM
    (
    select
    id,
    UPPER(left(opp_remark, 1)) as opp_remark,
    opp_level_last
    from opp where opp_remark regexp '[ABCDabcd]'
    )AS tmp
    WHERE tmp.opp_remark IN ('A', 'B', 'C', 'D')


) as f2
ON f1.id = f2.id


SET f1.opp_level_last = f2.opp_remark,
f1.opp_level = f2.opp_remark;

12、远程用户登录授权

//只授权给ip登录，密码为root
grant all privileges on *.* to 'root'@'ip' identified by 'root' with grant option;
flush privileges

//授权给所有用户密码是root
grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option;
flush privileges;

如果直接修改为ip不成功，可以先改为%，连接成功之后再修改为ip

13、默认情况下Mysql只允许本地登录，所以需要修改配置文件将地址绑定给注释掉：

vim /etc/mysql/my.cnf

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address           = 127.0.0.1  <---注释掉这一行就可以远程登录了


然后重启
sudo   /etc/init.d/mysql restart

14、查看安装路径

which mysql

whereis mysql

15、ubuntu删除mysql

sudo apt-get autoremove --purge mysql-server-5.0
sudo apt-get remove mysql-server
sudo apt-get autoremove mysql-server
sudo apt-get remove mysql-common //这个很重要
上面的其实有一些是多余的。

16、清理残留数据

dpkg -l |grep ^rc|awk '{print $2}' |sudo xargs dpkg -P

17、查看数据库表占用内存大小

show table status;

18、导入单张表

mysql -u user -p dbname < db.sql

19、unzip解压到指定目录

1、把文件解压到当前目录下
unzip test.zip

2、如果要把文件解压到指定的目录下，需要用到-d参数。
unzip -d /temp test.zip

3、解压的时候，有时候不想覆盖已经存在的文件，那么可以加上-n参数
unzip -n test.zipunzip -n -d /temp test.zip

4、只看一下zip压缩包中包含哪些文件，不进行解压缩
unzip -l test.zip

5、查看显示的文件列表还包含压缩比率
unzip -v test.zip

6、检查zip文件是否损坏
unzip -t test.zip

7、将压缩文件test.zip在指定目录tmp下解压缩，如果已有相同的文件存在，要求unzip命令覆盖原先的文件
unzip -o test.zip -d /tmp/

20、解压之后撤销操作

zipinfo -1 path/xx.zip | xargs rm -rf