Mysql基本操作 有这篇就够了

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

一、数据库操作

1. 连接数据库 mysql -u root -p mysql

2. 退出：quit exit ctrl+d

3. 显示数据库show databases;

4. 修改数据库为utf8alter database jaho charset=utf8

5. use 库名;

6. select database(); 查看当前的数据库

7. 创建数据库 create database jaho charset utf8;

8. 删除数据库 drop database jaho

9. 快捷键

   • \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。

1. 数值类型：

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

2. 字串类型:

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

3. 二进制类型:

tinyblob、blob、mediumblob、longblob

4. 文本类型:

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

5. 时间和日期类型：

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

6. NULL值

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

四、 表的字段约束

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

五、表的基本操作

1. 查看：

   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或多个)
   ）
   

2. 修改：

   • 修改重命名 alter table 表名 change 原名 新名 类型及约束
     alter table students change birthday datetime not null;

   • 修改不重命名 alter table students modify 名 类型 约束
     alter table students modify birth date not null;

3. 增加：

   • alter table 表名 add 列名 类型;
     alter table students add birthday datetime;

4. 删除:

   • 删除列： alter table 表名 drop 列名
     alter table students drop birth date

   • 删除表:
     drop table students;

六、数据的基本操作

1. 增：

   • 数据插入（全部列）
     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)
     )
     

2. 删除：
   delete from table where..

3. 修改：
   update table set col1 = val1,col2=val2 where 条件

4. 查：

   • 查全部
     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;

5. 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;

6. 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;

7. 导入和导出：

   • 将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: