/****************************************************************************
//这里主要记录在建表时所用的一些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 * 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;
//刷新记录.
分享到:
相关推荐
oracle-MySQL笔记整理资料 适合初学者
个人初学MYSQL笔记
MySQL笔记
自己做的笔记,初学者和复习者可以看下,很有用,有兴趣的可以看一下,有详细的查询语句什么的
ruby初学笔记ruby初学笔记ruby初学笔记ruby初学笔记ruby初学笔记
高手总结Java初学者学习经验笔记整理.pdf
mysql学习笔记MYSQL使用注意事项
MYSQL开发学习笔记适合初学者
mybatis学习笔记整理,参考B站狂神学习。
MySQL学习笔记(初学者总结而得)
MySQL超详细笔记整理,层次结构清晰,内容详实丰富,适合MySQL初学者和进阶学习者使用。 这份详尽的MySQL笔记是一个宝贵的资源,非常适合那些想要深入理解和掌握MySQL数据库管理系统的人。它全面覆盖了MySQL的关键...
非常好的java笔记适合初学者,培训班老师上课给的笔记,非常细致全面,适合初学者
JSP 初学者 笔记 示例代码JSP 初学者 笔记 示例代码JSP 初学者 笔记 示例代码JSP 初学者 笔记 示例代码JSP 初学者 笔记 示例代码
Web前端HTML5 CSS3初学者零基础入门全套学习笔记 Web前端HTML5 CSS3初学者零基础入门全套学习笔记 Web前端HTML5 CSS3初学者零基础入门全套学习笔记 Web前端HTML5 CSS3初学者零基础入门...
python初学者笔记
学习天池训练营mysql后根据里面做的初学笔记
Visual Basic 编程初学者笔记Visual Basic 编程初学者笔记Visual Basic 编程初学者笔记
作为初学者总结的mysql 入门笔记。我是做前端开发的,总结了下感觉对初学者很有帮助
适用于MySQL初学者,以及平时使用知识点回顾,里面内容基本覆盖了日常SQL代码的编写教学
这是我整理的一些MySQL基础部分的笔记,希望可以帮到和我一样的初学者们!