MySQL操作及函数集合
1.基础操作
操作数据库:
- 补充:登录数据库 : cmd窗口中输入: mysql -u root -p 回车 输入密码
-
创建库 : create database 库名 default character set utf8 collate utf8_bin;
-
查看所有数据库 : show databases;
-
显示创建数据库语句: show create database 数据库名
-
使用数据库 : use 库名;
-
删除库 : drop database 库名;
-
修改数据库:alter database 数据库名 character set utf-8 collate utf8_bin;
-
查看当前使用的数据库:select database();
-
备份数据库表中的数据 : cmd> mysqldump -u 用户名 -p 数据库名 > 磁盘名:/文件名.sql
-
恢复数据库 : (注意:如果数据库已经删除,先创建数据库再恢复数据。)
方式一: 在cmd中:mysql –u 用户名 -p 数据库名 < 磁盘名:/ 文件名.sql
方式二: 在mysql客户端中 source 磁盘名:/文件名.sql
操作表:
-
创建表:
create table tb_door( id int primary key auto_increment, door_name varchar(100), tel varchar(50) )charater set 字符集 collate 校对规则 ; //charater set 字符集 collate 校对规则 可写可不写
-
mysql常用数据类型
字符串 : varchar char
大数据 : blob text
数值型 : tinyint smallint int bigint flaot double
逻辑性 : B|T 1/0
日期型 : date time datetime timestamp
-
修改表 添加字段 : alter table 表名 add column 列名 类型(长度);
-
修改表 修改字段: alter table 表名 modify 列名 类型(长度);
-
修改表 删除字段: alter table 表名 drop 列名 ;
-
修改表 修改字段的名称 alter table 表名 change 旧列名 新列名 类型(长度);
-
修改表的名称 : rename table 旧表名 to 新表名
-
修改表的字符集 : alter table 表名 character set 字符集;
-
删除表 : drop table 表名;
-
查看所有表 : show tables;
-
查看表结构 : desc 表名;
操作数据:
-
插入记录 : insert into 表名 values(a,b,c,d,…);
insert into 表名(列a,列b) values(值1,值2);
-
查询表 : select * from 表名; * : 代表通配符,可以替换成列名,只显示这个列的数据
-
修改表记录 : update 表名 set 列名=新数据 where 筛选条件; 如果不加就是改一整列
-
删除表记录 : delete from 表名 where 筛选条件 ; 如果不加就是删除一整个表的记录
truncate 表名 :是将整张表摧毁并重建。表中数据会被清空和表结构的重建(效率高,但会影响表与表之间的关系),delete from 是逐行删除数据。
-
排序 : order by 列名 顺序排序
order by 列名 desc;
2.基础函数
-
lower : select lower(字段名) from 表名; --数据转小写
-
upper : select upper(dname) from dept; --数据转大写
-
length : select length(dname) from dept; --数据的长度
-
substr : select substr(dname,1,3) from dept; --substr(a,b,c)-a是截取谁,b是从那个字符开始,c是截取的长度
-
concat : select concat(dname,‘123’) from dept; --拼接数据
-
replace : select replace(dname,‘a’,‘666’) from dept; --把a字符替换成666
-
ifnull : select ifnull(comm,10) from dept; #判断,如果comm是null,用10替换;
-
round : select round(comm) from dept; --四舍五入取整
select round(comm,1) from dept; --四舍五入并保留一位小数
-
ceil : select comm,round(comm,1) from emp; --向上取整
-
floor : select floor(comm) from emp; --向下取整
-
uuid : select uuid(); --返回uuid:a08528ca-741c-11ea-a9a1-005056c00001
-
now : select now(); --年月日时分秒
select curdate() --年月日
select curtime() --时分秒
-
year()年 month()月 day()日 : select now(),year(now()),month(now()),day(now()) from emp ;from emp可有可无
-
hour()时 minute()分 second()秒 : select now(),hour(now()),minute(now()),second(now()) from emp ;
-
转义字符 \ : select ‘ab’cd’ --输出ab’ca 数据中有单引号时,用一个\转义变成普通字符
3.条件查询
-
distinct : select distinct loc from dept; --查看去除重复后的数据
-
where : select * from dept where deptno=1; 条件筛选,注意配合使用and or > < = != in ;
-
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开头后面有两个字符的 _代表一个字符位置
-
null : select * from emp where mgr is null --过滤字段值为空的
select * from emp where mgr is not null --过滤字段值不为空的
-
between and : select * from emp where sal between 3000 and 10000 --[3000,10000]
-
limit : select * from emp limit 2 --列出前两条
select * from emp limit 0,3 --从第一条开始,展示3条记录–前三条
-
order by : select * from emp order by sal #默认升序
select * from emp order by sal desc #降序
4 . 聚合函数
- count : select count(*) from emp --底层优化了 count(1)
- max / min : select min(sal) ,max(sal) from emp --最小值最大值
- sum / avg : select sum(sal),avg(sal) from emp --求和 平均值
- group by :select deptno,job,max(sal),avg(sal) from emp group by deptno,job #deptno和job都满足的
- having : select deptno, AVG(sal) from emp group by deptno having AVG(sal)<8000 #group by只能配合having