dbeaver的基础语句你要了解

❤️首先dbeaver是2013年正式开源的它基于javaeclip编写是最主流的免费通用数据库客户端之一学习dbeaver很重要哦。一.查看系统表select name from sqlite_master where type table order by name;二.查询语句单表查询语句写法术语作用SELECT ... FROM ...查询语句的主体查哪些列、从哪张表WHERE ...条件子句 / 过滤条件筛选行ORDER BY ...排序子句按某列排序DISTINCT去重去掉重复值LIMIT ...限制行数只取前 N 条举例①select distinct Composer from trackWhere genreid1and name like %rock%and unitprice between 0.99 and 1.99order by unitprice desclimit 5 offset 2;解释首先在第一行写上从track表找composer要求composer去重所以select后面写distinct然后最后一行是显示前五行但是不要前2行那就是3-7行最后条件写中间其中是精准匹配like是模糊查询的关键字%是任意长度任意字符含0—是单个任意字符这个的意思是只要含有rock就满足between和and是闭区间同样可写为uintprice0.99 and unitprice1.99多表连接查询语句1.内连接inner join其中inner可省略举例①连接俩表select al.title,ar.name as artistnamefrom album aljoin artist aron al.ArtistId ar.ArtistIdlimit 10;②连接三表select al.title, ar.name as artistname, t.name as tracknamefrom album aljoin artist ar on al.artistidar.artistidjoin track t on t.albumidal.albumidlimit 10;2.外连接主要使用左连接 left join 右连接right join 全连接full outer join知道有就可以了解释内外连接的区分在于内连接是将俩个表无法匹配的行删掉外连接则是无法匹配取值为full三 .聚合函数zhuyzhu函数作用COUNT()统计行数、计数SUM()求和只适合数字AVG()求平均值MAX()最大值MIN()最小值举例select countrycount*cntfrom customerwhere city is not nullgroup by countryhaving cnt10;注意1.where和having都是筛选但where在group by 分组前对数据筛选所以不能筛选条件写聚合函数having则可以。2.对于举例中别名cnt这样实际是省略了as 完整是count*as cnt这里用空格省略as那么在别名中带有空格时我们要用反引号引出。比如count*cnt a四 增删改增删改查是sql语句最基础也最常用的这里在学增删改之前复制表数据在测试表进行增删改create table customer_test as select * from customer;验证SELECT COUNT(*) FROM Customer;SELECT COUNT(*) FROM Customer_test;测试表坏了 删掉表drop table customer_test;1删注意先查再删再查看select *from customer_test where customerid10;delete from customer_test where customerid10;select *from customer_test where customerid10;2改注意先查看再修改再查看select *from customer_test where customerid10;update customer_testset FirstNamelulu,Lastnamezwhere customerid10;select * from customer_test where customerid10;3增insert into customer_test(10lily)完整应为insert into customer_test(id,name)values(10,lily)五 子查询1 where①inselect * from customerwhere city in(select city from employee)②not inselect * from customerwhere city not in(select city from employee)2 from派生表select *from(select country count(*) cntfrom customergroup by country)as t_tempwhere cnt53 标量子查询括号里返回单个数字可 select *from customerwhere customerid(select max(customerid)from customer);4 selectSELECT CustomerId, Country, (SELECT COUNT(*) FROM Customer_test AS b WHERE b.Country a.Country) AS country_total FROM Customer_test AS a;