黑马程序员技术交流社区

标题: 数据库基础学习笔记 [打印本页]

作者: 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;























欢迎光临 黑马程序员技术交流社区 (http://bbs.itheima.com/) 黑马程序员IT技术论坛 X3.2