资讯详情

数据库 基本的SQL语句

本节学习基础sql句子，然后熟悉一个主题。

操作数据库

操作数据库表

改变表的结构

字段属性设置：

对数据的操作 增删改查

增

删

改

查 （重点）

多表查询：

关联查询：inner join

结论：多表查询主要注意以下两点

模糊查找：like

练习

练习

答案：

--列出所有数据库
SHOWDATABASES;
--创建数据库
CREATE DATABASE school DEFAULT CHARACTER SET utf8;
--删除数据库
DROP DATABASE school;

--切换数据库
USEschool;
--创建表
CREATE TABLE student(
id INTPRIMARYKEYAUTO_INCREMENT,--
` name ` CHAR (10),
age INT ,
gender CHAR (1)
);
  
-- 查看所有表
SHOW TABLES;
-- 查看表的结构
DESC  student;  -- description
-- 删除表
DROP  TABLE  student;

-- 添加字段
ALTER  TABLE  student  ADD  COLUMN  address  CHAR (10);
-- 删除字段
ALTER  TABLE  student  DROP  COLUMN  address;
-- 修改表的字段
ALTER  TABLE  student CHANGE address addr  CHAR (20);
-- 修改表的名字
ALTER  TABLE  student RENAME  TO  stu;

1、not null： 不为空，表示该字段不能放“null”这个值。不写，则默认是可以为空
2、auto_increment: 设定int类型字段的值可以“自增长”，即其值无需“写入”，而会自动获得并增加
此属性必须随同 primary key 或 unique key 一起使用。 primary key = unique key + not null
3、[primary] key： 设定为主键。是唯一键“加强”：不能重复并且不能使用null，并且可以作为确定任意一行数据的“关键值”，最常见的类似：where id= 8; 或 where user_name = ‘zhangsan’;
通常，每个表都应该有个主键，而且大多数表，喜欢使用一个id并自增长类型作为主键。
但：一个表只能设定一个主键。
4、 unique [key] : 设定为唯一键：表示该字段的所有行的值不可以重复（唯一性）。
Duplicate entry 'zhangsan' for key 'name'
5、default ‘默认值’： 设定一个字段在没有插入数据的时候自动使用的值。
6、comment ‘字段注释’

INSERT  INTO  student(id, ` name ` ,age,gender)  VALUES (1, 'wangwu' ,23, '男' );
INSERT  INTO  student(id, ` name ` ,age,gender)  VALUES (3, '赵六' ,23, '男' );
INSERT  INTO  student  VALUES (4, '赵六22' ,33, '男' );
-- 插入部分字段值(必须把前面的字段名都写上)
INSERT  INTO  student( ` name ` ,age,gender)  VALUES ( '小张11' ,23, '男' );
-- 一次插入多条数据
INSERT  INTO  student( ` name ` ,age,gender)  VALUES ( '小张77' ,23, '男' ),( '小王' ,22, '男' );

  

DELETE  FROM  student;  -- 删除表中所有数据（很少使用，是非常危险）
DELETE  FROM  student  WHERE  age=24;  -- 所有age是24的数据都被删除了，可能有多条数据都是age=24
DELETE  FROM  student  WHERE  id=12;  -- 因为id是主键是唯一的，所以根据id删除只能删除唯一的一条数据
-- TRUNCATE删除表里面所有数据，与 DELETE不同的是 自 增的id会重新初始化为初始值1
TRUNCATE  TABLE  student;

UPDATE  student  SET  age=age+1;
UPDATE  student  SET  age=age+1  WHERE  id=7;
  

-- 显示所有列(字段)数据
SELECT  *  FROM  student;  -- 学习时候可以写*，但是在企业开发中需要什么字段就写什么字段
SELECT  id, ` name ` ,age,gender  FROM  student;
-- 查询指定列
SELECT ` name ` ,age  FROM  student;
-- 查询时候添加常量列，通过as可以起别名
SELECT  id, ` name ` ,age  AS  '年龄' , 'java1812'  AS  '班级'  FROM  student;
-- 查询时候合并列，字段可以当成java里面的变量来运算
SELECT  id, ` name ` ,(php+java)  AS  '总成绩'  FROM  student;
-- 查询时候去掉重复的记录
SELECT  DISTINCT  address  FROM  student;
  
-- 条件查询 where
SELECT  *  FROM  student  WHERE ` name ` = '小王' ;
  
-- 逻辑条件: and（同时成立） or(只要有一个成立)
SELECT  *  FROM  student  WHERE ` name ` = '小王'  AND  address= '青岛' ;
SELECT  *  FROM  student  WHERE ` name ` = '小王'  OR  address= '北京' ;
  
-- 比较运算: >  <  >=  <=  !=
SELECT  *  FROM  student  WHERE  java>=70  AND  java<=80;
-- between and (等价于>= and <=)
SELECT  *  FROM  student  WHERE  java  BETWEEN  70  AND  80;
-- 查询地址不是青岛的学生信息
SELECT  *  FROM  student  WHERE  address !=  '青岛' ;
  
-- 聚合查询
-- 聚合查询函数：sum(),avg(),max(),min(),count()
-- 统计学生php的总成绩（sum求和）
SELECT  SUM (php)  AS  'php总成绩'  FROM  student;
-- 统计学生php的平均值
SELECT  AVG (php)  AS  'php平均值'  FROM  student;
-- 统计学生php的最大值
SELECT  MAX (php)  AS  'php最大值'  FROM  student;
-- 统计学生表里面一共有多少学生
SELECT  COUNT (*)  AS  '总人数'  FROM  student;
SELECT  COUNT (id)  AS  '总人数'  FROM  student;
SELECT  COUNT (address)  AS  '总人数'  FROM  student;
-- 注意：count()函数统计的是指定列不包含NULL的数据个数
  
  
-- 查询排序
-- 语法：order by 字段  asc/desc  默认是asc升序，可以不写
SELECT  *  FROM  student  ORDER  BY  php;
SELECT  *  FROM  student  ORDER  BY  php  ASC ;
SELECT  *  FROM  student  ORDER  BY  php  DESC ;
-- 多个条件排序
-- 需求：先按照php降序，java升序(整体是按照php降序，如果php相同的数据再按照java标准排序)
SELECT  *  FROM  student  ORDER  BY  php  DESC , java  ASC ;

-- 分组查询(group by)

-- 需求：查询男女分别有多少人 

-- 分组查询后筛选
-- 需求：address大于1
-- group by之后的条件查询使用having
SELECT  address  AS  '地址' , COUNT (id)  AS  '人数'  FROM  student  GROUP  BY  address  HAVING  COUNT (id)>1;

学生表、班级表、课程表、班级课程表

-- 班级表
CREATE TABLE banji(
    id INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(10) NOT NULL
);
INSERT INTO banji(`name`) VALUES('java1807'),('java1812');
 
SELECT * FROM banji;
 
-- 学生表 reference:参考，引用
CREATE TABLE student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(10) NOT NULL,
    age INT,
    gender CHAR(1),
    banji_id INT,
    FOREIGN KEY(banji_id) REFERENCES banji(id)
);
INSERT INTO student(`name`,age,gender,banji_id) 
VALUES('张三',20,'男',1),('李四',21,'男',2),('王五',20,'女',1);
-- Cannot add or update a child row: a foreign key constraint fails (`java1812`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`banji_id`) REFERENCES `banji` (`id`))
INSERT INTO student(`name`,age,gender,banji_id) 
VALUES('张三',20,'男',3);
 
SELECT * FROM student;
 
-- 课程表
CREATE TABLE course(
    id INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(10) NOT NULL,
    credit INT COMMENT '学分'
);
INSERT INTO course(`name`,credit) VALUES('Java',5),('UI',4),('H5',4);
 
SELECT * FROM course;
 
-- 班级课程表
CREATE TABLE banji_course(
    -- id int PRIMARY KEY AUTO_INCREMENT,
    banji_id INT,
    course_id INT,
    PRIMARY KEY(banji_id,course_id), -- 联合主键
    FOREIGN KEY(banji_id) REFERENCES banji(id), -- banji_id既是联合主键又是外键
    FOREIGN KEY(course_id) REFERENCES course(id) -- course_id既是联合主键又是外键
);
INSERT INTO banji_course(banji_id,course_id) VALUES(1,1),(1,3),(2,1),(2,2),(2,3);
 
SELECT * FROM banji_course;
-- 子查询：嵌套查询，一个查询语句是另一个查询语句的条件
-- 查询班级是java1812班所有学生信息
SELECT  *  FROM  student  WHERE  banji_id=( SELECT  id  FROM  banji  WHERE  ` name `= 'java1812' );
-- "="：要求子查询只有一个结果。 "in"：子查询可以有多个结果

关联查询：inner join

-- 列出所有学生学习的课程名称

-- 学生姓名   班级名称   课程名称    学分

语法形式：字段 like '要查找字符'
说明：
1、like模糊查找用于对字符类型的字段进行字符匹配查找。
2、要查找的字符中，有两个特殊含义的字符：% , _:
     2.1: %含义是：代表0或多个的任意字符
     2.2: _含义是：代表1个任意字符
     2.3: 这里的字符都是指现实中可见的一个“符号”，而不是字节。
3、语法：like '%关键字%'
  *    student   `name`  LIKE  '张%' ;  -- 以张开头
  *    student   `name`  LIKE  '张_' ;  -- 以张开头，而且名字是两个字
  *    student   `name`  LIKE  '%张%' ;  -- 名字里面只要有张就可以
如果要查找的字符里中包含"%","_"，
如果要查找的字符中包含“%”或“_”，“  ”，则只要对他们进行 就可以：
like ‘%ab \ %cd%’ //这里要找的是： 包含 ab%cd 字符的字符
like ‘ \_ab%’ //这里要找的是： _ab开头的字符
like ‘%ab \'cd%’ //这里要找的是： 包含 ab'cd 字符的字符
'ab'cd'

练习

练习

首先直接运行以下代码构建表

create table goods (
  goods_id mediumint(8) unsigned primary key auto_increment,
  goods_name varchar(120) not null default '',
  cat_id smallint(5) unsigned not null default '0',
  brand_id smallint(5) unsigned not null default '0',
  goods_sn char(15) not null default '',
  goods_number smallint(5) unsigned not null default '0',
  shop_price decimal(10,2) unsigned not null default '0.00',
  market_price decimal(10,2) unsigned not null default '0.00',
  click_count int(10) unsigned not null default '0'
) ;

insert into `goods` values (1,'kd876',4,8,'ecs000000',1,1388.00,1665.60,9),
(4,'诺基亚n85原装充电器',8,1,'ecs000004',17,58.00,69.60,0),
(3,'诺基亚原装5800耳机',8,1,'ecs000002',24,68.00,81.60,3),
(5,'索爱原装m2卡读卡器',11,7,'ecs000005',8,20.00,24.00,3),
(6,'胜创kingmax内存卡',11,0,'ecs000006',15,42.00,50.40,0),  
(7,'诺基亚n85原装立体声耳机hs-82',8,1,'ecs000007',20,100.00,120.00,0),
(8,'飞利浦9@9v',3,4,'ecs000008',1,399.00,478.79,10),
(9,'诺基亚e66',3,1,'ecs000009',4,2298.00,2757.60,20),
(10,'索爱c702c',3,7,'ecs000010',7,1328.00,1593.60,11),
(11,'索爱c702c',3,7,'ecs000011',1,1300.00,0.00,0),
(12,'摩托罗拉a810',3,2,'ecs000012',8,983.00,1179.60,13),
(13,'诺基亚5320 xpressmusic',3,1,'ecs000013',8,1311.00,1573.20,13),
(14,'诺基亚5800xm',4,1,'ecs000014',1,2625.00,3150.00,6),
(15,'摩托罗拉a810',3,2,'ecs000015',3,788.00,945.60,8),
(16,'恒基伟业g101',2,11,'ecs000016',0,823.33,988.00,3),
(17,'夏新n7',3,5,'ecs000017',1,2300.00,2760.00,2),
(18,'夏新t5',4,5,'ecs000018',1,2878.00,3453.60,0),
(19,'三星sgh-f258',3,6,'ecs000019',12,858.00,1029.60,7),
(20,'三星bc01',3,6,'ecs000020',12,280.00,336.00,14),
(21,'金立 a30',3,10,'ecs000021',40,2000.00,2400.00,4),
(22,'多普达touch hd',3,3,'ecs000022',1,5999.00,7198.80,16),
(23,'诺基亚n96',5,1,'ecs000023',8,3700.00,4440.00,17),
(24,'p806',3,9,'ecs000024',100,2000.00,2400.00,35),
(25,'小灵通/固话50元充值卡',13,0,'ecs000025',2,48.00,57.59,0),
(26,'小灵通/固话20元充值卡',13,0,'ecs000026',2,19.00,22.80,0),
(27,'联通100元充值卡',15,0,'ecs000027',2,95.00,100.00,0),
(28,'联通50元充值卡',15,0,'ecs000028',0,45.00,50.00,0),
(29,'移动100元充值卡',14,0,'ecs000029',0,90.00,0.00,0),
(30,'移动20元充值卡',14,0,'ecs000030',9,18.00,21.00,1),
(31,'摩托罗拉e8 ',3,2,'ecs000031',1,1337.00,1604.39,5),  
(32,'诺基亚n85',3,1,'ecs000032',4,3010.00,3612.00,9);

-- 分类表
create table category (
    cat_id smallint unsigned auto_increment primary key,
    cat_name varchar(90) not null default '',
    parent_id smallint unsigned
);

INSERT INTO `category` VALUES
(1,'手机类型',0),
(2,'CDMA手机',1),
(3,'GSM手机',1),
(4,'3G手机',1),
(5,'双模手机',1),
(6,'手机配件',0),
(7,'充电器',6),
(8,'耳机',6),
(9,'电池',6),
(11,'读卡器和内存卡',6),   
(12,'充值卡',0),
(13,'小灵通/固话充值卡',12),
(14,'移动手机充值卡',12),
(15,'联通手机充值卡',12);

- 1:主键为32的商品

-- 2:不属第3栏目的所有商品（category中id为3）

-- 3:本店价格高于3000元的商品

-- 4:本店价格低于或等于100元的商品

-- 5:取出第4栏目或第11栏目的商品

-- 6:取出100<=价格<=500的商品

-- BETWEEN AND是能取到开始和结束的值，等价于>= and <=

-- 7:取出不属于第3栏目且不属于第11栏目的商品(and,或not in分别实现)

-- 8:取出价格大于100且小于300,或者大于4000且小于5000的商品()

-- 这种写法很容易出歧义，要加括号，写出有歧义的语句并不能显出你多厉害

-- 9:取出第3个栏目下面价格<1000或>3000,并且点击量>5的系列商品

-- 10:取出第1个栏目下面的商品(注意:1栏目下面没商品,但其子栏目下有)

-- 11:取出名字以"诺基亚"开头的商品

-- like 模糊匹配

-- % 通配任意字符

-- _ 通配单一字符

-- 15:把goods表中商品名为'诺基亚xxxx'的商品,改为'HTCxxxx',

-- 3、查询该店每个栏目下挤压的货款
-- 4、查询该店每个栏目下挤压的货款 > 20000
LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)
-- 1、按照栏目由低到高排序，栏目内部按照价格由高到低排序
-- 2、取出价格最高的前三名商品
-- limit offset,rowcount
-- limit 偏移到哪个位置,往下数几个
-- 3、取出点击量第三名到第五名的商品

答案：

-- 1:主键为32的商品
SELECT * FROM goods WHERE goods_id=32;
-- 2:不属第3栏目的所有商品（category中id为3）
SELECT * FROM goods WHERE cat_id!=3;
-- 3:本店价格高于3000元的商品
SELECT * FROM goods WHERE market_price>3000;
-- 4:本店价格低于或等于100元的商品
SELECT * FROM goods WHERE market_price<=100;
-- 5:取出第4栏目或第11栏目的商品
SELECT * FROM goods WHERE cat_id=4 OR cat_id=11;
-- 6:取出100<=价格<=500的商品
-- BETWEEN AND是能取到开始和结束的值，等价于>= and <=
SELECT * FROM goods WHERE market_price>=100 AND market_price<=500;
SELECT * FROM goods WHERE market_price BETWEEN 100 AND 500;
-- 7:取出不属于第3栏目且不属于第11栏目的商品(and,或not in分别实现)
SELECT * FROM goods WHERE cat_id !=3 AND cat_id!=11;
SELECT * FROM goods WHERE cat_id NOT IN(SELECT cat_id FROM goods WHERE cat_id=3 OR cat_id=11);
-- 8:取出价格大于100且小于300,或者大于4000且小于5000的商品()
-- 这种写法很容易出歧义，要加括号，写出有歧义的语句并不能显出你多厉害
SELECT * FROM goods WHERE (market_price BETWEEN 100 AND	300) OR (market_price BETWEEN 4000 AND 5000);
-- 9:取出第3个栏目下面价格<1000或>3000,并且点击量>5的系列商品
SELECT * FROM goods WHERE (market_price<1000 OR market_price>3000) AND cat_id=3 AND click_count>5;
-- 10:取出第1个栏目下面的商品(注意:1栏目下面没商品,但其子栏目下有)
SELECT * FROM goods WHERE cat_id IN(SELECT cat_id FROM category WHERE parent_id=1);
-- 11:取出名字以"诺基亚"开头的商品
-- like 模糊匹配
-- % 通配任意字符
-- _ 通配单一字符
SELECT * FROM goods WHERE goods_name LIKE'诺基亚%';

-- 15:把goods表中商品名为'诺基亚xxxx'的商品,改为'HTCxxxx',
SELECT goods_id,SUBSTR(goods_name, 4) FROM goods 
WHERE goods_name LIKE '诺基亚%';
SELECT goods_id,CONCAT('HTC',SUBSTRING(goods_name,4)) FROM goods 
WHERE goods_name LIKE '诺基亚%';
-- 3、查询该店每个栏目下挤压的货款
SELECT cat_id,SUM(goods_number*shop_price) 
FROM goods GROUP BY cat_id ;
-- 4、查询该店每个栏目下挤压的货款 > 20000
SELECT cat_id,SUM(goods_number*shop_price) AS total_price
FROM goods GROUP BY cat_id HAVING total_price>20000;

-- order by 与 limit：
-- LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。
-- LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。
-- 如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。
-- 初始记录行的偏移量是 0(而不是 1)
-- 1、按照栏目由低到高排序，栏目内部按照价格由高到低排序
SELECT goods_id,cat_id,goods_name,shop_price
FROM goods 
ORDER BY cat_id ASC,shop_price DESC;
-- 2、取出价格最高的前三名商品
-- limit offset,rowcount
-- limit 偏移到哪个位置,往下数几个
SELECT goods_id,cat_id,goods_name,shop_price
FROM goods
ORDER BY shop_price DESC LIMIT 0,3; -- limit 3
-- 3、取出点击量第三名到第五名的商品
SELECT goods_id,cat_id,goods_name,click_count
FROM goods
ORDER BY click_count DESC LIMIT 2,3;

标签： g101cb光电传感器g101p光纤传感器

锐单商城拥有海量元器件数据手册、 IC替代型号，打造 电子元器件IC百科大全！

锐单商城 - 一站式电子元器件采购平台