Mysql数据库基础

2019-03-18 本文已影响0人 E_1fe7

Mysql数据库基础

添加用户

土办法

root@host#   mysql -u root -p 
Enter password: ******
mysql> use mysql;
Database changed

mysql> INSERT INTO user (host, user, password, select_priv, insert_priv, updata_priv) 
VALUES('localhost', 'guest',  PASSWORD('guest123'), 'Y', 'Y', 'Y');

Query OK, 1 row affected (0.20 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 1 row affected (0.01 sec)

tip：在 MySQL5.7 中 user 表的 password 已换成了authentication_string。
tip：password() 加密函数已经在 8.0.11 中移除了，可以使用 MD5() 函数代替。
tip：在注意需要执行 FLUSH PRIVILEGES 语句。这个命令执行后会重新载入授权表。

GRANT 办法

root@host# mysql -u root -p
Enter password:*******
mysql> use mysql;
Database changed

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    -> ON TUTORIALS.*
    -> TO 'zara'@'localhost'
    -> IDENTIFIED BY 'zara123';

连接

使用PHP脚本连接

<?php
$dbhost = 'localhost:3306';  // mysql服务器主机地址
$dbuser = 'root';            // mysql用户名
$dbpass = '123456';          // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
    die('Could not connect: ' . mysqli_error());
}
echo '数据库连接成功！';
mysqli_close($conn);
?>

数据类型

数值类型

类型	大小（字节）	用途
TINYINT	1	小整数值
SMALLINT	2	大整数值
MEDIUMINT	3	大整数值
INT	4	大整数值
BIGINT	8	极大整数值
FLOAT	4	单精度浮点数值
DOUBLE	8	双精度浮点数值

字符串类型

类型	大小（字节）	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535 字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符
BLOB	0-65535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据

tip:详情

基本语法

库

创建数据库
CREATE DATABASE <数据库名称>;
删除数据库
DROP DATABASE <数据库名称>;

表

创建数据表
CREATE TABLE table_name (column_name column_type);
eg：

 CREATE TABLE　IF NOT EXISTS `table_name`(
    `id` INT UNSIGNED AUTO_INCREMENT,
    `title` VARCHAR(100) NOT NULL,
    `author` VARCHAR(40) NOT NULL,
     `date` DATA,
     PRIMARY KEY (`id`)
 )ENGINE=InnoDB DEFAULT CHAREST=utf-8;

tip:详情

删除数据表
DROP TABLE table_name ;

表内数据

插入数据
INSERT INTO table_name (field1, ...) VALUES (value1, ...);
查询数据
SELECT column_name,column_name FROM table_name [WHERE Clause] [LIMIT N][ OFFSET M];
更新数据
UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause];
删除数据
DELETE FROM table_name [WHERE Clause];
LIKE 子句
SELECT field1, ... FROM table_name WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue';
eg:

'%a' //以a结尾的数据
'a%' //以a开头的数据
'%a%' //含有a的数据
'_a_' //三位且中间字母是a的
'_a' //两位且结尾字母是a的
'a_' //两位且开头字母是a的
UNION 操作符
- 描述
  MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。tip:详情
- 语法
```
   SELECT expression1, expression2, ... expression_n
   FROM tables
   [WHERE conditions]
   UNION [ALL | DISTINCT]
   SELECT expression1, expression2, ... expression_n
   FROM tables
   [WHERE conditions];
```
- 参数
  DISTINCT：可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。

MYSQL进阶

排序

描述
通过SELECT获取的数据再使用ORDER BY将数据按照某一字段排序
语法
SELECT field1,... FROM table_name ORDER BY field,... [ASC [DESC]];
- 你可以设定多个字段来排序。
- 使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。默认情况下，它是按升序排列。
- 添加 WHERE...LIKE 子句来设置条件。

分组

描述
GROUP BY 语句根据一个或多个列对结果集进行分组。在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
语法
SELECT column_name, function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;
- function(column_name)可以类比COUNT()。*
示例

mysql> SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;
+--------+----------+
| name   | COUNT(*) |
+--------+----------+
| 小丽 |        1 |
| 小明 |        3 |
| 小王 |        2 |
+--------+----------+
3 rows in set (0.01 sec)

tip:使用SELECT field_name FROM table_name GROUP BY field_name ;去重数据

连接的使用（重要）

描述（过于复杂，详情请见菜鸟教程）
通过JOIN来联合对表格进行删、改、查；
- INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
- LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
- RIGHT JOIN（右连接）：与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
示例
SELECT a.id, a.author, b.title FROM table1 a INNER JOIN table2 b WHERE a.author = b.author;

NULL

描述
在 MySQL 中，NULL 值与任何其它值的比较（即使是 NULL）永远返回 false，即 NULL = NULL 返回false 。
因此使用一下三个方法来判断是否为NULL
- IS NULL: 当列的值是 NULL,此运算符返回 true。
- IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
- <=>: 比较操作符（不同于=运算符），当比较的的两个值为 NULL 时返回 true。
  错误示例：

SELECT * FROM runoob_test_tbl WHERE runoob_count = NULL;
SELECT * FROM runoob_test_tbl WHERE runoob_count != NULL;
正确示例
SELECT * FROM runoob_test_tbl WHERE runoob_count IS NULL;
SELECT * FROM runoob_test_tbl WHERE runoob_count IS NOT NULL;

正则表达式（REGEXP ）

描述
通过正则表达式模糊查询，功能比LIKE更加丰富。详情
示例
- 查找name字段中以‘gdl’开头的所有数据：
  SELECT name FROM table_name WHERE name REGEXP '^gdl';
- 查找name字段中以‘gdl’结尾的所有数据：
  SELECT name FROM table_name WHERE name REGEXP 'gdl$';
- 查找name字段中包含‘gdl’的所有数据：
  SELECT name FROM table_name WHERE name REGEXP 'gdl';

MYSQL高级

事务

多条语句全部执行，保证原子性，不然就回滚事务。

特点

使用了InnoDB引擎的数据库才能使用事务机制
事务用来管理INSERT、UPDATE、DELETE语句
原子性、隔离性（并发操作（锁））、一致性、持久性

语法

BEGIN（START TRANSACTION）开启事务
COMMIT（COMMIT WORK）提交事务
ROLLBACK（ROLLBACK WORK ）事务回滚
SET AUTOCOMMIT=0 禁止自动提交
eg：详情

ALTER命令

对数据表结构进行操作（如修改表名、增加字段）

语法

删除，添加或修改表字段
ALTER TABLE table_name DROP|ADD|MODIFY field_name INT(10)|CHAR(20)|...| NOT NULL DEFAULT 100;

-修改字段默认值
ALTER TABLE table_name ALTER field_name SET DEFAULT 1000;
ALTER TABLE table_name ALTER field_name SET DROP DEFAULT;

-修改引擎和表名
ALTER TABLE table_name ENGINE=MYISAM;
ALTER TABLE table_name RENAME TO new_table_name;

索引

索引是一张表，保存了主键与索引，可以高效查询实例数据。在使用INSERT、UPDATE、DELETE会更新索引表，因此更新速度变慢，同时占用磁盘。

复制表

CREATE TABLE targetTable LIKE sourceTable;
INSERT INTO targetTable SELECT * FROM sourceTable;

序列使用

使用AUTO_INCREMENT来定义
ALTER TABLE table_name AUTO_INCERMENT = 100;

重复数据的处理

表格中设置了PRIMARY KEY与UNIQUE是不会出现重复数据的。

语法
- 统计重复数据
  SELECT COUNT(*) AS repetitions, last_name, first_name FROM table_name GROUP BY last_name, first_name HAVING repetitions>1;
- 过滤重复信息
  SELECT DISTINCT last_name, first_name FROM table_name;
  SLECT last_name,first_name FROM table_name GROUP BY (last_name,first_name);
- 删除重复信息
```
    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;
```
ALTER IGNORE TABLE table_name ADD PRIMARY KEY (last_name, first_name);

导出/导入数据

基本方法

SELECT ... INTO OUTFILE

       SELECT * form table_name 
       INTO OUTFILE "temp/table.sql"
       FIELDS TERMINATED BY ',' OPTIONALLY BY ' " '
       LINES TERMINATED BY '\n';

LOAD DATA INFILE

        LOAD DATA INFILE "temp/table.sql" INTO TABLE table_name;

tips：在写出的时候会出现The MySQL server is running with the --secure-file-priv option so it cannot execute this statement的错误解决方法
出现这个错误是因为没有给数据库指定写出文件的路径或者写出的路径有问题。首先使用下面的命令 show variables like '%secure%'; 查看数据库的存储路径。如果查出的 secure_file_priv 是 null 的时候就证明在 my.ini 文件里面没有配置写出路径。这时候就可以在 mysql.ini 文件的 [mysqld] 代码下增加 secure_file_priv=E:/TEST 再重启 mysql 就可以了。

使用mydump
- 导出SQL格式数据
  mysqldump -u root -p database_name > database.sql
  mysqldump -u root -p --all-databases > database_all.sql
  mysqldump -u root -p database_name table_name > table.sql
- 导入sql格式数据
  mysqldump -u root -p database_name < database.sql
  mysqldump -u root -p database_name table_name < table.sql
- 将指定主机的数据库拷贝到本地
  mysqldump -h gdl.com -P 3306 -u root -p database_name > database.sql

内置函数

COUNT(计数)
SELECT COUNT(*) AS count FROM table_name;
COUNT(配合GROUP BY,计算某个学生所有记录次数)
SELECT a.uid, COUNT(a.uid) FROM record AS a GROUP BY a.uid;
SUM(求和)
SELECT SUM(money) AS all_money FROM table_name;
MAX(最大)
SELECT MAX(money) AS max_money FROM table_name;
MIN(最小)
SELECT MIN(money) AS min_money FROM table_name;

Mysql数据库基础