阅读数:308 评论数:0

一、登录mysql

mysql --version
-查看Mysql版本
mysql -h hostname -u username -p
-h：运行mysql服务器的机器，如果正在该机器上运行此命令，可以忽略hostname参数
-u：连接数据库的用户名，如果不指定，默认值是登录该操作系统的用户名
或者
mysql -D dbname -h hostname -u username -p

1、修改root密码

mysql -uroot -Pnb2022…a
Mysql> alter user  root@”localhost” identified by “123456”; 
 MySQL> exit;

如果在修改密码的过程中报：“Your password does not satisfy the current policy requirements”

mysql> set global validate_password_policy=0;

2、用户及权限管理

（1）创建用户

#使用 CREATE
 CREATE USER 'testDatabase'@'%' IDENTIFIED BY '111111';
# CREATE USER '你的用户名'@'可以访问数据库的ip，%表示所有' IDENTIFIED BY '数据库密码
# 使用GRANT USAGE，USAGE表示没有任何权限。
GRANT USAGE ON *.* TO 'dummy'@'localhost';

（2）用户授权

GRANT ALL ON testDatabase_jixian.* TO 'username'@'%';
-- 使用  with grant option ，可以是用户拥有授权给其他角色权限的能力
GRANT ALL ON testDatabase_jixian.* TO 'username'@'%' with grant option;

（3）查看用户权限

SHOW GRANTS FOR 'username'@'%'

（4）设置与更改用户密码

SET PASSWORD FOR 'username'@'指定ip' = PASSWORD('密码');

如果是当前登录用户直接用

SET PASSWORD = PASSWORD("密码");

（5）撤销用户权限

REVOKE privilegexxx ON databasename.tablenamexxx FROM 'username'@'指定ip';

（6）删除用户

DROP USER 'username'@'指定ip'; -- 方式一
delete from mysql.user where user="username"; -- 方式二

二、数据库管理

// 创建数据库
create database dbname;
// 删除数据库
drop database dbname;
// 切换数据库
use dbname;

1、字符集

--查看mysql支持的字符集
show character set;

优先级依次增加。所以一般情况下只需要设置character-set-server，而在创建数据库和表时不特别指定字符集，统一采用character-set-server。

在客户端，如果没有定义character-set-results，则采用character-set-client字符集作为默认的字符集。所以只需要设置character-set-client字符集。

要处理中文，则可以将character-set-server和character-set-client均设置为GB2312，如果要同时处理多国语言，则设置为UTF8。

（1）server层面

[mysqld]
character_set_server=utf8

这样配置会将全局的character_set_server变量设置为utf8。

（2）数据库层面

CREATE DATABASE db_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name]
 
ALTER DATABASE db_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name]

（3）表层面

CREATE TABLE tbl_name (column_list)
    [[DEFAULT] CHARACTER SET charset_name]
    [COLLATE collation_name]]
 
ALTER TABLE tbl_name
    [[DEFAULT] CHARACTER SET charset_name]
    [COLLATE collation_name]

（4）字段层面

col_name {CHAR | VARCHAR | TEXT} (col_length)
    [CHARACTER SET charset_name]
    [COLLATE collation_name]

2、字符的排序(collation)

--查看mysql的collation
show collation;

collations是与字符集编码对应的一套规则，每一种字符集都有一个默认的collation，这套规则决定了字符如何比较，从而解决如何排序的问题。

{character set name}_{language-specific}_{collation_suffixes}

第一部分是字符集的名字，第二部分表示语言，第三部分是后缀

（1）后缀

三、表操作

create table tablename(
    columns
)
每一列应该有一个名字，后面跟着数据类型和约束，mysql字段命名不能使用mysql的关键字

// 删除表
drop table table_name
// 清空表
truncate table_name;
// 表的查询
//通过在查询语句末尾添加\G可以实现以垂直模式显示结果（如果表行的宽度太大）

1、常见约束

not null：此属性必须有值。

auto_increment：在表中插入行时，如果此字段为空，会自动生成一个唯一标识，该值比本列中现存在最大值更大，每个表中只能有一个这样的值，指定auto_increment的列必须是索引列。

primary key：主键。

整数后面的unsigned表示它只能是0或者一个正数。

2、数字类型

M表示zerofill属性，当显示zerofill字段中的值时，空余部分用前导0来补充，自动变为unsigned数据类型，如int(4) 显示为0003，int(7)显示为0000003。

小数点后保留两位小数：方法是在建表时直接定义此数值类型为float、double或decimal。

3、日期与时间类型

datetime用于表示“年月日时分秒”，是date和time的组合，并且记录的年份比较长久。datetime列可以设置为多个，默认可为null，可以手动设置其值。datetime列不可设定默认值，

timestamp列必须有默认值，但不能为null。timestamp和时区相关，当插入日期时，会先转换为本地时区后再存放，当查询日期时，会将日期转换为本地区时区后再显示，所以不同时区的人看到的同一时间是不一样的。

4、字符串类型

5、其它类型

四、show和describe

show [FULL] tables [from db_name] [like 'pattern']

show tables显示的是非temporary表，如果要显示其它视图，可以添加full修饰符。

show tables; --查看数据库中所有的表
show databases; --查看数据库列表
describe tablename; --查看某特定表的详细信息

五、创建索引

如果需要对一个不是主键的列运行许多查询，可以在该列上添加索引来改善性能

create [unique|fulltext] index index_name
    on table_name (index_column_name [(length)] [ASC|DESC],...)
fulltext索引用来索引文本字段
length表示只有该字段的前length个字段将被 索引

1、普通索引

CREATE INDEX 索引名 ON 表名 (列名[(length)]);

如果忽略length，则使用整个列的值作为索引。如果指定使用列前的length个字符来创建索引，这样有利于减少索引文件大小。

create index cardid_index on team (cardid);
show create table team;

2、使用alter创建索引

ALTER TABLE 表名 ADD INDEX 索引名 (列名);

alter table team add index phone_index (phone);

3、创建表时指定索引

CREATE TABLE 表名 ( 字段1 数据类型,字段2 数据类型[,...],INDEX 索引名 (列名));

create table test(id int(4) not null,name varchar(10) not null,age int (3),cardid varchar(5) not null,index age_index(age));

4、唯一索引

CREATE UNIQUE INDEX 索引名 ON 表名(列名);

ALTER TABLE 表名 ADD UNIQUE 索引名 (列名);

CREATE TABLE 表名 ([...],PRIMARY KEY (列名));

5、组合索引

CREATE TABLE 表名 (列名1 数据类型,列名2 数据类型,列名3 数据类型,INDEX 索引名 (列名1,列名2,列名3));

6、全文索引

适合在进行模糊查询的时候使用，可用于在一篇文章中检索文本信息。

在 MySQL5.6 版本以前FULLTEXT 索引仅可用于 MyISAM 引擎，在 5.6 版本之后 innodb 引擎也支持

FULLTEXT 索引。全文索引可以在 CHAR、VARCHAR 或者 TEXT 类型的列上创建。每个表只允许有一个全文索引。

CREATE FULLTEXT INDEX 索引名 ON 表名 (列名);

7、查看索引

show index from 表名;

show index from 表名\G； 竖向显示表索引信息

show keys from 表名;

show keys from 表名\G;

8、删除索引

DROP INDEX 索引名 ON 表名;

ALTER TABLE 表名 DROP INDEX 索引名;

六、mysql关键字

七、optimize优化表

MySQL删除表数据（DELETE）时，MySQL并不会回收这条记录占据的存储空间以及索引位，而是空在那里，等待新的数据来弥补这个空洞。若一时半会没有数据来填补这个空洞，就会形成资源浪费。OPTIMIZE TABLE操作会整理数据文件的碎片，释放多余的空间。所以对于写操作比较频繁的表，需要根据实际情况定期进行optimize。

OPTIMIZE TABLE运行过程中，MySQL会锁定表。

1、语法

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb_name [, tbl_name] ...

常用方式

OPTIMIZE TABLE 表名;