2018年跨行学习mysql_MySQL学习笔记2018-02-07更新

前言：本人wechat：YWNlODAyMzU5MTEzMTQ=。

如果内容有错，请指出来。

win10下安装

https://dev.mysql.com/downloads/mysql/下载并解压mysql-5.7.21-winx64.zip

在 D:\mysql-5.7.21-winx64\目录中新建my.ini文件

[mysql]

# 设置mysql客户端默认字符集

default-character-set=utf8

[mysqld]

# 设置3306端口

port=3306

# 设置mysql的安装目录

basedir=D:\\software\mysql-5.7.21-winx64

# 设置mysql数据的存放目录

datadir=D:\\software\mysql-5.7.21-winx64\data

# 允许最大连接数

max_connections=200

# 服务端使用的字符集默认为8比特编码的latin1字符集

character-set-server=utf8

# 创建新表时将使用的默认存储引擎

default-storage-engine=INNODB

以管理员身份运行C:\Windows\system32\cmd.exe

切换到目录D:\mysql-5.7.21-winx64\bin

生成mysql服务

D:\mysql-5.7.21-winx64\bin>mysqld -install

Service successfully installed.

生成data目录D:\mysql-5.7.21-winx64\bin>mysqld --initialize-insecure --user=mysql

启动mysql服务

D:\mysql-5.7.21-winx64\bin>net start mysql

MySQL 服务正在启动 .

MySQL 服务已经启动成功。

登录数据库(默认密码为空)D:\mysql-5.7.21-winx64\bin>mysql -uroot

修改密码

mysql> show databases; # mysql语句后面必须加分号

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

+--------------------+

4 rows in set (0.00 sec)

mysql> use mysql;

Database changed

mysql> update user set authentication_string=password("toor") where User="root";

Query OK, 1 row affected, 1 warning (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 1

mysql>flush privileges; # 刷新权限

mysql>exit

Bye

关闭mysql服务

D:\mysql-5.7.21-winx64\bin>net stop mysql

MySQL 服务正在停止.

MySQL 服务已成功停止。

Linux下安装

基本语法

增

# 创建数据库

mysql> create database tests charset utf8;

# 创建数据表

mysql> create table teacher(

-> id int auto_increment,

-> name char(32) not null default 'zhangsan',

-> age int not null,

-> register_date date not null,

-> primary key (id));

# 插入数据

mysql> insert into student (name,age,register_date,class) values("lema",22,"2018-02-02","201601"),

->("michael",12,"2018-02-01","201601");

# 增加一个字段classroom

mysql> alter table student add classroom int;

删

# 删除数据内容

mysql> delete from student where id=2;

# 删除字段

mysql> alter table student drop age;

查

mysql> show databases; # 显示mysql中所有数据库名称

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

| test |

+--------------------+

mysql> use test; # 使用test数据库作为当前数据库

Database changed

mysql> show tables; # 显示当前数据库下所有表

mysql> select * from student; # 显示student表中所有字段的具体内容

mysql> select * from student\G; # 将查询结果按列打印

*************************** 1. row ***************************

stu_id: 1

name: lema

register_date: 2018-02-06

*************************** 2. row ***************************

stu_id: 2

name: alex

register_date: 2018-02-06

# 获取数据表结构

mysql> show columns from student;

mysql> desc student;

+---------------+----------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+---------------+----------+------+-----+---------+----------------+

| stu_id | int(11) | NO | PRI | NULL | auto_increment |

| name | char(32) | NO | | NULL | |

| register_date | date | NO | | NULL | |

+---------------+----------+------+-----+---------+----------------+

# 偏移量查询

mysql> select * from student limit 1;

+----+------+-----+---------------+-------+

| id | name | age | register_date | class |

+----+------+-----+---------------+-------+

| 1 | alex | 30 | 2018-02-07 | NULL |

+----+------+-----+---------------+-------+

mysql> select * from student limit 1 offset 2;

+----+---------+-----+---------------+--------+

| id | name | age | register_date | class |

+----+---------+-----+---------------+--------+

| 3 | michael | 12 | 2018-02-01 | 201601 |

+----+---------+-----+---------------+--------+

# 模糊查询

mysql> select * from student where register_date like "2018-02%";

select * from student where id>3 and age<20;

select * from student where id>3 or age<20;

改

# 修改字段的数据内容

mysql> update student set register_date="2017-03-01" where id=3;

# 修改字段的数据类型

mysql> alter table student modify id int not null;

# 修改字段的名称和数据类型

mysql> alter table student change id stu_id int;

排序

# 按照第3个字段排序

mysql> select * from student order by 3;

+----+---------+---------------+--------+------+

| id | name | register_date | class | age |

+----+---------+---------------+--------+------+

| 2 | lema | 2017-01-01 | 201602 | 12 |

| 3 | michael | 2017-03-01 | 201601 | 31 |

| 1 | alex | 2018-02-07 | 201602 | 31 |

| 4 | david | 2018-03-03 | 201703 | 34 |

| 5 | mm | 2018-09-02 | 201801 | 56 |

+----+---------+---------------+--------+------+

# 分组

mysql> select name,count(*) from student group by name;

mysql> select name,sum(age) from student group by name;

用户授权

用户权限管理主要有以下作用：

1.限制用户访问哪些库哪些表

2.限制用户对哪些表执行select,drop,alter,create等操作

3.限制用户登录的IP和域名

4.限制用户自己的权限是否可以授权给其它用户

新建用户

mysql> grant all privileges on *.* to 'lema'@'%' identified by 'toor' with grant option;

Query OK, 0 rows affected, 1 warning (0.52 sec)

mysql> flush privileges; # 刷新权限

Query OK, 0 rows affected (0.14 sec)

# all privileges：将所有权限授予用户，也可以指定具体权限，如select,create等

# *.*：应用到所有数据库所有表，数据库名.表名

# 'lema'@'%'：'用户名'@'IP或域名'，%表示任意主机

# identified by 'toor'：设置登录密码

# with grant option：允许用户将自己的权限授予其它用户

查看用户权限&删除权限

mysql> show grants for "root"@"localhost";

+---------------------------------------------------------------------+

| Grants for root@localhost |

+---------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |

| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |

+---------------------------------------------------------------------+

2 rows in set (0.00 sec)

mysql> revoke create on *.* from 'lema'@'localhost';

Query OK, 0 rows affected (0.00 sec)

查看所有用户&删除用户&用户重命名

mysql> select host,user from mysql.user;

+-----------+---------------+

| host | user |

+-----------+---------------+

| localhost | lema |

| localhost | mysql.session |

| localhost | mysql.sys |

| localhost | root |

+-----------+---------------+

4 rows in set (0.00 sec)

mysql> drop user 'lema'@'localhost';

Query OK, 0 rows affected (0.00 sec)

mysql> rename user 'lemachin'@'localhost' to 'lema'@'localhost';

Query OK, 0 rows affected (0.00 sec)

修改密码

# 更新mysql.user表

mysql> update mysql.user set authentication_string=password('toor') where user='lema';

# 用set password命令

mysql> set password for 'lema'@'localhost' =password('toor');

# mysqladmin位于bin目录下

D:\mysql-5.7.21-winx64\bin>mysqladmin -ulema -proot password toor