MYSQL的基本操作命令

1.登录MySQL及修改密码

-- 进入数据库的方法一
mysql -uroot -pmysql    # mysql 数据库密码(显示)

-- 进入数据库的方法二
mysql -uroot -p         # 隐藏密码输入
 --修改密码方式一--
mysqladmin -u用户名-p旧密码password 新密码
mysqladmin -uroot -pab12 password djg345
--修改密码方式二用set password命令--
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');
--修改密码方式三用update直接编辑user表--
update user set PASSWORD = PASSWORD('1234abcd') where user = 'root';

在丢失root密码的时候：
[root@rhel5 ~]# mysqld_safe --skip-grant-tables &
[root@rhel5 ~]# mysql -u root
mysql> use mysql
mysql> update user set password = PASSWORD('123456') where user = 'root';

2.数据库的基本操作

-- 显示数据库版本(记得加;
select version();

-- 显示当前的时间
select now();

-- 查看所有数据库
show databases;

-- 创建数据库
create database 数据库名 charset=utf8;
-- 创建淘宝数据库
create database taobao;
-- 创建淘宝数据库并指定编码
create database taobao charset=utf8;

-- 查看创建数据库的语句
show create database school


-- 使用数据库
use school;

-- 显示数据库中所有的表
show tables;

--删除数据库
drop database school;

3.数据表的基本操作

约束：
-- auto_increment :自动增长
-- not null :表示不为空约束
-- primary key :表示主键约束
-- default :默认值约束
-- unique : 唯一约束
-- foreing key : 外键约束
字段类型  
1．INT[(M)] 型：正常大小整数类型  
2．DOUBLE[(M,D)] [ZEROFILL] 型：正常大小(双精密)浮点数字类型  
3．DATE 日期类型：支持的范围是-01-01到-12-31。MySQL以YYYY-MM-DD格式来显示DATE
值，但是允许你使用字符串或数字把值赋给DATE列  
4．CHAR(M) 型：定长字符串类型，当存储时，总是是用空格填满右边到指定的长度  
5．BLOB TEXT类型，最大长度为(2^16-1)个字符。  
6．VARCHAR型：变长字符串类型  

# 复制表的所有列
create table t_user_copy as select * from t_user; #会把数据一起复制过来
# 只复制表结构
create table t_user_copy as select * from t_user where 1=0;
# 复制表以及约束但是不复制数据
create table t_user_copy like t_user;


-- 查看当前的数据库中所有的表
-- show tables;
建表命令：create table <表名> ( <字段名> <类型> [,..<字段名n> <类型n>]); 
-- 创建students数据表
create table students(
    id int unsigned not null auto_increment primary key,
    name varchar(50) not null default "张三",
    age tinyint unsigned not null default 18,
    high decimal(5,2) not null,
    gender enum("男", "女", "保密")default "保密",
    cls_id int unsigned not null
    );

-- 插入一条数据到students表中
insert into students values(0, "mike", 18, 145,"保密",2)

-- 查询students表中的所有的数据
select * from students;

-- 查看创建表的语句
show create table students;

-- 删除表
drop table students;

-- 查看表的字段
desc students; 或者show columns from 表名 
-- 更改表名
rename table 原表名 to 新表名;
rename table MyClass to YouClass;  

-- 添加表的字段
（）alter table students add birth datetime;
（）alter table dbname add column userid int(11) not null primary key auto_increment;  
-- 修改字段:不改变字段名字
alter table students modify birth date;

-- 修改字段:不重命名版
alter table students change birth birthday date default "2020-01-01";

-- 删除字段
alter table students drop cls_id;

-- 插入数据 insert into 表明 value(...)
-- 主键可以用 0 null default来占位
insert into students values(null, "lily", 22, 168, 2, "1990-01-01");

-- 部分插入
insert into students(high) values(172);

-- 多行插入
insert into students(name, high) values("李四", 178),("老王", 1.44);
-- 多行插入全部数据
insert into students values(null, "lily", 23, 173, 2, "1990-01-01"), (null, "xiao", 22, 189, 2, "1990-02-03");

-- 修改表 
-- 修改全部年龄
update students set age= 30; 
-- 修改指定id的年龄
update students set age=28 where id=1;
--文章前面加入个空格  
update article set content=concat(‘　　’,content);

--查询表的内容
select * from students;

-- 定条件查询
select * from students where id=2;
select * from students where id>=1 and id<=3;

--查询指定的列
select id, name from students where id>=1 and id<=3;
select name, id from students where id>=1 and id<=3;

-- 可以用as来为列表指定别名(显示出来的名字就是指定的名字)
select name as 姓名, id as 学号 from students where id>=1 and id<=3;

-- 物理删除
delete from student where id=6;

--逻辑删除(用新的字段作为条件限制显示信息)
alter table students add is_delete bit default 0;
-- 把id=1的is_delete改为1
update students set is_delete=1 where id=1;
-- 查询然后条件限制为is_delete=0 就可以隐藏数据
select * from students where is_delete=0;

4.数据表的查询操作

-- 查询所有字段
select * from students;

-- 查询指定字段
select name, age from students;

-- 给字段起别名(用别名显示)
select name as 姓名, age as 年龄 from students;

-- 从指定的表中寻找指定的字段
select students.name, students.age from students;

-- 用as起别名再用别名调用字段
select s.name, s.age from students as s;

-- 利用distinct字段消除重复行
select distinct gender from students;

-- 条件查询(比较运算符)
select * from students where age>19;
select * from students where age<19;
select * from students where age!=18;

-- 条件查询(逻辑运算符)
select * from students where age>=17 and age<=27;
select * from students where age>=13 or high>=159;
select * from students where not(age<=17 and gender=2);

-- 模糊查询
-- 查询以"李"开头的所有名字
select * from students where name like "李%";
-- 查询以"王"字结尾的所有名字
select * from students where name like "%王";
-- 查询有"三"的所有名字
select * from students where name like "%三%";
-- 查询有两个字的名字
select * from students where name like "__";
-- 查询有三个字的名字
select * from students where name like "___";
-- 查询至少有两个的名字
select * from students where name like "%__%";

-- 空判断is null
select * from students where high is null;

5.数据表内数据的排序

-- order by 字段  查询改字段内的的排序
-- asc从小到大排序,默然从小到大
-- desc 从大到小排序
select * from students where (age between 18 and 26)and gender=2 order by age;

-- 查询students表中,年纪17到30岁的女性 身高从高到矮
select * from students where (age between 17 and 30) and gender=2 order by high desc;

-- order by 多个字段
-- 查询students表中,按high 降序, age升序
select * from students where(age between 17 and 37) and gender=2 order by high desc ,age asc;

6.数据表的集合函数

-- 聚合函数
-- count(*)统计列数,count(字段)一样
select count(*) from students where gender=2;

-- 最大值,最小值,求和,平均
select max(age), min(age),sum(age),avg(age) from students;

7.分组

-- group by 按照性别分组
select gender from students group by gender;

-- 在students表中,计算每个性别的人数
select gender, count(*) from students group by gender;

--在students表中,通过性别分组显示名字
select gender, group_concat(name) from students group by gender;

-- group by + having :用来分组查询后指定一些条件的查询结果
select gender,count(*) from students group by gender having count(*)>2;

8.分页

-- 查询前3行女生的信息
select * from students where is_delete=0 limit(n-1)*m,n
select * from students where gender=2 limit 0,3;

9.连接查询

- 先建立一个班级表
-- 内连接查询班级表和学生表
select * from students inner join classes on students.cls_id=classes.id;

-- 左连接查询班级表和学生表
select * from students as s left join classes as c on c.cls_id=c.id;

-- 右连接查询班级表和学生表

10.自关联

-- 表中的某一列,关联表中的另一列,这就是自关联

11.子查询

-- 在一个select语句中,嵌入另外一个select语句,那么嵌入的select语句被称为子查询语句
-- 子查询是嵌入到主查询中
-- 子查询是辅助主查询的,要么充当条件,要么充当数据源
-- 子查询是可以独立存在的语句,是一条完整的 select 语句

-- 标准子查询
select * from students where age > (select avg(age) from students);

-- 列级子查询
select name from classes where id in (select id from students);

-- 行级子查询
select * from students where (height,age) = (select max(height),max(age) from students);

12.视图

-- 有个查询结果，这个结果表作为创建视图基础，这个结果中不能出现同名字段
select g.id, g.name, c.name as cate_name, b.name as brand_name, g.price 
from goods as g inner join goods_brands as b on g.brand_id=b.id inner join 
goods_cates as c on c.id=g.cate_id;

-- 新建了一个视图，这个视图它是一个虚拟表，这个表字段是原表字段的引用，可以简单理解为软链接
create view v_info as select g.id, g.name, c.name as cate_name, b.name as brand_name, g.price 
from goods as g inner join goods_brands as b on g.brand_id=b.id inner join 
goods_cates as c on c.id=g.cate_id;

-- 删除视图
drop view v_info

13.事务

1.MySQL 事务
MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，
你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，
如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！
在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，
要么全部不执行。

事务用来管理 insert,update,delete 语句
一般来说，事务是必须满足4个条件（ACID）：
原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。
原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，
不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，
就像这个事务从来没有执行过一样。
一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

1.原子性
一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性

2.一致性
数据库总是从一个一致性的状态转换到另一个一致性的状态。（在前面的例子中，一致性确保了，即使在执行第三、四条语句之间时系统崩溃，支票账户中也不会损失200美元，因为事务最终没有提交，所以事务中所做的修改也
不会保存到数据库中。）

3.隔离性
通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。（在前面的例子中，当执行完第三条语句、第四条语句还未开始时，此时有另外的一个账户汇总程序开始运行，则其看到支票帐户的余额并没有被减去200美元。）

4.持久性
一旦事务提交，则其所做的修改会永久保存到数据库。（此时即使系统崩溃，修改的数据也不会丢失。）

-- 开启事务
begin;
start transaction;

-- 提交事务
commit;

-- 回滚事务
rollback;

14.索引

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度(创建索引会缩短执行的时间)

-- 查看索引
show index from 表名;

--创建索引
create index 索引名称 on 表名(字符段名称(长度))

--删除索引:
drop index 索引名称 on 表名;

--查询
--开启运行时间
set profiling=1;

--查看执行时间
show profiles;

15. 数据库备份

###导出整个数据库
mysqldump -u 用户名 -p –default-character-set=latin1 数据库名 > 导出的文件名(数据库默认编码是latin1)
mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql

###导出一个表  
mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名  
mysqldump -u wcnc -p smgp_apps_wcnc users> wcnc_users.sql

###导出一个数据库结构
mysqldump -u wcnc -p -d –add-drop-table smgp_apps_wcnc >d:wcnc_db.sql 
-d 没有数据 –add-drop-table 在每个create语句之前增加一个drop table 

###导入数据库
A:常用source 命令  
进入mysql数据库控制台，  
如mysql -u root -p  
mysql>use 数据库 
然后使用source命令，后面参数为脚本文件(如这里用到的.sql)  
mysql>source wcnc_db.sql
B:使用mysqldump命令  
mysqldump -u username -p dbname < filename.sql  
C:使用mysql命令  
mysql -u username -p -D dbname < filename.sql

导入数据库表  
　　（）创建.sql文件  
　　（）先产生一个库如auction.c:mysqlbin>mysqladmin -u root -p creat auction，会提示输入密码，然后成功创建。  
　　（）导入auction.sql文件  
　　c:mysqlbin>mysql -u root -p auction < auction.sql。  
　　通过以上操作，就可以创建了一个数据库auction以及其中的一个表auction。

16mysql数据库创建拥有及授权删除用户

一. 创建用户
命令:CREATE USER 'username'@'host' IDENTIFIED BY 'password';
说明：username：你将创建的用户名
host：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%
password：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器
例子：
CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'pig'@'192.168.1.101_' IDENDIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '';CREATE USER 'pig'@'%';


二. 授权:
命令:GRANT privileges ON databasename.tablename TO 'username'@'host'
说明:privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL
databasename：数据库名
tablename：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.*
例子:
GRANT SELECT, INSERT ON test.user TO 'pig'@'%';GRANT ALL ON *.* TO 'pig'@'%';
GRANT ALL ON maindataplus.* TO 'pig'@'%';

注意:用以上命令授权的用户不能给其它用户授权，如果想让该用户可以授权，用以下命令:
GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

回收权限
revoke delete on *.* from 'jack'@'localhost';
删除用户
drop user 'jack'@'localhost';