• 操作化境为ubuntu

mysql 基础操作

• 安装
  安装
  完全卸载
  卸载出现问题解决

sudo apt-get mysql-server
sudo apt-get mysql-client
sudo apt install libmysqlclient-dev

• 打开并登录、修改密码

sudo  service mysql  start
mysql -u root #以管理员方式登陆  
mysql> set password for 用户名@localhost = password('新密码'); 

• 查看数据库

show databases;
use database_name; 

• 查看表

show tables

• 创建数据库

CREATE DATABASE    mysql_database;
USE mysql_database;
DROP DATABASE  BDNAME;

• 创建表

CREATE TABLE  employee (
id INT(10),
name CHAR  (20),
phone INT(12));
SHOW TABLES;

查看表详情

desc table_name;

• 插入数据

INSERT INTO table_name (...) VALUES ();

数据类型

图片.png

约束

图片.png

• mysql终端执行sql语句

source /home/.... # 后面是文件路径及sql文件名

• 自增

auto_increment 

• 示例SQL

CREATE DATABASE mysql_shiyan;
use mysql_shiyan;

CREATE TABLE department
(
  dpt_name   CHAR(20) NOT NULL,
  people_num INT(10) DEFAULT '10',
  CONSTRAINT dpt_pk PRIMARY KEY (dpt_name) 
-- dpt_pk是自己命名的主键名称
 );

CREATE TABLE employee
(
  id      INT(10) PRIMARY KEY,
  name    CHAR(20),
  age     INT(10),
  salary  INT(10) NOT NULL,
  phone   INT(12) NOT NULL,
  in_dpt  CHAR(20) NOT NULL,
  UNIQUE  (phone),
  CONSTRAINT emp_fk FOREIGN KEY (in_dpt) REFERENCES department(dpt_name)
-- 外键必须为其他表的主键
 );
 
CREATE TABLE project
(
  proj_num   INT(10) NOT NULL,
  proj_name  CHAR(20) NOT NULL,
  start_date DATE NOT NULL,
  end_date   DATE DEFAULT '2015-04-01',
  of_dpt     CHAR(20) REFERENCES department(dpt_name),
  CONSTRAINT proj_pk PRIMARY KEY (proj_num,proj_name)
 );

SELECT

SELECT name,age FROM employee WHERE age>25;
SELECT name,age,phone FROM employee WHERE name='Mary';
SELECT name,age FROM employee WHERE age<25 OR age>30;
SELECT name,age FROM employee WHERE age>25 AND age<30;
SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt IN ('dpt3','dpt4');

SQL中的通配符是 _ 和 % 。其中 _ 代表一个未指定字符，% 代表不定个未指定字符。
SELECT name,age,phone FROM employee WHERE phone LIKE '1101__';
这就要用到 ORDER BY 排序关键词。默认情况下，ORDER BY的结果是升序排列，而使用关键词ASC和DESC可指定升序或降序排序。

图片.png

SELECT MAX(salary) AS max_salary,MIN(salary) FROM employee;
SELECT of_dpt,COUNT(proj_name) AS count_project FROM project
WHERE of_dpt IN
(SELECT in_dpt FROM employee WHERE name='Tom');
SELECT id,name,people_num
FROM employee,department
WHERE employee.in_dpt = department.dpt_name
ORDER BY id;

数据库及表的修改

-- 删除数据库
DROP DATABASE name;
--重命名表
ALTER TABLE  name RENAME TO rename;
--给表增加列(给employee表增加一列height)
ALTER TABLE employee ADD height INT(4) DEFAULT 170;
(在某一列后面加属性)
ALTER TABLE employee ADD weight INT(4) DEFAULT 120 AFTER age;
(加到最前面)
ALTER TABLE employee ADD test INT(4) DEFAULT 12 FIRST;
-- 删除表的某一行
ALTER TABLE employee DROP test;
-- 重命名表的某一列
ALTER TABLE employee CHANGE height shengao INT(4) DEFAULT 170;
--更新数值(修改名字为Alex那一列为eric,26)
UPDATE employee SET age= 26, name = eric WHERE name ='Alex';
--删除某一行记录
DELECT FROM employee WHERE name = 'eric';
--增加索引(给id列增加索引为id_inx)
ALTER TABLE employee ADD INDEX  id_inx   (id);
--创建视图，视图就是一张虚拟表
ALTER CREATE VIEW  v_temp(v_name,v_age,v_phone)  AS SELECT name,age,phone FROM employee;

数据库备份与恢复

-- 备份
mysqldump -u root 数据库名>备份文件名;   #备份整个数据库

mysqldump -u root 数据库名 表名字>备份文件名;  #备份整个表
-- 恢复
mysqldump -u root 数据库名>备份文件名;   #备份整个数据库

mysqldump -u root 数据库名 表名字>备份文件名;  #备份整个表

常用方法示例

-- UNSIGNED ZEROFILL 无符号类型、自动补零
mysql> CREATE TABLE shop (
    -> article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
    -> dealer  CHAR(20)                 DEFAULT ''     NOT NULL,
    -> price   DOUBLE(16,2)             DEFAULT '0.00' NOT NULL,
    -> PRIMARY KEY(article, dealer));

• 通过子条件查询出price最行信息

mysql> SELECT article, dealer, price
    -> FROM   shop
    -> WHERE  price=(SELECT MAX(price) FROM shop);

• 通过limt 显示price最大行信息

mysql> SELECT article, dealer, price
    -> FROM shop
    -> ORDER BY price DESC
    -> LIMIT 1;