基础01-安装与基础查询
一，卸载与安装mysql数据库管理系统，具体操作可以去网上搜索，也可以直接去尚硅谷看视频（提供的是安装版的）。
如果你选用的是官网的免安装版(解压版)。进行如下操作。
解压MySQL-5.7.21-win32.zip（除了my.ini外，其他文件都直接从压缩包内解压出来）。
配置文件my.ini里有各种参数（以后更改之后记得重启服务）。
[mysqld] #port 设置端口 ,默认3306 可不指明 #basedir 设置Mysql的安装目录 #datadir 设置mysql数据库的数据的存放目录 port = 3309 basedir=E:\myServer\mysql\ datadir=E:\myServer\mysql\data # 允许最大连接数 max_connections=200 # 服务端使用的字符集默认为8比特编码的latin1字符集 character-set-server=utf8 # 创建新表时将使用的默认存储引擎 default-storage-engine=InnoDB #开启查询缓存以避免 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated explicit_defaults_for_timestamp=true [client] #cmd.exe mysql命令行所用端口 port = 3309 default-character-set=utf8 #https://dev.mysql.com/doc/refman/5.7/en/connecting.html
以管理员身份进入命令行cmd.exe执行安装和初始化
语句解释如下，默认路径为E:\myServer请根据实际修改 # 1 [跳转到MySQL主进程mysqld目录] E: cd E:\myServer\mysql\bin # 2 [加入windows服务,自定义名字MySQL3309] mysqld -install MySQL3309 #3 [初始化将生成数据存放文件夹data，生成无密码root帐号] mysqld --initialize-insecure # 4 [启动MySQL服务] net start MySQL3309 # 5 通过命令行,进入mysql控制台。默认密码为空，回车即可 mysql -u root -p #-------------------------------------------- # [卸载服务(未用到)] net stop MySQL3309 sc delete MySQL3309
如遇错误。删除生成的data文件夹，用上面的卸载服务，再重新仔细逐步执行上面的过程。
安装完成之后，最好去配置一下环境。
如果不想去配环境，也可以在命令与提示符里cd到mysql文件的bin目录里，一样可以用sql操作。
net start 自定义的名称 启动服务。
mysql -u root -p 登入。输入密码，回车。出现下图就是安装成功了。
一些常用命令
Microsoft Windows [版本 6.1.7600] 版权所有 (c) 2009 Microsoft Corporation。保留所有权利。 C:\Windows\system32>net stop mysql0815 MySQL0815 服务正在停止. MySQL0815 服务已成功停止。 C:\Windows\system32>net start mysql0815 MySQL0815 服务正在启动 . MySQL0815 服务已经启动成功。 C:\Windows\system32>mysql -h localhost -P 3306 -u root -p Enter password: **** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.5.15 MySQL Community Server (GPL) Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> exit Bye C:\Windows\system32>mysql -h localhost -P 3306 -u root -proot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.5.15 MySQL Community Server (GPL) Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> exit Bye C:\Windows\system32>mysql -u root -proot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.5.15 MySQL Community Server (GPL) Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) mysql> use test; Database changed mysql> show tables; Empty set (0.00 sec) mysql> show tables from mysql; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | event | | func | | general_log | | help_category | | help_keyword | | help_relation | | help_topic | | host | | ndb_binlog_index | | plugin | | proc | | procs_priv | | proxies_priv | | servers | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 24 rows in set (0.00 sec) mysql> select database(); +------------+ | database() | +------------+ | test | +------------+ 1 row in set (0.00 sec) mysql> create table stuinfo( -> id int, -> name varchar(20)); Query OK, 0 rows affected (0.01 sec) mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | stuinfo | +----------------+ 1 row in set (0.00 sec) mysql> desc stuinfo; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> select * from stuinfo; Empty set (0.00 sec) mysql> insert into stuinfo (id,name) values(1,'john'); Query OK, 1 row affected (0.00 sec) mysql> insert into stuinfo (id,name) values(2,'rose'); Query OK, 1 row affected (0.00 sec) mysql> select * from stuinfo; +------+------+ | id | name | +------+------+ | 1 | john | | 2 | rose | +------+------+ 2 rows in set (0.00 sec) mysql> update stuinfo set name='lilei' where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from stuinfo; +------+-------+ | id | name | +------+-------+ | 1 | lilei | | 2 | rose | +------+-------+ 2 rows in set (0.00 sec) mysql> delete from stuinfo where id=1; Query OK, 1 row affected (0.00 sec) mysql> select * from stuinfo; +------+------+ | id | name | +------+------+ | 2 | rose | +------+------+ 1 row in set (0.00 sec) mysql> select version(); +-----------+ | version() | +-----------+ | 5.5.15 | +-----------+ 1 row in set (0.00 sec) mysql> exit Bye C:\Windows\system32>mysql --version mysql Ver 14.14 Distrib 5.5.15, for Win32 (x86) C:\Windows\system32>mysql -V mysql Ver 14.14 Distrib 5.5.15, for Win32 (x86) C:\Windows\system32>mysql -uroot -p Enter password: **** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.5.15 MySQL Community Server (GPL) Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) mysql> SHOW DATABASES\g +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) mysql> use test; Database changed mysql> select * from stuinfo; +------+------+ | id | name | +------+------+ | 2 | rose | +------+------+ 1 row in set (0.00 sec) mysql> select * -> from stuinfo; +------+------+ | id | name | +------+------+ | 2 | rose | +------+------+ 1 row in set (0.00 sec) mysql> SELECT -> * -> FROM -> stuinfo; +------+------+ | id | name | +------+------+ | 2 | rose | +------+------+ 1 row in set (0.00 sec) mysql> select * from stuinfo; +------+------+ | id | name | +------+------+ | 2 | rose | +------+------+ 1 row in set (0.00 sec) mysql> #select * from stuinfo; mysql>
二，登录mysql服务端。
###MySQL的语法规范
1.不区分大小写,但建议关键字大写，表名、列名小写
2.每条命令最好用分号结尾
3.每条命令根据需要，可以进行缩进 或换行
4.注释
单行注释：#注释文字
单行注释：-- 注释文字
多行注释：/* 注释文字 */
###SQL的语言分类
DQL（Data Query Language）：数据查询语言 select
DML(Data Manipulate Language):数据操作语言 insert 、update、delete
DDL（Data Define Languge）：数据定义语言 create、drop、alter
TCL（Transaction Control Language）：事务控制语言
三，下载图形操作界面，Navicat和sqlyog都行，我选的是sqlyog。安装好然后进入。
#进阶1：基础查询 /* 语法： select 查询列表 from 表名; 类似于：System.out.println(打印东西); 特点： 1、查询列表可以是：表中的字段、常量值、表达式、函数 2、查询的结果是一个虚拟的表格 */ USE myemployees; #1.查询表中的单个字段 SELECT last_name FROM employees; #2.查询表中的多个字段 SELECT last_name,salary,email FROM employees; #3.查询表中的所有字段 #方式一： SELECT `employee_id`, `first_name`, `last_name`, `phone_number`, `last_name`, `job_id`, `phone_number`, `job_id`, `salary`, `commission_pct`, `manager_id`, `department_id`, `hiredate` FROM employees ; #方式二： SELECT * FROM employees; #4.查询常量值 SELECT 100; SELECT 'john'; #5.查询表达式 SELECT 100%98; #6.查询函数 SELECT VERSION(); #7.起别名 /* ①便于理解 ②如果要查询的字段有重名的情况，使用别名可以区分开来 */ #方式一：使用as SELECT 100%98 AS 结果; SELECT last_name AS 姓,first_name AS 名 FROM employees; #方式二：使用空格 SELECT last_name 姓,first_name 名 FROM employees; #案例：查询salary，显示结果为 out put SELECT salary AS "out put" FROM employees; #8.去重 #案例：查询员工表中涉及到的所有的部门编号 SELECT DISTINCT department_id FROM employees; #9.+号的作用 /* java中的+号： ①运算符，两个操作数都为数值型 ②连接符，只要有一个操作数为字符串 mysql中的+号： 仅仅只有一个功能：运算符 select 100+90; 两个操作数都为数值型，则做加法运算 select '123'+90;只要其中一方为字符型，试图将字符型数值转换成数值型 如果转换成功，则继续做加法运算 select 'john'+90; 如果转换失败，则将字符型数值转换成0 select null+10; 只要其中一方为null，则结果肯定为null */ #案例：查询员工名和姓连接成一个字段，并显示为 姓名 SELECT CONCAT('a','b','c') AS 结果; SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees;
#进阶2：条件查询 /* 语法： select 查询列表 from 表名 where 筛选条件; 分类： 一、按条件表达式筛选 简单条件运算符：> < = != <> >= <= 二、按逻辑表达式筛选 逻辑运算符： 作用：用于连接条件表达式 && || ! and or not &&和and：两个条件都为true，结果为true，反之为false ||或or： 只要有一个条件为true，结果为true，反之为false !或not： 如果连接的条件本身为false，结果为true，反之为false 三、模糊查询 like between and in is null */ #一、按条件表达式筛选 #案例1：查询工资>12000的员工信息 SELECT * FROM employees WHERE salary>12000; #案例2：查询部门编号不等于90号的员工名和部门编号 SELECT last_name, department_id FROM employees WHERE department_id<>90; #二、按逻辑表达式筛选 #案例1：查询工资z在10000到20000之间的员工名、工资以及奖金 SELECT last_name, salary, commission_pct FROM employees WHERE salary>=10000 AND salary<=20000; #案例2：查询部门编号不是在90到110之间，或者工资高于15000的员工信息 SELECT * FROM employees WHERE NOT(department_id>=90 AND department_id<=110) OR salary>15000; #三、模糊查询 /* like between and in is null|is not null */ #1.like /* 特点： ①一般和通配符搭配使用 通配符： % 任意多个字符,包含0个字符 _ 任意单个字符 *、 #案例1：查询员工名中包含字符a的员工信息 select * from employees where last_name like '%a%';#abc #案例2：查询员工名中第三个字符为e，第五个字符为a的员工名和工资 select last_name, salary FROM employees WHERE last_name LIKE '__n_l%'; #案例3：查询员工名中第二个字符为_的员工名 SELECT last_name FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$'; #2.between and /* ①使用between and 可以提高语句的简洁度 ②包含临界值 ③两个临界值不要调换顺序 */ #案例1：查询员工编号在100到120之间的员工信息 SELECT * FROM employees WHERE employee_id >= 120 AND employee_id<=100; #---------------------- SELECT * FROM employees WHERE employee_id BETWEEN 120 AND 100; #3.in /* 含义：判断某字段的值是否属于in列表中的某一项 特点： ①使用in提高语句简洁度 ②in列表的值类型必须一致或兼容 ③in列表中不支持通配符 */ #案例：查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号 SELECT last_name, job_id FROM employees WHERE job_id = 'IT_PROT' OR job_id = 'AD_VP' OR JOB_ID ='AD_PRES'; #------------------ SELECT last_name, job_id FROM employees WHERE job_id IN( 'IT_PROT' ,'AD_VP','AD_PRES'); #4、is null /* =或<>不能用于判断null值 is null或is not null 可以判断null值 */ #案例1：查询没有奖金的员工名和奖金率 SELECT last_name, commission_pct FROM employees WHERE commission_pct IS NULL; #案例1：查询有奖金的员工名和奖金率 SELECT last_name, commission_pct FROM employees WHERE commission_pct IS NOT NULL; #----------以下为× SELECT last_name, commission_pct FROM employees WHERE salary IS 12000; #安全等于 <=> #案例1：查询没有奖金的员工名和奖金率 SELECT last_name, commission_pct FROM employees WHERE commission_pct <=>NULL; #案例2：查询工资为12000的员工信息 SELECT last_name, salary FROM employees WHERE salary <=> 12000; #is null pk <=> IS NULL:仅仅可以判断NULL值，可读性较高，建议使用 <=> :既可以判断NULL值，又可以判断普通的数值，可读性较低
