美文网首页go知识
windows下mysql操作相关

windows下mysql操作相关

作者: 奔跑的图腾 | 来源:发表于2018-04-03 11:08 被阅读0次

    启动mysql数据库

    net start MySQL
    

    连接MySQL服务器

    MySQL -uroot  -h127.0.0.1 -p123456
    注:用户名为“root”,MySQL数据库服务器地址为“127.0.0.1”,密码为“123456”,三者之间必须有空格。
    

    创建用户

    insert into mysql.user(Host,User,Password) values("localhost","testuser",password("123456"));
    //创建了一个名为:testuser 密码为:123456 的用户。
    注意:此处的"localhost",是指该用户只能在本地登录,不能在另外一台机器上远程登录。
    如果想远程登录的话,将"localhost"改为"%",表示在任何一台电脑上都可以登录。也可以指定某台机器可以远程登录
    

    用户授权

    授权格式:grant 权限 on 数据库.* to 用户名@登录主机 identified by "密码";
    例如:
    登录MYSQL(有ROOT权限),这里以ROOT身份登录
    //为用户创建一个数据库(testDB):
    create database testDB;  
    //授权test用户拥有testDB数据库的所有权限:
    grant all privileges on testDB.* to testuser@localhost identified by '123456';
    flush privileges;//刷新系统权限表
    
    指定部分权限给一用户
    grant select,update on testDB.* to testuser@localhost identified by '123456';
    flush privileges; //刷新系统权限表
    //授权test用户拥有所有数据库的某些权限
    grant select,delete,update,create,drop on *.* to testuser@"%" identified by "123456";
    注意:@"%" 表示对所有非本地主机授权,不包括localhost
    对localhost授权:
    grant all privileges on testDB.* to testuser@localhost identified by '123456';
    

    修改mysql用户名和密码

    select user from mysql.user;//查看数据库有哪些用户名
    use mysql;
    update user set user ='newuser' where user ='root';//修改用户名
    //update user set user =’新用户名’ where user =’旧用户名’;
    update user set password=password('newpass') where user ='root';//修改密码
    flush privileges;
    

    关闭MySQL服务器

    net stop MySQL
    

    查看mysql编码:

    //查看character_set_database的编码
    show variables like 'character_set_database';
    查看数据表的编码格式
    show create table <表名>;
    
    show variables like "%character%";
    
    显示结果类似如下
    | character_set_client | utf8 |
    | character_set_connection | utf8 |
    | character_set_database | utf8 |
    | character_set_results | utf8 |
    | character_set_server | utf8 |
    | character_set_system | utf8 |
    | character_sets_dir | /usr/share/mysql/charsets/ |
    如果仍有编码不是utf8的,可使用mysql命令设置
    set character_set_client = utf8;
    set character_set_server = utf8;
    set character_set_connection = utf8;
    set character_set_database = utf8;
    set character_set_results = utf8;
    set collation_connection = utf8_general_ci;
    set collation_database = utf8_general_ci;
    set collation_server = utf8_general_ci;
    

    创建数据库:

    create database 数据库名;
    

    创建数据库并指定编码:

    create database 数据库名 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
    
    create database <数据库名> character set utf8;
    
    //修改数据库编码
    alter database 数据库名 default character set utf8 collate utf_8bin;
    
    alter database <数据库名> character set utf8;
    
    //修改表编码
    alter table 表名 default character set utf8 collate utf_8bin;
    
    alter table <表名> character set utf8;
    
    修改字段编码格式
    
    mysql>alter table <表名> change <字段名> <字段名> <类型> character set utf8;
    
    mysql>alter table user change username username varchar(20) character set utf8 not null;
    

    添加外键

    mysql>alter table <表名> add constraint <外键名> foreign key<字段名> REFERENCES <外表表名><字段名>;
    mysql>alter table tb_product add constraint fk_1 foreign key(factoryid) references tb_factory(factoryid);
    

    删除外键

    mysql>alter table <表名> drop foreign key <外键名>;
    mysql>alter table tb_people drop foreign key fk_1;
    

    列出所有数据库:

    show databases;
    

    切换数据库:

    use 数据库名;
    

    列出所有表:

    show tables;
    

    显示数据表结构:

    describe table_name;
    

    删除数据库:

    drop database 数据库名;
    

    修改数据表名或者修改数据表字段:

    修改字段类型
    把字段c的类型从CHAR(1)改为CHAR(10)
    alter table table_name modify c char(10);
    修改字段及类型
    把字段i修改为j 类型为bigint
    alter table table_name change i j bigint;
    alter table table_name change j j int;
    

    删除表:

    drop table table_name;
    

    删除表中的某条记录:

    delete from table_name where opid = 'o_XOw1bc51n3j1CyXmUIWguAnsO4';
    

    查询表中某条记录:

    select * from table_name
    

    排序查询:

    //降序查询
    select * from bracelet_ability order by bonus_ability desc;
    //升序查询
    select * from bracelet_ability order by bonus_ability asc;
    

    查询null的数据:

    select * from bracelet_ability where mac_ip is null;
    

    查询不为null的数据:

    select * from bracelet_ability where mac_ip is not null;
    

    查询不为空的数据:

    select mac_ip,gcs from bracelet where mac_ip !='' order by gcs desc;
    

    模糊查询:

    // %通配符代表任意多个字符
    select * from bracelet_ability where mac_ip like '%wq';
    // _通配符代表任意一个字符
    select * from bracelet_ability where Name like '_wq%';
    

    多条件关系查询:

    //并关系查询
    select * from bracelet where mac_ip !='' and gcs !=0;
    //或关系查询
    select * from bracelet where mac_ip !='' or mic !=0;
    //范围查询
    select * from bracelet_ability where bonus_ability >=5 and bonus_ability <=10;//取算力范围在(>=5 and <=10)之间的数据
    select * from bracelet_ability where bonus_ability between 5 and 10;//取算力范围在(>=5 and <=10)之间的数据
    

    统计函数:

    //查询表中有多少条数据
    select count(mac_ip) from bracelet_ability;
    //取算力的最大值
    select max(bonus_ability) from bracelet_ability;
    //取算力的最小值
    select min(bonus_ability) from bracelet_ability;
    //取算力的总和
    select sum(bonus_ability) from bracelet_ability;
    //取算力的平均值
    select avg(bonus_ability) from bracelet_ability;
    

    插入某条记录在表中:

    insert into table_name (field1,field2,field3) values (value1,value2,value3);
    

    更新表中某条数据:

    update table_name set field1=value1,field2=value2 where clause;
    

    启动ssdb数据库

    在D:\Program Files\SSDB\ssdb-bin-master目录下,右击选择Git Bash
    命令行输入
    ./ssdb-server-1.9.4.exe -d ./ssdb.conf -s restart//重启
    # 启动主库, 此命令会阻塞住命令行**
    ./ssdb-server ssdb.conf
    # 或者启动为后台进程(不阻塞命令行)**
    ./ssdb-server -d ssdb.conf
    # 停止 ssdb-server**
    ./ssdb-server ssdb.conf -s stop
    # 对于旧版本
    kill `cat ./var/ssdb.pid`
    # 重启**
    ./ssdb-server ssdb.conf -s restart
    

    导入数据库报错:

    [Err] 1153 - Got a packet bigger than 'max_allowed_packet' bytes [Err] INSERT INTO `apple_pay_verify_log` VALUES (2465,46,'20180420144844-46','
    mysql的默认配置,默认最大只能处理16M的文件
    
    解决方法:进入mysql,执行以下方法
    show variables like 'max_allowed_packet%';    
    set global max_allowed_packet=210241024*10;
    

    相关文章

      网友评论

        本文标题:windows下mysql操作相关

        本文链接:https://www.haomeiwen.com/subject/fpjqhftx.html