Mysql基本语法

1. Ubuntu18.04安装Mysql 8

---

使用apt安装

Step1. 添加MySQL APT Repository

1. 前往下载页面下载MySQL APT repository

https://dev.mysql.com/downloads/repo/apt/

2. 使用如下命令安装配置

包名换成刚才下载的.deb名称

shell> sudo dpkg -i /PATH/version-specific-package-name.deb

安装期间会被提示选择MySQL server的版本和其他组件，如果不确定的话就选择默认即可

3. 更新

shell> sudo apt-get update

Step2. 使用apt-get 安装mysql

shell> sudo apt-get install mysql-server

安装过程中会提示设置root用户密码，记住这个密码

Step3. 开启/关闭mysql服务

shell> sudo service mysql status
shell> sudo service mysql start
shell> sudo service mysql stop

移除mysql

# 先移除mysql server
shell> sudo apt-get remove mysql-server

# 再移除其他安装的包
shell> sudo apt-get autoremove

# 移除mysql apt repository
shell> sudo apt-get remove mysql-apt-config

# 检查是否完全删除
shell> dpkg -l | grep mysql | grep ii

按照上面的删除方法，并没有删除全部的依赖和配置文件，解决办法：

tar -zcvf ~/msql_backup.tar.gz /etc/mysql /var/lib/mysql 
sudo apt purge mysql-server mysql-client mysql-common mysql-server-core mysql-client-core
sudo rm -rfv /etc/mysql /var/lib/mysql
sudo apt autoremove
sudo apt autoclean

参考链接： https://askubuntu.com/questions/763534/cannot-reinstall-mysql-server-after-its-purge

2. 基本使用

使用mysql客户端程序.，其中的关键字是大小写不敏感的。

查看所有命令：

shell> mysql --help

连接数据库

shell> mysql -h host -u user -p
Enter password: ********

# 连接本地时
mysql -uroot -p

# 连接时选择数据库
shell> mysql -h host -u user -p 数据库名

# 连接时输入密码
shell> mysql -h host -u user -p密码

查看时间

mysql> SELECT CURRENT_DATE;
mysql> SELECT NOW();

查看版本呢

SELECT VERSION();

不执行当前已输入内容

mysql> SELECT
    -> USER()
    -> \c

数据库操作

显示数据库

只显示自己有权限的数据库

SHOW DATABASES;

切换数据库

 USE test

查看当前选择的数据库

SELECT DATABASE();

赋予用户权限

mysql> GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host';

创建数据库

mysql> CREATE DATABASE 数据库名 CHARSET=utf8;

删除数据库

drop database 数据库名;

表操作

查看当前数据库中的表

mysql> SHOW TABLES;

查看表结构

mysql> DESCRIBE 表名;

# 简写
desc 表名;

创建表

CREATE TABLE 表名(列和类型);

mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
    -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

CREATE TABLE STUDENTS(
id int auto_increment primary key,
sname varchar(10) not null
);

修改表结构

ALTER TABLE 表名 ADD|CHANGE|DROP 列名 类型;
# 没法修改列的名字，只能修改列的结构

更改表名

rename table 原表名 to 新表名;

查看表的创建语句

SHOW CREATE TABLE 表名;

数据操作

备份数据库

切换至超级用户

cd /var/lib/mysql

mysqldump -uroot -p 数据库名 > 文件名.sql;
# 其中没有创建数据库的脚本，恢复的时候需要自己创建数据库

数据恢复

mysql -uroot -p 数据库名 < 文件名.sql

查询

SELECT * FROM 表名;

-- 字符串比较默认是不区分大小写的
SELECT * FROM 表名 WHERE 条件;

-- 去重
SELECT DISTINCT * FROM 表名;
-- 逻辑符号  and or not
SELECT * FROM 表 WHERE 条件1 AND 条件2

-- 范围
SELECT * FROM 表 WHERE 列 IN (值1，值2，值3..);
SELECT * FROM 表 WHERE 列 BETWEEN 下限 AND 上限;

模糊查询

SELECT * FROM 表名 WHERE 列 LIKE '%_xas';   -- % 代表任意多个  _ 代表任意一个

-- 正则扩展  ：  REGEXP_LIKE()函数
mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b');
SELECT * FROM pet WHERE REGEXP_LIKE(name, BINARY '^b');    #BINARY关键字代表区分大小写

聚合

max count min sum avg```

判断NULL

mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
# MySQL中, 0 or NULL 代表false,其他都代表true

插入数据

# 使用LOAD
# 创建一个文本文件，将每一行数据按照表结构顺序书写，使用tab分割字段，Null值使用 \N ，比如：
Whistler        Gwen    bird    \N      1997-12-09      \N

# 运行命令：
mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;
# 字段分隔符，换行符都是可以指定的，默认为 tab 和 \n

# 使用Insert
INSERT INTO 表 VALUES(值们);
INSERT INTO 表(字段名） VALUES(值们);
INSERT INTO 表(字段名） VALUES(值们),(值们),(值们)..;

删除

DELETE FROM 表 WHERE 条件;

修改

UPDATE 表 列=值, 列=值.. WHERE 条件;

排序

mysql> SELECT name, birth FROM pet ORDER BY birth;
mysql> SELECT name, birth FROM pet ORDER BY birth DESC;
mysql> SELECT name, species, birth FROM pet ORDER BY species, birth DESC;

分组

SELECT 列 FROM 表 GROUP BY 列 HAVING 条件;

分页

SELECT * FROM 表 LIMIT 开始,数量;

语句执行顺序

1. from
2. where
3. group by
4. select distinct 列
5. having
6. order by
7. limit

事务

需要表的类型是innodb或者bdb

ALTER TABLE 表 ENGINE=innodb;

事务语句

BEGIN;
COMMIT;
ROLLBACK;

索引

查看索引

SHOW INDEX FROM 表名;

CREATE INDEX 索引名 ON 表(字段名[长度],字段名[长度]..);

DROP INDEX [索引名] ON 表;

批处理

shell> mysql < batch-file
shell> mysql < batch-file > mysql.out

# windows
C:\> mysql -e "source batch-file"

# 交互命令中批处理
mysql> source filename;
mysql> \. filename

3. 其他

1. SQL的语言属于类B系的，一般都是不区分大小写
2. 三范式：
   1NF. 列不可拆分
   2NF. 唯一标识
   3NF. 引用主键

4. Python使用pymsql连接

安装

$ pip3 install PyMySQL

示例

'''插入'''
conn = pymysql.connect(user='root', password='123456', host='192.168.181.129', database='test')
    with conn.cursor() as cur:
        sql = f"INSERT INTO STUDENT VALUES(%s,%s,%s);"
        cur.execute(sql, (0,'husongyao123',26))
        conn.commit()


''' 查询 '''
import pymysql.cursors

cnx = pymysql.connect(user='yyfyifan', password='1992590', host='192.168.181.129', database='test')
cursor = cnx.cursor()

query = ("SELECT * FROM STUDENT");
cursor.execute(query)


for (id,name,age) in cursor:
    print(f"{id}, {name} was hired on {age}")

import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='user',
                             password='passwd',
                             db='db',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

try:
    with connection.cursor() as cursor:
        # Create a new record
        sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
        cursor.execute(sql, ('webmaster@python.org', 'very-secret'))

    # connection is not autocommit by default. So you must commit to save
    # your changes.
    connection.commit()

    with connection.cursor() as cursor:
        # Read a single record
        sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
        cursor.execute(sql, ('webmaster@python.org',))
        result = cursor.fetchone()
        print(result)
finally:
    connection.close()

简单封装

class MySQLHelper(object):
    """ pymysql helper wrapper"""
    from pymysql import connect, cursors

    def __init__(self, host, user,passwd, db,port=3306):
        self.host = host
        self.user = user
        self.passwd = passwd
        self.db = db
        self.port = port
        self.conn= None
        self.cursor = None


    def open(self):
        self.conn = pymysql.connect(user=self.user, password=self.passwd, host=self.host, database=self.db,
                         port =self.port)
        self.cursor = self.conn.cursor()

    def cud(self,sqlstatement, params):
        try:
            self.open()
            self.cursor.execute(sqlstatement,params)
            self.conn.commit()
        except Exception as e:
            print(e.message)
        finally:
            self.close()

    def get_all(self,sqlstatement,params=None):
        try:
            self.open()
            self.cursor.execute(sqlstatement,params)
            return self.cursor.fetchall();
        except Exception as e:
            print(e.message)
        finally:
            self.close()

    def get_resultset(self, sqlstatement, params= None):
        try:
            self.open()
            self.cursor.execute(sqlstatement, params)
            return self.cursor
        except Exception as e:
            print(e.message)
        finally:
            self.close()

    def close(self):
        self.cursor.close()
        self.conn.close()