SQL执行顺序
SELECT 查询列表 ⑦
FROM 表1 别名 ①
连接类型 join 表2 ②
ON 连接条件 3
WHERE 筛选 ④
GROUP BY 分组列表 5
HAVING 筛选 ⑥
ORDER BY 排序列表 ⑧
LIMIT 起始索引, 条目数 ⑨
多表更新
update user u,left join order o on u. id = o. id set u. address = 'Chian' , o. paymod = '支付宝' ;
多表删除【级联删除】
delete u, o from user u left join order o on u. id = o. id where u. address = 'China' ;
delete truncate drop
delete from table_name;
truncate table table_name;
drop table table_name;
区别:
TRUNCATE ,表和索引所占用的空间会恢复到初始大小,保留表,删除所有数据
DELETE 操作不会减少表或索引所占用的空间,只删除数据
DROP 语句将表所占用的空间全释放掉,表结构等一并摧毁
效率:
drop > truncate > delete
DDL语法
CREATE DATABASE IF NOT EXISTS datamange;
ALTER DATABASE datamange CHARACTER SET gbk;
DROP DATABASE IF EXISTS datamange;
USE datamange;
CREATE TABLE ` user ` (
` id` int ( 11 ) NOT NULL AUTO_INCREMENT COMMENT 'Id' ,
` wx_id` varchar ( 64 ) NOT NULL DEFAULT '' COMMENT '微信id' ,
` wx_nickname` varchar ( 64 ) NOT NULL DEFAULT '' COMMENT '微信昵称' ,
` roles` varchar ( 100 ) NOT NULL DEFAULT '' COMMENT '角色' ,
` avatar_url` varchar ( 255 ) NOT NULL DEFAULT '' COMMENT '头像地址' ,
` create_time` datetime NOT NULL COMMENT '创建时间' ,
` update_time` datetime NOT NULL COMMENT '修改时间' ,
` bonus` int ( 11 ) NOT NULL DEFAULT '300' COMMENT '积分' ,
PRIMARY KEY ( ` id` )
) ENGINE = InnoDB AUTO_INCREMENT = 2 DEFAULT CHARSET = utf8 COMMENT = '分享' ;
CREATE TABLE user_copy LIKE user ;
CREATE TABLE user_copy_data SELECT * from ` user ` ;
ALTER TABLE ` user ` MODIFY COLUMN create_time ` TIMESTAMP ` ;
ALTER TABLE ` user ` ADD COLUMN order_id VARCHAR ( 32 ) ;
ALTER TABLE ` user ` CHANGE COLUMN order_id order_ids ` TIMESTAMP ` ;
ALTER TABLE ` user ` DROP COLUMN order_ids;
ALTER TABLE ` user ` RENAME TO users;
DESC ` user ` ;
CREATE TABLE IF NOT EXISTS ` user ` (
id INT PRIMARY KEY ,
user_name VARCHAR ( 20 ) NOT NULL ,
sex CHAR ( 1 ) ,
age INT DEFAULT 20 ,
nick_name VARCHAR ( 32 ) UNIQUE ,
order_id VARCHAR ( 32 ) ,
CONSTRAINT fk_user_major FOREIGN KEY ( order_id) REFERENCES major( id)
)
TCL语言 transaction Control Language
SET AUTOCOMMIT= 0 ;
视图
CREATE VIEW v AS SELECT * FROM ` user ` ;
SELECT * from v;
CREATE OR REPLACE VIEW v AS SELECT * FROM ` user `
ALTER VIEW v AS SELECT * FROM ` user `
DROP VIEW 视图1 , 视图2 ;