java 使用mysql数据表 java操作mysql语法

Mysql数据库

一、基础概念

1、定义

数据库(Database)就是按照数据结构来组织，存储和管理数据的仓库 专业的数据库是专门对数据进行创建，访问，管理，搜索等操作的软件，比起我们自己用文件读写的方 式对象数据进行管理更加的方便，快速，安全

2、作用

对数据进行持久化的保存 
方便数据的存储和查询，速度快，安全，方便 
可以处理并发访问 
更加安全的权限管理访问机制

3、分类

关系型数据库： MySQL，Oracle，PostgreSQL，SQLserver
非关系型数据库：Redis内存数据库，MongoDB文档数据库。

4、基本命令

-- 启动mysql服务：
	net  start mysql;
-- 关闭mysql服务：
	net  stop  mysql;
-- 登录mysql：
	mysql -u root -p(后面可以直接加密码)
-- 查看所有库：
	show databases;
-- 进入某一个数据库：
	use database;
-- 创建数据库 
	create database 库名 default charset=utf8; 
-- 删除数据库 
	drop database 库名;
-- 查看表:
	show tables; 
-- 创建表: 
	create table 表名(字段名1 类型,字段名2 类型)engine=innodb default charset=utf8; 
-- 创建表: 如果表不存在,则创建, 如果存在就不执行这条命令 create table if not exists 表名(字段1 类型,字段2 类型);
create table if not exists users(
    id int not null primary key auto_increment, 
    name varchar(4) not null, 
    age tinyint, 
    sex enum('男','女') 
)engine=innodb default charset=utf8;
-- 删除表: 
	drop table 表名; 
-- 表结构:
	desc 表名;
-- 查看建表语句：
	show create table users;
-- 在 users 表中 追加 一个 num 字段 
	alter table users add num int not null; 
-- 在指定字段后面追加字段 在 users 表中 age字段后面 添加一个 email 字段 
	alter table users add email varchar(50) after age;
-- 在指定字段后面追加字段，在 users 表中 age字段后面 添加一个 phone 
	alter table users add phone char(11) not null after age; 
-- 在表的最前面添加一个字段 
	alter table users add aa int first;
-- 删除字段 alter table 表名 drop 被删除的字段名 
	alter table users drop aa;
修改字段
语法格式： alter table 表名 change|modify 被修改的字段信息 change: 可以修改字段名， modify: 不能修改字段名。 
-- 修改表中的 num 字段 类型，使用 modify 不修改表名
	alter table users modify num tinyint not null default 12; 
-- 修改表中的 num 字段 为 int并且字段名为 nn 
	alter table users change num mm int;
--  注意：一般情况下，无特殊要求，不要轻易修改表结构
-- 修改表名
	alter table 原表名 rename as 新表名

注意：登录之前必须先启动mysql服务，不然会一直报错。

5、my.ini配置文件

my.ini的内容如下：
[mysql] default-character-set=utf8 
[mysqld] port = 3306 basedir=D:\MySQL5.7\mysql-5.7.17-winx64 datadir=D:\MySQL5.7\mysql-5.7.17- winx64\data max_connections=200 character-set-server=utf8 default-storage-engine=INNODB explicit_defaults_for_timestamp=true

6、SQL ( Structure query language ) 结构化查询语言

SQL语言分为4个部分：DDL（定义）、DML（操作）、DQL（查询）、DCL（控制）

快捷键

\G 格式化输出（文本式，竖立显示）
\s 查看服务器端信息 
\c 结束命令输入操作 
\q 退出当前sql命令行模式 
\h 查看帮助

7、增删改查语句

插入    
	insert into 表名(字段1,字段2,字段3) values(值1,值2,值3); 
	insert into 表名(字段1,字段2,字段3) values(a值1,a值2,a值3),(b值1,b值2,b值3); 
查询   
	select * from 表名; select 字段1,字段2,字段3 from 表名; 
	select * from 表名 where 字段=某个值;
修改   
	update 表名 set 字段=某个值 where 条件; 
	update 表名 set 字段1=值1,字段2=值2 where 条件; 
	update 表名 set 字段=字段+值 where 条件; 
删除   
	delete from 表名 where 字段=某个值;

8、数据类型

数据类型：整型、浮点型、字符串、日期等.

字符串：

定长串：char 
	1. 接受长度固定的字符串，其长度是在创建表时指定的。 定长列不允许存储多于指定长度字符的数据。 
	2. 指定长度后，就会分配固定的存储空间用于存放数据
变长串 varchar 
	存储可变长度的字符串 varchar(7) 如果实际插入4个字符, 那么它只占4个字符位置,当然插入的数据长度不能超过7 个字符。
既然变长数据类型这样灵活，为什么还要使用定长数据类型？
	回答：因为性能，MySQL处理定长列远比处理变长列快得多

数值类型：

有符号或无符号 
	所有数值数据类型（除BIT和BOOLEAN外）都可以有符号或无符号 有符号数值列可以存储正或负的数值 无符号数值列只能存储正数。 默认情况为有符号，但如果你知道自己不需要存储负值，可以使用UNSIGNED关键字
注意：MySQL中没有专门存储货币的数据类型，一般情况下使用DECIMAL(8, 2)

9、表的字段约束

表的字段约束 
unsigned 无符号(给数值类型使用，表示为正数，不写可以表示正负数都可以) 
字段类型后面加括号限制宽度 
	char(5). varchar(7) 在字符类型后面加限制 表示 字符串的长度 
	int(4) 没有意义，默认无符号的int为int(11)，有符号的int(10) 
	int(4) unsigned zerofill只有当给int类型设置有前导零时，设置int的宽度才有意义。 
not null 不能为空,在操作数据库时如果输入该字段的数据为NULL ，就会报错 
default 设置默认值 
primary key 主键不能为空,且唯一.一般和自动递增一起配合使用。 
auto_increment 定义列为自增属性，一般用于主键，数值会自动加1 
unique 唯一索引(数据不能重复:用户名)可以增加查询速度,但是会降低插入和更新速度

10、存储引擎

11、MyISAM和InnoDB表引擎的区别

1) 事务支持 
	MyISAM不支持事务，而InnoDB支持。
-- 事物：访问并更新数据库中数据的执行单元。事物操作中，要么都执行要么都不执行 
2) 存储结构 
	MyISAM：每个MyISAM在磁盘上存储成三个文件。 .frm文件存储表结构。 .MYD文件存储数据。.MYI文件存储索引
	InnoDB：主要分为两种文件进行存储 .frm 存储表结构 .ibd 存储数据和索引 （也可能是多个.ibd文件，或者是独立的表空间文件）
3) 表锁差异
	MyISAM：只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如 果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。
    InnoDB：支持事务和行级锁，是 innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有 效的，非主键的WHERE都会锁全表的
 4) 表主键 
 	MyISAM：允许没有任何索引和主键的表存在，索引都是保存行的地址。
 	InnoDB：如果没有设定主键或者非空唯 一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。 InnoDB的主键范围更大，最大是MyISAM的2倍。
 5) 表的具体行数
 	MyISAM：保存有表的总行数，如果select count() from table;会直接取出出该值。
    InnoDB：没有保存表的总行数 (只能遍历)，如果使用select count() from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后， myisam和innodb处理的方式都一样。 
 6) CURD操作 
 	MyISAM：如果执行大量的SELECT，MyISAM是更好的选择。 
 	InnoDB：如果你的数据执行大量的INSERT或 UPDATE，出于性能方面的考虑，应该使用InnoDB表。DELETE 从性能上InnoDB更优，但DELETE FROM table 时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，最好使用 truncate table这个命令。 
 7) 外键 
 	MyISAM：不支持 
 	InnoDB：支持 
8) 查询效率 
	MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM。 
	推荐考虑使用InnoDB来替代MyISAM引擎，原因是InnoDB自身很多良好的特点，比如事务支持、存储 过程、视 图、行级锁定等等，在并发很多的情况下，相信InnoDB的表现肯定要比MyISAM强很多。 
	另外，任何一种表都不是万能的，只用恰当的针对业务类型来选择合适的表类型，才能最大的发挥MySQL的性能优 势。如果不是很复杂的Web应用，非关键应用，还是可以继续考虑MyISAM的，这个具体情况可以自己斟酌。
9）MyISAM和InnoDB两者的应用场景： 
	MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那 么MyISAM是更好的选择。 
	InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要 执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。现在默认使用 InnoDB。

12、条件过滤

排序：
	order by                Asc 升序，默认 desc降序
分组：
	group by
Having 子句 
	having时在分组聚合计算后，对结果再一次进行过滤，类似于where， where过滤的是行数据，having过滤的是分组数据
Limit 数据分页 
	limit n 提取n条数据， 
	limit m,n 跳过m跳数据，提取n条数据
通配符：
	%  - 使用 % 模糊搜索。%代表任意个任意字符
	_  使用 _ 单个的下划线。表示一个任意字符，使用和%类似
注意：where子句中的like在使用%或者_进行模糊搜索时，效率不高，使用时注意： 尽可能的不去使用%或者_ 如果需要使用，也尽可能不要把通配符放在开头处

13、数据导入和导出 — 命令行

导出数据库
-- 不要进入mysql，然后输入以下命令 导出某个库中的数据
	mysqldump -u root -p tlxy > ~/Desktop/code/tlxy.sql
导出数据表
-- 不要进入mysql，然后输入以下命令 导出某个库中指定的表的数据 
	mysqldump -u root -p tlxy tts > ~/Desktop/code/tlxy-tts.sq
导入数据：
-- 在新的数据库中 导入备份的数据，导入导出的sql文件 
	mysql -u root -p ops < ./tlxy.sql 
-- 把导出的表sql 导入数据库 
	mysql -u root -p ops < ./tlxy-tts.sql

14、权限管理

创建用户的语法格式： 
	grant 授权的操作 on 授权的库.授权的表 to 账户@登录地址 identified by ‘密码’
示例：
# 在mysql中 创建一个 zhangsan 用户，授权可以对tlxy这个库中的所有表 进行 添加和查询 的权限 
	grant select,insert on tlxy.* to zhangsan@'%' identified by '123456'; 
# 用户 lisi。密码 123456 可以对tlxy库中的所有表有 所有操作权限
	grant all on tlxy.* to lisi@'%' identified by '123456'; 
# 删除用户 
	drop user 'lisi'@'%';