数据库基础语法

目录

一.数据库的基本操作

二.数据库增删改查(必会)

三.常用关键字

---

一.数据库的基本操作

#创建数据库并指定编码
CREATE DATABASE test1 CHARACTER SET 'GBK'

#查看数据库
SHOW CREATE DATABASE test1;
#显示所有的数据库
SHOW DATABASES

#修改数据库的编码
ALTER DATABASE test1 CHARACTER SET 'utf8';

#创建数据库表
/*
CREATE TABLE 表名(
  字段名1  类型(长度) 约束,
    字段名2  类型(长度) 约束
    );
*/
#指定数据库
USE test1;

CREATE TABLE person(
  id INT,
    name VARCHAR(20)
);

#查看表
SHOW TABLES
#查看指定表
SHOW CREATE TABLE person;

#查看表的详细信息
DESCRIBE person;
#修改表名
#RENAME TABLE 旧表名 TO 新表名;
RENAME TABLE person TO newperson;

#修改字段名
#ALTER TABLE newperson CHANGE 旧字段名 新字段名 类型(长度) 约束;
ALTER TABLE newperson CHANGE name newname VARCHAR(30);
#添加字段
#ALTER TABLE 表名 ADD 字段名 类型(长度) 约束;
ALTER TABLE newperson ADD birthday DATE;
#删除字段
ALTER TABLE newperson DROP birthday;
#删除表
DROP TABLE newperson;

二.数据库增删改查(必会)

          1.插入语句

             语法1:INSERT INTO 表名(列名1,列名2) VALUES(值1,值2);

             语法2:INSERT INTO 表名 VALUES(值1,值2);

         表的约束(维护数据的完整性):

#主键约束:给列唯一标识(主键的值不能为空,不能重复)
#PRIMARY KEY
CREATE TABLE student(
  id INT PRIMARY KEY,
    name VARCHAR(20)
);
#非空约束:添加了非空的列,该列不能为空
#NOT NULL
CREATE TABLE student1(
  id INT PRIMARY KEY,
    name VARCHAR(20) NOT NULL
);
#唯一约束:和主键约束相似
CREATE TABLE student2(
  id INT PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    number INT UNIQUE
);

#设置字段自动增长 auto_increment
CREATE TABLE student3(
  id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL
);
#给定起始值
CREATE TABLE student4(
  id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)
)auto_increment=100;
         

 2.查询语句

#准备测试数据
CREATE TABLE student(
  id INT PRIMARY KEY,
    name VARCHAR(20),
    age INT
);
INSERT INTO student VALUES(1,'yyy',18);
INSERT INTO student VALUES(2,'wtc',19);
INSERT INTO student VALUES(3,'糖糖',18);
INSERT INTO student VALUES(4,'果果',23);
INSERT INTO student VALUES(5,'猫猫',14);
INSERT INTO student VALUES(6,'糖糖',18);
#查询所有数据
SELECT * FROM student;
#查询指定的数据
SELECT id FROM student;
/*
  *:通配符
*/
#条件查询
#查询student表中id=2的学生姓名
SELECT name FROM student WHERE id=2;
#IN关键字
#查询 id的值为1,2的数据
SELECT * FROM student WHERE id IN(1,2);
#查询 id的值除了为1,2的数据
SELECT * FROM student WHERE id NOT IN(1,2);
#查询 id的值为1,2的数据
SELECT * FROM student WHERE id IN(1,2);
#查询 id的值为2-4之间的数据
SELECT * FROM student WHERE id BETWEEN 2 AND 4;
#查询 id的值不为2-4之间的数据
SELECT * FROM student WHERE id NOT BETWEEN 2 AND 4;
#空值查询
#查询student表里age为空的数据;
SELECT * FROM student WHERE age IS NULL;
#查询student表里age不为空的数据;
SELECT * FROM student WHERE age IS NOT NULL;
       

  3.修改语句

#更新
UPDATE 表名
SET
列名1=值1,
列名2=值2
WHERE 列名=条件值;
UPDATE student SET name='喵喵'WHERE id=2;
#更新某个表中id字段小于三的记录,将age都更新为20
UPDATE student SET age=100 WHERE id<2;
#更新表中的所有记录,将年龄全都改为19
UPDATE student SET age=19;

          4.删除语句

#删除
#DELETE FROM 表名 WHERE 条件
#删除id=1的数据
DELETE FROM student WHERE id=1;
#删除表中的所有数据,会先延续使用之前的自动增长
DELETE FROM student;

三.常用关键字

#去除重复的值
#查询name不重复的某列
SELECT DISTINCT name FROM student;
#查询不重复的某行(全部一样才是重复)
SELECT DISTINCT id,name,age FROM student;

#LIKE关键字
#查询student表中以y开头的name值
SELECT * FROM student WHERE `name` LIKE 'y%';
#查询student表中以y开头w结尾的name值
SELECT * FROM student WHERE `name` LIKE 'y%w';
#查询student表中name值包含w字符的值
SELECT * FROM student WHERE `name` LIKE '%w%';
#查询student表中name值以yy开始,ww结束,并且中间只能有两个字符的记录
#_一个下划线代表一个字符
SELECT * FROM student WHERE `name` LIKE 'yy%__ww';

#AND关键字
#查询student表中id字段值小于5并且age为18的学生信息
SELECT * FROM student WHERE id<5 AND age=18;

#OR关键字
#查询student表中id字段值小于5或者age为18的学生信息
SELECT * FROM student WHERE id<5 OR age=18;

#GROUP BY关键字

#Group by根据一个或多个列对结果集进行分组，例如聚合函数 (比如 SUM) 常常需要添加 Group by语句用于分组。

#HAVING关键字

#用来过滤由GROUP BY语句返回的记录集。HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足。

#排序
# asc升序
# desc 降序
#按年龄升序排序
SELECT * FROM student ORDER BY age ASC;

注意：mysql中的bolean类型为tinyint.长度为1，存储格式为1或0；

聚合函数

#统计数据的行数COUNT
SELECT COUNT(*) FROM student;
#SUM求和
SELECT SUM(id) FROM student;
#AVG求平均值
SELECT AVG(age) FROM student;
#max min 求最大最小值
SELECT MAX(age) FROM student;
SELECT MIN(age) FROM student;   

四.相关练习

#【例2-11】查询表的全部数据。
SELECT ALL * FROM dept;
#【例2-12】查询dept表的部门编号deptno和部门名称dname信息。
SELECT deptno,dname FROM dept;
#【例2-13】查询雇员表emp中各部门的职务信息。
SELECT job FROM emp;
#【例2-15】查询Mike的雇佣日期。
SELECT ename,hiredate FROM emp WHERE ename='Mike';
#【例2-16】查询emp表中在部门10工作的、工资高于1000或岗位是Draw的所有雇员的姓名、工资、岗位的信息。
SELECT ename,sal,Job FROM emp WHERE deptno=10 AND (sal>1000 OR Job='Draw');
#【例2-18】查询emp表中所以姓名以A开头或姓名第2个字母为C的员式的姓名、部门号及工资的信息。
SELECT ename,deptno,sal FROM emp WHERE (ename LIKE 'A%') OR (ename LIKE '_C%');
#【例2-20】查询emp表中部门10和3中的，岗位是CLERK的所有雇员的部门号、姓名、工资的信息。
SELECT deptno,ename,sal FROM emp WHERE Job='Draw' AND (deptno=10 OR deptno=3); 
#【例2-21】以部门号的降序、姓名升序，查询emp表中工资在2000~3000员工的部门号、姓名、工资、补助信息。
SELECT deptno,ename,sal,supply FROM emp WHERE sal BETWEEN 2000 AND 3000 ORDER BY deptno DESC,ename;
#【例2-22】使用列的别名、列的位置进行排序，改写2-21的例子。
SELECT deptno AS 员工号,ename AS 员工姓名,sal AS 工资,supply AS 补助金 FROM emp WHERE sal BETWEEN 2000 AND 3000 ORDER BY deptno DESC,ename;
#【例2-23】  统计deptno为30的部门的平均工资、总补助款，总人数，补助人数，最高工资和最低工资。
SELECT AVG(sal),SUM(subsidy),COUNT(*),SUM(supply),MAX(sal),MIN(sal) FROM emp WHERE deptno=30
#【例2-24】查询emp表中每个部门的平均工资和最高工资，并按部门编号升序排序。
SELECT deptno,AVG(sal),MAX(sal) FROM emp GROUP BY deptno ORDER BY deptno
#【例2-25】查询emp表中每个部门、每种岗位的平均工资和最高工资。
SELECT deptno,AVG(sal),MAX(sal) FROM emp GROUP BY deptno,Job;
#【例2-26】查询部门编号在30以下的各个部门的部门编号、平均工资，要求只显示平均工资大于等于2000的信息。
SELECT deptno,AVG(sal) FROM emp WHERE deptno<30 GROUP BY deptno HAVING AVG(sal)>4000;

#【例2-27】查询工资大于等于3000的员工的员工编号、姓名、工资、所在部门编号及部门所在地址，结果按部门编号进行排序。
SELECT empno,ename,sal,deptno FROM emp WHERE sal>=3000 ORDER BY deptno;