Mysql常用命令总结大全

一、数据库基本概念

数据的仓库:

数据库服务器–>数据库—>表–>记录->属性(列、字段)

sudo apt-get install -y mysql-server mysql-client

二、SQL

基本可分为：

• 数据定义语言DDL (create、drop）
• 数据操作语言DML（insert、delete、update）
• 数据查询语言DQL（select、where、group by、order by 、limit）
• 数据控制语言DCL（grant、revoke）
• 事务处理语言TPL（commit、rollback）

三、操作数据库

• 连接mysql数据库的命令

命令：
    mysql -h服务器地址 -u用户名  -p  #不要再p后面直接跟密码
    
如果要远程连接mysql数据库，需要以下步骤：
1.通过mysql -u用户名  -p登录mysql数据库
2.创建一个新用户来远程连接
     create user 'python'@'%' identified by '123'
     GRANT ALL PRIVILEGES ON *.* TO 'python'@'%' IDENTIFIED BY '123' WITH GRANT OPTION;
3.执行命令：flush privileges;
4.退出mysql，到/etc/mysql/mysql.conf.d下，编辑mysqld.cnf
	找到 “bind-address = 127.0.0.1” , 这一行要注释掉，只需在前面加个#
5.重启mysql服务
  sudo service mysql restart | start | stop  #重启、启动、停止
  或者：sudo /etc/init.d/mysql restart | start | stop
6.如果开启了防火墙，请添加3306端口
  sudo ufw allow 3306
  
#忘记mysql密码：
1.到/etc/mysql/mysqld.conf.d/,编辑mysqld.conf
sudo vim mysqld.conf
2.到[mysqld]下
添加：skip-grant-tables
保存退出
3.重启mysql服务：
   sudo service mysql restart | stop | start

• 数据库操作命令

#1.查看库
   show databases;
#2. 创建库
   create database 数据库名 default charset=utf8;# 数据库名不要纯数字，不要用汉字
#3. 删除库
   drop database 数据库名;
#4. 选中库
   use 数据库名;
#5. 查看表
   show tables;
#6.查看数据库创建语句
show create database 数据库名
#7.查看选中的数据库
select database()
#8 修改数据库字符集
alter database student default charset=utf8;

• 注意:

  • 每条命令结束必须使用; 或者 \g 结束
  • 退出mysql使用命令quit或exit
     
     

四、数据库表

• 创建表

create table [if not exists] 表名(
   列名1  类型  [限制],
   列名2  类型  [限制],
   ...
   列名n  类型  [限制]  #最后一列没有逗号
) [engine=myisam | innodb][ default charset=utf8];

primary key 主键 不允许有重复值,不允许为空
auto_increment 自增长，只对int型主键起作用

#复合主键
mysql> create table grade(
    sid int ,
    cid int,
    score float,
    primary key(sid,cid));

• 删除表

drop table 表名;

• 查看表结构

desc 表名;

• 查看建表语句

show create table 表名;

• 修改表

#修改字段类型
   alter table 表名 modify 字段名 类型 [限制]
#增加字段
   alter table 表名  add [column] 字段名 类型 [限制];
#删除字段
   alter table 表名 drop [column] 字段名;
修改字段名和类型
   alter table 表名 change [column] 旧字段名 新字段名 类型 [限制];
   
#修改表名
  alter table 表名 rename 新表名
  alter table 表名 [engine=myisam] [default charset=utf8];

  
 # 可以通过first、after指定插入位置
  alter table student add sno varchar(3) not null after sid; //在sid列后插入
   alter table student add sid int primary key auto_increment first;//在第一列插入

• 字段限制

primary key 不允许空值，唯一

not null 非空

unique 唯一

default 缺省,默认值

五、数据类型

• 数值型

  • 整型 能用整型尽量使用整型。包括int、smallint tinyint
  • int(3) 或者 tinyint(2) :3或者2不会去限制你所存储数据的长度 ，只有在配合zerofill 零填充的时候 才有意义
  • 浮点数 double 、decimal

• 字符型

• 注意：

(1) char 和 varchar 的区别:

• char的执行效率高于varchar ，varchar 相对于 char 节省存储空间

• 如果使用char 传入的数据的长度 小于指定的长度的时候 存储的实际长度 不够的会拿空格来填充

• 如果使用 varchar 传入的数据的长度 小于指定的长度的时候 存储的实际长度 为传进来的数据的长度

• 日期时间型:

• 枚举enum

#是自定义类型，可以多选一,实际上存的值是1，2，3…
alter table user add sex enum(‘男’,‘女’) default ‘男’;
insert into user(name,password,sex)
values(‘tom’,‘132’,‘男’);
values(‘tom’,‘132’,1);

• 集合set

类似复选框，可以存多个值
alter table student add hobby set('看电影','玩游戏','敲代码','烫头')
insert into users(uid,hobby) values(22,1+2+4+8)
insert into users(uid,hobby) values(22,1|2|4|8)
insert into users(uid,hobby) values(22,'看电影,玩游戏,敲代码')

0001
0010
-------
0011

六、数据操作

1. insert

写法一：insert into 表名(字段1，字段2...) values(值1,值2...);

省略了字段列表，则按照建表时的字段顺序进行插入，每一列都要给值
写法二：insert into 表名 values(值1,值2...);
写法三：插入多个记录
	   insert into 表名(字段1，字段2...) 
			 values(值1,值2...),
			 (值1,值2...),
			 (值1,值2...)....
写法四： insert into 表名(name,age,sex)
		select name,age,sex from stars;
		
		insert into histroy_student select * from student;

2.update

update 表名 set 字段1=值1,字段2=值2… where 条件 #不加where修改的是所有的记录

3. delete

删除表中的数据，自增主键的值不会重新开始
delete from 表名 where 条件；#如果不加条件，会删除表中所有数据,慎重使用

清空表，自增主键的值重新开始编号
truncate 
	   truncate table 表名,清空表中所有记录，等价于delete from 表名；
	   delete和truncate差别，truncate后，表中自增主键值从1开始

七、数据查询

基本结构： select 字段名列表 from 表名

1 基础查询

select username,password  from user;
select usernname as 用户名, password as 密码  from user;  #可以给字段起别名
select *  from user; #查询所有字段，慎用，一般不建议使用，会导致无法优化sql语句
select 2018,username,password  from user; #可以有常量，表达式
select sname,2018-year(sbirthday) from student; #year是mysql的内置函数
select distinct username  from  user; #去除重复记录 distinct 针对查询结果去除重复记录，不针对字段

2 条件查询（where）

• 关系运算

  关系运算符：> 、 >=、 <、 <=、 =、!=、<>、 between and

select username,password from user where uid <10
select username,password from user where uid != 10
select username,password from user where uid between 10 and 20

• 逻辑运算

  逻辑运算符：and 、or、not

select username,password from user where uid < 100 and uid > 20;
select username,password from user where uid > 100 or uid < 20;

• 集合运算

  集合运算符：in、not in

select username,password form user where uid in (2,3,4)
select username,password form user where uid not in (2,3,4)

• 判空

  判空运算：is null、is not null

select username,password from user where username is null

• 字符串的模糊查询(like)

  通配符 _代表一个字符，%代表任意长度字符串

select * from user where username like '王_';
select * from user where username like '王%';

3. 排序（order by）

 **asc 升序(默认)、desc  降序、**

select * from user order by age asc;
select * from user order by age desc;
多字段排序
   	select name,age from php_user_history  order by age desc,name;# 如果在第一列上有相同的值，在具有相同的age的记录上再按name升序排列

4.限制结果集(limit)

limit n #取前n条记录

limit offset,n #从第offset条开始取，取n条

select * from php_user_history limit 3;
select * from php_user_history limit 4,2;
注意结果集中记录从0开始数数，offset相对于0开始
实现分页必须的技术点
limit (page-1)*num,num

5.集合函数

• count统计结果集中记录数
• max 最大值
• min 最小值
• avg 平均值，只针对数值类型统计
• sum 求和，只针对数值类型统计
• 注意，集合函数不能直接使用在where后面的条件里，但可以在子查询中

select count(*) num from user;
select count(distinct age) num from user; //去除重复记录
select * from student where sno = max(sno);//错误

6.分组（group by)

将结果集分组统计，规则：

• 出现了groub by的查询语句，select后面的字段只能是集合函数和group by后面有的字段，不要跟其它字段
• 对分组进行过滤，可以使用having

select uid, count(*) num from php_forum group by uid;
select uid,title, count(*) num from forum group by uid having count(*) >=2;
having和where的区别：
    where针对原始表进行过滤
    having 是针对分组进行过滤

7.查询小结

- 整体顺序不能颠倒

- []表示可选，可以有也可以没有

  select 字段
   from 表名
    [where 条件]
    [group by ]
    [having]
    [order by ]
    [limit]	 

八 字符集和存储引擎

• 修改字符集
  为了能够正常显示中文，必须把数据库的字符集设置为utf8.

mysql> show variables like 'character%';  #查看字符集
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |需要修改
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |需要修改
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

修改mysql的配置文件
cd /etc/mysql/mysql.conf.d
sudo cp mysql.cnf  mysql.cnf.bak
sudo vim mysql.cnf
在[mysqld]下增加一句：
character_set_server = utf8
保存并重启服务
sudo systemctl restart mysql.service  #重启服务

• 数据库引擎

  • 常用的数据库引擎：myisam、innodb、archive、ndb、memory

  • myisam和innodb的区别

    • myisam查询速度快，不支持事务、不支持外键、支持表锁
    • innodb增删改效率高，支持事务、支持外键，支持行锁