mysql 安装 & 基本操作 & 数据类型

2021-11-06 04:18:59

windows10系统mysql5.7 安装

  1. 下载mysql-5.7.34-winx64 https://dev.mysql.com/downloads/file/?id=502363

  2. 解压mysql-5.7.34-winx64.zip

  3. 进入解压文件，创建my.ini文件

  4. my.ini文件编写

    [mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=C:\\ly\mysql-5.7.34-winx64
# 设置mysql数据库的数据的存放目录
datadir=C:\\ly\mysql-5.7.34-winx64\\data
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

  5. 以管理员身份运行cmd，进入bin目录执行：

    1. 初始化，创建mysql默认的root账户

      # mysqld --initialize-insecure --user=mysql

    2. 安装

      # mysqld install

    3. 启动MySql服务

      # net start mysql

    4. 修改环境变量，添加C:\ly\mysql-5.7.34-winx64\bin

    5. cmd窗口中执行mysql -uroot -p进入；默认没有密码

    6. 若要卸载先停止服务，再删除即可

      #启动mysql服务
net start mysql
#停止mysql服务
net stop mysql

Mysql基础操作

  • 使用方法：

    • 方式一：通过图形界面工具，如Navicat等
    • 方式二：通过在命令行敲命令来操作

  • SQL(Structure query language)结构化查询语言

    SQL语言分为4个部分：DDL（定义）、DML（操作）、DQL（查询）、DCL(控制)

  • 关系型数据库：表与表之间有练习

    • 比如：mysql,oracle,db2,sqlserver

  • 非关系型数据库：key-value键值对形式 没有表的概念

    • 比如：redis,mongodb,memcache

  • 登录完整语法：

    mysql -u用户名  -p密码 -h地址ip
（1）登录到本地mysql  默认用户root 是最高权限账户
mysql -uroot -p
localhost => 127.0.0.1 本地ip地址
（2）退出mysql
\q 或者 exit
（3）远程连接mysql服务器
mysql -uroot -p -h192.168.200.30

  • 常用命令

    #查看当前登录的用户
mysql> select user()
    -> ;
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.01 sec)

#设置密码
mysql> set password = password(‘123456‘);
Query OK, 0 rows affected, 1 warning (0.01 sec)

#去除密码
mysql> set password=password(‘‘);
Query OK, 0 rows affected, 1 warning (0.03 sec)

#windows连接远程linux中的mysql
	ipconfig  windows | ipconfig linux
	#创建用户ceshi100，密码333，授权192.168.65.1地址
	create user ‘ceshi100‘@‘192.168.65.1‘ identified by ‘333‘;
    #给具体某个网段下的所有ip设置账户，%代表任务的数据0~255
    create user ‘ceshi11‘@‘192.168.111.%‘ identified by ‘111‘
    #所有的ip都可以登录  %任意所有ip
    create user ‘ceshi12‘@‘%‘ identified by ‘111‘
    
    #查看用户ceshi12的权限
    #USAGE 没有任何权限
   mysql> show grants for ‘ceshi12‘@‘%‘;
	+-------------------------------------+
    | Grants for ceshi12@%                |
	+-------------------------------------+
	| GRANT USAGE ON *.* TO ‘ceshi12‘@‘%‘ |
	+-------------------------------------+
	1 row in set (0.00 sec)
    
    grant 权限 on 数据库.表名 to ‘用户名%ip地址‘ identified by ‘密码‘
    ####主要权限
    select 查询数据库的权限
    insert 插入数据库的权限
    update 更新数据库的权限
    delete 删除数据库的权限
    * 代表所有
    
    #授权
	grant select on *.* to ‘ceshi12‘@‘%‘ identified by ‘222‘;
    #all 代表所有的权限
    grant all on *.* to ‘ceshi12‘@‘%‘ identified by ‘222‘;
    #查看所有数据库
    show databases;
    #移除权限
    revoke select on *.* from ‘ceshi12‘@‘%‘;
    revoke all on *.* from ‘ceshi12‘@‘%‘;
    
mysql> create database abc;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| abc                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

	#删除账户
    drop user ‘ceshi12‘@‘%‘;
    #刷新权限,立即生效
    flush privileges

SQL语句中的快捷键

  • \G 格式化输出（文本式、竖立显示）
  • \s 查看服务器端信息
  • \c 结束命令输入操作
  • \q 退出当前sql命令行模式
  • \h 查看帮助
  • \cls 清屏（windows） ctrl+l(linux)
  • ctrl + c 终止

mysql增删改查

  1. 操作【数据库】（文件夹）

    • 增

      #创建数据库
create database db0616 charset utf8;

    • 删

      #删除数据库db0616
mysql> drop database db0616;
Query OK, 0 rows affected (0.05 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| abc                |
| db0616_2           |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

    • 改

      #alter 更改数据库的字符集
mysql> alter database db0616 charset gbk;
Query OK, 1 row affected (0.00 sec)

mysql> show create database db0616;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| db0616   | CREATE DATABASE `db0616` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)

    • 查

      #查看所有数据库
show databases;
#查看数据库的建表语句
mysql> show create database db0616;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| db0616   | CREATE DATABASE `db0616` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE DATABASE `db0616_2` /*!40100 DEFAULT CHARACTER SET utf8 */;
Query OK, 1 row affected (0.00 sec)

  2. 操作【数据表】（文件）

    选择数据库

    ? use 数据库名

    • 增

      #int 整型  char 字符
#字段名1 类型1，字段名2 类型2 ...
mysql> create table t1(id int ,name char);
Query OK, 0 rows affected (0.03 sec)

    • 删

      #删除表t1
drop table t1;

    • 改

      #modify 只能改变数据类型
mysql> alter table t1 modify name char(5);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| name  | char(5) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)

#change 改字段名 + 数据类型一起改变
mysql> alter table t1 change name NAME char(4);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| NAME  | char(4) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table t1 change NAME name char(5);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| name  | char(5) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

#add 添加字段
mysql> alter table t1 add age int;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| name  | char(5) | YES  |     | NULL    |       |
| age   | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

#drop 删除字段 column列（字段，关键字）
mysql> alter table t1 drop column age;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| name  | char(5) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

# rename 更改表名
mysql> alter table t1 rename t1_1;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+------------------+
| Tables_in_db0616 |
+------------------+
| t1_1             |
+------------------+
1 row in set (0.00 sec)

    • 查

      #查询所有数据表
mysql> show tables;
+------------------+
| Tables_in_db0616 |
+------------------+
| t1               |
+------------------+
1 row in set (0.00 sec)

#查看表的建表语句
mysql> show create table t1;
+-------+---------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                        |
+-------+---------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `name` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

#查看表结构
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| name  | char(1) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)


mysql> show create table t1\G;(加上\G垂直显示，默认是横向显示)
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `name` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.7.34, for Win64 (x86_64)

Connection id:          15
Current database:       db0616
Current user:           root@localhost
SSL:                    Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
Using delimiter:        ;
Server version:         5.7.34 MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3306
Uptime:                 16 hours 46 min 11 sec

Threads: 1  Questions: 74  Slow queries: 0  Opens: 127  Flush tables: 1  Open tables: 120  Queries per second avg: 0.001
--------------

mysql> \h

For information about MySQL products and services, visit:
   http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
   http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
   https://shop.mysql.com/

List of all MySQL commands:
Note that all text commands must be first on line and end with ‘;‘
?         (\?) Synonym for `help‘.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
notee     (\t) Don‘t write into outfile.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don‘t show warnings after every statement.
resetconnection(\x) Clean session context.

For server side help, type ‘help contents‘

  3. 操作记录（文件里面的数据）

    • mysql null 相当于python None是一样的 代表空的 什么也没有

    .frm文件 表结构

    .ibd文件 表数据

    • 增

      #一次插入一条数据
#insert into 表名(字段1，字段2) values(值1，值2)
mysql> insert into t1(id,name) values(1,‘xboy1‘);
Query OK, 1 row affected (0.01 sec)

#一次插入多条数据
mysql> insert into t1(id,name) values(2,‘xboy2‘),(3,‘xboy3‘),(4,‘xboy4‘);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
            
#不指定具体字段，默认把所有字段插一遍
mysql> insert into t1 value(5,‘xboy5‘);
Query OK, 1 row affected (0.00 sec)

#可以具体指定某个字段进行插入
mysql> insert into t1(name) values(‘xboy6‘);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | xboy1 |
|    2 | xboy2 |
|    3 | xboy3 |
|    4 | xboy4 |
|    5 | xboy5 |
| NULL | xboy6 |
+------+-------+
6 rows in set (0.00 sec)

    • 查

      # * 代表所有
mysql> select * from t1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | xboy1 |
+------+-------+
1 row in set (0.00 sec)

#把要搜索的字段写在select后面
select id,name from t1;

    • 改

      #update 表名 set 名字=值 where 条件
mysql> update t1 set name=‘王文‘ where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 王文   |
|    2 | xboy2  |
|    3 | xboy3  |
|    4 | xboy4  |
|    5 | xboy5  |
| NULL | xboy6  |
+------+--------+
6 rows in set (0.00 sec)

#如果不加条件，所有的数据都会改掉
mysql> update t1 set name=‘王文‘;
Query OK, 5 rows affected (0.00 sec)
Rows matched: 6  Changed: 5  Warnings: 0

mysql> select * from t1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 王文   |
|    2 | 王文   |
|    3 | 王文   |
|    4 | 王文   |
|    5 | 王文   |
| NULL | 王文   |
+------+--------+
6 rows in set (0.00 sec)

    • 删

      #删除id为2的这条数据
mysql> delete from t1 where id = 2;
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 王文   |
|    3 | 王文   |
|    4 | 王文   |
|    5 | 王文   |
| NULL | 王文   |
+------+--------+
5 rows in set (0.00 sec)

#不加条件删除所有数据
mysql> delete from t1 ;
Query OK, 5 rows affected (0.00 sec)

mysql> select * from t1;
Empty set (0.00 sec)

#重置数据表（重置id）
truncate table t1;

常用数据类型

  • 整型

    • tinyint 1个字节（8位） 小整型值
      • 有符号（-128 ~ 127）
        • 第一位代表符号
          • 0代表正数 0 111 1111 +127
          • 1代表负数 1 111 111 -128
      • 无符号（unsigned）(0~255)
    • int 4个字节 大型整数
      • 有符号（-21亿~21亿左右）
      • 无符号（0~42亿左右）
    #创建有符号的字段
mysql> create table t1(id int,sex tinyint);
Query OK, 0 rows affected (0.03 sec)
#insert into t1 values(250000000,127)

#创建无符号的字段
create table t1(id unsigned int,sex unsigned tinyint);

  • 浮点型

    • float(255,30) 单精度
    • double(255,30) 双精度
    • decimal(65,30) 金钱类型，使用字符串的形式保存小数
    #存在四舍五入
create table t2(f1 float(5,2),f2 double(5,2), f3 decimal(5,2));
insert into t2 values(1.666666666666666,1.666666666666666,1.666666666666666);

mysql> select * from t2;
+------+------+------+
| f1   | f2   | f3   |
+------+------+------+
| 1.67 | 1.67 | 1.67 |
+------+------+------+
1 row in set (0.00 sec)

###float 小数位默认保留5位，double小数位默认保留16位，decimal默认保留整数位，存在四舍五入
create table t3(f1 float,f2 double, f3 decimal);
insert into t3 values(1.666666666666666,1.666666666666666,1.666666666666666);

mysql> select * from t3;
+---------+-------------------+------+
| f1      | f2                | f3   |
+---------+-------------------+------+
| 1.66667 | 1.666666666666666 |    2 |
+---------+-------------------+------+
1 row in set (0.00 sec)

#float(5,2)  5=> 代表总长度（整数+小数） 2=>小数长度，保留2位
create table t4(f1 float(5,2));
insert into t4 values(12.3456);

mysql> select * from t4;
+-------+
| f1    |
+-------+
| 12.35 |
+-------+
1 row in set (0.00 sec)

  • 字符串

    • char(字符长度) 字符长度 255个
      • char(11) 定长：固定开辟11个字符长度的空间
      • 从开辟空间的速度上说：char更快
      • 应用场景（手机号、身份证）
    • varchar(字符串) 字符长度21845个
      • varchar(11) 变长：最多创建字符长度为11的空间
      • 从开辟空间的速度上说：相较于char稍慢
      • 应用场景（小广告，评论）
    • text
    • 文本类型，针对于文章，小说
    mysql> create table t7(c char(11), v varchar(11),t text);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t7 values(‘11111‘,‘asdfweafd‘,‘倚天屠龙宝刀，掉率0.0000001‘);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t7;
+-------+-----------+--------------------------------------+
| c     | v         | t                                    |
+-------+-----------+--------------------------------------+
| 11111 | asdfweafd | 倚天屠龙宝刀，掉率0.0000001          |
+-------+-----------+--------------------------------------+
1 row in set (0.00 sec)

    • 枚举 和 集合

      • enum 枚举：从列出来的数据当中选一个（性别）
      • set 集合：从列出来的数据中选多个（自动去重）
      create table t8(
id int, 
name char(10), 
money float(6,2),
sex enum("man","woman"),
hobby set("beat_doudou","smoke","drink","tang_head")
);

mysql> desc t8;
+-------+------------------------------------------------+------+-----+---------+-------+
| Field | Type                                           | Null | Key | Default | Extra |
+-------+------------------------------------------------+------+-----+---------+-------+
| id    | int(11)                                        | YES  |     | NULL    |       |
| name  | char(10)                                       | YES  |     | NULL    |       |
| money | float(6,2)                                     | YES  |     | NULL    |       |
| sex   | enum(‘man‘,‘woman‘)                            | YES  |     | NULL    |       |
| hobby | set(‘beat_doudou‘,‘smoke‘,‘drink‘,‘tang_head‘) | YES  |     | NULL    |       |
+-------+------------------------------------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> desc t8\G;
*************************** 1. row ***************************
  Field: id
   Type: int(11)
   Null: YES
    Key:
Default: NULL
  Extra:
*************************** 2. row ***************************
  Field: name
   Type: char(10)
   Null: YES
    Key:
Default: NULL
  Extra:
*************************** 3. row ***************************
  Field: money
   Type: float(6,2)
   Null: YES
    Key:
Default: NULL
  Extra:
*************************** 4. row ***************************
  Field: sex
   Type: enum(‘man‘,‘woman‘)
   Null: YES
    Key:
Default: NULL
  Extra:
*************************** 5. row ***************************
  Field: hobby
   Type: set(‘beat_doudou‘,‘smoke‘,‘drink‘,‘tang_head‘)
   Null: YES
    Key:
Default: NULL
  Extra:
5 rows in set (0.00 sec)

ERROR:
No query specified

#插入，正常写法
insert into t8(id,name,money,sex,hobby) values(1,‘张三‘,‘9.6666‘,‘woman‘,‘smoke,tang_head‘);

mysql> select * from t8;
+------+--------+-------+-------+-----------------+
| id   | name   | money | sex   | hobby           |
+------+--------+-------+-------+-----------------+
|    1 | 张三   |  9.67 | woman | smoke,tang_head |
+------+--------+-------+-------+-----------------+
1 row in set (0.00 sec)

#自动去重
insert into t8(id,name,money,sex,hobby) values(1,‘张三‘,‘9.6666‘,‘woman‘,‘smoke,tang_head,tang_head,tang_head,tang_head,tang_head‘);

mysql> select * from t8;
+------+--------+-------+-------+-----------------+
| id   | name   | money | sex   | hobby           |
+------+--------+-------+-------+-----------------+
|    1 | 张三   |  9.67 | woman | smoke,tang_head |
|    1 | 张三   |  9.67 | woman | smoke,tang_head |
+------+--------+-------+-------+-----------------+
2 rows in set (0.00 sec)

  • mysql常用函数

    • select user()

    • select concat(参数1，参数2，参数3,...)把所有的参数都拼接在一起

      mysql> select concat(c,v,t) from t7;
+----------------------------------------------------+
| concat(c,v,t)                                      |
+----------------------------------------------------+
| 11111asdfweafd倚天屠龙宝刀，掉率0.0000001          |
+----------------------------------------------------+
1 row in set (0.00 sec)
###
mysql> select concat(c,":",v,":",t) from t7;
+------------------------------------------------------+
| concat(c,":",v,":",t)                                |
+------------------------------------------------------+
| 11111:asdfweafd:倚天屠龙宝刀，掉率0.0000001          |
+------------------------------------------------------+
1 row in set (0.00 sec)

mysql 安装 & 基本操作 & 数据类型

上一篇：SQLite安装


下一篇：数据库审计audit