sql基础入门语法

2017-01-01 21:32:27来源:作者:人点击

sql基础入门语法。实验环境: mysql
DB链接工具: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

– 数据表中插入完整字段(初始化sql测试数据)
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

select *from books where price>23; – 算术表达式:查询价格大于23的数据
select *from books where (2>1) and (3>2); – 逻辑表达式:and只有两个条件都满足才为真
select *from books where (1>2) or (2>1) ; – or:两个条件只要有一个满足即为真

– and和or的优先级:and的优先级大于or

select *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(字段数)

select count(*) from books where gendor=’M’; – 查询满足条件的记录的条数(行数):5
select count(price) from books where gendor=’M’; – 查询满足添加的记录中字段值的个数:4

– 限制获取的记录数

select *from books where gendor=’M’ limit 2; – 表示记录数
select *from books limit 1,2; – 表示从满足记录的第1记录之后的连续两条记录(不包含第一条)

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台