DDL
数据库操作
- 查询所有数据库 show databases;
- 查询当前是哪个数据库:select database();
- 创建数据库:create database [if not exists] 数据库名; []是可选字段
- 删除数据库:drop database [if exists] 数据库名;# 如果存在的话就删除不存在也不报错
- 使用某个具体的数据库: use 数据库名;
表操作
- 查询当前数据库所有表:show tables;
- 查询表结构:desc 表名;
- 查询指定表的建表语句:show create table 表名;# 可能包含一些注释信息
- 创建表:create table 表名(字段1 字段1类型,字段2 字段2类型,字段3 字段3类型);
- 添加字段:alter table 表名 add 字段名 类型(长度) [comment 注释];
- 修改数据类型:alter table 表名 modify 字段名 新数据类型(长度);
- 修改字段名和字段类型:alter table 表名 change 旧字段名 新字段名 数据类型(长度);
- 删除字段:alter table 表名 drop 字段名;
- 修改表名:alter table 表名 rename to 新表名;
- 删除表:drop table [if exists] 表名;
- 删除指定表并重新创建该表(相当于内容清空):truncate table 表名;
DML
数据库操作语言,用来对数据库中表的数据记录进行增删改操作。
- 给指定字段添加数据:insert Into 表名(字段名1,字段名2,...) values(值1,值2,...);
insert into employee(id,workno,name,gender,age,idcard,entrydate) values (1,'1','tt','女',18,'123456789123456789','2001-01-01');
- 给全部字段添加数据:insert into 表名 values(值1,值2,...); # 值与表中字段顺序一一对应
insert into employee values (1,'1','tt','女',18,'123456789123456789','2001-01-01');
- 批量添加数据:insert into 表名 values(值1,值2,...),(值1,值2,...),(值1,值2,...);
insert into employee values (1,'1','tt','女',18,'123456789123456789','2001-01-01'),(2,'2','qq','男',18,'12345672123456789','1998-01-01');
- 修改数据:update 表名 set 字段名1 = 值1,字段名2 = 值2,...[where 条件];
update employee set name = 'zz' where id = 1;
update employee set name = 'zz',gender = '女' where id = 1;
update employee set data = '2008-01-01'; # 更新整张表
- 删除数据:delete form 表名 [where 条件]; # 没条件就删除整张表;该语句不能删除某一字段的值(可以使用update)
delete from employee where gender = '男'; # 删除男性员工
delete from employee;# 删除所有员工
DQL
基本查询
- 查询多个字段:select 字段1,字段2,字段3... from 表名;-------select * from 表名;
select * from emp;
select name from emp;
- 设置别名:select 字段1 [as 别名1],字段2 [as 别名2]... from 表名;
select workaddress as '工作地址' from emp; # as可以省略
- 去除重复记录: select distinct 字段列表 from 表名;
select distinct workaddress from emp;
条件查询
- 查询年龄等于/不等于88的员工
select * from emp where age = 88;
select * from emp where age != 88;
- 查询年龄小于20的员工
select * from emp where age < 20;
select * from emp where age <= 20;
- 查询有/没有身份证号的员工信息
select * from emp where idcard is null;
select * from emp where idcard is not null;
- 查询年龄在15岁(包含)到20岁(包含)之间的员工信息
select * from emp where age between 15 and 20;
select * from emp where age >=15 and age <=20;
- 查询性别为女 且年龄小于25的员工信息
select * from emp where gender = '女' and age < 25;
- 查询年龄等于18 或20 或40 的员工信息
select * from emp where age=18 or age=20 or age=40;
select * from emp where age in(18,20,40);
- 查询姓名为两个字的员工信息
select * from emp where name like '_ _';
- 查询身份证号最后一位是x的员工信息
select * from emp where idcard like '%X' ;
聚合函数 --null不参与聚合函数的运算
- 统计该企业员工数量
select count(*) from emp;
select count(id) from emp;
- 统计该企业员工的平均/最大年龄
select avg(age) from emp;
select max(age) from emp;
- 统计西安地区员工的年龄之和
select sum(age) from emp where workaddress = '西安';
分组查询
- 根据性别分组,统计男性员工和女性员工的数量
select gender,count(*) from emp group by gender;
- 根据性别分组,统计男性员工和女性员工的平均年龄
select gender,avg(age) from emp group by gender;
- 查询年龄小于45的员工,并根据工作地址分组,获取员工数量大于3的工作地址
select worksddress,count(*) from emp where age < 45 group by workaddress having count(*) >=3;
排序查询
- 根据年龄对公司员工进行排序
select * from emp order by age asc; # 默认升序 asc可省略
- 根据入职时间对员工进行降序排序
select * from emp order by date desc;
- 根据年龄对公司员工进行升序排序,年龄相同,再按照入职时间进行降序排序
select * from emp order by age asc , date desc;
分页查询
- 查询第一页员工数据,每页展示10条记录
select * from emp limit 10;
- 查询第2页员工数据,每页展示10条记录
select * from emp limit 10,10; # 起始索引(2-1)*10
DQL语句执行顺序
DCL
管理访问权限
- 创建用户itcast,智能在当前主机localhost访问,密码123456
create user 'itcast'@'localhost' indentified by '123456';
- 创建用户ttt,可以在任意主机访问,密码123456
create user 'ttt'@'%' indentified by '123456';
- 修改用户ttt的访问密码为1234
alter user 'ttt'@'%' identified with mysql_native_password by '1234';
- 删除用户
drop user 'ttt'@'%';
函数
字符串函数
- 字符串拼接
select concat('hello','mysql');
- 字符串填充
select lpad('01',5,'-'); # ---01
- 去除空格
select trim(' hello mysql '); #hello mysql#
- 截取字符串
select substring('hello mysql',1,5); # hello
数值函数
select ceil(1.5); #2
select floor(1.9); #1
select mod(5,4); # 1
select rand(); #0-1之间的随机数
select round(2.345,2); #2.35
日期函数
流程函数
约束
应用示例
外键约束
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);
alter table emp drop foreign key fk_emp_dept_id;
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on updata cascade on delete cascade;
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on updata set null on delete set null;
多表查询
多表关系
一对多(多对一)、多对多、一对一
多表查询
内连接
外连接
---------未完待续----------
事物
一组操作的集合,是一个不可分割的工作单位,事物会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同事失败回滚。
- 查看/设置事务提交方式
select @@autocommit; # 1就是自动提交
set @@autocommit = 0; # 设置为手动提交
- 提交事务/回滚事务
commit;
rollback;
- 开启事务
start transaction 或 begin;
事务四大特性ACID
- 原子性:事物是不可分割的最小操作单元,要么全部成功,要么全部失败回滚。
- 一致性:事务完成时,必须使所有的数据都保持一致状态,比如A向B转账,不可能A扣了钱,B却没收到。
- 隔离性:数据库系统提供的隔离机制,保证事物在不受外部并发操作的影响的独立环境下运行
- 持久性:事物一旦提交或回滚,它对数据库中的数据的改变就是永久的。
事物隔离级别越高,数据越安全,但是性能越低。
存储引擎
存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的,而不是基于库的,所以存储引擎也可被称为表类型。
InnoDB
MyISAM
Memory
三者区别