Mysql

• Mysql is a database manage system(DBMS)which is open source code
• Three part of SQL
  • DDL(data define language)
  • DML(data manage language)
  • DCL(data control language)

DDL(some operations to table,schema,domain,index and view)

Here are some command line of the mysql:

• CREATE(will make sure the name of table and column, the type of data and a construction)
  • CREATE TABLE CD( CD_ID INTEGER NOT NULL,CD_TITLE CHARCTER(30);)
• ALTER(which can realize the change function)
  • ALTER TABLE CD ADD NO_DISCS INTEGER;
• DROP(delete this table/or some data from the database)
  • DROP TABLE CD;

1. line: create database NAME;
   
2. line:show databases;
   
3. line:drop(=delete) database NAME;
   line: and reshow the list of database->show databases;
   
4. enter a specify database:line->use NAME;
   
5. view the database currently in use:line-> select database();
   If there no database is linked, the statu will be NULL.
   Here: the statu is successful to link.
   

The line of table

To create a new database t for table line

1. Line: create data table

CREATE TABLE the_name_of_table(
the_name_of_column1 the_type_of_data [limitation],
the_name_of_column2 the_type_of_data [limitation],
the_name_of_column3 the_type_of_data [limitation]
);

create table:student, and the column: name, age,sex.
And view the information of the column.


2. Alter table:line-> alter table Name add new_column_name type

3. change the column->line: alter table Name change previous_name new_name new_type

4. delete the column->line: alter table Name drop column_name

5. change the name of table->line :alter table previous_name rename new_name;

6. view the detail of created table：line-> show create table Name;
7. change the character (if it’s already utf8,not change it)
Line->alter table Name charecter set 编码方式;

DML(be used to manage the data part of database)

• SELECT
  -SELECT * FROM CD WHERE CD_COMPANY='CBS MUSIC';//return all rows of this circumstance
• UPDATE
• DELETE
• INSERT(用户能够直接向表中添加数据）

1. insert operation

• insert into table_name/column_name values;
• eg:insert into user(username,userage,usersex,birthday)values(‘xx’,18,‘a’,‘2000-1-1’);
  
• some tips:
  • 隔断用逗号
  • 列名和值要一一对应
  • 非数值的列值两侧要加单引号
  • 添加数据的时候可以将列名省略，但必须是当给所有列添加数据
  • 如果插入空值用NULL
  • 插入日期也需要用引号
  • 参数值不要超出列定义的长度
• insert more than one row
• eg:insert into user(username,userage,usersex,birthday)values(‘xx’,18,‘a’,‘2000-1-1’),(‘yy’,19,‘F’),(‘zz’,20,‘M’);
  

2. update operations

• change a column’s value together
• line->:update Name_of _table set column1=value1,column2=value2,column3=value3…where columnx=value(if change everything will not need this where syntax)
  “where be used in a specific situation”
  
  
• Change multiple columns in a row
  ->line: update user set username=‘usr’,userage=‘20’ where sex=‘M’;

3. supply of Operators

• Arithmetic operators：+ - * /
• Assignment operators：from right to left :=
• logic operators: and,or,not
• relationship operators :>,<,>=,<=,=,<>(not equal)
• tips
  • line: xxx=’ ’ is same with xxx is null;
  • line: age!=10 or age!=20;(right) age!=10 or 20;(wrong)
    

4. delete operations

• delete all or delete a part
• line->: delete from table_name where column=‘value’;
• delete all information of table(the information lost but the table still at here)
  4.1 the second way of delete:truncate table table_name;
  与delete的区别： DELETE 删除表中的数据，表结构还在;删除后的数据可以找回
• TRUNCATE 删除是把表直接DROP掉，然后再创建一个同样的新表。
• 删除的数据不能找回。执行速度比DELETE快。