mysql的基本使用

阅读数:282 评论数: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/default-character-set 服务器字符集,默认情况下采用
character-set-database 数据库字符集
character-set-table 数据库表字符集

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

character-set-client 客户端的字符集,当客户端向服务器发送请求时,请求以该字符集进行编码 
character-set-results 结果字符集。服务器向客户端返回结果时,以该字符集进行编码

在客户端,如果没有定义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)后缀

Suffix Meaning
_ai Accent-insensitive
_as Accent-sensitive
_ci Case-insensitive
_cs Case-sensitive
_bin Binary

_ci暗含了_ai,_cs暗含了_as,_bin后缀比较的是字符的编码值。

 

三、表操作

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、数字类型

类型 取值范围 存储空间(字节) 描述
tinyint -127...128或0...255 1 非常 小的数
bit     同tinyint
bool     同tinyint
smallint[(M)] -32768~32767 2  
mediumint[(M)] -8388608~8388607 3  
int[(M)] -2147483648~2147483647 4  
integer[(M)]     int同义词
bitint[(M)]   8  

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

类型 存储空间(字节) 描述
float(精度) 可变 可以指定单精度和双精度浮点数
float[(M,D)] 4(8位精度) 单精度浮点数,等同于float(4),可以指定显示宽度和小数位数
double[(M,D)] 8(16位精度) 双精度浮点数,等同于float(8),可以指定显示宽度和小数位数
double   double[(M,D)]的同义词
precision[(M,D)]   double[(M,D)]的同义词
real[(M,D)]   double[(M,D)]的同义词
decimal[(M[,D]] M+2 浮点数,以char存储
numberic[(M,D)]   decimal同义词
dec[(M,D)]   decimal同义词
fixed[(M,D)]   decimal同义词

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

 

3、日期与时间类型

类型 取值范围 描述
date 1000-01-01~9999-12-31 一个日期,以YYYY-MM-DD格式显示
time -838:59:59~838:59:59 一个时间,以HH:MM:SS形式显示
datetime 1000-01-01 00:00:00~9999-12-31 23:59:59 日期时间,以YYYY-MM-DD HH:MM:SS显示
timestamp[(M)] 1970-01-01 00:00:00~2037年的某个时间

时间标签,显示格式取决于M的值

timestamp YYYMMDDHHMMSS

timestamp(14) YYYYMMDDHHMMSS

timestamp(12) YYMMDDHHMMSS

timestamp(10) YYMMDDHHMM

timestamp(8) YYYYMMDD

timestamp(6) YYMMDD

timestamp(4) YYMM

timestamp(2) YY

year[(2/4)] (70~69)1970~2069, 1901~2155 年份

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

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

4、字符串类型

类型 取值范围 描述
[national] char(M)[binary|ascii|unicode] 0~255个字符 固定长度为M的字符串,national关键字指定应该使用的默认字符集,binary指定了数据是区分大小写的(默认也是区分大小写的),ascii指定使用latin1字符集,unicode指使用usc字符集
char   char(1)
[national] varchar(M) [binary] 1~255 除可变长度外,其他与char相同

 

5、其它类型

类型 最大长度 描述
tinyblob 255  
tinytext 255 text区分大小写,blob不区分
blob 65535 65K
text 65535  
mediumblob 2^{24}-1 16M
mediumtext 2^{24}-1  
longblob 2^{32}-1 4G
longtext 2^{32}-1  
json    

 

类型 集合中的最大值 描述
enum('value1','value2',...) 65535 该类型只可以容纳所列值之一或null
set(‘value1','value2',...) 64 可以容纳一组值或null

四、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关键字

ADD ALL ALTER
ANALYZE AND AS
ASC ASENSITIVE BEFORE
BETWEEN BIGINT BINARY
BLOB BOTH BY
CALL CASCADE CASE
CHANGE CHAR CHARACTER
CHECK COLLATE COLUMN
CONDITION CONNECTION CONSTRAINT
CONTINUE CONVERT CREATE
CROSS CURRENT_DATE CURRENT_TIME
CURRENT_TIMESTAMP CURRENT_USER CURSOR
DATABASE DATABASES DAY_HOUR
DAY_MICROSECOND DAY_MINUTE DAY_SECOND
DEC DECIMAL DECLARE
DEFAULT DELAYED DELETE
DESC DESCRIBE DETERMINISTIC
DISTINCT DISTINCTROW DIV
DOUBLE DROP DUAL
EACH ELSE ELSEIF
ENCLOSED ESCAPED EXISTS
EXIT EXPLAIN FALSE
FETCH FLOAT FLOAT4
FLOAT8 FOR FORCE
FOREIGN FROM FULLTEXT
GOTO GRANT GROUP
HAVING HIGH_PRIORITY HOUR_MICROSECOND
HOUR_MINUTE HOUR_SECOND IF
IGNORE IN INDEX
INFILE INNER INOUT
INSENSITIVE INSERT INT
INT1 INT2 INT3
INT4 INT8 INTEGER
INTERVAL INTO IS
ITERATE JOIN KEY
KEYS KILL LABEL
LEADING LEAVE LEFT
LIKE LIMIT LINEAR
LINES LOAD LOCALTIME
LOCALTIMESTAMP LOCK LONG
LONGBLOB LONGTEXT LOOP
LOW_PRIORITY MATCH MEDIUMBLOB
MEDIUMINT MEDIUMTEXT MIDDLEINT
MINUTE_MICROSECOND MINUTE_SECOND MOD
MODIFIES NATURAL NOT
NO_WRITE_TO_BINLOG NULL NUMERIC
ON OPTIMIZE OPTION
OPTIONALLY OR ORDER
OUT OUTER OUTFILE
PRECISION PRIMARY PROCEDURE
PURGE RAID0 RANGE
READ READS REAL
REFERENCES REGEXP RELEASE
RENAME REPEAT REPLACE
REQUIRE RESTRICT RETURN
REVOKE RIGHT RLIKE
SCHEMA SCHEMAS SECOND_MICROSECOND
SELECT SENSITIVE SEPARATOR
SET SHOW SMALLINT
SPATIAL SPECIFIC SQL
SQLEXCEPTION SQLSTATE SQLWARNING
SQL_BIG_RESULT SQL_CALC_FOUND_ROWS SQL_SMALL_RESULT
SSL STARTING STRAIGHT_JOIN
TABLE TERMINATED THEN
TINYBLOB TINYINT TINYTEXT
TO TRAILING TRIGGER
TRUE UNDO UNION
UNIQUE UNLOCK UNSIGNED
UPDATE USAGE USE
USING UTC_DATE UTC_TIME
UTC_TIMESTAMP VALUES VARBINARY
VARCHAR VARCHARACTER VARYING
WHEN WHERE WHILE
WITH WRITE X509
XOR YEAR_MONTH

ZEROFILL

七、optimize优化表

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

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

1、语法

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

常用方式

OPTIMIZE TABLE 表名;



相关推荐

Centos7的yum源中没有mysql,为了解决这个问题,我们需要先下载mysql的repo源。 一、下载mysql的repo源并安装 wget http://repo.mysql.com/mysq

备份的类型 1、根据是否需要数据库离线 (1)冷备: 需要关mysql服务,请写请求均不允许 (2)温备: 服务在线,但仅支持读请求。 <p

如果主库已经投入使用了,此时如果需要主从同步,需要导出所表结构和数据,然后导入到从库中。这时需要注意修改/var/lib/mysql/auto.cnf中uuid。 master配

一、事务的ACID 1、Atomicity原子性 Transactions are often composed of multiple statements. Atomicity guarantee

mysql的权限分为4级:全局、数据库、表、列。 1、grant grant privileges [

insert [into] table [(column1,column2,column3,...)] values (val

一、语法 select [optioins] items [into file_details] from tables [where conditions] [group by gr

一、语法 update [low_priority] [ignore] tablename set column1=expression1,... [where condition] [order b

一、语法 1、基本语法 alter table [ignore] tablename alteration [,alteration...] (1)ignore,如果会导致唯一键重复,则这些操作将自动

delete [low_priority] [quick] [ignore] from table [where conditio