MYSQL概念及增删改查

数据库database
数据库管理系统DBMS，数据库是通过DBMS创建和操纵的容器
结构化查询语言SQL，用来与数据库通信的语言
ACID：
原子性：事务中操作要么全成功，要么全失败
一致性：事务必须使数据库从一个一致性状态转变为另一个一致性状态
隔离性：多个并发事务要相互隔离
持久性：一个事务一旦被提交，对数据库的改变将是永久的
引擎的使用：
InnoDB是一个可靠的事务处理引擎，但不支持全文搜索
MEMORY功能等同于MyISAN，由于将数据存在内存，速度会快，适用于临时表
MyISAM是一个性能极高的引擎，支持全文搜索，不支持事务处理
查询语句：
SQL语句不区分大小写
SELECT prod_name FROM products;
select prod_name, prod_id from products;
select * from products;
只返回不同的vend_id，使用关键字distinct，该关键字应用于所有列
select DISTINCT vend_id from products;
限制结果，不多于5行
select prod_name from products LIMIT 5;
从行6（第一行为行0）开始的5行
select prod_name from products limit 6, 5;
使用限定标识
select products.pro_name from crashcourse.products;

排序(默认为升序)
select prod_name from products ORDER BY prod_name;
降序排序
select prod_id from products order by prod_price DESC;
select prod_id from products order by prod_price desc, prod_name;

添加过滤条件，只搜索价格为2.50的行
select prod_name from products WHERE prod_price = 2.50;
先使用where后使用order by
select prod_name from products where prod_price > 2.50 order by prod_price;
where子句的操作符：=, <>, !=, <, <=, >, >=, between and, is null
select prod_name from products where prod_price between 2.50 and 5.00;
is null 表示该列不包含值
select prod_name from products where prod_price is null;
使用多个where子句，用and或or连接
select prod_id, prod_namefrom products where prod_id = 1002 and prod_price is 2.50;
select prod_id, prod_namefrom products where prod_id = 1002 or prod_id = 1003;
and优先级高于or，使用多个子句时可以用圆括号限定优先级
select prod_name from products where (prod_id = 1002 or prod_id = 1003) and prod_price > 2.50;
where子句中的in操作符（效果等于or）
select prod_name from products where prod_id in (1002,1003);
where子句中的not操作符，not 可作用于in,between,exists
select prod_name from products where prod_price not between 2.50 and 5.00;
where子句中使用通配符进行过滤，使用通配符必须使用like关键字，反之也是如此；
%匹配任意字符的任意次数（不能是null）、_匹配一个字符
select prod_id from products where prod_name LIKE ‘%anvil%’;
使用正则表达式，用REGEXP替代
select prod_name from products where prod_name REGEXP ‘1000’ order by prod_name;
正则表达式中.匹配任一一个字符，|为or操作，[123]匹配中括号中的一个字符，[0-9]匹配0-9范围中的字符，使用\前导可匹配特殊字符，如\|可匹配字符|，
使用特定字段显示输出
select CONCAT(vend_name, ‘ (’, vend_country,’)’ ) from vendors;
rtrim可去掉右侧空格，ltrim可去掉左侧空格，trim可去掉双侧空格
select concat(trim(vend_name), ‘ (’, vend_country,’)’ ) from vendors;
使用别名as
select concat(trim(vend_name), ‘ (’, vend_country,’)’ )as vend_title from vendors;
执行计算±/
select prod_id, prod_name, quantityprod_price as expanded_price from products;
文本处理函数
left()返回串左边的字符
length()返回串的函数
locate()找出串的一个子串
lower()将串转换为小写
ltrim()去掉串左边的空格
right()返回串右边的字符
soundex()返回串的soundex值
substring()返回子串的字符
upper()将串转换为大写
汇总数据
avg()返回某列平均值
count()返回某列行数
max()返回某列最大值
min()返回某列最小值
sum()返回某列值之和
返回表中行的个数（包含值为null的情况）
select count(*) from products;

创建分组使用group by，用在where之后，order by之前
分组数据过滤使用关键字having，where在分组前过滤，having在分组后过滤
select cust_id as orders from orders GROUP BY cust_id HAVING count() >= 2;
select vend_id from products where prod_price=10 group by vend_id having count()=2;
返回订单价格大于等于50的订单号和订单价格，并按照订单总价排序
select order_num, sum(quantityitem_price) as ordertotal from orderitems group by order_num having sum(quantityitem_price) >= 50 order by ordertotal;

使用子查询，where子句中的列必须与子查询中select的列数目相同
select cust_id from orders where order_num in (select order_num from orderitems where prod_id = ‘TNT2’);
相关子查询需使用完全限定的列名
select cust_name, cust_state, (select count(*) from orders where orders.cust_id = customers.cust_id) as orders from customers order by cust_name;
使用where关键字实现两个表的联结
select vend_name, prod_name, prod_price from vendors, products where vendors.vend_id = products.vend_id order by vend_name, prod_name;
使用inner join 和on关键字实现两个表的联结
select vend_name, prod_name, prod_price from vendors INNER JOIN products ON vendors.vend_id = products.vend_id;
联结多个表时使用and逻辑关系
select cust_name, cust_contact from customers where cust_id in (select cust_id from orders where order_num in (select order_num from orderitems where prod_id = ‘TNT2’));
等价于
select cust_name, cust_contact from customers, orders, orderitems where customers.cust_id = orders.cust_id and orders.order_num = orderitems.order_num and prod_id = ‘TNT2’;
使用表的别名，可缩短语句
select cust_name, cust_contact from customers AS c, orders as o, orderitems as oi where c.cust_id = o.cust_id and oi.order_num = o.order_num and prod_id = ‘TNT2’;
表的自联结，只涉及一个表
select prod_id, prod_name from products where vend_id = (select vend_id from products where prod_id = ‘DTNTR’)
等价于
select prod_id, prod_name from products as p1, products as p2 where p1.vend_id = p2.vend_id and p2.prod_id = ‘DTNTR’;
使用left outer join 或 right outer join 和on关键字完成表的外部链接
a inner join b的结果是两个表的交集，a left outer join b 产生的是a的完全集并按条件匹配b表,a right outer join b产生的是b的完全集并按条件匹配a表。没有匹配则显示null
select customers.cust_id, orders.order_num from customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;
select customers.cust_id, orders.order_num from customers RIGHT OUTER JOIN orders ON orders.cust_id = customers.cust_id;

使用关键字union创建联合查询，该查询语句的实现可用where or等关键字达到同样效果
union查询语句中的每个查询必须包含相同的列、表达式、聚集函数，出现顺序可不同。列数据类型需要兼容，类型不必相同
select vend_id, prod_id, prod_price from orders where prod_price <= 5 UNION select vend_id, prod_id, prod_price from products where vend_id in (1001,1002);
union会自动删除多条select语句中重复的查询，若需要出现要使用union all
使用union语句的同时要使用order by排序，order by关键字必须再最后一个select语句后使用

插入语句
insert into 表名 values()
INSERT INTO customers VALUES(null, ‘Mike’, ‘100 main sterrt’, ‘Los Angeles’, ‘CA’, ‘90046’, ‘USA’, null, null);
指明列名的插入语句insert into 表名() values()
insert into customers(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) values(null, ‘Mike’, ‘100 main sterrt’, ‘Los Angeles’, ‘CA’, ‘90046’, ‘USA’, null, null);
可使用insert low_priority into降低插入的优先级，使他人对数据库的查询优先级更高。
同时插入多条信息
insert into customers values(null, ‘Mike’, ‘100 main sterrt’, ‘Los Angeles’, ‘CA’, ‘90046’, ‘USA’, null, null),(null, ‘M. martin’, ‘42 galaxy way’, ‘new york’, ‘NY’, ‘11213’, ‘USA’);
使用insert select语句，可将别的表的数据添加到目标表中
insert into customers(‘cust_id’, ‘cust_contact’, ‘cust_email’, ‘cust_name’, ‘cust_address’) select cust_id, cust_contact, cust_email, cust_name, cust_address from custnew;

使用update关键字更新内容,
若不使用where限定指定行，update将会更新整个表
UPDATE customers SET cust_email = ‘123@gmail.com’ WHERE cust_id = 10005;
更新多个列
update customers set cust_name = ‘Mike’, cust_email = ‘123@gmail.com’ where cust_id = 10005;
若使用update更新多行时，在某一行发生错误，则所有想更新的行都不会更新。如果想要忽略错误使更新生效需要使用ignore关键字
UPDATE IGNORE customers…

使用delete关键字删除行,
delete不会删除整个表，若不使用where过滤，则会删除表中所有行
DELETE FROM customers WHERE cust_id = 10006;
使用truncate table语句可删除表中所有行，效率更高，该语句是先删除表再重建一个新表

创建表，
primary key关键字用来指定表中的主键，auto_increment会自动对该列增量，每个表仅限使用一个这个关键字，一般用于主键。
CREATE TABLE customers
(
cust_id int NOT NULL AUTO_INCREMENT,
cust_name char(50) NOT NULL,
cust_address char(50) NULL,
PRIMARY KEY (cust_id)
)ENGINE = InnoDB;
可使用select last_insert_id()知道最后一个auto_incement的值
使用默认值
CREATE TABLE customers
(
cust_id int NOT NULL AUTO_INCREMENT,
cust_name char(50) NOT NULL,
cust_address char(50) NULL,
quantity int NOT NULL DEFAULT 1,
PRIMARY KEY (cust_id)
)ENGINE = InnoDB;

更改表
增加一个列
ALTER TABLE vendors ADD vend_phone char(20);
删除一个列
ALTER TABLE vendors DROP COLUMN vend_phone;
删除表
DROP TABLE customers;
重命名表
RENAME TABLE customers TO customer;