Python Day10 MySQL 01

原创

jedi911 2018-02-22 17:17:22 博主文章分类：Python ©著作权

文章标签 mysql Day10 文章分类 MySQL 数据库

©著作权归作者所有：来自51CTO博客作者jedi911的原创作品，请联系作者获取转载授权，否则将追究法律责任

MySQL数据库基本管理

Window版本安装

1、下载：MySQL Community Server 5.7.16 http://dev.mysql.com/downloads/mysql/

2、解压如果想要让MySQL安装在指定目录，那么就将解压后的文件夹移动到指定目录，如：C:\mysql-5.7.16-winx64

3、添加环境变量【右键计算机】--》【属性】--》【高级系统设置】--》【高级】--》【环境变量】--》【在第二个内容框中找到变量名为Path 的一行，双击】 --> 【将MySQL的bin目录路径追加到变值值中，用；分割】

4、初始化 mysqld --initialize-insecure

5、注册成系统服务注意：--install前，必须用mysql启动命令的绝对路径制作MySQL的Windows服务，在终端执行此命令： "c:\mysql-5.7.16-winx64\bin\mysqld" --install

移除MySQL的Windows服务，在终端执行此命令： "c:\mysql-5.7.16-winx64\bin\mysqld" --remove

#5.6 可以直接 mysqld --install MySQL56 注册后直接在windows 服务中查看即可

注册成服务之后，以后再启动和关闭MySQL服务时，仅需执行如下命令：启动MySQL服务 net start mysql

关闭MySQL服务 net stop mysql

6、启动MySQL客户端并连接MySQL服务 mysql -u root -p # 连接MySQL服务器

重置密码

net stop MySQL mysqld --skip-grant-tables #跳过授权表 mysql -uroot -p update mysql.user set password=password("") where user='root' and host="localhost"; flush privileges;

统一字符编码

配置文件

在mysql的解压目录下，新建my.ini,然后配置

在执行mysqld命令时，下列配置会生效，即mysql服务启动时生效 [mysqld] ;skip-grant-tables port=3306 character_set_server=utf8 default-storage-engine=innodb innodb_file_per_table=1

#解压的目录 basedir=E:\mysql-5.7.19-winx64 #data目录 datadir=E:\my_data #在mysqld --initialize时，就会将初始数据存入此处指定的目录，在初始化之后，启动mysql时，就会去这个目录里找数据

针对客户端命令的全局配置，当mysql客户端命令执行时，下列配置生效 [client] port=3306 default-character-set=utf8 user=root password=123
只针对mysql这个客户端的配置，2中的是全局配置，而此处的则是只针对mysql这个命令的局部配置 [mysql] ;port=3306 ;default-character-set=utf8 user=egon password=4573

！！！如果没有[mysql],则用户在执行mysql命令时的配置以[client]为准

统一字符编码

#1. 修改配置文件

[mysqld]
default-character-set=utf8
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8

#mysql5.5以上：修改方式有所改动

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8

#2. 重启服务 #3. 查看修改结果：

\s
show variables like '%char%'

基本SQL语句

操作文件夹（库）

    增
        create database db1 charset utf8;
    查
        show databases;
        show create database db1;
    改
        alter database db1 charset gbk;
    删
        drop database db1;

操作文件（表）

    查看当前所在的文件夹:select database();
    切换文件夹：use db1;

    增
        create table t1(id int,name char);
    查
        show tables;
        show create table t1;
        desc t1;
    改
        alter table t1 add sex char;
        alter table t1 drop sex;
        alter table t1 modify name char(16);
        alter table t1 change name Name char(13);
    删
        drop table t1;

操作文件的内容（记录）

    增
        insert into db1.t1 values
        (1,'egon'),
        (2,'alex'),
        (3,'wxx');
    查
        select id,name from db1.t1;
        select * from db1.t1;
    改
        update t1 set name='SB' where id=2;
    删
        delete from t1 where id=2;

库的操作

创建数据库

语法(help create database)

CREATE DATABASE 数据库名 charset utf8;

数据库命名规则：

可以由字母、数字、下划线、＠、＃、＄区分大小写唯一性不能使用关键字如 create select 不能单独使用数字最长128位

数据库相关操作

查看数据库
show databases;
show create database db1;
select database();
选择数据库
USE 数据库名
删除数据库
DROP DATABASE 数据库名;
修改数据库
alter database db1 charset utf8;

库操作

创建数据库

语法(help create database)

CREATE DATABASE 数据库名 charset utf8;

数据库命名规则：

可以由字母、数字、下划线、＠、＃、＄区分大小写唯一性不能使用关键字如 create select 不能单独使用数字最长128位

数据库相关操作

查看数据库 show databases; show create database db1; select database(); 选择数据库 USE 数据库名删除数据库 DROP DATABASE 数据库名; 修改数据库 alter database db1 charset utf8;

表的增删改查

创建表

create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);

注意：

在同一张表中，字段名是不能相同
宽度和约束条件可选
字段名和类型是必须的

往表中插入数据

MariaDB [db1]> insert into t1 values
    -> (1,'egon',18,'male'),
    -> (2,'alex',81,'female')
    -> ;

MariaDB [db1]> insert into t1(id) values
    -> (3),
    -> (4);

注意：表中的最后一个字段不要加逗号

查看表结构

describe t1; #查看表结构，可简写为desc 表名

show create table t1\G; #查看表详细结构，可加\G

修改表结构

语法：

修改表名

      ALTER TABLE 表名
                          RENAME 新表名;

增加字段

      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…],
                          ADD 字段名  数据类型 [完整性约束条件…];
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  FIRST;
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;

删除字段

      ALTER TABLE 表名
                          DROP 字段名;

修改字段

      ALTER TABLE 表名
                          MODIFY  字段名 数据类型 [完整性约束条件…];
      ALTER TABLE 表名
                          CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
      ALTER TABLE 表名
                          CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

复制表

复制表结构＋记录 （key不会复制: 主键、外键和索引）
mysql> create table new_service select * from service;

只复制表结构
mysql> select * from service where 1=2;        //条件为假，查不到任何记录
Empty set (0.00 sec)
mysql> create table new1_service select * from service where 1=2;  
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create table t4 like employees; #也是复制表结构

删除表

DROP TABLE 表名;

数据类型

数值类型

整数类型

作用：存储年龄，等级，id，各种号码等

tinyint[(m)] [unsigned] [zerofill]

小整数，数据类型用于保存一些范围的整数数值范围：
有符号：
    -128 ～ 127
无符号：
    0 ～ 255

PS： MySQL中无布尔值，使用tinyint(1)构造。

int[(m)][unsigned][zerofill]

整数，数据类型用于保存一些范围的整数数值范围：
有符号：
        -2147483648 ～ 2147483647
无符号：
        0 ～ 4294967295

bigint[(m)][unsigned][zerofill]

    大整数，数据类型用于保存一些范围的整数数值范围：
    有符号：
            -9223372036854775808 ～ 9223372036854775807
    无符号：
            0  ～  18446744073709551615

int的存储宽度是4个Bytes，即32个bit，即2**32

注意：为该类型指定宽度时，仅仅只是指定查询结果的显示宽度，与存储范围无关，存储范围如下

其实我们完全没必要为整数类型指定显示宽度，使用默认的就可以了

×××类型，其实没有必要指定显示宽度，使用默认的就ok

浮点型

作用：存储薪资、身高、体重、体质参数等

FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

定义：单精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30

有符号： -3.402823466E+38 to -1.175494351E-38, 1.175494351E-38 to 3.402823466E+38 无符号： 1.175494351E-38 to 3.402823466E+38

精确度： **** 随着小数的增多，精度变得不准确 ****

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

定义：双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30

有符号： -1.7976931348623157E+308 to -2.2250738585072014E-308 2.2250738585072014E-308 to 1.7976931348623157E+308

无符号： 2.2250738585072014E-308 to 1.7976931348623157E+308

精确度： ****随着小数的增多，精度比float要高，但也会变得不准确 ****

decimal[(m[,d])] [unsigned] [zerofill]

定义：准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。

精确度： **** 随着小数的增多，精度始终准确 **** 对于精确数值计算时需要用此类型 decaimal能够存储精确值的原因在于其内部按照字符串存储。

日期类型

作用：存储用户注册时间，文章发布时间，员工入职时间，出生时间，过期时间等

YEAR
    YYYY（1901/2155）

DATE
    YYYY-MM-DD（1000-01-01/9999-12-31）

TIME
    HH:MM:SS（'-838:59:59'/'838:59:59'）

DATETIME

    YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59    Y）

TIMESTAMP

    YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）

datetime与timestamp的区别

在实际应用的很多场景中，MySQL的这两种日期类型都能够满足我们的需要，存储精度都为秒，但在某些情况下，会展现出他们各自的优劣。下面就来总结一下两种日期类型的区别。

DATETIME的日期范围是1001——9999年，TIMESTAMP的时间范围是1970——2038年。

DATETIME存储时间与时区无关，TIMESTAMP存储时间与时区有关，显示的值也依赖于时区。在mysql服务器，操作系统以及客户端连接都有时区的设置。

DATETIME使用8字节的存储空间，TIMESTAMP的存储空间为4字节。因此，TIMESTAMP比DATETIME的空间利用率更高。

DATETIME的默认值为null；TIMESTAMP的字段默认不为空（not null）,默认值为当前时间（CURRENT_TIMESTAMP），如果不做特殊处理，并且update语句中没有指定该列的更新值，则默认更新为当前时间。

字符类型

#注意：char和varchar括号内的参数指的都是字符的长度

char类型：

定长，简单粗暴，浪费空间，存取速度快
字符长度范围：0-255（一个中文是一个字符，是utf8编码的3个字节）
存储：
    存储char类型的值时，会往右填充空格来满足长度
    例如：指定长度为10，存>10个字符则报错，存<10个字符则用空格填充直到凑够10个字符存储

检索：
    在检索或者说查询时，查出的结果会自动删除尾部的空格，除非我们打开pad_char_to_full_length SQL模式（SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';）

varchar类型：

变长，精准，节省空间，存取速度慢
字符长度范围：0-65535（如果大于21845会提示用其他类型 。mysql行最大限制为65535字节，字符编码为utf-8：https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html）
存储：
    varchar类型存储数据的真实内容，不会用空格填充，如果'ab  ',尾部的空格也会被存起来
    强调：varchar类型会在真实数据前加1-2Bytes的前缀，该前缀用来表示真实数据的bytes字节数（1-2Bytes最大表示65535个数字，正好符合mysql对row的最大字节限制，即已经足够使用）
    如果真实的数据<255bytes则需要1Bytes的前缀（1Bytes=8bit 2**8最大表示的数字为255）
    如果真实的数据>255bytes则需要2Bytes的前缀（2Bytes=16bit 2**16最大表示的数字为65535）

检索：
    尾部有空格会保存下来，在检索或者说查询时，也会正常显示包含空格在内的内容

了解两个函数

length：查看字节数 char_length:查看字符数

char填充空格来满足固定长度，但是在查询时却会很不要脸地删除尾部的空格（装作自己好像没有浪费过空间一样），然后修改sql_mode让其现出原形
虽然 CHAR 和 VARCHAR 的存储方式不太相同,但是对于两个字符串的比较,都只比较其值,忽略CHAR值存在的右填充,即使将SQL_MODE 设置为 PAD_CHAR_TO_FULL_LENGTH 也一样,但这不适用于like
总结

常用字符串系列：char与varchar 注：虽然varchar使用起来较为灵活，但是从整个系统的性能角度来说，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。因此，用户在设计数据库时应当综合考虑各方面的因素，以求达到最佳的平衡

其他字符串系列（效率：char>varchar>text） TEXT系列 TINYTEXT TEXT MEDIUMTEXT LONGTEXT BLOB 系列 TINYBLOB BLOB MEDIUMBLOB LONGBLOB BINARY系列 BINARY VARBINARY

text：text数据类型用于保存变长的大字符串，可以组多到65535 (2**16−1)个字符。

枚举类型与集合类型

字段的值只能在给定范围中选择，如单选框，多选框

enum 单选只能在给定的范围内选一个值，如性别 sex 男male/女female

set 多选在给定的范围内可以选择一个或一个以上的值（爱好1,爱好2,爱好3...）

MariaDB [db1]> create table consumer( 
    -> name varchar(50),
    -> sex enum('male','female'),
    -> level enum('vip1','vip2','vip3','vip4','vip5'), #在指定范围内，多选一
    -> hobby set('play','music','read','study') #在指定范围内，多选多
    -> );

MariaDB [db1]> insert into consumer values  
    -> ('egon','male','vip5','read,study'),
    -> ('alex','female','vip1','girl');

MariaDB [db1]> select * from consumer;
+------+--------+-------+------------+
| name | sex    | level | hobby      |
+------+--------+-------+------------+
| egon | male   | vip5  | read,study |
| alex | female | vip1  |            |
+------+--------+-------+------------+