mysql命令

一、MySql常用命令

1、mysql server 的基本操作

 #使用超级用户登录，按回车输入密码
 mysql -uroot -p
 #连接带远程主机
 mysql -h110.110.110.110 -uroot -p
 #退出MySQL命令
 exit
 #修改密码
 mysqladmin -u用户名 -p 旧密码 -password 新密码
 #添加root密码
 mysqladmin -uroot -password 123
 #修改root密码
 mysqladmin -uroot -p 旧密码 -password 新密码

 -- 命令行修改root密码
 update mysql.user SET password=PASSWORD("新密码") where user = 'root';
 flush privileges
 -- 显示当前的user
 select user();
 -- 增加新用户

2、基本命令

（1）显示表的结构

 -- 1)
 desc student;
 -- 2)
 DESCRIBE student;
 -- 3)
 show COLUMNS from student;

（2）显示表中所有内容

 SELECT * FROM student;

（3）行数据删除

删除table_name 表中的 deleter_tableid 为 delete_seq的行

delete from table_name where deleter_table = delete_seq

删除table_name表中的table_id从num开始的row_num列

delete from table_name where id=num order by table_id limit row_num

 -- 删除指定记录
 DELETE FROM student WHERE id = 1 OR id = 2;
 -- 删除指定行及后面的n条记录
 DELETE FROM student WHERE 1 ORDER BY id LIMIT 15;
 -- 将自增变量初始值设为 1
 alter table student auto_increment = 1;

事例

 DELETE FROM student WHERE id = 1 OR id = 2;

（4）创建表

 `CREATE TABLE student (id INT PRIMARY KEY ,name VARCHAR(255) ,sex CHAR(1))`:
 -- 创建表并定义字段

（5）删除表

drop table table_name：直接删除，若不存在则报错

drop table if exists table_name若存在则删除

 drop table student;
 drop table if exists student;

6、行数据读取

读取第一行

SELECT * from student order by id limit 0,1;

7、向表中插入数据

load data infile 文件路径 
into table 表名 fields terminated by ','
lines terminated by '\n'
ignore 1 rows;

8、主键约束、外键约束、唯一约束

-- 删除数据表主键约束
alter table 表名 drop primary key;
-- 创建表之后添加主键约束
alter table 表名 add primary key(字段名);
alter table 表名 modify 主键字段 char(4) primary key;
-- 在创建表时添加主键约束
create table books(
isbn char(4) primary key,
name varcahr(255),
author varchar(6)
-- 若要多个字段创建主键时
-- primary key(字段名1,字段名2)
);

-- 主键自动增长

-- 在创建表时添加外键约束
create table books(
isbn char(4) primary key,
name varcahr(255),
author varchar(6)
foreign key 字段名 references 外表名(字段名)
-- 另一种添加外键的方式，此方式可以删除外键约束而不会级联
-- constraint 外键逻辑名称 foreign key() references foreign_table(foreign_key);
);
-- 在已有表下创建外键约束
alter table 表名 add constraint 外键逻辑名称 foreign key(字段) references 外表名(字段);
-- 删除外键约束
alter table 表名 drop foreign key 外键逻辑名称;
-- 外键级联操作
	-- 在添加外键时增加级联修改和级联删除
alter table daily add constraint foreign key(Cno) references `course_2`(Cno) on update cascade on delete cascade;

-- 表中字段已存在时添加约束
alter table student add UNIQUE(`no`);
-- 取消unique约束
alter table student drop index `no`;

当表中属性被其他表占用时，可先将占用其属性的表的该属性设置为空，在使用update关键字进行修改

例：课程表course中的Cno主键属性被选课表sc作为外键占用sc.Cno = 1 ，若想修改course中的course.Cno = 5；则

-- 先将级联表解除占用
update sc set sc.Cno = null where sc.Cno = 1;
-- 接下来再修改值
update course set course.Cno = 5 where course.Cno = 1;

二、mysql常用函数

1、date函数

-- 可获取当前系统时间
SELECT NOW();
SELECT SYSDATE();

2、string函数

concat()：拼接字符串
upper()：转大写字母
lower()：转小写字母
substring(column, start, len)：截取字符从start开始截取len长度
REVERSE(s)：将字符串s的顺序反过来
REPLACE(s,s1,s2)	将字符串 s2 替代字符串 s 中的字符串 s1
REPEAT(s,n)	将字符串 s 重复 n 次
RTRIM(s)	去掉字符串 s 结尾处的空格
SPACE(n)	返回 n 个空格
TRIM(s)	去掉字符串 s 开始和结尾处的空格
INSERT(s1,x,len,s2)	字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串	

将字符串 runoob 重复三次：
	SELECT REPEAT('runoob',3) -- runoobrunoobrunoob
将字符串 abc 中的字符 a 替换为字符 x：
	SELECT REPLACE('abc','a','x') --xbc
去掉字符串 RUNOOB 的末尾空格：
	SELECT RTRIM("RUNOOB     ") AS RightTrimmedString;   -- RUNOOB

3、数字函数

ABS(x)	返回 x 的绝对值　　	
	返回 -1 的绝对值：
	SELECT ABS(-1) -- 返回1
AVG(expression)	返回一个表达式的平均值，expression 是一个字段	
	返回 Products 表中Price 字段的平均值：
	SELECT AVG(Price) AS AveragePrice FROM Products;
CEIL(x)	返回大于或等于 x 的最小整数　	
	SELECT CEIL(1.5) -- 返回2
COUNT(expression)	返回查询的记录总数，expression 参数是一个字段或者 * 号	
					返回 Products 表中 products 字段总共有多少条记录：
	SELECT COUNT(ProductID) AS NumberOfProducts FROM Products;
DEGREES(x)	将弧度转换为角度　　	
	SELECT DEGREES(3.1415926535898) -- 180
	n DIV m	整除，n 为被除数，m 为除数	
	计算 10 除于 5：
	SELECT 10 DIV 5;  -- 2
EXP(x)	返回 e 的 x 次方　　	
	计算 e 的三次方：
	SELECT EXP(3) -- 20.085536923188
FLOOR(x)	返回小于或等于 x 的最大整数　　	
	小于或等于 1.5 的整数：
	SELECT FLOOR(1.5) -- 返回1
GREATEST(expr1, expr2, expr3, ...)	返回列表中的最大值	
	返回以下数字列表中的最大值：
	SELECT GREATEST(3, 12, 34, 8, 25); -- 34
    返回以下字符串列表中的最大值：
    SELECT GREATEST("Google", "Runoob", "Apple");   -- Runoob
LEAST(expr1, expr2, expr3, ...)	返回列表中的最小值	
    返回以下数字列表中的最小值：
	SELECT LEAST(3, 12, 34, 8, 25); -- 3
    返回以下字符串列表中的最小值：
    SELECT LEAST("Google", "Runoob", "Apple");   -- Apple
MAX(expression)	返回字段 expression 中的最大值	
	返回数据表 Products 中字段 Price 的最大值：
	SELECT MAX(Price) AS LargestPrice FROM Products;
MIN(expression)	返回字段 expression 中的最小值	
	返回数据表 Products 中字段 Price 的最小值：
	SELECT MIN(Price) AS MinPrice FROM Products;
MOD(x,y)	返回 x 除以 y 以后的余数　	
	5 除于 2 的余数：
	SELECT MOD(5,2) -- 1
PI()	返回圆周率(3.141593）　　	
	SELECT PI() --3.141593
POW(x,y)	返回 x 的 y 次方　	
	2 的 3 次方：
	SELECT POW(2,3) -- 8
RAND()	返回 0 到 1 的随机数　　	
	SELECT RAND() --0.93099315644334
ROUND(x)	返回离 x 最近的整数	
	SELECT ROUND(1.23456) --1
SIGN(x)	返回 x 的符号，x 是负数、0、正数分别返回 -1、0 和 1　	
	SELECT SIGN(-10) -- (-1)
SQRT(x)	返回x的平方根　　	
	25 的平方根：
	SELECT SQRT(25) -- 5
SUM(expression)	返回指定字段的总和	
	计算 OrderDetails 表中字段 Quantity 的总和：
	SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;
TAN(x)	求正切值(参数是弧度)	
	SELECT TAN(1.75);  -- -5.52037992250933
TRUNCATE(x,y)	返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入）	
	SELECT TRUNCATE(1.23456,3) -- 1.234

连接查询

可以通过连接查询从多张数据表提取数据

在MySql中可以通过join关键字实现多表的连接查询

join按照其功能分为三个操作

• inner join 内连接

• left join 左连接

• right join右连接

1、内连接

-- 若直接做此操作，则获取的是两张数据表的笛卡尔积
select * from <table_name1> inner join <table_name2>;

-- 可以使用 where 关键字添加条件，先生成笛卡尔积再从笛卡尔积中筛选条件，效率低
select * from <table_name1> inner join <table_name2> 
where [条件];
-- 使用ON设置连接查询的匹配条件，不会先生成笛卡尔积，而是先判断连接条件是否成立，如果成立则将两张表的数据进行处理组合生成一条记录
select * from <table_name1> inner join <table_name2> 
on 匹配条件 where [条件];

2、外连接

-- 左外连接
select * from <table_name1> left join <table_name2> on 匹配条件
-- 右外连接
select * from <table_name1> right join <table_name2> on 匹配条件
-- 外连接不能使用 where 条件语句

SELECT course.Cno,Cname,AVG(grade) 
FROM course LEFT JOIN sc
on course.Cno = sc.Cno
where tname = "刘正"
GROUP BY course.Cno;

3、子查询

子查询 先进行一次查询，第一次查询的结果作为第二次查询的源\条件

CREATE PROCEDURE fun1 ( OUT result INT ) BEGIN
	DECLARE
		i INT DEFAULT 1;
		DECLARE 
		sum INT DEFAULT 0;
	WHILE
			i <= 10 DO
			SET sum = sum + i;
			SET i = i + 1;
		END WHILE;
		
		SELECT sum;
END;

set @m = 0;
CALL fun1(@m);