sql语句大全

CREATE DATABASE java1911a;
USE java1911a;
CREATE TABLE emp(
    eid INT,
    ename VARCHAR(20),
    age INT,
    gender VARCHAR(1),
    birthday DATE,
    salary DECIMAL(8,2)
    
);
#数据库表的删除 drop table student;
DROP TABLE teacher;
#查看表结构
DESC student
#查看几张表
SHOW TABLES;
#修改表
#添加字段
ALTER TABLE student ADD phone CHAR(11);
#删除字段
ALTER TABLE student DROP gender;
#修改字段类型
ALTER TABLE student MODIFY age VARCHAR(20);
#修改字段名称
ALTER TABLE student CHANGE ename NAME VARCHAR(20);
#修改表名称
ALTER TABLE student RENAME TO teacher;

#插入语句
INSERT INTO emp VALUES(1,'张三',22,'男','2020-09-09',9000);
INSERT INTO emp VALUES(2,'王五',22,'男','2020-09-09',9000),(3,'赵柳',22,'男','2020-09-09',9000);
INSERT INTO emp(eid,ename,gender) VALUES(4,'小红','女');

#修改语句
UPDATE emp SET gender='女';
UPDATE emp SET age=32,gender='女' WHERE eid=3;
UPDATE emp SET age=23 WHERE eid=4;
#删除语句 DELETE FROM student;
DELETE FROM emp WHERE eid=1;

TRUNCATE TABLE emp;

#查询列操作
#查询所有列SELECT * FROM student;
SELECT * FROM emp;
#查询指定列
SELECT ename,gender FROM emp WHERE eid=1;
#查询时合并列
SELECT *,(age+salary) AS SUM FROM emp;
#查询时去除重复记录
SELECT DISTINCT * FROM emp;

#条件查询
#比较条件： > <  >=  <=  =  <> /!=    between  x and y(等价于>= 且 <=)

SELECT * FROM emp WHERE eid>=2;
SELECT * FROM emp WHERE age BETWEEN 12 AND 13;
#逻辑条件 and(与)  查询出的数据多个条件必须同时满足    or(或)链接多个条件 查询出的数据多个条件只要满足一个就可以

SELECT * FROM emp WHERE age>=18 OR gender='男' ;
#判空条件  is null / is not null
SELECT * FROM emp WHERE ename IS  NULL;

#模糊查询 like

SELECT * FROM emp WHERE ename LIKE '王__';

#分页 limit 角标 , 长度
SELECT * FROM emp LIMIT 0,3;

#聚合函数

#最大值 max();
SELECT  MAX(salary) FROM emp;
#最小值 min()
SELECT MIN(salary) AS 最低工资 FROM emp;
#求和 sum()
SELECT SUM(salary) FROM emp;
#平均值 avg()
SELECT AVG(salary) FROM emp;
#记录数 count()
SELECT COUNT(*) FROM emp;

#分组查询 group by
SELECT gender FROM emp GROUP BY gender;
#求每组的总人数,总工资,平均工资
SELECT gender,COUNT(*),SUM(salary),AVG(salary) FROM emp GROUP BY gender;

#分组后做筛选 having 用于分组后做条件查询
SELECT gender FROM emp GROUP BY gender;
#查询那组的总工资大于20000;
SELECT gender FROM emp GROUP BY gender HAVING SUM(salary)<20000;

#查询排序  order by 字段 asc/desc
SELECT * FROM emp ORDER BY age DESC ;
SELECT * FROM emp WHERE eid<=4 ORDER BY age DESC;
SELECT * FROM emp WHERE gender='女' ORDER BY age DESC;

#约束
#非空约束  not null
    
#唯一约束 unique

#主键约束 primary key 约束值唯一不能为null
    #自增长 auto_increment
    
#默认值约束 default

#外键约束  foreign key

CREATE TABLE student(
    id INT PRIMARY KEY AUTO_INCREMENT ,
    NAME VARCHAR(20) NOT NULL,
    gender CHAR(1) DEFAULT '男',
    num CHAR(18) UNIQUE,
    age INT 
    );
INSERT INTO student VALUES(NULL,'aa','女','110',22);
INSERT INTO student VALUES(NULL,'aa','女','120',22);
INSERT INTO student VALUES(NULL,'cc','女','130',22);
INSERT INTO student(NAME,num,age) VALUES('dd','140',44);

DESC student;

#类别表
CREATE TABLE leibie(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
);
#商品表
CREATE TABLE goods(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(100),
    price DECIMAL(8,2),
    l_id INT,
    FOREIGN KEY(l_id) REFERENCES leibie(id)
);

DROP TABLE goods;

#多表查询

#内连接 inner join
    #显式内连接 select * from 表1 inner join 表2 on 主键=外键;
SELECT * FROM leibie l INNER JOIN goods g ON l_id=l.id;
SELECT * FROM leibie l INNER JOIN goods g ON l_id=l.id WHERE l.name='肉类';
SELECT * FROM leibie l INNER JOIN goods g ON l_id=l.id WHERE g.name='西瓜';
    #隐式内连接 select * from 表1 , 表 where 主键=外键;
SELECT * FROM leibie l,goods  g WHERE l_id=l.id AND l.name='肉类';
    
#外连接 outer join
    #左外连接 left outer join
SELECT * FROM goods g LEFT OUTER JOIN leibie l ON l_id=l.id;
SELECT * FROM goods g LEFT OUTER JOIN leibie l ON l_id=l.id WHERE l.name='肉类';
    #右外连接 right outer jon
SELECT * FROM goods g RIGHT OUTER JOIN leibie l ON l_id=l.id WHERE l.name='肉类';

#区别:
    SELECT * FROM goods g INNER JOIN leibie l ON l_id=l.id;
    
    SELECT * FROM goods g LEFT OUTER JOIN leibie l ON l_id=l.id;
    SELECT * FROM goods  RIGHT OUTER JOIN leibie  ON l_id=leibie.id;
    #相同点:都可以做多表查询,都会查询出有主外键关联的数据
    #不同点:内连接:只会查询出有主外键关联的数据
    #       左外连接:左边是主表会查询出主表所有数据,右边是副表,不会查询出无关联的数据
    #       右外连接:右边是主表会查询出主表所有的数据,左边是副表,不会查询出无关联的数据

#案例
CREATE TABLE consume (cid INT PRIMARY KEY AUTO_INCREMENT,consume_project VARCHAR(20));
CREATE TABLE expense(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20),
    consume_price DECIMAL(8,2),
    submit_date TIMESTAMP,
    STATUS VARCHAR(3),
    consume_id INT,
    FOREIGN KEY(consume_id) REFERENCES consume(cid)

);
#联合查询 union
SELECT * FROM expense INNER JOIN consume ON consume_id=cid;
#子查询 子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了。

#1.查询出肉类的id
SELECT id FROM leibie WHERE NAME='肉类';#2
#2.根据肉类的id查询出有哪些商品
SELECT * FROM goods WHERE l_id=2;
#3.把上面两部结合成一个sql语句:子查询案例1
SELECT * FROM goods WHERE l_id=(SELECT id FROM leibie WHERE NAME='肉类');

#哪些student表中的学生年龄大于emp表中杜甫的年龄
SELECT age FROM emp WHERE ename='杜甫';#15

SELECT * FROM student WHERE age>15;

SELECT * FROM student WHERE age>(SELECT age FROM emp WHERE ename='杜甫');

#哪些商品的价格 大于 水果类型的所有商品的价格
#求水果类型商品的最大值
SELECT MAX(price) FROM leibie INNER JOIN goods ON l_id=leibie.id WHERE leibie.name="水果";#2

SELECT * FROM goods WHERE price>(SELECT MAX(price) FROM leibie INNER JOIN goods ON l_id=leibie.id WHERE leibie.name="水果");