数据库语句练习

数据库语句练习

基础语句

goods表名

test_id 、 goodsName 为字段

1 添加三条数据

INSERT INTO goods VALUES(1,"dd");
INSERT INTO goods VALUES(2,"dd");
INSERT INTO goods VALUES(3,"dd");

2 插入指定字段

 INSERT INTO goods(goodsName) VALUES("aa");

3 修改数据

UPDATE goods set goodsName = "dd" WHERE tese_id = 5;

4 删除一条数据

删除tese_id = 4

DELETE FROM goods WHERE tese_id = 4;

查询语句

1 基本查询

查询所有所有数据

SELECT * FROM goods;

查询部分字段

SELECT goodsName FROM goods;

起别名 as

SELECT goodsName AS '商品名称' FROM goods;

2 条件查询

book为表名

id author isbn name pages price publishi publish_time size reanslate type 为字段

比较逻辑查询、逻辑查询

查询page>300的所有商品信息

SELECT * FROM book WHERE pages > 300;

模糊查询

查看所有含”日报“的出版社

范围查询

查看所有价格在20-30之间的数据

SELECT * FROM book WHERE price BETWEEN 20 AND 30;

空判断

select * from goods where remark is null

排序

ASC升序 DESC降序

SELECT * FROM book ORDER BY price ASC;

SELECT * FROM book ORDER BY price DESC;

聚合函数

查询信息总数count()

SELECT COUNT(*) FROM book;

查询最大值max()

SELECT max(price) FROM book;

查询最小值min()

SELECT MIN(price) FROM book;

查询平均值avg()

SELECT AVG(price) FROM book;

求和sum()

SELECT sum(price) FROM book;

分组

GROUP BY

SELECT publish,COUNT(*) FROM book GROUP BY publish;

分页查询

limit 范围

SELECT publish,`name` FROM book LIMIT 4,6;

连接查询

连接查询共通知识点

连接后的表进行字段显示限制——表名.字段 eg：book.price

表名.* 所有字段

表名起别名 eg：goods go

内连接

SELECT * FROM book bo INNER JOIN  borrow bor on bo.id = bor.book_id;

左连接

SELECT * FROM borrow bor LEFT JOIN  book bo on bo.id = bor.book_id;

右连接

SELECT * FROM book bo RIGHT  JOIN  borrow bor on bo.id = bor.book_id;

自关联

SELECT * FROM book bo1 INNER JOIN  book bo2 on bo1.author = bo2.author;

子查询

充当条件

SELECT * FROM book WHERE price >(SELECT AVG(price) FROM book );

充当数据源

SELECT * FROM book bo INNER JOIN (SELECT book_id FROM borrow) bor on bo.id = bor.book_id;

子查询关键字

in范围

some/any任意一个

all全部

！= all或<> all不等于所有

SELECT * FROM book WHERE price in (35,31);

SELECT * FROM book WHERE price = SOME(SELECT price FROM book WHERE price BETWEEN 20  AND 50);

SELECT * FROM book WHERE price = ANY(SELECT price FROM book WHERE price BETWEEN 20  AND 50);

SELECT * FROM book WHERE price <> ALL(SELECT price FROM book WHERE price BETWEEN 20  AND 50);

SELECT * FROM book WHERE price = ALL(SELECT price FROM book WHERE price BETWEEN 20  AND 50);

SELECT * FROM book WHERE price ！= ALL(SELECT price FROM book WHERE price BETWEEN 20  AND 50);

其他

查看外键

stu表名

show create table stu;

删除外键

stu_i外键名称

alter table stu drop foreign key stu_i;

开始时间检测

set profiling = 1;

查看运行时间

show profiles;

添加索引

create index 索引名称 on 表名（目标字段）;

查看索引

show index from 表名;

删除索引

drop index 索引名称 on 表名;