使用SqlSugar操作SQL Server数据库 使用sql语言

1、什么是SQL

1. SQL的全称是“结构话查询语句”（Structured Query Language ）,是1974年有Boyce和chamberlin 提出来的。经过多年的发展，SQL语言已经成为关系数据库的标准语言。
2. SQL不同与java这样的程序语言，它是只能被数据库识别的指令，但是在程序中，可以利用其他编程组织SQL语句发送给数据库，数据库在执行相应的操作。例如，在java程序中要得到MYSQL数据表中的记录，可以在java程序中编写SQL语句，再发送到数据库，数据库根据收到的SQL语句执行，并把执行的结果返回给java程序员。

2、SQL的组成

SQL yu言主要由以下几个部分组成。

1. DML(Data Manipulation Language， 数据操作语言，也称为数据操纵语言）；用来插入、修改和删除数        据以库中的数据，例如INSTER, UPDATE及DELETE等。
2. DDL (Date Definition Language，数据定义语言)；用来建立数据库、数据库对象和定义器列表，大部分          是CREATE 开头的命令，如CREATE TABLE，CREATE VIEW ，以及DROP TABLE等。
3. DQL(Data Query Language,数据查询语句)；用来对数据库中数据进行查询。
4. DCL (Data Control Langure,数据控制语言)；用来控制数据库组件的存取许可，存取权限等，如                    GRANT,REVOKE等。

除此之外，SQL还包括变量说明，内部函数等其他命令。

3、SQL 中的运算符

运算符是一种符号，用来进行行列之间或变量之间进行比较和数学运算。在SQL中，常用的几种运算符号有计算术运算符、赋值运算符、比较运算符和逻辑运算符。

3.1算术运算符

算术运算符包括：+（加）、-（减）、*（乘）、/（除）、%（模）五个。算术运算符用来在两个数或者表达式上执行数学运算，这两个表达式可以是任意两个数字数据类型的表达式，如表1-5所示。

SQL中的算术运算符

运算符
+	求两个或表达式相加的结果
-	求两个或表达式相减的差
*	求两个或表达式相乘的积
/	求两个或表达式相除的商
%	求两个或表达式相除的余数

3.2、赋值运算符　　

SQL有一个赋值运算符，即“=”（等号），用于用于将一个数或变量或表达式赋值给另一变量，如：

预算符	说明
=	把一个数或变量或表达式复制给另一个变量，例如，Name=‘王华’

3.3、比较运算符

比较运算符用来判断两个表达式的大小关系，除text、ntext或image数据类型的表达类型表达外，比较运算符几乎可以用于其他所有的表达式，SQL中的比较运算符如表

运算符	说明
=	等于，例如，age=23
>	大于，例如，price>100
<	小于
<>	不等于
>=	大于等于
<=	小于等于
!=	不等于

比较运算符计算结果为布尔数据类型，并根据测试条件的输出结果返回true或fale。

3.4、逻辑运算符

逻辑运算符用来对某个条件进行判断，以获得判断条件的真假，返回带true或false值的布尔数据类型。

运算符	说明
AND	当仅且当两个布尔表达式都为true时返回true
OR	当且仅当两个布尔表达式都为false时返回false
NOT	对布尔表达式的值取反，优先级最高

 

4.使用DDL语句操作数据库

4.1创建数据库

MySQL中创建数据库的基本SQL语法格式如下。

语法：

CREATE DATABASE 数据库名;

例如，创建myschool数据库的语句如下。

CREATE DATABASE  myschool;

4.2 查询数据库列表

执行查询数据库命令可以查看已经存在的数据库。

SHOW DATABASES;

从结果中发现，执行完成语句后，会显示一列表，改列表中除了有新建的muschool数据库之外，还有四个系统数据库。

4.3 选择数据库

数据库存放在数据表中，在对数据进行操作之前，需要确定改表所在的数据库，因此，在进行表操作之前需要先进行一个数据库。

USE  数据库名；
例如：
  USE  mybase;
  USE  myschool;

4.4 删除数据库

语法：DROP DATABASE 数据库名；
例如：
    DROP  DATABASE myschool;

4.5新增用户

(1)登录：mysql -u root -p  

(2)查看现有用户(mysql8.0.1)

mysql> select host,user,authentication_string from mysql.user;
 +-----------+------------------+----------------------------------------------------------------+
 | host | user | authentication_string |
 +-----------+------------------+----------------------------------------------------------------+
 | localhost | mysql.infoschema | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
 | localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
 | localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
 | localhost | root | $A$005$e!42 )Tf+4M{4W>MkFY9ktIVPhgVemeQsSQnuiGLRiH/909Zyaj9XF3/3Yk2 |
 +-----------+------------------+----------------------------------------------------------------+

(3)新建用户

格式:create user "username"@"host" identified by "password";

eg：

1.mysql->create user 'test'@'localhost' identified by '123';
2.mysql->create user 'test'@'192.168.7.22' identified by '123';

3.mysql->create user 'test'@'%' identified by '123';

    /*host="localhost"为本地登录用户，host="ip"为ip地址登录，host="%"，为外网ip登录*/

(4)删除用户

　格式：drop user 'username'@'host';

(5)授权

　格式：grant privileges on databasename.tablename to 'username'@'host' IDENTIFIED BY 'PASSWORD';

　  1. GRANT命令说明：
   　　 　　priveleges(权限列表),可以是all priveleges, 表示所有权限，也可以是select、update等权限，多个权限的名词,相互之间用逗号分开。

   　　 　　on用来指定权限针对哪些库和表。

   　　　　 *.* 中前面的*号用来指定数据库名，后面的*号用来指定表名。

    　　　　to 表示将权限赋予某个用户, 如 jack@'localhost' 表示jack用户，@后面接限制的主机，可以是IP、IP段、域名以及%，%表示任何地方。注意：这里%有的版本不包括本地，以前碰到过给某个用户设置了%允许任何地方登录，但是                  在本地登录不了，这个和版本有关系，遇到这个问题再加一个localhost的用户就可以了。

   　　       identified by指定用户的登录密码,该项可以省略。

    　　       WITH GRANT OPTION 这个选项表示该用户可以将自己拥有的权限授权给别人。注意：经常有人在创建操作用户的时候不指定WITH GRANT OPTION选项导致后来该用户不能使用GRANT命令创建用户或者给其它用户授权。

                 备注：可以使用GRANT重复给用户添加权限，权限叠加，比如你先给用户添加一个select权限，然后又给用户添加一个insert权限，那么该用户就同时拥有了select和insert权限。

　　2.授权原则说明：

　　　　权限控制主要是出于安全因素，因此需要遵循一下几个经验原则：

   　　　 a、只授予能满足需要的最小权限，防止用户干坏事。比如用户只是需要查询，那就只给select权限就可以了，不要给用户赋予update、insert或者delete权限。

    　　　b、创建用户的时候限制用户的登录主机，一般是限制成指定IP或者内网IP段。

    　　　c、初始化数据库的时候删除没有密码的用户。安装完数据库的时候会自动创建一些用户，这些用户默认没有密码。

    　　　d、为每个用户设置满足密码复杂度的密码。

   　　　 e、定期清理不需要的用户。回收权限或者删除用户。

　eg：

/*授予用户通过外网IP对于该数据库的全部权限*/

　　grant all privileges on `test`.* to 'test'@'%' ;

　 /*授予用户在本地服务器对该数据库的全部权限*/

　　grant all privileges on `test`.* to 'test'@'localhost';   

   grant select on test.* to 'user1'@'localhost';  /*给予查询权限*/

   grant insert on test.* to 'user1'@'localhost'; /*添加插入权限*/

   grant delete on test.* to 'user1'@'localhost'; /*添加删除权限*/

   grant update on test.* to 'user1'@'localhost'; /*添加权限*/

　 flush privileges; /*刷新权限*/

(6)查看权限

show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

查看某个用户的权限：

show grants for 'jack'@'%';
+-----------------------------------------------------------------------------------------------------+
| Grants for jack@%                                                                                   |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'jack'@'%' IDENTIFIED BY PASSWORD '*9BCDC990E611B8D852EFAF1E3919AB6AC8C8A9F0' |
+-----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

(7)删除权限

　　revoke privileges on databasename.tablename from 'username'@'host';

revoke delete on test.* from 'jack'@'localhost';

(8)更改用户名

　　mysql> rename user 'jack'@'%' to 'jim'@'%';

(9)修改密码

1.用set password命令

　　mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');

　　Query OK, 0 rows affected (0.00 sec)

2.用mysqladmin [root@rhel5 ~]# mysqladmin -uroot -p123456 password 1234abcd

　　备注： 格式：mysqladmin -u用户名 -p旧密码 password 新密码

3.用update直接编辑user表

(10)pycharm中python3.6+pymysql+mysql8.0.1连接报错　

pymysql.err.OperationalError: (1045, u"Access denied for user 'root'@'localhost' (using password: No)")

解决方法：　

在cmd命令行连接mysql, 通过mysql -u root -p dong1990

 

然后输入ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'dong1990';

 

 

 

5.SQLyog 管理工具

除了使用命令来操作MySQL数据库之外，还可以使用图形话工具来管理数据库。SQLyog官网（http://www.webyog.com/cn/）

 

 

 

6.使用DDL语句操作数据表

6.1 数据类型

我们知知道在java中通常会吧实体对象抽象成为一个实体类，一个实体类有很多的属性，每个属性都有自己的数据类型，如保存员工的姓名我们会选择使用String类型，二保存员工的性别我们可能会使用Character类型或者Integer类型。

数值类型

为方便阅读，下面列出了MySQL中的常用数值类型 

类型说明	存储需求	取值范围
tinyint[(m)]	1字节	有符号值：-128 到127（- 2^7 到2^7 – 1）  无符号值：0到255（0 到2^8 – 1）
smallint[(m)]	2字节	有符号值：-32768 到32767（- 2^15 到2^15 – 1）  无符号值：0到65535（0 到21 6 – 1）
mediumint[(m)]	3字节	有符号值：-8388608 到8388607（- 2^23 到2^23 – 1 ）  无符号值：0到16777215（0 到2^24 – 1）
int[(m)]	4字节	有符号值：-2147683648 到2147683647（- 2^31 到2^31- 1）  无符号值：0到4294967295（0 到2^32 – 1）
bigint[(m)]	8字节	有符号值：-9223372036854775808 到9223373036854775807（- 2^63到2^63-1）  无符号值：0到18446744073709551615（0到2^64 – 1）
float[(m, d)]	4字节	最小非零值：±1.175494351e – 38
double[(m, d)]	8字节	最小非零值：±2.2250738585072014e – 308
decimal (m, d)	m字节（mysql < 3.23）， m+2字节（mysql > 3.23 ）	可变；其值的范围依赖于m 和d

 

6.2 字符串类型

数据类型	说明
CHAR	1~255个字符的定长串，它的长度必须在创建时指定，否则MySQL假定为CHAR（1）
VARCHAR	可变长度，最多不超过255字节，如在创建时指定VARCHAR（n），则可存储0~n个字符的变长串
TINYTEXT	同TEXT，最大长度为255字节
MEDUIMTEXT	同TEXT，最大长度为16K
TEXT	最大长度为64K的变长文本
LONGTEXT	同Text，最大长度为4GB（纯文本，一般不会到4G）
ENUM	接受最多64K个串组成的预定义集合的某个串
SET	接受最多64K个串组成的预定义集合的零个或多个串

 

6.3 日期类型

数据类型	存储字节数	取值范围
DATE	4	1000-01-01——9999-12-31
TIME	3	-838:59:59——838:59:59
DATETIME	8	1000-01-01 00:00:00——9999-12-31 23:59:59
TIMESTAMP	4	19700101080001——20380119111407
YEAR	1	1901——2155

7.创建表

以上我们学习了MySQL中国常用的数据类型，接下来学习如何使用DDL创建数据库表。

创建表语法如下：

CREATE TABLE [ IF NOT EXISTS ]  表名 （
　　　　　　　　字段 1  数据类型  【字段属性|约束】 【索引】【注释】，
　　　　　　　　字段 2  数据类型  【字段属性|约束】 【索引】【注释】，
 　　　　　　　 ......
　　　　　　　　字段 n  数据类型  【字段属性|约束】 【索引】【注释】，
）;

注意：

　　　　MySQL的注释方式

　　　　单行注释：#.....

　　　　多行注释：/*......*/

7.1常用的属性约束

非空约束	NOT NULL	如该字段不允许为空，需要设置NOT NULL约束。如学生的姓名。。
默认约束	DEFAULT	赋予某字段默认值，如果该字段没有插入数据，则其值为默认值，如果学生表中男生居多，可以设置性别列为默认值'男'
唯一约束	UNIQUE  KEY(UK)	设置字段的值是唯一的。允许为空，单只能有一个控值
主键约束	PRIMARY  KEY(PK)	设置改字段为表的主键，可以作为改表记录的唯一标识，如学号为唯一确定学生，可以设置为主键
外建约束	FOREIGN KEY(FK)	用于在两表之间建立关系，需要指定引用主表的那一个字段。在发生插入或更新表中数据时，数据库将自动检查更新的字段的值是否符合约束的限制，如不符合约束的要求，则更新失败。使用式注意；(1) InnoDB支持外键，MySAM不支持外键，外键关联表要求都是InnoDB类型的表（2）作为外键的字段要求在主表中式主键（单字段主键）
自动增长	AUTO_INCREMENT	(1)设置该列为自增字段，默认每条自增1；（2）通常用与设置主键，且为整数类型。（3）可设置初始值和步长。

单字段主键在定义字段的同时指定主键，语法如下。

CREAT TABLE [IF NOT EXISTS] 表名 （
字段 1 数据类型 PRIMARY KEY,
....
）;

在定义完所用字段指定主键，语法如下。

多字段联合主键

主键有多字段组成，语法如下。

CREATE TABLE [IF NOT EXISTS] 表名 （
PRIMARY KEY [字段1，字段2。。。]
）;

注释

在创建表的同时可以为表或字段添加说明，机注释。注释式使用COMMENT关键字来添加的。例如：

CREATE TABLE test （
'id' int(11) UNSIGNED COMMENT '编号'
）COMMENT='测试表';

7.2编码设置

在默认情况下，MySQL 所有的数据库、表、字段等使用MySQL默认字符集式utf8，也可以在my。ini文件中的default-character-set参数来修改默认字符集。

当在特定需求下，为达到特殊存储内容要求，如某表需要存储西欧文字内容，可以指定其字符集。可以在创建表时指定字符集，语法如下：

CREATE TABLE [IF NOT EXISTS] 表单（
#省略代码）CHARASET=字符集名；

下面利用CREATE TABLE语句在数据库myschool中创建学生表student，具体设计如下：

序号	字段名	字段说明	数据类型	长度	属性	备注
1	studentNo	学号	INT	4	非空，主键
2	loginPwd	密码	VARCHAR	20	非空
3	studentName	姓名	VARCHAR	50	非空约束
4	sex	性别	CHARASET	2	非空，默认值‘男’
5	gradeId	年级编号	INT	4	无符号
6	phone	联系电话	VARCHAR	50
7	address	地址	VARCHAR	255	默认值“地址不详”
8	bomData	出生年月	DATETIME
9	email	邮件帐号	VARCHAR	50
10	identityCard	身份证	VARCHAR	18	唯一	身份证好全国唯一

代码请参见示例

CREATE TABLE 'student'(
'studentNo' INT(4) NOT NULL COMMENT'学号' #非空主键
'loginPwd' VARCHAR(20) NOT NULL COMMENT'密码'
'studentName' VARCHAR(50) NOT NULL COMMENT'学生姓名'
'sex' CHARASET(2) NOT NULL COMMENT'性别' #非空，默认性别‘男’
'gradeId' INT(4) UNSIGNED COMMENT'年级编号' #无符号
'phone' VARCHAR(50) COMMENT'练习电话' 
'address' VARCHAR(255) DEFAULT'地址不详' COMMENT'地址' #默认值
'bomData' DATETIME COMMENT'出生年月' 
'email' VARCHAR(50) COMMENT'邮件帐号'
'identityCard' VARCHAR(18) UNIQUE DEY COMMENT'身份证号' #唯一
)COMMENT='学生表'; #表注释"学生表"

注意：创建的字段名是不需要加引号的，在插入数据数据值时都要加引号（例如INSERT等），上面的表达式为参考示例，下面的才是真实场景的运用。

MariaDB [msschool]> CREATE TABLE test1(
    -> loginPwd VARCHAR(20) NOT NULL COMMENT'密码',
    -> studentName VARCHAR(50) NOT NULL COMMENT'学生姓名',
    -> sex CHAR(2) DEFAULT '男' NOT NULL COMMENT'性别',
    -> gradeId INT(4) UNSIGNED COMMENT'年级编号',
    -> phone VARCHAR(50) COMMENT'练习电话',
    -> address VARCHAR(255) DEFAULT'地址不详' COMMENT'地址',
    -> bomData DATETIME COMMENT'出生年月',
    -> email VARCHAR(50) COMMENT'邮件帐号',
    -> identityCard VARCHAR(18) UNIQUE KEY COMMENT'身份证号'
    -> );
Query OK, 0 rows affected (0.04 sec)

 

7.3查看表

创建完表之后，如果需要查看一下表式否存在，可以使用查看表的SQL命令，其语法如下：

SHOW TABLES;

如果要查看那表的定义，可以通过SQL语句DESCRIBE来实现，其语法如下。

DESCRIBE 表名；
或
DES 表明；

查看备注信息

语法：

　　show create table test;

示例：

MariaDB [msschool]> show create table test;
+-------+------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`loginPwd` varchar(20) NOT NULL COMMENT '密码'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [msschool]>

移动数据库中表的操作

语法：

　　rename 数据库名.表名 to 数据库名.表名;

　　例如将old数据库中的表移动到new数据库中命令就是：

 　　rename table old.book to new.book;

 真实示例：

将new_sakila数据库中的book表放到msschool数据库中。

MariaDB [new_sakila]> show tables;
+----------------------+
| Tables_in_new_sakila |
+----------------------+
| book                 |
| student              |
| test                 |
| test1                |
+----------------------+
4 rows in set (0.00 sec)

MariaDB [new_sakila]> show databases;
+--------------------+
| Database           |
+--------------------+
| class              |
| information_schema |
| msschool           |
| mysql              |
| new_sakila         |
| performance_schema |
+--------------------+
6 rows in set (0.00 sec)

MariaDB [new_sakila]> use msschool;
Database changed
MariaDB [msschool]> show tables;
Empty set (0.00 sec)

MariaDB [msschool]> rename table new_sakila.book to msschool.book;
Query OK, 0 rows affected (0.01 sec)

MariaDB [msschool]> show tables;
+--------------------+
| Tables_in_msschool |
+--------------------+
| book               |
+--------------------+
1 row in set (0.00 sec)

MariaDB [msschool]> select * from book;
Empty set (0.00 sec)

MariaDB [msschool]> use new_sakila;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [new_sakila]> show tables;
+----------------------+
| Tables_in_new_sakila |
+----------------------+
| student              |
| test                 |
| test1                |
+----------------------+
3 rows in set (0.00 sec)

MySQL数据库改名的三种方法

前不久去面试，被问到Innodb引擎的表如何改数据库名，当时我也只回答了MyISAM改如何操作，被一些细节问题打败，真是操蛋。

如果表示MyISAM那么可以直接去到数据库目录mv就可以。

Innodb完全不行，自己测试过，会提示相关表不存在。

第一种方法：

RENAME database olddbname TO newdbname
这个是5.1.7到5.1.23版本可以用的，但是官方不推荐，会有丢失数据的危险

第二种方法：
1.创建需要改成新名的数据库。
2.mysqldum 导出要改名的数据库
3.删除原来的旧库（确定是否真的需要）

当然这种方法虽然安全，但是如果数据量大，会比较耗时，哎，当时连这种方法都没有想到，真有想死的冲动。

第三种方法：

我这里就用一个脚本，很简单，相信大家都看的懂

#!/bin/bash
# 假设将sakila数据库名改为new_sakila
# MyISAM直接更改数据库目录下的文件即可

mysql -uroot -p123456 -e 'create database if not exists new_sakila'
list_table=$(mysql -uroot -p123456 -Nse "select table_name from information_schema.TABLES where TABLE_SCHEMA='sakila'")

for table in $list_table
do
    mysql -uroot -p123456 -e "rename table sakila.$table to new_sakila.$table"
done

7.4删除表

和创建数据库一样，如果当前数据库中已存在student表，则再次创建式系统将会提示出错。

我们需要预先检测当前数据库中是否存在该表，如果存在，则先删除，然后创建，需要使用IF EXISTS语句进行判断。删除表的语法如下。

DROP TABLE [IF EXISTS] 表名；

7.5修改表

在创建了数据表之后，有时候可能会在某原因需要修改表结构，如添加列等。这时，如果将表删除后重建，往往还需要考虑表总现有的数据，风险比较大，此时需要在已存在的数据结构上对其进行修改，MySQL使用ALTER关键字来实现，注意在修改表之前，式用SHOW TABLES语句查看该数据库中是否存在该表。

修改表名

ALTER TABLE <旧表名> RENAME [TO] <新表名>；

其中，TO是可选参数，使用与否不影响结果。仅仅修改表名，表结构不变。

添加字段

ALTER TABLE 表名 ADD 字段名 数据类型 [属性]；

例如：

ALTER TABLE demon02 ADD 'password' VARCHAR(32) NOT NULL;

添加字段

ALTER TABLE 表名 CHANGE 原字段名 新字段名 数据类型 [属性];

例如：

ALTER TABLE demon02 CHANGE 'username' CHAR(10) NOT NULL;

删除字段

删除字段式将数据从表中的某个字段中移除

ALTER TABLE 表名 DROP 字段名；

例如；删除demon02 表中password 字段，SQL语句如下。

#删除字段

ALTER TABLE demon02 DROP 'password';

 

添加主键约束

语法：
ALTER TABLE 表名 ADD CONSTRAINT 主键名 PRIMARY KEY 表名（主键字段）；
例如，将grade表中的gradeld设置主键，语句如下。
ALTER TABLE 'grade' ADD CONSTRAINT 'pk_grade' PRIMARY KEY 'grade'('gradeId');

添加外键约束

语法：
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREING KEY(外键字段) REFERENCES 关联表名（关联字段）;
例如，设置student表的gradld字段与grade表中的gradeld字段建立主外键关联，语句如下。
ALTER TABLE 'student' ADD CONSTRAINT fk_student_grade FOREIGN KEY('gradeId') PEFERENCES 'grade' ('gradeId')；

8.MySQL系统帮助

HELP 查询内容；

其中，查询内容为要查询的关键字。

(1)查看帮助文档目录列表

HELP contents;

(2)查看具体内容

HELP Data Types;

若要具体查看某一数据类型，如INT类型，命令如下；

HELP INT;

9.常用的MySQL数据存储引擎

InnoDB： 支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比        如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作          的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。

MyISAM： 插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择                    MyISAM 能实现处理高效率。如果应用的完整性、并发性要求比 较低，也可以使用。 

功能	InnoDB	MyISAM
支持事物	支持	不支持
支持索引	不支持	支持
外键约束	支持	不支持
表空间大小	较大	较小
数据行锁定	支持	不支持

操作默认存储引擎

安装MySQL 5.5 以上版本的默认存储引擎的是InnoDB,可以通过以下语句来查看当前的默认存储引擎。

查看数据库的默认存储引擎：

SHOW VARIABLES LIKE 'storage_engine%';

可以通过修改默认存储引擎，可以通过配置向导，也可以通过修改配置文件my.ini来实现。修改配置文件my.ini        时， 修改如下内容：defaultstoroge-engine=InnoDB

 

指定表的存储引擎

数据表默认使用当前MySQL默认的存储引擎，有时为了达到数据表的特殊功能要求，也可重新设置表的存储类    型，语法如下：

CREATE TABLE 表名 (

省略代码

)ENGINE=存储引擎；

例如：创建myisam表并设置为MyISAM 类型，SQL语句如下。

CREATE TABLE 'myisam' (

id INT(4)

)ENGINE=MyISAM;

 

10.使用DML插入数据 

10.1使用INSERT插入数据

使用数据库的之前，先要为数据表添加数据，语法如下。

插入单行数据

语法：

INSERT INTO 表名 [字段名列表] VALUES(列表)；

其中：

表的字段名是可选的，如果省略，则依次插入所有字段。

多个列表和多个值之间使用逗号分隔

值列表必须和字段名列表数量相同，且数据类型相符

如果插入的式表中部分数据，字段名列表必须填写。

例如，向student表中插入一条记录：

INSTER INTO 'STUDENT'('loginPwd','studentName','gradeId','phone','bornDate') VALUES('123','黄小平','1','13956799999','1996-5-8')

10.2.插入多行数据

在MySQL中INSTER语句支持一次插入多行记录，插入时间可以指定多个列表，每个值列表之间用逗号分隔。

语法

INSTER INTO 新表（字段名列表） VALUE(值列表1),(列表2),....,(值列表n)；

例如，一次向subject表中插入三条数据，SQL语句如下：

INSTER INTO 'subject'('subjectName','classHour','gradeID')VALUES('logic Java',220,1),('HTML',160,1),('Java OOP',230,2);

10.3.将查询的结果插入新表

语法

CREATE TABLE 新表 (SELECT 字段1，字段2，..... FROM 原表)；
例如，将student表中的studentName，phone字段数据保存到新表phoneList中，SQL语法如下。
CREATE TABLE 'phoneList'(SELECT 'studentNmae','phone', FROM 'student');

10.4.使用DML更新数据

数据更新式经常发生的事情，式用SQL可以进行数据更新。使用SQL更新表中的某行的语法格式如下。

语法

UPDATE 表名 SET 列名 = 更新值 [WHERE 更新条件]；

其中：

1.SET后面可以紧随多个"列明=更新值"，修改多个数据列的值，不限一个，使用逗号分隔。

2.WHERE字句是可选的，用来限制更新数据的条件。若不限制，则整个表的所有数据行将备更新。

需要注意的式，使用UPDATE语句，可能更新一行数据，也可能更新多行数据，还可能不会更新任何数据。

例如，在学生信息表中，要吧所有学生的性别改为女性

UPDATE student SET sex = '女'；

对地址为'北京女子职业技术学校刺绣班'的学生，若这个班级改为家政班，则需要按照条件进行更新。

UPDATE sutdent
SET address ='北京职业技术学习家政班'
WHERE address ='北京女子职业技术学校刺绣班';

前面已经提到，在SQL表达式中，可以使用列名和数值。如果写生在考试时，有一道题目的目标准确答案错了，      导致评分失误，事后需要在成绩表中更新成绩，所有低于或等于95分的成绩都在原来的基础上加5分，更新的          SQL语句如下。

UPDATE result
SET studentResult = studentResult + 5 
WHERE studentResult <= 95;

10.5.使用DML删除数据

删除数据行也是经常会用到的操作，使用SQL语句来操作相对比较简单。

使用DELETE删除数据

使用SQL删除表中的数据，语法格式如下。

语法

DELETE [FROM] 表名 [WHERE <删除条件>];
在的学生信息表中删除姓名"王宝宝"的数据的SQL语句如下。

DELETE FROM student WHERE sutdentNmae = '王宝宝'；

11.使用TRUNCATE TABLE删除数据

TRUNCATE TABLE用来删除表中的所有行，功能上它类似于没有WHERE 子句DELETE语句。

例如，要删除学生信息表中的所用记录行，可以使用一下语句。

TRUNCATE TABLE student;

但TRUCATE TABLE 比DELECT执行速度快，使用的系统资源和事务资源更少，并且删除数据后表的标识会重新开始编号。

12.使用DQL语句

使用SELECT语句进行查询

语法：

SELECT <列名|表达式|函数|常量>
FROM<表名> 
[WHERE <查询条件表达式>]
[ORDER BY <排序的列名>[ASC或DESC]];

其中，WHERE条件是可选的，若不限制，则查询返回所用行的数据项。ORDER BY 是用来排序的，后续内容将        会详细介绍。

12.1.查询所用的数据行和列

把表中的所有行和列都列举出来比较简单，这个时候可以使用"*",表示所用的列，例如：

SELECT * FROM student；

12.2.查询部分行和列

查询部分列需要列举不同的列明，二查询部分行需要使用WHERE子句进行条件限制，例如：

SELECT studentNo，sutentName，address FROM WHERE address = '河南新乡'；

以上的查询语句，将只查询地址为河南新乡的学生，并且显示编号、姓名和地址列。同理，以下语句用来查询地址不 是"河南新乡"的学生信息。

SELECT studentNo，sutdentName，address FROM WHERE address <> '河南新乡';

12.3.查询中使用列的别名 

AS子句可以用来改变结果集中列的名称，也可以为组合或计算出列指定名称，还有一种情况式让标题列的信息更易懂，例如，把studentNo列名查询后显示为“学生编号”。

在SQL中重新命名列名可以使用AS子句，例如

SELECT studentNo AS 学生编号,studentName AS 学生姓名,address AS 学生地址 FROM student WHERE            address <> '河南新乡'

还有一种情况是使用计算，和并得到新列的命名。例如，假设在某数据库的雇员表employee中存在firstName      列，现在需要将这两项合并成一个叫作'姓名'的列，可以使用以下查询语句。

SELECT firstName+'.'+lastName AS 姓名 FROM employee;

12.4.查询空值

在SQL语句中采用"IS NULL" 或者"IS NOT NULL" 来判断是否为空。因此，如果要查询学生信息列表中没有填写      email信息的学生，可以使用以下查询。

SELECT studentName FROM student WHERE email IS NULL;

12.5.查询中使用常量列

有时候，需要将一下常量的默认信息添加到查询输出中，以方便统计或计算。例如，查询学生信息的时候，学        校    名称统一都是"北京新兴桥" ，查询语句如下。

SELECT studentName AS 姓名，address AS 地址，'北京新兴桥' AS 学校名称 FROM student；

查询输出多一列“学校名称”，改列的所用数据都是“北京新兴桥”。

13.常用函数

SQL语言中的函数将一些常用的处理数据的操作封装起来，这样，就大大简化了程序员的工作，提高了开发效      率，因此，除了会使用SQL语句之外，还需要掌握一些常用函数。以下分类列出了MySQL中常用函数。

13.1聚合函数

MySQL中的聚合函数使来已经有数据进行汇总，如求和、平均数、最大值、最小值等。MySQL中常用函数如：

函数名 作用

AVG()	返回某字段的平均值
COUNT()	返回某字段的行数
MAX()	返回字段的最大值
MIN()	返回某字段的最小值
SUM()	返回某字段的和

很多需求中我们需要计算学员的总成绩，在这里就可以使用SUM()函数。SQL语句如下

SELECT SUM (studentResult) FROM result；

同上，如果我们要计算学员的平局成绩，在这里我们就可以使用AVG()函数。SQL语句如下

SELECT AVG (studentResult) FROM result；

13.2.字符串函数

字符串函数式常用函数之一，用来对字符串进行各类处理，MySQL中使用频率较高的字符串函数表

函数名	作用	举例
CONCAT(str1,strq,...,strn)	连接字符串str1，str2....strn为一个完整字符串	SELECT	CONCAT('My','S','QL');返回：MySQL
INSERT	将字符串str从pos位置开始，len个字符长的子串替换为字符串newstr	SELECT	INSERT('这是Oracle数据库'3,6'MySQL');返回：这式MySQL数据库
LOWER	将字符串str中所用字符变为小写	SELECT	LOWER('MySQL');返回：mysql
UPPPER	将字符串str中所用字符变为大写	SELECT	UPPER('MysSQL');返回：mysql
SUBSTRING	返回字符串str的第num个位置开始长度为len的字符串	SELECT	SUBSTRING('JavaMySQLOracle');返回：mysql

 

如果需求中规定我们要对学员的姓名和首写字母进行大写。采用UPPER(str)函数的SQL语句如下

SELECT UPPER (studentName) FROM student；

13.3.时间日期函数

除了聚合函数和日期函数之外，日期函数也是一类常用函数。

 

函数名	作用	举例
CURDATE()	获取当前日期	SELECT	CURDATE();	返回2016-08-08
CURTIME()	获取当前时间	SELECT	CURTIME();	返回19:19:26
NOW()	获取当前日期和时间	SELECT	NOW();返回2016-08-08	19:19:26
WEEK(date)	返回日期date为一年中的第几周	SELECT	WEEK(NOW());	返回：26
YEAR(date)	返回日期date的年份	SELECT	YEAR(NOW());返回：2016
HOUR(time)	返回时间time的小时值	SELECT	HOUR(NOW());返回：9
MINUTE(time)	返回时间time的分钟值	SELECT	MINUTE(NOW());返回：43
DATEDIFF(date1,date2)	返回日期参数date1和date2之间相隔的天数	SELECT	DATEDIFF(NOW(),'2008-8-8')；返回：2881
ADDATE(date,n)	计算日期参数date加上n天后的日期	SELECT	ADDDATE(NOW(),5);返回：2016-09-02	9:37:07

如果需求中规定我们对学院的出生年份进行统计，采用YEAR(date)函数的SQL语句如下。

SELECT YEAR(bornDate) from student；

13.4.数学函数

在使用SQL语句进行数据操作时，有时会需要进行数值运算，MySQL中支持的常用数学函数

函数名	作用	举例
CEIL(x)	返回大于等于数据x的最小整数	SELECT	CEIL(2.3);返回：3
FLOOR(x)	返回小于等于数据x的最大整数	select	FLLOR(2.3);返回：2
RAND(x)	返回0~1间的随机数	SELECT	RAND();返回：0.5525468583708134

如果需求中规定我们对学员的分数继续取整，采用CEIL(x)函数的SQL语句如下。

SELECT CEIL (studentResult) from result；

13.5.ORDER BY 字句

如果需要按照一定顺序排查语句选中的行，则需要是使用ORDER BY 子句。并且排序可以是升序(ASC)或者降序(DESC).结果按默认升序排序。

上述讲过的SQL语句，都可以在其后面加上ORDER BY 来进行排序。

例如，查询学生成绩的时候，如果把所用成绩都降低10%或加5分，再查询及格并按照成绩高低来进行排列.SQL语句如下语句如下。

SELECT studentID AS 学生编号,(student*0.9+5) AS 综合成绩
FORM RESULT WHERE (studentResult*0.9+5) > 60
ORDER BY studentResult;

还可以按照多个列进行排序。例如，要在学生成绩排序的基础上，在按照课程ID进行排序的语句如下。

SELECT studentID AS 学生编号， courseID AS 课程ID , studentREsult AS 成绩
FROM result
WHERE studentResult>60
ORDER BY studentResult,courseID;

13.6.LIMIT 子句

以上操作中实现了基础的对数据库的查询操作，但是展示的式一个数据库中的全部数据。但实际开发中，可能只是要求显示指定行数的记录。接下来将介绍如何通过DQL语句限制查出的数据的数目。

语法 :

SELECT <字段名列表>
FROM <表名或视图>
[where <查询条件>]
[GROUP BY<分组的字段名>]
[ORDER BY <排序的列名>[ASC或DESC]]
[LIMIT [位置偏移量，]行数];

在上述语法中在LIMIT部分介绍如下：

位置偏移量指定从结果集中第几条数据开始显示（第1条记录的位置偏移量是0，第二条记录的位置偏移量是1...）,此参数可选，当省略式从第1条记录开始显示。

行数指显示记录的条数。

LIMIT子句可以实现数据的分页查询，即从一批结果数据中，规定每页显示多少条数据，可以查询中间某页记录。LIMIT子句经常与ORDER BY 子句一起使用，即先查询结果进行排序，然后根据LIMIT的参数那个显示其中部分数据。例如，查询所用年级编号为1的学员信息，按学好升序显示前4条记录。SQL语句如下

SELECT 'studentNo','studentName','phone','address','bornDate'
FROM 'student'
WHERE 'gradeId'=1
ORDER BY studentNo
LIMIT 4;

以上示例省略位置偏移量，从第1条记录开始显示，如果每页显示4条数据，要求显示第2页全部数据，经过计算，应从第5条记录开始显示4条数据。则SQL语句如下 

SELECT 'studentNo','studentName','phone','address','bornDate'
FROM 'student'
WHERE 'gradeId' = 1 
ORDER BY studentNo
LIMIT 4,4;

14.子查询

简单子查询

#查找出"李斯文"的出生日期

SELECT 'bornDate' FROM 'student' WHERE 'sutdentname' = '李斯文';

返回结果是：'1993-07-23'

#利用WHERE语句筛选出出生日期比"李斯文"大的学生

SELECT studentNo, studentName,sex,bornDate,address FROM 'student' WHERE bornDate > '1993-07-23';

我们可以看到执行的语句比较复杂，有没有更简洁的表示方式呢？当然是有的

SELECT studentNo, studentName,sex,bornDate,address FROM 'student' WHERE bornDate > (SELECT 'bornDate' FROM 'student' WHERE 'sutdentname' = '李斯文');

语法：

SELECT ..... FROM 表1 WHERE 字段1 比较预算符 （子查询）

其中，子查询语句必须放在一对圆括号内，比较运算符包括：>,=,<,>=,<=。

习惯上外表的查询称为父查询，圆括号中嵌入的查询语句称为子查询。执行时，先执行查询部分，求出子查询部分的值，在执行整个父查询，返回最后的结果。

因为子查询作为WHERE条件的一部分，所以可以和UPDATA,INSTER,DELETE一起使用，语法类似与SELECT语句。

注意：

将子查询和比较运算符联合使用，必须保证子查询返回的值不能多于一个。

15.IN和NOT IN 子查询

使用IN关键子可以使父查询匹配子查询返回的多少单字段

IN子查询

使用=,>等比较运算符时，要求子查询只能返回一条或空的记录。在MySQL中，当子查询跟随在=,!=,<,<=,>和>=之后时，不允许子查询返回多条记录。

语法格式如下：

　　SELECT * FROM A WHERE COLUMN IN (SELECT COLUMN FROM B);

　　需要说明的是，WHERE中，COLUMN为A的某一列，IN 所对应的子查询语句返回为一列多行结果集

注意，IN所对应的SELECT语句返回的结果一定是一列！可以为多行。

示例如下：

SELECT * FROM P_USER_2 WHERE ID [NOT] IN (SELECT ID FROM P_USER )
查询ID在P_USER表ID集合的P_USER_2的记录。NOT IN则相反。

16.EXIST和NOT EXIST 子查询

EXIST 关键字我们并不陌生，在学习在学习长库和创表的时候用过，用它来检查数据库是否存在。下面来分别介绍EXIST 和NOT EXIST查询

16.1.EXIST子查询

在执行CREAT 和DROP语句前，可以使用EXIST语句判断该数据对象是否存在，返回值式true或false。例如，如果存在数据表temp，则先删除它。然后重新创建。

DROP TABLE IF EXISTS temp;

以上用法外，EXIST也可以存在WHERE语句的子查询，其基本语法如下。

语法：

SELECT ......  FROM 表名 WHERE EXISTS(子查询);

EXISTS后面的参数是一个任意的子查询，如果改子查询有返回行，则exist子查询的结果为true，此时在执行外层查询语句。如果子查询没有返回行，则EXIST子查询的结果为false，此时外层语句不在执行查询。

例句：

SELECT 'student' AS 学号, 'studentResult' 成绩 FROM 'result' 
  WHERE EXIST (
  #查询Llogic Java 最后一次考试成绩大于80的记录
    SELECT * FROM 'reslut'  
    WHERE 'subjectNO' = (SELECT 'subjectNo' FROM 'subject'  WHERE 'subjectName' = 'Logic Java') 
    AND 'examDAte' = (
      SELECT MAX('examDAte' ) FROM 'result' 
      WHERE 'subjectNo' =(SELECT 'subjectNo' FROM 'subject' WHERE 'subjectName' = 'Logic Java') )
    AND 'studentResult' > 80
    )
  AND 'subjectNo' = ( SELECT 'subjectNo' FROM 'subject' WHERE 'subjectName' = 'Logic Java')
  ORDERY BY 'studentResult' DESC  LIMIT 5;

16.2.子查询注意事项

在完成比较复杂的数据查询时，经常会用到子查询。编写子查询语句时，要注意如下事项。

1）子查询语句可以镶嵌在SQL语句中任何表达式出现的位置

在SELECT语句中，子查询可以镶嵌在SELECT语句的列、表和查询条件中，机SELECT子句、FROM子句、WHERE子句、GROUP BY 子句和HAVNING子句。已经介绍了WHERE子句中嵌套子查询的使用方法，下面是子查询在SELECT子句和FROM子句中的使用语法。

嵌套在SELECT语句中的SELECT子句中的子查询语句如下。

语法：

SELECT (子查询) FROM  表名;

 

嵌套在SELECT语句的FROM子句中的子查询语句如下。

语法：

SELECT * FORM (子查询) AS 表的别名;

 

2)只出现在子查询中没有出现在父查询中的表不能包含输出列中

多层嵌套查询的最终数据集只包含父查询（即最外层的查询）的SELECT 子句中出现的字段，而子查询的输出结果通常作为其外层子查询的数据源或用于数据判断匹配。

常见错误

 SELECT * FROM (SELECT * FROM result);

这个子查询语句产生语法错误的原因在于主查询语句的FROM子句是一个查询语句，因此一个该为子查询结果集指定别名。正确的代码如下：

SELECT * FROM (SELECT * FROM result) as Temp;

 

17.分组查询

17.1.使用GROUP BY 进行分组查询

看一下学生成绩表中，表中存储了学生参加考试的成绩。有时，可能需要统计不通科目的平均成绩，也就是说，首先需要多成绩表中的记录按照课来分组，然后针对每一个组来进行平均成绩计算。

这种情况很普遍，例如，一个电器销售店，销售洗衣机，冰箱，电视，月末时，就需要分类统计洗衣机销售总数、冰箱销售总数、电视销售总数。这个时候就需要首先分类，将冰箱、洗衣机、电视分为三组，然后在每组的基础上分别进行汇总和统计。这实际上也就是分组查询的原理。返组后的统计计算利用前面学习过的汇总和统计。这实际上也是分组查询的原理。分组后的统计计算要利用起前面学过的集合函数，如SUM(),AVG()等。

假设有个成绩表，该成绩表记录了三门课程的学生成绩、课程编号（subject）分别是1、2、3。此时，要统计不同课程的平均分数。首先把相同的subjectNo都分为一组，这样就将数据评分成三组，然后针对每一组使用前面的聚合函数取平均值，这样就得到没门课程的怕你平均分数。

语句如下：

SELECT subjectNo， AVG(studentResult) AS 课程平均成绩

FROM result

GROUP BY subjectNo;

多列分组查询

分组查询有时候可能还要按照多个列来进行分组。例如，学生信息表的student中记录了每个学生的信息，包括所述年级和性别等，

如果要统计每个学期男，女生人数，则理论生先把每个学期分开，在针对每个学期，把男，女生人数各自统计，也就是需要按照连个列进行分组：所属年级和性别。SQL语句如下。

SELECT COUNT(*) AS 人数,grade  AS 年级,sex AS 性别 FROM studnet  

GROUP BY grade,sex

ORDER BY grade;

17.2.使用HAVING子句进行分组筛选

通过前面的学习，我们已经基本了解分组查询的意义和原理。当我们需要查询总人数大于1的年级时，我们的所学到的 SQL是不能满足需求的，此时我们就需要用到HAVING。

我们的限定条件为COUNT(*)>2。这个时候使用WHERE子句式否能满足查询要求，因此WHERE子句只能没有分组统计前的数据进行筛选。对分组后的条件进行筛选必须使用HAVING子句。简单来说，HAVING子句用来对分组后的数据进行筛选，将组看作列来限定条件。

SQL语句如下：

SELECT COUNT(*) AS 人数,gradeId AS 年级  FROM student*

GROUP BY graeId

HAVING COUNT(*)>2;

17.3.查询平均成绩达到及格的课程信息

在查询每个科目平均分的基础上，增加了一个条件；平均分及格的科目。这样按照科目进行分组后，使用AVG(studet;)>=60控制条件即可。

SQ语句如下：

SELECT subjectNo AS 课程编号,AVG(studentResult) AS 平均成绩

FROM result  

GROUP BY subjectNo

HAVING AVG(studentResult)>=60;

HAVING 和WHERE 子句可以在同一个SELECT语句中一起是使用，使用顺序如下:

WHERE ----> GROUP BY ----> HAVING  

提示：

在SELECT语句中，WHERE,GROUP BY ,HAVING子句和聚合函数的执行次序如下：WHERE 子句从数据源中去掉不符合其搜索条件的数据；GROUP BY 子句收集数据到各组中，统计函数为各组计算统计值；

HAVING 子句去掉不符号其组搜索条件的各组数据。

查询没门课程及格总人数和几个平局分分在80分以上的记录

SELECT COUNT(*) AS 人数，AVG(studentResult) AS 平均分,subjectNo AS 课程

FROM result

WHERE studentResult>=60

GROUP BY subjectNo

HAVING AVG(studentResult)>=80;

  

18.多表连接查询

多表连接查询实际上是通过各个表之间共同列的关联性来查询数据的，它是关系数据库查询最主要的特征

1.内链接查询

内链接查询式最典型，最常用的连接查询，它根据表中共同的列列来进行匹配。特别是两个表存在主外键关系时通常会使用内连接查询。

2.外连接查询

外连接查询式至少返回一个表中的所有记录，根据匹配条件有选择性的返回另一张表的记录。外连接可以是左外连接，右外连接。

 

18.1内链接查询

内链接查询通常会使用'+'或'<>'等比较运算符来判断两个列数据值是否相等，上面所说的根据学生学号来判断学生姓名的连接式一种内链接。

内链接有INNER JOIN... ON 关键子或WHERE子句来进行表之间的关联。内连接查询可以通过两种方式实现。

在WHERE子句中指定连接条件

例如，查询学生姓名和成绩的SQL如下。

SELECT student.studentName,result.subjectNo,result.studentResult

FROM student,result

WHERE STUDENT.studentNo = result.studentNo;

上面这种形式的查询，相当与FROM后面紧跟两个表名，在字段表中使用"表名.列表"来区分列，再在WHERE条件子句加以判断，要求学生编号信息相等。

在FROM子句中使用INNER JOIN...ON  

上面的查询也可以通过的JOIN...ON子句来实现

SELECT S.studentName,R.subjectNo,studentResutl

FROM student AS  S  

INNER JOIN  result AS R ON (S.studentNo = R.studentNo);

 

18.2内链接查询详解

用两个表（a_table、b_table），关联字段a_table.a_id和b_table.b_id来演示一下MySQL的内连接、外连接（ 左(外)连接、右(外)连接、全(外)连接）。

MySQL版本：Server version: 5.6.31 MySQL Community Server (GPL)

数据库表：a_table、b_table

主题：内连接、左连接（左外连接）、右连接（右外连接）、全连接（全外连接）

前提
建表语句：
CREATE TABLE `a_table` (
`a_id` int(11) DEFAULT NULL,
`a_name` varchar(10) DEFAULT NULL,
`a_part` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `b_table` (
`b_id` int(11) DEFAULT NULL,
`b_name` varchar(10) DEFAULT NULL,
`b_part` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

表测试数据：

 

一、内连接
关键字：inner join on
语句：select * from a_table a inner join b_table bon a.a_id = b.b_id;
执行结果：

 

说明：组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分。

 

二、左连接（左外连接）
关键字：left join on / left outer join on
语句：select * from a_table a left join b_table bon a.a_id = b.b_id;
执行结果：

 

说明：
left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。
左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。

 

三、右连接（右外连接）
关键字：right join on / right outer join on
语句：select * from a_table a right outer join b_table b on a.a_id = b.b_id;
执行结果：

 

说明：
right join是right outer join的简写，它的全称是右外连接，是外连接中的一种。
与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。

 

(1)登录：mysql -u root -p  

(2)查看现有用户(mysql8.0.1)

mysql> select host,user,authentication_string from mysql.user;
 +-----------+------------------+----------------------------------------------------------------+
 | host | user | authentication_string |
 +-----------+------------------+----------------------------------------------------------------+
 | localhost | mysql.infoschema | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
 | localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
 | localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
 | localhost | root | $A$005$e!42 )Tf+4M{4W>MkFY9ktIVPhgVemeQsSQnuiGLRiH/909Zyaj9XF3/3Yk2 |
 +-----------+------------------+----------------------------------------------------------------+

(3)新建用户

格式:create user "username"@"host" identified by "password";

eg：

1.mysql->create user 'test'@'localhost' identified by '123';
2.mysql->create user 'test'@'192.168.7.22' identified by '123';

3.mysql->create user 'test'@'%' identified by '123';

    /*host="localhost"为本地登录用户，host="ip"为ip地址登录，host="%"，为外网ip登录*/

(4)删除用户

　格式：drop user 'username'@'host';

(5)授权

　格式：grant privileges on databasename.tablename to 'username'@'host' IDENTIFIED BY 'PASSWORD';

　  1. GRANT命令说明：
   　　 　　priveleges(权限列表),可以是all priveleges, 表示所有权限，也可以是select、update等权限，多个权限的名词,相互之间用逗号分开。

   　　 　　on用来指定权限针对哪些库和表。

   　　　　 *.* 中前面的*号用来指定数据库名，后面的*号用来指定表名。

    　　　　to 表示将权限赋予某个用户, 如 jack@'localhost' 表示jack用户，@后面接限制的主机，可以是IP、IP段、域名以及%，%表示任何地方。注意：这里%有的版本不包括本地，以前碰到过给某个用户设置了%允许任何地方登录，但是                  在本地登录不了，这个和版本有关系，遇到这个问题再加一个localhost的用户就可以了。

   　　       identified by指定用户的登录密码,该项可以省略。

    　　       WITH GRANT OPTION 这个选项表示该用户可以将自己拥有的权限授权给别人。注意：经常有人在创建操作用户的时候不指定WITH GRANT OPTION选项导致后来该用户不能使用GRANT命令创建用户或者给其它用户授权。

                 备注：可以使用GRANT重复给用户添加权限，权限叠加，比如你先给用户添加一个select权限，然后又给用户添加一个insert权限，那么该用户就同时拥有了select和insert权限。

　　2.授权原则说明：

　　　　权限控制主要是出于安全因素，因此需要遵循一下几个经验原则：

   　　　 a、只授予能满足需要的最小权限，防止用户干坏事。比如用户只是需要查询，那就只给select权限就可以了，不要给用户赋予update、insert或者delete权限。

    　　　b、创建用户的时候限制用户的登录主机，一般是限制成指定IP或者内网IP段。

    　　　c、初始化数据库的时候删除没有密码的用户。安装完数据库的时候会自动创建一些用户，这些用户默认没有密码。

    　　　d、为每个用户设置满足密码复杂度的密码。

   　　　 e、定期清理不需要的用户。回收权限或者删除用户。

　eg：

/*授予用户通过外网IP对于该数据库的全部权限*/

　　grant all privileges on `test`.* to 'test'@'%' ;

　 /*授予用户在本地服务器对该数据库的全部权限*/

　　grant all privileges on `test`.* to 'test'@'localhost';   

   grant select on test.* to 'user1'@'localhost';  /*给予查询权限*/

   grant insert on test.* to 'user1'@'localhost'; /*添加插入权限*/

   grant delete on test.* to 'user1'@'localhost'; /*添加删除权限*/

   grant update on test.* to 'user1'@'localhost'; /*添加权限*/

　 flush privileges; /*刷新权限*/

(6)查看权限

show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

查看某个用户的权限：

show grants for 'jack'@'%';
+-----------------------------------------------------------------------------------------------------+
| Grants for jack@%                                                                                   |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'jack'@'%' IDENTIFIED BY PASSWORD '*9BCDC990E611B8D852EFAF1E3919AB6AC8C8A9F0' |
+-----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

(7)删除权限

　　revoke privileges on databasename.tablename from 'username'@'host';

revoke delete on test.* from 'jack'@'localhost';

(8)更改用户名

　　mysql> rename user 'jack'@'%' to 'jim'@'%';

(9)修改密码

1.用set password命令

　　mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');

　　Query OK, 0 rows affected (0.00 sec)

2.用mysqladmin [root@rhel5 ~]# mysqladmin -uroot -p123456 password 1234abcd

　　备注： 格式：mysqladmin -u用户名 -p旧密码 password 新密码

3.用update直接编辑user表

(10)pycharm中python3.6+pymysql+mysql8.0.1连接报错　

pymysql.err.OperationalError: (1045, u"Access denied for user 'root'@'localhost' (using password: No)")

解决方法：　

在cmd命令行连接mysql, 通过mysql -u root -p dong1990

 

然后输入ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'dong1990';