mysql常用命令
【新建账号】
create user ben identified by '1234';
注：mysql用户账号和信息存储在名为mysql的数据库中，可以通过直接插入行到user表来增加用户，不过安全起见，一般不建议这样做。
【重命名一个账号】
rename user ben to adang;
【删除账号】
drop user adang;
【查看账号权限】
show grants for adang;
【授权】
grant select on products.* to adang;
【取消授权】
revoke select on products.* from adang;
【更改口令】
set password = Password('1111');
set password for adang = Password('abcd');
【查看数据库列表】：
show databases;
【切换数据库】：
use someDatabase;
【查看数据库内的表的列表】：
show tables;
【查看表结构】：
show columns from someTable;
或者
describe someTable;
【创建表】
create table customers
(
cust_id int not null auto_increment,
cust_name char(50) not null,
cust_address char(50) null,
cust_city char(50) not null default 'beijing',
cust_email char(255) null,
primary key (cust_id)
);
注：
1）主键只能使用不允许null值的列;
2）每个表只允许一个auto_increment列;
3）mysql支持的数据类型主要分成字符串型、数值型、日期时间和二进制数据类型。字符串主要有char、varchar、text这几种，char属于定长字符串，text属于不定长字符串，两者的差异表现在性能上。数值型主要有int、tinyint、float、double。日期时间主要有date、datetime、timestamp、time、year。
【改动表结构】
alter table vendors add vend_phone char(20);
alter table vendors drop column vend_phone;
注：
复杂的表结构更改一般需要手动删除过程，新建一张新布局的表，通过insert select语句把旧表复制到新表，如果有必要，可使用转换函数和计算字段，重命名旧表，用旧表原来的名字重命名新表。
【删除表】
drop table customers;
【重命名表】
rename table customers2 to customers;
rename table backup_customers to customers, backup_vendors to vendors, backup_products to products;
【基本查询】：
select prod_id, prod_name, prod_price from products order by prod_price desc, prod_name asc, prod_id desc limit 4 offset 3;
注：
1）order by子句中，可以使用多个字段，每个字段的升降序要单独使用desc、asc关键字设定，如果不写，默认使用asc;
2）limit可以接收两个参数，limit 3,4 。效果和limit 4 offset 3相同。
【检索不同的行】：
select distinct vend_id from products;
注：不能部分使用distinct，distinct关键字应用于所有列而不仅是前置它的列
【过滤数据】：
select * from products where prod_price <= 10 order by prod_price;
select * from products where prod_name = 'toy';
select * from products where vend_id != 1003;
select * from products where prod_price between 5 and 10;
select * from products where prod_price is null;
select * from products where (vend_id = 1002 or vend_id = 1003) and product_price <= 10;
select * from products where vend_id in (1002, 1003);
select * from products where vend_id not in (1002, 1003);
select * from products where prod_name like 'jet%';
select * from products where prod_name like '_ jet_';
select * from products where prod_name regexp '^[0-9\\.]';
注：
1）order by子句在where子句之后，limit子句在order by子句之后;
2）where子句中，如果比较的值是数值类型，则不使用引号，如果比较的值是字符串类型，则使用单引号;
3）where子句操作符有=、<>、!= (效果同<>)、<、<=、>、>=、between;
4）创建表时，可以设定其中的字段是否可以为空，检查是否为空需要使用 is null关键字;
5）使用and 和 or操作符时，会优先and操作符。可以使用圆括号改变优先级;
6）in操作符作用同or相同，但更简短，性能更快，更主要的是in可以配合where子句进行更复杂的查询操作;
7）not可以和in、between、exists子句配合使用;
8）like关键字用于模糊匹配，可以配合%、_这两种通配符使用，其中%代表0、1或任意多个字符，而_代表一个字符，不能多也不能少;
9）mysql可以在查询语句中使用正则表达式，相关的关键字是 regexp;
【计算字段】
select Concat(RTrim(vend_name), '(',RTrim(vend_country),')') as vend_title from vendors;
select prod_id, quantity, item_price, quantity * item_price as expanded_price from orderitems;
注：
1）计算字段并非存储在数据库中的真实字段，而是在查询时，动态生成的字段，用于返回给应用程序使用。这个工作也可以放在应用程序层面来实现，只是放在数据库层面会有更高的执行效率;
2）Concat函数是mysql实现拼接的方式，别的数据库常用+或||来实现拼接，这不是标准的sql语句，数据库移植的时候可能会出现问题;
3）mysql支持对字段进行+-*/的基本算术操作，例如上面的第二条sql语句就使用了*。另外，还支持圆括号区分优先顺序。
【数据处理函数】
select vend_name, Upper(vend_name) as vend_name_upcase from vendors;
select * from orders where order_date = '2005-09-01';
select * from orders where Date(order_date) = '2005-09-1';
select * from orders where Date(order_date) between '2005-09-01' and '2005-09-30';
select * from orders where Year(order_date) = 2005 and Month(order_date) = 9;
注：
1）大多数数据库都会提供一些数据处理函数，但这些函数不像sql语句那样标准，不同的数据库之间函数的可移植性不强。
2）文本类处理函数常用的有 Left()、Length()、Locate()、Lower()、LTrim()、Right()、RTrim()、Soundex()、SubString()、Upper();
3）日期和时间类处理函数有AddDate()、AddTime()、CurDate()、CurTime()、Date()、DateDiff()、Date_add()、Date_format()、Day()、DayOfWeek()、Hour()、Minute()、Month()、Now()、Second()、Time()、Year();
4）数值类函数有Abs()、Cos()、Exp()、Mod()、Pi()、Rand()、Sin()、Sqrt()、Tan();
【汇总数据】
select AVG(prod_price) as avg_price from products where vend_id = 1003;
select MAX(prod_price) as max_price from products;
select COUNT(*) as num_cust from customers;
select COUNT(cust_email) as num_cust from customers;
select SUM(quantity) as items_ordered from orderitems where order_num = 20005;
select SUM(item_price * quantity) as total_price from orderitems where order_num = 20005;
select AVG(distinct prod_price) as avg_price from products where vend_id = 1003;
select COUNT(*） num_items, MIN(prod_price) as price_min, AVG(prod_price) as price_avg from products;
注：
1）汇总数据有AVG()——求某列的平均值，COUNT()——求某列的行数，MAX()——求某列的最大值，MIN()——求某列的最小值，SUM()——求某列值之和;
2）COUNT()函数有两种使用方式，传入*和传入具体列名，前者包含空值，后者会忽略空值;
3）汇总数据函数可以配合distinct参数使用，只对“不同值”进行计算。需要注意的是，distinct不能和COUNT(*)这种形式配合使用，也不能和类似item_price * quantity这样的计算字段配合使用，只能和具体的列名一起使用。
【分组数据】
select vend_id, COUNT(*) as num_prods from products where prod_price >= 10 group by vend_id having COUNT(*) >= 2;
select order_num, SUM(quantity * item_price) as ordertotal from orderitems group by order_num having SUM(quantity * item_price) >= 50 order by ordertotal;
注：
1）group by一定是配合汇总数据而使用的，不再是针对“全部”数据，而是针对“分组”进行汇总;
2）having子句和where子句非常相似，where能用的语法having也都能用。唯一的区别是，where是针对“行”进行过滤，而having是针对“组”进行过滤。可以这么理解，where在分组前进行过滤，而having在分组后进行过滤;
3）having子句的判断条件只能写“全部表达式”，不能使用as出来的别名;
4）group by只是用来分组的，并不是用来排序的，不能严格保证返回结果的顺序，所以该用order by的地方一定不能省略。
【子查询】
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 proid_id = 'tnt2'
)
) order by cust_name desc;
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子句的in操作符中，以及用来填充计算列。
【表间联结】
select cust_name, cust_contact from customers, orders, orderitems where customers.cust_id = orders.cust_id and orderitems.order_num = orders.order_num and prod_id = 'tnt2';
select vend_name, prod_name, prod_price from vendors inner join products on vendors.vend_id = products.vend_id;
select cust_name, cust_contact from customers as c, orders as o, orderitems as oi where c.cust_id = o.cust_id and oi.oder_num and prod_id = 'tnt2';
select customers.cust_id, orders.order_num from customers left outer join orders on customers.cust_id = orders.cust_id;
注：
1）表间联结又叫多表查询，通常有一对一、一对多、多对多的情况，有主键、外键一说。
2）上面第一条语句用的等值联结，这种联结也称为内部联结，和第二条语句中通过 inner join 、on实现的查询效果相同;
3）除了列可以通过as使用别名，表也可以。应该注意，表别名只在查询执行中使用，与列别名不一样，表别名不返回至客户机;
4）外联结使用关键字left outer join或right out join。如果查询每个客户下了多少订单，“包括那些至今尚未下订单的客户”时，需要使用外联结，让订单表和客户表联结起来。至于用left outer join还是用right out join，取决于以左边的表为主，还是以右边的表为主，本例以客户表为主。改变两张表的顺序，就可以将left换成right，没有别的区别。
【组合查询】
select vend_id, prod_id, prod_price from products where prod_price <= 5 union select vend_id, prod_id, prod_price from products where vend_id in (1001,1002);
select vend_id, prod_id, prod_price from products where prod_price <= 5 or vend_id in (1001,1002);
注：union是用来将多条select语句的返回结果并成同一个结果集返回的。大部分时候，用or可以实现和union一样的效果，但如果是多表查询或者一些特殊情况下，用union可以让查询语句更易读。
【插入数据】
insert into Customers values (NULL, 'Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', NULL,NULL);
insert into Customers (cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) values ('Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', NULL,NULL);
insert in customers (cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country) values ('Pep E. LaPew', '100 main street', 'Los Angeles', 'CA', '90046', 'USA'), ('M. Martian', '42 Galaxy Way', 'New York', 'NY', '11213', 'USA');
insert into customers(cust_id, cust_contact, cust_email, cust_name, cust_address) select player_id, player_contact, player_email, player_name, player_address from custnew;
注：
1）插入操作可以省略插入的字段名，例如上面第一条插入语句。但如果省略字段名的话，插入值时必须将每一列的值填上，严格按照在表中的顺序。但这种做法不好，一旦表结构发生变化，那么插入语句就会报错;
2）推荐按第二条那句那样的方式插入，将字段名显示地写出来。那些设置为“允许null”或“给出默认值”的字段可以省略;
3）可以一次插入多条信息，value之间以逗号分隔。如第三条语句所示;
4）可以将一个表的查询结果直接插入另一个表中，只要它们之间的数据结构一致——字段名是否一致无所谓，只要结构一样就行。
【更新数据】
update customers set cust_name = 'The fudds', cust_email = 'elmer@fudd.com' where cust_id = 10005;
update ignore customers set cust_name = 'adang' where cust_id = 10005;
注：update操作在更新多行时，如果这些行的一行或多行出现一个错误，则整个update操作被取消。如果想实现“即使是发生错误，也继续进行更新”，可使用ignore关键字。
【删除数据】
delete from customers where cust_id = 10006;
delete from customers;
truncate table customers;
注：delete语句如果不加where子句，则清空整张表，但并不删除表。但delete清空表的速度不如truncate table快。
【事务】
start transaction;
delete from orderitems where order_num = 20010;
delete from orders where order_num = 20010;
commit;
select * from ordertotals;
start transaction;
delete from ordertotals;
select * from ordertotals;
rollback;
select * from ordertotals;
savepoint delete1;
rollback to delete1;
注：
1）insert、update、delete语句可以执行rollback回退操作。select执行了也没有意义。drop和create操作无法rollback回退;
2）当start transaction启动一个事务后，所有的delete、incert、update之类的操作都不再隐含提交了，但commit或rollback语句执行后，事务会自动关闭;
3）用savepoint设置保留点，rollback时可以指定回退到具体的保留点。保留点在事务处理完成（执行一条rollback或commit）后自动释放。
