语法:CREATE DATABASE[IF NOT EXISTS] db_name
sql>CREATE DATABASE mydb1;
sql>CREATE DATABASE IF NOT EXISTS csdn character SET GBK;
sql>CREATE DATABASE mydb3 character SET GBK COLLATE gbk_chinese_ci;
查看当前数据库服务器中的所有数据库
sql>SHOW DATABASES;
查看前面创建的csdn数据库的定义信息
sql>SHOW CREATE DATABASE csdn;
查看服务器中的数据库,并把csdn的字符集修改为utf8;
sql>ALTER DATABASE csdn character SET utf8;
删除
语法:DROP DATABASE[IF EXISTS] csdn;
sql>DROP DATABASE IF EXISTS csdn;
ALTER TABLE user CHANGE name uaername varchar(100);
DML操作
DML是对表中的数据进行增、删、改的操作。 不要与DDL混淆了
主要包括:INSERT、UPDATE、DELENTE
插入操作:INSERT:
语法INSERT INTO 表名(列名1,列名2…) values(列值1、列值2…)
主意:列名与列值的类型、个数、顺序要一一对应。
#一次一列
insert into user(id,username,age,address) values(1,'waking',22,'安徽');
#一次多列
insert into user(id,username,age,address) values(2,'w',22,'北京');
insert into user(id,username,age,address) values(3,'wa',22,'安徽');
insert into user(id,username,age,address) values(4,'wak',22,'杭州');
#一次多列
insert into user(id,username,age,address)
values(2,'w',22,'北京'),(3,'wa',22,'安徽'),(4,'wak',22,'杭州');
修改操作:UPDATE
语法:UPDATE 表名 SET 列名1=列值1,列名2=列值2…WHERE 列名=值
练习:
#将所有学生的年龄修改为25.
update user set age = 25;
#将id为1的地址修改为‘杭州’
update user set address = '杭州' where id=1;
#将姓名为‘w’的学生年龄改为23,地址改为‘河南’
update user set age=23,address='河南' where username = 'w';
#将所有学生的年龄加5岁
update user set age=age+5;
删除操作:DELECT
语法:DELECT FROM 表名 【WHERE 列名=值】
练习
#删除表中姓名为‘w’的记录
delete from user where username='w';
#删除表中所有记录
DELETE FROM user;
#使用truncate删除表中记录。(先把表删除,然后再创建空表)
truncate table user;
#DELETE 删除表中的数据,表结构还在,删除后的数据使用日志可以找回
#TRUNCATE删除是把表直接DROP掉,然后再创建一个同样的新表
#TRUNCATE删除的数据不能找回。执行速度比DELETE快
讲到这里介绍客户端工具
为了更方便用户操作和使用mysql数据库引入客户端工具
SQLyog的使用
Navicat的使用
DQL数据查询
数据库执行DQL语句不会对数据进行改变,而是让数据库发送结果集给客户端
查询返回的结果集是一张虚拟表
查询关键字:SELECT
语法:SELECT 列名 FROM 表名【WHERE–>GROUP BY—>HAVING–>ORDER BY–>LIMIT】
简单查询
#创建表stu
CREATE TABLE stu (
sid CHAR(6),
sname VARCHAR(50),
age INT,
gender VARCHAR(50)
);
#添加数据
INSERT INTO stu VALUES('S_1001', 'liuYi', 35, 'male');
INSERT INTO stu VALUES('S_1002', 'chenEr', 15, 'female');
INSERT INTO stu VALUES('S_1003', 'zhangSan', 95, 'male');
INSERT INTO stu VALUES('S_1004', 'liSi', 65, 'female');
INSERT INTO stu VALUES('S_1005', 'wangWu', 55, 'male');
INSERT INTO stu VALUES('S_1006', 'zhaoLiu', 75, 'female');
INSERT INTO stu VALUES('S_1007', 'sunQi', 25, 'male');
INSERT INTO stu VALUES('S_1008', 'zhouBa', 45, 'female');
INSERT INTO stu VALUES('S_1009', 'wuJiu', 85, 'male');
INSERT INTO stu VALUES('S_1010', 'zhengShi', 5, 'female');
INSERT INTO stu VALUES('S_1011', 'xxx', NULL, NULL);
#创建雇员表
CREATE TABLE emp(
empno INT,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
comm DECIMAL(7,2),
deptno INT );
#添加数据
INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
#创建部门表
CREATE TABLE dept(
deptno INT,
dname VARCHAR(14),
loc VARCHAR(13)
);
#添加数据
INSERT INTO dept VALUES(10, '财务部', 'beijing');
INSERT INTO dept VALUES(20, 'java开发部', 'tianjin');
INSERT INTO dept VALUES(30, '测试部', 'shanghai');
INSERT INTO dept VALUES(40, '销售部', 'shenzheng');
#查询所有列*表示所有列
SELECT * FROM user;
#查询指定列
SELECT id username FROM user;
条件查询
条件查询就是在查询是给出WHERE子句,在WHERE子句中可以使用如下运算符及关键字:
比较运算符
=、!=、<>、<、<=、>、>=;
BETWEEN...AND
IN(set);
IS NULL;
关键运算符
AND
OR
NOT
算术运算符
+ - * / %
查询性别为女,并且年龄小于50的记录
SELECT * FROM stu WHERE gender='female' AND age<50;
查询学号为S_1001,或者姓名为liSi的记录
SELECT * FROM stu WHERE sid='S_1001' OR sname='liSi';
查询学号为S_1001,S_1002,S_1003的记录
SELECT *FROM stu WHERE sid IN('S_1001','S_1002','S_1003');
#等同于
SELECT * FROM stu WHERE sid='S_1001' OR sid ='S_1002' OR sid='S_1003';
查询学生不是S_1001,S_1002,S_1003的记录
SELECT * FROM stu WHERE sid NOT IN('S_1001','S_1002','S_1003');
查询年龄为null的记录
SELECT * FROM stu WHERE age IS NULL;
查询年龄在20到40之间的学生记录
SELECT * FROM stu WHERE age>20 AND age<40;
SELECT * FROM stu WHERE age BETWEEN 20 AND 40;
查询性别非男的学生记录
SELECT * FROM stu WHERE gender!='male';
SELECT * FROM stu WHERE gender<>'male';
SELECT * FROM stu WHERE NOT gender='male';
查询姓名不为null的学生记录
SELECT * FROM stu WHERE sname IS NOT NULL;
SELECT * FROM stu WHERE NOT sname IS NULL;
模糊查询
当想查询姓名中包含a字母的学生时就需要模糊查询了。模糊查询需要使用关键字LIKE
通配符:
_:任意一个字符
%:任意0~n个字符
查询姓名由5个字符构成,并且第5个字符为‘i’的学生记录
SELECT * FROM stu WHERE sname LIKE '____i';
查询姓名以‘z’开头的学生记录
SELECT * FROM stu WHERE sname LIKE 'z%';
#其中'%'匹配0~n个字符
查询姓名中第2个字符为‘i’的学生记录
SELECT * FROM stu WHERE sname LIKE '_i%';
查询姓名中包含‘a’字符的学生记录
SELECT * FROM stu WHERE sname LIKE '%a%';
字段控制查询
去除重复记录DISTINCT
SELECT DISTINCT sal FROM emp;
SELECT DISTINCT sal,comm FROM emp;
查看雇员的月薪与佣金之和
#注意:因为sal和comm两列的类型都是数值类型,所以可以加运算
SELECT *,sal+comm FROM emp;
#comm列有很多记录的值为null,因为任何东西与null相加结果还是null
SELECT *,sal+IFNULL(comm,0) FROM emp;
给列名添加别名
#as
SELECT *,sal+IFNULL(comm,0) AS total FROM emp;
#as可省略
SELECT *,sal+IFNULL(comm,0) total FROM emp;