MySQl之最全且必会的sql语句

￡神魔★判官ぃ 2022-06-17 04:27 108阅读 0赞

原文链接：[http://blog.csdn.net/qq\_32059827/article/details/51763950][http_blog.csdn.net_qq_32059827_article_details_51763950]

创建一个名称为mydb1的数据库，如果有mydb1数据库则直接使用，如果无则创建mydb1数据库  
    create database if not exists mydb1;  
    create database if not exists mydb1;  
    
    创建一个使用UTF8字符集的mydb2数据库，注意这里不是UTF-8  
    create database if not exists mydb2 character set UTF8;  
    create database if not exists mydb2 character set UTF8;  
    
    创建一个使用UTF8字符集，并带校对规则的mydb3数据库  
    create database if not exists mydb3 character set UTF8 collate utf8_general_ci;  
    
    校对规则：是数据库表中的所有记录按什么方式存储数据的先后顺序，例如：a在前,z在后  
    字符集与校对规则是一一对应，不能乱改  
    如果不写校对规则的话，默认是[对应字符集]默认的校对规则。参考<<MySQL5手册--10.10.10>>  
    
    查看当前数据库服务器中的所有数据库  
    show databases;  
    
    查看前面创建的mydb1数据库的定义信息  
    show create database mydb1;  
    show create database mydb1;  
    
    删除前面创建的mydb1数据库，如果有mydb1则删除  
    drop database if exists mydb1;  
    drop database if exists mydb1;  
    
    使用mydb2数据库  
    use mydb2;  
    use mydb2;  
    
    查看数据库服务器中的数据库，并把其中mydb3库的字符集修改为GBK  
    alter database mydb3 character set GBK;  
    alter database mydb3 character set GBK;  
    
    以下代码是在mydb2数据库中创建users表，并插入几条记录，学员们暂时不用理会  
    create table if not exists users(  
        name varchar(10)  
    );  
    insert into users values('XX');  
    insert into users values('YY');  
    insert into users values('ZZ');  
    
    备份mydb1库中的数据到e:/xx.sql的文件中，以便将来恢复之用  
    window7（先exit到windows环境）备份：mysqldump -uroot -p mydb1 > e:\mydb1.sql回车  
                                        mysqldump -uroot -p mydb1 > d:/myydb1.sql  
    mysqldump是mysql提供的用于备份数据库的命令  
    mysqldump命令必须window环境运行  
    source命令中mysql环境运行  
    
    mysql恢复：source e:\mydb1.sql回车  
    注意：恢复时，[先创建数据库]并使用该数据库，[再]通过source命令，因为sql文件中[只有]表信息，[无]数据库信息  
    
    创建一个users表，包含id/username/password/birthday/salary  
    create table if not exists users(  
        id int(4),  
        username varchar(10),  
        password varchar(6),  
        birthday datetime,  
        salary float(6,2)  
    );  
    
    
    float(6,2)表示：2表示显示时小数点后最多几位，超过2位，四舍五入;  
                    6表示整数和小数最多几位，整数部份最多(6-2)位  
    以上表中的所有字段都允许为NULL，且默认NULL  
    如果数据显示出来是乱码，按如下步骤：  
    
    向users表中插入一条记录，先英后中(无法插入中文)  
    insert into users values(1,'jack','123456','2015-8-8 8:8:8',9999.99);  
    insert into users values(2,'哈哈','123456','2015-9-9 9:9:9',9999.99);  
    insert into users values(3,'呵呵','123456','2015-7-7 7:7:7',7777.77);  
    
    查询users表的结构  
    desc users;  
    
    创建employees表------------------------------用employees.sql表  
    
    在上面员工表的基本上增加一个image列  
    alter table employees  
    add image blob;  
    alter table employees add image blob;  
    
    企业中，不是真真正正存照片本身，而是存照片路径，即E:/zgl.jpg  
    
    修改name列，使其长度为60  
    alter table employees  
    modify name varchar(60);  
    
    alter table employees  
    modify name varchar(60);  
    
    删除image列  
    alter table employees  
    drop column image;  
    
    alter table employees  
    drop column image;  
    如果表中有内容的话，删除或添加某列，不影响其它列的信息  
    
    表名employees改为staff  
    rename table employees to staff;  
    rename table employees to staff;  
    
    修改表的字符集为GBK  
    alter table staff  
    character set UTF8/GBK/GB2312;  
    
    列名name修改为username  
    alter table staff  
    change column name username varchar(60);  
    alter table staff  
    change column name username varchar(60);  
    
    向staff表中插入数据  
    insert into staff values(3,'赵君','男','2005-1-1',3333.33,'2005-7-1','这是备注信息');  
    
    显示插入NULL  
    insert into employees values(1,'哈哈','男','2015-1-1',1111.11,'2015-5-5','这是备注信息',NULL);  
    
    隐式插入NULL  
    insert into employees(id,name,sex,birthday,salary,resume)   
    values(2,'呵呵','男','2015-1-1',2222.22,'这是备注信息');  
    
    如果存在表就删除表  
    drop table if exists staff;//表不在了 
    drop table if exists staff;  
    
    truncate table users;//表还在，只不过没有内容了 
    
    再重新创建表  
    create table staff(  
      id int(5),  
      name varchar(6),  
      sal int(5)  
    );  
    insert into staff values(1,'哈哈',7000);  
    insert into staff values(2,'呵呵',8000);  
    insert into staff values(3,'嘻嘻',9000);  
    insert into staff values(4,'明明',10000);  
    insert into staff(id,name,sal) values(4,'星星',6000);  
    insert into staff(name,id,sal) values('月月',5,6000);  
    ----------------------------------------------------------------------------------------------------------------  
    将所有员工薪水修改为10000元  
    update staff set sal=10000;  
    update staff set sal=10000;  
    
    以上就是SQL语句的威力，如果发送命令，MySQL数据库服务器自行会解析其命令，并做相对应的过程处理，这个  
    过程处理对程序员来讲，是封闭的，看不见。  
    SQL的全称【结构化查询语句】，第四代计算机语言  
    第一代：机器语言，即01010010100100101  
    第二代：汇编语言，即用一个代号去表示10101010这些数字  
    第三代：高级语言，即c/c++/vb/java/c#/... 
    第四代：命令语言，即SQL  
    第五代：智能语言，。。。  
    
    将姓名为'哈哈'的员工薪水修改为11000元  
    update staff set sal=11000 where name = '哈哈';  
    update staff set sal=11000 where name = '哈哈';  
    
    将月月的薪水在原有基础上增加1000元  
    update staff set sal = sal + 1000 where name = '月月';  
    update staff set sal = sal+1000 where name = '月月';  
    
    删除表中3号的记录  
    delete from staff where id = 3;  
    delete from staff where id = 3;  
    
    删除表中所有记录  
    delete from staff;  
    delete它是一行一行删除，速慢较【慢 】          drop table staff;整个表都被删除了  
    
    使用truncate删除表中记录  
    truncate它是整表删除后再重构，速慢较【快】。但是它的表结构还在，知识表内容没了  
    ---------------------------------------------------------------------------------------------------------------  
    students表  
    use mydb1;  
    drop table if exists students;  
    create table if not exists students(  
        id int(5),  
        name varchar(20),  
        chinese int(5),  
        english int(5),  
        math int(5)  
    );  
    
    insert into students(id,name,chinese,english,math) values(1,'张小明',89,78,90);  
    insert into students(id,name,chinese,english,math) values(2,'李进',67,98,56);  
    insert into students(id,name,chinese,english,math) values(3,'王五',87,78,77);  
    insert into students(id,name,chinese,english,math) values(4,'李一',88,98,90);  
    insert into students(id,name,chinese,english,math) values(5,'李来财',82,84,67);  
    insert into students(id,name,chinese,english,math) values(6,'张进宝',55,85,45);  
    insert into students(id,name,chinese,english,math) values(7,'黄蓉',85,75,80);  
    insert into students(id,name,chinese,english,math) values(8,'张一李',75,65,30);  
    insert into students(id,name,chinese,english,math) values(9,'何李',75,65,30);  
    insert into students(id,name,chinese,english,math) values(10,'单',75,65,30);  
    insert into students(id,name,chinese,english,math) values(11,'李',75,65,NULL);  
    insert into students(id,name,chinese,english,math) values(12,'jack',75,65,40);  
    insert into students(id,name,chinese,english,math) values(13,'marry',75,65,60);  
    
    查询表中所有学生的信息，*表示所有字段，顺序与表结构相同------------------------------用students.sql表  
    select id,name,chinese,math,english from students;  
    select name,id,chinese,math,english from students;  
    select name,chinese,math,english,id from students;  
    select * from students;  
    *号虽然写好起来方便，但充满不确定因素，要慎用  
    
    查询表中所有学生的姓名和对应的英语成绩  
    select name,english from students;  
    select name,english from students;  
    
    过滤表中重复语文成绩distinct（区别的）放在单列名前面，可以对该列名重复你元素进行筛选，仅仅保留一个  
    select distinct chinese from students;  
    select distinct chinese from students;  
    
    在所有学生分数上加10分特长分  
    select name,chinese+10,math+10,english+10 from students;  
    select name,chinese+10,math+10,english+10 from students;  
    NULL与任何数值进行运算，结果为NULL  
    
    统计每个学生的总分  
    select name,chinese+math+english from students;  
    select name,chinese+math+english from students;  
    
    **使用(别名)表示学生分数，注意使用别名加上双引号  
    select name "姓名",chinese+math+english "总分" from students;  
    select name "姓名",chinese+math+english "总分" from students;  
    
    查询姓名为'张小明'的学生成绩    注意字符串用单引号'' 条件用where  
    select id,name,chinese,math,english from students where name = '张小明';  
    select * from students where name = '张小明';  
    
    查询英语成绩大于90分的同学  
    select id,name,chinese,math,english   
    from students   
    where english > 90;  
    select * from students where english>90;  
    
    查询总分大于200分的所有同学  
    select id,name,chinese,math,english   
    from students   
    where chinese+math+english > 200;  
    
    查询数学分数为89或者90或者91的同学  
    方式一：  
    select name,math  
    from students  
    where (math=89) or (math=91) or (math=90);  
    
    方式二：（推荐）  
    select name,math  
    from students  
    where math in (91,89,90,100,10000);//即使加上一些不存在的值也没问题 
    
    查询英语分数在 80－90之间的同学，包含80和90  
    方式一：  
    select name,english  
    from students  
    where (english>=80) and (english<=90);  
    
    方式二：  
    select name,english  
    from students  
    where english between 80 and 90;  
    select name,english from students where english between 80 and 90;  
    
    查询所有姓'李'的学生成绩，%表示0或多个字符(模糊查询)  
    select name,english,math,english  
    from students  
    where name like '李%';  
    select * from students where name like '李%';  
    =表示精确比较  
    模糊比较，like关键字  
    
    查询所有名'李'的学生成绩  
    select name,english,math,english  
    from students  
    where name like '%李';  
    
    查询所有姓名中包含’李’的学生成绩  
    select name,english,math,english  
    from students  
    where name like '%李%';  
    
    以下三条SQL都表示查询表中的[所有]记录  
    select name,english,math,english  
    from students  
    where name like '%';  
    
    select name,english,math,english  
    from students  
    where name like '%%';  
    
    select name,english,math,english  
    from students  
    where name like '%%%';  
    
    查询所有姓'李'的学生成绩,但姓名必须是三个字符，_表示1个字符  
    select *  
    from students  
    where name like '李__';  
    select * from students where name like '李__';  
    
    查询数学分>80且语文分>80的同学  
    select *  
    from students   
    where 1=1 and   
    (math>80) and   
    (chinese>80);  
    
    select * from students where 1=1 and (math>80) and (chinese>80);  
    ---------------------------------------------  
    对数学成绩排序(降序)后输出  
    select id,name,math  
    from students  
    order by math desc;  
    select id,name,math from students order by math desc;  
    desc表示降序，排序字段用数值型  
    
    select id,name,math  
    from students  
    order by math asc;  
    asc表示升序，不写asc和desc默认是升序  
    
    **对总分排序(降序)后输出  
    SELECT name "姓名",math+chinese+english "总分"  
    From students  
    order by (math+chinese+english) desc;  
    select name "姓名",math+chinese+english "总分" from students order by (math+chinese+english) desc;  
    
    扩展：通常<order by>后面可以跟如下内容：  
    1）字段  
       order by math desc  
    2）表达式  
       order by (math+chinese+english) desc  
    3）别名(这个别名可以不加引号)  
       select name "姓名",math+chinese+english "总分"  
       from students  
       order by 总分 desc;  
    4）列号，即列在select中出现的位置，从1开始排序  
       select name "姓名",math+chinese+english "总分"  
       from students  
       order by 2 desc;  
    
    对姓'李'的学生总分排序(降序)输出  
    select name "姓名",(chinese+math+english) "总分"  
    from students  
    where name like '李%'  
    order by 2 desc;  
    --  
    select name "姓名",(chinese+math+english) "总分" from students where name like "李%" order by 2 desc;  
    
    查询数学分为NULL的学生  
    select name,math  
    from students  
    where math is null;  
    select name,math from students where math is null;  
    
    select name,math  
    from students  
    where math is not null;  
    
    还有not in,not between and,is not  
    -------------------------------------------------------------------------------------------------------  
    统计函数：统计函数会把null值得排除掉  
    统计一个班级共有多少学生  
    select count(*) "总人数"  
    from students;  
    select count(*) "总人数" from students;  
    
    不提倡用*号，而用非NULL唯一列，即id（主健）  
    select count(id)  
    from students;  
    select count(id) from students;  
    
    select count(math)  
    from students;  
    建议不要统计含有NULL的列值  
    
    统计数学成绩大于80的学生有多少个  
    select count(id)  
    from students  
    where math>80;  
    select count(id) from students where math>80;  
    
    统计总分大于250的人数有多少  
    select count(id)  
    from students  
    where (math+chinese+english)>250;  
    -----  
    统计一个班级数学总成绩  
    select sum(math) "数学总成绩"  
    from students;  
    select sum(math) "数学总成绩" from students;  
    
    统计一个班级语文、英语、数学各科的总成绩  
    select sum(math) "数学总成绩",sum(english) "英语总成绩",sum(chinese) "语文总成绩"  
    from students;  
    
    统计一个班级语文、英语、数学的成绩总和  
    select sum(chinese+math+english) "总成绩"  
    from students;  
    ------  
    统计一个班级语文成绩平均分  
    select avg(chinese) "语文平均分"  
    from students;  
    select avg(chinese) "语文平均分"  
    from students;  
    
    求一个班级数学平均分  
    select avg(math) "数学平均分"  
    from students;  
    
    求一个班级总分平均分  
    select avg(math+chinese+english) "班级总分平均分"  
    from students;  
    
    求班级最高分和最低数学分数  
    select max(math) "数学最高分",min(math) "数学最低分"  
    from students;  
    
    回顾：  
    count()   统计总数  
    sum()     求和  
    avg()     平均  
    max()     最值  
    min()  
    
    -------------------------------------------------------------------------------------------------------  
    
    orders表：  
    drop table if exists orders;  
    create table if not exists orders(  
        o_id int,  
        o_product varchar(20),  
        o_price int  
    );  
    
    insert into orders values(1,'电视',900);  
    insert into orders values(2,'洗衣机',100);  
    insert into orders values(3,'洗衣粉',90);  
    insert into orders values(4,'桔子',10);  
    insert into orders values(5,'洗衣粉',80);  
    
    
    
    对订单表中商品归类后，显示每一类商品的总价------------------------------用orders.sql表  
    select o_product "商品",sum(o_price) "总价"  
    from orders   
    group by o_product;  
    
    查询购买了几类商品，并且每类总价大于100的商品，即分类后，还要过滤  
    select o_product "商品",sum(o_price) "总价"  
    from orders   
    group by o_product  
    having sum(o_price) > 100;  
    
    小结：  
    where:  
    行过滤器  
    针对原始记录，即没有分组的记录  
    可以不出现  
    通常出现在where后面  
    先执行where  
    
    having:  
    组过滤器  
    针对分组后的记录  
    可以不出现  
    通过出现在group by 后面  
    后执行having  
    
    查询购买了几类商品，并且每类总价大于100的商品，同时按照总价的降序排列  
    select o_product "商品",sum(o_price) "总价"  
    from orders   
    group by o_product  
    having sum(o_price) > 100  
    order by 2 desc;  
    
    小结：  
    select子句  
    from 子句  
    where子句  
    group by子句  
    having子句  
    order by子句  
    
    在MySQL数据库服务器中，上面的所有子句，哪个是必须写的呢?  
    答：select

[http_blog.csdn.net_qq_32059827_article_details_51763950]: http://blog.csdn.net/qq_32059827/article/details/51763950