Mysql学习笔记(一)

Mysql是一种关系型的数据库管理系统，特点是将数据保存在不同的表中，再将这些表放入不同的
数据库中，而不是将所有数据保存在一个大仓库中。
成功最有效的方法是向有经验的人学习，站在巨人的肩膀上成长

1）Mysql的优势
1.Mysql性能卓越，服务稳定，很少出现异常宕机
2.开放源码无版权制约
3.社区及用户活跃，解决方案多
4.体积小，安装使用简单，易于维护
5.支持多种操作系统，提供多种API接口，支持多种开发语言

2）Mysql商业版与社区版区别:
 1.商业版组织管理与测试环节控制更严格，稳定性方面会比社区版本更稳定
 2.Mysql是成熟产品，两者性能方面差距不大
 3.商业版不遵循GPL协议，社区版遵循GPL协议可以免费使用
 4.使用商业版后可以购买相关的服务

3）Mysql版本选择建议
1)稳定版:选择开源的社区版的稳定版GA版
2)产品线：可以选择5.1或者5.5，互联网公司主流5.5，其次5.1和5.6
3)选择Mysql数据库GA版发布后6个月以上的版本
4)要选择前后几个月没有大Bug修复的版本，而不是大量修复BUG的集中版本
5)最好向后较长时间没有更新发布的版本
6)要考虑开发人员开发程序使用的版本是否兼容你选的版本
7)作为内部开发测试数据库环境，跑大概3-6个月时间
8)优先企业非核心业务采用新版本的数据库GA版本软件
9)向DBA高手请教

4）Mysql启动基本原理说明
/etc/init.d/mysqld是一个启动脚本，启动后会调用mysqld_safe脚本，最后调用mysqld服务
启动mysql。

5）优雅的关闭数据库

1.mysqladmin -uroot -phaha123 shutdown

2./etc/init.d/mysqld stop

**注意:千万不要用kill,pkill,killall直接杀数据库进程

history -c清除所有历史命令
history -d n 清除某一条命令

6) 修改mysql提示符
1.临时修改mysql提示符
prompt \u@\h [\d] \r:\m:\s
2.永久生效 my.cnf
[mysql]
prompt = \\u@\\oldboy [\\d]
3.通过环境变量配置。
Bash代码  
export MYSQL_PS1="(\u@\h) [\d]> "  

善用mysql数据库的帮助

7)增加修改mysql用户
增加system并提升为超级管理员，即和root等价的用户
grant all privileges on *.* to system@'localhost' identified by 'haha123' with grant option;

删除mysql.user表中所有的用户
drop user root@'localhost';
drop user root@'127.0.0.1'；
...
**记得删除test数据库
修改密码方法一:mysqladmin -uroot -phaha123 password 'hehe123';
修改密码方法二:update mysql.user set password=password('123123') where user=root and host='localhost';
               flush privileges;
修改密码方法三；set password=password('123123');
                flush privileges;

8）丢失mysql root密码解决方案

1.关闭mysql服务
/etc/init.d/mysqld stop

2.加参数启动服务
单实例:mysqld_safe --skip-grant-tables --user=mysql

多实例:mysqld_safe --defaults-file=/data/3306/my.cnf --skip-grant-tables --user=MySQL &

3.修改root密码
update mysql.user set password=password('123123') where user=root and host='localhost';
flush privileges;

4.重启mysql服务
单实例关闭服务:mysqladmin -uroot -p123123 shutdown

多实例关闭服务:mysqladmin -uroot -p123123 shutdown -S /data/3306/MySQL.sock
单实例:/etc/init.d/mysqld start

多实例启动:/data/3306/mysql start #脚本启动

SQL语言分类；
DQL数据查询语言 select
DML数据操作语言 insert update 和delete
TPL 事物处理语言 begin transaction ,commit,rollback
DCL 数据控制语言 grant revoke
DDl 数据定义语言 create drop
CCl 指针控制语言 declare cursor,fetch into

9）.数据库常见管理应用
1.创建数据库
create database 数据库名
create database test1 charset utf8 collate utf8_general_ci;

show databases;
show databases like '%test%';
select database();
drop database 数据库名;
use test #切换数据库

select user();
select now();
drop user 'user_name'#'hostname';

2.授权
方法一:grant all privileges on test.* to test@'localhost' identified by 'haha123';
show grant for 'test'@'localhost'; #查看权限
方法二:create user haha@'localhost' identified by 'haha123';
grant all on test.* to haha@'localhost';

授权局域网主机访问
grant all on test.* to haha@'192.168.1.%' identified by 'haha123';
grant all on test.* to haha@'192.168.1.0/255.255.255.0' identified by 'haha123';
revoke insert,update on test.* from root@'localhost';
mysql -uroot -phaha123 -e 'command' #在命令行执行SQL语句

企业生产环境下如果授权用户权限？
 

博客，cms等产品的数据库授权
   对于web链接用户授权尽量采用最小化原则，很多开源软件都是web界面安装，在安装期间除了
select，insert，update，delete4个权限外，还需要create,drop等比较危险的权限
grant select,insert,update,delete on blog.* to 'blog'@'192.168.200.%' identified by 'oldboy';
常规情况下授权select，insert，update,delete4个权限即可，有的开源软件，还需要create,drop等比较危险的权限

生成数据库后再收回create,drop授权
revoke create,drop on blog.* from 'blog'@'192.168.200.%';

3.表操作

创建表

mysql>create table test( id int(4) not null,
name char(20) not null, age tinyint(2) not null default '0',
dept varchar(16) default NULl);

查看表结构:desc table_name;

4.索引

创建索引：key index_name(列名)
create table test1( id int(4) not null primary key,
name char(20) not null, age tinyint(2) not null default '0',
dept varchar(16) default NULl,key key_id(age));

添加索引:alter table test add index index_name(列名)；

创建联合索引:create index index_name on student(name,id);

删除索引:drop index key_id on test1;

在某个列的前n个字符创建索引
create index index_name  on student(name(4),id(2));

创建唯一索引(非主键)
create unqiue index index_name on test(name,id);

索引可以加快查询速度，那么给所有的列创建索引可以吗?
    因为索引不但占用系统空间，更新数据库还要维护索引数据。因此，索引也是一把双刃剑，
并不是越多越好。例如,数十到几百行的小表上无需建立索引，更新频繁，读取比较少的表
要少建立索引。
到底在那些列上创建索引呢?
 select user,host from mysql.server where host=... 索引一定要建立在条件列上，而不是
select后选择数据的列。我们尽量在唯一值多的大表上建立索引

索引小结:
1.索引会加快查询速度，但是会影响更新的速度，对于频繁写入的表，不建议建立索引。
2.索引不是越多越好，要在频繁查询的where后的条件列上创建索引
3.小表或者唯一值较少的表不建立索引，要在大表以及不同内容多的列上创建索引

5.插入语句

insert语句
insert into test1(id,name,age,dept) value(1,'oldboy',18,'hahah');

insert into test1(name,age) values('zhangsan',15),('lisi',14),('te',12);
插入多行数据:insert into test1(id,name,age) values(2,'zhangsan',15),(3,'lisi',14),(4,'te',12);

mysqldump -uroot -phaha123 -B test > /opt/test.sql

6.修改表结构:
alter table 表名 add 字段 类型 其他;

7.更改表名:
rename table old_name to new_name;

8.清空整个表

truncate table test; #清空表（物理删除）
delete from test;清空表(逻辑删除)，很危险的操作