标题: 数据库基础学习笔记 [打印本页] 作者: 18512857683 时间: 2019-5-31 13:00 标题: 数据库基础学习笔记 数据库学习笔记：
数据库！：
创建数据库：create database 数据库名；
判断数据库是否已经存在，不存在则创建数据库:CREATE DATABASE IF NOT EXISTS 数据库名;创建数据库并制定字符集：CREATE DATABASE 数据库名 CHARACTER SET 字符集;
查看所有的数据库：show databases;
查看某个数据库的定义信息：show create database db3; show create database db
修改数据库默认的字符集：ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 字符集(utf8);
删除数据库：DROP DATABASE 数据库名;
查看正在使用的数据库：SELECT DATABASE(); 使用的一个 mysql 中的全局函数
使用/切换数据库：USE 数据库名；
表：
CREATE TABLE 表名 ( create table student( id
字段名 1 字段类型 1, id int, -- 整数
字段名 2 字段类型 2 name varchar(20), --字符串
); birthday date -- 生日);最后没有逗号
查看某个数据库中的所有表：show tables;
查看表结构:desc 表名;
查看创建表的 SQL 语句:show create table
快速创建一个表结构相同的表:create table 新表名Like 旧表名;
直接删除表：brop table 表名；
判断表是否存在，如果存在则删除表：drop table if exists 表名;
添加表列ADD：alter table 表名 ADD 列名 类型;
为学生表添加一个新的字段 remark,类型为 varchar(20)
Alter table student add remark varchar(20)
修改列类型modify;
Alter table 表名 modify 列名 新的类型;
将 student 表中的 remark 字段的改成 varchar(100)
alter table student modify remark varchar(100);
修改列名 changeg;
ALTER TABLE 表名 CHANGE 旧列名 新列名 类型;
将 student 表中的 remark 字段名改成 intro，类型 varchar(30)
alter table student change remark intro archar(30)
删除列 drop;
ALTER TABLE 表名 DROP 列名;
修改表名：rename table d表名 to 新表名;
将学生表 student 改名成 student2
rename table student to student2;
修改字符集 character set:ALTER TABLE 表名 character set 字符集;
将 student2 表的编码修改成 gbk: alter table student2 character set gbk
DML操作表中的数据；
INSERT [INTO] 表名 [字段名] VALUES (字段值
INSERT INTO 表名：表示往哪张表中添加数据
(字段名 1, 字段名 2, …)：要给哪些字段设置值
VALUES (值 1, 值 2)：设置具体的值
插入全部：
INSERT INTO 表名 (字段名 1, 字段名 2, 字段名 3…) VALUES (值 1, 值 2, 值3);
insert into student (id,name,age,sex) values ('孙悟空'20'男');
插入部分：INSERT INTO 表名 (字段名 1, 字段名 2, ...) VALUES (值 1, 值 2, ...);
修改数据: 不带条件修改：UPDATE 表名 SET 字段名=值; -- 修改所有的行
带条件修改数据:UPDATE 表名 SET 字段名=值 WHERE 字段名=值
带条件修改数据，将 id 号为 2 的学生性别改成男
update student set sex='男' where id=2;
删除数据: DELETE FROM 表名;
带条件删除数据：DELETE FROM 表名 WHERE 字段名=值;
不带条件删除数据,删除表中的所有数据：delete from student;
使用 truncate 删除表中所有记录:TRUNCATE TABLE 表名;
truncate 相当于删除表的结构，再创建一张表
DQL 查询表中的数据；
使用*表示所有列：SELECT * FROM 表名
查询指定列的数据,多个列之间以逗号分隔：SELECT 字段名 1, 字段名 2, 字段名 3, ... FROM 表名;
查询 student 表中的 name 和 age 列：select name,age from student;
对列指定别名：SELECT 字段名 1 AS 别名, 字段名 2 AS 别名... FROM 表名;
对列和表同时指定别名：SELECT 字段名 1 AS 别名, 字段名 2 AS 别名... FROM 表名 AS 表别名;
select st.name as 姓名,age as 年龄 from student as st
清除重复值：SELECT DISTINCT 字段名 FROM 表名;
select distinct address from student
查询结果参与运算：SELECT 列名 1 + 固定值 FROM 表名
SELECT 列名 1 列名 2 FROM 表名
select * from student;
-- 给所有的数学加 5 分
select math+5 from student;
-- 查询 math + english 的和select * from student;
select *,(math+english) as 总成绩 from student;-- as 可以省略
select *,(math+english) 总成绩 from student;
条件查询：SELECT 字段名 FROM 表名 WHERE 条件;
逻辑运算符：and \or\not
In 关键字：SELECT 字段名 FROM 表名 WHERE 字段 in (数据 1, 数据 2...)
in 里面的每个数据都会作为一次条件，只要满足条件的就会显示
范围查询：BETWEEN 值 1 AND 值 2
like 关键字：SELECT * FROM 表名 WHERE 字段名 LIKE '通配符字符串';
%匹配任意多个字符串； _ 匹配一个字符串
-- 查询姓名中包含'德'字的学生
select * from student3 where name like '%德%'
-- 查询姓马，且姓名有两个字的学生
select * from student3 where name like '马_';
MySQL约束与设计：
排序查询：SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 [ASC|DESC];
ASC:升序，默认值,DESC：降序
- 查询所有数据,使用年龄降序排序
select * from student order by age desc;
组合排序:SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 1 [ASC|DESC], 字段名 2 [ASC|DESC];
select * from student order by age desc, math asc
聚合函数:
SELECT COUNT(IFNULL(comm,0)) FROM emp; -- 求个数
SELECT MAX(SAL) FROM emp;
SELECT MIN(SAL) FROM emp;
SELECT SUM(sal) FROM emp;
SELECT AVG(sal) FROM emp;
分组查询：SELECT 字段 1,字段 2... FROM 表名 GROUP BY 分组字段 [HAVING 条件];
- 按性别进行分组，求男生和女生数学的平均分
select sex, avg(math) from student3 group by sex;
查询年龄大于 25 岁的人，按性别分组，统计每组的人数，并只显示性别人数大于 2 的数据
SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex having COUNT(*) >2;
分页查询：limit;
SELECT * FROM student LIMIT 0,3; -- 第1页
SELECT * FROM student LIMIT 3,3; -- 第2页
SELECT * FROM student LIMIT 6,3; -- 第3页
约束:
1. 主键约束：primary key
2. 非空约束：not null
3. 唯一约束：unique
4. 外键约束：foreign key
非空约束：not null，某一列的值不能为null
1. 创建表时添加约束
CREATE TABLE stu(
id INT,
NAME VARCHAR(20) NOT NULL -- name为非空
2. 创建表完后，添加非空约束 ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;
3. 删除name的非空约束 ALTER TABLE stu MODIFY NAME VARCHAR(20);
唯一约束：unique，某一列的值不能重复
1. 注意：* 唯一约束可以有NULL值，但是只能有一条记录为null
2. 在创建表时，添加唯一约束
CREATE TABLE stu(
id INT,
phone_number VARCHAR(20) UNIQUE -- 手机号
);
3. 删除唯一约束ALTER TABLE stu DROP INDEX phone_number;
4. 在表创建完后，添加唯一约束 ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;
主键约束：primary key。
1. 注意： 含义：非空且唯一. 一张表只能有一个字段为主键. 主键就是表中记录的唯一标识
2. 在创建表时，添加主键约束
create table stu(
id int primary key,-- 给id添加主键约束
name varchar(20)
);
3. 删除主键-- 错误 alter table stu modify id int ;
ALTER TABLE stu DROP PRIMARY KEY;
4. 创建完表后，添加主键
ALTER TABLE stu MODIFY id INT PRIMARY KEY;
5. 自动增长：
1. 概念：如果某一列是数值类型的，使用 auto_increment 可以来完成值得自动增长 2. 在创建表时，添加主键约束，并且完成主键自增长
create table stu(
id int primary key auto_increment,-- 给id添加主键约束
name varchar(20)
);
3. 删除自动增长
ALTER TABLE stu MODIFY id INT;
4. 添加自动增长
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
外键约束：
1.在创建表时，可以添加外键
2.* 语法：create table 表名(
....
外键列
constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称) );
2. 删除外键 ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
3. 创建表之后，添加外键 ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
Day03：多表查询
笛卡尔积：select * from emp,dept;
隐式内连接：SELECT 字段名 FROM 左表, 右表 WHERE 条件
例：-- 查询所有员工信息和对应的部门信息
SELECT * FROM emp,dept WHERE emp.`dept_id` = dept.`id`;
-- 查询员工表的名称，性别。部门表的名称
SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.`dept_id` = dept.`id`;
SELECT
t1.name, -- 员工表的姓名
t1.gender,-- 员工表的性别
t2.name -- 部门表的名称
FROM
emp t1,
dept t2
WHERE
t1.`dept_id` = t2.`id`;
显示内连接：SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件
例： SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`;（INNER可省略）;
外链接查询：
左外连接：SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件
SELECT t1.*,t2.`name` FROM emp t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.`id`;
右外链接：select 字段列表 from 表1 right [outer] join 表2 on 条件；
子查询：* 概念：查询中嵌套查询，称嵌套查询为子查询。
-- 1 查询最高的工资是多少：SELECT MAX(salary) FROM emp;
-- 2 查询员工信息，并且工资等于9000的SELECT * FROM emp WHERE emp.`salary` = 9000;
-- 一条sql就完成这个操作。子查询
SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp);
子查询结果的三种情况：
单行单列：*子查询可以作为条件，使用运算符去判断运算符：> >= < <= =
-- 查询员工工资小于平均工资的人
SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);
多行单列：* 子查询可以作为条件，使用运算符in来判断
-- 查询'财务部'和'市场部'所有的员工信息
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');
多行多列：* 子查询可以作为一张虚拟表参与查询
-- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2 WHERE t1.id = t2.dept_id;
事物：1. 概念：* 如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。
1.开启事务： start transaction;
2.回滚：rollback;
3.提交：commit;
例子：
CREATE TABLE account (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
balance DOUBLE
);
-- 添加数据
INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000);
SELECT * FROM account;
UPDATE account SET balance = 1000;
-- 张三给李四转账 500 元
-- 0. 开启事务
START TRANSACTION;
-- 1. 张三账户 -500
UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';
-- 2. 李四账户 +500
-- 出错了...
UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';
-- 发现执行没有问题，提交事务
COMMIT;
-- 发现出问题了，回滚事务
ROLLBACK;