Mysql学习随笔

学习视频网址链接https://www.bilibili.com/video/av5411645/

语句不区分大小写，字符串以单引号扩起来
select * from T_books 查询表格所有信息
select name from T_books 查询某一列
select name from T_books where price<30 按条件查询某列
select name as 姓名 from T_books where price<30 查询别名
select now() 获得当前时间

insert into T_books (ID, name, author, price) values(002, ‘三体’， ‘liucixin’,30) 增加语句
delete from T_books where name=’三体’ 删除一行语句
update T_books set price=32 where name=’三体’ 更改语句

select max(price), min(price), sum(price), avg(price), count(*) from T_books where price>20 聚合函数
selece * from T_books where price>20 order by priece asc, ID desc asc表示升序，desc表示降序，意思是优先按price升序排列， 有相同的再按ID降序

通配符过滤（用like）”_”匹配单个字符 “%”匹配多个字符
select * from T_books where name like ‘___cixin’
select * from T_books where name like ‘%liu%’

获取空值有关的信息
select * from T_books where name is null 不是name=null
select * from T_books where name is not null 不是name!=null

limit关键字限制返回的结果集 放在语句最后位置
selece * from T_books limit 0, 5 从第1行开始最多5行数据（数据从0行开始）

group by 对某特征进行分组
select age from T_employees group by age 按员工年龄分组（相同为一组）
select age, avg(salary), max(salary), count(*) from T_employees group by age
查询各年龄分组平均工资、最大工资、数量
selece age, group_contact(salary) from T_employees group by age
with rollup （最后一行查询总和）

通过外键链接各表 left join
select o.Id 订单Id, c.Id 客户Id, o.Number 订单编号 o.Price 订单价格 t.name订单类型
from T_Orders o
left join T_Customers c on o.CustomerId=c.id
left join T_OrderTypes t on o.Typeid=t.id

逻辑运算符号：与 and,&&; 或 or,||