[大数据]MySQL操作及函数集合

MySQL操作及函数集合

1.基础操作

操作数据库:

• 补充:登录数据库 : cmd窗口中输入： mysql -u root -p 回车 输入密码

1. 创建库 : create database 库名 default character set utf8 collate utf8_bin;

2. 查看所有数据库 : show databases;

3. 显示创建数据库语句: show create database 数据库名

4. 使用数据库 : use 库名;

5. 删除库 : drop database 库名;

6. 修改数据库:alter database 数据库名 character set utf-8 collate utf8_bin;

7. 查看当前使用的数据库:select database();

8. 备份数据库表中的数据 : cmd> mysqldump -u 用户名 -p 数据库名 > 磁盘名:/文件名.sql

9. 恢复数据库 : （注意：如果数据库已经删除，先创建数据库再恢复数据。）

   方式一: 在cmd中:mysql –u 用户名 -p 数据库名 < 磁盘名:/ 文件名.sql

   方式二: 在mysql客户端中 source 磁盘名:/文件名.sql

操作表:

1. 创建表:

   create table tb_door(
   id int primary key auto_increment,
   door_name varchar(100),
   tel varchar(50)
   )charater set 字符集  collate 校对规则 ;
   //charater set 字符集  collate 校对规则   可写可不写
   

2. mysql常用数据类型

   字符串 : varchar char

   大数据 : blob text

   数值型 : tinyint smallint int bigint flaot double

   逻辑性 : B|T 1/0

   日期型 : date time datetime timestamp

3. 修改表 添加字段 : alter table 表名 add column 列名 类型(长度);

4. 修改表 修改字段: alter table 表名 modify 列名 类型(长度);

5. 修改表 删除字段: alter table 表名 drop 列名 ;

6. 修改表 修改字段的名称 alter table 表名 change 旧列名 新列名 类型(长度);

7. 修改表的名称 : rename table 旧表名 to 新表名

8. 修改表的字符集 : alter table 表名 character set 字符集;

9. 删除表 : drop table 表名;

10. 查看所有表 : show tables;

11. 查看表结构 : desc 表名;

操作数据:

1. 插入记录 : insert into 表名 values(a,b,c,d,…);

   ? insert into 表名(列a,列b) values(值1,值2);

2. 查询表 : select * from 表名; * : 代表通配符,可以替换成列名,只显示这个列的数据

3. 修改表记录 : update 表名 set 列名=新数据 where 筛选条件; 如果不加就是改一整列

4. 删除表记录 : delete from 表名 where 筛选条件 ; 如果不加就是删除一整个表的记录

   truncate 表名 :是将整张表摧毁并重建。表中数据会被清空和表结构的重建(效率高,但会影响表与表之间的关系),delete from 是逐行删除数据。

5. 排序 : order by 列名 顺序排序

   ? order by 列名 desc;

2.基础函数

1. lower : select lower(字段名) from 表名; --数据转小写

2. upper : select upper(dname) from dept; --数据转大写

3. length : select length(dname) from dept; --数据的长度

4. substr : select substr(dname,1,3) from dept; --substr(a,b,c)-a是截取谁,b是从那个字符开始,c是截取的长度

5. concat : select concat(dname,‘123’) from dept; --拼接数据

6. replace : select replace(dname,‘a’,‘666’) from dept; --把a字符替换成666

7. ifnull : select ifnull(comm,10) from dept; #判断,如果comm是null,用10替换;

8. round : select round(comm) from dept; --四舍五入取整

   ? select round(comm,1) from dept; --四舍五入并保留一位小数

9. ceil : select comm,round(comm,1) from emp; --向上取整

10. floor : select floor(comm) from emp; --向下取整

11. uuid : select uuid(); --返回uuid：a08528ca-741c-11ea-a9a1-005056c00001

12. now : select now(); --年月日时分秒

    ? select curdate() --年月日

    ? select curtime() --时分秒

13. year()年 month()月 day()日 : select now(),year(now()),month(now()),day(now()) from emp ;from emp可有可无

14. hour()时 minute()分 second()秒 : select now(),hour(now()),minute(now()),second(now()) from emp ;

15. 转义字符 \ : select ‘ab’cd’ --输出ab’ca 数据中有单引号时,用一个\转义变成普通字符

3.条件查询

1. distinct : select distinct loc from dept; --查看去除重复后的数据

2. where : select * from dept where deptno=1; 条件筛选,注意配合使用and or > < = != in ;

3. like : select * from emp where ename like ‘a%’ --以a开头的

   ? select * from emp where ename like ‘%a’ --以a结束的

   ? select * from emp where ename like ‘%a%’ --包含a的

   ? select * from emp where ename like ‘a__’ --以a开头后面有两个字符的 _代表一个字符位置

4. null : select * from emp where mgr is null --过滤字段值为空的

   ? select * from emp where mgr is not null --过滤字段值不为空的

5. between and : select * from emp where sal between 3000 and 10000 --[3000,10000]

6. limit : select * from emp limit 2 --列出前两条

   ? select * from emp limit 0,3 --从第一条开始,展示3条记录–前三条

7. order by : select * from emp order by sal #默认升序

   ? select * from emp order by sal desc #降序

4 . 聚合函数

1. count : select count(*) from emp --底层优化了 count(1)
2. max / min : select min(sal) ,max(sal) from emp --最小值最大值
3. sum / avg : select sum(sal),avg(sal) from emp --求和 平均值
4. group by :select deptno,job,max(sal),avg(sal) from emp group by deptno,job #deptno和job都满足的
5. having : select deptno, AVG(sal) from emp group by deptno having AVG(sal)<8000 #group by只能配合having