mysql 
登录： mysql -uroot -p 
create database crashcource; 
show databases; 
use crashcource; 
source create.sql; 
show tables; 
source populate.sql; 
select * from vendors; 
select distinct vend_id from products; 去掉重复行 
select * from orders limit 3; 返回前三个的信息 
select * from orders limit 1,4;返回第2-5个信息，从0开始编号，4代表有几个 
select * from products order by prod_name;按序号排 
select * from products order by prod_name desc,prod_id asc;混合排序默认是asc 
select * from products order by prod_id limit 5; 排序和limit 混合 
select * from products where prod_price<10;(>,<,>=,<=,<>(不等于),is null,is not null)可搭配(and ,or)来达到一起用 
select * from products where prod_price between 5 and 10; 
select * from products where vend_id in (1001,1002); in的使用方法id=1001或1002 
select * from vendors where vend_country in (‘England’,’France’); 
select * from orderitems where order_num in (20005,20006,20007); 
select * from orderitems where order_num not in (20005,20006,20007); not in 的使用方法 
select * from products where prod_name like ‘j%’; like 和 % 的使用（任意多个字符） _ (任意一个字符)。 
select prod_name from products where prod_name REGEXP ‘1000’; 与 like ‘%1000%’;相同 不区分大小写 
select prod_name from products where prod_id REGEXP ‘[12345]000’;选取有1000，2000 
，3000,4000,5000的字符 
select prod_name from products where prod_id REGEXP ‘[1-5]000’;与上等同 
street 是东西方向 
avenue 是。。 
\表示转义字符 \.就是表示实际的. 
.在正则表达式中表示任意一个字符 
select * from products where prod_name regexp ‘sticks?’; stick或sticks 
select * from products where prod_id regexp ‘^.{3}0’; ^表示从文本开始，.表示任意一个字符， {3}表示重复3次 
select * from products where prod_id regexp ‘^…0’; 
select * from products where prod_id regexp ‘^.N’; 
select * from products where prod_name regexp ‘l′;以l为结尾select∗fromproductswhereprodnameregexp‘l.′;以l为结尾select∗fromproductswhereprodnameregexp‘l.’;倒数第二个字符为l 
select * from products where prod_name regexp ‘.* ‘;有空格 
regexp ‘^1[34578][0-9]{9}$’;手机号校验 
select 10-prod_price from products; 可计算 
select 10-prod_price as sub from products;将所得字段重命名为sub as可省略 
select concat(vend_name,’(‘,vend_state,’)’) from vendors;concat可实现显示的格式 
如果有其中一个字段是null，则结果还是null 
select trim(concat(vend_name,’ ‘)) as a from vendors; trim是去掉左右空格，rtrim右边空格，ltrim左边空格 
select substring(vend_name,1,3) from vendors; 从第一个字符取得三个字符 
select lower(vend_name) from vendors;全部变成小写 
select upper(vend_name) from vendors;全部变成大写 
select Length(vend_name) from vendors;求长度 
select now(); select year(now());取得当前时间和取得当前时间的年份 
sum max min avg count他们不能用在where子句 
select min(prod_price) from products; 
select * from products order by prod_price limit 1;输出最小的价格的信息 
select sum(item_price*quantity) from orderitems;求所有item_price*quantity的和 
select count(*) from products;计算有几条记录，AVG()、MIN()、SUM()同理 
select count(distinct prod_price) from products; 有几种价格除掉重复的

INSERT INTO customers VALUES( NULL, ‘person’);//如只有两列 
INSERT INTO customers(cust_id,cust_name) VALUES( NULL,’perso’);//如不止两列 
INSERT INTO custmoerNew(cs_id,cs_name) SELECT cust_id,cust_name 
FROM customers;//从选择中插入 
INSERT INTO custmoerNew(cs_id,cs_name) VALUES ( NULL,’perso’), ( NULL,’perso’); //批量插入

UPDATE customers SET cust_name=”立冬” WHERE cust_id=1

DELETE FROM custmoerNew WHERE cs_id = 5;