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;
