MySQL（二）

最新推荐文章于 2024-11-12 10:56:30 发布

weixin_30240349

最新推荐文章于 2024-11-12 10:56:30 发布

阅读量132

点赞数

文章标签：数据库 php 操作系统

原文链接：http://www.cnblogs.com/cqzhou/p/10907624.html

版权

MySQL连接管理

mysql 命令

-u 用户名
-p 密码

例子：
mysql -uroot -poldboy123

-h ip

mysql -uroot -poldboy123 -hlocalhost -P3306

例子：
mysql -uroot -poldboy123 -h 10.0.0.52

-P 3308

例子：
mysql -uroot -poldboy123 -h 10.0.0.52 -P 3308

-S /tmp/mysql.sock
例子：
mysql -uroot -poldboy123 -S /application/mysql/tmp/mysql.sock

-e "show variables like 'server_id';"
例子：
mysql -uroot -poldboy123 -e "show variables like 'server_id';"

mysqladmin

mysqladmin -uroot -p password oldboy123

---------------------------

mysql启动和关闭

mysql.server ------>mysqld_safe------->mysqld

实际上service mysqld start 也是调用的的mysqld_safe

启动：
[root@db02 ~]# /etc/init.d/mysqld start
或者：
[root@db02 ~]# /application/mysql/bin/mysqld_safe &

[root@db02 ~]# mysql -uroot -poldboy123

mysqladminshutdown
•
servivemysqld stop
•
kill -9 ?
•
第三种为利用系统进程管理命令关闭MySQL。
–
kill pid#<==这里的pid为数据库服务对应的进程号。
–
killallmysqld#<==这里的mysqld是数据库服务对应的进程名字。
–
pkillmysqld #<==这里的mysqld是数据库服务对应的进程名字。

可通过如下地址查看生产高并发环境野蛮粗鲁杀死数据库导致故障企业案例：

http://oldboy.blog.51cto.com/2561410/1431161

http://oldboy.blog.51cto.com/2561410/1431172

关闭：
[root@db02 ~]# mysqladmin -uroot -p123 shutdown
[root@db02 ~]# /etc/init.d/mysqld stop

老男孩报错代码整理：http://oldboy.blog.51cto.com/2561410/1728380

---------------------------

错误日志目录/application/mysql/data j文件：主机名.err

mysql 启动参数设置

1、预编译时候设置参数，参数会硬编码到程序中
2、命令行方式设定启动参数
3、初始化的配置文件
2>3>1
--------------------------

/etc/my.cnf

影响了什么？

（1）影响数据库的启动
[mysqld] 中括号内的定义标签影响相应的启动配置

[mysqld_safe]

[server]

（2）影响到数据库的链接

[mysql]

[mysqladmin]

[mysqldump]

[client]

----------

配置文件案例：

[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
socket=/application/mysql/tmp/mysql.sock
port=3306
server_id=10
log-error=/var/log/mysql.log
log-bin=/application/mysql/data/mysql-bin
binlog_format=row
skip_name_resolve

[mysql]
socket=/application/mysql/tmp/mysql.sock

注：假设4个配置文件都存在，同时使用--defaults-extra-file指定了参数文件，如果这时有一个"参数变量"在5个配置文件中都出现了，

那么后面的配置文件中的参数变量值会覆盖前面配置文件中的参数变量值，就是说会使用~/.my.cnf中设置的值
*****注意*****
如果使用./bin/mysqld_safe 守护进程启动mysql数据库时，使用了--defaults-file=<配置文件的绝对路径>参数，这时只会使用这个参数指定的配置文件。

-----------------

自定义配置文件位置

vim /tmp/aa.txt 编辑一个配置文件
[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
port=3307
socket=/tmp/mysql.sock

/etc/init.d/mysqld stop 先关闭mysql 避免冲突

mysqld_safe --defaults-file=/tmp/aa.txt & 启动并指定配置文件位置

----------------------------
思路:

1、启动多个mysqld进程
2、规划多套数据
3、规划多个端口
4、规划多套日志路径

多实例配置

1、创建多套目录

mkdir -p /data/330{7,8,9} 创建目录用于存放不同实列

2、准备多套配置文件

vi /data/3307/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/3307
server-id=3307
port=3307
log-bin=/data/3307/mysql-bin
socket=/data/3307/mysql.sock
log-error=/data/3307/mysql.log

vi /data/3308/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/3308
server-id=3308
port=3308
log-bin=/data/3308/mysql-bin
socket=/data/3308/mysql.sock
log-error=/data/3308/mysql.log

vi /data/3309/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/3309
server-id=3309
port=3309
log-bin=/data/3309/mysql-bin
socket=/data/3309/mysql.sock
log-error=/data/3309/mysql.log

3、初始化多套数据

/application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/data/3307 --user=mysql
/application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/data/3308 --user=mysql
/application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/data/3309 --user=mysql

4、启动多个实例

mysqld_safe --defaults-file=/data/3307/my.cnf &
mysqld_safe --defaults-file=/data/3308/my.cnf &
mysqld_safe --defaults-file=/data/3309/my.cnf &

5、查看启动端口

netstat -lnp |grep 330

6、分别连接测试

mysql -S /data/3307/mysql.sock -e "show variables like 'server_id';"
mysql -S /data/3308/mysql.sock -e "show variables like 'server_id';"
mysql -S /data/3309/mysql.sock -e "show variables like 'server_id';"

--------------------------------------------------------------------

MySQL用户管理

mysql> select user,host,password from mysql.user; 查询用户的信息

用户定义：

user 主机范围
使用某个用户从哪个（些）地址访问我的数据库

　　　　　　　　本地 localhost

单独的地址：10.0.0.200

　　　　　　　　一个网段：10.0.0.% -----------》10.0.0.1-10.0.0.254

　　　　　　　　　10.0.0.5% 50-59

用户的功能：

　　1、用来登录mysql数据库

　　2、用来管理数据库对象（库、表）

权限：
　　功能：针对不同用户设置对不同对象管理能力
　　select update delete insert create 。。。

权限范围：

　　*.* 　　　　：全局范围
　　oldboy.* 　　 :单库级别
　　oldboy.t1 　　 : 单表级别

grant all on wordpress.* to wordpress@'10.0.0.%' identified by 'oldboy123';
权限权限范围用户密码

-----
超级管理员（管理）用户修改密码：root
mysqladmin -uroot -p password oldboy123
root@localhost
普通（开发）用户： select、update 、delete 、insert 、create 、drop

-------

用户的操作命令

创建一个允许10.0.0.%网段登录的用户

本地 localhost

单独的地址：10.0.0.200

　　　　　　　　一个网段：10.0.0.% -----------》10.0.0.1-10.0.0.254

　　　　　　　　　10.0.0.5% 50-59

create user zabbix@'10.0.0.%' identified by '123';

select user,host,password from mysql.user;

删除没有密码的用户保证数据库的安全

drop user root@'db02';
drop user root@'127.0.0.1';
drop user root@'::1';
drop user ''@'localhost';
drop user ''@'db02';

-- 用户授权

创建一个数据库testdb库 create database testdb;

grant all on testdb.* to zabbix@'10.0.0.%';
权限权限范围用户

一下为ALL权限：
SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES,
INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE,
REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE,
CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE

-- 开发用户：

　　grant SELECT,INSERT, UPDATE, DELETE, CREATE, DROP on testdb.* to zabbix@'10.0.0.%';

　　授权zabbix用户能够对testdb执行的权限

-- 创建用户并授权

　　grant all on *.* to root@'10.0.0.%' identified by '123';

-- 查询用户权限

　　show grants for zabbix@'10.0.0.%';

　　show grants for root@'10.0.0.%';

-- 收回权限

　　revoke create,drop on testdb.* from zabbix@'10.0.0.%';

　　revoke all on testdb.* from zabbix@'10.0.0.%';

思考：

　　grant select on *.* to zabbix@'10.0.0.%' ;

　　grant INSERT, UPDATE, DELETE, CREATE, DROP on testdb.* to zabbix@'10.0.0.%';

　　grant update on testdb.t1 to zabbix@'10.0.0.%';

问：zabbix@'10.0.0.%'对T1表到底有什么权限？
结论，如果对某个用户在不同数据库级别都设置了权限，最终权限相叠加，及加起来的最大权限为准。

建议：尽量不要多范围授权。

grant revoke ----> DCL
--------------------------------------------------

mysql接口程序使用及SQL入门

mysql接口程序：

mysql -uroot -poldboy123 -e "show variables like '%server_id%'"

mysql>

1、接口自带的功能

1、\h 或 help 或 ?
2、\G
3、\T 或 tee
4、\c 或 CTRL+c
5、\s 或 status
6、\. 或 source
执行外部SQL脚本：二进制日志截取、备份出来的SQL脚本
7、\u 或use

2、服务器端命令（SQL）

（1）SQL：结构化的查询语言，mysql接口程序只负责接收SQL，传送给SQL层

（2）SQL种类： DDL:数据库（对象）定义语言
DCL:数据库控制语言（grant revoke）
DML：数据（行）操作语言（update delete insert）
DQL: 数据查询语言（show、select）

DDL操作：
对象：
库：
定义什么？
1、库名字
2、库的基本属性
如何定义？
create database lufei;
create schema lf;
show databases;
create database llf CHARACTER SET utf8 ;
show create database llf；
drop database llf;
help create database;
字符集： CHARACTER SET [=] charset_name
排序规则：COLLATE [=] collation_name

改库的字符集:
ALTER DATABASE [db_name] CHARACTER SET charset_name COLLATE collation_name
mysql> alter database lf charset utf8mb4;
mysql> show create database lf;

表：
表数据：数据行
表属性（元数据）：表名、列名字、列定义（数据类型、约束、特殊列属性）、表的索引信息
定义什么？
定义表的属性。

use lufei;
创建：
create table t1 (id int ,name varchar(20));
查询：
show tables;
show create table t1;
desc
删除
drop table t1;
修改：
（1）在表中添加一列
alter table t1 add age int;
（2）添加多列
alter table t1 add bridate datetime, add gender enum('M','F');
（3）在指定列后添加一列
alter table t1 add stu_id int after id;
（4）在表中最前添加一列
alter table t1 add sid int first;
（5）删除列
alter table t1 drop sid;
（6）修改列名
alter table t1 change name stu_name varchar(20);
（7）修改列属性
alter table t1 modify stu_id varchar(20);
（8）修改表名
rename table t1 to student;
alter table student rename to stu;
-------------------------------------------------------------

DML语句：数据操作语言

insert

use lufei
create table t1 (id int ,name varchar(20));
insert into t1 values(1,'zhang3');
select * from t1;
insert into t1 values (2,'li4'),(3,'wang5'),(4,'ma6');
insert into t1(name) values ('xyz');

update
update t1 set name='zhang33' ; ----会更新表中所有行的name字段，比较危险。
update t1 set name='zhang55' where id=1; ----update在使用时一般都会有where条件去限制。

delete
delete from t1 ; --删除表中所有行，比较危险。一行一行删除表中数据。
delete from t1 where id=2;

DDL
truncate table t1; ---在物理上删除表数据，速度比较快。
--------------------------------------------------------------------------------------

DQL:
select语句：
SELECT USER,PASSWORD ,HOST FROM mysql.user;

-- select 基本查询

DESC world.city
SELECT id ,NAME FROM world.city;
SELECT * FROM world.`city`;

-- select 条件查询 where

----1、查询中国（CHN）所有的城市信息

SELECT * FROM world.`city` WHERE countrycode='CHN';

---- 2、查询中国（CHN）安徽省所有的城市信息。

SELECT * FROM world.`city`
WHERE countrycode='CHN'
AND
district='anhui';

---- 3、查询世界上人口数量在10w-20w城市信息

SELECT * FROM world.`city`
WHERE
population BETWEEN 100000 AND 200000 ;

---- 4、中国或者日本的所有城市信息

where字句中的IN
SELECT * FROM world.city
WHERE countrycode IN ('CHN','JPN');

---- 5、模糊查询

SELECT * FROM world.city
WHERE countrycode LIKE 'ch%';

-- select 排序并限制---- 按照人口数量排序输出中国的城市信息（ASC\DESC）

SELECT * FROM world.`city` WHERE countrycode='CHN' ORDER BY population ASC;
SELECT * FROM world.`city` WHERE countrycode='CHN' ORDER BY population DESC;

---- 按照多列排序人口+省排序

SELECT * FROM world.`city` WHERE countrycode='CHN'
ORDER BY id DESC ;

SELECT * FROM city
ORDER BY 5 DESC ;

1-10
SELECT * FROM world.`city` WHERE countrycode='CHN'
ORDER BY 5 DESC LIMIT 20;

11-20
SELECT * FROM world.`city` WHERE countrycode='CHN'
ORDER BY 5 DESC LIMIT 10,10 ;

SELECT * FROM world.`city` WHERE countrycode='CHN'
ORDER BY 5 DESC LIMIT 10 OFFSET 10 ;

--表连接查询
-- 表连接查询

DESC city

DESC countrylanguage

传统的连接写法（使用where）

---- 中国所有城市信息+使用语言

SELECT NAME ,countrycode ,population FROM city WHERE countrycode ='CHN'

SELECT countrycode ,LANGUAGE FROM countrylanguage;

SELECT ci.NAME ,ci.countrycode ,ci.population,cl.language
FROM
city AS ci , countrylanguage AS cl
WHERE ci.countrycode ='CHN'
AND
ci.CountryCode=cl.CountryCode;

SELECT NAME,ci.countrycode ,cl.language ,ci.population
FROM city ci , countrylanguage cl
WHERE
ci.countrycode='chn' AND
ci.`CountryCode`=cl.countrycode;

SELECT NAME,countrycode ,LANGUAGE ,population
FROM city NATURAL JOIN countrylanguage
WHERE population > 10000000
ORDER BY population;

SELECT NAME,countrycode ,LANGUAGE ,population
FROM city JOIN countrylanguage
USING(countrycode);

---- 查询青岛这个城市，所在的国家具体叫什么名字

DESC city
DESC country

SELECT NAME,countrycode FROM city WHERE NAME='qingdao';

SELECT NAME FROM country WHERE CODE='CHN';

--------------------------------
SELECT ci.name ,ci.countrycode,ci.population ,co.name
FROM city AS ci
JOIN
country AS co
ON ci.countrycode=co.code
AND
ci.name='qingdao';
---------------------------------

group by +聚合函数（avg()、max()、min()、sum()）

SELECT countrycode ,SUM(population) FROM city
WHERE countrycode = 'chn'
GROUP BY countrycode;

union

用来替换 or 、in()

SELECT * FROM world.city
WHERE countrycode IN ('CHN','JPN');
改写为：

SELECT * FROM world.city
WHERE countrycode ='CHN'
union
SELECT * FROM world.city
WHERE countrycode ='JPN';

------------------

字符集：

charset:字符集
UTF8
UTF8mb4

gbk

collation：排序规则

a-z ,A-Z 大小写敏感

aA-zZ 小写不敏感

show charset;
show collation;

数据库：

服务器端字符集：

控制的是，存到mysql中时，字符集控制

客户端字符集

控制的是用户的输入及显示

系统字符集

控制的是系统相关的显示，和一些依赖于操作系统的应用

alter database oldboy CHARACTER SET utf8 collate utf8_general_ci;
alter table t1 CHARACTER SET latin1;

注意：更改字符集时，一定要保证由小往大改，后者必须是前者的严格超集。

生产中别随便改。

数据类型及列属性：

数字类型
字符类型
时间类型

列属性

create table student(id int not null primary key AUTO_INCREMENT)；
create table student1(id int not null primary key AUTO_INCREMENT,name varchar(20))charset utf8;
create table teacher(id int not null ,name varchar(20) not null);
create table teacher1(id int not null ,name varchar(20) not null,beizhu varchar(20) not null default "ok");

primary key 主键：非空、唯一
unique：唯一

获取元数据：
information_schema

元数据

SELECT TABLE_NAME, ENGINE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'world';

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'set';

SELECT CHARACTER_SET_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLLATIONS
WHERE IS_DEFAULT = 'Yes';

SELECT TABLE_SCHEMA, COUNT(*)
FROM INFORMATION_SCHEMA.TABLES
GROUP BY TABLE_SCHEMA;

mysqldump -uroot -poldboy123 world country >> /bakcup/world_country.bak.sql

select table_schema ,country from tables where table_schema='world';

------------------------

select concat("mysqldump -uroot -poldboy123 ",table_schema," ",table_name,"
>>","/backup/",table_schema,"_",table_name,".bak.sql")
from information_schema.tables where table_schema='world';

SELECT CONCAT('CREATE TABLE ', TABLE_SCHEMA, '.',
TABLE_NAME, '_backup LIKE ', TABLE_SCHEMA, '.',
TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = ‘world’;

-------------------------

show
show
show databases
show create database oldboy
show tables
show create table t1

SOHW databases：列出所有数据库
SHOW TABLES：列出默认数据库中的表
SHOW TABLES FROM <database_name>：列出指定数据库中的表
SHOW COLUMNS FROM <table_name>：显示表的列结构
SHOW INDEX FROM <table_name>：显示表中有关索引和索引列的信息
SHOW CHARACTER SET：显示可用的字符集及其默认整理
SHOW COLLATION：显示每个字符集的整理
SHOW STATUS：列出当前数据库状态
SHOW VARIABLES：列出数据库中的参数定义值

转载于:https://www.cnblogs.com/cqzhou/p/10907624.html