SQL 入门知识——基于MySQL数据库的SQL操作 学习笔记

 

 

本文查阅方法：
    1、查阅目录 —— 查阅本文目录，确定想要查阅的目录标题
    2、快捷“查找” —— 在当前浏览器页面，按键 “Ctrl+F” 按键组合，开启浏览器的查找功能，
             在查找搜索框中 输入需要查阅的 目录标题，便可以直接到达 标题内容 的位置。
    3、学习小结 —— 文中的学习小结内容，是笔者在学习之后总结出的，开发时可直接参考其进行应用开发的内容， 进一步加快了本文的查阅 速度。（水平有限，仅供参考。）​

 

​

 

---

 

 

 

本文目录

 

    ​​​学习小结​

 

    ​1、MySQL简介 

 

    ​2、数据库服务器、数据库和表的关系

 

    ​3、数据在数据库中的存储方式

 

    ​4、创建数据库

 

    ​5、查看、删除数据库

 

    ​6、修改、备份、恢复数据库

 

    ​7、创建表(基本语句)

 

    ​8、修改表

 

    ​9、Insert语句(增加数据)

 

    ​10、Update语句(更新数据)

 

    ​11、Delete语句(删除数据) 

 

    ​12、Select语句--  基本select语句

 

    ​13、Select语句--  使用表达式对查询的列进行运算

 

    ​14、Select语句--  使用where子句，进行过滤查询。

 

    ​15、Select语句--  在where子句中经常使用的运算符

 

    ​16、Select语句--  使用order by 子句排序查询结果。

 

    ​17、合计函数－Count ​

 

    ​18、合计函数－SUM

 

    ​19、合计函数－AVG

 

    ​20、合计函数－MAX/MIN

 

    ​21、Select语句--  使用group by 子句对列进行分组

 

    ​22、时间日期相关函数

 

    ​23、字符串相关函数

 

    ​24、数学相关函数

 

    ​25、定义表的约束

 

    ​26、mysql中文乱码

 

    ​27、数据库表的设计方案

 

    ​        (1)  表的设计方案 原则

 

    ​        (2)  “ 一对多”或“多对一”表关联关系的设计方案

 

    ​        (3)  “多对多”表关联关系的设计方案 

 

    ​        (4) “ 一对一”表关联关系的设计方案 

 

    ​        (5)  “自关联”表关联关系的设计方案 (应用：“无限级分类” 设计)

 

 

 

​

 

---

 

 

 

学习小结

 

(1)创建数据库—— 创建一个使用utf-8字符集，并带校对规则的mydb3数据库。

 

        create database mydb3 character set utf8 collate utf8_general_ci;

 

​

 

(2) 备份数据库-- ​​​启动一个window命令行窗口，执行如下命令：

 

      mysqldump -uroot -p tt>c:\tt.sql   (window命令:结尾没有分号)

 

 

 

(3) 恢复数据库

 

          a.先删除库：drop database tt;

 

      b.为恢复库，要先创建库  create database tt;

 

      c.恢复库   mysql -uroot -proot tt<c:\tt.sql   (window命令:结尾没有分号)

 

 

 

(4) 创建表(基本语句)

 

    CREATE TABLE table_name

 

    (

 

      field1  datatype,

 

      field2  datatype,

 

      field3  datatype

 

    )character set 字符集 collate 校对规则

 

    field：指定列名　datatype：指定列类型

 

 

 

(5) 使用 INSERT 语句向表中插入数据。(注：数值数据和文本数据都可以使用单引号‘’ 。)

 

  INSERT INTO table [(column [, column...])] VALUES (value [, value...]);

 

 

 

(6) 使用 update语句修改表中数据。

 

    Demo：将姓名为’bbb的员工薪水修改为在原有基础上增加1000元,job改为ccc。

 

    update employee set salary=salary+1000,job='ccc' where username='bbb';

 

 

 

(7) 使用 delete语句删除表中数据。

 

    Demo：删除表中名称为’zs’的记录。

 

        delete from employee where username='bbb';

 

 

 

(8) Select语句--  使用表达式对查询的列进行运算----  使用别名表示学生分数。

 

    Demo： select name as 姓名,(chinese+english+math)+10 as 总分 from student;

 

 

 

(9) Select语句--  使用where子句，进行过滤查询。

 

    Demo：查询总分大于200分的所有同学

 

        select name from student where (chinese+english+math)>200;

 

 

 

(10) 在where子句中经常使用的运算符：

 

    <,>,<=,>=,=,<>,between...and... ,in(set),like'张%/_',is null,and,or,not.

 

 

 

(11) Select语句--  使用order by 子句排序查询结果。

 

    Demo：对总分排序后输出，然后再按从高到低的顺序输出

 

        select name 姓名,(chinese+english+math) 总分 from student order by (chinese+english+math) desc;

 

 

 

(12) Select语句--  使用group by 子句对列进行分组 ---  使用having 子句过滤

 

    Demo：查询购买了几类商品，并且每类总价大于100的商品

 

        select product from orders group by product having sum(price)>100;

 

 

 

(13) 合计函数－Count 

 

    Demo：统计数学成绩大于90的学生有多少个？

 

        select count(*) from student where math>80;

 

 

 

(14) 合计函数－SUM

 

    Demo：统计一个班级总成绩平均分

 

        select sum(chinese+english+math)/count(*) from student;

 

 

 

(15) 合计函数－AVG

 

    Demo：求一个班级总分平均分

 

        select avg(chinese+math+english) from student;

 

 

 

(16) 合计函数－MAX/MIN

 

    Demo： 求班级最高分和最低分（数值范围在统计中特别有用）

 

        select max(chinese+math+english),min(chinese+math+english) from student;

 

 

 

(17) 定义表的约束 -- 主键、自动增长、唯一、非空、外键约束

 

    Demo样例：                             

 

    create table student

 

    (

 

      id int  primary key auto_increment,  //定义主键自动增长

 

       name varchar(40) unique not null,  // 定义唯一 + 非空 约束

 

      husband_id int,

 

      constraint husband_id_FK foreign key(husband_id) references husband(id)  //外键约束 

 

    );

 

​

 

---

 

​

 

1、MySQL简介 

 

    常用的数据库简介

 

        SQL Server、Oracle、MySQL、DB2、SyBase

 

    MySQL数据库的安装和配置（详见附件资料：（《MySQL安装图解.doc》）

 

    使用命令行窗口连接MYSQL数据库

 

    方式一： mysql –u用户名 –p密码 ——  注：该种方式会将密码明文显示在屏幕上，易被旁观。

 

    方式二：   mysql –u用户名 –p

 

               Enter password: *****
     注：方式二的密码可以“*” 的方式来显示，增强了保密性，推荐使用。       

 

​

 

---

 

​

 

2、数据库服务器、数据库和表的关系

 

 

 

​

 

    装好mysql，只是装好了一个数据库管理程序，要想通过这个程序保存数据，需要在这个程序下创建多个库来保存(一般开发人员会针对每一个应用创建一个数据库)。而库又是使用表保存数据的，所以库创建好后，我们又会在数据库下面创建多个表，以保存程序中不同实体的数据。 

 

​

 

---

 

​

 

3、数据在数据库中的存储方式

 

 

​

 

---

 

​

 

4、创建数据库

 

    语法：

 

    CREATE  DATABASE  [IF NOT EXISTS] db_name    

 

          [create_specification [, create_specification] ...] 

 

​

 

    create_specification:     

 

        [DEFAULT] CHARACTER SET charset_name  

 

    |   [DEFAULT] COLLATE collation_name 

 

​

 

    CHARACTER SET：指定数据库采用的字符集

 

    COLLATE：指定数据库字符集的比较方式(即：校对规则，查阅参考手册《MySQL_5.1_zh.chm-->10.字符集支持

 

                            -->10.10. MySQL支持的字符集和校对-->选择相应字符集的校对规则进行查阅。

 

    练习：

 

    (1) 创建一个名称为mydb1的数据库

 

        create database mydb1;

 

        show databases;

 

​

 

    (2) 创建一个使用utf-8字符集的mydb2数据库。

 

        create database mydb2 character set utf8;

 

​

 

    (3) 创建一个使用utf-8字符集，并带校对规则的mydb3数据库。

 

        create database mydb3 character set utf8 collate utf8_general_ci;

 

​

 

​

---

 

​

 

​

 

5、查看、删除数据库

 

    (1) 显示数据库语句：

 

        SHOW DATABASES

 

​

 

    (2) 显示数据库创建语句：

 

        SHOW CREATE DATABASE db_name

 

​

 

    (3) 数据库删除语句：

 

        DROP DATABASE  [IF EXISTS]  db_name 

 

​

 

    练习：

 

   (1)  查看当前数据库服务器中的所有数据库

 

        show databases;

 

​

 

    (2) 查看前面创建的mydb2数据库的定义信息

 

        show create database mydb2;

 

​

 

    (3) 删除前面创建的mydb1数据库

 

        drop database mydb1;

 

​

 

---

 

​

 

6、修改、备份、恢复数据库

 

    (1) 修改数据库语法：

 

        ALTER  DATABASE  [IF NOT EXISTS] db_name    

 

          [alter_specification [, alter_specification] ...] 

 

​

 

        alter_specification:    

 

            [DEFAULT] CHARACTER SET charset_name  

 

        |   [DEFAULT] COLLATE collation_name

 

​

 

    (2) 备份数据库表中的数据

 

          mysqldump -u 用户名 -p 数据库名 > 文件名.sql

 

    (3) 恢复数据库

 

          Source 文件名.sql

 

​

 

    练习

 

    (1)  查看服务器中的数据库，并把其中某一个库的字符集修改为gb2312;

 

        alter database mydb2 character set gb2312;

 

        show create database mydb2;

 

    (2)  备份test库中的数据，并恢复

 

        演示备份

 

    ​    ​    ​    ​(a) 首先创建一个数据库并插入数据

 

    ​    ​                create database tt;

 

    ​    ​                use tt;

 

    ​    ​                create table a

 

    ​    ​                (

 

    ​    ​                  name varchar(20)

 

    ​    ​                );

 

    ​    ​                insert into a(name) values('aaaa');

 

    ​    ​                select * from a;

 

    ​    ​                -----看到a表有数据 

 

           ​    ​(b) 对数据库tt作备份操作

 

    ​    ​    ​    ​    ​启动一个window命令行窗口，执行如下命令：

 

                        mysqldump -uroot -p tt>c:\tt.sql   (window命令:结尾没有分号)

 

​

 

        演示恢复

 

                步骤一：先删除库

 

                drop database tt;

 

​

 

                步骤二：.恢复tt库

 

                        恢复方式1：

 

                              a.  为恢复库，要先创建库  create database tt;

 

                              b.  再恢复tt库 

 

                                  use tt; 

 

                                  source c:\tt.sql  （结尾没有分号）   （source:可以执行一个 sql脚本）                         

 

                          恢复方式2：   

 

                              a.  为恢复库，要先创建库  create database tt;

 

                              b.  恢复库   mysql -uroot -proot tt<c:\tt.sql   (window命令:结尾没有分号)

 

                             【 备注：该种方式，是编程实现恢复数据库功能时所使用的方式。  】

 

​

 

---

 

​

 

7、创建表(基本语句)

 

CREATE TABLE table_name

 

(

 

  field1  datatype,

 

  field2  datatype,

 

  field3  datatype

 

)character set 字符集 collate 校对规则

 

field：指定列名　datatype：指定列类型

 

​

 

查阅表信息的语法
\s 查看当前所在库的名称。
show tables;  查看库的所有表
show create table employee;   查看表的创建细节
desc employee;     看表结构

 

​

 

注意：创建表前，要先使用use db语句使用库。

 

 

 

​

 

MySQL常用数据类型

 

 

 

注：VARCHAR、BLOB和TEXT类是变长类型。每个类型的存储需求取决于列值的实际长度。 

 

​

 

创建表练习

 

需求：创建一个员工表

 

字段 --属性：Id -- 整型；name -- 字符型；sex -- 字符型或bit型；brithday -- 日期型；Entry_date -- 日期型；job -- 字符型；Salary  -- 小数型；resume -- 大文本型

 

创建语句如下：
use mydb2;
create table employee
(
 id int,
 name varchar(40),
 sex varchar(4),
 birthday date,
 entry_date date,
 job varchar(40),
 salary decimal(8,2),
 resume text
);

 

​

 

​

 

​

---

 

​

 

​

 

8、修改表

 

使用 ALTER TABLE 语句追加, 修改, 或删除 列 的语法.

 

    (1) 追加列语句：

 

        ALTER TABLE table_name

 

        ADD       (column datatype [DEFAULT expr]

 

               [, column datatype]...);

 

    (2) 修改列语句：

 

        ALTER TABLE table_name

 

        MODIFY     (column datatype [DEFAULT expr]

 

               [, column datatype]...);

 

    (3) 删除列语句：

 

        ALTER TABLE table_name

 

        DROP        (column);

 

    (4) 修改表的名称：

 

            Rename table 表名 to 新表名

 

    (5) 修改表的字符集：

 

            alter table student character set utf8;

 

​

 

    修改表练习: 

 

        (1)  在上面员工表的基本上增加一个image列。

 

        alter table employee add image blob;

 

​

 

        (2)  修改job列，使其长度为60。

 

        alter table employee modify job varchar(60);

 

​

 

        (3)  删除sex列

 

        alter table employee drop sex;

 

​

 

        (4)  表名改为user。

 

        rename table employee to user;

 

​

 

        (5)  修改表的字符集为utf-8

 

        alter table user character set utf8;

 

​

 

        (6)  列名name修改为username

 

        alter table user change column name username varchar(40);

 

​

 

        (7)  删除表

 

        drop table user;

 

​

 

    【小技巧：

 

        mysql客户端 创建表输入错误时，停止当前的编辑状态：

 

        （1）先按照提示补全符号；

 

        （2）再输入“;”  或者输入“ \c ” 来 停止编辑状态。】

 

​

 

​

 

---

 

​

 

9、Insert语句(增加数据)

 

        数据库CRUD语句

 

        Insert语句    (增加数据)

 

        Update语句  (更新数据)

 

        Delete语句   (删除数据)

 

        Select语句　(查找数据)

 

​

 

Insert语句(增加数据)

 

使用 INSERT 语句向表中插入数据。

 

INSERT INTO  table [(column [, column...])]

 

VALUES    (value [, value...]);

 

​

 

插入的数据应与字段的数据类型相同。

 

数据的大小应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。

 

在values中列出的数据位置必须与被加入的列的排列位置相对应。

 

字符和日期型数据应包含在单引号中。

 

插入空值，不指定或insert into table value(null)

 

 

​

 

Insert语句练习

 

    练习：使用insert语句向表中插入三个员工的信息。

 

    字段名--  字段类型：id--  整形；name--  字符串型；sex--  字符或整数类型；birthday-- 日期型；salary-- 浮点型；entry_date--  日期型；resume-- 大文本型；

 

插入语句如下：

 

    rename table user to employee;    //先将上文中修改的表名改过来。

 

    insert into employee(id,username,birthday,entry_date,job,salary,resume) values(1,'aaa','1980-09-09','1980-09-09','bbb',90,'aaaaa');

 

    select * from employee;

 

​

 

插入数据的细节1-- 数值不需要加单引号，文本数据需要加单引号。

 

insert into employee values(1,'aaa','1980-09-09','1980-09-09','bbb',90,'aaaaa');

 

​

 

插入数据的细节2-- 数值数据和文本数据都可以添加单引号，省去区分是否添加单引号的麻烦。

 

insert into employee values('1','aaa','1980-09-09','1980-09-09','bbb','90','aaaaa');

 

​

 

​

 

---

 

​

 

10、Update语句(更新数据)

 

使用 update语句修改表中数据。

 

    UPDATE   tbl_name    

 

      SET col_name1=expr1 [, col_name2=expr2 ...]    

 

      [WHERE where_definition]    

 

    说明：

 

    (1) UPDATE语法可以用新值更新原有表行中的各列。

 

    (2) SET子句指示要修改哪些列和要给予哪些值。

 

    (3) WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。

 

​

 

Update语句练习：在上面创建的employee表中修改表中的纪录。

 

    (1)  将所有员工薪水修改为5000元。

 

    update employee set salary=5000;

 

​

 

    (2)  将姓名为’bbb’的员工薪水修改为3000元。

 

    update employee set salary=3000 where username='bbb';

 

​

 

    (3)  将姓名为’bbb的员工薪水修改为4000元,job改为ccc。

 

    update employee set salary=4000,job='ccc' where username='bbb';

 

​

 

    (4)  将bbb的薪水在原有基础上增加1000元。

 

    update employee set salary=salary+1000 where username='bbb';

 

​

 

    (5)  更新要注意的问题

 

    update employee set username='ccc',salary=9000,birthday='1980-09-09',.....................

 

    update  where id=1;

 

    一定要注意写 where子句的问题。

 

​

 

​

---

 

​

 

​

 

11、Delete语句(删除数据)

 

    使用 delete语句删除表中数据。

 

    delete from tbl_name       

 

      [WHERE where_definition]    

 

    说明：

 

        (1) 如果不使用where子句，将删除表中所有数据。

 

        (2) Delete语句不能删除某一列的值（可使用update）

 

        (3) 使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句。

 

        (4) 同insert和update一样，从一个表中删除记录将引起其它表的参照完整性问题，在修改数据库数据时，头脑中应该始终不要忘记这个潜在的问题。

 

        (5) 删除表中数据也可使用TRUNCATE TABLE 语句，它和delete有所不同，是先删除该表，然后再重新创建该表以达到删除表中数据的目的，具体参看mysql文档。

 

​

 

    Delete语句练习 

 

        (1) 删除表中名称为’zs’的记录。

 

        delete from employee where username='bbb';

 

        (2) 删除表中所有记录。

 

        delete from employee;

 

        (3) 使用truncate删除表中记录。

 

        truncate table employee;

 

​

 

---

 

​

 

12、Select语句--  基本select语句

 

    SELECT [DISTINCT] *|{column1, column2. column3..}

 

        FROM  table;

 

    Select 指定查询哪些列的数据。

 

        (1) column指定列名。

 

        (2) *号代表查询所有列。

 

        (3) From指定查询哪张表。

 

        (4) DISTINCT可选，指显示结果时，是否剔除重复数据

 

​

 

    练习：

 

        (1) 查询表中所有学生的信息。

 

        select * from student;

 

​

 

        (2) 查询表中所有学生的姓名和对应的英语成绩。

 

        select name,english from student;

 

​

 

        (3) 过滤表中重复的英语数据。

 

        select distinct english from student;

 

​

 

---

 

​

 

13、Select语句--  使用表达式对查询的列进行运算

 

        SELECT *|{column1｜expression, column2｜expression，..}

 

            FROM  table;

 

    说明：

 

    在select语句中可使用as语句设定列的别名：

 

        SELECT column as 别名 from 表名;

 

​

 

    练习

 

        (1) 在所有学生总分上加10分特长分。

 

        select name,(chinese+english+math)+10 from student;

 

​

 

        (2) 统计每个学生的总分。

 

        select name,(chinese+english+math) from student;

 

​

 

        (3) 使用别名表示学生分数。

 

        select name as 姓名,(chinese+english+math)+10 as 总分 from student;

 

        select name 姓名,(chinese+english+math)+10  总分 from student;

 

​

 

​

---

 

​

 

​

 

14、Select语句--  使用where子句，进行过滤查询。

 

     练习：

 

        (1) 查询姓名为wu的学生成绩

 

        select * from student where name='王五';

 

​

 

        (2) 查询英语成绩大于90分的同学

 

        select * from student where english>'90';

 

​

 

        (3) 查询总分大于200分的所有同学

 

        select name from student where (chinese+english+math)>200;

 

​

 

​

---

 

​

 

​

 

15、Select语句--  在where子句中经常使用的运算符

 

 

 

​

 

    练习：

 

        (1) 查询英语分数在 80－90之间的同学。

 

        select name from student where english>80 and english<90;

 

        select name from student where english between 80 and 90;  

 

            上句等同于：select name from student where english>=80 and english<=90;

 

​

 

        (2) 查询数学分数为89,90,91的同学。

 

        select * from student where math in(89,90,91);

 

​

 

        (3) 查询所有姓李的学生成绩。

 

        select * from student where name like '李%';

 

        select * from student where name like '李_';

 

​

 

        (4) 查询数学分>80，语文分>80的同学。

 

        select * from student where math>80 and chinese>80;

 

​

 

---

 

​

 

16、Select语句--  使用order by 子句排序查询结果。

 

        SELECT column1, column2. column3..

 

            FROM  table;

 

            order by column asc|desc

 

    说明：

 

        (1) Order by 指定排序的列，排序的列即可是表中的列名，也可以是select 语句后指定的列名。

 

        (2) Asc 升序、Desc 降序

 

        (3) ORDER BY 子句应位于SELECT语句的结尾。

 

    练习：

 

        (1) 对数学成绩排序后输出。

 

        select name,math from student order by math; 

 

​

 

        (2) 对总分排序后输出，然后再按从高到低的顺序输出

 

        select name 姓名,(chinese+english+math) 总分 from student order by (chinese+english+math) desc;

 

        select name 姓名,(chinese+english+math) 总分 from student order by 总分 desc;

 

​

 

        (3) 对姓李的学生成绩排序输出

 

        select * from student where name like '李%' order by (chinese+english+math) desc;

 

​

 

---

 

​

 

17、合计函数－Count 

 

    Count(列名)返回某一列，行的总数

 

    Select count(*)|count(列名) from tablename

 

        [WHERE where_definition]   

 

​

 

    练习：

 

        (1) 统计一个班级共有多少学生？

 

        select count(name) from student;

 

        select count(*) from student;

 

​

 

        (2) 统计数学成绩大于90的学生有多少个？

 

        select count(*) from student where math>80;

 

​

 

        (3) 统计总分大于250的人数有多少？

 

        select count(*) from student where (chinese+english+math)>250;

 

​

 

        (4) 关于 count的函数的细节 （count只统计有值的行）

 

​

 

---

 

​

 

18、合计函数－SUM

 

        Sum函数返回满足where条件的行的和

 

        Select sum(列名)｛,sum(列名)…｝ from tablename

 

            [WHERE where_definition]   

 

    练习：

 

        (1) 统计一个班级数学总成绩？

 

            select sum(math) from student;         

 

        (2) 统计一个班级语文、英语、数学各科的总成绩

 

            select sum(chinese),sum(english),sum(math) from student;         

 

        (3) 统计一个班级语文、英语、数学的成绩总和

 

            select sum(chinese+english+math) from student;         

 

        (4) 统计一个班级语文成绩平均分

 

            select sum(chinese)/count(*) from student;

 

​

 

        注意1：sum仅对数值起作用，否则会报错。

 

        注意2：对多列求和，“，”号不能少。

 

​

 

---

 

​

 

19、合计函数－AVG

 

        AVG函数返回满足where条件的一列的平均值

 

        Select sum(列名)｛,sum(列名)…｝ from tablename

 

            [WHERE where_definition]   

 

​

 

    练习：

 

        (1) 统计一个班级语文成绩平均分

 

        select avg(chinese) from student;

 

​

 

        (2) 求一个班级总分平均分

 

        select avg(chinese+math+english) from student;

 

​

 

---

 

​

 

20、合计函数－MAX/MIN

 

        Max/min函数返回满足where条件的一列的最大/最小值

 

        Select max(列名)　from tablename

 

            [WHERE where_definition]   

 

​

 

    练习：

 

        求班级最高分和最低分（数值范围在统计中特别有用）

 

            select max(chinese+math+english),min(chinese+math+english) from student;

 

​

 

---

 

​

 

21、Select语句--  使用group by 子句对列进行分组

 

        (1)  使用group by 子句对列进行分组

 

        SELECT column1, column2. column3.. FROM  table;

 

            group by column

 

​

 

          (2) 使用having 子句过滤

 

            SELECT column1, column2. column3..

 

                FROM  table;

 

                group by column having ...

 

        备注：Having和where均可实现过滤，但在having可以使用合计函数,having通常跟在group by后，它作用于组。

 

​

 

    练习：

 

        (1) 对订单表中商品归类后，显示每一类商品的总价

 

        select product,sum(price) from orders group by product;

 

​

 

        (2) 查询购买了几类商品，并且每类总价大于100的商品

 

        select product from orders group by product having sum(price)>100;

 

​

 

---

 

​

 

22、时间日期相关函数

 

        (1)  ADDTIME (date2 ,time_interval )    将time_interval加到date2

 

        (2)  CURRENT_DATE (  )                       当前日期

 

        (3)  CURRENT_TIME (  )                         当前时间

 

        (4)  CURRENT_TIMESTAMP (  )           当前时间戳        

 

        (5)  DATE (datetime )                             返回datetime的日期部分

 

        (6)  DATE_ADD (date2 , INTERVAL d_value d_type )  在date2中加上日期或时间

 

        (7)  DATE_SUB (date2 , INTERVAL d_value d_type )  在date2上减去一个时间

 

        (8)  DATEDIFF (date1 ,date2 )             两个日期差

 

        (9)  NOW (  )                                            当前时间

 

        (10)  YEAR|Month|DATE (datetime )    年月日

 

​

 

    示例：select addtime(‘02:30:30’,‘01:01:01’);           注意：字符串、时间日期的引号问题

 

          select date_add(entry_date,INTERVAL 2 year) from student;／／增加两年

 

    　　　select addtime(time,‘1 1-1 10:09:09’) from student;　／／时间戳上增加，注意年后没有-

 

​

 

---

 

​

 

23、字符串相关函数

 

        (1)  CHARSET(str)                                  返回字串字符集

 

        (2)  CONCAT (string2  [,... ])                   连接字串

 

        (3)  INSTR (string ,substring )                 返回substring在string中出现的位置,没有返回0

 

        (4)  UCASE (string2 )                              转换成大写

 

        (5)  LCASE (string2 )                              转换成小写

 

        (6)  LEFT (string2 ,length )                     从string2中的左边起取length个字符

 

        (7)  LENGTH (string )                              string长度

 

        (8)  REPLACE (str ,search_str ,replace_str )      在str中用replace_str替换search_str

 

        (9)  STRCMP (string1 ,string2 )              逐字符比较两字串大小,

 

        (10)  SUBSTRING (str , position  [,length ])          从str的position开始,取length个字符

 

        (11)  LTRIM (string2 ) RTRIM (string2 )  trim         去除前端空格或后端空格

 

​

 

---

 

​

 

24、数学相关函数

 

        (1)  ABS (number2 )                          绝对值

 

        (2)  BIN (decimal_number )              十进制转二进制

 

        (3)  CEILING (number2 )                   向上取整

 

        (4)  CONV(number2,from_base,to_base)      进制转换

 

        (5)  FLOOR (number2 )                     向下取整

 

        (6)  FORMAT (number,decimal_places )        保留小数位数

 

        (7)  HEX (DecimalNumber )             转十六进制

 

        (8)  LEAST (number , number2  [,..])                求最小值

 

        (9)  MOD (numerator ,denominator )                求余

 

        (10)  RAND([seed])  RAND([seed])

 

​

 

---

 

​

 

25、定义表的约束

 

        (1)  定义主键约束--  primary key:不允许为空，不允许重复            

 

                Demo样例： 

 

                    定义主键约束（每一个表必须有一个主键列）

 

                    create table student

 

                    (

 

                      id int  primary key,

 

                      name varchar(40)

 

                    );

 

        (2)  删除主键--   alter table tablename drop primary key ;

 

                Demo样例： 

 

​

 

        (3)  定义主键自动增长--   auto_increment

 

                Demo样例：                             

 

                        create table student

 

                        (

 

                          id int  primary key auto_increment,

 

                          name varchar(40)

 

                        );

 

        (4)  定义唯一约束--   unique

 

                Demo样例： 

 

                            drop table student;

 

                            create table student

 

                            (

 

                              id int primary key auto_increment,

 

                              name varchar(40) unique

 

                            );

 

        (5)  定义非空约束--   not null

 

                Demo样例： 

 

                            drop table student;

 

                            create table student

 

                            (

 

                              id int primary key auto_increment,

 

                              name varchar(40) unique not null

 

                            );

 

        (6)  定义外键约束--   constraint ordersid_FK foreign key(ordersid) references orders(id),

 

                Demo样例： 

 

                        create table husband

 

                        (

 

                          id int primary key,

 

                          name varchar(40)

 

                        );

 

​

 

                        create table wife

 

                        (

 

                          id int primary key,

 

                          name varchar(40),

 

                          husband_id int,

 

                          constraint husband_id_FK foreign key(husband_id) references husband(id)

 

                        );

 

​

 

​

 

​

 

---

 

​

 

26、mysql中文乱码

 

        (1)  mysql有六处使用了字符集

 

                分别为：client 、connection、database、results、server 、system。

 

                (a) client是客户端使用的字符集。 

 

                (b)  connection是连接数据库的字符集设置类型，如果程序没有指明连接数据库使用的字符集类型就按照服务器端默认的字符集设置。       

 

                (c)  database是数据w库服务器中某个库使用的字符集设定，如果建库时没有指明，将使用服务器安装时指定的字符集设置。    

 

                (d)  results是数据库给客户端返回时使用的字符集设定，如果没有指明，使用服务器默认的字符集。       

 

                (e)  server是服务器安装时指定的默认字符集设定。       

 

                (f)  system是数据库系统使用的字符集设定。

 

​

 

      (2)  使用mysql客户端，插入中文保存的是乱码数据。

 

          要告诉mysql客户采用gb2312编码

 

          show variables like 'chara%';

 

          set character_set_client=gb2312;

 

          insert into employee(id,username) values('3','张三');

 

​

 

      (3)  使用mysql客户端，要想查看时不乱码

 

          show variables like 'chara%';

 

          set character_set_results=gb2312;

 

          select * from employee;

 

          备注：上述两种方法仅设置了当前命令行窗口，当关闭该窗口后，这些设置就会失效。

 

​

 

        (4) 长久保存这些设置：修改mysql的配置文件：

 

            （a）在mysql安装文件夹下面，打开“my.ini”配置文件。

 

            （b）定位到：  

 

                    [client]

 

                    port=3306

 

                    [mysql]

 

                    default-character-set=utf8

 

            （c）修改：“default-character-set=gb2312”  即可

 

        备注：除非需要演示功能，否则尽量不要修改该值，容易发生未知的错误。

 

​

 

---

 

​

 

27、数据库表的设计方案

 

        (1)  表的设计方案 原则

 

            （a）先将各表的基本属性添加到各自表中。

 

            （b）分析各表间的关联关系来确定“外键”放置到哪个表中。

 

            （c）“一对多” 或 “多对一” 的关联关系：外键放置在“多”的一方。

 

            （d）“多对多”的关联关系，需要 建立中间表，以保存表间的关联关系。

 

            （e）“一对一”的关联关系，分清“主--从”表， 外键约束要添加到“从”表的主键上。

 

            （f）“自关联”的关联关系，在自己表中建立一列外键列，约束关联到自表中的主键上。

 

​

 

        (2)  “ 一对多”或“多对一”表关联关系的设计方案

 

                例如：部门和员工

 

                create table department

 

                (

 

                  id int primary key,

 

                  name varchar(40)

 

                );

 

​

 

                create table employee

 

                (

 

                  id int primary key,

 

                  name varchar(40),

 

                  salary decimal(8,2),

 

                  department_id int,

 

                  constraint department_id_FK foreign key(department_id) references department(id)

 

                ); 

 

​

 

        (3)  “多对多”表关联关系的设计方案 

 

    ​    ​    ​    ​例如：老师和学生

 

                create table teacher

 

                (

 

                  id int primary key,

 

                  name varchar(40),

 

                  salary decimal(8,2)

 

                );

 

​

 

                create table student

 

                (

 

                  id int primary key,

 

                  name varchar(40)

 

                );

 

​

 

                create table teacher_student

 

                (

 

                  teacher_id int,

 

                  student_id int,

 

                  primary key(teacher_id,student_id),

 

                  constraint teacher_id_FK foreign key(teacher_id) references teacher(id),

 

                  constraint student_id_FK foreign key(student_id) references student(id)  

 

                );

 

​

 

        (4) “ 一对一”表关联关系的设计方案 

 

                create table person

 

                (

 

                  id int primary key,

 

                  name varchar(40)

 

                );

 

​

 

                create table idcard

 

                (

 

                  id int primary key,

 

                  city varchar(40),

 

                  constraint id_FK foreign key(id) references person(id)  

 

                );

 

​

 

        (5)  “自关联”表关联关系的设计方案 (应用：“无限级分类” 设计)

 

                create table person

 

                (

 

                  id int primary key,

 

                  name varchar(40),

 

                  parent_id int,

 

                  constraint parent_id_FK foreign key(parent_id) references person(id)

 

                );​

 

​

 

---

 

​

敬请评论

（1）若您觉得本文 有用处  —— 请留言评论，以坚定其他 IT童鞋 阅读本文的信心。

（2）若您觉得本文 没用处  —— 请留言评论，笔者将会改进不足，以便为大家整理更加好用的笔记。
​

 

---

 

​

 

​

 

​