数据库mysql

数据库mysql的常用命令总结，内容包括：数据库、表的创建、增删改查等。

mysql

• 数据库
  • 链接数据库
    • mysql -uroot -p密码
  • 退出数据库
    • exit
    • quit
  • 查看数据库时间/版本
    • select now();
    • select version();
  • 查看数据库
    • Show databases;
  • 创建数据库
    • create database 数据库名字;
    • 查看数据库
      • show create database 数据库名字;
    • 一般创建设置编码
      • create database 数据库名字 charset=utf8;
  • 删除数据库
    • drop database 数据库名字;
    • 如果删除失败可以尝试【键盘tab上放的符号包裹名字】:drop database ‘drop database 数据库名字’;
  • 查看当前使用的数据库
    • select database();
  • 使用数据库
    • use 数据库名;
• 数据表
  • 查看数据库所有的表
    • show tables;
  • 创建数据表
    • 格式：create table 数据表名 (字段 类型 约束，字段 类型 约束)
    • create table aaa(id int,name char);
    • create table aaa(id int primary key not null,name char);
    • create table student( id int unsigned not null auto_increment primary key, name varchar(30), age tinyint unsigned default 0, high decimal(5,2), gender enum("男","女","保密") default "保密", cls_id int unsigned );
      • create table classes( id tinyint primary key auto_increment, name varchar(30) );
    • 子主题
  • 查看表本身
    • show create table student;
  • 查看表结构
    • desc 数据表名字
  • 修改表
    • 修改表名
      • 格式：-- ALTER TABLE 表名 RENAME [TO|AS] 新表名
      • 例子：alter table classify rename as classes;
    • 添加字段
      • 格式：alter table 表名 add 列名 类型;
      • 例子：alert table student add birthday datetime;
    • 修改字段
      • 修改表-修改字段：重命名版
        • 格式：alter table 表名 change 原名 新名 类型及约束;
        • 例子：alter table students change birthday birth datetime not null;
      • 修改表-修改字段：不重命名版
        • 格式：alter table 表名 modify 列名 类型及约束;
        • 例子：alter table student modify birth date not null;
    • 删除字段
      • alter table student drop high;
  • 删除表
    • drop table 表名
• 数据表增删改查（CURD）
  • curd的解释: 代表创建（Create）、更新（Update）、读取（Retrieve）和删除（Delete）
  • 增
    • 单行全列插入
      • 值的顺序与表中字段的顺序对应
      • 格式：insert into 表名 values(...)
      • insert into classes value(0,"菜鸟");
    • 单行部分插入
      • 值的顺序与给出的列顺序对应
      • 格式：insert into 表名(列1,...) values(值1,...)
      • insert into stundet (name.gender) value('小乔',2);
    • 多行插入
      • insert into 表名 values(...),(...)...;
      • insert into classes (name) value ('小包'),('小红');
      • insert into classes values(0,'python1'),(0,'python2');
  • 改
    • update 表名 set 列1=值1,列2=值2... where 条件
      • 列1=值1是要改的值，where后是修改的条件是那些
    • update students set gender=0,hometown='北京' where id=5;
  • 删
    • where 条件判断
      • 格式：delete from 表名 where 条件
      • delete from students where id=5;
    • 逻辑删除，本质就是修改操作
      • update students set isdelete=1 where id=1;
  • 查
    • mysql查询
      • 查询所有列
        • 格式：select * from 表名;
        • select * from classes;
        • mysql中，在sql语句后面加\G表示将查询结果按列打印
          • select * from df_user /G
      • 查询指定列
        • 普通
          • 格式：select 列1,列2,... from 表名;
          • select name,age from student;
        • as
          • 格式：select 列1 as 名字1,列2 as 名字2,... from 表名;
          • select name as 姓名,age as 年龄 from student;
    • 条件查询
      • 比较运算符
        • 格式：select * from 表名 where xxx
        • select * from student where gender=1;
        • select * from students where age >=25;
      • 逻辑运算符
        • 与(and)
          • --18到28之间的所有学生信息
          • select * from students where age>18 and age=28;
        • 或(or)
          • --18岁以上或者身高180以上
          • select * from students where age>18 or height>=180;
        • 非(not)
          • --不在18岁以上的女性
          • select * from students where not (age>18 and gender=2);
      • 模糊查询(效率低)
        • like %表示任意多个任意字符 _表示一个任意字符
          • 查询姓名中有“小”的名字
          • select * from students where name like "%小%";
          • 查询两个字名字
          • select * from students where name like "__";
        • rlike 正则
          • 查询以周开始的姓名
          • 子主题
      • 范围查询
        • in（非连续的范围）
          • 查询年龄为，18，23，42的姓名
          • select * from students where age in(18,23,42);
          • 查询年龄为，18，23，42的姓名
          • select * from students where age not in(18,23,42);
        • between..and...（连续范围）
          • 年龄在18-40之间的
          • select * from students where age between 18 and 40;
          • 年龄不在18-40之间的
          • select * from students where age not between 18 and 40;
            • between..and..是一个整体，不能用(between..and..)
      • 空判断查询
        • 判空is null
          • select * from students where height is null;
        • 判非空is not null
          • select * from students where height is not null and gender=1;
    • 排序
      • 格式：order by 字段 asc|desc
        • 【asc:从小到大，desc：从大到小】 默认是asc。
      • 查询年龄在18-32的女，安装年龄从小到大排列
      • select * from students where age between 18 and 32 and gender=2 order by age;
      • 查询年龄在18-32的女，安装年龄从小到大排列，当年龄相同，安装身高大小排列
      • select * from students where age between 18 and 32 and gender=2 order by age,height desc;
    • 聚合函数
      • 总数(count)
        • 查询男性有多少人
        • select count(*) as 男总数 from students where gender=1;
      • 最大值(max)
        • 查询年龄最大的
        • select max(age) as 最大值 from students;
      • 最小值(min)
        • 查询女性最矮的身高
        • select min(age) as 最小女性身高 from students where gender=2;
      • 求和(sum)
        • 所有年龄总和
        • select sum(age) from students;
      • 平均值(avg)
        • 年龄的平均值
        • select avg(age) from students;
        • select sum(age)/count(age) from students;
      • 四舍五入(round)
        • round(值，保留小数)
        • select round(avg(age),1) from students;
    • 分组
      • group by
        • group by的含义:将查询结果按照1个或多个字段进行分组，字段值相同的为一组 group by可用于单个字段分组，也可用于多个字段分组
        • select gender,count(*) from students group by gender;
      • group_concat
        • group_concat(字段名)可以作为一个输出字段来使用， 表示分组之后，根据分组结果，使用group_concat()来放置每一组的某字段的值的集合
        • select gender,group_concat(name) from students group by gender;
      • group by + having
        • having 条件表达式：用来分组查询后指定一些条件来输出查询结果 having作用和where一样，但having只能用于group by
        • select gender,count(*) from students group by gender having count(*)>2;
    • 分页
      • 格式：select * from 表名 limit start,count
        • 从start开始，获取count条数据 limit一般放在最后
      • select * from students limit 0,5;
      • 求第n页的数据
        • select * from students where is_delete=0 limit (n-1)*m,m
    • 连接查询
      • 当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的列返回
      • 格式：select * from 表1 inner或left或right join 表2 on 表1.列 = 表2.列
        • on后面是条件
      • select students.*,classes.name from students inner join classes on students.cls_id=classes.id;
      • 内连接查询
        • 查询的结果为两个表匹配到的数据
        • select * from students inner join classes on students.cls_id = classes.id;
      • 右连接查询
        • 查询的结果为两个表匹配到的数据，右表特有的数据，对于左表中不存在的数据使用null填充
        • select * from students as s left join classes as c on s.cls_id = c.id;
      • 左连接查询
        • 查询的结果为两个表匹配到的数据，左表特有的数据，对于右表中不存在的数据使用null填充
        • select * from students as s right join classes as c on s.cls_id = c.id;
    • 自关联
      • 一个表里面的字段关联同个表里的某字段称自关联【例如：省市县】
    • 子查询
      • 在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句
      • 查询班级学生的平均身高
      • select * from students where age > (select avg(age) from students);
      • 子查询耗性能高，少用
• 数据的导入导出
  • 导入
    • mysql> create database abc; # 创建数据库 mysql> use abc; # 使用已创建的数据库 mysql> source /home/abc/abc.sql # 导入备份数据库
• 创建表注意事项
  • 约束
    • 主键primary key：物理上存储的顺序
    • 非空not null：此字段不允许填写空值
    • 惟一unique：此字段的值不允许重复
    • 默认default：当不填写此值时会使用默认值，如果填写时以填写为准
    • 外键foreign key：对关系字段进行约束，当为关系字段填写值时，会到关联的表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并抛出异常
    • 自增约束(AUTO_INCREMENT)
  • 类型
    • 1.数据表字段的类型：越小越好，够用就行
  • 遵循三范式
  • 遵循E-R模型