数据库语法总结

数据库知识总结

mysql安装
目录：英文，软件和数据文件放在一起重点内容
端口号：3306
用户名：root 密码：root
服务名称：MySql
启动服务：
1.管理——服务与应用程序
2.cmd窗口 net start mysql
　　　　　Net stop mysql
mysql的登录：
　　在cmd窗口中
　　　　1.mysql -uroot -p密码
　　　　2.Mysql -uroot -p
　　输入密码：
密码的重置：
前提 ：保证mysql服务关闭
步骤：
　　1.在cmd窗口中输入 mysqld –console –skip-grant-tables
　　2.再次打开一个cmd窗口，输入mysql -uroot
　　3.切换数据库 use mysql
　　4.修改密码 update user set password=password(‘新密码’) where user=’root’;
　　5.关闭两个cmd窗口，启动mysql服务
分类：
DDL:数据库定义语言
操作对象：数据库和表
关键词：create alter drop
操作数据库：
创建(☆)：
格式：create database 数据库名称;
如：create database mjl;
修改(了解)：
就是修改数据库的字符集或者校对规则
格式：alter database mjl character set utf8;
删除(理解)：
格式：drop database 数据库名称;
如：drop database mjl;
常用命令：
查看所有的数据库：show databases;
进入或者切换数据库：use 数据库名称;
查看建库语句：show create database 数据库名;
操作表：
创建表：
格式：create table 表名(字段描述1，字段描述2….字段描述n);
字段描述的格式：字段名 字段类型 [约束]
如：create table user(id int,name varchar(20));
修改表：
格式：alter table 表名 …
修改表名：
格式：alter table 旧表名 rename to 新表名;
添加字段：
格式：alter table 表名 add[column] 新字段描述;
如：alter table user add password varchar(20);
修改字段类型：
格式：alter table 表名 modify 新字段描述;
如：alter table user modify password int;
修改字段名称：
格式：alter table 表名 change 旧字段名 新字段描述;
如：alter table user change password pwd varchar(20);
删除字段：
格式：alter table 表名 drop 字段名;
如：alter table user drop pwd;
删除表：
格式：drop table 表名;
常用命令：
查看当前数据库下所有表：show tables;
查看表结构：desc 表名;
查看建表语句：show create table 表名;
DML:数据库操作语言
操作对象：记录
关键词：insert update delete
插入记录：
格式1：
Insert into 表名 values(值1[,值2…]);
例如：
insert into user values(1,’zhangsan’);
注意：
默认插入全部的字段，values里面的值要和建表语句中字段顺序及类型保持一致。
Int类型可以不使用引号,varchar类型必须使用引号
格式2：
Insert into 表名(字段1[,字段2…]) values(值1[,值2…]);
例如：
Insert into user(name,id) values(‘李四’,2);
注意：
插入指定的字段values里面的值要和前面的字段顺序及类型保持一致。
插入记录乱码问题：
方法1：临时修改
Set names gbk;
方法2：永久修改
安装目录下/my.ini文件
port=3306
[mysql]
default-character-set=utf8
把utf8改成gbk,然后重启服务
修改记录：
格式：
Update 表名 set 字段名1=值1[,字段名2=值2…] where 条件;
例如：
Update user set id=3 where name=’李四’;
Update user set id=4,name=’赵六’ where id=3;
删除记录：
格式：
Delete from 表名 [where 条件];
例如：
Delete from user; – 清空表
Delete from user where id=4;
扩展：
在开发中添加一个字段用来标识该条记录是否被删除。例如 isdel
DQL:数据库查询语言
关键词：select
查询语句：select * from 表名;
基本查询：
1.查询出所有商品信息(查询全部)
Select * from products;
Select id,name,price,pnum,category from products;(标准)
2.查询出所有商品的名称，价格，类别及数量信息(查询部分)
Select name,price,category,pnum from products;
3.查询出所有的商品类别
去重复操作：distinct
Select distinct category from products;
4.查询出所有商品的名称及价格，将所有商品价格加10
在查询的结果之上进行运算。
Select name,price+10 from products;
注意：
NULL和任意数据进行运算结果都是NULL值。可以通过ifnull(字段名，默认是)
例如：ifnull(price,0) 若价格为null，则按0处理
Select name,ifnull(price,0)+10 from products;
5.查询出每一个商品的总价及名称
Select name,pnum*price from products;
字段起别名：字段 [as] 别名
select name,pnum*price as ‘总价’ from products;
条件查询：
格式：select * from 表名 where 条件;
Select 字段1[,字段2…] from 表名 where 条件;
1.查询所有计算机类商品信息
– 条件写法1：支持关系运算符 > >= < <= !=(<>)
– 格式：字段名 运算符 值;
Select * from products where category=’计算机’;
Select * from products where category!=’计算机’;
2.查询出商品价格大于90的商品信息
Select * from products where price >90;
3.查询出商品总价大于10000的商品信息
Select * from products where price*pnum>10000;
4.查询出价格在100-200之间的商品信息
– 条件写法2：支持逻辑操作符 and or not
– 格式：表达式1 and|or 表达式2
Not 表达式
Select * from products where price >=100 and price <=200;
– 条件写法 3：支持between and 操作
– 格式：字段 between 较小值and 较大值
Select * from products where price between 100 and 200;
5.查询出商品价格是65,100或190的商品信息
Select * from products where price =65 or price =100 or price =190;
– 条件写法4：支持 in 操作
– 格式：字段 in (值1，值2…)
Select * from products where price in (65,100,190);
6.查询出商品的名字中包含java的商品信息
– 条件写法5：匹配操作 like
– 格式：字段 like 匹配规则；
– 匹配内容
Like ‘龙’ 值为龙
Like ‘%龙’ 值以龙结尾
Like ‘龙%’ 值以龙开头
Like ‘%龙%’ 值包含龙
– 匹配个数
Like ‘_’ 值为一个字符
Like ‘__” 值为2个字符
Select * from products where name like ‘%java%’;
7.查询出书名是两个字的商品信息
Select * from products where name like ‘__’;
(很少用,一汉字三字节)Select * from products where length(name)=6;
8.查询出商品价格不为null的商品信息
– 条件写法6：Null操作
– 格式1：is null
Select * from products where price is null;
–格式2：is not null
Select * from products where price is not null;
排序：order by
格式1：
Select * from 表名 [where 条件] order by 排序字段 排序方式;
格式2：
Select * from 表名 [where 条件] order by 排序字段1 排序方式，排序字段2，排序方式;
排序方式：
升序：asc(默认)
降序：desc
1.查询出所有商品，并根据价格进行升序排列
Select * from products order by price asc;
2.查询出所有商品，根据数量进行升序排列。如果数量相同，根据价格进行 降序排列
Select * from products order by pnum asc,price desc;
聚集函数：
它是对一列的值进行计算，然后返回一个单一的值；另外聚集函数会忽略空值
Sum(字段)：求和
Count(字段)：计数
Max(字段)：最大值
Min(字段)：最小值
Avg(字段)：平均值
格式：
Select 聚集函数 from 表名 [where 条件];
1.统计商品表中共有多少条记录
Select count(id) from products;
开发中：
Select count(*) from products;
2.统计商品表中价格大于50的有多少条记录
Select count(*) from products where price >50;
3.统计有多少商品
Select sum(pnum) from products;
4.统计所有商品的总价值
Select sum(price*pnum) from products;
5.统计所有商品的平均价格
Select sum(price*pnum)/sum(pnum) from products;
–round(值，保留位数)
Select round(sum(price*pnum)/sum(pnum),2) from products;
6.统计出记录中pnum的平均值
Select avg(pnum) from products;
7.统计出商品表中price最大值
Select max(price) from products;
8.统计出商品表中price最小值
Select min(price) from products;
9.统计出生活百科类图书的总数量
Select sum(pnum) from products where category=’生活百科’;
分组：group by
格式1：
Select 分组字段[,集合函数] from 表名 [where 条件] group by分组字段;
1.对商品分类别统计，求出每一种类商品的总数量
Select category,sum(pnum) from products group by category;
2.对商品分类别统计，求出每一种类商品的总数量，数量要大于100
Select category,sum(pnum) from products where sum(pnum)>100 group by category; ×where条件后不能跟聚集函数
格式2：
Select 分组字段[,集合函数] from 表名 [where 条件] group by分组字段 sum(pnum)>100;
where和having的区别：
1.where后面不能使用聚集函数，having可以
2.where是对分组之前的数据进行筛选，having是对分组之后的数据进行筛选。
DQL的使用小结：
Select 分组字段[,聚集函数] from 表名 where 条件 group by 分组字段 having 条件 order by 排序字段 asc|desc;
注意：order by永远放在最后
执行顺序：
1.先确定哪张表 from
2.确定是否有条件 where
3.若需要分组，使用group by
4.若需要对分组后的数据进行筛选，执行having
5.确定要显示哪些字段，select
6.确定字段的显示方法，执行order by
DTL:数据库事物语言
DCL:数据库控制语言
字段类型：
Java mysql
Byte tinyint
Short smallint
Int int☆
Long bigint

Float float
Double double double(m,d) m代表的数字的长度，d代表小数位个数
Double(5,2) 存放的最大值为999.99

Char char(n) char(5):长度不可变 如：存abc,在数据库中“abc ”
String varchar(n)☆ varchar(5):长度可变 如：存abc,“abc”

Boolean

Java.sql.Date date:日期
Java.sql.Time time:时间
Java.sql.TimeStamp timestamp:时间戳
dateTime:日期和时间

约束：保证是数据的有效性和完整性
在mysql中有主键约束(primary key)，唯一约束(unique)，非空约束(not null)，外键约束(foreign key)

主键约束(primary key)：确定字段是该条记录唯一标识。
    注意：
        1.主键可以是一个字段，也可以是多个字段
        2.一张表只能有一个主键
    特点：
        被主键约束修饰过的字段唯一非空
    使用：
        格式1：在声明字段的同时，添加主键约束
            Create table pk01(
                id int primary key,
                name varchar(20)
            );
            insert into pk01 values(1,'tom'); --成功
            insert into pk01 values(1,'tom');--错误，已经存在，保证唯一性
            insert into pk01 values(null,'tom');--错误，非空
            扩展：
                一般情况下，使用id作为主键，id没有任何实际意义。

        格式2：在声明字段之后，在约束区域添加主键约束:[constraint] primary key(字段1[,字段2...]);联合主键，都不能为空
            Create table pk02(
                id int
                name varchar(20),
                primary key(id,name)
            );
            Insert into pk02 values(1,’tom’);
            Insert into pk02 values(1,’jack’);

        格式3：创建完表之后，通过修改表结构添加主键约束:
            Alter table 表名 add primary key(字段1[,字段2...]);
            Create table pk03(id int,name varchar(20));
            Alter table pk03 add primary key(name);
    使用小结：
        开发中最常使用的是第一种，只能在一个字段上添加主键约束。
        若需要在多个字段上添加主键约束(联合主键),请使用第二种或第三种方    式。

唯一约束(unique)
    特点：被修饰的字段唯一，对Null值不起作用
    使用：
        格式1：在声明字段的同时，添加唯一约束 
            写法：字段名 字段类型 unique
            Create table un01(
                id int unique,
                name varchar(20)
            );
                Insert into un01 values(null,’tom’); --多次创建成功，对null不起作用

        格式2：在声明字段之后，在约束区域添加唯一约束
            写法：[constraint] unique(字段1[,字段2...])
            Create table un02(
                id int,name varchar(20),
                unique(id,name)
            );--联合唯一约束

        格式3：在创建完表之后，通过修改表结构添加唯一约束
            Alter table 表名 add unique(字段1[,字段2]));
            Alter table un03 add unique(id);

        使用小结：
            在开发中一般不添加
            在项目最后上线的时候通过第三种方式添加唯一约束。

非空约束(not null)
    特点：被修饰的字段非空
    格式：在声明字段的同时添加非空约束
        Create table nn
            id int not null,
            name varchar(20) not null
        );

三个小知识(必须掌握)
auto_increment(开发)
作用：被它修饰过的字段可以自增
格式：
字段名称 字段类型 primary key auto_increment
特点：
1.被修饰的字段类型可以自增，一般是int
2.被修饰的字段必须是一个key,一般是primary key
create table ai01(
id varchar(20) primary key auto_increment,
name varchar(20)
); – 错误 Incorrect column specifier for column ‘id’

    create table ai02(
        id int auto_increment,
        name varchar(20)
    ); -- 错误  it must be defined as a key

    create table ai03(
        id int primary key auto_increment,
        name varchar(20)
    );

    使用的注意事项：
        插入值得时候可以是指定的值
        插入值得时候把它指定为Null
        插入值的时候忽略掉它也可以。
    insert into ai03 values(33,'sansan');
    insert into ai03 values(null,'xiaosan');
    insert into ai03(name) values('xiaosi');

truncate(面试)
    作用：清空表，干掉表结构，创建一个新表。
    格式：truncate table 表名;

    delete from 和truncate区别：
        1.truncate 属于DDL语句，delete 属于DML语句
        2.truncate 是干掉表结构，重新创建一个新表。delete 是逐条删除记录

default(看懂)
    作用：添加默认值
    格式：字段名称  字段类型 (约束) default 默认值
    例如：
        create table de(
            id int primary key auto_increment,
            name varchar(20) default '张三丰'
        );

        insert into de values(null,'sansan');
        insert into de values(null,null); -- null也算是赋值
        insert into de(id) values(null);

        结论：在不明确给定值得时候采用默认值。

数据库的备份与还原(了解)
备份：
格式：在cmd窗口下输入
mysqldump -uroot -p密码 要备份的数据库>文件磁盘路径
例如：
mysqldump -uroot -pmoujinling321 mjl>d:/mjl.sql

还原：前提是必须手动创建目的地数据库
    格式1：在cmd窗口下输入    
        mysql -uroot -p密码 目的地数据库<文件磁盘路径
        例如：
            mysql -uroot -pmoujinling321 u34<d:/mjl.sql
    格式2：必须在目的地数据库中执行命令
        source 文件磁盘路径    
            例如：
                source d:/mjl.sql
常用的命令：
    select database(); -- 查看当前所在的数据库

多表的设计与实现☆
关系型数据库：存放实体与实体之间的关系。
用户<拥有> 订单<包含> 商品

一对多
    设计：
        在实际开发中，我们称一方(用户表)为一表或者主表，称多方(订单表)为多表或者从表。
        然后在多表的一方添加一个字段，字段名称自定义(一般使用主表的名称_id,字段的类型一般和主表的id保持一致)。该字段称之为外键。
    实现：
        user(用户表)
        create table user(
            id int primary key auto_increment,
            username varchar(20)
        );

        orders(订单表)
        create table orders(
            id int primary key auto_increment,
            price double,
            user_id int
        );

        insert into user values(null,'强哥'),(null,'聪哥');

        insert into orders values(null,1314,1);
        insert into orders values(null,1315,2);
        insert into orders values(null,305,2);
    为了保证数据的有效性和完整性，必须给表添加外键约束。
    在多表的一方添加外键约束。
    格式：通过修改表结构添加外键约束。
        alter table 表名 add [constraint [外键名称]] foreign key(多表的外键) references 主表(主键);
    例如：    
        alter table orders add foreign key(user_id) references user(id);

    注意：
        主表中不能删除从表中已引用的数据
        从表中不能添加主表中不存在的数据
        外键可以为null

多对多
    设计：
        在开发中，一般会引入一个中间表，中间表中的字段一般为两表的主键。
        通过中间表，就可以把多对多的关系拆分成两个一对多的关系。
        一般我们会把中间表的两个字段设置成联合主键。
    实现：
        list(商品表)
        create table list(
            id int primary key auto_increment,
            price double
        );

        orders_pro(中间表)
        create table orders_pro(
            oid int,
            pid int,
            primary key(oid,lid)
        );

        insert into list values(null,999),(null,305),(null,10);

        insert into orders_list values(1,1),(1,2),(1,3),(2,1),(2,2),(2,3),(3,2);
    为了保证数据的有效性和完整性，必须给表添加外键约束。(在中间表上添加)
    在中间表上添加两个外键约束
    格式：    
        alter table 表名 add [constraint [外键名称]] foreign key(多表的外键) references 主表(主表的id)；
    例如：
        alter table orders_list add foreign key(oid) references orders(id);
        alter table orders_list add foreign key(lid) references list(id);