MySQL基础知识

MySQL  必知必会

 关系型数据库：
MySQL,ORACLE,SQL Server,IBM DB2,PostgreSQL

NoSQL数据库：
mongoDB,redis

MySQL的组成

数据库
表
视图
存储过程
触发器

MySQL的默认端口号：3306

 

 使用命令行操作数据库：

连接到数据库：

写道

mysql -uroot -proot [-h127.0.0.1]

 

断开数据库：

写道

exit
quit

 

显示所有数据库：

写道

show databases;

 

切换到mydb数据库：

写道

use mydb;

 

查看数据库中所有表：

写道

show tables;

 

查看表结构：

写道

desc t_user;

 

查看数据库版本和时间：

写道

select version(),now();

 

创建数据库：

写道

create database db001;

 

创建表：
创建表之前我们需要了解MySQL提供的数据类型
1整型
tinyint
smallint
mediumint
int(常用)
bigint
2浮点型
float
double
decimal(m,d)
3字符型
char
varchar(常用)
tinytext
text(常用)
mediumtext
longtext
4日期时间型
date
time
datetime
timestamp(常用)

下面我们创建表：

create table t_student( 
stuname varchar(20),
stuage int,
stuaddress varchar(100)
);

 

插入数据：

insert into t_student
(stuname,stuage,stuaddress)
values
('tom',23,'郑州');

 

查询表中数据：

select * from t_student;

 

主键：
在设计表时总是要定义表的主键
表的主键设计策略：
任意两行都不具备相同的主键值
每行都必须具有一个主键值（主键不允许null）
主键和业务无关，不更改，不重用
主键可以是一个列或者多个列的组合
使用primary key(**)来声明一个主键列
使用primary key(**,**)来声明组合主键

设计表时添加主键：

create table t_student(
id int,
stuname varchar(20),
stuage int,
primary key(id)
);

 

或者

create table t_student(
id int primary key,
stuname varchar(20),
stuage int
);

 

设置主键的自动增长：auto_increment

create table t_student(
id int auto_increment primary key,
stuname varchar(20),
stuage int
);

 

注意：一个表中只允许有一个自动增长列

创建表的时候除了可以添加主键约束外还可以给列添加一些其他约束

非null约束：

create table t_student(
id int primary key,
stuname varchar(20) not null,
stuage int
);

 

默认约束：

create table t_student(
id int primary key,
stuname varchar(20) default 'tom',
stuage int
);

 

唯一约束：

create table t_student(
id int primary key,
stuname varchar(20) not null unique,
stuage int
);

 

问题：
如果我创建表的时候，没有添加约束或表创建的有问题需要修改怎么办呢？

添加一列：

alter table t_student add tel char(20);

 

删除一列：

alter table t_student drop column tel;

 

添加唯一约束：

alter table t_student add constraint uk_stuname unique(stuname);

 

添加主键约束：

alter table t_user add constraint pk_t_user_id primary key(id);

 

添加默认约束：

alter table t_user alter password set default '000000';

 

添加非null约束：

alter table t_user modify column username varchar(20) not null;

 

重命名表：

rename table t_student to t_stu;

 

删除表：

drop table t_student;

 

导出数据库：

mysqldump -hlocalhost -uroot -proot mydb>c:/mydb.sql

 

导入数据库：

source c:/mydb.sql

 

上面介绍了数据库及表的结构操作，下面介绍更加常用的insert,update,select,delete操作


插入多条数据：

insert into t_student
(stuname,stuage,stuaddress)
values
('tom',23,'郑州')，（'tom',23,'郑州'）;

 

注意：
参数数量要匹配，如果有默认值可以使用default来传值

insert into t_student
(stuname,stuage,stuaddress)
values
('tom',23,default)，（'tom',23,'郑州'）;

 

更改数据：

update t_student 
set
stuname='alex',age=26 
where id=1;

 

删除数据：

delete from t_student where id=1;

 

删除整个表数据：
1速度快

truncate table t_student;

 
2速度较慢

delete from t_student;

 

查询：

select id,username,password from t_student where id=1;

 

去重查询：

select distinct username from t_user;

 

分页查询：

select * from t_user limit 5;

 
--等价于limit 0,5；从表的第一行（行0）开始查5个；即ID为1，2，3，4，5的

select * from t_user limit 5,5;

 
--从第六行（行5）开始查5个，ID为6，7，8，9，10的

排序（降序）：

select * from products order by prod_price desc;

 

排序（升序）：

select * from products order by prod_price asc;

 

多列排序：

select * from products order by prod_price asc,prod_name desc;

 

过滤查询：
查询产品价格在2到10之间的

select * from products 
where 
prod_price>=2 and prod_price <=10;

select * from products
where 
prod_price between 2 and 10;

 

查询产品价格不等于2.5的所有产品

select * from products where prod_price <> 2.5;

select * from products where prod_price != 2.5;

 

查询没有电子邮件信息的客户

select * from customers where cust_email is null;

select * from customers where cust_email is not null;

 

查询不是由供应商1001和1003制造的商品

select * from products where vend_id not in('1001','1003');

 

模糊查询：

select * from products where prod_name like '_ton%';

 

注意：
不要过度使用like进行查询，效率会非常慢，如果必须使用，则把like放到查询的最后。

别名查询：

select vend_id as '供应商编号' from products;

 

算术运算查询：

select quantity,item_price,quantity*item_price as '总价' from orderitems;

 

文本处理函数：

select prod_name,left(prod_name,2) from products;
select prod_name,right(prod_name,5) from products;

select prod_name,length(prod_name) from products;

select prod_name,lower(prod_name) from products;
select prod_name,upper(prod_name) from products;

select prod_name,ltrim(prod_name) from products;
select prod_name,rtrim(prod_name) from products;
select prod_name,trim(prod_name) from products;

select concat('I love ',prod_name) from products;

 

日期处理函数：

SELECT CURDATE();
SELECT CURTIME();
SELECT NOW();
SELECT DATE(NOW());
SELECT TIME(NOW());
SELECT YEAR(CURDATE());
SELECT MONTH(CURDATE());
SELECT DAY(CURDATE());
SELECT DAYOFWEEK(NOW());
SELECT HOUR(CURTIME());
SELECT MINUTE(TIME(NOW()));
SELECT SECOND(TIME(NOW()));
SELECT DATEDIFF(CURDATE(),'2005-5-5');
SELECT ADDDATE(CURDATE(),10);

 

聚合函数：

SELECT MAX(prod_price) FROM products;
SELECT MIN(prod_price) FROM products;
SELECT COUNT(*) FROM products;
SELECT SUM(prod_price) FROM products;
SELECT AVG(prod_price) FROM products;

 

分组统计：

SELECT vend_id,COUNT(*) FROM products GROUP BY vend_id HAVING COUNT(*) > 2;

 

HAVING语句用于GROUP BY的过滤
WHERE用于分组前的过滤

查询语句的顺序：

写道

select
from
where
group by
having
order by
limit

 

子查询：

SELECT cust_name FROM customers WHERE cust_id  
IN (SELECT cust_id FROM orders WHERE order_num  
IN(SELECT order_num FROM orderitems WHERE prod_id = "TNT2") 
);

 

等值查询：

SELECT ts.id AS 'stuid',stu_name,tc.id AS 'class_id',class_name 
FROM t_student AS ts,t_class AS tc 
WHERE ts.class_id = tc.id;

 

内联接查询：

SELECT ts.id AS 'stuid',stu_name,tc.id AS 'class_id',class_name 
FROM t_student AS ts 
INNER JOIN t_class AS tc 
ON ts.class_id = tc.id ;

 

左外联接：

SELECT ts.id AS 'stuid',stu_name,tc.id AS 'class_id',class_name 
FROM t_student AS ts 
LEFT JOIN t_class AS tc 
ON ts.class_id = tc.id ;

 

右外联接：

SELECT ts.id AS 'stuid',stu_name,tc.id AS 'class_id',class_name 
FROM t_student AS ts 
RIGHT JOIN t_class AS tc 
ON ts.class_id = tc.id ;

 

组合查询：

SELECT id,name,createtime FROM t_user 
UNION 
SELECT id,name,createtime FROM t_company;

 

数据库引擎：
InnoDB:可靠的事务处理引擎，不支持全文搜索
MyISAM:是一个性能极高的引擎，支持全文，但是不支持事务处理
MEMORY:功能同MyISAM，但是数据存储在内存，速度极快

create table xxx(
    .....
)engine=innodb;