sql之增删改查-基础语法

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${}^{\prime };以l为结尾select\ast fromproductswherepro{d}_{n}ameregex{p}^{\prime }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_pricequantity) from orderitems;求所有item_pricequantity的和
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;