ID488257875

• 查询数据库表：Select * from T_Students

            

• 插入记录：Insert into T_Students(Id,Gender,Name,Hobbies) values(5,1,'Tom','英雄联盟')

            

• 数据更新：

            Update T_Students Set Age=19 where Name='Tom'  

            

                     Update T_Students Set Age=19 where Name='Tom' or Age<18

            

                    where中可以使用的其他运算符：or , and , not ,  < , > , <= , >= , != (<>)

• 数据删除：

  Delete from T_Students where Age>30 or Age<20

            
               

Delete只是删除数据，表还在。Drop table T_Students

• 员工信息表：

  insert into t_employees(number,name,age,salary) value('DEV001','Tom',25,8300);

  insert into t_employees(number,name,age,salary) value('DEV002','Jerry',28,9300.80);

  insert into t_employees(number,name,age,salary) value('SALES001','John',23,5000);

  insert into t_employees(number,name,age,salary) value('SALES002','Kerry',28,6200);

  insert into t_employees(number,name,age,salary) value('SALES003','Stone',22,1200);

  insert into t_employees(number,name,age,salary) value('HR001','Jane',23,2200.88);

  insert into t_employees(number,name,age,salary) value('HR002','Tina',25,5200.36);

  insert into t_employees(number,name,age,salary) value('IT001','Smith',28,3900);

  insert into t_employees(number,age,salary) value('IT001',27,2800);

• 数据检索：

  只检索需要的列：select name,age from t_employees

            

   

列别名：select name as 姓名,salary 薪水 from t_employees

            

          计算列：select name,age,age+1 from t_employees

            

          条件检索：select * from t_employees where age>25 or salary>5000

            

        select now()  获取当前时间

            

• 数据汇总：

        SQL聚合函数：

        select max(salary) from t_employees

            

        select max(salary) from t_employees where age>25

            

        select max(salary) as 最高工资,min(salary) as 最低工资 from t_employees

            

        select count(*) from t_employees    (count数量/行数)

            

        select count(*) from t_employees where age>25

            

        select sum(salary),avg(salary) from t_employees   (avg平均值,sum求和)

            

• 数据排序：

        select * from t_employees order by age ASC  (按照age升序排列)

            

        select * from t_employees order by age DESC,salary DESC  (按照年龄工资降序排列)

            

        select * from t_employees where age>25 order by age DESC,salary DESC  (order by 放在where 后面)

            

• 通配符过滤：(like全表扫描，性能较差)

        select * from t_employees where name like '_erry'  (_匹配单个出现的字符)

            

        select * from t_employees where name like '%n'  (%匹配多个字符)

            

        select * from t_employees where name like '%n%'  (name中包含n的)

            

• 空值处理：

        数据库中null 表示 不知道

        select * from t_employees where name is null

            

        select * from t_employees where name is not null

            

• limit获取部分结果集

        select * from t_employees limit 2,5

            

        select * from t_employees where name is not null order by salary DESC limit 2,3     (limit放在所有的后面)

            

• group by 分组查询

        select age from t_employees group by age

            

        select age,avg(salary),max(salary),min(salary) from t_employees group by age   (先按age分组，再统计组内数据)

            

        select age,count(*) from t_employees group by age

            

• left join联合查询

        查询每张订单的订单号、价格、对应客户姓名以及客户年龄

        select o.Number,o.Price,c.Name,c,Age

        form T_Orders as o

        left join T_Custumers as c

        on o.CustumerId=c.Id