MySQL启动与更改密码
mysql启动基本原理说明：
/etc/init.d/mysqld是一个shell启动脚本，启动后最终会调用，mysqld_safe脚本，最后调用mysqld服务启动mysql，我们编辑/etc/init.d/mysqld，可以看到脚本，启动俩个进程mysqld和mysqld_safe，一般故障的时候我们用mysqld_safe来启动，
关闭mysql
1 mysqladmin - uroot -p密码 shut down2 /etc/init.d/mysqld stop3 kill USR2`cat path/pid`
优雅的关闭mysql但是不建议用killall杀掉所有的mysql进程，这样会导致mysql数据库起不来，所以网友遇到这样情况也很多，
我们登陆mysql后想分清出那个是正式环境那个是测试环境，
命令行修改登陆提示符
mysql> prompt\u@king\s->PROMPT set to '\u@king\s->'
配置文件修改登陆提示符
在my.cnf配置文件中[mysql]模块下添加如下内容，保存后，无需重启myysql，退出当前的session，重新登陆
[mysql]prompt=\\u@king\s->
登陆mysql
[root @king~]# mysqlWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.5.40 MySQL Community Server (GPL)Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>
更改root密码
1 mysql> update user set password=password('123456') where user='root' and host='localhost'; #password（'12345'）是指定一个函数2 Query OK, 1 row affected (0.00 sec)3 Rows matched: 1 Changed: 1 Warnings: 04 mysql> flush privileges; #刷新，没刷新前是在内存里面5 Query OK, 0 rows affected (0.17 sec)6 mysql> 说明，修改密码都需要刷新一下哦，linux找回mysql root用户密码
单实例mysql修改丢失root方法
1，首先停止mysql
/etc/init.d/mysql stop
2，使用--skip-grant-tables启动mysql，忽略授权登陆验证
1 mysqld_safe --user=mysql --skip-grant-tables --skip-networking & 2 mysql -u root -p #说明-p登陆时密码是空，也可以不加-p，亲测哦(#^.^#) 3 update mysql.user set password=password("newpassword") where user='root'and host='localhost'; 4 mysql> flush privileges; 5 mysql> quit 6 # /etc/init.d/mysql restart 7 # mysql -uroot -p 8 enter password: <输入新设的密码newpassword> 9 mysql> 多实例mysql修改丢失root方法
1关闭mysql
killall mysqld
2启动时加--skip-grant-tables参数
mysql_safe --defaults-file=/data/3306/my.cnf --skip-grant-tables& mysql -u root -p -S /data/3306/mysql.sock
3修改密码
mysql>update mysql.user set password=password("newpassword") where user='root'and host='localhost';mysql> flush privileges; Query OK, 0 rows affected (0.03 sec) SQl的分类
SQl结构化查询语言包含6个部分
- 数据查询语言（DQL（data query language）），作用从表中获取数据，关键字select，
- 数据操作语言（DMl（data manipulation language））作用处理表中的数据insert ，update，delete
- 事务处理语言（TPL）关键字begin，commit和 rollback
- 数据控制语言（DCl）grant（授权） 和revoke
- 数据定义语言（DDl）create和drop在数据库中创建新表或删除表 alter
- 指针控制语言（CCl） declare cursor， fetch into 和update where current用于对一个或多个表单独行的操作
查看数据库
show databases;或show database link '';或select database;
1 mysql> show databases; 2 +--------------------+ 3 | Database | 4 +--------------------+ 5 | information_schema | 6 | mysql | 7 | performance_schema | 8 | student | 9 | test |10 +--------------------+11 8 rows in set (0.01 sec)
创建数据库
命令语法：create database <数据库名> 注意库名字不能数字开头
1 mysql> create database king; 2 Query OK, 1 row affected (0.01 sec) 3 4 mysql> show databases; 5 +--------------------+ 6 | Database | 7 +--------------------+ 8 | information_schema | 9 | king |10 | mysql |11 | performance_schema |12 | student |13 | test |14 +--------------------+15 6 rows in set (0.01 sec)16 17 mysql> show create database king\G18 *************************** 1. row ***************************19 Database: king20 Create Database: CREATE DATABASE `king` /*!40100 DEFAULT CHARACTER SET utf8 */21 1 row in set (0.00 sec)22 23 mysql>
创建一个指定字符集的数据库
1 mysql> CREATE DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; #红色指定编码，蓝色校验规则。2 Query OK, 1 row affected (0.01 sec)
删除数据库
drop database <数据库名字>
1 mysql> drop database test; 2 Query OK, 1 row affected (0.07 sec) 3 4 mysql> show databases; 5 +--------------------+ 6 | Database | 7 +--------------------+ 8 | information_schema | 9 | king |10 | mysql |11 | performance_schema |12 | student |13 +--------------------+14 5 rows in set (0.01 sec)
连接数据库
命令：use <数据库名>相当于linux下的cd切换目录的命令，use是切换数据库
1 mysql> use king; 2 Database changed 3 mysql> select database(); #查看当前的数据库，带（）相当于函数， 4 +------------+ 5 | database() | 6 +------------+ 7 | king | 8 +------------+ 9 1 row in set (0.00 sec)
查看数据库
select database （）;相当于linux下的pwd
1 mysql> select version(); #查看当前的版本 2 +-----------+ 3 | version() | 4 +-----------+ 5 | 5.5.40 | 6 +-----------+ 7 1 row in set (0.06 sec) 8 mysql> select user(); #查看当前的用户 9 +----------------+10 | user() |11 +----------------+12 | root@localhost |13 +----------------+14 1 row in set (0.04 sec)15 mysql> select now(); #查看当前的时间16 +---------------------+17 | now() |18 +---------------------+19 | 2018-11-02 19:26:39 |20 +---------------------+21 1 row in set (0.02 sec)22 mysql>
表操作
创建表并查看
create table <表名>（<字段名1><类型1>）;
1 mysql> create table im(id int(3) not null, name varchar(20) not null default'QQ' #创建表id为int类型，name为varchar 2 ); 3 Query OK, 0 rows affected (0.59 sec) 4 mysql> show tables from king; #从king数据库中查看表 5 +----------------+ 6 | Tables_in_king | 7 +----------------+ 8 | im | 9 +----------------+10 1 row in set (0.04 sec)11 mysql> desc im; #查看表结构12 +-------+-------------+------+-----+---------+-------+13 | Field | Type | Null | Key | Default | Extra |14 +-------+-------------+------+-----+---------+-------+15 | id | int(3) | NO | | NULL | |16 | name | varchar(20) | NO | | QQ | |17 +-------+-------------+------+-----+---------+-------+18 2 rows in set (0.22 sec)19 mysql>
show colums from im;查看表结构
1 mysql> show create table im \G; #\G Send command to mysql server，display result vertically向MySQL服务器发送命令，垂直显示结果2 *************************** 1. row ***************************3 Table: im4 Create Table: CREATE TABLE `im` (5 `id` int(3) NOT NULL,6 `name` varchar(20) NOT NULL DEFAULT 'QQ'7 ) ENGINE=InnoDB DEFAULT CHARSET=utf88 1 row in set (0.64 sec)
mysql表的字段类型
| 列类型 | 需要的存储量 |
| tinyint | 1字节 |
| smallint | 2个字节 |
| mediumint | 3个字节 |
| int | 4个字节 |
| integer | 4个字节 |
| bigint | 8个字节 |
| float（X） | 4如果x<=24或8如果25<=X=53 |
| float | 4个字节 |
| double | 8个字节 |
| double precision | 8个字节 |
| real | 8个字节 |
| decimal（M，D） | M字节（D+2，如果m<D） |
| numeric（M，D） | M字节（D+2，如果m<D） |
为表的字段创建索引
创建主键索引，查询数据库，按主键查询是最快的，每一个表只有一个主键列，但是可以有多个普通的索引列，主键列要求列的内容必须唯一，而索引列不要求内容必须唯一。
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));1 mysql> create table student( id int (4) not null auto_increment, name char(20) n2 ot null, age tinyint(2) not null default'0',dept varchar(16) default null,primar3 y key (id), key index_name(name));4 Query OK, 0 rows affected (0.18 sec)
auto_increment自增，primary key (id), 主键，key index_name(name));普通索引，
1 mysql> desc student; #查看创建的student表 2 +-------+-------------+------+-----+---------+----------------+ 3 | Field | Type | Null | Key | Default | Extra | 4 +-------+-------------+------+-----+---------+----------------+ 5 | id | int(4) | NO | PRI | NULL | auto_increment | 6 | name | char(20) | NO | MUL | NULL | | 7 | age | tinyint(2) | NO | | 0 | | 8 | dept | varchar(16) | YES | | NULL | | 9 +-------+-------------+------+-----+---------+----------------+10 4 rows in set (0.17 sec)
alter table student drop primary key ; 删除主键，测试不行，
1 mysql> alter table student drop primary key ;2 ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
不正确的表定义；只能有一个自动COLUM它必须被定义为一个密钥
alter table student change id id int primary key auto_increment; 如果创建表忘记添加主键了，就执行这个。
创建普通索引分为唯一索引和普通索引
alter table student drop index index_name; # index固定索引，index_name是你创建表的时候的索引
alter table student add index index_name（name）;
create index index_name on student （name（8））; # index固定索引（固定写法），index_name 随便写但要见名知意， on student 在哪个表上（student）8就是前8个字符创建索引。
create index index_name_dept on student （name，dept）
按条件列查询数据时，联合索引是有前缀生效特性的 index（a，b，c）仅a，ab，abc三个查询条件列可以走索引
查看表的索引
1 mysql> show index from student\G; 2 *************************** 1. row *************************** 3 Table: student 4 Non_unique: 0 5 Key_name: PRIMARY 6 Seq_in_index: 1 7 Column_name: id 8 Collation: A 9 Cardinality: 010 Sub_part: NULL11 Packed: NULL12 Null:13 Index_type: BTREE14 Comment:15 Index_comment:16 *************************** 2. row ***************************17 Table: student18 Non_unique: 119 Key_name: index_name20 Seq_in_index: 121 Column_name: name22 Collation: A23 Cardinality: 024 Sub_part: NULL25 Packed: NULL26 Null:27 Index_type: BTREE28 Comment:29 Index_comment:30 2 rows in set (0.00 sec)
创建唯一索引
create unique index uni on student（name）;
1 mysql> create unique index uni on student (name); 2 Query OK, 0 rows affected (0.09 sec) 3 Records: 0 Duplicates: 0 Warnings: 0 4 mysql> desc student; 5 +-------+-------------+------+-----+---------+----------------+ 6 | Field | Type | Null | Key | Default | Extra | 7 +-------+-------------+------+-----+---------+----------------+ 8 | id | int(4) | NO | PRI | NULL | auto_increment | 9 | name | char(20) | NO | UNI | NULL | |10 | age | tinyint(2) | NO | | 0 | |11 | dept | varchar(16) | YES | | NULL | |12 +-------+-------------+------+-----+---------+----------------+13 4 rows in set (0.02 sec)
- 要在表的列上创建索引
- 索引会加快查询的速度，但是会影响更新的速度
- 索引不是越多越好，要在频繁查询的where后的条件列上创建索引
- 小表或唯一值极少的列上不建索引，要在带包以及不同内容多的列上创建索引
往表中插入数据
create table test(
id int(4) not null auto_increment,
name char(20) not null,
primary key (id)
);
1 mysql> create table test(id int(4) not null auto_increment, name char(20) not n 2 ll,primary key (id)); 3 Query OK, 0 rows affected (0.01 sec) 4 mysql> show tables; 5 +----------------+ 6 | Tables_in_king | 7 +----------------+ 8 | student | 9 | test |10 +----------------+11 2 rows in set (0.00 sec)12 mysql> insert into test (id,name) values(1,'boy'); #插入数据，主键自增我们直接插name，insert into test（name） values（'new'）;13 Query OK, 1 row affected (0.05 sec)14 mysql> select *from test;15 +----+------+16 | id | name |17 +----+------+18 | 1 | boy |19 +----+------+20 1 row in set (0.04 sec)
mysql> delete from test;Query OK, 1 row affected (0.05 sec)mysql> select *from test;Empty set (0.00 sec)mysql> insert into test (id,name) values(1,'boy'),(2,'new');Query OK, 2 rows affected (0.00 sec)Records: 2 Duplicates: 0 Warnings: 0
查询数据
select *from 表名;
1 mysql> select *from test;2 +----+------+3 | id | name |4 +----+------+5 | 1 | boy |6 | 2 | new |7 +----+------+8 2 rows in set (0.47 sec)
指定条件查询
1 mysql> select *from test where id =1; #根据id查询2 +----+------+3 | id | name |4 +----+------+5 | 1 | boy |6 +----+------+7 1 row in set (0.09 sec)
1 mysql> select *from test where name='new'; #根据name查询，字符串需要带引号2 +----+------+3 | id | name |4 +----+------+5 | 2 | new |6 +----+------+7 1 row in set (0.03 sec)
多个条件查询and区交集，or取并集。排序 order by id desc（正序），sec（倒序）
1 mysql> select *from test where name='new'or id=1;2 +----+------+3 | id | name |4 +----+------+5 | 1 | boy |6 | 2 | new |7 +----+------+8 2 rows in set (0.08 sec)
explain查看索引、
1 mysql> explain select *from test where name='new'\G; 2 *************************** 1. row *************************** 3 id: 1 4 select_type: SIMPLE 5 table: test 6 type: ALL 7 possible_keys: NULL 8 key: NULL 9 key_len: NULL #可以看到索引为空10 ref: NULL11 rows: 212 Extra: Using where13 1 row in set (0.04 sec)
help explain;
修改表中指定条件固定列的数据
命令语法：update 表名 set 字段=新值，where 条件
1 mysql> update test set name='wangxinxia' where id=2; 2 Query OK, 1 row affected (0.90 sec) 3 Rows matched: 1 Changed: 1 Warnings: 0 4 mysql> desc test; 5 +-------+----------+------+-----+---------+----------------+ 6 | Field | Type | Null | Key | Default | Extra | 7 +-------+----------+------+-----+---------+----------------+ 8 | id | int(4) | NO | PRI | NULL | auto_increment | 9 | name | char(20) | NO | | NULL | |10 +-------+----------+------+-----+---------+----------------+11 2 rows in set (0.25 sec)12 mysql> select* from test;13 +----+------------+14 | id | name |15 +----+------------+16 | 1 | boy |17 | 2 | wangxinxia |18 +----+------------+19 2 rows in set (0.00 sec)
truncate table test;和delete from test的区别
- truncate 更快，清空物理文件
- delete逻辑清楚，按行删
删除数据
1 mysql> delete from student where id=3; 2 Query OK, 1 row affected (0.30 sec) 3 mysql> show tables; 4 +----------------+ 5 | Tables_in_king | 6 +----------------+ 7 | student | 8 | test | 9 +----------------+10 2 rows in set (0.07 sec)11 mysql> select * from student;12 +----+----------+-----+-------+13 | id | name | age | dept |14 +----+----------+-----+-------+15 | 1 | zhangsan | 20 | yanfa |16 | 2 | zhaoliu | 21 | dba |17 +----+----------+-----+-------+
增删改表的字段
语法alter table 表名 add sex char（4）；
1 mysql> alter table student add sex char (4); 2 Query OK, 2 rows affected (0.54 sec) 3 Records: 2 Duplicates: 0 Warnings: 0 4 mysql> desc student; 5 +-------+-------------+------+-----+---------+----------------+ 6 | Field | Type | Null | Key | Default | Extra | 7 +-------+-------------+------+-----+---------+----------------+ 8 | id | int(4) | NO | PRI | NULL | auto_increment | 9 | name | char(20) | NO | UNI | NULL | |10 | age | tinyint(2) | NO | | 0 | |11 | dept | varchar(16) | YES | | NULL | |12 | sex | char(4) | YES | | NULL | |13 +-------+-------------+------+-----+---------+----------------+14 5 rows in set (0.20 sec)
alter table 表名 add qq varchar（10）first；
1 mysql> alter table student add qq varchar(10) first; 2 Query OK, 2 rows affected (0.06 sec) 3 Records: 2 Duplicates: 0 Warnings: 0 4 mysql> desc student; 5 +-------+-------------+------+-----+---------+----------------+ 6 | Field | Type | Null | Key | Default | Extra | 7 +-------+-------------+------+-----+---------+----------------+ 8 | qq | varchar(10) | YES | | NULL | | 9 | id | int(4) | NO | PRI | NULL | auto_increment |10 | name | char(20) | NO | UNI | NULL | |11 | age | tinyint(2) | NO | | 0 | |12 | dept | varchar(16) | YES | | NULL | |13 | sex | char(4) | YES | | NULL | |14 +-------+-------------+------+-----+---------+----------------+15 6 rows in set (0.02 sec)
rename table 原表to新表
1 mysql> show tables; 2 +----------------+ 3 | Tables_in_king | 4 +----------------+ 5 | student | 6 | test | 7 +----------------+ 8 2 rows in set (0.00 sec) 9 mysql> rename table test to test1;10 Query OK, 0 rows affected (0.05 sec)11 12 mysql> show tables;13 +----------------+14 | Tables_in_king |15 +----------------+16 | student |17 | test1 |18 +----------------+19 2 rows in set (0.00 sec)
alter table test1 rename to test；
mysql插入数据解决乱码问题
- set names 库的字符集，这种方法是临时的每次进来之前都需要执行
- source test.sql在这个文件中插入set names 库字符集
- 对已有数据，需要把数据导出去，重新建库建表，在导进来。
创建/查看/删除mysql系统的用户
语法：
- create user '用户名'@'主机名' identified by '用户名'; 创建用户
- select host,user from mysql.user; 查看用户
- drop user 用户名@'%';注意可以是单或者双引号，但不能不加，删除用户
- delete from mysql.user where user='用户名' and host=‘@后面指定的主机名’;
1 mysql> create user 'usrabc'@'%' identified by 'usrabc'; #创建usrabc用户 2 Query OK, 0 rows affected (0.49 sec) 3 mysql> select host,user from mysql.user; #查看所有用户 4 +-----------+--------+ 5 | host | user | 6 +-----------+--------+ 7 | % | usrabc | 8 | localhost | root | 9 +-----------+--------+10 2 rows in set (0.00 sec)11 mysql> drop user usrabc@'%'; #删除usrabc用户12 Query OK, 0 rows affected (0.00 sec)
处理完用户最好刷新一下权限，flush privileges;
创建mysql用户及赋予用户权限
通过help grant 查看帮助，比较常用的创建用户的方法是：CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass'
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass'GRANT ALL ON db1.* TO 'jeffrey'@'localhost' #这俩命令是先用create创建用户，然后在授权GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost'GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90
通过grant命令创建用户并授权，命令语法：grant all privileges on dbname.* to username@localhost identified by 'passwd';
| grant | all privileges | on dbname.* | to username@localhost | identified by 'passwd' |
| 授权命令 | 对应权限 | 目标：库和表 | 用户名和客户端主机 | 用户密码 |
1 mysql> grant all privileges on king.* to liang@localhost identified by '123456';2 #创建liang用户，对king库具备所有权限，允许从localhost主机登陆管理数据库，密码是123456.3 Query OK, 0 rows affected (0.01 sec)4 mysql> flush privileges;5 Query OK, 0 rows affected (0.96 sec)
1 mysql> show grants for liang@localhost; #查看权限 2 +------------------------------------------------------------------------------- 3 -------------------------------+ 4 | Grants for liang@localhost 5 | 6 +------------------------------------------------------------------------------- 7 -------------------------------+ 8 | GRANT USAGE ON *.* TO 'liang'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74 9 329105EE4568DDA7DC67ED2CA2AD9' |10 | GRANT ALL PRIVILEGES ON `king`.* TO 'liang'@'localhost'11 |12 +-------------------------------------------------------------------------------13 -------------------------------+14 2 rows in set (0.07 sec)
create和grant配合
1 mysql> create user zh@localhost identified by '123456';2 Query OK, 0 rows affected (0.04 sec)3 mysql> grant all on king.* to zh@localhost;4 Query OK, 0 rows affected (0.00 sec)5 mysql> flush privileges;6 Query OK, 0 rows affected (0.00 sec)
授权局域网内主机远程连接数据库grant all privileges on king.* to liang@10.0.0.% identified by '123456';把localhost改为ip就可以了。连接，mysql -uliang -p123456 -h10.0.0.%
1 mysql> show grants for zh@localhost; #查看zh用户权限 2 +------------------------------------------------------------------------------- 3 ----------------------------+ 4 | Grants for zh@localhost 5 | 6 +------------------------------------------------------------------------------- 7 ----------------------------+ 8 | GRANT USAGE ON *.* TO 'zh'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329 9 105EE4568DDA7DC67ED2CA2AD9' |10 | GRANT ALL PRIVILEGES ON `king`.* TO 'zh'@'localhost'11 |12 +-------------------------------------------------------------------------------13 ----------------------------+14 2 rows in set (0.16 sec)15 16 mysql> revoke insert on king.* from zh@localhost; #更改权限17 Query OK, 0 rows affected (0.04 sec)18 19 mysql> show grants for zh@localhost;20 +-------------------------------------------------------------------------------21 --------------------------------------------------------------------------------22 -------------------------------------------------------------+23 | Grants for zh@localhost24 25 |26 +-------------------------------------------------------------------------------27 --------------------------------------------------------------------------------28 -------------------------------------------------------------+29 | GRANT USAGE ON *.* TO 'zh'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB7432930 105EE4568DDA7DC67ED2CA2AD9'31 |32 | GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE T33 EMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, A34 LTER ROUTINE, EVENT, TRIGGER ON `king`.* TO 'zh'@'localhost' |35 +-------------------------------------------------------------------------------36 --------------------------------------------------------------------------------37 -------------------------------------------------------------+38 2 rows in set (0.06 sec)
1 mysql -uroot -p123456 -e "show grants for zh@localhost;"|grep -i grant #用linux中grep命令过滤需要的权限2 Grants for zh@localhost3 GRANT USAGE ON *.* TO 'zh'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB743294 105EE4568DDA7DC67ED2CA2AD9'5 GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE T6 EMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, A7 LTER ROUTINE, EVENT, TRIGGER ON `king`.* TO 'zh'@'localhost'
查看mysql的ALL PRIVILEGES权限mysql -uroot -p123456 -e "show grants for zh@localhost;"|grep -i grant|tail -1|tr ‘，’ ‘\n’ >a.txt
我们在授权用户最小的 满足业务需求的权限，而不是一味的授权ALl PRIVILEGES。