MySQL学习笔记

MySQL的安装

（MySQL官网下载地址：http://dev.mysql.com/downloads/mysql/）

PS：我的安装包的解压路径是 C:\mysql\mysql-8.0.13-winx64 ，文中涉及路径问题，希望自行改为自己的解压路径。

1、将安装包下载并解压指定位置后，在解压后的目录中插入（win系统）一个my.ini文件：（文件内容如下）

[mysql]
default-character-set=utf8

[mysqld]
port = 3306
basedir=C:\mysql\mysql-8.0.13-winx64　　#输入你自己的（解压）安装路径
datadir=C:\mysql\mysql-8.0.13-winx64\data　　#输入你自己的（解压）安装路径+ \data
character-set-server=utf8
default-storage-engine=INNODB

2、文件创建后，进入设置环境变量，将自己的安装路径添加至path中（不了解的可以百度环境变量设置方法）

3、以管理员身份权限允许cmd，利用cd命令进入自己的mysql路径下的 \bin 目录下（我操作的cd命令 ：cd C:\mysql\mysql-8.0.13-winx64\bin）

4、进入目录后输入MySQL的初始化命令：mysqld --initialize (这类初始化命令会默认空密码，所以在需要输入密码时按回车确认即可，不需要输入）

5、初始化完成后，输入 mysqld --install 命令查看是否安装成功。

6、完成上述内容后，即可关闭当前的cmd窗口，再次以管理员身份进入cmd；输入：net start mysql  启动mysql服务。

　　启动出现失败的原因解决：

　　　　1：查看自己的环境变量是否设置，如未设置，请利用 cd命令 进入 mysql\bin 目录执行上述net start mysql命令

　　　　2：查看自己的mysql是否安装成功，上述第五条。

　　　　3：确保使用管理员身份运行cmd，否则无法执行 启动mysql的命令。

---

 

MySQL的打开

1、进入cmd，输入 mysql -h 127.0.0.1 -u root -p（示例如下）

解答： 上述的mysql命令构成元素如下：

　　　　mysql -h [(mysql服务端)主机地址] -u [用户名] -p 

通俗理解为：以***用户（***密码）身份启动mysql客户端（mysql）连接mysql服务器（的ip地址） 

ps：本次示例中的mysql用户设置了密码，如果没有设置密码的用户可以直接回车，不需要输入任何内容即可（密码设置方法如下）：

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '密码';

 再次进入则用你输入的密码。

---

 

MySQL的管理：

用户管理：

创建用户：

create user '用户名'@'主机名/IP' indentified by '密码'；

 删除用户：

drop user '用户名'@'IP'；

 修改用户：

rename user '用户名'@'IP': to '新用户名'@'IP';

修改密码：

set password for '用户名'@'IP' = password('新密码')；

 查看权限：

show grants for '用户名'@'IP'

 用户授权（仅root账号可以操作）：

grant 权限 on 授权数据库.表 to '用户名'@'IP';

all privileges  除grant外的所有权限
            select          仅查权限
            select,insert   查和插入权限
            ...
            usage                   无访问权限
            alter                   使用alter table
            alter routine           使用alter procedure和drop procedure
            create                  使用create table
            create routine          使用create procedure
            create temporary tables 使用create temporary tables
            create user             使用create user、drop user、rename user和revoke  all privileges
            create view             使用create view
            delete                  使用delete
            drop                    使用drop table
            execute                 使用call和存储过程
            file                    使用select into outfile 和 load data infile
            grant option            使用grant 和 revoke
            index                   使用index
            insert                  使用insert
            lock tables             使用lock table
            process                 使用show full processlist
            select                  使用select
            show databases          使用show databases
            show view               使用show view
            update                  使用update
            reload                  使用flush
            shutdown                使用mysql-admin shutdown(关闭MySQL)
            super                   􏱂􏰈使用change master、kill、logs、purge、master和set global。还允许mysql-admin 􏵘􏲊􏲋 调试登陆
            replication client      服务器位置的访问
            replication slave       由复制从属使用

 

数据库名.*           数据库中的所有
 数据库名.表          指定数据库中的某张表
 数据库名.存储过程    指定数据库中的存储过程
 *.*                 所有数据库

用户名@IP地址         用户只能在改IP下才能访问
用户名@192.168.1.%    用户只能在改IP段下才能访问(通配符%表示任意) 
用户名@%              用户可以再任意IP下访问(默认IP地址为%)

 

---

 

MySQL的使用（ps：sql语句命令均以分号结束）

数据库的查看、新建、删除、打开：

查看所有数据库（目录）：　　　　show databases;
　　　　　　新建数据库：　　　　create database 数据库名；
　　　　　　删除数据库：　　　　drop database 数据库名；
(进入目录下)打开数据库：　　　　use 数据库名；
    查看数据表的列信息:　　　　DESCRIBE  数据表;(DESCRIBE 数据表 列名)

 

 ps：mysql中的数据库就等同于win中的文件夹。同理：文件夹中的单一文件就等同于数据库中的——表；

 

数据表的查看、新建、删除、打开：（数据表的操作需要进入某一数据库目录下操作）

查看当前数据库下的所有（目录）表：　　show tables；
　　　　查看指定数据表的创建数据：　　show create 表名 \G;
　　　　　　　　　　　新建数据表:
　　　　　　　　　　　　　　　　　　　　create table 表名(
　　　　　　　　　　　　　　　　　　　　　　　　列名 类型 是否为空，
　　　　　　　　　　　　　　　　　　　　　　　　列名 类型 是否为空
　　　　　　　　　　　　　　　　　　　　)engine=innodb default charset=utf8;
　　`　　　　　　　　　删除数据表：　　drop table 表名；

 数据表的重命名：

　　RENAME table 原表名 to 重命名 ;

 数据表的副本创建：

　　create table 副本名 link 引用表名；

 

 

数据表的创建：

创建数据表SQL命令格式：　　
　　　　　　　　create table 表名(
　　　　　　　　　　列名 类型 是否为空，
　　　　　　　　　　列名 类型 是否为空
　　　　　　　　)engine=innodb default charset=utf8;

 命令格式解答：

create table 表名    创建表单元（文件）
列名  标注表中的基本单元结构 列的名字（同时也是重要的索引）
类型  分很多种结构类型（下文详说）
是否为空  not null / null 设定列单元中是否可为空
engine=innodb  指定表的引擎为innodb
default charset=utf8  设定表的编码格式（支持中文）
ps：如果需要支持中文格式，再创建数据库时同样需要添加 default charset=utf8  例如：
　　　　create database 数据库名 create database 数据库名;

 ps：MySQL的数据类型可查看官方文档或菜鸟教程

 

自增：

如果为某列设置自增属性列，无需插入数据，每当表中的数据行有插入时，会自动增值：（自增列必须是可索引的主键）

create table tab1(
    nid int not null auto_increment primary key,
    num int null
)engine=innodb default charset=utf8;
或
create table tab2(
　　nid int nou null auto_increment,
　　num int null
)engine=innodb default charset=utf8;

 ps：auto_increment：表示自增　　primary key：表示约束（不重复且不为空，起到加速查找的作用）

 

自增可自定义：

SQL中的自增是可以设置初始步和步长的：
步长的设置分两种情况：1、表中(会话)     2、全局

1、表中：
    起始值：set session auto_increment_offset=起始值
    步长值：set session auto_incrment_increment=步长
2、全局：
    起始值：set global auto_increment_offset=起始值
    步长值：set global auto_increment_increment=步长

查看表中的变量：
    show session variables like 'auto_inc%'
查看全局的变量：
    show global variables like 'auto_inc%'

 

主键：（唯一标识）

一种特殊的索引（唯一），不允许Null，主键使用单个列或多列，它的值或组合必须是唯一的；

create table tb3(
    nid int not null auto_increment primary key,
    num int null
)engine=innodb default charset=utf8;
或
create table tb4(
    nid int not null,
    num int null,
    primary key(nid,num)
)engine=innodb default charset=utf8;

 ps：primary key：表示约束（不重复且不为空，起到加速查找的作用）

 

外键：（节省空间，约束数据的统一性）

一种特殊的索引，所定义的列只能是指定的表的内容内容索引；

create table color(
    nid int not null primary key,
    name char(10) not null
)engine=innodb default charset=utf8;
或
create table fruit(
    nid int not null primary key,
    sum char(10) null,
    color_id int not null,
    constraint fk_color foreign key (color_id) references color(nid)
)engine=innodb default charset=utf8;

 上面的代码示例中， 表 fruit 引用了外键，将color_id列的索引指向了表 color中的nid列

外键索引的格式：
    constraint [外键名称] foreign key (外键引入的列名) references 引用的表名(列名)

 唯一约束：（约束列内容不重复）

unique 约束名称 （约束列名）

 

数据表的操作：

修改表：

添加列：alter table 表名 add 列名 类型;
删除列：alter table 表名 drop column 列名;
修改列：alter table 表名 modify column 列名 类型;
          alter table 表名 change 原类名 新列名 类型；

添加主键：alter table 表名 add primary key（列名）;
删除主键：alter table 表名 drop primary key;
            alter table 表名 modify 列名 int,drop primary key;

添加外键：alter table 从表1 add constraint 外键名
             foreign key 从表（外键字段） references 主表（主键字段）
删除外键：alter table 表名 drop foreign key 外键名

 

修改默认值：alter table 表名 alter 列名 set default 1000;
删除默认值：alter table 表名 alter 列名 drop default;

 

 

表（数据）内容操作：

一、增

insert into 表名 (列名，列名...) values (值，值...);
insert into 表名 (列名，列名...) values (值，值...),(值，值...);
insert into 表名 (列名，列名...) select (列名，列名...) from 表名    【将另一个表的内容整列复制】

 

二、删

delete from 表名
delete from 表名 where 列名和判断条件 

 

三、改

update 表名 set 列名 = 新列名    【修改列名】
update 表名 set 列名 = 新列名 where 位置条件   【位置条件为合法的约束格式】

 

四、查

select * from 表名
select * from 表名 where 约束条件

 

五、其它

a、条件
    select * from 表 where id > 1 and name != 'alex' and num = 12;
 
    select * from 表 where id between 5 and 16;
 
    select * from 表 where id in (11,22,33)
    select * from 表 where id not in (11,22,33)
    select * from 表 where id in (select nid from 表)
 
b、通配符
    select * from 表 where name like 'ale%' - ale开头的所有（多个字符串）
    select * from 表 where name like 'ale_' - ale开头的所有（一个字符）
 
c、限制（分页）
    select * from 表 limit 5;            - 前5行
    select * from 表 limit 4,5;          - 从第4行开始的5行
    select * from 表 limit 5 offset 4    - 从第4行开始的5行
 
d、排序
    select * from 表 order by 列 asc - 根据 “列” 从小到大排列
    select * from 表 order by 列 desc - 根据 “列” 从大到小排列
    select * from 表 order by 列1 desc,列2 asc - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序
 
e、分组
    select num from 表 group by num
    select num,nid from 表 group by num,nid
    select num,nid from 表  where nid > 10 group by num,nid order nid desc
    聚合：
    select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid
  count(*)==计次,sum(score)==求和,max(score)==最大,min(score)==最小
    select num from 表 group by num having max(id) > 10
 
    特别的：group by 必须在where之后，order by之前；聚合之后二次筛选必须用 having。
 
f、连表
    无对应关系则不显示
    select A.num, A.name, B.name from A,B Where A.nid = B.nid
打开 （显示）表的列 *号表示所有列 from 表名，表名  where 表.列 = 表.列（对应的关系外键）
 
    无对应关系则不显示
    select A.num, A.name, B.name from A inner join B on A.nid = B.nid
 
    A表所有显示（left的作用），如果B中无对应关系，则值为null
    select A.num, A.name, B.name from A left join B on A.nid = B.nid
 
    B表所有显示（right的作用），如果B中无对应关系，则值为null
    select A.num, A.name, B.name from A right join B on A.nid = B.nid
 
g、组合
    组合，自动处理重合
    select nickname
    from A
    union
    select name
    from B
 
    组合，不处理重合
    select nickname
    from A
    union all
    select name
    from B

 

 

 

——未完待续——