Mysql数据库基础

Mysql数据库操作

一、对数据库的一些操作

(1)查询所有的数据库

SHOW DATABASES;

(2)选择使用数据库

USE 数据库名称;

(3)查询当前正在使用的数据库

SELECT DATABASE();

(4)创建数据库

CREATE DATABASE 数据库名称;  
##当数据库不存在时才进行创建
CREATE DATABASE IF NOT EXISTS 数据库名称;

(5)删除数据库

DROP DATABASE 数据库名称;
##当数据库存在时才进行删除
DROP DATABASE IF EXISTS 数据库名称;

二、对数据库表的相关操作

(1)创建表

CREATE TABLE 表名(
列名1 数据类型1 约束1,
列名2 数据类型2 约束2,
...   ...       ...,

列名n 数据类型n 约束n
);

例子：创建一个userinfo表
CREATE TABLE userinfo(
userid INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(10) NOT NULL,
userage INT,
usersex VARCHAR(2),
usernum VARCHAR(11) UNIQUE
);

(2)对表的操作

a、复制表

##复制userinfo表给userinfoB
CREATE TABLE userinfoB LIKE userinfo;

b、查看某个数据库中的所有的数据表

SHOW TABLES;

c、查看数据表的结构

DESCRIBE 表名;
DESC 表名;

d、修改表名

ALTER TABLE 旧表名 RENAME TO 新表名;
ALTER TABLE userinfo RENAME TO userinfoA;

e、修改表的字符集

ALTER TABLE userinfo CHARACTER SET 字符集名称;

f、添加一列

ALTER TABLE 表名 ADD 列名 数据类型;
ALTER TABLE userinfo ADD userpass VARCHAR(10);

g、删除一列

ALTER TABLE 表名 DROP 列名；
ALTER TABLE userinfo DROP userpass;

h、删除表

DROP TABLE 表名;
DROP TABLE userinfo；

(3)表的约束

a、主键约束：能够唯一确定一张表中的一条记录，我们可以通过给某个字段添加约束，可以使得这个字段不重复，且不为空。

###userid是主键，并且自增
userid INT PRIMARY KEY AUTO_INCREMENT
CREATE TABLE userinfoc (
 	userid INT,
 	username VARCHAR(20),
 	userpassword VARCHAR(20),
 	##联合主键
 	PRIMARY KEY(userid, username)
);

b、唯一约束:unique修饰的字段的值不可以重复

c、非空约束：not null修饰的字段的值不能为空

CREATE TABLE userinfo(
userid INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(10) NOT NULL,
usernum VARCHAR(11) UNIQUE
);

d、默认约束： default 当我们给修饰的字段未赋值时，会自动赋默认值。

create table userinfo(
	userid int primary key auto_increment,
	userage int default 18
	);

e、外键约束

create table 表名(
....
外键列
foreign key (外键列名称) references 主表名称(主表列名称)
);

 ##班级
create table classes(
	id int primary key,
	name varchar(20)
	);	
## 学生表
create table student (
		id	int primary key,
		name varchar(20),
		class_id int,
		foreign key(class_id) references classes(id)
		);
		

(4)向表中插入数据

##列名要和值一一对应
INSERT INTO 表名 (列名1,列名2,...,列名n) VALUES (值1,值2,...值n);
##默认给所有列赋值
INSERT INTO 表名 VALUES(值1,值2,...值n);

(5)删除修改表中数据

##不加where条件删除表中所有数据
DELETE FROM 表名 WHERE 条件;
##删除表中所有数据，推荐使用，效率更高，先删除表，再创建一张一样的表
TRUNCATE TABLE 表名;
##不加where条件修改表中所有数据
UPDATE 表名 SET 列名1=值,列名2=值,... WHERE 条件;

(6)查询数据

6.1全表查询

##查询所有的行和列 *代表显示所有行，顺序为物理结构
SELECT *FROM 表名;
SELECT *FROM userinfo;

6.2筛选部分列

SELECT username,usersex FROM userinfo;

6.3筛选部分行

## =等号   <>  !=不等号   > >=  <  <=   and  or not !
SELECT *FROM userinfo WHERE 筛选条件;
SELECT *FROM userinfo WHERE userage>28;

6.4范围查询

##BETWEEN AND    IN    NOT IN
SELECT *FROM Userinfo WHERE userage BETWEEN 18 AND 40;#查询年龄在18 到 40 之间的人
SELECT *FROM Userinfo WHERE useraddress IN('北京','重庆','西安');#查找地址在北京 重庆 西安的人
SELECT *FROM Userinfo WHERE useraddress NOT IN('北京','重庆','西安');#查找地址不在北京 重庆 西安的人

6.5常量查询

SELECT 8;
SELECT 15,'a';
SELECT *,8 FROM userinfo;
SELECT userid,username,userage+10 FROM userinfo;#年龄加10岁，加号不代表拼接

6.6别名查询（重命名查询）

##AS 
SELECT userid AS'编号',username AS'姓名'FROM userinfo ;

6.7模糊查询

## %
SELECT *FROM userinfo WHERE username LIKE '%张%';##查询名字里面有张的
SELECT *FROM userinfo WHERE username LIKE '张%';##查询姓张的

6.8加密查询

SELECT MD5('aaaa');#只能加密，不能解密

6.9拼接行查询

##union  union all
#这三行是一条语句拼接忽略重复行
SELECT 8,'a'
UNION 
SELECT 9,'b';
#拼接，不忽略重复行
SELECT 8,'a'
UNION ALL
SELECT 8,'a'
UNION ALL
SELECT 9,'b';

6.10排序查询

## order by ,desc ,asc(默认)
SELECT *FROM userinfo ORDER BY userage ASC;#按照年龄升序排序(如果不写默认升序)
SELECT *FROM userinfo ORDER BY userage DESC;#按照年龄降序排序
SELECT *FROM userinfo WHERE usersex='男' ORDER BY userage;#先WHERE后 ORDER BY
SELECT *FROM userinfo ORDER BY userage,userid DESC;#先按照年龄升序排序，如果年龄相同则按照userid值降序排序

6.11唯一查询

SELECT DISTINCT useraddress FROM userinfo;#仅查看userinfo表中不同的useraddress

6.12为空查询

SELECT *FROM userinfo WHERE userAddress IS NULL;#为空查询
SELECT *FROM userinfo WHERE useradddress=NULL;#错误写法
SELECT *FROM userinfo WHERE userAdddress IS NOT NULL;#不为空查询

6.13分页查询（部分行查询）

SELECT *FROM userinfo LIMIT 0,5;#从第零行开始显示5行
SELECT *FROM userinfo LIMIT (page-1)*size,size;#(第几页-1)*每页显示多少个=从第几行开始显示，每页显示多少个
##page：当前页数
##size：每页显示多少行

6.14分组查询函数

SELECT MAX(userage),MIN(userage),SUM(userage),AVG(userage),COUNT(userid),COUNT(userAddress)FROM userinfo;#SUM, AVG只能放数值类型 COUNT会忽略空行

6.15分组查询

SELECT useraddress FROM userinfo GROUP BY useraddress;##按照地址分组，分组之后显示出来
SELECT useraddress,COUNT(*) FROM userinfo GROUP BY useraddress;##按照地址分组，分组之后显示每个组有多少人
SELECT useraddress,COUNT(*)FROM userinfo WHERE userage>38 GROUP BY useraddress;##按照地址分组，分组之后显示年龄大于38岁的每个组的人数
SELECT useraddress,COUNT(*)FROM userinfo WHERE userage>38 GROUP BY useraddress HAVING COUNT(userid)>3;##找出有哪些城市有3人以上年龄都是38岁的
##1.如果使用了分组查询，那么select和from只能显示分组字段和分组函数
##2.先筛选后查询（即先 WHERE 后 GROUP BY）
##3.WHERE分组之前筛选 HAVING分组之后筛选
##4.三者顺序： WHERE,GROUP BY,HAVING 

6.16表连接查询

#6.16.1：内连接 两者之间平等关系如果左表中的数据没有在右表对应则不显示
SELECT 待显示字段 FROM 表A INNER JOIN 表B b ON 连接条件[WHERE 筛选条件];
SELECT *FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;
#6.16.2左外连接 以左表为主右表为辅的一种连接方式如果左表中的数据没有在右表对应显示会用到补全
SELECT 待显示字段 FROM 表A LEFT [OUTER] JOIN 表B b ON 连接条件[WHERE 筛选条件];
SELECT *FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno;

6.17子查询

##一个查询的结果是另一个查询的条件，这个查询就是子查询，外面的查询就是父查询
#查询年龄比“折为成”大的人
SELECT userAge FROM userinfo WHERE userName='折为成';
SELECT *FROM userinfo WHERE userAge>50;

SELECT *FROM userinfo WHERE userAge>(SELECT userAge FROM userinfo WHERE userName='折为成');

#查询地址和“马果树”，“杨平”一样的人

SELECT userAddress FROM userinfo WHERE userName='马果树';
SELECT userAddress FROM userinfo WHERE userName='杨平';

SELECT *FROM userinfo WHERE userAddress=(SELECT userAddress FROM userinfo WHERE userName='马果树'OR userName='杨平');#错误写法
SELECT *FROM userinfo WHERE userAddress IN(SELECT userAddress FROM userinfo WHERE userName='马果树'OR userName='杨平');#推荐用IN
SELECT *FROM userinfo WHERE userAddress=ANY(SELECT userAddress FROM userinfo WHERE userName='马果树'OR userName='杨平');
##1.如果查询了多行内容不能使用关系运算符，需要使用范围运算
##2.子查询的结果不能有多列，只能返回一列
##3.返回结果类型和查询类型一直

6.18 any和all

SELECT *FROM userinfo WHERE userAge>ANY(18,19,28,29);#大于任意一个
SELECT *FROM userinfo WHERE userAge>ALL(18,19,28,29);#大于所有

6.19函数查询

6.19.1数字函数

SELECT ABS (-9),ABS(9);##取绝对值函数
SELECT OCT(10),HEX(10),10##8进制，16进制，10进制
SELECT MOD(9,4);##取余
SELECT FLOOR(6.5),FLOOR(6.4),CEIL(6.5),CEIL(6.4),ROUND(6.5),ROUND(6.4);##向下取整 先上取整 四舍五入取整
SELECT RAND();##取随机数[0,1)
SELECT FLOOR (RAND()*6+1);##求随机数,求1-6的随机数
SELECT BIN(x); #返回x的二进制
SELECT GREATEST(5,6,7,8),LEAST(5,6,7,8);#得到集合中的最大值和最小值
SELECT MOD(6,4);#求余数

6.19.2字符函数

SELECT LENGTH('ni好a'),CHAR_LENGTH('ni好a'),BIT_LENGTH('ni好a');##6所占容量   4字符长度  48字节长度
SELECT CONCAT('a','b','c');##拼接字符串，参数有多个
SELECT CONCAT_WS(',','a','b','c');#拼接字符串给中间加逗号
SELECT REPLACE ('nihao','a','8');##替换将a变成8
SELECT INSERT('asdasdas',2,3'5555');##从第2个开始到第3个字符替换成5555
SELECT SUBSTR('asdasfd',3,4);##截取字符串第三个字符开始一共截取4个
SELECT LEFT('asfsafer',3),RIGHT('asfdasdgf',4);#截取字符串 从左截取3格，从右截取4格 
SELECT INSTR('afssfd','e');##判断字符在目标字符中的位置，从1开始数，不存在返回0
SELECT LOWER ('asd'),UPPER('SAFFA');##变成全大写，全小写
SELECT LTRIM('a  a  '),RTRIM(' a  a '),TRIM(' a  a ');#去左空格，去右空格，去两边空格
SELECT REVERSE('asda');#将字符串反转

6.19.3时间函数

SELECT NOW();##获取当前系统时间
SELECT CURRENT_DATE(), CURRENT_TIME();##分别获取年月日和时分秒
SELECT DATE_ADD(NOW(),INTERVAL 1 YEAR);##增加一年将YEAR及其参数改成其他的年、月、日、时、分、秒则增加多少时间参数为负数则表示几天前
SELECT LAST_DAY(NOW());##一个月的最后一天
SELECT DATE_ADD(NOW(),INTERVAL -3 DAY)#前3天
SELECT LAST_DAY(NOW());#最后一天
SELECT CAST('029' AS SIGNED INTEGER);#将029转换为有符号类型