MySQL
基础操作
1. 使用方法:
**一: 通过图型界面工具 , 如 Navicat **
二: 通过命令行敲命令来操作
2. SQL ( Structure query language ) 结构化查询语言
SQL语言分为4个部分: DDL (定义) DML (操作) DQL (查询) DCL (控制)
1、DDL语句 数据库定义语言: 数据库、表、视图、索引、存储过程,例如CREATE DROP ALTER
2、DCL语句 数据库控制语言: 例如控制用户的访问权限GRANT、REVOKE
3、DML语句 数据库操纵语言: 插入数据INSERT、删除数据DELETE、更新数据UPDATE
4、DQL语句 数据库操纵语言: 查询数据SELECT
3. SQL 语句中的快捷键
\G 格式化输出 (文本式 , 垂直显示)
\s 查看服务器端信息
\c 结束命令输入操作
\q 退出当前sql命令行模式
\h 查看帮助
MySQL 介绍
# mysql数据库管理软件,记录事物一些数据特征:
由库,表,记录组成.
库相当于一个文件夹
表相当于一个文件
记录就是文件里面一条一条的内容
表中的成员属性就是一个一个字段
可以为每个项目建立一个数据库
# 关系型数据库:表与表之间有联系
比如:mysql,oracle,db2,sqlserver
# 非关系型数据库: key-value 键值对形式 没有表的概念
比如:redis,mongodb,memcache
# 数据库语法特点:
1. SQL 语句可以换行,要以分号结尾
2. 命令不区分大小写,关键字和函数建议用大写
3. 如果提示符为'> 那么需要输入一个' 回车
4. 命令打错了换行后不能修改,可以用 \c 取消
# 数据库操作
查看数据库 show databases;
创建数据库 create database 库名 default charset utf8;
删除数据库 drop databases 库名;
打开数据库 use 库名;
# 数据表操作
查看表 show tables;
创建表 create table 表名(字段名1 类型,字段2 类型) engine=innodb default charset=utf8;
创建表 如果表不存在就创建 如果存在就不执行这条命令
create table if not exists 表名(
字段1 类型,
字段2 类型
);
删除表 drop table 表名;
表结构 desc 表名;
# 记录操作 增删改查
插入单条数据 insert into 表名(字段1,字段2,字段3) values(值1,值2,值3);
插入多条数据 insert into 表名(字段1,字段2,字段3) values(a值1,a值2,a值3),(b值1,b值2,b值3);
查询 select * from 表名;
select 字段1,字段2,字段3 from 表名;
select * from 表名 where 字段=某个值;
修改 update 表名 set 字段=某个值 where 条件;
update 表名 set 字段1=值1,字段2=值2 where 条件;
update 表名 set 字段=字段+值 where 条件;
删除 delete from 表名 where 字段=某个值;
# 退出MySQL
exit; 或者 quit;
# 卸载MySQL
1. windows 卸载
先关闭服务
cmd : mysql remove
删除已经解压的文件
重启电脑
2. linux 卸载
sudo apt-get autoremove --purge mysql-server-5.7
sudo apt-get remove mysql-common
sudo rm -rf /etc/mysql/ /var/lib/mysql
# 清理残留数据
dpkg -l |grep ^rc|awk '{print $2}' | sudo xargs dpkg -P
sudo apt autoremove
sudo apt autoreclean
1. 登录mysql
# 登录mysql的完整语法
mysql -u用户名 -p密码 -h地址ip
# 1. 登录mysql语法 默认root账户 是系统最高权限用户
mysql -uroot -p
127.0.0.1 => localhost 本地ip地址
# 2. 退出mysql
exit 或者 \q
# 3. 远程连接mysql服务器
mysql -uroot -p -h192.168.65.135
2. 查询当前登录的用户
# 查询当前登录的用户
select user();
# 设置密码
set password = password('123456')
# 去除密码
set password = password('')
# 设置密码
update mysql.user set password=password('123456') where user='root' and host='localhost';
# 刷新权限
flush privileges;
# 忘记密码
1. 先关闭mysql服务端
2. 重新启动: mysql --skip-grant-tables
3. 启动客户端: mysql -u
update mysql.user set password=password('') where user='root' and host='localhost';
flush privileges
4. 重新启动mysqld
3. 创建用户
# 给具体某个ip设置账户密码 用来访问数据库(公网ip)
create user 'ceshi100'@'192.168.65.1' identified by '333';
# 给具体某个网段下的所有ip设置共同的账户密码,用来访问数据库(公网ip) % 代表任意的ip 0 ~ 255
create user 'ceshi101'@'192.168.65.%' identified by '444';
# 所有的ip都可以登录到服务器数据库中
create user 'ceshi102'@'%' identified by '555';
# 刷新权限,立刻生效
flush privileges
4. 权限设置
# 查看哪个ip 哪个用户的权限
'''USAGE 没有任何权限'''
show grants for 'ceshi102'@'%';
+--------------------------------------+
| Grants for ceshi102@% |
+--------------------------------------+
| GRANT USAGE ON *.* TO 'ceshi102'@'%' |
+--------------------------------------+
grant 权限 on 数据库.表名 to '用户名'@'ip地址' identified by '密码'
'''
主要权限
select 查看数据表的权限
insert 插入数据表的权限
update 更新数据表的权限
delete 删除数据表的权限
drop 删除数据库的权限
* 代表所有
'''
# 设置权限
grant select,drop on *.* to 'ceshi102'@'%' identified by '333';
# all 代表所有权限
grant all on *.* to 'ceshi102'@'%' identified by '333';
# 移除权限
# 单个
revoke drop on *.* from 'ceshi102'@'%'
# 所有
revoke all on *.* from 'ceshi102'@'%'
# 删除用户
drop user 'ceshi102'@'%'
# 刷新权限 立刻生效
flush privileges
5. 操作数据库
1. 操作[数据库] (文件夹)
# 增
# 创建一个数据库
create database db0901 charset utf8;
# 查
# 查看所有数据库
show databases
# 查看建立数据库的语句
show create database db0901
# 改
alter database db0901 charset gbk;
# 删
drop database db0901;
2. 操作[数据表] (文件)
# 先选择数据库 use 数据库
use db0901;
select database();
# 增
# 字段名1 类型1 , 字段名2 类型2 ...
create table t1(id int , name char);
# 查
# 查看所有表
show tables;
# 查看建表语句 配合\G 可以垂直显示
show create table t1;
# 查看表结构
desc t1
# 改
# modify 只能改变数据类型
alter table t1 modify id tinyint;
alter table t1 modify name char(5);
# change 连字段名 + 类型 一起改变
alter table t1 change id ID tinyint;
alter table t1 change id ID tinyint,change name NAME char(4);
# add 添加字段
alter table t1 add age int;
alter table t1 add gender char(4);
alter table t1 add gender char(4) first;
alter table t1 add level int after ID;
# drop 删除字段 column列
alter table t1 drop column age;
alter table t1 drop gender;
# rename 更改表名
alter table t1 rename t1_1;
# 复制表
create table t2 select user,host,password from mysql.user;
# 只复制表结构
create table t3 select user,host,password from mysql.user where 1!=1;
# 删
drop table t1_1;
3. 操作[记录] (文件中的数据)
# 增
# 一次插入一条数据
'''insert into 表名(字段1,字段2, ...) values(值1,值2, ... )'''
insert into t1_1(id,name) values(1,'xboy1');
# 一次插入多条数据
insert into t1_1(id,name) values(2,'xboy2'),(3,'xboy3'),(4,'xboy4');
# 不指定具体字段插入(默认把所有字段对应的值查一遍)
insert into t1_1 values(5,'xboy5');
# 指定单个字段插入数据
insert into t1_1(name) values('xboy6');
# 查
# * 代表所有
select * from t1_1;
# 指定字段进行查询
select id,name from t1_1;
# 改
# update 表名 set 字段=值 where 条件
update t1_1 set name='王闻你真帅' where id = 1
# 切记更改是,加上条件,否则会被全部改掉
update t1_1 set name='王闻你真帅'
# 删
# 指定删除id=2的这条数据
delete from t1_1 where id = 1
# 如果不加条件,删除所有数据
delete from t1_1
# 重置数据表 (删除数据 + 重置id)
truncate table t1_1;
6. 常用数据类型
1. 整型
tinyint 1个字节 有符号(-128 ~ 127) 无符号(unsigned) (0 ~ 255) 小整型值
int 4个字节 有符号(-21亿 ~ 21亿 左右) 无符号(unsigned) (0 ~ 42亿) 大整型值
create table t1(id int , age tinyint);
insert into t1 values(2200000000,1) error
insert into t1 values(2100000000,1);
insert into t1 values(2200000000,128) error
insert into t1 values(2200000000,127)
2. 浮点型
float(255,30) 单精度
double(255,30) 双精度
decimal(65,30) 金钱类型,使用字符串的形式保存小数
'''默认存在四舍五入'''
create table t2(f1 float(5,2) , f2 double(5,2) , f3 decimal(5,2));
insert into t2 values(1.777777777777777777777,1.777777777777777777777,1.777777777777777777777)
'''float 默认小数保留5位,double 小数位截取16位,decimal(10,0) 默认保留整数部分,存在四舍五入'''
create table t3(f1 float , f2 double , f3 decimal);
insert into t3 values(1.777777777777777777777,1.777777777777777777777,1.777777777777777777777)
create table t4(f1 float(5,2));
insert into t4 values(12.3456789) # 12.35
insert into t4 values(123.3456789) # 123.35
insert into t4 values(1234.3456789) # error
3. 字符串
char 字符长度 255个
varchar 字符长度 21845个 (注意: 总字节数不超过65535)
char 定长 ,不够则空格补全
特点:
浪费空间
读取速度快
varchar 变长 , 预留1-2bytes来存储真是数据的长度
特点:
节省空间
读取速度慢
ps: 在存储的数据量刚好达到存储宽度限制时,varchar更费空间
总结:
大多数情况下存储的数据量都达不到宽度限制,所以大多数情况下varchar更省空间
但省空间不是关键,关键是省空间会带来io效率的提升,进而提升了查询效率
# 验证
create table t11(x char(5));
create table t12(x varchar(5));
insert t11 values("我擦嘞 "); -- "我擦嘞 "
insert t12 values("我擦嘞 "); -- "我擦嘞 "
t11=>字符个数 5 字节个数 11
t12=>字符个数 4 字节个数 10
set sql_mode="pad_char_to_full_length";
select char_length(x) from t11;
select char_length(x) from t12;
select length(x) from t11;
select length(x) from t12;
char(11) 定长: 固定开辟11个字符长度的空间(手机号,身份证号,银行卡), 开辟空间速度上char速度更快
varchar(11) 变长: 最多开辟11个字符长度的空间(评论,消息留言,地址), 开辟空间速度相较于char慢一点
text 文本类型: 存论文 小说 文章
create table t5(c char(11), v varchar(11), t text);
insert into t5 values('1111','健康第三方三房空间三大','sadfafdsafsfs');
insert into t5 values('1111','健康第三方三房空间三大2','sadfafdsafsfs'); error
# concat 拼接任意长度的字符串
select concat(c,':',v,':',t) from t5
4. mysql 中的函数
select user();
select concat();
select concat(参数1,参数2,参数3, ... ) 把所有的参数拼接在一起
5. 枚举 集合
enum 枚举: 从列出来的数据当中选一个(性别)
set 集合: 从列出来的数据当中选多个(自动去重)
create table t6(
id int,
name varchar(10),
money float(6,3),
sex enum('男性','女性','禽兽','人妖','雌雄同体','半兽人','阿凡达'),
hobby set('抽烟','喝酒','吃肉','烫头','喝假酒')
);
# 正常写法
insert into t6(id,name,money,sex,hobby) values(1,'克拉依买买提',123.3211,'半兽人','喝假酒,抽烟');
insert into t6(id,name,money,sex,hobby) values(1,'克拉依买买提',123.3211,'半兽人','喝假酒,抽烟,喝假酒,抽烟,烫头,烫头,烫头',);
6. 时间
date YYYY-MM-DD 年月日 (结婚纪念日,节假日)
time HH:MM:SS 时分秒 (体育竞赛)
year YYYY 年份值 (历史,酒的年份)
datetime YYYY-MM-DD HH:MM:SS 年月日 时分秒 (用户登录时间,下单时间)
timestamp YYYYMMDDHHMMSS(时间戳) 自动更新时间(不需要手动,系统自动更新时间) 数据上一次的修改时间
# select now() 获取当前时间
create table t1(d date , t time , y year , dt datetime);
insert into t1 values('2020-09-03','08:30:00','2020','2020-09-03 08:30:00');
insert into t1 values(now() , now() , now() , now());
create table t2(dt datetime , ts timestamp);
insert into t2 values(null , null);
insert into t2 values(20200825082530,20200825082530);
insert into t2 values(20200825082530,20380825082530); error 不能超过2038年的某一年
create table student(
id int,
name char(10),
born_year year,
bitrh date,
reg_time datetime
);
insert student values
(1,"wangjing","1911","1911-11-11","1911-11-11 11:11:11"),
(2,"lxx","1988","1988-11-11","1988-11-11 11:11:11");
insert student values
(3,"wangjing","1911","19111111","19111111111111");
# 注意: timestamp 应该用于记录更新时间
create table t9(
id int,
name varchar(16),
-- update_time datetime not null default now() on update now(),
update_time timestamp,
reg_time datetime not null default now()
);
insert into t9(id,name) values(1,"egon");
# 测试效果
mysql> select * from t9;
+------+------+---------------------+---------------------+
| id | name | update_time | reg_time |
+------+------+---------------------+---------------------+
| 1 | egon | 2020-09-01 16:45:51 | 2020-09-01 16:45:51 |
+------+------+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> update t9 set name="EGON" where id=1;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t9;
+------+------+---------------------+---------------------+
| id | name | update_time | reg_time |
+------+------+---------------------+---------------------+
| 1 | EGON | 2020-09-01 16:46:50 | 2020-09-01 16:45:51 |
+------+------+---------------------+---------------------+
1 row in set (0.00 sec)