Mysql基本操作有这篇就够了

目录
一、数据库操作
二、用户授权
三、数据类型
四、表的字段约束
五、表的基本操作
六、数据的基本操作

一、数据库操作

连接数据库 mysql -u root -p mysql
退出：quit exit ctrl+d
显示数据库show databases;
修改数据库为utf8alter database jaho charset=utf8
use 库名;
select database(); 查看当前的数据库
创建数据库 create database jaho charset utf8;
删除数据库 drop database jaho
快捷键
- \G 格式化输出（文本式，竖立显示）
- \s 查看服务器端信息
- \c 结束命令输入操作
- \q 退出当前sql命令行模式
- \h 查看帮助

二、用户授权

格式：grant 允许操作 on 库名.表名 to 账号@来源 identified by '密码';

--实例：创建zhangsan账号，密码123，授权lamp61库下所有表的增/删/改/查数据,来源地不限
mysql> grant select,insert,update,delete on lamp61.* to zhangsan@'%' identified by '123';
mysql> grant all on . to zhangsan@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)

三、数据类型

MySQL的数据类型分为四大类：数值类型、字串类型、日期类型、NULL。

数值类型：

 tinyint(1字节) 0~255 -128~127  smallint(2字节)
 mediumint(3字节)          int(4字节)
 bigint(8字节)              float(4字节) float(6,2)
 double(8字节)              decimal(自定义)字串形数值

字串类型:

普通字串、char 定长字串 char(8)、varchar 可变字串 varchar(8)

二进制类型:

tinyblob、blob、mediumblob、longblob

文本类型:

tinytext text 常用于<textarea></textarea>

时间和日期类型：

date 年月日 time 时分秒 datetime 年月日时分秒 timestamp 时间戳 year 年

NULL值

NULL意味着“没有值”或“未知值”，可以测试某个值是否为NULL，不能对NULL值进行算术计算，-对NULL值进行算术运算，其结果还是NULL，0或NULL都意味着假，其余值都意味着真

四、表的字段约束

- unsigned 无符号(正数)
- zerofill 前导零填充
- auto_increment 自增
- default  默认值
- not null 非空
- PRIMARY KEY 主键 （非null并不重复）
- unique 唯一性 （可以为null但不重复）
- index 常规索引

五、表的基本操作

查看：
1. 查看表的创建语句（编码） show create database jaho;
2. 查看所有表 show tables
3. 表名\G --查看表的建表语句。 show create table \G
4. 查看表内容 desc 表名
5. 更改表名称：
  ALTER TABLE 旧表名 RENAME AS 新表名
6. 更改表类型：
  ALTER TABLE 旧表名 RENAME AS 新表名
7. 创建表
```
CREATE TABLE 表名(
      id int auto_increment primary  key
      columnname1 datatype contrai,
      columnname1 datatype ,
      primary key (1或多个)
）
```
修改：
- 修改重命名 alter table 表名 change 原名新名类型及约束
  alter table students change birthday datetime not null;
- 修改不重命名 alter table students modify 名类型约束
  alter table students modify birth date not null;
增加：
- alter table 表名 add 列名类型;
  alter table students add birthday datetime;
删除:
- 删除列： alter table 表名 drop 列名
  alter table students drop birth date
- 删除表:
  drop table students;

六、数据的基本操作

增：
- 数据插入（全部列）
  insert into students values(0,'jaho'..)
- 插入部分列
  insert into students (name,hometown) values('jaho','shwtao')
- 批量插入多行数据
  insert into students values(0,'jaho'..),(0,'jaho'..)
- 插入部分列多行
  insert into students (name) values('jaho')('red')
- 往一个表内插入另外一个表的数据不用values
  insert into goods_cates (name) select cate_name from goods group by cate_name;
- 创建外键
```
 create table study_record(
      id int auto_increment primary,
      day varchar(10) not null,
      stu_id int not null   # 创建外键与其他关联 必须要有
      foreign key(stu_id) references students(id)
)
```
删除：
delete from table where..
修改：
update table set col1 = val1,col2=val2 where 条件
查：
- 查全部
  select * from table
- 查非重复
  select distinct name,score from students;
- 偏移查询：从第二条数据开始查3行
select * from table limit 3 offset 2
- 分页: 每页显示m条数据,当前显示第n页求5第n页的数据
  
  select * from students where is_delete=0 limit(n-1)*m,n
- 模糊查询必须为字符串，%表示0到多个任意字符 , _表示一个任意字符
  select * form table where day like '2018-6%';
- 范围查询 in,非连续范围
  select * from students where id in(1,3,8);
- 排序
  select * from table order by day (asc,desc降序);
- 中文排序
  select * from table where convert(name using gbk) asc
- 标量子查询
```
select * from students where age > ( select avg(age) from students )`
select cate_name, price, id from goods where 
price > (select avg(price) from goods) order by price desc;
```
- 列级子查询
  select name from students where cls_id in (select id from classes);
- 查询每种类型中最贵的电脑信息把主表中的商品与副表,(即价格最贵的商品的名字与价格 )一起作交集
```
select * from goods inner join(select cate_name, max(price) as  max_price 
from goods group by cate_name) as good_info on goods.price =   
good_info.max_price and good_info.cate_name = goods.cate_name;
```
- between..and... 在连续的范围内
  select * from students where id between 3 and 8
- 空判断
  where xx is null / is not null
- 权重
  not > and >or
- 聚合函数 count(*),count(col) max(col) min(col) avg(col) round(avg(score),2) 保留2位小数
  select avg(score) from students
- 分组查询 group by+group_concat():统计某个字段的作为输出
  select sex,group_concat(name) from students group by sex;
- group by + 聚合函数
  - 某列的总数,分组统计,前后一致的name
    select name, count(*) from students group by name
  - 分组统计取别名,前后一致的name
    select name, count(*) as stu_num from students group by name
- with rollup 计算总数; coalesce(name,'总数') 给前面的取名字
  SELECT coalesce(name,'总数'),sum(socre) as '总分' from students group by name with rollup;
mysql 连接
1. 取别名
  select * from students as s left join classes as c on s.cls_id=c.id;
2. 左连接：左差集
  select * from a left join on a.a = b.b
3. 右连接：右差集
  select * from a right join on a.a = b.b
4. 内连接等值连接
  select * from a inner join b on a.a=b.b
5. 并集
  select * from a left join b on a.a=b.b union select * from a right join b on a.a=b.b;
6. mysql事务
  1. begin; 开启事务
  2. rollback 出现错误回滚之前的信息不保存
  3. commit 提交数据没提交之前数据存于内存中,执行之后保存到数据库里
  4. exit;
mysql 索引
1. 创建索引
  create index index_name on students(username(长度)) 必须加长度
  index index_name (name(10)) 创建表时添加
2. 删除索引
  drop index index_name on students;
3. 为user表中的name字段添加唯一性索引，索引名为uni_name
  alter table user add unique uni_name(name);
4. 为user表中的email字段添加普通索引，索引名为index_eamil
  alter table user add index index_email(email);
5. 将user表中index_email的索引删除
  alter table user drop index index_email;
导入和导出：
- 将lamp138库导出
  mysqldump -u root -p lamp138 >lamp138.sql; Enter password:
- 将lamp138库中的stu表导出
  mysqldump -u root -p lamp138 stu >lamp138_stu.sql Enter password:
- 将lamp138库导入
  mysql -u root -p lamp138<lamp138.sql Enter password:
- 将lamp138库中stu表导入
  mysql -u root -p lamp138<lamp138_stu.sql Enter password:

Mysql基本操作 有这篇就够了