设为首页 收藏本站
查看: 600|回复: 0

[经验分享] MySQL学习笔记之三:SQL的用法

[复制链接]

尚未签到

发表于 2018-10-22 06:42:47 | 显示全部楼层 |阅读模式
  一、mysql中的字符大小写问题:
  ①SQL关键字及函数名不区字符大小写,但是,为了保证缓存命中率,宜遵循同一种风格
  ②数据库、表、索引及视图的名称是否区分大小写取决于底层的OS及FS;
  ③存储过程、存储函数及事件调度器不区分字符大小写;但触发器区分;
  ④表别名不区分大小写;
  ⑤字段中字符数据,类型为binary、blog、varbinary时区分大小写;其它的不区分;
  二、SQL
  SQL(Structured Query Language,结构化查询语言)是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统
  1、SQL语言有如下几个部分组成:
  ①数据定义语言DDL;定义关系,修改关系,删除关系
  完整性(intigrity):定义完整性约束的命令,隶属于DDL
  视图定义:管理视图view(虚表);
  授权:定义对表或视图的访问权限;
  DDL:CREATE, DROP,>
  ②数据操纵语言DML;插入行、修改行、删除行
  DML:INSERT, DELETE, UPDATE, SELECT
  ③事务控制:创建事务和结束事务
  2、约束:
  主键(primary key):属于惟一键,但其值不能为空,一张表只能有一个主键
  惟一键(unique key)
  外键(foreign key):引用性约束
  检查性约束
  3、SQL的数据定义:
  创建关系,即创建表:
  每个关系模式;
  每个属性的取值类型;
  完整性约束;
  索引的集合;
  表的安全性和权限信息;
  表的物理存储结构:因存储引擎不同而不同
  MyISAM:.frm, .MYD(数据), .MYI(索引)
  InnoDB:.frm, .ibd(数据+索引)
  4、SQL数据的类型
  ①字符型:(以下数字为可表示的最大字符数)
  CHAR(255,固定长度,不区分大小写)、VARCARH(65535,可变长度)
  TINYTEXT(255)、TEXT(65535,以文本格式存储)、MEDIUMTEXT(2^24)、LONGTEXT(2^32)
  BINARY(255,区分大小写)、VARBINARY(65535)
  TINYBLOB、BLOB(以二进制格式存储)、MEDIUMBLOG、LONGBLOB
  修饰符:
  NULL(可为空)、NOT NULL(不能为空)
  DEFAULT 'string'   #不适用于BLOB类型
  CHARACTER SET 'set'   #指定字符集,SHOW CHARACTER SET;
  COLLATION 'collation'  #指定排序规则,SHOW COLLATION;
  查找字符时可使用通配符
  %:匹配任意长度的任意字符
  _:匹配任意单个字符;
  ②数值型
  整型:
  TINYINT(1Byte)、SMALLINT(2Bytes)、MEDIUMINT(3Bytes)、INT(4Bytes)、BININT
  修饰符:
  NULL、NOT NULL
  DEFAULT #
  UNSIGNED   #无符号
  AUTO_INCREMENT   #自动增长
  特殊要求:非空,且必须是主键或唯一键
  符点型:
  FLOAT、DOUBLE、REAL、BIT
  修饰符:NULL、NOT NULL、DEFAULT #、UNSIGNED
  ③布尔型:没有专用布尔型,其是TINYINT(1)的别名;
  ④日期时间型
  DATE(3Bytes)、TIME(3Bytes)、DATETIME(8Bytes)、TIMESTAMP(4Bytes)
  YEAR(2):1Byte、YEAR(4):1Byte
  修饰符:NULL、NOT NULL、DEFAULT value
  ⑤内置类型
  ENUM:枚举,表示仅能从给出的选项选择其中一个
  ENUM('string1','string2')
  SET:集合, 表示能使用给出的元素组合成的字符串
  SET('a','b','c')
  修饰符:NULL、NOT NULL、DEFAULT 'string'
  △另外,还有定义主键或唯一键的修饰符:PRIMARY KEY、UNIQUE KEY
  
  选择数据类型应遵循以下几种法则
  越小越好,节约空间
  越简单越好,能存储为数值就不要存储为字符
  能用定长就不要使用变长,char在数据插入、比较、检索上的效率要高于varchar,varchar适用于最长串与最短串相差较大的场景
  尽量避免NULL值;
  
    5、MySQL sql_mode:sql模式,用来限定mysqld的工作特性,默认为空模式。
  在服务器变量sql_mode中定义,可取值主要有:
  TRADITIONAL
  STRICT_TRANS_TABLES:对支持事务的表使用严格模式
  STRICT_ALL_TABLES:对所有表使用严格模式
  三、DDL
  1、库管理
  ⑴创建库:
  CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] ...;
  create_specification:
  [DEFAULT] CHARACTER SET [=] charset_name
  [DEFAULT] COLLATE [=] collation_name
  说明:IF NOT EXISTS 的作用在于当欲创建的库已存在时,不阻止语句的执行,常用于脚本中
  ⑵删除库:DROP {DATABASE | SCHEMA} [IF EXISTS] db_name;
  ⑶修改库:

  ALTER {DATABASE | SCHEMA} [db_name]>  alter_specification:
  [DEFAULT] CHARACTER SET [=] charset_name
  [DEFAULT] COLLATE [=] collation_name
  ALTER {DATABASE | SCHEMA} db_name UPGRADE DATA DIRECTORY NAME;   #升级数据字典
  ③查看有哪些库:SHOW DATABASES;
  ④设置默认库:USE DB_NAME
[root@node1 ~]# mysql  
...
  
MariaDB [(none)]> show databases;
  
+--------------------+
  
| Database           |
  
+--------------------+
  
| information_schema |
  
| mysql              |
  
| performance_schema |
  
| test               |
  
+--------------------+
  
4 rows in set (0.24 sec)
  

  
MariaDB [(none)]> create database testdb;   #创建数据库
  
Query OK, 1 row affected (0.17 sec)
  

  
MariaDB [(none)]> create database testdb;   #欲创建的库已存在,报错
  
ERROR 1007 (HY000): Can't create database 'testdb'; database exists
  

  
MariaDB [(none)]> create database if not exists testdb;   #添加if not exists后不报错
  
Query OK, 1 row affected, 1 warning (0.00 sec)
  

  
MariaDB [(none)]> show warnings\G   #可显示上一条语句给出的警告信息
  
*************************** 1. row ***************************
  
  Level: Note
  
   Code: 1007
  
Message: Can't create database 'testdb'; database exists
  
1 row in set (0.00 sec)
  2、表管理
  ⑴创建表:
  CREATE TABLE [IF NOT EXISTS] [db_name.]tb_name (col1_def, col2_def, PRIMARY KEY(col_name,...), UNIQUE (col1,...), INDEX (col1,...)) [table_options];
  table_option:
  ENGINE [=] engine_name
  COMMENT [=] 'string'
  ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
  TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]
  CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] select_statement;   #复制表数据
  CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name LIKE old_tb_name;  #复制表结构
  定义主键或唯一键的两种方式
  当作字段修饰符;
  如 create table tb2 (Name char(30) primary key,Gender enum('m','f'));
  当作特殊的字段;
  如 create table tb2 (Name char(30),Gender enum('m','f'),primary key(Name,Gender));
  ⑵删除表:
  DROP [TEMPORARY] TABLE [IF EXISTS] tb_name [, tb_name]... [RESTRICT | CASCADE];
  CASCADE实现级连效果,即将依赖此表的其它表也一并删除
  ⑶修改表:
  ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tb_name
  ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ];  #添加一个字段,FIRST表示添加为第一个字段

  例:alter table tb5 add>  ADD [COLUMN] (col_name column_definition,...);   #添加多个字段
  ADD PRIMARY KEY [index_type] (index_col_name,...);   #添加主键
  DROP PRIMARY KEY;
  DROP [COLUMN] col_name;  #删除某个字段
  CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name];   #修改字段名和字段定义
  MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name];   #修改字段定义
  例:alter table wuxia modify Age tinyint after Gender;
  CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];   #修改字符集和排序规则
  ENGINE = 'engine_name';   #不宜随便修改,特别是对于很大的表
  AUTO_INCREMENT = num   修改auto_increment的起始值
  COMMENT = 'string';   #修改注释信息
  InnoDB表的表空间管理(前提:每表使用独立表空间):
  DISCARD TABLESPACE;   #删除表空间,即删除表的数据和索引,只留下表结构定义
  IMPORT TABLESPACE;
  以上两项常用于mysql数据备份或迁移的场景中
  改表名

  >  RENAME TABLE old_name TO new_name;
  ⑷查看表结构:DESC tb_name;
  ⑸查看表的状态信息:SHOW TABLE STATUS [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr];
  例:show table status like 'students';
  ▲示例:
  新建如下表(包括结构和内容):

  >  1    Ling Huchong   27     Male     Pixie Jianfa
  2    Zhou Zhiruo   25     Female   Jiuyin Zhenjin
  3    Qiao Feng     30     Male     Xianglong Shiba Zhang
  ①新增Class放置于Name字段后;
  ②将ID字段名称修改为TID;
  ③将Age字段放置最后;
MariaDB [(none)]> use testdb   #设置其为默认库  
Database changed
  
MariaDB [testdb]> create table wuxia (    #创建表
  
    -> ID tinyint unsigned not null auto_increment primary key,
  
    -> Name char(20) not null,
  
    -> Age tinyint,
  
    -> Gender enum('f','m') not null,
  
    -> Course char(30)
  
    -> );
  
Query OK, 0 rows affected (1.10 sec)
  

  
MariaDB [(none)]> desc testdb.wuxia;   #查看表定义
  
+--------+---------------------+------+-----+---------+----------------+
  
| Field  | Type                | Null | Key | Default | Extra          |
  
+--------+---------------------+------+-----+---------+----------------+
  
| ID     | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
  
| Name   | char(20)            | NO   |     | NULL    |                |
  
| Age    | tinyint(4)          | YES  |     | NULL    |                |
  
| Gender | enum('f','m')       | NO   |     | NULL    |                |
  
| Course | char(30)            | YES  |     | NULL    |                |
  
+--------+---------------------+------+-----+---------+----------------+
  
5 rows in set (0.28 sec)
  

  
MariaDB [testdb]> alter table wuxia add Class char(20) after Name;   #给表添加一个字段
  
Query OK, 0 rows affected (1.30 sec)
  
Records: 0  Duplicates: 0  Warnings: 0
  

  
MariaDB [testdb]> alter table wuxia change ID TID tinyint unsigned not null auto_increment primary key;
  
ERROR 1068 (42000): Multiple primary key defined
  

  
MariaDB [testdb]> alter table wuxia change ID TID tinyint unsigned not null auto_increment;
  
Query OK, 0 rows affected (0.07 sec)
  
Records: 0  Duplicates: 0  Warnings: 0
  

  
MariaDB [testdb]> alter table wuxia drop Course;
  
Query OK, 0 rows affected (0.13 sec)
  
Records: 0  Duplicates: 0  Warnings: 0
  

  
MariaDB [testdb]> alter table wuxia modify Age tinyint after Gender;
  
Query OK, 0 rows affected (0.28 sec)
  
Records: 0  Duplicates: 0  Warnings: 0
  

  
MariaDB [testdb]> desc wuxia;   #查看表定义
  
+--------+---------------------+------+-----+---------+----------------+
  
| Field  | Type                | Null | Key | Default | Extra          |
  
+--------+---------------------+------+-----+---------+----------------+
  
| TID    | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
  
| Name   | char(20)            | NO   |     | NULL    |                |
  
| Class  | char(20)            | YES  |     | NULL    |                |
  
| Gender | enum('f','m')       | NO   |     | NULL    |                |
  
| Age    | tinyint(4)          | YES  |     | NULL    |                |
  
+--------+---------------------+------+-----+---------+----------------+
  
5 rows in set (0.00 sec)
  

  
MariaDB [testdb]> insert wuxia (Name,Class,Gender,Age) values   #批量插入数据
  
    -> ('Ling Huchong','Huashanpai','m',27),
  
    -> ('Zhou Zhiruo','Emeipai','f',25),
  
    -> ('Qiao Feng','Gaibang','m',30);
  
Query OK, 3 rows affected (0.18 sec)
  
Records: 3  Duplicates: 0  Warnings: 0
  

  
MariaDB [testdb]> select * from wuxia;
  
+-----+--------------+------------+--------+------+
  
| TID | Name         | Class      | Gender | Age  |
  
+-----+--------------+------------+--------+------+
  
|   1 | Ling Huchong | Huashanpai | m      |   27 |
  
|   2 | Zhou Zhiruo  | Emeipai    | f      |   25 |
  
|   3 | Qiao Feng    | Gaibang    | m      |   30 |
  
+-----+--------------+------------+--------+------+
  
3 rows in set (0.01 sec)
  

  
MariaDB [testdb]> show table status like 'wuxia'\G    #查看表状态
  
*************************** 1. row ***************************
  
           Name: wuxia
  
         Engine: InnoDB
  
        Version: 10
  
     Row_format: Compact
  
           Rows: 3
  
Avg_row_length: 5461
  
    Data_length: 16384
  
Max_data_length: 0
  
   Index_length: 0
  
      Data_free: 0
  
Auto_increment: 4
  
    Create_time: 2016-03-01 12:09:11
  
    Update_time: NULL
  
     Check_time: NULL
  
      Collation: utf8_general_ci
  
       Checksum: NULL
  
Create_options:
  
        Comment:
  
1 row in set (0.00 sec)
  3、用户和权限管理
  ⑴mysql账号:user@host,表示允许此user从host范围内的某主机登录
  host中可使用通配符%和_,例如 172.16.0.0/16 可表示为 172.16.%.%
  ⑵创建用户账号:CREATE USER 'username'@'host'>

  例:create user 'testuser'@'192.168.30.%'>  ⑶删除用户账号:DROP USER 'username'@'host';
  ⑷用户重命名:RENAME USER old_user TO new_user;
  例:RENAME USER 'jeffrey'@'localhost' TO 'jeff'@'127.0.0.1';
  ⑸查看用户获得的权限SHOW GRANTS FOR 'username'@'host';
  ⑹MySQL的权限类别:库级别,表级别,字段级别,管理类,程序类
  ①管理类:
  CREATE USER, FILE, SHOW DATABASES, SHUTDOWN, LOCK TABLES, PROCESS
  CREATE TEMPORARY TABLES  创建临时表会使用memory存储引擎,从而占据更多的内存空间
  SUPER   #不便归类的管理类权限

  RELOAD  #执行flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh,>  REPLICATION SLAVE   主从架构中实现从服务器向主服务器获取日志数据的权限
  REPLICATION CLIENT  向主服务器发起连接请求获取其状态信息的权限
  ②库和表级别:

  >
  >  storage routine(存储例程)包括:
  storage procedure
  storage function
  INDEX   #创建和删除索引的权限
  GRANT OPTION  #是否可以转授权限的权限;
  SHOW VIEW     show create view stus;
  ③数据操作(表级别):
  SELECT, INSERT, UPDATE, DELETE
  ④字段级别:
  SELECT(col1,...), UPDATE(col1,...), INSERT(col1,...)
  ⑤所有权限:
  ALL [PRIVILEGES]
  ⑺mysql中的授权相关的表:mysql库中
  db、host、user
  columns_priv、tables_priv, procs_priv
  ⑻授权
  GRANT priv1, priv2, ... ON [TABLE|FUNCTION|PROCEDURE] db_name.tb_name|routine TO 'username'@'host' [IDENTIFIED BY 'password'] [REQUIRE ssl_option] [WITH with_option]
  with_option:
  GRANT OPTION   #将获得的权限转赠给他人的权限
  MAX_QUERIES_PER_HOUR count
  MAX_UPDATES_PER_HOUR count
  MAX_CONNECTIONS_PER_HOUR count
  MAX_USER_CONNECTIONS count
  说明:授权的时候可直接创建用户,不需事先用create命令创建
  可指定ssl相关选项要求客户端使用ssl建立连接
  刷新授权表,以使得权限立即生效:FLUSH PRIVILEGES;
  若多次授权,后面的权限并不会覆盖之前的权限,而会合并

  例:grant create,drop,index on testdb.* to 'testuser'@'192.168.30.%'>  ⑼回收权限:
  REVOKE priv1,priv2,... ON TABLE|FUNCTION|PROCEDURE] db_name.tb_name|routine FROM 'username'@'host',...;   #可一次收回多个账号的权限
  ▲示例:
  ①授予testuser能够通过192.168.30.0/24网络内的任意主机访问当前mysql服务器的权限;
  ②让此用户能够创建及删除testdb数据库,及库中的表;
  ③让此用户能够在testdb库中的wuxia表中执行查询、删除、更新和插入操作;
  ④让此用户能够在testdb库上执行创建和删除索引;
  ⑤让此用户能够在testdb.students表上查询id和name字段,并允许其将此权限转授予其他用户;
MariaDB [testdb]> grant create,drop,index on testdb.* to 'testuser'@'192.168.30.%' identified by 'magedu';  
Query OK, 0 rows affected (0.22 sec)
  

  
MariaDB [testdb]> grant select,delete,update,insert on testdb.wuxia to 'testuser'@'192.168.30.%';
  
Query OK, 0 rows affected (0.08 sec)
  

  
MariaDB [testdb]> grant select(ID,Name) on testdb.students to 'testuser'@'192.168.30.%' with grant option;
  
Query OK, 0 rows affected (0.08 sec)
  

  
MariaDB [testdb]> flush privileges;
  
Query OK, 0 rows affected (0.06 sec)
  

  
MariaDB [testdb]> show grants for 'testuser'@'192.168.30.%';
  
+--------------------------------------------------------------------------------------------------------------------+
  
| Grants for testuser@192.168.30.%                                                                                   |
  
+--------------------------------------------------------------------------------------------------------------------+
  
| GRANT USAGE ON *.* TO 'testuser'@'192.168.30.%' IDENTIFIED BY PASSWORD '*6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664' |
  
| GRANT CREATE, DROP, INDEX ON `testdb`.* TO 'testuser'@'192.168.30.%'                                               |
  
| GRANT SELECT, INSERT, UPDATE, DELETE ON `testdb`.`wuxia` TO 'testuser'@'192.168.30.%'                              |
  
| GRANT SELECT (Name, ID) ON `testdb`.`students` TO 'testuser'@'192.168.30.%' WITH GRANT OPTION                      |
  
+--------------------------------------------------------------------------------------------------------------------+
  
4 rows in set (0.01 sec)
  四、DML
  1、插入数据
  INSERT [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} (...),...;
  #如果省略字段名,则表示向每个字段都插入数据;建议批量插入,避免索引频繁更新
  例:insert into students (Name,Age,Gender,Class) values ('jerry',43,'m','class 2'),('Ou Yangfeng',77,'m','Hamopai');
  INSERT [INTO] tbl_name SET col_name={expr | DEFAULT}, ...;
  例:insert into tb5 set Name='yue buqun',Gender='m';
  INSERT [INTO] tbl_name [(col_name,...)] SELECT ...;
  例:insert into temp2 (fld_id) select fld_order_id from temp1 where fld_order_id > 100;
  2、更新数据
  UPDATE table_reference SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ... [WHERE where_condition] [ORDER BY ...] [LIMIT row_count];

  示例:update students set Age=70 where>
  update students set>  3、删除数据
  DELETE FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count];
  例:delete from students where Gender = 'f';
  4、查询数据
  ⑴SELECT values_to_display FROM table_name
  WHERE expression
  GROUP BY how_to_group   #分组
  HAVING expression   #对聚合运算的结果进行过滤
  ORDER BY how_to_sort
  LIMIT row_count;
  说明:
  WHERE expression:比较表达式,内置函数;
  特殊的比较操作符:
  BETWEEN... AND ...
  IN(list):指定的字段取的取值在此list中,则表示符合条件;
  IS NULL:判断某字段值为空
  IS NOT NULL
  LIKE:可使用通配符,%, _
  RLIKE或REGEXP:可使用正则表达式的模式
  逻辑操作符:AND, OR, NOT
  GROUP BY:表示根据指定的字段,对符合条件的行做分组;可对每组分别做聚合计算;
  聚合计算AVG(), SUM(), MAX(), MIN(), COUNT()
  HAVING:对聚合计算的结果做过滤;
  ORDER BY col1[,...] {ASC|DESC}  #ASC表示升序,DESC表示降序
  LIMIT [offset,]count   #offset表示偏移量
  示例:
  select Name as Pupil,Age from students;   #字段和表都可以使用as取别名
  select distinct Age from students;   #distinct表示对于相同的结果只显示一次

  select>  select Name,Age from students order by Age desc limit 4,3; #略过前4个后显示3个
  ⑵select语句的执行顺序
  FROM --> WHERE --> GROUP BY --> HAVING --> ORDER BY --> SELECT --> LIMIT
  选择->分组->对聚合计算的结果做过滤->排序->投影->截取指定数目的行
  ⑶连接查询:事先将两张或多张表执行相应的join操作,而后根据join结果做查询;
  交叉连接
  内连接:
  等值连接,不等值连接,自然连接
  外连接:
  左外连接:以左表为基准,右表对应在数据不存在则留空
  右外连接
  示例:

  select students.Name,classes.Class from students,classes where students.ClassID =>  select s.Name as student,t.Name as teacher from students as s left join teachers as t on s.TeacherID = t.TID;   #左外连接
  ⑷子查询:查询中嵌套着查询;msyql的子查询性能不好,尽量不要使用
  用于WHERE子句的子查询:
  ①用于比较表达式中的子查询,要求子查询只能返回单个结果(IN除外);
  select Name,Age from students where Age > (select avg(Age) from students);
  ②用于IN中的子查询,判断是否存在于指定的列表中
  select Name from students where StuID in (select TID from teachers);
  ③用于EXISTS中子查询
  用于FROM中的子查询
  SELECT alias.col,... FROM (SELECT statement) AS alias WHERE clause;   #必须给子表取个别名
  例:select s.Name from (select * from students where Age > 20) as s where s.Name like 's%';
  ⑸联合查询:将两个或多个返回值字段相同的查询的结果合并输出;
  SELECT statement UNION SELECT statement;
  例:select Name,Age from teachers where Age >= 40 union select Name,Age from students where Age >= 40;
  ▲示例:
  导入hellodb.sql,完成以下题目:
  ①对于students表,以Gender分组,显示各组中年龄大于19的学员的年龄之和
  ②显示前5位同学的姓名、课程及成绩;
  ③求前6位同学每位同学自己所有课程的平均成绩,并按降序排列;
  ④显示每门课程课程名称及学习了这门课的同学的个数;
MariaDB [(none)]> source hellodb.sql   #  
Query OK, 0 rows affected (0.05 sec)
  
...
  
MariaDB [hellodb]> use hellodb
  
Database changed
  
MariaDB [hellodb]> show tables;
  
+-------------------+
  
| Tables_in_hellodb |
  
+-------------------+
  
| classes           |
  
| coc               |
  
| courses           |
  
| scores            |
  
| students          |
  
| teachers          |
  
| toc               |
  
+-------------------+
  
7 rows in set (0.05 sec)
  

  
MariaDB [hellodb]> desc students;
  
+-----------+---------------------+------+-----+---------+----------------+
  
| Field     | Type                | Null | Key | Default | Extra          |
  
+-----------+---------------------+------+-----+---------+----------------+
  
| StuID     | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
  
| Name      | varchar(50)         | NO   |     | NULL    |                |
  
| Age       | tinyint(3) unsigned | NO   |     | NULL    |                |
  
| Gender    | enum('F','M')       | NO   |     | NULL    |                |
  
| ClassID   | tinyint(3) unsigned | YES  |     | NULL    |                |
  
| TeacherID | int(10) unsigned    | YES  |     | NULL    |                |
  
+-----------+---------------------+------+-----+---------+----------------+
  
6 rows in set (0.13 sec)
  

  
MariaDB [hellodb]> desc courses;
  
+----------+----------------------+------+-----+---------+----------------+
  
| Field    | Type                 | Null | Key | Default | Extra          |
  
+----------+----------------------+------+-----+---------+----------------+
  
| CourseID | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
  
| Course   | varchar(100)         | NO   |     | NULL    |                |
  
+----------+----------------------+------+-----+---------+----------------+
  
2 rows in set (0.00 sec)
  

  
MariaDB [hellodb]> desc scores;
  
+----------+----------------------+------+-----+---------+----------------+
  
| Field    | Type                 | Null | Key | Default | Extra          |
  
+----------+----------------------+------+-----+---------+----------------+
  
| ID       | int(10) unsigned     | NO   | PRI | NULL    | auto_increment |
  
| StuID    | int(10) unsigned     | NO   |     | NULL    |                |
  
| CourseID | smallint(5) unsigned | NO   |     | NULL    |                |
  
| Score    | tinyint(3) unsigned  | YES  |     | NULL    |                |
  
+----------+----------------------+------+-----+---------+----------------+
  
4 rows in set (0.00 sec)
  

  

  
MariaDB [hellodb]> select sum(Age) from students where Age > 19 group by Gender;
  
+----------+
  
| sum(Age) |
  
+----------+
  
|       62 |
  
|      476 |
  
+----------+
  
2 rows in set (0.00 sec)
  

  
MariaDB [hellodb]> select prestu.Name,courses.Course,scores.Score from
  
    -> (select Name from students limit 5) as prestu,students,courses,scores
  
    -> where prestu.Name = students.Name and students.StuID = scores.StuID and scores.CourseID = courses.CourseID;
  
+-------------+----------------+-------+
  
| Name        | Course         | Score |
  
+-------------+----------------+-------+
  
| Shi Zhongyu | Kuihua Baodian |    77 |
  
| Shi Zhongyu | Weituo Zhang   |    93 |
  
| Shi Potian  | Kuihua Baodian |    47 |
  
| Shi Potian  | Daiyu Zanghua  |    97 |
  
| Xie Yanke   | Kuihua Baodian |    88 |
  
| Xie Yanke   | Weituo Zhang   |    75 |
  
| Ding Dian   | Daiyu Zanghua  |    71 |
  
| Ding Dian   | Kuihua Baodian |    89 |
  
| Yu Yutong   | Hamo Gong      |    39 |
  
| Yu Yutong   | Dagou Bangfa   |    63 |
  
+-------------+----------------+-------+
  
10 rows in set (0.13 sec)
  

  
MariaDB [hellodb]> select students.Name,avg(scores.Score) from students,courses,scores
  
    -> where students.StuID = scores.StuID and scores.CourseID = courses.CourseID
  
    -> group by students.Name order by avg(scores.Score) desc limit 6;
  
+-------------+-------------------+
  
| Name        | avg(scores.Score) |
  
+-------------+-------------------+
  
| Shi Qing    |           96.0000 |
  
| Shi Zhongyu |           85.0000 |
  
| Xi Ren      |           84.5000 |
  
| Xie Yanke   |           81.5000 |
  
| Ding Dian   |           80.0000 |
  
| Lin Daiyu   |           75.0000 |
  
+-------------+-------------------+
  
6 rows in set (0.61 sec)
  

  

  
MariaDB [hellodb]> select courses.Course,count(courses.Course) from students,courses,scores
  
    -> where students.StuID = scores.StuID and scores.CourseID = courses.CourseID
  
    -> group by courses.Course;
  
+----------------+-----------------------+
  
| Course         | count(courses.Course) |
  
+----------------+-----------------------+
  
| Dagou Bangfa   |                     2 |
  
| Daiyu Zanghua  |                     2 |
  
| Hamo Gong      |                     3 |
  
| Jinshe Jianfa  |                     1 |
  
| Kuihua Baodian |                     4 |
  
| Taiji Quan     |                     1 |
  
| Weituo Zhang   |                     2 |
  
+----------------+-----------------------+
  
7 rows in set (0.00 sec)
  五、视图(view)
  视图是存储下来的SELECT语句,是虚表
  虚表:视图
  基表:视图中的查询语句针对其进行查询的表
  ⑴创建视图:CREATE VIEW view_name AS SELECT statement;
  例:create view stus as select Stuid,Name,Age,Gender from students;
  ⑵删除视图:DROP VIEW view_name;
  ⑶可以将视图当作普通表那样执行插入、删除或更新操作,基表上的数据也会随之改变
  六、EXPLAIN:获取关于查询执行计划的信息
  EXPLAIN [explain_type] SELECT select_options;
  例:explain select Name,Age from students where Name like 's%' or Age > 28\G
  id: 1
  select_type: SIMPLE
  table: students
  type: index_merge
  possible_keys: Name,Age
  key: Name,Age
  key_len: 152,1
  ref: NULL
  rows: 7
  Extra: Using sort_union(Name,Age); Using where
  说明:

  >  select_type:查询类型
  SIMPLE
  PRIMARY:联合查询中的第一个查询
  UNION:联合查询中相对于第一个查询而言的后续的查询
  UNION RESULT:UNION的执行结果
  SUBQUERY
  DERIVED:FROM子句中的子查询
  table:查询语句所关系到的表的名字;
  type:访问到目标记录的方法
  system:表中仅有一行;
  const:表中至多有一行匹配;一般只有用于PRIMARY KEY或UNIQUE KEY(NOT NULL)索引时,此种结果才会出现;
  eq_ref:使用的索引是唯一索引,表中至多有一行匹配
  ref:[用到的索引不是主键或唯一健,即等值比较返回的结果可能不只一个]
  fulltext
  ref_or_null
  index_merge: 索引合并优化
  unique_subquery:通常出现于IN子查询中
  index_subquery:类似上一个
  range:带有范围限制的索引
  index:全索引扫描
  ALL:全表扫描
  rows:加载到内存中的行数
  Extra:
  using where:从存储引擎返回的结果不是最终结果,还需二次过滤
  using index:从索引中就能得到最终结果,即覆盖索引
  using index for group by
  using filesort:使用外部索引排序
  七、索引
  索引是特殊的数据结构,用于快速找到数据记录
  ⑴索引类型:B-Tree (B+ Tree) (左前缀), hash (key-value), R-Tree, FullText
  ⑵索引的优点:
  大大减少服务器需要扫描的数据量;
  索引可以帮助服务器尽量避免进行排序及使用临时表;
  索引可以将随机I/O转换为顺序I/O;
  ⑶索引可以创建在一个字段,也可是多个字段上:简单索引、组合索引
  示例:索引(姓名,性别)
  where name like 'tom%'
  where gender = 'female'  #此用法使用不上索引
  where familyname = 'tom' and gender = 'female'
  ⑷键可作索引,但并非所有的索引都是键
  ⑸B-Tree索引的适用场景:
  全值匹配:使用比较操作符 =,
  左前缀匹配:LIKE 'tom%'
  列前缀匹配
  匹配范围值:
  组合索引类型中,精确匹配前一列,并范围匹配后一列;
  只访问索引的查询:覆盖索引,即从索引就可以直接得到最终结果;
  ⑹哈希索引适用场景:
  哈希索引只包含哈希值和行指针;不是按照索引值顺序存储,无法用于排序;不支持部分索引匹配查找;
  哈希索引只支持等值比较查询,包含 =, IN(),
  ⑺聚簇索引和非聚簇索引
  聚簇索引:索引和数据一起存放
  数据文件:索引顺序文件
  非聚簇索引:索引和数据分开存放,而数据记录未必顺序存放;但索引数据一般顺序存放;
  数据文件:堆文件
  ⑻添加索引:
  在创建表的同时创建索引
  CREATE TABLE tb_name (col1_def,...INDEX [index_name] [index_type] (index_col_name,...) [index_option] ...);
  index_type: USING {BTREE | HASH}   #默认是BTREE
  给已存在的表添加索引
  CREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type] ON tbl_name (index_col_name,...) [index_option] ...;
  例:create index na_ge on testdb.students (Name,Gender);
  ALTER TABLE tb_name ADD INDEX [index_name] [index_type] (index_col_name,...) [index_option] ...;
  ⑼显示表上的索引:
  SHOW {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name [{FROM | IN} db_name] [WHERE expr];
  ⑽删除索引
  DROP [ONLINE|OFFLINE] INDEX index_name ON tbl_name;
  ALTER TABLE tb_name DROP INDEX index_name;
  ⑾索引创建的基本法则:基于搜索键来创建,SELECT的WHERE子句的查询条件中的字段;
  ⑿索引的使用策略:
  ①要使用独立的列:索引列不是表达式的一部分;
  SELECT Name FROM students WHERE Age + 2 > 32;  #错误的使用方式
  ②前缀索引
  索引选择性:单独的索引项与数据表中的记录的总数的比值;取值范围:0 show indexes in testdb.students;   #查看表上的索引  
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
  
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  
| students |          0 | ID       |            1 | ID          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
  
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  
1 row in set (0.07 sec)
  

  
MariaDB [hellodb]> create index na_ge on testdb.students (Name,Gender);   #创建一个组合索引
  
Query OK, 0 rows affected (1.30 sec)
  
Records: 0  Duplicates: 0  Warnings: 0
  

  
MariaDB [hellodb]> show indexes in testdb.students;
  
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
  
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  
| students |          0 | ID       |            1 | ID          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
  
| students |          1 | na_ge    |            1 | Name        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
  
| students |          1 | na_ge    |            2 | Gender      | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
  
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  
3 rows in set (0.00 sec)
  

  
MariaDB [hellodb]> drop index na_ge on testdb.students;
  
Query OK, 0 rows affected (0.06 sec)
  
Records: 0  Duplicates: 0  Warnings: 0
  八、几个常用的SHOW命令
  ①查看MariaDB支持哪此存储引擎:SHOW ENGINES;
  ②查看表的属性信息:SHOW TABLE STATUS [LIKE 'tb_name'];
  ③查看支持的字符集:SHOW CHARACTER SET;
  ④查看排序规则:SHOW COLLATION;
  ⑤查看MySQL的服务器变量或状态变量:SHOW {GLOBAL|SESSION} VARIALES [LIKE ''];
  ⑥查看MySQL的状态变量:SHOW {GLOBAL|SESSION} STATUS [LIKE ''];



运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表,该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有,请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意,请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息,一经发现立即删除。若您因此触犯法律,一切后果自负,我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责,亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏,不得用于商业或者其他用途,否则,一切后果均由您自己承担,我们对此不承担任何法律责任
7、如涉及侵犯版权等问题,请您及时通知我们,我们将立即采取措施予以解决
8、联系人Email:admin@iyunv.com 网址:www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址:https://www.yunweiku.com/thread-624651-1-1.html 上篇帖子: Fatal server error:Server is already active 下篇帖子: 大话数据库SQL注入的N种姿势
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群,最新一手资源尽在官方微信交流群!快快加入我们吧...

扫描微信二维码查看详情

客服E-mail:kefu@iyunv.com 客服QQ:1061981298


QQ群⑦:运维网交流群⑦ QQ群⑧:运维网交流群⑧ k8s群:运维网kubernetes交流群


提醒:禁止发布任何违反国家法律、法规的言论与图片等内容;本站内容均来自个人观点与网络等信息,非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来,其版权均归原作者及其网站所有,我们尊重他人的合法权益,如有内容侵犯您的合法权益,请及时与我们联系进行核实删除!



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表