IT虾米网

MySQL学习之基本语法详解

admin 2018年05月29日 数据库 176 0

MySQL学习笔记之数据库、数据表的基本操作 MySQL是一种小型关系型数据库管理系统

1.SQL语言的分类

  • 数据定义语言(DDL): DROP、CREATE、ALTER等语句

  • 数据操作语言(DML): INSERT、UPDATE、DELETE

  • 数据查询语言(DQL): SELECT

  • 数据控制语言(DCL): GRANT、REVOKE、COMMIT、ROLLBACK等语句

2.数据库访问接口

  • ODBC(ODBC,开放数据库互连)

  • ADO.NET(.NET)

  • PDO(PHP)

  • JDBC(JAVA)用于Java应用程序连接数据库的标准方法,是一种用户执行SQL语句的Java API,可以为多种关系数据库提供统一访问,他是由一组用Java语言编写的类和接口组成。

3.MySQL数据库的登录和访问

1. Windows

  • 启动MySQL服务:net start MySQL

  • 关闭MySQL服务:net stop MySQL注意: 以上命令中 MySQL 是你MySQL服务的名称(Windows服务管理器,在搜索框中输入services.msc),比如:你在服务管理器中看到你的MySQL服务名称是:mysql ,那么,你的命令就应该改为:启动: net start mysql 关闭: net stop mysql

2. Linux

  • 命令行安装

 style="font-family:Consolas, 'Liberation Mono', Courier, monospace;font-size:.9em;white-space:pre;text-align:left;background-image:inherit;background-attachment:inherit;background-color:rgb(248,248,248);border:1px solid rgb(221,221,221);padding:8px 1em 6px;margin-bottom:15px;margin-top:15px;color:rgb(51,51,51);font-style:normal;font-weight:normal;letter-spacing:normal;text-indent:0px;text-transform:none;word-spacing:0px;text-decoration:none;">sudo mysql apt-get update sudo mysql apt-get install mysql-server mysql-client
  • 查看是否启动

 style="font-family:Consolas, 'Liberation Mono', Courier, monospace;font-size:.9em;white-space:pre;text-align:left;background-image:inherit;background-attachment:inherit;background-color:rgb(248,248,248);border:1px solid rgb(221,221,221);padding:8px 1em 6px;margin-bottom:15px;margin-top:15px;color:rgb(51,51,51);font-style:normal;font-weight:normal;letter-spacing:normal;text-indent:0px;text-transform:none;word-spacing:0px;text-decoration:none;">pgrep mysqld
  • 完全卸载的办法

 style="font-family:Consolas, 'Liberation Mono', Courier, monospace;font-size:.9em;white-space:pre;text-align:left;background-image:inherit;background-attachment:inherit;background-color:rgb(248,248,248);border:1px solid rgb(221,221,221);padding:8px 1em 6px;margin-bottom:15px;margin-top:15px;color:rgb(51,51,51);font-style:normal;font-weight:normal;letter-spacing:normal;text-indent:0px;text-transform:none;word-spacing:0px;text-decoration:none;">sudo apt-get autoremove --purge mysql-server-5.6 sudo apt-get autoremove mysql-server sudo apt-get remove mysql-common sudo dpkg -l |grep ^rc|awk '{print $2}' |sudo xargs dpkg -P
  • 启动|停止|重启|查看MySQL数据库:service mysql start|stop|restart|status

3.统一登录命令

mysql -u root -p 回车后输入密码即可登录


4. 数据库的基本操作

查看所有数据库: show databases

创建数据库: create database databases_name;

使用数据库: use databases_name;

查看数据库创建信息: show create database databases_name;

删除数据库: drop database databases_name;


5. 数据库储存引擎

  • InnoDB: 提供提交、回滚和崩溃回复能力的事务安全能力,实现并发控制。

  • MyISAM: 对于数据表主要用来插入和查询数据,提供较高的处理效率。

  • Memory: 临时存放数据、数据量不大、并且不需要较高的数据安全性的首选。

  • Archive: 支持高并发的插入操作,本身不是事务安全的,适合储存归档数据、记录日志等

查看系统中所有的储存引擎show engines

查看数据库的默认储存引擎show variables like 'storage_engine';


6.数据类型和运算符

1.MySQL中整数数据类型

 style="font-family:Consolas, 'Liberation Mono', Courier, monospace;font-size:.9em;white-space:pre;text-align:left;background-image:inherit;background-attachment:inherit;background-color:rgb(248,248,248);border:1px solid rgb(221,221,221);padding:8px 1em 6px;margin-bottom:15px;margin-top:15px;color:rgb(51,51,51);font-style:normal;font-weight:normal;letter-spacing:normal;text-indent:0px;text-transform:none;word-spacing:0px;text-decoration:none;">tinyint       1字节 smallint      2字节 mediumint     3字节 int(integer)  4字节 bigint        8字节  整数类型的属性字段可以添加auto_increment自增约束条件

2.浮点数类型和定点数类型

 style="font-family:Consolas, 'Liberation Mono', Courier, monospace;font-size:.9em;white-space:pre;text-align:left;background-image:inherit;background-attachment:inherit;background-color:rgb(248,248,248);border:1px solid rgb(221,221,221);padding:8px 1em 6px;margin-bottom:15px;margin-top:15px;color:rgb(51,51,51);font-style:normal;font-weight:normal;letter-spacing:normal;text-indent:0px;text-transform:none;word-spacing:0px;text-decoration:none;">浮点类型: float   单精度  4个字节 double  双精度  8个字节  定点类型: decimal

3.日期和时间类型

 style="font-family:Consolas, 'Liberation Mono', Courier, monospace;font-size:.9em;white-space:pre;text-align:left;background-image:inherit;background-attachment:inherit;background-color:rgb(248,248,248);border:1px solid rgb(221,221,221);padding:8px 1em 6px;margin-bottom:15px;margin-top:15px;color:rgb(51,51,51);font-style:normal;font-weight:normal;letter-spacing:normal;text-indent:0px;text-transform:none;word-spacing:0px;text-decoration:none;">year      YYYY(1901-2155)     一字节 time      HH:MM:SS              三字节 date      YYYY-MM-DD            三字节 datatime  YYYY-MM-DD HH:MM:SS   八字节 timestamp YYYY-MM-DD HH:MM:SS   四字节

6. 数据表的基本操作

1.创建数据表:

语法

 style="font-family:Consolas, 'Liberation Mono', Courier, monospace;font-size:.9em;white-space:pre;text-align:left;background-image:inherit;background-attachment:inherit;background-color:rgb(248,248,248);border:1px solid rgb(221,221,221);padding:8px 1em 6px;margin-bottom:15px;margin-top:15px;color:rgb(51,51,51);font-style:normal;font-weight:normal;letter-spacing:normal;text-indent:0px;text-transform:none;word-spacing:0px;text-decoration:none;">use database databases_name;  create table table_name(   字段名1   数据类型  [约束条件]  [默认值],   字段名2   数据类型  [约束条件]  [默认值],   ...   [表级约束条件] );

1.主键约束

要求主键列的数据唯一,且不允许为空。主键分为:1、单字段主键 2、多字段联合主键

1.单字段主键

 style="font-family:Consolas, 'Liberation Mono', Courier, monospace;font-size:.9em;white-space:pre;text-align:left;background-image:inherit;background-attachment:inherit;background-color:rgb(248,248,248);border:1px solid rgb(221,221,221);padding:8px 1em 6px;margin-bottom:15px;margin-top:15px;color:rgb(51,51,51);font-style:normal;font-weight:normal;letter-spacing:normal;text-indent:0px;text-transform:none;word-spacing:0px;text-decoration:none;">create table test(   id int primary key,   username varchar(100),   password varchar(100) );  或  create table test(   id int,   username varchar(100),   password varchar(100),   primary key(id) );

2.多字段联合主键

 style="font-family:Consolas, 'Liberation Mono', Courier, monospace;font-size:.9em;white-space:pre;text-align:left;background-image:inherit;background-attachment:inherit;background-color:rgb(248,248,248);border:1px solid rgb(221,221,221);padding:8px 1em 6px;margin-bottom:15px;margin-top:15px;color:rgb(51,51,51);font-style:normal;font-weight:normal;letter-spacing:normal;text-indent:0px;text-transform:none;word-spacing:0px;text-decoration:none;">create table test(   id int,   deptId int,   username varchar(100),   password varchar(100),   primary key(id,deptId) );

2.外键约束

解释: 外键是表中的一个字段,它可以不是表本身的主键,但对应另外一张表的主键。外键用来连接两张表,它可以是一列或多列。一章表可以有一个或多个外键。外键可以为空值,若不为空值,则必须等于另一张表的主键的某个值。所以外键主要作用是为了保证数据引用的完整性,定义外键后不允许删除在另一张表中有关联的行

  • 主表(父表):主键值所在的表

  • 从表(子表):外键值所在的表

语法:

 style="font-family:Consolas, 'Liberation Mono', Courier, monospace;font-size:.9em;white-space:pre;text-align:left;background-image:inherit;background-attachment:inherit;background-color:rgb(248,248,248);border:1px solid rgb(221,221,221);padding:8px 1em 6px;margin-bottom:15px;margin-top:15px;color:rgb(51,51,51);font-style:normal;font-weight:normal;letter-spacing:normal;text-indent:0px;text-transform:none;word-spacing:0px;text-decoration:none;">constraint <外键名> foreign key 字段名1 [,字段名2...] references <主表名> 主键列1 [,主键列2...]  <外键名>:为定义外键约束而定义的名称,一表中不能存在相同名称的外键; <字段名>:表示子表需要添加外键约束的字段列; <主表名>:被字表外键所依赖的表的名称; <主键列>:主表中定义的主键列;

实例

 style="font-family:Consolas, 'Liberation Mono', Courier, monospace;font-size:.9em;white-space:pre;text-align:left;background-image:inherit;background-attachment:inherit;background-color:rgb(248,248,248);border:1px solid rgb(221,221,221);padding:8px 1em 6px;margin-bottom:15px;margin-top:15px;color:rgb(51,51,51);font-style:normal;font-weight:normal;letter-spacing:normal;text-indent:0px;text-transform:none;word-spacing:0px;text-decoration:none;"><主表> create table tb_dept(   id int primary key,   name varchar(100),   location varchar(100) );   <子表> create table tb_emp(   id int primary key,   name varchar(100),   password varchar(100),   dept_id int,   constraint fk_emp_dept foreign key(dept_id) references tb_dept(id)  );

注意:创建的字表中的外键数据类型必须和主表的主键数据类型匹配,否则无法创建字表。

3.非空约束

  • 语法 字段名 数据类型 not null

  • 注意 非空约束的字段的值不能为空,如果添加数据没有指定这个字段,就会报错。

4.唯一性约束

  • 解释: 唯一性约束要求该列唯一,允许为空,但只能出现一个空值。保证一列或几列不出现重复值。

  • 语法: 1.在定义完列之后就指定唯一约束:name varchar(100) unique, 2.在定义完所有列之后指定唯一约束:constraint <约束名> unique(<字段名>);

5.默认约束

  • 解释: 指定某列的默认值。如果插入信息的数据没有为这个字段赋值,就会系统就会默认为这个字段添加该值

  • 语法: 字段名 数据类型 default 默认值

6.设置表的属性值自动增加

  • 解释: 当我们希望每次插入新的记录时,系统自动生成字段的主键值。那么久可以对主键用 auto_increment 约束,这样此主键的初始值为1,每次新增一条记录,字段值都会自动加1。

  • 语法: 字段名 数据类型 auto_increment

  • 示例:

 style="font-family:Consolas, 'Liberation Mono', Courier, monospace;font-size:.9em;white-space:pre;text-align:left;background-image:inherit;background-attachment:inherit;background-color:rgb(248,248,248);border:1px solid rgb(221,221,221);padding:8px 1em 6px;margin-bottom:15px;margin-top:15px;color:rgb(51,51,51);font-style:normal;font-weight:normal;letter-spacing:normal;text-indent:0px;text-transform:none;word-spacing:0px;text-decoration:none;">create table tb_emp_2(   id int primary key auto_increment,   name varchar(100) not null,   password varchar(100) not null );  执行:   insert into tb_emp_2(name,password) values('tycoding','123'); 结果:     +----+----------+----------+   | id | name     | password |   +----+----------+----------+   |  1 | tycoding | 123      |   +----+----------+----------+ 分析:   在insert语句中,我们并没有输入主键值,但是存入的数据默认已经有值了(从1开始递增)。
  • 注意:以上插入语句 insert into tb_emp_2(name,password) values('',''); 已经指定了插入的数据是哪条(name,password)。如果我们使用 insert into tb_emp_2 values('tycoding','123') 因没有指定插入的数据是哪个字段的,所以就会报错

  • 提示:使用以上语法向一个存在自增约束主键的表中插入数据,要注意:插入的数据默认的id是递增的,且如果你删除了哪个id对应的行,那么再次插入数据,是从此删除id开始递增的,也就是,你即使删除了数据,但是递增的序列不会后退。

2. 查看数据表结构

语法:

show create table <表名>; 或 describe <表名>; 或 desc <表名>;

 style="font-family:Consolas, 'Liberation Mono', Courier, monospace;font-size:.9em;white-space:pre;text-align:left;background-image:inherit;background-attachment:inherit;background-color:rgb(248,248,248);border:1px solid rgb(221,221,221);padding:8px 1em 6px;margin-bottom:15px;margin-top:15px;color:rgb(51,51,51);font-style:normal;font-weight:normal;letter-spacing:normal;text-indent:0px;text-transform:none;word-spacing:0px;text-decoration:none;">  show create table tb_emp_2;     | tb_emp_2 | CREATE TABLE `tb_emp_2` (       `id` int(11) NOT NULL AUTO_INCREMENT,       `name` varchar(100) NOT NULL,       `password` varchar(100) NOT NULL,       PRIMARY KEY (`id`)     ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 |    describe/desc tb_emp_2;     +----------+--------------+------+-----+---------+----------------+     | Field    | Type         | Null | Key | Default | Extra          |     +----------+--------------+------+-----+---------+----------------+     | id       | int(11)      | NO   | PRI | NULL    | auto_increment |     | name     | varchar(100) | NO   |     | NULL    |                |     | password | varchar(100) | NO   |     | NULL    |                |     +----------+--------------+------+-----+---------+----------------+

3.修改数据表

1.修改表名

语法: alter table <旧表名> rename <新表名>;

2.修改字段的数据类型

语法: alter table <表名> modify <字段名> <数据类型>;

3.修改字段名

alter table <表名> change <旧字段名> <新字段名> <新数据类型>; 比如修改id字段: alter table tb_emp_2 change id t_id int 注: 修改字段名,必须要指定新数据类型,可以与之前的相同。 对于修改id主键名,如果仅指定了新字段名的数据类型,没有写比如auto_increment约束, 那么新主键id字段就会丢失这个约束,但仍是主键,即primary key不变

示例:

 style="font-family:Consolas, 'Liberation Mono', Courier, monospace;font-size:.9em;white-space:pre;text-align:left;background-image:inherit;background-attachment:inherit;background-color:rgb(248,248,248);border:1px solid rgb(221,221,221);padding:8px 1em 6px;margin-bottom:15px;margin-top:15px;color:rgb(51,51,51);font-style:normal;font-weight:normal;letter-spacing:normal;text-indent:0px;text-transform:none;word-spacing:0px;text-decoration:none;">  show create table tb_emp_2\G;    Create Table: CREATE TABLE `tb_emp_2` (     `id` int(11) NOT NULL AUTO_INCREMENT,     `name` varchar(100) NOT NULL,     `password` varchar(100) NOT NULL,     PRIMARY KEY (`id`)   ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1    ------------------------------------------      alter table tb_emp_2 change id t_id int;    Create Table: CREATE TABLE `tb_emp_2` (     `t_id` int(11) NOT NULL,     `name` varchar(100) NOT NULL,     `password` varchar(100) NOT NULL,     PRIMARY KEY (`t_id`)   ) ENGINE=InnoDB DEFAULT CHARSET=latin1

4.添加字段

  • 语法: alter table <表名> add <新字段名> <数据类型> [约束条件] [FIRST | AFTER 已存在的字段名];解释: first | after 已存在的字段名 ,即指定插入新的字段到什么位置(默认是添加在最后)

  • 示例:

 style="font-family:Consolas, 'Liberation Mono', Courier, monospace;font-size:.9em;white-space:pre;text-align:left;background-image:inherit;background-attachment:inherit;background-color:rgb(248,248,248);border:1px solid rgb(221,221,221);padding:8px 1em 6px;margin-bottom:15px;margin-top:15px;color:rgb(51,51,51);font-style:normal;font-weight:normal;letter-spacing:normal;text-indent:0px;text-transform:none;word-spacing:0px;text-decoration:none;">alter table tb_emp_2 add age  varchar(100) not null AFTER;

5.删除字段

  • 语法: alter table <表名> drop <字段名>;

6.修改字段的排列位置

  • 语法: alter table <表名> modify <字段一> <数据类型> FIRST|AFTER <字段二>;

  • 解释: <字段一>是要修改位置的字段,<数据类型>是要修改位置的字段的数据类型;FIRST|AFTER <字段二>表示要修改位置到字段二的前面还是后面

7.更改表的储存引擎

  • 查看系统支持的所有数据引擎: show engines

  • 修改表的储存引擎: alter table <表名> engine=<更改后的储存引擎名>

8.删除表的外键约束

  • 语法: alter table <表名> drop foreign key <外键约束名>

4.删除数据表

1.删除没有被关联的表

 style="font-family:Consolas, 'Liberation Mono', Courier, monospace;font-size:.9em;white-space:pre;text-align:left;background-image:inherit;background-attachment:inherit;background-color:rgb(248,248,248);border:1px solid rgb(221,221,221);padding:8px 1em 6px;margin-bottom:15px;margin-top:15px;color:rgb(51,51,51);font-style:normal;font-weight:normal;letter-spacing:normal;text-indent:0px;text-transform:none;word-spacing:0px;text-decoration:none;">**语法:** `drop table [if exists] 表1,表2...;`

2.删除被其他表关联的主表

 style="font-family:Consolas, 'Liberation Mono', Courier, monospace;font-size:.9em;white-space:pre;text-align:left;background-image:inherit;background-attachment:inherit;background-color:rgb(248,248,248);border:1px solid rgb(221,221,221);padding:8px 1em 6px;margin-bottom:15px;margin-top:15px;color:rgb(51,51,51);font-style:normal;font-weight:normal;letter-spacing:normal;text-indent:0px;text-transform:none;word-spacing:0px;text-decoration:none;">*步骤:* 因为两张表关联,直接删除主表会报错。那么想要保留字表,并删除主表,可以:     ```     -- 查看子表结构     show create table tb_emp_2;          -- 根据外键名先删除外键     alter table tb_rmp_2 drop foreign key fk_emp_dept;          -- 删除主表     drop table tb_dept;     ```

常用运算符

  • 算术运算符: +、-、*、/、求余、%

  • 比较运算符: >、<、=、>=、<=、!=、IN、BETWEEN AND、IS NULL、GREATEST、LEAST、LIKE、REGEXP等

  • 逻辑运算符: 1(TRUE)、0(FALSE)、非(NOT或!)、与(AND或&&)、或(OR或||)、异或(XOR)

  • 位运算符: 位与(&)、位或(|)、位非(~)、位异或(^)、左移(<<)、右移(>>)

<!--more-->

常用运算符的语法

 style="font-family:Consolas, 'Liberation Mono', Courier, monospace;font-size:.9em;white-space:pre;text-align:left;background-image:inherit;background-attachment:inherit;background-color:rgb(248,248,248);border:1px solid rgb(221,221,221);padding:8px 1em 6px;margin-bottom:15px;margin-top:15px;color:rgb(51,51,51);font-style:normal;font-weight:normal;letter-spacing:normal;text-indent:0px;text-transform:none;word-spacing:0px;text-decoration:none;">1. BETWEEN AND   select value BETWEEN min AND max;             -->假如expr大于或等于min且小于或等于max,则BETWEEN的返回值为1,否则为0  2. LEAST   select least(value1,value2,value3...);        -->所有比较的值中不含null就返回其中的最小值,有一个值为null,就返回null  3. GREATEST   select greatest(value1,value2,...);           -->所有比较的值中不含null就返回其中的最大值,有一个值为null,就返回null  4. IN、NOT IN   select value in(value1,value2,...);           -->所有比较的值中不含null值,若包含在in列表中就返回1,否则返回0,如果in列表中存在null,就返回null   select value not in(value1,value2,...);   5. LIKE   select value like value1;                     -->如果value1包含在value中就返回1   select value lile val_;                       -->只能匹配一个字符   select value like val%;                       -->匹配任何数目的字符,甚至包括零字符

<br/>

常用函数

IF函数

语法:

 style="font-family:Consolas, 'Liberation Mono', Courier, monospace;font-size:.9em;white-space:pre;text-align:left;background-image:inherit;background-attachment:inherit;background-color:rgb(248,248,248);border:1px solid rgb(221,221,221);padding:8px 1em 6px;margin-bottom:15px;margin-top:15px;color:rgb(51,51,51);font-style:normal;font-weight:normal;letter-spacing:normal;text-indent:0px;text-transform:none;word-spacing:0px;text-decoration:none;">IF(expr,v1,v2) 例如:select if(1>2,2,3)    -->1>2是否正确,正确就返回2,否则就返回3  IFNULL(v1,v2) 例如:select ifnull(1,2)    -->如果v1不为null,就返回v1,否则就返回v2

CASE函数

 style="font-family:Consolas, 'Liberation Mono', Courier, monospace;font-size:.9em;white-space:pre;text-align:left;background-image:inherit;background-attachment:inherit;background-color:rgb(248,248,248);border:1px solid rgb(221,221,221);padding:8px 1em 6px;margin-bottom:15px;margin-top:15px;color:rgb(51,51,51);font-style:normal;font-weight:normal;letter-spacing:normal;text-indent:0px;text-transform:none;word-spacing:0px;text-decoration:none;">case expr when v1 then r1 [when v2 then r2] else rn end; 解释:如果expr的值等于v1,就返回r1,如果expr等于v2,就返回r2,如果expr不等于v1、v2,就返回rn.  例如:   select cast 1 when 1 then '等于1' when 2 then '等于2' else '不等于1页不等于2' end;   结果:等于1

其他函数

 style="font-family:Consolas, 'Liberation Mono', Courier, monospace;font-size:.9em;white-space:pre;text-align:left;background-image:inherit;background-attachment:inherit;background-color:rgb(248,248,248);border:1px solid rgb(221,221,221);padding:8px 1em 6px;margin-bottom:15px;margin-top:15px;color:rgb(51,51,51);font-style:normal;font-weight:normal;letter-spacing:normal;text-indent:0px;text-transform:none;word-spacing:0px;text-decoration:none;">1. charset(str) 返回str自变量的字符集     select charset('tycoding');    -->utf8  2. convert(...using...) 改变字符集的函数     select charset('tycoding'),charset(convert('tycoding' using latin1));      +---------------------+-------------------------------------------+     | charset('tycoding') | charset(convert('tycoding' using latin1)) |     +---------------------+-------------------------------------------+     | utf8                | latin1                                    |     ---------------------+--------------------------------------------+  3. last_insert_id() 返回最后一个自动生成ID值的函数




发布评论

分享到:

IT虾米网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!

MySQL存储过程基础语法详解
你是第一个吃螃蟹的人
发表评论

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。