此文仅作个人笔记使用
数据库安装
1 mySQL常见命令
//1. 查看当前所有的数据库:
show databases;
//2. 打开指定库:
use 库名;
//3. 查看当前的表:
show tables;
//4. 查看其他库的表:
show tables from 库名;
//5. 创建表:
create tables 表名(
列名 列类型;
列名 列类型;
....
);
//6. 查看表结构:
desc 表名;
2 SQL
2.1 SQL 通用语法
- 可以单行和多行、缩进;用分号结束
- 不区分大小写,关键字建议用大写
- 注释
- 单行注释:–注释内容或 #注释内容(MYSQL独有)
- 多行注释: / *注释内容 */
2.2 SQL DDL数据定义语言
-
查询数据库
SHOW DATABASES;--查询所有数据库 SHOW DATABASES();--查询当前数据库
-
创建数据库
CREATE DATABASE TEST; CREATE DATABASE IF NO EXISTS TEST; CREATE DATABASE ITHEIMA DEFAULT CHARSET UTF8MB4; CREATE DATABASE [IF NOT EXISTS] 数据库名[default charset 字符集] [collate 排序规则]
-
删除数据库
drop database [if exists] 数据库名字;
-
使用数据库
use 数据库名字;
2.2.1 DDL 表创建和查询
-
查询当前数据库的所有表
show tables
-
查询表结构
desc 表名; show create table 表名;
-
创建
CREATE TABLE 表名{ 字段1 字段类型1 [comment '字段注释1'], 字段2 字段类型2 [comment '字段注释2'], 字段3 字段类型3 [comment '字段注释3'], 字段4 字段类型4 [comment '字段注释4'] }[comment '表注释']
2.2.2 DDL表操作-数据类型
-
数值表格
类型 大小 描述 无符号大小 TINYINT 1 BYTES 小整数形 SMALLINT 2 BYTES 整数类型 MEDIUMINT 3 BYTES 整数类型 INT或INTEGER 4 BYTES 整数类型 RIGINT 8 BYTES 整数 FLOAT 4 BYTES 单精度浮点 DOUBLE 8 BYTES 双精度浮点 写法:double(4,1);4全部长度,1小数点后位数 DECIMAL 小数值 依赖与M(精度)和D标度 -
字符串类型
a. char–定长字符串char(10)
b. varchar–不定长字符串 char(10)
c. tinyblob
d. tinytext
f. … -
日期类型
a. DATE 年月日
b. TIME 时分秒或持续事件
c. YEAR 年份
d. DATETIME 混合日期和时间值
e. TIMESTAMP 混合日期和事件,事件戳
2.2.3 DDL表操作-修改
ALTER TABLE 表名字 ADD 字段名字 类型[COMMENT 字段注释]
ALTER TABLE 表名字 modify 字段名 字段类型;
ALTER TABLE 表名字 change 旧字段名 新字段名 类型(长度) [comment 注释][约束]
--修改表名
alter table 表名字 rename to 新表名;
2.2.4 DDL 表操作 -删除
alter table 表名 drop 字段名;
--删除表
alter table [if exists] 表名;
--删除指定表,并重新创建该表
truncate table 表名;
2.3 DML 数据操作语句
-
添加 insert into
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,...);
-
修改 update
UPDATE 表名 SET 字段名=值1,字段名=值2,....[where 条件]
-
删除 delete from
DELETE FROM 表名[WHERE 条件];
2.4 DQL 数据查询操作
-
SELECT语句
//1.查询多个字段 select 字段 from 表名 select id,name from emploree; //查询多个字段并且起名例子 select workadress as '工作地址' from emploree; select workadress '工作地址 'from emploree; select distinct wordadress from emploree;//去除重复项 //聚合函数,所有的null值不参与 select 聚合函数(字段类型) from 表名; select gender,avg(age) from employee group by gender;//查询男女平均年龄 //排序查询 select * from employee order by age asc,entrytime desc;//先按照年龄升序排序,相同按照入职时间降序排序
-
查询条件
-
聚合函数
-
分组查询
-
排序查询
-
分页查询
2.4 DCL 管理用户
DCL,Data Control Language,数据控制语言,用来管理数据库用户、控制数据库的访问权限。
-
-
权限控制查询
//1. 查询权限
SHOW GRANTS FOR '用户名'@'主机名';
//2. 授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
//3. 撤销权限
REVOKE 权限类表 ON 数据库名.表名 FROM '用户名字'@'主机名'
3 函数
函数是一段可以直接被另一段代码调用的程序或代码
3.1 字符串函数
//注意事项
//substring从1开始计数
SELECT SUBTRING("HELLO MYSQL",1,5);//HELLO
3.2 数值函数
-- 案例:通过数据库的函数,生成一个六位数的随机验证码
SELECT LPAD(ROUND(RAND()*1000000,0),6,'0');
3.3 日期函数
3.4 流程函数
//案例 查询emp表格的员工姓名和工作地址(北京/上海--->一线城市,其他--->二线城市
SELECT
name,
address,
case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市'
from emp;
4 约束
//AUTO_INCREMENT自动增长mysql的
-
外键约束
用来让两张表的数据之间建立连接,从而保证数据的一致性和完整性
5 多表查询
项目开发中,在进行数据库设计时,会根据业务需求及业务模块之间的关系,分析并设计表结构,由于业务之间相互联系,所以各个表结构之间也存在各种联系,基本分为三种
- 一对多(多对一)
-
多对多
create table student( id int auto_increment primary key comment '学号', name char(50) not null comment '姓名', gender char(10) comment '性别' )comment '学生'; create table course ( id int auto_increment primary key comment 'id主键', name varchar(10) comment '课程名称' )comment '课程表'; insert into course values (null,'java'),(null,'PHP'),(null,'hadoop'); create table student_course( id int auto_increment primary key comment '主键', studentid int not null comment '学生id', courseid int not null comment '课程id', constraint fk_courseid foreign key (courseid) references course(id), constraint fk_studentid foreign key (studentid) references student(id) )comment '课程中间表格'; insert into student_course values (null,2,1); insert into student_course values (null,2,2); insert into student_course values (null,2,2); insert into student_course values (null,1,3); insert into student_course values (null,1,2);
-
一对一
create table tb_user(
id int auto_increment primary key comment '主键ID',
name varchar(10) comment '姓名',
age int comment '年龄',
gender char(1) comment '1:男,2:女',
phone char(11) comment '手机号'
)comment '用户信息表';
create table tb_user_edu(
id int auto_increment primary key comment '主键id',
degree varchar(20) comment '学历',
major varchar(50) comment '专业',
primaryschool varchar(50) comment '小学',
middlesschool varchar(50) comment '中学',
university varchar(50) comment '大学',
userid int unique comment '用户ID',
constraint fk_userid foreign key (userid) references tb_user(id)
)comment '用户信息表';
5.1 多表关系
//查询表的时候,限制数据的条件
select * from emp,dept where emp.dept_id = dept.id;
//emp记录所有员工信息和部门的id,dept记录了id对应的部门
5.2 内连接
//5.2.1 隐式连接
SELECT 字段列表 FOROM 表1,表2 WHERE 条件;
//5.2.2 显式连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件
//例子
select emp.name,dept name from emp,dept where emp.dept_id = dept.id;
select e.name,d.name from emp e,dept d whree e.dept id =d.id;//如果为表起了别名,所有都要用别名
select e.name,d.name from emp e inner join dept d on e.dept_id = d.id;
//内连接时两张表格的交集部分
5.3 外连接
5.4 自连接
5.5 联合查询
5.6 子查询
- 标量子查询
//查询销售部所有员工信息
select * from emp where dept_id = (select id from dept where name = "销售部");
//emp员工信息表格,dept部门信息表格
- 列子查询
//查询销售部和市场部的所有员工信息
select * from emp in dept_id = (select id from dept where name = "销售部" or name = '市场部');
//emp员工信息表格,dept部门信息表格
//查询比财务部所有人都高的工资
select id from dept where name = '财务部';
select salary from emp where dept_id = 3;
select name,salary from emp where salary > all(select salary from emp where dept_id =(select id from dept where name = '财务部'));
//查询比研发部任何一个人的工资都高的员工信息
select id from dept where name = '研发部';
select salary from emp where dept_id = (select id from dept where name ='研发部');
select * from emp where salary > any(select salary from emp where dept_id = (select id from dept where name ='研发部'));
-
行子查询
//查询张无忌的薪资和直属领导相同的员工信息 select salary,managerid from emp where name = '张无忌'; select * from emp where salary = 12500 and managerid = 1; select * from emp where (salary,managerid) = (12500,1); select * from emp where (salary,managerid) = (select salary,managerid from emp where name = '张无忌');
-
表子查询
//查询与“鹿杖客”,“宋远桥”的职位和薪资相同的员工信息 //a.查询与鹿杖客与宋远桥的薪资和职位 select job,salary from emp where name = '宋远桥'or name = '鹿杖客'; select * from emp where (job,salary) in (select job,salary from emp where name = '宋远桥'or name = '鹿杖客'); //查询入职日期是“2006-01-01”之后的员工信息,以及其部门信息 //联产 select * from emp where entrydate > '2006-01-01'; select e.*,d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id=d.id
5.7 多表查询练习
6 事务
6.1 事务简历
6.2 事务的操作
CREATE TABLE ACCOUNT(
ID INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
NAME VARCHAR(10) COMMENT '姓名',
MONEY INT COMMENT '余额'
)COMMENT '账户表';
INSERT INTO ACCOUNT(ID,NAME,MONEY)VALUES (NULL,'张三',2000),(null,'李四','2000');
--恢复数据
UPDATE ACCOUNT SET MONEY = 2000 WHERE NAME ='张三' or name = '李四';
--转账操作
//a.查询账户的余额
select * from account where name='张三';
update account set money = money -1000 where name = '张三';
update account set money = money + 1000 where name = '李四';
//6.2.1 查看。设置事务的提交方式
select @@autocommit;
SET @@autocommit = 0;
//6.2.2 开启事务
start transaction 或者 begin
//6.2.3 提交事务
commit;
//6.2.4 回滚
rollback;
6.3 事务的四大特性
6.4 并发事务问题
6.5 事务的隔离级别