WHCSRL 技术网

java学习笔记(MySQL)

1.数据库添加/删除/查询:

    1)展示数据库的内容:

show databases ;

     2)创建数据库:

create database + 库名;

    3)删除库:

drop database +库名;

      4)查询创建数据的默认字符集格式:

show create database+库名;

     5)默认查询当前数据库中所有的带字符集的字符集格式.

show variables like '%%character%%';   

2.数据库表添加/删除/修改:

      1) 创建表的前提:

use +库名; 创建表的前提,先选择库,进入到库中.

       2)查询这个库中的所有的表:

show  tables;

      3)查询表的结构:

desc 表名;(可以看到表的字段以及字段的类型).

       4)创建表:

  1. create table 表名(
  2. -> 字段 字段类型 ,
  3. -> ;字段 字段类型.
  4. -> );

     5)修改表的字段名称:

alter table 表名  change 旧字段名称 新字段名称 数据类型(旧字段的);

     6)修改字段类型:

alter table 表名 modify 字段名称 新的字段类型;

    7)添加一个新的列(新的字段):

alter table 表名 add 新的字段名称 类型(alter table student add socre double(3,1););

    8)删除某一个字段:

alter table 表名 drop 字段名称;

    9)复制一张新的表跟以前的表结构相同:

create table 新的表名 like 以前的表名;

     10)修改的表的名称:

alter table 以前的表名 rename to 新的表名;

      11)删除表:

  1. drop table 表名 ;
  2. drop table if exists 表名;

3.数据库的DML语句:

      1)一次性插入全部字段值:

  1. insert into 表名 values(字段名称1的值,字段名称2的值,....) ;
  2. 一次性插入全部字段值以及多行信息(注意按照顺序)

      2)一次性插入部分字段值:

  1. insert into 表名(字段名称1,字段名称2...) values(值1,值2,值3....) ;
  2. 注意:按照顺序.

      3)修改字段:

update 表名 set 字段名称1 =1,  ......  等:后期都是where id = 值 ;

      4)批量修改字段值:

update 表名 set 字段名称 = 值;

       5)删除语句整行:

delete  from 表名 where 带条件删除:DELETE FROM student WHERE id =  7 ;

        6)删除的是全表记录:

  1. delete  from 表名:删除的是全表记录(注意:仅仅只是删除表的记录, 
  2. 不会删除这个表的结构,那么针对自增长约束的字段不影响)
  3. truncate table 表名;删除的全部表记录,
  4. 自增长的主键约束(数据库约束)者这个语法它会将影响

      7)创建一张带一个自增长主键约束表:

  1. 创建表 create table 表名(   
  2. -> id int primary key auto_increment, ---id自增长住建唯一    
  3. -> 字段   字段类型  
  4. -> );

      8)查询全表(或指定的字段)内容:

  1. select * from 表名:
  2. select 全部字段名称(或指定的字段) from 表名:

       9)给表起了个别名:

  1. SELECT
  2. s.`id` '编号',
  3. s.`name` '姓名',
  4. s.`age` '年龄',
  5. s.`address` '地址'
  6. FROM
  7. student s; -- 给表起了个别名 s (as省略可以不写)

      10)数据库的DISTINCT(字段名称),可以实现字段去重:

  1. SELECT
  2. DISTINCT(address) -- 数据库的DISTINCT(字段名称),可以实现字段去重
  3. FROM student ;

       11)求和(字段内有空值null):

  1. FROM student ;
  2. -- 两个数据类型一致:int,求和 举例:英语和数学字段;
  3. -- 如果有一个字段的值位null(空值),没有意义,数学+NULL = NULL
  4. -- 数据库的函数 ifnull(字段名称,预期值) ;
  5. SELECT 
  6. id '学号'
  7. NAME '姓名'
  8. math+IFNULL(english,0'总分' 
  9. FROM 
  10. student ;

4.数据库的DML语句:

      1)关系运算符/逻辑运算符( > ,= ,!=):

  1. 第一种大于:
  2. SELECT
  3. id '编号',
  4. NAME '姓名',
  5. age '年龄',
  6. sex '性别',
  7. address '地址'
  8. FROM
  9. student
  10. WHERE
  11. age >20 ; 比较运算符 大于 等于 小于 不等于(!=20 /<>)

      2)逻辑或运算:

  1. 第一种:
  2. -- 查询年龄在20-30岁之间的学生信息
  3. -- (Java中逻辑符号呢?&& 可以,数据库and ,使用between 值1 and 值2)
  4. SELECT
  5. id,
  6. NAME,
  7. age,
  8. sex,
  9. address,
  10. math,
  11. english
  12. FROM
  13. student
  14. WHERE
  15. age>=20 && age<=30 ; Java中的逻辑符号 &&add
  16. 第二种:
  17. SELECT
  18. id,
  19. NAME,
  20. age,
  21. sex,
  22. address,
  23. math,
  24. english
  25. FROM
  26. student
  27. WHERE
  28. age BETWEEN 20 AND 30 ;

     3)查询某字段值为空或不为空的:

  1. 要查询英语成绩为null的学生信息
  2. SELECT
  3. id,
  4. NAME,
  5. age ,
  6. address,
  7. english
  8. FROM
  9. student
  10. WHERE
  11. english IS NULL ;
  12. 要查询英语成绩不为null的学生信息
  13. SELECT
  14. id,
  15. NAME,
  16. age,
  17. address,
  18. english
  19. FROM
  20. student
  21. WHERE
  22. english IS NOT NULL ;

      5)查询某字段的或值:

  1. -- 要查询年龄是18或者20,或者30的学生信息
  2. SELECT
  3. id ,
  4. NAME,
  5. age,
  6. sex,
  7. address
  8. FROM
  9. student
  10. WHERE
  11. age =18 OR age = 20 OR age = 30 ; -- || 或者 or

5.DQL语句:

      1)使用逻辑运算符查询

  1. where 条件后面跟表达式(关系运算符/逻辑运算符 ||(or) &&(and) between...and... )
  2. SELECT
  3. id ,
  4. NAME,
  5. sex,
  6. age,
  7. score
  8. FROM
  9. students
  10. WHERE
  11. age >= 20;
  12. age !=或(<>) 20 ;
  13. age>=20 && age<=30 ;
  14. age>=20 AND age<=30 ;
  15. age BETWEEN 20 AND 30 ; -- Java中的逻辑符号
  16. age =18 OR age = 20 OR age = 30 ; age IN(18,20,30) ; -- || 或者 or

       2)数据库中:某个字段名称 is null   /某个字段名称 is not null

  1. SELECT
  2. id,
  3. NAME,
  4. age ,
  5. address,
  6. english
  7. FROM
  8. student
  9. WHERE
  10. english IS NULL ;空
  11. english IS NOT NULL ;非空

6.模糊查询:

      1)查询某字段中包含特殊字:

  1. SELECT
  2. id,
  3. NAME,
  4. age,
  5. sex,
  6. address
  7. FROM
  8. student
  9. WHERE
  10. -- 查询学生姓名中包含马的学生
  11. NAME LIKE '%%马%%' ;
  12. -- 姓名中第二个字符是化的学生新'%%_化%%'
  13. NAME LIKE '%%_化%%' ;
  14. -- 姓名包含三个字符的人的信息
  15. NAME LIKE '___' ;

7.聚合函数:

    1)单行单列的数据

  1. count(字段名称) 查询总记录数
  2. count(字段名称:一般情况使用的非业务字段
  3. max(字段名称) 求某个列中的最大值
  4. min(字段名称) ...最小值
  5. sum(字段名称) 针对某个字段列表进行求和
  6. avg(字段):针对某个字段列求平均分
  7. SELECT
  8. COUNT(IFNULL(english,0)) '总记录数' -- ifnull(字段名称,预期值)
  9. COUNT(id) '总人数'
  10. MAX(math)
  11. MIN(IFNULL(english,0)) '最低分'
  12. AVG(math) '数学平均分'
  13. FROM
  14. student ;

     2)排序查询order by:

  1. 格式:
  2. select
  3. 字段列表
  4. from
  5. 表名
  6. order by
  7. 字段名称 排序方式;
  8. SELECT
  9. *
  10. FROM
  11. student
  12. ORDER BY
  13. math ; 不带排序方式:默认 asc
  14. 将英语成绩按照降序排序
  15. SELECT
  16. *
  17. FROM
  18. student
  19. ORDER BY
  20. english DESC ;

       3)分组查询 group by:

  1. SELECT
  2. sex '性别', 可以查询分组字段
  3. COUNT(id) '总人数',
  4. AVG(math) '数学平均分'
  5. FROM
  6. student
  7. GROUP BY
  8. sex ; 性别分组
  9. 1):数学成绩不大于70分的人不参与分组
  10. SELECT
  11. sex '性别', -- 可以查询分组的字段
  12. COUNT(id) '总人数',
  13. AVG(math) '数学平均分'
  14. FROM
  15. student
  16. where
  17. math > 70
  18. GROUP BY
  19. sex;
  20. 注意: 上面语法错误:正常是要先满足条件,在分组:如果sql语句中存在where语句以及group by
  21. 语句,先进行条件,然后再分组,group by 的后面不能where 语句,where 必须在group by之前;
  22. 筛选(过滤) having
  23. 按照性别分组,查询他们的数学的平均分以及总人数
  24. 1):数学成绩不大于70分的人不参与分组
  25. 2):筛选出总人数大于2的一组
  26. havinggroup by 的区别
  27. having后面可以使用聚合函数,而group by 的后面不能使用聚合函数查;
  28. 如果存在where 语句,group by,having,先where条件,然后分组group by ,
  29. 然后筛选having;
  30. SELECT
  31. sex '性别',
  32. AVG(math) '数学平均分',
  33. COUNT(id) '总人数'
  34. FROM
  35. student
  36. WHERE
  37. math > 70 条件
  38. GROUP BY
  39. sex 分组
  40. 筛选出总人数大于2的一组
  41. HAVING
  42. COUNT(id) > 2 ;

       4)分页查询 limit:

  1. select
  2. 字段列表
  3. from 表名 limit 起始行数(起始索引),每页显示的条数;
  4. 起始行数(起始索引):从0开始计算 = (当前页码-1)*每页显示的条数
  5. 查询当前学生表中第一页数据,每页显示2条数据;
  6. SELECT
  7. *
  8. FROM
  9. student
  10. LIMIT 0,2 ;
  11. 查询第二页的数据
  12. 起始行数= (2-1)*2
  13. .........

       5)数据库的约束:

  1. 约束:约束用户操作表时候的行为,防止无意义的数据在表中存在!
  2. 1. 默认约束 default
  3. CREATE TABLE stu_test(
  4. id INT ,
  5. NAME VARCHAR(20),
  6. gender VARCHAR(5) DEFAULT '女' -- 创建的时候给性别添加默认约束,default
  7. );
  8. 1)举例: 如果插入部分字段的时候,没有插入的字段就是NULL,没有意义 ,需要加入约束这种非法行为
  9. INSERT INTO stu_test(id,NAME) VALUES(3,'高圆圆') ; -- 加入约束,不插入默认约束就起作用
  10. 2.非空约束 not null
  11. 一些业务字段的必须存在值,设置非空约束
  12. CREATE TABLE stu_test(
  13. id INT ,
  14. NAME VARCHAR(20) NOT NULL -- 非空约束
  15. ) ;
  16. 举例:insert into stu_test values(3,null) ;-- Column 'name' cannot be null :非空约束起作用
  17. 1)通过修改表:删除非空约束
  18. ALTER TABLE stu_test MODIFY NAME VARCHAR(20) ;
  19. 2)通过修改表:添加非空约束
  20. ALTER TABLE stu_test MODIFY NAME VARCHAR(20) NOT NULL ;
  21. 3.唯一约束 unique
  22. 字段值不能重复
  23. 1)创建一张表
  24. CREATE TABLE stu_test(
  25. id INT ,
  26. NAME VARCHAR(20),
  27. phone VARCHAR(11) UNIQUE -- 添加唯一约束
  28. ) ;
  29. 举例:
  30. 1)删除唯一约束 (drop index 唯一约束的字段名称)
  31. alter table stu_test modify phone varchar(11) ;
  32. ALTER TABLE stu_test DROP INDEX phone ;
  33. 2)添加唯一约束
  34. ALTER TABLE stu_test MODIFY phone VARCHAR(11) UNIQUE ;
  35. 4.主键约束 primary key 非空且唯一
  36. 注意:一般开发中,一张表都会有也非业务字段id,自增长的主键
  37. 业务字段不会将它设置为主键,因为业务在的数据在不断变化!
  38. id 给定值,下次它会随着之前的值继续自增
  39. CREATE TABLE stu_test(
  40. id INT PRIMARY KEY, -- 编号 添加主键约束
  41. NAME VARCHAR(20), -- 姓名
  42. age INT, -- 年龄
  43. address VARCHAR(50)
  44. );
  45. 1)通过修改表:删除主键 的语法
  46. ALTER TABLE stu_test DROP PRIMARY KEY ;
  47. 2)通过修改表:添加主键
  48. ALTER TABLE stu_test MODIFY id INT PRIMARY KEY ;
  49. 5.自增长约束 auto_incrment
  50. CREATE TABLE employee(
  51. id INT PRIMARY KEY AUTO_INCREMENT, -- 编号
  52. NAME VARCHAR(20), -- 员工名称
  53. age INT, -- 年龄
  54. gender VARCHAR(5) ,-- 性别
  55. dept_name VARCHAR(10) -- 部门名称
  56. ) ;
  57. 1)查询最后一次自增长主键的值(后期mybatis框架中使用:获取最后一次自增长主键的id值)
  58. SELECT LAST_INSERT_ID() ;
  59. 6.外键约束 foreign key
  60. 创建一个部门表
  61. CREATE TABLE dept(
  62. id INT PRIMARY KEY AUTO_INCREMENT, -- 部门编号
  63. dept_name VARCHAR(10)
  64. );
  65. -- 插入三个部门
  66. INSERT INTO dept(dept_name) VALUES('开发部'),('测试部'),('运维部') ;
  67. 创建员工表
  68. -- id 编号,name 员工名称,age 年龄,gender 性别,dept_id 部门编号
  69. CREATE TABLE employee(
  70. id INT PRIMARY KEY AUTO_INCREMENT, -- 编号
  71. NAME VARCHAR(20), -- 姓名
  72. age INT, -- 年龄
  73. gender VARCHAR(5), -- 性别
  74. dept_id INT -- 部门编号
  75. CONSTRAINT dept_emp_fk -- 声明 跟的外键名称
  76. FOREIGN KEY (dept_id) -- 作用在外键上
  77. REFERENCES dept(id) -- 关联与主表的主键id
  78. );
  79. INSERT INTO employee(NAME,age,gender,dept_id) VALUES
  80. ('高圆圆',42,'女',1),
  81. ('文章',35,'男',2) ,
  82. ('赵又廷',45,'男',1),
  83. ('张三丰',50,'男',3),
  84. ('王宝强',44,'男',2),
  85. ('王聪',25,'男',3);
  86. 1)通过sql语句:修改表语句将外键约束删除
  87. -- alter table 表名 drop foreign key 外键的名称
  88. ALTER TABLE employee DROP FOREIGN KEY dept_emp_fk ;
  89. 2)通过修改表的语句添加外键约束
  90. ALTER TABLE employee ADD
  91. CONSTRAINT dept_emp_fk -- 声明 跟的外键名称
  92. FOREIGN KEY (dept_id) -- 作用在外键上
  93. REFERENCES dept(id); -- 关联与主表的主键id
  94. 7.外键约束基础,可以进行级联操作(级联修改和级联删除)CASCADE
  95. 用户在操作主表的时候,跟主表相关的从表的数据也随之更改;
  96. 如果不使用操作:针对修改或者删除,都应该先执行从表,然后主表;
  97. 级联删除和级联修改 on delete cascade /on update cascade
  98. 1)通过sql语句:外键删除
  99. ALTER TABLE employee DROP FOREIGN KEY dept_emp_fk ;
  100. 2) 添加外键约束的同时,添加级联删除和级联修改
  101. ALTER TABLE employee ADD
  102. CONSTRAINT dept_emp_fk -- 声明 跟的外键名称
  103. FOREIGN KEY (dept_id) -- 作用在外键上
  104. REFERENCES dept(id) -- 关联与主表的主键id
  105. ON DELETE CASCADE
  106. ON UPDATE CASCADE ;
  107. 3)直接部门表的4号部门,那么在4号部门的员工也随着 删除了
  108. DELETE FROM dept WHERE id = 4 ;

8)数据库的备份和还原:

 1)还原过程

  1. - 方式1:命令行方式
  2. -- mysql8---进入mysql自动控制台或者dos登录mysql
  3. 方式2:图形界面化
  4. -- 在sqlYog或者navicat里面都可以用,直接将库选中, 删除,删除之后,
  5. -- 在创建的新库右键---选择 import---> Execute Sql Scripts:执行sql脚本
  6. -- 选择备份的地址,直接执行sql文件!

推荐阅读