C/S架构
服务器程序是 mysqld.exe
客户端程序是 mysql.exe
cmd>mysql -uroot -p123456 -h127.0.0.1 -P3306
查看服务器中的所有数据库
mysql> show databases;
数据库以目录的形式保存在服务器
安装目录/data目录
创建新的数据库
mysql> create database company;
切换当前工作数据库
mysql> use company;
查看当前数据库中的所有表
mysql> show tables;
把.sql文件中数据导入数据库
mysql> source d:/mywork/mysql/company.sql;
查看表中的所有数据
mysql> select * from employees;
创建world数据库,并导入 world.sql ,查看表中的数据
mysql> create database world;
mysql> use world;
mysql> source d:/mywork/mysql/world.sql;
服务器 :
数据库1
表1
记录1
记录2
记录3
.....
表2
.....
.....
mysql> use company;
查看当前工作数据库
mysql> select database();
创建表
mysql> create table customer(
id int,
name varchar(20),
age int,
email varchar(50),
gender enum('男', '女')
);
查看表结构
mysql> describe customer;
丢弃表
drop table customer;
插入数据
mysql> insert into customer(
id,
name,
age,
email,
gender
)values (
1,
'张三',
30,
'zhang3@qq.com',
'男'
);
mysql> insert into customer(
id,
name,
age,
email,
gender
)values (
2,
'李四',
30,
'li3@qq.com',
'女'
);
mysql> insert into customer(
id,
name,
age,
email,
gender
)values (
3,
'王五',
30,
'wanbg5@qq.com',
'男'
);
mysql> insert into customer(
id,
name,
age,
email,
gender
)values (
4,
'赵六',
30,
'zhao6@qq.com',
'女'
);
修改数据, 如果没有where 语句会导致修改所有记录
mysql> update customer set
age = 25,
email = 'zhang3@qq.com'
where
id = 1;
删除数据, 如果没有where语句会导致删除所有记录
delete from customer
where id = 3;
C 增 insert
R 删 delete
U 改 update
D 查 select
查看表结构
desc 表名;
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Code | char(3) | NO | PRI | | |
| Name | char(52) | NO | | | |
| Continent | enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') | NO | | Asia | |
| Region | char(26) | NO | | | |
| SurfaceArea | float(10,2) | NO | | 0.00 | |
| IndepYear | smallint(6) | YES | | NULL | |
| Population | int(11) | NO | | 0 | |
| LifeExpectancy | float(3,1) | YES | | NULL | |
| GNP | float(10,2) | YES | | NULL | |
| GNPOld | float(10,2) | YES | | NULL | |
| LocalName | char(45) | NO | | | |
| GovernmentForm | char(45) | NO | | | |
| HeadOfState | char(60) | YES | | NULL | |
| Capital | int(11) | YES | | NULL | |
| Code2 | char(2) | NO | | | |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
desc city;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | MUL | | |
| District | char(20) | NO | | | |
| Population | int(11) | NO | | 0 | |
+-------------+----------+------+-----+---------+----------------+
desc countrylanguage
+-------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| CountryCode | char(3) | NO | PRI | | |
| Language | char(30) | NO | PRI | | |
| IsOfficial | enum('T','F') | NO | | F | |
| Percentage | float(4,1) | NO | | 0.0 | |
+-------------+---------------+------+-----+---------+-------+
select * from country;
select
code,
name,
population
from
country;
select
population,
name,
code
from
country;
SQL注意 :
SQL 语言大小写不敏感。
SQL 可以写在一行或者多行
关键字不能被缩写也不能分行
各子句一般要分行写。
使用缩进提高语句的可读性。
--给列起别名, 可以省略as关键字, 别名中如果有特殊符号, 可以使用""包围.
select
population as pop,
name "国家 名称",
code 编号
from
country;
查询国家代码, 名称, 国土面积, 首都列, 要求列起别名.
select
code 国家编号,
name 国家名称,
SurfaceArea 国土面积,
Capital 首都
from
country;
where 条件布尔 行过滤器
执行顺序 : 先from, 再where 最后select
SELECT employee_id, last_name, job_id, department_id
FROM employees
WHERE department_id = 90 ;
SELECT
employee_id empId,
last_name name,
job_id job,
department_id deptId
FROM
employees
WHERE
department_id = 90 ;
-- 错误!! where中不可以使用列的别名, 因为此时虚表的列还没有生成好.
SELECT
employee_id empId,
last_name name,
job_id job,
department_id deptId
FROM
employees
WHERE
deptId = 90 ;
SELECT last_name, salary
FROM employees
WHERE salary <= 3000;
查询人口总数大于1亿的国家的名称和大洲及人口, 给人口列起别名.
select
name 国家,
population 人口,
continent 洲
from
country
where
population > 100000000;
Between a and b 都包含
SELECT last_name, salary
FROM employees
WHERE salary BETWEEN 2500 AND 3500; -- 相当于 salary >= 2500 and salary <= 3500
SELECT employee_id, last_name, salary, manager_id
FROM employees
WHERE manager_id IN (100, 101, 201); -- 相当于 manager_id = 101 or manager_id = 100 or manager_id = 201
查询所有的亚洲或欧洲国家的名称和大洲及国家代码.
select
name,
code,
continent
from
country
where
continent In('Asia','Europe');
% 表示任意个任意字符
select
code,
name,
continent
from
country
where
name like '%na%';
_ 表示一个任意字符
select
code,
name,
continent
from
country
where
name like '__na%';
查询人均寿命小于50岁的国家
select
name,
LifeExpectancy
from
country
where
LifeExpectancy < 50;
查询城市名称包含ek的城市 %ek%
select
name
from
city
where
name like '%ek%';
查询姓名中第2个字母是o其他无所谓
SELECT last_name
FROM employees
WHERE last_name LIKE '_o%';
查询哪些国家没有首都
--错误!!
select
name,
continent,
capital
from
country
where
capital = null;
只要有null参与比较运算, 结果一定是false
select
code,
name,
continent,
capital
from
country
where
capital is null;
查询哪些国家有首都
select
code,
name,
continent,
capital
from
country
where
capital is not null;
查询哪些国家没有独立
select
code,
name,
continent,
indepYear,
capital
from
country
where
indepYear is null;
查询员工中(工资大于5000的,且部门号是90的)或者名字包含s的
select
last_name,
department_id 部门编号,
salary
from
employees
where
salary > 5000
and
department_id = 90
or
last_name like '%s%';
查询所有亚洲国家 (人口大于1亿或者尚未独立)的国家, 的代码,名称和大洲及人口
and 并且
or 或者
select
code,
name,
indepYear,
continent,
population pop
from
country
where
continent = 'Asia'
and
(population > 100000000
or
indepYear is n