这篇博客详细介绍了MySQL的SQL命令，包括show、use/select、desc、create、select等，涵盖了数据库和表格的创建、查看、操作，以及数据的检索、插入、更新和删除。还讲解了各种查询技巧，如where、order by、join、union等，并讨论了索引、视图和数据类型的使用。
目录
5.18.聚合函数(avg(),count(),max(),min(),sum())
前言
结构化查询语言(Structured Query Language)简称SQL
一、mysql的安装和查看
1.1安装mysql服务端：sudo apt-get install mysql-server
1.2安装mysql客户端：sudo apt-get install mysql-client
1.3查看mysql进程：ps -ef |grep mysqld
1.4查看mysql端口：netstat -an |grep 3306
1.5登录本机mysql：mysql -u root -p
1.6登录远程mysql：mysql -h 127.0.0.1 -u root -p
1.7导入sql文件：source /home/abc/abc.sql;
二、SQL命令
1.show查看数据库/表格目录
1.1查看mysql中有多少数据库：show databases;
1.2.查看数据库中有多少表格：show tables;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.04 sec)2.use/select切换/查看当前数据库
2.1切换/选择数据库：use databasename;
2.2查看当前数据库名称：select database();
mysql> use sys
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
mysql> select database();
+------------+
| database() |
+------------+
| sys |
+------------+
1 row in set (0.00 sec)
3.desc查看表结构
查看表结构：desc tablename;
mysql> desc session;
+------------------------+------------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+------------------------------------------+------+-----+---------+-------+
| thd_id | bigint(20) unsigned | NO | | NULL | |
| conn_id | bigint(20) unsigned | YES | | NULL | |
| user | varchar(128) | YES | | NULL | |
....
....4.create创建数据库/表格/视图/索引
4.1.创建数据库-适配中文
create database databasename default chartset utf8;
CREATE DATABASE IF NOT EXISTS my_db default charset utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
-- "COLLATE utf8_general_ci",大致意思是在排序时根据utf8变码格式来排序--适配中文
4.2.创建表格
create table tablename(
列名(字段) 数据类型，主键，约束，
列名(字段) 数据类型,
......
列名(字段) 数据类型
);
create table my_table (name varchar(20) not null default '');
Query OK, 0 rows affected (0.36 sec)
--创建的表格，默认是utf8编码，创建表格已default utf8适配中文
CREATE TABLE Persons
(
Id_P int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
Query OK, 0 rows affected (0.41 sec)4.3.添加索引
您可以在表中创建索引，以便更加快速高效地查询数据。
用户无法看到索引，它们只能被用来加速搜索/查询。
注释：更新一个包含索引的表需要比更新一个没有索引的表更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。
在表上创建一个简单的索引。允许使用重复的值：
CREATE INDEX index_name
ON table_name (column_name) --普通索引
CREATE UNIQUE INDEX index_name
ON table_name (column_name) --唯一索引CREATE INDEX PersonIndex
ON Person (LastName) --创建索引，名为 "PersonIndex"，在 Person 表的 LastName 列：
CREATE INDEX PersonIndex
ON Person (LastName DESC) --以降序索引某个列中的值，您可以在列名称之后添加保留字 DESC：
CREATE INDEX PersonIndex
ON Person (LastName, FirstName) --索引不止一个列，括号中列出这些列的名称，用逗号隔开：4.4.创建视图
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No
CREATE VIEW [Products Above Average Price] AS
SELECT ProductName,UnitPrice
FROM Products
WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products) --创建视图
SELECT * FROM [Products Above Average Price] --查询视图
4.5.AUTO_INCREMENT字段
每次插入新记录时，自动地创建主键字段的值。
CREATE TABLE Persons
(
P_Id int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)
5.select检索数据
5.1基础语法
select 列名称 from 表名称
select FirstName from NameTable --单列
select FirstName,SecondName from NameTable --多列
select * from NameTable --所有列
5.2.distinct去重
语法：select distinct 列名称 from 表名称
select Company from Orders --未去重
select distinct Company from Orders --去重5.3.where条件子句
语法：select 列名称 from 表名称 where 列名称 操作符 值
select FirstName from NameTable where FirstName=‘Li’
select FirstName from NameTable where Age>20
select FirstName from NameTable where Age between 'Li' and 'Zhang'5.4.and和or运算符
涵义：AND 和 OR 可在 WHERE 子语句中把两个或多个条件结合起来。解释如字面意思
select FirstName from NameTable where SecondName='Xiu' and LastName='Chou'
select FirstName from NameTable where SecondName='Xiu' or SecondName='Liu'5.5.order by排序
ORDER BY 语句用于根据指定的列对结果集进行排序，默认按照升序对记录进行排序。如果需要按照降序对记录进行排序，可以使用 DESC 关键字。多列排序仅在第一个列名有多个值时，才会按第二列排序，如果值都是唯一的，则不会按第二个列排序
SELECT Company, OrderNumber FROM Orders ORDER BY Company --单列排序
SELECT Company, OrderNumber FROM Orders ORDER BY Company，Number --多列排序
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC，Name --降序，DESCENDING
SELECT company, OrderNumber FROM Orders ORDER BY ASC --升序，默认即是，可省略
SELECT company, OrderNumber FROM Orders ORDER BY 2,3 --按列位置排序
--DESC关键字只应用到直接位于其前面的列名，即Name列仍是以升序排列，即需要特定指定才会以降序排序：
SELECT Compamy，OrderNumber FROM Orders ORDER BY Company DESC，Name DESC
5.6.limit/top/rownum限制行数
mysql：SELECT column_name(s) FROM table_name LIMIT number
oracle：SELECT column_name(s) FROM table_name WHERE ROWNUM <= number
SQL server：SELECT TOP number|percent column_name(s) FROM table_name
SELECT * FROM Persons LIMIT 5 --只显示前5行数据
SELECT * FROM Persons LIMIT 1,2 --显示2和3行
SELECT * FROM Persons DESC LIMIT 1 --显式最后一行，同LAST语句
SELECT * FROM Persons ORDER BY Peple_id DESC LIMIT 5 --显式后5行
SELECT * FROM Persons WHERE ROWNUM <= 5 --只显示前5行数据
SELECT TOP 2 * FROM Persons --只显示前2行
SELECT TOP 50 PERCENT * FROM Persons --显式50%行的数据
5.7.like模糊查询和通配符
LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式，在搜索数据库中的数据时，SQL 通配符可以替代一个或多个字符。
SQL 通配符必须与 LIKE 运算符一起使用。语法：
SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern
| 通配符 | 描述 |
|---|---|
| % | 替代一个或多个字符,区分大小写，且不匹配空值 |
| _ | 仅替代一个字符 |
| [charlist] | 字符列中的任何单一字符 |
| [^charlist] 或者 [!charlist] | 不在字符列中的任何单一字符，^脱字符 |
SELECT * FROM Persons WHERE City LIKE 'N%' --显示以N开头的城市，%为通配符
SELECT * FROM Persons WHERE City LIKE '%g' --显示以g结尾的城市，%为通配符
SELECT * FROM Persons WHERE City LIKE '%lon%' --显示包含lon的城市，%为通配符
SELECT * FROM Persons WHERE City LIKE 'Ne%' --Ne开头的城市
SELECT * FROM Persons WHERE City LIKE '%lond%' --包含lond的城市
SELECT * FROM Persons WHERE FirstName LIKE '_eorge' --第一个字符之后是eorge的城市
SELECT * FROM Persons WHERE LastName LIKE 'C_r_er' --_代表一个任务字符
SELECT * FROM Persons WHERE City LIKE '[ALN]%' --以A/L/N开头的城市
SELECT * FROM Persons WHERE City LIKE '[!ALN]%' --不宜A/L/N开头的城市5.8.in范围查询
语法：SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...)
SELECT * FROM Persons WHERE LastName IN('Adams','Carter') --列的取值范围，in与括号间没有空格5.9.between..and操作符
操作符 BETWEEN ... AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期
语法：SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2
SELECT * FROM Persons WHERE LastName BETWEEN 'Adams' AND 'Carter' --范围内
SELECT * FROM Persons WHERE LastName NOT BETWEEN 'Adams' AND 'Carter' --范围外
select * from kr where price between 250 and 290;
select * from kr where price <=250 and pname='simida';
select * from kr where pname='simida' or pname='heygor';
select * from kr where price >=250 and price <=290;5.10.as别名Alias
as是可选的，即可写可不写，用空格表示也能达到期望
SELECT po.OrderID, p.LastName, p.FirstName
FROM Persons AS p, Product_Orders AS po
WHERE p.LastName='Adams' AND p.FirstName='John' --表别名
SELECT LastName AS Family, FirstName AS Name FROM Persons --列别名，显示为别名5.11.join表链接(多表联查)
数据库中的表可通过键将彼此联系起来。主键（Primary Key）是一个列，在这个列中的每一行的值都是唯一的。在表中，每个主键的值都是唯一的。这样做的目的是在不重复每个表中的所有数据的情况下，把表间的数据交叉捆绑在一起
sql表连接分成外连接、内连接和交叉连接。外连接包括三种,分别是左外连接、右外连接、全外连接。
外链接对应的sql关键字:LEFT/RIGHT/FULL OUTER JOIN,通常我们都省略OUTER关键字,写成LEFT/RIGHT/FULL JOIN。
内连接：如果表中有至少一个匹配，则返回行，where xxx.xxx=xxx.xx也是内链接，sql关键字JOIN 或者INNER JOIN，通常我们写成JOIN
左、右外连接：以一种表(左/右)为基表，基表的所有行、列都会显示，外表如果和条件不匹配则所有的外表列值都为NULL。
全外连接：所有表的行、列都会显示，条件不匹配的值皆为NULL。
交叉链接：没有where条件的交叉连接将产生连接表所涉及的笛卡尔积。即TableA的行数*TableB的行数的结果集
语法：
内链接：表1 inner join 表2 on 表1.主键=表2.主键; 或 表1 join 表2 on 表1.主键=表2.主键
左外链接：表1 left join 表2 on 表1.主键=表2.主键
右外链接：表1 right join 表2 on 表1.主键=表2.主键
全外链接：表1 full join 表2 on 表1.主键=表2.主键
交叉链接：select * from TableA cross join TableB 或 select * from 表1 cross join 表2 #没有on
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons, Orders
WHERE Persons.Id_P = Orders.Id_P --普通表链接，使用完全限定列名
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders --内链接
ON Persons.Id_P = Orders.Id_P --通过Id_P，使用完全限定列名
ORDER BY Persons.LastName --排序5.12.union操作符组合查询
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。联合查询只能使用一个orderby排序，不存在不同select使用不同排序方法，去除列重复行，UNION ALL则不会 去重语法：
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
Employees_China:
| E_ID | E_Name |
|---|---|
| 01 | Zhang, Hua |
| 02 | Wang, Wei |
| 03 | Carter, Thomas |
| 04 | Yang, Ming |
Employees_USA:
| E_ID | E_Name |
|---|---|
| 01 | Adams, John |
| 02 | Bush, George |
| 03 | Carter, Thomas |
| 04 | Gates, Bill |
SELECT E_Name FROM Employees_China --只显式不相同的姓名
UNION
SELECT E_Name FROM Employees_USA
--UNION命令无法列出在中国和美国的所有雇员。在上面的例子中，我们有两个名字相同的雇员，他们当中只有一个人被列出来了。UNION 命令只会选取不同的值。
SELECT E_Name FROM Employees_China
UNION ALL
SELECT E_Name FROM Employees_USA
--UNION ALL命令可以列出所有中国和美国的所有雇员，包括重复项5.13.select .. into查询插入
SELECT INTO 语句从一个表中选取数据，然后把数据插入另一个表中。常用于创建表的备份复件或者用于对记录进行存档。
语法：SELECT column_name(s) INTO new_table_name [IN externaldatabase] FROM old_tablename
SELECT * INTO Persons_backup FROM Persons --备份表格
SELECT * INTO Persons IN 'Backup.mdb' FROM Persons --从其他数据库中备份表
SELECT LastName,FirstName INTO Persons_backup FROM Persons --如果我们希望拷贝某些域，可以在 SELECT 语句后列出这些域
SELECT LastName,Firstname INTO Persons_backup FROM Persons WHERE City='Beijing' --带有 WHERE 子句
SELECT Persons.LastName,Orders.OrderNo INTO Persons_Order_Backup FROM Persons
INNER JOIN Orders ON Persons.Id_P=Orders.Id_P --被链接的表5.14.constraint约束
可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后也可以（通过 ALTER TABLE 语句）
- NOT NULL，约束强制列不接受 NULL 值
- UNIQUE，约束唯一标识数据库表中的每条记录，UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证
- PRIMARY KEY，默认定义的 UNIQUE 约束且不为NULL。每个表可有多个 UNIQUE ，但只能有一个 PRIMARY KEY 约束
- FOREIGN KEY，外键，一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY
- CHECK，CHECK 约束用于限制列中的值的范围
- DEFAULT，DEFAULT 约束用于向列中插入默认值
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
) --NOT NULL
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (Id_P)
) --mysql语法UNIQUE
CREATE TABLE Persons
(
Id_P int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
) --oracle语法UNIQUE
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)
) --多个表添加UNIQUE
ALTER TABLE Persons
ADD UNIQUE (Id_P) --表已存在，新增UNIQUE
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName) --表已存在，新增多个UNIQUE
ALTER TABLE Persons
DROP INDEX uc_PersonID --mysql语法删除约束UNIQUE
ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID --oracle语法删除UNIQUE
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (Id_P)
) --mysql语法添加主键
ALTER TABLE Persons
ADD PRIMARY KEY (Id_P) --修改主键
ALTER TABLE Persons
DROP PRIMARY KEY --撤销主键
CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (Id_O),
FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
) --mysql添加外键
CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (Id_O),
CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)
) --添加多个外键
ALTER TABLE Orders
ADD FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P) --表已存在，新增外键
ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders --撤销外键
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (Id_P>0)
) --添加列值的限制
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
) --添加默认值
ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES' --表已存在，添加默认值，语法不同
ALTER TABLE Persons
ALTER City DROP DEFAULT --删除默认值
5.15.is null 和is not null
SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NULL --为空
SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NOT NULL --不为空
SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))
FROM Products --？？
SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0))
FROM Products --？？5.16.子查询
select 列 from 表 [where 条件] group by 分组条件 [having 分组后过滤条件];
子查询的执行顺序为先内后外，即先查询括号内的语句，作为子查询的SELECT语句只能检查单个列，否则将报错
在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。
SELECT子句顺序
WHERE 行级过滤，所以无法使用统计count和max等
GROUP BY 分组
HAVING 组(列)级过滤，可以同count、max等聚合函数配合使用
ORDER BY 排序
SELECT cust_id FROM Orders
WHERE order_num IN (SELECT order_num FROM OrderItems WHERE prod_id='rgan') --普通子查询--获取子查询语句获得的数据
SELECT cust_name,cust_concact FROM customers
WHERE cust_id IN(SELECT cust_id FROM Orders WHERE order_num IN (SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN')) --嵌套子查询
SELECT cust_name,cust_state,(SELECT COUNT(*) FROM Orders WHERE Orders.cust_id=Customers.cust_id) AS orders FROM Customs ORDER BY cust_name --子查询语句作为查询列
SELECT vend_id,COUNT(*) as num_prods FROM products
WHERE prod_price >= 4 GROUP BY vend_id HAVING COUNT(*) >= 2 --where过滤行，HAVING过滤列
select count(first_name) from customer,country,address,city where customer.address_id=address.address_id and address.city_id=city.city_id and city.country_id=country.country_id group by country.country having country='china';5.17.GROUP BY 分组
GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。
SELECT vend_id,COUNT(*) AS num_prods FROM products
GROUP BY vend_id #普通分组
SELECT vend_id,COUNT(*) AS num_prods FROM products
GROUP BY vend_id HAVING COUNT(*) >=2 #过滤分组，count数超过2的才显式
SELECT vend_id,COUNT(*) as num_prods FROM products
WHERE prod_price >= 4 GROUP BY vend_id HAVING COUNT(*) >= 2 #where过滤行，HAVING过滤列
SELECT ordrer_num,COUNT(*) AS items FROM products
GROUP BY order_num HAVING COUNT(*)>=3 ORDER BY items,order_num #一般在使用GROUP BY子句时，也应该给出ORDER BY 子集，因为这是保证正确排序的唯一办法5.18.聚合函数(avg(),count(),max(),min(),sum())
SELECT Customer FROM Orders
WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders) --平均值
SELECT COUNT(Customer) AS CustomerNilsen FROM Orders
WHERE Customer='Carter' --统计
SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders --最大值
SELECT MIN(OrderPrice) AS SmallestOrderPrice FROM Orders --最小值
SELECT SUM(OrderPrice) AS OrderTotal FROM Orders --求和5.19.first()
FIRST() 函数返回指定的字段中第一个记录的值。
提示：可使用 ORDER BY 语句对记录进行排序。
SELECT FIRST(OrderPrice) AS FirstOrderPrice FROM Orders5.20.last()
LAST() 函数返回指定的字段中最后一个记录的值。
提示：可使用 ORDER BY 语句对记录进行排序。
SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders5.21.ucase
ucase()函数返回指定的字段中的字符转小写
SELECT UCASE(LastName) as LastName,FirstName FROM Persons5.22.lcase
lcase()函数返回指定字段中的字符转小写
SELECT LCASE(LastName) as LastName,FirstName FROM Persons5.23.mid()文本中提取字符
SELECT MID(column_name,start[,length]) FROM table_name
| 参数 | 描述 |
|---|---|
| column_name | 必需。要提取字符的字段。 |
| start | 必需。规定开始位置（起始值是 1）。 |
| length | 可选。要返回的字符数。如果省略，则 MID() 函数返回剩余文本。 |
SELECT MID(City,1,3) as SmallCity FROM Persons --提取前三个字符,和python不同,以1开始5.24.len()
LEN() 函数返回文本字段中值的长度。
SELECT LEN(City) as LengthOfCity FROM Persons --返回整型5.25.round()四舍五入
SELECT ROUND(column_name,decimals) FROM table_name
| 参数 | 描述 |
|---|---|
| column_name | 必需。要舍入的字段。 |
| decimals | 必需。规定要返回的小数位数。 |
SELECT ProductName, ROUND(UnitPrice,0) as UnitPrice FROM Products5.26.now()当前时间
SELECT ProductName, UnitPrice, Now() as PerDate FROM Products
--返回的结果
ProductName UnitPrice PerDate
gold 32.35 12/29/2008 11:36:05 AM
silver 11.56 12/29/2008 11:36:05 AM
copper 6.85 12/29/2008 11:36:05 AM5.27.format()字段格式化
SELECT FORMAT(column_name,format) FROM table_name
| 参数 | 描述 |
|---|---|
| column_name | 必需。要格式化的字段。 |
| format | 必需。规定格式。 |
SELECT ProductName, UnitPrice, FORMAT(Now(),'YYYY-MM-DD') as PerDate
FROM Products
--结果集类似这样：
ProductName UnitPrice PerDate
gold 32.35 12/29/2008
silver 11.56 12/29/2008
copper5.28{$id}查询上一条记录
select * from table1 where id>20 order by id asc dlimit 1 ; --正序排序,查询一条记录($id)的下一条记录
select * from table1 where id<$id order by id desc dlimit 1; --倒序排序,查询一条记录($id)的上一条记录
select * from exam where linux=(select linux from exam where linux>{$linux} order by linux asc limit 1); --查询上一条记录5.29.limit...offset指定从哪开始返回几行
LIMIT 指定返回的行数，OFFSET指定从哪开始
SELECT * FROM Product LIMIT 5 OFFSET 5 #返回从第5行起的5行数据5.30concat()函数拼接字段
SELECT CONCAT(’My’, ‘S’, ‘QL’);
--> ‘MySQL’
SELECT CONCAT(’My’, NULL, ‘QL’);
--> NULL
SELECT CONCAT(14.3);
--> ‘14.3′5.31TRIM()、LTRIM()、RTRIM()
SELECT LTRIM(' barbar'); --删除左空格
SELECT RTRIM('barbar '); --删除右空格
SELECT TRIM(' bar '); --默认删除前后空格
--> 'bar'
SELECT TRIM(LEADING '，' FROM '，，barxxx'); --删除指定首字符 如'，‘
--> 'barxxx'
SELECT TRIM(BOTH '，' FROM '，，bar，，，'); --删除指定首尾字符
--> 'bar'
SELECT TRIM(TRAILING '，' FROM 'barxxyz，，'); --
--> 'barxxyz'
UPDATE table SET `field`=TRIM(TRAILING ',' FROM `FIELD`) WHERE WHERE `FIELD` LIKE '%,'; 5.32中位数
6.insert into插入数据
INSERT INTO 表名称 VALUES (值1, 值2,....)#全部行赋值
INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)#指定列赋值
INSERT INTO Persons VALUES ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing')
INSERT INTO Persons(LastName, Address) VALUES ('Wilson', 'Champs-Elysees')
INSERT INTO Persons(LastName) SELECT LastName from Peoles --插入检索的数据
SELECT * INTO TableB FROM TableA
INSERT INTO Persons(LastName) VALUES('W'),('A') --插入多行7.update..set修改数据
语法：UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某列
UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson' --单值
UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing' WHERE LastName='Wilson' --多值8.delete from删除数据
语法：DELETE FROM 表名称 WHERE 列名称 = 值
DELETE FROM Person WHERE LastName = 'Wilson' --删除某行
DELETE FROM Person或DELETE * FROM Person --删除所有行9.drop删除表格结构
ALTER TABLE table_name DROP INDEX index_name --删除表索引
DROP TABLE 表名称 --删除表
DROP DATABASE 数据库名称 --删除数据库
10.truncate清除表数据
TRUNCATE TABLE 表名称11.alter修改表结构
11.1添加/删除/修改列
ALTER TABLE table_name
ADD column_name datatype --添加列
ALTER TABLE table_name
DROP COLUMN column_name --删除列
ALTER TABLE table_name
ALTER COLUMN column_name datatype --修改列
ALTER TABLE Persons
ADD Birthday date
ALTER TABLE Persons
ALTER COLUMN Birthday year
ALTER TABLE Person
DROP COLUMN Birthday
--修改列属性(新名同旧名，也能达到修改列名的目的)11.2增加主键
alter table tablename change columnname_old columnname_new type;
--修改列属性
alter table tablename modify columnname newtype
--增加主键
alter table tablename add primary key(column_name)
--ps：增加主键的列不能有重复值，而且主键是惟一的，已存在主键的无法再add;
--表的结构如下:
show create table person;
| person | CREATE TABLE `person` (
`number` int(11) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`birthday` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
--删除列
ALTER TABLE person DROP COLUMN birthday;
--添加列
ALTER TABLE person ADD COLUMN birthday datetime;
--修改列,把number修改为bigint:
ALTER TABLE person MODIFY number BIGINT NOT NULL;
--是把number修改为id,类型为bigint:
ALTER TABLE person CHANGE number id BIGINT;
--添加主键:
ALTER TABLE person ADD PRIMARY KEY (id);
--删除主键:
ALTER TABLE person DROP PRIMARY KEY;
--添加唯一索引:
ALTER TABLE person ADD UNIQUE name_unique_index (`name`);
--为name这一列创建了唯一索引,索引的名字是name_unique_index.
--添加普通索引:
ALTER TABLE person ADD INDEX birthday_index (`birthday`);
--删除索引:
ALTER TABLE person DROP INDEX birthday_index;
ALTER TABLE person DROP INDEX name_unique_index;
--禁用非唯一索引
ALTER TABLE person DISABLE KEYS;
--ALTER TABLE...DISABLE KEYS让MySQL停止更新MyISAM表中的非唯一索引。
--激活非唯一索引
ALTER TABLE person ENABLE KEYS;
--ALTER TABLE ... ENABLE KEYS重新创建丢失的索引。
--把表默认的字符集和所有字符列（CHAR, VARCHAR, TEXT）改为新的字符集:
ALTER TABLE person CONVERT TO CHARACTER SET utf8;
--修改表某一列的编码
ALTER TABLE person CHANGE name name varchar(255) CHARACTER SET utf8;
--仅仅改变一个表的默认字符集
ALTER TABLE person DEFAULT CHARACTER SET utf8;
12.rename重命名/移动
rename table oldtablename to newtablename; --修改表名
rename table olddb.tablename to newdb.tablename; --移动表格到其他数据库
三、mysql的数据类型
在 MySQL 中，有三种主要的类型：文本、数字和日期/时间类型。
Text 类型：
| 数据类型 | 描述 |
|---|---|
| CHAR(size) | 保存固定长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的长度。最多 255 个字符。 |
| VARCHAR(size) | 保存可变长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的最大长度。最多 255 个字符。 注释：如果值的长度大于 255，则被转换为 TEXT 类型。 |
| TINYTEXT | 存放最大长度为 255 个字符的字符串。 |
| TEXT | 存放最大长度为 65,535 个字符的字符串。 |
| BLOB | 用于 BLOBs (Binary Large OBjects)。存放最多 65,535 字节的数据。 |
| MEDIUMTEXT | 存放最大长度为 16,777,215 个字符的字符串。 |
| MEDIUMBLOB | 用于 BLOBs (Binary Large OBjects)。存放最多 16,777,215 字节的数据。 |
| LONGTEXT | 存放最大长度为 4,294,967,295 个字符的字符串。 |
| LONGBLOB | 用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字节的数据。 |
| ENUM(x,y,z,etc.) | 允许你输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。如果列表中不存在插入的值，则插入空值。 注释：这些值是按照你输入的顺序存储的。 可以按照此格式输入可能的值：ENUM('X','Y','Z') |
| SET | 与 ENUM 类似，SET 最多只能包含 64 个列表项，不过 SET 可存储一个以上的值。 |
Number 类型：
| 数据类型 | 描述 |
|---|---|
| TINYINT(size) | -128 到 127 常规。0 到 255 无符号*。在括号中规定最大位数。 |
| SMALLINT(size) | -32768 到 32767 常规。0 到 65535 无符号*。在括号中规定最大位数。 |
| MEDIUMINT(size) | -8388608 到 8388607 普通。0 to 16777215 无符号*。在括号中规定最大位数。 |
| INT(size) | -2147483648 到 2147483647 常规。0 到 4294967295 无符号*。在括号中规定最大位数。 |
| BIGINT(size) | -9223372036854775808 到 9223372036854775807 常规。0 到 18446744073709551615 无符号*。在括号中规定最大位数。 |
| FLOAT(size,d) | 带有浮动小数点的小数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。 |
| DOUBLE(size,d) | 带有浮动小数点的大数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。 |
| DECIMAL(size,d) | 作为字符串存储的 DOUBLE 类型，允许固定的小数点。 |
* 这些整数类型拥有额外的选项 UNSIGNED。通常，整数可以是负数或正数。如果添加 UNSIGNED 属性，那么范围将从 0 开始，而不是某个负数。
Date 类型：
| 数据类型 | 描述 |
|---|---|
| DATE() | 日期。格式：YYYY-MM-DD 注释：支持的范围是从 '1000-01-01' 到 '9999-12-31' |
| DATETIME() | *日期和时间的组合。格式：YYYY-MM-DD HH:MM:SS 注释：支持的范围是从 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59' |
| TIMESTAMP() | *时间戳。TIMESTAMP 值使用 Unix 纪元('1970-01-01 00:00:00' UTC) 至今的描述来存储。格式：YYYY-MM-DD HH:MM:SS 注释：支持的范围是从 '1970-01-01 00:00:01' UTC 到 '2038-01-09 03:14:07' UTC |
| TIME() | 时间。格式：HH:MM:SS 注释：支持的范围是从 '-838:59:59' 到 '838:59:59' |
| YEAR() | 2 位或 4 位格式的年。 注释：4 位格式所允许的值：1901 到 2155。2 位格式所允许的值：70 到 69，表示从 1970 到 2069。 |
* 即便 DATETIME 和 TIMESTAMP 返回相同的格式，它们的工作方式很不同。在 INSERT 或 UPDATE 查询中，TIMESTAMP 自动把自身设置为当前的日期和时间。TIMESTAMP 也接受不同的格式，比如 YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD。
四、存储过程
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
