社区微信群开通啦,扫一扫抢先加入社区官方微信群
社区微信群
相关信息在/etc/my.cnf中
# 进入本机mysql
mysql -u 用户名 -p密码 # mysql -u root -proot p后直接跟密码
mysql -h localhost -u root -proot
mysql -h 127.0.0.1 -u root -proot
mysql --user=root --password=root
#连接到远程主机上的mysql
mysql -h 远程主机地址 -u test -p123
mysql --host=ip --user=用户名 --password=密码
'''
-h 有三种参数
%:任意主机
localhost:仅本地可以登录(比127.0.0.0更优)
localhost和127.0.0.1大致下一样,区别:
localhost:
无需联网
不适用网卡,不受网络防火墙和网卡的限制,不会解析成ip,不占网卡、网络资源。
本机访问
127.0.0.1:
无需联网
网卡传输,受网络防火墙和网卡限制
本机访问
本机ip
联网
网卡传输,受防火墙和网卡限制
本机或外部访问
Ip地址:指定的ip可登录
'''
# 退出mysql
mysql> quit;
mysql> eixt;
# 查看数据文件存放路径
mysql> show variables like '%datadir%';
# 查看用户使用端口
mysql> show variables like 'port';
#查看每个库所使用的编码
mysql> show variables like 'character%';
# 查看表中的索引,两种方式:
mysql> show index from 表名;
mysql> desc 表名;(看哪个是主键 PRI)
1. 创建 Create
# 创建
mysql> create database db;
# 创建指定编码格式的数据库
mysql> create database character set utf8/gbk;
# 创建数据库,当不存在该数据库时再创建
mysql> create database if not exists db;
#创建数据库,并制定编码类型
mysql> create database if not exists db character set gbk/utf8;
2. 查询 Retrieve
# 查询所有数据库的名字
mysql> show databases;
# 查询某个数据库的创建时的编码格式
mysql> show create database db;
# 现在使用的是哪个数据库
mysql> select database();
3. Update 修改
# 修改数据库的编码格式为:
mysql> alter database db character set utf8/gbk;
4. Delete 删除
# 删除数据库
mysql> drop database db;
# 判断是否存在,然后再删除
mysql> drop database if exists db;
5. 使用
mysql> use db;
1. Create 创建
# 创建表 一定要设置主键(是索引,当数据库中量大时,可加快查找速度)。null:可为空,not null:不可为空
# 数据类型
# 1 整数型 int(n) 存储时占4个字节 ,n表明了存的数的位数下限为n,默认11位长度,不够n的会在前面补0,-2 147 483 648~2 147 483 647,或者 0~4 294 967 295(无符号,2^32-1)
# 2 小数型 double(总共几位,小数点后面几位)
# 3 字符串 标准为单引号,双引号为扩展,使用varchar(n)存储时占空间更小
# char(n) 存储时占n个字节,输入最大为n个字符,长度为 0~255 个字符 2^8-1
#varchar(n) 存储时占String长度+1个字节,最大为n个字符,长度为 0~65535 个字符 2^16-1
# 4 日期
* date 只包含年月日 yyyy-MM-dd
* datetime 包含年月日时分秒,yyyy-MM-dd HH:mm:ss
* 时间戳 包含年月日时分秒,yyyy-MM-dd HH:mm:ss
* 如果不给这个字段赋值,或者赋值为null,则默认使用当前系统时间
mysql> create table biao(
#默认自增(一表最多一个,做主键),插入数据时,可不用管它,primary key主键,一列的唯一索引,可有多列组合而成复合主键,此时每个字段都是索引
id int auto_increment,
name varchar(3) not null default 'hua',
age int(3) null default 18,
sex int(1) null,
primary key(name,sex) #复合主键,每个字段都是索引,为之后加快查找
)engine=InnoDB default charset=utf8;
mysql> create table user(
id int primary key auto_increment,
name varchar(5),
password varchar(10)
);
insert into user(name,password) values('bb','bbb');
insert into user values(null,'aa','aaa');
insert into user(name,password) values
('bb','bbb'),
('cc','ccc');
# 复制一个表的结构
mysql> create table teacher_table like student_table;
2. Retrieve 查询
# 查看当前数据库中有几个表
mysql> show tables;
# 查看当前数据库中某个表的结构
mysql> desc student_table;
# explain:可看是否通过主键索引进行查询,显示了 mysql 如何使用索引来处理 select 语句以及连接表
mysql> explain select name,age from biao1 where sex=0;
mysql> select * from biao1 where id>1 and name != 'mign' and sex=0 and age between 5 and 14 and num in (3,6,8) and count not in (3,12);
3. Update 修改
1. 修改表名
alter table 表名 rename to 新表名;
2. 修改表的字符集/编码
alter table 表名 character set utf8/gbk;
3. 添加一列
alter table 表名 add 列名 数据类型;
4. 修改列名称、类型
mysql> alter table 表名 modify 列名 修改后的类型;//只修改列的类型
mysql> alter table 表名 change 原列名 新列名 修改后的类型;//修改列的名字的类型
alter table biao change id name varchar(20);
5. 删除列
mysql> alter table 表名 drop 列名;//需在表中保留至少一个列
4. Delete 删除
# 删除表
mysql> drop table student_table;
# 如果存在某表,将其删除
mysql> drop table if exists student_table;
1. 添加数据
* 除了数字类型,其他类型使用要用引号(单双皆可)
# 向表中插入一条数据
mysql> insert into biao1 values('li',32,0);#每个字段都插入,默认给所有列添加
mysql> insert into biao1(name,age) values('li',22); #个别字段插入,没有添加的字段,给默认值。
# 向表中插入几条数据,不定义列名,默认给所有列添加值
mysql> insert into biao1 values
('ling',2,0),
('dsf',321,0),
('lsa',323,0);
1.5 添加数据replace
replace into biao1(name, sex) values('hua',12);
首先该表要有主键和唯一索引
插入时,按主键或唯一索引查找
若没找到:插入
若找到:先把原来的那行删掉,再插入
2. 删除数据
#删除一条/若干条
mysql> delete from 表名 [where name='dalian'];
# 删除所有行,将biao1清空,列名还在
1. mysql> delete from biao1;//不推荐使用,有多少行,会执行多少次删除操作
2. mysyl> truncate table 表名;//推荐,先删除表,在创建一张一样结构的表,执行2次操作
3. 修改数据
# 将biao中所有sex=0的行中的name改为'hua',age改为18,不加where会将表中所有的记录修改
mysql> update 表名 set name='hua',age=18 [where sex=0];
# 查看表中数据
select * from biao1;
select * from db.biao;#查看db库下的biao表的数据
'''
where:条件列表
group by:分组字段
having:分组之后的条件
order by:排序
limit:分页限定
'''
1. 基础查询
1.0.查询某个表中所有列名:有两种方法,分别是:
>DESC 表名
>select columns from 表名
1.1 多个字段的查询
select 列名1,列名2 from 表名;#可用*表示查询所有字段select * from biao
1.2 去除重复的结果集
select distinct 列A from biao;//把列A所有的数据去重后返回
select distinct 列名,列名2 from biao;//此时两个列的名字都一样时,才去重
1.3 计算列(一般是数值型)
# 可使用四则运算
# 计算英语和数学成绩的总分
select ength,math,ifnull(ength,0)+ifnull(math,0) from biao;//会构造一个ength+math列
#ifnull(是否值的null的列,若为null则替换成xx)
1.4 起别名
select ength,math,ifnull(ength,0)+ifnull(math,0) as 总分 from biao;//会构造一个总分列(ength+math类名的替换)
select max(math) 数学分数 from biao;
select max(math) as
2. 条件查询
2.1 where后跟条件
运算符
< > <= >= =
where age <> 20;#年龄不等于20
where age between 20 and 30;#年龄在[20,30]
where age in(20,30,40);#年龄等于20或30或40
where math is null;#查询数学成绩为Null
where math is not null;#isn't 查询数学成绩不为Null
and && or || not != #都可以
3. 模糊查询
like
'''
_:表示单个任意字符
%:表示任意个字符(0或多个)
'''
#查询名字是三个字的人
select * from biao where name like '___';
#查询名字第二个字是化的人
select * from biao where name like '_化%'
#查询姓马的人有哪些
select * from biao where name like '马%';
4. 排序查询
select * from biao1 order by name; #根据name列,默认从小到大排列
select * from biao1 order by name asc; #根据name列,从小到大排列
select * from biao1 order by name desc; #根据name列,从大到小排列
select * from biao1 order by name asc,age desc; #根据name从小到大排,若相同,按age从大到下排
5. 聚合函数 #将一列数据作为整体,进行纵向的计算
5.0 计算时,会默认排除Null的那个数据,怎么办?
1 选择不包含null的列进行计算:eg:主键 select count(id) from biao;
2 ifnull函数:select count(ifnull(english,0)) from biao;
5.1 count : 计算个数 默认排除Null的那个数据
select count(english,0) from student;#有多少人
select count(id) from biao;
5.2 max :计算最大值
select max(math) from biao;
5.3 min :计算最小值
select min(math) from biao;
5.4 sum : 计算和 默认排除Null的那个数据
select sum(math) from biao;
5.5 avg : 计算平均值
select avg(math) from biao;#默认排除Null的那个数据
select avg(ifnull(math,0)) from biao;#把没参加考试的那个看作0分
6. 分组查询
select 分组字段、聚合函数 group by 分组字段;
select 分组字段、聚合函数 group by 字段1, 字段2;
# 按性别分组,分别查询男、女(性别)的平均分和人数
select sex, avg(math), count(id) group by sex;
# 按性别分组,分别查询男、女(性别)的平均分和人数,分数低于70不参与分组
select sex, avg(math), count(id) where math>=70 group by sex;
# 按性别分组,分别查询男、女(性别)的平均分和人数,分数低于70不参与分组,分组后,人数要大于2个人
select sex, avg(math), count(id) where math>=70 group by sex having count(id)>2;
where和having的区别:
where在分组前进行限定(所以:where x group by xx),不满足条件,不参与分组
having在分组后进行限定(所以: group by xx having x),不满足条件,不会被查询出来
7. 分页查询
limit 为mysql的“方言”,在其他软件中,有不同的分页关键字
语法:
limit 显示前几条数据;
limit 开始的索引,每页查询的条数;
公式:开始的索引=从0开始
select * from biao1 limit 5; #只显示前5条数据
-- 每页显示3条
select * from biao limit 0,3; -- 第1条~第三条
select * from biao limit 6,3; -- 第7条~第9条
8. 多表查询
多表的联结:
1)左联结(left join),联结结果保留左表的全部数据
2)右联结(right join),联结结果保留右表的全部数据
3)内联结(inner join),取两表的公共数据
//通过z查找A中的x和B中的y,y可以为空
select x,y from A left join B on A.z=B.z;
select a.x,b.y from A as a left join B as b on a.z=b.
// left join
//inner join
概念: 对表中的数据进行限定,保证数据的正确性,有效性和完整性
分类:
1.主键约束:primary key
1.1 主键约束 非空且唯一,一般一张表只有一个字段为主键(也可有复合主键),主键时表中记录的唯一标识
1.1.1 创建表时,添加主键约束
create table su(
id int primary key,
name varchar(20)
);
1.1.2 创建表后,还没有数据呢,添加主键约束
alter table su modify id int primary key;
alter table su add primary key(id);
1.1.3 表中已有数据后,添加主键
*. 把表中的主键部分重复的删掉,再设置主键(但一般不现实)
*. 新建一张表,设置主键,把原来表中的数据插进来。
1.1.4 删除主键约束
不正确:alter table su modify id int;
alter table su drop primary key;#为主键的该列并不会消失,只是不再作为主键了,但当自增的id作为主键时,该命令无法执行,因为自增就意味着是主键
1.1.5 创建表时,添加复合主键
create table su(
id int,
age int,
name varchar(20),
primary key (age,name)
);
1.1.6 创建表后,还没有数据呢,添加复合主键
alter table su add parimary key(age,name);
1.1.7 删除复合主键
alter table su drop primary key;
1.2 自动增长:和主键一块使用,如果某一列是数值类型(一般是int)的,使用auto_increment,可完成值自动增长
1.2.1 创建表时,添加自动增长
create table su(
id int primary key auto_increment,
name varchar(20)
);
insert into table su values(10,"hua");#可指定此时的id
insert into table su values(null,"hua");#会是11,从上次的id值自动增长一下
1.2.2 创建表后,添加自动增长
alter table su modify id int auto_increment;
1.2.3 删除自动增长
alter table su modify id int;
2. 非空约束:not null 该列的值不能为空
2.1 创建表示添加约束
create table su (
id int,
name varchar(20) not null -- name非空
);
2.2 创建表后,添加非空约束
alter table su modify name varchar(20) not null;
2.3 删除name的非空约束
alter table su modify name varchar(20);
3. 唯一约束:unique 唯一约束限定的列的值不能重复,但可由多个null
3.1 创建表示,添加唯一约束
create table su(
id int,
phone_num varchar(20) unique -- 添加唯一约束
);
3.2 创建表后,添加唯一约束
alter table su modify phone_num varchar(20) unique;
3.3 删除列的唯一约束/唯一索引
不可以如此:alter table su modify phone_num varchar(20); ×
alter table su drop index phone_num;
4. 外键约束:foreign key
4.1 让表与表之间产生关系,从而保证数据的正确性和一致性
当表中有冗余【意味着在数据库中具有相同数据的多个副本】,某些列的值大量重复,修改时及其不方便,要逐条修改,可将表进行拆分,eg:员工在深圳的都是研发部,可分成两张表,一张员工表(多方),一张部门表,分别存储,查询时,从员工表中得到部门编号,再从部门表中通过部门编号得到which部门,但其中一个部门撤销了之后,从部门表/主表中将其删除,但是员工表/从表中有员工仍然还有在被删除的部门,导致数据不一致。如何做呢?引入外键约束
4.1.2 主表/主键表/-方
以公共关键字作主键的表/公共关键字不重复的表/部门表
4.1.3 从表/外键表/多方
以公共关键字作外键的表/公共关键字可重复的表/员工表
4.1.4 创建从表时,添加外键
create table 从表名/员工表(
dep_id int,
constraint dep_id_waijian foreign key (dep_id) references 主表名/部门表 (id)
);
4.1.5 创建从表后,添加外键
alter table 从表名 add constraint 外键约束的名字 foreign key (从表中公共关键字字段名) references 主表名 (主表中公共关键字字段名称【被唯一约束,一般是主键】);
alter table 员工表 add constraint dep_id_waijian foreign key (dep_id) references 部门表 (id);
4.1.6 删除外键
alter table 从表名 drop foreign key 外键约束的名字;
4.2 级联操作
如何修改从表/多方/员工表中某个部门的编号?直接修改主表/部门表是会报错的,所以要先把从表中对应的修改
a. 很麻烦
update 员工表 set dep_id=null where dep_id=1;
将主表/部门表中部门对应的id改为5
update 员工表 set dep_id=5 where dep_id=null;
b. 设置级联更新
级联更新和级联删除,修改主表/部门表,从表/员工表直接自动更新或删除
4.2.1 添加级联操作
要先删除外键 alter table 从表名 drop foreign key 外键约束的名字;
然后设置 alter table 从表/员工表 add constraint 外键约束名 foreign key (从表中公共关键字字段名) references 主表名 (主表中公共关键字字段名称【被唯一约束,一般是主键】) on update cascade on delete casecade;
1.增
#创建用户
mysql> create user '用户名'@'ip地址' identified by '密码';
# ip地址:
# %:任意主机;localhost:仅本地可以登录;Ip地址:指定的ip可登录
# 若无需密码
# identified 及其后面可以不用写
# 一般形式
mysql> create user 'test'@'%' identified by '123'
2.删
mysql> drop user '用户名'@'ip地址'
# mysql> drop user 'test'@'%';
3.改(修改密码)
3.1 修改用户密码
3.1.1 set password for '用户名'@'ip地址' = password('新密码');
# set password for 'root'@'localhost' = password('123');
3.1.2 update user set password=password('abc') where user=root;
#因为 表中存储的密码是加密的,所以不能直接set password='abc'
3.2 别的用户密码忘了可登陆root用户进行修改,而忘了root用户的密码,怎么办?
3.2.1 cmd->net stop mysql 停止mysql服务 (打开服务之后,才能登陆,退出mysql)
* 需要管理员运行cmd
3.2.2 无验证启动mysql服务器,并修改密码
cmd->mysqld --skip-grant-tables #不关闭
新的cmd->mysql # 登陆成功
mysql> use mysql;
mysql> update user set password=password('新密码') where user='root';
关闭这两个cmd命令行
打开任务管理器,手动结束mysqld.exe进程
3.2.3 重新启动mysql服务,使用新密码登陆
cmd->net start mysql 停止mysql服务 (打开服务之后,才能登陆,退出mysql)
* 需要管理员运行cmd
cmd->mysql -u root -p
3.3 权限
3.3.1
# 查看某个用户的权限
mysql> show grants for '用户'@'ip';
eg: show grants for 'test'@'%';
3.3.2 授权
# 给某个用户的授权
mysql> grant 权限列表 on 数据库.表 to '用户'@'ip';
eg: grant all privileges on 库名.表名 to 'test'@'%';
grant select,insert,update,delete on
'''
数据库名.* 数据库中的所有表
数据库名.表 指定数据库中的某张表
数据库名.存储过程 指定数据库中的存储过程
*.* 所有数据库中的所有表
usage 无访问权限
select 查看权限
insert 插入权限
alter 更改表权限
create 创建表权限
create user 创建用户权限
drop 删除表权限
update
delect
'''
3.3.3 取消授权
# 给某个用户取消某个权限
mysql> revoke 权限列表 on 数据库.表 from '用户'@'ip'
4.查
# 查看所有的用户的信息,(有个库加mysql,里有个表叫user,里有用户的信息)
mysql> select User,Host,Password from mysql.user;
或先进入mysql库
use mysql;
select User,Password from user;
# 查看现在所使用的用户名
mysql> select user();
# 查看某个用户的权限
mysql> show grants for '用户'@'ip';
eg: show grants for 'test'@'%';
# 事务操作(批处理:不用插入一次就提交一次浪费时间;也不是所有的插入后再提交,以免中间出错,前面的都操作失败,即便该次未提交成功,也只是该批次失败,未殃及全部):ACID 原子性(Atomicity),一致性(Consistency),隔离性(Isolation),持久性(Durability)
mysql> begin;#开启事务 或 start transaction;
mysql> insert into biao1 values('li',32,0);
mysql> insert into biao1 values
('ling',2,0),
('dsf',321,0),
版权声明:本文来源CSDN,感谢博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
原文链接:https://blog.csdn.net/puspos/article/details/89923333
站方申明:本站部分内容来自社区用户分享,若涉及侵权,请联系站方删除。
-
发表于 2020-03-08 11:05:12
- 阅读 ( 1342 )
- 分类:数据库
如果觉得我的文章对您有用,请随意打赏。你的支持将鼓励我继续创作!