mysql入门

mysql是最流行的关系型数据库，因为它是开源的。它现在是oracle公司的，有被闭源的可能，所以现在流行的linux发行版如CentOS7已经不提供安装了，但是可以用其一个分支mariadb代替，用法一样。

基本操作

连接

$ mysql -h localhost -u root -p

修改密码

$ mysqladmin -u root -p password 123456

常用sql命令

show databases;-- 查看有哪些数据库
create database db1;-- 创建数据库db1
use db1;-- 进入数据库db1
drop database db1;-- 删除数据库db1
show tables;-- 查看当前数据库下存在哪些表
exit
quit; -- 退出连接
create table person(id int(4),name char(10),age int);-- 创建表
create table p(id int,name char(10))charset=gbk;-- 创建支持插入中文(gbk/utf8)记录的表
create table p(id int auto_increment primary key, name char(10) not null,age int default 100);-- 带列完整性约束创建
desc person;-- 查看表结构
insert into person(id,name,age) values(1,'aaa',22);-- 插入记录
insert into person set id=1,name='aaa',age=22;-- 另一种插入方式
select * from person;-- 查询所有记录
update person set age=20 where name='aaa';-- 更新记录
delete from person where id=1;-- 删除记录
truncate person;-- 清空表
drop table person;-- 删除表

source x.sql;-- 执行sql脚本文件
\. x.sql;-- 另一种执行方式
select database(),version(),current_date,now(),user();
load data local infile '/path/a.txt' into table a;-- 装载数据
alter table user modify column id int primary key auto_increment;-- 修改字段
rename table tab_name1 to tab_name2;-- 重命名表

where条件

• where后跟一个逻辑表达式；
• 逻辑表达式内部使用关系运算符，如=,<,>,<=,>=,<>等，还有between...and,in等运算符；
• 逻辑表达式之间可以用and,or操作；

约束

• 主键：primary key(col1,col2,...)
• 自增：auto_increment
• 非空：not null
• 唯一：unique
• 默认值：default value

查用查询命令

select name,age from person where id=1;
select name as 姓名,age as 年龄 from person;
-- 统计函数：count(),sum(),max(),min(),avg()
select now();-- 获得当前时间
select user();-- 查看登录用户
select 7*8*9;-- 计算器
select distinct id,age from person;-- 去重
select id,name from person where id between 3 and 7;
select name from person where id not in(3,4,5);
select name from person where age is not null;
select name from person where name is like '%A_';-- 查询名字倒数第二个字符为A的记录
select name from person where name regexp '大';-- 正则表达式查询
select count(name) from person group by age; -- 按年龄分组统计人数
select count(name) from person group by age having count(name)>1;-- 显示大于1的
select count(name) from person group by age having age!=40;-- 不统计年龄为40的
select * from person limit 0,3;-- 取前3条记录
select * from person order by name asc,age desc;-- name升序age降序
select * from p1 union select * from p2;-- 联合查询，去重
select * from p1 union all select * from p2;-- 不去重

select语序
x|where|having
—|—|—
顺序|在group之前|在group之后
可用字段|所有|group,select,外查询
习惯|常用|与group by连用

子查询

将一个查询语句嵌套到另一个查询、插入、修改、删除语句中，这个查询语句就是一个子查询。

select id,name,age from p1 where id>=(select max(id) from p2);
select id,name,age from p1 where id in (select id from p2);
select * from (select * from p1 where id<=floor(ran()*5)) as p3 where name regexp '大';
select * from p1 where exists(select * from p2 where p1.age=p2.age);

连接查询

连接查询即多表查询。

外连接：左连接、右连接、全连接（mysql不支持全连接）。

select * from p1 left join p2 on p1.id=p2.id
select * from p1 right join p2 on p1.id=p2.id

交叉连接：表间进行笛卡尔积。

select * from p1,p2;

内连接：内连接每条记录的连接条件都是相同的。

select * from p1 inner join p2 on p1.id=p2.id
select * from p1 inner join p2 using(id);-- 另一种形式的内连接

自然连接：

select * from p1 natural join p2;

高级操作

事务

事务主要用于处理操作量大，复杂度高的数据。

• 在mysql中只有使用了Innodb数据库引擎的数据库或表才支持事务。
• 事务处理可以用来维护数据库的完整性，保证成批的sql语句要么全部执行，要么全部不执行。
• 事务用来管理insert，update，delete语句。

事务特性：ACID

• 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。
• 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。
• 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
• 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT来实现

• BEGIN #开始一个事务
• ROLLBACK #事务回滚
• COMMIT #事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

• SET AUTOCOMMIT=0 #禁止自动提交
• SET AUTOCOMMIT=1 #开启自动提交

事务例子：

create table aaa(id int)engine=innodb;
select * from aaa;
begin;
insert into aaa value(5);
insert into aaa value(6);
commit;
select * from aaa;
begin;
insert into aaa value(7);
rollback;
select * from aaa;

alter

alter是用来修改表的，直接上例子：

create table test1(i int, c char(1));
show columns from test1;
alter table test1 drop i;
alter table test1 add i int;
alter table test1 add i int first;
alter table test1 add i int after c;
alter table test1 modify c char(10);
alter table test1 change i j bigint;
alter table test1 change j j int;
alter table test1 modify j bigint not null default 100;
alter table test1 alter i set default 1000;
alter table tets1 alter i dorp default;
alter table test1 engine=myisam;
alter table test1 rename to test2;
alter table test1 drop foreing key key_name;
alter table test1 modify name1 type1 first|after name2;

索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。
索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。
创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。

普通索引

建立索引：

create index idxname on tabname(colname(length));
alter table tabname add index idxname(colname);

删除索引：

drop index [idxname] on tabname;

唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

create unique index idxname on tabname(colname(length));
alter table tabname add unique [idxname] (colname(length));

显示索引信息

show index from tabname;