第四天（Mysql数据库）

复习：

SQL：结构化查询语言，是一种用于增删改查数据库服务器中数据的语言。

SET NAMES UTF8;
DROP DATABASE IF EXISTS xuezi;
CREATE DATABASE xuezi CHARSET=UTF8;
USE xuezi;
CREATE TABLE xz_user(….);
INSERT INTO xz_user VALUES(…);
DELETE FROM xz_user WHERE uid=?;
UPDATE xz_user SET uname=?,upwd=? WHERE uid=?;
SELECT * FROM xz_user;

12345.6789  = 1234.56789E1            = 123.456789E2                      = 12.3456789E3                      = 1.23456789E4

MySQL中的列类型 —— 查看参考手册

数字类型：
TINYINT、SMALLINT、INT、BIGINT
FLOAT、DOUBLE、DECIMAL
字符串类型：
CHAR(255)、VARCHAR(65535)、TEXT(4G)
日期时间类型：
DATE、TIME、DATETIME
布尔类型：
BOOL

MySQL中的列约束
(1)主键约束 PRIMARY KEY
(2)非空约束 NOT NULL
(3)唯一约束 UNIQUE
(4)默认值约束 DEFAULT
(5)外键约束

今日目标：
(1)外键约束
(2)自增列
(3)简单查询 —— 重点
(4)复杂查询 —— 重点 &难点

1.主键约束和外键约束

主键：PRIMARY KEY，唯一且非空约束，是排序依据；
外键：FOREIGN KEY，可重复可为空，外键列上出现的值必须在另外一个表的主键列上出现过

外键约束的语法： references
列名 类型,
FOREIGN KEY(列名) REFERENCES 表名(列名)
提示：如果为员工表添加了一个参考部门表的外键列，增加、修改员工都要查询一下部门表，操作效率会降低。老版本的MySQL默认是不支持此约束；新版本MySQL支持。

2.MySQL数据库中专有的“自增列”

AUTO_INCREMENT，只有MySQL支持，用于实现自增列！自增列无需手工指定特定的值，只需要赋值为NULL，MySQL服务器会自动查询当前已有的最大整数，在此基础上+1.
语法： CREATE TABLE xx (
主键列名 INT PRIMARY KEY AUTO_INCREMENT,
…
);
提示：自增列只能用于主键列，且必须是整数型主键。自增列也可以手工赋值。

午间练习：
删除并重新创建数据库 xuezi；
创建保存用户信息的表 xz_user( uid, uname, upwd, phone )；
插入三个用户信息；
创建保存用户收货地址表 xz_receive_addr ( aid, rcv_name, addr, rcv_phone, user_id )；
插入六个收货地址；

SET NAMES UTF8;DROP DATABASE IF EXISTS xuezi;CREATE DATABASE xuezi CHARSET=UTF8;USE xuezi;#创建用户信息表CREATE TABLE xz_user(             uid INT PRIMARY KEY AUTO_INCREMENT,  uname VARCHAR(32) UNIQUE NOT NULL,      #注册名  upwd VARCHAR(32) NOT NULL,          #注册密码  phone VARCHAR(16) UNIQUE NOT NULL,      #注册手机号  avatar VARCHAR(128) DEFAULT 'img/1.jpg' #用户的头像);#插入3个用户的注册信息   INSERT INTO xz_user VALUES(1, 'haikuo', '123456', '13501234567', 'img/6113.jpg'),(2, 'weilan', '123456', '13501234568', 'img/1234.jpg'),(3, 'nikang', '123456', '13501234569', 'img/8245.jpg'),(4, 'balabala', '123456', '13501234570', 'img/8181.jpg');#查看所有用户数据SELECT * FROM xz_user;#创建表保存每个用户的所有收货地址CREATE TABLE xz_receive_addr (  aid INT PRIMARY KEY AUTO_INCREMENT,  rcv_name VARCHAR(6) NOT NULL, #收货人姓名  addr VARCHAR(128) NOT NULL,   #收货地址  phone VARCHAR(16),        #收货人电话  user_id INT,          #用户编号  FOREIGN KEY(user_id) REFERENCES xz_user(uid));#为每个用户创建三个收货地址INSERT INTO xz_receive_addr VALUES(NULL,'丁大壮','北京市海淀区128号','13501231412',1),(NULL,'丁二壮','北京市海淀区128号','13501231412',1),(NULL,'丁三壮','北京市海淀区128号','13501231412',1),(NULL,'丁大壮','北京市海淀区111号','13501231412',2),(NULL,'丁大壮','北京市海淀区222号','13501231412',2),(NULL,'丁大壮','北京市海淀区333号','13501231412',2),(NULL,'丁二壮','北京市海淀区128号','13501231412',3),(NULL,'丁二壮','北京市海淀区129号','13501231412',3);

3.简单查询语句

(1)只查询特定的列

语法：SELECT 列名, 列名,..列名 FROM 表名;
示例：查询所有的员工姓名及其工资
SELECT ename, salary FROM emp;
练习 : 查询所有的部门的名称；
SELECT dname FROM dept;
查询所有的员工工资、生日、姓名
SELECT salary, birthday, ename FROM emp;

(2)查询所有的列

语法：SELECT * FROM 表名;
提示：* 指代“所有列”

(3)给列取别名，以简化或者更好的说明

语法：SELECT 列名 AS 别名, 列名 AS 别名 FROM 表名;
SELECT 列名 别名, 列名 别名 FROM 表名;
示例：查询所有的员工birthday（生日）、salary（月薪）、ename（姓名）
SELECT birthday AS 生日, salary AS s, ename 姓名 FROM emp;
SELECT birthday AS ‘生日’FROM emp;

(4)只显示列上不同的值（即合并相同的值）

语法：SELECT DISTINCT 列名 FROM 表名;
示例：查看哪些部门有员工
SELECT DISTINCT dept_id FROM emp;

(5)按照指定列上的值排序

语法：SELECT … FROM …ORDER BY列名[ASC];
SELECT … FROM …ORDER BY列名DESC;
示例：查询员工的所有信息，记录行按照工资由小到大排序
SELECT * FROM emp ORDER BY salary;
SELECT * FROM emp ORDER BY salary DESC;
练习：查询员工的所有信息，按照员工姓名排序（升序）
SELECT * FROM emp ORDER BY ename;
查询员工的所有信息，按照员工姓名排序（降序）
SELECT * FROM emp ORDER BY ename DESC;
查询员工的所有信息，按照员工部门编号升序排序；若部门编号相同，再按生日降序排序
SELECT * FROM emp ORDER BY dept_id ASC, birthday DESC;

(6)查询时进行运算

示例：查询每个员工的姓名及其年薪
SELECT ename, salary*12 AS 年薪 FROM emp;
练习：公司给每人加薪500，查询出每个员工姓名、月薪、年薪
SELECT ename, salary+500 AS 月薪, (salary+500)*12 AS 年薪 FROM emp;

(7)查询出满足特定条件的记录行 —— 重要

语法：SELECT … FROM … WHERE 条件;
其中的“条件”可以是多种表达式，如：
= ><>= <= !=
示例：查询出30号部门的员工的所有信息
SELECT * FROM emp WHERE dept_id=30;
练习：查询出工资在18000及以上的员工的所有信息
SELECT * FROM emp WHERE salary>=18000;
查询出在1990-10-5日以前出生的员工所有信息
SELECT * FROM emp WHERE birthday < ‘1990-10-5’ ORDER BY birthday;
查询出不在20号部门的员工的所有信息
SELECT * FROM emp WHERE dept_id != 20;
SELECT * FROM emp WHERE dept_id <> 20;

其中的“条件”还可以是多种表达式的组合，如
AND(并且) OR(或者) NOT(非)
示例：查询出工资在10000~20000的员工的所有信息
SELECT * FROM emp WHERE (salary<=20000) AND(salary>=10000);
练习：查询出1990年10月出生的员工；
SELECT * FROM emp WHERE (birthday>=’1990-10-1’) AND (birthday<=’1990-10-31’);
SELECT * FROM emp WHERE birthday BETWEEN ‘1990-10-1’AND ‘1990-10-31’;
查询出20和30号部门的员工的所有信息
SELECT * FROM emp WHERE (dept_id=20) OR (dept_id=30);
查询出20、30、40、80号部门的员工的所有信息
SELECT * FROM emp WHERE (dept_id=20) OR (dept_id=30) OR (dept_id=40) OR (dept_id=80);
SELECT * FROM emp WHERE dept_id IN (20, 30, 40, 80);
查询出部门不在20、30、40、80范围的员工的所有信息
SELECT * FROM emp WHERE (dept_id!=20) AND (dept_id!=30) AND (dept_id!=40) AND (dept_id!=80);
SELECT * FROM emp WHERE dept_id NOT IN (20, 30, 40, 80);

课后作业：
(1)把上述“简单查询”中的示例和练习中代码删除，自己编写出需要的语句
(2)创建一个文本文件，编写SQL：
删除并重建数据库：xuezi
创建商品信息表：xz_laptop(lid, title, pic, price,spec)
创建用户信息表：xz_user(uid, uname, upwd)
创建购物车内容表：xz_shoppingcart_content(
cid 购物车内容编号
laptop_id 所够商品
buy_count 购买数量
user_id 该购物车所属的用户
)
插入适当的测试数据，并查询。

#设置后续所有SQL中的字符所用字符集SET NAMES UTF8;#丢弃数据库xuezi，如果存在的话DROP DATABASE IF EXISTS xuezi;#创建数据库xuezi，保存数据所用字符集为UTF8CREATE DATABASE xuezi CHARSET=UTF8;#进入指定数据库USE xuezi;#创建保存笔记本电脑商品的表CREATE TABLE xz_laptop(  lid INT PRIMARY KEY AUTO_INCREMENT,   #笔记本编号  title VARCHAR(128) NOT NULL,      #主标题  price DECIMAL(7,2),           #单价  pic VARCHAR(128),         #图片路径  spec VARCHAR(64)          #规格);#插入3行笔记本记录INSERT INTO xz_laptop VALUES(1,'戴尔燃700-1',4888,'img/1.jpg','13英寸 8G 128G'),(2,'联想小新-5',5888,'img/2.jpg','14英寸 8G 128G'),(3,'MacBook-3',6888,'img/3.jpg','15英寸 8G 128G');#创建用户信息表CREATE TABLE xz_user(  uid INT PRIMARY KEY AUTO_INCREMENT,   uname VARCHAR(32) NOT NULL UNIQUE,   upwd VARCHAR(32));#插入2行用户记录INSERT INTO xz_user VALUES(100, 'tom', '123'),(200, 'mary', '456');#创建购物车内容表，不需要保存商品和用户信息，只需要两个外键即可CREATE TABLE xz_shoppingcart_content(  cid INT PRIMARY KEY AUTO_INCREMENT,  laptop_id INT,        #笔记本商品编号  user_id INT,          #用户编号  buy_count INT,        #购买数量  FOREIGN KEY(laptop_id) REFERENCES xz_laptop(lid),  FOREIGN KEY(user_id) REFERENCES xz_user(uid));#插入几条购买记录       INSERT INTO xz_shoppingcart_content VALUES(NULL, 3, 100, 2),(NULL, 1, 100, 1),(NULL, 1, 200, 2),(NULL, 3, 200, 1),(NULL, 2, 200, 3);