MYSQL 学习笔记

1、创建数据库

 create database database_name; 
 show databases;
 use database_name; 

2、在数据库中建表（给定字段加约束条件，数据类型）

CREATE TABLE dept(deptno INT(2), 
   dname VARCHAR(14), 
   loc VARCHAR(13)); 

DESCRIBE dept ；

3、追加一个新列

ALTER TABLE table_name ADD job_id varchar(15); 
describe dept80；

4、数据增删改查

①插入给定数据到表中

INSERT INTO employees (employee_id, first_name,  department_id) 
VALUES  (1130, 'Louis', 200);       

②更新数据

UPDATE employees SET department_id = 70 
WHERE employee_id = 113; 

③删除指定的记录

DELETE FROM departments WHERE department_name = 'Finance'; 

DELETE FROM table_name; #删除全部数据

5、分组、子查询、关联、distinct用法以及常见函数的使用

①分组数据

group by
SELECT department_id, AVG(salary) 
FROM employees 
GROUP BY department_id；

②HAVING#过滤分组 ，使用了组函数，满足HAVING 子句中条件的分组将被显示

SELECT 
department_id, MAX(salary) 
FROM employees GROUP BY department_id 
HAVING MAX(salary)>10000 ;

③子查询

select first_name from employees where
department_id in(
select department_id from departments
where location_id=1700
)；

④多表查询

SELECT
table1.column, table2.column 
FROM table1, table2
WHERE table1.column1 = table2.column2; 

⑤关联（join on)

SELECT 
bt.id,NAME,boyname 
FROM beauty btInner join boys b On bt.`boyfriend_id`=b.id ; 

6、创建存储过程及调用

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
存储过程体（一组合法的SQL语句）
END

案例演示-空参列表

SELECT * FROM admin;
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO admin(username,`password`) 
VALUES('john1','0000'),('lily','0000'),('rose','0000'),('jack','0000'),('tom','0000');
END $
#调用
CALL myp1()$

7、创建函数及调用

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
	函数体
END

案例演示

CREATE FUNCTION myf1() RETURNS INT
BEGIN
	DECLARE c INT DEFAULT 0;#定义局部变量
	SELECT COUNT(*) INTO c#赋值
	FROM employees;
	RETURN c;	
END $
SELECT myf1()$

8、创建用户以及授权

允许外网IP访问数据库editest，本命令包含上面的命令，是所有的IP都可以访问该数据库
create user 'editest'@'%' identified by 'editest123456';

允许本地 IP访问localhost的Mysql数据库
create user 'username'@'localhost' identified by 'mima';
用户创建完成后，刷新授权
flush privileges;


将改用户editest赋权给数据库editestdb，并刷新授权
grant all privileges on `editestdb`.* to 'editest'@'localhost';
grant all privileges on `editestdb`.* to 'editest'@'%'; 
flush privileges;

9、导入数据

先查询当前的sql_mode,再设置sql_mode的模式

show variables like 'sql_mode';
set sql_mode='';

案例演示

load data infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/cjsj.csv' 
into table kcxxsj character set gb2312 
fields terminated by ',' optionally enclosed by '"' escaped by '"'
lines terminated by '\r\n'
ignore 1 lines;

报错

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

解决方法
查看secure-file-priv设置

show variables like '%secure%';

将文件放入secure显示的路径下，刷新后再次导入。