MySQL数据库——基本操作

关系型数据库

• 永久保存数据；
• 提供数据安全；
• 提供数据的并发访问；
• 提供多种数据类型；
• 提供事务支持；
• 提供SQL语言操作数据；

著名的数据库

• Oracle 甲骨文
• MySQL 甲骨文
• Sql Server 微软（主要用在windows环境）
• DB2 IBM
• SQLlite 用于移动端开发

安装MySQL

解压后
bin/mysql.exe 客户端程序
bin/mysqld.exe 服务端程序
bin/mysqldump

操作数据库

• 初始化数据库
  bin\mysqld --initialize --console
  –console显示初始化过程信息
  初始化后会生成data文件夹，其保存系统和用户的数据；
  
  注意记录临时密码（在初始化信息最后一行）

• 启动数据库的服务
  bin\mysqld --console
  端口号是3306
  可以使用Ctrl+c停止服务或直接关闭黑窗口；

• 使用客户端去连接服务器
  bin\mysql -u 用户名 -p
  初始化的时候mysql只有一个root用户（管理员）；
  正确登录后会有“mysql>“提示符，如果想退出使用命令quit

修改密码
alter user ‘用户名’@‘localhost’ identified by '新密码'；

配置环境变量

将mysql安装成系统服务

bin\mysqld intsall 服务名服务名默认为MySQL;以后每次开机自动启动。
计算机管理——>服务和应用程序——>服务；
第一次安装后需要手动启动。

卸载
sc delete 服务名
如果运行安装服务或删除服务出现权限不足的问题使用管理员身份来执行

建库

• 建库SQL语句（都是在MySQL提示符下运行）
  create database 数据库名；

• 查看建库信息
  show create database 数据库名;

• 设置默认字符编码

  • 建库语句上带上字符编码；
    MySQL中的utf8字符不够完整，如果想包含全部字符就选择utf8mb4；
    create database 数据库名 character set utf8;

  • 在创建MySQL服务的时候加上配置文件；
    改变全局的设置，在mysql解压目录下创建my.ini文件
    文件内容

  [mysqld]
  character-set-server=utf8mb4

停止旧服务：net stop mysql;
删除旧服务：sc delete mysql
安装新服务：mysqld install
启动新服务：net start mysql

删除库
drop datebase 数据库名;

基本操作

建表
一个数据库中有多张表，每张表可能会有多条数据；
表分为行(row)和列(column)

create table 表名(
	列名		数据类型，
	列2名	数据类型，
	...
);

如果表已经存在，则不执行。

create table if	not	exists	表名(
	列名		数据类型，
	列2名	数据类型，
	...
);

比如创建一个学生表：

create table student(
	id int，
	name varchar(10),
	sex char(1)
);

• 整数类型：tinyint(1个字节对应java的byte)、smallint(2个字节对应short)、int(4字节)、bigint(8字节对应long对应)；
• 浮点类型：float、double；
• 定点小数：准确存储小数，但是花费资源较多,decimal(总位数，小数位数)；
• 字符类型：
  char(lenth) 表示最多存lenth个字符，定长 ,存储时，长度不足，用空格补齐；
  varchar(lenth) 表示最多存10，存储时根据实际长度来存储；
• 日期类型：
  datetime
  timestamp
  选库
  use 库名;

插入数据
注意值的个数与列的个数一致；
insert into 表名(列1，列2...列n) values (值1，值2...值n);

查询数据
select 列1,列2... from 表名;

查看所有库
show databases;

查看所有表
show tables

唯一主键
每张表只能有一个主键，主键的值必须是唯一且非空的；

create table 表名(
	列名		数据类型		primary key，
	列2名	数据类型，
	...
);

自增列
用来解决主键冲突问题,由于加了自增列的列有数据库维护，所以插入数据时不在写被维护的列；
在主键后加上：auto_increment

create	table	[if not exists] 表名{
	列名		数据类型		约束，
	...
}

约束类型:
not null:表示此列不能为空；
unique:表示此列的取值是惟一的；
primary key:唯一且非空，一个表只能有一个列为主键。

一次插入记录
insert into student(name,sex) values(n1,s1),(n2,s2)...;

删除记录
只删除记录，不删表
delete from 表

删除表

drop table 表

只删除指定记录
删除student表中id=6的记录
delete from student where id=6

日期列
create table a(birthday datetime);

添加列
alter table 表名 add 列名 列类型;
例子：给学生表student添加age字段unsigned非负数，无符号数；
alter table studnet add age int unsigned;

修改列
alter table 表名 modify 列名 新类型;
例子：将要name字段长度修改
alter table student modify name varchar(20);

删除列
alter table 表名 drop 列名;

列别名
select id id的别名 from 表名;
不改变底层真正的名字。

重命名列(mysql8.0才有)

SQL语句

DDL数据定义语言

• create database 数据库名；
• create table 表名(列定义);
• drop database 数据库名；
• drop table 表名；
• alter table 表 …(添加列，修改列，删除列，重命名列)；
• alter user 用户；

DML(数据操控语言)（重点）

• 语法1（插入一行或多行）：insert into 表名 (要赋值的列，用","分隔) values (要插入的值，和前面的列名对应);

• （从表1查询把查询的结果插入表2，如果两个表结构一样，列名可以省略）：insert into 表2 select * from 表1;
  如果表结构不一样：insert into 表2 select 要插入的列 from 表2；

• load data(把外部文本文件的内容导入到数据库表中)
  语法：load data infile ‘文件路径\文件名字’ into table 表名;
  文件中的内容要和表结构对应;load data要对配置文件中加入secure-file-priv=默认值为null表示不允许加载文件；如果指定了目录则只能从该目录加载文件；要是空串表示可以从任意路径加载文件；改动my.ini文件必须重启服务。
  查看secure-file-priv=的值执行SQLshow variables like ‘secure-file-priv’;

• 以指定分隔符分割的文件
  load data infile ‘文件路径\文件名字’ into table 表名 [fields|column] terminated by '分隔符';

• source
  把SQL语句写在外部文件（一般以.sql结尾的文件，文件内容必须是合法的SQL语句);source 文件路径/文件名;
  注意:

  • 文件名不用加单引号；
  • 路径分隔符使用/斜杠，且不用转义；
  • 文件编码操作系统编码一致（gbk编码）；

update更新
语法：update 表名 set 列名=新值;把所有记录都会修改
修改指定列：update 表名 set 列名=新值 where 条件;
例子：update person set sex='男' where id=1;

delete删除
语法：delete from 表名；//删除所有记录
删除指定列：delete from 表名 where 条件;

select查询
语法：select 列名... from 表名 where 条件;

条件

• = 等值相等；
• ！= 不等于；
• > 大于…
  逻辑运算符组合多个条件
• 逻辑与 and
• 逻辑或 or
• 逻辑非 not
• 列 between 值1 and 值2 等价于 列>=值1 and 列 <=值2（必须小的值在前大的在后，包含边界）
• 列 in (值1，值2，值3…)
• like 模糊查询 其中匹配通配符%表示匹配零到多个任意字符。_表示一个任意字符；

去除这一列的重复值
select distinct 去重的列 from 表;
count(distinct 列名):取出并统计这一列的个数。

order by（排序）
排序条件：列名、升序（asc）、降序（desc）
如果省略升降序默认为升序，
select * from 表名 order by 列 [asc|desc] limit n(取前几条);
多列排序
先按照条件1排序，若条件1取值相同则按条件2排序。
select * from 表名 order by 排序条件1,排序条件2... [asc|desc]

limit（限制返回的结果个数）

• limit m;//m为数字表示最多返回m条记录；
• limit n,m;//m返回记录数，n代表起始下标，下标从0开始；

分页引用
第一页 limit 0，

group by（分组条件）
分组之后select子句中只能出现分组条件列和组函数，其他列不能出现；

• group by column//按列column分组select count(*) from 表名 group by column;
  count(*)：表示求每组的个数；
  max(列) 求最大值；
  min(列) 求最小值；
  sum(列) 求和；
  avg(列) 求平均值；

having 也是过滤

where>group by>having>select>order by>limit//SQL语句执行顺序

多列分组
列的顺序不影响结果
select count(*) from 表名 group by column1,colimn2..;

多表结构和连接查询

• 将两张表连接起来
  select ... from 表1 inner join 表2 on 连接条件;（内连接）

emp表

+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-07-13 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-07-13 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

dept表

+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

将两张表按指定列连接到一起·
select empno,ename,sal,emp.deptno,dept.deptno,dname,loc from emp inner join dept on emp.deptno=dept.deptno;

+-------+--------+---------+--------+--------+------------+----------+
| empno | ename  | sal     | deptno | deptno | dname      | loc      |
+-------+--------+---------+--------+--------+------------+----------+
|  7782 | CLARK  | 2450.00 |     10 |     10 | ACCOUNTING | NEW YORK |
|  7839 | KING   | 5000.00 |     10 |     10 | ACCOUNTING | NEW YORK |
|  7934 | MILLER | 1300.00 |     10 |     10 | ACCOUNTING | NEW YORK |
|  7369 | SMITH  |  800.00 |     20 |     20 | RESEARCH   | DALLAS   |
|  7566 | JONES  | 2975.00 |     20 |     20 | RESEARCH   | DALLAS   |
|  7788 | SCOTT  | 3000.00 |     20 |     20 | RESEARCH   | DALLAS   |
|  7876 | ADAMS  | 1100.00 |     20 |     20 | RESEARCH   | DALLAS   |
|  7902 | FORD   | 3000.00 |     20 |     20 | RESEARCH   | DALLAS   |
|  7499 | ALLEN  | 1600.00 |     30 |     30 | SALES      | CHICAGO  |
|  7521 | WARD   | 1250.00 |     30 |     30 | SALES      | CHICAGO  |
|  7654 | MARTIN | 1250.00 |     30 |     30 | SALES      | CHICAGO  |
|  7698 | BLAKE  | 2850.00 |     30 |     30 | SALES      | CHICAGO  |
|  7844 | TURNER | 1500.00 |     30 |     30 | SALES      | CHICAGO  |
|  7900 | JAMES  |  950.00 |     30 |     30 | SALES      | CHICAGO  |
+-------+--------+---------+--------+--------+------------+----------+

几种连接查询

• 表1 inner join 表2 on 连接条件;（内连接：两张表中的记录必须完全满足连接条件才会出现在最后结果）
• 表1 left outer join 表2 on 连接条件;(左外连接)和上面连接不同的是部门表放在了左侧，且是左外连接；部门表中的记录不管有没有连接到emp中，最后结果中都有记录；位于连接左侧的表，不管是否连接到记录，都会出现在结果中，其中outer关键字可以省略；
• 表1 right outer join 表2 on 连接条件;(右外连接：和左外连接含义一样)

左外连接，为了使语句看起来简介下面语句用到了dept d这种起别名的方法。
select empno,ename,e.deptno,d.deptno,d.dname,d.loc from dept d left outer join emp e on d.deptno=e.deptno;
可以看到在内连接中由于部门编号为40的部门没有员工，所以结果没有出现部门编号为40的记录；而在左外连接中部门表放在连接左边，即使没有部门编号为40的员工，但是也出现了部门编号为40的记录，记录结果为null;
| NULL | NULL | NULL | 40 | OPERATIONS | BOSTON |

+-------+--------+--------+--------+------------+----------+
| empno | ename  | deptno | deptno | dname      | loc      |
+-------+--------+--------+--------+------------+----------+
|  7782 | CLARK  |     10 |     10 | ACCOUNTING | NEW YORK |
|  7839 | KING   |     10 |     10 | ACCOUNTING | NEW YORK |
|  7934 | MILLER |     10 |     10 | ACCOUNTING | NEW YORK |
|  7369 | SMITH  |     20 |     20 | RESEARCH   | DALLAS   |
|  7566 | JONES  |     20 |     20 | RESEARCH   | DALLAS   |
|  7788 | SCOTT  |     20 |     20 | RESEARCH   | DALLAS   |
|  7876 | ADAMS  |     20 |     20 | RESEARCH   | DALLAS   |
|  7902 | FORD   |     20 |     20 | RESEARCH   | DALLAS   |
|  7499 | ALLEN  |     30 |     30 | SALES      | CHICAGO  |
|  7521 | WARD   |     30 |     30 | SALES      | CHICAGO  |
|  7654 | MARTIN |     30 |     30 | SALES      | CHICAGO  |
|  7698 | BLAKE  |     30 |     30 | SALES      | CHICAGO  |
|  7844 | TURNER |     30 |     30 | SALES      | CHICAGO  |
|  7900 | JAMES  |     30 |     30 | SALES      | CHICAGO  |
|  NULL | NULL   |   NULL |     40 | OPERATIONS | BOSTON   |
+-------+--------+--------+--------+------------+----------+

连接查询的等价写法

• 内连接查询的等价写法：select ... from 表1,表2 where 连接条件;
• selct ... from 表1 inner join 表2 inner|left join using(deptno);//两张表连接列名要相同。

常用函数

• count(*)
• max()
  help functions;
• Bit Functions 位运算符
• Comparison operators 比较运算符
• Control flow functions 流程控制
• Date and Time Functions 日期
  • year() 截取年份
  • month()
  • date()
  • date_add(日期 日期间隔); 其中时间间隔的语法：interval n 单位
• Encryption Functions 加密
• Information Functions
• Logical operators 逻辑运算符
• Miscellaneous Functions
• Numeric Functions 数学函数
  • rand() 生成一个[0.0~1.0)之间的随机小数
  • floor() 舍去小数
  • round() 四舍五入
• String Functions 字符串函数
  • left(字符串，n) n代表 从左边要截取的字符
  • lower() 转小写
  • upper() 转大写
  • substr(字符串，下标，长度)

导出数据
cmd>mysqldump -u root -p 库名 >> 要保存的文件（source的逆操作）
select * from 表 into outfile '文件路径名\文件名'; (load data infile的逆操作)

子查询（sub query）

把子查询当做一个值
分解问题:

案例：求最高工资
select max(sal) from emp; 第一步：5000把它看做一个值
select * from emp where sal=5000; 第二步：写主查询

子查询
select * from emp where sal=(select max(sal) from emp);

把子查询当做一个表
每个部门的最高工资

按部门分组
select max(sal),deptno from emp group by deptno;

把上面查询结果看做一张临时表，它可以与其他表之间做连接操作
select * from emp b inner join a on b.deptno=a.deptno and b.sal=a.sal;

把子查询带入
select * from emp b inner join (select max(sal),deptno from emp group by deptno) a on b.deptno=a.deptno and b.sal=a.sal;

case when
可以配合select工作
类似于java中的 if else

语法：

case
	when 条件1 then 结果1
	when 条件2 then 结果2
	...
	else
end	

2000以下显示低工资，2000~3000显示中等工资，3000以上显示高工资

select empno,ename,sal,
case
	when sal<=2000 then '低工资'
	when sal>2000 and sal <=3000 then '中等工资'
	else '高工资'
end  工资级别 from emp;

DCL(数据控制语言)

• grant(授权)：

• revoke(回收)：

创建用户
create user 用户名 indentified by '密码';

授权语法
grant 权限 to 用户名;

例如：
把查询权限授权给新用户
grant select on test.* to 新用户;

回收权限
revoke 权限 from 用户名;

事务和锁

事务（transaction）

多条SQL语句视为一个整体执行，多条语句要么都成功，如果其中有一个失败了那么之间的也的撤销；这种多条语句成为事务。

mysql的事务控制
默认情况下，一条语句是一个事务；如果想要多条语句为一个事务需要通过begin commit rollback

begin//表示事务的起点 等价方式：start transaction
sql1;
sql2;
···
commit(提交，结果都生效)和rollback（回滚，撤销事务内所做的更改）表示事务的终点。

TCL（transaction controll）(事务控制语言)

锁

InnoDB 行级锁：只要两个客户端更新的是不同的行，互不干扰
MyISAM 表锁：锁住整个表

增删改都会在行上加排他锁（X锁）
查询可以加共享锁（S锁）表示可以同时查询，但其他人不能增删改。
select * from 表名 lock in share mode;
查询时加排它锁
select * from 表 for update； 别人不能再加排他锁，

多版本并发访问（MVCC）
select 就是利用得多版本并发查询，好处：并发性高
不用锁也能实现并发访问，产生一个数据的副本，查询的是旧值，更改的是新值在副本，等修改完了，再用新的内容替换旧的内容，实现查询和更改的并发。
java中的java.util.concurrent.CopyOnWriteArrayList也是这个原理。与Vector的区别，Vector是全局加锁，保证线程安全。

事务的四个特性

原子性（A）：多个sql要作为一个整体运行，不可分割；
一致性（C）：一个事务内结果应当是一致的；
隔离性（I）：
持久性（D）：事务一旦提交，修改就行该永久生效。

事务隔离性
有不同的隔离级别，隔离级别越低，并发性越好，但是数据的一致性较差。
改变隔离级别：在my.ini配置文件里transaction-isolation=READ-COMMITIED

隔离级别：读未提交<读提交<可重复读(mysql默认级别)<序列化读
序列化读：把多版本并发退化到所机制并发控制。

错误现象：脏读、不可重复读、幻读

• 脏读（读未提交）：事务2查到了事务1修改了但是未提交的数据。如果事务1回滚了，则事务2读到了无效的数据。
  如何避免：提高隔离级别到读提交的隔离级别。

• 不可重复读（读提交）：一边做查询，一边做修改，一个事务内，多次查询结果不一样。
  如何避免：将隔离级别提高到可重复读。

• 幻读（可重复读）：一边做查询，一边做新增操作。多次查询结果不一致。
  如何避免：将隔离级别提高到序列化读。

---

其他sql语句

查看建表语句：show create table 表名;
\G可以取代’;’,效果是把表行转列。
查看系统变量的值：select @@系统变量名;
查兰所有系统变量：show variables;
查看事务的隔离级别：select @@transaction_isolation；
查看端口号：select @@port；
查看字符集：select @@character_set_server;

外键约束

某一列的取值来自于另一张表（列必须是主键或唯一的）
语法：

	[constraint 约束名] foreign key(本表列名) references 主表(列名);

create	table cource(
	cid	int	primary key,
	cname varchar(20) not null,
	tid int,
	foreign key(tid) TEACHER_FK references teacher(tid)
);
//本表中tid这一列的取值，要引用teacher中的tid列