mysql语句

数据库操作

        查看数据库

三种方法
    直接进入数据库查看
        show databases;
    以行的方式显示
        show databases \G
    在shell环境查看
        mysql -uroot -p123456 -e 'show databases'

        创建数据库

在文件系统中，MySQL的数据存储区以目录方式表示MySQL数据库。因此，上面命令中的数据库名字必须与操作系统的约束的目录名字一致。例如不允许文件和目录名中有\,/,:,*,?,”,<,>,|这些特殊符号，在MySQL数据库名字中这些字母会被自动删除。
 数据库的名字不能超过64个字符，包含特殊字符的名字或者是全部由数字或保留字组成的名字必须用反引号``括起来。
 数据库不能重名

语法
    create database 库名;

        删除数据库

drop database 库名

表的操作

        

创建表

create table 表名(字段名 类型, 字段名 类型)
    create table student(id int(20),name char(40),age int);

    查看表消息

show tables

查看表结构
    1. desc 表名
    2. explain 库名.表名;
    3. show fields from mysql.user;
查看创建表时执行了哪些命令
    show create table 表名
指定存储引擎和字符集

create table student2(id int(20),name char(40),age int) ENGINE=InnoDB DEFAULT CHARSET=utf8;
删除表

drop table 表名

修改表字段的操作 alter

修改表名字    rename
    alter table 表名 rename 新表名
    例子：alter table bb rename cc；
修改表中字段类型    modify
    alter table 表名 modify 要修改的字段名 要修改的字段类型
    例子：alter table bb modify id int(20)；
修改表中字段名称和字段类型 change
    alter table 表名 change 原字段名 新字段名 新字段类型
    例子：alter table bb change id uid int(20)；
表中添加字段 

添加    add
    alter table 表名 add 字段名 字段类型
    例子：alter table bb add name char(20);
第一行添加    first
        alter table 表名 add 字段名 字段类型 first
        例子：alter table bb add name char(10) first
在哪行后面添加    after
        alter table 表名 add 字段名 字段类型 after id     #在id字段后面添加
        例子：alter table bb add name char(10) after id;
删除表字段

alter table 表名 drop 字段名
例子： alter table bb drop id    #删除id字段


表中内容操作 insert into
    

插入
    insert into 表名 values(值1,值2)
    例子：insert into bb values(1,'zhangs',21);
插入多条
    insert into 表名 values(值1,值2),(值1,值2)
    例子：insert into bb values(2,'lis',24),(3,'wange',26);
指定插入的数据字段
        insert into 表名(字段1,字段2) values(值1,值2)   
        例子：insert into bb(id,name) values(4,'hangl');
查询表中内容 select

查询所有 \G以行显示
    select * from 表名
    例子：select * from bb \G
查询某个字段
    select name,age from 表名
    例子：select name,age from bb
查询其他数据库中表的内容
    select * from 库名.表名
    例子：select * from mysql.user;
删除表中内容 delete

删除某一行
    delete from 表名 where id=3   #删除id为三的行

修改表内容（更新记录）update

修改某一行    
    update 表名 set 修改内容 where 修改哪一行（条件）
    例子： update bb set age=20 where id=3    #将id为3的行 age内容修改为20
修改所有（不加条件）
    update 表名 set 修改内容
    例子：update bb set id=2     #将id全部改为2
更改多个字段值
    update 表名 set 修改值,修改值 where 条件
    例子： update bb set id=1,name="zs" where age=21    #将年龄为21的行，id改为1，名字改为zs

查询表内容

sql条件查询语句

去重查询    distinct
    select distinct name,age from 表名
    例子：select distinct name,age from bb
使用and和or查询
    select name,age from 表名 where age>10 and age<20    #查询年龄大于10 并且 年龄小于20 的内容
    例子：select id,name,age from bb where id>3 and age>25;
区分大小写查询 binary
    select name from 表名 where binary name="kill"  
查询排序    order by
    升序    asc
    select 字段1，字段2 from 表名order by 字段名 asc;        以哪个字段为排序  
    例子：select * from bb order by id asc：
    
    降序    desc
    select 字段1，字段2 from 表名 order by 字段名 desc;     以哪个字段为排序
    例子：select * from bb order by id desc;

mysql语句进阶

常用select命令

打印当前日期和时间
    select now()
打印当前日期
    select curdate()
打印当前的时间
        select curtime()
打印当前数据库
    select database()
打印mysql版本
    select version()
打印当前用户
    select user()
查看系统信息
    SHOW  [GLOBAL | SESSION]  VARIABLES  [like_or_where]
    例子
        
                1. show variables;
                2. show global variables;
                3. show global variables like '%version%';
                4. show variables like '%storage_engine%'; 默认的存储引擎
                5. like模糊搜索还可用户where字句
                        select * from students where name like 'li%'


导入导出数据库mysqldump

导出
    mysqldump -u root -p123456 HA>HA.sql
导入
    一
        mysql -uroot -p123456 HA2 < HA.sql
    二
        进入数据库
         source /root/HA.sql
扩展
Mysqldump -uroot -p123456 -B 库名>文件.sql
-B : 导出整个库包含建库语句 （接-B选项后不需要再创建数据库，导出时以将建库的sql语句导出）
-A：导出全部数据库

将select结果导出到文本
    语法：select * from HA.students into outfile '/tmp/123.txt';
查看secure-file-priv 这个选项是否为空
null 不允许导入导出
/tpm 只能在此目录中执行导入导出
  没值时，表示不限制mysql导入导出

修改方法
    编辑mysql主配置文件
    vim /etc/my.cnf
加入      secure_file_priv=''
重启 systemctl restart mysql

查询语句加深

判断条件查询

逻辑运算符    
    and         or        not
    and 且    or  或    not 非
    例子
            查询出书籍价格为（30,40,50,60）
            select bName,publishing,price from books where price=30 or price=40 or price=50 or price=60;
算数运算符   
    =     等于
    <>    不等于 !=
    >     大于
    <     小于
    >=    大于等于
    <=    小于等于
in运算符
    例子    WHERE column IN (value1,value2,...)
    not非   WHERE column NOT IN (value1,value2,...)
多字段排序
    例子    select bName,price from books where price  in (50,60,70) order by price desc,bName desc;
范围运算
        between  ... and ...
        例子：select bname,price from books where price between 30 and 60;
    也可以：select bname,price from books where price >=30 and price <=60;
模糊配查询
    例子： select bName from books where bName like '%程序%';
    查找不包括“程序”的数据
    例子：select bName from books where bName not like '%程序%';

子查询

嵌套查询
    嵌套查询就是把两个select语句结合起来
    例子：select bName,bTypeId from books where bTypeId=(select bTypeId from category where bTypeName='网络技术');


limit限定显示条目

语法：SELECT * FROM table LIMIT [offset,] rows;
                                  偏移量  行数
    例子：select * from category limit 3,1;
结合多个查询
    找出价格比电子工业出版社出版的书中最便宜还要便宜的书。
    例子：select bName,price from books where price<(select price from books where publishing="电子工业出版社" order by price asc limit 0,1);

另一种
    例子：select bName,price from books where price < all(select price from books where publishing="电子工业出版社");
    all表示子查询中返回全部值

多表连接查询

内连接
语法：select 字段  from 表1 inner join 表2  on 表1.字段=表2.字段
    例子：select bname,price,btypename from books inner join category on books.btypeid=category.btypeid;
实际使用中inner可省略掉，使用where子句代替（只限内连接使用）
    例子：select bname,price,btypename from books,category where books.btypeid=category.btypeid;
别名
    例子：select a.bname,a.price,b.btypename from books as a inner join category as b on a.btypeid=b.btypeid;
    as可以省略


外连接（左外连接）
    语法：select  字段 from a表 left join b表  on 连接条件
    例子： select a.bname,a.price,b.btypename from books a left join category b on a.btypeid=b.btypeid;
    以左边表为主表，指定的内容都显示


右外连接
    语法：select 字段 from a表 right join  b表 on 连接条件
    例子：select a.bname from books a right join category b on a.btypeid=b.btypeid;
    以右边表为主表，指定的内容都显示
更多表查询
    语法：select  字段 from a表 left join b表  on 连接条件 left join c表 on 连接条件 left join d表 on 连接条件
    例子： select bname from aa left jobs bb on aa.id=bb.id right join cc on bb.id=cc.id

聚合函数

算数运算函数
Sum()求和
    例子：select sum(price) from books;
    别名
        例子：select sum(price)  as 图书总价 from books;
avg()平均值
    例子：select avg(price) from books where bId<=3;
max() 最大值
    例子：select bName,price from books where price=(select max(price) from books);
min()最小值
    例子：select bName,price from books where price=(select min(price) from books);
count()统计记录数
    例子：select count(*) from books where price>40;

算数运算
    + - * /
给所有价格小于40元的书籍，涨价5元
    update books set price=price+5 where price<40;
给所有价格高于70元的书籍打8折
    update books set price=price*0.8 where price>70;

字符串函数
    substr(string ,start,len) 截取：从start开始，截取len长.start 从1开始算起
substr 截取字符串
    从1开始截取，截取到第7个字符
    select substr(bTypeName,1,7) from category where bTypeId=10;
   
     把多个字段拼成一个字段输出
    select concat(bName,"-----",publishing) from books;

大小写转换
    upper()大写 : 转为大写输出
    select upper(bname) from books where bId=9;
    lower()小写：转为小写输出
    select lower(bName) from books where bId=10;

mysql字段约束-索引-外键

字段修饰符（约束）

null和not null
    例子：create table test(col1 varchar(10) not null, col2 varchar(10) null)
default 默认值（为字段指定默认的值）
   例子： create table test2(name varchar(8) not null,dept varchar(25) default 'SOS');
auto_increment字段约束
    自动增长，只能修饰 int字段。 表明mysql应该自动为该字段生成一个唯一没有用过的数（每次在最大ID值的基础上加1。特例：如果目前最大ID是34，然后删除34，新添加的会是35.）
    例子：create table items ( id int not null auto_increment primary key);
清除自增长记录
    truncate
    例子：truncate table bb

索引

类型
    普通索引
    唯一性索引
    主键索引
    复合索引
普通索引
    创建
        方法一：create table bb(字段 类型,index 索引名称(字段))
        注意：index和 key 是相同的
        例子：create table demo( id int(4), pwd varchar(20), index(pwd) );
        例子：create table demo( id int(4), pwd varchar(20), key(pwd) );
        例子：create table demo2( id int(4), pwd varchar(20), key index_pwd(pwd) );  #加上名称
    查
        例子：desc bb;
        例子：show index[keys] from bb
    删除
        例子：alter table bb drop key index_pwd;
            注意此处的pwd指的是索引的名称,而不是表中pwd的那个字段
    修改（先删除在修改）
        语法：alter table 表名 add index 索引名称 （字段1，字段2.....）
        例子：alter table bb add key aa(pwd);
唯一性索引
    创建
        语法：create table bb(字段 类型,unique key 索引名称(字段))
        例子：create table test(id int(10),name varchar(10),unique key aa(id));
        例子：create table test(id int(10) unique key,name varchar(10));
    查
        例子：desc bb;
        例子：show index[keys] from bb
    删除
        例子：alter table demo drop key aa;
            注意此处的pwd指的是索引的名称,而不是表中pwd的那个字段
    修改（先删除在修改）
        语法：alter table 表名 add unique key 索引名称 （字段1，字段2.....）
        例子：alter table bb add unique key(pwd);
主键索引
    创建
        例子：create table demo5( id int(4) not null ,primary key(id));
        例子：create table demo5( id int(4) primary key, name varchar(20) default null);
    删除
        例子：alter table demo drop primary key;
    修改（先删除在修改）
        语法：alter table 表名 add primary key;
        例子：alter table bb add primary key(pwd);
复合索引
    创建
        例子：create table bb( host varchar(15),port smallint(4) not null, primary key (host,port));  #复合主键

外键约束

外键主要用来保证数据的引用完整性和一致性。通过级联更新与级联删除，减少无头数据的产生。
foreign key就是表与表之间的某种约定的关系，由于这种关系的存在，我们能够让表与表之间的数据，更加的完整，关连性更强
语法：create table 表名( 字段 字段类型,[constraint [外键名]] foreign key(字段1,字段2) references [主表名](主键字段1，主键字段2) on delete cascade on update cascade);
    注意：主表做外键的字段必须为主键
    注意: 创建多字段外键是，主表为复合索引。
    例子：CREATE TABLE bb(score DOUBLE, sid INT, constraint fk_aa_bb_sid foreign key(sid) references aa(sid) on delete cascade on update cascade);
    或 ：CREATE TABLE bb(score DOUBLE, sid INT, foreign key fk_aa_bb_sid(sid) references aa(sid) on delete cascade on update cascade);

通过alter table创建外键
    语法：alter table 表名 add [constraint [外键名]] foreign key(字段1,字段2) references [主表名](主键字段1，主键字段2) on delete cascade on update cascade);
    例子：alter table bb add foreign key kf_aa_bb_sid(sid) references aa(sid) on delete cascade on update cascade;
    
    或  ：alter table bb add constraint waijian foreign key(sid) references aa(sid) on delete cascade on update cascade;

删除外键
    语法：alter table 数据表名称 drop foreign key 约束（外键）名称
    例子：alter table order1 drop foreign key bk;    #删除名称为bk的外键

视图

视图就是一个存在于数据库中的虚拟表。是因为视图本身没有数据，只是通过执行相应的select语句完成获得相应的数据。
创建视图
    语法：create view 视图名 as select 语句
    例子：create view bc as select b.bName ,b.price ,c.bTypeName from books as b inner join category as c  on b.bTypeId=c.bTypeId ;
查看视图
    例子：show create view bc
    查看视图里内容： select * from bc
修改视图
    例子：alter view bc as select b.bName ,b.price ,b.publishing, c.bTypeName from books as b inner join category as c  on b.bTypeId=c.bTypeId ;
更新视图内容
    更新视图里也会改变原表内容
    例子：update bc set bName='HA' where price=49;  #更新视图
删除视图
    语法：drop view 视图
    例子：drop view bc;