MySQL- 5..7.24-winx64 安装详解
一 MySQL 安装及配置
1.下载
首先上MySql的官网下载 https://dev.mysql.com/downloads/mysql/ ，本人下载的是 mysql-5.7.24-winx64.zip版。
2.解压存放目录
下载完解压到你想要存放的位置 我的是解压到D:\Program Files\MySQL 。
3.配置环境变量
在环境变量path中追加一句：;D:\Program Files\MySQL\bin 切记前面有其他环境变量时一定加 ;
4.自己配置mysql的配置文件my.ini
在D:\Program Files\MySQL\ 目录下新建my.ini,打开加入如下代码：
[mysql] # 设置mysql客户端默认字符集 default-character-set=utf8 [mysqld] #设置3306端口 port = 3306 # 设置mysql的安装目录 basedir=D:\Program Files\MySQL # 设置mysql数据库的数据的存放目录 datadir=D:\Program Files\MySQL\data # 允许最大连接数 max_connections=200 # 服务端使用的字符集默认为8比特编码的latin1字符集 character-set-server=utf8 # 创建新表时将使用的默认存储引擎 default-storage-engine=INNODB
5.在cmd中配置
以管理员身份打开cmd命令窗口，进入D:\Program Files\MySQL\bin>目录下，运行mysqld --initialize --user=mysql --console ，见下图所示：
6.验证安装是否成功
完成上面两个文件的创建后，还是在D:\mysql-5.7.20-winx64\bin下用管理员运行cmd 输入命令 mysqld -install 如果出现Service successfully installed 说明注册成功了
7.启动和关闭mysql服务
启动服务：net start mysql
关闭服务：net stop mysql
到此mysql数据库安装和配置完毕。
二 MySQL的基础知识
1.创建、删除及显示数据库
C:\Users\Dell>mysql -u root -p Enter password: ******* Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.7.24 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database db; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> drop database db; Query OK, 0 rows affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec)
注意：一次只能删除一个数据库，不能同时删除多个库
2.创建数据库db1,并设置数据库字符编码为utf8
mysql> create database db1 character set utf8; Query OK, 1 row affected (0.03 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db | | db1 | | mysql | | performance_schema | | sys | +--------------------+ 6 rows in set (0.00 sec)
3.创建数据库db2,设置编码utf8和校对规则utf8_general_ci (校对规则：可以理解成排序规则；默认使用utf8_general_ci )
mysql> create database db2 character set utf8 collate utf8_general_ci; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db | | db1 | | db2 | | mysql | | performance_schema | | sys | +--------------------+ 7 rows in set (0.00 sec)
4.显示数据库创建语句
mysql> show create database db; +----------+-------------------------------------------------------------+ | Database | Create Database | +----------+-------------------------------------------------------------+ | db | CREATE DATABASE `db` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+-------------------------------------------------------------+ 1 row in set (0.00 sec)
5.mysql数据类型
需要重点掌握的有以下几种：
数值型： int(整型，不指定代表有符号) float(存储小数) decimal(存储小数，精度高)
字符型: char(存储字符，范围 0~255字符) varchar(存储字符，范围 0~65532 字节) text
日期型：data (存储日期类型)
字符数据类型解释说明如下图
6.创建数据表
基本语法
create table 表名 (
字段1 数据类型,
字段2 数据类型,
字段n 数据类型,
)character set 字符编码 collate 校对规则 engine 存储引擎方式 field:指定列名 datatype: 指定列类型
注意：表后面的各项设置以创建表时设置的为准，如表没有设置，以数据库为准。
创建数据表user并显示其归属哪个数据库(显示归属的数据库命令：show tables; )
mysql> use db; Database changed mysql> create table user(id int,salary float(4,2),bonus decimal(4,2)); Query OK, 0 rows affected (0.05 sec) mysql> select * from user; Empty set (0.01 sec) mysql> show tables; +--------------+ | Tables_in_db | +--------------+ | user | +--------------+ 1 row in set (0.00 sec)
注：float(4,2) 表示存储范围在 -99.99~99.99 ; decimal(6,2)表示存储范围在 -9999.99~9999.99 ；即 float (n,m)表示共有 n 位数字，m表示保留 m 位小数。精度测试数据为：(10,2)
7.创建案例：雇员表 employee
mysql> create table employee(
-> id int unsigned,
-> name varchar(100) not null default '',
-> sex char(1) not null default '',
-> brithday date ,
-> job varchar(30) not null default '',
-> salary decimal(10,2) not null default 0,
-> introduction text)character set utf8 engine MyISAM;
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
+--------------+
| Tables_in_db |
+--------------+
| employee |
| user |
+--------------+
2 rows in set (0.00 sec)
表employee说明如下：
8.查看表结构的详细信息
mysql> desc employee; +--------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+------------------+------+-----+---------+-------+ | id | int(10) unsigned | YES | | NULL | | | name | varchar(100) | NO | | | | | sex | char(1) | NO | | | | | brithday | date | YES | | NULL | | | job | varchar(30) | NO | | | | | salary | decimal(10,2) | NO | | 0.00 | | | introduction | text | YES | | NULL | | +--------------+------------------+------+-----+---------+-------+ 7 rows in set (0.00 sec)
9.数据表CRUD操作
- insert语句(插入数据)
insert into 表名(字段1，字段2，...字段n) values (字段 值1，字段值2，...字段值n);
示例代码
mysql> insert into employee(id,name,sex,brithday,job,salary,introduction)
-> values(001,'张国锋','男','1981-12-28','software engineer',12000,'工作态度端正，认真负责本职工作，技术能力强，是不可多得的人才！');
Query OK, 1 row affected (0.00 sec)
mysql> select * from employee;
+------+-----------+-----+------------+-------------------+----------+-----------------------------------------------------------------------------------------------+
| id | name | sex | brithday | job | salary | introduction |
+------+-----------+-----+------------+-------------------+----------+-----------------------------------------------------------------------------------------------+
| 1 | 张国锋 | 男 | 1981-12-28 | software engineer | 12000.00 | 工作态度端正，认真负责本职工作，技术能力强，是不可多得的人才！ |
+------+-----------+-----+------------+-------------------+----------+-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
注：在插入数据时，sql代码工具一定要与表设置的字符编码相同，否则插入的数据会出现乱码；也可在mysql命令提示符下输入：mysql> set names utf8 或 gbk;
- update语句(更新数据)
update 表名 set 字段1=新值,字段2=新值 where 条件;
示例代码
mysql> select * from employee; +------+------------+-----+------------+-------------------+----------+-----------------------------------------------------------------------------------------------+ | id | name | sex | brithday | job | salary | introduction | +------+------------+-----+------------+-------------------+----------+-----------------------------------------------------------------------------------------------+ | 1 | 张国锋 | 男 | 1981-12-28 | software engineer | 12000.00 | 工作态度端正，认真负责本职工作，技术能力强，是不可多得的人才！ | | 2 | 李建林 | 男 | 1972-10-21 | teacher | 5200.00 | 工作认真，讲课生动有趣，在学校评为优秀讲师 | +------+------------+-----+------------+-------------------+----------+-----------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> update employee set salary=6000 where id=2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from employee; +------+------------+-----+------------+-------------------+----------+-----------------------------------------------------------------------------------------------+ | id | name | sex | brithday | job | salary | introduction | +------+------------+-----+------------+-------------------+----------+-----------------------------------------------------------------------------------------------+ | 1 | 张国锋 | 男 | 1981-12-28 | software engineer | 12000.00 | 工作态度端正，认真负责本职工作，技术能力强，是不可多得的人才！ | | 2 | 李建林 | 男 | 1972-10-21 | teacher | 6000.00 | 工作认真，讲课生动有趣，在学校评为优秀讲师 | +------+------------+-----+------------+-------------------+----------+-----------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> update employee set salary=salary+1000 where id=2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from employee; +------+------------+-----+------------+-------------------+----------+-----------------------------------------------------------------------------------------------+ | id | name | sex | brithday | job | salary | introduction | +------+------------+-----+------------+-------------------+----------+-----------------------------------------------------------------------------------------------+ | 1 | 张国锋 | 男 | 1981-12-28 | software engineer | 12000.00 | 工作态度端正，认真负责本职工作，技术能力强，是不可多得的人才！ | | 2 | 李建林 | 男 | 1972-10-21 | teacher | 7000.00 | 工作认真，讲课生动有趣，在学校评为优秀讲师 | +------+------------+-----+------------+-------------------+----------+-----------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
注：如果不加wherer条件，所有数据的指定字段值全部修改！
- delete语句(删除数据)
delete from 表名 where 条件;
示例代码
mysql> select * from employee; +------+------------+-----+------------+-------------------+----------+-----------------------------------------------------------------------------------------------+ | id | name | sex | brithday | job | salary | introduction | +------+------------+-----+------------+-------------------+----------+-----------------------------------------------------------------------------------------------+ | 1 | 张国锋 | 男 | 1981-12-28 | software engineer | 12000.00 | 工作态度端正，认真负责本职工作，技术能力强，是不可多得的人才！ | | 2 | 李建林 | 男 | 1972-10-21 | teacher | 7000.00 | 工作认真，讲课生动有趣，在学校评为优秀讲师 | +------+------------+-----+------------+-------------------+----------+-----------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> delete from employee where id=2; Query OK, 1 row affected (0.00 sec) mysql> select * from employee; +------+-----------+-----+------------+-------------------+----------+-----------------------------------------------------------------------------------------------+ | id | name | sex | brithday | job | salary | introduction | +------+-----------+-----+------------+-------------------+----------+-----------------------------------------------------------------------------------------------+ | 1 | 张国锋 | 男 | 1981-12-28 | software engineer | 12000.00 | 工作态度端正，认真负责本职工作，技术能力强，是不可多得的人才！ | +------+-----------+-----+------------+-------------------+----------+-----------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
- select语句(查询数据)
#按指定字段查询 select 字段1，字段2，... 字段n from 表名 where 条件; #对表进行所有字段的查询 select * from 表名 where 条件;
注：在mysql中，表名和字段名不区分大小写
- order by语句(对查询结果进行排序)
select 字段1，字段2，... 字段n from 表名 where 条件 order by 字段 [asc|desc];
代码示例
