MySQL入门总结

、

一、注释方式

/*
多行注释
*/

-- 单行注释

# 单行注释

二、数据库操作

1、查看全部数据库：show databases;

2、选择数据库：use mysql；

3、查看当前数据库：select database();

4、创建数据库：create database 数据库名；

5、删除数据库：drop database 数据库名；
若存在再删除：drop database if exists 数据库名；

三、表操作

1、查询数据库中的表：show tables；

2、创建表：

create table 表名(
字段名 数据类型，
字段名 数据类型，
......
);

3、删除表：drop table 表名;

4、修改表的名称：rename table 表名 to 新表名;

5、添加新的列：alter table 表名 add 新列名 数据类型(长度);

6、删除指定列：alter table 表名 drop 列名;

7、修改列的数据类型(长度)：alter table 表名 modify 列名 修改后的数据类型(长度);

8、 修改列的名称：alter table 表名 change 列名 新列名 新列名的数据类型(长度);

四、内容操作

1、插入表记录：
方式一：对指定的字段插入值： insert into 表名(字段1, 字段2, ...) values (值1,值2, ...);
方式二： 对所有字段插入值： insert into 表名 values(值1, 值2, ...);
方式三：一次性插入多条数据： 表名(字段1, 字段2, ...) values (值1, 值2, ...),(值1, 值2, ...);

2、更新表记录：update 表名 set 字段1=值, 字段2=值... where 条件;

3、删除表记录： delete from 表名 where 条件;

五、SQL约束

1、主键约束
（1）创建主键约束
方式一：创建表时，在字段描述处，声明指定字段为主键

字段名 数据类型[长度] PRIMARY KEY

CREATE TABLE persons (
pid int primary key,       #此处添加了主键约束，则pid为主键
lastname varchar(255),
firstname varchar(255),
address varchar(255)
);

方式二：创建表时，在constraint约束区域，声明指定字段为主键

constraint 名称 primary key (字段列表)

CREATE TABLE persons (
pid int,
lastname varchar(255),
firstname varchar(255),
address varchar(255),
constraint pk_persons primary key (pid) # 添加主键约束, 单一字段
);

CREATE TABLE persons (
pid INT,
lastname VARCHAR(255),
firstname VARCHAR(255),
address VARCHAR(255),
CONSTRAINT pk_persons PRIMARY KEY (lastname, firstname) # 添加主键约束, 多个字段, 称为联合主键。
);

方式三：创建表之后，通过修改表结构，声明指定字段为主键

 ALTER TABLE 表名 ADD [CONSTRAINT 名称] PRIMARY KEY (字段列表)

CREATE TABLE persons (
pid int,
lastname varchar(255),
firstname varchar(255),
address varchar(255)
);
alter table persons add constraint pk_persons primary key (lastname, firstname);

（2）删除主键约束： alter table 表名 drop primary key

2、自动增长列
（1）添加：字段名 整数类型[长度][约束] auto_increment

CREATE TABLE persons (
pid INT PRIMARY KEY AUTO_INCREMENT,   #添加关键字
lastname VARCHAR(255),
firstname VARCHAR(255),
address VARCHAR(255)
);

3、非空约束
（1）添加非空约束
方式一：创建表时，在字段描述处，声明指定字段为非空约束字段名 数据类型[长度] NOT NULL

CREATE TABLE persons (
pid INT PRIMARY KEY AUTO_INCREMENT,
lastname VARCHAR(255) not null,
firstname VARCHAR(255) not null,
address VARCHAR(255)
);

方式二：修改表时ALTER TABLT <表名> MODIFY [COLUMN] <列名> <数据类型> NOT NULL;

 ALTER TABLE persons MODIFY firstname varchar(255) NOT NULL ;

（2）删除非空约束ALTER TABLE 表名 MODIFY 字段名 数据类型[长度]

4、唯一约束

（1）添加唯一约束
方式一：创建表时，在字段描述处，声明唯一字段名 数据类型[长度] UNIQUE
方式二：创建表时，在约束区域，声明唯一constraint 名称 UNIQUE (字段)
方式三：创建表后，修改表结构，声明字段唯一ALTER TABLE 表名 ADD [CONSTRAINT 名称] UNIQUE (字段)
（2）删除唯一约束
删除 UNIQUE 约束，SQL格式:ALTER TABLE 表名 DROP INDEX 名称

5、默认约束
（1）添加默认约束
在创建表时候添加格式:字段名 数据类型[长度] default 默认值

CREATE TABLE persons (
pid INT,
lastname VARCHAR(255),
firstname VARCHAR(255),
address VARCHAR(255) DEFAULT '香港' -- 添加默认约束
);

2）删除默认约束ALTER TABLE 表名 MODIFY 字段名 数据类型[长度]

ALTER TABLE persons MODIFY address VARCHAR(255);

六、数据查询

1、简单查询：select X，X，X from 表名;

2、条数限制查询：select 列名 from 表名 limit [m,n]

3、排序select 列名,... from 表名 order by 排序字段 ASC|DESC;
ASC 升序 (默认), 从小到大排序
DESC 降序, 从大到小排序

4、条件查询：简单查询：select X，X，X from 表名 where 条件;

5、模糊查询：select 列名,... from 表名 where 列名 like 条件;
语句中，% 代表零个或多个任意字符，_ 代表一个字符

6、正则表达式：select 列名 from 表名 where 列名 regexp '条件';

7、分组查询select 字段1,字段2… from 表名 group by 分组字段 having 分组条件;

8、子查询

SELECT cust_id, cust_name
FROM customers
WHERE cust_id IN (
   SELECT cust_id
   FROM orders
   WHERE order_num IN (
      SELECT order_num
      FROM orderitems
      WHERE prod_id = 60005
   )
);

9、组合查询
UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。默认：多个 SELECT 语句会删除重复的数据

 SELECT 列名, 列名,...
 FROM tables [WHERE 条件] 
 UNION [ ALL | DISTINCT] 
       SELECT 列名, 列名,...
       FROM tables [WHERE 条件];

10、表关联查询

 SELECT p.*,v.vend_name 
 FROM products p ,vendors v  # p和v是给起的表别名
 WHERE p.vend_id=v.vend_id;

（1）两表内联

# 这里没有使用表别名
 SELECT vend_name, prod_name, prod_price
 FROM products,
      vendors
 WHERE vendors.vend_id = products.vend_id;
 
 # 这种方式叫等值连接。也可以用表别名，来达到同样效果
 SELECT v.vend_name, p.prod_name, p.prod_price
 FROM products p,  # 起了别名就要用
      vendors v
 WHERE p.vend_id = v.vend_id;
 
 # 这种连接方式也被称为内部连接，可以用以下语法来明确指定连接类型 
 SELECT vend_name, prod_name, prod_price
 FROM products  INNER JOIN vendors         #内连接的关键字inner join，inner可以省略
 ON vendors.vend_id = products.vend_id;  
 
 # 这两种方式都叫内连接，一种是隐式内联，一种是显示内联 

（2）多表内联

 -- 隐式连接
 SELECT prod_name, vend_name, prod_price, quantity
 FROM vendors,
      products,
      orderitems
 WHERE vendors.vend_id = products.vend_id
   AND products.prod_id = orderitems.prod_id
   AND order_num = 20005;
   
 -- 显式连接
 SELECT prod_name, vend_name, prod_price, quantity
 FROM orderitems o
          JOIN products p ON o.prod_id = p.prod_id
          JOIN vendors v ON p.vend_id = v.vend_id
 WHERE o.order_num = 20005;

（3）左外连接

 # 查询所有客户的订单情况，包括没有订单的客户
 SELECT customers.cust_id, orders.order_num
 FROM customers
          LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;    # OUTER 可以省略

（4）右外连接

 SELECT customers.cust_id, orders.order_num
 FROM customers
          RIGHT OUTER JOIN orders ON customers.cust_id = orders.cust_id;

（5）自连接

 # 1.使用现有的知识储备，子查询
 # 方法一：
 SELECT vend_id FROM products WHERE prod_id=60001;
 SELECT prod_id,prod_name FROM products WHERE vend_id=1001;
 # 方法二：
 SELECT prod_id,prod_name FROM products 
 WHERE vend_id=(SELECT vend_id FROM products WHERE prod_id=60001);
 
 # 2.采用自连接
 SELECT p1.prod_id, p1.prod_name, p1.vend_id
 FROM products p1,
      products p2
 WHERE p1.vend_id = p2.vend_id
   AND p2.prod_id = 60001;

七、SQL函数

1、字段拼接函数：用于将多个字符串连接成一个字符串格式
select concat(str1,str2,…) from table_name;

2、列的计算和别名
SELECT prod_id,item_price,quantity,item_price*quantity AS total_price FROM orderitems WHERE order_num=20005;

3、文本处理函数
（1）查询产品表，将产品名称中左右字母转换为大写、小写
upper(string)：将字符串转换为大写SELECT upper(prod_name) FROM products;
lower(string)：将字符串转换为小写SELECT lower(prod_name) FROM products;
（2)查询产品表，返回产品名称的前三个字符/后三个字符
left(string,n)：返回字符串左边的n个字符SELECT left(prod_name,3) FROM products;
right(string,n)：返回字符串右边的n个字符SELECT right(prod_name,3) FROM products;
（3）substring()：返回子串的字符
substring(字符串，n)或substring(string from n)从第n个索引位置开始截取字符串
substring(字符串，索引位置，n)从索引位置开始截取字符串，向后截取n个字符
substring_index(string，关键字，n)从头开始截取字符串，向后截取到关键字出现的第n次

4、日期函数
（1）DATE_ADD() 函数向日期添加指定的时间间隔DATE_ADD(date,INTERVAL expr type)
（2）DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据DATE_FORMAT(date,format)
（3）DATEDIFF() 函数返回两个日期之间的天数DATEDIFF(date1,date2)
（4）常见函数

AddDate()：增加一个日期（天、周等）
AddTime()：增加一个时间（时、分等）
CurDate()：返回当前日期
CurTime()：返回当前时间
Date()：返回日期时间的日期部分
DateDiff()：计算两个日期之差
Date_Add()：高度灵活的日期运算函数
Date_Format()：返回一个格式化的日期或时间串
Day()：返回一个日期的天数部分
DayOfWeek()：对于一个日期，返回对应的星期几
Hour()：返回一个时间的小时部分
Minute()：返回一个时间的分钟部分
Month()：返回一个日期的月份部分
Now()：返回当前日期和时间
Second()：返回一个时间的秒部分
Time()：返回一个日期时间的时间部分
Year()：返回一个日期的年份部分

5、数学函数

round()：对数值进行四舍五入
ceil()：向上取整 返回大于等于该参数的最小整数
floor()：向下取整 返回小于该参数的最大整数值。
truncate()：截断 取小数点多少位**
abs()：返回一个数的绝对值
cos()：返回一个角度的余弦
exp()：返回一个数的指数值
mod()：返回除操作的余数
pi()：返回圆周率
rand()：返回一个随机数
sin()：返回一个角度的正弦 例如SIN(x) 返回 x 的正弦值，其中 x 为弧度值。
sqrt()：返回一个数的平方根
tan()：返回一个角度的正切

6、聚合函数

（1）COUNT() 函数返回匹配指定条件的行数。
COUNT(column_name) 函数返回指定列的值的数目（NULL 不计入）
SELECT COUNT(column_name) FROM table_name;

COUNT( * ) 函数返回表中的记录数（null也计入）SELECT COUNT(*) FROM table_name;

COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目
SELECT COUNT(DISTINCT column_name) FROM table_name;

（2）MAX() 函数返回指定列的最大值SELECT MAX(column_name) FROM table_name;

（3）SUM()函数返回数值列的总数。SELECT SUM(column_name) FROM table_name;

（4）AVG() 函数返回数值列的平均值。AVG()函数忽略列值为NULL的行。
SELECT AVG(column_name) FROM table_name;

7、其他函数
（1）查询当前用户，显示当前登陆的用户名与它对应的hostSELECT user();
（2）查询当前数据库的版本号SELECT version();

八、外键

1、添加外键
alter table 从表 add [constraint][外键名称] foreign key (从表外键字段名) references 主表 (主表的主键);
2、删除外键
alter table 从表 drop foreign key 外键名称

九、事务

MySQL有一个系统变量autocommit，用来自动提交事务SHOW VARIABLES LIKE 'autocommit';
默认是开启的，也就意味着在默认情况下，如果不显示地执行 START TRANSACTION 或者 BEGIN 开启一个事务，那么每条SQL语句都算是一个独立的事务，这种特性被称作事务的自动提交。如果想关闭这种自动提交的功能，可以使用两种办法
1、显式使用START TRANSACTION 或者 BEGIN 开启一个事务。这样在本次事务中提交或者回滚前会暂时关闭自动提交的
2、把系统变量 autocommit 设置为 OFF（设置开为ON）SET AUTOCOMMIT = off;

rollback;  # 此指令可将之前的操作恢复原样——事务回滚
commit;    # 事务提交
 
savepoint  保存点名称;           # 定义保存点
rollback to 保存点名称;          # 回滚到保存点
release savepoint 保存点名称;    # 删除保存点

十、索引

1、创建索引
（1）创建表时创建
普通索引：

 CREATE TABLE book(
     id int AUTO_INCREMENT PRIMARY KEY ,
     bookname varchar(255) NOT NULL ,
     INDEX (bookname)   # 创建普通索引
 );

唯一索引：

 CREATE TABLE book2(
     id int AUTO_INCREMENT PRIMARY KEY ,
     bookname varchar(255) NOT NULL ,
    UNIQUE INDEX (bookname)    # 创建唯一索引
 ); 

给索引起名字：

 CREATE TABLE book3(
     id int AUTO_INCREMENT PRIMARY KEY ,
     bookname varchar(255) NOT NULL ,
    UNIQUE INDEX  name_idx (bookname)  # 设定索引名字
 ); 

（2）单独创建索引
普通索引：

 CREATE INDEX index_name ON table_name (column_name);

唯一索引：

 CREATE UNIQUE INDEX index_name ON table_name (column_name);

组合索引：

 CREATE INDEX index_name ON table_name (column_name1,column_name2);

（3）修改表结构创建

 ALTER TABLE table_name ADD INDEX name_idx(column_name);

2、删除索引

 ALTER TABLE table_name DROP INDEX index_name;
 DROP INDEX index_name ON table_name; 

十一、视图

1、创建视图，包含所有订单的产品名称，供应商名称，产品价格，购买数量

 CREATE VIEW order_detail AS
     SELECT order_num,prod_name, vend_name, prod_price, quantity
 FROM orderitems o
          JOIN products p ON o.prod_id = p.prod_id
          JOIN vendors v ON p.vend_id = v.vend_id;

2、查看视图的所有数据 SELECT * FROM order_detail;
3、从视图中过滤数据SELECT * FROM order_detail WHERE order_num=20005;
4、删除视图 DROP VIEW order_detail;

十二、触发器

1、创建只有一个执行语句的触发器

 CREATE TRIGGER trigger_name trigger_time trigger_event
 ON tab_name FOR EACH ROW trigger_stmt

查看触发器 SHOW TRIGGERS ;
删除触发器 DROP TRIGGER book_insert_select;

2、创建有多个执行语句的触发器

CREATE TRIGGER trigger_name trigger_time trigger_event
ON tab_name FOR EACH ROW 
BEGIN    
 语句执行列表
END

十三、存储过程

1、创建存储过程的基本形式如下：

   CREATE PROCEDURE  存储过程名 (参数列表)
   BEGIN    
     SQL语句代码块
   END

2、调用存储过程 CALL my_test();

3、删除存储过程DROP PROCEDURE my_test;