MySQL基础笔记整理

MySQL安装和启动

Windows下安装和启动服务

下载地址：https://dev.mysql.com/downloads/mysql/5.7.html#downloads

安装

1 在MySQL官网下载zip文件，

2 在任意位置保存解压。这里在E盘创建MySQL目录

E:MySQL

3 E:Monkeymysql-5.7.16-winx64目录创建data目录。

4 在命令行运行E:MySQLmysql-5.7.16-winx64mysql-5.7.16-winx64inmysqld --initialize-insecure进行mysql服务端初始化，默认的创建了root用户 密码为空

配置和启动

1 启动服务端：E:MySQLmysql-5.7.16-winx64mysql-5.7.16-winx64inmysqld

2 启动客户端：E:MySQLmysql-5.7.16-winx64mysql-5.7.16-winx64inmysql -uroot -p # 密码为空

3 执行show databases;

结果如下：

4 添加环境变量，把E:MySQLmysql-5.7.16-winx64mysql-5.7.16-winx64in添加到Windows的环境变量（添加环境变量：https://jingyan.baidu.com/article/47a29f24610740c0142399ea.html）

5 Windows服务：

注册：

E:MySQLmysql-5.7.16-winx64mysql-5.7.16-winx64inmysqld --install
注册之后可以用 "net start MySQL" 启动服务 "net stop MySQL"停止服务

移除：

E:MySQLmysql-5.7.16-winx64mysql-5.7.16-winx64inmysqld --remove

Linux 下安装和启动服务

yum安装

yum list installed |grep mysql  

//若Linux自带mysql，将其卸载
yum -y remove mysql-libs.x86_64

在官网下载yum包 url：https://dev.mysql.com/downloads/repo/yum/
yum localinstall mysql57-community-release-el6-11.noarch.rpm

yum repolist enabled |grep "mysql.*-community.*"  # 看是否成功添加yum仓库

yum install mysql-community-server    # 安装

service mysqld start    # 启动

Starting mysqld:[ OK ]  # 表示安装成功

源码安装

安装编译源码所需的包

 yum -y install make gcc-c++ cmake bison-devel  ncurses-devel

之后下载并解包文件

wget http://cdn.mysql.com/Downloads/MySQL-5.6/mysql-5.6.14.tar.gz
tar xvf mysql-5.6.14.tar.gz

编译安装

cd mysql-5.6.14
cmake .

-DCMAKE_INSTALL_PREFIX=/usr/local/mysql 
-DMYSQL_DATADIR=/usr/local/mysql/data 
-DSYSCONFDIR=/etc 
-DWITH_MYISAM_STORAGE_ENGINE=1 
-DWITH_INNOBASE_STORAGE_ENGINE=1 
-DWITH_MEMORY_STORAGE_ENGINE=1 
-DWITH_READLINE=1 
-DMYSQL_UNIX_ADDR=/var/lib/mysql/mysql.sock 
-DMYSQL_TCP_PORT=3306 
-DENABLED_LOCAL_INFILE=1 
-DWITH_PARTITION_STORAGE_ENGINE=1 
-DEXTRA_CHARSETS=all 
-DDEFAULT_CHARSET=utf8 
-DDEFAULT_COLLATION=utf8_general_ci

make && make install

MySQL的初始化

cd /usr/local/mysql
scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql

注意：在/etc目录下会存在一个my.cnf，需要将此文件更名为其他的名字，如：/etc/my.cnf.bak，否则，该文件会干扰源码安装的MySQL的正确配置，造成无法启动。

注册为系统服务：

cd /usr/local/mysql/support-files

cp mysql.server /etc/rc.d/init.d/mysql   # 注册服务

cp my-default.cnf /etc/my.cnf    # 使用默认配置文件

服务启动：

service mysql start 

　　

MySQL之SQL基础语句

数据库相关

MySQL启动后

连接数据库：

mysql -uroot -ppassword    # u 后面跟用户名 p 后面是密码

查看数据库：

show databases;

使用数据库：

use databases;

查看数据库中所有表：

show tables;　

创建数据库：(create database + 数据库名)

create database test_db_name

删除数据库：(drop database + 数据库名)

drop database dbname;

用户相关

创建用户：

create user "monkey’@"192.168.2.2’ identified by "passwod’;
# 创建名为 ‘monkey’ 密码为’password’ 登陆IP只能为：192.168.2.2 的用户

create user "monkey’@"192.168.2.%" identified by "password’;
# 创建名为 ‘monkey’ 密码为’password’ 登陆IP为：192.168.2 网段的用户

create user "monkey’@"%" identified by "password’;
# 创建名为 ‘monkey’ 密码为’password’ 登陆IP不限的用户

对用户进行授权：

grant select,insert,update  on test_db_name.table1 to "monkey’@"%";
# 对 monkey 用户 开放数据库：test_db_name 下表：table1 的查 插 更新的权限

grant all privileges  on test_db_name.table1 to "monkey’@"%";
# 对 monkey 用户 开放数据库：test_db_name 下表：table1 的所有权限

revoke all privileges  on test_db_name.table1 from "monkey’@"%";			  
# 取消权限

关于用户：

在mysql数据库下的user表，这张表存放着用户的信息

use mysql;
select user,Host from user;

　　

对用户的操作也可以修改这张表，但是不建议这样做！

数据表相关

数据的字段类型

数值型：

字符串类型

日和期时间类型

数据表的创建和增加

创建表：

create table tablename(
				id int auto_increment primary key,
				name varchar(32),
				age int
			)engine=innodb default charset=utf8;

其中 语法：create table +tablename(列名1 类型 其他,

　　　　　　　　　　　　　　　　　列名2 类型 其他　) engine = innodb default charset=utf-8

engine 指明数据库建表引擎为 innodb （支持事务操作）。

myisam 不支持事务。

default charset=utf-8 指明数据表的编码字符集 为 utf-8。

在表中添加数据（三种）

insert into tablename(name,age) values("monkey’,18);
			
insert into tablename(name,age) values("JIAJIA’,18),("xiaoliu’,18);
# 可以跟多条记录，一元组的形式添加
			
insert into tablename(name,age) select name,age from tablename2;
# 从别的表查找数据 写入 

删除表中数据不删除表（三种）：

delete from tablename;    # 删除数据，但是自增计数不会被删除 单纯的清掉数据
truncate table tablename;    # 清空表，相当于新的表 自增计数 0
delete from tb1 where id > 10    # 跟条件

删除表:

drop table tablename;    # 删除表 

表中插入数据：

insert into t1(id,name) values(100,"monkey’);

修改数据

update tablename set age=1024;
update tablename set age=2048 where age=18;

查看数据

select * from tablename; # 查看表的所有行列
select  name,age from tablename;    # 查看name和age 列

外键

create table tablename1(id int auto_increment primary key ,
name char(32));

create table tablename2(id int auto_increment primary key ,
name char(32),
friend_id int,
constraint fk_t1_t2 foreign key (friend_id) references tablename1(id));

　　外键的创建：定义表的列时预料外键字段，之后 constraint fk_t1_t2 foreign key (friend_id) references tablename1(id)); constaint ... foreign key 预留字段名 references 被关联表名（字段名）

表的补充

desc t;    # 查看t 的表结构
		
show create table t;    # 查看创建t的创建语句
		
show create table t G;    # 横向查看
		
alter table t10 AUTO_INCREMENT=10000;    # 设置自增的起始值
			

MySQL的自增问题

自增的起始值：

alter table t10 AUTO_INCREMENT=10000;    # 设置自增的起始值

MySQL: 自增步长
基于会话级别：

show session variables like "auto_inc%";	查看全局变量
set session auto_increment_increment=2; 设置会话步长
# set session auto_increment_offset=10;

基于全局级别：

show global variables like "auto_inc%";	查看全局变量
set global auto_increment_increment=2; 设置会话步长
# set global auto_increment_offset=10;

SqlServer：自增步长：

基于表级别：

CREATE TABLE `table1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
) ENGINE=InnoDB AUTO_INCREMENT=3, 步长=2 DEFAULT CHARSET=utf8
# 自增起始值为 3 步长为 2

CREATE TABLE `table2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
) ENGINE=InnoDB AUTO_INCREMENT=9, 步长=3 DEFAULT CHARSET=utf8
# 自增起始值为 9 步长为 3

约束与索引

三种约束关系

一对多约束

外键约束本身就实现了一对多的约束

create table tablename1(id int auto_increment primary key ,
name char(32));

create table tablename2(id int auto_increment primary key ,
name char(32),
friend_id int,
constraint fk_t1_t2 foreign key (friend_id) references tablename1(id));

一对一约束

实现一对一约束 外键 加 唯一索引

create table class(id int auto_increment primary key ,
name char(32));

create table teacher(id int auto_increment primary key ,
name char(32),
class_id int,
unique (class_id),
constraint fk_class_teacher foreign key (class_id) references class(id));

多对多约束

通过一张关系表和外键约束建立多对多的映射关系

create table class(id int auto_increment primary key ,
name char(32));

create table student(id int auto_increment primary key ,
name char(32),
class_id int);

create table student_class_relation(id int auto_increment primary key ,
student_id int,
class_id int,
constraint fk_relation_class foreign key (class_id)references class(id),
constraint fk_relation_student foreign key (student_id)references student(id));

主键外键唯一约束

所有的索引都有一个共有的作用：加速查找

主键：

---

主键约束即在表中定义一个主键来唯一确定表中每一行数据的标识符。主键可以是表中的某一列或者多列的组合，其中由多列组合的主键称为复合主键。主键应该遵守下面的规则：

• • • 每个表只能定义一个主键。
    • 主键值必须唯一标识表中的每一行，且不能为 NULL，即表中不可能存在两行数据有相同的主键值。这是唯一性原则。
    • 一个列名只能在复合主键列表中出现一次。
    • 复合主键不能包含不必要的多余列。当把复合主键的某一列删除后，如果剩下的列构成的主键仍然满足唯一性原则，那么这个复合主键是不正确的。这是最小化原则。

---

主键声明的两种方式：

直接在定义字段的时候声明为主键，但是这样只能声明单一字段为主键无法声明 复合主键。　

create table class(id int auto_increment primary key , 
name char(32));

在字段定义全部结束之后声明主键，这样既可以声明单一的主键，也可以声明为 复合主键。

create table person(name char(32) not null,
part char(32) not null ,
company char(32) not null ,
primary key (part,company,name));

---

联合主键

当建立多对多关系的两个表，通过关系表来唯一确定关系的时候，关系表的主键称为 联合主键。

例如： 多对多示例中的

student_class_relation 表中的 id int auto_increment primary key 字段。

创建表后修改主键约束

create table user(id int, name char(32), sex char(4));
# 创建 user表 包含 id name sex 三个字段 没有主键

# 添加主键
alter table user add primary key(id);     # 将id字段设置为主键

# 修改主键
alter table user drop primary key,add primary key(name);    # 删除原主键 将name设置为新的主键

# 将主键改为 复合主键
alter table username drop primary key,add primary key(id,name);  # 主键改为id 和 name的联合主键

外键：

---

主表和从表：

主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表。

从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所在的表就是从表。

• • • 父表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则父表与子表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。
    • 必须为父表定义主键。
    • 主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。
    • 在父表的表名后面指定列名或列名的组合。这个列或列的组合必须是父表的主键或候选键。
    • 外键中列的数目必须和父表的主键中列的数目相同。
    • 外键中列的数据类型必须和父表主键中对应列的数据类型相同。

---

外键的声明：

　　　　定义时声明本篇上述 一对多对应关系

创建表后修改外键：

alter table <数据表名> add constraint <索引名>
foreign key(<列名>) refrences <主表名> (<列名>);

alter table user add part_id int;    # 为user表添加 part_id列 作为外键
alter table user add constraint fk_part_user foreign key(part_id) references part(id);     # 为user表添加外键约束

唯一约束

---

• • • 唯一约束也叫唯一索引，MySQL唯一约束（Unique Key）要求该列唯一，允许为空，但只能出现一个空值。唯一约束可以确保一列或者几列不出现重复值。
    • 唯一约束允许为空，但是不允许有重复，主键约束不允许为空，不允许重复。
    • 唯一约束可以对多个字段设置，联合唯一索引。
    • 注意 唯一索引中的空值允许重复，即可以有多个空值记录存在。

---

声明唯一约束的两种方式：

在定义字段时声明，这样的方式像声明主键一样，同样的无法声明联合唯一。

create table unique_table0(name char(32) unique ,
company char(32);

在字段声明全部结束的时候单独的声明唯一索引，这样可以声明成多个字段的唯一索引 即联合唯一索引。

create table unique_table1(name char(32) ,
part char(32) ,
company char(32) ,
unique (part,company,name));

***创建表后修改唯一约束：*

**

*创建唯一约束：*

create table t1(id int, name char(32));

# way 1:  alter table "数据表名" add  constraint "索引名"  unique(‘要添加的字段名’)；
alter table t1 add  constraint unique_id_name unique(id,name);

# way 2:  create unique index 索引名 on 数据表名(字段名);
create unique index unique_id_name unique(id,name);

*删除唯一约束*

# way 1:  alter table 数据表名 drop index 删除的索引名；
alter table t1 drop index unique_id_name;

# way 2:  drop index 索引名 on 数据表名；
drop index unique_id_name on t1;

索引

索引的详细介绍戳这里url：

普通索引

　　普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件(WHERE column = …)或排序条件(ORDER BY column)中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列(如一个整数类型的数据列)来创建索引。

索引的通用方法

# 查看索引
show index from table_name;
# 其中 Key_name 字段为索引名

# 删除索引 
drop index index_name on table_name；

普通索引方法

–直接创建索引(length表示使用名称前1ength个字符)  
CREATE INDEX index_name ON table_name(column_name(length))  
–修改表结构的方式添加索引  
ALTER TABLE table_name ADD INDEX index_name ON (column_name)  
–创建表的时候同时创建索引  
CREATE TABLE table_name (  
id int(11) NOT NULL AUTO_INCREMENT ,  
title char(255) NOT NULL ,  
PRIMARY KEY (id),  
INDEX index_name (title)  
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;   
建立复合索引 。  
CREATE INDEX mytable_categoryid_userid ON mytable (category_id,user_id);  
  注意命名时的习惯了吗？使用“表名字段1名字段2名”的方式  

唯一索引

　　唯一索引就是唯一约束，与普通索引类似，除索引列的值必须唯一外跟普通索引无异。创建方法和普通索引类似。也就是说唯一约束的创建方式也可以由索引的创建方式创建。

　　唯一索引简化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率。

　　唯一索引是MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了。

主键索引

　　必须为主键字段创建一个索引，这个索引就是所谓的”主索引”。主索引与唯一索引的唯一区别是：前者在定义时使用的关键字是PRIMARY而不是UNIQUE。

外键索引

　　如果为某个外键字段定义了一个外键约束条件，MySQL就会定义一个内部索引来帮助自己以最有效率的方式去管理和使用外键约束条件。

全文索引

　　MySQL从3.23.23版开始支持全文索引和全文检索，fulltext索引仅可用于 MyISAM 表；他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或CREATE INDEX被添加。////对于较大的数据集，将你的资料输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把资料输入现有FULLTEXT索引的速度更为快。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。
文本字段上的普通索引只能加快对出现在字段内容最前面的字符串(也就是字段内容开头的字符)进行检索操作。如果字段里存放的是由几个、甚至是多个单词构成的较大段文字，普通索引就没什么作用了。这种检索往往以LIKE %word%的形式出现，这对MySQL来说很复杂，如果需要处理的数据量很大，响应时间就会很长。
　　这类场合正是全文索引(full-text index)可以大显身手的地方。在生成这种类型的索引时，MySQL将把在文本中出现的所有单词创建为一份清单，查询操作将根据这份清单去检索有关的数据记录。全文索引即可以随数据表一同创建，也可以等日后有必要时再使用下面这条命令添加：
　　ALTER TABLE table_name ADD FULLTEXT(column1, column2)
　　有了全文索引，就可以用SELECT查询命令去检索那些包含着一个或多个给定单词的数据记录了。下面是这类查询命令的基本语法：
　　SELECT * FROM table_name
　　WHERE MATCH(column1, column2) AGAINST(‘word1’, ‘word2’, ‘word3’)
　　上面这条命令将把column1和column2字段里有word1、word2和word3的数据记录全部查询出来。

–创建表的适合添加全文索引  
CREATE TABLE table_name (  
id int(11) NOT NULL AUTO_INCREMENT ,  
content text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,  
PRIMARY KEY (id),  
FULLTEXT (content)  
);  
–修改表结构添加全文索引  
ALTER TABLE table_name ADD FULLTEXT index_name(column_name)  
–直接创建索引  
CREATE FULLTEXT INDEX index_name ON table_name (column_name)  

单列、多列索引

　　多个单列索引与单个多列索引的查询效果不同，因为执行查询时，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。

组合(复合)索引

　　平时用的SQL查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL的效率，就要考虑建立组合索引。例如上表中针对title和time建立一个组合索引：ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10))。建立这样的组合索引，其实是相当于分别建立了下面两组组合索引：
–title,time
–title
为什么没有time这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这两列的查询都会用到该组合索引，如下面的几个SQL所示
–使用到上面的索引
SELECT * FROM article WHREE title=’测试’ AND time=1234567890;
SELECT * FROM article WHREE title=’测试’;
–不使用上面的索引
SELECT * FROM article WHREE time=1234567890;

SQL高级查找

条件查询

排序 order by

select * form table_name order by id desc    # 从大到小
select * form table_name order by id asc    # 从小到大

限制 limit

select * form table_name order by id asc limit 10    # 取查询结果的前10条

select * form table_name order by id asc limit 20,10    # 取查询结果从第20条开始 往后查10条

select * form table_name order by id asc limit 10 offset 20   # 取查询结果从第20条开始 往后查10条

模糊查寻 like

%表示任意长度的字符 _表示单字符

select * from table_name where name like "张%"    # 以张开头的

select * from table_name where name like "%张%"    # 包含张的

select * from table_name where name like "%张"    # 以张结尾的

select * from table_name where name like "张_"    # 以张开头的 两个字的  

select * from table_name where name like "_浩_%"    # 三个字的 并且中间是 浩 的 

日期查询

select * form table_name where date_key between "2019-10-10" and "2019-10-10";

MySQL比较运算符

分组聚合

聚合函数：

sum() 求和

avg() 求平均（期望）

max() 最大值

min() 最小值

conut() 计数（求有多少个记录）

对聚合函数的结果进行二次筛选的时候，条件 应该跟在having后 而不是 where 后面。

创建表：part 并插入数据：

create table part(id int,
name char(32));

创建表：person 并插入数据：

create table person(name char(32),
part_id int,
constraint fk_part_person foreign key(part_id) references part(id));

select part_id,count(part_id) from person group by(part_id);

内链接查询

select * from person,part where part.id = person.part_id;  # 有时候性能可能会不如下面 
select * from person inner join part on part.id = person.part_id;  # 性能可能会更好一些

注意 使用 where 子句定义连接条件比较简单明了，而 inner join 语法是 ANSI SQL 的标准规范，使用 inner join 连接语法能够确保不会忘记连接条件，而且 where 子句在某些时候会影响查询的性能。

外链接查询

select * from person left join part on part.id = person.part_id; 

select * from person right join part on part.id = person.part_id;  

注意 关于 left 和 right ：两张表 连表查询，left 就是left 左边的表呃记录要全部显示出来 right 就是 right 右边的表要全部现实出来

分组聚合查询

每个部门有多少个人：

select part.name,count(person.name) from person left join part on part.id = person.part_id group by(part.name);