《MySQL系列》MySQL详细入门教程

本文链接：https://blog.csdn.net/m0_51197424/article/details/115582480

MySQL学习

在这里插入图片描述

MySQL语法

一事务

针对数据库多次DML操作的时候，保证数据完整性的一种手段

1 ACID

A:Aotomicity:原子性。不可被分割。也就是说事务中的所有操作要么成功，要么失败。

C:Consistency:一致性。事务执行之后，数据库的状态或者其他业务的规则保持一致。

I:Isolation:隔离性。指在并发操作中，不同的事务之间应该是隔离开来的，各事务之间互不干扰。

D:Durability:持久性。事务一旦提交成功，将不能回滚。

2 事务中产生的问题

事务中3中读的问题：
脏读: 读取到另外一个事务未提交的数据 ****脏读必须要解决*****
不可重复读: 对同一记录的两次读取不一致，另一事务对改记录进行了修改
幻读/虚读: 对同一张表的两次查询不一致，因为另一事务插入了一条记录

3 事务隔离级别

串行化: 避免事务中出现的所有问题，但是效率低

可重复读: 性能比串行化好，可以避免脏读和不可重复读，不可避免幻读，ORACLE的默认隔离级别

读已提交数据: 性能比可重复读好，可以避免脏读，MySQL的默认隔离级别

读未提交数据: 性能最好，但是任何问题都不能避免

4 手动提交事务

开启事务: START TRANSACTION
结束事务: COMMIT 或 ROLLBACK

二 DDL

1 show

show databases; -- 查询所有数据库
show tables; --查询所有表

2 create

-- 创建字符集为utf8的数据库zxy
create database if not exists zxy
character set = utf8;
-- 选用数据库
use zxy;
-- 创建表
create table if not exists zxy.user(
	id int,
    name varchar(20)
);

3 drop

drop database if exists zxy; -- 删除数据库
drop table if exists zxy.user; --删除数据表

4 alter

alter database zxy character set utf32; --修改字符集
alter table zxy.user rename users; --修改表名
alter table zxy.user modify id TINYINT NOT NULL,change name uname varchar(25); --修改列属性，列名
alter table zxy.user add age timestamp; -- 添加年龄列
alter table zxy.user drop column age; -- 删除列

三 DML

1 insert

insert into zxy.user(id,name) values(2,'zxy'); --向id,name插入值

2 update

update zxy.user set name = 'zxy'
where id = '1';
-- 将id=1的学生对应的name改为zxy

3 delete

delete from zxy.user; --删除全表
truncate table zxy.user;--删除全表记录
delete from zxy.user where id = 1; --删除指定条件的记录

四 DCL

1 分配权限

-- 分配权限
grant all privileges on *.* to 'root'@'%' identified by '111213' with grant option;
-- 刷新权限
flush privileges;

五 DQL

1 where

select * from user where id = 1;
-- 在where后的条件合理的情况下，可以查询到结果
-- 仅提供思路
-- 例如：
name = 'zxy';
id > 100;
sex = 'man' and name = 'zxy';
sex = 'man' or name = 'zxy';
id between 1 and 5
id in (1,2,3,4,5)
id not in (1,2,3,4,5)

2 like

-- %表示多个字符的占位符
select * from zxy.user
where name like 'z%';
-- _表示单个字符的占位符
select * from zxy.user
where name like 'zx_';

3 distinct

-- 去重查询
select distinct name from zxy.user;

4 ifnull

-- 如果为空，设置默认值
select id,name,ifnull(age,'0') from zxy.user;

5 count

-- 计算个数
select count(*) from zxy.user;

6 max

-- 统计出最大的数
select max(age) from zxy.user;

7 min

-- 统计出最小的数
select min(age) from zxy.user;

8 sum

-- 统计出总数
select count(age) from zxy.user;

9 group by

-- 按条件分组查询
select id,name,gender,sum(ifnull(salary,0)) as sum 
from zxy.user
group by gender
having id < 100;

10 limit

-- limit n
select * from zxy.user limit 1; -- 只查询第一条
-- limit m,n
select * from zxy.user limit 0,2; --从下标索引0处也就是第一行开始，往后取5行输出
select * from zxy.user limit 1,1; --取到第二条数据

11 order by

-- 查询用户信息，按年龄降序，女士有限原则
select * from zxy.user
order by age desc,gender asc;

六约束

1 primary key

非空
唯一
能被外键所引用

-- 方式一
create table if not exists zxy.user(
	id int primary key,
    name varchar(20)
);
-- 方式二
create table if not exists zxy.user(
	id int,
    name varchar(20)
    primary key(id)
);

1.1 alter … add

alter table zxy.user add primary key(id);

1.2 alter … drop

alter table zxy.user drop primary key(id);

1.3 自增长

create table if not exists zxy.user(
	id int AUTO_INCREMENT,
    name varchar(20),
    primary key(id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8;

2 foreign key

create table dept (
	id int(11) not null,
    name varchar(25) default null,
    primary key (id)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
-- 建表的同时，简历dept表的id的外键
create table emp (
	id int(11) not null,
    name varchar(25) defalut null,
    deptid int(11) not null,
    primary key (id),
    CONSTRAINT emp_dept FOREIGN KEY(deptid) references dept(id)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

2.1 alter … add

alter table zxy.user add constraint emp_dept foreign key(deptid) references dept(id);

2.2 alter … drop

alter table zxy.user drop foreign key emp_dept;

3 unique

create table user.zxy (
	id int(11) not null,
    name varchar(25) unique
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

4 Having和Where

HAVING后面可以写聚合函数、WHERE后面不可以写聚合函数
HAVING是对分组后的数据做的过滤，WHERE是对分组前的数据做的过滤
> WHERE是对分组千的数据做过滤，如果没有满足WHERE条件的过滤，将不参加分组
> HAVING是分组后的数据做过滤

5 去重distinct

select distinct deptno from emp

6 primary key主键设置

alter table stu add primary key(id)

7 unique 唯一性约束

alter table tableName add unique(username)

8 域完整性

alter table tableName modify colName type not null;

9 多重循环：


SELECT * 
FROM emp
WHERE sal > (
	SELECT sal FROM emp
	WHERE  empno = 7566
)

-- 查询工资大于10号部门的平均工资的非10号部门的员工信息
SELECT *
FROM emp 
WHERE sal >(
	SELECT AVG(
		IFNULL(sal,0)
		) 
	FROM emp 
	WHERE deptno = 10 
	)
AND deptno !=10

SELECT *,CASE
	WHEN sal >= 5000 THEN '第一等级'
	WHEN sal >= 3000 THEN '第二等级'
	ELSE '其他'
	END levels
FROM emp


SELECT ename ,CASE deptno
	WHEN 10 THEN '第一部门'
	WHEN 20 THEN '第二部门'
	ELSE '其他'
	END
FROM emp

10 时钟日历函数


SELECT CURDATE();
SELECT CURTIME();
SELECT NOW();
SELECT SYSDATE();
SELECT CURRENT_TIMESTAMP();

SELECT DAYOFMONTH();
SELECT WEEKDAY(NOW());
SELECT DAYNAME('2020-06-04')

SELECT DAYOFWEEK(NOW())
SELECT DAYOFYEAR(NOW())
SELECT MONTHNAME(NOW())

SELECT YEAR(NOW()),MONTH(NOW()),DAY(NOW()),HOUR(NOW()),MINUTE(NOW()),SECOND(NOW())



SELECT DATE_FORMAT(NOW(),'%y/%m/%d %h:%i:%s %p %w')

SELECT ADDDATE(NOW(),INTERVAL 3 DAY)
SELECT DATE_ADD(NOW(),INTERVAL 3 DAY)

SELECT DATE_SUB(NOW(),INTERVAL 3 DAY)
SELECT SUBDATE(NOW(),INTERVAL 3 MONTH)

11 字符串函数


-- 字符串函数
SELECT CONV(6,10,2) -- 把数字6从10进制转为2进制

SELECT CONCAT('hello','world')

SELECT LPAD('aaaaaaaaa',12,'k'),RPAD('bbbbbbbb',12,'k')

SELECT LEFT('aasdaddadas',6), RIGHT('dsdsdd',6)

SELECT SUBSTRING('heloworld',6,3)

SELECT LENGTH('你好'),OCTET_LENGTH('大家好') -- 输出字节长度

SELECT CHAR_LENGTH('你好'),CHARACTER_LENGTH('大家好') -- 输出字符个数

SELECT LTRIM('   hello'),RTRIM('ff  '),TRIM('  ff  ')-- 补空，左靠齐

SELECT LCASE('ABS'),LOWER('ASD'),UCASE('ass'),UPPER('sss') -- 大写，小写

SELECT REPLACE ('hello world','world','hello') -- 替换

SELECT INSERT('hello wworld',7,3,'aaa')

12 not null

create table user.zxy (
	id int(11) not null,
    name varchar(25) unique
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

七多表查询

将多个表的查询结果连接在一起

create table zxy.user （
	id int,
	name varchar(50),
	score varchar(50)
);

create table zxy.stu (
	id int,
    date timestamp
);

1 union

-- 1.union 合并去除
select id,name from zxy.user
union
select id,name from zxy.user;

2 union all

-- 1.union 合并但不去重
select id,name from zxy.user
union all
select id,name from zxy.user;

3 inner join

-- 只有等式两边成立才能将结果查询出来
select 
user.id,
user.name,
user.score,
stu.date
from user inner join stu
on user.id = stu.id;

4 left join

-- 只有等式两边成立才能将结果查询出来
select 
user.id,
user.name,
user.score,
stu.date
from user left inner join stu
on user.id = stu.id;

5 right join

-- 只有等式两边成立才能将结果查询出来
select 
user.id,
user.name,
user.score,
stu.date
from user right inner join stu
on user.id = stu.id;

6 natural join

-- 自然内连接查询
-- ON条件取笛卡尔积有点多余：
-- 1.要求外键名称和你另一张表的主键名称必须完全相同
-- 2.主外键约束
select 
user.id,
user.name,
user.score,
stu.date
from user natural inner join stu

八 JDBC

驱动：mysql-connector-java-5.1.7-bin.jar

package com.zxy.demo.Pojo;

import java.sql.*;

public class MySQL {
    public static Connection getConnection() throws ClassNotFoundException, SQLException {
        /**
         * 参数1：驱动 com.mysql.jdbc.Driver
         * 参数2: URL jdbc:mysql://localhost:3306/zxy
         * 参数3：用户名 root
         * 参数4：密码 root
         */
        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.获取连接对象
        String url = "jdbc:mysql://localhost:3306/zxy";
        String user = "root";
        String password = "root";
        Connection connection = DriverManager.getConnection(url, user, password);

        return connection;
    }
    public static void close(Statement statement, Connection connection) throws SQLException {
        if (statement != null && connection != null){
            statement.close();
            connection.close();
        }
    }

    /**
     * 测试
     * @param args
     */
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        //获得connection
        Connection connection = MySQL.getConnection();
        //SQL语句
        String sql = "select * from zxy.user";
        //执行对象
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        //遍历查询
        while (resultSet.next()){
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            System.out.println(id + "," + name);
        }
        //关闭连接
        MySQL.close(statement,connection);
    }
}

九、Win操作指令

1.登录：

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -uroot -p
Enter password: ****

2.重置密码：

C:\Users\ZXY>mysqladmin -uroot -pzxy password root

mysql> set password for root@localhost = password('root');
Query OK, 0 rows affected, 1 warning (0.00 sec)

3.密码忘记：

如果密码忘记，可以用notepad++打开my.ini文件，在[mysqld]后面加上skip-grant-tables
然后利用第三种修改密码的方式，修改密码后，再把这一行密码删除

C:\Users\ZXY>cd C:\Program Files\MySQL\MySQL Server 5.7\bin

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -uroot -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.10-log MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> creat database dbl:zzz
    -> creat database dbl:
    -> creat database dbl;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'creat database dbl:zzz
creat database dbl:
creat database dbl' at line 1
mysql> create database dbl;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| dbl                |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| video              |
| world              |
+--------------------+
8 rows in set (0.01 sec)

mysql> show create database dbl;
+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| dbl      | CREATE DATABASE `dbl` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> use dbl;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| dbl        |
+------------+
1 row in set (0.00 sec)

mysql> use video;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| video      |
+------------+
1 row in set (0.00 sec)

mysql> drap database dbl;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'drap database dbl' at line 1
mysql> show
    -> databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| dbl                |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| video              |
| world              |
+--------------------+
8 rows in set (0.00 sec)

mysql> create database zxy
    -> create database zxy;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'create database zxy' at line 2
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| dbl                |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| video              |
| world              |
+--------------------+
8 rows in set (0.00 sec)

mysql> create database zxy;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| dbl                |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| video              |
| world              |
| zxy                |
+--------------------+
9 rows in set (0.00 sec)

mysql> use zxy;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table users{
    ->
    ->
    ->
    -> exit
    -> create table users{;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '{



exit
create table users{' at line 1
mysql> create table users(
    -> name varchar(50),
    -> age int,
    -> gender varchar(10)
    -> height int);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'height int)' at line 5
mysql> create table users(
    -> name varchar(50),
    -> age int,
    -> gender varchar(10),
    -> height int);
Query OK, 0 rows affected (0.05 sec)

mysql> show tables;
+---------------+
| Tables_in_zxy |
+---------------+
| users         |
+---------------+
1 row in set (0.00 sec)

mysql> desc users;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(50) | YES  |     | NULL    |       |
| age    | int(11)     | YES  |     | NULL    |       |
| gender | varchar(10) | YES  |     | NULL    |       |
| height | int(11)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

mysql> alter table users add adress varchar(10);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc users;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(50) | YES  |     | NULL    |       |
| age    | int(11)     | YES  |     | NULL    |       |
| gender | varchar(10) | YES  |     | NULL    |       |
| height | int(11)     | YES  |     | NULL    |       |
| adress | varchar(10) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> alter table users modify adress address;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'address' at line 1
mysql> alter table users modify adress varchar(9);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table users drop adress;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc users;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(50) | YES  |     | NULL    |       |
| age    | int(11)     | YES  |     | NULL    |       |
| gender | varchar(10) | YES  |     | NULL    |       |
| height | int(11)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table users change gender sex varchar(10);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc users;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(50) | YES  |     | NULL    |       |
| age    | int(11)     | YES  |     | NULL    |       |
| sex    | varchar(10) | YES  |     | NULL    |       |
| height | int(11)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> drop table users;
Query OK, 0 rows affected (0.02 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| dbl                |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| video              |
| world              |
| zxy                |
+--------------------+
9 rows in set (0.00 sec)