mysql小记

另外应用的场合就是在服务器管理中，在不涉及数据库服务器程序的父进程的停止运行，也可以用这些工具来终止。为什么数据库服务器的父进程不能用这些工具杀死呢？原因很简单，这些工具在强行终止数据库服务器时，会让数据库产生更多的文件碎片，当碎片达到一定程度的时候，数据库就有崩溃的危险。比如mysql服务器最好是按其正常的程序关闭，而不是用pkill mysqld 或killall mysqld 这样危险的动作；当然对于占用资源过多的数据库子进程，我们应该用kill 来杀掉。

 

更改mysql命令提示符,为了防止误操作

1、临时修改
mysql> prompt u@weiwei 
:m:s->  ，
PROMPT set to 'u@weiwei 
:m:s->'
root@weiwei 01:11:11->
2、永久修改到配置文件里面
[mysql]
prompt=\u@weiwei \r:\m:\s->

查看用户的权限

select user,host from mysql.user;
+------+-----------------------+
| user | host                  |
+------+-----------------------+
| root | 127.0.0.1             |
| root | ::1                   |
|      | localhost             |
| root | localhost             |
|      | localhost.localdomain |
| root | localhost.localdomain |
+------+-----------------------+
6 rows in set (0.00 sec)

SHOW GRANTS FOR 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

密码修改

为管理员root用户设置密码,在linux命令行
mysqladmin -u root password'****'
mysqladmin -uroot password oldboy123 -S /data/3306/mysql.sock

有密码后修改密码
命令行修改密码
mysqladmin -u root -p oldpassword password'****'
#mysqladmin -u root -p password password'*****' -S /data/3306/mysql.sock

通过sql语句修改密码,这种方法适合root密码忘记了的情况来修改密码，这个有风险，条件必须指定正确，必须使用password()函数来加密,有时候单引号可能有问题
update mysql.user set password=password("456") where user='root' and host='localhost'
flush privileges;

通过set password=password('***')


单实例
修改完密码后，如果此时提示关于pid的问题就用以下的方法，则需要mysqladmin -u root -pnewpassword shutdown,然后再开启

多实例的方式

mysql_safe --defaults-file=/data/3306/my.cnf --skip-grant-table &
mysql -u root -p -S /data/3306/mysql.sock 登录

　　

sql分类：

1、数据查询语言（DQL）
select
2、数据操作语言（DML）
insert update delete
3、事务处理语言（TPL）
begin transaction commit rollback
4、数据控制语言（dcl）
grant revoke
5、数据定义语言（ddl)
create drop
6、指正控制语言（ccl)
declare cursor fetch into

常用的就3类
DDL-数据定义语言 create alter drop  =======运维用的多
DML-数据操作语言 select insert delete update=======开发用的多
DCL-数据控制语言 grant revoke commit rollback===========运维用的多

查看建库语句

show create database oldboyG
*************************** 1. row ***************************
       Database: oldboy
Create Database: CREATE DATABASE `oldboy` /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)

创建一个GBK的数据库

create database  oldboy_gbk default character set gbk collate gbk_chinese_ci;
mysql> show create database oldboy_gbk;
+------------+--------------------------------------------------------------------+
| Database   | Create Database                                                    |
+------------+--------------------------------------------------------------------+
| oldboy_gbk | CREATE DATABASE `oldboy_gbk` /*!40100 DEFAULT CHARACTER SET gbk */ |
+------------+--------------------------------------------------------------------+
1 row in set (0.00 sec

创建一个utf8的数据库

create database oldboy_utf8 character set utf8 collate utf8_general_ci;

像这些字符集的问题可以在编译的时候定义进去，指定之后就不用在设置，使用默认值

企业里要根据开发的程序确定字符集（建议utf8），编译的时候指定字符集
-DDEFAULT_CHARSET=utf8
-DDEFAULT_COLLATION=utf8_general_ci

显示当前的数据库
select database();


删除数据库
drop database ****;

删除系统多余账号

drop user "user"@"主机"
如果drop删除不了，可以用下面的方式删除
delete from mysql.user where user='root' and host ='oldboy';
flush privileges;

通过grant命令创建用户并授权：

例子：
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;

grant	all privileges	on dbname.*		to username@localhost		indentified by 'passwd'
授权命令	对应权限	目标：库和表	用户名和客户端主机			用户密码

一步到位的方法
grant all privileges on test.* to oldboy@'localhost' identified by '123'；
show grants for oldboy@localhost;


先创建用户再授权
create user oldgirl@localhost identified by '123';
grant all on dbname.* to username@localhost;

授权局域网内主机远程连接数据库：

根据grant命令，我们知道test@localhost位置为授权访问数据库的主机，localhost可以用域名，ip地址或ip段来代替，因此，要授权局域网内主机可以通过如下方法实现：
1、百分号匹配
grant all on *.* to test@'10.0.0.%' identified by '****'
2、子网掩码匹配
grant all on *.* to test@'10.0.0.0/255.255.255.0' identified by '****'

flush privileges;

mysql用户可以授权的权限,授权的时候最好不要给所有的权限
收回insert权限
revoke insert on test.* from oldboy@localhost;


所以企业中一般都给select ,insert update delete4个权

　　

建表
create table 表名(
<字段名1><类型1>，
……
<字段名n><类型n>);

查看建表语句
show create table ****;

查看建表的描述
desc *****

为表的字段创建索引

创建主键索引，查询数据库，按主键查询是最快的，每个表只能有一个主键列，但是可以有多个普通索引列。主键列要求列的所有内容必须唯一，而普通索引列不要求内容必须唯一。

1、可以在建表的时候就创建索引
create table student(
id int(4) not null AUTO_INCREMENT,	自增
name char(20) not null,
age tinyint(2) not null default '0',
dept varchar(16) default null,
primary key(id),  主键索引
key index_name(name)  普通索引
);

　　

2、建表后通过alter命令增加主键索引（不建议这么搞）
alter table student drop primary key;

alter table student change id id int primary key auto_increment

　　

3、唯一索引，在有主键的情况下，还想让某列唯一，就要创建唯一索引

普通索引
alter table student drop index index_name

alter table student add index index_name(name);
				索引名字
								
唯一索引，通常是用来约束表的内容(非主键)
create unique index index_name on student(name);

4、对字段的前n个字符创建普通索引
create index index_name on table(name(8));
	        索引名字     表	  列
			   
			   
show index from tableG

　　

5、为表的多个字段创建联合索引
如果查询的数据条件是多列时，我们可以为多个查询的列创建联合索引，甚至可以为多列的前n个字符列创建联合索引
create index ind_name_dept on student(name,dept)

drop index ind_name_dept on student;

name列的前8个字符，dept列的前10个字符
create index ind_name_dept on student(name(8),dept(10))


注意：按条件列查询数据时，联合索引是有前缀生效特性的
index(a,b,c)仅a,ab,abc三个查询条件列可以走索引，别的都不可以，b,bc,ac,c等无法使用索引

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

　　

对表的操作

插入,最好是批量插入不要一个个插入
1、按规矩指定所有列名，并且每列都插入值
insert into test(id ,name) values(1,'oldboy');
2、由于id列为自增的，所以，可以只在name列插入值
insert into test(name) values('oldgirl');

3、如果不指定列，就要根据表结构
insert into test values(3,'adfasdf');
也可以一次插多个insert into test values(3,'adfasdf'),(4,'3242342d');

　　

数据库的备份：

mysqldump -uroot -ppass -B database >/opt/oldboy_bak.sql
			    数据库
清空一个文件 >******

恢复
mysql -uroot -ppass oldboy </opt/oldboy_bak.sql

select,不要用*，用具体的列会好

　　

select id,name from test limit 2;
select id,name from test where id=1;
select id,name from test where name='adsfadsf';字符串要加引号

select id,name from test order by id desc;倒叙，默认升序
select id,name from test order by id aesc;升序

　

多表查询,例子

select student.no,student.name,sc.grade,cource.name from student,sc,cource where student.sno=sc.no and cource.jjj

　　

使用ecplain查询select语句的执行计划
如果发现此条sql语句没有走索引可能就会出现问题
explain select * from test where name='dfafds'G

　　

修改表中的数据

update test set name='gongli' where id=3,如果不加条件会将整个表都给改了

　　

防止mysql数据误操作

在登录的时候加上-U参数
mysql -uroot -ppass oldboy -U 		这样就无法对数据写

　　

删除数据

delete from test where id=1 不加条件会全部删除

　　

清空表

truncate table test;

　　

添加字段

alter table test add sex char(4);


alter table test add age(4) after name;


help alter table

　

更改表名

rename table test to test1

alter table test1 rename to test

　　

删除表

drop table test;

　　

mysql插入中文不乱码5种方法：

1、登录mysql，先做”set names latin1;“,然后在执行更新语句或执行语句文件
set names latin1;
source test.sql
2、在sql文件中指定set names latin1;然后登录mysql,通过如下命令执行。
source test.sql
3、在sql文件中指定set names latin1;然后通过mysql命令导入数据
mysql -uroot -ppass oldboy<test.sql
mysql -uroot -ppass oldboy -e "set names latin1;select * from oldboy.test;"
4、通过指定mysql命令的字符集参数实现--default-character-set=latin1
cat test.sql
mysql -uroot -ppass --default-character-set=latin1 oldboy <test.sql
5、在配置文件中改。

不乱码的思想就是客户端与服务端的字符集同意，库表，程序都统一 

客户端
临时更改：
set names latin1
永久：
更改my.cnf 客户端模块的参数，可以实现set names latin1 的效果，并且永久生效

服务端
更改配置文件
[mysqld]
default-character-set=utf8 适合5.1及以前的版本
character-set-server=latin1 适合5.5

库表，程序

create database oldboy default character set utf8 collate utf8_general_ci;

show variables;

show status;

show global status;

key_buffer /etc/my.cnf myisam引擎的缓冲

mysql -e

   GBK      长度2   不是国际标准
   UTF-8        3   中英文混合的环境，建议使用此字符集，用的比较多
   latin1       1   mysql的默认字符集
   utf8mb4      4   utf-8 unicode ，这个好像用于ios开发方面

mysql如何选择合适的字符集

字符集的选择：
   1、如果处理各种各样的文字，发布到不同的语言国家地区，应选unicode字符集，对mysql来说就是utf-8(每个汉字三个字节)，如果应用需处理英文，仅有少量汉字utf-8更好
   2、如只需支持中文，并且数据量很大，性能要求也很高，可选GBK（定长，每个汉字占双字节，英文也占双字节），如果需要大量运算，比较排序等，定长字符集，更快，性能高
   3、处理移动互联网业务，可能需要使用utf8mb4字符集

　　

show variables like 'character_set%';
	
	character_set_client 	 客户端字符集						客户端[client]
	character_set_connection 连接字符集						客户端[client]
	character_set_database   数据库字符集，配置文件指定或建库建表指定			可以在配置文件中的服务端也就是[mysqld]中修改
	character_set_results    返回结果字符集						客户端[client]
	character_set_server     服务器字符集，配置文件指定或建库建表指定			可以在配置文件中的服务端也就是[mysqld]中修改

系统上更改/etc/sysconfig/i18n上修改字符集后需要 . /etc/sysconfig/i18n生效 相当于source /etc/sysconfig/i18n
LANG='zh_CN.utf8'

当字符集不对时，插入的值也是不对的。就算插入之后改成正确的字符集，还是会显示乱码
当字符集不对时，需要根据库和表的字符集进行改

所以要环境不出问题，全都搞utf8

当mysql字符集出现问题即数据出现乱码时，先查看系统，服务端，客户端，库，表等的字符集，默认情况下，建库的时候的默认字符集是跟随系统的，如果创建数据库或者表时改了字符集就要用show create database/table ***G,查看字符集，然后修改字符集为该库/表的字符集set names ***。　　

　

对于已有的数据库想修改字符集不能直接通过"alter database character set ****"或"alter table tablename character set ***"，这两个命令都没有更新已有记录的字符集，而只是对新创建的表或者记录生效。
已经有记录的字符集的调整，必须先将数据导出，经过修改字符集后重新导入后才可完成。

下面模拟将latin1字符集的数据库修改成GBK字符集的实际过程。

1、导出表结构
	mysqldump -uroot -p --default-character-set=latin1 -d dbname > alltable.sql，以latin1的形式导出
	--default-character-set=GBK字符集进行连接 -d 只导表结构
	
	2、编辑alltable.sql将latin1改成GBK
	
	3、确保数据库不再更新，导出所有数据
	mysqldump -uroot -p --quick --no-create-info --extended-insert --default-character-set=latin1 dbname>alldata.sql
	参数说明：
	--quick:用于转储大的表，强制mysqldump从服务器一次一行的检索数据而不是检索所有行，并输出前cache到内存中。
	--no-create-info:不创建create table语句
	--extended-insert:使用包括几个values列表的多行insert语法，这样文件更小，io也小，导入数据时会非常快
	--default-character-set=latin1：按照原有字符集导出数据，这样导出的文件中，所有中文都是可见的，不会保存成乱码
	
	4、打开alldata.sql将set names latin1修改成set names gbk;（或者修改系统的服务端和客户端）
	
	5、建库
	create database dbname default charset gbk;
	
	6、建表，执行alltable.sql
	mysql -uroot -p dbname<alltable.sql
	
	7、导入数据
	mysql -uroot -p dbname<alldata.sql