4、创建表是先判断表是否存在
create table if not exists students(……);
5、从已经有的表中复制表的结构
create table table2 select * from table1 where 1<>1;
6、复制表
create table table2 select * from table1;
7、对表重新命名
alter table table1 rename as table2;
8、修改列的类型
alter table table1 modify id int unsigned;//修改列id的类型为int unsigned
alter table table1 change id sid int unsigned;//修改列id的名字为sid，而且把属性修改为int unsigned
9、创建索引
alter table table1 add index ind_id (id);
create index ind_id on table1 (id);
create unique index ind_id on table1 (id);//建立唯一性索引
10、删除索引
drop index idx_id on table1;
alter table table1 drop index ind_id;
11、联合字符或者多个列(将列id与":"和列name和"="连接)
select concat(id,':',name,'=') from students;
12、limit(选出10到20条)<第一个记录集的编号是0>
select * from students order by id limit 9,10;
20、使用procedure analyse()
可以使用procedure analyse()显示最佳类型的建议，使用很简单，在select语句后面加上procedure analyse()就可以了；例如：
select * from students procedure analyse();
select * from students procedure analyse(16,256);
第二条语句要求procedure analyse()不要建议含有多于16个值，或者含有多于256字节的enum类型，如果没有限制，输出可能会很长；
2. MYSQL
2.1. 连接MYSQL
格式： mysql -h主机地址 -u用户名 -p用户密码
连接远程机器：
E:\mysql>mysql -h10.4.3.188 -uptsdb -p
等价写法
E:\mysql>mysql --host=10.4.3.188 --user=ptsdb --password
连接本地机器：
E:\mysql>mysql -uroot -p
等价写法
E:\mysql>mysql --user=root -password
（注:u与root可以不用加空格，其它也一样）
注意事项：环境变量path 里面要设定mysql的bin的路径：
C:\Program Files\MySQL\MySQL Server 5.0\bin
2.2. 修改密码
方法一：使用mysqladmin
格式：mysqladmin -u用户名 -p旧密码 password 新密码
例1：E:\mysql>mysqladmin -uroot password root
注：因为开始时root没有密码，所以-p旧密码一项就可以省略了。
例2：再将root的密码改为root123。
E:\mysql>mysqladmin -uroot -proot password root123
方法二：直接更新 user 表
mysql>UPDATE user SET password=PASSWORD("test123") WHERE user='test';
mysql> FLUSH PRIVILEGES;
mysql> SET PASSWORD FOR test=PASSWORD('test123');
mysql> FLUSH PRIVILEGES;
方法三：使用 grant
格式：grant 权限 on 数据库.表格| 其他 to 用户@主机 IDENTIFIED BY 口令
例1：给test用户在本地localhost 所有权限(除了GRANT OPTION)，口令为 test
(相当于修改了test 用户的口令)
mysql>grant all on *.* to test@localhost identified by "test";
等同于
mysql>grant all on *.* to test @localhost identified by PASSWORD " *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 ";
例2、增加一个用户test密码为abc，让他可以在任何主机上登录，并对test数据库有查询、插入、修改、删除的权限。首先用以root用户连入MYSQL，然后键入以下命令：
mysql>grant select,insert,update,delete on test.* to test@"%" Identified by "abc";
同样可以取消部分权限（全部）
mysql>revoke insert,update,delete on test.* from test@"%"
mysql>REVOKE ALL PRIVILEGES, GRANT OPTION FROM test@"%"
然后 mysql> FLUSH PRIVILEGES;
Test 用户不再使用用了，也可以删除
mysql>Delete from user where user='test' and host='%'
mysql> FLUSH PRIVILEGES;
注意：例2增加的用户是比较危险的，你想如某个人知道test的密码，那么他就可以在internet上的任何一台电脑上登录你的mysql数据库并对你的数据库test为所欲为了(可以通过限定主机)
mysql>grant select,insert,update,delete on test.* to test@"IP地址" Identified by "abc";
2.3. 显示命令
显示数据库列表:
mysql>show databases;
mysql>show schemas; --mysql 5.0.2
显示表格
mysql>show tables from mydb;
显示表格状态
Mysql>SHOW TABLE STATUS;
显示字符集:
mysql> SHOW CHARACTER SET;
显示创建表:
mysql> show create table quote;
显示用户权限:
mysql> SHOW GRANTS FOR 'test'@'localhost';
mysql>SHOW GRANTS;
mysql>SHOW GRANTS FOR CURRENT_USER;
mysql>SHOW GRANTS FOR CURRENT_USER();
显示index:
mysql>SHOW INDEX FROM mydb.mytable;
显示表结构:
mysql>desc mydb.tablename;
mysql>show columns from mydb.tablename;
显示MySQL数据库的版本：
mysql>select version();
显示函数
mysql>Select * from mysql.func;
显示存储过程
mysql>Select * from mysql.proc;
显示存储引擎
mysql> SHOW ENGINES;
显示变量:
mysql>SHOW VARIABLES;
显示状态:
Mysql> SHOW STATUS;
显示进程
Mysql>SHOW PROCESSLIST
显示 INNODB 状态
Mysql>SHOW INNODB STATUS
显示连接状态
Mysql>SHOW STATUS LIKE '%CONNECT%';
显示线程状态
Mysql>SHOW STATUS LIKE '%THREAD%';
create_specification:
[DEFAULT] CHARACTER SET charset_name
| [DEFAULT] COLLATE collation_name
例如:
CREATE DATABASE IF NOT EXISTS ddd --如果不存在,则创建.
CHARACTER SET 'ujis' --设定字符集
COLLATE 'ujis_japanese_ci';
2.4.2. 创建表
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options] [select_statement]
例子:
CREATE TABLE if not exists `Admin_User` (
`id` int(11) NOT NULL auto_increment, --PRIMARY KEY，
`livedoorId` varchar(255) NOT NULL default '',
`password` varchar(255) NOT NULL default '',
`auth` int(11) default '0',
PRIMARY KEY (`id`) --设定主健
) ENGINE=MyISAM DEFAULT CHARSET=ujis ?设定字符集
ENGINE=MyISAM 默认存储引擎
The binary portable storage engine that is the default storage engine used by MySQL
对于每个MyISAM 存储引擎的表,在硬盘上存在3个文件
File Purpose
tbl_name.frm Table format (definition) file
tbl_name.MYD Data file
tbl_name.MYI Index file
ENGINE= InnoDB
Transaction-safe tables with row locking and foreign keys.
ENGINE = BDB
Transaction-safe tables with page locking.
还有其他的内存引擎 MEMORY 归档 ARCHIVE 等等
ISAM 不再使用了
2.4.3. 创建索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[USING index_type]
ON tbl_name (index_col_name,...)
index_col_name:
col_name [(length)] [ASC | DESC]
将customer 表的name 字段前十个字符做为索引
CREATE INDEX part_of_name ON customer (name(10));
MYSQL 5.0 特性
存储引擎为 MyISAM, InnoDB, or BDB 的表格上,可以在有null值的字段上创建索引
存储引擎为 MyISAM, InnoDB, or BDB 的表格上,可以在BLOB TEXT 上创建索引
只有在MyISAM 类型表格上,可以在CHAR, VARCHAR, and TEXT 字段类型上创建FULLTEXT 索引
Storage Engine Allowable Index Types
MyISAM BTREE
InnoDB BTREE
MEMORY/HEAP HASH, BTREE
可以指定索引类型
Example:
CREATE TABLE testtable (id INT) ENGINE = MEMORY;
CREATE INDEX id_index USING BTREE ON testtable (id);
2.4.4. 修改表
ALTER [IGNORE] TABLE tbl_name
alter_specification [, alter_specification] ...
IGNORE 忽略主健重复的错误,如果重复,采用第一条,其余删除
例子:同时多个操作
mysql> ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;
例子, 重命名 INTEGER 字段,从 a 到 b:
mysql> ALTER TABLE t1 CHANGE a b INTEGER;
例子 修改字段类型,仍然需要新旧字段名称,即使字段名称相同:
mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;
也可以使用modify
mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;
在mysql 5.0 可以使用FIRST or AFTER 字段来添加add 字段,默认是在最后
modify change 也可以使用
在mysql 5.0 InnoDB 存储引擎支持 ALTER TABLE 删除外健:
mysql>ALTER TABLE yourtablename DROP FOREIGN KEY fk_symbol;
例子:
创建表
mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10));
重命名表
mysql> ALTER TABLE t1 RENAME t2;
MODIFY a 字段为 TINYINT NOT NULL , 并且 change 字段 b,从 CHAR(10) 到 CHAR(20) 并改名为c:
mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);
添加新字段 d:
mysql> ALTER TABLE t2 ADD d TIMESTAMP;
在a d 上增加索引:
mysql> ALTER TABLE t2 ADD INDEX (d), ADD INDEX (a);
删除字段c:
mysql> ALTER TABLE t2 DROP COLUMN c;
添加一个自动增长的字段c ,并且添加c 为主健:
mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> ADD PRIMARY KEY (c);
2.4.4.1. 修改外键
语法:
ALTER TABLE tbl_name
ADD [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
例子:
create table aa (id1 int not null,name varchar(20),primary key(id1))type=InnoDB;
create table b(id2 int not null,lessonname varchar(20),primary key(id2))type=InnoDB;
alter table b add FOREIGN KEY id (id2) references aa(id1);
2.4.5. 删除
删除数据库
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
例子: mysql>drop DATABASE IF EXISTS testdb;
删除表
DROP [TEMPORARY] TABLE [IF EXISTS]
tbl_name [, tbl_name] ...
[RESTRICT | CASCADE]
例子: mysql>drop TABLE IF EXISTS testTable;
删除索引
DROP INDEX index_name ON tbl_name
例子: mysql>drop index testIndex on testTable;
重命名
RENAME TABLE tbl_name TO new_tbl_name
[, tbl_name2 TO new_tbl_name2] ...
例子:RENAME TABLE current_db.tbl_name TO other_db.tbl_name;
2.5. 数据库备份恢复
数据库备份
数据库备份命令：
mysqldump --opt --user=用户名 --password=密码 --default_character-set=字符集 -B数据库> 输出的sql文件
例子:
E:\mysql>Mysqldump --user=ptsdb --password=ptsdb --default_character-set=ujis --opt pts>dump.sql
参见批处理文件
数据库导入命令：
mysql --user=用户名 --password=密码 --default_character-set=字符集 [数据库]<导入的sql 语句
例子： 对于InnoDB（没有设定字符集）
Following mysqldump import example for InnoDB tables is at least 100x faster than previous examples.
1. mysqldump --opt --user=username --password database > dumbfile.sql
2. Edit the dump file and put these lines at the beginning:
SET AUTOCOMMIT = 0;
SET FOREIGN_KEY_CHECKS=0;
3. Put these lines at the end:
SET FOREIGN_KEY_CHECKS = 1;
COMMIT;
SET AUTOCOMMIT = 1;
4. mysql --user=username --password database < dumpfile.sql
参数说明：
--add-drop-database
Add a DROP DATABASE statement before each CREATE DATABASE statement.
--add-drop-table
Add a DROP TABLE statement before each CREATE TABLE statement.
--all-databases, -A
Dump all tables in all databases. This is the same as using the --databases option and naming all the databases on the command line.
--databases, -B
Dump several databases. Normally, mysqldump treats the first name argument on the command line as a database name
and following names as table names. With this option, it treats all name arguments as database names.
CREATE DATABASE IF NOT EXISTS db_name and USE db_name statements are included in the output before each new database.
--host=host_name, -h host_name
Dump data from the MySQL server on the given host. The default host is localhost.
--opt
This option is shorthand; it is the same as specifying --add-drop-table --add-locks --create-options
--disable-keys --extended-insert --lock-tables --quick --set-charset.
It should give you a fast dump operation and produce a dump file that can be reloaded into a MySQL server quickly.
In MySQL 5.0, --opt is on by default, but can be disabled with --skip-opt.
To disable only certain of the options enabled by --opt, use their --skip forms;
for example, --skip-add-drop-table or --skip-quick.
还有一些其他参数，有兴趣可以学习
2.6. 表数据备份
mysql>use test;
mysql> CREATE TABLE imptest(id INT, n VARCHAR(30));
Query OK, 0 rows affected (0.03 sec)
方法一：
导出使用：Mysqldump
E:\mysql>mysqldump -uptsdb -pptsdb -where "id>='100'"
test imptest
E:\mysql>mysqldump -uptsdb -pptsdb test imptest>e:\mysql\imp\imptest2.txt
导入使用 mysql
mysql -uptsdb -pptsdb< imptest2.txt
方法二：
导出使用 select into OUTFILE
mysql> select * from imptest where id=101 into OUTFILE 'e:\\mysql\\imp\\test3.txt' FIELDS TERMINATED BY ',';
导入使用 LOAD DATA INFILE
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[FIELDS
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char' ]
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number LINES]
[(col_name_or_user_var,...)]
[SET col_name = expr,...]]
例子：
mysql> LOAD DATA INFILE 'e:\\mysql\\imp\\test3.txt' INTO TABLE imptest FIELDS TERMINATED BY ',';
导入使用mysqlimport：
E:\mysql>mysqlimport -uptsdb -pptsdb --local test E:\mysql\imp\imp.txt
mysqlimport: Error: Table 'test.imp' doesn't exist, when using table: imp
E:\mysql>mysqlimport -uptsdb -pptsdb --local test E:\mysql\imp\imptest.txt
test.imptest: Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
注意：文件名必须跟表名相同
参数
-d or --delete 新数据导入数据表中之前删除数据数据表中的所有信息
-f or --force 不管是否遇到错误，mysqlimport将强制继续插入数据
-i or --ignore mysqlimport跳过或者忽略那些有相同唯一
-r or -replace 这个选项与－i选项的作用相反；此选项将替代 表中有相同唯一关键字的记录
2.7. 数据管道导入导出
E:\mysql>mysql -h10.5.1.66 -uroot -proot --default-character-set=name frontdb_20060415(databasename)
>e:/mysql/test.txt (输出的sql 语句)
insert into emp_dept() values
("R&D",1,"Dennis-1"),
("DEv",2,"Dennis-2"),
("R&D",3,"Dennis-3"),
("Test",4,"Dennis-4"),
("Test",5,"Dennis-5");
("dddd",20,"eeee");
>> left join
-------------
select a.id,a.name,b.dept_id
from emp a left join emp_dept b on (a.id=b.emp_id);
mysql> select a.id,a.name,b.dept_id
-> from emp a left join emp_dept b on (a.id=b.emp_id);
+----+-----------+---------+
| id | name | dept_id |
+----+-----------+---------+
| 1 | Dennis-1 | R&D |
| 2 | Dennis-2 | DEv |
| 3 | Dennis-3 | R&D |
| 4 | Dennis-4 | Test |
| 5 | Dennis-5 | Test |
| 6 | Dennis-6 | NULL |
| 7 | Dennis-7 | NULL |
| 8 | Dennis-8 | NULL |
| 9 | Dennis-9 | NULL |
| 10 | Dennis-10 | NULL |
+----+-----------+---------+
# 挑出 table emp 中有而 table emp_dept 中没有的人员资料
select a.id,a.name,b.dept_id
from emp a left join emp_dept b on (a.id=b.emp_id)
where b.dept_id IS NULL;
mysql> select a.id,a.name,b.dept_id
-> from emp a left join emp_dept b on (a.id=b.emp_id)
-> where b.dept_id IS NULL;
+----+-----------+---------+
| id | name | dept_id |
+----+-----------+---------+
| 6 | Dennis-6 | NULL |
| 7 | Dennis-7 | NULL |
| 8 | Dennis-8 | NULL |
| 9 | Dennis-9 | NULL |
| 10 | Dennis-10 | NULL |
+----+-----------+---------+
# 把 table emp_dept 放在左边的情形(当然以 emp_dept 中的数据为基础来显示资料,emp 中比emp_dept 中多的资料也就不会显示出来了):
select a.id,a.name,b.dept_id
from emp_dept b left join emp a on (a.id=b.emp_id);
mysql> select a.id,a.name,b.dept_id
-> from emp_dept b left join emp a on (a.id=b.emp_id);
+------+----------+---------+
| id | name | dept_id |
+------+----------+---------+
| NULL | NULL | dddd |
| 2 | Dennis-2 | DEv |
| 1 | Dennis-1 | R&D |
| 3 | Dennis-3 | R&D |
| 4 | Dennis-4 | Test |
| 5 | Dennis-5 | Test |
+------+----------+---------+
>> right join
---------------
select a.id,a.name,b.dept_id
from emp a right join emp_dept b on (a.id=b.emp_id);
# 挑资料时以右边 table emp_dept 中的资料为基础来显示资料
mysql> select a.id,a.name,b.dept_id
-> from emp a right join emp_dept b on (a.id=b.emp_id);
+------+----------+---------+
| id | name | dept_id |
+------+----------+---------+
| NULL | NULL | dddd |
| 2 | Dennis-2 | DEv |
| 1 | Dennis-1 | R&D |
| 3 | Dennis-3 | R&D |
| 4 | Dennis-4 | Test |
| 5 | Dennis-5 | Test |
+------+----------+---------+
6 rows in set (0.00 sec)
# 我们再把 table 的位置交换一下,再用 right join 试试
select a.id,a.name,b.dept_id
from emp_dept b right join emp a on (a.id=b.emp_id);
mysql> select a.id,a.name,b.dept_id
-> from emp_dept b right join emp a on (a.id=b.emp_id);
+----+-----------+---------+
| id | name | dept_id |
+----+-----------+---------+
| 1 | Dennis-1 | R&D |
| 2 | Dennis-2 | DEv |
| 3 | Dennis-3 | R&D |
| 4 | Dennis-4 | Test |
| 5 | Dennis-5 | Test |
| 6 | Dennis-6 | NULL |
| 7 | Dennis-7 | NULL |
| 8 | Dennis-8 | NULL |
| 9 | Dennis-9 | NULL |
| 10 | Dennis-10 | NULL |
+----+-----------+---------+
# 是不是和 left join 一样了?
>> inner join STRAIGHT_JOIN
select a.id,a.name,b.dept_id
from emp a ,emp_dept b
where a.id=b.emp_id;
mysql> select a.id,a.name,b.dept_id
-> from emp a ,emp_dept b
-> where a.id=b.emp_id;
+----+----------+---------+
| id | name | dept_id |
+----+----------+---------+
| 2 | Dennis-2 | DEv |
| 1 | Dennis-1 | R&D |
| 3 | Dennis-3 | R&D |
| 4 | Dennis-4 | Test |
| 5 | Dennis-5 | Test |
+----+----------+---------+
2.9.3. 别名 alias
你可以在GROUP BY、ORDER BY或在HAVING部分中使用别名引用列。别名也可以用来为列取一个更好点的名字：
SELECT SQRT(a*b) as rt FROM table_name GROUP BY rt HAVING rt > 0;
SELECT id,COUNT(*) AS cnt FROM table_name GROUP BY id HAVING cnt > 0;
SELECT id AS "Customer identity" FROM table_name;
注意，你的 ANSI SQL 不允许你在一个WHERE子句中引用一个别名。这是因为在WHERE代码被执行时，列值还可能没有终结。例如下列查询是不合法：
SELECT id,COUNT(*) AS cnt FROM table_name WHERE cnt > 0 GROUP BY id;
WHERE语句被执行以确定哪些行应该包括GROUP BY部分中，而HAVING用来决定应该只用结果集合中的哪些行。
2.9.4. 正则
正则表达式（regex）是定义复杂查询的一个强有力的工具。
这里是一个简单的资料，它忽略了一些详细的信息。
正则表达式定义了一个字符串的规则。最简单的正则表达式不包含任何保留字。例如，
mysql> select dateFunction(1);
+----------------------+
| dateFunction(1) |
+----------------------+
| Monday November 2005 |
+----------------------+
1 row in set (0.00 sec)
mysql> select dateFunction(2);
+---------------------+
| dateFunction(2) |
+---------------------+
| 2005-11-14 15:05:43 |
+---------------------+
1 row in set (0.00 sec)
2.11. 补充：trigger
CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt
CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
b4 INT DEFAULT 0
);
DELIMITER |
CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END|