Python Day10 MySQL 01
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服务启动时生效
;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客户端命令执行时，下列配置生效
port=3306
default-character-set=utf8
user=root
password=123
[*]只针对mysql这个客户端的配置，2中的是全局配置，而此处的则是只针对mysql这个命令的局部配置
;port=3306
;default-character-set=utf8
user=egon
password=4573
！！！如果没有,则用户在执行mysql命令时的配置以为准
统一字符编码
#1. 修改配置文件
default-character-set=utf8
default-character-set=utf8
default-character-set=utf8
#mysql5.5以上：修改方式有所改动
character-set-server=utf8
collation-server=utf8_general_ci
default-character-set=utf8
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> select * from db1.t1;
改
update t1 set name='SB' where> 删
delete from t1 where>
库的操作
创建数据库
[*]语法(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 > insert into t1 values -> (1,'egon',18,'male'),
-> (2,'alex',81,'female')
-> ;
MariaDB > insert into t1(id) values -> (3),
-> (4);
注意：表中的最后一个字段不要加逗号
查看表结构
describe t1; #查看表结构，可简写为desc 表名
show create table t1\G; #查看表详细结构，可加\G
修改表结构
语法：
[*] 修改表名
>RENAME 新表名;
[*] 增加字段
>ADD 字段名数据类型 [完整性约束条件…],
ADD 字段名数据类型 [完整性约束条件…];
ALTER TABLE 表名
ADD 字段名数据类型 [完整性约束条件…]FIRST;
ALTER TABLE 表名
ADD 字段名数据类型 [完整性约束条件…]AFTER 字段名;
[*] 删除字段
>DROP 字段名;
[*]修改字段
>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: 0Duplicates: 0Warnings: 0
mysql> create table t4 like employees; #也是复制表结构
删除表
DROP TABLE 表名;
数据类型
数值类型
整数类型
作用：存储年龄，等级，id，各种号码等
tinyint[(m)]
小整数，数据类型用于保存一些范围的整数数值范围：
有符号：
-128 ～ 127
无符号：
0 ～ 255
PS： MySQL中无布尔值，使用tinyint(1)构造。
int[(m)]
整数，数据类型用于保存一些范围的整数数值范围：
有符号：
-2147483648 ～ 2147483647
无符号：
0 ～ 4294967295
bigint[(m)]
大整数，数据类型用于保存一些范围的整数数值范围： 有符号：
-9223372036854775808 ～ 9223372036854775807
无符号：
0～18446744073709551615
int的存储宽度是4个Bytes，即32个bit，即2**32
注意：为该类型指定宽度时，仅仅只是指定查询结果的显示宽度，与存储范围无关，存储范围如下
其实我们完全没必要为整数类型指定显示宽度，使用默认的就可以了
整形类型，其实没有必要指定显示宽度，使用默认的就ok
浮点型
作用：存储薪资、身高、体重、体质参数等
FLOAT[(M,D)]
定义：
单精度浮点数（非准确小数值），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)]
定义：
双精度浮点数（非准确小数值），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])]
定义：
准确的小数值，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 > create table consumer( -> name varchar(50),
-> sex enum('male','female'),
-> level enum('vip1','vip2','vip3','vip4','vip5'), #在指定范围内，多选一
-> hobby set('play','music','read','study') #在指定范围内，多选多
-> );
MariaDB > insert into consumer values
-> ('egon','male','vip5','read,study'),
-> ('alex','female','vip1','girl');
MariaDB > select * from consumer;
+------+--------+-------+------------+
| name | sex | level | hobby |
+------+--------+-------+------------+
| egon | male | vip5| read,study |
| alex | female | vip1| |
+------+--------+-------+------------+
页:
[1]