几年前初学mysql的笔记(整理)

/****************************************************************************
//这里主要记录在建表时所用的一些SQL语法以及建立整个表的过程。
****************************************************************************/
 //运行MYSQL的方法：
 //到DOS环境下，进入到mysql/bin>的目录下：
D:mysql/bin> mysqld
D:mysql/bin> mysqld-nt --standalone
D:mysql/bin>    mysql --user=root;这表示是以root身份进入的，这是mysql默认的管理员
 //即可启动mysql.

 //关闭mysql，首先要退出mysql回到mysql/bin>目录下.
D:mysql/bin> mysqladmin -u root shutdown 或者
D:mysql/bin> mysqladmin --user=root shutdown
 //即可关闭mysql.

 //在启动好mysql后，便可直接进入mysql的环境中，输入mysql回车即可。
D:mysql/bin> mysql

mysql> show databases;
 //显示当前存在的数据库.

mysql> use test;
 //选择数据库并显示当前选择的数据库. 注：这一步必须要有，否则下面的show tables;命令将报错。

mysql> show tables;
 //显示表名;

mysql> desc employee;
 //查看表结构和属性

mysql> create table test (date date, date_time datetime, time_stamp timestamp);
 //创建test表以及字段和字段类型

mysql> insert into test values("1998-12-31","1998-12-31 23:59:59",19981231235959");
mysql> insert into test values("1999-01-01","1999-01-01 00:00:00",19990101000000");
mysql> insert into test values("1999-09-09","1999-09-09 23:59:59",19990909235959");
mysql> insert into test values("2000-01-01","2000-01-01 00:00:00",20000101000000");
mysql> insert into test values("2000-02-28","2000-02-28 00:00:00",20000228000000");
mysql> insert into test values("2000-02-29","2000-02-29 00:00:00",20000229000000");
mysql> insert into test values("2000-03-01","2000-03-01 00:00:00",20000301000000");
mysql> insert into test values("2000-12-31","2000-12-31 23:59:59",20001231235959");
mysql> insert into test values("2001-01-01","2001-01-01 00:00:00",20010101000000");
mysql> insert into test values("2004-12-31","2004-12-31 23:59:59",20041231235959");
mysql> insert into test values("2005-01-01","2005-01-01 00:00:00",20050101000000");
 //向表test里面插入相应的记录，按顺序插入。

mysql> select * from text; //查看表的所有字段以及字段内容
 +------------+---------------------+----------------+
 | date       | date_time           | time_stamp     |
 +------------+---------------------+----------------+
 | 1998-12-31 | 1998-12-31 23:59:59 | 19981231235959 |
 | 1999-01-01 | 1999-01-01 00:00:00 | 19990101000000 |
 | 1999-09-09 | 1999-09-09 23:59:59 | 19990909235959 |
 | 2000-01-01 | 2000-01-01 00:00:00 | 20000101000000 |
 | 2000-02-28 | 2000-02-28 00:00:00 | 20000228000000 |
 | 2000-02-29 | 2000-02-29 00:00:00 | 20000229000000 |
 | 2000-03-01 | 2000-03-01 00:00:00 | 20000301000000 |
 | 2000-12-31 | 2000-12-31 23:59:59 | 20001231235959 |
 | 2001-01-01 | 2001-01-01 00:00:00 | 20010101000000 |
 | 2004-12-31 | 2004-12-31 23:59:59 | 20041231235959 |
 | 2005-01-01 | 2005-01-01 00:00:00 | 20050101000000 |
 +------------+---------------------+----------------+

mysql> create table employee (name varchar(20), sex char(1), birth date, birthadd varchar(20));
 //创建employee表以及字段和字段类型

mysql> insert into employee values('Allen','f','1982-01-01','WuHoo');
mysql> insert into employee values('myBrother','f','1980-01-31','WuHoo');
mysql> insert into employee values('myFather','f','1948-01-18','WuHoo');
mysql> insert into employee values('myMother','m','1958-12-21','WuHoo');
mysql> insert into employee values('myWife','m','1979-04-05','XuZou');
 //向表employee里面插入记录

mysql> update employee set birth="1982-01-25" where name="Allen";
 //更新表字段里面输入错误的内容

mysql> alter table employee drop column birthadd;
 //由于在建表时一时疏忽将employee表中的birthaddr写成了birthadd所以要先将此字段先删除

mysql> alter table employee add birthaddr varchar(20);
 //在employee表中插入birthaddr这个字段定义类型为varchar型，长度为20。

mysql> update employee set birthaddr = "WuHoo" where sex = "f";
mysql> update employee set birthaddr = "WuHoo" where birth = "1958-12-21";
mysql> update employee set birthaddr = "XuZou" where name = "myWife";
 //对birthaddr字段内容进行更新插入

mysql> select * from employee;
 //查看表里面所有字段的所有记录，看是否内容已更新。

mysql> create table family (id int(3) primary key, name varchar(20), sex char(1), birth date, birthadd varchar(20));
 //由于在建表employee时没有定义主键所以应该定义主键来唯一识别。

mysql> select * from family;
 //查看表里是否有记录，如出现Empty set (0.00 sec)表示无记录。

mysql> desc family;
 //查看表结构与表的类型.

mysql> insert into family values('1','Allen','f','1982-01-01','WuHoo');
 //插入第一条记录。有时对于ID不需要自己添加，系统会自行增加，这主要取决于数据库系统的支持。

mysql> insert into family values('2','myBrother','f','1980-01-31','WuHoo');
 //插入第二条记录。

mysql> insert into family values('3','myFather','f','1948-01-18','WuHoo');
 //插入第三条记录.

mysql> insert into family values('4','myMother','m','1958-12-21','WuHoo');
 //插入第四条记录.

mysql> insert into family values('5','myWife','m','1979-04-05','XuZou');
 //插入第五条记录。

mysql> insert into family values('0','myLover','m','1983-09-09','WuHoo');
 //插入第六条记录。

mysql> update family set id = "6" where name = "myLover";
 //更新第六条记录的ID。

mysql> select * from family;
 //查看表里面的记录内容。

mysql> select * from family where name = "Allen";
 //查看选择的特定行.

mysql> select * from family where sex = "f" and birthaddr = "WuHoo";
 //用组合条件进行查询.

mysql> select * from family order by birth;
 //对表中的记录按生日大小进行排序.

mysql> select * from family order by birth desc;
 //对表中的记录用DESC来进行逆序排序.

mysql> select count(*) from family;
 //行计数;count()函数用于对非null结果的记录进行计数.

mysql> select sex, count(*) from family group by sex;
 //用来对于表中的记录进行男女数量的统计：使用了broup by对sex进行了分组.

mysql> create table article (bookID int(3), writer varchar(20) not null, title varchar(40) not null, senddate date,primary key(bookID,title));
 //创建与family相关联的article表。设定两个主键.

mysql> insert into article values('1','Allen','水孟年华','2004-08-03');
 //插入第一条记录。

mysql> insert into article values('2','Allen','飘雨如生','2004-08-03');
 //插入第二条记录。

mysql> insert into article values('3','Allen','提问的智慧','2004-08-03');
 //插入第三条记录。

mysql> insert into article values('4','Allen','人月神话','2004-08-03');
 //插入第四条记录。

mysql> insert into article values('5','myWife','当我遇上你','2004-08-03');
 //插入第五条记录。

mysql> commit;
 //提交记录.

mysql> select name,sex,title from family,article where name=writer and name='Allen';
 //多表查询(现有family和article两张表)，进行组合查询，查询作者Allen的姓名、性别、文章
 //注意：如果第二个表article中的writer列也取名为name（与family表中的name列相同）而不是writer时，就必须用family.name和article.name表示，以示区别。

mysql> select title,writer,birthaddr,birth from family,article where family.name=article.writer and name='myWife';
 //又是一例多表查询，不过此处于上面有所不同主要区别在于family.name和article.writer。

mysql> alter table family add column truename varchar(20);
 //对数据库表和数据库进行修改和删除的操作如：增加一列"实名"字段

mysql> update family set truename='艾伦' where name='Allen';
 //对"实名"字段进行更新操作。

mysql> update family set truename='aaaa where name='myBrother';
mysql> update family set truename='bbbb' where name='myFather';
mysql> update family set truename='cccc' where name='myMother';
mysql> update family set truename='dddd' where name='myWife';
mysql> update family set truename='eeee' where name='myLover';

mysql> insert into family values ("7","abc","f","1989-06-08","AnHui","abc");
 //插入了一条无用的记录.
mysql> delete from family where name='abc';
 //删除对应条件的无用记录。
mysql> drop table ****(表1的名字), ****(表2的名字);
 //可以删除一个或多个表，小心使用,不可恢复。
mysql> drop database 数据库名;
 //数据库的删除：小心使用。

 //数据库的备份：回到DOS。
mysql> quit;

D:mysql/bin>mysqldump --opt test>test.dbb
 //完成备份.

 //用批处理方式使用MySQL:
 //首先建立一个批处理文件mytest.sql,内容如下： (该文件必须放入bin目录下)
 use test;
 select * from family;
 select name,sex from family where name='Allen';

D:mysql/bin>mysql < mytest.sql
 //在屏幕上会显示执行结果。
 //如果想看结果，而输出结果很多，则可以用这样的命令：mysql < mytest.sql | more
 //我们还可以将结果输出到一个文件中：mysql < mytest.sql > mytest.out

mysql> int mysql_affected_rows([int link_id]);
mysql> int mysql_connect(string [hostname] [:port], string [username], string [password]);

mysql> select user, Update_priv from user;
 //+------+-------------+
 //| user | Update_priv |
 //+------+-------------+
 //| root | N           |
 //| root | Y           |
 //|      | N           |
 //|      | N           |
 //+------+-------------+
4 rows in set (0.00 sec)

mysql> select user();
 //查看是以什么用户身份登陆的.
mysql> set password for abc@"localhost"=password('xyz');
 //更新密码

mysql> select * from user;
 //+-----------+------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+
 //| Host      | User | Password         | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections |
 //+-----------+------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+
 //| localhost | root | 6f036d8834f82807 | Y           | Y           | Y    | Y           | Y           | Y         | Y           | Y             | Y        | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y    | Y               | Y                |          |            |             |             |             0 |           0 |               0 |
 //| build     | root |                  | Y           | Y           | Y    | Y           | Y           | Y         | Y           | Y             | Y        | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y    | Y               | Y                |          |            |             |             |             0 |           0 |               0 |
 //| localhost |      |                  | N           | N           | N    | N           | N           | N         | N           | N             | N        | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N    | N               | N                |          |            |             |             |             0 |           0 |               0 |
 //| build     |      |                  | N           | N           | N    | N           | N           | N         | N           | N             | N        | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N    | N               | N                |          |            |             |             |             0 |           0 |               0 |
 //+-----------+------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+
 //4 rows in set (0.00 sec)

mysql> grant all on permission.* to permission@localhost identified by "permission";
 //就把permission数据库的权限赋给了permission用户.
mysql> flush privileges;
 //刷新记录.