web学习六——mysql

8 Mysql

8.1 常见的关系化数据库

商业化数据库
Oracle
http://www.oracle.com/index.html
微软的SQLServer
http://www.microsoft.com/SQL/default.mspx
IBM 的DB2
http://www-306.ibm.com/software/data/db2/9/download.html
Sybase
http://www.sybase.com/

开源的数据库
MySQL
http://www.mysql.com/
SQLite
http://www.sqlite.org/
HSQLDB 纯Java小型数据库
http://www.hsqldb.org/
PostgreSQL
http://www.postgresql.org/

8.2Mysql数据库服务器、数据库和表的关系

8.3数据库表中的数据与javabean实体的对应关系

8.4 E—R实体关系图

8.5 Mysql安装配置及使用

**1.**配置Mysql按照bin目录到Path中
**2.**使用命令行窗口连接MYSQL数据库 mysql –u用户名 –p密码
3. MySQL数据库Root密码重置

方法一:
在my.ini的[mysqld]字段加入：
skip-grant-tables
重启mysql服务，这时的mysql不需要密码即可登录数据库
然后进入mysql
mysql>use mysql;
mysql>更新 update user set password=password(‘新密码’) WHERE User=‘root’;
mysql>flush privileges;
运行之后最后去掉my.ini中的skip-grant-tables，重启mysqld即可。
方法二:
不使用修改my.ini重启服务的方法，通过非服务方式加skip-grant-tables运行mysql来修改mysql密码
停止mysql服务
打开命令行窗口，在bin目录下使用mysqld-nt.exe启动，即在命令行窗口执行: mysqld-nt --skip-grant-tables
然后另外打开一个命令行窗口，登录mysql，此时无需输入mysql密码即可进入。
按以上方法修改好密码后,关闭命令行运行mysql的那个窗口，此时即关闭了mysql，如果发现mysql仍在运行
的话可以结束掉对应进程来关闭。
启动mysql服务
4. sql语言分类
DDL （数据定义语言）
数据定义语言 - Data Definition Language
用来定义数据库的对象，如数据表、视图、索引等
DML （数据操纵语言）
数据处理语言 - Data Manipulation Language
在数据库表中更新，增加和删除记录
如 update， insert， delete
DCL （数据控制语言）
数据控制语言 – Data Control Language
指用于设置用户权限和控制事务语句
如grant，revoke，if…else，while，begin transaction
DQL （数据查询语言）
数据查询语言 – Data Query Language
select
**5.**使用教程：

数据库中 sql 语句 不区分 大小写

+++++++++++++++++++++++++++++++++++DDL 语句 +++++++++++++++++++++++++++++++++++

=====创建数据库
语法:
create database 数据库名称 character set 编码集 collate 比较规则;

练习：
创建一个名称为mydb1的数据库。
create database mydb1;

创建一个使用utf8字符集的mydb2数据库。
create database mydb2 character set utf8;

创建一个使用utf8字符集，并带校对规则的mydb3数据库。
create database mydb3 character set utf8 collate utf8_general_ci;

首先查看下现有的数据库:
show databases;

查看数据库的建表语句:
show create database mydb2;


删除数据库:
语法:
drop database 数据库名称;

练习：
查看当前数据库服务器中的所有数据库
show databases;

查看前面创建的mydb2数据库的定义信息
show create database mydb2;

删除前面创建的mydb1数据库
drop database mydb1;

修改数据库:
语法:
alter database 数据库名称 更改后的信息;

切换数据库:
use 数据库名称;

查看当前使用的数据库:
select database();

练习
 查看服务器中的数据库，并把其中某一个库的字符集修改为gbk;
 show databases();
 alter database mydb2 character set gbk;


==== 如何创建表

表是二维表, 有行有列 , 表中存的一行行的记录, 每一行记录对应着 javabean 的一个实体 

语法:
create table 表名
(
    字段一 字段类型,
    字段二 字段类型,
    字段三 字段类型
);

字段一, 字段二... 就是二维表的 列的名称,  但是每一列实际上是有列的 数据的类型的
而这里每一行都对应着 javabean的一个实体,javabean 中每个 字段的数据是有具体的类型的,
在msyql 中 的字段的类型与 java中的字段类型又 是怎样的对应 关系呢? 

java 中: 
byte, short ,int ,long , float,double, char,String, boolean


字符流,  字节流

mysql: 

数值型
TINYINT 、SMALLINT、INT、BIGINT、FLOAT、DOUBLE  ,  bit (0,1)

字符串型 
VARCHAR、CHAR
varchar(1), char(1)
varchar(5), char(5)

大数据类型
BLOB(字节流)、TEXT(字符流)

text:  
blob:

日期型
DATE(只有日期)、TIME(只有时间)、DATETIME(有日期,有时间)、
TIMESTAMP(时间戳,是自动生成的,不需要人工是维护)


练习:
创建一个员工表employee:

字段	属性
id	整形        int
name	字符型      varchar(20)
gender	字符型      varhcar(10)
birthday	日期型  date
entry_date	日期型  date
job	字符型       varchar(100)
salary	小数型        float
resume	大文本型    varchar(255)


create table employee 
(
   id int,
   name varchar(20),
   gender varchar(10),
   birthday date,
   entry_date date,
   job varchar(100),
   salary float,
   resume varchar(255)
);


查看数据库下表:
-- 会将当前数据库下所有的表都 都列出来(这叫做注释)
show tables;

查看表结构: 
desc 表名;

desc employee;

查看 表的创建语句:

show create table employee;

| employee | CREATE TABLE `employee` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `gender` varchar(10) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `entry_date` date DEFAULT NULL,
  `job` varchar(100) DEFAULT NULL,
  `salary` float DEFAULT NULL,
  `resume` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |


定义单表字段的约束: 

不管是单表还是多表, 添加约束的都是为了保证数据库中表的数据的有效性和完整性

单表约束:

a) 主键约束 --- 不允许 为空, 也不允许 重复
b) 唯一约束----不允许重复
c) 非空约束-----不允许为空


对于以上三种 约束, 通常会根据需求 将某些字段设置为 非空, 唯一约束或者 主键约束
其中 设置 某个字段为主键约束时, 如果那个 字段是 数值 类型, 那么通常可以在主键约束后
加上  auto_increment (表示主键的值自动增长), 

create table emp2 
(
   id int primary key auto_increment,
   name varchar(20) not null,
   gender varchar(10) not null,
   birthday date,
   entry_date date,
   job varchar(100),
   salary float not null,
   resume varchar(255)
);

修改表:

添加列:
语法:
alter table 表名 add 列名称 列字段类型;

修改列:
alter table 表名 modify 列名称 列字段类型;

删除列:
alter table 表名 drop 列名;

修改表的名称：
rename table 表名 to 新表名;
修改列的名称： 
alter table 表名 change 现有的列的名称 新的列的名称 新的列的类型;

修改表的字符集：
alter table 表名 character set utf8;


练习
在上面员工表的基本上增加一个image列。
alter table employee add image blob;

修改job列，使其长度为60。
alter table employee modify job varchar(60);

删除gender列。
alter table employee drop gender;

表名改为users。
rename table employee to users;

修改表的字符集为utf8
alter table users character set utf8;

列名name修改为username
alter table users change name username varchar(30);


++++++++++++++++++++++DML语句+++++++++++++++++++++++++++++++++++

dml 语句 包括 ,增加(insert ), 删除(delete) , 修改 (update)

=====insert 语句:

插入数据记录到表中

语法:
insert into 表名 (列名一,列名二,列名N) values(列一的值,列二的值,列N的值);

第一种写法(全部都写):
insert into users (id,username,birthday,entry_date,job,salary,resume,image) values(1,'bb','1995-09-09','2015-07-15','developer',15000,'a good boy','not uploaded');

第二种写法(只写部分):
insert into users (id,username,entry_date,job,salary,resume) values(2,'pp','2015-06-30','project manager',20000,'a hansome man');

第三种写法(不写列名,直接写值,所有的值都要按照表的列的顺序去写):
insert into users values(3,'junjie','1993-10-10','2015-08-02','a good coder',21000,'a lady killer','clik to visit');

注意: 以上三种语法中用的最多的是 第三种. 

insert into users values(3,'童林','1999-10-10','2015-08-10','a good boy',22000,'一个钻石王老五的哥们','点我吧');


mysql> insert into users values(3,'童林','1999-10-10','2015-08-10','a good boy',22000,'一个钻石王老五的哥们','点我吧');
ERROR 1366 (HY000): Incorrect string value: '\xC1\xD6' for column 'username' at row 1

解决方式:
两种
方式一(临时的解决):
只针对当前的窗口有效
set names gbk;

方式二(一劳永逸的解决):
进到 mysql安装文件夹下, 将编码改为 gbk ,然后重新启动mysql的服务就可以了.

show variables like 'character%';


=====update 语句:

主要用于对表中现有的数据进行更新操作

语法:
update 表名 set 列名一=列名的值,列名二=列名的值,列名三=列名...;

where 从句-- 加上限定条件, 只有满足条件的才会被修改,更新
不加where 从句, 那就是所有的都更新

要求
将所有员工薪水修改为5000元。
update users set salary=5000;

将姓名为’pp’的员工薪水修改为6000元。
update users set salary=6000 where username='pp';

将姓名为’junjie’的员工薪水修改为4000元,job改为ccc。
update users set salary=4000,job='ccc' where username='junjie';

将童林的薪水在原有基础上增加1000元。
update users set salary=salary+1000 where username='童林';


=====delete  语句:

删除表中的数据

语法:
delete from  表名 [where从句];

如果不加where 从句,就会全部删除

练习:
删除表中名称为’阿花’的记录。
delete from users where username='阿花';

删除表中所有记录。
-- 属于 DML语句
delete from users;

使用truncate删除表中记录。
-- 先将表给摧毁,然后新建一个同样的表
-- 属于DDL语句
truncate users;


++++++++++++++++++++++++DQL语句++++++++++++++++++++++++++++++++++++

DQL 语句, 指的是使用sql 语句对数据库表中的记录做查询操作 .

关键字是 select , 非常的复杂, 


create table exams(
   id int,
   name varchar(30),
   chinese double,
   english double,
   math double
);

insert into exams values(1,'马云',96,97,55);
insert into exams values(2,'童林',88,92,91);
insert into exams values(3,'何生',89,82,99);
insert into exams values(4,'何生2',89,83,91);


语法一:
select *| 列名一,列名二 from 表名;

select * from exams;

select distinct chinese from exams;

练习：
查询表中所有学生的信息。
select * from exams;

查询表中所有学生的姓名和对应的英语成绩。
-- select 后面跟的是 最终显示的数据
select name,english from exams;

过滤表中重复数据。
-- 过滤表中语文重复的数据
select distinct chinese from exams;

练习
在所有学生分数上加5分特长分。
-- 这个语句只是将查询到的结果 进行 加法运算,并没有真正的修改表中的数据
select name,chinese+5, english+5,math+5 from exams;

统计每个学生的总分。
select name,chinese+math+english from exams;

使用别名表示学生分数。
select name as 姓名,chinese+math+english as 总分 from exams;
select name 姓名,chinese+math+english 总分 from exams;

使用where子句，进行过滤查询。
练习：
查询姓名为何生的学生成绩
select * from exams where name='何生';

查询英语成绩大于90分的同学
select * from exams where english>90;

查询总分大于255分的所有同学
select * from exams where chinese+math+english>255;


练习:

查询英语分数在 80－90之间的同学。
select * from exams where english>=80 and english<=90;
select * from exams where english between 80 and 90;

查询数学分数为89,90,91的同学。
select * from exams where math in(89,90,91,92,99);

查询所有姓何的学生成绩。
select * from exams where name like '何%';

查询数学分>80，语文分>80的同学。
select * from exams where math>80 and chinese>80;

注意点:
一:
Like语句中，% 代表零个或多个任意字符，_ 代表一个字符，例first_name like ‘_a%’;

查询所有姓何的学生成绩。
select * from exams where name like '何__';
insert into exams values(5,'小何生',89,83,91);

查询名字中包含 何字 的哥们..
select * from exams where name like '%何%';

二:
and 和or ?
当一个sql语句中既有 and 还有or 时, 哪个先执行?
and 优先级　高于　ｏｒ　
select * from exams where math<80 or math>0 and english<90;


===== 使用order by 进行 排序===
order by 列名 desc|asc
asc: ascend 升序
desc: descend 降序

练习：
对数学成绩排序后输出。
-- 升序排列, 不加asc 时 默认的也是升序.
select * from exams order by math asc;

对总分排序按从高到低的顺序输出
select * from exams order by math+chinese+english desc;

对姓何的学生总成绩排序输出

select * from exams where name like '何%' order by math+english+chinese desc;

====聚合函数 使用:
count ---- 用于 进行 求和, 求的是满足条件列的总的行数 

练习：
统计一个班级共有多少学生？
select count(*) from exams;

统计数学成绩大于90的学生有多少个？
select count(*) from exams where math>90;

统计总分大于270的人数有多少？
select count(*) from exams where chinese+math+english>270;

sum求和运算

练习：
统计一个班级数学总成绩?
select sum(math) from exams;

统计一个班级语文、英语、数学各科的总成绩
select sum(chinese),sum(english),sum(math) from exams;
select sum(chinese) 语文总分,sum(english) 英语总分,sum(math) 数学总分 from exams;

统计一个班级语文、英语、数学的成绩总和
select sum(chinese+math+english) from exams;

统计一个班级语文成绩平均分
select sum(chinese)/count(*) 语文平均分 from exams;

avg--求平均

练习：
求一个班级数学平均分？
select avg(math) from exams ;
select sum(math)/count(*) from exams ;

求一个班级总分平均分
select avg(math+english+chinese) from exams;

聚集函数－MAX/MIN
求最大值,最小值

求班级数学最高分和最低分（数值范围在统计中特别有用）
select max(math),min(math) from exams;

练习：查询购买了几类商品，并且每类总价大于100的商品

select product,sum(price) from orders group by product having sum(price)>100;

where , having ?

1.where 后面是不允许接  聚合函数的, 而having 是可以接 聚合函数的
2.where 是在分组前进行操作, having是在分组后操作. 

select 语句中关键字的书写顺序?select from where group having order
书写顺序:
s...f...w...g...h..o

解析顺序:
f...w..g...h...s...o

备份数据库表中的数据
cmd> mysqldump -u 用户名 -p 数据库名 > 文件名.sql
例如: mysqldump -u root -p day12 > c:\day12.sql

恢复数据库
source 文件名.sql   // 在mysql内部使用
mysql –u 用户名 p 数据库名 < 文件名.sql  // 在cmd下使用

定义外键约束
  foreign key
  foreign key(ordersid) references orders(id)