MySQL数据库操作
1.基础操作
mysql -uroot -p1234 //登录不加分号
show databases //显示数据库
create database data1 //创建数据库
create database if not exists data2 //当data2数据库不存在时创建数据库
drop database data2 //删除数据库
drop database if exists data2 //当data2数据库存在时删除数据库
use data1 //使用data1数据库
select database() //显示当前使用的数据库
show tables //显示当前数据库的表
desc [表名称] //查看当前表的格式
drop table [表名称] //删除表
drop table if exists [表名称 ] //判断表是否存在再进行删除
alter table [当前表名] rename to [更改的表名] //表名修改
alter table stu add [数据名] [数据类型] //添加
alter table stu modify [数据名] [数据类型] //修改数据数据类型
alter table stu change [准备修改的数据名] [修改的数据名] [数据类型] //整列修改
alter table stu drop [数据名] //删除整列
2.添加数据
INSERT INTO [表名](列名1,列名2,.......) VALUES(值1,值2,.......); //添加指定的数据
INSERT INTO [表名] VALUES(值1,值2,.......); //添加全部数据
//批量添加数据
INSERT INTO [表名](列名1,列名2,.......) VALUES(值1,值2,.......),(值1,值2,.......),(值1,值2,.......); //添加指定的数据
INSERT INTO [表名] VALUES(值1,值2,.......),(值1,值2,.......),(值1,值2,.......); //添加全部数据
3.修改数据
UPDATE [表名] SET 列名1=值1,列名2=值2,..... WHERE 条件 //注意修改语句中如果不加条件,则将所有数据都修改!
4.删除数据
DELETE FROM [表名] WHERE 条件;//注意删除语句中如果不加条件,则会删除所有数据
5.查询基础
5.1指定列查询
SELECT 列名1,列名2,.... FROM 表名;
//查询所有列的数据,列表可以用*代替,但不要使用大厂规范
select * from [表名];
//去除重复数据 添加DISTINCT关键字
SELECT DISTINCT 列1,列2,.... FROM 表名;
//起别名
SELECT 列名1 AS 新列名1,列名2 AS 新列名2,.... FROM 表名;
5.2条件查询
// 大于 小于 等于 不等于号使用
select * from stu where age>20;
select * from stu where age>=20;
select * from stu where age=18;
select * from stu where age !=18; //!= 和<>相同效果
//筛选范围内的数据
select * from stu where age>=20 and age<=30;
select * from stu where age between 20 && 30; //不常用
select * from stu where age between 20 and 30;
select * from stu where birthday between '1998-09-01'and '199-09-01'; //日期类也可以比较
select * from stu where age=18 || age=20 || age=28; //不常用
select * from stu where age=18 or age=20 or age=28;
select * from stu where age in (18,20,28); //简化写法
//null值不可用符号比较
select * from stu where math is null;
select * from stu where math is not null;
5.3模糊查询 like
通配符
_:表示单个任意字符
%:表示任意个数的字符
//查询姓马的同学
select * from stu where name like '马%';
//查询第二个是马的同学
select * from stu where name like '_马%';
//查询包含马的同学
select * from stu where name like '%马%';
5.4排序查询
order by
升序 asc(默认升序)
降序 desc
select * from stu order by age;//升序排列
select * from stu order by math desc;//降序排列
select * from stu order by math desc,english asc ;//降序排列
5.5聚合函数
将一列数据作为整体,纵向计算
//count统计数量 可以指代列统计但是只记录数据不为空的列,一般用*
SELECT COUNT(*) FROM [表名];
//max :求最大值
SELECT MAX(列名) FROM [表名];
//min :求最小值
SELECT MIN(列名) FROM [表名];
//sum :求和值
SELECT SUM(列名) FROM [表名];
//avg :求平均值
SELECT AVG(列名) FROM [表名];
注意:NULL的值不参与运算。
5.6分组查询
GROUP BY
SELECT [聚合函数和分组字段] from [表名] [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤];
WHERE和HAVING区别
执行时间不一样,可判断的条件不一样
执行顺序:where>聚合函数>having
5.7分页查询
SELECT 字段列表 FROM [表名] LIMIT 起始索引,查询条目数;
索引从0开始
计算公式:起始索引=(当前页码-1)*每页显示的条数
MySQL数据类型
常用数据类型:
数值类型
日期和时间类型
字符串类型
数据类型表:
注意:
score double(总长度,小数点后保留几位)0-100 2 score double(5,2)
birthday date 年 月 日
“张三”
name char(10)10个字符空间 储存性能高,浪费空间
name varcaher(10)2个字符空间 储存性能低,节约空间
MySQL数据库约束
约束分类6种
1.非空约束 //NOT NULL
2.唯一约束 //UNIQUE
3.主键约束 //PRIMARY KEY
4.检测约束 //CHECK
5.默认约束 //DEFAULT
6.外键约束 //FOREIGN KEY
auto_increment 自增加
外键约束
(1)添加约束
CREATE TABLE 表名(
列名 数据类型,
.......
[CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名称) REFERENCES 主表名(主列表名称)
)
//删除外键
ALTER TABLE emp1 DROP FOREIGN KEY fk_emp1_dept;
// 添加外键
ALTER TABLE emp1 ADD CONSTRAINT fk_emp1_dept FOREIGN KEY(dep_id) REFERENCES dept(id);
MySQL数据库设计
表关系
1.一对一
2.一对多
3.多对多
多表查询
//内连接查询语法
-- 隐式内连接 【select 字段列表 from 表名,表名 where 条件】
SELECT emp1.`name`,emp1.age,dept.dep,dept.addr FROM emp1,dept WHERE dept.id = emp1.dep_id;
//起别名
SELECT t1.`name` AS 姓名,t1.age AS 年龄,t2.dep AS 部门,t2.addr AS 地名 FROM emp1 t1,dept t2 WHERE t2.id = t1.dep_id;
-- 显示内连接 【select 字段列表 from 表名 inner join 表名2 on 条件】
SELECT emp1.`name`,emp1.age,dept.dep,dept.addr FROM emp1 INNER JOIN dept ON emp1.dep_id = dept.id;
//外连接查询语句
1.左外连接 【select 字段列表 from 表名1 left join 表名2 on 条件】
SELECT * FROM emp1 LEFT JOIN dept ON emp1.dep_id = dept.id;
2.右外连接 【select 字段列表 from 表名1 right join 表名2 on 条件】
SELECT * FROM emp1 RIGHT JOIN dept ON emp1.dep_id = dept.id;
//子查询
单行单列
select * from 表 where 字段名=(子查询)
多行单列
select * from 表 where 字段名 in (子查询)
多行多列
select * from (子查询)where 条件
MySQL数据库事务
事务
-- 转账操作
BEGIN;
-- 查询余额
-- 李四金额 -500
UPDATE account set money = money-500 WHERE name='李四';
-- //出错了
-- 张三金额 +500
UPDATE account SET money = money+500 WHERE name='张三';
-- 提交事务 无错误
COMMIT;
-- 回滚事务 有错误
ROLLBACK;
事务的四大特征:
原子性:事务是不分割的最小操作单位,要么同时成功,要么同时失败。
一致性:事务完成时,必须使所有的数据都保持一致状态。
隔离性:多个事务之间,操作的可见性。
持久性:事务一旦提交或回滚,它对数据库中的数据的改变就是永久的。
一般数据书写都自动提交
SELECT @@autocommit; -- 默认数值为1自动提交
SET @@autocommit=0; -- 值为0时候需要手动提交commit
以后的数据需要commit才可以永久更改。