JavaWeb-数据库

来源：互联网发布：四叠半神话大系知乎编辑：程序博客网时间：2024/06/05 05:06

一、数据库简介

Structured Query Language(结构化查询语言)
SQL：工业标准。各个数据库厂商都支持。
SQL-Server:对标准进行了扩展。扩展后的方言为TSQL。
Oracle:对标准进行了扩展。扩展后的方言为PLSQL,把MySQL收购了
规定：
1. shell>window下命令
2. mysql>mysql中的命令,一般以;结尾（进入数据库）

二、安装MySQL数据库

如何验证MySQL数据库：shell>mysql -u root -psorry //root为用户名，sorry为密码
除了test，其他2个数据库不要动。

三、SQL语句

辅助：查看、改名、备份、恢复

查看
1. 查看所有的数据库：mysql>SHOW DATABASES; 注意后面加 ; 分号
2. 查看数据库的创建细节： mysql>SHOW CREATE DATABASE mydb1;
3. 查看数据库中的所有表：mysql>SHOW TABLES;
4. 查看前面创建的mydb2数据库的定义信息： mysql>SHOW CREATE DATABASE mydb2;
5. 查看某张表格的结构： mysql>DESC employee;
6. 查看数据： mysql>SELECT * FROM user;
改名
1. 表名改为user。
2. mysql>RENAME TABLE employee TO user;
备份
1. shell>mysqldump -h localhost -u root -psorry test>c:/test.sql 把test备份到 c:/下，这里推荐使用反斜杠/
恢复
1. 第一步，先创建被删除掉的数据库：mysql>CREATE DATABASE test;
2. 第二步，选中这个数据库：mysql>USE test;
3. 下面分为两种方式：
4. 第三步（方式一）：
  1. SOURCE恢复 : mysql>SOURCE C:/test.sql;
  2. 选择数据库 : mysql>USE test;
5. 第三步（方式二）
  1. shell>mysql -u root -psorry test<c:/test.sql
告诉服务器客户端用的编码是什么？（5和6组合使用）
1. mysql>SET character_set_client=gbk;
解决返回的数据中文乱码（5和6组合使用）
1. mysql>SET character_set_results=gbk;
查看有哪些地方用到编码
1. mysql>SHOW VARIABLES LIKE 'character%';
2. character_set_client:此变量通知服务器客户端用的是什么编码
3. character_set_connection：此变量通知服务器客户端链接时用的是什么编码
4. character_set_database：数据库用的编码
5. character_set_results：是数据库给客户端返回时使用的字符集设定，如果没有指明，使用服务器默认的字符集。
6. character_set_server：是服务器安装时指定的默认字符集设定
7. character_set_system ：是数据库系统使用的字符集设定。

DDL：Data Definition Language(数据定义语言)
- 关键字:（操作对象：数据库和表结构。就是定义数据库或表的结构）
1. CREATE(创建)
  - 创建一个名称为mydb1的数据库。
  - mysql>CREATE DATABASE mydb1;
  - 创建一个使用gbk字符集的mydb2数据库。
  - mysql>CREATE DATABASE mydb2 CHARACTER SET gbk;
  - 创建一个使用utf8字符集，并带校对规则的mydb3数据库。
  - mysql>CREATE DATABASE mydb3 CHARACTER SET utf8 COLLATE utf8_general_ci;
2. ALTER（修改）
  - 在上面员工表的基本上增加一个image列。
  - mysql>ALTER TABLE employee ADD (image blob);
  - 修改job列，使其长度为60。
  - mysql>ALTER TABLE employee MODIFY job varchar(60);
  - 删除gender列。
  - mysql>ALTER TABLE employee DROP gender;
  - 修改表的字符集为utf-8
  - mysql>ALTER TABLE user CHARACTER SET utf8;
  - 列名name修改为username
  - mysql>ALTER TABLE user CHANGE name username varchar(100);
3. DROP（去除）
  - 删除前面创建的mydb2数据库
  - mysql>DROP DATABASE mydb2;
DML：Data Manipulation Language（数据操作语言）
- 关键词：（操作对象：表中的数据。就是操作表中的记录用的）
- 在MySQL中，字符或字符串或日期类型的数据要使用单引号引起来。关键字NULL表示空值，不同于空字符串值。''
1. INSERT
  1. 使用insert语句向表中插入三个员工的信息。
    1. mysql>INSERT INTO user (id,username,birthday,entry_date,job,salary,resume) VALUES (1,'huangshanshan','1992-09-08','2012-11-15','CEO','10000','beautiful girl');
    2. mysql>INSERT INTO user VALUES (2,'niuyang','1992-09-08','2012-11-15','CTO','10000','beautiful boy');
    3. mysql>INSERT INTO user VALUES (3,'于连林','1992-09-08','2012-11-15','CMO','10000','帅锅');
2. UPDATE
  1. 将所有员工薪水修改为5000元。
  2. mysql>UPDATE user SET salary=5000;
  4. 将姓名为’niuyang’的员工薪水修改为30000元。
  5. mysql>UPDATE user SET salary=30000 WHERE username='niuyang';
  7. 将姓名为’huangshanshan’的员工薪水修改为20000元,job改为HR。
  8. mysql>UPDATE user SET salary=20000,job='HR' WHERE username='huangshanshan';
  10. 将于连林的薪水在原有基础上增加10000元。
  11. mysql>UPDATE user SET salary=salary+10000 WHERE username='于连林';
3. DELETE
  1. 删除表中名称为’于连林’的记录。
  2. mysql>DELETE FROM user WHERE username='于连林';
  4. 删除表中所有记录。(一条一条地删除)
  5. mysql>DELETE FROM user;
  7. 使用truncate删除表中记录。(摧毁整张表，然后重建表结构)
  8. mysql>TRUNCATE user;
DQL:Data Query Language(数据查询语言)
1. SELECT
  - 查询表中所有学生的信息。
  - mysql>SELECT id,name,chinese,english,math FROM student;
  - 或者SELECT * FROM student;
  - 查询表中所有学生的姓名和对应的英语成绩。
  - mysql>SELECT name,english FROM student;
  - 过滤表中重复数据。
  - mysql>SELECT DISTINCT english FROM student;
  - 在所有学生语文分数上加10分特长分。
  - mysql>SELECT id,name,chinese+10 FROM student;
  - 统计每个学生的总分。
  - mysql>SELECT id,name,chinese+english+math FROM student;
  - 使用别名表示学生分数。(AS可以省略)
  - mysql>SELECT id,name,chinese+english+math AS 总分 FROM student;
  - mysql>SELECT id,name,chinese+english+math 分 FROM student;
  - 查询姓名为wu的学生成绩
  - mysql>SELECT id,name,chinese,english,math FROM student WHERE name='王五';
  - 查询英语成绩大于90分的同学
  - mysql>SELECT name,english FROM student WHERE english>90;
  - 查询总分大于200分的所有同学
  - mysql>SELECT name,chinese+english+math AS 总分 FROM student WHERE (chinese+english+math)>200;

四、常用函数

Order by 指定排序的列，排序的列即可是表中的列名，也可以是select 语句后指定的列名。
Count(列名)返回某一列，行的总数
Sum函数返回满足where条件的行的和
AVG函数返回满足where条件的一列的平均值
Max/min函数返回满足where条件的一列的最大/最小值
使用group by 子句对列进行分组
使用having 子句过滤

五、约束

主键约束（唯一定位一条记录）
1. PRIMARY KEY:唯一的，且不能为null。主键是确定找到一条记录的关键。
2. 逻辑主键：不参与业务运算，与业务无关。
3. 业务主键：
4. 删除主键：alter table tablename drop primary key ;
定义唯一约束：UNIQUE: 。可以不填，但是不能重复。
定义非空约束： NOT NULL:不能为空。
1. 不为空且不能重复： UNIQUE 加上NOT NULL。
定义外键约束：constraint ordersid_FK foreign key(ordersid) references orders(id),
1. 约束外键_fk 外部键（外键名）引用命令(键名)

六、多表的设计

一对多
- CREATE TABLE department(
- id int primary key,
- name varchar(100) not null unique
- );
- CREATE TABLE employee(
- id int primary key,
- name varchar(100) not null,
- salary float(8,2),
- dept_id int,
- CONSTRAINT dept_id_fk FOREIGN KEY(dept_id) REFERENCES department(id)
- );
多对多
- CREATE TABLE teacher(
- id int primary key,
- name varchar(100),
- salary float(8,2)
- );
- CREATE TABLE student(
- id int primary key,
- name varchar(100),
- grade varchar(100)
- );
- CREATE TABLE teacher_student(
- t_id int,
- s_id int,
- primary key(t_id,s_id),
- CONSTRAINT t_id_fk FOREIGN KEY(t_id) REFERENCES teacher(id),
- CONSTRAINT s_id_fk FOREIGN KEY(s_id) REFERENCES student(id)
- );
一对一
- CREATE TABLE person(
- id int primary key,
- married boolean
- );
- CREATE TABLE idcard(
- id int primary key,
- num varchar(100),
- name varchar(100),
- CONSTRAINT id_fk FOREIGN KEY(id) REFERENCES person(id),
- );