mysql常用命令
show databases; //会显示数据库服务器上的所有的数据库
create databases test; //创建数据库test
drop database test; //删除test数据库
use test; //选择test数据库
show tables; //查看当前选中数据库中的数据表
create table user(username varchar(20),password char(32)); //创建user表,表内有username字段和password字段
desc user; //查看表的结构
drop table user; //删除user表
show create database test; //查看数据库创建方式
show create tables user; //查看表创建方式
alter table user modify username char(30); //修改字段类型,不能修改名字
alter table user add age int(3); //添加字段age,默认自动添加到最后
alter table user add email varchar(60) after password; //在指定的password字段后面添加一个email字段
alter table user add id int(11) first; //在表的最前面添加一个id字段
alter table user drop age; //删除age字段
alter table user change email em char(32); //修改指定字段email,改为em,同时修改字段类型和长度为char(32)
alter table user modify em char(32) first; //修改em字段的位置到最前面
alter table user modify em char(32) after password; //修改字段em的位置到password字段后面
alter table user rename new_user; //把user表名字修改为new_user
数据类型
- 整型:tinyint(1个字节)、smallint(2个字节)、mediumint(3个字节)、int(4个字节)、bigint(8个字节)
- 浮点类型:
-
float(m, d),单精度浮点数,4个字节,m表示总位数,d表示小数位数
-
double(m, d),双精度浮点数,8个字节,m表示总位数,d表示小数位数
-
decimal(m, d),以字符串的形式存储浮点数,用于金融领域等要求严格的场景
-
-
字符类型:
-
char:定长字符串,0~255个字节
-
varchar:变长字符串,0~65535个字节
-
-
时间日期:
-
date:日期,格式:2018-04-16
-
time:时间,格式:15:36:30
-
datetime:日期时间,格式:2018-04-16 15:37:38
-
timestamp:时间戳
-
year:年,只占1个字节,年份范围:1901~2155
-
-
符合类型:
-
set:集合类型,格式:set(s1, s2, ..., s63),最多63种可能
-
enum:枚举类型,格式:enum(e1,e2, ... ,e65535),最多65535种可能
-
-
字段修饰:
-
unsigned:无符号数
-
zerofill:高位0填充,防止出现负数
-
auto_increment:自动增加(1),用于整数,经常与主键结合使用
-
default:设置默认值
-
not null:不能为空
-
-
字符集及存储引擎
-
查看支持的字符集:
show character set;
,我们通常只使用utf8,不能书写成utf-8 -
查看支持的存储引擎:
show engines;
-
常用存储引擎:MyISAM和InnoDB
-
-
-
索引:
-
说明:简单理解,就是一本书最前面的目录,虽然可以提高读取效率,单并非越多越好。
-
分类:
-
普通索引(index):最基本的索引
-
唯一索引(unique):修饰的字段不能重复,比字段email设置成唯一索引,那么两条用户的email就不能一样。
-
主键索引(primary key):是一种特殊的唯一索引,一张表中只能有一个字段设置
-
全文索引(fulltext):多全局数据添加索引。
-
-
- 示例
alter table user add index(em); # 给user表的em字段添加普通索引
alter table user add unique(username); # 给user表的username字段添加唯一索引
alter table user add primary key(id); # 将user表的id字段设置为主键索引
alter table user drop index em; # 删除user表的em字段的普通索引
alter table user drop unique username; # 删除user表的username字段的唯一索引
alter table user drop primary key id; # 删除user表的id字段的主键索引
完整的创建一个表
create table star(
id int auto_increment, //id字段,int类型,自增
name varchar(20) not null, //name字段,字符串类型,最大20字符,不能为空
money float not null, //money字段,浮点类型,不能为空
province varchar(20) default null, //省份字段,字符串类型,可以为空
age tinyint unsigned not null, //年龄字段,短整型,无符号,不能为空
sex tinyint not null, //性别字段,短整型,不能为空
primary key(id) //设置id字段为主键
)engine=innodb default charset=utf8; //设置引擎为ubbidb,设置字符集为utf8
插入数据
方式1:不指定字段,按照数据表的数据添加一条数据的全部字段(可以同时插入多条数据,每条数据为一个小括号,两条数据中间用,隔开)
insert into star values(1,'小明',20000,'山东',28,0);
方式2:指定字段,只需要传递指定字段的值,且前面的字段和后面values小括号的顺序要一致
insert into star(name,money,age,sex,province) values('小岳岳',4000000, 33, 0, '河南');
注意:自增字段、有默认值的字段、可以为空的字段可以不传字段值
一般插入数据用方式2,可以根据需要进行数据的插入
查询数据
select * from star; //查询star表所有数据
删除数据
delete from star where id=1; //where后的id=1是条件,表示把star表内id为1的一条数据删除
注意:删除操作后面一定要添加条件,否则容易误删整个表的数据
修改数据
update star set age=22,money=8000 where id=1
表名 设置 age字段,money字段 查询条件id=1
常用查询
select id,name,money from star; //查询star表内所有数据的id,name,money字段
select distinct name from star; //查询star表内所有数据的name字段的值,并且name字段的值不能重复,如:有两条数据的name字段值都为小明,第二条数据不显示。
select id,name,money from star where id > 4; //选取id大于4的数据的id,name,money字段
select id,name from star where id between 2 and 5; //等于id >=2 and id<=5,查询id字段在2~5的数据
select id,name from star where id in(2,4,6); //查询id为2,4,6的数据
select id,name from star where id not in(2,4,6); //查询id不为2,4,6的数据
select id,name from star where name like '小%'; //查询name字段的值为'小xxx'开头的数据
select id,name from star where name not like '小%'; //查询name字段的值不是'小xxx'开头的数据
指定条件查询:
-
条件:
符号 说明 > 大于 >= 大于等于 < 小于 <= 小于等于 = 等于 != 或 <> 不等于 and 并且 or 或者 [not] between m and n [不]在指定[m,n]的闭区间 [not] in [不]在指定的集合中 [not] like 模糊匹配,%表示任意字符
-
结果集排序:
-
示例:
select id,name,money from star order by money desc;
-
order by :指定排序字段
-
asc:升序,默认的
-
desc:降序
-
-
也可以多字段排序,先按照前面的字段排序,再按照后面字段排序
-
示例:
select id,name,money,age from star order by money desc,age asc;
-
-
-
限制结果集:
-
示例:
select id,name,money from star limit 3; # 取3条数据 select id,name,money from star limit 2,3; # 偏移2条,然后取3条数据 select id,name,money from star limit 3 offset 2; # 偏移2条,然后取3条数据
-
用途:分页显示,假设一页显示10条数据
第一页:limit 0, 10 第二页:limit 10, 10 第三页:limit 20, 10 page:表示页数,pageSize:表示一页的大小 查询条件:limit (page-1)*pageSize, pageSize
-
-
常用聚合函数
函数 功能 count 统计个数 sum 求和 max 最大值 min 最小值 avg 平均值 -
使用count时指定任何字段都行
-
使用其他函数时必须要指定字段
示例:
select count(*) from star; select max(money) as m from star;
as可以给字段其别名
-
-
分组操作
-
示例:
select * from star group by sex; # 分组 select count(*), sex from star group by sex; # 分组统计
-
-
结果集过滤:
-
示例:
select count(*) as c,province from star group by province having c>1;
搜索所有记录,然后按照省份分组,统计成员大于1的省份
-
数据控制语言(DCL)
-
创建用户:
-
格式:
create user '用户名'@'主机' identified by '密码'
-
示例:
create user 'jerry'@'%' identified by '123456'
-
-
授权用户:
-
格式:
grant 权限 privileges on 库.表 to '用户'@'主机' identified by '密码'
-
示例:
grant all privileges on test.* to 'jerry'@'%' identified by '123456'
-
-
查看授权:
-
格式:
show grants for '用户'@'主机'
-
示例:
show grants from 'jerry'@'%'
-
-
刷新权限:
flush privileges;
-
取消授权:
-
格式:
revoke 权限 privileges on 库.表 from '用户'@'主机';
-
示例:
revoke all privileges on test.* from 'jerry'@'%';
-
-
删除用户:
-
格式:
drop user '用户'@'主机'
-
示例:
drop user 'jerry@'%'
-
-
root用户远程登录:
-
sudo mysql_secure_installation
,根据提示配置,允许root用户远程登录 -
修改配置文件:
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
-
将bind-address=127.0.0.1,改为bind-address=0.0.0.0
-
-
给root添加授权主机,%表示所有主机
-
如:
grant all privileges on *.* to 'root'@'%' identified by '123456'
-
给root用户添加任意主机以123456作为密码登录,对所有的库下的所有表用于所有权限
-
-
多表联合查询
-
隐式内连接:没有出现join关键的连接
-
示例:
select username,name from user,goods where user.gid=goods.gid;
-
说明:查看用户买的商品名
-
-
显式内连接:会出现join关键字,后面的条件使用on
-
示例:
select username,name from user [inner/cross] join goods on user.gid=goods.gid;
-
说明:功能同上
-
join前可以加inner,也可以使用cross,也可以省略
-
-
外左连接:以左表为主
-
示例:
select username,name from user left [outer] join goods on user.gid=goods.gid;
-
说明:以左表为主,显示左边所有内容,右表不匹配的选项显示NULL
-
-
外右连接:以右表为主
-
说明:将left换成right即表示右连接
-