数据库管理

--查看MySQL默认端口号
show global variables like 'port';   

--查看数据库所有全局变量
SHOW VARIABLES   

-- 登录数据库
mysql -u root -p;  
  
-- 查看所有数据库
show databases;     

--  创建数据库
create database 数据库名称;   

--指定数据库字符集
default character set utf8;   
 
--删除一个数据库
drop database 数据库名称; 
 
--  查看数据库字符集
show creare database 数据库名称;  

-- 修改数据库字符集(utf8)
alter database 数据库名称 default character set gbk;  

表管理

--  进入数据库
use 表名;    

-- 查看所有表
show tables;  

--     创建表（内为 字段名 类型） student;    查看标内结构
create table 表名(字段名 类型(长度),字段名 类型(长度)......);

-- 删除表
drop table 表名;    

-- 查看表中所有字段
desc 表名;

--  添加表字段（字段名 类型
alter table 表名 add 字段名 类型(长度);    

--    删除表中字段（字段名）
alter table 表名 drop 字段名;

--  修改字段存储类型(字段名 类型)
alter table 表名 modify 字段名 类型(长度) 

--  修改字段名(字段名 类型)
alter table 表名 change 原字段名 字段名 类型(长度) 

-- 修改表名
alter table student rename to teacher; 

增删改

-- 插入所有字段
INSERT INTO 表名 VALUES(字段值,字段值...... );  

--  插入部分字段
INSERT INTO 表名 (字段名,字段名) VALUES(字段值,字段值);    

--修改所有数据
UPDATE 表名 SET 字段名 = 字段值;    

-- 修改单条数据值
UPDATE 表名 SET 字段名 = 字段值 WHERE 条件字段名 =条件值;    

 -- 修改多个字段值
UPDATE 表名 SET 字段名 = 字段值,字段名 = 字段值 WHERE 条件字段名 =条件值;    

-- 删除数据
DELETE FROM 表名 WHERE 条件字段名 =条件值;      

-- 重置表
TRUNCATE TABLE 表名   

查

-- 查询表
SELECT * FROM 表名;    

 -- 查询指定列
SELECT 字段名,字段名 FROM 表名;

-- 查询时指定别名
SELECT 字段名 AS 别名值,字段名 AS 别名值 FROM 表名;

-- 查询是增加常量列
SELECT 字段名,字段名,字段名,常量名 AS 别名值 FROM 表名;

-- 查询时合并列（只能合并数值类型）
SELECT 字段名,字段名,(字段名+字段名) AS 别名值 FROM 表名;

-- 查询去重 另一种语法 SELECT DISTINCT(age) FROM `user`
SELECT DISTINCT 字段名 FROM 表名;

 -- 条件查询
SELECT * FROM 表名 WHERE 条件字段名 =条件值;

# 条件查询
-- 逻辑条件 and代表(&&)  or 代表(||)
SELECT * FROM 表名 WHERE 条件字段名 =条件值 AND 条件字段名 =条件值;

-- 比较条件 > < >= <=    ==    <>(不等于)  BETWEEN AND(范围值)
SELECT * FROM 表名 WHERE 数值字段名 BETWEEN 起始值 AND 结束值;

-- 判空条件 (NULL 空字符串)    IS NULL (判空)    IS NOT NULL(判非空)      = ' ' (判断等于空字符串)    <>' '(判断不等于空字符串)

-- 判断null
SELECT * FROM 表名 WHERE 字段名 IS NULL ;

--  (不包括null和空字符串)
SELECT * FROM 表名 WHERE 字段名 IS NOT NULL AND 字段名<>''; 


# 模糊条件(LIKE)
-- 模糊查询 % 可以替换任意下标字符, _ 只能替换一个字符
SELECT * FROM 表名 WHERE NAME LIKE '张%';

-- 常用的聚合函数 sum(求和) avg(平均数) max(最大数) min(最小数) count(个数)

-- 查询学生英语总成绩
SELECT SUM(字段名)  FROM student2;

-- 统计学生平均分
SELECT AVG(字段名) FROM student2;

-- 查询学生个数
SELECT COUNT(字段名) FROM student2;


 ## 分页查询 LIMIT(起始行,查询多少行)
-- 分页查询(当前页-1)*每页条数
SELECT * FROM 表名 LIMIT 0,2;   

-- 查询排序 语法 ORDER BY 字段 ASC/DESC
-- ASC 顺序，正序，数组递增
-- DESC 倒叙 反序，数组递减 
SELECT * FROM 表名 ORDER BY id ASC;

-- 按照英语正序并且数学到序排序
SELECT * FROM 表名 ORDER BY english ASC,math DESC;

-- 分组查询(GROUP BY)
-- 查询男女人数
SELECT 字段名,COUNT(字段名) FROM 表名 GROUP BY 字段名;

##分组筛选(HAVING)
-- 查询人数大于二的性别(分组之前的条件用 WHERE 分组之后用 HAVING)
SELECT 字段名,COUNT(字段名) FROM 表名 GROUP BY gender HAVING COUNT(字段名) >2;

数据约束

-- 主键(PRIMARY KEY)
CREATE TABLE 表名(字段名  字段类型 PRIMARY KEY );

-- DEFAULT 默认值
CREATE TABLE 表名(字段名 类型,字段名 类型(长度),字段名 类型(长度) DEFAULT '没有添加地址');

-- 非空 NOT NULL
CREATE TABLE 表名(字段名 类型,字段名 类型(长度),字段名 类型(长度) NOT NULL);

-- 不能重复(UNIQUE)
CREATE TABLE 表名(字段名 类型 UNIQUE);

-- 自增长（AUTO_INCREMENT ）(ZEROFILL 零填充)
CREATE TABLE 表名(字段名  类型(长度)ZEROFILL PRIMARY KEY AUTO_INCREMENT);

-- 外键,多张表联合使用(CONSTRAINT 外键名 FOREIGN KEY(外键) REFERENCES 参考表（参考字段）)
CREATE TABLE test2(id  INT PRIMARY KEY CONSTRAINT asdf FOREIGN KEY id REFERENCES student2(id));


--  多表查询 (INNER JOIN  ON) 

-- 内连接查询SELECT * FROM 表名,关联表名称 WHERE 关联条件;
SELECT * FROM user,student2 WHERE user.cid = student2.id;

-- SELECT * FROM 表名 INNER JOIN 关联表名称 ON 关联条件;
SELECT * FROM `user` INNER JOIN student2 ON user.cid = student2.id;

-- 左外连接查询(LEFT OUTER JOIN)
SELECT * FROM student2 d LEFT JOIN `user` e ON  d.id = e.id;

-- 右外连接查询
SELECT * FROM `user` RIGHT JOIN student2 ON `user`.id = student2.id;

--  自连接
SELECT e.`name`,b.`name` FROM `user` AS e LEFT JOIN `user` AS b ON e.id=b.id;

存储过程

CREATE PROCEDURE por_test()
BEGIN 
-- 可以写多个SQL语句
SELECT * FROM `user`;
END;

-- 执行存储过程
CALL por_test();

-- 删除存储过程
DROP PROCEDURE pro_test2;

-- 带有输入参数存储过程
CREATE PROCEDURE pro_find(IN id INT)
BEGIN SELECT * FROM `user` WHERE id = id;
END

CALL pro_test2(@name);

-- 带有输出参数存储过程
CREATE PROCEDURE pro_test2(OUT str VARCHAR(100))
BEGIN
SET str = '这是一个输出参数';
END

-- 调用带参数存储过程
CALL pro_test2(@name);

-- 获取返回值
SELECT @`name`; 



-- 带有输入输出存储过程
CREATE PROCEDURE pro_test2(INOUT str VARCHAR(100))
BEGIN
SELECT str;
SET str = '十里桃花';
END;

SET @name = '三生三世';
CALL pro_test2(@name);

SELECT @`name`;

-- 带有循环存储过程
CREATE PROCEDURE pro_test3(IN num INT,OUT reslt INT)
BEGIN
        -- 定义局部变量
        DECLARE i INT DEFAULT(1);
        DECLARE vsum INT DEFAULT(0);
        WHILE i <= num DO
        SET vsum = vsum+i;
        SET i = i+1;
END WHILE;
SET reslt = vsum;
END;

CALL pro_test3(100,@vsum);
SELECT @vsum;

触发器

CREATE TRIGGER tri_student AFTER INSERT ON student2 FOR EACH ROW 
INSERT INTO `user`(id,account,`password`,gender,address,name) VALUES (12,12312,asdfas,'女',asdfas,'李四');

分配权限账号

GRANT SELECT DELETE ON day16.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';

备份

mysqldump -uroot -p day17 > c:/bak.sql

恢复

mysql -uroot -p day17 < d:/back.sql