文章目录
安装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 补充
40万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
