MySql

发表于 | 分类于
Mysql

  • https://blog.csdn.net/nancy50/article/details/81080693

  • Windows系统启动和关闭Mysql服务器

    启动：mysqld –console

    关闭：mysqladmin -uroot shutdown

  • Linux系统下启动关闭mysql服务

    检查mysql服务是否启动：ps -ef | grep mysqld

    启动Mysql：sudo systemctl start mysqld.service

    停止：sudo systemctl restart mysqld.service

    查看 MySQL 运行状态：systemctl status mysqld.service

    1
    2
    3
    4
    5
    6
    7
    8
    9
    #服务管理启动：sudo service mysql start
    #服务启动：sudo systemctl mysql start
    #停止：sudo service mysql stop
    #服务状态：sudo service mysql status
    #重启Mysql：sudo service mysql restart
    #连接数据库：mysql -h 127.0.0.1 -P 3306 -uroot -p123456 #-h为远程IP，-P为端口号，-u为用户名，-p为密码
    #退出连接：quit
    #测试SQL：show databases;
    #登录MySql数据库：mysql -u root -p

  • mysql添加用户

    1
    2
    3
    4
    5
    6
    首先创建用户
    create user 'usrabc'@'%' identified by 'usrabc';
    再赋予权限
    grant all privileges on testdb to 'test'@'localhost';
    flush privileges；
    查看mysql的用户列表select host,user from mysql.user;

    那么这里有一个问题，再赋予权限的时候，会有‘test’@‘%’是什么意思？

    1
    2
    3
    4
    5
    6
    7
    8
    9
    授权所有数据库的所有表的所有权限给ip为任意值用户名为test密码为pwd的用户

    GRANT ALL ON *.* TO 'test'@'%' IDENTIFIED BY 'pwd';
    授权mydb数据库的所有表的增删改查权限给ip为1.1.1.1用户名为test密码为pwd的用户

    GRANT SELECT,INSERT,UPDATE,DELETE ON mydb.* TO 'test'@'1.1.1.1' IDENTIFIED BY 'pwd';
    授权mydb数据库的stu表的修改权限给ip为1.1.1.1用户名为test密码为pwd的用户

    GRANT UPDATE(name,age) ON mydb.stu TO 'test'@'1.1.1.1' IDENTIFIED BY 'pwd';

  • mysql默认有一个root用户，但root用户的权限很大，一般只有在管理数据库的时候才会用到。如果在项目中要连接mysql数据库，则建议新建一个权限较小的用户连接。那么流程怎样的呢？

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    用户创建：
    CREATE USER username IDENTIFIED BY 'password';
    给用户分配权限：
    GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';

    GRANT ALL PRIVILEGES ON wordpress.* TO 'username'@'localhost' IDENTIFIED BY 'password';
    当我们以username登陆mysql的时候，只有wordpress数据库才是可见的

    用户删除
    　　@>mysql -u root -p
    　　@>密码
     　　mysql>Delete FROM user Where User='test' and Host='localhost';
     　　mysql>flush privileges;
     　　mysql>drop database testDB; //删除用户的数据库
    删除账户及权限：>drop user 用户名@'%';
    　　　　　　　　>drop user 用户名@ localhost;

    Django中的host为数据库服务器地址

  • mysql创建数据库

    CREATE DATABASE IF NOT EXISTS my_db default character set utf8 COLLATE utf8_general_ci;

    python的Django里面命令行操作mysql数据库：python manage.py dbshell

    Django使用命令makemigrations无法执行表修改动作解决方式：

    1
    2
    3
    python3 manage.py makemigrations --empty 你的应用名
    python3 manage.py makemigrations
    python3 manage.py migrate

  • Django管理工具的激活

    通常生成项目的时候会在urls.py 中自动设置好，我们只需去掉注释即可

    1
    2
    3
    4
    相关代码
    urlpatterns = [
        url(r'^admin/', admin.site.urls),
    ]

  • 当你用Django创建superuser的时候，如果报了错误：

    将数据模型migrate到数据库后再执行超级用户创建

  • Django表结构的修改

    1
    2
    3
    4
    表结构修改后，原来表中已存在的数据，就会出现结构混乱，makemigrations更新表的时候就会出错
    解决方法：
    1、新增加的字段，设置允许为空。生成表的时候，之前数据新增加的字段就会为空。(null=True允许数据库中为空，blank=True允许admin后台中为空)
    2、新增加的字段，设置一个默认值。生成表的时候，之前的数据新增加字段就会应用这个默认值

  • Mysql数据类型的属性

    NULL：数据列可包含NULL值

    NOT NULL：数据列不允许包含null值

    Default：默认值

    Primary Key：主键

    AUTO_INCREASED：自动递增，适用于整数类型，一般用于主键

    UNSIGNED：无符号

    Chracter Set Name：制定一个字符集

  • Mysql数据表的创建

    1
    2
    3
    4
    5
    6
    7
    8
    CREATE TABLE IF NOT EXISTS `runoob_tbl`(
       `runoob_id` INT UNSIGNED AUTO_INCREMENT,
       `runoob_title` VARCHAR(100) NOT NULL,
       `runoob_author` VARCHAR(40) NOT NULL,
       `submission_date` DATE,
       PRIMARY KEY ( `runoob_id` )
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;
    创建 MySql 的表时，表名和字段名外面的符号 ` 不是单引号，而是英文输入法状态下的反单引号，也就是键盘左上角 esc 按键下面的那一个 ~ 按键，坑惨了。

  • 数据库查询

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    SELECT column_name,column_name
    FROM table_name
    [WHERE Clause]
    [LIMIT N][ OFFSET M]

    SELECT field1, field2,...fieldN FROM table_name1, table_name2...
    [WHERE condition1 [AND [OR]] condition2.....

    UPDATE runoob_tbl SET runoob_title='学习 C++' WHERE runoob_id=3;

    当我们需要将字段中的特定字符串批量修改为其他字符串时，可已使用以下操作：
    UPDATE table_name SET field=REPLACE(field, 'old-string', 'new-string') 
    [WHERE Clause]

    DELETE FROM table_name [WHERE Clause]
    如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。
    delete 是 DML 语句，操作完以后如果没有不想提交事务还可以回滚，truncate 和 drop 是 DDL 语句，操作完马上生效，不能回滚，打个比方，delete 是发微信说分手，后悔还可以撤回，truncate 和 drop 是直接扇耳光说滚，不能反悔。

    UNION用于组合多个select语句的结果
    UNION 不能用于列出两个表中所有的country。如果一些网站和APP来自同一个国家，每个国家只会列出一次。UNION 只会选取不同的值。请使用 UNION ALL 来选取重复的值！

    Mysql排序
    SELECT field1, field2,...fieldN table_name1, table_name2...
    ORDER BY field1, [field2...] [ASC [DESC]]

    Mysql分组
    SELECT column_name, function(column_name)
    FROM table_name
    WHERE column_name operator value
    GROUP BY column_name;
    SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;
    group by 可以实现一个最简单的去重查询，假设想看下有哪些员工，除了用 distinct,还可以用：
    SELECT name FROM employee_tbl GROUP BY name;
    分组后的条件使用 HAVING 来限定，WHERE 是对原始数据进行条件限制。几个关键字的使用顺序为 where 、group by 、having、order by ，例如：
    SELECT name ,sum(*)  FROM employee_tbl WHERE id<>1 GROUP BY name  HAVING sum(*)>5 ORDER BY sum(*) DESC;

    表连接查询
    SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

    在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。
    transaction再执行的时候可以执行回滚rollback，当所有sql语句都正确执行后，才会提交

    alter的使用
    当我们需要修改数据表名或者修改数据表字段时，就需要使用到MySQL ALTER命令。

    索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。
    创建索引时，你需要确保该索引是应用在	SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
    CREATE INDEX indexName ON mytable(username(length)); 
    修改表结构(添加索引)：ALTER table tableName ADD INDEX indexName(columnName)
    我们可以在创建表的时候直接指定索引
    CREATE TABLE mytable(  
    ID INT NOT NULL,   
    username VARCHAR(16) NOT NULL,  
    INDEX [indexName] (username(length))  
    );  

    MySQL 序列是一组整数：1, 2, 3, ...，由于一张数据表只能有一个字段自增主键， 如果你想实现其他字段也实现自动增加，就可以使用MySQL序列来实现。MySQL 中最简单使用序列的方法就是使用 MySQL AUTO_INCREMENT 来定义列。

  • Mysql处理重复数据

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    可以在MySQL数据表中设置指定的字段为 PRIMARY KEY（主键） 或者 UNIQUE（唯一） 索引来保证数据的唯一性。
    CREATE TABLE person_tbl
    (
       first_name CHAR(20) NOT NULL,
       last_name CHAR(20) NOT NULL,
       sex CHAR(10),
       UNIQUE (last_name, first_name)
    );
    使用distinct过滤重复数据
    SELECT DISTINCT last_name, first_name FROM person_tbl;
    使用分组group过滤重复数据
    SELECT last_name, first_name FROM person_tbl GROUP BY (last_name, first_name);
    删除重复的数据
    CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl  GROUP BY (last_name, first_name, sex);
    DROP TABLE person_tbl;
    ALTER TABLE tmp RENAME TO person_tbl;
    当然你也可以在数据表中添加 INDEX（索引） 和 PRIMAY KEY（主键）这种简单的方法来删除表中的重复记录。方法如下：
    ALTER IGNORE TABLE person_tbl ADD PRIMARY KEY (last_name, first_name);

  • 导出mysql

    • mysql的dump用于转存储数据库的实用程序。它主要产生一个 SQL 脚本，其中包含从头重新创建数据库所必需的命令 CREATE TABLE INSERT 等。

      1
      2
      $ mysqldump -u root -p --no-create-info \
                  --tab=/tmp RUNOOB runoob_tbl

    • 导出sql格式的数据

      1
      mysqldump -u root -p RUNOOB runoob_tbl > dump.txt

      如果你需要导出整个数据库的数据，可以使用以下命令

      1
      mysqldump -u root -p RUNOOB > database_dump.txt

    • 将数据表及数据库拷贝到其他主机

      即将数据拷贝至其他的mysql服务器上，可以再mysqldump命令中数据库名字以及数据表

      在源主机上执行一下命令，备份至dump.txt中

      1
      mysqldump -u root -p database_name table_name > dump.txt

      将备份的数据库导入到mysql服务器中，注意使用该命令前需要确认数据库已经创建

      1
      mysql -u root -p database_name < dump.txt

      将导出的数据直接导入到远程的服务器上，但请确保两台服务器是相通的，是可以相互访问的：

      1
      2
      mysqldump -u root -p database_name \
             | mysql -h other-host.com database_name

      将远程主机的数据库导到本地

      1
      mysqldump -h other-host.com -P port -u root -p database_name > dump.txt

  • mysql导入数据

    • 使用mysql导入：

      1
      mysql -u用户名    -p密码    <  要导入的数据库数据(runoob.sql)

    • source命令导入：source 命令导入数据库需要先登录到数库终端：

      1
      2
      3
      4
      mysql> create database abc;      # 创建数据库
      mysql> use abc;                  # 使用已创建的数据库 
      mysql> set names utf8;           # 设置编码
      mysql> source /home/abc/abc.sql  # 导入备份数据库

    • 使用mysqlimport导入数据

      从文件 dump.txt 中将数据导入到 mytbl 数据表中, 可以使用以下命令

      1
      mysqlimport -u root -p --local database_name dump.txt

      mysqlimport命令可以指定选项来设置指定格式,命令语句格式如下：

      1
      2
      $ mysqlimport -u root -p --local --fields-terminated-by=":" \
         --lines-terminated-by="\r\n"  database_name dump.txt

      ​

      ​