mysql基础总结

DDL 数据定义语言 create(建表) alter(修改属性) drop(移除) truncate(清除数据回收空间)
DQL 数据查询语言 select(查询)
DML 数据操作语言 update(修改表) delete(删除记录) insert(插入记录)
DCL 数据控制语言 grant(授权) revoke(回收权限) commit(提交事务) rollback(回收事务)

/* 创建数据库MyShool */
CREATE DATABASE test_shool;

/* 设置数据库编码 */
DEFAULT CHARACTER
SET utf8;
/* 修改表的编码格式 */
ALTER TABLE 表名 CONVERT TO CHARACTER
SET utf8mb4; #utf8不是数据utf-8编码格式,要用utf8mb4

/* 移除表grade */
USE MyShool DROP TABLE grade;

建表语句:
create table [if not exists] 表名
(列1的名字 数据类型(长度) [属性] [索引] [注释],
 列2的名字 数据类型(长度) [属性] [索引] [注释],
 ..., 列n的名字 数据类型(长度) [属性] [索引] [注释],primary key(列名称)
 )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 comment="注释";

/* 新建表 */
CREATE TABLE test_grade (
    gradel_id INT (11),
    grade_name VARCHAR (50)
);

/* 显示表内容 */
SELECT
    *
FROM
    test_grade;

/* 显示表结构 */
DESCRIBE test_grade;

/*查看建表语句*/
show create table [表名];

/*查看表信息语句*/
select * from information_schema.tables [where table_name="条件名需要大写"];

/*查看字段信息语句*/
select * from information_schema.columns;

/* 新建表subject 加`号与关键字区分 */
CREATE TABLE `test_subject` (
    subject_no INT,
    subject_name VARCHAR (50),
    class_hour INT,
    grade_id INT
);

#######################alter
/* 注释表 */
alter table student comment = '注释内容';
/* 注释列 */
alter table student modify studentname comment '注释内容';
/* 增加列 */
alter table 表名 add (列1 类型(长度),列2...);
/* 删除列 */
alter table 表名 drop column 列名;
/* 修改列 */
alter table 表名 change 原列 新列 类型;
/* 修改列类型 */
alter table 表名 modify 列名 数据类型 ;

alter table 表名 modify 列名 列的属性 comment '你要注释的内容'
ALTER TABLE grade MODIFY gradeId INT COMMENT '班级编号';

ALTER TABLE grade MODIFY gradeName VARCHAR (50) COMMENT '班级编号';

ALTER TABLE `subject` -- 表名
MODIFY subjectNo INT (11) COMMENT '科目编号' -- 更换表名
-- 语法： alter table 表名 rename to 新的表名；
ALTER TABLE grade1 RENAME TO grade;

-- 在表建好过后添加一列或多列
-- alter table 表名 add (列名1 列的数据类型,列名2 列的数据类型);
ALTER TABLE grade ADD (hei VARCHAR(50)) ALTER TABLE grade ADD (hello INT(11));

-- 删除列
-- 语法：alter table 表名 drop column 你要删除的列名；
ALTER TABLE grade DROP COLUMN hei;

ALTER TABLE grade DROP COLUMN hello;

-- 修改列名
-- 语法：alter table 表名 change 原有的列名  新的列名 数据类型;
ALTER TABLE grade CHANGE test1 test VARCHAR (10);

ALTER TABLE grade CHANGE test testReName VARCHAR (50);

-- 修改属性
-- 语法：alter table 表名 modify 你要修改的列名 数据类型 not null;
ALTER TABLE grade MODIFY gradeName VARCHAR (40) NOT NULL;

/*
* 主键约束
* 外键约束
* 唯一约束
* 非空约束
* 检查约束
* 检查约束
* 增加约束是为了保持数据的完整性
*/
/* 主键约束 */

USE myshool ALTER TABLE student ADD CONSTRAINT pk_student_StudentNo PRIMARY KEY student (studentNo);

ALTER TABLE grade ADD CONSTRAINT pk_grade_GradeID PRIMARY KEY grade (GradeID);

/* 外键约束 */
ALTER TABLE `student` ADD CONSTRAINT fk_student_GradeId FOREIGN KEY (GradeID) REFERENCES grade (GradeID);

/* 唯一约束 */
ALTER TABLE grade ADD CONSTRAINT uk_grade_GradeName UNIQUE (GradeName);

/* 非空约束 */
ALTER TABLE grade ADD GradeID INT (11) NOT NULL;

/* 检查约束 */
ALTER TABLE student ADD CONSTRAINT ck_student_Sex CHECK (sex = '男' OR sex = '女');

/* 插入一条数据 */
INSERT INTO grade
VALUES
    (4, '四年级');

/* 删除数据 */
DELETE
FROM
    test_grade
WHERE
    grade_id = 2;#条件

/* 修改数据 */
UPDATE test_grade
SET grade_id = 1
WHERE
    grade_name = '大一';

/* 插入多条数据 */
INSERT INTO test_student ()
VALUES
    (
        StudentNo #,
        #StudentName,
        #Sex,
        #Phone,
        #Address,
        #Email,
        #IdentityCard
    )
VALUES
    (
        1101 #,
        #'明明',
        #'男',
        #13500000001,
        #'深圳大学城',
        #'[email protected]',
        #441522144875485688
    );

/* where 判断 */
SELECT DISTINCT#排除重复的 关键字
    (student_no)
FROM
    result
WHERE
    student_result IS NOT NULL;

SELECT
    *
FROM
    SUBJECT AS c1
JOIN SUBJECT AS c2 ON c1.subjectno = c2.gradeID;

/* 左连接 */
SELECT
    student.StudentName,
    result.StudentNo
FROM
    student
LEFT JOIN result ON SELECT
    *
FROM
    student
LEFT JOIN result ON student.StudentNo = result.StudentNo ;

/* 右连接 */
SELECT
    student.StudentName,
    result.StudentNo
FROM
    student
LEFT JOIN result ON SELECT
    *
FROM
    student
RIGHT JOIN result ON student.StudentNo = result.StudentNo ;

/* 子查询 */
SELECT
    studentname
FROM
    student
WHERE
    studentno IN (
        SELECT
            studentno
        FROM
            result
        WHERE
            subjectno IN (
                SELECT
                    subjectno
                FROM
                    SUBJECT
                WHERE
                    subjectname = 'java基础'
            )
        GROUP BY
            studentresult
    )
LIMIT 5;

/* 联合查询 */
SELECT   [ALL | DISTINCT]
{  * |  table.* | [ table.field1 [ as  alias1] [,table.field2 [as  alias2]][,…]] }
FROM  table_name  [ as  table_ alias  ]
    [left|out|inner  join  table_name2 ]    #联合查询
    [ WHERE  … ]       #指定结果需满足的条件
    [ GROUP BY …]    #指定结果按照哪几个字段来分组
    [HAVING …]    #过滤分组的记录必须满足的次要条件
    [ ORDER BY… ]    #指定查询记录按一个或者多个条件排序
    [ LIMIT  {   [ offset,] row_count   |   row_count OFFSET

/* 统计函数 */
COUNT(元素集) #统计数量
SUM(元素集) #合计
AVG(元素集) #平均值
MAX(元素集) #最大值
MIN(元素集) #最小值
/* 分组查询 */
SELECT
    `subject`.SubjectName AS 科目名,
    result.subjectno AS 科目ID,
    AVG(result.studentresult) AS 平均分,
    MAX(result.studentresult) AS 最高分,
    MIN(result.studentresult) AS 最低分
FROM
    `result`
JOIN SUBJECT ON result.SubjectNo = `subject`.SubjectNo # 内连接
GROUP BY
    result.SubjectNo # 根据subjectno分组
HAVING
    AVG(result.studentresult) >= 60; # 筛选条件

/* 事务托管 */
/* 事务回滚 */
SET autocommit = 0;
START TRANSACTION;
UPDATE account
SET money = money - 500
WHERE
    id = 1;
#commit;
ROLLBACK;
SET autocommit = 1;
/**/
select * from account;

/* 事务提交 */
SET autocommit = 0;
START TRANSACTION;
UPDATE account
SET money = money - 500
WHERE
    id = 1;
commit;
#ROLLBACK;
SET autocommit = 1;

/* 索引 优化查询 */

/* 主键索引（PRIMARY KEY）*/
 CREATE TABLE  `表名` (
    `GradeID`  INT(11)   AUTO_INCREMENT    PRIMARY KEY,
    #或  PRIMARY KEY(`GradeID`)
)

/* 唯一索引（UNIQUE）*/
CREATE TABLE  `Grade` (
    `GradeID`  INT(11)  AUTO_INCREMENT  PRIMARY KEY,
    `GradeName`  VARCHAR(32)  NOT NULL  UNIQUE
    #或  UNIQUE  KEY  `GradeID`  (`GradeID`)
)

/* 常规索引（INDEX）*/
CREATE TABLE  `result` (
     //省略一些代码
    INDEX/KEY   `ind`  (`studentNo`, `subjectNo`)
)

ALTER TABLE  `result`  ADD  INDEX  `ind`  (`studentNo`, `subjectNo`);

/* 全文索引（FULLTEXT）*/
CREATE TABLE  `student` (
     #省略一些SQL语句
    FULLTEXT (`StudentName`)
)ENGINE=MYISAM;

ALTER TABLE employee ADD FULLTEXT (`first_name`);
/* 追加索引 */
ALERT TABLE 表名 ADD  索引类型（数据列名）

/* 删除索引 */
DROP  INDEX 索引名 ON    表名
ALTER TABLE 表名   DROP  INDEX  索引名
ALTER TABLE 表名   DROP  PRIMARY KEY

/* 查看索引 */
SHOW  INDEX(或KEYS) FROM 表名

导入表
mysql
mysql>create database school;
mysql>use school;
mysql>source 路径(school.sql);
(或将school.sql换为school.teacher.sql / school.teacher.student.sql)

导出数据库
mysqldump --databases db1 db2 > 路径(db1.db2.sql)
mysqldump -h host -u user -p pass --databases dbname > file.dump

/***********    ***********************************************************************/
引用的文档

Database changed
mysql> select database(); 显示当前选择的数据库
mysql> show tables; 显示当前数据库中存在的表
mysql> select * from db; 显示表(db)的内容
mysql> describe mytable; 显示表的结构
或show columns from 表名；

mysql> select
-> user()
-> \c
mysql>

练习如下操作：
mysql> select
-> USER()
-> ,
-> now()
->;
mysql> Select (20+5)*4;
mysql> Select (20+5)*4,sin(pi()/3);
mysql> Select (20+5)*4 AS Result,sin(pi()/3); (AS: 指定假名为Result)

查看MySQL当前用户占用的连接数

命令： show processlist;
如果是root帐号，你能看到所有用户的当前连接。如果是其它普通帐号，只能看到自己占用的连接。
show processlist;只列出前100条，如果想全列出请使用show full processlist;
mysql> show processlist;

一、数据库：

mysql> CREATE DATABASE abccs; 创建一个数据库
mysql> USE abccs 选择使用数据库
mysql> drop database 数据库名; 删除数据库

二、表：

1。创建一个表mytable:

mysql> CREATE TABLE mytable
-> (
-> name VARCHAR(20),
-> sex CHAR(1),
-> birth DATE,
-> birthaddr VARCHAR(20)
-> );
建立一个员工生日表，表的内容包含员工姓名、性别、出生日期、出生城市。
由于name、birthadd的列值是变化的，因此选择VARCHAR，其长度不一定是20。
可以选择从1到255的任何长度，如果以后需要改变它的字长，可以使用ALTER TABLE语句。
性别只需一个字符就可以表示："m"或"f"，因此选用CHAR(1);
birth列则使用DATE数据类型。

2。查询刚添加的记录：
mysql> select * from mytable;
Empty set (0.00 sec) 这说明刚才创建的表还没有记录。

3。添加新记录：
mysql> insert into mytable
-> values
->(
->'abccs',
->'f',
->'1977-07-07',
->'china'
->);

4、用文本方式将数据装入一个数据库表：
一条一条地添加新记录，实在太麻烦。
创建一个文本文件“mysql.txt”，按表结构排好每行每条记录，用定位符(tab)把值分开。
abccs f 1977-07-07 china
mary f 1978-12-12 usa
tom m 1970-09-02 usa
使用此命令将文本文件“mytable.txt”装载到表中:
mysql> Load data local infile "mytable.txt" into table mytable;

数据传入命令load data local infile "文件名" into table 表名;
注意：你最好将文件复制到mysql/bin目录下，并且要先用use命令选表所在的库。

5。更新记录：
mysql> update mytable set birth = "1973-09-02" where name = "tom";

6。删除记录：
mysql> delete from mytable where id=10; //删除掉所有id=10的记录;
mysql> delete from mytable where id=10 limit 1; //限制删除掉1条id=10的记录;
mysql> delete from mytable //删除一个表的全部记录;
mysql> DELETE FROM t1 WHERE C>10;
mysql> drop table tablename1,tablename2,…; //删除整一个表或多个表，小心使用。

7。重命名表：
mysql> alter table t1 rename t2;

修改字段属性
mysql> alter table tablename modify id int(10) unsigned auto_increment primary key not null

修改默认值
mysql> alter table tablename alter id default 0

给字段增加primary key
mysql> alter table tablename add primary key(id);

删除primary key
1、alter table tablename drop primary key;
2、drop primary key on tablename;

修改table表数据引擎
mysql> alter table tableName ENGINE = MyISAM (InnoDB);

增加一新字段名：
mysql> alter table mytable add column single char(1);
mysql> ALTER TABLE table ADD field INT(11) UNSIGNED NOT NULL

删除字段
mysql> alter table t2 drop column c;

实例：
为了改变列a，从INTEGER改为TINYINT NOT NULL(名字一样)，
并且改变列b，从CHAR(10)改为CHAR(20)，同时重命名它，从b改为c:
ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);

增加一个新TIMESTAMP列，名为d：
ALTER TABLE t2 ADD d TIMESTAMP;

在列d上增加一个索引，并且使列a为主键：
ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);

增加一个新的AUTO_INCREMENT整数列，命名为c：
ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,ADD INDEX (c);

注意，我们索引了c，因为AUTO_INCREMENT柱必须被索引，
并且另外我们声明c为NOT NULL，因为索引了的列不能是NULL。

使用name列的头10个字符创建一个索引:
CREATE INDEX part_of_name ON customer (name(10));

三、数据的备份与恢复：

导出和导入数据：(命令在DOS的mysql/bin目录下执行)
导出表
mysqldump --opt school > school.sql
注释：将数据库school中的表全部备份到school.sql文件，school.sql是一个文本文件，
文件名任取，打开看看你会有新发现。
mysqldump --opt school teacher student > school.teacher.student.sql
注释：将数据库school中的teacher表和student表备份到school.teacher.student.sql文
件，school.teacher.student.sql是一个文本文件，文件名任取，打开看看你会有新发现。

导入表
mysql
mysql>create database school;
mysql>use school;
mysql>source school.sql;
(或将school.sql换为school.teacher.sql / school.teacher.student.sql)

导出数据库
mysqldump --databases db1 db2 > db1.db2.sql
注释：将数据库dbl和db2备份到db1.db2.sql文件，db1.db2.sql是一个文本文件，文件名
任取，打开看看你会有新发现。
(举个例子：
mysqldump -h host -u user -p pass --databases dbname > file.dump
就是把host上的以名字user，口令pass的数据库dbname导入到文件file.dump中。)

导入数据库
mysql < db1.db2.sql

复制数据库mysqldump --all-databases > all-databases.sql
注释：将所有数据库备份到all-databases.sql文件，all-databases.sql是一个文本文件，文件名任取。

导入数据库
mysql
mysql>drop database a;
mysql>

修改注释
ALTER TABLE table_name COMMENT='这是表的注释'
ALTER table table_name MODIFY `column_name` datetime DEFAULT NULL COMMENT '这是字段的注释'