1、数据库操作

1.创建数据库

  1. --CREATE DATABASE 数据库名称;
  2. CREATE DATABASE test;

2.查看数据库

1)查看已经存在的数据库

  1. SHOW DATABASES;

2)查看创建好的数据库test

  1. --SHOW CREATE DATABASE 数据库名称;
  2. SHOW CREATE DATABASE test;

3.修改数据库

  1. --ALTER DATABASE test DEFAULT CHARACTER SET 编码方式 COLLATE 编码方式_bin
  2. ALTER DATABASE test DEFAULT CHARACTER SET UTF8 COLLATE UTF8_bin

4.删除数据库

  1. --DROP DATABASE 数据库名称;
  2. DROP DATABASE test;

2、数据类型

1.数值类型

17a87214a51e4a2d5a89354baecfa37.jpg

2.日期和时间类型

f608b7d696232bb7b05e6dcce5e7243.jpg

3、数据表的基本操作

1.创建数据表

  1. USE test;
  2. CREATE TABLE teacher
  3. (
  4.     id INT(5),
  5.     name VARCHAR(10),
  6.     email VARCHAR(50)
  7. );
  8. --使用数据库
  9. --CREATE TABLE 数据库名称
  10. --(
  11. --    id INT(5),
  12. --    name VARCHAR(10),
  13. --    email VARCHAR(50)
  14. --);

2.查看数据表

1）使用SHOW CREATE TABLE语句查看数据表

  1. SHOW CREATE TABLE teacher;
  2. --SHOW CREATE TABLE 数据表名称;

2）使用DESCRIBE语句查看数据表

  1. --DESCRIBE 表名
  2. --DESC 表名
  3. DESCRIBE teacher;

3.修改数据表

1）修改表名

  1. --ALTER TABLE 旧表名 RENAME [TO] 新表明
  2. ALTER TABLE teacher RENAME t_teacher;

2）修改字段名和数据类型

  1. --ALTER TABLE t_teacher 旧字段名 新字段名 新数据类型；
  2. ALTER TABLE t_teacher CHANGE id workid INT(5);

3）修改字段的数据类型

  1. --ALTER TABLE 表名 MODIFY 字段名 新数据类型;
  2. ALTER TABLE t_teacher MODIFY workid TINYINF;

4）添加字段

  1. --ALTER TABLE 表名 ADD 新字段名 数据类型[约束条件] [FIRST|AFTER 已经存在的字段名];
  2. ALTER TABLE t_teacher ADD age INT(5);

5）删除字段

  1. --ALTER TABLE 表名 DROP 字段名；
  2. ALTER TABLE t_teacher DROP email;

4.删除数据表

  1. --DROP TABLE 表名；
  2. DROP TABLE t_teacher;

4、数据库表的约束

4.1 主键约束

单字段主键

1）创建表时指定主键
  1. CREATE TABLE company
  2. (
  3.   --字段名 数据类型 PRIMARY KEY；
  4.   company_id INT(11) PRIMARY KEY,
  5.   company_name VARCAHR(50)
  6. );

2）添加主键约束
  1. ALTER TABLE 表名 MODIFY 字段名 数据类型 PRIMARY KEY;
  2. ALTER TABLE t_teacher MODIFY id INT(5) PRIMARY KEY;

3）删除主键约束
  1. --ALTER TABLE 表名 DROP PRIMARY KEY
  2. ALTER TABLE t_teacher DROP PRIMARY KEY;

复合主键

1）创建表时指定复合主键
  1. --PRIMARY KEY(字段名1, 字段名2,···,字段名3);
  2. CREATE TABLE sales
  3. (
  4.   product_id INT(11),
  5.   region_code VARCHAR(10),
  6.   quantity INT(11),
  7.   price FLOAT,
  8.   PRIMARY KEY(product_id,region_code)
  9. );

2）为已存在的表添加复合主键
  1. --ALTER TABLE 表名 ADD PRIMARY KEY(字段名1，字段名2，字段名3);
  2. DPOR TABLE sales;
  3. CREATE TABLE sales
  4. (
  5.   product_id INT(11),
  6.   region_code VARCHAR(10),
  7.   quantity INT(11),
  8.   price FLOAT
  9. );
  10. ALTER TABLE sales ADD PRIMARY KEY(product_id,region_code);

3）删除复合主键约束
  1.  ALTER TABLE 表名 DROP PRIMARY KEY;

4.2 外键约束

FOREIGN KEY

  1. CONSTRAINT 外键名 FOREIGN KEY(从表的外键字段名) REFERENCES 主表名(主表的主键字段名);
  2. CONSTRAINT fk_emp_dept FOREIGN KEY(dept_id) REFERENCES dept(id);

4.3 唯一约束

UNIQUE；

4.4 默认约束

  1. --1.创建表时添加默认约束
  2. 字段名 数据类型 DEFAULT 默认值;
  3. company_tel VARCHAR(20) DEFAULT '0371-';
  5. --2.为已存在的表添加默认约束
  6. --ALTER TABLE 表名 MODIFY 字段名 新数据类型 DEFAULT 默认值;
  7. ALTER TABLE company MODIFY company_tel VARCHAR(20) DEFAULT '0371-'
  9. --3.删除默认约束
  10. ALTER TABLE 表名 MODIFY 字段名 数据类型；
  11. ALTER TABLE company MODIFY company_tel VARCHAR(20);

4.5 设置表的字段自动增加

AUTO_INCREMENT

  1. --1.创建表时指定字段值自动增加
  2. --字段名 数据类型 PRIMARY KEY AUTO_INCREMENT
  4. --2.为已存在的表设置字段值自动增加
  5. --ALTER TABLE 表名 MODIFY 字段名 新数据类型 AUTO_INCREMENT;
  6. ALTER TABLE company MODIFY company_id INT(11) AUTO_INCREMENT;
  8. --3.删除字段值的自动增加
  9. --ALTER TABLE 表名 MODIFY 字段名 数据类型;
  10. ALTER TABLE company MODIFY company_id INT(11);

5、数据表记录的更新操作

1. 插入

  1. --插入单条数据
  2. INSERT INTO studentinfo(sno,sname,sgender,sbirth) VALUES ('10101001','张三丰','男','2000-10-20');
  5. --插入多条数据
  6. INSERT INTO studentinfo
  7.         VALUES ('10102001','王二','男','1999-10-10'),
  8.                ('10102002','李三','女','1999-12-12');

2. 修改

  1. --将sclass字段下的值全部改为了就三家
  2. UPDATE studentinfo SET sclass='计算机';
  4. --将张三的改为计算机
  5. UPDATE studentinfo SET sclass='计算机' WHERE sname='张三'

3. 删除

  1. --只删除张三
  2. DELETE FROM studentinfo where sname='张三';
  4. --删除表全部信息
  5. DELETE FROM studentinfo

4. 使用TRUNCATE清空数据

  1. TRUECATE TABLE studentinfo;
  2. --执行效果与DELETE一样

5. 查询

5.1 SELECT

  1. --1.查询数据表中所有信息
  2. SELECT * FROM studentinfo
  4. --2.根据字段查询数据表字段信息
  5. SELECT sno,snamme,sgender,sbirth FROM studentinfo
  7. --3.设置别名查找信息
  8. SELECT sno 学生学号,sname AS 学生姓名 FROM studentinfo;

5.2 WHERE 设置查询条件

  1. --1. 设置sclass='软件工程101'查询条件
  2. SELECT * FROM studentinfo WHERE sclass='软件工程101';
  4. --2. 设置范围查询条件
  5. SELECT * FROM studentinfo WHERE sbirth BETWEEN '2000-1-1' AND '2000-12-31';
  7. --3.列表运算符的使用
  8. SELECT * FROM studentinfo WHERE sno IN('101010001','10102001','11101001');
  10. --4.模式匹配运算符
  11. SELECT * FROM studentinfo WHERE sname LIKE '张%';
  13. --5.空值判断运算符
  14. SELECT * FROM studentinfo WHERE score IS NULL;
  16. --6.逻辑运算符AND,OR,NOT
  17. SELECT * FROM studentinfo WHERE sname LIKE '王%' AND sclass='电子商务';
  18. SELECT * FROM studentinfo WHERE sname LIKE '王%' OR sclass='电子商务'
  19. --查询不是1992年的
  20. SELECT *FROM studentinfo WHERE NOT(YEAR(sbirth)=1992);

5.3 ORDER BY 排序

ASC是升序（默认），DESC是降序

  1. SELECT * 
  2. FROM elective 
  3. WHERE cno='coo1'
  4. ORDER BY score DESC;

5.4 LIMIT限制

  1. --1.输出前三条学生记录的信息，显示前3行
  2. SELECT *
  3. FROM studentinfo
  4. LIMIT 3;
  5. --2.显示第5行的一条数据
  6. SELECT *
  7. FROM studentinfo
  8. LIMIT 4,1

5.5 统计查询

5.5.1 集合查询

82665626fa56e7c5d9ae17d7eaa77bc.jpg

  1. --1.查询学生总人数
  2. SELECT COUNT(*) As 学生总人数
  3. FROM studentinfo
  5. --
  6. SELECT COUNT(*) AS 学生人数,
  7. SUM(score) AS 总成绩, 
  8. AVG(score) 平均分, 
  9. MIN(score), MAX(score)
  10. FROM elective
  11. WHERE cno='c003'

5.5.2 使用GROUP BY子句
  1. --1.查询studentinfo表，分别统计男女生人数
  2. SELECT sgender,COUNT(*) AS 人数
  3. FROM studentinfo
  4. GROUP BY sgender;
  6. --2.查询elective表，统计并输出每个学生所选课程数目及平均分
  7. SELECT sno, COUNT(cno) AS 选修课程数目,AVG(score) AS 平均分
  8. FROM elective
  9. GROUP BY sno;
  11. --3.查询elective表，统计并输出每门课程所选学生人数及最高分
  12. SELECT cno COUNT(sno) AS 所选人学生人数, MAX(score) AS 最高分
  13. FROM elective
  14. GROUP BY cno;
  17. --4.查询elective表中每门课成绩都在70~90分的学生的学号
  18. SELECT sno AS 每门课成绩都在70~90分的学生的学号
  19. FROM elective
  20. GROUP BY sno
  21. HAVING MIN(score)>=70 AND MAX(score)<=90;
  24. --5.查询至少选修了三门课程的学生的学号
  25. SELECT sno ,COUNT(*) 选修课程数
  26. FROM elective
  27. GROUP BY sno
  28. HAVING COUNT(*)>3;

5.6 多表查询

82665626fa56e7c5d9ae17d7eaa77bc.jpg

5.6.1 交叉连接
  1. --1.对course表和teacher表进行交叉连接，观察交叉连接后的结果集
  2. SELECT * 
  3. FROM cource CROSS JOIN teacher;

5.6.2 内连接
  1. SELECT s.sno, sname, cno score
  2. FROM studentinfo AS s JOIN elective AS e ON s.sno = e.sno
  3. WHERE score<60;
  5. SELECT s.sno, sname, cno, socre
  6. FROM studentinfo AS s, elective AS e
  7. WHERE s.sno = e.sno AND score<60;

5.6.3 外连接
  1. SELECT *
  2. FROM teacher AS t LEFT JOIN cource AS c ON t.tno=c.ctno;