day17-mysql相关指令

1、DDL

常用数据类型

• 创建表
  create table user(username varchar(30), password char(32));
• 查看表结构
  desc user;
• 查看表创建语句
  show create table user;
• 删除表
  drop table user;
• 创建表指定字符集和引擎
  create table user(username varchar(30), password char(32)) engine=innodb default charset=utf8;
• 修改字段类型
  alter table user modify username varchar(20);
• 添加字段
  alter table user add email varchar(30)
  alter table user add email varchar(30) after username;

增删改查 curd操作

首先通过mysql -uroot -p 进入数据库系统，创建数据库dudu，进入数据库dudu use dudu，然后才是创建表的过程
所有的符号都是英文。

• 创建表开始学习增删改

        mysql> create table user(
            -> id int auto_increment,
            -> name varchar(30) not null,
            -> money int not null,
            -> province varchar(20) default null,
            -> age tinyint unsigned not null,
            -> sex enum('女', '男') not null,
            -> primary key(id)
            -> )engine=innodb default charset=utf8;

【注】单选在插入值的时候，可以直接使用1 2，那么1代表女，2代表男，enum类型如不不给值，默认是第一个

• set类型如何插入
  set('吃', '喝', '嫖', '赌', '抽')
  1 2 4 8 16
  4|8|16 如果是后三个，可以这么写
  28 也可以加起来，这么写

2、DML

增删改语句

3、可视化工具

当你去操作数据库的时候，其实有3中方式
（1）使用mysql自带的客户端进行操作
（2）使用一些第三方的可视化工具管理数据库 比如navicate、sqlyog
（3）使用代码去操作数据库，比如python、java、php

4、DQL

• query ： 查询
  模糊查询：like '%德%' 只要有 德 字的都符合要求
  % 代表任意多的字符
  like '柳_' 叫 柳某 的都符合要求
  _ 代表一个任意字符

• limit：
  limit 2 : 在结果集中只要前两个
  limit offset, number : offset代表偏移量，number代表数量
  LIMIT number1 OFFSET number2 : number1 是数量 number2 是偏移量

  上网的时候，经常会有分页，每页显示10条，
  第一页：select * from table limit 0, 10
  第二页：select * from table limit 10, 10
  第三页：select * from table limit 20, 10
  第n页：select * from table limit (n-1)*10, 10

• 分组：
  字段只能出现分组字段和统计信息，其它的字段出现没有意义
  select province, count() from user group by province;
  select province, count() as c from user group by province having c>=2;
  【注】having经常跟在group by的后面，where是跟在表的后面
  select使用顺序
  SELECT xxx FROM xxx WHERE xxx GROUP BY xxx HAVING xxx ORDER BY xxx LIMIT xxx;
  取出拥有年龄大于30岁的明星个数大于两个且第二多的省份

• 多表联合

        mysql> create table user(
            -> id int auto_increment,
            -> name varchar(30) not null,
            -> gid varchar(10) default 0,
            -> primary key(id)
            -> )engine=innodb default charset=utf8;

mysql> create table goods(
            -> gid int auto_increment,
            -> name varchar(30) not null,
            -> price int not null,
            -> category varchar(20) not null,
            -> primary key(gid)
            -> )engine=innodb default charset=utf8;

        mysql> insert into user(name, gid) values('郭德纲', 1),
            -> ('岳云鹏', 2),
            -> ('曹云金', 0),
            -> ('于谦', 3),
            -> ('牛群', 1),
            -> ('冯巩', 1),
            -> ('大兵', 4),
            -> ('马三立', 0),
            -> ('贾玲', 2);

• 隐式内连接
  select user.name as uname, goods.name as gname from user, goods where user.gid=goods.gid;
• 显示内连接
  select * from user as u join goods as g on u.gid=g.gid;
  select u.name as uname, g.name as gname from user as u join goods as g on u.gid=g.gid;
• 三表链接格式
  select * from user as u join goods as g on u.gid=g.gid join price as p on g.pid=p.pid

指令汇总

一、什么是SQL
    SQL(Structured Query Language)结构化查询语言的缩写，是一种特殊目的的编程语言，
是一种数据库查询和程序设计语言，用于存储数据以及查询、更新和管理关系型数据库系统；
同时也是数据库脚本文件的扩展名。
二、SQL分类
    数据定义语言(DDL)，例如：CREATE、DROP、ALTER等语句。

    数据操作语言(DML)，例如：INSERT、UPDATE、DELETE。

    数据查询语言(DQL)，例如：SELECT语句。
    
    数据控制语言(DCL)，例如：GRANT、REVOKE。

    数据事务语言(DTL)，例如：BEGIN、COMMIT、ROLLBACK等语句。
三、DDL
1、连接数据库
mysql -h服务器地址 -u用户名 -p密码
基本语法
a.查看库  show databases
b.创建库  create database 数据库名
c.删除库  drop database 数据库名
d.选中库  use  数据库名
e.查看表  show tables
f.创建表  create table(字段1 字段类型，字段名2 字段类型2) 表名
g.查看表字段  desc 表名
h.删除表  drop table 表名
i.指定字符集和引擎
1.存储引擎 engine=InnoDB
2.创建表时指定字符集default charset=utf8
j.修改表字段类型
alter table 表名 modify 字段名 类型名
k.增加表字段
alter table 表名 add 字段名 类型名
l.增加字段时控制顺序
alter table 表名 add 字段名 字段类型 after 字段名2
alter table 表名 add 字段名 字段类型 first
m.删除表字段
alter table 表名 drop 字段名
n.表字段改名
alter table 表名 change 字段原名 字段新名 字段类型
o.修改表名
alter table 旧表名 rename 新表名
四、MDL
操作前先建立表
create table user(
    id int auto_increment,
    name varchar(50) not null,
    money float not null , 
    province varchar(20) default null , 
    age tinyint  unsigned not null , 
    sex tinyint not null , 
    primary key (id)
    ) engine= InnoDB default charset=utf8;
a.插入记录1
insert into 表 values(值1,值2,值n)
插入记录2
insert into 表(字段1,字段2,字段n) values(值1,值2,值n)
插入数据3
insert into star(name, money, province, age, sex)
        values('黄晓明', 2000000,  '山东',  39, 0),
        ( 'angelababy', 3000000, '上海', 27, 1),
        ( '陈赫', 150000, '福建', 31, 0);
删除记录   delete from 表 where 条件
修改记录   update 表 set 字段1=值1,字段2=值2 where 条件
基础查询   select * from 表
五、DQL
指定字段查询   select 字段 from 表名
指定字段组合不重复记录   select distinct 字段 from 表
条件查询   select 字段 from where 条件查询
where后可接的条件
> < >= <= !=/<> or and between and in/not in  like(模糊查询) 
结果集排序   select 字段 from 表 order by 字段 排序关键词(asc升序 默认升序，desc降序)
多字段排序   select 字段 from 表 order by 字段1 desc|asc,字段n desc|asc
限制查询的结果集   select 字段 from 表 limit 数量
限制排序后的结果集   select 字段 from 表 order by 字段 desc|asc limit 数量
结果集区间选择   select 字段 from 表 limit 偏移量，数量
用limit分页   第1页为 limit 0,10 第2页为 limit 10,10第3页为 limit 20,10
依此类推... ...
常用统计函数
sum   求和
count   统计整数
max    最大值
min    最小值
avg    平均值
统计函数使用   select 函数(字段) from 表
分组   select * from 表 group by 字段
分组统计   select * from 表 group by 字段  
结果集过滤   select * from 表 group by 字段 having 条件
整体使用SQL
顺序   select from where group by  order by limit
 SELECT xxx FROM xxx WHERE xxx GROUP BY xxx HAVING xxx ORDER BY xxx LIMIT xxx;
 多表联合查询
 隐式内连接
 select 表1.字段 [as 别名],表n.字段 from 表1[别名],表n where 条件
 select username,  name from user, goods where user.gid=goods.gid;
 显式外连接
 select 表1.字段[as 别名],表n.字段 from 表1 inner join 表2 on条件
 select username,  name from user inner join goods on user.gid=goods.gid
外连接之左连接
select 表1.字段 [as 别名], 表n.字段 from 表1 left join 表n on 条件
外连接之右连接
select 表1.字段 [as 别名], 表n.字段 from 表1 right join 表n on 条件
子(嵌套)查询
select 字段 from 表 where 字段 in(条件)
记录联合
select 语句1 union[all] select 语句2
修改更新
update 表名 set 字段1=值1, …, 字段n=值n where 条件
两个表同时更新
update 表1,表2 set 字段1=值1, …, 字段n=值n where 条件
清空表记录
truncate table 表名
创建用户
Create user ‘用户名’@‘用户地址’ identified by ‘密码’
删除用户
drop user ‘用户名’@‘用户地址’
授予权限
grant 权限 on *.* to ‘用户名’@‘用户地址’
剥夺权限
revoke 权限 on *.*  from ‘用户名’@‘用户地址’