mysql数据库表操作
查看表的结构
mysql> desc / describe 表名;
或者:
mysql> show create table 表名;
常见数据库引擎: innodb, myISAM...
删除表
mysql> drop table [if exists] 表名 ;
表(结构)的修改
一般语法格式: alter table 表名 操作方式 数据
1. 修改表名称
mysql> alter table 表名 rename newtb;
2. 添加字段
mysql> alter table 表名 add 字段名 字段类型; [位置];
位置: 增加到最后
first:增加到第一列
after 已有字段: 增加到指定字段之后;
3. 修改字段名称:
mysql> alter table 表名 change 旧字段名 新字段名 字段类型;
4. 修改字段类型:
mysql> alter table 表名 modify 字段名 字段类型;
5. 修改字段顺序:
mysql> alter table 表名 modify 字段名 字段类型 位置;
位置:
first:增加到第一列
after 已有字段: 增加到指定字段之后;
删除字段
mysql> alter table 表名 drop 字段名 ;
设置约束
1) 设置 primary key / unique key
mysql> alter table 表名 add primary key / unique key (字段名) ;
2) 设置 not null 和 auto_increment
mysql> alter table 表名 modify 字段名 字段类型 not null / auto_increment;
3) default 约束
mysql> alter table 表名 alter 字段名 set default '默认值';
4) 外键约束
mysql> ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY(字段) REFERENCES 主表名(主键列) [ON DELETE 参数]
取消约束
1) 取消 primary key / foreign key
mysql> alter table 表名 drop primary key / foreign key;
2) 取消 unique key
mysql> alter table 表名 drop index 字段名;
3) 取消 default 约束
mysql> alter table 表名 alter 字段名 drop default;
4) 取消 not null 和 auto_increment
mysql> alter table 表名 modify 字段名 字段类型;
修改表中字段的字符集
mysql> alter table 表名 convert to character set utf8;
数据的操作
1. 数据增加
SQL 语句的语法格式:
insert into 表名 [(字段1,字段2...)] values (值11,值12...)[,(值21,值22...)];
2. 数据删除
SQL 语句的语法格式:
delete from 表名 where 条件;
特别注意:数据删除时一定要指定条件,否则会删除所有数据;
条件如何构建:
MYSQL 提供了关系运算符和逻辑运算符: > < != = >= <= && || ! xor
3. 数据修改
SQL 语句的语法格式:
update 表名 set 字段1 = 值1,字段2 = 值2 where 条件;
4. 数据查询 (单表查询)
4.1 查看所有数据:
mysql> select * from 表名;
4.2 查看指定字段数据:
mysql> select 字段名1,字段名2... from 表名;
4.3 避免重复查询:
mysql> select distinct 字段名 from 表名;
4.4 空值查询
mysql> select 字段名 from 表名 where 字段 = NULL; error
mysql> select 字段名 from 表名 where 字段 is NULL; OK
4.5 条件查询
mysql> select 字段名,... from 表名 where 条件;
mysql> select 字段名,... from 表名 where 条件1 and 条件2;
4.6 范围查询
mysql> select 字段名,... from 表名 where 字段 between 值1 and 值2;
mysql> select 字段名,... from 表名 where 字段 not between 值1 and 值2;
多表连接查询
1.内连接
又称为简单连接,或者自然连接,是一种常见的连接查询,内连接使用比较运算符,对两个表中的数据,进行比较,并列出与连接条件匹配的数据行,组合成新的记录。在内连接查询中,只有满足条件的记录,才会出现在查询结果中简单理解:返回表1和表2同时满足条件的记录。
语法格式
SELECT 查询字段 FROM 表1[INNERIJOIN 表2 ON 表1.关系字段= 表2.关系字段;
例子:
两个表联合查询:
select 字段名... FROM 表1 INNERJOIN 表2 ON 条件
多个表查询时,可使用:
select 字段名...FROM 表1 INNERJOIN 表2 ON 条件
INNERJOIN 表3 ON 条件
2.外连接:
内连接查询,返回的结果,只包含符合查询条件和连接条件的数据有时需要返回的查询结果,不仅包含符合条件的数据,而且,包括左表和右表中的所有数据,此时,需要使用外连接。
左外连接
以左边的表为主,在右边的表中找到所有满足条件的元素,并把他们连接起来,如果没有对应的元素,则在相应位置上的值为null。
select 字段名... FROM 表1 LEFT JOIN 表2 ON 条件;
右外连接
与左外连接相反,以右边的表为主,在左边的表中找到所有满足条件的元素,并把他们连接起来,如果没有对应的元素,则在相应位置上的值为nuIl。
select 字段名... FROM 表1 RIGHT JOIN 表2 ON 条件;
SQL语句的执行顺序
子查询
子查询是指一个查询语句嵌套在另一个查询语句内部的查询,在select子句中先计算子查询,子查询结果作为外层另一个查询的条件,查询可以基于个表或者多个表.子查询中常用的操作符有ANY(SOME),ALL,IN,EXISTS,子查询可以添加到select.update.delete语句中,而且可以进行多层嵌套。子查询也可以使用比较运笪符如“<,>,<=,>=,!=,=”等
1.带有ANY(SOME)关键字的子查询:
它允许创建一个表达式,对子查询的返回值列表,进行比较,只要满足内层子查询中的任意一个比较条件,就返回一个结果作为外层查询条件。
select 字段名...FROM 表 where 字段 > ANY (select字段名..... FROM 表)
2.带有ALL关键字的子查询:
ALL与ANY不同,表示同时满足所有内层查询的条件
select 字段名... FROM 表where 字段>ALL(select 字段名...FROM表);
3.带有EXISTS关键字的子查询:
EXISTS后面的参数,可以是任意一个子查询,这个子查询的作用相当于测试,它不产生任何数据,只返回TRUE或FALSE.当返回值为TRUE时,外层的查询才会执行,
select字段名... FROM表where EXISTS (select字段... FROM 表);
只有在 (select字段... FROM 表)部分的子查询存在结果时,才去执行外层的查询
4.带有IN关键字的子查询:
进行子查询时,内层查询语句返回一个数据列,这个使用IN关键字,数据列中的值,将供外层查询语句进行比较操作
select 字段名... FROM 表where 字段IN (select 字段名... FROM 表);
5.带有比较运算符的子查询:
子查询中,可以使用比较运算符,比如<、>、<=、>=、=、!=等
select 字段名... FROM表where字段=(select字段... FROM 表);
请注意如果用=比较运算符,子查询的结果往往是唯一的
mysqlCAPI接口函数
函数 | mysql_init |
头文件 | #include<mysql/mysql.h> |
函数原型 | MYSQL *mysql_init(MYSQL *mysql) |
功能 | 分配或初始化MYSQL对象。 |
参数 | mysql:若非空,将初始化参数代表的连接对象; 如果mysql是NULL指针,该函数将分配、初始化、并返 回新对象。否则,将初始化对象,并返回对象的地址。如果 mysql_init()分配了新的对象,当调用mysql_close()来关闭 数据库连接时。将一起释放该对象。 |
返回值 | MYSQL*句柄。如果无足够内存以分配新的对象,返回NULL。 |
函数 | mysql_close |
头文件 | #include<mysql/mysql.h> |
函数原型 | void mysql_close(MYSQL *mysql) |
功能 | 关闭MYSQL连接对象。 |
参数 | 1.mysql:关闭前面打开的连接。 |
返回值 | 无 |
函数 | mysql_real_connect() |
头文件 | #include<mysql/mysql.h> |
函数原型 | MYSQL *mysql_real_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd, const char *db, unsigned int port, const char *unix_socket, unsigned long client_flag) |
功能 | 连接数据库。 |
参数 | 1.mysql: mysql连接对象, 2.host: 主机名 3.user: 用户名 4.passwd: 用户密码 5.db: 数据库名称 ,设为NULL,代表连接MYSQL默认库 6.port: 数据库TCP/IP连接端口 一般设为0; 7.socket: 套接字或命名管道 一般设为NULL, 8.client_flag, 如果设为CLIENT_MULTI_STATEMENTS.表明可支持一 次执行多 条SQL语句 (用;分开),但多语句执行时需要利用 mysql_next_result 处理 上一条语句是否产生结果集,若上一条语句生成结果集,结果集必须经过释 放才能继续执行下一条SQL语句。这在多语句执行时尤为重要。 通常为0未设置只能支持单语句执行。 |
返回值 | 如果连接成功,返回MYSQL*连接句柄。如果连接失败,返回NULL。对于成功的连接,返回值与第1个参数的值相同 |
函数 | mysql_querymysql_real_query |
头文件 | #include<mysql/mysql.h> |
函数原型 | int mysql_query(MYSQL *mysql, const char *query) int mysql_real_query(MYSQL *mysql, const char *query, unsigned long length) |
功能 | 执行由“query”指向的SQL语句。mysql_query()不能用于包含二进制数据的查询,应使用mysql_real_query()取而代之(二进制数据可能包含字符‘\0’,mysql_query()会将该字符解释为查询字符串结束) |
参数 | 1.mysql:mysql连接对象, 2.query:需查询的SQL 语句 3.length:SQL 语句字符长度 |
返回值 | 如果查询成功,返回0。如果出现错误,返回非0值。 |
函数 | mysql_next_result |
头文件 | #include<mysql/mysql.h> |
函数原型 | int mysql_next_result(MYSQL *mysql) |
功能 | mysql_next_result 读取下一个语句结果,并返回一个状态以指示是否存在更多结果,常用于多语句执行时使用; 在每次调用mysql_next_result之前,如果当前语句是返回结果集(而不仅仅是结果状态)的语句,则必须为其调用mysql_free_result 释放结果集。 调用mysql_next_result之后,连接的状态就为您准备好了下一条语句的调用; |
参数 | 1.mysql:mysql连接对象, |
返回值 | 执行成功: 返回0 代表仍存在更多结果集。 返回-1 代表不存在更多结果集。 如果出现错误,返回 >0 值。 |
函数 | mysql_store_result |
头文件 | #include<mysql/mysql.h> |
函数原型 | MYSQL_RES *mysql_store_result(MYSQL *mysql) |
功能 | 存储查询结果集 |
参数 | 1.mysql:mysql连接对象, |
返回值 | 具有多个结果的MYSQL_RES结果集合。如果出现错误,返回NULL。 |
函数 | mysql_num_fields |
头文件 | #include<mysql/mysql.h> |
函数原型 | unsigned int mysql_num_fields(MYSQL_RES *result) |
功能 | 检索结果集的字段数量 |
参数 | 1.result:指向结果集的指针, |
返回值 | 表示结果集中字段数量的无符号整数。 |
函数 | mysql_fetch_fields |
头文件 | #include<mysql/mysql.h> |
函数原型 | MYSQL_FIELD *mysql_fetch_fields(MYSQL_RES *result) |
功能 | 检索结果集的字段 |
参数 | 1.result:指向结果集的指针, |
返回值 | 关于结果集所有列的MYSQL_FIELD结构的数组,每个结构提供了结果集中1列的字段定义信息 |
函数 | mysql_num_rows |
头文件 | #include<mysql/mysql.h> |
函数原型 | my_ulonglong mysql_num_rows(MYSQL_RES *result) |
功能 | 检索结果集中记录的数量 |
参数 | 1.result:指向结果集的指针, |
返回值 | 表示结果集中记录的数量。 |
函数 | mysql_fetch_row |
头文件 | #include<mysql/mysql.h> |
函数原型 | MYSQL_ROW mysql_fetch_row(MYSQL_RES *result) |
功能 | 检索结果集的一条记录 |
参数 | 1.result:指向结果集的指针, |
返回值 | 返回结果集中表示一条记录的MYSQL_ROW结构。同时该函数会将结果集内部的用于指向当前行的游标移动到下一条记录处,如果没有更多要检索的行或出现了错误,返回NULL。 |
函数 | mysql_data_seek |
头文件 | #include<mysql/mysql.h> |
函数原型 | void mysql_data_seek (MYSQL_RES *result, my_ulonglong offset) |
功能 | 移动结果集中的游标到指定记录 |
参数 | 1.result:指向结果集的指针, 2. offset: 相对于第一条记录的偏移量 |
返回值 | 无 |
函数 | mysql_free_result |
头文件 | #include<mysql/mysql.h> |
函数原型 | void mysql_free_result(MYSQL_RES *result) |
功能 | 检索结果集的字段记录数量 |
参数 | 1.result:指向结果集的指针, |
返回值 | 无 |
函数 | mysql_set_character_set |
头文件 | #include<mysql/mysql.h> |
函数原型 | int mysql_set_character_set(MYSQL *mysql, const char *csname) |
功能 | 为当前连接设置默认的字符集, |
参数 | 1.mysql:MYSQL的连接对象, 2.csname:指定了1个有效的字符集名称 (例:“utf8”或者“latin1”) |
返回值 | 成功:0 失败:非零值 |
函数 | mysql_character_set_name |
头文件 | #include<mysql/mysql.h> |
函数原型 | const char *mysql_character_set_name(MYSQL *mysql) |
功能 | 为当前连接返回默认的字符集, |
参数 | 1.mysql:MYSQL的连接对象 |
返回值 | 默认的字符集名称,该函数不会失败。 |
函数 | mysql_error mysql_errno |
头文件 | #include<mysql/mysql.h> |
函数原型 | const char *mysql_error(MYSQL *mysql) unsigned int mysql_errno(MYSQL *mysql) |
功能 | 获取错误信息与错误码 |
参数 | 1.mysql:mysql连接对象, |
返回值 | mysql_error 返回错误信息 mysql_errno 返回错误码 |