1,服务端和客户端
MySQL 包括服务端和客户端,服务端是MySQL server,客户端包括命令行客户端和图形用户客户端;
命令行客户端:mysql,mysqladmin,mysqldump (都存在于C:\programs\MySQL\MySQL Sever n.n\bin或自定义安装目录下,配置环境变量后可直接在cmd启动)
图形用户客户端:MySQL Adminstrator, MySQL Query Browser, phpMyAdmin及其他软件等
2,命令行客户端
2.1 mysql: 向MySQL Sever发送用户输入的命令,并显示MySQL Sever返回的结果。
a,连接MySQL Sever : mysql -u root -p
命令参数 :-p(小写)表示需要密码登陆MySQL Server; -h computername 远程MySQL Server的电脑host名;-protocol = name 远程连接协议;-P(大写),连接端口 号,默认3306; -default-character-set=name 设置mysql和MySQL Server之间的数据格式; databasename 可以指定连接的数据库。
示例命令 : mysql -u root -p -h hostname -default-character-set=utf8 databsename
b, show databases; 显示所有数据库
use dbname; 进入数据库dbname
show tables; 显示该数据库的所有表格
c,其他常用命令(尽量以冒号结尾)
\h; 或者 help;
2.2 mysqladmin:执行一些管理任务,创建和删除数据库,修改密码等。
2.3 mysqldump : 用来进行数据备份等
备份命令
(将数据库mybbs1备份到文件blogback.sql)
下面命令利用备份文件恢复数据库
或者如下命令:
use dbname;
source /home/zack/backfile.sql;
3.数据库表设计(数据类型)
Interger 整数: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, SERIAL
Floating-point number 浮点数: FLOAT, DOUBLE
Fixed-point number 定点数: DECIMAL
Date and time 日期和时间: DATE, TIME, DATETIME, TIMESTAMP
日期格式验证设置: (DATE)
Character string 字符窜:CHAR, VARCHAR, TEXT
字符串格式设置和排序方式设置: 对于类型为字符串的列,可以设置字符格式和排序方式
CHARACTER SET charatcterset_name
COLLATE order_name
常见字符格式和排序方式
Binary data 二进制数据:BIT, TINYBLOB, BLOB, MEDIUM BLOB, LONGBLOB
其他数据类型:ENUM, SET, GEOMETRY, POINT
可用选项和属性
4,sql语句 (structured query language)
Data manipulate language(DML): SELECT, UPDATE, DELETE, INSERT
Data definition language(DDL): CREATE TABLE, ALTER TABLE, DROP TABLE
Data control language(DCL): GRANT, REVOKE
4.1 Data definition language(DDL):创建数据库,数据表,改变数据表设计和删除表等
创建数据库:
CREATE DATABASE mydb; (或者用mysqladmin: mysqladmin -u root -p create mydb)
CREATE DATABASE IF NOT EXISTS mydb;(存在时不会报错,显示一条警告,通过SHOW WARNINGS;可以看到具体警告内容)
CREATE DATABASE mydb DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
(设置数据库默认数据格式,以及排序方式;COLLATE, 限定字符窜的排序比较,以_ci 表示大小写不敏感,以_cs 表示大小写敏感,以_bin表示用编码值进行比较)
改变数据库编码格式:ALTER DATABASE dbname CHARACTER SET gbk
查看数据库格式:
SHOW CREATE DATABASE dbname; 查看数据库创建时的格式
进入数据库:
USE mydb;
SELECT DATABASE(); 查看当前进入的数据库
创建表格:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbname(
colname1 coltype coloptions referrence,
colname2 coltype coloptions referrence,
[index1, index2],
[KEY],
[PRIMARY KEY]
)[ENGINE=MyISAM|InnoDB|Heap][DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci]
示例:
(CONSTRAINT titles_ibfk_1 FOREIGN KEY : CONSTRAINT给该外键约束条件起名字titles_ibfk_1,方便后面删除, 不起名时可以不用CONSTRAINT)
通过查找的数据创建表格:
CREATE TABLE newtable SELECT * FROM titles WHERE publID=1;
数据库表格间复制
查看表格:
SHOW CREATE TABLE tbname;
DESC tbname;
SHOW COLUMNS FORM titles;
创建索引(create index):
下面命令给titles表中的title列增加索引 (索引名称为idxtitle),有三种方式如下:
1,创建表格时指定:
CREATE TABLE titles(
title varchar(100),
publID INT,
INDEX idxtitle (title)
);
2,创建索引:
CREATE INDEX idxtitle ON titles(title);
3,修改表设计,添加索引
ALTER TABLE titles ADD INDEX idxtitle (title);
改变表格设计(ALTER TABLE):
增加列:ALTER TABLE tablename ADD newcol coltype coloption [FIRST | AFTER existingcol];
改变列: ALTER TABLE tablename CHANGE oldcolname newcolname coltype coloptions;
删除列; ALTER TABLE tablename DROP colname;
增加索引:ALTER TABLE tablename ADD PRIMARY KEY (indexcols..);
ALTER TABLE tablename ADD INDEX [indexname] (indexcols..);
ALTER TABLE tablename ADD UNIQUE [indexname] (indexcols..);
ALTER TABLE tablename ADD FULLTEXT [indexname] (indexcols..);
删除索引:ALTER TABLE tablename DROP PRIMARY KEY ;
ALTER TABLE tablename DROP INDEX [indexname] ;
ALTER TABLE tablename DROP FOREIGN KEY [indexname] ;
增加外键关系:ALTER TABLE tablename ADD FOREIGN KEY [idxname] (column1) REFERENCES table2 (column2);
改变表格字符格式: ALTER TABLE tablename CONVERT TO CHARACTER SET charsetname;
改变表格类型(引擎):ALTER tablename TABLE ENGINE typename;
删除数据库或表格:DROP TABLE tablename;
DROP DATABASE dbname;
DROP DATABASE IF EXISTS dbname;
show commands :返回该用户权限下能得到的所有数据(数据库和表格)
https://www.cnblogs.com/SQL888/p/5750161.html
show create table tbname; 显示数据表结构
查看当前的数据格式设置:
关于数据格式设置:
4.2 Data manipulate language(DML): 对表格的增删改查
a, SELECT :从表格查找数据
SELECT * FROM table_name 查找所有数据
SELECT column_name FROM table_name 查找某一列数据
计算COUNT:
SELECT COUNT(column_name) FROM table_name 某一列的数据个数(行数)
SELECT COUNT(DISTINCT column_name) FROM table_name 某一列不重复的数据个数
SELECT COUNT(*) / COUNT(DINSTINCT column_name) FROM table_name 两数据相除
限定返回数量;
SELECT colname FROM tbname LIMIT 2 返回前两条数据
SELECT colname FROM tbname LIMIT 2, 2 返回第二条开始的前两条 (第三行和四行数据)
SELECT SQL_CALC_FOUND_ROWS column_name FROM table_name LIMIT 3 ; SELECT FOUND_ROWS();先咨询第一条命令返回前三条数据,再执行第二条 时能返回总行数 (相当于SELECT COUNT(*) FROM table_name )
排序:
SELECT colname FROM tbname ORDER BY colname;
SELECT colname FROM tbname ORDER BY colname DESC; 反序排列
条件查询:
SELECT authname FROM TABLE authors WHERE authname>=’M’ ; 名字开头字母介入L-Z
SELECT authname FROM TABLE authors WHERE authname LIKE ‘%er%’ ; ( % 匹配任意数量的字符串, 返回作者名字中包含er的
SELECT authname, authID FROM authors WHERE authID IN (2,7,12);
多表连接查询:
两张表连接 (两张表都有pubID)
SELECT title, publisher FROM titles, publishers WHERE titles.pubID = publishers.pubID
SELECT title, publisher FROM titles LEFT JOIN publishers ON titles.pubID = publishers.pubID
SELECT title, publisher FROM titles LEFT JOIN publishers USING (pubID)
三张表或多表查询:
titles表和authors表 之间为多对多关系,通过第三张表rel_title_author定义:SELECT title, authname FROM titles, rel_title_author, authors WHERE titles.titleID= rel_title_author.titleID AND authors. authID = rel_title_author. authID ORDER BY title
Publishers 和 titles 为一对多关系,Titles和authors 之间为多对多关系,通过第三张表rel_title_author定义,先通过publish和title联系,再通过rel_title_author和authors联系:SELECT DISTINCT publname, authname FROM publishers, titles, rel_title_author, authors WHERE titles.titleID= rel_title_author.titleID AND authors. authID = rel_title_author. authID AND titles.pubID = publishers.pubID ORDER BY publname, authname (DISTINCT 去除重复的数据记录)
Left join, Right join, Natural join: 连接表
titles 表和publish表的数据如下,分别进行Left join, Right join, Natural join
Left join: 以table1 的数据列xyID为依据进行连接,如果table1中的行数较多时,table2用null补全; (以titles表的publID列数据为依据连接)
Right join: 以table2的数据列xyID,如果table2中的行数较多时,table1用null补全 (以publish表的publID列数据为依据连接)
Natural join: 两张表有相同名称的数据列,将该列进行合并连接(数据类型不一致能转换时可以连接,不能转换时报错)
查询结果连接:UNION, UNION ALL(UNION会自动去除重复的数据,UNION ALL 不去除)
将查询到的结果合并(一列中),也可以连接不同表的查询结果,但两张表必须有相同的列数,且数据类型相同或可以转换
SELECT * FROM authors WHERE authname LIKE ‘b%’ UNION SELECT * FROM authors WHERE authname LIKE ‘g%’
对每一个查询用括号,可以加一些其他选项命令(下面命令显示5行数据)
(SELECT * FROM tab1 ORDER BY col1 LIMIT 10) UNION (SELECT * FROM tab2 ORDER BY col2 LIMIT 10) ORDER BY authname LIMIT 5
分组查询:GROUP BY
聚集函数: COUNT, SUM, MIN, MAX
单表,按publID分组,查询每一个publID 对应的title数量: (COUNT(title) AS num :返回结果中,COUNT(title)这一列的名字为num)
跨表,按书籍类名来分类,并计算每一个类别下书籍总数(计算title)
SELECT catName, COUNT(title) AS numofitem FROM categories, titles WHERE titles.catID=categories.catID GROUP BY catName ORDER BY catName
按多列分组 (GROUP BY for several columns),下面命令查找一种语言和一种书籍类别下的书籍数量
下面的命令,对于不含有书籍数据的书籍类别也进行了统计(left join的特性)
SELECT catName, COUNT(title) AS numofitem FROM categories LEFT JOIN ON titles.catID=categories.catID GROUP BY catName ORDER BY numofitem DESC
GROUP BY WITH ROLLUP: 对于没有的数据项用NULL填充
GROUP_CONCAT: 将多项数据(一对多)拼接到一列 (HAVING cnt>1 总数大于1的)
SELECT title, GROUP_CONCAT(authname ORDER BY authname SEPARATOR ‘,’) AS authors, COUNT(authors.authID) AS cnt FROM titles, rel_title_author, authors WHERE titles.titleID= rel_title_author.titleID. AND authors. authID = rel_title_author. authID GROUP by titles.titleID HAVING cnt>1 ORDER BY title
b, INSERT INTO :插入数据
INSERT INTO titles (title, year) VALUES (‘MySQL’, 2005)
插入所有数据时可以不带列标题(包括NULL,AUTO_INCREMENT):
对于NULL,DEFAULT, AUTO_INCREMENT 可以不插入:
CREATE TABLE person(id INT, name VARCHAR(16) DEFAULT 'ZACK');
INSERT INTO person(id) VALUES(i); (name会自动赋值)
插入多行数据
INSERT INTO table(column1, column2, column3) VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9)
插入多对多或外键关系数据库,需要自己手动创建 (SELECT LAST_INSERT_ID()返回上次插入行数据的自增ID值)
下面命令,利用publID,在titles表中插入一行数据,建立外键关系,再利用authID,在rel_title_author表中插入三条数据,建立多对多关系
c,UPDATE SET:更改数据记录
UPDATE tablename SET column1=value1, column2=value2 WHERE columnID=n (没有WHERE语句时column1, column2两列所有数据都更改)
UPDATE语句中支持简单的计算: UPDATE titles SET price=price*1.05;
d,DELETE FROM: 删除数据记录
DELETE FROM titles WHERE titleID=8 (没有where时整张表数据会被删除,保留表定义和index) (删除整张表 DROP titles)
多表关联删除:
仅删除titles表数据
titles, rel_title_author,authors三张表数据都删除
若上面两条命令在删除时报错:外键关联约束。 则其相应的解决方法如下:
方法一:先设置 SET FOREIGN_KEY_CHECKS=0, 然后执行上述删除命令,最后再设置SET FOREIGN_KEY_CHECKS=1
方式二:在定义外键时设置约束规则, ON DELETE CASCADE (关联数据会自动删除)
delete结合LIMIT使用
仅删除排序后的第一条数据:DELETE FROM authors ORDER BY authname DESC LIMIT 1
删除和更新时的外键约束:RESTRICT (默认), NO ACTION, CASCADE, SET NULL
RESTRICT/ NO ACTION: 当外键约束关系设置为RESTRICT/ NO ACTION时,删除或更新主表中的外键记录时,不允许删除
CASCADE:删除或更新主表中的外键记录时,外键关系所在子表的对应记录也删除
SET NULL:删除或更新主表中的外键记录时,外键关系所在子表的对应记录设置为NULL(要求该字段允许设置为NULL)
5,常见函数使用
5.1 字符窜处理函数
CONCAT(str1, ' ', str2): 将字符窜str1 和 str2 用指定的符号连接起来
SUBSTR(string, pos, n) 截取返回的string中pos到n的字符串:SELECT SUBSTR(title, 1, 10) FROM titles;
IF(a, b , c) : a=true时返回b,否则返回c
SELECT IF(CHAR_LENGTH(title)>30,CONCAT(LEFT(title, 20), '&',RIGHT(title,5)), title) FROM titles AS shorttitle
(CHAR_LENGTH(): 返回字符个数; LENGTH(): 返回字节数; LEFT(title, 20): 从最左边开始,返回20个字符; RIGHT(title,5):从最右边开始,返回5个字符)
case分支
CONVERT(string using charset):改变字符串的数据格式, SELECT CONVERT(title USING utf8) FROM titles
下面两条命令为表格增加一列title_utf8,数据和title相同,格式不同
5.2 日期和时间:
下面Ts 列为TIMESTAMP或 DATETIME数据类型时,命令都适用
MONTH(), YEAR()函数返回TIMESTAMP, DATETIME数据的年月, SELECT COUNT(*), MONTH(ts) AS m FROM titles WHERE YEAR(ts)=2018 GROUP BY m;
DATE_FORMAT()函数,格式化日期, SELECT COUNT(*), DATE_FORMAT(ts,'%Y-%M') AS ym FROM titles WHERE YEAR(ts)=2018 GROUP BY ym;
日期和时间计算:
时区:
6.sql练习题
http://www.cnblogs.com/wupeiqi/p/5748496.html