目录
删除全表delete from 表名 和truncate table 表名的区别?
数据库
数据库:存储数据的仓库
最早开始存储:定义临时变量;
数组存储数据:弊端长度固定
StringBuffer:可以存储数据---字符序列---转换成String
Collection/Map:存储一些元素或者键值元素,集合创建完毕,使用完毕也就不在存在
IO流可以永久存储数据,IO---写数据---读数据(字节流)耗时(本地内存)
常见数据库
使用数据库DataBase(db)存储数据:
分为:
1)关系数据库
支持“事务系统”:读/写过程:保证数据的安全性/一致性
支持高并发访问:同时多个访问数据库
存储大类型数据:大字节类型/大字符类型
2)非关系数据库
常见noSQL数据库:没有sql语句
redis--->作为“缓存用户的”
key-value元素存储进去
操作数据库定义语句DDL
操作数据库:
1)查询所有库:show databases;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema | --mysql自带配置信息相关的库文件(_schema约束文件)
| mysql | --有mysql里面user用户相关的信息表数据:root用户或者添加普通用户
| performance_schema | --mysql数据库本身自带的优化mysql性能相关的
| sys | -- mysql系统相关的库文件
+--------------------+
4 rows in set (0.00 sec)
2)创建一个新的数据库
2.1)create database 库名; 直接创建
mysql> create database mydb01;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb01 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
2.2)creat database if not exists 库名; 判断不存在库才创建
mysql> create database if not exists mydb_02;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb01 |
| mydb_02 |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
3)查看创建指定库的字符集编码格式
show create database 库名;
mysql> show create database mydb01;
+----------+-------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------+
| mydb01 | CREATE DATABASE `mydb01` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.01 sec)
4)修改指定库的字符集格式
alter database 库名 (default) character set 字符集格式;
mysql> alter database mydb01 default character set = utf8;
Query OK, 1 row affected (0.01 sec)
mysql> show create database mydb01;
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| mydb01 | CREATE DATABASE `mydb01` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
5)删除库
5.1)drop database 库名; 直接删除
mysql> drop database mydb01;
Query OK, 0 rows affected (0.03 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb_02 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
5.2)drop database if exists 库名; 判断存在库名, 直接删除
mysql> drop database if exists mydb_02;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
6)进入到指定的库中---使用这个库
use 库名; --- 提示“database changed”表示正在使用
mysql> use ee2302;
Database changed
7)mysql字段的查询正在使用库的函数 select database();
mysql> select database();
+------------+
| database() |
+------------+
| ee2302 |
+------------+
1 row in set (0.00 sec)
数据库中表的创建/删除/修改/查询DDL语句
常见的数据表的字段类型:
1)int :整数类型 默认11位 里面所占的实际值
int (指定的字符数)
2)字符串 varchar(指定最大长度)
3)小数类型 double
4)日期类型 date 仅仅表示日期
datetime :日期+时间
timestamp :时间戳(某个时间点去操作了数据库中某个表或者某个表的记录的时间)或者瞬时时间
1、创建表的语法---创建表之前 ,选库 (use 库名;)
create table 表名(字段名称1 字段类型1,字段名称2 字段类型2,... 字段名称n 字段类型n);
mysql> create table student(
-> id int,
-> name varchar(10),
-> age int,
-> gender varchar(3),
-> address varchar(50),
-> birthday date);
Query OK, 0 rows affected (0.05 sec)
2、查询库中的所有表 show tablse;
mysql> show tables;
+------------------+
| Tables_in_ee2302 |
+------------------+
| student |
+------------------+
1 row in set (0.00 sec)
3、查询表的结构:查询这个表的“列”有哪些 desc 表名;
mysql> create table student(
-> id int,
-> name varchar(10),
-> age int,
-> gender varchar(3),
-> address varchar(50),
-> birthday date);
Query OK, 0 rows affected (0.05 sec)
mysql> show tables;
+------------------+
| Tables_in_ee2302 |
+------------------+
| student |
+------------------+
1 row in set (0.00 sec)
4、修改表---修改表的字段名称(列的名称)
alter table 表名 change 以前的字段名称 现在的字段名称 以前的字段类型;
mysql> alter table student change gender sex varchar(3);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| sex | varchar(3) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
| birthday | date | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
4.1、修改表的字段类型
alter table 表名 modify 字段名称 对应的新的字段类型;
mysql> alter table student modify address varchar(20) ;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| sex | varchar(3) | YES | | NULL | |
| address | varchar(20) | YES | | NULL | |
| birthday | date | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
4.2、给表中添加一个新的字段名称(新的列)
alter table 表名 add 字段名称 字段类型;
mysql> alter table student add zipcode varchar(10) ;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| sex | varchar(3) | YES | | NULL | |
| address | varchar(20) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| zipcode | varchar(10) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
4.3、将表中的字段名称删除
alter table 表名 drop 字段名称;
mysql> alter table student drop zipcode;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| sex | varchar(3) | YES | | NULL | |
| address | varchar(20) | YES | | NULL | |
| birthday | date | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
5、复制一张一模一样的表
create table 新的表名 like 旧的表名;
mysql> create table teacher like student;
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
+------------------+
| Tables_in_ee2302 |
+------------------+
| student |
| teacher |
+------------------+
2 rows in set (0.00 sec)
mysql> desc teacher;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| sex | varchar(3) | YES | | NULL | |
| address | varchar(20) | YES | | NULL | |
| birthday | date | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
6、删除表
6.1、drop table 表名 ; 直接删除
mysql> drop table teacher;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+------------------+
| Tables_in_ee2302 |
+------------------+
| student |
+------------------+
1 row in set (0.00 sec)
6.2、drop table if exists 表名 ; 如果存在这个表,删除
mysql> drop table if exists student;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
Empty set (0.00 sec)
mysql:查询mysql服务器中所有的跟字符集相关的指令:模糊搜索
show variables like '%character%' ;
mysql> show variables like '%character%' ;
+--------------------------+---------------------------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------------------------+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | utf8(创建的所有库默认字符集utf8) |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | utf8 (服务器端) |
| character_set_system | utf8(mysql系统字符集) |
| character_sets_dir | C:\Program Files\MySQL\MySQL Server 5.7\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set, 1 warning (0.01 sec)
DML语句--数据库操作语句:操作表记录
数据库操作语句:
给表中插入全表记录:每一个值要和当前字段类型一致
1)insert into 表名 values(值1,值2...值n);
INSERT INTO student VALUES(1,'张三',25,'2000-03-10','天津市','男') ;
2)插入全标记类:一次插入多条
insert into 表名 values(值1,值2,...值n),(值1,值2,...值n),(...);
INSERT INTO student VALUES(2,'张晓峰',44,'1981-10-30','鄠邑区','男'),
(3,'田小娥',23,'1999-11-20','白鹿原','男、女') ;
3)插入部分字段,没有插入字段的值默认值null
insert into 表名 (字段名称1,字段名称2,....) values (值1,值2,...),(值1,值2,...) ;
INSERT INTO student (id, NAME, age, birthday)
VALUES
(4, '王宝强', 35, '1985-02-28'),
(5, '邓超', 34, '1982-10-11') ;
插入表中记录注意事项:
1、出入全表字段的值的数量要盒子段的数量匹配
2、插入同类型字段要注意(字段名称和字段值大类型必须匹配)
删除表的记录
1)一般带条件删除(基本带条件)
delete from 表名 where 字段名称 = 值;
-- 删除id为5的学生信息
DELETE FROM student WHERE id = 5 ; -- id:非业务字段(每一个表都有id:唯一的)
delete from 表名 where 字段名称1 = 值1 and 字段名称2 = 值2;
-- 删除名称为邓超的学生信息 (通过业务字段删除)+附件条件 同时年龄34 的
DELETE FROM student WHERE NAME = '邓超' && age = 34;-- &&:java逻辑符号,and mysql提供的关键字
DELETE FROM student WHERE NAME = '邓超' AND age = 34 ;-- 推荐and
满足多个条件:删除(如果这个字段是业务字段:可能会出现重复)
2)不带条件删除
delete from 表名 ; 删除全表记录
DELETE FROM student ;
删除全表记录的:truncate table 表名;
TRUNCATE TABLE student ;
删除全表delete from 表名 和truncate table 表名的区别?
共同点:
delete from 表名 和 truncate table 表名 都是删除全表的数据
不同点:
delete from 表名 :仅仅只是删除表的记录,表的结构还在(约束相关信息也在)不影响自增主键id的值,下一次插入数据之后,id值在上一次的基础上继续自增!
truncate from 表名 :不仅仅删除全表记录,会将表删除(表的结构就不存在了),会自己创建一张结构一 一 的空表直接影响自增主键id的值
自增主键 auto_increment
CREATE TABLE test(
id INT PRIMARY KEY AUTO_INCREMENT, -- id字段加入主键自增长约束
NAME VARCHAR(10),
age INT
) ;
mysql内置函数:可以获取最后一次自增的id值,插入之后,记录最后一次自增长id的值
SELECT LAST_INSERT_ID() ;
修改表的记录 update 关键字
1)不带条件修改(where)修改,属于“批量修改”
update 表名 set 字段名称 = 值;或者 update 表名 set 字段名称1 = 值1,字段名称2 = 值2;
UPDATE student SET NAME = '邓超' ,age= 18 ;
2)带条件修改
update 表名 set 字段名称 = 值;
UPDATE student SET NAME = '小黑' ;
update 表名 set 字段名称1 = 值1,字段名称2 = 值 2,... where 非业务字段名称 = 值;
UPDATE
student
SET
NAME = '王丽',
age = 23,
address = '北海区',
gender = '女'
WHERE id = 1 ;
DQL语句--数据库的查询语句
基本查询表数据select*(所有字段名称) from 表名; ()
SELECT * FROM student ;
开发中:不写*,必须写上所有的字段名称
1)查询表的记录
select 字段名称1,字段名称2,...from表名;
SELECT
id,
NAME,
age,
sex,
address
FROM
student ;
2)查询的时候给字段名起别名
select 字段名称1 as 别名1,字段名称2 as 别名2,... from 表名;(as可以省略)
SELECT
id '学号',
NAME '姓名',
age '年龄',
sex '性别',
address '地址',
math '数学成绩',
english '语文成绩'
FROM
student ;
3)基本select查询,去除重复的字段值(字段名称前面加上 distinct:去除重复字段值)或者distinct(字段名称) ;
SELECT DISTINCT address '地址' FROM student;
4)基本查询:对int类型字段进行求和
int类型字段进行求和,如果某个字段值是null,结果是null(没有意义)
使用mysql自带的函数:ifnull(字段名称,预期值);如果当前字段值是null,给一个预期值
SELECT
NAME '姓名',
(math + IFNULL(english,0)) '总成绩'
FROM
student ;
DQL之条件查询
1)基本条件:where 条件
赋值运算符 :=
比较运算符:<,>,<=,>=,!=
逻辑运算符: && || and or
2)聚合函数:查询出来结果:单行单列的数据
count (字段名称 ):统计总条数 ----count(非业务字段):统计总记录数
max(字段名称):最大值
min(字段名称):最小值
avg(字段名称):平均分
sum(字段名称):求和
模糊查询(重点)
select 字段列表 from 表名 where 字段名称 like '%关键字%';
%:代表任意字符
_:代表一个字符
in关键字
select *from 表名 where (值1,值2,...);代表多个or连接
SELECT
*
FROM
student
WHERE age = 18
OR age = 30
OR age = 45 ;
SELECT
*
FROM
student
WHERE age IN(18,30,45) ;
查询指定的字段是null的信息
select 字段列表 from 表名 where 某个字段名称 is null;
SELECT
*
FROM
student
WHERE english IS NULL ;
查询某个字段不是null的信息: is not null;
SELECT
*
FROM
student
WHERE english IS NOT NULL ;
排序查询:order by
select 字段列表 from 表名 order by 字段名称 排序规则;
排序规则:默认值 asc:升序 / desc:降序
SELECT
*
FROM
student
ORDER BY math ASC ; -- 默认不写,asc
多个排序条件
select 字段列表 from 表名 order by 字段名称1 排序规则,字段名称2 排序规则...;
SELECT
*
FROM
student
ORDER BY math DESC,
english ASC ;
分组查询:group by
group by 后面跟分组字段 ,在select的时候-查询分组字段
聚合函数在group by 的后面不能使用
select 字段列表 from 表名 group by 字段名称;
先满足条件:当where 和 group by 一块使用,先执行 where ,然后再执行 group by ;
SELECT
sex '性别',
AVG(math)
FROM
student
WHERE math > 70
GROUP BY sex ;
筛选 having
一个sql语句,where条件,有分组 group by ,有having(它后面可以跟聚合函数)依次先后顺序,先满足where,在分组,分组的基础上进行筛选
select 字段列表 from 表名 where 判断条件 group by 字段名称 having 聚合函数;
SELECT
sex '性别', -- 查询分组字段
AVG(math) '数学平均分',
COUNT(id) '总人数' -- 查询满足筛选条件的信息
FROM
student
WHERE math > 70
GROUP BY sex
HAVING
COUNT(id)> 2 ;
分页查询:limit关键字
select 字段列表 from 表名 limit 起始行数,每页显示的条数;
起始行数:从0开始 --- = (当前页码数-1)*每页显示的条数;
查询这个表的时候限制指定的条数
select * from 表名 limit 值;
-- student的第一页的数据;
SELECT * FROM student LIMIT 0,2 ;
-- 第二页数据
SELECT * FROM student LIMIT 2,2 ;
-- 第三页数据
SELECT * FROM student LIMIT 4,2;
-- 第四页数据
SELECT * FROM student LIMIT 6,2 ;
-- 第五也数据
SELECT * FROM student LIMIT 8,2;
-- 查询 这个表的时候限制指定的条数
-- select * from 表名 limit 值;
SELECT * FROM student LIMIT 4;
数据库的备份和还原
DBA(数据库管理员) 设计完数据库,对库中的所有的数据以及结构进行备份;
防止数据库的信息丢失等等
数据库还原:就是将备份库 加载已知存在的库中(使用备份好.sql脚本文件,执行这个sql脚本)
备份和还原:
1)命令行方式的备份(dos管理员的运行)
mysqldump -uroot -p输入密码 库名称 > 指定磁盘路径 "d:\\mysql_dump\\xx.sql文件"
1)命令行方式的还原:
1.1)dos登录mysql,将之前的库删除
1.2)新建一个新的库,使用库
1.3)source 执行本地或者远程路径上的.sql文件 (加载指定的sql脚本文件)
2)使用图形界面化工具去备份和还原
任何的图形界面化工具都是一样的
备份:
在已知的库上右键---backup--->backup SQL 备份到本地磁盘上的sql脚本
还原:删除已知库,新建库,使用库, 存在库上右键--->import--->选择执行 execute SQL script 本地sql文件
数据库的约束
约束:限制用户操作数据库的行为
默认约束:default
create table 表名 (字段名称 字段类型 default 约束条件,...);
CREATE TABLE test(
id INT, -- 编号
NAME VARCHAR(10), -- 姓名
gender VARCHAR(3) DEFAULT '女' -- 性别 使用默认约束default关键字
) ;
非空约束 not null
create table 表名 (字段名称 字段类型 not null,...);
CREATE TABLE test(
id INT , -- 编号
NAME VARCHAR(10) NOT NULL , -- 姓名 不能为空
address VARCHAR(50) NOT NULL -- 地址 不能为空
);
唯一约束unique不能重复
create table 表名 (字段名称 字段类型 unique,...);
CREATE TABLE test(
id INT, -- 编号
NAME VARCHAR(10), -- 姓名
telephone VARCHAR(11) UNIQUE -- 手机号 创建表,给手机号添加唯一
) ;
主键约束 primary key
特点:非空且唯一 ,不能直接插入null值,不能重复
primary key 作用于每一张表的id(非业务字段,设置主键)
create table 表名 (字段名称 字段类型 primary key 自增(auto_increment),...);
CREATE TABLE test(
id INT PRIMARY KEY AUTO_INCREMENT, -- 创建表的时候添加主键,同时自增长
NAME VARCHAR(10)
);
外键约束:foreign key
create table 表名 (
字段名称 字段类型 primary key 自增(auto_increment),
...
constrsint -- 外键名称:主表名_从表名_fk
foreing key (从表字段名称) -- 关联
references 主表(主键字段)-- 主表的主键字段
);
CREATE TABLE employee (
id INT PRIMARY KEY AUTO_INCREMENT,
-- 员工编号
NAME VARCHAR (10),
-- 员工姓名
gender VARCHAR (3),
-- 员工性别
salary DOUBLE,
-- 工资
dept_id INT,
-- 所在的部门编号
-- 声明
CONSTRAINT -- 外键名称:主表名_从表名_fk
dept_emp_fk -- 外键作用在哪个字段上
FOREIGN KEY (dept_id) -- 关联
REFERENCES -- 主表的主键字段(就是id)
dept(id)
) ;
删除外键约束:
alter table 表名 drop foreign key 外键名称 ;
ALTER TABLE employee DROP FOREIGN KEY dept_emp_fk;