一、开启/关闭mysql服务
net start mysql //开启
net stop mysql //关闭
二、登陆mysql
mysql -uroot -p //密码默认回车
三、数据库操作
1.创建/删除/显示数据库
CREATE DATABASE IF NOT EXISTS my_db CHARACHTER SET gbk; -- 创建
DROP DATABASE my_db; -- 删除(慎重)
SHOW DATABASES; -- 显示所有数据库
SHOW CREATE DATABASE my_db; -- 显示指定数据库
2.修改数据库字符集
ALTER DATABASE my_db CHARACTER SET utf8;
3.使用指定数据库
USE my_db;
四、数据表操作
1.创建表/复制表
CREATE TABLE IF NOT EXISTS sys_student (
name VARCHAR(10),
age INT,
class VARCHAR(10)
math DECIMAL(4,1),
chinese DECIMAL(4,1)
);
CREATE TABLE sys_student _clone LIKE sys_student ; -- 复制表
2.删除表结构/表数据
DROP TABLE sys_student ; -- 删表结构
TRUNCATE TABLE sys_student; -- 删表数据
3.修改表名
ALTER TABLE sys_student RENAME TO sys_student _new;
4.修改列名称和数据类型
ALTER TABLE sys_student CHANGE age ag VARCHAR(10);
5.修改列数据类型
ALTER TABLE sys_student MODIFY age VARCHAR(10);
6.增加一列
ALTER TABLE sys_student ADD sex VARCHAR(10);
7.删除一列
ALTER TABLE sys_student DROP sex;
五、数据库表增、删、改
-- 增
INSERT INTO sys_student VALUES('张三',20);
-- 删
DELETE FROM sys_student WHERE name = '李四'; -- 条件删除
TRUNCATE TABLE sys_student ; -- 全删
-- 改
UPDATE sys_student SET name = 'zhansan' WHERE name ='张三';
六、数据库表表查询操作
-- 去重操作
SELECT DISTINCT name FROM sys_student ;
-- 四则运算查询
SELECT IFNULL (math,0) + IFNULL(chinese,0) AS total FROM sys_student ;
-- 与查询(&&)
SELECT * FROM sys_student WHERE age>20 && age<30;
SELECT * FROM sys_student WHERE age>20 AND age<30;
SELECT * FROM sys_student WHERE age BETWEEN 20 AND 30;
-- 或查询(||)
SELECT * FROM sys_student WHERE age=20 || age=30;
SELECT * FROM sys_student WHERE age=20 OR age=30;
SELECT * FROM sys_student WHERE age IN(20,30);
-- 非查询(NOT)
SELECT * FROM sys_student WHERE class IS NULL;
SELECT * FROM sys_student WHERE class IS NOT NULL;
-- 占位符
SELECT * FROM sys_student WHERE name LIKE '武_'; -- 占位符查询 (_)
SELECT * FROM sys_student WHERE name LIKE '_文%'; -- 占位符查询 (%)
七、查询语法规则
SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段
HAVING
筛选条件(聚合函数)
ORDER BY
排序(ASC,DESC)
LIMIT
分页限定(LIMIT(当前页,条数));
八、聚合函数
SELECT COUNT(id) FROM sys_student; # 统计数量
SELECT MAX(math) FROM sys_student; # 最大值
SELECT MIN(math) FROM sys_student; # 最小值
SELECT SUM(mant) FROM sys_student; # 求和
SELECT AVG(IFNULL(math,0)) FROM sys_student;# 求平均值
九、分组函数
SELECT is_male, AVG(math) FROM sys_student GROUP BY is_male; # 直接分组
SELECT is_male, AVG(math) FROM sys_student GROUP BY is_male HAVING COUNT(id)>2; # 条件限制
-- MySql分页
SELECT * FROM sys_student LIMIT 3,10; # 第三页,分页十条
SELECT * FROM sys_student LIMIT (当前页数-1)*每页条数,每页条数; # 分页公式
十、主键约束
ALTER TABLE sys_student MODIFY id INT PRIMARY KEY AUTO_INCREMENT; #添加主键约束
ALTER TABLE sys_student MODIFY id INT; # 删除主键约束
十一、非空约束
ALTER TABLE sys_student MODIFY is_male TINYINT NOT NULL; # 添加非空约束
ALTER TABLE sys_student MODIFY is_male TINYINT; # 删除非空约束
十二、唯一约束
ALTER TABLE sys_student MODIFY id INT UNIQUE; # 添加唯一约束
ALTER TABLE sys_student DROP INDEX name; # 删除唯一约束
十三、外键约束
ALTER TABLE sys_student ADD CONSTRAINT FK_student_class FOREIGN KEY(class_id) REFERENCES sys_class(id); # 添加外键约束
ALTER TABLE sys_student DROP FOREIGN KEY FK_student_class; # 删除外键约束
十四、MySql 备份
-- 备份
mysqldump -uroot -p my_db > e://mysql.sql
-- 导入
mysql -uroot -p
CREATE DATABASE my_db2;
SOURCE e://mysql.sql;
十五、多表全查(笛卡尔积)
SELECT * FROM emp,dept;
十六、内连接
SELECT * FROM emp t1,dept t2 WHERE t1.dept_id = t2.id;
SELECT * FROM emp t1 (INNER) JOIN dept t2 ON t1.dept_id = t2.id;
十七、外连接
-- 左连接
SELECT * FROM emp t1 LEFT (OUTER) JOIN dept t2 ON t1.dept_id = t2.id;
-- 右连接
SELECT * FROM emp t1 RIGHT (OUTER) JOIN dept t2 ON t1.dept_id = t2.id;
十八、子连接
1、 单行单列:A语句的结果当B语句的条件
-- 查询员工工资小于平均工资的人
SELECT
*
FROM
emp
WHERE
emp.`salary` = (SELECT MAX(salary) FROM emp);
2、 多行单列:关键字IN
-- 查询'财务部'和'市场部'所有的员工信息
SELECT
*
FROM
emp
WHERE
dept_id
IN
(SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');
3、多行多列:采用虚拟表
-- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
SELECT
*
FROM
dept t1 ,
(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2
WHERE
t1.id = t2.dept_id;
查询测试用表:
– 部门表
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
INSERT INTO dept (NAME) VALUES (‘开发部’),(‘市场部’),(‘财务部’);
– 员工表
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
gender CHAR(1), # 性别
salary DOUBLE, # 工资
join_date DATE, # 入职日期
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES dept(id) # 外键,关联部门表(部门表的主键)
);
摘自:
https://blog.csdn.net/mollen/article/details/82421316
https://blog.csdn.net/mollen/article/details/82357153
https://blog.csdn.net/mollen/article/details/82389940