MySql Structured Query Language 整理

最新推荐文章于 2023-11-16 19:53:37 发布

Wedfrend

最新推荐文章于 2023-11-16 19:53:37 发布

阅读量435

点赞数

分类专栏： mysql 文章标签： sql

本文链接：https://blog.csdn.net/Wedfrend/article/details/79300159

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

MySql Structured Query Language 整理

安装mysql

下载网址（选择适合硬件的版本安装）

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

doc 下启动mysql

对于这部分如果自己在出错的时候的时候，请自行百度

1：cmd 进入 mysql 安装路径，在window下，还有空格或者汉语路径使用 “” 
   example cd "Program Files"

2:启动mysql

  mysqld install

3:connect mysql

   C:\Program Files\xxxxxxxxxx\bin>mysql -uroot -hlocalhost -p

    Enter password:

4:在安装mysql中，可能我们都是一路next。并不会特别注意mysql安装的路径以及数据库存放的物理路径，那么可以这么查找
show global variables['vɛrɪəbl]  like "%datadir%";

了解数据库（基本组成）

数据库（Database）

数据表（Table）

字段（column），字段类型（type）

列（column  ['kɑləm]）

行（row）

Mysql 数据类型

注意：Mysql 语句：关键字 不区分 大小写

注释：#注释内容
     --+空格+注释内容
     /*注释内容*/

类型：数值型

     整数   tinyint smallint mediumint['midɪəm] int   bigint 


     小数   浮点型  double float
            十进位   decimal['dɛsɪml](m,n)  m:总长度  n:小数位长度

     日期类型：year  timestamp（时间类型，时间戳） time date datetime

     字符串类型：set enum['enəm] blob text varchar['vɑkɚ] char


    关键字：unsigned
           auto_increment
           default
           comment

           unique
           index
           key
           primary key
           auto_increment

sql语法符号

= 既是判断也是赋值
！= <>
>=  <=
AND &&  OR ||
Between ...  and ...
IN  在...之内
NOT IN 不在...之内

Sql 语句的分类

DDL（data defination language）数据定义语言。
DML（data manipulation[mə,nɪpjʊ'leʃən] language）数据操作语言
DCL（data control language）数据控制语言
DQL（data query language）数据查询语言

DDL

查询所有数据库
show databases;

使用数据库
use databaseName

创建数据库
create database Name;

删除数据库
drop database Name；


数据表Table

创建数据表：
1：use 数据库
2：创建数据表
    mysql> create table user(
    -> id int unsigned not null auto_increment comment "用户id",
    -> user_name varchar(20) not null comment "用户名",
    -> email varchar(50) not null comment "用户邮箱",
    -> age tinyint unsigned not null comment "年龄",
    -> price decimal(10,2) not null default 0.00 comment "用户余額",
    -> created_at timestamp not null comment "注冊時間",
    -> primary key(id)
    -> );
3：查看数据表

desc user;
mysql> desc user;
+------------+---------------------+------+-----+-------------------+-----------------------------+
| Field      | Type                | Null | Key | Default           | Extra                       |
+------------+---------------------+------+-----+-------------------+-----------------------------+
| id         | int(10) unsigned    | NO   | PRI | NULL              | auto_increment              |
| user_name  | varchar(20)         | NO   |     | NULL              |                             |
| email      | varchar(50)         | NO   |     | NULL              |                             |
| age        | tinyint(3) unsigned | NO   |     | NULL              |                             |
| price      | decimal(10,2)       | NO   |     | 0.00              |                             |
| created_at | timestamp           | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------+---------------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.04 sec)



查看数据表的创建语句
show create table user;

删除数据表
drop table user; 

修改数据表中的某个字段的修饰
alter['ɔltɚ] table 表名 modify 列名 varchar(50);

修改数据表中的某个字段名：
alter table 表名 change 【email原字段名】 【user_email新字段名】

添加新的字段名：
altere table 表名 add 【新增字段名】 【数据类型】 【相应描述】 【是否为null】 after 【位置定位】

删除字段
alter table 表名 drop 【字段名】

修改表名
alter table 【原表名】 rename to 【新表名】

DML (manipulation)

数据的增删改查操作CRUD

【C(Create)】

INSERT INTO 【表名】(COLUMN 1，COLUMN 2,...) VALUES(“values”,"values",... )；
相互对应


其余两种写法：insert into 【表名】values('',''....);
            insert into 【表名】(COLUMN 1,COLUMN2) values('','');


【注意】：关于插入时数据产生乱码的问题：

1：查看客户端的编码：

mysql> show variables like '%char%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | gbk                                                     |
| character_set_connection | gbk                                                     |
| character_set_database   | utf8                                                    |
| character_set_filesystem | binary                                                  |
| character_set_results    | gbk                                                     |
| character_set_server     | utf8                                                    |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.7\share\charsets\ |
+--------------------------+---------------------------------------------------------+

如果你的是GBK编码，没问题，如果是utf-8，那么使用

set names utf-8;

这种修改方法只在当前的链接中才可以，如果退出，编码格式则会被重置为配置文件的设置：

找到本地配合文件 my.ini

修改 default-character-set=utf8  （修改客户端编码，但是不会改变服务器的编码）


【U(update)】

update 【表名】 set [columnName]=[values],... where [约束条件]；

update users set user_name="wedfrend" where id=1;

update users set user_name="wang" where id in(1,3);

【D(delete)】
delete form 【表名】 where [约束条件];

完全删除数据表中的数据两种方式：
truncate[trʌŋ'ket] 【表名】 ：再一次insert数据时  被限定为自增值从头开始
delete from 【表名】：再一次insert数据时  自增的数据接着上次的数据

DCL（database control language）

忘记密码或者修改密码

1：如果忘记数据库登陆密码：

    先停止数据库

    忘记数据库密码  ：mysqld --skip grant table 跳过密码验证

    进入数据库，查询user表

2：修改用户密码。

    进入数据库使用update操作来进行密码的修改。


    修改数据库用户密码： 5.7版本的密码字段  authentication_string 之前版本为 password

    mysql> select host,user,authentication_string from user;
    +-----------+---------------+-------------------------------------------+
    | host      | user          | authentication_string                     |
    +-----------+---------------+-------------------------------------------+
    | localhost | root          | *043E8B8782FC1ED3ACBAA3873D33E7A3E2308892 |
    | localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
    | localhost | mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
    | %         | wedfrend      | *043E8B8782FC1ED3ACBAA3873D33E7A3E2308892 |
    +-----------+---------------+-------------------------------------------+

ROOT:权限应用（修改权限后需要：flush privileges [ˈprɪvəlɪdʒ]）

创建用户：

CREATE USER [用户名] @"Ip ADDRESS|%" 
IDENTIFIED BY 'PASSWORD'

添加完成之后
flush privileges


指定IP，保障安全性


添加权限：

为什么：当我们添加一个新的user的时候，此时该用户是不能执行过多的操作。如下

C:\Users\welive>mysql -uwang -p
Enter password: ******
Welcome to the MySQL monitor.

mysql> use mysql;
ERROR 1044 (42000): Access denied for user 'wang'@'localhost' to database 'mysql'



授权：
GRANT 权限1，权限2，... ON 数据库名.* TO 用户名 @ 【IP地址|%】

eg:

grant[ɡrænt] select,insert,delete,update,alter on test.* to wangxiaobo @'127.0.0.1';

如果授权所有的数据库就使用 '*.*'

grant all privileges[ˈprɪvəlɪdʒ] on *.* to 'test' @'127.0.0.1';


撤销权限：
REVOKE 权限1，权限2，...ON 数据库名.* FROM 用户名 @【IP地址|%】

eg:
revoke[rɪ'vok] update on test.* from wangxiaobo @'127.0.0.1';


授权一个不存在的用户：
grant select,insert,delete,update on test.* to [用户名]@[Ip|%] identified by "password"；


查看权限：
show grants for 'test'@'27.0.0.1';

删除用户：
drop user 'test'@'127.0.0.1';

DQL（data query language）

select [*|columnName)] from [TableName] where [查询条件] 


过滤掉重复的值：

SELECT DISTINCT

select distinct[dɪ'stɪŋkt] password from user;

链接字段

SELECT CONCAT(列名，列名) from user;

select concat(name,age) as name_age from user;

select concat_ws("==",name,age) as name_age from user;


对列名重新命名：
select [原列名] as [新列名] from [表名]

模糊查询：

select name from user where name like "%wang%";

排序

select * from tableName order by id ASC(ascending[ə'sɛndɪŋ] order)

select * from tableName order by id DESC(descending[dɪ'sɛndɪŋ]);

聚合函数（分页处理使用的时候）

COUNT（*） 查询这个表中公有多少条数据

select count(*) from tableName;

SUM()  求和

select sum(ColumnName) from tableName;
select sum(columnName) as sum_columnName from tableName;


AVG() average['ævərɪdʒ] 平均值

select avg(columnName) from tableName

MAX() 最大值

MIN() 最小值

分组查询：

select * from tableName group by columnName;

select count(*) as total,sex from tableName group by columnName;

select sex from tableName group by columnName having count(*)>2;

综合查询

连接查询

SELECT U.USER_NAME,H.HOBBIES FROM USERS AS U,HOBBY AS H WHERE U.ID=H.USER_ID;

SELECT U.USER_NAME,H.HOBBIES FROM USERS AS U INNER JOIN HOBBY AS H WHERE|ON U.ID=H.USER_ID;


左连接：

SELECT U.USER_NAME,HHOBBIES FROM USERS AS U LEFT JOIN HOBBY AS H  ON U.ID=H.USER_ID;

右连接
SELECT U.USER_NAME,HHOBBIES FROM USERS AS U LEFT JOIN HOBBY AS H  ON U.ID=H.USER_ID;

联合查询

SELECT USER_NAME FROM USERS UNION['junɪən] ALL SELECT HOBBIES FROM HOBBY;

子查询

SELECT * FROM USERS WHERE ID IN(SELECT USER_ID FROM HOBBY);

主查询依托于子查询的结果，那么执行顺序就是子查询先行

限制查询条数

SELECT * FROM USERS LIMIT 1;
限制查询次数（默认第一个数为0）

SELECT * FROM USERS LIMIT 2,3;
查询从第二条开始（不包含第二条数据向后查询3条）

实际应用中主要为程序分页

Engine‘ɛndʒɪn

MyISAM

InnoDB

CSV

BLACKHOLE

MEMORY

数据库默认的Engine设置在my.ini配置文件中

目前我的是 

# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB

引擎基于数据表，那么如何查看自己创建的表中的引擎

mysql> show create table users;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                  |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_name` varchar(20) NOT NULL,
  `email` varchar(50) NOT NULL,
  `mobile` char(11) NOT NULL,
  `fee` decimal(10,2) NOT NULL DEFAULT '0.00',
  `age` tinyint(4) NOT NULL,
  `gender` char(5) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

此时,自己创建表的引擎为数据库中默认的配置。

当然，我们可以在建表的时候配置引擎

create table test(id int unsigned not null auto_incremet primary key,name varchar(30)) engine MyISAM;

查看所有表中的引擎：
show table status\G

查看支持引擎：
show engines\G

修改已经存在的表的引擎

mysql> alter table users engine=MyISAM;

MyISAM

支持全文索引，不支持事务

事务：一个完整的执行过程

InnoDB

支持事务，不支持索引，5.6版本之后开始支持，性能好，行级锁，崩溃处理好

字符集

utf8
gbk（中文编码）


show character set;

show create database test;

创建数据库的时候可以指定字符集

create database test character set gbk;

创建字符集


关于数据库   关于数据表 独立

字符集校对：
utf8_general_ci 不区分大小写



char(3) 字符的长度

select length(columnName) from table Name;
select char_length(columnName) from table Name;

varchar()字节的长度


mysql 的三种mode
mode = ANSI;

设置mysql的模式：
set sql_mode=STRICT_TRANS_TABLES;

DTL 事务控制语言

一条sql语句就是一个事务

事务可以保证一组sql语句要么成功要么都失败。

默认自动执行

1: 关闭set atuocommit=0 关闭自动提交
    最后 插入或者修改时，只有commit才回最终存入否则不存


    mysql> show variables like "%commit%";
            +-----------------------------------------+-------+
            | Variable_name                           | Value |
            +-----------------------------------------+-------+
            | autocommit                              | ON    |
            | binlog_group_commit_sync_delay          | 0     |
            | binlog_group_commit_sync_no_delay_count | 0     |
            | binlog_order_commits                    | ON    |
            | innodb_api_bk_commit_interval           | 5     |
            | innodb_commit_concurrency               | 0     |
            | innodb_flush_log_at_trx_commit          | 1     |
            | slave_preserve_commit_order             | OFF   |
            +-----------------------------------------+-------+
            8 rows in set, 5 warnings (0.00 sec)

            mysql> set autocommit=OFF;

2：start transaction

    commit/rollback;

原子性：一个事务中的所有语句，应该做到要么全做，要么都不做

一致性：逻辑合理性

隔离性：如果多个事务同时并发执行，但是每个事务就像独立执行一样

持久性：    一经提交改变，则永久改变

典型的实际例子：银行转账

start transaction;//开启事务
mysql->  /*一系列需要执行的sql语句*/

commit;  //成功
或者：
rollback;//失败 

 /*如果你的数据表中不能执行事务的操作那么必须请查看自己的ENGINE*/


实例：

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update user set fee 2000 where id=1;
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 '2000 where id=1' at line 1
mysql> update user set fee= 2000 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update user set fee=5000 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from userl
    -> ;
ERROR 1146 (42S02): Table 'bank.userl' doesn't exist
mysql> select * from user;
+----+----------+---------+
| id | name     | fee     |
+----+----------+---------+
|  1 | 王晓波   | 2000.00 |
|  2 | wedfrend | 5000.00 |
+----+----------+---------+
2 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.06 sec)

mysql> select * from user;
+----+----------+----------+
| id | name     | fee      |
+----+----------+----------+
|  1 | 王晓波   | 46000.00 |
|  2 | wedfrend |  3000.00 |
+----+----------+----------+
2 rows in set (0.00 sec)

视图

create view ViewName(ColumnName,) as select columnName from TableName where xxx;


 create view userView(name)as select user_name from users;
 Query OK, 0 rows affected (0.08 sec)

 mysql> select * from userView;

触发器

create trigger 触发器名 after|before insert|update|delete on 
TableName for each row

begin

sql 语句

end;

show create tigger tiggerName\G;

drop trigger tiggerName;


mysql> create trigger nuum after insert on users for each row
    -> begin
    -> update hobby set hobbies='wang IAO波' where user_id=10;
    -> end//
Query OK, 0 rows affected (0.13 sec)

mysql> delimiter ;

数据库导出

导出

mysqldump -uroot -p****** DatabaseName TableName > NewFileName.sql;

使用navicat 工具执行转存储sql文件

导入 ：

mysql -uroot -p****** DatabaseName < NewFileName.sql;

使用Navicat 工具执行运行sql文件

xtrabackup工具可以执行大数据的备份恢复

索引

添加索引：
alter table TableName add index|unique|fulltext [indexName]

alter table TableName drop index [IndexName]





索引

primary key() 唯一主键
index 普通索引
key 普通索引
unique 唯一索引


 create table user(
    ->      id int unsigned not null auto_increment,
    ->      userName varchar(50) not null,
    ->      passWord varchar(50) not null,
    ->      email varchar(32) not null,
    ->      age tinyint unsigned not null,
    ->      primary key(id),
    ->      unique(userName),
    ->      key(age),
    ->      index(email),
    ->      fulltext(password)
    ->      );



mysql> show index from user\G


在一些数据表中无法创建全文索引，原因是你的Engine为InnoDB,需要转换为 MyISAM

对于已经存在的表进行添加索引  
alter table TableName add fulltext(columnName);


全文索引：
select * from account where match(password) against('xxxx');

解决模糊查询

外键约束(InnoDB)

foreign key(columnName) references TableName(ColumnName);

如果说两张表含有因果关系的情况，那么在数据表中可以使用外键约束来进行相应的关联处理

注意问题：使用外键约束的字段，类型设置必须一致

create table mark(
    id int unsigned not null auto increment,
    mark int unsigned not null,
    stu_id int not null,
    primary key(id),
    foreign key(stu_id) references user(id)
);

Mysql 优化

当查询一条sql语句的耗时长达1s的时候，那么证明我们的数据库查询一定存在问题，如果sql语句慢了,解决办法：

1：mysql> show variables like '%slow%';
    +---------------------------+-----------------------+
    | Variable_name             | Value                 |
    +---------------------------+-----------------------+
    | log_slow_admin_statements | OFF                   |
    | log_slow_slave_statements | OFF                   |
    | slow_launch_time          | 2                     |
    | slow_query_log            | ON                    |
    | slow_query_log_file       | -slow.log     |
    +---------------------------+-----------------------+
    5 rows in set, 5 warnings (0.04 sec)

2：set global log_slow_admin_statements=ON;


方法二：看性能

 mysql>show variables like '%profiling%';
    +------------------------+-------+
    | Variable_name          | Value |
    +------------------------+-------+
    | have_profiling         | YES   |
    | profiling              | OFF   |
    | profiling_history_size | 15    |
    +------------------------+-------+

mysql>set profiling=on;

之后执行的sql语句会被记录。然后窒息性相应的方法查看

mysql> show profiles;
+----------+------------+-----------------------------------+
| Query_ID | Duration   | Query                             |
+----------+------------+-----------------------------------+
|        1 | 0.00222050 | show variables like '%profiling%' |
|        2 | 0.00097125 | select * from users               |
|        3 | 0.00009375 | show prifiles                     |
+----------+------------+-----------------------------------+

将时间比较大的语句内容展示详情

mysql> show profile for query 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000109 |
| checking permissions | 0.000013 |
| Opening tables       | 0.000033 |
| init                 | 0.000110 |
| System lock          | 0.000021 |
| optimizing           | 0.000007 |
| statistics           | 0.000025 |
| preparing            | 0.000015 |
| executing            | 0.000004 |
| Sending data         | 0.000270 |
| end                  | 0.000025 |
| query end            | 0.000019 |
| closing tables       | 0.000021 |
| freeing items        | 0.000190 |
| cleaning up          | 0.000112 |
+----------------------+----------+


找到耗时长问题进行处理

简单的查询问题下：加上索引字段，那么sql执行的时候就会查询固定的行

缓存

在sql中，含有缓存。一般情况下，在测试下需要把缓存关闭，默认情况下是开着的

mysql>show variables like '%cache%';

mysql> show variables like  '%cache%';
+--------------------------------+----------------------+
| Variable_name                  | Value                |
+--------------------------------+----------------------+
| binlog_cache_size              | 32768                |
| binlog_stmt_cache_size         | 32768                |
| have_query_cache               | YES                  |
| host_cache_size                | 279                  |
| innodb_disable_sort_file_cache | OFF                  |
| innodb_ft_cache_size           | 8000000              |
| innodb_ft_result_cache_limit   | 2000000000           |
| innodb_ft_total_cache_size     | 640000000            |
| key_cache_age_threshold        | 300                  |
| key_cache_block_size           | 1024                 |
| key_cache_division_limit       | 100                  |
| max_binlog_cache_size          | 18446744073709547520 |
| max_binlog_stmt_cache_size     | 18446744073709547520 |
| metadata_locks_cache_size      | 1024                 |
| query_cache_limit              | 1048576              |
| query_cache_min_res_unit       | 4096                 |
| query_cache_size               | 1048576              |
| query_cache_type               | OFF                  |
| query_cache_wlock_invalidate   | OFF                  |
| stored_program_cache           | 256                  |
| table_definition_cache         | 1400                 |
| table_open_cache               | 2000                 |
| table_open_cache_instances     | 16                   |
| thread_cache_size              | 10                   |
+--------------------------------+----------------------+

query_cache_type 为 OFF；

mysql>set query_cache_type=ON;

使用sql语句注意的问题

1：尽量避免在字段中进行运算
2：在查询中尽量不要使用select *
3：like 模糊查询做模糊或者全模糊的时候不执行索引。

数据库配置全局变量

临时生效，当mysql退出会后，会被重置
show variables like '%xxx%';

set global xxxx=xxxx;

set xxxx=xxxx;

永久修改：

直接修改my.ini配置文件

设计表规则

1NF ：表不可再分

2NF： 在1NF的基础上设计，满足row中含有唯一可被区分的属性

3NF:先满足2NF，在表的设计中尽量满足数据不要出现冗余情况

Wedfrend

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySql Structured Query Language 整理

MySql Structured Query Language 整理安装mysql下载网址（选择适合硬件的版本安装）https://dev.mysql.com/downloads/mysql/doc 下启动mysql对于这部分如果自己在出错的时候的时候，请自行百度1：cmd 进入 mysql 安装路径，在window下，还有空格或者汉语路径使用 “” ex...
复制链接

扫一扫