文章目录
安装MySQL
- 我的 MySQL 版本是 5.7 的
- 安装mysql命令
sudo apt-get install mysql-server
- 安装成功后可以通过下面的命令测试是否安装成功
sudo netstat -tap | grep mysql
- 成功如图:
- 如果忘记密码了,可以先跳过密码登录进去,然后重新设置
- 编辑mysql的配置文件/etc/mysql/mysql.conf.d/mysqld.cnf,在[mysqld]段下加入一行“skip-grant-tables”。
- 重启mysql服务:
service mysql restart
- 直接mysql进去
use mysql; 进入名为 mysql 数据库
- 执行
update mysql.user set authentication_string=password('111111') where user='root' and Host ='localhost';
把密码重置为11111,退出数据库管理 - 立即刷新权限:
flush privileges;
- 退出后,回到
sudo gedit /etc/mysql/mysql.conf.d/mysqld.cnf
,把刚才加入的那一行“skip-grant-tables”注释或删除掉 - 再次重启mysql服务
sudo service mysql restart
,使用新的密码登陆,修改成功
- 让mysql可以远程访问
- 首先编辑文件/etc/mysql/mysql.conf.d/mysqld.cnf:
- 将
bind-address = 0.0.0.0
注释掉 - 保存退出,然后进入mysql服务,执行授权命令:
- 允许所有用户远程访问 修改用户名和密码为你自己的
GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'%' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;
- 允许单个ip 修改用户名和密码为你自己的
GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'1.2.3.4' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;
- 让权限立即生效:flush privileges;
- 重启mysql服务service mysql restart(如果是比较老的版本比如5.0,用service mysqld restart)
重启这步也比较关键,之前改完没重启,结果怎么都不成功,一直显示报错ERROR 2003 (HY000): Can’t connect to MySQL server on ‘x.x.x.x’ (111),查了好多,方法就那四五种,最后重启了试试就成功了
- 允许所有用户远程访问 修改用户名和密码为你自己的
- 如果此时还是报出错误
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
- 方法1:通过
sudo su
后再执行mysql -r...
或者sudo mysql -u...
- 方法2:进入mysql中,先选择一个数据库(use mysql;),然后输入select user,plugin from user;如果root如下图:
- 错误原因是因为plugin root的字段是auth_socket,那我们改掉它为下面的mysql_native_password就行了,输入:
update user set authentication_string=password("xxx"),plugin='mysql_native_password' where user='root';
然后flush privileges;
- 方法1:通过
- 给某个用户授权:
grant all privileges on *.* to 'ubuntu'@'localhost' identified by '111111';
,将用户名(我的是 ubuntu)和密码(我的是 111111)改为自己的,授权这步也很重要,如果这里没授权过,会报错ERROR 1045 (28000): Access denied for user 'ubuntu'@'localhost' (using password: YES)
- 到此所有操作完成,可以在任何主机连接此mysql数据库服务器了
解决中文乱码,数据的中文问题
- 临时方法1:创建数据库或表时在结尾加上
default charset=utf8
- 临时方法2:先通过命令查询字符类型
show variables like '%char%';
- 如果如上图,进行更改
set global character_set_database=utf8;
set global character_set_server=utf8;
show variables like '%char%';
- 永久更改:更改
/etc/mysql/mysql.conf.d/mysqld.cnf
#更改或添加如下代码
[mysqld]
character-set-server=utf8
如果加上下面的,会出现 mysql 中无法输入中文(环境是 ubuntu18 + mysql 5.7)
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
可以通过命令 show create table table_name;
查看表的详细结构信息,包括存储引擎和字符集设置
可以看出这里的 charset 是 latin1,将他改为 utf8
alter table table_name default character set utf8;
数据库简介
- 数据库:DB
- 数据库管理系统:DBMS,数据库是通过 DBMS 创建和操作的
- MySQL 是最流行的关系型数据库管理系统,在WEB应用方面 MySQL 是最好的RDBMS(Relational Database Management System:关系数据库管理系统)应用软件之一
- 关系型数据库管理系统:采用关系模型来组织管理数据的数据库系统
- 特点:把数据保存在不同的表中,而不是将数据放在一个大仓库中,可以运行于多个系统上,并且支持多种编程语言,包括C、C++、Python、Java、Perl、PHP、Ruby等
- sql:结构化查询语言,用于和数据库通信
- 概念:按照一定的数据结构来存储和管理数据的仓库
- 计算机处理数据的分类:
- 关系型数据库:存储方式固定,安全
- 非关系型数据库(nosql(not only SQL)):存储方式比较灵活,存储数据的效率比较高,不太安全
- 数据库优点:实现数据持久化,使用完整的管理系统统一管理,便于查询
- 注释:主要用于图形用户化界面
- 单行:
#
或者--
- 多行:
/* 注释内容 */
- 单行:
- 字段名正常需要用着重符号标记:就是 ~ 这个键
+ 的作用
- MySQL 中的 + 只有一个功能:运算符
+ 两边都为数值型,做加法运算
+ 两边一个为字符型,则试图将字符型转换成数值型
- 转换成功(比如 ‘102’+8),则继续做加法:102+8=110
- 转换失败(‘xiaoge’+8),则将字符型数值转换成 0:0+8=8
+ 两边只要有 null,结果肯定是 null
结构
- 关系型数据库和非关系型数据库:
- MySQL的结构:
- MySQL 为关系型数据库,这种所谓的"关系型"可以理解为"表格"的概念, 一个关系型数据库由一个或数个表格组成, 如图所示的一个表格:
起别名
- 便于理解
- 出现字段重名可以用起别名方式解决
- as 或者 空格,别名如果有关键字、特殊符号等可以用单引号,防止报错
MySQL库级和表级操作
- SQL语句正规是大写,不区分大小写,表名和数据库名区分大小写,建议关键字用大写,表名、列名用小写
- 进入MySQL
- mysql -u用户名 -p密码
- mysql -u用户名 -p #回车再输密码,输的密码看不到
- 退出MySQL
- \q
- 进入用户后每条语句结尾是
;
- 创建用户
create user "wenwen"@"localhost" identified by "wendong";
#创建用户grant all on *.* to "wenwen"@"localhost";
#赋予用户所有的权限,相当于管理员
- 查询用户:
select user();
数据库操作
- 增:create,删:drop,查:select,show,进入:use
- 创建数据库:
create database xiaoge;
#已有的库再建会报错create database if not exists xiaoge;
#不确定是否有此库,不报错
- 删除数据库:
drop database meimei;
#慎用 - 查询当前所在库:
select database();
#information_schema,mysql,performance_schema,sys这四个是系统默认数据库,慎动! - 查询所有库:
show databases;
- 进入数据库:
use xiaoge;
字段
- MySQL字段支持多种类型,大致可以分为三类:数值,日期/时间,字符串(字符)类型
- 数值类型:
注:5.0.3 版本以前 varchar(字节),5.0.3 版本以后 varchar(字符)
数值类型:
时间日期类型:
- 数值类型:
字段操作
- 添加字段:
alter table myorder add product_phone varchar(11);
- 删除字段:
alter table myorder drop product_phone;
- 修改字段:
alter table myorder modify product_id int not null;
表操作
- 创建表:
create table student( id int,name varchar(64),age int,gender enum("boy","girl"));
#create table 表名(字段);enum用于选择 - 查看当前数据库所有的表:show tables;
- 查看表结构:desc student;
ield Type Null Key Default Extra id int(11) YES NULL name varchar(64) YES NULL age int(11) YES NULL gender enum(‘boy’,‘girl’) YES NULL - 删除表:drop table student; #慎用
表中数据的操作
- 插入数据:insert into
- 删除数据:delete
- 查询数据:select
- 修改数据:update
插入数据
- 指定字段插入:
insert into student(name,age) values("na",16);
- 全字段插入:
insert into student values(1,"xiaona",16,"girl");
- 多行插入:
insert into student values(2,"jingyi",19,"girl"),(3,"wenwen",25,"girl");
删除数据(慎用)
- 删除所有数据:
delete from student;
- 删除指定数据:
delect from student where gender="girl";
修改数据
- 修改一个字段的所有数据:
update student set gender="girl";
- 修改满足条件的数据:
update student set name="nana" where name="na";
- 修改多条数据:
update student set id=1,age=15 where name="na";
查询数据
- 查所有数据:
select * from student;
#全字段查询 - 指定字段查询:
select name,age from student;
- 带条件查询:
select * from student where gender="girl";
筛选条件
- 比较运算符
- 逻辑运算符
- 其他操作
比较运算符
- 等于:=(不是==),
select * from student where gender="boy";
- 不等于:!=或<>,大于:>,小于:<,大于等于:>=,小于等于:<=
- 空:is null,
select * from student where gender is null;
- 不空:is not null
逻辑运算符
- 与:and,
select * from student where gender="girl" and age<20;
#筛选出女孩而且年龄小于20的数据 - 或:or,
select * from student where gender="boy" or age>20;
#筛选出男孩或者年龄大于20的数据 - 非:not
其他操作
- 排序:order by 字段名
select * from student order by age;
正序(默认从小到大)
select * from student order by age desc;
倒序(从大到小) - 限制:
select * from student order by id limit 3;
#限制数量不大于3
select * from student order by id limit 1,3;
#1个位置偏移,即跳过第一个,从第二个开始数量不大于三个 - 去重:
distinct,select distinct age from student;
- 模糊查询:like(如果不和%或者_配合,就和=一样效果)
- 任意一个字符:_,
select * from student where name like "__";
#查询名字是任意两个字符的(两个_
) - 任意多个字符:%
select * from student where name like"%en%";
#查询名字中包含en的 - 转义:用
\
或者 escape 自定义
select * from bank where name like '#_%' escape '#'; //获取名字以'_'开头的数据
select * from bank where name like '\_%'; //同上
- 范围查询:
连续范围:between a and b(包含a和b)
between一般接数值,日期和时间,字符串也可以,一般不用
select * from student where age between 17 and 22;
- 间隔范围:in,
select * from student where id in (2,4);
#取id为2,4的数据
聚合与分组
- 统计个数:count(字段)
select count(id) from student where age>18;
#统计年龄大于18的
#如果字段里存在null,这组数据不会计算在内 - 求和:sum(字段)
select sum(age) from student where name like"%ao%";
- 平均值:avg(字段),
select avg(age) from student;
- 最大值:max(字段)
select max(age) from student;
- 最小值:min(字段)
- 分组:group by
select name,sum(age) as total_age from student group by name;
#as后接别名,上面total_age是sum(age)的别名
#按照人名分组查询每个人的总年纪 - having子句,
select name,sum(age) as total_age from student group by name having sum(age)>18;
#按照人名分组查询总年纪大于18的数据
select name,sum(age) as total_age from student where name like"%ao%" group by name having sum(age)>18;
#按照人名分组查询名字中有ao字符的总年纪大于18的数据 - 执行顺序:一个查询语句同时包含了别名,聚合函数,where和having
- 最先执行where
- 然后是聚合函数,别名
- 最后执行having
- group by后面不能用where,所以用having
- 当查询聚合函数和另一个字段时就需要有 group by(分组)
- group by 后面不能直接接聚合函数,需要接另一个字段
- where 是对 from 后面指定的表进行数据筛选,属于对原始数据的筛选,having 是对 group by 的结果进行筛选
- 聚合与分组一般是同时出现,因为聚合操作在没有分组的情况下一般是没有意义的,因为多数情况下聚合函数都是对一组数据处理,而不是对所有数据处理
- where 后不可以跟聚合函数,having可以进行聚合函数的判断
子查询
- 查询年龄小于平均年龄的学生数据
- 先查出平局年龄
select avg(age) from student;
#平局年龄为19.25
- 先查出平局年龄
- 再根据平均年龄查询
select * from student where age<19.25;
- 子查询:
select * from student where age<(select avg(age) from student);
#执行顺序由括号内到括号外 - 要求:1. 嵌套在查询语句内
2. 必须用括号包裹
select 查询顺序
- from->join->on->where->group by->having->select->order by-> limit
连接查询
#创建新表score:create table score( id int, name varchar(64), gender enum("boy","girl"), score int);
#增加数据:insert into score values(1,"na","girl",75),(2,"jingyi","girl",88),(3,"wenwen","girl",92),(4,"xiaoge","boy",97),(5,"jiao","girl",81);
mysql> create database test_join;
Query OK, 1 row affected (0.00 sec)
mysql> use test_join;
Database changed
mysql> create table person(
-> id int,
-> name varchar(20),
-> card_id int
-> );
Query OK, 0 rows affected (0.10 sec)
mysql> create table card(
-> id int,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> desc person;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| card_id | int(11) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> desc card;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
内连接:
- inner join
//无条件内连接
mysql> select * from person inner join card;
+------+--------+---------+------+----------------+
| id | name | card_id | id | name |
+------+--------+---------+------+----------------+
| 1 | xiaoge | 1 | 1 | fan_card |
| 2 | hong | 3 | 1 | fan_card |
| 3 | yang | 4 | 1 | fan_card |
| 4 | jiao | 7 | 1 | fan_card |
| 1 | xiaoge | 1 | 2 | jian_bank_card |
| 2 | hong | 3 | 2 | jian_bank_card |
| 3 | yang | 4 | 2 | jian_bank_card |
| 4 | jiao | 7 | 2 | jian_bank_card |
| 1 | xiaoge | 1 | 3 | nong_bank_card |
| 2 | hong | 3 | 3 | nong_bank_card |
| 3 | yang | 4 | 3 | nong_bank_card |
| 4 | jiao | 7 | 3 | nong_bank_card |
| 1 | xiaoge | 1 | 4 | you_bank_card |
| 2 | hong | 3 | 4 | you_bank_card |
| 3 | yang | 4 | 4 | you_bank_card |
| 4 | jiao | 7 | 4 | you_bank_card |
| 1 | xiaoge | 1 | 5 | gong_bank_card |
| 2 | hong | 3 | 5 | gong_bank_card |
| 3 | yang | 4 | 5 | gong_bank_card |
| 4 | jiao | 7 | 5 | gong_bank_card |
+------+--------+---------+------+----------------+
20 rows in set (0.00 sec)
//有条件内连接,把两个表中有关系的数据查询出来
mysql> select * from person inner join card where person.card_id=card.id;
+------+--------+---------+------+----------------+
| id | name | card_id | id | name |
+------+--------+---------+------+----------------+
| 1 | xiaoge | 1 | 1 | fan_card |
| 2 | hong | 3 | 3 | nong_bank_card |
| 3 | yang | 4 | 4 | you_bank_card |
+------+--------+---------+------+----------------+
3 rows in set (0.00 sec)
//进行条件选择时也可以用 on
mysql> select * from person inner join card on person.card_id=card.id;
+------+--------+---------+------+----------------+
| id | name | card_id | id | name |
+------+--------+---------+------+----------------+
| 1 | xiaoge | 1 | 1 | fan_card |
| 2 | hong | 3 | 3 | nong_bank_card |
| 3 | yang | 4 | 4 | you_bank_card |
+------+--------+---------+------+----------------+
3 rows in set (0.00 sec)
//join 和 inner join 效果一样
mysql> select * from person join card on person.card_id=card.id;
+------+--------+---------+------+----------------+
| id | name | card_id | id | name |
+------+--------+---------+------+----------------+
| 1 | xiaoge | 1 | 1 | fan_card |
| 2 | hong | 3 | 3 | nong_bank_card |
| 3 | yang | 4 | 4 | you_bank_card |
+------+--------+---------+------+----------------+
3 rows in set (0.00 sec)
mysql> select * from person join card;
+------+--------+---------+------+----------------+
| id | name | card_id | id | name |
+------+--------+---------+------+----------------+
| 1 | xiaoge | 1 | 1 | fan_card |
| 2 | hong | 3 | 1 | fan_card |
| 3 | yang | 4 | 1 | fan_card |
| 4 | jiao | 7 | 1 | fan_card |
| 1 | xiaoge | 1 | 2 | jian_bank_card |
| 2 | hong | 3 | 2 | jian_bank_card |
| 3 | yang | 4 | 2 | jian_bank_card |
| 4 | jiao | 7 | 2 | jian_bank_card |
| 1 | xiaoge | 1 | 3 | nong_bank_card |
| 2 | hong | 3 | 3 | nong_bank_card |
| 3 | yang | 4 | 3 | nong_bank_card |
| 4 | jiao | 7 | 3 | nong_bank_card |
| 1 | xiaoge | 1 | 4 | you_bank_card |
| 2 | hong | 3 | 4 | you_bank_card |
| 3 | yang | 4 | 4 | you_bank_card |
| 4 | jiao | 7 | 4 | you_bank_card |
| 1 | xiaoge | 1 | 5 | gong_bank_card |
| 2 | hong | 3 | 5 | gong_bank_card |
| 3 | yang | 4 | 5 | gong_bank_card |
| 4 | jiao | 7 | 5 | gong_bank_card |
+------+--------+---------+------+----------------+
20 rows in set (0.00 sec)
- inner join
select * from student inner join score;
(无条件连接或交叉连接,也叫笛卡尔连接)- 第一张表中每一项会和另一张表中的每一项依次组合,如果第一张表a行,第二张表b行,则合成a*b
- 有条件内连接:
select * from student inner join score on score.name=student.name;
#在无条件连接的基础上加上一个on子句,当连接的时候,会筛选出有实际意义的记录来组合,当筛选条件有任意一个表不满足,则都不会列出
- 第一张表中每一项会和另一张表中的每一项依次组合,如果第一张表a行,第二张表b行,则合成a*b
外连接
- left join 和 right join
mysql> select * from person left join card on person.card_id=card.id;
+------+--------+---------+------+----------------+
| id | name | card_id | id | name |
+------+--------+---------+------+----------------+
| 1 | xiaoge | 1 | 1 | fan_card |
| 2 | hong | 3 | 3 | nong_bank_card |
| 3 | yang | 4 | 4 | you_bank_card |
| 4 | jiao | 7 | NULL | NULL |
+------+--------+---------+------+----------------+
4 rows in set (0.00 sec)
mysql> select * from person right join card on person.card_id=card.id;
+------+--------+---------+------+----------------+
| id | name | card_id | id | name |
+------+--------+---------+------+----------------+
| 1 | xiaoge | 1 | 1 | fan_card |
| 2 | hong | 3 | 3 | nong_bank_card |
| 3 | yang | 4 | 4 | you_bank_card |
| NULL | NULL | NULL | 2 | jian_bank_card |
| NULL | NULL | NULL | 5 | gong_bank_card |
+------+--------+---------+------+----------------+
5 rows in set (0.00 sec)
//left join 和 left outer join,right join 和 right outer join 效果一样
mysql> select * from person right outer join card on person.card_id=card.id;
+------+--------+---------+------+----------------+
| id | name | card_id | id | name |
+------+--------+---------+------+----------------+
| 1 | xiaoge | 1 | 1 | fan_card |
| 2 | hong | 3 | 3 | nong_bank_card |
| 3 | yang | 4 | 4 | you_bank_card |
| NULL | NULL | NULL | 2 | jian_bank_card |
| NULL | NULL | NULL | 5 | gong_bank_card |
+------+--------+---------+------+----------------+
5 rows in set (0.00 sec)
- 左连接:left join(右连接:right join),student 在 score 的左侧,以左侧的表为基准,右连接同理
- 两张表做连接的时候,显示左表中的全部数据,而右表的数据如果不存在匹配的则以 null 填充
- left outer join
select * from student left join score on score.name=student.name;
- 完全外连接:full join 或 full outer join
注
- 连接的好处在于可以不用创建外键,可以将有关系字段的两个表放一起比较
- 通常表连接都是条件连接,无条件连接没什么实际意义
- inner join 效果等同于 join,left/right join 效果等同 left/right outer join
- MySQL 不支持 full join/full outer join
- left join union right join 相当于 full join
mysql> select * from person left join card on person.card_id=card.id union select * from person right join card on person.card_id=card.id;
+------+--------+---------+------+----------------+
| id | name | card_id | id | name |
+------+--------+---------+------+----------------+
| 1 | xiaoge | 1 | 1 | fan_card |
| 2 | hong | 3 | 3 | nong_bank_card |
| 3 | yang | 4 | 4 | you_bank_card |
| 4 | jiao | 7 | NULL | NULL |
| NULL | NULL | NULL | 2 | jian_bank_card |
| NULL | NULL | NULL | 5 | gong_bank_card |
+------+--------+---------+------+----------------+
6 rows in set (0.00 sec)
小例
- student 表和 score 表内容
- 内连接,通过筛选条件,当两个表中有相同的名字的数据才会显示出来,方便比较,只有一个表有的名字的数据不显示
- 不论是左连接还是右连接,join 后面接的表在右侧,join 前面的表在左侧
- 左连接 score,以 student 表的 name 为基准,如果左侧没有,右侧有,则右侧多余的不显示;如果左侧有,右侧没有,则右侧的用 null 补充
- 右连接 score,以 score 表的 name 为基准,如果右侧没有,左侧有,则左侧多余的不显示;如果右侧有,左侧没有,则左侧的用 null 补充