MySQL数据库操作

太过爱你忘了你带给我的痛 2022-02-15 12:15 304阅读 0赞

**一、开启/关闭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_82421316]  
[https://blog.csdn.net/mollen/article/details/82357153][https_blog.csdn.net_mollen_article_details_82357153]  
[https://blog.csdn.net/mollen/article/details/82389940][https_blog.csdn.net_mollen_article_details_82389940]

[https_blog.csdn.net_mollen_article_details_82421316]: https://blog.csdn.net/mollen/article/details/82421316
[https_blog.csdn.net_mollen_article_details_82357153]: https://blog.csdn.net/mollen/article/details/82357153
[https_blog.csdn.net_mollen_article_details_82389940]: https://blog.csdn.net/mollen/article/details/82389940