精华内容
下载资源
问答
  • 数据库从入门到精通
    2021-12-04 19:31:52

    目录

    SQL基础

    1.常见命令

    2.数据类型

    3.创建约束

    4.对表的操作

    基础查询

    SQL运算符

    查询语句

    模糊查询

    常见函数

    练习实例

    高级查询

    聚合函数

    分组查询

    多表联查

    子查询

    合并查询

    分页查询

    SQL语句导入、导出数据库

    分析SQL语句执行性能

    实例

    socket(补课)

    反射

    数据库对象

    索引

    视图

    索引和视图代码

    事务

    数据库编程

    变量

    游标

    存储过程

    语句

    函数

    触发器

    JDBC操作数据库

    java数据库连接技术

    占位符

    批处理

    DBHelper

    DBinfo

    DBUtil

    javabean


    SQL基础

    1.常见命令

    cmd:
    
    mysql -u rooot -p
    
    net start mysql
    
    net stop mysql
    
    
    
    停止MySQL服务 net stop mysql80 启动MySQL服务 net start mysql80 登录 mysql -u root -p mysql -u root -p123456 mysql -h localhost -u root -p mysql --host=localhost --user=root --password=123456
    查看版本
     mysql -V;
    mysql --version;
    mysql> select version();
    查看帮助 mysql --help; 退出 mysql> quit; mysql> exit;
    
    创建数据库 create database [if not exists] <database name>; 查看现有数据库 show databases; 指定当前默认数据库 use <database name>; 查看当前数据库 select database();
    
    查看当前数据库所包含的表
    
    show tables;
    查看指定数据库所包含的表
        show tables from <database name>;
    n查看指定表的结构
        desc <table name>;
    n查看现有表的创建语句
        show create table <table name>;
    n查看表中的数据
        select * from <table name>;
           创建普通用户
    create user '用户名'@'主机地址' identified by '密码';
    create user 'frank'@'localhost' identified by '123456';
    查看用户权限 show grants; 查询当前登录用户权限 show grants for '用户名'@'主机'; 查询指定用户权限 例: show grants for 'frank'@'localhost';
    
      给用户授权
    grant 权限列表 on 库.表 to 用户名@主机地址;
    grant SELECT on db01.* to 'frank'@'localhost';
    回收用户权限 REVOKE 权限列表 ON 库.表 FROM 用户名@主机名; REVOKE SELECT ON db01.* FROM 'frank'@'localhost'; 备注:不要忘记 flush privileges
    
           给角色授权
    grant 权限列表 on 库.表 to 角色名;
    grant SELECT on db01.* to dev;
    回收角色权限 REVOKE 权限列表 ON 库.表 FROM 角色名; REVOKE SELECT ON db01.* FROM dev;
    
    给用户分配角色 grant 角色名 to 用户名@主机地址; grant dev to 'frank'@'localhost'; 回收用户角色 REVOKE 角色名 FROM 用户名@主机地址; REVOKE dev FROM 'frank'@'localhost';
    
    修改密码 ALTER USER '用户名'@'主机地址' IDENTIFIED BY '新密 码'; alter user 'frank'@'localhost' identified by '123'; 删除用户 DROP USER <用户名1> [ , <用户名2> ]… drop user 'frank'@'localhost';

    2.数据类型

    数字 int double(7,2)

    字符 char(10) varchar(10)(可变)

    日期 date(yyyy-MM-dd)

    datetime (yyyy-MM-dd HH:mm:ss)

    timestamp(yyyyMMddHHmmss)

    CHAR(n)定长字符串n字符个数,长度范围:0-255 VARCHAR(n)可变字符串n字符个数,长度范围:0-65535 TINYTEXT微型文本串0~28–1字节 TEXT文本串0~216–1字节 LONGBLOB超大文本最大4G

    3.创建约束

    两种:列级约束、表级约束

    create table student(

    stuid int primary key auto_increment,

    sname varchar(40) not null unique,

    birthday date,

    sex char(1) check(sex in('男','女')),

    cid int refenerces classes(classid)

    );

    添加非空约束 ALTER TABLE 表名称 MODIFY COLUMN 列名 列类型 NOT NULL; 添加唯一约束 ALTER TABLE 表名称 ADD UNIQUE(列名); ALTER TABLE 表名称 MODIFY COLUMN 列名 列类型 UNIQUE; ALTER TABLE 表名称 ADD CONSTRAINT 约束名 UNIQUE(列名); 添加主键约束 ALTER TABLE 表名称 ADD PRIMARY KEY(列名); ALTER TABLE 表名称 MODIFY COLUMN 列名 列类型 PRIMARY KEY; ALTER TABLE 表名称 ADD CONSTRAINT 约束名 PRIMARY KEY(列名);

    添加检查约束 ALTER TABLE 表名称 ADD CHECK(表达式); ALTER TABLE 表名称 ADD CONSTRAINT 约束名 CHECK(表达式); 添加默认值 ALTER TABLE 表名称 MODIFY COLUMN 列名 列类型 DEFAULT 默认值; 添加外键约束 ALTER TABLE 表名称 ADD CONSTRAINT 约束名 FOREIGN KEY(列名) REFERENCES 主表(列名);

    删除not null约束: alter table 表名 modify 列名 类型; 删除unique约束: alter table 表名 drop index 唯一约束名; 删除primary key约束: alter table 表名 drop primary key; 删除check约束: alter table 表名 drop check 检查约束名; 删除foreign key约束: alter table 表名 drop foreign key 外键约束名

    4.对表的操作

    增加新列 ALTER TABLE 表名 ADD 列名 列类型; 修改列类型 ALTER TABLE 表名 MODIFY 列名 列类型; 修改列名 ALTER TABLE 表名 RENAME COLUMN 旧列名 TO 新列名; 删除列 ALTER TABLE 表名 DROP COLUMN 列名; 更改表名 ALTER TABLE 旧表名 RENAME TO 新表名; 删除表 DROP TABLE 表名;

    1. 添加一个字段 Alter table student add classId int(2);

    2. 修改字段的长度或类型 Alter table student modify name varchar(30);

    1. 修改字段的名字 Alter table student rename column name to sname;

    1. 删除一个字段 Alter table student drop column sal;

    1. 修改表的名字 Rename table student to stu; Alter table student rename to stu;

    1. 删除表 Drop table stu;

    2. 插入数据insert

    (1)所有字段都插入: INSERT INTO 表名 VALUES(value1,value2,value3...); (2)插入部分字段: INSERT INTO 表名 (column1,column2,column3,...) VALUES (value1,value2,value3,...); 注意:1> 插入空值可以用null,默认值用default。 2> 插入日期类型数据时,按照日期对应格式的字符形式插入 (2)一次性插入多行记录: INSERT INTO 表名 (column1,column2,column3,...) VALUES (value1,value2,value3,...), (value1,value2,value3,...), (value1,value2,value3,...),…;

    1. 修改数据update

      UPDATE 表名 SET column1=value1,column2=value2,... [WHERE 条件];

    2. 删除数据delete

      DELETE FROM 表名 [WHERE 条件];

      TRUNCATE TABLE 表名 ;

    基础查询

    *代表所有列

    SQL运算符

    查询语句

    消除重复行

    select distinct job from emp;

    加在要查询的列之前

    别名

    select sal*12 年薪 from emp 空格加别名即可改

    为别名

    select(sal + ifnull(comm,0))*12 年薪 from emp

    如果comm提成为0则为0

    排序

    order by

    无标注默认升序

    使用order by子句进行排序

    升序 asc 降序des

    select * from emp order by sal desc;

    多列排序

    按照第一个类进行排序。如果第一个列内容相同,则按照第二个列排序

    每一个列都可以单独指定排序方式

    select * from emp order by sal desc,ename;

    按照别名排序

    select ename 员工姓名,(sal + if null(comm,0))*12 年薪 from order by 年薪 desc

    --复制一张表,目标表不存在

    create table emp1 as select * from emp;

    select *from emp1;

    delete form emp1;

    --复制一张表,目标表存在

    insert into emp1 select * from emp where sal>2000;

    查询数据的列要好原表的列匹配

    --union

    insert into emp1 select * from emp where sal >= 3000 union

    select * from emp where job = 'MANAGER';

    模糊查询

    使用通配符进行模糊查询

    — 下划线匹配一个字符

    %   匹配任意个字符

    -- 查询名字以S开头的员工信息

    select * from emp where ename Like'S%';

    -- 查询名字第三位为M的员工信息

    select * from emp where ename Like'_ _M%';

    -- 查询名字中有M的员工信息

    select * from emp where ename Like'%M%';

    正则匹配

    --正则匹配 regexp 或 rlike

    -- 查询名字为四个字母的员工

    select * from emp where ename regexp '[A-Z]{4}$';

    -- 查询名字以M开头的员工信息

    select * from emp where ename rlike '^M';

    in

    --查询这四个人的信息

    select * from emp where ename in ('SMITH','SCOTT','FORD','KING');

    between

    区间 between A and B

    select * from emp where sal between 1100 and 3000;

    常见函数

    字符函数

    lower(str):将字符串转化为小写的格式。

    upper(str):将字符串转化为大写的格式。

    length(str)/char_length(str):返回字符串的长度。

    substr(str,pos,len):截取字符串的子串。pos 起始位置,从1开始,可以为负数;len 截取长度

    substr('hello',-3,3) from dual; 截取后三位

    substring(str,pos,len) ,mid(str,pos,len):同substr

    replace(str,oldstr,newstr):替换字符串

    select replace('hexxo worxd', 'x', 'l'); -- hello world

    insert(str,pos,len,newstr) :替换指定位置的字符串

    select insert('这是SQL Server数据库',3,10,'MySQL');

    concat(str1,str2,…,strn) :连接字符串

    select concat('Hello',' MySQL!'); -- Hello MySQL!

    space(n) :返回n个空格组成的字符串

    instr(str,substr):取子串在字符串第一次出现的位置

    select instr('hello world', 'llo'); -- 3

    format(x,d):获取指定小数位的数字,四舍五入

    select format(12332.12345,3); -- 12,332.123

    trim(str):获取str去掉左右两边空格的副本

    select concat('',trim(' abc '), ''); -- abc

    数学函数

    --mod(n) 求模

    select 11 mod 4;

    select mod(11,4);

    select 11%4;

    --floor(n)

    向下取整

    --ceil(n)

    向上取整

    select ceil (-2.567);等于负二

    --rand() 返回0-1之间的随机数

    --获取20-50之间的随机整数

    select floor(30*rand() + 20);

    -- 随机乱序

    select * from emp order by rand();

    pow(x,y)、power(x,y) x的y次幂

    round(x[,d]) 保留d为小数,四舍五入取整

    d 小数位数,可以省略

    select round(3.1415926)

    truncate(x,[,d])保留d位小数,舍掉其余部分

    select truncate(3.1415926,4);

    日期函数

    now()、sysdate()、 current_timestamp() :返回当前系统时间

    curdate()、current_date():返回当前日期(年月日)

    curtime()、current_time():返回当前时间(时分秒)

    adddate(date,days|interval expr unit) :时间运算

    select adddate(now(),11); select adddate(now(),interval 2 month );

    date_add(date,interval expr unit) :时间运算

    datediff(date1,date2) :两个日期的差值,只计算日期部分

    date(date)/ time(date) :提取日期/时间部分

    week(date) :返回date为一年中的第几周

    day(date)、dayofmonth(date) :date为一个月中的第几天

    dayofweek(date):返回date为星期几

    last_day(date):返回date所在月份的最后一天

    转换函数

    在某些情况下,MySQL允许值的数据类型和实际不一样,这时MySQL会隐含的转换数据类型。

    如:create table test1(tid int, tname varchar(10) );

    insert into test1 values('10', 9); -----会将'10'-->10, 9—>'9'

    n为了提高程序的可靠性,我们应该使用转换函数。

    date_format(date,format):将时间转换为指定格式的字符串

    例: select date_format(now(),'%Y-%m-%d %H:%i:%s');

    str_to_date(str,format):将字符串转换成datetime类型

    例:str_to_date('2020-11-27 13:34:43', '%Y-%m-%d %T')

    注意:str要和format的格式保持一致,否则,结果为null

    cast(expr as type):数据类型转换,expr可以为任意数据类型

    type的值: binary,char,date,datetime,decimal,json

    select cast(123.42 as char);

    select cast('2021-05-28 12:13:16' as datetime);

    n例1:时间显示出时分秒。

    sql>select ename, date_format(hiredate, '%Y-%m-%d %T') from emp;

    n例2:显示1981年入职的所有员工

    select * from emp where date_format(hiredate,'%Y')=1981;

    n例3:显示所有12月份入职的员工

    select * from emp where date_format(hiredate,'%m')=12;

    条件判断函数

    IF(expr,v1,v2)

    如果expr为TRUE,则返回v1,否则,返回v2

    IFNULL(v1,v2)

    如果v1不为NULL,则返回v1,否则返回v2

    系统函数

    VERSION():查看MySQL版本号

    CONNECTION_ID():查看当前用户的连接数

    USER()、CURRENT_USER()、SYSTEM_USER()、SESSION_USER() :查看当前被MySQL服务器验证的用户名和主机的组合,一般这几个函数的返回值是相同的

    CHARSET(str):查看字符串str使用的字符集

    加密函数

    MD5(str): MD5加密,返回32位十六进制的数字字符串

    SHA(str):为字符串计算出一个数字消息所对应的,长度为40的字符串

    (又称消息摘要)

    练习实例

    -- 找出部门10中所有经理、部门20中所有办事员,-- 既不是经理又不是办事员但其薪金>=2000的所有雇员的详细资料select * from emp where (deptno=10 and job='MANAGER')                
    or (deptno=20 and job='CLERK')                     
    or (job!='MANAGER' and job<>'CLERK' and sal>=2000);
    select * from emp where (deptno=10 and job='MANAGER')                     
    or (deptno=20 and job='CLERK')                     
    or (job not in ('MANAGER','CLERK') and sal>=2000);

    高级查询

    聚合函数

    --max(column) min(column)

    select max(sal) 最高薪资,min(sal) 最低薪资 from emp;

    COUNT(*) 表中行的数量

    COUNT(column) 列不为null的行数

    COUNT(distinct column) 指定列中相异值的数量

    sum(column) 求和

    avg(column) 求平均

    round(avg(sal),2)代表求平均薪水,保留两位小数

    除了COUNT()以外,聚合函数都会忽略null值

    select sum(comm) from emp;

    select min(comm) from emp;

    分组查询

    group by

    select cid,round(avg(score),1) 平均成绩 from stuscore group by cid;

    按cid分组排序平均成绩3

    分组后继续筛选:

    having

    --having 在分组之后进行条件筛选

    select cid,round(avg(score),1) 平均成绩 from stuscore

    group by cid having 平均成绩 >70;

    select cid,round(avg(score),1) 平均成绩 from stuscore

    where score > = 60 group by cid having 平均成绩 >70;

    结算: 先 where再group by 再 having

    select cid,round(avg(score),1){第3步} 平均成绩 from stuscore{1}

    where score > = 60 {2}group by cid {4} having 平均成绩 >70{5};

    where是筛选原表的数据 group by 分组 having 在分组基础之上进行筛选,

    如果没有分组就不会有having

    多表联查

    内联结(INNER JOIN) 外联结 ——左外联结(LEFT JOIN) ——右外联结(RIGHT JOIN) ——全外联结(LEFT JOIN UNION RIGHT JOIN) 交叉联结(CROSS JOIN),笛卡尔集

    SELECT S.SName,C.CourseID,C.Score From Score C INNER JOIN Students S ON C.StudentID = S.SCode SELECT S.SName, C.CourseID, C.Score FROM Students S,Score C WHERE S.SCode = C.StudentID

    多表联查时,如果没有给表起别名,使用表名.列名

    如果给表起来别名,必须使用别名.列名,否则会出错

    -- 两表联查-- 方法1:select s.sname,s.stuid,sc.* from student s    inner join stuscore sc on s.stuid=sc.stuid;
    -- 方法2:select s.sname,sc.* from student s,stuscore sc    where s.stuid=sc.stuid;
    -- 三表联查 student、course、stuscore
    -- 方法1:select s.sname,c.cname,sc.score from student s    inner join stuscore sc on s.stuid=sc.stuid    inner join course c on sc.cid=c.cid;
    -- 方法2:select s.sname,c.cname,sc.score from student s,course c,stuscore sc    where sc.stuid=s.stuid and sc.cid=c.cid;
    select * from student;select * from stuscore;
    -- 左外连接select s.sname,sc.* from student s left outer join stuscore sc on s.stuid=sc.stuid;
    -- 右外连接select s.sname,sc.* from student s right join stuscore sc on s.stuid=sc.stuid;
    -- 全外连接select s.sname,sc.* from student s left outer join stuscore sc on s.stuid=sc.stuidunionselect s.sname,sc.* from student s right join stuscore sc on s.stuid=sc.stuid;

    子查询

    指嵌入在其他sql语句中的其他select语句,也叫嵌套查询

    查询薪资比SCOTT低的员工信息

    select*from emp where sal<

    (select sal from emp where ename = 'SCOTT');

    /--采用IN子查询参加考试的学员名单--/ SELECT stuName FROM stuInfo WHERE stuNo IN (SELECT stuNo FROM stuMarks) GO

    子查询的结果为一行一列,当作一个数值

    子查询的结果为一行多列,用小括号括起来,当作一个整体

    子查询的结果为一列多行,使用关键字in

    子查询的结果为多行多列,当作一张新表

    合并查询

    union

    合并去重

    union all

    合并不去重

    有时在实际应用中,为了合并多个select语句的结果,可以使 用集合操作符号union, union all。 union 该操作符用于取得两个结果集的并集。当使用该操作 符时,会自动去掉结果集中重复行。 例:select ename, sal, job from emp where sal>2500 union select ename, sal, job from emp where job='MANAGER'; union all 该操作符与union相似,但是它不会取消重复行, 而且不会排序。 例:select ename, sal, job from emp where sal>2500 union all select ename, sal, job from emp where job='MANAGER';

    分页查询

    start 从0开始

    分页查询(limit) 语法:select * from emp limit start_row,row_count; start_row: 起始行,从0开始 row_count: 记录数 例: select * from emp limit 6; -- emp表前6行 select * from emp limit 6,10; -- emp表7-16行

    -- 两表联查-- 方法1:select s.sname,s.stuid,sc.* from student s    inner join stuscore sc on s.stuid=sc.stuid;-- 方法2:select s.sname,sc.* from student s,stuscore sc    where s.stuid=sc.stuid;-- 三表联查 student、course、stuscore-- 方法1:select s.sname,c.cname,sc.score from student s    inner join stuscore sc on s.stuid=sc.stuid    inner join course c on sc.cid=c.cid;
    -- 方法2:select s.sname,c.cname,sc.score from student s,course c,stuscore sc    where sc.stuid=s.stuid and sc.cid=c.cid;
    select * from student;select * from stuscore;-- 左外连接select s.sname,sc.* from student s left outer join stuscore sc on s.stuid=sc.stuid;-- 右外连接select s.sname,sc.* from student s right join stuscore sc on s.stuid=sc.stuid;-- 全外连接select s.sname,sc.* from student s left outer join stuscore sc on s.stuid=sc.stuidunionselect s.sname,sc.* from student s right join stuscore sc on s.stuid=sc.stuid;

    SQL语句导入、导出数据库

    导出数据库 语法:select 列名 from 表名 into outfile 文件路径; 例:select * from emp into outfile 'D:/Program Files/mysql- 8.0.25-winx64/uploads/a.sql'; 导入数据库 语法:load data infile 文件路径 into table 表名; 例: load data infile 'D:/Program Files/mysql-8.0.25- winx64/uploads/emp.sql' into table emp1; 注意:导入导出路径必须为系统变量secure_file_priv指定的路径, 查询该路径使用:show variables like '%secure_file_priv%'。

    分析SQL语句执行性能

    查询表的详细信息 语法:explain 表名; 查看SQL语句的执行计划 语法:explain select 语句; id: 选择标识符 select_type: 表示查询的类型。 table: 输出结果集的表 partitions: 匹配的分区 type: 表示表的连接类型 possible_keys: 表示查询时,可能使用的索引 key: 表示实际使用的索引 key_len: 索引字段的长度 ref: 列与索引的比较 rows: 扫描出的行数(估算的行数) filtered: 按表条件过滤的行百分比 extra:执行情况的描述和说明

    实例

    -- 求平均薪水的等级最低的部门名称
    select dname from dept where deptno=
        (select e.deptno from salgrade sg,
        (select deptno,round(avg(sal),2) avgsal from emp
            group by deptno) e
        where e.avgsal between sg.losal and sg.hisal
        order by sg.grade limit 1);
    select * from team1;
    create table team1 as select min(tid) teamid from team group by tname;
    delete from team where tid not in (select teamid from team1);

    数据库对象

    TABLE表是存储数据的逻辑单元,以行和列的形式存在
    DICTIONARY数据字典就是系统表,存放数据库相关信息的表
    INDEX索引用于提高查询性能,相当于书的目录
    VIEW视图以不同的侧面反映表的数据,是一种逻辑上的表
    CONSTRAINT约束执行数据校验的规则,用于保证数据完整性的规则
    PROCEDURE存储过程用于完成一次完整的业务处理,没有返回值,但可通过传出参数将多个值传给调用环境
    FUNCTION函数用于完成一次特定的计算,具有一个返回值
    TRIGGER触发器相当于一个事件监听器,当数据库发生特定事件后,触发器被触发,完成相应的处理

    索引

    关键字index,目的是加快查询速度

    索引是关系数据库中用于存放每一条记录的一种对象,主要目的是加快数据的读取速度,合理的使用索引可以大大降低i/o次数,从而提高数据访问性能。但是并不是索引越多越好,只是针对大数据表和经常查询的字段进行设置,否则会影响其性能。

    1、 类似书的目录结构

    2、 MySQL 的“索引”对象,用来提高SQL查询语句的速度

    3、 索引直接指向包含所查询值的行的位置,减少磁盘I/O

    4、 索引和表是相互独立的物理结构

    5、 MySQL 自动使用并维护索引,插入、删除、更新表后,自动更新索引

    6、对表进行insert,update,delete处理时,由于要将表的存放位置记录到索引项中而会降低一些速度。

    7、MySQL中的索引存储类型有两种:BTREE、HASH,MySQL中的索引大多数为BTREE类型。

    创建索引不需要特定的系统权限,建立索引的语法如下:

    CREATE [ UNIQUE | FULLTEXT | … ] INDEX 索引名

    ON 表名(列名1[,列名2,...]);

    其中:

    UNIQUE代表创建惟一索引。4

    FULLTEXT 代表创建全文索引。

    列名是创建索引的关键字列,可以是一列或多列。

    索引有很多种,我们主要介绍以下几种:

    普通索引

    1)普通索引:这是最基本的索引类型,它没有唯一性之类的限制

    创建索引:

    create index 索引名 on 表名(列名);

    修改表时添加索引:

    alter table 表名 add index 索引名(列名);

    创建表时添加索引:

    create table 表名 (…,index 索引名);

    唯一索引

    2)唯一索引:和普通索引基本相同,有一个区别,索引的列的所有值必须唯一

    创建索引:

    create unique index 索引名 on 表名(列名);

    修改表时添加索引:

    alter table 表名 add unique index 索引名(列名);

    创建表时添加索引:

    create table 表名 (…,unique index 索引名);

    主键索引

    3)主键索引:是一种特殊的唯一索引,一般在创建表时指定

    语法:

    create table 表名(…,primary key (列名));

    注:

    1. 在MySQL中,当建立主键时,主键索引同时也已经建立起来了,不必重复设置。

    2. 一张表只能有一个主键,即也只能有一个主键索引。

    也可以通过修改表的方式加入主键索引:

    语法:alter table 表名 add primary key(列);

    全文索引

    4)全文索引:全文索引只限于char、varchar和text类型的列上创建

    创建索引:

    create fulltext index 索引名 on 表名(列名);

    修改表时添加索引:

    alter table 表名 add fulltext index 索引名(列名);

    创建表时添加索引:

    create table 表名 (…,fulltext [index] 索引名);

    组合索引

    5)组合索引:也叫多列索引,就是含有多个列字段的索引

    创建索引:

    create index 索引名 on 表名(列名1,列名2,…);

    修改表时添加索引:

    alter table 表名 add index 索引名(列名1,列名2,…);

    创建表时添加索引:

    create table 表名 (…,index 索引名);

    注:多列索引只有在where条件中含有索引中的首列字段时才有效。

    使用索引

    查看索引:

    show index | keys from 表名;

    删除和禁用索引:

    删除索引:drop index 索引名 on 表名;

    alter table 表名 drop index 索引名;

    删除主键索引:alter table 表名 drop primary key;

    禁用索引:alter table 表名 disable keys;

    打开索引: alter table 表名 enable keys;

    缺点分析

    索引缺点分析

    索引有一些先天不足:

    1:建立索引,系统要占用硬盘和内存空间来保存索引。

    2:更新数据的时候,系统必须要有额外的时间来同时对索引进行更新,以维持数据和索引的一致性。实践表明,不恰当的索引不但于事无补,反而会降低系统性能。因为大量的索引在进行插入、修改和删除操作时比没有索引花费更多的系统时间。

    在如下字段建立索引应该是不恰当的:

    1、很少或从不引用的字段;

    2、逻辑型的字段,如男或女(是或否)等。综上所述,提高查询效率是以消耗一定的系统资源为代价的,索引不能盲目的建立,这是考验一个DBA是否优秀的很重要的指标

    视图

    视图是一个虚拟表,其内容由查询定义。

    同真实的表一样,视图包含一系列带有名称的列和行数据。但是,视图并不在数据库中以存储的数据值集形式存在。行和列数据来自于定义视图的查询所引用的表,并且在引用视图时动态生成。

    视图和表的区别

    表需要占用磁盘空间,视图不需要;

    视图不能添加索引

    使用视图可以简化复杂查询

    视图有利于提高安全性,比如不同用户查看不同的视图

    创建视图

    create [or replace] view 视图名 as select 语句

    注:创建视图需要有create view的权限,并且对于查询涉及的列有select权限。

    修改视图

    alter view 视图名 as select 语句

    删除视图

    drop view [if exists] 视图名

    查询视图

    select * from 视图名

    显示所有视图

    show tables

    显示视图结构

    desc 视图名

    显示视图详细信息

    show create view 视图名

    重命名视图

    rename table 视图名 to 新视图名

    索引和视图代码

    select deptno,round(avg(sal),2) avgsal from emp
        group by deptno having avgsal>2000;
    select e.*,d.dname from emp e
        inner join dept d on e.deptno=d.deptno;
    select e.*,d.dname from emp e,dept d
        where e.deptno=d.deptno;
    delete from emp1;
    insert into emp1 select * from emp;
    select * from emp1;
    ​
    ​
    -- 表索引
    -- 查看表索引
    show keys from emp1;
    -- 1. 给job添加普通索引
    create index job_index on emp1(job);
    -- 2. 给ename添加唯一索引
    -- 某个列添加了唯一约束,系统会自动创建唯一索引
    -- 如果该列没有唯一约束,可以单独添加唯一索引
    create unique index ename_index on emp1(ename);
    ALTER TABLE emp1 ADD UNIQUE(ename);
    -- drop index job_index on emp1;
    -- 3. 主键索引
    -- 给列添加主键约束(primary key)时,系统会自动创建主键索引,不必重复设置
    -- 4. 全文索引(fulltext),只适用于char、varchar、text类型的列
    create fulltext index job_fulltext on emp1(job);
    -- 5. 组合索引,一次性给多个列添加索引
    ​
    -- 添加索引时,如何选择具体哪一个索引
    -- 1. 该列是主键,自动会添加主键索引
    -- 2. 如果该列值必须唯一,考虑添加唯一索引
    -- 3. 如果该列是字符类型,考虑添加全文索引
    -- 4. 添加普通索引或组合索引
    ​
    select count(*) from manydata;
    show keys from manydata;
    select * from manydata;
    create index empno_index on manydata(empno);
    select * from manydata where empno=99999; -- 125ms
    ​
    -- view(视图)
    -- 创建视图,各个部门的平均薪资
    create or replace view view_avgsal as
        select deptno,round(avg(sal),2) avgsal from emp group by deptno;
    -- 可以像普通表一样查询视图
    select * from view_avgsal;
    -- 修改视图,修改视图对应的sql语句
    alter view view_avgsal as select * from emp;
    show tables ;
    -- 修改视图名
    rename table view_avgsal to avgsal;
    select * from avgsal;

    事务

    什么是事务

    事务是用于保证数据的一致性,它由一组相关的DML语句组成,该组的DML语句要么全部执行成功,要么都不执行。在 MySQL 中只有使用了innodb数据库引擎的数据库或表才支持事务。

    如:网上转账就是典型的要用事务来处理,用以保证数据的一致性。

    事务特性

    事务有4个特性,通常称为ACID特性:

    原子性(atomicity):一个事务中包含的所有SQL语句是一个不可分割的单元,要么都做,要么都不做。

    一致性(consistency):事务开始时,数据库中的数据是一致的,事务结束时,数据也是一致的。

    隔离性(isolation):多个并发的事务可以独立运行,而不能相互干扰。一个事务修改的数据在未提交前,其它事务看不到它所做的更改。

    持久性(durability):事务提交后,数据库的变化就会永久的保留下来。

    数据库管理系统采用重执行日志来保证原子性、一致性和持久性。

    数据库管理系统采用数据库锁机制来实现隔离性

    隔离级别

    隔离级别脏读(Dirty Read)不可重复读(NonRepeatableRead)
    未提交读(Read uncommitted)可能可能可能
    已提交读(Read committed)不可能可能可能
    可重复读(Repeatable read)不可能不可能可能
    可串行化(Serializable )不可能不可能不可能

    InnoDB默认是可重复读级别的:

    脏读:脏读就是指当一个事务对数据进行了修改,而这种修改还没有提交到数据库中,这时另外一个事务也访问这个数据,然后使用了这个数据。

    不可重复读:是指在一个事务内,多次读同一数据。在一个事务多次读取某数据时,同时另外一个事务修改该数据。那么第一个事务两次读数据之间,由于第二个事务的修改,两次读到的数据可能是不一样的,因此称为是不可重复读。

    幻读:第一个事务对一个表中的数据进行修改,这种修改涉及到表中的全部行。同时第二个事务向表中插入一行新数据。那么,就会发生操作第一个事务的用户发现表中还有没被修改的数据行,就好象产生了幻觉一样,幻读是数据行记录变多了或者少了。

    脏读是指读取了未修改完的记录,不可重复读指因为被其它事务修改了记录导致某事务两次读取记录不一致,而幻读是指因为其它事务对表做了增删导致某事务两次读取的表记录数不一致问题

    事务操作

    提交事务:commit

    使用commit语句可以提交事务,MySQL默认事务是自动提交的

    set autocommit = 1(true); 默认自动提交事务

    set autocommit = 0(false); 手动提交事务

    保存点:

    在事务的任何地方都可以设置保存点,可以将修改保存到保存点处。这样可以防止保存点后的误操作。

    如:savepoint sp1;

    回退事务:

    当执行rollback时,通过指定保存点可以回退到指定的点。

    (若执行了commit,则无法回退)

    如:rollback to sp1; --回退到保存点sp1

    rollback 全部回滚

    设置事务隔离级别

    SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL <isolation-level>

    其中的<isolation-level>可以是:

    – READ UNCOMMITTED

    – READ COMMITTED

    – REPEATABLE READ

    – SERIALIZABLE

    例:SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

    隔离级别的作用范围

    – 全局级(global):对所有的会话有效

    – 会话级(session):只对当前的会话有效

    例:SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

    开启事务

    start transaction;

    查看当前会话隔离级别

    select @@**session.transaction_isolation;

    查看全局隔离级别

    select @@global.transaction_isolation;

    查看当前用户的隔离级别

    select @@transaction_isolation;

    事务和锁

    当执行事务操作时,MySQL会在被作用的表上加锁,防止其它用户改表的结构。

    锁是防止访问同一资源的事务之间进行相互破坏的机制。

    锁用于保护将要被或正在被修改的数据。当某个用户正在修改某个表,则拒绝其他用户修改该表,直到提交或回滚了事务之后,其他用户才可以更新数据。

    锁的分类:

    MySQL分为三类:全局锁、表级锁和行级锁。

    全局锁

    全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法,命令是 flush tables with read lock;

    全局锁的典型使用场景是,做全库逻辑备份。也就是把整库每个表都 select 出来存成文本

    表级锁

    MySQL 里面表级别的锁有两种:一种是表锁,一种是元数据锁。

    1. 表锁,表锁的语法: lock tables 表名 read/write,可以用 unlock tables 主动释放锁,也可以在客户端断开的时候自动释放

    2. 元数据锁(meta data lock,MDL)MDL 不需要显式添加,系统默认会添加。当对一个表做DML操作的时候,加 MDL 读锁;对表做DDL操作的时候,加 MDL 写锁,读锁之间不互斥,读写和写写之间都互斥。

    行级锁

    MySQL 行锁是在引擎层由各个引擎自己实现的, MyISAM 引擎不支持行锁,InnoDB 支持行锁。在innodb事务中,行锁是在需要的时候才加上的,但并不是不需要了就立刻释放,而是要等到事务结束时才释放,这个就是两阶段锁协议。

    死锁

    当两个事务相互等待对方释放资源时,就会形成死锁。

    MySQL有两种死锁处理方式:

    1. 等待,直到超时(innodb_lock_wait_timeout=50s)。

    2. 发起死锁检测,主动回滚一条事务,让其他事务继续执行(innodb_deadlock_detect=on)。

    数据库编程

    变量

    局部变量

    用户变量

    全局变量

    会话变量

    会话变量和全局变量统称为系统变量

    局部变量必须先声明再使用

    用户变量无需声明

    -- 变量
    -- 1. 局部变量
    -- 定义在存储过程、函数、触发器的begin/end语句块之间
    -- declare v_name varchar(15) default '';
    -- 2. 用户变量
    -- 不需要声明,直接使用
    -- ① set @num=1 或 set @num:=1
    -- ② select @num:=18 或 select @num:=列 from 表名
    -- 注意:select可以直接查询变量的值
    -- set可以使用=或:=,但是select必须使用:=赋值
    set @num=8;
    set @num:=10;
    select @num:=100;
    select @num:=sal from emp where ename='SCOTT';
    select @num;
    -- 3. 全局变量
    -- 4. 会话变量
    show variables like '%autocommit%';
    set @@autocommit=0;
    set global autocommit = 1;
    set @@autocommit = 1;

    全局变量和会话变量的区别

    对全局变量的修改会影响到整个服务器,但是对会话变量的修改,只会影响到当前的会话(也就是当前的数据库连接)

    游标

    游标使用户可逐行访问SQL返回的结果集

    使用游标的主要原因就是它可以把集合操作转换成单个记录处理方式

    将表中数据导入到设置的变量中

    -- 游标
    -- 1. 用游标提取emp表中7788雇员的名称和职务
    create procedure findempbyempno()
    begin
        -- 编码部分
        -- 定义变量
        declare v_name varchar(15);
        declare v_job varchar(10);
        -- 声明游标
        declare cur_emp cursor for select ename,job
            from emp where empno=7788;
        -- 打开游标
        open cur_emp;
        -- 提取数据
        fetch cur_emp into v_name,v_job;
        select concat('员工姓名:',v_name,',工作:',v_job);
        -- 关闭游标
        close cur_emp;
    end;

    存储过程

    存储过程的创建

    create procedure <存储过程名>[(parameter list)]

    begin 和end里面只能用局部变量,之外只能用用户变量

    语句

    赋值语句

    1. SET

      SET 变量名 = 表达式;

      SET 变量名 := 表达式;

      SET 变量名1 = 表达式1,变量名2 = 表达式2,…;

    2. SELECT

      SELECT 表达式 INTO 变量 FROM 表名; -- 不产生结果集

      SELECT 会话变量 := 表达式; -- 产生结果集

      SELECT 会话变量 :=列名 FROM 表名; -- 产生结果集

    BEGIN-END语句块

    BEGIN

    -- 要执行的功能代码

    END;

    注:将功能封装到存储过程、函数、触发器等存储程序内部。

    条件控制语句

    1. IF语句:

      IF 条件 THEN 语句块;

      ELSE IF 条件 THEN 语句块;

      ……

      ELSE 语句块;

      END IF;

    2. CASE语句:

      CASE 表达式

    WHEN 值1 THEN 语句块;

    WHEN 值2 THEN 语句块;

    ……

    ELSE 语句块;

    END CASE;

    循环语句

    1. WHILE-DO循环:

      WHILE 条件表达式 DO -- 继续循环的条件

    循环体;

    END WHILE;

    1. REPEAT-UNTIL循环:

      REPEAT

    循环体;

    UNTIL 条件表达式 END REPEAT; -- 结束循环的条件

    1. LOOP循环:

      循环标签:LOOP

    循环体;

    IF 条件表达式 THEN LEAVE 循环标签; -- 跳出循环的条件

    END IF;

    END LOOP;

    1. LEAVE和ITERATE

      LEAVE类似于Java中的break,

      ITERATE类似于Java中的continue。

    使用游标

    1. 声明游标

    DECLARE cursor_name CURSOR FOR SELECT 语句

    1. 打开游标

    OPEN cursor_name

    1. 提取数据

    FETCH cursor_name INTO var1[,var2,…]

    1. 关闭游标

    CLOSE cursor_name

    例1:用游标提取emp表中7788雇员的名称和职务。

    create procedure findempinfo()

    begin* * declare v_name varchar(15); declare v_job varchar(10); -- 声明游标 * declare cur_emp1 cursor for select ename,job from emp where empno=7788; * open cur_emp1; -- *打开游标 * fetch cur_emp1 into v_name,v_job; -- 提取数据 select v_name,v_job; close cur_emp1; -- 关闭**游标

    end;

    call findempinfo(); -- 调用存储过程

    存储过程类似方法

    函数

    存储过程和函数区别

    1.函数必须有声明返回值类型,存储过程没有

    2.函数必须返回一个和声明类型一致的值,存储过程无需返回任何值

    3.调用方式不同,函数使用select,存储过程使用call进行调用

    4.存储过程的参数有in、out、inout三种模式,函数一般只有in

    触发器

    触发器是一种特殊的存储过程,当特定对象上的特定时间出现时,将自

    动触发执行的代码块,触发器比过程有更精细和更复杂的数据控制能力

    应用

    -- 触发器 trigger
    -- 同一张表,同样的触发时间和触发事件最好只写一个触发器
    -- 在student表上建立触发器,在向表student插入记录时触发,
    -- 将时间和学生信息存入logs表中。
    create trigger tri_student after inserton student for each rowbegin    
    -- 自动给logs表添加一条数据    
    -- new 表示即将要添加的行    
    insert into logs(msg, createtime) values(new.sname,now());end;
    -- 触发触发器
    insert into student values(6,'二狗子','女');insert into student values(7,'张三','男'),(8,'李四','男'),(9,'王麻子','男');
    select * from logs;select * from student;
    -- 限制更改的薪资不能小于原来的薪资
    create trigger tri_update_sal before updateon emp1 for each rowbegin    
    -- new、old    
    -- new和old对应唯一的行    
    -- 即将要修改的薪资 new.sal    
    -- 原来的薪资  用new.empno查询的薪资    
    -- declare v_sal decimal(7,2);    
    -- select sal into v_sal from emp1 where empno=new.empno;    
    -- 判断即将要要求的薪资是否小于原来的薪资    
    if new.sal<old.sal then        
    -- 发起系统异常        
    signal sqlstate '45008' set message_text = '黑心老板!不允许降薪';    end if;end;drop trigger tri_update_sal;
    -- 限制每个部门下的员工数量不能超过5位
    create trigger tri_deptcount before inserton emp1 for each rowbegin    declare v_count int default 0;    -- 查询即将要添加数据的部门的人数    
    select count(*) into v_count from emp1 where deptno=new.deptno;    
    -- 如果该部门人数大于等于5,发起系统异常    
    if v_count>=5 then        signal sqlstate '45002' set message_text = '该部门已满员,出门右拐!';    end if;end;
    create trigger tri_deptcount2 before inserton emp1 for each rowbegin    signal sqlstate '45003' set message_text = '我是新的触发器!';end;

    管理触发器

    JDBC操作数据库

    Java DataBase Connectivity

    BeanListHandler是将ResultSet中左右的数据转化成List

    List中存放的是对象,里面装的是查询的结果

    QueryRunner能进行增删改

    qr.update

    javabean是数据模型,将数据库中数据导入java中

    java数据库连接技术

     public static void main1(String[] args) throws ClassNotFoundException, SQLException {
            // jdbc连接mysql数据库,对表做CRUD(增删改查)
            // 全查emp表
            // 1. 加载驱动类
            // 8.0以下 com.mysql.jdbc.Driver
            // 8.0及以上 com.mysql.cj.jdbc.Driver
             Class.forName("com.mysql.jdbc.Driver");
            // 2. 获取mysql数据库的连接   !!!orcl为数据库名,看情况修改为要查表所在的数据库
            String url = "jdbc:mysql://localhost:3306/orcl?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC";
            Connection conn = DriverManager.getConnection(url, "root", "123456");
            // 3. 准备sql语句
            String sql = "select * from emp where sal>2000";
            PreparedStatement ps = conn.prepareStatement(sql);
            // 4. 执行sql语句
            // 查询 executeQuery()  返回ResultSet 结果集
            // 增删改 executeUpdate() 返回int
            ResultSet rs = ps.executeQuery();
            // 5. 遍历rs,取出数据
            while(rs.next()){
                // 每循环一次,取出一行数据
                System.out.println("**************************************");
                System.out.println("员工编号:" + rs.getInt("empno"));
                System.out.println("员工姓名:" + rs.getString("ename"));
                System.out.println("员工工作:" + rs.getString("job"));
                System.out.println("上级编号:" + rs.getInt("mgr"));
                System.out.println("入职日期:" + rs.getDate("hiredate"));
                System.out.println("员工薪资:" + rs.getDouble("sal"));
                System.out.println("员工提成:" + rs.getDouble("comm"));
                System.out.println("部门编号:" + rs.getInt("deptno"));
            }
    ​
    ​
    ​
        }
    }

    加载驱动类不是必需的

    数据模型java bean/pojo

    占位符

    public static void main2(String[] args) throws SQLException {
        // 修改emp1
        String url = "jdbc:mysql://localhost:3306/orcl?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC";
        Connection conn = DriverManager.getConnection(url, "root", "123456");
        double sal = 3200;
        int empno = 7369;
        String job = "MANAGER";
        // String sql = "update emp1 set sal="+sal+",job='"+job+"' where empno=" + empno;
        // ? 占位符
        String sql = "update emp1 set sal=?,job=? where empno=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        // 在sql语句执行之前,设置占位符的值
        ps.setDouble(1, 3500);
        ps.setString(2, "MANAGER");
        ps.setInt(3, 7369);
        int updateRows = ps.executeUpdate();
        System.out.println("修改了" + updateRows + "行数据");
    }

    批处理

    public static void main45(String[] args) throws SQLException {
        // 批处理
        // jdbc默认为自动提交
        // 一次性给emp1添加10条数据
        String url = "jdbc:mysql://localhost:3306/orcl?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC";
        Connection conn = DriverManager.getConnection(url, "root", "123456");
        // 设置提交方式为手动提交
        conn.setAutoCommit(false);
        String sql = "insert into emp1 values(?,?,?,?,?,?,?,?)";
        int[] empnos = {9527,9528,9529,9530,9531,9532,9533,9534,9535,9536};
        String[] names = {"华安","华文","华武","刘德华","周杰伦","吴京","李晨","胡军","朱亚文","易烊千玺"};
        String[] jobs = {"CLERK","MANAGER","SALESMAN","ANALYST"};
        int[] deptnos = {10,20,30,40};
        PreparedStatement ps = conn.prepareStatement(sql);
        // 循环添加10条数据
        for (int i = 0; i < 10; i++) {
            ps.setObject(1, empnos[i]);
            ps.setObject(2, names[i]);
            ps.setObject(3, jobs[(int)Math.floor(Math.random()*jobs.length)]);
            ps.setObject(4, 7788);
            ps.setObject(5, new Date());
            ps.setObject(6, 1000);
            ps.setObject(7, 0);
            ps.setObject(8, deptnos[(int)Math.floor(Math.random()*deptnos.length)]);
    ​
            // 添加批处理
            ps.addBatch();
            System.out.println(i);
        }
        // 一次性执行10条语句
        ps.executeBatch();
        // 手动提交
        conn.commit();
    }

    DBHelper

    public class DBHelper {
        // 增删改查
        // emp表的所有查询
        public List<Employee> findEmp(String sql,Object... args){
            Connection conn = null;
            PreparedStatement ps = null;
            ResultSet rs = null;
            List<Employee> emps = new ArrayList<>();
            try {
                conn = DBUtil.getConn();
                ps = conn.prepareStatement(sql);
                for(int i=0;i<args.length;i++){
                   ps.setObject(i+1, args[i]);
                }
                rs = ps.executeQuery();
                while(rs.next()){
                    Employee emp = new Employee();
                    emp.setEmpno(rs.getInt("empno"));
                    emp.setEname(rs.getString("ename"));
                    emp.setJob(rs.getString("job"));
                    emp.setMgr(rs.getInt("mgr"));
                    emp.setHiredate(rs.getDate("hiredate"));
                    emp.setSal(rs.getDouble("sal"));
                    emp.setComm(rs.getDouble("comm"));
                    emp.setDeptno(rs.getInt("deptno"));
    ​
                    // 将emp对象放进集合
                    emps.add(emp);
                }
                return emps;
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                DBUtil.close(conn, ps, rs);
            }
            return null;
        }
    ​
        // 所有表的增删改
        // update emp1 set sal=?,job=? where empno=?
        public int updateTable(String sql,Object... args){
            Connection conn = null;
            PreparedStatement ps = null;
            try{
                conn = DBUtil.getConn();
                ps = conn.prepareStatement(sql);
                // 在执行之前设置占位符的值
                for(int i=0;i<args.length;i++){
                    ps.setObject(i+1,args[i]);
                }
                return ps.executeUpdate();
            }catch (SQLException e){
                e.printStackTrace();
            }finally {
                DBUtil.close(conn,ps,null);
            }
            return 0;
        }
    ​
        public static void main(String[] args) {
            DBHelper helper = new DBHelper();
    //        String sql = "select * from emp";
    //        System.out.println(helper.findEmp(sql));
            String sql = "update emp1 set sal=?,job=? where empno=?";
            helper.updateTable(sql,4100,"CLERK",7369);
        }
    ​
    ​
    ​
    }

    DBinfo

    public interface DBInfo {
    ​
        // 数据库连接信息
        public String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
        public String JDBC_URL = "jdbc:mysql://localhost:3306/orcl?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC";
        public String JDBC_USERNAME = "root";
        public String JDBC_PASSWORD = "123456";
    }

    DBUtil

    public class DBUtil {
    ​
        // 获取数据库连接
        public static Connection getConn(){
            try {
                Class.forName(DBInfo.JDBC_DRIVER);
                return DriverManager.getConnection(DBInfo.JDBC_URL,DBInfo.JDBC_USERNAME,DBInfo.JDBC_PASSWORD);
            } catch (Exception e) {
                e.printStackTrace();
            }
            return null;
        }
    ​
        // 关流
        public static void close(Connection conn, PreparedStatement ps, ResultSet rs){
            try {
                if(null!=rs){
                    rs.close();
                }
                if(null!=ps){
                    ps.close();
                }
                if(null!=conn && !conn.isClosed()){
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    javabean

    public class Employee {
    ​
        private Integer empno;
        private String ename;
        private String job;
        private Integer mgr;
        private Date hiredate;
        private Double sal;
        private Double comm;
        private Integer deptno;
    ​
        public Integer getEmpno() {
            return empno;
        }
    ​
        public void setEmpno(Integer empno) {
            this.empno = empno;
        }
    ​
        public String getEname() {
            return ename;
        }
    ​
        public void setEname(String ename) {
            this.ename = ename;
        }
    ​
        public String getJob() {
            return job;
        }
    ​
        public void setJob(String job) {
            this.job = job;
        }
    ​
        public Integer getMgr() {
            return mgr;
        }
    ​
        public void setMgr(Integer mgr) {
            this.mgr = mgr;
        }
    ​
        public Date getHiredate() {
            return hiredate;
        }
    ​
        public void setHiredate(Date hiredate) {
            this.hiredate = hiredate;
        }
    ​
        public Double getSal() {
            return sal;
        }
    ​
        public void setSal(Double sal) {
            this.sal = sal;
        }
    ​
        public Double getComm() {
            return comm;
        }
    ​
        public void setComm(Double comm) {
            this.comm = comm;
        }
    ​
        public Integer getDeptno() {
            return deptno;
        }
    ​
        public void setDeptno(Integer deptno) {
            this.deptno = deptno;
        }
    ​
        @Override
        public String toString() {
            return "Employee{" +
                    "empno=" + empno +
                    ", ename='" + ename + '\'' +
                    ", job='" + job + '\'' +
                    ", mgr=" + mgr +
                    ", hiredate=" + hiredate +
                    ", sal=" + sal +
                    ", comm=" + comm +
                    ", deptno=" + deptno +
                    '}';
        }
    }
    更多相关内容
  • 详细介绍了oracle数据库表空间的创建存储过程、游标的使用等内容,通过阅读这篇文档能够让各位对oracle数据库有个深入的理解和掌握
  • HP-Vertica数据库从入门到精通. 作 者: 李中原 创 作 日 期: 2014年07月24日 最 近 修 改 日 期: 2015年01月08日
  • 惠普的数据库Vertica还是很不错,这是少有的教程,网上Vertica的资料比较少,只能看这个他们的内部文档了
  • 《MySQL数据库从入门到精通》是千锋教育根据市场人才需求编著的数据库初级、中级、高级的书籍,适合各个学习层次的IT人员。数据库和表的基本操作选自《MySQL数据库从入门到精通》第二章,项目分析明确,知识点完整,...
  • 数据库从入门到精通01

    万次阅读 多人点赞 2021-04-27 23:41:57
    #数据库应用 ##1.1 概念 1 ###1.1.1 什么是数据库 1 ###1.1.2 关系型和非关系型 13 ###1.1.3 关系型数据库 14 ##1.2 Mysql工具安装 14 ###1.2.1 MySQL数据存放在哪里? 14 ###1.2.2 MySQL服务端 15 ###1.2.3 MySQL...

    数据库应用

    概念

    什么是数据库

    简而言之,就是存储数据,管理数据的仓库。

    常见的数据库分为:

    • 关系型数据库, Oracle、MySQL、SQLServer、Access
    • 非关系型数据库, MongoDB、Redis、Solr、ElasticSearch、Hive、HBase
      在这里插入图片描述

    关系型和非关系型

    早期发展的数据库建立在数据的紧密关系基础之上(如:父子关系、师生关系),我们称其为关系型数据库,也称为传统数据库;现今数据库建立在数据的松散关系基础之上(如:中国人和美国人、中国人和印度人、视频、音频),我们称其为非关系型数据库nosql(not only sql)。业界总在争论nosql能否干掉传统数据库,很多初学者也有这个困惑。以我来看,两者没有矛盾,它们各有特点,根据业务情况互补才是真谛。但总的来说原来关系型数据库一统天下的格局早被打破,领土不断被蚕食,规模一再的缩小,虽然无法全面被替代,但却早已风光不在,沦落到一偶之地,Oracle的衰落就是最好的证明,早期只要是全球大企业无一例外都是部署Oracle,但现在都在去Oracle化,阿里就已经全面排斥Oracle。
    在这里插入图片描述

    既然干不掉,很多传统项目的还是围绕关系型数据库的居多,所以我们先来学习关系型数据库,目前最流行的关系型数据库是MySQL。

    关系型数据库

    关系型数据库有特定的组织方式,其以行和列的形式存储数据,以便于用户理解。关系型数据库这一系列的行和列被称为表,一组表组成了数据库。用户通过查询来检索数据库中的数据,而查询是一个用于限定数据库中某些区域的执行代码。关系模型可以简单理解为二维表格模型,而一个关系型数据库就是由二维表及其之间的关系组成的一个数据集合。
    在这里插入图片描述

    Mysql数据库

    1. mysql服务端,它来处理具体数据维护,保存磁盘
    2. mysql客户端,CRUD新增,修改,删除,查询

    MySQL数据存放在哪里?

    在MySQL的配置文件my.ini中会进行默认配置

    在这里插入图片描述

    MySQL服务端

    mysql-5.5.27-winx64.msi

    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述
    Mysql数据库默认的编码是latin1等价于iso-8859-1,修改为utf-8

    在这里插入图片描述
    在这里插入图片描述
    注意:配置完,mysql开始执行,最后一步出错有时仍可以使用,使用SQLyog工具测试,如不行,再执行安装程序,选择remove,删除,然后重新安装。同时注意必须是管理员权限。

    MySQL客户端1:DOS窗口

    mysql -uroot -proot

    语法:mysql.exe执行文件

    代表参数

    -u 用户名,紧接着写的

    -p 密码,紧接着写的

    MySQL客户端2:可视化工具

    在这里插入图片描述

    数据库的结构

    数据库结构

    在这里插入图片描述

    SQL语句

    定义

    结构化查询语言(Structured Query Language)简称SQL(发音:/ˈes kjuː ˈel/ "S-Q-L"),是一种特殊目的的编程语言,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统;同时也是数据库脚本文件的扩展名。

    SQL 是1986年10 月由美国国家标准局ANSI)通过的数据库语言美国标准,接着,国际标准化组织(ISO)颁布了SQL正式国际标准。

    分类

    • DML(Data Manipulation Language)数据操纵语言

    如:insert,delete,update,select(插入、删除、修改、检索)简称CRUD操新增Create、查询Retrieve、修改Update、删除Delete

    • DDL(Data Definition Language)数据库定义语言

    如:create table之类

    • DCL(Data Control Language)数据库控制语言

    如:grant、deny、revoke等,只有管理员才有相应的权限

    • DQL(Data Query Language)数据库查询语言
      如: select 语法

    注意:SQL不区分大小写

    数据库常用操作

    建库

    • 创建数据库,数据库名称:cgb2022
    create database cgb2022 DEFAULT CHARACTER SET utf8;
    

    删库

    • 删除名称是cgb2022的数据库
    drop database cgb2022;
    

    查看所有数据库

    • 查看所有数据库
    show databases;
    

    表的常用操作

    使用数据库:use cgb2022;
    

    表设计

    门店表:** tb_door**
    在这里插入图片描述
    订单详情表:** tb_order_detail**
    在这里插入图片描述

    创建表

    • 创建tb_door表,有id,door_name,tel字段
    create table tb_door(
    id int primary key auto_increment,
    door_name varchar(100),
    tel varchar(50)
    );
    

    修改表

    • 添加列
    alter table tb_door add column money NUMERIC(7,2)
    

    删除表

    • 删除名称是tb_door的表
    drop table tb_door;
    

    查看所有表

    • 查看所有表
    show tables;
    

    查看表结构/设计表

    • 查看tb_door表结构
    desc tb_door;
    

    表记录的常用操作

    插入记录

    • 向tb_door表中插入2条记录
    insert into tb_door values(null,'永和大王1店',666);
    
    insert into tb_door values(null,' 永和大王2店',888);
    

    查询记录

    • 查询tb_door表中的所有记录
    SELECT * FROM tb_door;
    

    修改记录

    • 修改tb_door表中id为1的记录
    update tb_door set tel=555 where id=1;
    

    删除记录

    • 删除tb_door表中id为2的数据
    Delete from tb_door where id=2;
    

    排序

    • 将tb_door表记录按照tel排序
    Select * from tb_door order by tel desc;
    

    记录总数

    • 查询tb_door表中的总记录数
    Select count(*) from tb_door;
    

    数据类型

    命名规则

    • 字段名必须以字母开头,尽量不要使用拼音
    • 长度不能超过30个字符(不同数据库,不同版本会有不同)
    • 不能使用SQL的保留字,如where,order,group
    • 只能使用如下字符az、AZ、0~9、$ 等
    • Oracle习惯全大写:USER_NAME,mysql习惯全小写:user_name
    • 多个单词用下划线隔开,而非java语言的驼峰规则

    字符

    • char长度固定,不足使用空格填充,最多容纳2000个字符,char(11)存储abc,占11位。查询速度极快但浪费空间
    • varchar变长字符串,最多容纳4000个字符,varchar(11)存储abc,只占3位。查询稍慢,但节省空间。Oracle为varchar2
    • 大文本: 大量文字(不推荐使用,尽量使用varchar替代)

    以utf8编码计算的话,一个汉字在u8下占3个字节

    注:不同数据库版本长度限制可能会有不同

    数字

    • tinyint,int整数类型
    • float,double小数类型
    • numeric(5,2) decimal(5,2)—也可以表示小数,表示总共5位,其中可以有两位小数
    • decimal和numeric表示精确的整数数字

    日期

    • date 包含年月日
    • time时分秒
    • datetime包含年月日和时分秒
    • timestamp时间戳,不是日期,而是从1970年1月1日到指定日期的毫秒数

    图片

    • blob 二进制数据,可以存放图片、声音,容量4g。早期有这样的设计。但其缺点非常明显,数据库庞大,备份缓慢,这些内容去备份多份价值不大。同时数据库迁移时过大,迁移时间过久。所以目前主流都不会直接存储这样的数据,而只存储其访问路径,文件则存放在磁盘上。

    准备数据

    部门表 dept

    字段名称数据类型是否为空备注
    deptnoint部门编号,PK主键
    dnamevarchar(20)Y部门名称
    locvarchar(13)Y部门所在地点
    CREATE TABLE dept(
    deptno int primary key auto_increment ,
    dname VARCHAR(20),
    loc VARCHAR(13)
    );
    INSERT INTO dept VALUES(null,'accounting','一区');
    INSERT INTO dept VALUES(null,'research','二区');
    INSERT INTO dept VALUES(null,'operations','二区');
    

    员工表 emp

    字段名称数据类型是否为空备注
    empnoint员工编号,PK主键
    enamevarchar(10)Y员工名称
    jobvarchar(10)Y职位
    mgrintY上级编号
    hiredatedatetimeY入职时间
    saldoubleY月工资
    commNUMERIC(8,2)Y奖金
    deptnointY所属部门 FK外键

    Mysql:

    CREATE TABLE emp(
    empno int primary key auto_increment,
    ename VARCHAR(10),
    job VARCHAR(10),
    mgr int,
    hiredate DATE,
    sal double,
    comm NUMERIC(7,2),
    deptno int
    );
    INSERT INTO emp VALUES(100,'jack','副总',NULL,'2002-05-1',90000,NULL,1);
    INSERT INTO emp VALUES(200,'tony','总监',100,'2015-02-02',10000,2000,2);
    INSERT INTO emp VALUES(300,'hana','经理',200,'2017-02-02',8000,1000,2);
    INSERT INTO emp VALUES(400,'leo','员工',300,'2019-02-22',3000,200.12,2);
    INSERT INTO emp VALUES(500,'liu','员工',300,'2019-03-19',3500,200.58,2);
    

    字段约束

    主键约束

    主键约束:如果为一个列添加了主键约束,那么这个列就是主键,主键的特点是唯一且不能为空。通常情况下,每张表都会有主键。

    添加主键约束,例如将id设置为主键:

    主键自增策略** **当主键为数值类型时,为了方便维护,可以设置主键自增策略(auto_increment),设置了主键自增策略后,数据库会在表中保存一个AUTO_INCREMENT变量值,初始值为1,当需要id值,不需要我们指定值,由数据库负责从AUTO_INCREMENT获取一个id值,作为主键值插入到表中。而且每次用完AUTO_INCREMENT值,都会自增1. AUTO_INCREMENT=1

    create table abc(
    id int primary key auto_increment
    );
    insert into abc values(null);
    insert into abc values(null);
    insert into abc values(null);
    select * from abc;
    

    非空约束

    非空约束:如果为一个列添加了非空约束,那么这个列的值就不能为空,但可以重复。

    添加非空约束,例如为password添加非空约束:

    create table user(
    id int primary key auto_increment,
    password varchar(50) not null
    );
    show tables;
    insert into user values(null,null);//不符合非空约束
    insert into user values(null,123;);//OK
    

    唯一约束

    唯一约束:如果为一个列添加了唯一约束,那么这个列的值就必须是唯一的(即不能重复),但可以为空。

    添加唯一约束,例如为username添加唯一约束及非空约束:

    create table test(
    id int primary key auto_increment,
    username varchar(50) unique--唯一约束
    );
    show tables;
    insert into test values(null,'lisi');
    insert into test values(null,'lisi');--username的值要唯一,重复会报错的
    select * from test;
    
    展开全文
  • Oracle数据库从入门到精通,高清PDF版本,适合零基础入门学习。
  • Oracle学习过程中做的总结,由浅入深,一看就懂,对初学者很友好,可以打印出来随时参考,当然还是要多练
  • MySQL数据库应用从入门到精通 第2版;MySQL数据库应用从入门到精通 第2版
  • MySQL数据库从入门到精通实战教程

    千人学习 2020-02-03 14:19:52
    MySQL数据库从入门到精通实战教程,本课程使用豆瓣电视剧频道作为教学案例,可以让零基础学员快速的掌握MySQL数据库,顺利进入Java学习通道。同时本课程也是Java初级工程师必备科目。
  • 小可爱们,接下来我们要学习的知识是数据库相关的知识,本贴开始,0基础带大家入门到精通,要加油哦~ 1 前言 1.1 为什么要学习数据库? 那我们首先要搞清楚第一个问题:为什么要学数据库? 原因1: 我们现在浏览...

    小可爱们,接下来我们要学习的知识是数据库相关的知识,从本贴开始,从0基础带大家入门到精通,要加油哦~

    1 前言

    1.1 为什么要学习数据库?

    那我们首先要搞清楚第一个问题:为什么要学数据库?
    原因1: 我们现在浏览或使用的各种各样的网页、应用、软件系统等等,都会涉及到很多很多的数据。
    比如:要刷一会抖音,那就会产生很多的数据,比如你的账户信息、浏览信息、点赞、评论、转发、收藏等等,而这些数据就需要进行保存与管理,方便程序员对这些数据进行进一步的处理。
    原因2:我们现在的学习肯定是为了后续的就业或技术提升的需求,大家不妨点开招聘网站的具体需求,可以发现,不管是初级还是中级、高级程序员,都是需要熟练掌握数据库技术的,所以这也是我们很重要的一块敲门砖。所以我们需要熟练掌握数据库的知识,后续我也会给大家出一个数据库面试题的帖子。

    1.2 什么是数据库?怎么学?

    古代如果需要存放粮食,就把粮食存放在粮仓中,所以“粮仓”就是存放粮食的仓库。
    而数据库,顾名思义,就是存放数据的“仓库”,方便我们能更好的存储与管理数据。
    在学习数据库相关的知识之前,你首先需要安装好一个数据库,才能对数据库做操作。

    我们通过SQL来操作数据库,比如可以进行数据的添加、查询、更新与删除。
    SQL语言掌握了我们就可以达到数据库的入门级要求,也就是可以完成实际开发中大部分的业务开发工作。
    后续我们还会进一步深入到数据库的事务、存储引擎、索引、SQL优化、锁等知识,对应的是中级工作。
    再往后还有一些日志管理、主从复制、分库分表、读写分离等高级知识。
    可能这段话对于初入门的你有一些难度,但没关系,后面这些我们都会展开来为大家讲解。

    所以我们可以把数据库的知识分为3大部分,逐步深入学习,争取早日成为一个数据库高手~
    在这里插入图片描述

    2 MySQL入门篇-MySQL相关的概念

    1. 什么是数据库?

    数据库其实就是存储数据的仓库,对数据进行有组织的存储,它对应的单词是DataBase,我们一般简称为DB。

    2. 什么是数据库管理系统?

    数据库中已经保存了数据,那我们就需要进一步对数据库中的数据进行操作和管理,这个就需要对应的软件来协助完成,对应的单词是DataBase Management System,我们简称为DBMS,其实大家也不用感觉很难,其实这就是个软件,后面介绍的时候就会知道它其实是很简单的。
    TIPS:比如我们现在学习的MySQL数据库就是一个数据库管理系统DBMS,只不过程序员间为了方便,把MySQL数据库管理系统简称为MySQL数据库。

    3. 什么是SQL?

    为了方便操作数据库,我们定义了一套操作关系型数据库的标准编程语言,它对应的单词是Structured Query Language(结构化查询语言),我们一般称之为SQL。
    我们就可以通过SQL来操作数据库管理系统,再通过数据库管理系统来操作数据库中的数据。
    TIPS:这里提到的“关系型”一会来解释,现在可以先跳过

    4.常用的关系型数据库管理系统有哪些?

    下图为在DB-Engines.com的数据库排行:
    我们这里要学习的数据库就是MySQL,大家也不用担心,这么多的数据库我没学过怎么办,其实对于关系型数据库而言,我们都是通过SQL来操作数据库的,SQL语句就是为了操作数据库而提供的统一标准,大同小异,上手很快。
    在这里插入图片描述

    以下内容不作重点,感兴趣的可以简单了解一下:

    1. Oracle是甲骨文公司的一个大型收费的数据库,而且软件与后续的服务收费比较昂贵
    2. MySQL早期是属于sun公司的一个免费的数据库,后来sun公司又被Oracle公司收购了,所以现在MySQL也属于甲骨文公司
      甲骨文公司收购MySQL后对其进行了升级,产生了收费版本的MySQL,当然也提供免费的社区版本的MySQL.
    3. SQL Server是微软公司研发的一个中型数据库,也是收费的
    4. PostgreSQL是一个中小型的开源免费的数据库
    5. MongoDB是一个由C++语言编写的分布式文件存储数据库,介于关系型数据库与非关系数据库之间
    6. IBM Db2 是一个收费的数据库
    7. Redis是一个跨平台的非关系数据库,采用的是Key-Value存储系统
    8. SQLite嵌入式的微型数据库,安卓内置的数据库选择的就是它
    下一节在这里
    展开全文
  • 1、本课程全方面讲解了mysql数据库的知识,让使用mysql或者对mysql感兴趣的同学有进一步的提升; 2、由行业大牛精心整理资料,课程讲解详细; 重点内容:sql语法、存储过程、存储函数、视图、 触发器、sql注入等
  • MongoDB数据库从入门到精通

    千人学习 2019-08-07 15:57:44
    本课程是MongoDB数据库的精讲课程,课程环境搭建开始详细讲述了MongoDB的各个方面,内容涵盖:Mongo shell、数据库CRUD、聚合、索引、数据库模型、MongoDB Java开发和MongoDB Python开发。
  • 第 章 数据表 数据类型 数据表基础 表逻辑设计 表的创建CREATE TABLE 表结构的修改ALTER TABLE 表的删除截断与重命名 创建与删除数据库 数据表也被称为表或基本表是数据库最基本的用于存储数据的对象可以认为关 系...
  • 为了杜绝高校毕业生求职时常常因技术经验不足而被拒之门外的现象,由千锋教研院高教产品研发部编著、清华大学出版社出版的《MySQL数据库从入门到精通》重磅问世。 通俗易懂,注重实战经验 《MySQL数据库从入门到...

    众所周知,每年就业市场都会迎来千万量级的高校毕业生,然而企业招工难和毕业生就业难的矛盾却一直没有得到很好地解决。究其原因,主要矛盾还是在于传统的学历教育与企业实际需求相脱节。为了杜绝高校毕业生求职时常常因技术经验不足而被拒之门外的现象,由千锋教研院高教产品研发部编著、清华大学出版社出版的《MySQL数据库从入门到精通》重磅问世。

    通俗易懂,注重实战经验

    《MySQL数据库从入门到精通》一书,从初学者的角度出发,通过通俗的语言、丰富的案例详细讲解了MySQL开发应该掌握的各项技术。教材共分13章,内容囊括MySQL数据库的基础知识和高级进阶,最后一章安排了综合案例,有助于读者巩固所学知识。教材中所有知识点都结合具体实例进行讲解,对涉及的程序代码给出了详细解释,可以使读者轻松领会MySQL的精髓,快速掌握开发技能。

    《MySQL数据库从入门到精通》经过教研院高教产品研发部成员反复推敲琢磨,多名经验丰富的院校老师悉心指导,以及千锋教育500多名精英学员通过试读给出的宝贵意见,使得《MySQL数据库从入门到精通》一书更加趋于实战性。它即可作为高等院校本、专科计算机相关专业的MySQL数据库入门教材,也适合广大编程爱好者自学参考。

    不断创新,打造教材精品

    因为出版的教材内容新颖,实用性强,已经被近百所高校选择和使用,受到高校老师和学生的一致好评。弥补大学教材实战型知识的不足,扩展大学生知识面,更加契合企业高薪用人技能需求,让初学者从书本中获益。

    此次出版的《MySQL数据库从入门到精通》,延续了以往教材生动、实用的特点,倡导“快乐学习,实战就业”,让初学者能够快速成长为初级程序员,并拥有一定的项目开发经验,从而在职场中拥有一个坚实的起点。

    转载于:https://www.cnblogs.com/coffees/p/11200133.html

    展开全文
  • 第 章 数据表 数据类型 数据表基础 表逻辑设计 表的创建CREATE TABLE 表结构的修改ALTER TABLE 表的删除截断与重命名 创建与删除数据库 数据表也被称为表或基本表是数据库最基本的用于存储数据的对象可以认为关 系...
  • 完整版 MySQL8.0从入门到精通 MySQL数据库教程 第01章 初始MySQL(共19页).ppt 完整版 MySQL8.0从入门到精通 MySQL数据库教程 第02章 MySQL的安装与配置(共14页).ppt 完整版 MySQL8.0从入门到精通 MySQL数据库...
  • 完整版 MySQL8.0从入门到精通 MySQL数据库教程 第01章 初始MySQL(共19页).ppt 完整版 MySQL8.0从入门到精通 MySQL数据库教程 第02章 MySQL的安装与配置(共14页).ppt 完整版 MySQL8.0从入门到精通 MySQL数据库...
  • 1.mysql 备份数据 2.Mysql 更新数据 3.MySQL常用操作命令 4.MySQL的数据类型和建库策略详解 5.MySQL多表操作和备份处理 6.MySQL索引分类和各自用途 7.MySQL中的字符串比较函数 8.MySQL中字符串函数详细介绍 ...
  • 分为是10个章节,介绍access从入门到精深
  • sql 从入门到精通

    2015-11-22 17:13:02
    sql 从入门到精通 SQL 的诞生于 IBM 公司在加利福尼亚 San Jose 的试验室中 在七十年代 SQL 由这里 开发出来 最初它们被称为结构化查询语言 Structured Query Language 并常常简称为 sequel 开始时它们是为 IBM 公司...
  • Oracle从入门到精通

    2021-07-14 20:58:03
    想要快速学习Oracle的伙伴可以免费下载学习。自己整合的。包括连接,子查询,分页,视图,序列,同义词,索引,PLSQL等等。通俗易懂,一看就会,同时也适合复习。
  • MYSQL基础学习资料,高清PDF版本,内容详细,理论讲解加实例操作演示
  • Redis入门到精通最新教学视频!!!!!!!!!!!!!!!!!!!
  • SQL Server从入门到精通
  • 教程名称: 【动力节点】Oracle从入门到精通视频教程_数据库实战精讲本套Java视频中讲解了Oracle数据库基础、搭建Oracle数据库环境、SQL*Plus命令行工具的使用、标准SQL、Oracle数据核心-表空间、Oracle数据库常用...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 47,876
精华内容 19,150
关键字:

数据库从入门到精通

友情链接: i2c.rar