MySQL
day1_2023.9.4
MySQL
控制台命令 :
登录 : mysql -uroot -p 回车 ,输入密码
查看可以使用的数据库 : show databases;
使用某个数据库 : use 数据库名;
查看库中有哪些表 : show tables;
用户管理
创建用户
create user ‘用户名’@‘ip地址’ identified by ‘密码’;
删除用户
drop user ‘用户名’@‘ip地址’;
修改密码
set password for ‘用户名’@‘ip地址’ = Password(‘新密码’);
权限管理
数据库名.* :针对某个数据库中的所有表
数据库名. 表名 : 针对某个数据库的某张表
. :所有数据库的所有表
查看权限
show grants for ‘用户名’@‘ip地址’;
授权
grant 权限 on 数据库.表 to ‘用户名’@‘ip地址’;
常用的授权命令:
GRANT ALL PRIVILEGES ON . TO ‘root’@‘localhost’ WITH GRANT OPTION;
允许远程登录的(本地登录服务器或者虚拟机)
GRANT ALL PRIVILEGES ON . TO ‘root’@‘%’ WITH GRANT OPTION;
MySql的表操作
创建表
create table 表名(
字段名 类型 约束,
字段名 类型 约束
)engine = InnoDB default charset=utf8;
示例:
-- 创建表
CREATE TABLE `t_user`(
`user_id` INT(1) PRIMARY KEY AUTO_INCREMENT COMMENT '用户id,主键',
`user_name` VARCHAR(100) NOT NULL COMMENT '用户名',
`user_password` VARCHAR(100) NOT NULL COMMENT '用户密码',
`user_phone` VARCHAR(11) UNIQUE NOT NULL COMMENT '用户手机',
`user_address` VARCHAR(100) DEFAULT '南京' COMMENT '用户地址',
`user_birthday` DATE NOT NULL COMMENT '用户生日'
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- AUTO_INCREMENT 表示设置某个字段为自增(一般是主键)
-- ENGINE=INNODB 表示设置这个表的引擎是 INNODB
删除表
drop table 表名;
清空表内容
delete from 表名; – 事务的删除,可以回滚
truncate table 表名; – 直接清空表,不能回滚
修改表
添加列: alter table 表名 add 字段 类型;
删除列 : alter table 表名 drop column 字段;
修改列 :
alter table 表名 modify column 字段名 新类型; – 修改字段类型
alter table 表名 change 原字段 新字段 类型; – 修改字段名称、类型
修改约束
添加主键 : alter table 表名 add primary key(字段);
删除主键 : alter table 表名 drop primay key;
添加默认约束 : alter table 表名 alter 字段 set default 值;
删除默认约束 : alter table 表名 alter 字段 drop default 值;
添加外键 :
alter table 从表 add constraint 外键名 foreign key 从表(字段) references 主表(主键字段)
删除外键 :
alter table 表名 drop foreign key 外键名;
练习:
创建一个订单表,订单表中,存在如下字段 :
订单id , 下单时间 , 用户编号 ,订单是否付款,订单是否签收
创建的时候加上约束,最后外键指向用户表的用户id
CREATE TABLE `t_order`(
`order_id` INT(1) PRIMARY KEY AUTO_INCREMENT COMMENT '订单id,主键',
`order_time` TIMESTAMP NOT NULL COMMENT '下单时间',
`user_id` INT(1) NOT NULL COMMENT '用户编号',
`order_pay` CHAR(1) NOT NULL COMMENT '是否付款 1已付款 0未付款',
`order_wuliu` CHAR(1) COMMENT '0 未发货 1已发货 2已签收'
)ENGINE=INNODB DEFAULT CHARSET=utf8;
MySQL中的存储引擎
存储引擎可以理解为表的存储结构,每种存储引擎都支持不同的特性。
– 查看支持的存储引擎
show engines;
– 最常用的三种引擎
InnoDB: MySQL默认的存储引擎,支持事务、支持行锁、表锁,支持各种索引、支持外键,高版本支持全文索引,但是批量插入效率低。
**MyISAM:**批量数据插入效率高,数据查询效率快,支持全文索引,不支持事务,不支持行锁,只支持表锁
**MEMORY:**使用这个存储引擎的时候,会将表中的数据加载到内存中,查询很快,对内存要求较高
MySQL的数据类型
bigint 64位大整数
整数长度 : 整数类型声明的时候可以指定长度 ,比如 int(11)表示长度为11的int类型,长度在大多数场景下没有太大意义
小数类型: decimal 类型可以存储较大类型,计算的效率比float和double 要低
字符串类型 : varchar 、char 、text 、 blob
varchar是可变长度,char是固定长度的,数据长度不足,用空格填充
时间日期类型: timestamp 的效率比datetime高
常用函数
数学类函数 :
abs() 绝对值 、 mod()取余、ceil() 向上取整、floor()向下取整
字符串类函数:
length() 返回字符串长度
upper() 和 lower() 大小写函数
concat() 字符串拼接
trim() 去除空格
substring() 字符串截取
replace() 字符串替换
reverse() 字符串反转
日期函数
-- mysql日期函数
-- 当前时间
SELECT SYSDATE();
SELECT NOW();
-- 当前日期
SELECT CURDATE();
-- 当前时间
SELECT CURTIME();
-- 返回某天是星期几
SELECT DAYOFWEEK(NOW())-1;
SELECT WEEKDAY(NOW()) + 1;
-- 返回每个月的第几天
SELECT DAYOFMONTH(NOW());
-- 一年的第几天
SELECT DAYOFYEAR(NOW());
-- 返回月份
SELECT MONTH(NOW());
-- 返回某天
SELECT DAY(NOW());
-- 指定日期月份的最后一天
SELECT LAST_DAY(NOW());
-- 查询哪些员工是入职当月的最后3天入职的
SELECT * FROM emp WHERE DAY(LAST_DAY(hiredate)) - DAY(hiredate) < 3;
-- 查询本周过生日的员工
SELECT * FROM emp WHERE WEEK(hiredate) = WEEK(NOW());
-- 查询下周过生日的员工
SELECT * FROM emp WHERE WEEK(hiredate) = WEEK(NOW()) + 1;
-- 查询本月过生日的员工
SELECT * FROM emp WHERE MONTH(hiredate) = MONTH(NOW());
-- 查询下月过生日的员工
SELECT * FROM emp WHERE MONTH(hiredate) = MONTH(NOW()) + 1;
聚合函数
max() 最大值 、 min() 最小值 、 count() 数量 、 sum() 求和 、 avg() 平均值
使用聚合函数的时候,默认如果没有指定分组(group by) 的话,那么就把查询到的所有数据当做一个组
-- 分组函数
-- 查询工资最高和工资最低, 工资分别是多少
SELECT MAX(sal),MIN(sal) FROM emp;
-- 查询所有员工的平均工资,员工数量、奖金之和
SELECT AVG(sal) ,COUNT(1),SUM(comm) FROM emp;
-- 平均奖金,默认排除null来求平均值
SELECT AVG(comm) FROM emp;
MySQL表内的操作
DML操作
增
insert into 表 (字段,字段…) values(值,值…);
insert into 表 (字段,字段…) values(值,值…),(值,值…);
insert into 表 values(值,值,值…); – 值的数量类型,比如和字段的数量、类型一一对应
删
delete from 表 ; – 删除全部
delete from 表 where 条件; --根据条件删除
改
update 表 set 字段 = ‘值’ where 条件;