MySQL数据库基本操作(上)


史上最简单的 MySQL 教程>>>
MySQL运行机制原理&架构>>>
触发器
视图(上)
视图(下)
数据备份与还原(上)
数据备份与还原(中)
数据备份与还原(下)
MYSQL常用基本SQL语句总结
Python 操作 MySQL 之 pysql 与 ORM(转载)


一. MySQL简介

MySQL是一个关系型数据库管理系统,由瑞典MySQL AB 公司开发,目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一,在 WEB 应用方面,MySQL是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件。

二. MySQL的安装与启动

1 . 安装

  • 服务端:
    sudo apt install mysql-server
  • 客户端:
    sudo apt install mysql-client

2 .启动

  • mysql -uroot -p (设置的用户密码)
  • 查看端口号
    nestat -antp | grep 3306
  • 查看MySQL进程
    ps aux | grep mysql 或 ps -ef | grep mysql

3.设置和修改密码和允许远程访问设置

mysql设置连接配置.png

4. 解决中午字符集乱码问题及重装MySQL的过程

mysql乱码问题及重装.png

二. 数据库的基本操作

1. 数据库的创建与打开

show databases 查看所有的数据库
create database 库名 [charset=utf8] 创建数据库
drop databases 库名 删除数据库
use 库名 打开数据库
show tables 查看当前数据库中所有的表
desc 表名 查看表结构

2. 表的管理

1). 表的创建
  • 格式:
    create table 表名(字段名1 类型(数据长度) [约束][comment "列注释"],字段名2.....) comment "表注释"
  • 常用数据类型

varcahr :可变字符类型
int : 整数值,默认长度11位,也可以使用integer, long
flot :小数类型,float(长度,小数位), double
data :日期类型.timestamp (日期时间)
text :多文本类型
enum('F','M') :多文本类型
注意:
varchar和char 的区别:
varchar 长度不固定,最多能存放 65532 个字符。VARCHAR 的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是 65,532字节.
char 的长度是固定,最多能存放的字符个数 255,和编码无关。
从编码的效率和占用内存的情况来看,char长度是固定的比较省时间,但有些字节并用不了那些空间,比较浪费空间,varchar长度可变,可以就具体长度决定所占空间,节省空间,但每次计算比较耗时,所有具体情况具体对待.

2). 表的修改
  • 修改表名
    alter table 表名 rename 型表名
  • 添加列
    alter table 表名 add 列名 类型(数据长度)[约束]
  • 列重命名
    alter table 表名 change 旧的列名 新的列名 类型(数据长度) [约束]
  • 修改列
    alter table 表名 modify 列名 类型(数据长度) [约束]
  • 删除列
    alter table 表名 drop[column] 列名
3). 删除表
  • drop table 表名
4). 查看创建表的sql语句
  • show create table 表名

3. 基本SQL语句

1). insert插入语句
  • 格式1:给某一列添加
    insert into 表名(列名1,列名2) values(值1,值2)
  • 格式2:给所有列添加
    insert into 表名 values(值1,值2,值3,...)
  • 格式3:批量插入
    insert into 表名 values(值1,值2,值3), (值1,值2,值3),...
2). select 查询语句

基本查询语句

  • 查询所有的
    select * from 表名;
  • 查看指定的列
    select 列1,列2 from 表名
  • 查看表中有多少条数据
    select count(*) from 表名;
  • 按条件查询
    select * from 表名 [where 条件]

条件查询(where)

  • 常用的操作符
    > , <, =, !=,>=, <=
  • between
    between 起始值 and 结束值
    select * from student where score between 85 and 95;
  • 模糊匹配查询
    like % 任意多个字符
       _ 任意一个字符
    模糊查询的SQL语法:
    select 字段列表 from 表名称 where 字段名 like 匹配条件
    • 示例
      查询名字中带有"刚"的工人
      select * from worker where name like '%刚%';
      查询名字中第二个字是"刚"的工人
      select * from worker where name like "_刚%";
      查询user表中已t开头的用户的名字
      select * from user where name like "t%";
  • 范围查询
    in()
    查询order表中价格在100到200之间的订单记录
    select * from order where price in(100,200);
3). update更新语句

update 表名 set 字段名=字段值 [where 条件]

4). delete删除语句
  • 清空整个表的数据
    delete from 表名
  • 删除指定的行
    delete from 表名 [where 条件]

4. 排序, 分组, 链接, 限制, 分页

1) 排序查询 order by

格式:
select 字段1,字段2 from 表名 order by [desc](降序) [asc](升序)
示例:

按照成绩从高到低,将学生排序
select * from student order by score desc;
按照成绩从高到低,将学生排序,如果成绩相等则再按照年龄降序排序
select * from student order by score desc,age desc;

2) 分组查询 group by

格式:
select name, count(*) from 表名 group by name.
示例:

按照商品类型type对product表中的记录进行分组,求出每一组的平均价格
select type as 类型,avg(price) as 平均价格 from product group by type;
按照商品类型type对product表中的记录进行分组,求出零食组的平均价格
select type as 类型,avg(price) as 平均价格 from product group by type having type='零食';

3). JOIN链接

join链接操作链接的表越多,那么函数嵌套的层数也会越多,算法复杂度就会呈指数增长。同时,join操作分为三种:①等值链接,INNER JOIN,其中INNER可以省略;②左外连接,LEFT JOIN;③右外连接,RIGHT JOIN。

  • 等价连接
    select 字段列表 from 表A, 表B, ... where 连接条件 [where 条件];
    示例

    (1). 使用“等值连接”查询学生姓名、学生成绩、学校名称。
    select student.name,student.score,school.schoolname fromschool,studentwherestudent.school_id=school.schoolid;
    (2). 使用“等值连接”查询出“张红”的学生姓名、学生成绩、学校名称。
    select student.name,student.score,school.schoolname from school,student where student.school_id=school.schoolid
    and student.name='张红';
    (3). 查看哪些用户购买了产品,那么最好的显示就是用户信息+该用户所对应的消费信息,那么我们可以这么描述为:
    select * from user u join order o on u.user_id = o.user_id;

  • 内连接:
    select 字段1, 字段2 from 表A, 表B, ... where 连接条件 [where 条件];
    示例:

(1). 使用“内连接”查询学生姓名、学生成绩、学校名称。
select student.name,student.score,school.schoolname from school inner join student on student.school_id=school.schoolid;
(2). 使用“内连接”查询出“张红”的学生姓名、学生成绩、学校名称。
select student.name,student.score,school.schoolname from school inner join student on student.school_id=school.schoolid
and student.name='张红';

  • 外链接
    1). 左外连接
    select 字段列表 from 表A left join 表B on 连接条件 [where 条件];
    注意:左外连接的查询结果不但包括了符合连接条件的查询记录,而且还包括了左表中不符合连接条件的记录。
    示例

    先插入一条学校记录,再使用“左外连接”查询学生姓名、学生成绩、学校名称,
    要求显示所有学校的记录。
    insert into school(schoolname,note)values('东北师范',null);
    select student.name,student.score,school.schoolname from school left join student on student.school_id=school.schoolid;

    2). 右外连接
    select 字段列表 from 表A right join 表B on 连接条件 [where 条件];
    注意:右外连接的查询结果不但包括了符合连接条件的查询记录,而且还包括了右表中不符合连接条件的记录。

    select deptname,deptno.name from staff right join deptno on deptno.name=staff.name;

4). 限制查询
  • 限定条数
    select 字段列表 from 表名称 [where 条件] limit 最多记录数;
    示例:

    查询学生表中的前三条记录
    select * from student limit 3;

  • 从指定偏移量查询限定条数
    select 字段列表 from 表名称 [where 条件] limit 偏移量,最多记录数;
    示例:

查询成绩大于等于70分的学生中,前两条记录
select * from student where score >= 70 limit 2;

5). 分页查询

已知当前页为currentPage,每页最多显示的记录数为pageSize,则currentPage页
显示的表中的记录为:
select 字段列表 from 表名称 [where 条件]
limit (currentPage-1)*pageSize,pageSize;
示例:

假如每页最多显示2条记录,查询第三页应该显示的记录
select * from student limit 4,2;

推荐阅读更多精彩内容