首页 > 代码库 > mysql数据库基础
mysql数据库基础
MySQL数据库
数据库存储数据结构：
库
Database
表
Table
字段
Colmun
记录
Record
数据类型：
数值型
INT, TINYINT, SMALL INT, MEDIUM INT, BIG INT
INT UNSIGNED
无符号整型
DECIMAL
精确数字
FLOAT
单精度浮点型
DOUBLE
双精度浮点型
字符型
CHAR(5)
2
定长字符
VARCHAR(20)
变长字符
TEXT
ENUM(‘M‘,‘F‘)
枚举
SET(‘M‘,‘F‘)
集合
日期/时间型
DATE
YYYY-MM-DD
TIME
HH:MM:SS
DATETIME
YYYY-MM-DD HH:MM:SS
YEAR
YYYY
TIMESTAMP
时间戳 YYYY-MM-DD HH:MM:SS
特殊属性：
主键
PRIMARY KEY
自动增长
AUTO_INCREMENT （主键+NOT NULL）
NOT NULL
不允许为空
DEFAULT "value"
设置默认值
数据库管理软件：
MySQL, Oracle, Redis, postgreSQL
SQL Server
MariaDB
www.percona.com
# yum install -y mariadb-server
# rpm -qa | grep mariadb
mariadb-devel-5.5.35-3.el7.x86_64
mariadb-libs-5.5.35-3.el7.x86_64
mariadb-bench-5.5.35-3.el7.x86_64
mariadb-server-5.5.35-3.el7.x86_64
mariadb-5.5.35-3.el7.x86_64
mariadb-test-5.5.35-3.el7.x86_64
# systemctl start mariadb
# systemctl enable mariadb.service
# netstat -antp | grep :3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 2243/mysqld
1、登录数据库
# mysql -u <user>
# mysql -u <user> -p
# mysql -u <user> -p -h <Host>
2、设置root用户密码
# mysqladmin -u root -p password "redhat"
Enter password:
库、表管理
数据库管理
1、查看数据库
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.01 sec)
2、创建数据库
MariaDB [(none)]> CREATE DATABASE mydb;
Query OK, 1 row affected (0.01 sec)
3、切换数据库
MariaDB [(none)]> USE mydb
Database changed
4、删除数据库
MariaDB [mysql]> DROP DATABASE test ;
Query OK, 0 rows affected (0.01 sec)
5、导入数据库
# mysql -u root -p < /root/jiaowu.sql
Enter password:
MariaDB [(none)]> SOURCE /root/jiaowu.sql;
数据表管理
1、查看数据表
MariaDB [jiaowu]> SHOW TABLES;
+------------------+
| Tables_in_jiaowu |
+------------------+
| courses |
课表
| scores |
成绩
| students |
学生
| tutors |
教授
+------------------+
4 rows in set (0.01 sec)
2、查看表结构
MariaDB [jiaowu]> DESC tutors\G;
3、创建表
CREATE TABLE tb_name(col1 DataType [property], col2 DataType [property],....)
MariaDB [mydb]> CREATE TABLE mytb(
-> id INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
-> name CHAR(10) NOT NULL,
-> gender ENUM("M","F") NOT NULL,
-> age TINYINT);
4、删除表
MariaDB [jiaowu]> DROP TABLE scores;
Query OK, 0 rows affected (0.01 sec)
5、导入数据表
# mysql -u root -p mydb < /root/a.sql
Enter password:
导出数据库和表：mysqldump
mysqldump -u root -p 库名 表名 > 存放位置和文件名
6、修改表结构-------ALTER TABLE
1)修改表名称
MariaDB [mydb]> ALTER TABLE mytb RENAME game;
Query OK, 0 rows affected (0.01 sec)
2)删除字段
MariaDB [mydb]> ALTER TABLE game DROP age;
3)添加字段
MariaDB [mydb]> ALTER TABLE game ADD age TINYINT NOT NULL;
MariaDB [mydb]> ALTER TABLE game ADD city VARCHAR(20) FIRST;
#将该列置为首列。
MariaDB [mydb]> ALTER TABLE game ADD address VARCHAR(500) AFTER gender;
4)修改字段名称及数据类型
MariaDB [mydb]> ALTER TABLE game CHANGE age NianLing TINYINT;
5)修改字段的数据类型
MariaDB [mydb]> ALTER TABLE game MODIFY address VARCHAR(50);
数据管理
1、添加数据
INSERT INTO tb_name(col1,col2,col3,...) VALUES(value1,value2,value3,....)
MariaDB [mydb]> INSERT INTO game(id,name) VALUES(1,"tom");
MariaDB [mydb]> INSERT INTO game(id,name) VALUES(2,"jerry"),(3,"Mike");
MariaDB [mydb]> INSERT INTO game VALUES(6,"userC","F");
2、更新数据
UPDATE tb_name SET col=new_value WHERE condition
MariaDB [mydb]> UPDATE game SET gender="F" WHERE name="Mike";
3、删除数据
DELETE FROM tb_name WHERE condition
MariaDB [mydb]> DELETE FROM game WHERE gender="M";
Query OK, 4 rows affected (0.03 sec)
数据查询
单表查询
多表查询/连接查询
嵌套查询
单表查询
SELECT col1,col2,col3,... FROM tb_name [select_statement]
MariaDB [jiaowu]> SELECT * FROM tutors;
MariaDB [jiaowu]> SELECT Tname,Age FROM tutors;
设置字段别名
MariaDB [jiaowu]> SELECT Tname AS 姓名, Age AS 年龄 FROM tutors;
子句1：
WHERE condition
算术运算符： =, >, >=, <, <=, !=
逻辑运算符： AND, OR, NOT
MariaDB [jiaowu]> SELECT * FROM tutors WHERE Age > 80;
MariaDB [jiaowu]> SELECT * FROM tutors WHERE Age >= 70 AND Age <= 90;
BETWEEN n AND m
MariaDB [jiaowu]> SELECT * FROM tutors WHERE Age BETWEEN 70 AND 90;
> SELECT Tname,Age,Gender FROM tutors WHERE Tname="YiDeng" OR Tname="HuYiDAO";
IN(value1,value2)
> SELECT Tname,Age,Gender FROM tutors WHERE Tname IN("YiDeng","HuYiDao");
IS NULL/IS NOT NULL
MariaDB [mydb]> SELECT * FROM game WHERE age IS NULL;
MariaDB [mydb]> SELECT * FROM game WHERE age IS NOT NULL;
模糊查询
LIKE "通配符"
通配符：
%：任意长度字符
_：任意单个字符
MariaDB [jiaowu]> SELECT * FROM tutors WHERE Tname LIKE "%ang%";
RLIKE "正则表达式"
MariaDB [jiaowu]> SELECT * FROM tutors WHERE Tname RLIKE "^[HY]";
子句2：
ORDER BY col [ ASC | DESC ]
排序
ASC：升序，默认
DESC：降序
MariaDB [jiaowu]> SELECT * FROM tutors ORDER BY Age;
MariaDB [jiaowu]> SELECT * FROM tutors ORDER BY Age DESC;
子句3：
LIMIT [n,] m
LIMIT m：仅显示前m行
LIMIT n,m：忽略前n行，共显示m行
MariaDB [jiaowu]> SELECT * FROM tutors LIMIT 3;
MariaDB [jiaowu]> SELECT * FROM tutors LIMIT 4,1;
聚合函数：
SUM(col)
AVG(col)
MAX(col)
MIN(col)
COUNT(col)
MariaDB [jiaowu]> SELECT AVG(Age) AS 平均年龄 FROM tutors;
子句4：
GROUP BY col [HAVING condition]
> SELECT AVG(Age) AS 平均年龄,Gender AS 性别 FROM tutors GROUP BY Gender;
> SELECT AVG(Age) AS 平均年龄,Gender AS 性别 FROM tutors GROUP BY Gender HAVING 平均年龄>65 ;
DISTINCT：重复的值只显示一次
> SELECT DISTINCT Gender FROM tutors;
多表查询/连接查询
内连接
外连接
左连接
右连接
前提：多表间存在相关联字段
内连接
相关联字段具有相同的值时才会生成结果
SELECT tb1.col,tb2.col,... FROM tb1 INNER JOIN tb2 ON tb1.col=tb2.col
> SELECT students.name,students.age,courses.Cname FROM students INNER JOIN courses ON students.CID1=courses.CID;
左外连接：
以左表为主，显示左表中所有数据，右表没有相关联数据时，显示为NULL
SELECT tb1.col,tb2.col,... FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col
> SELECT students.name,students.age,courses.Cname FROM students LEFT JOIN courses ON students.CID1=courses.CID;
右外连接
以右表为主，显示右表中所有数据，左表没有相关联数据时，显示为NULL
SELECT tb1.col,tb2.col,... FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col
> SELECT students.name,students.age,courses.Cname FROM courses RIGHT JOIN students ON students.CID1=courses.CID;
嵌套查询/子查询
将一个查询的结果作为另外一个查询数据源、条件使用
> SELECT * FROM tutors WHERE Age > (SELECT AVG(Age) FROM tutors);
> SELECT name,age FROM students WHERE Age IN(SELECT Age FROM tutors);
SELECT a.name,a.age FROM a,b WHERE a.age=b.age ;
用户、权限管理
用户名：
user@host
存放用户的表：
mysql.user
MariaDB [(none)]> SELECT user,host,password FROM mysql.user;
+------+---------------+-------------------------------------------+
| user | host | password |
+------+---------------+-------------------------------------------+
| root | localhost | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| root | server.bj.com | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | server.bj.com | |
| tom | 127.0.0.1 | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
+------+---------------+-------------------------------------------+
创建用户
CREATE USER user@host IDENTIFIED BY "password"
> CREATE USER ‘admin‘@\‘192.168.1.1\‘ IDENTIFIED BY "redhat";
> FLUSH PRIVILEGES;
192.168.1.1
192.168.1.%
%
删除用户
DROP USER user@host
MariaDB [(none)]> DROP USER ‘‘@\‘localhost\‘;
MariaDB [(none)]> DROP USER ‘‘@\‘server.bj.com\‘;
MariaDB [(none)]> FLUSH PRIVILEGES;
使用UPDATE语句修改用户密码
> UPDATE mysql.user SET password=Password("redhat") WHERE user="root";
> FLUSH PRIVILEGES;
使用SET PASSWORD修改密码
MariaDB [(none)]> SET PASSWORD FOR ‘admin‘@\‘192.168.1.1\‘=Password("123456");
MariaDB [(none)]> FLUSH PRIVILEGES;
权限：
1、授权语句
GRANT priv ON db_name.tb_name TO user@host [IDENTIFIED BY password] [WITH GRANT OPTION]
priv权限：
create, drop, alter
select, update, insert, delete
all
db_name.tb_name
jiaowu.tutors
jiaowu.*
*.*
WITH GRANT OPTION：
将权限授予其他用户
> GRANT select,insert ON jiaowu.* TO ‘admin‘@\‘10.1.1.22\‘ IDENTIFIED BY "redhat";
> FLUSH PRIVILEGES;
2、查看用户权限
SHOW GRANTS FOR user@host
> SHOW GRANTS FOR ‘admin‘@\‘10.1.1.22\‘;
+--------------------------------------------------------------------------------------------------------------+
| Grants for admin@10.1.1.22 |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO ‘admin‘@\‘10.1.1.22\‘ IDENTIFIED BY PASSWORD ‘*84BB5DF4823DA319BBF86C99624479A198E6EEE9‘ |
| GRANT SELECT, INSERT ON `jiaowu`.* TO ‘admin‘@\‘10.1.1.22\‘ |
+--------------------------------------------------------------------------------------------------------------+
3、撤销权限
REVOKE priv ON db_name.tb_name FROM user@host
> REVOKE insert ON jiaowu.* FROM ‘admin‘@\‘10.1.1.22\‘;
> FLUSH PRIVILEGES;
本文出自 “肖咏卓的博客” 博客，谢绝转载！
mysql数据库基础