数据库: mysql
数据库管理工具：DBeaver(开源)
基础建表语句：(图书编号 书名 作者 性别 价格 供应商)
注意： sql语句字符与大小写无关
create table books( id int primary key auto_increment, -- 定义主键，并声明主键值增长策略 bookName varchar(10), -- 字符类型：长度可变 author varchar(10) not null, -- 约束不能为空 gendor char(2) check (gendor in ('M','F')), -- 性别约束：男或女 price float, -- 浮点型 vendorId int, -- 出版社代号 foreign key(vendorId) references vendors(id) -- 建立外键约束)character set utf8 collate utf8_general_ci; -- 建表时确认表数据的编码格式 书籍发布商数据表
create table vendors ( id int primary key auto_increment, name varchar(100) NOT NULL, tele varchar(100) NULL, manager varchar(100) NULL)character set utf8 collate utf8_general_ci;
删除数据表
drop table books
数据表中插入完整字段
books表：insert into books values(1, '西游记', '吴承恩', 'M',20.5, 1);insert into books values(2, '三国演义', '罗贯中','F', 49.4,2);insert into books values(3, '水浒传', '施耐庵','M', 23.5, 3);insert into books values(4, '红楼梦', '曹雪芹', 'M', 50.5, 3);insert into books values(5, '史记', '司马迁', 'M', NULL, 2);insert into books values(7, 'AAAA', '吴承恩', 'M', 30.21, 3);vendors表：insert into vendors values(1,'北京大学出版社','020-25325452','张三');insert into vendors values(2,'清华大学出版社','0755-27327552','李四');insert into vendors values(3,'人民日报出版社','010-25322452','王五');insert into vendors values(4,'南方日报出版社','0783-25565452','马六');
数据表中传入部分字段
格式：表名后定义需存放的字段
insert into books(bookName,author,gendor,price,vendorId) values('World War','Stepheon','F',25.4,1); 删除数据表中的记录
delete from books where id=1;
更新数据表记录
update books set gendor='M', price=20 where id=1;
数据表查询
select *from books;select *from vendors;
查询数据记录的部分字段:字段\表别名：as关键字可缺省
select bookName 书名,author as 作者,price 价格 from books allBooks where allBooks.id=1;
条件语句: where、and、or
-- 算术表达式：查询价格大于23的数据select *from books where price > 23;-- 逻辑表达式：and只有两个条件都满足才为真select *from books where (2>1) and (3>2);-- or：两个条件只要有一个满足即为真select *from books where (1>2) or (2>1) ;-- and和or的优先级：and的优先级大于orselect *from books where gendor='M' and id=1 or price>50; -- 等价于：(gendor='M' and id=1) or price>50:相当于合并满足两个条件的结果-- and和or的优先级问题在进行sql拼接时容易产生sql注入漏洞select *from books where gendor='M' and 1 or 1=1; -- 等价于：(gendor='M' and 1) or 1=1:条件永远为真
sql统计函数
select min(price),price from books; -- 查询价格的最小值select max(price),price from books; -- 查询价格的最大值select avg(price) from books; -- 查询价格的平均值select sum(price) from books; -- 查询价格的总价select ucase(bookName) from books; -- 将字段值转换为大写字母select lcase(bookName) from books; -- 字段值转换为小写字母select length(bookName) from books where id=6; -- 获取字段的大小select round(price) from books where id=2; -- 四舍五入
格式化函数
--日期格式化select date_format(now(),'%Y-%m-%d'),allBooks.* from books as allBooks;-- 数字截取：最后一位四舍五入select formate(price,1),allBooks.* from books as allBooks; -- 数字截取：其他位直接舍弃select truncate(price,1),allBooks.* from books as allBooks;
order by: 默认升序排列
select *from books order by price;select *from books order by price asc; -- 升序：默认select *from books order by price desc; -- 降序：默认
group by分组:按字段划分不同的组,默认按分组字段的升序排列
select * from books group by gendor order by id;select *from books --：等价于select *from books group by id -- 因为主键唯一，主键分组得到的数据即数据表所有的记录-- group by ... having联合使用:筛选出书籍单价总额大于60的男性作者信息select author 作者,sum(price) 总价,gendor 性别 from books where gendor='M' group by author having sum(price)>60-- count(*)： 满足条件的记录数 -- count(字段名)：满足条件的记录的特定字段非NULL值行的个数： count(*) >= count(字段数)-- 查询满足条件的记录的条数(行数)：5select count(*) from books where gendor='M'; -- 查询满足添加的记录中字段值的个数:4select count(price) from books where gendor='M'; -- 限制获取的记录数-- 表示记录数select *from books where gendor='M' limit 2; -- 表示从满足记录的第1记录之后的连续两条记录(不包含第一条)select *from books limit 1,2;