MySql-入门

SQL

Structure Query Language(结构化查询语言),被国际化标准组织(ISO)采纳为关系数据库语言的国际标准。

​SQL 是一种标准化的语言,它允许你在数据库上执行操作,如创建项目,查询内容,更新内容,并删除条目操作(CRUD)。

SQL语言分类

DDL:数据定义语言,定义数据库对象,create、drop、alter
DML:数据操作语言,操作数据库记录,insert into、update、delete
DCL:数据控制语言,控制数据库权限
DQL:数据查询语言,查询数据库记录

MySql DDL

创建数据库

CREATE DATABASE DATABASE_NAME CHARACTER SET utf8;

删除数据库

DROP DATABASE IF EXISTS DATABASE_NAME;

使用数据库

USE DATABASE_NAME;

删除表

DROP TABLE IF EXISTS TABLE_NAME;

创建表

CREATE TABLE TABLE_NAME (
  字段1 字段类型(长度) 约束,
  字段2 字段类型(长度) 约束,
  字段n 字段类型(长度) 约束
);

常见字段类型
int:java int
bigint:java long
double:java double
decimal:java single/double
char:固定长度字符串,输入字串符长度不够会自动补齐
varchar:范围内自适应长度字符串,范围内输入长度是多少就是多少
text:长字符串型
blob:字节型
date:java.sql.date
datetime:java.util.date
bit:一位
常用约束
主键约束:primary key,唯一,不能为null
唯一约束:unique key,唯一,可以为null
非空约束:not null
​默认约束:default
外键约束:foreign key(colum) REFERENCES ohter_table(colum)
自动增长:auto_increment

增加列

ALTER TABLE TABLE_NAME ADD COLUM_NAME 字段类型 约束;

删除列

ALTER TABLE TABLE_NAME COLUM_NAME;

修改列

ALTER TABLE TABLE_NAME MODIFY COLUM_NAME 字段类型 约束;

MySql DML

插入数据

INSERT INTO TABLE_NAME(colum1,column2,...) VALUES(val1,val2,...)

删除数据

DELETE FROM TABLE_NAME WHERE COLUM = VAL

修改数据

UPDATE TABLE_NAME SET colum1=val1,colum2=val2,...WHERE COLUM = VAL

MySql DQL

SELECT COLUM/内置函数
FROM TABLE_NAME/视图/子查询
WHERE 条件
GROUP BY ...
HAVING ...
ORDER BY colum ASC(升序,默认)/DESC(降序)...
LIMIT start(行号从0开始,=(pageno-1)*size),size

内置函数

CASE
  WHEN ... THEN ... 
  ELSE ...
END

COUNT(colum)
SUM(colum)
AVG(colum)
MIN(colum)
MAX(colum)

条件运算符

=
!=
<
<=
>
>=
is null
is not null
in()
not in()
like ...(%代表任意个字符,_表示一个字符)
and
or

union查询
union会自动去重,union all不会自动去重
union一般在查询结果对应相同,查询条件不同时使用

join查询
表和表之间的关系:一对一,一对多,多对多

SELECT T1.*,T2.*
FROM T1
LEFT JOIN T2
ON T1.colum = T2.colum(消除笛卡尔积)

分类:
内连接 (INNER) JOIN,必须连接的表都有数据才能查出来
左连接 LEFT (OUTER) JOIN,以左边的表为基准
右连接 RIGHT (OUTER) JOIN,以右边的表为基准
注意:
超过三张表禁止JOIN;
JOIN字段数据类型必须一致;
关联字段需要有索引;

数据库优化手段
1、不使用SELECT *
2、不使用LIKE做全文查询
3、union取代or、in
4、不建立外键
5、建立索引

MySql DCL

创建用户

CREATE USER 'USER_NAME'@'主机名' IDENTIFIED BY '密码'

主机名:用户可以在哪个主机上登录,本地可用 localhost,任意远程主机可用通配符 %

授权

GRANT 权限1,权限2... ON 数据库名.表名 TO '用户名'@'主机名'

权限:CREATE、ALTER、SELECT等

撤销授权

REVOKE 权限 ON 数据库名.表名 FROM '用户名'@'主机名'

查看权限

SHOW GRANTS FOR '用户名'@'主机名'

删除用户

DROP USER '用户名'@'主机名'

JDBC连接MySql数据库

导入mysql-connector-java驱动包;

查询操作:

// 动态加载驱动类
Class.forName("com.mysql.jdbc.Driver");
// 获得数据库的连接对象
String url = "";
Connection con = DriverManager.getConnection(url,"账号","密码");
// 获得数据库的操作对象
String sql = "SELECT * FROM TABLE_NAME WHERE colum_id=?";
PrepareStatement ps = con.prepareStatement (sql);
// 设置占位符
ps.setString(1,"700039");
// 执行操作
ResultSet rs = ps.executeQuery();
// 解析结果集
while(rs.next()){
String(字段对应类型) col1 = rs.getString(index从1开始/"col_name");
}
// 依次关闭结果集、操作对象、连接对象

增删改操作:

// 动态加载驱动类
Class.forName("com.mysql.jdbc.Driver");
// 获得数据库的连接对象
String url = "";
Connection con = DriverManager.getConnection(url,"账号","密码");
// 获得数据库的操作对象
String sql = "DELETE FROM TABLE_NAME WHERE colum_id=?";
PreparedStatement ps = con.prepareStatement (sql);
// 设置占位符
ps.setString(1,"700039");
// 执行操作
int count = ps.executeUpdate();
// 依次关闭操作对象、连接对象

Statement和PreparedStatement
都是数据库操作对象

// PreparedStatement对象
String sql = "DELETE FROM TABLE_NAME WHERE colum_id=?";
PreparedStatement ps = con.prepareStatement (sql);
ps.setString(1,"700039");
int count = ps.executeUpdate();

// Statement 对象
String sql = "DELETE FROM TABLE_NAME WHERE colum_id=700039";
Statement statement = con.createStatement ();
int count = statement .executeUpdate(sql);

区别:
1、PreparedStatement需要设置占位符,可以防止sql注入攻击,更加安全
2、Statement每次执行都要编译,PreparedStatement可以预存,不用每次都编译,效率更高
3、PreparedStatement 是 Statement 的子接口

数据库事务

事务:一组要么同时执行成功,要么同时执行失败的sql语句,是数据库操作的基本执行单元。

怎么做?(以下操作保证是同一个连接对象connection)

// 连接对象connection关闭自动提交
connection.setAutoCommit(false);
// 全部执行完后手动提交
connection.commit();
// 中间出现任何问题,回滚
connection.rollback();

事务的特征ASID
actomicity(原子性),一个事务内的操作是一个整体,要么一起成功,要么一起失败;
consistency(一致性),一个事务失败时,所有的数据必须回滚到事务前的状态,保证数据的一致性;
isolation(隔离性),事务之间相互隔离,互不影响
durability(持久性),事务成功后,对数据库的修改是持久的

线程容器
ThreadLoacl threadLoacl = new ThreadLoacl ();
特性:
一个线程能往threadloacl中存放一个元素,这个元素只能由存放的线程取出来。

连接池

创建数据库连接对象很消耗系统资源,使用连接池可以实现连接对象的重复使用。

常见产品:c3p0、durid。

索引

使用索引可以避免全表搜索,提高查询效率。

索引也是一张表,作用相当于图书目录,查询的时候,先从索引中找到符合查询条件的索引值,然后通过保存在索引中的ROWID快速找到表中对应的数据。

索引分类
主键索引
主键字段会自动创建主键索引

唯一索引
唯一字段会自动创建唯一索引

ALTER TABLE_NAME
ADD  UNIQUE INDEX INDEX_NAME (colum) USRING BTREE;

普通索引

ALTER TABLE_NAME
ADD INDEX INDEX_NAME (colum) USRING BTREE;

全文索引
ENGINE = InnoDB 支持事务,不支持全文索引
ENGINE = MyIsam 支持全文索引,不支持事务

组合索引
可以把经常查询的字段绑在一起创建索引,组合索引适用最左匹配原则。

ALTER TABLE_NAME
ADD INDEX INDEX_NAME (colum1,colum2) USRING BTREE;

使用索引的优缺点
优点:可以提高查询效率
缺点:降低更新效率,因为更新数据时,还要更新索引

创建索引的原则
经常作为查询条件的列要建立索引;
经常GROUP BY,Order BY的字段要建立索引;
不同值较少的字段不需要创建索引,如性别;
经常更新的列不需要创建索引。

索引失效
使用explain解析sql。

三大范式

第一范式1NF
列的原子性,每列不可分割

第二范式2NF
在1NF基础上,其他列完全依赖于主键

第三范式3NF
在2NF基础上,列不依赖于其它非主键列,列直接依赖于主键

存储过程

创建存储过程

CREATE PROCDURE PROCDURE _NAME (IN|OUT 参数名 数据类型...)
BEGIN
  sql
END

IN代表输入数据,OUT代表输出数据(返回值)

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
平台声明:文章内容(如有图片或视频亦包括在内)由作者上传并发布,文章内容仅代表作者本人观点,简书系信息发布平台,仅提供信息存储服务。