web day015sql

   -------myspl数据库-------
数据库:文件系统,通过标准SQL语句访问文件.
关系型数据库:通常保存实体间关系 实体[用户 订单 商品 分类]
  购物网站er模型图/实体-联系图(Entity Relationship Diagram)
    实体长方体: 用户[属性:id/username/password] 订单[属性:id/time/address] 商品[属性:id/price/name] 分类
属性椭圆:
关系菱形: 用户--关系[生成]--订单 订单--关系[包含]--商品
常见关系型数据库:mySQL重点[小型/免费]/SQLserver收费中型/Oracle收费大型/DB2收费大型/SyBase过时[powerDesigner:数据建模]
  /SQLiter嵌入式,安卓
  mysql安装:确认电脑无mysql,安装过程中注意mysql服务器安装路径和mysql保存路径(卸载时需要把这两个目录删除)
  编码设置,记住root密码,认端口3306
  -------myspl内部结构-------
  电脑安装Tomcat服务器,这台电脑是web服务器.电脑安装mysql数据库,这台电脑是数据库服务器
  客户端安装mysql后,输入mysql -u root -p[省略两个参数 -p:端口号 -h:主机]进行连接,默认连接本地服务器以及3306端口
  ***1.服务器内部有多个数据库,创建数据库.一般情况,一个软件对应一个数据库[DB:OA系统/购物商城]
  一个数据库有多张表[table:实体(java类)],表的内部[多条记录:id/name/age],记录:类似类的实例对象
  ***2.数据服务器和数据库关系:一对多,数据库与数据库表[java类]关系:一对多,表与表记录[实例对象]的关系:一对多
  -------myspl语句-------
  sql:结构化查询语言(Structured Query Language)
  sql特性:
    1.非过程性语言:一条语句的执行(就会有一条结果)无需依赖其他语言, (过程性语言:一条语句的执行需要依赖其他语言)
为增强SQL语言能力,数据库生产商增强了SQL语言:SQL Server:T-SQL.
  ----****myspl分类------- DMCQ:Definition/Manipulation/Control/Query
  DDL:Data Definition Language.数据定义语言. create,alter[更改],drop...
  DML:Data Manipulation Language.数据处理语言. update,insert,delete...
  DCL:Data Control Language.数据控制语言. if,grant[允许]...
  DQL:Data Query Language.数据查询语言. select
  =========================================DDL====================================================
  ----****1.SQL操作数据库:CRUD-------
  **1.创建 create datebase 库名 [character set 字符集 collate 字符集校对规则]
    练习:
    创建一个数据库名称为mydb1数据库.
create database mydb1;
创建一个使用utf8字符集的mydb2数据库.
create database mydb2 character set utf-8;
创建一个使用utf8字符集,并带有校对规则的mydb3数据库.
  **2.查看 show databases;
  数据库定义细节: show create database 数据库名;
  练习
  查看服务器中有哪些数据库: show databases;
  查看前面创建的mydb3数据库定义信息: show create database mydb3;
  3.删除数据库:drop database 库名;
  4.修改数据库: alter database 库名 character set 字符集 collate 校对规则;
  练习: 修改mydb2数据库,字符集改为gbk.  alter database mydb2 character set gbk;
  **5.切换 use 库名;
  6.显示当前使用数据库 select database();
  --------*******MYSQL常用数据类型:----------------
        byte    TINYINT
short SMALLINT
int            INT
long    BIGINT
float FLOAT
double DOUBLE
boolean BIT
Date DATE/TIME/DATETIME/TIMESTAMP
datetime与timestamp区别?
datetime:既有日期又有时间,须手动传入日期.
timestamp:既有日期又有时间.没有给值,会采用系统当前时间存入到这个列
 char和varchar的区别:
char:固定长度字符串.ex:定义数据库某个字段类型为char(8).存入hello.会在hello字符串后面添加3个空格进行补齐
varchar:可变长度字符串.定义数据库某个字段类型为varchar(8).存入hello.那么存入数据库就是hello
File  BLOB:二进制文件/TEXT:文本文件. ***** 在Oracle数据库中用BLOB和CLOB
练习:创建员工表:employee.
     建表前一定要选择数据库:
create database web015;
use web015;
create table employee(
   id int,
name varchar(20),
gender varchar(10),
birthday date,
entry_date date,
job varchar(50),
salary double,
resume text   
);
最后一个无需加逗号,字符串长度必写,其他类型都有默认长度.int默认长度11
  ----****2.表操作:CRUD-------
  **创建: create table 表名 (字段名 字段类型(长度) 约束,字段名 字段类型(长度) 约束..)character set 字符集
  collate 校对规则;
  一般情况下一个实体会创建一个表.
     public class User{
private int id;
private String username;
private String password;
private Double salary;
private int age;
}
   ----------建表约束(单表):保证数据完整性  库 表 记录 字段
    *主键:primary key 唯一确定表中一条记录(默认非空唯一) 主键整形 anto_increment自动增长
*唯一:unique 表中字段唯一
*非空:字段值不能为空
   练习:创建带有约束的表
    create table employee2(
 id int primary key auto_increment,
 name varchar(20) unique,
 gender varchar(20) not null,
 birthday date not null,
 entry_date date not null,
 job varchar(50) not null,
 salary double not null,
 resume text not null
);
   ----------***查看/修改/删除表
    *查看: 1.数据库中有哪些表 show tables; 2.表的结构 desc 表名
    修改: 1.alter table 表名 add 字段名 类型(长度) 约束;  添加字段.
 2.alter table 表名 modify 字段名 类型(长度) 约束; 修改字段类型长度和约束
 3.alter table 表名 drop 字段名; 删除表中字段
 4.alter table 表名 change 旧字段名 新字段名 类型(长度) 约束; 修改字段名
 5.rename table 旧表名 to 新表名; 修改表名
 6.alter table 表名 character set 字符集; 修改表字符集
     练习：员工表中添加image列 alter table employee add image varchar(20);
      job列长度变为60 alter table employee modify job varchar(60);
  删除gender字段 alter table employee drop gender;
  表名改为user: rename table employee to user;
  字符集变为utf8 alter table user character set utf8;
  列name改为username alter table user change name username varchar(45);
    *删除: drop table 表名 练习:删除 user; drop table user;
=========================================DML====================================================
   ----****3.表中记录操作:CRUD-------
     *添加
    表中插入部分字段 insert into 表名 (字段名1,字段名2,字段名3...) values (值1,值2,值3...);
         表中插入所有字段 insert into 表名 values (值1,值2,值3...);
***注意:
 1.添加字段,字段的个数与类型与值的个数与类型一致
 2.添加字段值,值的长度不能超过字段设置最大长度
 3.添加字段值类型如果是字符串/日期[要用单引号]
练习:
向数据库中添加3条记录:
 1.部分字段 insert into employee(id,name,gender,job,salary) values (1,'chen','man','CEO',900000);
 2.所有字段 
     insert into employee values (2,'che','man','19951215','20170903','CFO',900000,'hard');
 3.带有中文的数据 
     insert into employee values (3,'陈','男','19951215','20170903','CFO',1000000,'强');
 直接插入报错会乱码,mysql有六个字符集相关的参数,三个客户端,三分服务器
 解决:找到mysql安装路径/my.ini
 1.客户端[mysql] default-character-set=utf8 2.服务器[mysqld] character-set-server=utf8
 3.客户端有关的编码修改为gbk;既default-character-set=gbk 编解码不同不会乱码,mysql有自动转换机制
 show variables like '%character%';六个字符集相关的参数的编码 !!!修改完毕重启mysql
*修改
  update 表名 set 字段名=值 字段名=值 [where 条件];
  练习:
   所有员工工资改为* update employee set salary = 6000;
姓名为'chen'的工资改为7000 update employee set salary = 7000 where name = 'chen';
姓名为'bbb'工资改为*,同时job改为COO update employee set salary = 8000 ,job = 'GB' where name = 'che';
姓名为'ccc'工资加1000 update employee set salary = salary+2000 where name = '陈';
*删除
  delete from 表名 [条件];
   !!注意:1.只能删除一条记录,不能删除记录的某个字段(修改),2.不能删除整个表(drop table) 3.有外界关系得先解除
4.truncate table 表名也可删除记录, 删除所有记录delete from 表名;
  练习
   删除'陈'记录 delete from employee where name = '陈';
删除所有记录 delete from employee;
truncate table删除所有记录 truncate table employee;
！！！truncate table和delete from 区别:delete属于DML,事物可以回滚(删除数据可以找回),一条条记录挨着删除,效率低
truncate属于DDL,事物不能回滚(rollback),直接把整个表删除,重新创建一个表,效率高
  =========================================DQL====================================================
*****查询最复杂最重要
create table exam(
   id int primary key auto_increment,
name varchar(20),
math int,
ch int,
en int
);
   insert into exam values(null,'陈',93,96,100);
   insert into exam values(null,'星',99,95,97);
   insert into exam values(null,'力',92,99,95);
   insert into exam values(null,'和',95,93,97);
   insert into exam values(null,'槐',93,96,94);]
 1.基础 select [distinct:去掉重复值] *|[列名1,2,3..] from 表名;
   练习:查询所有学生 select * from exam;
        查询所有学生姓名与英语成绩 select name,en from exam;
过滤重复信息 select distinct en from exam;
 2.执行运算,记录改名
   select 表达式 from 表;
   select 列名 as 别名,列名 as 别名 from 表名; [as可省略]
  练习:所有学生分数加十 select name,math+10,ch+10,en+10 from exam;
       统计所有学生总分 select name,math+ch+en from exam;
  别名表示学生总分 select name,math+ch+en as num from exam;/select name,math+ch+en sum from exam;
 3.条件过滤 select *|列名,列名 from 表名 where 条件;
   练习:查询'陈'成绩 select * from exam where name = '陈';
        英语大于106的学生 select * from exam where en >96;
总分大于286的学生 select * from exam where math+en+ch >286;
!!!where后面的运算符 > < <= >= = <>[!=]  in:一组值 like:模糊查询[需使用占位符(_ and %)]
_:一个字符[ex: where like '张_';以张开头,只能是两位] %:多个字符 [ex: where like '张%';以张开头,多位]
 %张:以张开头 张%:以张结尾 %张%:有张即可  is /not null:查询是否为空 and/or/not:与或非
练习:英语94-96; select * from exam where en >93 and en < 97;
    数学93-95; select * from exam where math >92 and math < 96;
    所有姓陈学生 select * from exam where name ='陈';
所有数学大于94,语文<96 select * from exam where math >94 and ch< 96;
 4.排序 select * from 表 where 条件 order by 列[asc升序/desc降序]; 默认升序asc
   练习:数学asc select * from exam order by math;
        总分desc select * from exam order by math+en+ch;
数学desc,同分则参考en select * from exam order by math desc,en asc;
姓陈asc select name,math+en+ch from exam where name='陈' order by math desc,en asc;
 5.聚集[分组]函数 a.count统计 select count(*|列) from 表;
   练习:统计学生 select count(*) from exam;
        统计数学大于95 select count(*) from exam where math > 95;
统计总成绩大于286 select count(*) from exam where math+ch+en > 286;
   b.sum()求和 select sum(列) from exam;
    练习: 所有人数学总成绩 select sum(math) from exam;
     所有人语数外总成绩 select sum(math),sum(en),sum(ch) from exam;
 所有人语数外总成绩累加 1.select sum(math)+sum(en)+sum(ch) from exam;/2.select sum(math+en+ch) from exam;
 1和2的区别:如果插入一条包含null的条件,1竖向能统计到null后面的值,2不行 解决看下面
ifnull():ifnull(math,0)如果math这列值为null,会用0替代.select sum(ifnull(math,0)+en+ch) from exam;
     所有人英语平均成绩 select sum(en)/count(*) from exam;
  c.avg()平均 select avg(列) from 表;
    练习 班级语文平均 select avg(ch) from exam; 班级语文平均 select avg(ch+ifnull(math,0)+en) from exam;
  d/e.max/min() select max/min(列) from 表; 班级 select max/min(ch+ifnull(math,0)+en) from exam;
 6.分组统计 select * from 表 where 条件 group by 列;
  练习;
    归类并显示每类商品价格总和 select product,sum(price) from orders group by product;
归类并显示每类商品总数量 select product,count(*) from orders group by product;
查询购物单,显示总价大于1200 select product,sum(price) from orders where sum(price) > 1200 group by product;
 上面和下面都错,select product,sum(price) from orders group by product where sum(price) > 1200;
!!where的条件(不能用聚集函数)和having(后面可以跟聚集函数,条件过滤)区别:
select product,sum(price) from orders group by product having sum(price) >1200; ok!
 7.查询总结  select/from/where/group by/having/order by
   a. S..F..W..G..H..O select 列 from 表 where 条件 group by 列 having 条件过滤 order by 列;
 ----****数据库的备份与还原-------
   备份:cmd>mysqldump -u root -p 数据库名称 > E:/web015.sql ex:mysqldump -u root -p web015 > E:/web015.sql
   还原:1.还原前:必须先有一个空的数据库: cmd>mysql -u root -p 数据库名称 < c:/web015.sql ex:cmd>mysql -u root -p web015 < E:/web015.sql
    2.进入mysql数据库中进入新建数据库: source E:/web015.sql
 ----****可视化工具-------
   SQLyog：创建数据库[java类]/表[实例对象]/记录[局部变量]/字段[类似给记录赋值] 备份还原更改表