MySQL（1）

MySQL（1）

1.客户端访问服务器的过程？

 

2.MySQL的分类？

DDL（操作数据库和表）

DQL（查询表中的数据）

DML（增删改表中的数据）

DCL（授权）

##DDL：操作数据库和表

1.操作数据库：crud

（1）c（创建）

###创建表itms（商品表）

CREATE TABLE items (
  id INT NOT NULL  AUTO_INCREMENT,
  itemsname VARCHAR(32) NOT NULL COMMENT '商品名称',
  price FLOAT(10,1) NOT NULL COMMENT '商品定价',
  detail TEXT COMMENT '商品描述',
  pic VARCHAR(64) DEFAULT NULL COMMENT '商品图片',
  createtime DATETIME NOT NULL COMMENT '生产日期',
  PRIMARY KEY (id)
)  DEFAULT CHARSET=utf8;                    

###创建表t_user（用户表）

CREATE TABLE t_user (
  id INT NOT NULL AUTO_INCREMENT,
  username VARCHAR(32) NOT NULL COMMENT '用户名称',
  birthday DATE DEFAULT NULL COMMENT '生日',
  sex CHAR(1) DEFAULT NULL COMMENT '性别',
  address  VARCHAR(256) DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

###创建表order_itms（订单详情表）

CREATE TABLE orderdetail (
  id INT NOT NULL AUTO_INCREMENT,
 orders_id INT NOT NULL COMMENT '订单id',
  items_id INT NOT NULL COMMENT '商品id',
  items_num INT  DEFAULT NULL COMMENT '商品购买数量',
  PRIMARY KEY (id),
  KEY `FK_orderdetail_1` (`orders_id`),
  KEY `FK_orderdetail_2` (`items_id`),
  CONSTRAINT `FK_orderdetail_1` FOREIGN KEY (`orders_id`) REFERENCES `orders` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_orderdetail_2` FOREIGN KEY (`items_id`) REFERENCES `items` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
)  DEFAULT CHARSET=utf8;

###创建表orders（订单详情表）

CREATE TABLE orders (
  id INT NOT NULL AUTO_INCREMENT,
  user_id INT NOT NULL COMMENT '下单用户id',
  number VARCHAR(30) NOT NULL COMMENT '订单号',
  createtime DATETIME NOT NULL COMMENT '创建订单时间',
  note VARCHAR(100) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `FK_orders_1` (`user_id`),
  CONSTRAINT `FK_orders_id` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
)  DEFAULT CHARSET=utf8;

###插入数据

/*Data for the table `items` */

INSERT  INTO items(itemsname,price,detail,pic,createtime) VALUES 
('台式机',3000.0,'该电脑质量非常好！',NULL,'2015-07-07 13:28:53'),
('笔记本',6000.0,'笔记本性能好，质量好！',NULL,'2015-07-08 13:22:57'),
('背包',200.0,'名牌背包，容量大质量好！',NULL,'2015-07-010 13:25:02');

/*Data for the table `orderdetail` */

INSERT  INTO `orderdetail`(`orders_id`,`items_id`,`items_num`) VALUES
 (1,1,1),
 (1,2,3),
 (2,3,4),
 (3,2,3);

/*Data for the table `orders` */

INSERT  INTO `orders`(`user_id`,`number`,`createtime`,`note`) VALUES 
(1,'1000010','2015-06-04 13:22:35',NULL),
(1,'1000011','2015-07-08 13:22:41',NULL),
(2,'1000012','2015-07-17 14:13:23',NULL),
(3,'1000012','2015-07-16 18:13:23',NULL),
(4,'1000012','2015-07-15 19:13:23',NULL),
(5,'1000012','2015-07-14 17:13:23',NULL),
(6,'1000012','2015-07-13 16:13:23',NULL);

/*Data for the table `user` */

INSERT  INTO `t_user`(`username`,`birthday`,`sex`,`address`) VALUES 
('王五',NULL,'2',NULL),
('张三','2014-07-10','1','北京市'),
('张小明',NULL,'1','河南郑州'),
('陈小明',NULL,'1','河南郑州'),
('张三丰',NULL,'1','河南郑州'),
('陈小明',NULL,'1','河南郑州'),
('王五',NULL,NULL,NULL),
 ('小A','2015-06-27','2','北京'),
('小B','2015-06-27','2','北京'),
('小C','2015-06-27','1','北京'),
('小D','2015-06-27','2','北京');

（2）r（查询）

##一对一查询（查询订单，关联查询用户信息）

SELECT
    *
FROM
    orders,
    items
WHERE
    orders.user_id = items.id;

##一对多查询（查询订单(关联用户)及订单明细）

SELECT
    *
FROM
    orders,
    orderdetail,
    t_user
WHERE
    orders.id = orderdetail.orders_id
AND t_user.id = orders.user_id;

##多对多查询（查询用户以及用户购买的商品信息）

SELECT
    t1.*, t2.username,
    t2.sex,
    t2.address,
    t3.id orderdetail_id,
    t3.items_id,
    t3.items_num,
    t3.orders_id,
    t4.itemsname items_name,
    t4.detail items_detail,
    t4.price items_price
FROM
    orders t1,
    t_user t2,
    orderdetail t3,
    items t4
WHERE
    t1.user_id = t2.id
AND t3.orders_id = t1.id
AND t3.items_id = t4.id