Mysql终端Terminal操作

datebase管理

1.创建数据库-create

语法：create database 数据库名 character set 编码

# 注意：默认会存在四个数据库，其数据库中存储的是mysql数据库服务器的配置的数据

示例：create database firstDB character set utf8;

2.查看所有数据库-show

2.1.查看创建语句(也可看编码)

语法：show create database 数据库名;

2.2、查看数据库服务器中的所有数据库

语法：show databases;

3.修改数据库-alter

修改编码：
    语法：alter database 数据库名称 character set 编码;
    例如：alter database mydb_01 character set gbk;
    查看：show create database mydb_01;

4.使用数据库-use

语法：use 数据库名称;
例如：use mydb_01;

5.查看当前使用的数据库

语法：select database();
例如：select database();

6.删除数据库-drop

语法：drop dastabase 数据库名称;
例如：drop database mydb_01;

table管理

1.创建数据库表

语法

create table 表名称(
    字段名称 数据类型[约束],
    字段名称 数据类型[约束],
    ...
);

示例：

create table user(
    id int,
    name varchar(15),
    age int
);

数据库类型

常用的类型：
    int：整数型
    varchar：可变字符串
    double：浮点型，例如(double(5,2),长度为5，其中必须包含两位小数)
    date：日期(只有年月日)
    timestamp：时间戳(年月日，时分秒都有)
java和mysql类型对比
    mysql类型                                     java的类型
    int                                         java.lang.Integer
    double                                      java.lang.Double
    char                                        java.lang.String 
    varchar                                     java.lang.String 
    text                                        java.lang.String
    blob                                        java.lang.byte[]
    date                                        java.sql.Date
    time                                        java.sql.Time
    timestamp                                   java.sql.TimeStamp

约束

定义：
    约束就是对字段的某种性质的一种约束
类型：
    主键约束：primary key
    外键约束：(下节课学)
    非空约束：not null
    唯一性约束：unique
    默认值约束：default 默认值(指的是没有插入此字段时有默认值，当插入此字段时，即使值是null他也不会有默认值，即值为null)
主键约束：
    主键是每一条记录的唯一性标识，一般没有实际意义，特点：非空 唯一
    语法：字段名称 字段类型 primary key
    注意：如果主键是int型 我们可以将其定义成 auto_increment(自增长)
    示例：id int primary key auto_increment
例如：
    create table user(
        id int primary key auto_increment,
        username varchar(20) not null,
        password varchar(20) not null,
        gender varchar(20),
        age int default 25,
        email varchar(50) not null unique,
        salary double(8,2),
        state int default 0,
        role varchar(10) default 'VIP',
        registTime  timestamp
    );

2.查看表

2.1.查看当前数据库中的所有表

语法：show tables;

2.2.查看指定表的创建语句

语法：show create table 表名称;

2.3.查看表结构

语法：desc 表名称;

3.删除表

语法：drop table 表名称；

4.修改表

4.1.修改表名称

语法：rename table 旧名称 to 新名称；
例如：rename table user to newuser;

4.2.修改表的编码

语法：alter table 表名称 character set 编码；
例如：
　　alter table newuser character set gbk;
　　ALTER TABLE hq_message CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

5.新增字段

语法：alter table 表名称 add 字段名称 数据类型 [约束]；
例如：alter table newuser add idcart varchar(18) unique;

6.删除字段

语法：alter table 表名称 drop 字段名；
例如：alter table newuser drop idcart;

7.修改字段名称

语法：alter table 表名称 change 旧名称 新名称 数据类型
例如：alter table newuser change gender sex varchar(2);

8.修改数据类型

语法：alter table 表名称 modify 字段名称 新的数据类型;
例如：alter table newuser modify sex int;

数据操作

注意

select @@tx_isolation;--查看mysql的事物隔离级别
show variables like 'character%';--查看mysql数据库的当前编码
set character_set_client = gbk;--设置客户端编码
set character_set_results = gbk;--设置结果集的编码

1.插入数据

语法：insert into 表名(字段名1,字段名2,...字段名n) valuse(值1,值2,...值n);
注意：
    (1):如果主键是auto_increment的，那么，主键列的值可以写成null或者主键字段和主键值省略不写
    (2):如果插入的数据是全数据的话，那么字段名称可以省略
例如：
    insert into newuser(id,username,password,sex,age,email,salary,state,role,registtime) values(1,'zhangsan','123',1,25,'zs@itcast.cn',100,0,'teacher','2015-10-10 12:35:50');

2.修改数据

语句：update 表名称 set 字段名称1=值1,字段名称2=值2,....字段名称n=值n[where条件语句]；
注意：如果不写where条件子句的话 会把表中的所有相应字段的数据全部修改
例如：update newuser set password=111 where id=1;

3.删除数据

语法：delete from 表名 where条件句;
注意：如果不写where条件句的话 那么会把表中的数据全部删掉

delete from newuser和drop table user的区别？
    前者是删除数据，但是表的结构还在
    后者是把整个表全部删掉

truncate table newuser;和delete from newuser;区别？
    前者一次性将表摧毁，然后在新建表结构
    后者是一条一条的将数据删除

4.查询数据

4.1.全部查询

语法：select * from 表名;

4.2.查询部分数据

语法：select 字段名称1,字段名称2... from 表名称;

4.3.去重查询

语法：select distinct 字段名称 from 表名;

4.4.聚合函数

注意：聚合函数尽量不要和其他字段混合使用

4.41.计数函数

名称：count():统计当前表中有多少条数据
用法：select count(* 或 者字段名) from 表名;
注意：当使用字段进行统计时，如果该列中有null值，则不算一条记录

4.4.2.求和函数

名称：sum()
用法：select sum(salary) from 表名；
注意：如果要求和的字段中有null则把null当作0进行求和

4.4.3.最大值函数

名称：max()
用法：select max(salary) from user;
    
注意：null不作为数值比较

4.4.4.最小值函数

名称：min()
用法：select min(salary) from user;
注意：null不作为数值比较

4.4.5.平均值

名称：avg()
用法：select avg(salary) from user;
注意：如果字段中有null值，在相加时当作了0或者根本就没有算数，在相除的时候没有算此条记录

4.5.运算查询

语法：select username,salary+1000 from user;
注意：可进行数学运算
    null在进行运算的时候还是null

ifnull函数
    作用：判断是否为null
    语法：select username,ifnull(salary,0)+1000 from user;

as函数
    作用:给字段起别名
    语法：字段名 as 别名
    注意：as可以省略不写

4.6.排序查询

语法：order by 字段名;
注意：
    默认升序(asc)：order by 字段名 asc;
    降序(desc)：order by 字段名 desc;
例如：select * from user order by salary;

4.7.条件查询

4.7.1.单一条件查询

语法：select * from 表名 where 字段名称='字段值';       
注意：可以使用的运算符：>   <   >=    <=  !=
例如：select * from user where username='zhangsan';

 4.7.2.多条件查询

语法：select * from 表名 where 字段名1='字段值' and 字段名2='字段值'......;
注意：逻辑连接关键字可以使用and、or、not；优先级：and  > or
例如：
    select * from user where role='技术部' and sex='女';
    select * from user where role='teacher' or role='财务部';

4.7.3.范围查询

语法：select * from 表名 where 要查询的字段 between ... and ...
注意：即包左又包右

4.7.4.枚举查询 

语法：in(枚举字段);---满足枚举字段的就会被查出来

语法：not in(枚举字段);排除满足枚举字段的所有字段

4.7.5.模糊查询

语法：like '数据';
用法：
    select * from user where username like '张';---全名叫张的
    select * from user where username like '张%';---姓张的
    select * from user where username like '%张';---最后一个字是张的
    select * from user where username like '%张%';---包含张的
    select * from user where username like '_张%';---第二个字是张的
注意：%：代表多个任意字符    _代表任意一个字符

4.7.6.空/非空查询

语法：
    is null;---某字段为null
    is not null;---某字段不为null

4.8.分组查询

语法：select 字段 from 表名 group by 字段 [having(条件)] 表达式；

4.9.分页查询

limit 3,5

5.sql语句的书写顺序与执行顺序

书写顺序：select 字段 from 表名 where 条件 group by 字段 having 条件 order by 字段;
执行顺序：from   >   where   >   group by   >   having   >  select  >  order by

多表连查

1.准备数据

创建customer表
    id 整型 主键 自动增长，
    姓名 字符串20 不能为空，
    电话 字符串20 不能为空，
    等级 字符串20 默认为铜牌会员
    create table customer(
      id int primary key auto_increment,
      name varchar(20) not null,
      tel varchar(20) not null,
      grade varchar(20) default '铜牌会员'
    );
创建orders表
    id 整型 主键 自动增长，
    订单编号 字符串30 唯一 不能为空，
    订单提交时间 时间戳类型，
    客户id 整型 外键（与客户主键对应）
    create table orders(
        id int primary key auto_increment,
        ocode varchar(30) unique not null,
        createTime timestamp,
        cid int not null,
        constraint customer_orders foreign key orders(cid)   references customer(id)
    );
    customer表插入数据
        insert into customer values(null,'张三','13825545454','银牌会员');
        insert into customer values(null,'李四','13820145999','银牌会员');
        insert into customer values(null,'王五','17745589215','金牌会员');
    orders表插入数据
        insert into orders values(null,'jd001',null,1);
        insert into orders values(null,'jd002',null,2);
        insert into orders values(null,'jd003',null,1);
        insert into orders values(null,'jd004',null,2);

创建goods表
    id 整型 主键 自动增长，
    gcode 字符串20 唯一 非空，
    gname 字符串30 非空，
    gprice 浮点型（8，2） 非空，
    producttime 日期类型
    create table goods(
        id int primary key auto_increment,
        gcode varchar(20) unique not null,
        gname varchar(30) not null,
        gprice double(8,2) not null,
        producttime date
    );
    插入数据
        insert into goods values(null,'DQ0012','电视机',2999,'2014-12-25');
        insert into goods values(null,'DQ0013','洗衣机',1999,'2014-11-25');
        insert into goods values(null,'DQ0014','空调扇',235,'2014-5-21');
        insert into goods values(null,'SJ0012','iphone5',3288,'2014-10-27');
        insert into goods values(null,'SJ0013','魅族MX5',1799,'2015-4-11');
        insert into goods values(null,'RY0012','运动鞋',450,'2013-8-19');

创建orders_goods关系表
    id 整型 主键 自动增长，
    oid 整型 非空，
    gid 整型 非空
    create table orders_goods(
        id int primary key auto_increment,
        oid int not null,
        gid int not null,
        constraint orders_goods_rel foreign key orders_goods(oid) references orders(id),
        foreign key orders_goods(gid) references goods(id)
    );
    插入数据
        insert into orders_goods values(null,1,1);
        insert into orders_goods values(null,1,3);
        insert into orders_goods values(null,1,5);
        insert into orders_goods values(null,2,1);
        insert into orders_goods values(null,2,2);
        insert into orders_goods values(null,3,5);
        insert into orders_goods values(null,4,5);
        insert into orders_goods values(null,4,6);

1.1.内连接

内连接就是在查询的时候数据之间的必须一一对应才能查出

显示内连接
    语法：select 字段 from 表1 inner join 表2 on 条件
    例如：
        内连接需求：查询所有的用户的订单中的商品信息
        select * from customer c inner join orders o on c.id=o.cid inner join orders_goods o_g on o_g.oid=o.id inner join goods g on o_g.gid=g.id;
        查询客户表连接到订单表，条件是客户表中的主键id等于订单表中外键；因为订单表和商品表之间的关系是多对多，所以需要介入中间表，

隐式内连接
    语法：select 字段 from 表1，表2，... where 条件
    注意：一般情况下 n张表需要最少 n-1个条件可以保证数据不冗余
    例如：内连接需求：查询所有的用户的订单中的商品信息
        select * from customer c,orders o,orders_goods og,goods g where c.id=o.cid and o.id=og.oid and og.gid=g.id;

1.2.外连接

左外连接
    保证左表中的数据全部显示，右表中的数据仅仅显示与左表有关联的，没有数据的会显示null
    语法：select 字段 form 左表 left [outer] join 右表 on 条件
    例如：左外连接需求：查询所有的用户的信息，如果该用户有订单信息一起显示
        select * from customer c left join orders o on c.id=o.cid;

右外连接
    保证右表中的数据全部显示，左表中的数据仅仅显示与右表有关联的，没有数据会显示null
    语法：select 字段 from 左表 right [outer] join 右表 on 条件
    例如：有外连接需求：查询所有的商品信息，如果该商品有所属的订单则一起显示订单信息
        select * from orders o right join orders_goods og on o.id=og.oid right join goods g on og.gid=g.id;

1.3.子查询

查出的数据可以当作一个表看待

select * from customer where id=(select cid from orders where id=2);

数据库的备份与恢复

备份：
    mysqldump -u 用户名 -p 数据库名称  表名[表名]>磁盘路径   --回车
    password:*****     --回车      形成一个.sql的文件
恢复：
    mysql -u 用户名 -p 数据库名称(指定导入到那一个数据库)<磁盘路径   --回车
    password:*****   --回车
注意：
    如果指定的数据库不存在，则失败；需要自己先创建数据库

数据库中的权限、用户操作(DCL)

1.创建用户

语法：
    CREATE USER 用户名@地址 IDENTIFIED BY '密码';
    CREATE USER user1@localhost IDENTIFIED BY ‘123’;
    CREATE USER user2@’%’ IDENTIFIED BY ‘123’;
    user1用户只能访问localhost
    user2用户可以远程访问

2.给用户授权

语法：
    GRANT 权限1, … , 权限n ON 数据库.* TO 用户名@IP
    GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON mydb1.* TO user1@localhost;
    GRANT ALL ON mydb1.* TO user2@’%’;

3.撤销授权

语法：
　　REVOKE权限1, … , 权限n ON 数据库.* FORM 用户名
    REVOKE CREATE,ALTER,DROP ON mydb1.* FROM user1@localhost;

4.查看用户权限

语法：
    SHOW GRANTS FOR 用户名
    SHOW GRANTS FOR user1@localhost;

5.删除用户

语法：
    DROP USER 用户名
    DROP USER user1@localhost;

6.修改用户密码

语法：
    Use mysql;
    UPDATE USER SET PASSWORD=PASSWORD(‘密码’) WHERE User=’用户名’;
    FLUSH PRIVILEGES;
    UPDATE USER SET PASSWORD=PASSWORD('1234') WHERE User='user2';
    FLUSH PRIVILEGES;

视图管理

标注：

    不管那种创建视图的方式，新创建的视图中的数据会随着源表中的数据变化而变化   

创建数图：

1、同一数据库下

第一类：create view v as select * from table;
第二类：create view v as select id,name,age from table;
第三类：CREATE VIEW d_u as SELECT u.user_id as user_id, u.login_ip as login_ip, d.parent_id as parent_id FROM sys_dept d join sys_user u WHERE u.dept_id=d.dept_id; 
第四类：create view v as (select * from table1) union all (select * from table2);

2、不同数据库

这种情况只比上面的sql语句多一个数据库的名字，如下：
create view 数据库1.v as (select * from 数据库1.table1) union all (select * from 数据库2.table2);
或
create view 数据库2.v as (select * from 数据库1.table1) union all (select * from 数据库2.table2);
如果执行第一个sql将在数据库1下建立视图，反之亦然；

3、基于不同服务器的

这种情况稍微麻烦一点，需要先建立一个远程表来访问远程服务器的数据表，然后再对这个刚建立的远程表和本地表进行视图，步骤如下：

1、查看MySql是否支持federated引擎
    (1).登录Mysql;    
    (2).mysql>show engines;
    (3).如果显示为no,在配置文件中添加：federated (在my.ini)，重新启动mysql服务。

2、创建远程表
     CREATE TABLE federated_table (
     　　id  INT(20)  NOT NULL AUTO_INCREMENT,  
        name  VARCHAR(32) NOT NULL DEFAULT '',  
        other  INT(20) NOT NULL DEFAULT '0',   
        PRIMARY KEY  (id)
     )
     ENGINE=FEDERATEDDEFAULT
     CHARSET=utf8
     CONNECTION='mysql://fed_user@remote_host:9306/federated/test_table';

     CONNECTION可以按如下方式进行配置：
      (1).CONNECTION='mysql://username:password@hostname:port/database/tablename'
      (2).CONNECTION='mysql://username@hostname/database/tablename'
      (3).CONNECTION='mysql://username:password@hostname/database/tablename'

3、建立视图
    create view 本地数据库.v as (select * from 本地数据库.table1) union all (select * from 远程数据库.test_table);

数图的作用：

作用一：
    提高了重用性，就像一个函数。如果要频繁获取user的name和goods的name。就应该使用以下sql语言。
    示例：select a.name as username, b.name as goodsname from user as a, goods as b, ug as c where a.id=c.userid and c.goodsid=b.id;
    
    但有了视图就不一样了，创建视图other。
    示例:create view other as select a.name as username, b.name as goodsname from user as a, goods as b, ug as c where a.id=c.userid and c.goodsid=b.id;
    创建好视图后，就可以这样获取user的name和goods的name。
    示例：select * from other;
 
作用二：
    对数据库重构，却不影响程序的运行。
    假如因为某种需求，需要将user拆房表usera和表userb，
    该两张表的结构如下：
        测试表:usera有id，name，age字段
        测试表:userb有id，name，sex字段
    这时如果服务端使用sql语句：select * from user; 那就会提示该表不存在，这时该如何解决呢。
    解决方案：创建视图。
    以下sql语句创建视图：
        create view user as select a.name,a.age,b.sex from usera as a, userb as b where a.name=b.name;
        以上假设name都是唯一的。此时服务端使用sql语句：select * from user;就不会报错什么的。
        这就实现了更改数据库结构，不更改脚本程序的功能了。
 
作用三：
    提高了安全性能。
    可以对不同的用户，设定不同的视图。
    例如：某用户只能获取user表的name和age数据，不能获取sex数据。则可以这样创建视图。
    示例如下：
        create view other as select a.name, a.age from user as a;
    这样的话，使用sql语句：select * from other; 最多就只能获取name和age的数据，其他的数据就获取不到了。
 
作用四：
    让数据更加清晰。想要什么样的数据，就创建什么样的视图。经过以上三条作用的解析，这条作用应该很容易理解了吧

删除视图

mysql> DROP VIEW PEOPLE_VIEW;