1.数据库的英文单词：DataBase 简称：DB
2.什么是数据库？
- 用于存储和管理数据的仓库
3.数据库的特点：
- 持久化存储数据。其实数据库就是一个文件系统
- 方便存储和管理数据
- 使用了统一的方式操作数据库 -- SQL
MySQL数据库软件
MySQL的卸载
1.安装
2.卸载
1.去mysql的安装目录找到my.ini文件并打开
- 复制datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"
2.卸载MySQL
3.删除C:/ProgramData目录下的MySQL文件夹 （隐藏文件）
3.配置
MySQL服务启动
- 手动。
- cmd--> services.msc 打开服务的窗口
- 使用管理员打开cmd
net start mysql :启动mysql的服务 net stop mysql :关闭mysql的服务
MySQL登陆
1.mysql -uroot -p密码 2.mysql -hip -uroot -p 连目标的密码 3.mysql --host=ip --user=root --password=连接目标的密码 连接mysql3306端口命令 mysql -h192.168.199.70 -uroot -p123456 连接非3306端口(指定其他端口) 的命令 mysql -h192.168.199.120 -P3308 -uroot -p1234
SQL
1.什么是SQL ？
- Structured Query Languange：结构化查询语言
其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方，称为“方言”。
进入数据库
SQL通用语法
- SQL语句可以单行或多行书写，以分号结尾。
- 可使用空格和缩进来增强语句的可读性。
- MySQL数据库的SQL 语言不区分大小写，关键字建议使用大写。
- 3种 注释
- 单行注释： -- 注释内容 或 # 注释内容（mysql特有）
- 多行注释： /*注释*/
SQL分类
1.DDL(Data Definition Language)数据定义语言
- 用来定义数据库对象：数据库、表、列等。关键字：create，drop，alter等
2.DML(Data Manipulatio Language)数据操作语言
- 用来对数据库中表的数据进行增删改。关键字：insert，delete，update 等
3.DQL(Data Query Language)数据库查询语言
- 用来查询数据库中表的记录（数据）。关键字：select，where 等
4.DCL(Data Control Language)数据控制语言（了解）
用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANY，REVOKE 等。
DDL:操作数据库表
1.操作数据库：CRUD
- 创建数据库：
create database 数据库名称
- 创建数据库，判断不存在，再创建：
create database if not exists 数据库名称；
- 创建数据库，并指定字符集
create database 数据库名称 character set 字符集名；
- 练习：创建db3数据库，判断是否存在，并指定字符集为gbk
create database if not exists db3 character set gbk;
mysql> create database abc003 character set gbk; Query OK, 1 row affected (0.00 sec)
2.R(Retrieve):查询
- 询所有数据库的名称：
show databases；
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | abc001
- 查询某个数据库的字符集：查询某个数据库的创建语句
show create database 数据库名称；
mysql> show create database abc001; +----------+-----------------------------------------------------------------------------------------+ | Database | Create Database | +----------+-----------------------------------------------------------------------------------------+ | abc001 | CREATE DATABASE `abc001` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */ | +----------+-----------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
3.U(Update):修改
- 修改数据库的字符集
alter database 数据库名称 character set 字符集名称：
mysql> alter database abc001 character set gbk; Query OK, 1 row affected (0.00 sec) mysql> show create database abc001; +----------+----------------------------------------------------------------+ | Database | Create Database | +----------+----------------------------------------------------------------+ | abc001 | CREATE DATABASE `abc001` /*!40100 DEFAULT CHARACTER SET gbk */ | +----------+----------------------------------------------------------------+ 1 row in set (0.00 sec)
4.D(Delete):删除
- 删除数据库
drop database 数据库名称；
- 判断数据库存在，存在再删除
drop database if exists 数据库名称；
5:使用数据库
- 查询当前正在使用的数据库名称
select database();
- 使用数据库
use 数据库名称；
2.操作表
1.C(Create):创建
- 语法;
create table 表名( 列名1 数据类型1， 列名2 数据类型2， ... 列名n 数据类型n );
注意：最后一列，不需要加逗号（，）
- 数据类型：
1.int：整数类型
age int，
2.double：小数类型
score duoble （3，7）
3.date：日期，只包含年月日，yyyy-MM-dd
4.datetime：日期，包含年月日时分秒 ，yyyy-MM-dd HH：mm；ss
5.timestamp：时间类型 包含年月日时分秒 yyyy-MM-dd HH：mm；ss
如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值
6.varchar：字符串
name varchar(20):姓名最大20的字符 cainiao 7个字符 张三 2个字符
- 创建表
create table student( id int, name varchar(37), age int, score double(4.1), birthday data, insert_time timestamp );
- 复制表：
create table 表名 like 被复制的表名
mysql> create table abc004 like abc; Query OK, 0 rows affected (0.15 sec) mysql> show tables; +------------------+ | Tables_in_abc001 | +------------------+ | ab001 | | abc | | abc004 | +------------------+ 3 rows in set (0.00 sec)
2.R(Retrieve):查询
- 查询某个数据库中所有的表名称
show tables；
- 查询表结构
desc 表名；
3.U(Update):修改
- 1.修改表名
alter table 表名 rename to 新的表名；
mysql> alter table abc rename to abc002; Query OK, 0 rows affected (0.12 sec) mysql> show tables; +------------------+ | Tables_in_abc001 | +------------------+ | ab001 | | abc002 | | abc004 | +------------------+ 3 rows in set (0.00 sec)
- 2.修改表的字符集
alter table 表名 character set 字符集名称；
- 3.添加一列
alter table 表名 add 列名 数据类型；
- 4.修改列名称 类型
alter table 表名 change 列名 新列名 新列别 新数据类型； alter table 表名 modify 列名 新数据类型；
- 5.删除列
alter table 表名 drop 列名；
4.D(Delete):删除
drop table 表名； drop table if exists 表名；//先判断再删除
mysql> drop table abc002; Query OK, 0 rows affected (0.01 sec) mysql> show tables; +------------------+ | Tables_in_abc001 | +------------------+ | ab001 | | abc004 | +------------------+ 2 rows in set (0.00 sec)
DML：增删改表中数据
添加数据：
- 语法：
insert into 表名(列名1，列名2，...列名n) values(值1，值2，...值n)；
- 注意：
1.列名和值要一一对应。
2.如果表名后，不定义列名，则默认给所有列添加值。
insert into 表名 values(值1，值2，...值n)；
mysql> create table abc003(id int,name varchar(37),age int); --创建表 mysql> show tables; +------------------+ | Tables_in_abc001 | +------------------+ | ab001 | | abc003 | | abc004 | +------------------+ mysql> desc abc003; --查询表结构 +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(37) | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ mysql> insert into abc003 values(1,'cai',18),(2,'chuan',20),(3,'qi',30); --增加列 mysql> select * from abc003; --查询列 +------+-------+------+ | id | name | age | +------+-------+------+ | 1 | cai | 18 | | 2 | chuan | 20 | | 3 | qi | 30 | +------+-------+------+
3.除了数字类型，其他类型需要使用引号（单双都可以）引起来
2.删除数据：
- 语法：
delete from 表名 [where 条件]
mysql> show tables; +------------------+ | Tables_in_abc001 | +------------------+ | abc003 | | abc004 | +------------------+ 2 rows in set (0.00 sec) mysql> desc abc003; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(37) | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> select * from abc003; +------+-------+------+ | id | name | age | +------+-------+------+ | 1 | cai | 18 | | 2 | chuan | 20 | | 3 | qi | 30 | +------+-------+------+ 3 rows in set (0.00 sec) mysql> delete from abc003 where id=1; --删除列中的值 Query OK, 1 row affected (0.45 sec) mysql> select * from abc003; --查询列 +------+-------+------+ | id | name | age | +------+-------+------+ | 2 | chuan | 20 | | 3 | qi | 30 | +------+-------+------+
- 注意：
- 如果不加条件，则删除表中所有记录。
- 如果要删除所有记录
- delete from 表名；-- 不推荐使用。有多少条记录就会执行多少次删除操作。
- TRUNCATE TABLE 表名； -- 推荐使用，效率更高 先删除表，然后再创建一张一样的表。
3.修改数据
- *语法：
update 表名 set 列名1 = 值1，列名2 = 值2，...[where 条件];
mysql> update abc003 set age=99 where id=3; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 215 Current database: abc001 Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from abc003; +------+-------+------+ | id | name | age | +------+-------+------+ | 2 | chuan | 20 | | 3 | qi | 99 | +------+-------+------+ 2 rows in set (0.00 sec)
注意：
- 如果不加任何条件，则会将表中所有记录全部修改。
DQL：查询表中记录
select * from 表名；
1.语法
select 字段列表 from 表名列表 where 条件列表 group by 分组字段 having 分组之后的条件 order by 排序 limit 分页限定
2.基础查询
1.多个字段的查询
select 字段名1，字段名2... from 表名；
mysql> insert into abc003 values(1,'cai',18),(4,'test',30),(5,"ddd",66); ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 216 Current database: abc001 Query OK, 3 rows affected (0.13 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from abc003; +------+-------+------+ | id | name | age | +------+-------+------+ | 1 | cai | 18 | | 2 | chuan | 20 | | 3 | qi | 99 | | 4 | test | 30 | | 5 | ddd | 66 | +------+-------+------+ 5 rows in set (0.00 sec)
mysql> select id,name from abc003; +------+-------+ | id | name | +------+-------+ | 1 | cai | | 2 | chuan | | 3 | qi | | 4 | test | | 5 | ddd | +------+-------+ 5 rows in set (0.10 sec)
注意：
- 如果查询所有字段，则可以使用*来替代字段列表。
2.去除重复
distinct
3.计算列
一般可以使用四则运算计算一些列的值。（一般只会进行数值型的计算）
ifnull(表达式1，表达式2);
- 表达式1：那个字段需要判断是否为null
- 如果改字段为null后的替换值。
4.起别名
as ：as也可以省略
条件查询where
https://www.runoob.com/sql/sql-where.html
1.where子句后跟条件
2.运算符 >,<,>=,<=,=,<> BETWEEN...AND IN(集合) LIKE IS NULL and or && or or || not or !
mysql> select * from abc003 where id=2;+------+-------+------+ | id | name | age | +------+-------+------+ | 2 | chuan | 20 | +------+-------+------+ 1 row in set (0.12 sec) mysql> select * from abc003 where id>1 and id<4; +------+-------+------+ | id | name | age | +------+-------+------+ | 2 | chuan | 20 | | 3 | qi | 99 | +------+-------+------+ 2 rows in set (0.10 sec)
其他
- 排序查询
- 聚合函数
- 分组查询
- 分页查询
1.排序查询
语法：order by 子句 order by 排序字段1 排序方式1，排序字段2 排序方式2...
mysql> select * from abc003 order by name; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 218 Current database: abc001 +------+-------+------+ | id | name | age | +------+-------+------+ | 1 | cai | 18 | | 2 | chuan | 20 | | 5 | ddd | 66 | | 3 | qi | 99 | | 4 | test | 30 | +------+-------+------+ 5 rows in set (0.10 sec) mysql> select * from abc003 order by id desc; +------+-------+------+ | id | name | age | +------+-------+------+ | 5 | ddd | 66 | | 4 | test | 30 | | 3 | qi | 99 | | 2 | chuan | 20 | | 1 | cai | 18 | +------+-------+------+ 5 rows in set (0.00 sec)
排序方式：
ASC：升序，默认的 DESC：降序
注意：
如果有多个排序条件，则当前的条件值一样时，才会判断第二条件。
2.聚合函数
将一列数据作为一个整体，进行纵向的计算。
1.count：计算个数
- 一般选择非空的列：主键
- count(*)
mysql> select count(*) as age from abc003; +-----+ | age | +-----+ | 5 | +-----+ 1 row in set (0.00 sec)
2.max：计算最大值
3.min：计算最小值
4.sum：计算和
5.avg：计算平均值
注意：聚合函数的计算，排除null值。
解决方案：
- 选择不包含非空的列进行计算
- IFNULL函数
3.分组查询
语法：group by 分组字段
mysql> select name,sum(age) from abc003 group by name; +-------+----------+ | name | sum(age) | +-------+----------+ | cai | 18 | | chuan | 20 | | ddd | 66 | | qi | 99 | | test | 30 | +-------+----------+ 5 rows in set (0.00 sec)
注意：
1.分组之后查询的字段：分组字段、聚合函数
2.where 和 having的区别？
- where 在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来
- where 后不可以跟聚合函数，having可以进行聚合函数的判断。
4.分页查询
语法：limit开始的索引，每页查询的条数；
公式：开始的索引 = （当前的页码 - 1） * 每页显示的条数 --每页显示3条记录 SELECT * FROM student LIMIT 0.3； --第1页 SELECT * FROM student LIMIT 3.3； --第2页 SELECT * FROM student LIMIT 6.3； --第3页 limit 是一个MySQL"方言"
mysql> select * from abc003 limit 1; +------+------+------+ | id | name | age | +------+------+------+ | 1 | cai | 18 | +------+------+------+ 1 row in set (0.04 sec) mysql> select * from abc003 limit 3； +------+-------+------+ | id | name | age | +------+-------+------+ | 1 | cai | 18 | | 2 | chuan | 20 | | 3 | qi | 99 | +------+-------+------+ 3 rows in set (0.00 sec)
约束
概念：对表中的数据进行限定，保证数据的正确性、有效性和完整性。
分类：
主键约束：primary key 非空约束：not null 唯一约束：unique 外键约束：foreign key
非空约束：not null
1.创建表时添加约束
CREATE TABLE stu( id INT, NAME VARCHAR(20) NOT NULL --name为非空 );
2.创建表完后，添加非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;
3.删除name的非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20);
唯一约束：unique，值不能重复
1.注意：
唯一约束可以有null值，但是只能有一条记录为null
2.创建表时，添加唯一约束
CREATE TABLE stu( id INT, phone_number VARCHAR(20) UNIQUE --添加了唯一约束(手机号) );
3.删除唯一约束
ALTER TABLE stu DROP INDEX phone_number;
4.在表创建完后，添加唯一约束
ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;
主键约束：primary key
1.注意：
- 含义：非空且唯一
- 一张表只能有一个字段为主键
- 主键就是表中记录的唯一标识
2.在创建表时，添加主键约束
create table stu( id int primary key, --给id添加主键约束 name varchar(20) );
3.删除主键
--错误写法 alter table stu modify id int ; AlTER TABLE stu DROP PRIMARY KEY;
4.创建完表后，添加主键
ALTER TABLE stu MODIFY id INT PRIMARY KEY;
5.自动增长：
1.概念：如果莫一列是数值类型的，使用 auto_increment 可以来完成值的自动增长
2.在创建表时，添加主键约束，并且完成主键自增长
create table stu( id int primary key auto_increment, --给id添加主键约束 name varchar(20) );
3.删除自动增长
ALTER TABLE stu MODIFY id INT;
--添加自动增长
ALTER TABLE stu MODIFY id INT auto_increment
外键约束：foreign key
让表与表产生关系，从而保证数据的正确性。
1.在创建表时，添加外键
语法：
create table 表名( ... 外键列 constraint 外键名称 foregin key （外键列名称） references 主表名称（主表列名称） );
2.删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称；
3.创建表之后，添加外键
ALTER TABLE employee（表名） ADD CONSTRAINT（外键名称） emp_dept_fk FOREIGN KEY (dep_id（外键字段名称）) REFERENCES department(id)（主表名称（主表列名称））
4.级联操作
添加级联操作
语法：
ALTER TABLE 表名 ADD CONSTRAINT（外键名称） FOREIGN KEY (外键字段名称) REFERENCES 主表名称（主表列名称）
分类：
1.级联更新：
ON UPDATE CASCADE
2.级联删除：
ON DELETE CASCADE
1.多表之间的关系
1.一对一(了解） 如：人和身份证 分析：一个人只有一个身份证，一个身份证只能对应一个人 2.一对多（多对一）: 如：部门和员工 分析：一个部门有多个员工，一个员工只能对应一个部门 3.多对多： 如：学生和课程 分析：一个学生可以选择多个课程，一个课程也可以被多个学生选择
2.实现关系：
1.一对一 如：人和身份证 实现关系：一对一关系实现，可以在任意一方添加唯一外键指向另一方的主键。 2.一对多（多对一）: 如：部门和员工 实现关系：在多的一方建立外键，指向另一方的主键。 3.多对多： 如：学生和课程 实现关系：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键。
2.数据库设计的范式
概念：设计数据库时，需要遵循的一些规范。要遵循后边的范式要求，必须先遵循前边的所有范式要求
设计关系数据库时，遵从不同的规范要求，设计合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递规范，越高的范式数据库冗余越小。
目前关系数据库有六种范式：第一范式（1NF），第二范式（2NF），第三范式（3NF），巴斯-科德范式（BCNF），第四范式（4NF），
分类：
1.第一范式（1NF）：每一列都是不可分割的原子数据项
2.第二范式（2NF）：在1NF的基础上,非码属性必须完全依赖于候选码(在1NF基础上消除非主属性对主码的部分函数依赖)
几个概念: 1.函数依赖:A-->B,如果通过A属性(属性组)的值,可以确定唯一B属性的值,则称B依赖于A. 例如:学号-->姓名.(学号,课程名称)--> 分数 2.完全函数依赖:A-->B ,如果A是一个属性组,则B属性值的确定需要依赖于A属性组中所有的属性值. 例如:(学号,课程名称) --> 分数 3.部分函数依赖:A-->B,如果A是一个属性组,则B属性值的确定只需要依赖于A属性组某一些性值即可. 例如:(学号,课程名) -->姓名 4.传递函数依赖:A-->B,B-->C,如果通过A属性(属性组)的值,可以确定唯一B属性的值,再通过B属性(属性组)的值可以确定唯一属性的值,则称C传递函数依赖于A 5.码:如果在一张表中,一个属性或属性组,被其他所有属性所完全依赖,则称这个属性(属性组)为该表的码 例如:该表中码为:(学号,课程名称) 主属性:码属性组中的所有属性 非主属性:除了码属性组的属性
3.第三范式(3NF):在2NF基础上,任何非主属性不依赖于其他非主属性(在2NF基础上消除传递依赖)
多表查询
查询语法
select 列名列表 from 表名列表 where ...
准备sql
#创建部门表
CREATE TABLE dept();
#创建员工表
CREATE TABLE emp();
笛卡尔积
有两个集合A,B,去这两个集合的所有组成情况.
要完成多表查询,需要消除无用的数据
多表查询的分类:
1.内连接查询
- 隐式内连接:使用where条件消除无用数据
- 显示连接:语法:select 字段列表 from 表名1 [inner] join 表名2 on 条件
- 内连接查询:
- 从那些表中查询数据
- 条件是什么
- 查询那些字段
2.外连接查询
1.左外连接:
语法:select 字段列表 from 表1 left [outer] join 表2 on 条件; 查询的是左表所有数据以及其交集部分.
2.右外连接
语法:select 字段列表 from 表1 right [outer] join 表2 on 条件; 查询的是右表所有数据以及其交集部分.
子查询
概念:查询中嵌套查询,称嵌套查询为子查询
--查询工资最高的员工信息
--1查询最高的工资是多少 9000 SELECT MAX(salary) FROM emp; --2 查询员工信息,并且工资等于9000的 select * from emp WHERE emp.'salary'=9000; --一条sql就完成这个操作.子查询 SELECT * FROM emp WHERE emp.'salary' = (SELECT MAX(salary) FROM emp);
DCL：管理用户、授权
SQL分类:
- DDL:操作数据库和表
- DML：增删改表中的数据
- DQL:查询表中的数据
- DCL:管理用户、授权
DBA:数据库管理员
DCL:管理用户、授权
1.管理用户
1.添加用户:
语法:CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
2.删除用户
语法:DROP USER '用户名'@'主机名';
3.修改用户密码:
UPDATE USER SET PASSWORD('新密码') WHERE USER = '用户名';
UPDATE USER SET PASSWORD('abc') WHERE USER = 'cainiao';
SET PASSWORD FOR '用户名'@'主机名'=PASSWORD('新密码');
SET PASSWORD FOR 'root'@'localhost'=PASSWORD('123');
如果使用mysql中忘记了root用户的密码?
cmd --> net stop mysql 停止mysql服务 -- 需要管理员运行该cmd
使用无验证方式启动mysql服务: mysql --skip-grant-tables
打开新的cmd窗口,直接输入mysql命令,敲回车.就可以登陆成功
use mysql
uodate user set password = password('你的新密码') where user = 'root';
关闭两个窗口
打开任务管理器,手动结束mysql.exe的进程
启动mysql的服务
使用新密码登陆.
4.查询用户:
--1.切换到 mysql数据库
USE mysql ;
--2.查询user表
SELECT * FROM USER;
通配符:% 表示可以在任意主机使用用户登陆数据库
2.权限管理
--查询权限:
SHOW GRANTS FOR '用户名'@'主机名'; SHOW GRANTS FOE 'CAINIAO'@'%';
--授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
--给张三用户授予所有权限,在任意数据库任意表上
GRANT ALL ON *.* TO 'zhangsan'@ 'localhost';
--撤销权限:
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';