数据库、表、表内容增删改查

数据库和表的创建

• 用户的增 删 改 查

  • 增 create user '用户名'@'192.168.16.%' identified by ‘123’；创建一个账户，并设置密码，可不设密码

    • grant all on *.* to '用户'@'地址';   #给账户授权
      flush privileges；     #刷新授权立即生效
      

      创建账户并授权

      grant all on *.* to 'eva'@'%' identified by '123'
      

  • 删 drop user '用户名'@'ip地址'；

  • 改 rename user '用户名'@'ip地址' to '新用户名'@‘ip地址' ;

  • 查 select user,host from mysql.user; 查看账户和地址

• 库(文件夹）的增 删 改 查

  • 增 create database 库名 charset utf8; 增加一个库，编码为utf8
  • 查 show databases; 查看所有的数据库
  • 改 alter database 库名 charset latin1;
  • 删除 drop database 库名 ; 慎用

• 操作表（文件） 增 查 改 删

  • 先切换到库中

    • use 库名 例 use db

  • 增 create table 表名 (列名 类型 约束，列名 类型 约束)； 列名与列名之间用逗号隔开， **约束可不设置**

    • 类型

      • int 整数 float 小数 （常用）
      • date 年月日 time 时分秒 datetime 年月日时分秒
      • char 定长字符串 varchar 变长字符串

    • **约束 not null, unique , primary key , foreign key （自列名） references 约束表名（列名），auto_increment, **

      • not null 非空约束，指定某列不能为空

      • unique 唯一约束

      • primary key 主键 指定给列名的值可以唯一地标识该记录

      • foreign key 外键 指定该行记录从属于主表中的一条记录，主要用于参照完整性

        • 例：foreign key (id) references class(cid)

        • foreign key(dpt_id)
          references department(id)
          on delete cascade  # 连级删除
          on update cascade # 连级更新
          

      • atuo_increment 表示自增

  • 查 show tables 查库中的所有表名

    • desc 表名 查看表结构

  • 改

    • alter table 表名 modify 原列名 新的类型 约束； 在表名中的列名改成新的类型和约束（覆盖掉原来的类型和约束）
    • alter table 表名 change 原列名 新列名 新的类型 约束; 将表名中的列名更换成新的列名，和新的类型，约束(**必须写新的类型和约束，不能直接只修改列名**)

  • 删 drop table 表名; 删除某个表

• 操作表(文件)内容 增 查 改 查 清空表

  • 增 insert into 表名 values (1,'egon1'),(2,'egon2');

  • 改 update 表名 set 列名=’新的类容‘ where id = 2; 需要用到主键作为约束条件

  • 删

    • delete from 表名 where id =1; 根据条件进行删除某一行

  • 清空表

    • delete from 表名； 清空表中的内容，如果表中有自增id，那么新增的数据仍然是删除前的最后一个+1
    • truncate table 表名; 清空，有自增的也从0开始，速度比上一条快

  • 查

    • 1. 简单查询

         select * from 表名; 可以单独查表名中的某些列名，将*号换成列名，列名与列名之间用逗号隔开

      • 例select id,name from student;

    • 1. 去重查 distinct

         select distinct 列名 from 表名； 对所查列名进行去重

      • 1. 通过四则运算查询

      select 列名*12 as 别名 from 表名； 比如说月薪x12 进行查询，as是取别名的意思,as可以省略不写

      • 1. 定义显示格式，concat() 用于连接字符串类似格式化输出

      select concat('内容',列名,'内容',列名) as 别名 from 表名

      例 select concat('姓名：',name,'年龄：',age) as user from users;

      concat_ws() 第一个参数为分割符

      例select concat_ws(':',name,age) as user from users;

      ​ 结果为: alex：38

      • 1. 结合case语句

          SELECT
              (
                  CASE
                  WHEN emp_name = 'jingliyang' THEN
                      emp_name
                  WHEN emp_name = 'alex' THEN
                      CONCAT(emp_name,'_BIGSB')
                  ELSE
                      concat(emp_name, 'SB')
                  END
              ) as new_name
          FROM
              emp;
      

      • where约束 where 类似if条件语句

    • between 80 and 100 值在80到100之间between 是之间的意思

      • not between 值不在80到100之间的数,

    • in(a,b,c) 表示取值为a或b或c的数

    • is null 或者 is not null判断某个字段是否为空，不能用等号

    • like 'eg%' 以eg...开头的 %表示eg后面接任意长度字符，

      • like 'eg_' 以eg后只能由一个字符 _表示任意长度为1字符

    • 聚合函数 count(),对于数字类的列名可以用avg（平均），min，max，等

      • 例 select avg(salary) from employee where depart_id = 3;

    • 分组关键字group by 分组的意思 通常和group_concat() ,聚合函数配合使用

      • SELECT post,GROUP_CONCAT(emp_name) FROM employee GROUP BY post;#按照岗位分组，并查看组内成员名
        select post,count(id) as count from employee group by post;#按照岗位分组，并查看每个组有多少人
        

    • 优先级 where >group by > having >order by

    • 单例排序 以...进行排序 order by 列名 osc;小到大，osc为默认值，可以不写 或者order by 列名 desc;大到小

      • 多例排序

        • 先按照age排序，如果年纪相同，就按薪资排序

          SELECT * from employee ORDER BY age,salary DESC;
          

      • 限制次数 limit 顾首不顾尾，初始值为0

        •     SELECT * FROM employee ORDER BY salary DESC
                  LIMIT 0,5; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条
          
              SELECT * FROM employee ORDER BY salary DESC
                  LIMIT 5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条
          

    • 正则关键字 regexp

      • SELECT * FROM employee WHERE emp_name REGEXP 'on$';