MySQL练习笔记

1、mysql登录

mysql -u用户名 -p
hadoop001:mysqladmin:/usr/local/mysql:>mysql -uroot -p
Enter password:
输入密码即可进去mysql

在这里大家一定很好奇我的为什么是hadoop001:mysqladmin:/usr/local/mysql:>而不是[root@hadoop001 ~]#

其实这个是我在编译mysql后在mysqladmin用户的环境变量.bash_profile中做了如下配置

PS1=`uname -n`":"'$USER'":"'$PWD'":>"; export PS1

这样当你从root用户切换到mysqladmin用户时就会显示

hadoop001:mysqladmin:/usr/local/mysql:>
当前机器名：当前用户：当前目录>

如此做数据库操作时就可以一眼看出当前目录等信息，避免操作错了目录和文件而造成重大损失。

2、创建用户并指定密码

mysql> create user bigdata identified by '123456';
Query OK, 0 rows affected (0.01 sec)

3、修改密码

mysql> update user set password=password('654321') where user='bigdata';
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql>

bigdata用户的密码由123456变成654321了

4、查看数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| jepsondb           |
| mysql              |
| performance_schema |
| ruozedb            |
| test               |
+--------------------+
6 rows in set (0.11 sec)

5、创建数据库&&(选择)使用数据库

创建数据库
>CREATE DATABASE db_name;　　//db_name为数据库名
>mysql> create database bigdata;
Query OK, 1 row affected (0.00 sec)

使用数据库（先选定你要操作的数据库再操作里面的表）
mysql> use bigdata;
Database changed

6、删除数据库

>DROP DATABASE db_name; //db_name为数据库名

7、创建表

>CREATE TABLE table_name(
>id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,　//id值，无符号、非空、递增——唯一性，可做主键。
>name VARCHAR(60) NOT NULL
>score TINYINT UNSIGNED NOT NULL DEFAULT 0,　//设置默认列值
>PRIMARY KEY(id))
>ENGINE=InnoDB　　//设置表的存储引擎，一般常用InnoDB和MyISAM；InnoDB可靠，支持事务；MyISAM高效不支持全文检索
>DEFAULT charset=utf8;　//设置默认的编码，防止数据库中文乱码

8、复制表

CREATE TABLE tb_name2 SELECT * FROM tb_name;
　或者部分复制：
CREATE TABLE tb_name2 SELECT id,name FROM tb_name;

9、创建临时表：

　>CREATE TEMPORARY TABLE tb_name;

10、查看数据库中可用的表

>SHOW TABLES;
//在此之前要通过use db_name;来选择数据库

11、查看表的结构

>DESCRIBE tb_name;
也可以使用：
>SHOW COLUMNS in tb_name;  

12、删除表

>DROP TABLE IF EXISTS tb_name;

13、表重命名

>RENAME TABLE name_old TO name_new;
还可以使用：
>ALTER TABLE name_old RENAME name_new;

14、修改表

>ALTER TABLE tb_name ADD[CHANGE,RENAME,DROP] ...要更改的内容...
实例：
>ALTER TABLE tb_name ADD COLUMN address varchar(80) NOT NULL;
>ALTER TABLE tb_name DROP address;
>ALTER TABLE tb_name CHANGE score score SMALLINT(4) NOT NULL;

15、插入数据：

>INSERT INTO tb_name(id,name,score)VALUES(NULL,'张三',140),(NULL,'张四',178),(NULL,'张五',134);

这里的插入多条数据直接在后边加上逗号，直接写入插入的数据即可；主键id是自增的列，可以不用写。

插入检索出来的数据：

>INSERT INTO tb_name(name,score) SELECT name,score FROM tb_name2;

16、指定更新数据：

>UPDATE tb_name SET score=189 WHERE id=2;
>UPDATE tablename SET columnName=NewValue [ WHERE condition ]

17、删除数据：

>DELETE FROM tb_name WHERE id=3;
删除id=3的这条数据

18、MySQL的正则表达式：

Mysql支持REGEXP的正则表达式：
>SELECT * FROM tb_name WHERE name REGEXP '^[A-D]'   //找出以A-D 为开头的name

20、分组查询可以按照指定的列进行分组：

>SELECT COUNT(*) FROM tb_name GROUP BY score HAVING COUNT(*)>1;

21、全文检索——MATCH和AGAINST

>SELECT MATCH(note_text)AGAINST('PICASO') FROM tb_name;
InnoDB引擎不支持全文检索，MyISAM可以；

22、视图

创建视图

>CREATE VIEW name AS SELECT * FROM tb_name WHERE ~~ ORDER BY ~~;

视图的特殊作用：

　a、简化表之间的联结（把联结写在select中）；

　b、重新格式化输出检索的数据（TRIM，CONCAT等函数）；

　c、过滤不想要的数据（select部分）

　d、使用视图计算字段值，如汇总这样的值。

23、使用存储过程：

个人理解，存储过程就是一个自定义函数，有局部变量参数，可传入参数，可以返回值

　创建存储过程：

>CREATE PROCEDURE pro(
>IN num INT,OUT total INT)
>BEGIN
>SELECT SUM(score) INTO total FROM tb_name WHERE id=num;
>END;

***这里的  IN (传递一个值给存储过程)，OUT（从存储过程传出一个值），INOUT（对存储过程传入、传出），INTO（保存变量）

存储过程的其他操作：

>SHOW PROCEDURE STATUS;　//显示当期的存储过程
>DROP PROCEDURE pro;　　　//删除指定存储过程