java mysql ddl_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;