mysql数据库一些常用操作

mysql命令行语句一定要加；结束，不然就算你enter以后还是需要输入命令的。

ALTER TABLE 原表名 RENAME TO 新表名   //修改表名

mysql -u root -p //登陆
create database xhkdb;//创建数据库
drop database xhkdb;//删除数据库
use database(库名) ; //用某个库//连接数据库
show tables;//查看这个库里的表
create table t_table2(
    uid int(10) not null auto_increment,
    name varchar(100) not null,
    time varchar(100) not null,
    primary key(uid)
     );//创建一个表，表里面可以没有任何数据；但是你只要写了数据，就必须定义key值(primary key(uid));
CREATE TABLE tbl_emp(
emp_id int(11) not NULL AUTO_INCREMENT,
emp_name VARCHAR(255) not null,
gender char(1) not null,
email VARCHAR(255) not NULL,
PRIMARY KEY(emp_id)
);

select:
select DISTINCT name  from t_customer; //查询到的字段中不会有重复的信息
select DISTINCT name  from t_customer limit 5; //查询这个字段的前五个数据
select  name from t_customer limit 5, 5;//从第五行开始查询后五个字段；
select t_customer.id from test.t_customer;//完全限定的方式
select telephone from t_customer order by telephone;//通过telephone字段来按字母顺序排序
order by:
select telephone,name from t_customer order by telephone,name;//按其中的两个列分别按字母顺序排序
select telephone from t_customer order by telephone desc;//通过telephone字段来按字母逆序顺序排序
select telephone,id  from t_customer order by id desc,telephone;//先通过id字段来按字母逆序顺序排序，然后通过telephone顺序排序
select telephone from t_customer order by telephone desc limit 1;//查询最大值
select telephone from t_customer order by telephone limit 1;//查询最小值
/****order by 子句一定要位于From字段以后 limit子句一定要位于order by子句以后****/
/****子句顺序不对的话会报错****/
where:
//过滤数据（where）
select name from t_customer where id = 1;//查询id值为1的name字段
select id, name from t_customer where name='陈晓强';//查询id值，name值根据name字段的值
= 等于 < 小于 <= 小于等于 > 大于 >= 大于等于  !=或者<> 不等于 between 在指定的两个值之间
select id, name from t_customer where  id between 5 and 20;//查询id值为5到20之间的字段
select id from t_customer where gender is null;//判断空值
//数据过滤(in / not in /between / not between /)
select * from t_customer where id<5 and gender='男';//查询id小于5和gender为男的数据；
select * from t_customer where id<5 or gender='女';// 查询id小于5和gender为女的数据；
select * from t_customer where id in (1,5,20) order by id;// in子句
select * from t_customer where id not in (1,15,29,5) order by id;//not in 子句
select * from t_customer where id not between 5 and 20 order by id;//not between子句
//用通配符进行过滤（like）(%匹配零个、一个或者多个字符)（ _ 匹配一个字符）
select * from t_customer where telephone like '%123%';
select * from t_customer where telephone like '%123';
select * from t_customer where telephone like '123%';
select * from t_customer where telephone like '17764226233';
select * from t_customer where telephone like '%2%4%';
select * from t_customer where telephone like '1776422623_';
Notes:通配符不要过度使用。
//用正则表达式进行搜索
select * from t_customer where address regexp '湖北省';//查找
select * from t_customer where telephone regexp '[123]33';//匹配133、233或者333字符
select * from t_customer where telephone regexp '[1-3]3';//匹配13、23或者33字符 [1-3]相当于匹配单个字符1到3
 . 可以匹配任何字符 但是不能匹配NULL
\\-可以匹配特殊字符-   \\.匹配特殊字符.  \\/匹配特殊字符/

//创建计算字段
select concat(name,'(',id,')') from t_customer;//输出为t_customer表中的name(id) //拼接字段
select concat(ltrim(address)) from t_customer where id =32;//删除字段左边的所有空格
select concat(rtrim(address)) from t_customer where id =32;//删除字段右边的所有空格
select concat(name,'(',rtrim(ltrim(address)),')') as customer from t_customer where id =32;//跟拼接的字段起一个别名
select id,name, concat(name,'(',rtrim(ltrim(address)),')') as customer from t_customer where id =32;//可以同时跟其他字段同时查询
//带时间的字段
select 字段名 from where Data(带时间的字段名) between '2015-09-01' and '2015-10-01'；
//聚集函数（确定表中行数 获取表中行组的和 找出表列、行、特定列、特定行）的最大值，最小值和平均值。
count() 返回某列的行数
select avg(distinct price) as price from t_customer where id = 1003;
//顺序
select
from
where
group by 分组过滤
having 组级过滤 
order by /oder by desc
limit

select * from t_customer where id in (select id from t_customer where name='陈小强' );//子句查询

//联结查询
外键：外键为某一个表的一列，它包含另一个表的主键值，定义了两个表之间的关系。

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 orderitems.order_num = orders.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************************************************/
insert into t_customer values(33,'59','男','24332334654856436','河北省');//按照表的顺序插入
insert into t_customer(id,gender,name,address,telephone) values(34,'女','小平','湖北省武汉市','7578363567345738');//按照需要插入的顺序插入，要在前面写对应的字段
insert into t_customer values(35,'59','男','24332334654856436','河北省'),(36,'59','男','523523525325','河北省');//一次性插入多行数据
//插入从表中查出的数据
insert into customers(cust_id,cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country) select cust_id,cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country from custnew;
/***************************************delect/update********************************************/
update t_customer set telephone='4536363464364262' where id = 22;//更改一个字段
update t_customer set telephone='435353464362326847',gender='女' where id = 23;//更改多个字段

delete from t_customer where id = 23;//删除id为23的行
如果想从表中删除所有行；不要使用delete。可以使用truncate table语句，它可以完成相同的工作 ，但速度更快
/******************************创建表***********************************/
create table student(
stuNo int not null auto_increment primary key, //不允许为空、主键、自增长
stuName varchar(50) not null, //不允许为空
stuAge int null    //允许为空
);

drop table t_table;//删除一个表
rename table t_student to MyTable;//修改表的名字
rename table MyTable to table1,student to table2;//修改多个表的名字

ALTER TABLE t_customer CHANGE COLUMN name  cust_name VARCHAR(50);;//修改表中的字段的名称

alter table t_student add stuAddress varchar(30);//在表中插入一个新的字段
alter table t_student drop stuAddress;//在表中删除一个字段
//alter table 经常使用来给其他表写外键

/*****************************添加外键************************************/
alter table userinfo add CONSTRAINT fk_user_depart FOREIGN KEY(department_id) REFERENCES department(id)

create table userinfo(
uid INT auto_increment PRIMARY KEY,
name varchar(32),
department_id INT,
CONSTRAINT fk_user_depart FOREIGN KEY ("department_id") REFERENCES department("id")
)ENGINE=INNODB DEFAULT CHARSET=utf8;

create table department(
id int auto_increment PRIMARY KEY,
title varchar(32)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

/***************************************分组查询***************************************/
select count(uid),department_id from userinfo group by department_id；
select count(uid) as 部门总人数,department_id from userinfo where uid>0 group by department_id having count(uid)>1;

/***************************************连表查询**************************************/
select * from userinfo,department where userinfo.department_id = department.id; 

select * from userinfo LEFT JOIN department on userinfo.department_id = department.id; // 左边的表全显示

select * from userinfo 
LEFT JOIN department on userinfo.department_id = department.id;
LEFT JOIN part on xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx;//可以连接多张表
select * from userinfo RIGHT JOIN department on userinfo.department_id = department.id; // 右边的表全显示

索引（加速查找）
创建索引的过程中会创建额外的文件（某种存储格式）会占用硬盘的位置

hash索引：
    单值速度快
    范围查找较慢
btree索引：
    二叉树

建立索引：
    -a 额外的文件保存特殊的数据结构
    -b 查询快：插入更新删除慢
    -c 查询语句需要命中索引

普通索引创建方法：
/*******************表中创建索引*************************/
create index 索引名称 on 表名(字段名); 
create index ind_name on account(name); //对account表中的name字段创建索引
/*******************表中删除索引*************************/
drop index 索引名称 on 表名;
drop index ind_name on account;//删除account表中的ind_name索引

create table t_table2(
    uid int(10) not null auto_increment primary key,
    name varchar(100) not null,
    time varchar(100) not null,
    index ix_name(name)  //创建普通索引
     );

唯一索引创建方法：
create table t_table2(
    uid int(10) not null auto_increment primary key,
    name varchar(100) not null,
    time varchar(100) not null,
    unique ix_name(name)  //创建普通索引
     );
create unique index 索引名 on 表明(字段名);
drop unique index 索引名 on 表名;