MySQL必知必会——语句总结

来自《Mysql必知必会》总结与“Mysql官网”总结

Mysql使用:
图形界面
C/S模式使用Mysql:
    连接Mysql服务器：
        mysql -h hostname/host_IP   -p port             -u username -p 
               指定主机             指定端口，默认3306    登录的用户   输入密码
    退出：exit
————————————————————————————————————————————————————————————————————————————————————


数据库
show databases;
show table status from db_name \G
create database test;
use test;
drop database test;
-- create schema education authorization John;
-- drop schema education cascade/restrict;

show engines;
show variables like 'have_%';


————————————————————————————————————————————————————————————————————————————————————


表
show tables;
create table students;
create table students(
	id not null auto_increment,
	class_id int,
	average double not null,
	primary key(id),
	check (id between 10 and 20)
);
create table students select * from classes where id =1; 嵌套，复制表
alter table s add address carchar(30);	增加列
alter table s add primaty key(SC);		增加主键
alter table students change column origin_name new_name;
alter table s drop primaty key(SC);
alter table students drop column_name;
drop table students;
exit;


————————————————————————————————————————————————————————————————————————————————————


索引		非显示类型，查询时自动发挥作用
create unique/clustered index  s on studnets(id);
drop index s;

————————————————————————————————————————————————————————————————————————————————————

触发器
创建触发器：
create trigger trigger_name
    before/after insert/delete/update
        on table_name for each row
            insert into table_t2 values(1,"123");

例子：多操作触发器
    create trigger tri_d
        after insert
            on t_dept for each row
                begin
                    insert into t_diary2 values(NULL,"123");
                    insert into t_diary3 values(NULL,"123");
                end

查看触发器：show triggers;
    或者查看系统表：
            use information_schema;
            select * from triggers;

删除触发器：drop trigger trigger_name;

注：对一张表操作后，触发器只能对本表update.insert\delete\create只能对其他表，否则会造成循环触发。

————————————————————————————————————————————————————————————————————————————————————



视图(虚表) 
create view s(sn,sd,sa,g) 
as select * from students where sd='cs' 	创建全计算机系学生视图
with check option;
drop view s;
show tables;
alter view s as select name from students;
select * from s; 视图查询，最终仍转化为对基本表的查询
//来源于多个基本表，不可添加删除数据。
//来源于单个表，添加删除直接影响基本表
insert into s values(...);
delete fron s where name="asd";
update s set price=3.5 where name="asd";


————————————————————————————————————————————————————————————————————————————————————

查询:

1）单表查询：
    select id,name from student;
    select * from student where score>=60;
        //条件语句：四则运算，比较运算>/</=/!=，逻辑运算 and/or/xor/not/between
    select * from student where id in(1,3,5,7,9);
        //条件语句：is null , is not null , in , not in
    select name from student where name like'_a%';
        //like模糊查询，'_'表示一个字符，'%'表示任意个字符 ， like 和 not like
    select * from students order by id asc/desc;
    select * from students order by id asc,age desc;
        //排序，多排序（按照先后顺序，前者排完后者排）
    select distinct name from student; 
        //筛选出无重复的
    select * from student limit 5; 
        //选择数量为5
    select * from student limit 0,5;
        //从第一条开始选择，选5条,第一个参数从0开始表示第一条
    统计函数：count() avg() sum() max() min()
        select max(age) from students;
    select salary*2 from employee;	//加减乘除四则运算
    select concat(name,'的年薪为:',salary*12) year_salary from employee;
    select * from students group by class_id;
        //按照id分类，每个分组随机选出一条显示，意义不大
    select group_concat(name) from student group by class;
        //按分组，把同组名字一块显示
    select name from student group by class,age;
        //按照班级分组，同班按照年龄分组
    select depno,avg(sal) average from employee group by depno having avg(sal)>2000;
        //group by 配合having条件
2）多表查询
    union:并，两个字段相同的表合并，再去重
        select * from t1
        union
        select * from t2;
    笛卡尔积：m*n
    连接：
        自然连接：筛选出两表间某字段名相同&&字段值相等
                select * from t1 natural join t2;
        内连接：
            内连接基本与自然连接相同，不同之处在于自然连接的是同名属性列的连接，而内连接则不要求两属性列同名，可以用using或on来指定某两列字段相同的连接条件。
                select f1,f2,f3 from t1 inner join t2 on t1.id=t1.id;
                select f1,f2 from test t1 inner join test t2 on t1.id=t2.id2;
                    //自连接
            不等连接：筛选出笛卡尔积中某两字段值不相等
                select f1,f2,f3 from t1 inner join t2 on t1.age>=t2.age;
        外连接：必须要用using或者on
            左外连接：相同属性名字段值右表等于左表或者右表中为null
                t1 left join t2 on...
            右外连接：相同属性名字段值左表等于右表或者左表中为null
                t1 right join t2 on...
            全外连接：相同属性名字段值两表相等或者左表/右表为null
                full outer join
        交叉连接：
    嵌套查询（子查询）：any / all / exists / not exists
        select * from any (select * from t);

————————————————————————————————————————————————————————————————————————————————————

运算符：

+ - * / %
> >= = != < <=
between .. and ..
is null
in 
    where id in (1,2,3);
like
    where name like "xiao%" ;
regexp
    正则表达式
and or not xor
& | ~ ^ << >> 
    位运算

————————————————————————————————————————————————————————————————————————————————————

常用函数：

    字符串函数：
        concat(s1,s2,s3,..,sn) //连接字符串
        concat_ws('-',s1,s2,...,sn)    //用'-'连接字符串
        insert(str,x,y,str2)   //str第x位置长为y的子串替换为str2，字符串从1开始
        lower(str)             //str全变为小写
        upper(str)             //str全变为大写
        left(str,x)            //str最左边x个字符
        right(str,x)           //str最右边x个字符
        lpad(str,n,str2)       //用str2在str左边扩充，直到长度为n
        rpad(str,n,str2)       //用str2在str右边扩充，直到长度为n
        ltrim(str)             //去掉左边空格
        rtrim(str)             //去掉右边空格
        trim(str)              //去掉首尾空格
        repeat(str,x)          //str重复x次
        replace(str,a,b)       //str中字符串a全部替换为字符串b
        strcmp(str1,str2)      //比较str1 str2,s1>s2返回1，s1=s2返回0，s1<s2返回-1
        substring(str,x,y)     //返回str从x开始长度为y的字符串，字符串从1开始
        length(str)            //返回str长度
        char_length(str)       //返回str所占字节数，中文一个字占两个字节
        locate(str1,str)       //返回str1在str中的开始位置

    数值函数：
        abs(x)
        ceil(x)         //向上取整
        floor(x)        //向下取整
        mod(x,y)        //返回x%y
        rand()          //返回0~1内的随机数
        round(x,y)      //返回x四舍五入后有y个小数
        truncate(x,y)   //返回x截断为y位小数
            truncate(903.53567,2)  ---> 903.53
            truncate(903.53567,-1) ---> 900

    时间/日期函数
        curdate()        //返回年月日
        curtime()        //返回小时分钟秒
        now()            //返回年月日时分秒
        week(now())      //返回第几周
        year(now()) hour(now()) minute(now()) monthname(now()) 
        weekday(now())   //返回0~6 周一到周日
        adddate(now(),interval 3 year) //3年后的日期
            year , month , day , hour , minute , second
        subdate(now(),interval '1,2' day_hour) //1天2小时前的日期
        datediff(now(),'2012-12-01') //相隔天数

    系统类函数：
        version()
        database()
        user()
        last_insert_id()

————————————————————————————————————————————————————————————————————————————————————


数据更新:

插入：
    insert into students (class_id,name) values(2,'mike'),(3,'jack'); //可以指定字段插入
    插入查询结果：insert 和 select 嵌套
更新：
    update students set name='mike',score=66 where id=1;
    update students set score=score+10 where score<60;
删除：
    delete from students where id=1 or score<60;
    truncate table; //删除表所有行，成为空表




————————————————————————————————————————————————————————————————————————————————————


数据控制
grant select/insert/update/delete/alter/create index/create table/all privileges on table s to user;
grant all privileges on s,sc to user2;

revoke all privileges/select/... on tale s from user/public;


————————————————————————————————————————————————————————————————————————————————————


事务数据库:innoDB引擎支持
    begin;//开始
    update test set name="a" where id=1;
    rollback;//回滚，撤销上一条更新
    commit;//结束
    
    隔离级别：
    set transaction isolation level read uncommitted; 
        //未提交可读，事务A更新还未提交时，事务B即可读
    set transaction isolation level read committed;
        //提交读，事务A更新并提交后，事务B可读，但同一事务B中读的前后不一样，即不可重复读。
    set transaction isolation level repeatable read;
        //可重复读，默认事务隔离级别
        //InnoDB含有隐藏值，每行的创建/过期时间、存储时的版本号
        //每有新事务版本号+1，每个查询根据版本号查询
    set transaction isolation level serializable;
        //可串行化，不推荐

    begin;
    update students set name='bob' where id=1;
    rollback;		脏读：其他mysql服务器易产生脏数据

    set transaction isolation level read committed;
    begin;
    update students set name='bob' where id=1;
    commit;			不可重复读：同一事务中读的两次不同

    set transaction isolation level repeatable read;
    幻读：原本为空一个服务器插入了一条，而另一服务器查询不到（状态等同初始时），但可以更新此条。
    alter table, truncate table, drop table等部分DDL语言会隐式提交事务

    InnoDB锁机制：
        共享锁（对行/元组加锁）：多个事务可获得
        排他锁（对行/元组枷锁）：仅一个事务可获得，对数据进行操作
        意向锁（对表）：分为意向共享锁、意向排他锁
    InnoDB锁为间隙锁


————————————————————————————————————————————————————————————————————————————————————

用户操作

    mysql -u root -ppassword  
        //登录root用户
    create user 'wesley'@'localhost' identified by '123'  
        //创建名字为welsey，密码为123的用户
    grant select,insert,delete on database.tablename to 'wesley'@'localhost' identified by '123';
    grant create,drop on *.* to 'test'@'localhost' identified by '12345';
    mysqladmin -u root -p old_password "new_password"
        //修改root密码
    set password for 'wesley'@'localhost'=password("123456";
        //root下修改普通用户密码
    drop user 'wesley'@'localhost';
    


————————————————————————————————————————————————————————————————————————————————————

日志文件：
    二进制日志：二进制形式记录数据库各种操作，不记录查询语句
        启动
            设置配置文件mysql.ini,默认每次启动服务器产生一个filename.number日志文件，
            每次启动number自动增加，产生新日志文件
            [mysqld]
            log-bin
        mysqlbinlog filename.number //查看二进制日志文件
        set sql_log_bin=0    //暂停日志功能
        set sql_log_bin=1    //重启日志功能
        reset master;    //删除所有日志文件
        purge master logs to filename.number;    //删除编号小于number的日志文件
        purge master logs before 'yyyy-mm-dd hh:MM:ss'    //删除指定时间之前的日志文件

    错误日志：记录错误
        my.ini配置文件,默认保存到数据文件中，localhost.error文件
            [musqld]
            error-bin
        删除日志
            mysqladmin -u root -p flush-logs

    通用查询日志：记录mysql服务器启动关闭、客户端连接、更新、查询记录
    慢查询日志：记录执行时间超过指定时间的操作
        启动：自动生成filename-slow.log
            [mysqld]
            log-slow-queries
            long_query_time=n
        分析：
            mysqldumpslow.pl -s at -t 5
            //mysqldumpslow.pl分析慢查询日志，-s排序参数，at平均查询时间，-t 符合条件的行数
        删除日志
            mysqladmin -u root -p flush-logs



————————————————————————————————————————————————————————————————————————————————————

数据备份：
    mysqldump -u username -p dbname table1 table2 > D:/backup.sql
    //把username用户的dbname数据库的表1表2备份到backup.sql ，无表参数就备份整个数据库
    mysqldump -u username -p --databases db1 db2 >backup.sql
    //备份多个数据库

数据还原：
    mysqldump -u username -p dbname < backup.sql
    //还原指定数据库，若不指定dbname则还原所有数据库
    
导出到文本文件：
    select * from table into outfile 'D:/file_name.txt'; //不指定格式
    select * from table into outfile 'D:/file_name.txt'    
    fields terminated by '\、'
    optionally enclosed by '\"';
    lines starting by '\>'
    terminated by '\n';
    //指定格式导出到文本，
      字段用、分开，每行用\n分开，每行开头> ， 每个值用""括起来

文本导入：
    load data infile 'D:/file_name.txt'
    fields terminated by '\、'
    optionally enclosed by '\"';
    lines starting by '\>'
    terminated by '\n';


————————————————————————————————————————————————————————————————————————————————————

同其他语言结合
JDBC