数据库基本知识和基础sql语句.docx

1、数据库基本知识和基础sql语句数据库的开展历程没有数据库，使用磁盘文件存储数据；层次构造模型数据库；网状构造模型数据库；关系构造模型数据库：使用二维表格来存储数据；关系-对象模型数据库；理解数据库RDBMS = 管理员manager+仓库databasedatabase = N个tabletable：表构造：定义表的列名和列类型！表记录：一行一行的记录！Mysql安装目录:bin目录中都是可执行文件；my.ini文件是MySQL的配置文件；相关命令:启动：net start mysql；关闭：net stop mysql；mysql -u root -p 123 -h localhost；-u

2、：后面的root是用户名，这里使用的是超级管理员root；-p：后面的123是密码，这是在安装MySQL时就已经指定的密码；退出：quit或exit；sql语句语法要求SQL语句可以单行或多行书写，以分号结尾；可以用空格和缩进来来增强语句的可读性；关键字不区别大小写，建议使用大写；分类DDLData Definition Language：数据定义语言，用来定义数据库对象：库、表、列等；DMLData Manipulation Language：数据操作语言，用来定义数据库记录数据；根本操作查看所有数据库名称：SHOW DATABASES；切换数据库：USE mydb1，切换到mydb1数据库

3、；创立数据库：CREATE DATABASE IF NOT EXISTS mydb1；修改数据库编码：ALTER DATABASE mydb1 CHARACTER SET utf8创立表：CREATE TABLE 表名(列名列类型,列名列类型,.);查看当前数据库中所有表名称：SHOW TABLES；查看指定表的创立语句：SHOW CREATE TABLE emp，查看emp表的创立语句；查看表构造：DESC emp，查看emp表构造；删除表：DROP TABLE emp，删除emp表；修改表：1.修改之添加列：给stu表添加classname列：ALTER TABLE stu ADD (cl

4、assname varchar(100);2.修改之修改列类型：修改stu表的gender列类型为CHAR(2)：ALTER TABLE stu MODIFY gender CHAR(2);3.修改之修改列名：修改stu表的gender列名为sex：ALTER TABLE stu change gender sex CHAR(2);4.修改之删除列：删除stu表的classname列：ALTER TABLE stu DROP classname;5.修改之修改表名称：修改stu表名称为student：ALTER TABLE stu RENAME TO student;其他常用命令:mysql根

5、本操作命令一、数据库操作1.新增数据库create database 数据库名字 数据库选项;数据库选项：规定数据库部该用什么进展规字符集：charset 具体字符集(utf8)校对集：collate 具体校对集依赖字符集 2.查看数据库2.1查看所有的数据库show databases;匹配查询：show databases like pattern; *pattern可以使用通配符_：下划线匹配，表示匹配单个任意字符，如：_s，表示任意字符开场，但是以s结尾的数据库%：百分号匹配，表示匹配任意个数的任意字符，如：student%，表示以student开场的所有数据库2.2查看数据库的创立语

6、句show create database 数据库名字; 3.修改数据库数据库名字在mysql高版本中不允许修改，所以只能修改数据库的库选项字符集和校对集alter database 数据库名字 数据库选项;eg:alter database stu charset utf8; 4.删除数据库对于数据库的删除要慎重考虑，是不可逆的。drop database 数据库名字; 4.选择数据库use 数据库名字;二、数据表操作字段 1.新增数据表create table 表名(字段名1 数据类型 ment 备注.,字段名2 数据类型 ment 备注.,. *最后一行不需要逗号)表选项;表选项：1字符

7、集：charset/character set可以不写，默认采用数据库的 2校对集：collate3存储引擎：engine = innodb默认的：存储文件的格式数据如何存储注意：创立数据表的时候，需要指定要在哪个数据库下创立。创立方式有隐式创立和显式创立 1)显式创立：create table 数据库名字.数据表名字 2)隐式创立：use 数据库名字; 2.查看数据表 2.1查看所有的数据表show tables; 2.2查看表使用匹配查询Show tables like pattern;*与数据库的pattern一样：_和%两个通配符 2.3查看数据表的创立语句show create ta

8、ble 数据表名字; 2.4查看数据表的构造desc 数据表名字; 3.修改数据表 3.1修改表名字rename table 旧表名 to 新表名; 3.2修改表选项存储引擎，字符集和校对集alter table 表名 表选项; 3.3修改字段新增字段，修改字段名字,修改西段类型,删除字段新增字段：alter table 表名 add column 字段名字数据库类型 位置first/after;位置选项：first 在第一个字段 after 在某个字段之后，默认就是在最后一个字段后面修改字段名称：alter table 表名 change 旧字段名字新字段名字字段数据类型 位置; eg:al

9、ter table student name fullname varchar(30) after id;修改字段的数据类型：alter table 表名 modify 字段名字数据类型 位置;删除字段：alter table 表名 drop 字段名字;4.删除数据表drop table 表名;三、数据操作1. 新增数据inser into table 表名 (字段列表) values 值列表);2.查看数据select */字段列表 from 表名 where条件;3.修改数据update 表名 set 字段名 = 值 where 条件;注意：使用update操作最好配合limit 1使用,

10、防止操作大批量数据更新错误.4.删除数据delete from 表名 where 条件;注意：没有where 条件就是默认删除全部数据.四、列属性字段1.删除主键：alter table 表名 drop primary key;2.增加主键：alter table 表名 add primary key(字段列表);*可以是复合主键3.删除自增长：只能通过修改字段属性的方法操作.4.删除唯一键：alter table 表名 drop index 索引名字;*默认的唯一键名字就是字段的本身5.增加唯一键：alter table 表名 add unique key (字段列表);*可以是复合唯一索引

11、五、外键约束1.创立表的时候增加外键constraint 外键名字 foreign key(外键字段) references 父表主键字段;eg:- 创立父表班级表create table class(id int primary key auto_increment,name varchar(10) not null ment 班级名字,room varchar(10) not null ment 教室号)charset utf8;- 创立子表外键表create table student(id int primary key auto_increment,number char(10) n

12、ot null unique ment 学号：itcast + 四位数,name varchar(10) not null ment XX,c_id int ment 班级ID,- 增加外键foreign key(c_id) references class(id)charset utf8;2.创立表之后增加外键alter table 表名 add constraint 外键名字 foreign key(外键字段) references 父表(主键字段);eg:- 增加外键alter table student add constraint student_class_fkforeign ke

13、y(c_id) references class(id);3.删除外键alter table 表名 drop foreign key 外键名字; *查看外键名字需要通过表创立语句来查询.eg:- 删除外键alter table student drop foreign key student_ibfk_1;数据查询语法DQLDQL就是数据查询语言，数据库执行DQL语句不会对数据进展改变，而是让数据库发送结果集给客户端。SELECT selection_list /*要查询的列名称*/ FROM table_list /*要查询的表名称*/ WHERE condition /*行条件*/ GRO

14、UP BY grouping_columns /*对结果分组*/ HAVING condition /*分组后的行条件*/ ORDER BY sorting_columns /*对结果分组*/ LIMIT offset_start, row_count /*结果限定*/根底查询1.1查询所有列SELECT * FROM stu;1.2查询指定列SELECT sid, sname, age FROM stu;2条件查询2.1条件查询介绍条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字：=、!=、=；BETWEENAND；IN(set)；IS NULL；AND；

15、OR；NOT；2.2查询性别为女，并且年龄50的记录SELECT * FROM stu WHERE gender=female AND ge=20 AND age=40;或者SELECT * FROM stu WHERE age BETWEEN 20 AND 40;2.8查询性别非男的学生记录SELECT * FROM stuWHERE gender!=male;或者SELECT * FROM stuWHERE gendermale;或者SELECT * FROM stuWHERE NOT gender=male;2.9查询XX不为null的学生记录SELECT * FROM stuWHERE

16、 NOT sname IS NULL;或者SELECT * FROM stuWHERE sname IS NOT NULL;3模糊查询当想查询XX中包含a字母的学生时就需要使用模糊查询了。模糊查询需要使用关键字LIKE。3.1查询XX由5个字母构成的学生记录SELECT * FROM stuWHERE sname LIKE _;模糊查询必须使用LIKE关键字。其中“_匹配任意一个字母，5个“_表示5个任意字母。3.2查询XX由5个字母构成，并且第5个字母为“i的学生记录SELECT * FROM stuWHERE sname LIKE _i;3.3查询XX以“z开头的学生记录SELECT *

17、FROM stuWHERE sname LIKE z%;其中“%匹配0n个任何字母。3.4查询XX中第2个字母为“i的学生记录SELECT * FROM stuWHERE sname LIKE _i%;3.5查询XX中包含“a字母的学生记录SELECT * FROM stuWHERE sname LIKE %a%;4字段控制查询4.1去除重复记录去除重复记录两行或两行以上记录中系列的上的数据都一样，例如emp表中sal字段就存在一样的记录。当只查询emp表的sal字段时，那么会出现重复记录，那么想去除重复记录，需要使用DISTINCT：SELECT DISTINCT sal FROM emp;

18、4.2查看雇员的月薪与佣金之和因为sal和m两列的类型都是数值类型，所以可以做加运算。如果sal或m中有一个字段不是数值类型，那么会出错。SELECT *,sal+m FROM emp;m列有很多记录的值为NULL，因为任何东西与NULL相加结果还是NULL，所以结算结果可能会出现NULL。下面使用了把NULL转换成数值0的函数IFNULL：SELECT *,sal+IFNULL(m,0) FROM emp;4.3给列名添加别名在上面查询中出现列名为sal+IFNULL(m,0)，这很不美观，现在我们给这一列给出一个别名，为total：SELECT *, sal+IFNULL(m,0) AS

19、total FROM emp;给列起别名时，是可以省略AS关键字的：SELECT *,sal+IFNULL(m,0) total FROM emp;5排序5.1查询所有学生记录，按年龄升序排序SELECT *FROM stuORDER BY sage ASC;或者SELECT *FROM stuORDER BY sage;5.2查询所有学生记录，按年龄降序排序SELECT *FROM stuORDER BY age DESC;5.3查询所有雇员，按月薪降序排序，如果月薪一样时，按编号升序排序SELECT * FROM empORDER BY sal DESC,empno ASC;6聚合函数聚合

20、函数是用来做纵向运算的函数：COUNT()：统计指定列不为NULL的记录行数；MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；6.1COUNT当需要纵向统计时可以使用COUNT()。查询emp表中记录数：SELECT COUNT(*) AS t FROM emp;查询emp表中有佣金的人数：SELECT COUNT(m) t FR

21、OM emp;注意，因为count()函数中给出的是m列，那么只统计m列非NULL的行数。查询emp表中月薪大于2500的人数：SELECT COUNT(*) FROM empWHERE sal 2500;统计月薪与佣金之和大于2500元的人数：SELECT COUNT(*) AS t FROM emp WHERE sal+IFNULL(m,0) 2500;查询有佣金的人数，以及有领导的人数：SELECT COUNT(m), COUNT(mgr) FROM emp;6.2SUM和AVG当需要纵向求和时使用sum()函数。查询所有雇员月薪和：SELECT SUM(sal) FROM emp;查询

22、所有雇员月薪和，以及所有雇员佣金和：SELECT SUM(sal), SUM(m) FROM emp;查询所有雇员月薪+佣金和：SELECT SUM(sal+IFNULL(m,0) FROM emp;统计所有员工平均工资：SELECT SUM(sal), COUNT(sal) FROM emp;或者SELECT AVG(sal) FROM emp;6.3MAX和MIN查询最高工资和最低工资：SELECT MAX(sal), MIN(sal) FROM emp;分组查询当需要分组查询时需要使用GROUP BY子句，例如查询每个部门的工资和，这说明要使用局部来分组。7.1分组查询查询每个部门的部门

23、编号和每个部门的工资和：SELECT deptno, SUM(sal)FROM empGROUP BY deptno;查询每个部门的部门编号以及每个部门的人数：SELECT deptno,COUNT(*)FROM empGROUP BY deptno;查询每个部门的部门编号以及每个部门工资大于1500的人数：SELECT deptno,COUNT(*)FROM empWHERE sal1500GROUP BY deptno;HAVING子句查询工资总和大于9000的部门编号以及工资和：SELECT deptno, SUM(sal)FROM empGROUP BY deptnoHAVING SU

24、M(sal) 9000;注意，WHERE是对分组前记录的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分组；而HAVING是对分组后数据的约束。8LIMITLIMIT用来限定查询结果的起始行，以及总行数。8.1查询5行记录，起始行从0开场SELECT * FROM emp LIMIT 0, 5;注意，起始行从0开场，即第一行开场！8.2查询10行记录，起始行从3开场SELECT * FROM emp LIMIT 3, 10;8.3分页查询如果一页记录为10条，希望查看第3页记录应该怎么查呢.第一页记录起始行为0，一共查询10行；第二页记录起始行为10，一共查询10行；第三

25、页记录起始行为20，一共查询10行；多表连接查询连接查询连接外连接左外连接右外连接全外连接MySQL不支持自然连接子查询连接查询连接查询就是求出多个表的乘积，例如t1连接t2，那么查询出的结果就是t1*t2。连接查询会产生笛卡尔积，假设集合A=a,b，集合B=0,1,2，那么两个集合的笛卡尔积为(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)。可以扩展到多个集合的情况。那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢，当然是通过条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。2.1连接上面的连接语句就是连接，但

26、它不是SQL标准中的查询方式，可以理解为方言！SQL标准的连接为：SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;连接的特点：查询结果必须满足条件。例如我们向emp表中插入一条记录：其中deptno为50，而在dept表中只有10、20、30、40部门，那么上面的查询结果中就不会出现“三这条记录，因为它不能满足e.deptno=d.deptno这个条件。2.2外连接左连接、右连接外连接的特点：查询出的结果存在不满足条件的可能。左连接：SELECT * FROM emp e LEFT OUTER JOIN dept d ON

27、e.deptno=d.deptno;左连接是先查询出左表即以左表为主，然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL。子查询子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了。子查询出现的位置：where后，作为条件的一局部；from后，作为被查询的一条表；当子查询出现在where后作为条件时，还可以使用如下关键字：anyall子查询结果集的形式：单行单列用于条件单行多列用于条件多行单列用于条件多行多列用于表练习：1.工资高于smith的员工。分析：查询条件：工资smith工资，其中smith工资需要一条子查询。第一步：查询smith的工资SELECT sal FROM emp WHERE ename=smith第二步：查询高于smith工资的员工SELECT * FROM emp WHERE sal ($第一步)结果：SELECT * FROM emp WHERE sal (SELECT sal FROM emp WHERE ename=smith)子查询作为条件子查询形式为单行单列2.工资高于30部门所有人的员工信息分析：查询条件：工资高于30部门所有人工资，其中30部门所有人工资是子查询。高于所有需要使用all关键字