数据库笔记

cmd

1、启动和关闭mysql：net start / stop mysql

2、登录mysql：mysql -u用户名 -p密码

3、退出mysql：exit / quit

DDL部分

1、创建数据库：①直接创建数据库：create database db1;

                           ②判断不存在之后，再创建：create database if not exists db1;

                           ③创建数据库并指定字符集：create database db1 character set utf8 / gbk;

                           ④先判断是否存在，再指定字符集：create database if not exists db1 character set utf8 / gbk;

2、修改数据库字符集：alter database db1 character set utf8;

     显示数据库字符集：show create database db1;

3、删除数据库：①直接删除：drop database db1;

                           ②先判断存在，再删除：drop database if exists db1;

4、使用数据库：①查询当前正在使用的数据库：select database();

                           ②使用数据库：use db1;

5、查询表：①查询某个数据库中所有的表名称：show tables;

                    ②查询表结构：desc student;

6、创建表：create table student(

                           id int,

                           name varchar(32),

                           age int,

                           score double(4,1),

                           birthday date,

                           insert_time timestamp /**最后没有逗号/

                     );

7、删除表：①drop table student;

                    ②drop table if exists student;

8、修改表：①修改表名：alter table student rename to stu;

                    ②修改表的字符集：alter table stu character set utf8 / gbk;

                       显示表的字符集：show craete table stu;

                    ③添加一列：alter table stu add gender varchar(10);

                    ④修改列名称和数据类型：alter table stu change gender sex varchar(20);

                       只修改数据类型：alter table stu modify sex varchar(10);

                    ⑤删除列：alter table stu drop sex;

DML部分

1、添加数据：insert into stu(id, name, age) values(1, 'Jack', 18);

                       如果表名后面不定义列明=名，则默认给所有列添加值。

2、删除数据：delete from stu where id = 1;

                       如果不加条件，则删除表中所有记录。

                       truncate table stu; -- 删除表，然后再创建一个一模一样的空表

3、修改数据：update stu set age = 25, score = 58.0 where id = 1;

                       如果没有where条件，则会把表中所有数据改掉。

DQL部分

1、去重：select distinct address from student;

2、求和：select name, math, english, math + english from student;

3、遇到null置为0：select name, math, english, ifnull(math, 0) + ifnull(english, 0) from student;

4、起别名：select name, english, ifnull(math, 0) + ifnull(english, 0) as sum from student;

5、排序查询：select * from student order by math desc;（升序：ASC降序：DESC）

     先按数学排，数学相同再按英语排：select * from student order by math asc, english asc;

6、聚合函数（排除null值）：

     count：计算个数，select count(english) from student;

     max：计算最大值

     min：计算最小值

     sum：计算和

     avg：计算平均值

     把null计算在内：select  count(ifnull(english, 0))  from  student;

7、分组查询：

      ①按照性别分组，分别查询男、女同学的平均分和人数

      select sex, avg(math), count(id) from student group by sex;

      ②按照性别分组，分别查询男、女同学的平均分和人数，要求：分数低于70的，不参与分组

      select sex, avg(math), count(id) from student where math > 70 group by sex;

      ③按照性别分组，分别查询男、女同学的平均分和人数，要求：分数低于70的，不参与分组；组内人数大于2个人的被  查询。

      select sex, avg(math), count(id) from student where math > 70 group by sex having count(id) > 2;

8、分页查询：select * from student limit 0, 3;

      开始的索引 = （当前页码 - 1） *   每页显示的条数

约束

1、非空约束（not null）：

     ①创建表时添加约束：create table stu(
                                                id int,
                                                name varchar(32) not null
                                         );

     ②创建表之后，添加约束：alter table stu modify name varchar(32) not null;

     ③删除非空约束：alter table stu modify name varchar(32)；

2、唯一约束（unique，值不能为空）：

      ①创建表时添加约束 和 创建表之后添加约束与非空约束类似。

      ②删除唯一约束：alter table stu drop index number;

3、主键约束（非空且唯一）：

      ①创建表时添加约束 和 创建表之后添加约束与非空约束类似。

      ②删除主键约束：alter table stu drop primary key;

      ③自动增长（一般配合主键使用）：id int primary key auto_increment

      ④删除自动增长：alter table stu modify id int;

      ⑤添加自动增长：alter table stu modify id int auto_increment;

4、外键约束：

CREATE TABLE department(
	id INT PRIMARY KEY AUTO_INCREMENT,
	dep_name VARCHAR(20),
	dep_location VARCHAR(20)
);

CREATE TABLE employee(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	age INT,
	dep_id INT, -- 外键
	CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id)
);

INSERT INTO department VALUES(NULL, '研发部', '广州'), (NULL, '销售部', '深圳');

INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);

-- 删除外键
ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;

-- 添加外键
ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id);

-- 添加外键，设置级联更新，设置级联删除
ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id)
ON UPDATE CASCADE ON DELETE CASCADE;

SELECT * FROM employee;

注：constraint emp_dept_fk可以省略。

多表关系

1、一对多（多对一）：

     如：部门和员工。

     实现方式：在多的一方建立外键，指向一的一方的主键。

2、多对多：

     如：学生和课程。

     实现方式：多对多关系实现需要借助第三张中间表，中间表至少包含两个字段，做为中间表的外键，分别指向两张表的主键。

3、一对一：

     如：学生和身份证。

     实现方式：在任意一方添加唯一（unique）外键，指向另一方主键。（一般用的很少，既然一对一，干脆合成一张表）

4、简单案例：某旅游网站

①一个旅游线路分类下可以有多个旅游路线，而一个旅游线路只能属于一个分类，所以分类和线路是一对多关系。

②一个用户可以收藏多个线路，一个线路也可以被多个用户收藏，所以用户和线路是多对多关系。

需要创建的表：分类一个，线路一个，用户一个，因为用户和线路是多对多关系，所以要创建一个中间表。

大体关系如下图：

代码：

/*
创建旅游线路分类表 tab_category
cid 旅游线路分类主键，自动增长
cname 旅游线路分类名称，非空，唯一，字符串 100
*/
CREATE TABLE tab_category(
	cid INT PRIMARY KEY AUTO_INCREMENT,
	cname VARCHAR(100) NOT NULL UNIQUE
);

/*
创建旅游线路表 tab_route
rid 旅游线路主键，自动增长
rname 旅游线路名称，非空，唯一，字符串 100
price 价格
rdate 上架时间
cid 外键，所属分类
*/
CREATE TABLE tab_route(
	rid INT PRIMARY KEY AUTO_INCREMENT,
	rname VARCHAR(100) NOT NULL UNIQUE,
	price DOUBLE,
	rdate DATE,
	cid INT,
	FOREIGN KEY (cid) REFERENCES tab_category (cid)
);

/*
创建用户表 tab_user
uid 用户主键，自动增长
username 用户名，长度 100，非空，唯一
password 密码，长度 30，非空
name 真实姓名，长度100
birthday 生日
sex 性别，定长字符串 1
telephone 手机号，定长字符串 11
email 邮箱，字符串长度 100
*/
CREATE TABLE tab_user(
	uid INT PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(100) NOT NULL UNIQUE,
	PASSWORD VARCHAR(30) NOT NULL,
	NAME VARCHAR(100),
	birthday DATE,
	sex CHAR(1) DEFAULT '男',
	telephone VARCHAR(11),
	email VARCHAR(100)
);

/*
创建收藏表 tab_favorite
rid 旅游线路id，外键
date 收藏时间
uid 用户id，外键
rid和uid不能重复，设置复合主键，同一个用户不能收藏同一个线路两次
*/
CREATE TABLE tab_favorite(
	rid INT,
	DATE DATETIME,
	uid INT,
	-- 创建复合主键
	PRIMARY KEY (rid, uid), -- 联合主键
	FOREIGN KEY (rid) REFERENCES tab_route (rid),
	FOREIGN KEY (uid) REFERENCES tab_user (uid)
);

多表查询

1、创建表

-- 创建部门表
CREATE TABLE dept(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
);
INSERT INTO dept (NAME) VALUES('开发部');
INSERT INTO dept (NAME) VALUES('市场部');
INSERT INTO dept (NAME) VALUES('财务部');

-- 创建员工表
CREATE TABLE emp(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	sex CHAR(1),
	salary DOUBLE,
	join_date DATE,
	dept_id INT, -- 外键
	FOREIGN KEY (dept_id) REFERENCES dept (id)
);
INSERT INTO emp VALUES(1, '张三', '男', 3000, '2020-01-01', 1);
INSERT INTO emp VALUES(2, '李四', '女', 3500, '2020-02-02', 2);
INSERT INTO emp VALUES(3, '王五', '男', 4000, '2020-03-03', 2);
INSERT INTO emp VALUES(4, '老王', '男', 7000, '2020-09-10', 3);
INSERT INTO emp VALUES(5, '小王', '女', 5600, '2020-06-07', 1);

2、内连接

①隐式内连接：使用where子句消除无用数据。

-- 查询所有员工信息和对应的部门信息
SELECT
	t1.name,
	t1.sex,
	t2.name
FROM
	emp t1,
	dept t2 -- 起别名
WHERE
	t1.dept_id = t2.id;

②显式内连接：inner可省略。

-- 查询所有员工信息和对应的部门信息
SELECT
	emp.name,
	emp.sex,
	dept.name
FROM
	emp
INNER JOIN
	dept
ON
	emp.dept_id = dept.id;

3、外连接

在emp表新插入数据

INSERT INTO emp VALUES(6, '小六', '男', 5800, NULL, NULL);

①左外连接：查询的是左表的所有数据以及两个表的交集。outer可省略。

-- 查询所有员工信息，如果员工有部门，则显示部门名称，没有部门，则不显示
SELECT
	t1.name,
	t1.sex,
	t2.name
FROM
	emp t1
LEFT OUTER JOIN
	dept t2
ON
	t1.dept_id = t2.id;

由于小六这个人是emp表（左表）的，尽管部门为null，结果还是会显示。

②右外连接：查询的是右表的所有数据以及两个表的交集。outer可省略。

-- 查询所有员工信息，如果员工有部门，则显示部门名称，没有部门，则不显示
SELECT
	t1.name,
	t1.sex,
	t2.name
FROM
	emp t1
RIGHT OUTER JOIN
	dept t2
ON
	t1.dept_id = t2.id;

小六不会显示。

4、子查询

①子查询结果是单行单列，子查询作为条件。

-- 查询emp表中工资最高的员工信息
SELECT
	NAME,
	sex,
	salary
FROM
	emp
WHERE
	salary = (
		SELECT
			MAX(salary)
		FROM
			emp
	);

②子查询结果是多行单列，子查询作为条件。

-- 查询财务部和市场部所有员工的信息
SELECT
	*
FROM
	emp
WHERE
	dept_id
IN
	(
		SELECT
			id
		FROM
			dept
		WHERE
			NAME = '财务部' OR NAME = '市场部'
	);

③子查询结果是多行多列，那么子查询可以作为一张虚拟表。

-- 查询员工入职日期是2020-03-03之后的员工信息和部门信息
SELECT
	*
FROM
	dept t1,
	(
		SELECT
			*
		FROM
			emp
		WHERE
			emp.join_date > '2020-03-03'
	) t2
WHERE
	t1.id = t2.dept_id;

数据库事务

1、如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么全部执行，要么一个也不执行。

2、事务操作：

-- 开启事务
START TRANSACTION;

UPDATE ACCOUNT SET money = money - 500 WHERE NAME = 'Tom';
UPDATE ACCOUNT SET money = money + 500 WHERE NAME = 'Jerry';

-- 回滚
ROLLBACK;

-- 提交
COMMIT;

3、事务的四大特征：

①原子性：事务包含的所有操作，要么全部成功，要么全部失败回滚。

②持久性：一个事务一旦被提交了，那么对数据库中数据的改变就是永久性的。

③一致性：事务必须使数据库从一个一致性状态变换到另一个一致性状态。例子：张三有1000元，李四有1000元，那么两个人一共有2000元，如果张三要给李四转账500元，最后张三有500元，李四有1500元，两个人的总和还是2000元，不会变。

④隔离性：多个事务之间相互独立，一个事务的执行不能被其他事务干扰。

4、几个概念：

①脏读：一个事务，读取到另一个事务中没有提交的数据。

②不可重复读：一个事务先后读取同一条记录，而该数据在这两次读取之间，被其他事务修改，则两次读取的数据不同，这种情况称之为不可重复读。

③幻读：是指当事务不是独立执行时发生的一种现象。例如第一个事务对一个表中的数据进行了修改，比如这种修改涉及到表中的“全部数据行”。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入“一行新数据”。那么，以后就会发生操作第一个事务的用户发现表中还存在没有修改的数据行，就好像发生了幻觉一样。

5、事务的隔离级别：

①read uncommitted：读未提交。产生的问题：脏读、不可重复读、幻读。

②read committed（Oracle默认）：读已提交。产生的问题：不可重复读、幻读。

③repeatable read（MySQL默认）：可重复读。产生的问题：幻读。

④serializable：串行化。可以解决所有问题。

事务的隔离级别从小到大安全性越来越高，但是效率越来越低。