JavaWeb - 数据库基础 - DDL、DML、DQL、约束

1.MySQL服务启动和关闭

　　*命令台下输入services.msc

　　*或者以管理员身份启动cmd, 输入 net start mysql

2.MySQL登录和退出

　　* mysql -uroot -p 加密码

　　访问其他用户的mysql： cmd下输入： mysql -h+IP地址 -uroot -p+连接谁，就敲谁的密码

　　　　　　　　　　　　或者mysql --host = ip地址 --user = root --password = 连接目标密码

　　退出：quit或exit

3.MySQL目录结构

　　1.MySQL安装目录

　　　　*配置文件 my.ini

　　2.MySQL数据目录

　　　　*数据库：文件夹

　　　　*表：文件

　　　　*数据

4.SQL

　　1.什么是SQL

　　　　Structured Query Language：结构化查询语言　　　　

　　　　　　其实就是定义了操作所有关系型数据库的规则

5.SQL通用语法

　　*SQL语句可以单行或多行书写，以分号结尾

　　*可使用空格和TAP键来增强语句的可读性

　　*MySQL数据库的SQL语句不区分大小写，关键字建议使用大写

　　*三种注释

　　　　*单行注释 -- 注释内容（必须有空格）或 # 注释内容（MySQL特有）

　　　　*多行注释

6.SQL分类

　　 1) DDL(Data Definition Language)数据定义语言
            用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等
        2) DML(Data Manipulation Language)数据操作语言
            用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等
        3) DQL(Data Query Language)数据查询语言
            用来查询数据库中表的记录(数据)。关键字：select, where 等
        4) DCL(Data Control Language)数据控制语言(了解)
            用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等

7.DDL 操作数据库、表

　　（1）操作数据库：CRUD

　　　　　　*C（Create）：创建

　　　　　　　　# - - > 创建db3数据库，判断是否存在，并指定字符集为gbk

　　　　　　　　　　create database if not exists db3 character set gbk;

　　　　　　*R（Retrive）：查询

　　　　　　　　#查询所有数据库的名称：show databases；

　　　　　　　　#查看某个数据库的字符集（查看某个数据库的创建语句）：show create database 数据库名称；

　　　　　　*U（Update）：修改

　　　　　　　　#修改数据库的字符集： alter database 数据库名称 character set 字符集名称；

　　　　　　*D（Delete）：删除

　　　　　　　　#删除数据库 drop database 数据库名称；

　　　　　　　　　　drop databases if exist db3;

　　　　　　*使用数据库

　　　　　　　　#查询当前正在使用的数据库名称：select database（）；

　　　　　　　　#使用数据库：use 数据库名称；

　　（2）操作表

　　　　　　*C（Create）：创建

　　　　　　　　#创建表：create table 表名称（

　　　　　　　　　　　　　　列名1 数据类型1,

　　　　　　　　　　　　　　列名2 数据类型1,

　　　　　　　　　　　　　　 . . . . .

　　　　　　　　　　　　　　列名n 数据类型n

　　　　　　　　）；

　　　　　　　　 #注意最后一列不需要加逗号

　　　　　　　　#复制表：create table 表名 like 被复制的表名；

　　　　　　　　 #数据类型：

　　　　　　　　　　　　1. int : 整数类型 * age int,

　　　　　　　　　　　　2.double : 小数类型 * score double（5，2）, #第一个参数表示最大有几位，第二个参数为小数点后面保留几位，此时最大值为999.99

　　　　　　　　　　　　3.data : 日期，只包含年月日,yyyy-MM-dd

　　　　　　　　　　　　4.datatime:日期，包含年月日，时分秒 yyyy-MM-dd HH：mm：ss

　　　　　　　　　　　　5.timestamp:时间戳类型包含年月日，时分秒 yyyy-MM-dd HH：mm：ss

　　　　　　　　　　　　　　*如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间来自动赋值

　　　　　　　　　　　　6.varchar:字符串： name varchar（20）; 表示姓名最大20个字符

　　　　　　　　　　#案例：创建学生表

　　　　　　　　　　　　create table student（

　　　　　　　　　　　　　　　　id int,

　　　　　　　　　　　　　　　　name varchar（32),

　　　　　　　　　　　　　　　　age int,

　　　　　　　　　　　　　　　　score double(4,1),

　　　　　　　　　　　　　　　　birthday data,

　　　　　　　　　　　　　　　　create_time timestamp　

　　　　　　　　　　　　　　　　　）;

　　　　　　*R（Retrive）：查询

　　　　　　　　#查询某个数据库中所有的表名称：show tables；

　　　　　　　　#查看表结构：desc 表名；

　　　　　　*U（Update）：修改

　　　　　　　　#修改表名:alter table 表名 rename to 新表名

　　　　　　　　#修改表的字符集：alter table 表名 character set 字符集名称

　　　　　　　　#添加一列：alter table 表名 add 列名数据类型；

　　　　　　　　#修改列名称类型：1. alter table 表名 change 列名新列名数据类型； 2. alter table 表名 modify 列名新数据类型；

　　　　　　　 #删除列：alter table 表名 drop 列名；

　　　　　　*D（Delete）：删除

　　　　　　　　# drop table if exists 表名;

8.DML:增删改表中数据

　　（1）添加数据：

　　　　　　*insert into 表名（列名1，列名2...列名n） values (值1，值2,...值n);

　　　　注意：

　　　　　　*若表名后不定义列名，则默认给所有列添加值

　　　　　　*除了数字类型，其他类型需要使用引号引起来（单双都可以）

　　（2）删除数据

　　　　　　* delect from 表名【where 条件】

　　　　注意：

　　　　　　*如果不加条件，则删除表中所有数据

　　　　　　*如果要删除所有记录：

　　　　　　　　（1）delete form 表名； - - 不推荐使用，效率低，有多少记录，则执行多少次删除操作

　　　　　　　　（2）truncate table 表名； - - 先删除表，再创建一张一样的空表，推荐，效率高

　　（3）修改数据

　　　　　　*update 表名 set 列名1 = 值1 ，列名2 = 值2，... ，【where 条件】

　　　　注意：

　　　　　　*如果不加任何条件，则会将表中所有记录全部修改

9.DQL：查询表中的记录

　　查询表中的记录：select * from 表名

　　语法：

　　　　select 字段列表 from 表名列表 where 条件列表 group by 分组字段 having 分组之后的条件 order by 排序 limit 分页限定

　　基础查询：

　　　　（1）多个字段的查询：select 字段名1，字段名2 from 表名称；

　　　　　　SELECT NAME, age, sex FROM student3;

　　　　　　注意：如果查询所有字段，则可以使用替代

　　　　（2）去除重复

　　　　　　SELECT DISTINCT address FROM student3;

　　　　（3）计算列：一般可以使用四则运算计算一些列的值（一般只会进行数值的运算）

　　　　　　计算math和english的和：SELECT NAME,math,english,math + english FROM student3;

　　　　　　如果有null参与的运算，计算结果都为null 解决：SELECT NAME,math,english,math + IFNULL(english,0) FROM student3;

　　　　　　ifnull（表达式1，表达式2）表达式1：那个字段需要判断是否为null 表达式2：null字段的替换值

　　　　（4）起别名：as 或省略空格

　　　　　　SELECT NAME,math,english,math + IFNULL(english,0) AS 总分 FROM student3;

　　条件查询：

　　　　1.where 子句后跟条件

　　　　2.运算符

　　　　　　* >、<、<=、>=、=、<>不等于或者！=

　　　　　　*BETWEEN...AND

　　　　　　*IN(集合)

　　　　　　*LIKE '张%' 模糊查询

　　　　　　　　　*占位符 _ : 单个任意字符 % ：多个任意字符

　　　　　　* is null　　

　　　　　　* and 或 &&

　　　　　　* or 或 ||

　　　　　　*not 或！

　　　　查询年龄大于20岁的人：SELECT * FROM student3 WHERE age >= 20;

　　　　查询年龄为18,22,35的人：SELECT * FROM student3 WHERE age in (18,22,35) ;

　　　　查询英语程序为null的人：SELECT * FROM student3 WHERE english is null;

　　　　查询班里姓马的人：select * from student3 where name like '马%';

　　　　查询姓名中第二个字时化的人：SELECT * FROM student3 WHERE NAME LIKE '_化%';

　　　　查询姓名是三个字的人：SELECT * FROM student3 WHERE NAME LIKE '___';

　　　　查询姓名中包含马的人：SELECT * FROM student3 WHERE NAME LIKE '%马%' ;

10.DQL排序查询、聚合函数、分组查询、分页查询

　　（1）排序查询语法：order by 排序字段1 排序方式1，排序字段2 排序方式2

　　　　　　*排序方式：

　　　　　　　　*ASC:升序，默认的

　　　　　　　　*DESC：降序

　　　　　　按照数学成绩降序排名，如果一样，则按英语成绩降序排名：SELECT * FROM student3 ORDER BY math DESC, english DESC;

　　（2）聚合函数：将一列数据作为一个整体，进行纵向的计算

　　　　　　1.count：计算个数

　　　　　　　　一般选择非空的列：主键

　　　　　　　　count（*）：只要一行中有一个值不会空，则算一个

　　　　　　　　SELECT COUNT(IFNULL(english,0)) FROM student3;

　　　　　　2.max：计算最大值

　　　　　　3.min：计算最小值

　　　　　　4.sum:求和

　　　　　　5.avg：计算平均值

　　　　　　*注意：聚合函数的计算会排除null值

　　　　　　　　解决方案：选择不包含空的列或者使用IFNULL函数

　　（3）分组查询：group by 分组字段

　　　　　　*分组之后查询的字段：分组字段、聚合函数

　　　　　　　　如：查询男女之间，数学的平均分，以及男女个数：

　　　　　　　　　　SELECT sex,AVG(math),COUNT(id) FROM student3 GROUP BY sex;

　　　　　　*where 和 having 的区别：

　　　　　　　　*where在分组之前进行限定，如果不满足这个条件则不进行分组，having是在分组之后进行限定，如果不满足结果则不会被查询出来

　　　　　　　　*where之后不可以跟聚合函数，having之后可以跟聚合函数

　　　　　　　　如：查询男女之间，数学的平均分，以及男女个数，分数低于70分的不参与分组

　　　　　　　　　　SELECT sex,AVG(math),COUNT(id) FROM student3 WHERE math > 70 GROUP BY sex;

　　　　　　　　如：查询男女之间，数学的平均分，以及男女个数，分数低于70分的不参与分组，分组之后，人数要大于两个人

　　　　　　　　　　SELECT sex,AVG(math),COUNT(id) 此处可以加别名 FROM student3 WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;

　　（4）分页查询：limit 开始索引，每页查询的条数

　　　　　　--每页显示三条记录
　　　　　　　　SELECT * FROM student3 LIMIT 0,3; -- 第一页

　　　　　　　　SELECT * FROM student3 LIMIT 3,3; -- 第二页

　　　　　　　　SELECT * FROM student3 LIMIT 6,3; -- 第三页

　　　　　　公式：开始的索引 = （当前的页码 - 1）每页显示的条数

　　　　　　*分页的操作是一个'方言'，在不同的数据库是不一样的

11.约束

　　概念：对表中的数据进行限定，保证数据的正确性、有效性和完整性。

　　　　1.主键约束：primary key

　　　　2.非空约束： not null　　　　

　　　　3.唯一约束：unique

　　　　4.外键约束：foreign key

　　*非空约束：not null　　

　　　　1.创建表时添加非空约束

　　　　　　CREATE TABLE stu(
　　　　　　　　 id INT,
　　　　　　　　NAME VARCHAR(20) NOT NULL -- name 为非空
　　　　　　　　);

　　　　2.创建表后，添加非空约束

　　　　　　ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;

　　　　3.删除非空约束

　　　　　　ALTER TABLE stu MODIFY NAME VARCHAR(20) ;

　　　　*唯一约束：unique，某一列的值不能重复，唯一约束可以有null值，但是也只能由一个null

　　　　　　1.创建表时添加唯一约束

　　　　　　　　CREATE TABLE stu(
　　　　　　　　　　 id INT,
　　　　　　　　　　 phone_number VARCHAR(20) UNIQUE -- 手机号
　　　　　　　　　　　　　　);

　　　　　　2.删除唯一约束

　　　　　　　　ALTER TABLE stu DROP INDEX phone_number ; 与删除非空约束不同

　　　　　　3.创建表后添加唯一约束

　　　　　　　　ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;

　　　　*主键约束：primary key 非空且唯一一张表只能有一个字段为主键

　　　　　　1.创建表时，添加主键约束

　　　　　　　　CREATE TABLE stu_info(
　　　　　　　　　　　 id INT PRIMARY KEY ,
　　　　　　　　　　 NAME VARCHAR(20)
　　　　　　　　　　　);

　　　　　　2.删除主键　　

　　　　　　　　ALTER TABLE stu_info DROP PRIMARY KEY;

　　　　　　3.创建表后，添加主键约束

　　　　　　　　ALTER TABLE stu_info MODIFY id INT PRIMARY KEY;

　　　　　　4.自动增长：如果某一列是数值类型的，使用auto——increment 可以来完成值的自动增长　　　　　　　

　　　　　　　　创建表时，添加主键约束，并且完成自动增长

　　　　　　　　　　CREATE TABLE stu_info(
　　　　　　　　　　　　　 id INT PRIMARY KEY AUTO_INCREMENT,
　　　　　　　　　　　　 NAME VARCHAR(20)
　　　　　　　　　　　　　);

　　　　　　　　删除自动增长：ALTER TABLE stu3 MODIFY id INT;

　　　　　　　　创建表后，添加自动增长：ALTER TABLE stu3 MODIFY id INT AUTO_INCREMENT;

　　*外键约束：让表与表产生关系，从而保证数据的正确性

　　　　1.创建表时，可以添加外键

　　　　　　CREATE TABLE 表名（

　　　　　　　　......

　　　　　　　　外键列

　　　　　　　　constraint 外键名称 foreign key (外键列名称) references 主表名称（主表列名称）

　　　　　　　　）；

　　　　2.删除外键

　　　　　　ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

　　　　3.在创建表后，添加外键

　　　　　　ALTER TABLE 表名称 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称（主表列名称）;

　　　　　　ALTER TABLE employee ADD CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES department(id);

　　　　4.级联操作，设置级联更新,设置级联删除

　　　　　　ALTER TABLE 表名称 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称（主表列名称）ON UPDATE CASCADE DELETE CASCADE;

posted @ 2020-05-28 10:37 五号世界阅读(431) 评论(0) 编辑收藏举报

刷新页面返回顶部

五号世界

JavaWeb - 数据库基础 - DDL、DML、DQL、约束

1.MySQL服务启动和关闭

*命令台下输入services.msc

*或者以管理员身份启动cmd, 输入 net start mysql

2.MySQL登录和退出

* mysql -uroot -p 加密码

访问其他用户的mysql： cmd下输入 ： mysql -h+IP地址 -uroot -p+连接谁，就敲谁的密码

或者mysql --host = ip地址 --user = root --password = 连接目标密码

退出：quit或exit

3.MySQL目录结构

1.MySQL安装目录

*配置文件 my.ini

2.MySQL数据目录

*数据库：文件夹

*表：文件

*数据

4.SQL

1.什么是SQL

Structured Query Language：结构化查询语言

其实就是定义了操作所有关系型数据库的规则

5.SQL通用语法

*SQL语句可以单行或多行书写，以分号结尾

*可使用空格和TAP键来增强语句的可读性

*MySQL数据库的SQL语句不区分大小写，关键字建议使用大写

*三种注释

*单行注释 -- 注释内容（必须有空格） 或 # 注释内容（MySQL特有）

*多行注释

6.SQL分类

7.DDL 操作数据库、表

（1）操作数据库：CRUD

*C（Create）：创建

# - - > 创建db3数据库，判断是否存在，并指定字符集为gbk

create database if not exists db3 character set gbk;

*R（Retrive）：查询

#查询所有数据库的名称：show databases；

#查看某个数据库的字符集（查看某个数据库的创建语句）：show create database 数据库名称；

*U（Update）：修改

#修改数据库的字符集： alter database 数据库名称 character set 字符集名称；

*D（Delete）：删除

#删除数据库 drop database 数据库名称；

drop databases if exist db3;

*使用数据库

#查询当前正在使用的数据库名称：select database（）；

#使用数据库：use 数据库名称；

（2）操作表

*C（Create）：创建

#创建表：create table 表名称（

列名1 数据类型1,

列名2 数据类型1,

. . . . .

列名n 数据类型n

）；

#注意最后一列不需要加逗号

#复制表：create table 表名 like 被复制的表名；

#数据类型：

1. int : 整数类型 * age int,

2.double : 小数类型 * score double（5，2）, #第一个参数表示最大有几位，第二个参数为小数点后面保留几位，此时最大值为999.99

3.data : 日期，只包含年月日,yyyy-MM-dd

4.datatime:日期，包含年月日，时分秒 yyyy-MM-dd HH：mm：ss

5.timestamp:时间戳类型 包含年月日，时分秒 yyyy-MM-dd HH：mm：ss

*如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间来自动赋值

6.varchar:字符串： name varchar（20）; 表示姓名最大20个字符

#案例：创建学生表

create table student（

id int,

name varchar（32),

age int,

score double(4,1),

birthday data,

create_time timestamp

）;

*R（Retrive）：查询

#查询某个数据库中所有的表名称：show tables；

#查看表结构：desc 表名；

*U（Update）：修改

#修改表名:alter table 表名 rename to 新表名

#修改表的字符集：alter table 表名 character set 字符集名称

#添加一列：alter table 表名 add 列名 数据类型；

#修改列名称 类型：1. alter table 表名 change 列名 新列名 数据类型； 2. alter table 表名 modify 列名 新数据类型；

　　*命令台下输入services.msc

　　*或者以管理员身份启动cmd, 输入 net start mysql

　　* mysql -uroot -p 加密码

　　访问其他用户的mysql： cmd下输入： mysql -h+IP地址 -uroot -p+连接谁，就敲谁的密码

　　　　　　　　　　　　或者mysql --host = ip地址 --user = root --password = 连接目标密码

　　退出：quit或exit

　　1.MySQL安装目录

　　　　*配置文件 my.ini

　　2.MySQL数据目录

　　　　*数据库：文件夹

　　　　*表：文件

　　　　*数据

　　1.什么是SQL

　　　　Structured Query Language：结构化查询语言　　　　

　　　　　　其实就是定义了操作所有关系型数据库的规则

　　*SQL语句可以单行或多行书写，以分号结尾

　　*可使用空格和TAP键来增强语句的可读性

　　*MySQL数据库的SQL语句不区分大小写，关键字建议使用大写

　　*三种注释

　　　　*单行注释 -- 注释内容（必须有空格）或 # 注释内容（MySQL特有）

　　　　*多行注释

　　（1）操作数据库：CRUD

　　　　　　*C（Create）：创建

　　　　　　　　# - - > 创建db3数据库，判断是否存在，并指定字符集为gbk

　　　　　　　　　　create database if not exists db3 character set gbk;

　　　　　　*R（Retrive）：查询

　　　　　　　　#查询所有数据库的名称：show databases；

　　　　　　　　#查看某个数据库的字符集（查看某个数据库的创建语句）：show create database 数据库名称；

　　　　　　*U（Update）：修改

　　　　　　　　#修改数据库的字符集： alter database 数据库名称 character set 字符集名称；

　　　　　　*D（Delete）：删除

　　　　　　　　#删除数据库 drop database 数据库名称；

　　　　　　　　　　drop databases if exist db3;

　　　　　　*使用数据库

　　　　　　　　#查询当前正在使用的数据库名称：select database（）；

　　　　　　　　#使用数据库：use 数据库名称；

　　（2）操作表

　　　　　　*C（Create）：创建

　　　　　　　　#创建表：create table 表名称（

　　　　　　　　　　　　　　列名1 数据类型1,

　　　　　　　　　　　　　　列名2 数据类型1,

　　　　　　　　　　　　　　 . . . . .

　　　　　　　　　　　　　　列名n 数据类型n

　　　　　　　　）；

　　　　　　　　 #注意最后一列不需要加逗号

　　　　　　　　#复制表：create table 表名 like 被复制的表名；

　　　　　　　　 #数据类型：

　　　　　　　　　　　　1. int : 整数类型 * age int,

　　　　　　　　　　　　2.double : 小数类型 * score double（5，2）, #第一个参数表示最大有几位，第二个参数为小数点后面保留几位，此时最大值为999.99

　　　　　　　　　　　　3.data : 日期，只包含年月日,yyyy-MM-dd

　　　　　　　　　　　　4.datatime:日期，包含年月日，时分秒 yyyy-MM-dd HH：mm：ss

　　　　　　　　　　　　5.timestamp:时间戳类型包含年月日，时分秒 yyyy-MM-dd HH：mm：ss

　　　　　　　　　　　　　　*如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间来自动赋值

　　　　　　　　　　　　6.varchar:字符串： name varchar（20）; 表示姓名最大20个字符

　　　　　　　　　　#案例：创建学生表

　　　　　　　　　　　　create table student（

　　　　　　　　　　　　　　　　id int,

　　　　　　　　　　　　　　　　name varchar（32),

　　　　　　　　　　　　　　　　age int,

　　　　　　　　　　　　　　　　score double(4,1),

　　　　　　　　　　　　　　　　birthday data,

　　　　　　　　　　　　　　　　create_time timestamp　

　　　　　　　　　　　　　　　　　）;

　　　　　　*R（Retrive）：查询

　　　　　　　　#查询某个数据库中所有的表名称：show tables；

　　　　　　　　#查看表结构：desc 表名；

　　　　　　*U（Update）：修改

　　　　　　　　#修改表名:alter table 表名 rename to 新表名

　　　　　　　　#修改表的字符集：alter table 表名 character set 字符集名称

　　　　　　　　#添加一列：alter table 表名 add 列名数据类型；

　　　　　　　　#修改列名称类型：1. alter table 表名 change 列名新列名数据类型； 2. alter table 表名 modify 列名新数据类型；

　　　　　　　 #删除列：alter table 表名 drop 列名；

　　　　　　*D（Delete）：删除

　　　　　　　　# drop table if exists 表名;

　　（1）添加数据：

　　　　　　*insert into 表名（列名1，列名2...列名n） values (值1，值2,...值n);

　　　　注意：

　　　　　　*若表名后不定义列名，则默认给所有列添加值

　　　　　　*除了数字类型，其他类型需要使用引号引起来（单双都可以）

　　（2）删除数据