一、数据库安装配置
1）数据库的概念
1、数据库相关概念 数据库服务器（本质就是一个台计算机，该计算机之上安装有数据库管理软件的服务端） 数据库管理管理系统RDBMS（本质就是一个C/S架构的套接字软件） 库（文件夹）=====》数据库 表（文件） 记录：抽取一个事物所有典型的特征/数据 egon,18,180,male,True 数据 name='egon' age=18 height=180 sex="male" beutiful=True 2、数据库管理系统/软件分类： 关系型： 有表结构，存取数据前必先定义表结构，存数据必须按照字段的类型或者约束来 典型代表：MySQL，Oracle，DB2，SQL server 非关系型: 存取数据都是采用key:value的形式 非关系型：Mongodb，redis，memcache
2）mysql数据库的下载
社区版本
下载地址: https://dev.mysql.com/downloads/mysql/5.7.html#downloads
选择版本
根据电脑需求下载
不需要注册，直接下载
3）mysql的安装使用
解压即安装，
bin目录存放于环境变量里面
管理员用户启动cmd,输入mysqld启动mysql服务器程序
mysql -h 127.0.0.1 -P 3306 -uroot -p 启动客户端程序，没有密码直接登录
简写本地登录：mysql -uroot -p
4）修改root密码操作
修改成空密码
破解root密码方法
跳过授权表启动服务端，客户端登录则无需要密码
修改root密码
5）存的库，即data下的文件夹。performance_schema内存文件，及不会存在硬盘空间里面
information_schema： 虚拟库，不占用磁盘空间，存储的是数据库启动后的一些参数，如用户表信息、列信息、权限信息、字符信息等 performance_schema： MySQL 5.5开始新增一个数据库：主要用于收集数据库服务器性能参数，记录处理查询请求时发生的各种事件、锁等现象 mysql： 授权库，主要存储系统用户的权限信息 test： MySQL数据库系统自动创建的测试数据库
6）关闭mysql服务的方式，并制作系统服务
杀死进程号，关闭mysql
mysql制作成系统服务
制作：先关闭 管理员cmd里面输入：mysqld --install 查看： windows+r 输入services.msc 制作完系统服务后就可以鼠标点击启动或关闭mysql： windows+r 输入services.msc 找到mysql，启动或关闭
7）增加配置文件，my.ini，调字符编码
[mysqld] character-set-server=utf8 collation-server=utf8_general_ci [client] default-character-set=utf8 [mysql] user="root" password="123456" default-character-set=utf8
强调：utf8会存在字符集不兼容问题，需要将 utf8 改成 utf8mb4
重启服务 mysql> s 查看修改结果
在Linux里面的mysql修改字符编码操作在 /etc/my.cnf 里面加上相应的内容。即可
二、MySQL数据库语法的使用
1）基本的SQL语句
1、文件夹（库） 增 create database db1 charset utf8; 改 alter database db1 charset gbk; 查 查看所有库的库名 show databases; 单独查看某一个库的信息 show create database db1; 删 drop database db1; 2、文件（表） 首先切换文件夹： use db1; select database(); #查看当前所在的文件夹 增 create table t1(id int,name char); 改 alter table t1 modify name char(16); 查 查看当前库下所有的表名 show tables; 查看t1表的详细信息 show create table t1; 查看表结构 desc t1; 删 drop table t1; 3、文件的一行内容（记录） 增 insert into db1.t1 values (1,'egon'), (2,'alex'), (3,'lxx'); 改 update db1.t1 set name='sb' where id > 1; 查 select id,name from db1.t1; 删 delete from db1.t1 where name = "SB" ;
实际操作方法
库操作 mysql> create database db1 charset utf8; 创建库 Query OK, 1 row affected (0.07 sec) mysql> show databases; 显示所有的库 mysql> show create database db1; 查看被创建库的信息 +----------+--------------------------------------------------------------+ | Database | Create Database | +----------+--------------------------------------------------------------+ | db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+--------------------------------------------------------------+ mysql> alter database db1 charset gbk; 修改库，只是修改库的字符编码， Query OK, 1 row affected (0.01 sec) mysql> show create database db1; mysql> drop database db1; 删除库 表操作 mysql> create database db1; 修改了配置文件的字符编码，默认创建出来的就是 utf8 Query OK, 1 row affected (0.00 sec) mysql> use db1; 切换到该库 Database changed mysql> select database(); 查看当前在哪个库 mysql> create table t1(id int,name char(16)); 创建表 mysql> show create table t1; 查看被创建表的信息 mysql> desc t1; 查看表结构 +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | char(16) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ mysql> alter table t1 modify name char(15); 修改表信息的操作 mysql> alter table t1 change name NAME char(10); 字段名都能修改 mysql> desc t1; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | NAME | char(10) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.01 sec) mysql> drop table t1; 删表操作 修改文件的一行内容（记录） mysql> create table t1(id int,name char(16)); mysql> insert into db1.t1 values -> (1,'egon'), -> (2,'alex'), -> (3,'lxx'); Query OK, 3 rows affected (0.10 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select id,name from t1; +------+------+ | id | name | +------+------+ | 1 | egon | | 2 | alex | | 3 | lxx | +------+------+ 3 rows in set (0.00 sec) mysql> update t1 set name="sb" where id=3; 单独修改某行操作 mysql> select * from t1; +------+------+ | id | name | +------+------+ | 1 | egon | | 2 | alex | | 3 | sb | +------+------+ 3 rows in set (0.00 sec mysql> delete from t1 where id >=2; 删除某些行 # delete 只能用于删除符合条件的某几条记录，不能用于清空表 # 清空表应该使用truncate,不仅删除所有记录，而且将自增字段的值归0 mysql> truncate t1; 清空表 Query OK, 0 rows affected (0.15 sec)
2）mysql支持的存储引擎
show engines; 查看MySQL所支持的存储引擎 create table t1(id int)engine=innodb; （默认）常用 create table t2(id int)engine=myisam; create table t3(id int)engine=blackhole; create table t4(id int)engine=memory;
3）创建表的完整语法
create table 表名( 字段名1 类型[(宽度) 约束条件], 字段名2 类型[(宽度) 约束条件], 字段名3 类型[(宽度) 约束条件] ); [ ] 内部条件可有可无 #解释： 类型：使用限制字段必须以什么样的数据类型传值 约束条件：约束条件是在类型之外添加一种额外的限制 # 注意： 1. 在同一张表中，字段名是不能相同 2. 宽度和约束条件可选，字段名和类型是必须的 3、最后一个字段后不加逗号 create database db37; # 创建库 create table t1(name char); # 语法结构：create table 表名(字段名1 类型[(宽度) 约束条件]) desc t1; # 查看存储的类型 insert into t1 values('egon'); # 由于上面创建的char类型默认存储1个字符，只能存储一个e create table t2(id int,name char not null); # 约束条件
4）清空表
delete from tb1; 强调：上面的这条命令确实可以将表里的所有记录都删掉，但不会将id重置为0， 所以收该条命令根本不是用来清空表的，delete是用来删除表中某一些符合条件的记录 delete from tb1 where id > 10; 如果要清空表，使用truncate tb1； 作用：将整张表重置
5）修改表
语法： 1. 修改表名 ALTER TABLE 表名 RENAME 新表名; 2. 增加字段 ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…], ADD 字段名 数据类型 [完整性约束条件…]; ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] FIRST; ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名; 3. 删除字段 ALTER TABLE 表名 DROP 字段名; 4. 修改字段 ALTER TABLE 表名 MODIFY 字段名 数据类型 [完整性约束条件…]; ALTER TABLE 表名 CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…]; ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
示例
1. 修改存储引擎 mysql> alter table service -> engine=innodb; 2. 添加字段 mysql> alter table student10 -> add name varchar(20) not null, -> add age int(3) not null default 22; mysql> alter table student10 -> add stu_num varchar(10) not null after name; //添加name字段之后 mysql> alter table student10 -> add sex enum('male','female') default 'male' first; //添加到最前面 3. 删除字段 mysql> alter table student10 -> drop sex; mysql> alter table service -> drop mac; 4. 修改字段类型modify mysql> alter table student10 -> modify age int(3); mysql> alter table student10 -> modify id int(11) not null primary key auto_increment; //修改为主键 5. 增加约束（针对已有的主键增加auto_increment） mysql> alter table student10 modify id int(11) not null primary key auto_increment; ERROR 1068 (42000): Multiple primary key defined mysql> alter table student10 modify id int(11) not null auto_increment; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 6. 对已经存在的表增加复合主键 mysql> alter table service2 -> add primary key(host_ip,port); 7. 增加主键 mysql> alter table student1 -> modify name varchar(10) not null primary key; 8. 增加主键和自动增长 mysql> alter table student1 -> modify id int not null primary key auto_increment; 9. 删除主键 a. 删除自增约束 mysql> alter table student10 modify id int(11) not null; b. 删除主键 mysql> alter table student10 -> drop primary key;
6）复制表操作。复制表结构＋记录 （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 表名; 删除表
7）约束条件，约束条件与数据类型的宽度一样，都是可选参数
作用：用于保证数据的完整性和一致性
PRIMARY KEY (PK) 标识该字段为该表的主键，可以唯一的标识记录
FOREIGN KEY (FK) 标识该字段为该表的外键
NOT NULL 标识该字段不能为空
UNIQUE KEY (UK) 标识该字段的值是唯一的
AUTO_INCREMENT 标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT 为该字段设置默认值
UNSIGNED 无符号
ZEROFILL 使用0填充
说明
1. 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值 2. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值 sex enum('male','female') not null default 'male' age int unsigned NOT NULL default 20 必须为正值（无符号） 不允许为空 默认是20 3. 是否是key 主键 primary key 外键 foreign key 索引 (index,unique...)
三、数字类型
1）整型类型，建议用 int
1、作用：id号，各种号码，年龄，等级 2、分类： tinyint（**） int （*****） bigint（***） 3、测试：默认整型都是有符号的 create table t1(x tinyint); insert into t1 values(128),(-129); # 超过范围，存取最大值 create table t2(x tinyint unsigned); # unsigned 约束条件，无符号 insert into t2 values(-1),(256); create table t3(x int unsigned); #4294967295 insert into t3 values(4294967296); create table t4(x int(12) unsigned); insert into t4 values(4294967296123); 4、强调：对于整型来说，数据类型后的宽度并不是存储限制，而是显示限制 所以在创建表示，如果字段采用的是整型类型，完全无需指定显示宽度， 默认的显示宽度，足够显示完整当初存放的数据 # 显示时，不够8位用0填充，如果超出8位则正常显示 create table t5(x int(8) unsigned zerofill); insert into t5 values(4294967296123); insert into t5 values(1);
实际操作案例
mysql> create table t2(id tinyint); Query OK, 0 rows affected (1.05 sec) mysql> insert into t2 values(-1); Query OK, 1 row affected (0.15 sec) mysql> insert into t2 values(128); 严格模式，超过范围了 ERROR 1264 (22003): Out of range value for column 'id' at row 1 mysql> insert into t2 values(127); Query OK, 1 row affected (0.07 sec) mysql> select * from t2; +------+ | id | +------+ | -1 | | 127 | +------+ mysql> select @@sql_mode; 查看全局模式 +--------------------------------------------+ | @@sql_mode | +--------------------------------------------+ | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | +--------------------------------------------+ mysql> create table t3(id tinyint unsigned); # 创建无符号的整数 Query OK, 0 rows affected (1.06 sec) mysql> insert into t3 values(-1); # 说明表设计是无符号的，不能插入负数 ERROR 1264 (22003): Out of range value for column 'id' at row 1 mysql> create table t5(id int(3) unsigned); Query OK, 0 rows affected (0.18 sec) mysql> insert into t5 values(1); Query OK, 1 row affected (0.06 sec) mysql> select * from t5; # int(3) 控制的是显示宽度，默认无法查看。unsigned zerofill 可以显示出来，0填充 +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec) create table t5(x int(8) unsigned zerofill);
2）sql_mode 修改为严格模式，比如超出范围报错等
# 查看sql_mode mysql> show variables like "%sql_mode%"; +----------------------------+---------------------+ | Variable_name | Value | +----------------------------+---------------------+ | binlogging_impossible_mode | IGNORE_ERROR | | block_encryption_mode | aes-128-ecb | | gtid_mode | OFF | | innodb_autoinc_lock_mode | 1 | | innodb_strict_mode | OFF | | pseudo_slave_mode | OFF | | slave_exec_mode | STRICT | | sql_mode | STRICT_TRANS_TABLES | +----------------------------+---------------------+ 8 rows in set (0.00 sec) #修改sql_mode为严格模式：在该模式下，如果插入的数据超过限制，则会立即报错 mysql> set global sql_mode="strict_trans_tables";
3）小数类型，建议用float
作用：存储身高、体重、薪资 分类： float （*****） double （**） decimal （**） 测试： #相同点 #1、对于三者来说，都能存放30位小数， #不同点： 1、精度的排序从低到高：float,double,decimal 2、float与double类型能存放的整数位比decimal更多 create table t9(x float(255,30)); create table t10(x double(255,30)); create table t11(x decimal(65,30)); insert into t9 values(1.111111111111111111111111111111); insert into t10 values(1.111111111111111111111111111111); insert into t11 values(1.111111111111111111111111111111); mysql> select * from t9; +----------------------------------+ | x | +----------------------------------+ | 1.111111164093017600000000000000 | +----------------------------------+ 1 row in set (0.00 sec) mysql> select * from t10; +----------------------------------+ | x | +----------------------------------+ | 1.111111111111111200000000000000 | +----------------------------------+ 1 row in set (0.00 sec) mysql> select * from t11; +----------------------------------+ | x | +----------------------------------+ | 1.111111111111111111111111111111 | +----------------------------------+ 1 row in set (0.00 sec)
四、其他常用类型
1）字符类型，char 和 varchar
char 和 varchar 的作用
1、作用：姓名，地址，描述类的信息 2、分类： char(5) 定长，最大存放5个字符，不够5个字符，那么用空格补齐 varchar(5) 变长，最大存放5个字符，传入几个字符，就存入几个字符
使用方法
测试:字符的宽度限制单位是字符个数 create table t12(x char(4)); # 超出4个字符则报错，不够4个字符则用空格补全成4个字符 create table t13(y varchar(4));# 超出4个字符则报错，不够4个字符那么字符有几个就存几个 insert into t12 values('hello'); insert into t13 values('hello'); insert into t12 values('a'); #'a ' insert into t13 values('a'); #'a' # 设置全局模式，查看字符长度 set global sql_mode="strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH"; select char_length(x) from t12; #4 select char_length(y) from t13; #1 # 注意： 针对char类型，mysql在存储时会将数据用空格补全存放到硬盘中 但会在读出结果时自动去掉末尾的空格，因为末尾的空格在以下场景中是无用 mysql> select * from t14 where name="lxx"; # name字段明确地等于一个值，该值后填充空格是没有用 mysql> select * from t14 where name like "lxx"; # name字段模糊匹配一个值，该值后填充空格是有用的 # 对比char与varchar name char(5) # 缺点：浪费空间 # 优点：存取速度都快 egon alex lxx wxx yx name varchar(5) # 缺点：存取速度都慢 # 优点：节省空间 (1bytes+egon)(1bytes+alex)(1bytes+lxx)
2）时间，日期类型
时间范围
# 时间类型 time ,date, datetime,timestamp,year 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 年某时） create table t7(x timestamp); = create table t7(x datetime not null default now());
使用方法，如果不考虑时间范围的话，请使用timestamp，因为和datetime相比，占用空间更小，且默认存放当前时间
1、作用：时间相关 2、分类： date：1999-01-27 time: 11:11:11 datetime:1999-01-27 11:11:11 year:1999 3、测试 create table student( id int, name char(16), born_year year, birth date, class_time time, reg_time datetime ); insert into student values(1,'egon','2000','2000-01-27','08:30:00','2013-11-11 11:11:11');
实际操作
create table student( id int primary key auto_increment, name char(16), born_year year, class_time time, birth date, reg_time datetime ); insert into student(name,born_year,class_time,birth,reg_time) value ('egon',now(),now(),now(),now()); mysql> create table student( -> id int primary key auto_increment, -> name char(16), -> born_year year, -> class_time time, -> birth date, -> reg_time datetime -> ); Query OK, 0 rows affected (0.40 sec) mysql> insert into student(name,born_year,class_time,birth,reg_time) value -> ('egon',now(),now(),now(),now()); Query OK, 1 row affected, 1 warning (0.18 sec) mysql> select * from student; +----+------+-----------+------------+------------+---------------------+ | id | name | born_year | class_time | birth | reg_time | +----+------+-----------+------------+------------+---------------------+ | 1 | egon | 2018 | 22:52:19 | 2018-07-08 | 2018-07-08 22:52:19 | +----+------+-----------+------------+------------+---------------------+ mysql> insert into student(birth) values('2018-08-08'); Query OK, 1 row affected (0.12 sec) mysql> select * from student; +----+------+-----------+------------+------------+---------------------+ | id | name | born_year | class_time | birth | reg_time | +----+------+-----------+------------+------------+---------------------+ | 1 | egon | 2018 | 22:52:19 | 2018-07-08 | 2018-07-08 22:52:19 | | 2 | NULL | NULL | NULL | 2018-08-08 | NULL | +----+------+-----------+------------+------------+---------------------+ 2 rows in set (0.00 sec) mysql> create table t7(x timestamp); # 不传时间，则默认传入当前时间 Query OK, 0 rows affected (0.18 sec) mysql> insert into t7 values(); Query OK, 1 row affected (0.04 sec) mysql> select * from t7; +---------------------+ | x | +---------------------+ | 2018-07-08 23:00:55 | +---------------------+ 1 row in set (0.00 sec)
3）枚举与集合类型。选择类型
作用与分类： 枚举enum，多选一个 集合set，多选多 测试 create table teacher( id int, name char(16), sex enum('male','female','others'), hobbies set('play','read','music','piao') );
实际操作
# 枚举类型enum与集合类型set # enum: 多选一 # set: 多选多 create table emp( id int primary key auto_increment, name char(16), sex enum('male','female','others'), hobbies set('play','read','music','piao') ); insert into emp(name,sex,hobbies) values ('egon','male','play,music'); mysql> create table emp( -> id int primary key auto_increment, -> name char(16), -> sex enum('male','female','others'), -> hobbies set('play','read','music','piao') -> ); Query OK, 0 rows affected (0.24 sec) mysql> insert into emp(name,sex,hobbies) values -> ('egon','male','play,music'); Query OK, 1 row affected (0.04 sec) mysql> select * from emp; +----+------+------+------------+ | id | name | sex | hobbies | +----+------+------+------------+ | 1 | egon | male | play,music | +----+------+------+------------+
五、约束条件
1）not null + default 缺值，则使用默认值
create table t15( id int, name char(16) not null, sex enum('male','female','other') not null default "male" ); # 性别默认选择男性 alter table t15 modify name char(16) not null; # 单独修改表字段 insert into t15(id,name) values(4,'alex') insert into t15(id,name) values (1,'egon1'), (2,'egon2'), (3,'egon3');
2）限制字段的值唯一
#单列唯一 create table t16( id int unique, name char(16) ); # 联合唯一 create table server( id int unique, ip char(15), port int, unique(ip,port) );
3）约束条件之主键。primary key:单单从约束角度去看，primary key就等同于not null unique
#强调（******） 1、一张表中必须有，并且只能有一个主键 2、一张表中都应该有一个id字段，而且应该把id字段做成主键
主键的使用
create table t17( id int primary key, name char(16), age int, sex char(6) )engine=innodb; #联合主键 create table t19( ip char(15), port int, primary key(ip,port) );
auto_increment 与 与primary key连用，实现自增 id
# primary key auto_increment create table t20( id int primary key auto_increment, name char(16) )engine=innodb; # primary和auto_increment注意点： 1、单从约束角度去看，主键的约束效果就是not null + unique 2、但其实innodb存储引擎会以主键为准创建聚集索引 3、通常与primary key连用，而且通常是给id字段加 4、auto_incremnt只能给被定义成key（unique key，primary key）的字段加 结论： 如果表的类型为innodb，那么在建立表时，应该有一个id字段 并且 id int primary key auto_increment
六、权限管理设置
1）创建授权用户
create user 'egon'@'1.1.1.1' identified by '123'; create user 'egon'@'192.168.1.%' identified by '123'; create user 'egon'@'%' identified by '123';
2）授权
#授权：对文件夹，对文件，对文件某一字段的权限 查看帮助：help grant 常用权限有：select,update,alter,delete all可以代表除了grant之外的所有权限 #针对所有库的授权:*.* #只在user表中可以查到egon1用户的select权限被设置为Y grant select on *.* to 'egon1'@'localhost' identified by '123'; #针对某一数据库：db1.* #只在db表中可以查到egon2用户的select权限被设置为Y grant select on db1.* to 'egon2'@'%' identified by '123'; #针对某一个表：db1.t1 #只在tables_priv表中可以查到egon3用户的select权限 grant select on db1.t1 to 'egon3'@'%' identified by '123'; #删除权限 revoke select on db1.* from 'egon'@'%';
3）授权常用操作
服务端授权用户 grant all on *.* to 'egon'@'192.168.1.%' identified by '123'; flush privileges; 客户端连接 mysql -uegon -p123 -h192.168.1.5