mysql中的操作_MYSQL 常用操作

1.登录到mysql ：mysql -u root -p

2.创建一个数据库：create database 数据库名 [其他选项];

如：create database firstbase character set gbk;

3.show databases; 命令查看已经创建了哪些数据库。

4.要对一个数据库进行操作，必须先选择该数据库，否则会提示错误。

两种方式：

1.在登录数据库时指定，命令：mysql -D 所选择的数据库名 (-h 主机名) -u 用户名 -p

如：mysql -D firstbase -u root -p

2.登录后使用use语句指定，命令： use 数据库名;

5.创建数据库表：create table 表明(列表名);

如：create table student

(

id int unsigned not null auto_increment primary key,

name char(8) ont null,

age tinyint unsigned not null,

);

对于一些较长的语句在命令提示符下可能容易输错, 因此我们可以通过任何文本编辑器将语句输入好后保存为             createtable.sql 的文件中, 通过命令提示符下的文件重定向执行执行该脚本。

打开命令提示符, 输入: mysql -D samp_db -u root -p < createtable.sql

以 "id int unsigned not null auto_increment primary key" 行进行介绍:

"id" 为列的名称;

"int" 指定该列的类型为 int(取值范围为 -8388608到8388607), 在后面我们又用 "unsigned" 加以修饰, 表示该类型        为无符号型, 此时该列的取值范围为 0到16777215;

"not null" 说明该列的值不能为空, 必须要填, 如果不指定该属性, 默认可为空;

"auto_increment" 需在整数列中使用, 其作用是在插入数据时若该列为 NULL, MySQL将自动产生一个比现存值更大的唯        一标识符值。在每张表中仅能有一个这样的值且所在列必须为索引列。

primary key" 表示该列是表的主键, 本列的值必须唯一, MySQL将自动索引该列。

char(8) 表示存储的字符长度为8, tinyint的取值范围为 -127到128, default 属性指定当该列值为空时的默认 值。

6.可以创建一个后缀为.sql的文件名来存放SQL语句，然后使用命令:source 文件路径 ,即可执行。

7.解决乱码问题：在需要中文的变量后加上CHARACTER SET gb2312

如下例子：

create table carList(

number char(60) CHARACTER SET gb2312 not null,

name char(50) CHARACTER SET gb2312,

price float,

year date,

PRIMARY KEY(number)

);

*8.当已经建好表,但是没有设置编码格式,此时可以使用如下命令来改变整张表的编码格式.

alter table table-name  convert to character set gbk;

9.创建后表的修改:

alter table 语句用于创建后对表的修改, 基础用法如下:

9.1

添加列:

基本形式: alter table 表名 add 列名 列数据类型 [after 插入位置];

如:  在表的最后追加列 address:  alter table students add address char(60);

在名为 age 的列后插入列 birthday: alter table students add birthday date after age;

10.CURD:(增删查改操作)

增: insert into 表名 (列名1,列名2) values (值1,值2);

删: delete from 表名 (where ...);

查: select 列名1,列名2(*) from 表名 (where name='');

改: update 表名 set 列名1='',列名2='' (where name='');

一.创建后表的修改:

alter table 语句用于创建后对表的修改, 基础用法如下:

1.添加列:基本形式: alter table 表名 add 列名 列数据类型 [after 插入位置];

如:

在表的最后追加列 address:alter table student add address char(50) not null default '-';

在名为 age 的列后插入列: birthday:alter table student add birthday date not null after age;

2.修改列:基本形式: alter table 表名 change 列名称 列新名称 新数据类型;

如:

将表tel列改名为telphone: alter table student change tel telphone char(13) dafault '-';

将name列的数据类型改为char(16):alter table name name char(16) not null;

3.删除列:基本形式:alter table 表名 drop 列名称;

如:

删除 birthday 列: alter table student drop birthday;

4.重命名表:基本形式:alter table 表名 rename 新表名;

5.删除整张表:基本形式:drop table 表名;

6.删除整个数据库:基本形式:drop database 数据库名;

7.使用 ALTER 来修改字段的默认值

ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;

8.使用 ALTER 命令及 DROP子句来删除字段的默认值

ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

9.查看数据表类型可以使用 SHOW TABLE STATUS 语句。

SHOW TABLE STATUS LIKE 'carlist'\G

10.获取数据表的完整结构:

SHOW CREATE TABLE runoob_tbl \G;

----修改root用户密码:

set password for 用户名@localhost = password('新密码');

例子：mysql> set password for root@localhost = password('123');

二.

1. like子句:

SQL LIKE 子句中使用百分号 %字符来表示任意字符，类似于UNIX或正则表达式中的星号 *。

如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的。

如:SELECT * from runoob_tbl  WHERE runoob_author LIKE '%COM';该句将查到所有以com结尾.

2. UNION 操作符

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。

多个 SELECT 语句会删除重复的数据。

如:

SELECT country FROM Websites UNION (ALL) SELECT country FROM apps ORDER BY country;

如果带all,重复数据不会被去掉. order by 是排序.

3. ORDER BY 子句

可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升序排列。

如: SELECT * from carlist ORDER BY date ASC;

4.  DROP TABLE IF EXISTS `employee_tbl`;

一般放在.sql文件里第一句.

5. JOIN 的使用: 用来在两个或多个表中查询数据.

可以在 SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。

INNER JOIN(内连接,或等值连接)：获取两个表中字段匹配关系的记录。

LEFT JOIN(左连接)：获取左表所有记录，即使右表没有对应匹配的记录。

RIGHT JOIN(右连接)： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

例子:

1. 连接两张表来读取runoob_tbl表中所有runoob_author字段在tcount_tbl表对应的runoob_count字段值

SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a

INNER JOIN

tcount_tbl b ON a.runoob_author = b.runoob_author;

以上SQL语句等价于:

SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a, tcount_tbl b

WHERE

a.runoob_author = b.runoob_author;

2. 以 runoob_tbl 为左表，tcount_tbl 为右表,LEFT JOIN会读取左边数据表的全部数据，即便右边表无对应数据。

SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a

LEFT JOIN

tcount_tbl b ON a.runoob_author = b.runoob_author;

6. NULL 值处理:

MySQL 使用 SQL SELECT 命令及 WHERE 子句来读取数据表中的数据,

但是当提供的查询条件字段为 NULL 时，该命令可能就无法正常工作。

为了处理这种情况，MySQL提供了三大运算符:

IS NULL: 当列的值是 NULL,此运算符返回 true。

IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。

<=>: 比较操作符(不同于=运算符)，当比较的的两个值为 NULL 时返回 true。

关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。

在 MySQL 中，NULL 值与任何其它值的比较(即使是 NULL)永远返回 false，即 NULL = NULL 返回false 。

例子:

查找数据表中 runoob_test_tbl 列是否为 NULL:

SELECT * FROM runoob_test_tbl WHERE runoob_count IS NULL;

SELECT * FROM runoob_test_tbl WHERE runoob_count = NULL;(该句不起作用)

7.MySQL 正则表达式:

MySQL中使用 REGEXP 操作符来进行正则表达式匹配。

1.  ^ :  匹配输入字符串的开始位置。

2.  $ :  匹配输入字符串的结束位置。

3.  . :  匹配除 "\n" 之外的任何单个字符。要匹配包括 '\n' 在内的任何字符，请使用象 '[.\n]' 的模式。

4.  [...] : 字符集合。匹配所包含的任意一个字符。

5.  [^...]: 负值字符集合。匹配未包含的任意字符。

6.  p1|p2|p3 : 匹配 p1 或 p2 或 p3。例如，'z|food'能匹配"z" 或"food"。'(z|f)ood'则匹配"zood"或"food"。

7.  * :  匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}。

8.  + :  匹配前面的子表达式一次或多次。例如，'zo+' 能匹配 "zo" 以及"zoo"，但不能匹配 "z"。+ 等价于 {1,}。

9. {n}:  n 是一个非负整数。匹配确定的n次。例如,'o{2}'不能匹配"Bob"中的 'o'，但是能匹配 "food" 中的两个 o。

10.{n,m} : m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。

例子:

查找name字段中以'st'为开头的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';

查找name字段中以'ok'为结尾的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

查找name字段中包含'mar'字符串的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';

查找name字段中以元音字符开头或以'ok'字符串结尾的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';

8.   MySQL事务主要用于处理操作量大，复杂度高的数据。

比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，

也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务

ROLLBACK 事务回滚

COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

SET AUTOCOMMIT=0 禁止自动提交

SET AUTOCOMMIT=1 开启自动提交

9.  获取服务器元数据:

SELECT VERSION( )    服务器版本信息

SELECT DATABASE( )    当前数据库名 (或者返回空)

SELECT USER( )        当前用户名

SHOW STATUS        服务器状态

SHOW VARIABLES        服务器配置变量

10.重复数据:

例子:

CREATE TABLE person_tbl

(

first_name CHAR(20) NOT NULL,

last_name CHAR(20) NOT NULL,

sex CHAR(10),

PRIMARY KEY (last_name, first_name)

);

1.防止表中出现重复数据:

在MySQL数据表中设置指定的字段为 PRIMARY KEY(主键) 或者 UNIQUE(唯一) 索引来保证数据的唯一性。

2.统计重复数据:

SELECT COUNT(*) as repetitions, last_name, first_name

FROM person_tbl

GROUP BY last_name, first_name

HAVING repetitions > 1;

一般情况下，查询重复的值，请执行以下操作：

确定哪一列包含的值可能会重复。

在列选择列表使用COUNT(*)列出的那些列。

在GROUP BY子句中列出的列。

HAVING子句设置重复数大于1。

3.过滤重复数据:

可以在 SELECT 语句中使用 DISTINCT 关键字来过滤重复数据:

SELECT DISTINCT last_name, first_name FROM person_tbl;

也可以使用 GROUP BY 来读取数据表中不重复的数据：

ELECT last_name, first_name FROM person_tbl GROUP BY (last_name, first_name);

4.删除重复数据:

CREATE TABLE tmp SELECT last_name, first_name,sex FROM person_tbl   GROUP BY (last_name, first_name, sex);

DROP TABLE  person_tbl;

ALTER TABLE tmp RENAME TO person_tbl;