MySQL必知必会

MySQL 常用语句

一、显示(show)

// 显示帮助
help show; # 显示所有可以显示的信息语法；

// 显示数据库
show databases;

// 选中数据库
use tablename; # 回车后显示Database changed 表示选中成功；

// 显示表
show tables;

// 显示列
show columns from tablename;
或者
describe tablename;
或者
show columns in tablename;

// 其他
// 显示服务器状态信息
show status;

// 显示创建数据库的sql语句
show create database databasename;

// 显示创建表的sql语句
show create table tablename;

// 显示数据库用户
show grants;

// 显示服务器错误信息
show errors;

// 显示服务器警告信息
show warnings;

二、数据库的操作

1. 查看数据库
   show databases;
2. 创建数据库
   create database databasename;
3. 删除数据库
   drop database databasename;
4. 使用数据库
   use databasename;

三、数据表的操作

1. 查看数据库中可用的数据表
   show tables;

2. 查看数据表结构
   show columns from databasename;
   或者
   show columns in databasename;
   或者
   describe tablename;

3. 创建表

   create table if not exists tablename(
       id tinyint not null auto_increment,
       name varchar(20) not null,
       score int(3) not null default 0,
       primary key(id)
   )ENGINE=InnoDB;
   default charset=utf8
   

   或者

   drop table if exists tablename;
   create table tablename(
       id tinyint not null auto_increment,
       name varchar(20) not null,
       score int(3) not null default 0,
       primary key(id)
   )ENGINE=InnoDB;
   

4. 删除表
   drop table tablename;
   或者(有条件)
   drop table if exists tablename;

5. 复制表

   // 复制表结构和数据
   create table if not exists newtablename select * from oldtablename;
   // 只复制表结构
   create table if not exists newtablename select * from oldtablename where 1=2;
   或者
   create table if not exists newtablename like oldtablename;
   // 只复制数据(新表与旧表表结构完全一致)
   insert into newtablename select * from oldtablename;
   // 只复制数据(新表与旧表表结构部分列一致)
   insert into newtablename(field1, field2, field3) select field1,field2,field3 from oldtablename;

6. 重名民表
   rename table oldtablename to newtablename;
   或者
   alter table oldtablename rename newtablename;

四、表结构(列)的操作

1. 增加新列
   alter table tablename add columnname datatype[(20)] [not null];
2. 删除列
   alter table tablename drop columnname;
3. 修改列
   alter table tablename modify columnname datatype[(20)];
4. 修改表选项
   alter table tablename charset set gbk;

五、表数据的操作

############查询############

1、查询数据
//单列查询
select prod_name from products;

//多列查询
select prod_name, prod_price from products;

//所有列查询
select * from products; #通配符(*)

//去重查询
select distinct vend_id from products; #distinct 作用于所有列为不仅是前置它的列；
select all vend_id from products;#默认为all,所有记录,可省略；

//限制记录数(行数)查询
//limit offset, row_count
//offset:偏移量，可省略，默认为0，即从第几行开始；
//row_count:查询的记录数
select * from products limit 5;
select * from products limit 2,10;

2、排序数据
//单列排序 
select prod_name from products order by prod_name;

//多列排序
select prod_id, prod_name, prod_price from porducts order by prod_price, prod_name;

//指定方向排序
select prod_name from products order by prod_price desc; #降序
select prod_name from products order by prod_price asc; #升序,默认可省略
select prod_id, prod_name, prod_price from porducts order by prod_price desc, prod_name asc;

例子：查询价格最高的10条记录
select * 
from products 
order by prod_price desc 
limit 0,10;
#order by子句一定要在from子句之后；limit子句一定要在order by子句之后；

3、过滤数据
//where 子句操作符
//8种条件操作符：= <> != < <= > >= between
//2中逻辑操作符：and、or、in、(not in)

//单值where子句
select prod_name, vend_id from products where prod_price = 2.50; #where子句一般在from子句之后，order by子句之前；
select prod_name, vend_id from products where prod_price between 2.50 and 10.00; #左右都是闭区间
select prod_name, vend_id from products where prod_price is null; #检查空值
select prod_name, vend_id from products where prod_price is not null; #检查非空

//多值where子句
select * from products where vend_id=1003 and prod_price < 10; #and子句
select * from products where vend_id=1002 or vend_id=1003; #or子句

例子：查询供应商为1002或者1003且价格高于10的商品名称和价格
//and和or组合使用，运算优先级：and > or
//解决方案：添加括号。
//代码规范：and 和 or组合使用的时候，不管默认计算是不是你想要的，都最好添加括号，增加可读性；
错误示范：select prod_name, prod_price from products where vend_id=1002 or vend_id=1003 and prod_price >= 10;
相当于：select prod_name, prod_price from products where vend_id=1002 or (vend_id=1003 and prod_price >= 10);
参考答案：select prod_name, prod_price from products where (vend_id=1002 or vend_id=1003) and prod_price >= 10;

//in 与 not in
//sql优化：in 好于 or；
select * from products where vend_id in (1002, 1003) order by prod_name; #in 功能上与 or 相似；
相当于
select * from products where vend_id=1002 or vend_id=1003 order by prod_name;
select prod_name from products where vend_id in (select distinct vend_id from products where prod_price >= 10) order by prod_name asc; #子查询

//MySQL中not对in、between、exists取反
select * from products where vend_id not in (1002, 1003) order by prod_name; #not in

//通配符：%：多个字符 _：仅一个字符
//sql优化：慎用，效率低；
select prod_name from products where prod_name like '%lin%'; #首字母就是通配符的，检索效率是最低的；
select prod_name from products where prod_name like '_lin_';

//正则表达式过滤数据
select * from products where prod_name regexp '.000' order by prod_name;
select * from products where prod_name regexp '1000|2000' order by prod_name;
select * from products where prod_name regexp '[123] Ton' order by prod_name;
select * from products where prod_name regexp '\\.' order by prod_name;
//简单正则表达式测试
select ('hello' regexp '[0-9]'); #返回值0：未匹配 1：匹配