MySQL学习笔记

一、MySQL设置

MySQL服务器的启动和停止

net start 服务名

net stop 服务名

MySQL服务的登录和退出

方式一：通过mysql自带的客户端

只限于root用户

方式二：通过windows自带的客户端

Mysql [-h主机名 –p端口号]  –u用户名 –p密码

例mysql –h localhost –P 3306 –u root –p000000

退出：

Exit或Ctrl+c

• mysql修改密码
• 

常见命令：

1. 查看当前所有的数据库   show databases;
2.  打开指定的库    use 库名
3. 查看当前库的所有表   show tables；
4. 查看其他库的所有表  show tables from 库名；
5. 创建表  create table 表名（

列名 列类型，

列名 列类型，

）

1. 查看表结构  desc 表名；
2. 查看服务器的版本

方式一：登录到mysql服务端

select version();

方式二：没有登录到mysql服务端

mysql - -version 或 mysql - -V

二、查询

#查询表中的单个字段
SELECT last_name FROM employees;
#查询表中的多个字段
SELECT last_name,salary,email FROM employees;
#查询表中的所有字段

SELECT * FROM employees;

#查询常量值
SELECT 100;
SELECT 'john';
#查询表达式
SELECT 100%98;
#查询函数
SELECT VERSION();
#起别名
SELECT 100%98 AS 结果;

#方式一：使用as
SELECT last_name AS 姓,first_name AS 名 FROM employees;
#方式二:使用空格
SELECT last_name 姓,first_name 名 FROM employees;

SELECT salary AS "out put" FROM employees;

#去重
SELECT DISTINCT department_id FROM employees;

#拼接
SELECT CONCAT('a','b','c') AS 结果;

#为NULL的处理

IFNULL(`commission_pct`,0)

#筛选

#一、按条件表达式筛选
#案例1：查询工资>12000的员工信息
SELECT
*
FROM
employees
WHERE
salary>12000;

 

#三、模糊查询

like

between and
in
is null|is not null
/*
特点
①一般和通配符搭配使用
通配符：
%任意多个字符

last_name LIKE '_$_%' ESCAPE '$';   #ESCAPE指定转义

四、安全查询   <=>

#案例2：查询工资为12000的员工信息
SELECT
last_name,
salary
FROM
employees
WHERE
salary <=>12000;

 

区别：

is null:仅仅可以判断NULL值，可读性较高

<=> ：既可以判断NULL值，又可以判断普通的数值，可读性较低

六、分组函数

功能：用作统计使用

分类：

sum 求和、avg 平均值、max 最大值、min 最小值、count 计算个数

特点：

1.sum/avg一般用于处理数值型

   max/min/count可以处理任何类型

2.已上分组函数都忽略null值

3、可以和distinct搭配使用，去重

七、分组查询

#分组前筛选

#查询邮箱中包含a字符的，每个部门的平均工资
SELECT AVG(salary),department_id,email
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id

#分组后筛选

#案例3：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;

#排序

SELECT department_id,COUNT(*),AVG(salary) a
FROM employees
GROUP BY department_id
ORDER BY a DESC;

#连接查询
SELECT * FROM beauty;
SELECT * FROM boys;

SELECT NAME,boyName
FROM boys,beauty
WHERE beauty.boyfriend_id=boys.id;

#修改表

#修改单表的记录
USE test;
UPDATE beauty SET phone = '1112'
WHERE NAME LIKE '柳%';

#修改多表的记录
#修改张无忌的女朋友的手机号为114
UPDATE boys bo
INNER JOIN beauty b ON bo.`id`=b.`boyfriend_id`
SET b.phone `id``boyfriend_id`= '114'
WHERE bo.boyname = '张无忌';

#删除表

#单表的删除delete
DELETE FROM beauty WHERE phone LIKE '%2';

#多表的删除
#案例：删除张无忌的女朋友的信息
DELETE b
FROM beauty b
INNER JOIN boys bo ON b.`boyfriend_id` = bo.`id`
WHERE bo.`boyName`='张无忌';

#创建表
USE myemployees;
CREATE TABLE my_employees(
Id INT(10),
First_name VARCHAR(10),
Last_name VARCHAR(10),
Userid VARCHAR(10),
Salary DOUBLE(10,2)
);

#显示表结构
DESC my_employees;

#向表中插入数据
INSERT INTO my_employees
VALUES(1,'patel','Ralph','Rpatel',895),
(2,'Dance','Betty','Bdance',860),
(3,'Biri','Ben','Bbiri',1100),
(4,'Newman','Chad','Cnewman',750),
(5,'Ropeburn','Audrey','Aropebur',1550);

#DDL语言

#创建库
CREATE DATABASE IF NOT EXISTS books;

#更改库的字符集
ALTER DATABASE books CHARACTER SET gbk;

#库的删除
DROP DATABASE IF EXISTS books;

#修改表
#修改列名
ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;
#修改列的类型或约束
ALTER TABLE book MODIFY COLUMN Userid TIMESTAMP;
#添加新列
ALTER TABLE book ADD COLUMN annual DOUBLE;
#删除列
ALTER TABLE book DROP COLUMN annual;
#修改表名
ALTER TABLE book RENAME TO book_author;

#表的删除
DROP TABLE IF EXISTS book_author;
SHOW TABLES;

#表的复制

#创建表
USE books;
CREATE TABLE author(
Id INT(10),
au_name VARCHAR(10),
nation VARCHAR(10)
);
#表的复制
INSERT INTO author VALUES
(1,'村上春树','日本'),
(2,'莫言','中国'),
(3,'冯唐','中国');
#仅仅复制表的结构
CREATE TABLE copy LIKE author;
#复制表的结构和数据
CREATE TABLE copy2
SELECT * FROM author;
#只复制部分数据
CREATE TABLE copy3
SELECT id,au_name
FROM author
WHERE nation = '中国';
#仅仅复制某些字段
CREATE TABLE copy4
SELECT id,au_name
FROM author
WHERE 0;

#不跨库复制表结构
USE myemployees;
CREATE TABLE dept2
SELECT department_id,department_name
FROM departments;

#跨库复制表结构
USE books;
CREATE TABLE dept2
SELECT department_id,department_name
FROM myemployees.departments;

#添加列级约束
USE students;
CREATE TABLE stuinfo(
id INT PRIMARY KEY,#主键
stuName VARCHAR(20) NOT NULL,#非空
gender CHAR(1) CHECK(gender='男' OR gender='女'),#检查
seat INT UNIQUE,#唯一
age INT DEFAULT 18#默认约束
);

#通用的写法：
CREATE TABLE IF NOT EXISTS stuinfo(
id INT PRIMARY KEY,
stuname VARCHAR(20) NOT NULL,
sex CHAR(1),
age INT DEFAULT 18,
seat INT UNIQUE,
majorid INT,
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);