# 连接Mysql

1
2

# mysql -h主机地址 -P端口 -u用户名 -p密码
mysql -hlocalhost -P3306 -uroot -p

# 数据库操作

# 新增数据库

create database [IF NOT EXISTS] 数据库名字 [charactar set 字符集] [collate 排序规则]

1. IF NOT EXISTS: 如果数据库不存在就直接创建, 存在就跳过, 不报错.
2. charactar set: 字符集, 代表当前数据库所有表都用该字符集存储.
3. collate: 排序规则.

# example 1

1

create database mydatabase;

# example 2

1

create database if not exists mydatabase charactar set utf8mb4 collate utf8mb4_general_ci;

# 删除数据库

drop database [IF EXISTS] 数据库名;

# example

1

drop database mydatabase;

# 修改数据库

alter database 数据库名 default [character set 字符集] [collate 排序规则];

# example

1

alter database mydatabase default character set utf8mb4;

# 查看数据库

show database [like ‘表达式’];

like: 表达式, 可以使用_表示匹配单个字符; %表示匹配多个字符.

show create database 数据库名;

查看数据库的字符集和排序规则.

# example1

1

show databases;

# example2

匹配my开头的数据库

1

show databases like 'my%';

# 选择数据库

use 数据库名字;

1

use mydatabase;

# 数据表操作

# 新增数据表

1 2 3 4

create table [IF NOT EXISTS] [数据库名.]表名( 字段名 字段类型 [not null] [default '内容'] [comment '注释'] [primary key[(字段1, 字段2, ...)]] [auto_increment] [unique key[(字段1, 字段2, ...)]], ...... ) [engine=engine_name] [default] [charset=[字符集](#数据库字符集)] [collate=[排序规则](#数据库排序规则)];

1. IF NOT EXISTS: 如果表不存在则创建
2. 数据库名.: 在没有使用use指定数据库名字的情况下, 可以通过指定数据库名字创建到对应数据库中.
3. 字段选项:
   • not null: 字段不能为null.
   • default: 默认内容.
   • comment: 注释.
   • primary key: 主键, 如果需要设置复合主键的话添加各个字段名.
   • auto_increment: 内容自增.
   • unique key: 唯一键, 复合唯一键添加各个字段名.
4. 建表选项
   • engine: 使用对应的存储引擎
   • default: 如果没有为表指定后面的选项, 则使用后面的选项设定.
   • charset: 字符集
   • collate: 排序规则

# example

创建一张表.

• id: 不能为空, 自增, 主键.
• name: 最长15个字符, 不能为空.
• age: 最大255, 不能为空.
• height_cm: 最大255, null表示不设定.
• gender: 性别.
• create_at: 不能为空, 默认是数据添加时间
• update_at: 但数据发生更新时, 自动更新该字段的时间为当前时间.
• update_by: 修改人id, 为它创建一个索引.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

create table users(
	id bigint not null auto_increment primary key,
	name varchar(15) not null comment '名字',
	age tinyint unsigned not null comment '年龄',
	height_cm tinyint unsigned comment '身高(cm)',
	gender tinyint(1) unsigned comment '性别(0: 女; 1: 男;)',
	create_at datetime not null default current_timestamp comment '创建时间',
	update_at datetime on update current_timestamp comment '修改时间',
	update_by bigint comment '修改人id',
	index(update_by)
);

# 删除数据表

drop table [数据库名.]表名;

# 修改数据表

用到了在更新~ 现在懒得更新哈哈, 但是大致使用该命令: alter table add/drop ...

# example

1

alter users add index(create_at); # 添加索引

1

alter users drop index(create_at); # 删除索引

# 查看数据表

show tables [like ‘表达式’];

like: 表达式, 可以使用_表示匹配单个字符; %表示匹配多个字符.

查看表结构:

• describe 表名;
• desc 表名;
• show columns from 表名;

# example 1

显示所有表

1

show tables;

# example 2

显示users结尾的所有表

1

show tables '%users';

# 新增数据表, 从已有的表迁移结构

create table [数据库名.]表名(复制到的位置) like [数据库名.]表名(从哪里复制的位置);

# example

1

create table users like new_users;

# 数据操作

# 新增数据

insert into 表名[(字段列表)[,…]] values(值)[,…];

insert命令还有其他更多选项可以输入, 等我用到了再更新吧哈哈

# example 1

1

insert into users(name, age, gender) values('kaylee', 24, true);

# example 2

1

insert into users(name, age, gender) values('张三', 30, true),('李四', 32, false),('王五', 35, true);

# 删除数据

delete from [数据库名.]表名 [where 条件表达式];

truncate table 表名;

truncate会直接清空整张表, 包括自增长键的起始值. delete则是删除部分键, 如果删除全部数据的话, 自增键的值不会被重置.

# example

删除表中所有数据: delete from users;

删除name为kaylee的数据.

1

delete from users where name = 'kaylee';

# 修改数据

update [数据库名.]表名 set 字段名 = 值[, …] [where 条件表达式];

# example

修改所有数据的update_by值: update users set update_by = 1;

修改name为kaylee的height_cm

1

update users set height_cm = 175 where name = 'kaylee';

# 查看数据

select 字段名[ as 别名][,…] from [数据库名.]表名 [where 条件表达式[ and/or 表达式 …]] [group by 字段名[,…]] [having 条件表达式] [order by 字段名[,…] [排序方式]] [limit 数量] [distinct];

• 字段名可以使用*号表示所有字段
• 条件表达式: name = 'kaylee' age >= 24 name in ('张三', '李四')
• group by: 数据分组
• having: 可以在数据分组后, 使用having进行聚合计算去筛选数据, 该表达式的值只能为Boolean.
• order by: 排序, 如果不指定排序方式, 默认是ASC(升序), 也可以指定为DESC(降序).
• limit: 限制结果返回的数量
• distinct: 不显示重复记录

当然了, select语句还有更多的操作, 这里简单展示最常用的, 还有表链接等等.

# example 1

查询全部数据: select * from users;

查询年龄>=24且性别为男的用户.

1

select * from users where age >= 24 and gender = true;

# example 2

查询大于男性平均年龄值的用户, 并按照年龄降序展示. 这里涉及到子表查询, 基础差的同学可能看了会纳闷.

1

select * from users where age >= (select avg(age) from users where gender = true) order by age desc;

# example 3

查询男性平均年龄和女性平均年龄分别是多少, 并按照平均年龄降序排序.

1

select gender, avg(age) as age_avg from users group by gender order by age_avg desc;

# 从SQL文件导入数据

你可以使用下面两种方式导入数据:

# 第一种: 连接时导入

1

mysql -uroot -p123456 < db.sql

# 第二种: 已连接导入

1

source db.sql

# 用户操作

# 新增用户

create user ‘用户名’[@‘主机名’] identified by ‘密码’;

主机名: 不填写主机名, 默认为%, 表示允许所有主机访问.

# example

创建用户名为kaylee, 密码为2CJ^8Tz$rF27f*的用户, 并允许只有本地才能的访问.

1

create user 'kaylee'@'localhost' identified by '2CJ^8Tz$rF27f*';

# 删除用户

drop user ‘用户名’[@‘主机名’];

主机名: 不填写主机名, 默认为%.

# example

1

drop user 'kaylee'@'localhost';

==注意: 删除用户时, 主机名需要能够匹配上, 如果kaylee的主机是localhost, 直接执行drop user 'kaylee';是无法删除的, 因为省略主机名后, mysql默认去删除'kaylee'@'%', 此时会删除失败.==

# 修改密码

set password for ‘用户名’[@‘主机名’] = password(‘密码’);

主机名: 不填写主机名, 默认为%.

# example

1

set password for 'kaylee'@'localhost' = password('K!G4k4QtZU6JU!');

# 查看用户

1

select * from mysql.user;

# 用户权限操作

# 为用户新增权限

grant 权限名[, …] on [数据库名.]表名 to ‘用户名’[@‘主机名’];

权限名:

• all privileges: 代表全部权限
• 其他细粒度的权限可以在这里找到

表名: 可以使用*.*表示所有数据库的所有表, 也可以使用dbname.*来指定某个数据的所有表.

主机名: 不填写主机名, 默认为%.

# example

将users表的select权限赋予用户kaylee.

1

grant select on users to 'kaylee'@'localhost';

# 删除用户权限

revoke 权限 on [数据库名.]表名 to ‘用户名’[@‘主机名’];

权限名:

• all privileges: 代表全部权限
• 其他细粒度的权限可以在这里找到

表名: 可以使用*.*表示所有数据库的所有表, 也可以使用dbname.*来指定某个数据的所有表.

主机名: 不填写主机名, 默认为%.

# 查看用户权限

show grants for ‘用户名’[@‘主机名’];

主机名: 不填写主机名, 默认为%.

# example

1

show grants for 'kaylee'@'localhost';

# example

回收kaylee对于users表的select权限.

1

revoke select on users to 'kaylee'@'localhost';

# 同步/生效权限

1

flush privileges;

# 其他操作

# 设置数据库字符集

其他字符集内容参考数据库字符集

1

set names utf8;

执行上面命令等同于执行:

1
2
3

SET character_set_client='utf8';
SET character_set_connection='utf8';
SET character_set_results='utf8';

# 查看MYSQL信息

# 查看全部信息

1

show variables

# 查看数据库字符集信息

1

show variables like '%character%';

# 数据库字符集

更多内容可以使用show charset;命令在mysql中查看.

# 数据库排序规则

utf8mb4_unicode_ci为了能够处理特殊字符的情况, 实现了略微复杂的排序算法.

更多内容可以使用show charset;命令在mysql中查看.

# 数据库类型

# 数值型

# 类型中的M解释

M表示总数字位数, 可以不指定, 默认存储到硬件允许的限制内. TINYINT(1) 只能存储一位数字, 即范围为0~9.

# DECIMAL类型

DECIMAL(6,2): 最多可以存储6位数字，小数位数为2位; 因此，列的范围是从-9999.99到9999.99。

从上面的表格可以看出, 从MySQL 8.0.17开始, 存储带有小数的内容推荐应该使用DECIMAL类型.

# 日期和时间类型

# 类型中的fsp解释

可以给出0~6范围内的可选fsp值来指定小数秒精度. 值0表示没有小数部分. 如果省略，则默认为 0.

# DATETIME和TIMESTAMP类型配置自动填写

DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

• DEFAULT: 表示数据被时间时自动填充当前时间
• ON UPDATE: 表示数据被更新时自动填充当前时间

# 字符串类型