学习Mysql实用语句

Ip地址：127.0.0.1

修改数据库的密码

Mysqladmin -uroot -proot password

查看数据库

Show databases;

选择数据库

Use 数据库名;

查看使用中的数据库

Select database();

查看版本

Select version();

查看当前时间

Select now();

删除数据库

Drop database 数据库名;

显示连接的用户

Select user();

创建数据库

Create database 数据库名;
创建数据库之前查询数据库是否存在
Create database if not exists 数据库名;
完整语句
Create database if not exists 数据库名character set 字符编码;

创建表

Create table 表名(
列名1 数据类型,(自增AUTO_INCREMENT)
列名2 数据类型);

查看数据库下所有表

Show tables [ from 数据库名];

查看数据表基本结构

Desc 表名;

给数据表添加列

Alter table 表名 add 列名 数据类型[first /after 已存在的列名 ]

修改列的数据类型

Alter table 表名 modify 列名 新的数据类型;

修改数据表的列名

Alter table 表名 change 列名 新列名 数据类型;

删除表中的列

Alter table 表名 drop 列名;

修改表名

Rename table 旧表名 to 新表名
Alter table 旧表名 rename 新表名

修改表的字符集

Alter table 表名 character set 字符集

查询创建表的语句

Show create table 表名;

修改列的排列位置

Alter table 表名 modify 列名 数据类型 first/after 列名

删除表

Drop table 表名;

使用insert语句向表中添加数据

Insert into 表名(列名,列名2)
Values(数据1,数据2);
插入的数据应与字段的数据类型相同
字符和日期型数据应包含在单引号中

使用delete语句删除数据

Delete from 表名 where id = 1;
如果不使用where语句 将删除表中所有数据

使用update语句更新数据

Update 表名 set 列名1 = 新的值 where id = 1;
如果不使用where语句 将更新所有的行

使用select语句查询数据

Select * from 表名;
代表所有列的数据
把改为列名可指定查询列的数据

通过修改表添加非空约束

Alter table 表名 modify 列名 数据类型 not null;
注意：添加非空约束之前不能有数据

删除非空约束

Alter table 表名 modify 列名 数据类型;

主键约束

Alter table 表名 modify 列名 数据类型 primary key;
Alter table 表名 add primary key(列名);
Alter table 表名 add constraint 主键名 primary key(列名);
注意：一张表中最多只有一个主键约束

删除主键

Alter table 表名 drop primary key;

添加唯一约束

Alter table 表名 modify 列名 数据类型 unique;
Alter table 表名 add unique (列名);
Alter table 表名 add constraint 唯一名 unique(列名);

删除唯一约束

Alter table 表名 drop index 列名;

添加默认值

Alter table 表名 modify 列名 数据类型 default ‘默认值’;

删除默认值

Alter table 表名 modify 列名 数据类型;

比较运算符：

Select * from 表名 where javac between 70 and 90;
Select * from 表名 where javac in(77,88);
Select * from 表名 where name like ‘%的%’;
Select * from 表名 where name like ‘的_’;
%代表其它数值 _代表一个数值
Select * from 表名 where name is null;

逻辑运算符：

Select * from 表名 where javac = 12 and name = ‘某某’;
Select * from 表名 where javac = 12 or name = ‘某某’;
Select * from 表名where not (javac = 66);
Sclect排序语句：
默认升序
Select * from表名 order by javac desc;（倒序）
Select * from表名 order by javac asc;（升序）

聚合函数：

Count(列名)返回某一列，行的总数
Select count(*) from 表名 where javac = 77;
Sum函数返回满足where条件的行的和
Select sum(javac) from 表名;
AVG函数返回满足where条件的一列的平均值
Select avg(javac+mysqlc) from 表名;
Max/min函数返回满足where条件的一列的最大/最小值
Select max(javac) from 表名;
Select min(javac) from 表名;

子查询：

Select * from 表名where javac = (select max(javac) from 表名);
外键：

创建表时添加外键

constraint 外键名 foreign key (外键列名) references 外表表名 (对应的外表主键名)
///
alter table 表名add constraint 外键名 foreign key(外键列名) references外表表名(对应的外表主键名);
ALTER TABLE stu ADD FOREIGN KEY (class) REFERENCES grade (id);

左链接： right (右链接)

Select * from 表名1 left join 表名2 on 表名1的外键 = 表名2的主键;

多表查询：

Select * from 表名1,表名2 where 表1的外键 = 表2的主键 and 表2的主键 = 表3的外键

去除重复的值：distinct

Select distinct name from student

Union 将两个表连一块 去掉重复的值
Union all 显示全部

临时表：

SELECT degree FROM score a WHERE degree <(SELECT avg(degree) FROM score b GROUP BY cno HAVING a.cno = b.cno)

Limit:

从第几条开始或者跳过第几条,显示几条;

动态获取当前日期：

Now ();

获取date类型的年的部分

Year(date类型的列);

视图：视图的创建语法

create view 自定义视图名 as select 语句;
删除视图：
Drop view 视图名;

事务：开始一个事务

start transaction;
事务中间错误,自动恢复原来状态（回滚）
Rollback;
提交事务 事务结束时提交完成事务中代码
Commit;

存储过程：

Create procedure 存储过程名称 ()
begin
– sql语句
end;
删除存储过程:drop procedure 存储过程名称;
(Sql语句声明变量
Declare 变量名称 数据类型;
Set设置变量
Set 变量名称 = …; )
Create procedure jisuan(in num1 int,in num2 int)
Begin
Declare num3 int;
Set num3 = num1+num2;
Select num3;
End
调用存储过程：call 存储过程名称();
存储过程给out输出类型值用into
例：select age into setage from student;
@a接收存储过程中输出类型的参数 @+任意字符
Call 存储过程名称(‘李军’,@a);
Select @a;
Inout 既能输入也能输入（可以赋值也可以接收）

If 条件 then end if;

例:
If age > 30 then
Select ‘中年人’;
End if;
所有if都走

追加字符串：

concat(值,值) 拼接起来

Mysql while循环：

While 条件 do
sql代码
End while;
例:
Declare i int default 1;
While i < 10 do
Insert into 表名(列名)values(值);
Set i = i+1;
End while;

Mysql loop循环：

例：
Declare i int default 1;
循环名称 ：loop
Set i = i+1;
If i > 10 （判断什么时候结束循环）
then leave 循环名称;
End if;
End loop;

Mysql repeatuntil循环：

例：
Declare i int default 1;
Repeat
Set i = i+1;
Until i > 10 （判断什么时候结束循环）
End repeat;

Mysql case分支结构：

Select case 列名when ‘张三’ then ‘李四’ end from 表名
例：
Select case name when ‘癔症蛋’ then ‘李龙方’ else ‘其他人’ end from sys_student

创建函数语法

Create function 函数名(参数名 参数类型)
Returns varchar(20); 【返回数据类型】
Begin

Return i;
End

事件

CREATE EVENT IF NOT EXISTS 事件名
ON SCHEDULE EVERY 1 SECOND （每1秒）
ON COMPLETION PRESERVE
DO CALL 存储过程名();

查询是否开启事件：
SHOW VARIABLES LIKE ‘%event_scheduler%’;
开关事件：
SET GLOBAL EVENT_scheduler = off;(on开)

Mysql优化

分区： 通过那一列分区
partition by range(YEAR(separated))(
partition 区名0 VALUES less than (1991),
partition p1 VALUES less than (1996),
partition p2 VALUES less than (2001),
partition p3 VALUES less than MAXVALUE
)
小于多少年份分区
List分区：
Partition by list (列名) (
partition pNorth VALUES in (3,5,6,9,17),
partition pEast values in (1,2,10,11,19,20),
partition pWast VALUES in (4,12,13,14,18),
partition pCentral values in (7,8,15,16)
)
列名中包含的内容分区

索引

添加索引：
Alter table 表名 add index (列名);
删除索引：
Drop index 索引列名;
mysql查询结构
Explain select查询语句;

DCL数据控制语言

创建用户：
Create user ‘用户名’@’主机名’ identified by ‘密码’;
Create user ‘zhangsan’@’localhost’ identified by ‘123’;
添加用户条件：
Grant select,update,… on 数据库名 to 用户;
All： 添加所有权限
Geant all on test to ‘zhangsan’@’localhost’;
撤销权限：
Revoke all on数据库名 from 用户;
删除用户：
Drop user 用户;